一. 多表關系

• 一對多（多對一）

• 多對一

• 一對一

1. 一對多

(1). 案例：部門與員工的關系

(2). 關系：一個部門對應多個員工，一個員工對應一個部門

(3). 實作：在多的一方建立外建，指向一的一方的主鍵

2. 多對多

(1).案例：學生與課程的關系

(2).關系：一個學生可以選修多門課程，一門課程也可以供多個學生選擇

(3).實作：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵

create table student( 
	id int auto_increment primary key comment '主鍵ID', 
	name varchar(10) comment '姓名', 
	no varchar(10) comment '學號' 
) comment '學生表'; 


insert into student 
values (null, '黛綺絲', '2000100101'),
(null, '謝遜', '2000100102'),
(null, '殷天正', '2000100103'),
(null, '韋一笑', '2000100104');

create table course( 
	id int auto_increment primary key comment '主鍵ID', 
	name varchar(10) comment '課程名稱' 
) comment '課程表'; 

insert into course 
values (null, 'Java'), 
(null, 'PHP'), 
(null , 'MySQL') , 
(null, 'Hadoop');

create table student_course(
	id int auto_increment comment '主鍵' primary key, 
	studentid int not null comment '學生ID', 
	courseid int not null comment '課程ID', 
	constraint fk_courseid foreign key (courseid) references course (id), 
	constraint fk_studentid foreign key (studentid) references student (id) 
)comment '學生課程中間表'; 

insert into student_course 
values (null,1,1),(null,1,2),
(null,1,3),(null,2,2), 
(null,2,3),(null,3,4);

3. 一對一

(1). 案例：用戶與用戶詳情的關系

(2). 關系：一對一關系，多用于單表拆分，將一張表的基礎欄位放在一張表中，其他詳情欄位放在另一張表中，以提升操作效率

(3). 實作：在任意一方加入外鍵，關聯另外一方的主鍵，并且設定外鍵為唯一的(UNIQUE)

create table tb_user( 
	id int auto_increment primary key comment '主鍵ID', 
	name varchar(10) comment '姓名', 
	age int comment '年齡', 
	gender char(1) comment '1: 男 , 2: 女', phone char(11) comment '手機號' 
) comment '用戶基本資訊表';

create table tb_user_edu( 
	id int auto_increment primary key comment '主鍵ID', 
	degree varchar(20) comment '學歷', 
	major varchar(50) comment '專業', 
	primaryschool varchar(50) comment '小學', 
	middleschool varchar(50) comment '中學',
	university varchar(50) comment '大學', 
	userid int unique comment '用戶ID', 
	constraint fk_userid foreign key (userid) references tb_user(id) 
) comment '用戶教育資訊表';

insert into tb_user(id, name, age, gender, phone) 
values (null,'黃渤',45,'1','18800001111'), 
(null,'冰冰',35,'2','18800002222'), 
(null,'碼云',55,'1','18800008888'), 
(null,'李彥宏',50,'1','18800009999'); 

insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) 
values (null,'本科','舞蹈','靜安區第一小學','靜安區第一中學','北京舞蹈學院',1),
(null,'碩士','表演','朝陽區第一小學','朝陽區第一中學','北京電影學院',2),
(null,'本科','英語','杭州市第一小學','杭州市第一中學','杭州師范大學',3),
(null,'本科','應用數學','陽泉第一小學','陽泉區第一中學','清華大學',4);

二.多表查詢概述

1. 資料準備

(1). 洗掉之前emp，dept表

drop table emp;

drop table dept;

(2). 執行如下腳本，創建emp表與dept表并插入測驗資料

-- 創建dept表，并插入資料 
create table dept( 
	id int auto_increment comment 'ID' primary key, 
	name varchar(50) not null comment '部門名稱' 
)comment '部門表'; 

INSERT INTO dept (id, name) 
VALUES (1, '研發部'), (2, '市場部'),
(3, '財務部'), (4, '銷售部'), 
(5, '總經辦'), (6, '人事部');

-- 創建emp表，并插入資料 
create table emp( 
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '姓名', 
	age int comment '年齡', job varchar(20) comment '職位', 
	salary int comment '薪資', 
	entrydate date comment '入職時間', managerid int comment '直屬領導ID',
dept_id int comment '部門ID' 
)comment '員工表';

-- 添加外鍵 
alter table emp add constraint fk_emp_dept_id foreign key (dept_id)
references dept(id);

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid,
dept_id) 
VALUES (1, '金庸', 66, '總裁',20000, '2000-01-01', null,5), 
(2, '張無忌', 20, '專案經理',12500, '2005-12-05', 1,1), 
(3, '楊逍', 33, '開發', 8400,'2000-11-03', 2,1), 
(4, '韋一笑', 48, '開發',11000, '2002-02-05', 2,1), 
(5, '常遇春', 43, '開發',10500, '2004-09-07', 3,1), 
(6, '小昭', 19, '程式員鼓勵師',6600, '2004-10-12', 2,1), 
(7, '滅絕', 60, '財務總監',8500, '2002-09-12', 1,3), 
(8, '周芷若', 19, '會計',48000, '2006-06-02', 7,3), 
(9, '丁敏君', 23, '出納',5250, '2009-05-13', 7,3), 
(10, '趙敏', 20, '市場部總監',12500, '2004-10-12', 1,2), 
(11, '鹿杖客', 56, '職員',3750, '2006-10-03', 10,2), 
(12, '鶴筆翁', 19, '職員',3750, '2007-05-09', 10,2), 
(13, '方東白', 19, '職員',5500, '2009-02-12', 10,2), 
(14, '張三豐', 88, '銷售總監',14000, '2004-10-12', 1,4), 
(15, '俞蓮舟', 38, '銷售',4600, '2004-10-12', 14,4), 
(16, '宋遠橋', 40, '銷售',4600, '2004-10-12', 14,4), 
(17, '陳友諒', 42, null,2000, '2011-10-12', 1,null);

2. 概述

多表查詢就是指從多張表中查詢資料，

原來查詢單表資料，執行的SQL形式為：select * from emp;

那么我們要執行多表查詢，就只需要使用逗號分隔多張表即可，如：select * from emp, dept;

具體的執行結果如下：

此時，我們看到查詢結果中包含了大量的結果集，總共102條記錄，而這其實就是員工表emp的所有記錄(17)與部門表dept的所有記錄(6)的所有組合情況，這種現象稱之為笛卡爾積，

笛卡爾積：笛卡爾積是指在數學中，兩個集合，A集合和B集合的所有組合情況，

而在多表查詢中，我們是需要消除無效的笛卡爾積的，只保留兩張表關聯部分的資料，

在SQL陳述句中，我們可以給多表查詢加上連接查詢條件即可去除無效的笛卡爾積，

select * from emp , dept where emp.dept_id = dept.id;

而由于id為17的員工，沒有dept_id欄位值，所以在多表查詢時，根據連接查詢的條件并沒有查詢到，

三. 多表查詢分類

1. 內連接

內連接查詢的是兩張表交集部分的資料（也就是綠色部分的資料）

(1). 隱式內連接

SELECT 欄位串列 FROM 表1 , 表2 WHERE 條件 ... ;

案例1：查詢每一個員工的姓名 及關聯的部門的名稱 (隱式內連接實作)

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;

(2). 顯示內連接

SELECT 欄位串列 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;

案例1：查詢每一個員工的姓名及關聯部門的名稱（顯式內連接），

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

注：一旦為表起了別名，就不能再使用表明名來指定對應的欄位了，此時只能夠使用別名來指定欄位

2. 外連接

外連接分為兩種，分別是左外連接和右外連接（左外連接是藍色和綠色部分，右外連接是黃色和綠色部分）

(1). 左外連接

SELECT 欄位串列 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;

左外連接相當于查詢表1(左表)的所有資料，當然也包含表1和表2交集部分的資料，

案例1：查詢emp表的所有資料和對對應的部門資訊，

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

(2). 右外連接

SELECT 欄位串列 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

右外連接相當于查詢表2(右表)的所有資料，當然也包含表1和表2交集部分的資料

案例2：查詢dept表的所有資料和對應的員工資訊(右外連接)

select d.*, e.* from emp e right join dept d on e.dept_id = d.id;

注：左外連接和右外連接是可以相互替換的，只需要調整在連接查詢時SQL中，表結構的先后順序就可以了，而我們在日常開發使用時，更偏向于左外連接，

3. 自連接

(1). 自連接查詢

自己連接自己，就是把一張表連接查詢多次

SELECT 欄位串列 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

對于自連接查詢，可以是內連接查詢，也可以是外連接查詢，

案例1：查詢員工及其所屬領導的名字

select a.name , b.name from emp a , emp b where a.managerid = b.id;

案例2：查詢所有員工及其領導的名字，如果員工沒有領導，也需要查詢出來

select a.name '員工', b.name '領導' from emp a left join emp b on
a.managerid = b.id;

注：在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、回傳的欄位到底是哪一張表的欄位，

(2). 聯合查詢

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

SELECT 欄位串列 FROM 表A ... 

UNION [ ALL ] 

SELECT 欄位串列 FROM 表B ....;

對于聯合查詢的多張表的列數必須保持一致，欄位型別也需要保持一致，

union all會將全部的資料直接合并在一起，union會對合并之后的資料去重，

案例1：將薪資低于5000的員工和年齡大于50歲的員工全部查詢出來（用union）

select * from emp where salary < 5000 
union all 
select * from emp where age > 50;

union all查詢出來的結果僅僅進行了簡單的合并，并未去重，

select * from emp where salary < 5000 
union
select * from emp where age > 50;

union聯合查詢會對查詢出來的結果進行去重處理，

注：如果多條查詢陳述句查詢出來的結果，欄位數量不一致，在進行union/union all聯合查詢時，將會報錯

4. 子查詢

SQL陳述句中嵌套SELECT陳述句，稱為嵌套查詢，又稱子查詢，

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查詢外部的陳述句可以是 INSERT / UPDATE / DELETE / SELECT中的任何一個，

根據查詢結果不同：可分為：

A. 標量子查詢（子查詢結果為單個值）

B. 列子查詢（子查詢結果為一列）

C. 行子查詢（子查詢結果為一行）

D. 表子查詢（子查詢結果為多行多列）

根據子查詢位置，可分為：

A. WHERE之后

B. FROM之后

C. SELECT之后

(1). 標量子查詢

子查詢回傳的結果是單個值（數字、字串、日期等），最簡單的形式，這種子查詢稱為標量子查詢，

**常用的運算子：= <> > >= < <= **

案例1：查詢”銷售部”的所有員工資訊

select * from emp where dept_id = (select id from dept where name = '銷售部');

案例2：查詢在”東方白”入職之后的員工資訊

select * from emp where entrydate > (select entrydate from emp where name = '方東白');

(2). 列子查詢

子查詢回傳的結果是一列（可以是多行），這種子查詢稱為列子查詢

常用的運算子：IN、NOT IN、ANY、SOME、ALL

案例1：查詢”銷售部”和”市場部”的所有員工資訊

select * from emp where dept_id in 
(select id from dept where name = '銷售部' or name = '市場部');

案例2：查詢比”財務部”所有人工資都高的員工資訊

select * from emp where salary > all (select salary from emp 
where dept_id = (select id from dept where name = '財務部'));

案例3：查詢比研發部其中任意一人工資高的員工資訊

select * from emp where salary > any (select salary from emp 
where dept_id = (select id from dept where name = '研發部'));

(3). 行子查詢

子查詢回傳的結果是一行（可以是多列），這種子查詢稱為行子查詢

常用的運算子：=、<>、IN、NOT IN

案例1：查詢與”張無忌”的薪資及直屬領導相同的員工資訊，

select * from emp where (salary,managerid) = (select salary, managerid
from emp where name = '張無忌');

(4). 表子查詢

子查詢回傳的結果是多行多列，這種子查詢稱為表子查詢，

常見的運算子：IN

案例1：查詢與”鹿杖客”，”宋遠橋”的職位和薪資相同的員工資訊

select * from emp where (job,salary) in (select job, salary from emp
where name = '鹿杖客' or name = '宋遠橋');

案例2：查詢入職日期是”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;

四. 多表查詢案例

準備環境資料：

create table salgrade( 
	grade int, losal int, 
	hisal int 
) comment '薪資等級表'; 

insert into salgrade values (1,0,3000); 
insert into salgrade values (2,3001,5000); 
insert into salgrade values (3,5001,8000); 
insert into salgrade values (4,8001,10000); 
insert into salgrade values (5,10001,15000); 
insert into salgrade values (6,15001,20000); 
insert into salgrade values (7,20001,25000); 
insert into salgrade values (8,25001,30000);

主要設計三張表：emp員工表，dept部門表，salgrade薪資等級表

1. 查詢員工的姓名、年齡、職位、部門資訊(隱式內連接)

select e.name, e.age, e.job, d.name from emp e, dept d 
where e.dept_id = d.id;

2. 查詢年齡小于30歲的員工的姓名、年齡、職位、部門資訊（顯式內連接）

select e.name , e.age , e.job , d.name from emp e 
inner join dept d on e.dept_id = d.id where e.age < 30;

3. 查詢擁有員工的部門ID、部門名稱

select distinct d.id, d.name from emp e, dept d 
where e.dept_id = d.id;

4.查詢所有年齡大于40歲的員工及其歸屬的部門名稱；如果員工沒有分配部門，也需要展示出來，

select e.*, d.name from emp e left join dept d on e.dept_id = d.id
where e.age > 40;

5. 查詢所有員工的工資等級

select e.*, s.grade, s.losal, s.hisal from emp e, salgrade s 
where e.salary between s.losal and s.hisal;

6. 查詢”研發部”所有員工的資訊及工資等級

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 = '研發部';

7. 查詢”研發部”員工的平均工資

select avg(e.salary) from emp e, dept d 
where e.dept_id = d.id and d.name = '研發部';

8. 查詢工資比”滅絕”高的員工資訊

select * from emp where salary > (select salary from emp where name = '滅絕');

9. 查詢比平均薪資高的員工資訊

select * from emp where salary > (select avg(salary) from emp);

10. 查詢低于本部門平均工資的員工資訊

select * from emp e2 where e2.salary < (select avg(e1.salary) from 
emp e1 where e1.dept_id = e2.dept_id);

11. 查詢所有的部門資訊，并統計部門的員工人數

select d.id, d.name, (select count(*) from emp e 
where e.dept_id = d.id) '人數' from dept d;

更多mysql學習請關注微信公眾號”云哥技術yun3k”，回復”mysql學習”，免費領取mysql全套學習資料，

標籤：其他

上一篇：Mysql基礎篇（二）之函式和約束

下一篇：返回列表