多表關(guān)系

• 一對(duì)多
• 多對(duì)多
• 一對(duì)一

-- 結(jié)果是一個(gè)笛卡爾積
select * from emp, dept;

-- 多表查詢加上連接查詢的條件來(lái)去除無(wú)效的笛卡爾積（null值的樣本不會(huì)查詢到）
select * from emp, dept where emp.dept_id = dept.id;

連接查詢

自連接：當(dāng)前表與自身的連接查詢，自連接必須使用表別名
內(nèi)連接：相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)
外連接：

• 左外連接：查詢左表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
• 右外連接：查詢右表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)

內(nèi)連接

-- 隱式內(nèi)連接
SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ;
-- 顯式內(nèi)連接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;

-- 查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱
-- 表結(jié)構(gòu): emp , dept
-- 連接條件: emp.dept_id = dept.id

-- 隱式內(nèi)連接實(shí)現(xiàn)
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 為每一張表起別名,簡(jiǎn)化SQL編寫
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

-- 顯式內(nèi)連接實(shí)現(xiàn)
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 為每一張表起別名,簡(jiǎn)化SQL編寫
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

外連接

-- 左外連接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;
-- 右外連接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

-- 查詢emp表的所有數(shù)據(jù), 和對(duì)應(yīng)的部門信息
-- 由于需求中提到，要查詢emp的所有數(shù)據(jù)，所以是不能內(nèi)連接查詢的，需要考慮使用外連接查詢。
-- 表結(jié)構(gòu): emp, dept
-- 連接條件: emp.dept_id = dept.id

-- 左外連接
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

-- 右外連接
-- 工作中習(xí)慣于左外，左外和右外可以改變表結(jié)構(gòu)的先后順
序替換
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

自連接

SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

-- 查詢員工 及其 所屬領(lǐng)導(dǎo)的名字
-- 表結(jié)構(gòu): emp
select a.name, b.name from emp a, emp b where a.managerid = b.id;

-- 查詢所有員工 emp 及其領(lǐng)導(dǎo)的名字 emp , 如果員工沒有領(lǐng)導(dǎo), 也需要查詢出來(lái)
-- 表結(jié)構(gòu): emp a , emp b
select a.name '員工', b.name '領(lǐng)導(dǎo)' from emp a left join emp b on a.managerid = b.id;

聯(lián)合查詢

對(duì)于union查詢，就是把多次查詢的結(jié)果合并起來(lái)，形成一個(gè)新的查詢結(jié)果集

• 對(duì)于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。
• union all 會(huì)將全部的數(shù)據(jù)直接合并在一起，union 會(huì)對(duì)合并之后的數(shù)據(jù)去重。

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

-- 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來(lái)
-- union all查詢出來(lái)的結(jié)果，僅僅進(jìn)行簡(jiǎn)單的合并，并未去重。
select * from emp where salary < 5000
union all
select * from emp where age > 50;

-- union 聯(lián)合查詢，會(huì)對(duì)查詢出來(lái)的結(jié)果進(jìn)行去重處理。
select * from emp where salary < 5000
union
select * from emp where age > 50;

子查詢

• 標(biāo)量子查詢（子查詢結(jié)果為單個(gè)值）
• 列子查詢(子查詢結(jié)果為一列)
• 行子查詢(子查詢結(jié)果為一行)
• 表子查詢(子查詢結(jié)果為多行多列)

標(biāo)量子查詢

-- 查詢 "銷售部" 的所有員工信息
select * from emp where dept_id = (select id from dept where name = '銷售部');

-- 查詢?cè)?"方東白" 入職之后的員工信息
select * from emp where entrydate > (select entrydate from emp where name = '方東白');

列子查詢

子查詢返回的結(jié)果是一列（可以是多行），這種子查詢稱為列子查詢

-- 查詢 "銷售部" 和 "市場(chǎng)部" 的所有員工信息
select * from emp where dept_id in (select id from dept where name = '銷售部' or name = '市場(chǎng)部');
select * from emp where dept_id in (select id from dept where name in ('銷售部','市場(chǎng)部'));

-- 查詢比 財(cái)務(wù)部 所有人工資都高的員工信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '財(cái)務(wù)部'));

-- 查詢比研發(fā)部其中任意一人工資高的員工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研發(fā)部'));

行子查詢

子查詢返回的結(jié)果是一行（可以是多列），這種子查詢稱為行子查詢。
常用的操作符：= 、<> 、IN 、NOT IN

-- 查詢與 "張無(wú)忌" 的薪資及直屬領(lǐng)導(dǎo)相同的員工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '張無(wú)忌');

表子查詢

子查詢返回的結(jié)果是多行多列，這種子查詢稱為表子查詢。
常用的操作符：IN

-- 查詢與 "鹿杖客" , "宋遠(yuǎn)橋" 的職位和薪資相同的員工信息
select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋遠(yuǎn)橋');

-- 查詢?nèi)肼毴掌谑?"2006-01-01" 之后的員工信息 , 及其部門信息
select e.*, d.* from (select * from emp where entrydate > 2006-01-01) e left join dept d on e.dept_id = d.id;

練習(xí)

-- 4). 查詢所有年齡大于40歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出來(lái)(外連接)
-- 表: emp , dept
-- 連接條件: emp.dept_id = dept.id
select e.*, d.name from emp e left join dept d on d.id = e.dept_id where age > 40;

-- 5). 查詢所有員工的工資等級(jí)
-- 表: emp , salgrade
-- 連接條件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;

-- 6).查詢 "研發(fā)部" 所有員工的信息及工資等級(jí)
-- 表: emp , salgrade , dept
-- 連接條件 : emp.salary between salgrade.losal and salgrade.hisal 
-- emp.dept_id = dept.id
-- 查詢條件 : dept.name = '研發(fā)部'
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal ) and d.name = '研發(fā)部';


-- 10).查詢低于本部門平均工資的員工信息
select *, (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) '平均' from emp e2 where salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

-- 11). 查詢所有的部門信息, 并統(tǒng)計(jì)部門的員工人數(shù)
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人數(shù)' from dept d;

-- 12). 查詢所有學(xué)生的選課情況, 展示出學(xué)生名稱, 學(xué)號(hào), 課程名稱 
-- 表: student , course , student_course
-- 連接條件: student.id = student_course.studentid , course.id = student_course.courseid
select s.name, no, c.name '課程' from student s, course c, student_course sc where s.id = sc.studentid and c.id = sc.courseid;