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

語(yǔ)法：

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

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

2. 聚合查詢(xún)

2.1 介紹

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

2.2 聚合函數(shù)

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

補(bǔ)充：

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

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

2.3 group by 子句

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

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

2.4 having

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

注意：

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

示例1： 查詢(xún)薪資大于10000的崗位

3. 聯(lián)合查詢(xún)

3.1 介紹

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

笛卡爾積思想：

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

學(xué)生表 A：

班級(jí)表 B：

新表 C：

補(bǔ)充：

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

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

新表 D：

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

注意：

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

以下示例都是通過(guò)下面 SQL 語(yǔ)句建的表來(lái)進(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級(jí)1班', '學(xué)習(xí)了計(jì)算機(jī)原理、C和Java語(yǔ)言、數(shù)據(jù)結(jié)構(gòu)和算法'),
('中文系2019級(jí)3班','學(xué)習(xí)了中國(guó)傳統(tǒng)文學(xué)'),
('自動(dòng)化2019級(jí)5班','學(xué)習(xí)了機(jī)械自動(dòng)化');

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','好好說(shuō)話(huà)','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外學(xué)中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中國(guó)傳統(tǒng)文化'),('計(jì)算機(jī)原理'),('語(yǔ)文'),('高階數(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),
-- 好好說(shuō)話(huà)
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

3.2 內(nèi)連接

語(yǔ)法：

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

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

補(bǔ)充：

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

示例1： 查詢(xún)?cè)S仙同學(xué)每門(mén)課的成績(jī)

3.3 外連接

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

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

A 表：

B 表：

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

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

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

補(bǔ)充：

• 當(dāng)兩個(gè)表中的數(shù)據(jù)可以一一對(duì)應(yīng)時(shí)，使用外連接和內(nèi)連接是等價(jià)的
• 除了內(nèi)連接、左外連接、右外連接，其實(shí)還有全外連接，但是 MySQL 中不支持全外連接操作

語(yǔ)法：

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

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

3.4 自連接

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

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

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

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

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

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

3.5 子查詢(xún)

子查詢(xún)：是指嵌入在其它 SQL 語(yǔ)句中的 select 語(yǔ)句，也叫嵌套查詢(xún)

分類(lèi)：

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

補(bǔ)充：

• 用 in 進(jìn)行多行查詢(xún)過(guò)程： 使用子查詢(xún)時(shí)，先執(zhí)行子查詢(xún)，將查詢(xún)的結(jié)果存放在內(nèi)存里，再執(zhí)行外層查詢(xún)，根據(jù)內(nèi)存里的結(jié)果進(jìn)行篩選
• 用 exists 進(jìn)行多行查詢(xún)過(guò)程： 先執(zhí)行外層循環(huán)，這樣就會(huì)得到很多記錄，在針對(duì)每行記錄將它帶入到子查詢(xún)中，符合條件的就保留（exists 就是檢測(cè)子查詢(xún)結(jié)果是否為空集合）

綜上所述：

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

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

3.6 合并查詢(xún)

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

補(bǔ)充：

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

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

最新評(píng)論