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

語法：

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

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

2. 聚合查詢

2.1 介紹

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

2.2 聚合函數(shù)

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

補(bǔ)充：

• 當(dāng)只針對某列進(jìn)行行數(shù)的查詢時，如果某行的值為 null，則查詢結(jié)果不計算這行
• 對數(shù)據(jù)進(jìn)行求和時，數(shù)據(jù)類型必須為數(shù)字，字符串和日期都無法求和
• 如果語法沒有錯誤，但是出現(xiàn)運(yùn)行時錯誤則會報出警告 warnings，此時可以通過 show warnings SQL 語句查看警告信息

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

2.3 group by 子句

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

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

2.4 having

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

注意：

• where 語句是在分組之前進(jìn)行篩選
• having 語句是在分組之后進(jìn)行篩選
• where 語句和 having 語句可以同時使用

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

3. 聯(lián)合查詢

3.1 介紹

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

笛卡爾積思想：

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

學(xué)生表 A：

班級表 B：

新表 C：

補(bǔ)充：

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

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

新表 D：

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

注意：

聯(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ī)系2019級1班', '學(xué)習(xí)了計算機(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ī)原理'),('語文'),('高階數(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ǔ)充：

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

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

3.3 外連接

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

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

A 表：

B 表：

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

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

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

補(bǔ)充：

• 當(dāng)兩個表中的數(shù)據(jù)可以一一對應(yīng)時，使用外連接和內(nèi)連接是等價的
• 除了內(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é)的信息時，就是要對行與行之間進(jìn)行比較，但是一張表是不能進(jìn)行該操作的

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

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

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

3.5 子查詢

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

分類：

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

補(bǔ)充：

• 用 in 進(jìn)行多行查詢過程： 使用子查詢時，先執(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é)生）

3.6 合并查詢

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

補(bǔ)充：

• union 操作符不會對結(jié)果集的數(shù)據(jù)進(jìn)行去重，union all 會進(jìn)行去重
• 集合操作符的功能其實(shí)和操作符 or 的功能類似，但是如果針對不同的表進(jìn)行查詢，那么 or 就不能使用

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

最新評論