Mysql day02_汇总

多表查询

等值连接

等值连接两张表字段意义相同，字段名不一定相同，类型一般相同
分析过程：
1.先分析数据或条件来自哪些表 产生from子句
2.如果有多张表先写连接条件（注意n-1） where 已经连接一张大表了
3.再分析其他约束条件 and …（用多表的数据里）
4.看一下有没有排序分页 ORDER BY LIMIT
5.显示哪些列 SELECT 子句

练习：查询员工信息，显示员工编号，员工姓名，部门名称,部门编号

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

如果没指定两张表的连接条件，那么会产生第一张表的记录与第二张的每条记录
产生的记录数 m*n 这种情况叫笛卡尔积（交叉连接）
如何想消除这种现象需要指定连接条件
n张表至少有n-1个连接条件

SELECT e.empno,e.ename,d.dname,d.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno;

练习：写一个查询，显示所有工作在CHICAGO并且奖金不为空的
员工姓名，工作地点，奖金

SELECT e.ename,d.loc,e.comm
FROM dept d,emp e
WHERE d.deptno=e.deptno AND d.loc='CHICAGO' AND comm IS NOT NULL;

练习：写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

SELECT e.ename,d.loc
FROM dept d,emp e
WHERE d.deptno=e.deptno AND e.ename LIKE "%A%";

自身连接

练习：查询每一个员工的姓名和直接上级姓名
员工表e 经理表m

SELECT e.ename  员工姓名,m.ename 经理姓名
FROM emp e,emp m
WHERE e.mgr=m.empno;

表别名

练习：查询员工信息及其部门信息

SELECT e.*,d.*
FROM emp e,dept d
WHERE e.deptno=d.deptno

练习：查询员工编号，员工姓名，工作岗位名称，工作地点

SELECT e.deptno,e.ename,e.job,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno

多于两个表的连接及非等值连接

练习：查询员工编号，员工姓名，工作岗位，工作地点，月工资，薪资等级

SELECT e.empno,e.ename,e.job,d.loc,e.sal,s.grade
FROM emp e,salgrade s,dept d
WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno;

练习：显示员工SMITH的姓名，部门名称，直接上级名称

SELECT e.ename 姓名,d.dname 部门名称,m.ename 经理姓名
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.ename='SMITH';

练习：显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

SELECT e.ename,d.dname,e.sal,s.grade
FROM emp e,salgrade s,dept d
WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno AND s.grade>4;

练习：显示员工KING和FORD管理的员工姓名及其经理姓名。

SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno AND (m.ename='KING' OR m.ename='FORD');

练习：显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

SELECT e.ename,e.hiredate,m.ename,m.hiredate
FROM emp e,emp m
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate;

外连接

左外连接 右外连接

查询所有雇员姓名，部门编号，部门名称

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

FROM emp,dept emp是左表 dept是右表
FROM dept,emp dept是左表 emp是右表
左右可以相互转换

练习：查询所有雇员姓名，部门编号，部门名称
（没有员工的部门部门，也要显示（部门表是主表））

SELECT d.deptno,d.dname,e.ename
-- from中的逗号改成left join(right join)
-- 把连接条件改为on 其他条件还可以放 where
FROM dept d LEFT JOIN emp e
ON e.deptno=d.deptno;

练习：1）练习刚才的左连接
2）把左连接改成右连接

SELECT d.deptno,d.dname,e.ename
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

练习：显示 员工KING和FORD 管理的员工姓名，及其KING和FORD的经理姓名
及两个人的信息 没有经理也要显示

以m为主

SELECT e.empno 下属,e.ename 下属,
m.empno 我的,m.ename 我的,
b.empno 经理,b.ename 经理
FROM emp e RIGHT JOIN emp m ON e.mgr=m.empno
LEFT JOIN emp b ON m.mgr=b.empno
WHERE m.ename='KING' OR m.ename='FORD';