MySQL之表的连接

MySQL之表的连接

1 、交叉连接

• 不适用任何匹配条件。生成笛卡尔积

SELECT e.ename,e.deptno,d.dname FROM emp e,dept d ；

2、内连接

• 找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果。（只连接匹配的行）

语法规则1：（92法）

SELECT  table1.column, table2.column

  FROM  table1, table2
  WHERE  table1.column1 = table2.column2;
语法2：（99法）

SELECT  table1.column, table2.column
  FROM  table1 inner join table2 on table1.column1 = table2.column2 （inner可以忽略不写）

-- 查询所有用户的姓名，部门编号，部门名称
SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;（92法）
SELECT e.ename,e.deptno,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;（99法  推荐使用）
SELECT e.ename,e.deptno,d.dname FROM emp e JOIN dept d USING(deptno);
-- 什么时候使用using  使用99方法时 还有两张表的字段名相同时

-- 查出员工的编号，姓名，领导的编号和姓名
SELECT e1.empno,e1.ename,e2.mgr,e2.ename FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno; 
-- 列出员工姓名，工资和工资的等级
SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal> losal AND e.sal<hisal;

3、外连接

• 左外连接、右外连接 、满连连接（mysql不支持）

语法：
左外连接 ：优先显示左表全部记录。在按照on的条件取到两张表共同部分的基础上，保留左表的记录
select deptno, dname,empno,ename,job
  from dept left outer join emp
  using(deptno)
右外连接 ：优先显示右表全部记录。在按照on的条件取到两张表共同部分的基础上，保留右表的记录
select deptno, dname,empno,ename,job
  from dept right join emp
  using(deptno);


-- 查询所有用户的姓名，部门编号，部门名称
SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
SELECT e.ename,e.deptno,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
SELECT e.ename,e.deptno,d.dname FROM emp e JOIN dept d USING(deptno);
-- 什么时候使用using  使用99方法时 还有两张表的字段名相同时

-- 查出员工的编号，姓名，领导的编号和姓名
SELECT e1.empno,e1.ename,e2.mgr,e2.ename FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno; 
-- 列出员工姓名，工资和工资的等级
SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal> losal AND e.sal<hisal;


-- 查询所有部门的部门名字和该部门下的员工名字，如果该部门没有员工，也要显示出部门名字来
SELECT d.dname,e.ename,d.deptno FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

-- 查询所有的员工的编号，姓名，有领导的显示领导的编号和姓名，没有领导的只显示员工的信息
SELECT e1.empno,e1.ename,e2.mgr,e2.ename FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;
-- 查询所有部门的详细信息及每个部门的平均工资，包含没有员工的部门
SELECT d.deptno,d.dname,d.loc,AVG(e.sal)FROM emp e RIGHT JOIN dept d USING(deptno) GROUP BY deptno;
    

4 、子查询

特点：

• 子查询在主查询前执行一次
• 主查询使用子查询的结果

-- 子查询
-- 思考：查询工资高于平均工资的雇员名字和工资
SELECT ename,sal FROM emp WHERE(
SELECT AVG(sal) FROM emp); 

-- 思考：查询和SCOTT同一部门且比他工资低的雇员名字和工资
--   方法1 
SELECT ename,sal FROM emp WHERE deptno=(
SELECT deptno FROM emp WHERE ename='scott') AND sal< (SELECT sal FROM emp WHERE ename='scott');
--   方法二  内连接
SELECT e.ename,e.sal FROM emp e JOIN (SELECT deptno,sal FROM emp WHERE ename='scott') t ON t.deptno=e.deptno AND t.sal > e.sal;

4.1 单行子查询

-- 单行子查询
-- 思考：查询工资最高的雇员名字和工资
SELECT ename, sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

4.2  多行子查询

-- 写代码技巧 ： 先分析写出来判断条件 然后在写主查询  

特点：

• 多行子查询返回多行记录
• 对多行子查询只能使用多行记录比较运算符 ALL 和子查询返回的所有值比较 ANY 和子查询返回的任意一个值比较 IN 等于列表中的任何一个

-- 多行子查询
-- 查询工资低于任何一个“CLERK”的工资的雇员信息。
SELECT sal FROM emp WHERE sal< ANY(
SELECT sal FROM emp WHERE job='clerk'); 

-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
SELECT empno,ename,sal FROM emp WHERE sal>
ALL(SELECT sal FROM emp WHERE job='salesman');

-- 查询部门20中职务同部门10的雇员一样的雇员信息
SELECT * FROM emp WHERE deptno = 20 AND job IN
(SELECT job FROM emp WHERE deptno = 10);

5 、事务

事务是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，事务是为了保证数据库的完整性

事务语句

• 开启 begin;
• 提交 commit;
• 回滚 rollback;

注意：

• 提交或回滚前数据的状态
  • 以前的数据可恢复
  • 当前的用户可以看到DML操作的结果
  • 其他用户不能看到DML操作的结果
  • 被操作的数据被锁住,其他用户不能修改这些数据
• 提交后数据的状态
  • 数据的修改被永久写在数据库中
  • 数据以前的状态永久性丢失
  • 所有的用户都能看到操作后的结果
  • 记录锁被释放,其他用户可操作这些记录
• 回滚后数据的状态
  • 语句将放弃所有的数据修改
  • 修改的数据被回退
  • 恢复数据以前的状态
  • 行级锁被释放