一、什么是聯合查詢

1.1、概念

聯合查詢是SQL中用于合并多個SELECT語句結果集的操作。條件是被合并的結果集列數相同并且數據類型兼容。也可以說一次查詢涉及兩張或兩張以上的表，就稱為聯合查詢。

1.2、為什么要使用聯合查詢

如果數據被拆分到多個表中，我們需要查看一條數據的完整信息，這時就需要用到聯合查詢。

1.3、語法

SELECT * FORM 表名,表名......;

注意：多個表進行聯合查詢，取笛卡爾積

1.4、示例

創建一個班級表，一個學生表

# 創建班級表
drop table if exists class;
create table class(c_id bigint auto_increment primary key,c_name varchar(10)
);
# 創建學生表
drop table if exists students;
create table students(id bigint auto_increment primary key,name varchar(10),class_id bigint
);
# 插入數據
insert into class(c_id,c_name) values (1,'數學1班'),(2,'物理1班'),(3,'化學1班');
insert into students(id,name,class_id) values (1,'張三',1),(2,'李四',1),(3,'王五',2),(4,'趙六',3);
# 進行聯合查詢
select * from students,class;

其運行結果如下：

通過觀察，我們發現兩張表聯合查詢的結果集中，有些是無效數據。那么問題來了，如何過濾掉這些無效數據呢？

通過表與表之間的連接條件過濾掉無效數據

select * from students,class where students.class_id=class.c_id;

其運行結果如下：

注意：class_id和c_id的前面要加上各自對應的"表名.",這樣數據庫才能精確地知道到底該使用哪張表里的id列

通過指定列查詢，精減查詢結果

select students.id,students.name,students.class_id,class.c_name from students,class where students.class_id=class.c_id;

其運行結果如下：

通過給表起別名來簡化查詢語句

select s.id,s.name,s.class_id,c.c_name from students s,class c where s.class_id=c.c_id;

其運行結果如下：

二、內連接

2.1、概念

內連接是根據兩個表中的共同字段，將符合條件的數據進行關聯查詢，具體來說，當兩個表中的連接條件都滿足時，才會返回相關聯的數據。

2.2、語法

//寫法一
select 字段 from 表1 別名,表2 別名 where 連接條件 and  其它條件;
//寫法2
select 字段 from 表1 別名 [inner] join 表2 別名 on 連接條件 where 其它條件;

簡記：join兩邊是表名，on的后邊是條件。

2.3、示例

查詢學生id為4的同學的班級和姓名

select c.c_name,s.name from students s join class c on s.class_id=c.c_id where s.id=4;

其運行結果如下：

三、外連接

3.1、概念

外連接是關系數據庫中的一種操作。它用于合并兩個或多個表中的數據，和內連接不同的是，外連接會返回至少一個表中的所有行。

3.2、分類

外連接分為左外連接、右外連接和全外連接三種類型，MySQL不支持全外連接。

左外連接

返回左表的所有記錄和右表中匹配的記錄。如果右表中沒有匹配的記錄，則結果集中對應字段會顯示為NULL。

1、語法

select 字段名 from 表名1 left join 表名2 on 連接條件; 

left join 左邊的表為基準表

2、示例

插入在班級表中沒有編號的學生的信息

insert into students values(5,'錢七',4);

使用左外連接

select s.id,s.name,c.c_name from students s left join class c on s.class_id=c.c_id;

其結果運行如下：

由上圖，我們可以看出學生表的數據完全顯示，班級表沒有的數據為Null。

右外連接

與左外連接相反，返回右表的所有記錄和左表中匹配的記錄。如果左表中沒有匹配的記錄，則結果集中對應字段會顯示為NULL。

1、語法

select 字段 from 表名1 right join 表名2 on 連接條件;

2、示例

插入在學生表中沒有編號的班級信息

insert into class values(5,'地理1班');

使用右外連接

select s.id,s.name,c.c_name from students s right join class c on s.class_id=c.c_id;

其運行結果如下：

全外連接

結合了左外連接和右外連接的特點，返回左右表中的所有記錄。如果某?邊表中沒有匹配的記錄，則結果集中對應字段會顯示為NULL。
由于MySQL不支持，這里我就不多贅述。

四、自連接

4.1、概念

自連接是自己與自己取笛卡爾積，可以把行轉化成列，在查詢的時候可以使用where條件對結果進行過濾，或者說實現行與?行之間的比較。在做表連接時為表起不同的別名。

4.2、示例

創建成績表

create table score(id bigint,數學 int,物理 int,化學 int
);
# 插入數據
insert into score values(1,70,80,90),(2,88,66,90),(3,75,82,86);
# 查詢表結果
select * from score;

其結果運行如下：

使用自連接

select * from score sco1,score sco2;

其運行結果如下：

查詢數學成績大于物理成績的信息

select sco1.id,sco1.`數學`,sco1.`物理` from score sco1,score sco2 where sco1.id=sco2.id and sco1.數學>sco2.物理;

其運行結果如下：

五、子查詢

5.1、概念

子查詢是把?個SELECT語句的結果當做別?個SELECT語句的條件，也叫嵌套查詢。

5.2、語法

select * from 表1 where 列1 {= | IN} (select 列1 from 表2 where 列2 {= | IN} [(select ...)] ...
)

5.3、分類

1、單行子查詢

嵌套的查詢中只返回一行數據

語法

select * from 表名 where 列1 =(select 列1 from 表名 where 條件);

示例

查詢數學成績為70的同學的所有信息

select * from score where id =(select id from score where 數學=70); 

其運行結果如下：

2、多行子查詢

嵌套的查詢中返回多?數據，使?[NOT]IN關鍵字

語法

select * from 表名 where 列1 [not] in(select 列1 from 表名 where 條件);

示例

查詢化學成績為90的所有同學的信息

select * from score where id in(select id from score where 化學=90);

其運行結果如下：

查詢物理成績不為80的所有同學的信息

select * from score where id not in(select id from score where 物理=80);

其運行結果如下：

其與單行子查詢的書寫差別是把=改成in

3、多列子查詢

單行子查詢和多行子查詢都只返回一列數據，多列子查詢中可以返回多個列的數據，外層查詢與嵌套的內層查詢的列要匹配

示例

新增重復的分數

insert into score (id,數學,物理,化學) values(4,70,80,90),(5,88,66,90);

查詢重復錄入的分數

select * from score where (數學,物理,化學) in(select 數學,物理,化學 from score group by 數學,物理,化學 having count(0)>1);

其運行結果如下：

六、合并查詢

合并多個select操作返回的結果，可以使?集合操作符union，union all

6.1、Union

該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。

示例

查詢id為2和數學成績為70的同學信息

select * from score where id=3 union select * from score where 數學=70;

查詢id為1和數學成績為70的同學信息

select * from score where id=1 union select * from score where 數學=70;

這里只有兩條數據，是因為union有去重功能

6.2、Union all

該操作符?于取得兩個結果集的并集。當使?該操作符時，不會去掉結果集中的重復?。

示例

查詢id為1和數學成績為70的同學信息

select * from score where id=1 union all select * from score where 數學=70;

這里使用union all并沒有去重

七、復制表結構

語法

create table 新表名 like 原表名;

示例

新構建一個班級表

create table new_class like class;

查看原表結構

查看新表結構

八、復制表信息

語法

insert into 新表名 select * from 原表名;

示例

insert into new_class select * from class;

通過查詢我們可以發現，新表的數據與原表相同。