「前言」文章內(nèi)容大致是對(duì)MySQL表內(nèi)容的基本操作，即增刪查改。

「歸屬專欄」MySQL

「主頁(yè)鏈接」個(gè)人主頁(yè)

「筆者」楓葉先生(fy)

一、MySQL表內(nèi)容的增刪查改

• 表內(nèi)容的增刪查改簡(jiǎn)稱CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（刪除）
• DML【data manipulation language】數(shù)據(jù)操縱語(yǔ)言，用來(lái)對(duì)數(shù)據(jù)進(jìn)行操作代表指令：insert，delete、update
• DML中又單獨(dú)分了一個(gè)DQL【Data Query Language】，數(shù)據(jù)查詢語(yǔ)言，代表指令：select

1.1 Create

create是用于新增數(shù)據(jù)，新增數(shù)據(jù)的SQL語(yǔ)法如下：

INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ...

• 大寫的表示關(guān)鍵字，[] 是可選項(xiàng)，可以選擇不寫
• table_name是表的名字
• column列，用于指定每個(gè)value_list中的值應(yīng)該插入到表中的哪一列
• value_lis的值與column一一對(duì)應(yīng)

語(yǔ)法到下面再一一解釋

1.1.1 單行數(shù)據(jù)+全列插入

先創(chuàng)建一個(gè)學(xué)生表，表當(dāng)中包含自增長(zhǎng)的主鍵id、學(xué)號(hào)、姓名和QQ號(hào)。

mysql> create table if not exists student(
    -> id int unsigned primary key auto_increment,
    -> stu_id int unsigned not null unique comment '學(xué)號(hào)',
    -> name varchar(20) not null,
    -> qq varchar(20)
    -> );

面使用insert語(yǔ)句向?qū)W生表中插入記錄，每次向表中插入一條記錄，并且插入記錄時(shí)不指定column列，表示按照表中默認(rèn)的列順序進(jìn)行全列插入，因此插入的每條記錄中的列值需要按表列順序依次列出（全列插入）

-- 全列插入
mysql> insert into student values (1, 10001, '張三', 222222);
mysql> insert into student values (2, 10002, '李四', 222223);

注意：value_list數(shù)量必須和定義表的列的數(shù)量及順序一致

注：into也可以省略，不過(guò)為了符號(hào)插入語(yǔ)義，一般都寫出來(lái)。

1.1.2 多行數(shù)據(jù)+指定列插入

插入的時(shí)候，也可以不用指定id(這時(shí)候就需要明確插入數(shù)據(jù)到那些列了，即指定列插入)，對(duì)于表中的ID來(lái)說(shuō)，mysql會(huì)使用默認(rèn)的值進(jìn)行自增

insert into student (stu_id, name, qq) values (10003, '王五', 222224);

insert語(yǔ)句也可以一次向表中插入多條記錄，插入的多條記錄之間使用逗號(hào)隔開，并且插入記錄時(shí)可以只指定某些列進(jìn)行插入。

mysql> insert into student (stu_id, name, qq) values (10004, '趙六', null), (10005, '田七', null);

注意：不允許為空一列，必須插入值，否則報(bào)錯(cuò)。

1.1.3 插入否則更新

向表中插入記錄時(shí)，如果待插入記錄中的主鍵或唯一鍵已經(jīng)存在，那么就會(huì)因?yàn)橹麈I沖突或唯一鍵沖突導(dǎo)致插入失敗。

主鍵沖突

唯一鍵沖突

這時(shí)可以選擇性的進(jìn)行同步更新操作，語(yǔ)法：

INSERT ... ON DUPLICATE UPDATE column1=value1 [, column2=value2] ...;

注：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• ON DUPLICATE KEY 當(dāng)發(fā)生重復(fù)key的時(shí)候，就執(zhí)行后面的語(yǔ)句
• UPDATE后面的column=value，表示當(dāng)插入記錄出現(xiàn)沖突時(shí)需要更新的列值

規(guī)則：

• 如果表中沒(méi)有沖突數(shù)據(jù)，則直接插入數(shù)據(jù)
• 如果表中有沖突數(shù)據(jù)，則將表中的數(shù)據(jù)進(jìn)行更新

例如，插入的值主鍵發(fā)生沖突，則將表中沖突的列進(jìn)行更新

mysql> insert into student (id, stu_id, name) values (1, 10001, '孫悟空')
    -> on duplicate key update stu_id = 10011, name = '孫悟空';
Query OK, 2 rows affected (0.00 sec)

執(zhí)行插入否則更新的語(yǔ)句，可以通過(guò)受影響的數(shù)據(jù)行數(shù)來(lái)判斷本次數(shù)據(jù)的插入情況：

• 0 rows affected：表中有沖突數(shù)據(jù)，但沖突數(shù)據(jù)的值和指定更新的值相同
• 1 row affected：表中沒(méi)有沖突數(shù)據(jù)，數(shù)據(jù)直接被插入
• 2 rows affected：表中有沖突數(shù)據(jù)，并且數(shù)據(jù)已經(jīng)被更新

也可以通過(guò) MySQL 函數(shù)獲取受到影響的數(shù)據(jù)行數(shù)

SELECT ROW_COUNT();

1.1.4 數(shù)據(jù)替換

• 如果表中沒(méi)有沖突數(shù)據(jù)，則直接插入數(shù)據(jù)
• 如果表中有沖突數(shù)據(jù)，則先將表中的沖突數(shù)據(jù)刪除，然后再插入數(shù)據(jù)

語(yǔ)法：只需要在插入數(shù)據(jù)時(shí)將SQL語(yǔ)句中的INSERT改為REPLACE即可，其他相同

例如：主鍵或者唯一鍵如果沖突，則刪除后再插入

mysql> replace into student (stu_id, name) values (10002, '唐三藏');
Query OK, 2 rows affected (0.00 sec)

執(zhí)行該語(yǔ)句后，也可以通過(guò)受影響的數(shù)據(jù)行數(shù)來(lái)判斷本次數(shù)據(jù)的插入情況：

• 1 row affected：表中沒(méi)有沖突數(shù)據(jù)，數(shù)據(jù)直接被插入
• 2 rows affected：表中有沖突數(shù)據(jù)，沖突數(shù)據(jù)被刪除后重新插入

1.2 Retrieve

查找數(shù)據(jù)的SQL語(yǔ)法如下：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• { }中的 | 代表可以選擇左側(cè)的語(yǔ)句或右側(cè)的語(yǔ)句

接下來(lái)創(chuàng)建表結(jié)構(gòu)，用于測(cè)試

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同學(xué)姓名',
chinese float DEFAULT 0.0 COMMENT '語(yǔ)文成績(jī)',
math float DEFAULT 0.0 COMMENT '數(shù)學(xué)成績(jī)',
english float DEFAULT 0.0 COMMENT '英語(yǔ)成績(jī)'
);

插入測(cè)試數(shù)據(jù)

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孫悟空', 87, 78, 77),
('豬悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('劉玄德', 55, 85, 45),
('孫權(quán)', 70, 73, 78),
('宋公明', 75, 65, 30);

1.2.1 SELECT列

全列查詢

查詢數(shù)據(jù)時(shí)直接用*代替column列表，表示進(jìn)行全列查詢，這時(shí)將會(huì)顯示被篩選出來(lái)的記錄的所有列信息

mysql> select * from exam_result;

注意：通常情況下不建議使用*進(jìn)行全列查詢，查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大，可能會(huì)影響到索引的使用

指定列查詢

在查詢數(shù)據(jù)時(shí)也可以只對(duì)指定的列進(jìn)行查詢，這時(shí)將需要查詢的列在column列表列出即可

注意：指定列的順序不需要按定義表的順序來(lái)

mysql> select id, name from student;

查詢字段為表達(dá)式

select不僅能夠用來(lái)查詢數(shù)據(jù)，還可以用來(lái)計(jì)算某些表達(dá)式或執(zhí)行某些函數(shù)

在查詢數(shù)據(jù)時(shí)，column列表中除了能羅列表中存在的列名外，也可以將表達(dá)式羅列到column列表中

mysql> select id, name, math, 10+20 from exam_result;

如果將表達(dá)式添加到column列表，那么每當(dāng)一條記錄被篩選出來(lái)時(shí)就會(huì)執(zhí)行這個(gè)表達(dá)式，然后將表達(dá)式的計(jì)算結(jié)果作為這條記錄的一個(gè)列值進(jìn)行顯示

注意：存儲(chǔ)的數(shù)據(jù)依舊沒(méi)有發(fā)生改變

column列表中的表達(dá)式中也可以包含多個(gè)表中已有的字段

為查詢結(jié)果指定別名

語(yǔ)法：

SELECT column [AS] alias_name [...] FROM table_name;

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)

查詢結(jié)果列名太長(zhǎng)了，可以對(duì)列名進(jìn)行重命名

進(jìn)行對(duì)結(jié)果重命名

mysql> select id, name, math+chinese+english as 總分 from exam_result;

as也可以省略

mysql> select id, name, math+chinese+english 總分 from exam_result;

對(duì)查詢結(jié)果去重

如果想要對(duì)查詢結(jié)果進(jìn)行去重操作，可以在SQL中的select后面帶上distinct關(guān)鍵字

mysql> select distinct math from exam_result;

1.2.2 SELECT查詢加WHERE條件

• 如果在查詢數(shù)據(jù)時(shí)沒(méi)有指定where子句，那么會(huì)直接將表中某一列所有的記錄都顯示出來(lái)
• 如果在查詢數(shù)據(jù)時(shí)指定了where子句，那么在查詢數(shù)據(jù)時(shí)會(huì)先根據(jù)where子句篩選出符合條件的記錄，查詢結(jié)果只會(huì)顯示符合條件的記錄

where子句中可以指明一個(gè)或多個(gè)篩選條件，各個(gè)篩選條件之間用邏輯運(yùn)算符AND或OR進(jìn)行關(guān)聯(lián)，下面給出了where子句中常用的比較運(yùn)算符和邏輯運(yùn)算符。

比較運(yùn)算符

邏輯運(yùn)算符

注意：MySQL里面比較相等使用的是一個(gè)=，不使用兩個(gè)等號(hào)，與C/C++里面的不一樣

測(cè)試案例

英語(yǔ)不及格的同學(xué)及英語(yǔ)成績(jī) ( < 60 )

在where子句中指明篩選條件為英語(yǔ)成績(jī)小于60

mysql> select id, name, english from exam_result where english<60;

語(yǔ)文成績(jī)?cè)?[80, 90] 分的同學(xué)及語(yǔ)文成績(jī)

在where子句中指明篩選條件為語(yǔ)文成績(jī)大于等于80并且小于等于90，使用and進(jìn)行并列條件

mysql> select id, name, chinese from exam_result where chinese >= 80 and chinese <= 90;

此外，這里也可以使用BETWEEN a0 AND a1來(lái)指明語(yǔ)文成績(jī)的的所在區(qū)間

mysql> mysql> select id, name, chinese from exam_result where chinese between 80 and 90;

數(shù)學(xué)成績(jī)是 58 或者 59 或者 98 或者 99 分的同學(xué)及數(shù)學(xué)成績(jī)

在where子句中使用 or 進(jìn)行條件連接

mysql> select id, name, math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

此外，也可以通過(guò)IN (option, ...)的方式來(lái)判斷數(shù)學(xué)成績(jī)是否符合篩選要求

mysql> select id, name, math from exam_result where math in (58, 59, 98, 99);

姓孫的同學(xué)

通過(guò)模糊匹配來(lái)判斷當(dāng)前同學(xué)是否姓孫（需要用到%來(lái)匹配任意多個(gè)字符），使用到關(guān)鍵字like

mysql> select id, name from exam_result where name like '孫%';

孫某同學(xué)

通過(guò)模糊匹配來(lái)判斷當(dāng)前同學(xué)是否為孫某（需要用到_來(lái)嚴(yán)格匹配任意單個(gè)字符），使用到關(guān)鍵字like

mysql> select id, name from exam_result where name like '孫_';

語(yǔ)文成績(jī)好于英語(yǔ)成績(jī)的同學(xué)

按照條件使用where子句查詢即可

mysql> select id, name, chinese, english from exam_result where chinese > english;

總分在 200 分以下的同學(xué)

在select的column列表中添加表達(dá)式查詢，查詢的表達(dá)式為語(yǔ)文、數(shù)學(xué)和英語(yǔ)成績(jī)之和，為了方便觀察可以將表達(dá)式對(duì)應(yīng)的列指定別名為“總分”，在where子句中指明篩選條件為三科成績(jī)之和小于200

mysql> select id, name, chinese+english+math as 總分 from exam_result where chinese+english+math < 200;

需要注意的是，在where子句中不能使用select中指定的別名：

• 查詢數(shù)據(jù)時(shí)是先根據(jù)where子句篩選出符合條件的記錄。
• 然后再將符合條件的記錄作為數(shù)據(jù)源來(lái)再依次執(zhí)行select語(yǔ)句。

也就說(shuō)說(shuō)語(yǔ)句的執(zhí)行順序是where子句先執(zhí)行，在執(zhí)行select語(yǔ)句

所以在where子句中不能使用別名select的別名，如果在where子句中使用別名，那么在查詢數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)

語(yǔ)文成績(jī) > 80 并且不姓孫的同學(xué)

where子句要使用AND與NOT的關(guān)鍵字和通過(guò)模糊匹配like

mysql> select id, name, chinese from exam_result where chinese > 80 and name not like '孫%';

孫某同學(xué)，否則要求總成績(jī) > 200 并且 語(yǔ)文成績(jī) < 數(shù)學(xué)成績(jī) 并且 英語(yǔ)成績(jī) > 80

mysql> select id, name, chinese+english+math as 總分 from exam_result where name like '孫_' or (
    -> chinese+math+english >  200 and chinese < math and english > 80);

NULL的查詢

使用的測(cè)試表是上面的student表

查詢qq號(hào)已知的同學(xué)姓名

mysql> select name, qq from student where qq is not null;

查詢QQ號(hào)未知的同學(xué)

mysql> select name, qq from student where qq is null;

需要注意的是，在與NULL值作比較的時(shí)候應(yīng)該使用<=>運(yùn)算符，使用=運(yùn)算符無(wú)法得到正確的查詢結(jié)果，不過(guò)都不怎么使用<=>運(yùn)算符，判斷為空或者不為空常使用is null或is not null

=運(yùn)算符是NULL不安全的，使用=運(yùn)算符將任何值與NULL作比較，得到的結(jié)果都是NULL

<=>運(yùn)算符是NULL安全的，使用<=>運(yùn)算符將NULL和NULL作比較得到的結(jié)果為TRUE(1)，將非NULL值與NULL作比較得到的結(jié)果為FALSE(0)

1.2.3 對(duì)查詢結(jié)果排序

排序語(yǔ)法：

-- ASC 為升序（從小到大）
-- DESC 為降序（從大到?。?/span>
-- 默認(rèn)為 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)

注意：沒(méi)有ORDER BY子句的查詢，返回的順序是未定義的，永遠(yuǎn)不要依賴這個(gè)順序

查詢所有同學(xué)及數(shù)學(xué)成績(jī)，按數(shù)學(xué)成績(jī)升序顯示

下面使用的測(cè)試表是上面的exam_result表

mysql> select name, math from exam_result order by math;

查詢所有同學(xué)及 qq 號(hào)，按 qq 號(hào)排序按升序顯示

使用的測(cè)試表是上面的student表

mysql> select name, qq from student order by qq asc;

注意： NULL值視為比任何值都小，因此排升序時(shí)出現(xiàn)在最上面。

查詢所有同學(xué)及 qq 號(hào)，按 qq 號(hào)排序按降序顯示

mysql> select name, qq from student order by qq desc;

注意： NULL值視為比任何值都小，因此降序時(shí)出現(xiàn)在最下面。

查詢同學(xué)各門成績(jī)，依次按 數(shù)學(xué)降序，英語(yǔ)升序，語(yǔ)文升序的方式顯示

mysql> select name, math, english, chinese from exam_result order by math desc, english asc, chinese asc;

這里說(shuō)明一下，首先排序的是數(shù)學(xué)，數(shù)學(xué)成績(jī)進(jìn)行降序排序的，只有滿足了數(shù)學(xué)降序，然后才到英語(yǔ)升序排序

比如，當(dāng)兩條記錄的數(shù)學(xué)成績(jī)相同時(shí)就會(huì)按照英語(yǔ)成績(jī)進(jìn)行排序，如果這兩條記錄的英語(yǔ)成績(jī)也相同就會(huì)繼續(xù)按照語(yǔ)文成績(jī)進(jìn)行排序，以此類推

查詢同學(xué)及總分，由高到低

mysql> select name, math+english+chinese from exam_result order by math+english+chinese desc;

需要注意的是，在order by子句中可以使用select中指定的別名：

• 查詢數(shù)據(jù)時(shí)是先根據(jù)where子句篩選出符合條件的記錄（如果有where子句）
• 然后再將符合條件的記錄作為數(shù)據(jù)源來(lái)依次執(zhí)行select語(yǔ)句
• 最后再通過(guò)order by子句對(duì)select語(yǔ)句的執(zhí)行結(jié)果進(jìn)行排序

也就是說(shuō)，order by子句的執(zhí)行是在select語(yǔ)句之后的，所以在order by子句中可以使用別名

mysql> select name, math+english+chinese as 總分 from exam_result order by 總分 desc;

查詢姓孫的同學(xué)或者姓曹的同學(xué)數(shù)學(xué)成績(jī)，結(jié)果按數(shù)學(xué)成績(jī)由高到低顯示

mysql> select name, math from exam_result where name like '孫%' or name like '曹%' order by math desc;

1.2.4 篩選分頁(yè)結(jié)果

語(yǔ)法如下：

-- 起始下標(biāo)為 0

-- 從 s 開始，篩選 n 條結(jié)果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n；

-- 從 0 開始，篩選 n 條結(jié)果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 從 s 開始，篩選 n 條結(jié)果，比第二種用法更明確，建議使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• 查詢SQL中各語(yǔ)句的執(zhí)行順序?yàn)椋?code>where、select、order by、limit
• limit子句在篩選記錄時(shí)，不加限制，記錄的下標(biāo)從0開始

注意：對(duì)未知表進(jìn)行查詢時(shí)，最好加一條 LIMIT 1，避免因?yàn)楸碇袛?shù)據(jù)過(guò)大，查詢?nèi)頂?shù)據(jù)導(dǎo)致數(shù)據(jù)庫(kù)卡死

按 id 進(jìn)行分頁(yè)，每頁(yè) 3 條記錄，分別顯示 第 1、2、3 頁(yè)

mysql> select id, name, math, chinese, english from exam_result
    -> order by id limit 3 offset 0;

從第3條記錄開始，向后篩選出3條記錄

mysql> select id, name, math, chinese, english from exam_result order by id limit 3 offset 3;

從第6條記錄開始，向后篩選出3條記錄（如果結(jié)果不足 3 個(gè)，不會(huì)有影響）

select id, name, math, chinese, english from exam_result order by id limit 3 offset 6;

1.3 Update

修改表中數(shù)據(jù)語(yǔ)法如下：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• column=expr，表示將記錄中列名為column的值修改為expr
• 在修改數(shù)據(jù)之前需要先找到待修改的記錄，update語(yǔ)句中的where、order by和limit就是用來(lái)定位數(shù)據(jù)的，不進(jìn)行條件限制，表中一列數(shù)據(jù)都會(huì)被修改
• 所以要慎用該命令

將孫悟空同學(xué)的數(shù)學(xué)成績(jī)變更為 80 分

先查看原數(shù)據(jù)，再進(jìn)行修改，最后再查詢是否已修改

mysql> update exam_result set math = 80 where name = '孫悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

將曹孟德同學(xué)的數(shù)學(xué)成績(jī)變更為 60 分，語(yǔ)文成績(jī)變更為 70 分

也是如此，先查看原數(shù)據(jù)，再進(jìn)行修改，最后再查詢是否已修改

mysql> update exam_result set math = 60, chinese = 70 where name = '曹孟德';

將總成績(jī)倒數(shù)前三的 3 位同學(xué)的數(shù)學(xué)成績(jī)加上 30 分

先查看原數(shù)據(jù)

mysql> select name, math, math+chinese+english as 總分 from exam_result order by 總分 asc limit 3;

再進(jìn)行修改，在update語(yǔ)句中指明要將篩選出來(lái)的記錄的數(shù)學(xué)成績(jī)加上30分，最后再查詢是否已修改

mysql> update exam_result set math = math+30 order by math+chinese+english asc limit 3;

需要注意的是，MySQL中不支持+=這種復(fù)合賦值運(yùn)算符，此外，這里在查看更新后的數(shù)據(jù)時(shí)不能查看總成績(jī)倒數(shù)前三的3位同學(xué)，因?yàn)橹翱偝煽?jī)倒數(shù)前三的3位同學(xué)，數(shù)學(xué)成績(jī)加上30分后可能就不再是倒數(shù)前三了

mysql> select name, math, math+chinese+english as 總分 from exam_result where name in('宋公明', '劉玄德', '曹孟德');

將所有同學(xué)的語(yǔ)文成績(jī)更新為原來(lái)的 2 倍

查看原始數(shù)據(jù)

在update語(yǔ)句中指明要將篩選出來(lái)的記錄的語(yǔ)文成績(jī)變?yōu)樵瓉?lái)的2倍，并在修改后再次查看數(shù)據(jù)確保數(shù)據(jù)成功被修改

mysql> update exam_result set chinese = chinese*2;

注意：更新全表的語(yǔ)句慎用，沒(méi)有條件限制，則會(huì)更新全表

1.4 Delete

1.4.1 刪除數(shù)據(jù)

刪除數(shù)據(jù)語(yǔ)法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• 在刪除數(shù)據(jù)之前需要先找到待刪除的記錄，delete語(yǔ)句中的where、order by和limit就是用來(lái)定位數(shù)據(jù)的

刪除孫悟空同學(xué)的考試成

先查看原數(shù)據(jù)，再刪除數(shù)據(jù)，再查看數(shù)據(jù)是否存在

刪除整張表數(shù)據(jù)

創(chuàng)建測(cè)試表

CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

插入測(cè)試數(shù)據(jù)

INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

在delete語(yǔ)句中只指明要?jiǎng)h除數(shù)據(jù)的表名，而不通過(guò)where、order by和limit指明篩選條件，這時(shí)將會(huì)刪除整張表的數(shù)據(jù)。

mysql> delete from for_delete;

再向表中插入一些數(shù)據(jù)，在插入數(shù)據(jù)時(shí)不指明自增長(zhǎng)字段的值，這時(shí)會(huì)發(fā)現(xiàn)插入數(shù)據(jù)對(duì)應(yīng)的自增長(zhǎng)id值是在之前的基礎(chǔ)上繼續(xù)增長(zhǎng)的

查看創(chuàng)建表時(shí)的相關(guān)信息時(shí)可以看到，有一個(gè)AUTO_INCREMENT=n的字段，當(dāng)通過(guò)delete語(yǔ)句刪除整表數(shù)據(jù)時(shí)，不會(huì)重置AUTO_INCREMENT=n字段，因此刪除整表數(shù)據(jù)后插入數(shù)據(jù)對(duì)應(yīng)的自增長(zhǎng)id值會(huì)在原來(lái)的基礎(chǔ)上繼續(xù)增長(zhǎng)。

注意： 刪除整表操作要慎用！

1.4.2 截?cái)啾?/h4>

截?cái)啾碚Z(yǔ)法如下：

TRUNCATE [TABLE] table_name

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• truncate只能對(duì)整表操作，不能像delete一樣針對(duì)部分?jǐn)?shù)據(jù)操作
• truncate實(shí)際上不對(duì)數(shù)據(jù)操作，所以比delete更快
• 但是truncate在刪除數(shù)據(jù)的時(shí)候，并不經(jīng)過(guò)真正的事物，所以無(wú)法回滾
• truncate會(huì)重置AUTO_INCREMENT=n字段

創(chuàng)建一張測(cè)試表

CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)

-- 插入測(cè)試數(shù)據(jù)
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
);

在truncate語(yǔ)句中只指明要?jiǎng)h除數(shù)據(jù)的表名，這時(shí)便會(huì)刪除整張表的數(shù)據(jù)，但由于truncate實(shí)際不對(duì)數(shù)據(jù)操作，因此執(zhí)行truncate語(yǔ)句后看到影響行數(shù)為0

mysql> truncate table for_truncate;
Query OK, 0 rows affected (0.01 sec)

再向表中插入一些數(shù)據(jù)，在插入數(shù)據(jù)時(shí)不指明自增長(zhǎng)字段的值，這時(shí)會(huì)發(fā)現(xiàn)插入數(shù)據(jù)對(duì)應(yīng)的自增長(zhǎng)id值是重新從1開始增長(zhǎng)的

注意： 截?cái)啾聿僮饕饔?/p>

1.5 插入查詢結(jié)果

語(yǔ)法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

說(shuō)明：

• 大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• 該SQL的作用是將篩選出來(lái)的記錄插入到指定的表當(dāng)中

刪除表中的的重復(fù)復(fù)記錄，重復(fù)的數(shù)據(jù)只能有一份

-- 創(chuàng)建原數(shù)據(jù)表
CREATE TABLE duplicate_table (id int, name varchar(20));
-- 插入測(cè)試數(shù)據(jù)
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

思路：

• 創(chuàng)建一張臨時(shí)表，該表的結(jié)構(gòu)與測(cè)試表的結(jié)構(gòu)相同。
• 以去重的方式查詢測(cè)試表中的數(shù)據(jù)，并將查詢結(jié)果插入到臨時(shí)表中。
• 將測(cè)試表重命名為其他名字，再將臨時(shí)表重命名為測(cè)試表的名字，實(shí)現(xiàn)原子去重操作。

-- 創(chuàng)建一張空表 no_duplicate_table，結(jié)構(gòu)和 duplicate_table 一樣
mysql> create table no_duplicate_table like duplicate_table;

-- 將 duplicate_table 的去重?cái)?shù)據(jù)插入到 no_duplicate_table
mysql> insert into no_duplicate_table select distinct * from duplicate_table;

-- 通過(guò)重命名表，實(shí)現(xiàn)原子的去重操作
mysql> rename table duplicate_table to old_duplicate_table, 
    -> no_duplicate_table to duplicate_table;

臨時(shí)表的結(jié)構(gòu)與測(cè)試表相同，因此在創(chuàng)建臨時(shí)表的時(shí)候可以借助like進(jìn)行創(chuàng)建

通過(guò)插入查詢語(yǔ)句將去重查詢后的結(jié)果插入到臨時(shí)表中，由于臨時(shí)表和測(cè)試表的結(jié)構(gòu)相同，并且select進(jìn)行的是全列查詢，因此在插入時(shí)不用在表名后指明column列表

將測(cè)試表重命名為其他名字（相當(dāng)于對(duì)去重前的數(shù)據(jù)進(jìn)行備份），將臨時(shí)表重命名為測(cè)試表的名字，這時(shí)便完成了表中數(shù)據(jù)的去重操作

1.6 聚合函數(shù)

聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算并返回單一的值，常用的聚合函數(shù)如下：

注意：聚合函數(shù)可以在select語(yǔ)句中使用，此時(shí)select每處理一條記錄時(shí)都會(huì)將對(duì)應(yīng)的參數(shù)傳遞給這些聚合函數(shù)

統(tǒng)計(jì)班級(jí)共有多少同學(xué)

這里用之前的student表來(lái)進(jìn)行演示

使用*做統(tǒng)計(jì)，不受 NULL 影響，將*作為參數(shù)傳遞給count函數(shù)，這時(shí)便能統(tǒng)計(jì)出表中的記錄條數(shù)

mysql> select count(*) from student;

也可以使用表達(dá)式做統(tǒng)計(jì)，使用count函數(shù)，并將表達(dá)式作為參數(shù)傳遞給count函數(shù)，這時(shí)也可以統(tǒng)計(jì)出表中的記錄條數(shù)

mysql> select count(1) from student;

這種寫法相當(dāng)于在查詢表中數(shù)據(jù)時(shí)，自行新增了一列列名為特定表達(dá)式的列，我們就是在用count函數(shù)統(tǒng)計(jì)該列中有多少個(gè)數(shù)據(jù)，等價(jià)于統(tǒng)計(jì)表中有多少條記錄

統(tǒng)計(jì)班級(jí)收集的 qq 號(hào)有多少

使用count函數(shù)統(tǒng)計(jì)qq列中數(shù)據(jù)的個(gè)數(shù)，NULL不會(huì)計(jì)入結(jié)果

mysql> select count(qq) from student;

統(tǒng)計(jì)本次考試的數(shù)學(xué)成績(jī)分?jǐn)?shù)個(gè)數(shù)

這里用之前的exam_result表來(lái)進(jìn)行演示

先使用count函數(shù)統(tǒng)計(jì)math列中數(shù)據(jù)的個(gè)數(shù)（包含重復(fù)的）

使用count函數(shù)時(shí)（包括其他聚合函數(shù)），在傳遞的參數(shù)之前加上distinct，這時(shí)便能統(tǒng)計(jì)出表中數(shù)學(xué)成績(jī)?nèi)ブ睾蟮膫€(gè)數(shù)。

mysql> select count(distinct math) from exam_result;

統(tǒng)計(jì)數(shù)學(xué)成績(jī)總分

可以使用sum函數(shù)統(tǒng)計(jì)math列中數(shù)據(jù)的總和

mysql> select sum(math) from exam_result;

統(tǒng)計(jì)不及格的數(shù)學(xué)成績(jī)總分

使用where子句中指明篩選條件為數(shù)學(xué)成績(jī)小于60分

mysql> select sum(math) from exam_result where math < 60;

注意：如果沒(méi)有結(jié)果，返回 NULL

統(tǒng)計(jì)平均總分

可以使用avg函數(shù)計(jì)算總分的平均值

mysql> select avg(chinese+math+english) 平均總分 from exam_result;

返回英語(yǔ)最高分

可以使用max函數(shù)查詢英語(yǔ)成績(jī)最高分

mysql> select max(english) as maxEnglish from exam_result;

返回 > 70 分以上的數(shù)學(xué)最低分

使用where子句中指明篩選條件為英語(yǔ)成績(jī)大于70分，在select語(yǔ)句中使用min函數(shù)查詢英語(yǔ)成績(jī)最低分

mysql> select min(english) as minEnglish from exam_result where english > 70;

1.7 group by子句的使用（分組查詢）

在select中使用group by 子句可以對(duì)指定列進(jìn)行分組查詢，語(yǔ)法：

select column1, column2, .. from table group by column;

說(shuō)明：

• SQL中大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)
• 查詢SQL中各語(yǔ)句的執(zhí)行順序?yàn)椋?/strong>where、group by、select、order by、limit
• group by后面的列名，表示按照指定列進(jìn)行分組查詢

source SQL文件路徑
-- 例如
mysql> source /home/fy/mysql/scott_data.sql

mysql> select deptno, avg(sal) as 平均工資, max(sal) as 最高工資 from emp group by deptno;

mysql> select deptno, job, avg(sal) 平均工資, min(sal) 最低工資 from emp group by deptno, job;

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] ...;

mysql> select deptno, avg(sal) 平均工資 from emp group by deptno;

mysql> select deptno, avg(sal) 平均工資 from emp group by deptno having 平均工資 < 2000;

「 作者 」 楓葉先生
「 更新 」 2023.8.18
「 聲明 」 余之才疏學(xué)淺，故所撰文疏漏難免，
          或有謬誤或不準(zhǔn)確之處，敬請(qǐng)讀者批評(píng)指正。