多表查询

关联查询 - 作用:可以跨多表查询

查询出员工的名字和他所在部门的名字【错误】

  1. select e.empno,e.ename,d.deptno,d.dname
  2. from emp e,dept d

错误原因:

以上写法会出现笛卡尔积,产生很多冗余错误的数据,如果要

排除笛卡尔积,则应该使用where字句进行条件的过滤.

正确写法:传统方式

  1. select e.empno,e.ename,d.deptno,d.dname
  2. from emp e,dept d
  3. where e.deptno = d.deptno
  4.  [表的主键 = 表的外键]

给表格取别名,可以简化SQL语句,还可以区别不同的列

正确写法:join方式

  1. select e.firstname,d.name from semp e join sdept on e.deptid=d.id;

多表连接查询

1. 內连接[ inner ] join : 多表连接满足指定条件的结果集

  1. table1  t1  inner join table2 t2  
  2. on  t1.列 = t2.列  
  3. a. 等值连接  ： 连接的条件是 = 连接          on  t1.列 = t2.列  
  4. b.不等值连接 ：  连接的条件是 不相等 连接    on t1.列 > t2.列  
  5. c.自然连接 natural join  (删除重复列)
  • 使用表别名可以简化查询
  • 使用表名(表别名)前缀可提高查询效率
  • 六、SQL多表查询 - 图1
  1. (第一种写法)
  2. select * 
  3. from customer c,recevice_address r
  4. where c.cust_id =r.cust_id
  5. (第二种写法)
  6. select *
  7. from customer c
  8. Inner join myorder m on c.cust_id =m.cust_id

2. 外连接 outer join

  1. a. 左外连接 left  [ outer ] join ：    把左边不满足条件的记录也需要查询出来

六、SQL多表查询 - 图2

  1. (第一种写法)
  2. select *
  3. from customer c left join recevice_address r
  4. on c.cust_id = r.cust_id
  5. (第二种写法)
  6. select * 
  7. from customer c,myorder m 
  8. where c.cust_id = m.cust_id

b.右外连接 right [ outer ] join ：把右边不满足条件的记录也需要查询出来

六、SQL多表查询 - 图3

  1. (第一种写法)
  2. select *
  3. from customer c right join recevice_address r
  4. on c.cust_id = r.cust_id
  5. (第二种写法)
  6. select *
  7. from customer c ,myorder m 
  8. where c.cust_id = m.cust_id

3.完全连接

将A表和B表中的数据完全匹配。(FULL JOIN)在查询资料后发现MySQL上不适配，属于order数据库的方法。

六、SQL多表查询 - 图4

  1. -- 不适配版本
  2. SELECT *
  3. FROM  dbo.customer
  4. full join dbo.recevice_address
  5. on dbo.customer.cust_id = dbo.recevice_address.cust_id

联合查询用到的方法是先用左连接查出来后在差右连接。左右两个全部查出来后用UNION来拼接起来。

  1. select *
  2. from customer c
  3. left join recevice_address r on c.cust_id = r.cust_id
  4. union 
  5. select *
  6. from customer c
  7. RIGHT JOIN recevice_address r on c.cust_id = r.cust_id

4.自连接: 把表复制一份 作为另一个表

注意： 表一定要取别名

(1). 查询出在 ACCOUNTING 部门的员工编号，姓名

  1. select empno, ename
  2. from emp e  join dept d 
  3. on e.deptno = d.deptno 
  4. where d.dname = 'ACCOUNTING ';

(2) 查询出所有部门的所有员工，列出所有部门信息、员工信息

  1. select  * 
  2. from emp e     join dept  d 
  3. on e.deptno = d.deptno;

5. 自然连接

  1. select  * 
  2. from emp  natural  join dept

(3). 查询在北京工作的员工的平均薪资

  1. select avg(salary)
  2. from emp e join dept d
  3. on e.deptno = d.deptno 
  4. where d.loc = 'beijing';

(4).查询出各部门的员工人数（没有员工的部门也需要统计）

  1. select d.deptno , count(e.empno)
  2. from emp  e   right outer  join dept d
  3. on e.deptno = d.deptno
  4. group by d.deptno;
  6. select d.deptno , count(e.empno)
  7. from dept  d left  outer  join emp e
  8. on e.deptno = d.deptno
  9. group by d.deptno;
  11.     +--------+----------+
  12.     | deptno | count(*) |
  13.     +--------+----------+
  14.     |     10 |        3 |
  15.     |     20 |        5 |
  16.     |     30 |        6 |
  17.     |     40 |        0 |
  18.     |     50 |        0 |
  19.     +--------+----------+

(5). 查询出员工编号，姓名，和该员工上级领导的编号与姓名 （给结果列名 取别名）

  1. select e.empno 员工编号, e.ename 员工姓名,e.mgr  上级领导的编号, m.ename   上级领导的名称 
  2. from emp e  join emp m 
  3. on e.mgr = m.empno ;

(6). 查询出员工编号，姓名，和该员工上级领导的编号与姓名 （给结果列名 取别名 ， 没有上级领导的记录也需要查询）

  1. select e.empno 员工编号, e.ename 员工姓名,e.mgr  上级领导的编号, m.ename   上级领导的名称 
  2. from emp e left  join emp m 
  3. on e.mgr = m.empno ;

(7). 查询出各年份员工入职人数

  1. select YEAR(emp.hiredate),COUNT(emp.empno) 
  2. from emp 
  3. GROUP BY YEAR(emp.hiredate);

(8). 查询出各年份各月份员工入职人数

  1. select YEAR(emp.hiredate),MONTH(emp.hiredate),COUNT(emp.empno) 
  2. from emp  
  3. GROUP BY YEAR(emp.hiredate),MONTH(emp.hiredate);

(9). 查询出在 ACCOUNTING 部门的员工编号，姓名

  1. select e.empno,e.ename 
  2. from emp e right join  dept d 
  3. on e.deptno=d.deptno 
  4. where d.dname='ACCOUNTING';

(10). 查询在北京工作的员工的平均薪资

(11). 查询出谌燕老师带的课程的学生有哪些**

  1. select t.tname,c.cname,stu.sname
  2. from teacher t join course c
  3. on t.tno = c.tno
  4. join  score s
  5. on c.cno = s.cno
  6. join student stu
  7. on stu.sno = s.sno
  8. where t.tname ='谌燕'

课堂练习

  1. -- 1.查询出员工的信息（包含部门信息）
  2. select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc
  3. from emp e,dept d --笛卡尔积【错误】
  5. -- 多表查询
  6. 1、内链接：inner join
  7.             (1).等值链接
  8. 2、外链接：outer join
  10. select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc
  11. from emp e inner join dept d
  12. on e.deptno = d.deptno
  14. -- 2.查询出姓名'JONES'员工的编号，薪资，所在部门编号，部门名称这些信息
  15. select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc
  16. from emp e inner join dept d
  17. on e.deptno = d.deptno
  18. where ename = 'JONES'
  19. -- 3. 查询各部门的员工人数，显示部门编号，部门名称，员工人数
  20. select e.deptno,d.dname,count(e.ename)
  21. from emp e inner join dept d
  22. on e.deptno = d.deptno
  23. group by e.deptno
  24. -- 4. 查询在'SALES'部门的员工平均薪资
  25. select d.dname,avg(e.salary)
  26. from emp e inner join dept d
  27. on e.deptno =d.deptno
  28. where d.dname ='SALES'
  29. -- 5. 查询出员工的编号，姓名，薪资，与他的领导的编号，姓名，薪资
  30. select e.empno,e.ename,e.salary,e.mgr,m.ename,m.salary
  31. from emp e join emp m
  32. on e.mgr = m.empno
  33. -- 6. 查询在北京工作的员工的平均薪资
  34. select d.loc,avg(e.salary)
  35. from emp e inner join dept d
  36. on e.deptno = d.deptno
  37. where d.loc = 'beijing'
  38. -- 7.查询各部门的人数，显示部门编号，部门名称，人数
  39. select e.deptno,d.dname,count(e.ename)
  40. from emp e join dept d
  41. on e.deptno = d.deptno
  42. group by e.deptno
  44. -- 外链接
  45. 查询出员工编号，姓名，和该员工上级领导的编号与姓名（给结果列名 取别名，没有上级领导的也需要查询）
  46. select e.empno,e.ename,e.salary,e.mgr,m.ename,m.salary
  47. from emp e left outer join emp m 
  48. on e.mgr = m.empno
  49. -- left outer join 交集部分，左外链接
  51. select e.empno,e.ename,e.salary,e.mgr,m.ename,m.salary
  52. from emp e right outer join emp m 
  53. on e.mgr = m.empno
  54. -- right outer join 交集部分，右外链接
  56. -- 统计各部门的人数，没有员工的部门，也要查询出来
  57. select d.deptno ,count(empno)
  58. from emp e right join dept d
  59. on e.deptno = d.deptno
  60. group by d.deptno