多表查询
关联查询 - 作用:可以跨多表查询
查询出员工的名字和他所在部门的名字【错误】
select e.empno,e.ename,d.deptno,d.dname from emp e,dept d
错误原因:
以上写法会出现笛卡尔积,产生很多冗余错误的数据,如果要
排除笛卡尔积,则应该使用where字句进行条件的过滤.
正确写法:传统方式
select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno [表的主键 = 表的外键]
给表格取别名,可以简化SQL语句,还可以区别不同的列
正确写法:join方式
select e.firstname,d.name from semp e join sdept on e.deptid=d.id;
多表连接查询
1. 內连接[ inner ] join : 多表连接满足指定条件的结果集
table1 t1 inner join table2 t2
on t1.列 = t2.列
a. 等值连接 ： 连接的条件是 = 连接 on t1.列 = t2.列
b.不等值连接 ： 连接的条件是 不相等 连接 on t1.列 > t2.列
c.自然连接 natural join (删除重复列)
- 使用表别名可以简化查询
- 使用表名(表别名)前缀可提高查询效率
(第一种写法) select * from customer c,recevice_address r where c.cust_id =r.cust_id (第二种写法) select * from customer c Inner join myorder m on c.cust_id =m.cust_id
2. 外连接 outer join
a. 左外连接 left [ outer ] join ： 把左边不满足条件的记录也需要查询出来
(第一种写法) select * from customer c left join recevice_address r on c.cust_id = r.cust_id (第二种写法) select * from customer c,myorder m where c.cust_id = m.cust_id
b.右外连接 right [ outer ] join ：把右边不满足条件的记录也需要查询出来
(第一种写法) select * from customer c right join recevice_address r on c.cust_id = r.cust_id (第二种写法) select * from customer c ,myorder m where c.cust_id = m.cust_id
3.完全连接
将A表和B表中的数据完全匹配。(FULL JOIN)在查询资料后发现MySQL上不适配，属于order数据库的方法。
-- 不适配版本 SELECT * FROM dbo.customer full join dbo.recevice_address on dbo.customer.cust_id = dbo.recevice_address.cust_id
联合查询用到的方法是先用左连接查出来后在差右连接。左右两个全部查出来后用UNION来拼接起来。
select * from customer c left join recevice_address r on c.cust_id = r.cust_id union select * from customer c RIGHT JOIN recevice_address r on c.cust_id = r.cust_id
4.自连接: 把表复制一份 作为另一个表
注意： 表一定要取别名
(1). 查询出在 ACCOUNTING 部门的员工编号，姓名
select empno, ename from emp e join dept d on e.deptno = d.deptno where d.dname = 'ACCOUNTING ';
(2) 查询出所有部门的所有员工，列出所有部门信息、员工信息
select * from emp e join dept d on e.deptno = d.deptno;
5. 自然连接
select * from emp natural join dept
(3). 查询在北京工作的员工的平均薪资
select avg(salary) from emp e join dept d on e.deptno = d.deptno where d.loc = 'beijing';
(4).查询出各部门的员工人数（没有员工的部门也需要统计）
select d.deptno , count(e.empno) from emp e right outer join dept d on e.deptno = d.deptno group by d.deptno; select d.deptno , count(e.empno) from dept d left outer join emp e on e.deptno = d.deptno group by d.deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | | 40 | 0 | | 50 | 0 | +--------+----------+
(5). 查询出员工编号，姓名，和该员工上级领导的编号与姓名 （给结果列名 取别名）
select e.empno 员工编号, e.ename 员工姓名,e.mgr 上级领导的编号, m.ename 上级领导的名称 from emp e join emp m on e.mgr = m.empno ;
(6). 查询出员工编号，姓名，和该员工上级领导的编号与姓名 （给结果列名 取别名 ， 没有上级领导的记录也需要查询）
select e.empno 员工编号, e.ename 员工姓名,e.mgr 上级领导的编号, m.ename 上级领导的名称 from emp e left join emp m on e.mgr = m.empno ;
(7). 查询出各年份员工入职人数
select YEAR(emp.hiredate),COUNT(emp.empno) from emp GROUP BY YEAR(emp.hiredate);
(8). 查询出各年份各月份员工入职人数
select YEAR(emp.hiredate),MONTH(emp.hiredate),COUNT(emp.empno) from emp GROUP BY YEAR(emp.hiredate),MONTH(emp.hiredate);
(9). 查询出在 ACCOUNTING 部门的员工编号，姓名
select e.empno,e.ename from emp e right join dept d on e.deptno=d.deptno where d.dname='ACCOUNTING';
(10). 查询在北京工作的员工的平均薪资
(11). 查询出谌燕老师带的课程的学生有哪些**
select t.tname,c.cname,stu.sname from teacher t join course c on t.tno = c.tno join score s on c.cno = s.cno join student stu on stu.sno = s.sno where t.tname ='谌燕'
课堂练习
-- 1.查询出员工的信息（包含部门信息） select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d --笛卡尔积【错误】 -- 多表查询 1、内链接：inner join (1).等值链接 2、外链接：outer join select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e inner join dept d on e.deptno = d.deptno -- 2.查询出姓名'JONES'员工的编号，薪资，所在部门编号，部门名称这些信息 select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e inner join dept d on e.deptno = d.deptno where ename = 'JONES' -- 3. 查询各部门的员工人数，显示部门编号，部门名称，员工人数 select e.deptno,d.dname,count(e.ename) from emp e inner join dept d on e.deptno = d.deptno group by e.deptno -- 4. 查询在'SALES'部门的员工平均薪资 select d.dname,avg(e.salary) from emp e inner join dept d on e.deptno =d.deptno where d.dname ='SALES' -- 5. 查询出员工的编号，姓名，薪资，与他的领导的编号，姓名，薪资 select e.empno,e.ename,e.salary,e.mgr,m.ename,m.salary from emp e join emp m on e.mgr = m.empno -- 6. 查询在北京工作的员工的平均薪资 select d.loc,avg(e.salary) from emp e inner join dept d on e.deptno = d.deptno where d.loc = 'beijing' -- 7.查询各部门的人数，显示部门编号，部门名称，人数 select e.deptno,d.dname,count(e.ename) from emp e join dept d on e.deptno = d.deptno group by e.deptno -- 外链接 查询出员工编号，姓名，和该员工上级领导的编号与姓名（给结果列名 取别名，没有上级领导的也需要查询） select e.empno,e.ename,e.salary,e.mgr,m.ename,m.salary from emp e left outer join emp m on e.mgr = m.empno -- left outer join 交集部分，左外链接 select e.empno,e.ename,e.salary,e.mgr,m.ename,m.salary from emp e right outer join emp m on e.mgr = m.empno -- right outer join 交集部分，右外链接 -- 统计各部门的人数，没有员工的部门，也要查询出来 select d.deptno ,count(empno) from emp e right join dept d on e.deptno = d.deptno group by d.deptno
