目錄

一、表的更新update

二、表的刪除delete

三、聚合函數(shù)

四、group by 分組查詢

---

一、表的更新update

語(yǔ)法：

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

使用實(shí)列：

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

update exam_result set math=80 where name='孫悟空';

原先分?jǐn)?shù)：

更改后分?jǐn)?shù)：

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

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

?原先分?jǐn)?shù)：

更改后分?jǐn)?shù)：

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

對(duì)于這個(gè)問(wèn)題，我們可以先拿到總成績(jī)倒數(shù)前三的3位同學(xué)的總成績(jī)和數(shù)學(xué)成績(jī)：

select name,math,chinese+math+english total from exam_result order by total limit 3;

上圖顯示出來(lái)的數(shù)據(jù)其實(shí)也是一張表，我們可以對(duì)該表進(jìn)行數(shù)據(jù)修改：

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

?

注：如果沒(méi)有篩選條件，update將進(jìn)行整表更新。?

二、表的刪除delete

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

語(yǔ)法：

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

?使用實(shí)例：

~??刪除孫悟空同學(xué)的考試成績(jī)

mysql> delete from exam_result where name='孫悟空';

刪除前的數(shù)據(jù)：

刪除后數(shù)據(jù)：

~ 刪除總分第一名的同學(xué)的考試成績(jī)

mysql> delete from exam_result order by chinese+math+english desc limit 1;

原先數(shù)據(jù)：總分第一名是豬悟能，所以我們需要?jiǎng)h除他的成績(jī)。

刪除后的數(shù)據(jù)：

?刪除表的所有數(shù)據(jù)

我們先創(chuàng)建一張用于測(cè)試的表：

mysql> 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');

?

然后刪除表的所有數(shù)據(jù)，并查看：

mysql> delete from for_delete;

我們查看一下創(chuàng)建語(yǔ)句：

mysql> show create table for_delete\G;

?

我們發(fā)現(xiàn)，auto_increment是4，那么如果我們?cè)俨迦胍粭l新的數(shù)據(jù)：自增 id 在原值上增長(zhǎng)

INSERT INTO for_delete (name) VALUES ('D');

?

查看表結(jié)構(gòu)，會(huì)有 AUTO_INCREMENT項(xiàng)：?

所以，對(duì)于delete清空表數(shù)據(jù)的方式，其不會(huì)清空AUTO_INCREMENT的值。

截?cái)啾?/p>

語(yǔ)法：

TRUNCATE [TABLE] table_name；

注：這個(gè)操作慎用，其特點(diǎn)如下，

1、只能對(duì)整表操作，不能像 DELETE 一樣可以針對(duì)部分?jǐn)?shù)據(jù)操作，即只能用于清空表的所有的數(shù)據(jù)。

2、實(shí)際上 TRUNCATE 不對(duì)數(shù)據(jù)操作，所以比 DELETE 更快，但是TRUNCATE在刪除數(shù)據(jù)的時(shí)候，并不經(jīng)過(guò)真正的事務(wù)，所以無(wú)法回滾。

3、會(huì)重置 AUTO_INCREMENT 項(xiàng)。

我們先創(chuàng)建一個(gè)測(cè)試表：

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

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

然后，查看一下該表的創(chuàng)建語(yǔ)句：auto_increment是4。

接著，我們截?cái)嗾頂?shù)據(jù)，注意影響行數(shù)是 0，所以實(shí)際上沒(méi)有對(duì)數(shù)據(jù)真正操作：

然后，查看一下該表截?cái)嗪蟮膭?chuàng)建語(yǔ)句：auto_increment已經(jīng)被清空了。

然后，向表中插入一條新的數(shù)據(jù)：

再查看一下該表的創(chuàng)建語(yǔ)句：auto_increment是2。

注：delete和truncate都是對(duì)表中的數(shù)據(jù)進(jìn)行操作。所以數(shù)據(jù)沒(méi)了，但是表任然存在。?

三、聚合函數(shù)

聚合函數(shù)可以對(duì)一組值執(zhí)行計(jì)算并返回單一的值。

函數(shù)	說(shuō)明
COUNT([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的數(shù)量
SUM([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的總和，不是數(shù)字沒(méi)有意義
AVG([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的平均值，不是數(shù)字沒(méi)有意義
MAX([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的最大值，不是數(shù)字沒(méi)有意義
MIN([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的 最小值，不是數(shù)字沒(méi)有意義

使用實(shí)例：

下面的所有操作和結(jié)果均來(lái)源下表：

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

mysql> select count(*) from exam_result;

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

mysql> select sum(math) from exam_result;

~?統(tǒng)計(jì)數(shù)學(xué)成績(jī)平均分

mysql> select avg(math) from exam_result;

~?返回英語(yǔ)成績(jī)的最高分

mysql> select max(english) from exam_result;

~?返回 <?70 分以下的數(shù)學(xué)成績(jī)的最低分

mysql> select min(math) from exam_result where math<70;

四、group by 分組查詢

在select中使用group by 子句可以對(duì)指定列進(jìn)行分組查詢。分組的目的是為了進(jìn)行分組之后，方便進(jìn)行聚合統(tǒng)計(jì)。

語(yǔ)法：

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

使用實(shí)例

首先，創(chuàng)建一個(gè)雇員信息表(來(lái)自O(shè)racle 9i的經(jīng)典測(cè)試表)：EMP員工表，DEPT部門(mén)表，SALGRADE工資等級(jí)表。

~?顯示每個(gè)部門(mén)的平均工資和最高工資

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

?

所以說(shuō)，分組統(tǒng)計(jì)的本質(zhì)，就是把一組按照條件拆成了多個(gè)組，然后進(jìn)行各自組內(nèi)的統(tǒng)計(jì)。即分組就是，把一張表按照條件在邏輯上拆成了多個(gè)子表，然后分別對(duì)各自的子表進(jìn)行聚合統(tǒng)計(jì)。

~?顯示每個(gè)部門(mén)的每種崗位的平均工資和最低工資

首先，我們分析一下需求，我們需要的數(shù)據(jù)是平均工資和最低工資，這個(gè)可以使用函數(shù)avg和min，來(lái)實(shí)現(xiàn)。限制條件就是，我們需要根據(jù)部門(mén)和崗位進(jìn)行分組，可以使用group by。

mysql> select deptno,job,avg(sal),min(sal) from emp group by deptno,job;

~?顯示平均工資低于2000的部門(mén)和它的平均工資

首先，我們分析一下需求，我們需要根據(jù)部門(mén)分組后，得到部門(mén)的平均工資。

mysql> select deptno,avg(sal) from emp group by deptno;

然后，我們需要根據(jù)分組聚合的結(jié)果，進(jìn)行篩選，顯示平均工資低于2000的部門(mén)和它的平均工資。

select deptno,avg(sal) mysal from emp group by deptno having mysal<2000;

注：其中，having是對(duì)聚合統(tǒng)計(jì)后的數(shù)據(jù)，進(jìn)行條件篩選。

having 和 where?

兩者區(qū)別：條件篩選的階段是不同的。

where——對(duì)具體的任意列進(jìn)行條件篩選。

having——對(duì)分組聚合之后的數(shù)據(jù)結(jié)果進(jìn)行條件篩選。

注：SQL查詢中各個(gè)關(guān)鍵字的執(zhí)行先后順序，from > on> join > where > group by > with > having > select > distinct > order by > limit