目錄

一、表的更新update

二、表的刪除delete

三、聚合函數(shù)

四、group by 分組查詢

---

一、表的更新update

語法：

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

使用實列：

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

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

原先分數(shù)：

更改后分數(shù)：

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

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

?原先分數(shù)：

更改后分數(shù)：

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

對于這個問題，我們可以先拿到總成績倒數(shù)前三的3位同學的總成績和數(shù)學成績：

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

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

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

?

注：如果沒有篩選條件，update將進行整表更新。?

二、表的刪除delete

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

語法：

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

?使用實例：

~??刪除孫悟空同學的考試成績

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

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

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

~ 刪除總分第一名的同學的考試成績

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

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

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

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

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

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

然后插入測試數(shù)據(jù)：

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

?

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

mysql> delete from for_delete;

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

mysql> show create table for_delete\G;

?

我們發(fā)現(xiàn)，auto_increment是4，那么如果我們再插入一條新的數(shù)據(jù)：自增 id 在原值上增長

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

?

查看表結(jié)構(gòu)，會有 AUTO_INCREMENT項：?

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

截斷表

語法：

TRUNCATE [TABLE] table_name；

注：這個操作慎用，其特點如下，

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

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

3、會重置 AUTO_INCREMENT 項。

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

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

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

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

接著，我們截斷整表數(shù)據(jù)，注意影響行數(shù)是 0，所以實際上沒有對數(shù)據(jù)真正操作：

然后，查看一下該表截斷后的創(chuàng)建語句：auto_increment已經(jīng)被清空了。

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

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

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

三、聚合函數(shù)

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

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

使用實例：

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

~ 統(tǒng)計班級共有多少同學

mysql> select count(*) from exam_result;

~?統(tǒng)計數(shù)學成績總分

mysql> select sum(math) from exam_result;

~?統(tǒng)計數(shù)學成績平均分

mysql> select avg(math) from exam_result;

~?返回英語成績的最高分

mysql> select max(english) from exam_result;

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

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

四、group by 分組查詢

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

語法：

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

使用實例

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

~?顯示每個部門的平均工資和最高工資

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

?

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

~?顯示每個部門的每種崗位的平均工資和最低工資

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

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

~?顯示平均工資低于2000的部門和它的平均工資

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

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

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

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

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

having 和 where?

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

where——對具體的任意列進行條件篩選。

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

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