多表查詢
關(guān)聯(lián)查詢 - 作用:可以跨多表查詢
查詢出員工的名字和他所在部門的名字【錯(cuò)誤】
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
錯(cuò)誤原因:
以上寫法會出現(xiàn)笛卡爾積,產(chǎn)生很多冗余錯(cuò)誤的數(shù)據(jù),如果要
排除笛卡爾積,則應(yīng)該使用where字句進(jìn)行條件的過濾.
正確寫法:傳統(tǒng)方式
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno
[表的主鍵 = 表的外鍵]
給表格取別名,可以簡化SQL語句,還可以區(qū)別不同的列
正確寫法:join方式
select e.firstname,d.name from semp e join sdept on e.deptid=d.id;
多表連接查詢
1. 內(nèi)連接[ inner ] join : 多表連接滿足指定條件的結(jié)果集
table1 t1 inner join table2 t2
on t1.列 = t2.列
a. 等值連接 ： 連接的條件是 = 連接 on t1.列 = t2.列
b.不等值連接 ： 連接的條件是 不相等 連接 on t1.列 > t2.列
c.自然連接 natural join (刪除重復(fù)列)
- 使用表別名可以簡化查詢
- 使用表名(表別名)前綴可提高查詢效率
inner_join.png
2. 外連接 outer join
（2.1） 左外連接 left [ outer ] join ：
把左表與右表滿足條件查詢出來置吓，與左表不滿足條件的記錄也需要查詢出來
left_outer_join.png
（2.2） 右外連接 right [ outer ] join ：
把左表與右表滿足條件查詢出來无虚，與右表不滿足條件的記錄也需要查詢出來
right_outer_join.png
3.自連接: 把表復(fù)制一份 作為另一個(gè)表
注意： 表一定要取別名
示例
(1). 查詢出在 ACCOUNTING 部門的員工編號，姓名
select empno, ename
from emp e inner 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;
自然連接
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).查詢出各部門的員工人數(shù)（沒有員工的部門也需要統(tǒng)計(jì)）
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). 查詢出員工編號，姓名戴质，和該員工上級領(lǐng)導(dǎo)的編號與姓名 （給結(jié)果列名 取別名）
select e.empno 員工編號, e.ename 員工姓名,e.mgr 上級領(lǐng)導(dǎo)的編號, m.ename 上級領(lǐng)導(dǎo)的名稱
from emp e join emp m
on e.mgr = m.empno ;
(6). 查詢出員工編號度宦，姓名，和該員工上級領(lǐng)導(dǎo)的編號與姓名 （給結(jié)果列名 取別名 告匠， 沒有上級領(lǐng)導(dǎo)的記錄也需要查詢）
select e.empno 員工編號, e.ename 員工姓名,e.mgr 上級領(lǐng)導(dǎo)的編號, m.ename 上級領(lǐng)導(dǎo)的名稱
from emp e left join emp m
on e.mgr = m.empno ;
(7). 查詢出各年份員工入職人數(shù)
select YEAR(emp.hiredate),COUNT(emp.empno)
from emp
GROUP BY YEAR(emp.hiredate);
(8). 查詢出各年份各月份員工入職人數(shù)
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). 查詢在北京工作的員工的平均薪資
select d.deptno,d.dname,avg(e.salary)
from emp e join dept d
on e.deptno=d.deptno
where d.loc='beijing'
group by d.deptno
