数据库的连接与事务

数据库的连接有内连接，外连接，外连接又分为左外连接，右外连接。
内连接:
根据查询条件不会显示查询条件不存在情况下的空值。
隐式内连接：select * from 表1，表2 where 主键=外键。
显示内连接：select * from 表1 inner join 表2 on 主键=外键 where 其他条件。
给表名设置别名select * from 表1 [as] 别名 inner join 表2 [as] 别名2 on 主键=外键 where 其他条件。
笛卡尔积：select * from 表1，表2; 1表与2表每行进行拼接形成中间虚拟表。
【0，1】与【0，1，2】做笛卡尔积：【0，0】【0，1】【0，2】【1，0】【1，1】【1，2】

左外连接：
左边的表是主表保留右表进行条件拼接
select * from 表1 left [outer] join 表2 on 主键=外键。
右外连接：
与左连接相反 右边的表是主表保留
select * from 表1 right [outer] join 表2 on 主键=外键。

子查询：2个及以上的select查询，嵌套查询。
子查询根据查询结果的处理情况：
1.单个值：select * from 表 where a=( select * from 表2);
2.单列多值：select * from 表 where a in ( select * from 表2);
3.多行多列：（表）
select * from 表 [as]别名1，(select * from 表2 ) [as] 别名2 where 其他条件；
注意：1.子查询用()括起来表示先执行。
2.单个值用=，多个值用in
3.子查询的查询结果是多行多列时（也就是表）。

事务：
事务的特性：ACID。
A：Atomicity 原子性。事务代码块是一个整体，要么都执行成功提交，要么全部失败回滚。
C:Consistency 一致性。数据库开始的全部状态与事务执行之后的状态保持一致。
I：Isolation 隔离性 一事务与二事务互不影响。
D:Durability 持久性 事务的操作数据库造成持久化的影响。

数据安全问题：
根据不同的隔离级别会产生不同的数据库数据安全问题：
脏读，不可重复读，幻读。
隔离级别：
1.读未提交 ：对应脏读。
A事务查询数据，得到一个值。B事务修改数据，还未提交，A事务查询数据得到另一个值。
2.读已提交：对应不可重复读。
A事务查询数据，得到一个值。B事务修改数据，还未提交，A事务查询数据得到相同的值（不再脏读）。当B事务提交之后，A事务再查询得到另一个值。
3.可重复读：对应幻读。
A事务查询数据，得到一个值。B事务修改数据，还未提交，A事务查询数据得到相同的值（不再脏读）。当B事务提交之后，A事务再查询得到相同的值（可重复读）。但是得到的结果的条数不一样。
4.串行化：最高隔离级别，让事务一个一个排队运行，事务之间互补影响，但是运行效率最低。

mysql默认自动事务管理 ， 一条sql语句就是一条事务。
手动事务管理：start transaction
java中connection.setAutoCommit(flase));设置不自动提交。
转账事务demo:
当张三给李四转账，张三的钱要少100，李四的钱要+100,其他情况就是异常情况。
中间产生的语句写在日志中。没有出现异常就commit ，提交日志；出现异常rollback，删除日志文件返回执行之前的状态。

– 内连接
select * from emp,dept where emp.dept_id =dept.id;
select * from emp inner join dept on emp.dept_id=dept.id;

– 外连接
select * from dept left join emp on emp.dept_id=dept.id;
select * from emp left join dept on emp.dept_id=dept.id;

– 子查询
– 查询工资最高的员工是谁？
select * from emp where salary=(select max(salary) from emp ) ;
– 查询工资小于平均工资的员工有哪些？
select * from emp where salary < (select avg(salary) from emp);
– 查询工资大于5000的员工，来自于哪些部门的名字
select * from dept d,(select dept_id from emp where salary >5000) e where d.id=e.dept_id;
连接之后产生虚拟表，对虚拟表进行where过滤。

习题：
emp 员工信息表：

dept部门表：

job职位表：

salarygrade工资等级表：

多表查询规律总结

1. 不管我们查询几张表，表连接查询会产出笛卡尔积，我们需要消除笛卡尔积，拿到正确的数据。
   我们需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）

2. 消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。（条件数量=表的数量-1），每张表都要参与进来

3. 多表连接查询步骤：
   3.1. 确定要查询哪些表
   3.2. 确定表连接条件
   3.3. 确定查询字段

– 1.查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
具体操作：
1.确定要查询哪些表：emp e, job j
SELECT * FROM emp e INNER JOIN job j;
2.确定表连接条件： e.job_id=j.id
SELECT * FROM emp e INNER JOIN job j ON e.job_id=j.id;
3.确定查询字段：员工编号，员工姓名，工资，职务名称，职务描述
SELECT e.id, e.ename, e.salary, j.jname, j.description FROM emp e INNER JOIN job j ON e.job_id=j.id;

– 2.查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
具体操作：

  1. 确定要查询哪些表，emp e, job j, dept d
   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d;
   
  2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id
   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d ON e.job_id=j.id AND e.dept_id=d.id;
   
  3. 确定查询字段：员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
   SELECT e.`id`, e.`ename`, e.`salary`, j.`jname`, j.`description`, d.`dname`, d.`loc` FROM emp e INNER JOIN job j INNER JOIN dept d ON e.job_id=j.id AND e.dept_id=d.id;

– 3.查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
具体操作：

  1. 确定要查询哪些表，emp e, job j, dept d, salarygrade s
   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s;

  2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id and e.salary between s.losalary and hisalary
   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary;

  3. 确定查询字段：员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
   SELECT e.`ename`, e.`salary`, j.`jname`, j.`description`, d.`dname`, d.`loc`, s.`grade` FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary;

– 4.查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
具体操作：

  1. 确定要查询哪些表，emp e, job j, dept d, salarygrade s
     SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s;

  2. 确定表连接条件 e.job_id=j.id and e.dept_id=d.id and e.salary between s.losalary and hisalary
   SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary;

  额外条件：只需要查询经理的信息（j.jname='经理'）
  3. 确定查询字段：员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
   SELECT e.`ename`, e.`salary`, j.`jname`, j.`description`, d.`dname`, d.`loc`, s.`grade` FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s ON e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary AND j.jname='经理';

– 5.查询出部门编号、部门名称、部门位置、部门人数
具体操作：

  1. 去员工表中找到每个部门的人数和部门id
   SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id;

  2. 再和部门表连接查询
  SELECT * FROM dept d INNER JOIN (SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id) e ON e.dept_id=d.`id`;

  3. 显示对应的字段
   SELECT d.`id`, d.dname, d.`loc`, e.total 部门人数 FROM dept d INNER JOIN (SELECT dept_id, COUNT(*) total FROM emp GROUP BY dept_id) e ON e.dept_id=d.`id`;

– 6.查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示
具体操作：

  1. 确定要查询哪些表，emp e, dept d
   SELECT * FROM emp e INNER JOIN dept d;

  2. 确定表连接条件 e.dept_id=d.id，没有员工的部门也要显示。右边数据要全部显示所以使用右外连接
   SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id=d.id;

  注意：没有员工的部门也要显示。右边数据要全部显示所以使用右外连接

  3. 确定查询字段：员工信息，部门名称
   SELECT e.*, d.`dname` FROM emp e RIGHT JOIN dept d ON e.dept_id=d.id;

– 7.查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序

 具体操作：
 
  1. 确定要查询哪些表，emp e, job j, salarygrade s
   SELECT * FROM emp e INNER JOIN job j INNER JOIN salarygrade s;


  2. 确定表连接条件 e.job_id=j.id and e.salary between s.losalary and s.hisalary
   SELECT * FROM emp e INNER JOIN job j INNER JOIN salarygrade s ON e.job_id=j.id AND e.salary BETWEEN s.losalary AND s.hisalary;

  3. 确定查询字段：员工姓名，员工工资，工资等级，并按工资升序排序
   SELECT e.`ename`, j.`jname`, e.`salary`, s.`grade` FROM emp e INNER JOIN job j INNER JOIN salarygrade s ON e.job_id=j.id AND e.salary BETWEEN s.losalary AND s.hisalary ORDER BY e.`salary`;

– 8.列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
具体操作：

  1.确定要查询哪些表，emp e, emp m
 SELECT * FROM emp e INNER JOIN emp m;

2. 确定表连接条件 e.mgr=e2.id
SELECT * FROM emp e INNER JOIN emp m ON e.`mgr`=m.`id`;
SELECT * FROM emp e LEFT JOIN emp m ON e.`mgr`=m.`id`;

3. 确定查询字段：员工的姓名及其直接上级的姓名	 SELECT e.`ename`, IFNULL(m.`ename`, '没有') 上司 FROM emp e LEFT JOIN emp m ON e.`mgr`=m.`id`;

– 9.查询入职期早于直接上级的所有员工编号、姓名、部门名称
具体操作：

  1. 确定要查询哪些表，emp e, emp m, dept d
   SELECT * FROM emp e INNER JOIN emp m INNER JOIN dept d;
  2. 确定表连接条件 e.mgr=m.id and e.dept_id=d.id and e.dept_id=d.id and e.joindate<m.joindate
   SELECT * FROM emp e INNER JOIN emp m INNER JOIN dept d ON e.mgr=m.id AND e.dept_id=d.id AND e.joindate<m.joindate;

  3. 确定查询字段：员工编号、姓名、部门名称
   SELECT e.`id`, e.`ename`, d.`dname` FROM emp e INNER JOIN emp m INNER JOIN dept d ON e.mgr=m.id AND e.joindate<m.joindate AND e.`dept_id`=d.`id`;

– 10.查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级
具体操作：
先统计公司平均工资
SELECT AVG(salary) FROM emp;

  1. 确定要查询哪些表，emp e, emp m, dept d, salarygrade s
  SELECT * FROM emp e INNER JOIN emp m INNER JOIN dept d INNER JOIN salarygrade s;


  2. 确定表连接条件 e.dept_id=d.id and e.mgr=m.id and e.salary between s.losalary and hisalary and e.salary>公司平均薪金
  SELECT * FROM emp e INNER JOIN emp m INNER JOIN dept d INNER JOIN salarygrade s WHERE e.dept_id=d.id AND e.mgr=m.id AND e.salary BETWEEN s.losalary AND hisalary AND e.salary>(SELECT AVG(salary) FROM emp);


  3. 确定查询字段：员工信息，部门名称，上级领导，工资等级。
  SELECT e.*, d.`dname`, m.`ename`, s.`grade` FROM emp e INNER JOIN emp m INNER JOIN dept d INNER JOIN salarygrade s WHERE e.dept_id=d.id AND e.mgr=m.id AND e.salary BETWEEN s.losalary AND hisalary AND e.salary>(SELECT AVG(salary) FROM emp);