mysql-第三章 多表查询

– 第三章 多表查询

– 从多个表中获取数据

– oracle的多表连接写法：将连接条件写在where子句中

sql99标准写法

– oracle的多表连接写法：将连接条件写在where子句中
select * from emp;
select * from dept;
– 笛卡尔积 多表连接没有连接的条件
select * from emp,dept; – 60 条数据 15*4=60
1、查询员工姓名、员工编号、工作 、部门编号、工作地点、部门名称
– 注意：
– ①连接条件中的列名相同时，使用表名.列名规定列
– ②select子句中不明确的列使用表名.列名规定列(歧义列名)
– ③在WHERE子句中书写连接条件
– ④N个表相连时，至少需要N－1个连接条件
– 等值连接
select ename,empno,job,dept.deptno,loc,dname from emp,dept where emp.deptno=dept.deptno
– 使用AND运算符增加其它查询条件
2、查询工作在20号部门的员工姓名、员工编号、工作 、部门编号、工作地点、部门名称
select ename,empno,job,emp.deptno,loc,dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=20;

– 歧义列名
在用到多个表时可以使用表名作前缀来限定列；
通过使用表前缀可以提高性能；
通过使用列的别名可以区分来自不同表但是名字相同的列
select ename,empno,job,emp.deptno,loc,dname from emp,dept
where emp.deptno=dept.deptno and emp.deptno=20; – 效率较低

select emp.ename,emp.empno,emp.job,emp.deptno,dept.loc,dept.dname
from emp,dept
where emp.deptno=dept.deptno and emp.deptno=20;-- 效率较高
– tab_coun_pro_type 表名太长
– 表别名：通过使用表的别名来简化查询语句
select e.ename,e.empno,e.job,e.deptno,d.loc,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.deptno=20;

– 非等值连接
3、查询员工姓名、员工编号、工作、工资以及工资等级
select *from salgrade;
select * from emp;
select * from dept;
select ename,empno,job,sal,grade
from emp e,salgrade s
where e.sal<=hisal and losal<=e.sal
– 多于两张表的连接：核心：分析连接条件 一般是主外键
– 关系模型：
– 1-1 商品-商品详情 将任意一端的主键放到另一端中作为外键
– 1-n 部门-员工 将一端主键放到多端作为外键维护两者关系
– n-n 订单-商品
– 新建一张订单详情表 维护他们的关系(将n-n 转为1-n或者1-1), 订单-订单详情：1-1 订单详情-商品：1-1
– n-n 学生-课程 成绩表 学生-成绩 1-n 课程-成绩 1-1
4、查询员工姓名、员工编号、部门名称、部门编号、部门类型
select ename,empno,dname,e.deptno,typename
from emp e,dept d,depttype t
where e.deptno=d.deptno and d.depttypeid =t.depttypeid
5、查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select empno,ename,sal,grade,loc
from emp e,dept d,salgrade s
where e.deptno=d.deptno and (sal<=hisal and losal<=sal)
order by grade

– 自身连接：就是将自身当成另一张表
6、查询员工编号、经理编号、员工姓名及其经理姓名
select * from emp;
select * from emp;

select worker.empno 员工编号,manager.empno 经理编号,worker.ename 员工姓名,manager.ename 经理姓名
from emp worker,emp manager
where worker.mgr=manager.empno

7、查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号
select w.ename,w.empno,m.empno,m.ename
from emp w,emp m,dept d
where w.deptno=d.deptno and w.mgr=m.empno and (loc=‘NEW YORK’ or loc=‘CHICAGO’)

– sql99标准写法

– 交叉连接 cross join 笛卡尔积
select * from emp cross join dept;-- 60条
– 自然连接:
自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；
如果两个表之间相同名称的列的数据类型不同，则会产生错误
select *from emp NATURAL join dept;
– using子句
自然连接是使用所有名称和数据类型相匹配的列作为连接条件，
而USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件
select *from emp join dept using(deptno);

– *** on子句 on(连接条件)
select * from emp join dept on (emp.deptno=dept.deptno);
select * from emp e join dept d on (e.deptno=d.deptno);

8、查询工作在20号部门的员工信息包括部门信息
select * from emp e join dept d on (e.deptno=d.deptno) where e.deptno=20

– on子句：多于两张表的连接
9、查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号
select w.ename,w.empno,m.empno,m.ename
from emp w
join emp m on(w.mgr=m.empno)
join dept d on(w.deptno=d.deptno)
where loc in(‘NEW YORK’,‘CHICAGO’);
select * from emp;
select * from dept;
– 外部连接:可以使用外部连接来查看哪些行,按照连接条件没有被匹配上
10、查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来
select ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno
– 左外连接
select ename,e.deptno,dname from emp e left join dept d on(e.deptno=d.deptno)
– 右外连接
select ename,e.deptno,dname from dept d right join emp e on(e.deptno=d.deptno)
11、查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来
– 左外链接
select ename,d.deptno,dname from dept d left join emp e on(e.deptno=d.deptno)
– 右外连接
select ename,d.deptno,dname from emp e right join dept d on(e.deptno=d.deptno)

– 全外连接 可以使用全外连接 来查看所有哪些行,按照连接条件没有被匹配上
12、查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门和没有部门的员工也要显示出来
– MySQL目前不支持此种方式，可以用其他方式替代解决