MySQL 數據庫聚合查詢和聯合查詢操作

1. 插入被查詢的結果

語法：

insert into 要插入的表 [(列1, ..., 列n)] select {* | (列1, ..., 列n)}from 要查詢的表

上述語句可以將要查詢的表的某些列插入到新的表中對應的某些列

示例1： 將 student1 表（隻包含 id 和 name 字段，且該表已經插入內容）的內容全部插入到 student2 表（隻包含 id 和name 字段，該表尚未插入內容）中

示例2： 將 student1 表（隻包含 id 和 name 字段，且該表已經插入內容）的字段 name 插入到 student2 表（隻包含 id 和name 字段，該表尚未插入內容）中

2. 聚合查詢

2.1 介紹

聚合查詢：是指對一個數據表中某個字段的數據進行部分或者全部統計查詢的一種方式（即是在行的維度進行合並的查詢）。比如所有全部書的平均價格或者是書籍的總數量等等，在這些時候就會使用到聚合查詢這種方法。

2.2 聚合函數

聚合查詢可以使用以下常用聚合函數，這些聚合函數就相當於 SQL 提供的“庫函數”

補充：

• 當隻針對某列進行行數的查詢時，如果某行的值為 null，則查詢結果不計算這行
• 對數據進行求和時，數據類型必須為數字，字符串和日期都無法求和
• 如果語法沒有錯誤，但是出現運行時錯誤則會報出警告 warnings，此時可以通過 show warnings SQL 語句查看警告信息

接下來以表名為 exam_result，具體數據如下的表，進行示例展示

示例1： 返回查詢到的數據的行數

示例2： 返回查詢到的列為 chinese 數據的行數

示例3： 返回查詢到的語文成績的總和

示例4： 返回查詢到的各科成績的平均值

示例5： 返回總成績的最高值

示例6： 返回總成績的最低值

2.3 group by 子句

使用前面的聚合函數，實際上是把該表中的所有行結合起來。但還可以使用 group by 來進行分組聚合（在 group by 後面加上指定列名，那麼該列中值相同的就將分成一組）

接下來我們將對表名為 emp，數據如下的表進行示例展示

示例1： 查看各個崗位的平均薪資

示例2： 查看各個崗位的平均、最高、最低薪資

2.4 having

如果使用 group by 子句進行分組以後，需要對分組結果再進行條件過濾，這時就不能使用 where 語句瞭，而是使用 having 語句

註意：

• where 語句是在分組之前進行篩選
• having 語句是在分組之後進行篩選
• where 語句和 having 語句可以同時使用

示例1： 查詢薪資大於10000的崗位

示例2： 排除張三薪資後，薪資大於10000的崗位

3. 聯合查詢

3.1 介紹

聯合查詢：是可合並多個相似的選擇查詢的結果集。 也就是進行多表查詢，其核心思想是使用瞭笛卡爾積

笛卡爾積思想：

使用笛卡爾積的思想，其實就是把兩個表的結果進行一個排列組合，接下來我們將兩個表 A、B 通過笛卡爾積的思想得到一個新的表 C

學生表 A：

班級表 B：

新表 C：

補充：

• 笛卡爾積得到的結果任然是一個表
• 該表的列數，是兩張表的列數之和
• 該表的行數，是兩張表的行數之積

通過新得到的 C 表，我們就可以將 A、B 兩張表聯系起來，而聯系的紐帶在上面的示例中就是班級id。到此時，雖然將兩個表聯系起來瞭，但是不是新表中的每條數據都是合理的，例如第2行的信息其實就是不正確的。因此將兩表聯系起來後，還需要加上一些條件的限制，如 A 和 B 表的班級id應該相同，此時就可以得到一個數據更合理的表 D

新表 D：

此時我們就可以進行一個多表查詢

註意：

聯合查詢由於使用瞭笛卡爾積，那麼新表的行數就是所有表聯合的乘積。因此使用聯合查詢結果的數據可能很大，要謹慎使用

以下示例都是通過下面 SQL 語句建的表來進行操作學習的，如果你想在後面的內容進行操作，可以直接復制使用

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('計算機系2019級1班', '學習瞭計算機原理、C和Java語言、數據結構和算法'),
('中文系2019級3班','學習瞭中國傳統文學'),
('自動化2019級5班','學習瞭機械自動化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風李逵','[email protected]',1),
('00835','菩提老祖',null,1),
('00391','白素貞',null,1),
('00031','許仙','[email protected]',1),
('00054','不想畢業',null,1),
('51234','好好說話','[email protected]',2),
('83223','tellme',null,2),
('09527','老外學中文','[email protected]',2);

insert into course(name) values
('Java'),('中國傳統文化'),('計算機原理'),('語文'),('高階數學'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋風李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素貞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 許仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想畢業
(81, 5, 1),(37, 5, 5),
-- 好好說話
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

3.2 內連接

語法：

-- 方法一：
select 展示的列名 from 表1 [表1別名],表2 [表2別名] where 連接條件;

-- 方式二：使用 [inner] join on
select 展示的列名 from 表1 [表1別名] [inner] join 表2 [表2別名] on 連接條件;

補充：

• 使用多表查詢時，由於有多個表，所以使用其中的列，方式為：表名.列名
• 可以使用 表名 別名 的方式，將表名起一個別名使用
• 使用 [inner] join on 方式，如果省略 inner，則默認內連接

示例1： 查詢許仙同學每門課的成績

示例2： 查詢每個同學的總成績

示例3： 查詢每個同學每門課的成績

3.3 外連接

外連接：分為左外連接和右外連接。如果使用聯合查詢，左側的表完全顯示就是用瞭左外連接；右側的表完全顯示就是用瞭右外連接

外連接其實和內連接差不多，都是使用瞭笛卡爾積。內連接是針對的兩個表中的每一條數據都是一一對應的，那怎麼就不是一一對應瞭呢？例如下面兩個表 A、B

A 表：

B 表：

我們發現經過笛卡爾積後建立的新表時，A 表的 id 為3的記錄和 B 表中沒有對應的數據，B 表中 student_id 為4的記錄和 A 表中也沒有對應的數據，因此這兩個表就不能使用內連接的方式去查詢，要使用外連接

如果使用左連接的方式，新表 C 為：

如果使用右連接的方式，新表 D 為：

補充：

• 當兩個表中的數據可以一一對應時，使用外連接和內連接是等價的
• 除瞭內連接、左外連接、右外連接，其實還有全外連接，但是 MySQL 中不支持全外連接操作

語法：

-- 左連接，表1完全顯示
select 展示的列名 from 表1 [表1別名] [left] join 表2 [表2別名] on 連接條件;

-- 右連接，表2完全顯示
select 展示的列名 from 表1 [表1別名] [right] join 表2 [表2別名] on 連接條件;

3.4 自連接

自連接：是指在同一張表中連接自身進行查詢，使用自連接其實可以將”行轉換成列“來進行操作

為什麼自連接可以將行轉換成列來進行操作呢？假設有一張表 A

如果我想找到原表中 student_id 為1，且其課程2成績高於課程3的同學的信息時，就是要對行與行之間進行比較，但是一張表是不能進行該操作的

通過對自己進行笛卡爾積之後，得到新的表 B

此時我們發現，如果將原表進行笛卡爾積後，有瞭兩張一樣的表，就可以實施行與行之間操作

示例： 查詢 Java 成績高於計算機原理成績的同學

3.5 子查詢

子查詢：是指嵌入在其它 SQL 語句中的 select 語句，也叫嵌套查詢

分類：

• 單行子查詢：返回一行記錄的子查詢
• 多行子查詢：返回多行記錄的子查詢（使用 in 或者 exists）

補充：

• 用 in 進行多行查詢過程： 使用子查詢時，先執行子查詢，將查詢的結果存放在內存裡，再執行外層查詢，根據內存裡的結果進行篩選
• 用 exists 進行多行查詢過程： 先執行外層循環，這樣就會得到很多記錄，在針對每行記錄將它帶入到子查詢中，符合條件的就保留（exists 就是檢測子查詢結果是否為空集合）

綜上所述：

基於 in 的寫法，速度快，適合子查詢結果集合比較小的情況（較大內存裝不下）
基於 exists 的寫法，速度慢，適合子查詢結果集合比較大，且外層查詢結果數量比較少的情況

示例1： 查詢不想畢業同學的同班同學（首先要知道不想畢業同學的班級，然後通過班級篩選學生）

示例2： 查詢語文或者英語課程的成績信息（使用 in）

示例3： 查詢語文或者英語課程的成績信息（使用 exists）

3.6 合並查詢

合並查詢：是使用集合操作符 union 或 union all 來合並多個 select 的執行結果。使用合並查詢時，前後查詢的結果集中，字段需要一致

補充：

• union 操作符不會對結果集的數據進行去重，union all 會進行去重
• 集合操作符的功能其實和操作符 or 的功能類似，但是如果針對不同的表進行查詢，那麼 or 就不能使用

示例： 查看 id 小於3，或者課程為 Java 的信息

推薦閱讀：

• 一文弄懂MYSQL如何列轉行
• mysql常用sql與命令之從入門到刪庫跑路
• Java mysql特殊形式的查詢語句詳解
• SQL Server 數據庫基礎編程詳解
• MySQL 行轉列詳情