1.多表查询

1.1连接查询

  1. select * from dept,emp;

上面的查询中存在大量错误的数据，一般我们不会直接使用这种查询。

笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。

1.2.内连接查询

—-1 隐式内连接

虽然笛卡尔积查询中包含大量错误数据，但我们可以通过where子句将错误数据剔除，保留下来的就是正确数据。

  1. -- 条件 员工所属的部门编号 和 部门的编号 相等 
  2.     select * from dept,emp where dept_id = id; --错误 这里的部门编号要指定 
  3. Q：(通过where子句将错误的数据剔除 保留正确的数据)
  4.     select * from dept,emp where dept_id = dept.id;eg 查询员工表的名称，性别。部门表的名称
  5.     select emp.name,gendre,dept.name from emp,dept where emp.dept_id = dept.id; 
  6.   SELECT  t1.name, -- 员工表的姓名     
  7.           t1.gender,-- 员工表的性别     
  8.           t2.name -- 部门表的名称  
  9.    FROM     emp t1, dep t2  
  10.    WHERE       t1.`dept_id` = t2.`id`;

—-2.显示内连接

语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
例如：

  1.      表1：多对一的多（查询的主要内容） 表2：为外键的关联的表（为关联的内容）
  2.     select * from emp inner join dept on emp.dept_id = dept.id; 
  3.     select * from emp join dept on emp.dept_id = dept.id;

内连接的使用：
1. 从哪些表中查询数据
2. 条件是什么
3. 查询哪些字段

1.3 外连接查询

—-1.左外连接：

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。（交集：右表中满足条件的部分）
例子：

  1. -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部 门名称。
  2. select * from emp left join dept d on dept_id =d.id ;

左外连接查询：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。

—-2.右外连接：

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。交集部分同样指左表满足条件的部分
例子：查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部 门名称。

  1. SELECT     * FROM dept t2  RIGHT JOIN emp t1  ON t1.`dept_id` = t2.`id`;

右外连接查询：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。
注意：
这里可以发现，左外连接和右外连接是一个相似的过程，只是左右的顺序调换一下。所以这两种写法可以互相转换。平时中没有特殊声明可以直接使用左外连接。

扩展：如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录)，可以使用全外连接查询，但是mysql又不支持全外连接查询。
使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录。

  1.     select * from dept left join emp on emp.dept_id = dept.id 
  2. union 
  3.     select * from dept right join emp on emp.dept_id = dept.id;

注意 ：union可以将两条SQL语句执行的结果合并有前提:
(1)两条SQL语句查询的结果列数必须一致
(2)两条SQL语句查询的结果列名、顺序也必须一致
并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用union all)

2.子查询

2.1概念：

查询中嵌套查询，称嵌套查询为子查询。

  1. eg：列出薪资比'王海涛'的薪资高的所有员工，显示姓名、薪资
  2. select sal from emp where name = '王海涛';-- 王海涛的薪资 ：2450
  3. select name sal from emp where sal > 2450; 
  4. select name sal from emp  where sal>(select sal from emp where name ='王海涛');
  6. eg: 列出与'刘沛霞'从事相同职位的所有员工，显示姓名、职位。
  7. select name,job from emp  where job = (select job from emp where name = '刘沛霞');
  9. eg: 列出薪资比'大数据部'部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资
  10. 和部门名称。如果不考虑没有部门的员工
  11.     select emp.name,sal,dept.name 
  12.     from emp,dept  
  13.   where 
  14.       emp.dept_id = dept.id  
  15.   and 
  16.       sal >(select max(sal) from emp where dept_id = 30);
  18. 如果加上没有部门的员工
  19.     select emp.name,sal,dept.name 
  20.   from emp left join dept  
  21.   on 
  22.       emp.dept_id = dept.id 
  23.   and 
  24.       sal >(select max(sal) from emp where dept_id = 30);

2.1查询情况：

1. 子查询的结果是单行单列的：

  • 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

2. 子查询的结果是多行单列的：

  • 子查询可以作为条件，使用运算符in来判断 ```plsql — 查询’财务部’和’市场部’所有的员工信息 SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’; //id=2 and id=3 SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

— 子查询 SELECT * FROM emp
WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);

  1. **3. 子查询的结果是多行多列的：**
  3.    -  子查询可以作为一张虚拟表参与查询
  4. ```plsql
  5. -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 
  6. -- 子查询 
  7. SELECT * 
  8. FROM dept t1 , (SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 
  9. WHERE 
  10.     t1.id = t2.dept_id; 
  12. -- 普通内连接 
  13. SELECT * 
  14. FROM 
  15.     emp t1,dept t2  
  16. WHERE 
  17.     t1.`dept_id` = t2.`id`  AND t1.`join_date` >  '2011-11-11'

多表查询练习

  1. eg: 列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
  2. /* emp e1 员工表, emp e2 上级表 (一张表起两个别名,可以当作两张表来查)  
  3.  * 查询的列: e1.name, e2.id, e2.name  
  4.  * 显示的列 e1.name,e1.topid,e2.name  
  5.  * 查询的表: emp e1, emp e2  
  6.  * 连接条件: e1.topid=e2.id  
  7.  */ 
  8.  select 
  9.      e1.name,e2.id,e2.name 
  10.  from 
  11.      emp e1,emp e2 
  12.  where 
  13.      e1.topid = e2.id;  
  15. eg:列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资
  16. select min(sal) from emp group by job --分组求出最低薪资 
  17. select job,min(sal) from emp -- 对分组之后添加条件  最低薪资>1500 
  18. group by job having min(sal)>1500;
  20. eg:列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
  21. select dept_id,count(id),avg(sal) from emp group by dept_id;
  23. eg:.查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
  24. select
  25.     dept_id,dept.name,loc,count(*) 
  26. from 
  27.     dept,emp  
  28. where 
  29.     dept_id = dept.id group by dept_id having count(*) >=1 ;
  31. eg:列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
  32. select
  33.     e1.id 编号,e1.name 姓名,d.name 部门 
  34. from 
  35.     emp e1,emp e2,dept d 
  36. where 
  37.     e1.dept_id = d.id and e1.topid = e2.id and e1.hdate<e2.hdate;
  39. eg:查询员工表中薪资最高的员工信息
  40. 方式一：
  41.  SELECT * FROM emp 
  42.  WHERE 
  43.   sal = (select max(sal) FROM emp) ;  
  44. 方式二：
  45. SELECT * FROM emp ORDER BY sal DESC LIMIT 0,1;

2.3where中不能使用列别名

（1）SQL语句的书写顺序:

  1. select * | 列名 -- 确定要查询的列有哪些 
  2. from 表名 -- 确定查询哪张表 
  3. where 条件 -- 通过筛选过滤，剔除不符合条件的记录 
  4. group by 分组的列 -- 指定根据哪一列进行分组 
  5. having 条件 -- 通过条件对分组后的数据进行筛选过滤 
  6. order by 排序的列 -- 指定根据哪一列进行排序 
  7. limit (countPage-1)*rowCount, rowCount -- 指定返回第几页记录以及每页显示多少条

（2）SQL语句的执行顺序:

  1. from 表名 -- 确定查询哪张表 
  2. where 条件 -- 通过筛选过滤，剔除不符合条件的记录 
  3. select * | 列名 列别名 -- 确定要查询的列有哪些， 
  4. group by 分组的列 -- 指定根据哪一列进行分组 
  5. having 条件 -- 通过条件对分组后的数据进行筛选过滤 
  6. order by 排序的列 -- 指定根据哪一列进行排序 
  7. limit (countPage-1)*rowCount, rowCount

关于where中不能使用列别名但是可以使用表别名?
是因为，表别名是声明在from中，from先于where执行 ，先声明再使用没有问题，但是列别名是声明在select中，where先于select执行，如果先使用列别名，再声明，这样执行会报错!!

3.事务简介

3.1概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

3.2操作

1. 开启事务： start transaction;
2. 回滚：rollback;
3. 提交：commit;

3.3例子

·在多个步骤的执行操作的过程中如果出现错误，则后续的业务就不会执行。这样业务的执行可能就会造成极大的问题，必须保证业务在执行的过程中的统一性， 就需要开启事务，当一个业务正常执行完成之后提交事务，否则进行回滚，回到事务开启时的状态。开启事务之后，一定要提交否则执行的操作只在临时有效，关闭窗口或者新建连接时没有任何效果，只有提交之后才能持久化数据库。 ·MySQL的原子性保证了每条SQL语句为一个执行步骤，并且MySQL自动提交事务。所以一条SQL语句即完成了持久化操作。

  1. CREATE TABLE account (     
  2.   id INT PRIMARY KEY AUTO_INCREMENT,     
  3.   NAME VARCHAR(10),     
  4.   balance DOUBLE ); 
  5.   -- 添加数据 
  6.   INSERT INTO account (NAME, balance)  VALUES 
  7.   ('zhangsan', 1000), ('lisi', 1000); 
  9.   SELECT * FROM account;         
  10.   UPDATE account SET balance = 1000; -- 张三给李四转账 500 元         
  11.   -- 0. 开启事务 
  12.   START TRANSACTION;
  13.   -- 1. 张三账户 -500     
  14.   UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; 
  15.   -- 2. 李四账户 +500 
  16.   -- 出错了... 
  17.   UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; 
  19.   -- 发现执行没有问题，提交事务 
  20.   COMMIT;         
  21.   -- 发现出问题了，回滚事务 
  22.   ROLLBACK;

3.4 事务提交

MySQL数据库中事务默认自动提交
(1)事务提交的两种方式：
* 自动提交：
mysql就是自动提交的
 一条DML(增删改)语句会自动提交一次事务。
* 手动提交：
Oracle 数据库默认是手动提交事务
 需要先开启事务，再提交
(2)修改事务的默认提交方式：

  1. * 查看事务的默认提交方式：SELECT @@autocommit;             
  2. -- 1 代表自动提交  0 代表手动提交             
  3. * 修改默认提交方式： set @@autocommit = 0;

4.事务的特性

4.1事务的四大特性：ACID

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变

一致性：指出系统从一个正确的状态到另一个正确的状态（总量不变） AID都是属于数据库的特性，就是依赖数据库的具体实现，而一致性体现应用层，即用户的设定。上文中说到的正确的状态就是满足我们设定的状态。 举例说明： 在转账的过程中A，B用户分别拥有1000元， 一致性不允许 ：A—>B 500，但是B没有收到，A:500，B:1000这种情况。 但是满足没有特殊设定下，A—>B 1200：： A:-200 B:2200, 这种违反我们自己业务逻辑，但是不违反一致性。

4.2.隔离级别：

  • 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题（类似于并发），设置不同的隔离级别就可以解决这些问题。
    存在问题：
    1. 脏读：一个事务，读取到另一个事务中没有提交的数据
    2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
    3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
     隔离级别：

    1.       1. read uncommitted：读未提交
    2.           * 产生的问题：脏读、不可重复读、幻读
    3.       2. read committed：读已提交 （Oracle）
    4.           * 产生的问题：不可重复读、幻读
    5.       3. repeatable read：可重复读 （MySQL默认）
    6.           * 产生的问题：幻读
    7.       4. serializable：串行化
    8.           * 可以解决所有的问题
    1.   * 注意：隔离级别从小到大安全性越来越高，但是效率越来越低<br />        * 数据库查询隔离级别：<br />            `* select @@tx_isolation;`<br />        * 数据库设置隔离级别：<br />            `* set global transaction isolation level  级别字符串;`