多表關(guān)系

• 一對多
• 多對多
• 一對一

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

-- 多表查詢加上連接查詢的條件來去除無效的笛卡爾積（null值的樣本不會查詢到）
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 連接條件 ... ;

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

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

-- 顯式內(nèi)連接實現(xiàn)
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 為每一張表起別名,簡化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ù), 和對應(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), 也需要查詢出來
-- 表結(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)合查詢

對于union查詢民泵，就是把多次查詢的結(jié)果合并起來，形成一個新的查詢結(jié)果集

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

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

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

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

子查詢

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

標(biāo)量子查詢

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

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

列子查詢

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

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

-- 查詢比 財務(wù)部 所有人工資都高的員工信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '財務(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

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

表子查詢

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

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

-- 查詢?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歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出來(外連接)
-- 表: 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). 查詢所有員工的工資等級
-- 表: 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ā)部" 所有員工的信息及工資等級
-- 表: 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)計部門的員工人數(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é)號, 課程名稱 
-- 表: 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;