MySQL數(shù)據(jù)庫聚合查詢和聯(lián)合查詢怎么實(shí)現(xiàn)

這篇文章主要介紹“MySQL數(shù)據(jù)庫聚合查詢和聯(lián)合查詢怎么實(shí)現(xiàn)”，在日常操作中，相信很多人在MySQL數(shù)據(jù)庫聚合查詢和聯(lián)合查詢怎么實(shí)現(xiàn)問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL數(shù)據(jù)庫聚合查詢和聯(lián)合查詢怎么實(shí)現(xiàn)”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

1. 插入被查詢的結(jié)果

語法：

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

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

示例1： 將 student1 表（只包含 id 和 name 字段，且該表已經(jīng)插入內(nèi)容）的內(nèi)容全部插入到 student2 表（只包含 id 和name 字段，該表尚未插入內(nèi)容）中

示例2： 將 student1 表（只包含 id 和 name 字段，且該表已經(jīng)插入內(nèi)容）的字段 name 插入到 student2 表（只包含 id 和name 字段，該表尚未插入內(nèi)容）中

2. 聚合查詢

2.1 介紹

聚合查詢：是指對一個(gè)數(shù)據(jù)表中某個(gè)字段的數(shù)據(jù)進(jìn)行部分或者全部統(tǒng)計(jì)查詢的一種方式（即是在行的維度進(jìn)行合并的查詢）。比如所有全部書的平均價(jià)格或者是書籍的總數(shù)量等等，在這些時(shí)候就會使用到聚合查詢這種方法。

2.2 聚合函數(shù)

聚合查詢可以使用以下常用聚合函數(shù)，這些聚合函數(shù)就相當(dāng)于 SQL 提供的“庫函數(shù)”

補(bǔ)充：

• 當(dāng)只針對某列進(jìn)行行數(shù)的查詢時(shí)，如果某行的值為 null，則查詢結(jié)果不計(jì)算這行

• 對數(shù)據(jù)進(jìn)行求和時(shí)，數(shù)據(jù)類型必須為數(shù)字，字符串和日期都無法求和

• 如果語法沒有錯(cuò)誤，但是出現(xiàn)運(yùn)行時(shí)錯(cuò)誤則會報(bào)出警告 warnings，此時(shí)可以通過 show warnings SQL 語句查看警告信息
  

接下來以表名為 exam_result，具體數(shù)據(jù)如下的表，進(jìn)行示例展示

示例1： 返回查詢到的數(shù)據(jù)的行數(shù)

示例2： 返回查詢到的列為 chinese 數(shù)據(jù)的行數(shù)

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

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

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

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

2.3 group by 子句

使用前面的聚合函數(shù)，實(shí)際上是把該表中的所有行結(jié)合起來。但還可以使用 group by 來進(jìn)行分組聚合（在 group by 后面加上指定列名，那么該列中值相同的就將分成一組）

接下來我們將對表名為 emp，數(shù)據(jù)如下的表進(jìn)行示例展示

示例1： 查看各個(gè)崗位的平均薪資

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

2.4 having

如果使用 group by 子句進(jìn)行分組以后，需要對分組結(jié)果再進(jìn)行條件過濾，這時(shí)就不能使用 where 語句了，而是使用 having 語句

注意：

• where 語句是在分組之前進(jìn)行篩選

• having 語句是在分組之后進(jìn)行篩選

• where 語句和 having 語句可以同時(shí)使用
  

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

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

3. 聯(lián)合查詢

3.1 介紹

聯(lián)合查詢：是可合并多個(gè)相似的選擇查詢的結(jié)果集。 也就是進(jìn)行多表查詢，其核心思想是使用了笛卡爾積

笛卡爾積思想：

使用笛卡爾積的思想，其實(shí)就是把兩個(gè)表的結(jié)果進(jìn)行一個(gè)排列組合，接下來我們將兩個(gè)表 A、B 通過笛卡爾積的思想得到一個(gè)新的表 C

學(xué)生表 A：

班級表 B：

新表 C：

補(bǔ)充：

• 笛卡爾積得到的結(jié)果任然是一個(gè)表

• 該表的列數(shù)，是兩張表的列數(shù)之和

• 該表的行數(shù)，是兩張表的行數(shù)之積
  

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

新表 D：

此時(shí)我們就可以進(jìn)行一個(gè)多表查詢

注意：

聯(lián)合查詢由于使用了笛卡爾積，那么新表的行數(shù)就是所有表聯(lián)合的乘積。因此使用聯(lián)合查詢結(jié)果的數(shù)據(jù)可能很大，要謹(jǐn)慎使用

以下示例都是通過下面 SQL 語句建的表來進(jìn)行操作學(xué)習(xí)的，如果你想在后面的內(nèi)容進(jìn)行操作，可以直接復(fù)制使用

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 
('計(jì)算機(jī)系2019級1班', '學(xué)習(xí)了計(jì)算機(jī)原理、C和Java語言、數(shù)據(jù)結(jié)構(gòu)和算法'),
('中文系2019級3班','學(xué)習(xí)了中國傳統(tǒng)文學(xué)'),
('自動化2019級5班','學(xué)習(xí)了機(jī)械自動化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風(fēng)李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素貞',null,1),
('00031','許仙','xuxian@qq.com',1),
('00054','不想畢業(yè)',null,1),
('51234','好好說話','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外學(xué)中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中國傳統(tǒng)文化'),('計(jì)算機(jī)原理'),('語文'),('高階數(shù)學(xué)'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋風(fēng)李逵
(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),
-- 不想畢業(yè)
(81, 5, 1),(37, 5, 5),
-- 好好說話
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

3.2 內(nèi)連接

語法：

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

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

補(bǔ)充：

• 使用多表查詢時(shí)，由于有多個(gè)表，所以使用其中的列，方式為：表名.列名

• 可以使用 表名 別名 的方式，將表名起一個(gè)別名使用

• 使用 [inner] join on 方式，如果省略 inner，則默認(rèn)內(nèi)連接
  

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

示例2： 查詢每個(gè)同學(xué)的總成績

示例3： 查詢每個(gè)同學(xué)每門課的成績

3.3 外連接

外連接：分為左外連接和右外連接。如果使用聯(lián)合查詢，左側(cè)的表完全顯示就是用了左外連接；右側(cè)的表完全顯示就是用了右外連接

外連接其實(shí)和內(nèi)連接差不多，都是使用了笛卡爾積。內(nèi)連接是針對的兩個(gè)表中的每一條數(shù)據(jù)都是一一對應(yīng)的，那怎么就不是一一對應(yīng)了呢？例如下面兩個(gè)表 A、B

A 表：

B 表：

我們發(fā)現(xiàn)經(jīng)過笛卡爾積后建立的新表時(shí)，A 表的 id 為3的記錄和 B 表中沒有對應(yīng)的數(shù)據(jù)，B 表中 student_id 為4的記錄和 A 表中也沒有對應(yīng)的數(shù)據(jù)，因此這兩個(gè)表就不能使用內(nèi)連接的方式去查詢，要使用外連接

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

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

補(bǔ)充：

• 當(dāng)兩個(gè)表中的數(shù)據(jù)可以一一對應(yīng)時(shí)，使用外連接和內(nèi)連接是等價(jià)的

• 除了內(nèi)連接、左外連接、右外連接，其實(shí)還有全外連接，但是 MySQL 中不支持全外連接操作
  

語法：

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

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

3.4 自連接

自連接：是指在同一張表中連接自身進(jìn)行查詢，使用自連接其實(shí)可以將”行轉(zhuǎn)換成列“來進(jìn)行操作

為什么自連接可以將行轉(zhuǎn)換成列來進(jìn)行操作呢？假設(shè)有一張表 A

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

通過對自己進(jìn)行笛卡爾積之后，得到新的表 B

此時(shí)我們發(fā)現(xiàn)，如果將原表進(jìn)行笛卡爾積后，有了兩張一樣的表，就可以實(shí)施行與行之間操作

示例： 查詢 Java 成績高于計(jì)算機(jī)原理成績的同學(xué)

3.5 子查詢

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

分類：

• 單行子查詢：返回一行記錄的子查詢

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

補(bǔ)充：

• 用 in 進(jìn)行多行查詢過程： 使用子查詢時(shí)，先執(zhí)行子查詢，將查詢的結(jié)果存放在內(nèi)存里，再執(zhí)行外層查詢，根據(jù)內(nèi)存里的結(jié)果進(jìn)行篩選

• 用 exists 進(jìn)行多行查詢過程： 先執(zhí)行外層循環(huán)，這樣就會得到很多記錄，在針對每行記錄將它帶入到子查詢中，符合條件的就保留（exists 就是檢測子查詢結(jié)果是否為空集合）
  

綜上所述：

基于 in 的寫法，速度快，適合子查詢結(jié)果集合比較小的情況（較大內(nèi)存裝不下）
基于 exists 的寫法，速度慢，適合子查詢結(jié)果集合比較大，且外層查詢結(jié)果數(shù)量比較少的情況

示例1： 查詢不想畢業(yè)同學(xué)的同班同學(xué)（首先要知道不想畢業(yè)同學(xué)的班級，然后通過班級篩選學(xué)生）

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

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

3.6 合并查詢

合并查詢：是使用集合操作符 union 或 union all 來合并多個(gè) select 的執(zhí)行結(jié)果。使用合并查詢時(shí)，前后查詢的結(jié)果集中，字段需要一致

補(bǔ)充：

• union 操作符不會對結(jié)果集的數(shù)據(jù)進(jìn)行去重，union all 會進(jìn)行去重

• 集合操作符的功能其實(shí)和操作符 or 的功能類似，但是如果針對不同的表進(jìn)行查詢，那么 or 就不能使用
  

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

到此，關(guān)于“MySQL數(shù)據(jù)庫聚合查詢和聯(lián)合查詢怎么實(shí)現(xiàn)”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！