Mysql基礎篇（三）之多表查詢

来源:https://www.cnblogs.com/yun3k/archive/2023/07/02/17520892.html
-Advertisement-
Play Games

# 一. 多表關係 - **一對多（多對一）** - **多對一** - **一對一** ## 1. 一對多 ### (1). 案例：部門與員工的關係 ### (2). 關係：一個部門對應多個員工，一個員工對應一個部門 ### (3). 實現：在多的一方建立外建，指向一的一方的主鍵 ![](http ...


一. 多表關係

  • 一對多（多對一）

  • 多對一

  • 一對一

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欄位值，所以在多表查詢時，根據連接查詢的條件並沒有查詢到。

三. 多表查詢分類

名稱 子分類 說明
連接查詢 內連接 相當於查詢A、B交集部分數據
外連接 mysql不原生支持全外連接
左外連接 查詢左表所有數據以及兩張表交集部分數據
右外連接 查詢右表所有數據以及兩張表交集部分數據
自連接 當前表與自身的連接查詢，自連接必須使用表別名
子查詢

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

操作符 描述
IN 在指定的集合範圍之內，多選一
NOT IN 不在指定的集合範圍之內
ANY 子查詢返回列表中，有任意一個滿足即可
SOME 與ANY等同，使用SOME的地方都可以使用ANY
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全套學習資料。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 掌握嵌入式Linux編程2工具鏈
    Linux下PAM認證詳解（以centos7為例） PAM簡介(Pluggable Authentication Modules，可插拔認證模塊) Sun公司於1995年開發的一種與認證相關的通用框架機制：PAM（可插拔認證模塊）是實現認證工作的一個模塊。 因為每個服務都用到不同的認證方式，所以就需 ...
  • ASL晶元CS5466方案設計|集睿致遠CS5466代理商|Type-c轉HDMI電路原理
    CS5466作為ASL集睿致遠新推出的高性能Type-C to HDMI2.1協議轉換器，可以通過HDMI輸出埠作為TMDS或FRL發射機進行操作。 CS5466適配於多個配件市場和現實應用主板，例如：主板,顯示埠,擴展塢等。CS5266還配備了最高級別的HDCP嵌入式秘鑰，能夠安全傳輸受保護... ...
  • Linux磁碟操作：分區、格式化、掛載
    # Linux磁碟操作：分區、格式化、掛載 ## 一、分區 > **fdisk分區** > > （1）fdisk命令只支持msdos，分區的時候只支持小容量硬碟（ （2）fdisk命令不支持gpt，所以當使用fdisk命令給gpt類型硬碟分區是會出現告警 1. 首先先把設備關機，添加一塊新的磁碟 2 ...
  • MySQL自動安裝腳本分享
    這裡分享一個我寫的MySQL自動安裝腳本mysql_auto_install.sh，它的功能非常簡單，就是自動化安裝MySQL單實例，讓DBA的工作更簡單、輕鬆一些，不用去手工安裝MySQL實例。從簡單重覆的工作中釋放出來。 下麵是關於mysql_auto_install.sh腳本的一些說明： 1： ...
  • sql server 資料庫自動備份
    一丶打開客戶端: 對象資源管理器->管理->維護計劃(右鍵點擊)->維護計劃嚮導 二丶打開後點擊下一步, 填寫名稱與說明並更改備份計劃 三丶點下一步, 選擇維護任務 四丶點擊下一步, 選擇需要備份的資料庫, 和備份文件路徑 五丶點擊下一步, 選擇報告文件保存路徑 六丶點擊下一步, 查看維護計劃, 沒 ...
  • Mysql基礎篇（四）之事務
    # 一. 事務簡介 **事務是一組操作的集合，它是一個不可分隔的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要麼同時成功，要麼同時失敗。** **就比如：張三給李四轉賬1000塊錢，張三銀行賬戶的錢減少了1000，而李四銀行賬戶的錢要增加1000。這一組操作就必須 ...
  • 完全相容DynamoDB協議！GaussDB(for Cassandra)為NoSQL註入新活力
    摘要：DynamoDB是一款托管式的NoSQL資料庫服務，支持多種數據模型，廣泛應用於電商、社交媒體、游戲、IoT等場景。 本文分享自華為雲社區《完全相容DynamoDB協議！GaussDB(for Cassandra)為NoSQL註入新活力》，作者：GaussDB 資料庫 。 DynamoDB是一 ...
  • SQL Server中的NULL值處理：判斷與解決方案
    摘要： 在SQL Server資料庫中，NULL是表示缺少數據或未知值的特殊標記。處理NULL值是SQL開發人員經常遇到的問題之一。本文將介紹SQL Server中判斷和處理NULL值的不同方法，以及一些解決方案，幫助您更好地處理資料庫中的NULL值情況。 文章內容： 引言： 在資料庫開發中，經常會 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類