目錄
? 多表關(guān)系
- 項(xiàng)目開發(fā)中恐疲，在進(jìn)行數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)時(shí)省咨，會(huì)根據(jù)業(yè)務(wù)需求及業(yè)務(wù)模塊之間的關(guān)系敌蜂，分析并設(shè)計(jì)表結(jié)構(gòu)秸脱，由于業(yè)務(wù)之間相互關(guān)聯(lián)笔呀，所以各個(gè)表結(jié)構(gòu)之間也存在著各種聯(lián)系。
- 基本上分為三種：
- 一對(duì)多(多對(duì)一)
- 多對(duì)多
- 一對(duì)一
?一對(duì)多
- 案例: 部門 與 員工的關(guān)系
- 關(guān)系: 一個(gè)部門對(duì)應(yīng)多個(gè)員工搭幻，一個(gè)員工對(duì)應(yīng)一個(gè)部門
- 實(shí)現(xiàn): 在多的一方建立外鍵坠七，指向一的一方的主鍵
image.png
?多對(duì)多
- 案例: 學(xué)生 與 課程的關(guān)系
- 關(guān)系: 一個(gè)學(xué)生可以選修多門課程，一門課程也可以供多個(gè)學(xué)生選擇
- 實(shí)現(xiàn): 建立第三張中間表筋栋，中間表至少包含兩個(gè)外鍵，分別關(guān)聯(lián)兩方主鍵
image.png
- 代碼部分
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">create table student(
id int auto_increment primary key comment '主鍵ID',</pre>
no varchar(10) comment '學(xué)號(hào)'
) comment '學(xué)生表';
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 '學(xué)生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 '學(xué)生課程中間表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);
?一對(duì)一
- 案例: 用戶 與 用戶詳情的關(guān)系
- 關(guān)系: 一對(duì)一關(guān)系，多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中鉴嗤，其它詳情字段放在另一張表中，以提升操作效率
- 實(shí)現(xiàn): 在任意一方加入外鍵韭寸，關(guān)聯(lián)另外一方的主鍵春哨，并且設(shè)置外鍵為唯一的(UNIQUE)
image.png
- 代碼部分
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 '手機(jī)號(hào)'
) comment '用戶基本信息表';
create table tb_user_edu(
id int auto_increment primary key comment '主鍵ID',
degree varchar(20) comment '學(xué)歷',
major varchar(50) comment '專業(yè)',
primaryschool varchar(50) comment '小學(xué)',
middleschool varchar(50) comment '中學(xué)',
university varchar(50) comment '大學(xué)',
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,'本科','舞蹈','靜安區(qū)第一小學(xué)','靜安區(qū)第一中學(xué)','北京舞蹈學(xué)院',1),
(null,'碩士','表演','朝陽(yáng)區(qū)第一小學(xué)','朝陽(yáng)區(qū)第一中學(xué)','北京電影學(xué)院',2),
(null,'本科','英語(yǔ)','杭州市第一小學(xué)','杭州市第一中學(xué)','杭州師范大學(xué)',3),
(null,'本科','應(yīng)用數(shù)學(xué)','陽(yáng)泉第一小學(xué)','陽(yáng)泉區(qū)第一中學(xué)','清華大學(xué)',4);
多表查詢就是指從多張表中查詢數(shù)據(jù)沧踏。
原來(lái)查詢單表數(shù)據(jù)产禾，執(zhí)行的SQL形式為：select * from emp;
那么我們要執(zhí)行多表查詢卿泽，就只需要使用逗號(hào)分隔多張表即可烘挫，如： select * from emp , dept;
? 數(shù)據(jù)準(zhǔn)備
1.創(chuàng)建emp表并插入測(cè)試數(shù)據(jù)
--創(chuàng)建dept
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 '入職時(shí)間',
managerid int comment '直屬領(lǐng)導(dǎo)ID',
dept_id int comment '部門ID'
)comment '員工表';
--插入數(shù)據(jù)
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
(2, '張無(wú)忌', 20, '項(xiàng)目經(jīng)理',12500, '2005-12-05', 1,1),
(3, '楊逍', 33, '開發(fā)', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開發(fā)',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '開發(fā)',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序員鼓勵(lì)師',6600, '2004-10-12', 2,1),
(7, '滅絕', 60, '財(cái)務(wù)總監(jiān)',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '會(huì)計(jì)',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出納',5250, '2009-05-13', 7,3),
(10, '趙敏', 20, '市場(chǎng)部總監(jiān)',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, '銷售總監(jiān)',14000, '2004-10-12', 1,4),
(15, '俞蓮舟', 38, '銷售',4600, '2004-10-12', 14,4),
(16, '宋遠(yuǎn)橋', 40, '銷售',4600, '2004-10-12', 14,4),
(17, '陳友諒', 42, null,2000, '2011-10-12', 1,null);
2.創(chuàng)建dept表并插入測(cè)試數(shù)據(jù)
--創(chuàng)建dept
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部門名稱'
)comment '部門表';
--插入數(shù)據(jù)
INSERT INTO dept (id, name) VALUES (1, '研發(fā)部'), (2, '市場(chǎng)部'),(3, '財(cái)務(wù)部'), (4, '銷售部'), (5, '總經(jīng)辦'), (6, '人事部');
3.添加外鍵
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
?笛卡爾積
笛卡爾乘積是指在數(shù)學(xué)中，兩個(gè)集合A集合 和 B集合的所有組合情況搔预。
而在多表查詢中灵疮，是需要消除無(wú)效的笛卡爾積的叽讳，只保留兩張表關(guān)聯(lián)部分的數(shù)據(jù)褒脯。在SQL語(yǔ)句中可以給多表查詢加上連接查詢的條件即可便瑟。
select * from emp , dept where emp.dept_id = dept.id;
image.png
- 注意：
- 而由于id為17的員工，沒有dept_id字段值番川，所以在多表查詢時(shí)到涂，根據(jù)連接查詢的條件并沒有查詢到。
? 內(nèi)連接
- 相當(dāng)于查詢A颁督、B交集部分?jǐn)?shù)據(jù)
- 內(nèi)連接的語(yǔ)法分為兩種: 隱式內(nèi)連接践啄、顯式內(nèi)連接。
?隱式內(nèi)連接
- 1.語(yǔ)法格式：
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
--起別名
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
image.png
?顯式內(nèi)連接
- 1.語(yǔ)法格式：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;
2.案例：
查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (顯式內(nèi)連接實(shí)現(xiàn)) INNER JOIN … ON …
表結(jié)構(gòu): emp , dept
連接條件: emp.dept_id = dept.id
代碼實(shí)現(xiàn)：
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
---inner可以省略
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
image.png
?外連接
外連接分為兩種沉御，分別是：左外連接 和 右外連接屿讽。
?左外連接
1.查詢左表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
2.左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù)嚷节，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)聂儒。
3.語(yǔ)法格式
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;
4.案例:
查詢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;
---省略outer
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
image.png
?右外連接
- 1.查詢右表所有數(shù)據(jù)效斑，以及兩張表交集部分?jǐn)?shù)據(jù)
- 2.右外連接相當(dāng)于查詢表2(右表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)柱徙。
- 3.語(yǔ)法格式
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;
4.案例:
查詢dept表的所有數(shù)據(jù), 和對(duì)應(yīng)的員工信息(右外連接)
由于需求中提到缓屠，要查詢dept表的所有數(shù)據(jù)，所以是不能內(nèi)連接查詢的护侮，需要考慮使用外連接查詢敌完。
表結(jié)構(gòu): emp, dept
連接條件: emp.dept_id = dept.id
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
---省略outer
select e.*, d.name from emp e right join dept d on e.dept_id = d.id;
image.png
?自連接
- 1.自連接查詢，顧名思義羊初，就是自己連接自己滨溉，也就是把一張表連接查詢多次什湘。
- 2.當(dāng)前表與自身的連接查詢，自連接必須使用表別名
- 3.而對(duì)于自連接查詢晦攒，可以是內(nèi)連接查詢闽撤，也可以是外連接查詢。
- 4.查詢語(yǔ)法
select a.name , b.name from emp a , emp b where a.managerid = b.id;
image.png
- 查詢所有員工 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;
image.png
?聯(lián)合查詢
- 1.對(duì)于union查詢脯颜，就是把多次查詢的結(jié)果合并起來(lái)哟旗，形成一個(gè)新的查詢結(jié)果集。
- 2.對(duì)于聯(lián)合查詢的多張表的列數(shù)必須保持一致栋操，字段類型也需要保持一致闸餐。
- 3.union all 會(huì)將全部的數(shù)據(jù)直接合并在一起，union 會(huì)對(duì)合并之后的數(shù)據(jù)去重矾芙。
- 4.語(yǔ)法格式
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
5.案例
5.1將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來(lái)舍沙，可以直接使用多條件查詢，使用邏輯運(yùn)算符 or 連接即可蠕啄。也可以通過(guò)union/union all來(lái)聯(lián)合查詢
select * from emp where salary < 5000
union all
select * from emp where age > 50;
image.png
- 5.2union all查詢出來(lái)的結(jié)果场勤，僅僅進(jìn)行簡(jiǎn)單的合并，并未去重歼跟。
select * from emp where salary < 5000
union
select * from emp where age > 50;
image.png
注意：union 聯(lián)合查詢和媳，會(huì)對(duì)查詢出來(lái)的結(jié)果進(jìn)行去重處理。
5.3如果多條查詢語(yǔ)句查詢出來(lái)的結(jié)果哈街，字段數(shù)量不一致留瞳，在進(jìn)行union/union all聯(lián)合查詢時(shí)，將會(huì)報(bào)錯(cuò)
select * from emp where salary < 5000
union
select name from emp where age > 50;
image.png
?子查詢
SQL語(yǔ)句中嵌套SELECT語(yǔ)句骚秦，稱為嵌套查詢她倘，又稱子查詢。
子查詢外部的語(yǔ)句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個(gè)作箍。
-
根據(jù)子查詢結(jié)果不同硬梁，分為：
- 標(biāo)量子查詢（子查詢結(jié)果為單個(gè)值）
- 列子查詢(子查詢結(jié)果為一列)
- 行子查詢(子查詢結(jié)果為一行)
- 表子查詢(子查詢結(jié)果為多行多列)
-
根據(jù)子查詢位置，分為：
- WHERE之后
- FROM之后
- SELECT之后
?標(biāo)量子查詢
- 1.子查詢返回的結(jié)果是單個(gè)值（數(shù)字胞得、字符串荧止、日期等），最簡(jiǎn)單的形式阶剑，這種子查詢稱為標(biāo)量子查詢跃巡。
- 常用的操作符：= <> > >= < <=
- 2.案例:
- 2.1查詢 “銷售部” 的所有員工信息，完成這個(gè)需求時(shí)牧愁，可以將需求分解為兩步：
- 1.查詢 “銷售部” 部門ID
select id from dept where name = '銷售部';
image.png
- 2.根據(jù) “銷售部” 部門ID, 查詢員工信息
select * from emp where dept_id = (select id from dept where name = '銷售部');
image.png
2.2查詢?cè)?“方東白” 入職之后的員工信息素邪，完成這個(gè)需求時(shí)，可以將需求分解為兩步：
1.查詢 方東白 的入職日期
select entrydate from emp where name = '方東白';
image.png
2.查詢指定入職日期之后入職的員工信息
select * from emp where entrydate > (select entrydate from emp where name = '方東白');
image.png
?列子查詢
- 1.子查詢返回的結(jié)果是一列（可以是多行）猪半，這種子查詢稱為列子查詢兔朦。
- 常用的操作符：IN 偷线、NOT IN 、 ANY 烘绽、SOME 淋昭、 ALL
image.png
- 2.案例:
- 2.1查詢 “銷售部” 和 “市場(chǎng)部” 的所有員工信息
- 分解為以下兩步:
- 1.查詢 “銷售部” 和 “市場(chǎng)部” 的部門ID
select id from dept where name = '銷售部' or name = '市場(chǎng)部';
image.png
2.根據(jù)部門ID, 查詢員工信息
select * from emp where dept_id in (select id from dept where name = '銷售部' or name = '市場(chǎng)部');
]
ip%7CimageView2/2/w/1240)
- 2.2查詢比 財(cái)務(wù)部 所有人工資都高的員工信息
- 分解為以下兩步:
- 1.查詢所有 財(cái)務(wù)部 人員工資
select id from dept where name = '財(cái)務(wù)部';
select salary from emp where dept_id = (select id from dept where name = '財(cái)務(wù)部');
image.png
2.比財(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ù)部') );
2.3查詢比研發(fā)部其中任意一人工資高的員工信息
分解為以下兩步:
1.查詢研發(fā)部所有人工資
select salary from emp where dept_id = (select id from dept where name = '研發(fā)部');
image.png
2.比研發(fā)部其中任意一人工資高的員工信息
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研發(fā)部') );
image.png
?行子查詢
1.子查詢返回的結(jié)果是一行（可以是多列），這種子查詢稱為行子查詢安接。
常用的操作符：= 翔忽、<> 、IN 盏檐、NOT IN
2.案例
2.1查詢 “張無(wú)忌” 的薪資及直屬領(lǐng)導(dǎo)
select salary, managerid from emp where name = '張無(wú)忌';
image.png
? 表子查詢
1.子查詢返回的結(jié)果是多行多列歇式，這種子查詢稱為表子查詢。
常用的操作符：IN
2.案例
2.1查詢與 “鹿杖客” , “宋遠(yuǎn)橋” 的職位和薪資相同的員工信息
1.查詢 “鹿杖客” , “宋遠(yuǎn)橋” 的職位和薪資
select job, salary from emp where name = '鹿杖客' or name = '宋遠(yuǎn)橋';
image.png
2.2查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息 , 及其部門信息
1.入職日期是 “2006-01-01” 之后的員工信息
select * from emp where entrydate > '2006-01-01';
image.png
2.查詢這部分員工, 對(duì)應(yīng)的部門信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
image.png
