欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL表的增刪查改及聚合函數(shù)/group?by子句的使用方法舉例

 更新時間：2024年01月20日 11:55:36   作者：椿融雪  
這篇文章主要給大家介紹了關(guān)于MySQL表的增刪查改及聚合函數(shù)/group?by子句的使用方法,在MySQL中可以使用聚合函數(shù)與GROUP BY語句可以對數(shù)據(jù)進行分組并進行聚合計算,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

一、創(chuàng)建–Create

語法：

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

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

-- 創(chuàng)建一張學(xué)生表
CREATE TABLE students (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	sn INT NOT NULL UNIQUE COMMENT '學(xué)號',
	name VARCHAR(20) NOT NULL,
	qq VARCHAR(20)
);

插入兩條記錄，value_list 數(shù)量必須和定義表的列的數(shù)量及順序一致

注意，這里在插入的時候，也可以不用指定id(當(dāng)然，那時候就需要明確插入數(shù)據(jù)到那些列了)，那么mysql會使用默認(rèn)的值進行自增。

INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO students VALUES (101, 10001, '孫悟空', '11111');

查看插入結(jié)果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)

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

插入兩條記錄，value_list 數(shù)量必須和指定列數(shù)量及順序一致

INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孫仲謀');

-- 查看插入結(jié)果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
| 102 | 20001 | 曹孟德 | NULL |
| 103 | 20002 | 孫仲謀 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

3.插入否則更新

由于 主鍵 或者 唯一鍵 對應(yīng)的值已經(jīng)存在而導(dǎo)致插入失敗

-- 主鍵沖突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大師');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

-- 唯一鍵沖突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞞');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

可以選擇性的進行同步更新操作語法

INSERT ... ON DUPLICATE KEY UPDATE
	column = value [, column = value] ...

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大師')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大師';
Query OK, 2 rows affected (0.47 sec)

– 0 row affected: 表中有沖突數(shù)據(jù)，但沖突數(shù)據(jù)的值和 update 的值相等

– 1 row affected: 表中沒有沖突數(shù)據(jù)，數(shù)據(jù)被插入

– 2 row affected: 表中有沖突數(shù)據(jù)，并且數(shù)據(jù)已經(jīng)被更新

通過 MySQL 函數(shù)獲取受到影響的數(shù)據(jù)行數(shù)

SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
-- ON DUPLICATE KEY 當(dāng)發(fā)生重復(fù)key的時候

4.替換 – replace

– 主鍵 或者 唯一鍵 沒有沖突，則直接插入；

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

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞞');

– 1 row affected: 表中沒有沖突數(shù)據(jù)，數(shù)據(jù)被插入

– 2 row affected: 表中有沖突數(shù)據(jù)，刪除后重新插入

二、讀取–Retrieve

語法：

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

案例：

-- 創(chuàng)建表結(jié)構(gòu)
CREATE TABLE exam_result (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL COMMENT '同學(xué)姓名',
	chinese float DEFAULT 0.0 COMMENT '語文成績',
	math float DEFAULT 0.0 COMMENT '數(shù)學(xué)成績',
	english float DEFAULT 0.0 COMMENT '英語成績'
);
-- 插入測試數(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);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

1.SELECT列

1.1全列查詢

– 通常情況下不建議使用 * 進行全列查詢

– 1. 查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大；

– 2. 可能會影響到索引的使用。

SELECT * FROM exam_result;

SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權(quán) | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

1.2指定列查詢

– 指定列的順序不需要按定義表的順序來

SELECT id, name, english FROM exam_result;

SELECT id, name, english FROM exam_result;
+----+-----------+--------+
| id | name | english |
+----+-----------+--------+
| 1 | 唐三藏 | 56 |
| 2 | 孫悟空 | 77 |
| 3 | 豬悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 劉玄德 | 45 |
| 6 | 孫權(quán) | 78 |
| 7 | 宋公明 | 30 |
+----+-----------+--------+
7 rows in set (0.00 sec)

1.3查詢字段為表達式

– 表達式不包含字段

SELECT id, name, 10 FROM exam_result;

SELECT id, name, 10 FROM exam_result;
+----+-----------+----+
| id | name | 10 |
+----+-----------+----+
| 1 | 唐三藏 | 10 |
| 2 | 孫悟空 | 10 |
| 3 | 豬悟能 | 10 |
| 4 | 曹孟德 | 10 |
| 5 | 劉玄德 | 10 |
| 6 | 孫權(quán) | 10 |
| 7 | 宋公明 | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)

– 表達式包含一個字段

SELECT id, name, english + 10 FROM exam_result;

SELECT id, name, english + 10 FROM exam_result;
+----+-----------+-------------+
| id | name | english + 10 |
+----+-----------+-------------+
| 1 | 唐三藏 | 66 |
| 2 | 孫悟空 | 87 |
| 3 | 豬悟能 | 100 |
| 4 | 曹孟德 | 77 |
| 5 | 劉玄德 | 55 |
| 6 | 孫權(quán) | 88 |
| 7 | 宋公明 | 40 |
+----+-----------+-------------+
7 rows in set (0.00 sec)

– 表達式包含多個字段

SELECT id, name, chinese + math + english FROM exam_result;

SELECT id, name, chinese + math + english FROM exam_result;
+----+-----------+-------------------------+
| id | name | chinese + math + english |
+----+-----------+-------------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權(quán) | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+-------------------------+
7 rows in set (0.00 sec)

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

語法：

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

SELECT id, name, chinese + math + english 總分 FROM exam_result;

+----+-----------+--------+
| id | name | 總分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權(quán) | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec)

1.5 結(jié)果去重 – distinct

-- 98 分重復(fù)了
SELECT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
7 rows in set (0.00 sec)

-- 去重結(jié)果
SELECT DISTINCT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)

2.WHERE 條件

比較運算符：

運算符
>, >=, <, <=大于，大于等于，小于，小于等于
=等于，NULL 不安全，例如 NULL = NULL 的結(jié)果是 NULL
<=>等于，NULL 安全，例如 NULL <=> NULL 的結(jié)果是 TRUE(1)
!=, <>不等于
BETWEEN a0 AND a1范圍匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)如果是 option 中的任意一個，返回 TRUE(1)
IS NULL是 NULL
IS NOT NULL不是 NULL
LIKE模糊匹配。% 表示任意多個（包括 0 個）任意字符；_ 表示任意一個字符

邏輯運算符：

運算符說明
AND多個條件必須都為 TRUE(1)，結(jié)果才是TRUE(1)
OR任意一個條件為 TRUE(1), 結(jié)果為 TRUE(1)
NOT條件為 TRUE(1)，結(jié)果為 FALSE(0)

表的內(nèi)容如下：

SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權(quán) | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

1.英語不及格的同學(xué)及英語成績 ( < 60 )

SELECT name, english FROM exam_result WHERE english < 60;
+-----------+--------+
| name | english |
+-----------+--------+
| 唐三藏 | 56 |
| 劉玄德 | 45 |
| 宋公明 | 30 |
+-----------+--------+
3 rows in set (0.01 sec)

2.語文成績在 [80, 90] 分的同學(xué)及語文成績

– 使用 AND 進行條件連接

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;
+-----------+-------+
| name | chinese |
+-----------+-------+
| 孫悟空 | 87 |
| 豬悟能 | 88 |
| 曹孟德 | 82 |
+-----------+-------+
3 rows in set (0.00 sec)

– 使用 BETWEEN … AND … 條件

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
+-----------+-------+
| name | chinese |
+-----------+-------+
| 孫悟空 | 87 |
| 豬悟能 | 88 |
| 曹孟德 | 82 |
+-----------+-------+
3 rows in set (0.00 sec)

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

– 使用 OR 進行條件連接

SELECT name, math FROM exam_result
WHERE math = 58
OR math = 59
OR math = 98
OR math = 99;
+-----------+--------+
| name | math |
+-----------+--------+
| 唐三藏 | 98 |
| 豬悟能 | 98 |
+-----------+--------+
2 rows in set (0.01 sec)

– 使用 IN 條件

SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
+-----------+--------+
| name | math |
+-----------+--------+
| 唐三藏 | 98 |
| 豬悟能 | 98 |
+-----------+--------+
2 rows in set (0.00 sec)

4.姓孫的同學(xué) 及 孫某同學(xué)

– % 匹配任意多個（包括 0 個）任意字符

SELECT name FROM exam_result WHERE name LIKE '孫%';
+-----------+
| name |
+-----------+
| 孫悟空 |
| 孫權(quán) |
+-----------+
2 rows in set (0.00 sec)

– _ 匹配嚴(yán)格的一個任意字符

SELECT name FROM exam_result WHERE name LIKE '孫_';
+--------+
| name |
+--------+
| 孫權(quán) |
+--------+
1 row in set (0.00 sec)

5.語文成績好于英語成績的同學(xué)

– WHERE 條件中比較運算符兩側(cè)都是字段

SELECT name, chinese, english FROM exam_result WHERE chinese > english;
+-----------+-------+--------+
| name | chinese | english |
+-----------+-------+--------+
| 唐三藏 | 67 | 56 |
| 孫悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 劉玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+-------+--------+
5 rows in set (0.00 sec)

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

– WHERE 條件中使用表達式

– 別名不能用在 WHERE 條件中

SELECT name, chinese + math + english 總分 FROM exam_result
WHERE chinese + math + english < 200;
+-----------+--------+
| name | 總分 |
+-----------+--------+
| 劉玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+
2 rows in set (0.00 sec)

7.語文成績 > 80 并且不姓孫的同學(xué)

– AND 與 NOT 的使用

SELECT name, chinese FROM exam_result
WHERE chinese > 80 AND name NOT LIKE '孫%';
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+-------+--------+--------+
2 rows in set (0.00 sec)

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

SELECT name, chinese, math, english, chinese + math + english 總分 FROM exam_result WHERE name LIKE '孫_' OR (chinese + math + english > 200 AND chinese < math AND english > 80);

+-----------+-------+--------+--------+--------+
| name | chinese | math | english | 總分 |
+-----------+-------+--------+--------+--------+
| 豬悟能 | 88 | 98 | 90 | 276 |
| 孫權(quán) | 70 | 73 | 78 | 221 |
+-----------+-------+--------+--------+--------+
2 rows in set (0.00 sec)

9.NULL 的查詢

表的內(nèi)容如下：

-- 查詢 students 表
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大師 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
| 103 | 20002 | 孫仲謀 | NULL |
| 104 | 20001 | 曹阿瞞 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

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

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

SELECT name, qq FROM students WHERE qq IS NOT NULL;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孫悟空 | 11111 |
+-----------+-------+
1 row in set (0.00 sec)

NULL 和 NULL 的比較，= 和 <=> 的區(qū)別

SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
| NULL | NULL | NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)

SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1 | 0 | 0 |
+---------------+------------+------------+
1 row in set (0.00 sec)

3.結(jié)果排序

語法：

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

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

表的內(nèi)容如下：

SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權(quán) | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

-- 查詢 students 表
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大師 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
| 103 | 20002 | 孫仲謀 | NULL |
| 104 | 20001 | 曹阿瞞 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

1.同學(xué)及數(shù)學(xué)成績，按數(shù)學(xué)成績升序顯示

select name, math from exam_result order by math;

SELECT name, math FROM exam_result ORDER BY math;
+-----------+--------+
| name | math |
+-----------+--------+
| 宋公明 | 65 |
| 孫權(quán) | 73 |
| 孫悟空 | 78 |
| 曹孟德 | 84 |
| 劉玄德 | 85 |
| 唐三藏 | 98 |
| 豬悟能 | 98 |
+-----------+--------+
7 rows in set (0.00 sec)

2.同學(xué)及 qq 號，按 qq 號排序顯示

select name, qq from students order by qq;

-- NULL 視為比任何值都小，升序出現(xiàn)在最上面
SELECT name, qq FROM students ORDER BY qq;
+-----------+-------+
| name | qq |
+-----------+-------+
| 唐大師 | NULL |
| 孫仲謀 | NULL |
| 曹阿瞞 | NULL |
| 孫悟空 | 11111 |
+-----------+-------+
4 rows in set (0.00 sec)

-- NULL 視為比任何值都小，降序出現(xiàn)在最下面
SELECT name, qq FROM students ORDER BY qq DESC;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孫悟空 | 11111 |
| 唐大師 | NULL |
| 孫仲謀 | NULL |
| 曹阿瞞 | NULL |
+-----------+-------+
4 rows in set (0.00 sec)

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

select chinese, math, english from exam_result 
order by myth desc,english asc,chinese asc;

SELECT name, math, english, chinese FROM exam_result
ORDER BY math DESC, english, chinese;
+-----------+--------+--------+-------+
| name | math | english | chinese |
+-----------+--------+--------+-------+
| 唐三藏 | 98 | 56 | 67 |
| 豬悟能 | 98 | 90 | 88 |
| 劉玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孫悟空 | 78 | 77 | 87 |
| 孫權(quán) | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+--------+--------+-------+
7 rows in set (0.00 sec)

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

select chinses+math+english 總分 from exam_result order by chinese+math+english desc;

-- ORDER BY 中可以使用表達式
SELECT name, chinese + english + math FROM exam_result
ORDER BY chinese + english + math DESC;
+-----------+-------------------------+
| name | chinese + english + math |
+-----------+-------------------------+
| 豬悟能 | 276 |
| 孫悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孫權(quán) | 221 |
| 劉玄德 | 185 |
| 宋公明 | 170 |
+-----------+-------------------------+
7 rows in set (0.00 sec)

-- ORDER BY 子句中可以使用列別名
SELECT name, chinese + english + math 總分 FROM exam_result
ORDER BY 總分 DESC;
+-----------+--------+
| name | 總分 |
+-----------+--------+
| 豬悟能 | 276 |
| 孫悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孫權(quán) | 221 |
| 劉玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+
7 rows in set (0.00 sec)

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

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

-- 結(jié)合 WHERE 子句 和 ORDER BY 子句
SELECT name, math FROM exam_result
WHERE name LIKE '孫%' OR name LIKE '曹%'
ORDER BY math DESC;
+-----------+--------+
| name | math |
+-----------+--------+
| 曹孟德 | 84 |
| 孫悟空 | 78 |
| 孫權(quán) | 73 |
+-----------+--------+
3 rows in set (0.00 sec)

4.篩選分頁結(jié)果

語法：

-- 起始下標(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;

建議：對未知表進行查詢時，最好加一條LIMIT 1，避免因為表中數(shù)據(jù)過大，查詢?nèi)頂?shù)據(jù)導(dǎo)致數(shù)據(jù)庫卡死

按 id 進行分頁，每頁3 條記錄，分別顯示 第1、2、3 頁

-- 第 1 頁
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 0;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 1 | 唐三藏 | 98 | 56 | 67 |
| 2 | 孫悟空 | 78 | 77 | 87 |
| 3 | 豬悟能 | 98 | 90 | 88 |
+----+-----------+--------+--------+-------+
3 rows in set (0.02 sec)

-- 第 2 頁
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 3;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 4 | 曹孟德 | 84 | 67 | 82 |
| 5 | 劉玄德 | 85 | 45 | 55 |
| 6 | 孫權(quán) | 73 | 78 | 70 |
+----+-----------+--------+--------+-------+
3 rows in set (0.00 sec)

-- 第 3 頁，如果結(jié)果不足 3 個，不會有影響
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 6;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 7 | 宋公明 | 65 | 30 | 75 |
+----+-----------+--------+--------+-------+
1 row in set (0.00 sec)

三、更新–Update

語法：

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

對查詢到的結(jié)果進行列值更新

案例：

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

-- 更新值為具體值
-- 查看原數(shù)據(jù)
SELECT name, math FROM exam_result WHERE name = '孫悟空';
+-----------+--------+
| name | math |
+-----------+--------+
| 孫悟空 | 78 |
+-----------+--------+
1 row in set (0.00 sec)
-- 數(shù)據(jù)更新
UPDATE exam_result SET math = 80 WHERE name = '孫悟空';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 查看更新后數(shù)據(jù)
SELECT name, math FROM exam_result WHERE name = '孫悟空';
+-----------+--------+
| name | math |
+-----------+--------+
| 孫悟空 | 80 |
+-----------+--------+
1 row in set (0.00 sec)

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

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

-- 一次更新多個列
-- 查看原數(shù)據(jù)
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
name | math | chinese |
+-----------+--------+-------+
| 曹孟德 | 84 | 82 |
+-----------+--------+-------+
1 row in set (0.00 sec)
-- 數(shù)據(jù)更新
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 查看更新后數(shù)據(jù)
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
| name | math | chinese |
+-----------+--------+-------+
| 曹孟德 | 60 | 70 |
+-----------+--------+-------+
1 row in set (0.00 sec)

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

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

-- 更新值為原值基礎(chǔ)上變更
-- 查看原數(shù)據(jù)
-- 別名可以在ORDER BY中使用
SELECT name, math, chinese + math + english 總分 FROM exam_result
ORDER BY 總分 LIMIT 3;
+-----------+--------+--------+
| name | math | 總分 |
+-----------+--------+--------+
| 宋公明 | 65 | 170 |
| 劉玄德 | 85 | 185 |
| 曹孟德 | 60 | 197 |
+-----------+--------+--------+
3 rows in set (0.00 sec)
-- 數(shù)據(jù)更新，不支持 math += 30 這種語法
UPDATE exam_result SET math = math + 30
ORDER BY chinese + math + english LIMIT 3;
-- 查看更新后數(shù)據(jù)
-- 思考：這里還可以按總分升序排序取前 3 個么？
SELECT name, math, chinese + math + english 總分 FROM exam_result
WHERE name IN ('宋公明', '劉玄德', '曹孟德');
+-----------+--------+--------+
| name | math | 總分 |
+-----------+--------+--------+
| 曹孟德 | 90 | 227 |
| 劉玄德 | 115 | 215 |
| 宋公明 | 95 | 200 |
+-----------+--------+--------+
3 rows in set (0.00 sec)
-- 按總成績排序后查詢結(jié)果
SELECT name, math, chinese + math + english 總分 FROM exam_result
ORDER BY 總分 LIMIT 3;
+-----------+--------+--------+
| name | math | 總分 |
+-----------+--------+--------+
| 宋公明 | 95 | 200 |
| 劉玄德 | 115 | 215 |
| 唐三藏 | 98 | 221 |
+-----------+--------+--------+
3 rows in set (0.00 sec)

4.將所有同學(xué)的語文成績更新為原來的 2 倍

update exam_result chinese = chinese * 2;

-- 沒有 WHERE 子句，則更新全表
-- 查看原數(shù)據(jù)
SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 80 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 劉玄德 | 55 | 115 | 45 |
| 6 | 孫權(quán) | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 95 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)
-- 數(shù)據(jù)更新
UPDATE exam_result SET chinese = chinese * 2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
-- 查看更新后數(shù)據(jù)
SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孫悟空 | 174 | 80 | 77 |
| 3 | 豬悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 劉玄德 | 110 | 115 | 45 |
| 6 | 孫權(quán) | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

四、刪除–Delete

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

語法：

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

案例：

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

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

-- 查看原數(shù)據(jù)
SELECT * FROM exam_result WHERE name = '孫悟空';
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 2 | 孫悟空 | 174 | 80 | 77 |
+----+-----------+-------+--------+--------+
1 row in set (0.00 sec)
-- 刪除數(shù)據(jù)
DELETE FROM exam_result WHERE name = '孫悟空';
Query OK, 1 row affected (0.17 sec)
-- 查看刪除結(jié)果
SELECT * FROM exam_result WHERE name = '孫悟空';
Empty set (0.00 sec)

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

-- 準(zhǔn)備測試表
CREATE TABLE for_delete (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)

-- 插入測試數(shù)據(jù)
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

-- 查看測試數(shù)據(jù)
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)

-- 刪除整表數(shù)據(jù)
DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)

-- 查看刪除結(jié)果
SELECT * FROM for_delete;
Empty set (0.00 sec)

-- 再插入一條數(shù)據(jù)，自增 id 在原值上增長
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

-- 查看數(shù)據(jù)
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4 | D |
+----+------+
1 row in set (0.00 sec)

-- 查看表結(jié)構(gòu)，會有 AUTO_INCREMENT=n 項
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

我們發(fā)現(xiàn)刪除表之后，自增長的數(shù)字依然在增加，并沒有重新從1開始增長

2.截斷表

語法：

TRUNCATE [TABLE] table_name

注意：這個操作慎用

1.只能對整表操作，不能像 DELETE 一樣針對部分?jǐn)?shù)據(jù)操作；

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

3.會重置 AUTO_INCREMENT 項

創(chuàng)建表并插入數(shù)據(jù)

-- 準(zhǔn)備測試表
CREATE TABLE for_truncate (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);

Query OK, 0 rows affected (0.16 sec)
-- 插入測試數(shù)據(jù)
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

-- 查看測試數(shù)據(jù)
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)

截斷整表數(shù)據(jù)

-- 截斷整表數(shù)據(jù)，注意影響行數(shù)是 0，所以實際上沒有對數(shù)據(jù)真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)

-- 查看刪除結(jié)果
SELECT * FROM for_truncate;
Empty set (0.00 sec)

我們發(fā)現(xiàn)影響行數(shù)是 0，所以實際上沒有對數(shù)據(jù)真正操作，但是表已經(jīng)刪除

-- 再插入一條數(shù)據(jù)，自增 id 在重新增長
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

我們再插入一條數(shù)據(jù)，發(fā)現(xiàn)自增 id 在重新增長

-- 查看數(shù)據(jù)
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)

-- 查看表結(jié)構(gòu)，會有 AUTO_INCREMENT=2 項
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

總結(jié)：

delete整張表之后，自增長的數(shù)據(jù)會繼續(xù)增長，并不會繼續(xù)從1開始增長

truncate整張表之后，沒有經(jīng)過事物，無法回滾，自增長會從1開始增長

五、插入查詢結(jié)果

語法：

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

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

-- 創(chuàng)建原數(shù)據(jù)表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

-- 插入測試數(shù)據(jù)
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

思路：

創(chuàng)建一張空表 no_duplicate_table，結(jié)構(gòu)和 duplicate_table 一樣

CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.00 sec)

將 duplicate_table 的去重數(shù)據(jù)插入到 no_duplicate_table

INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

通過重命名表，實現(xiàn)原子的去重操作

RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.00 sec)

查看最終結(jié)果

SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)

六、聚合函數(shù)

函數(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ù)字沒有意義

案例：

表的內(nèi)容如下：

-- 查詢 students 表
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大師 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
| 103 | 20002 | 孫仲謀 | NULL |
| 104 | 20001 | 曹阿瞞 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權(quán) | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)

1.統(tǒng)計班級共有多少同學(xué)

select count(*) from students;

-- 使用 * 做統(tǒng)計，不受 NULL 影響
SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

-- 使用表達式做統(tǒng)計
SELECT COUNT(1) FROM students;
+----------+
| COUNT(1) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

2.統(tǒng)計班級收集的 qq 號有多少

select count(qq) from students;

-- NULL 不會計入結(jié)果
SELECT COUNT(qq) FROM students;
+-----------+
| COUNT(qq) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)

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

select count(distinct math) from exam_result;

-- COUNT(math) 統(tǒng)計的是全部成績
SELECT COUNT(math) FROM exam_result;
+---------------+
| COUNT(math) |
+---------------+
| 6 |
+---------------+
1 row in set (0.00 sec)

-- COUNT(DISTINCT math) 統(tǒng)計的是去重成績數(shù)量
SELECT COUNT(DISTINCT math) FROM exam_result;
+------------------------+
| COUNT(DISTINCT math) |
+------------------------+
| 5 |
+------------------------+
1 row in set (0.00 sec)

4.統(tǒng)計數(shù)學(xué)成績總分

select sum(math) from exam_result;

SELECT SUM(math) FROM exam_result;
+-------------+
| SUM(math) |
+-------------+
| 569 |
+-------------+
1 row in set (0.00 sec)

-- 不及格 < 60 的總分，沒有結(jié)果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
+-------------+
| SUM(math) |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)

5.統(tǒng)計平均總分

select avg(chinese + math + english) from exam_result;

SELECT AVG(chinese + math + english) 平均總分 FROM exam_result;
+--------------+
| 平均總分 |
+--------------+
| 297.5 |
+--------------+

6.返回英語最高分

select max(english) from exam_result;

SELECT MAX(english) FROM exam_result;
+-------------+
| MAX(english) |
+-------------+
| 90 |
+-------------+
1 row in set (0.00 sec)

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

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

SELECT MIN(math) FROM exam_result WHERE math > 70;
+-------------+
| MIN(math) |
+-------------+
| 73 |
+-------------+
1 row in set (0.00 sec)

七、group by子句的使用

在select中使用group by 子句可以對指定列進行分組查詢

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

案例：

準(zhǔn)備工作，創(chuàng)建一個雇員信息表（來自oracle 9i的經(jīng)典測試表）

EMP員工表

DEPT部門表

SALGRADE工資等級表

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部門編號',
  `dname` varchar(14) DEFAULT NULL COMMENT '部門名稱',
  `loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號',
  `hiredate` datetime DEFAULT NULL COMMENT '雇傭時間',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '獎金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號'
);

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等級',
  `losal` int(11) DEFAULT NULL COMMENT '此等級最低工資',
  `hisal` int(11) DEFAULT NULL COMMENT '此等級最高工資'
);

insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

將上面的內(nèi)容放到一個文件中，然后使用source 命令即可創(chuàng)建三張表

表的內(nèi)容如下：

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

1.如何顯示每個部門的平均工資和最高工資

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

mysql> select deptno, avg(sal) 平均工資, max(sal) 最高工資 from emp group by deptno;
+--------+--------------+--------------+
| deptno | 平均工資     | 最高工資     |
+--------+--------------+--------------+
|     10 |  2916.666667 |      5000.00 |
|     20 |  2175.000000 |      3000.00 |
|     30 |  1566.666667 |      2850.00 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)

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

mysql> select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     10 | CLERK     | 1300.000000 |  1300.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     20 | CLERK     |  950.000000 |   800.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

3.顯示平均工資低于2000的部門和它的平均工資

1.統(tǒng)計各個部門的平均工資

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

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

2.having和group by配合使用，對group by結(jié)果進行過濾

select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;
mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

–having經(jīng)常和group by搭配使用，作用是對分組進行篩選，作用有些像where

總結(jié)

到此這篇關(guān)于MySQL表的增刪查改及聚合函數(shù)/group by子句的使用方法舉例的文章就介紹到這了,更多相關(guān)MySQL表增刪查改及聚合函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • Windows下MySQL8.0.11社區(qū)綠色版安裝步驟圖解

    Windows下MySQL8.0.11社區(qū)綠色版安裝步驟圖解

    在本教程中使用MySQL最新的MySQL服務(wù)8.0.11的社區(qū)綠色版本進行安裝，綠色版為zip格式的包，安裝步驟分為四大步驟，具體哪四大步驟大家跟隨腳本之家小編一起學(xué)習(xí)吧
    2018-05-05
  • MySQL為什么臨時表可以重名

    MySQL為什么臨時表可以重名

    本文主要介紹了MySQL為什么臨時表可以重名，有的人可能會認(rèn)為，臨時表就是內(nèi)存表，其實是完全不同的，感興趣的可以一起來了解一下
    2022-03-03
  • 基于sqlalchemy對mysql實現(xiàn)增刪改查操作

    基于sqlalchemy對mysql實現(xiàn)增刪改查操作

    這篇文章主要介紹了基于sqlalchemy對mysql實現(xiàn)增刪改查操作,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2020-06-06
  • MySQL實現(xiàn)用逗號進行拼接、以逗號進行分割

    MySQL實現(xiàn)用逗號進行拼接、以逗號進行分割

    這篇文章主要介紹了MySQL實現(xiàn)用逗號進行拼接、以逗號進行分割問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2022-12-12
  • MySQL數(shù)據(jù)庫優(yōu)化與定期數(shù)據(jù)處理策略

    MySQL數(shù)據(jù)庫優(yōu)化與定期數(shù)據(jù)處理策略

    在當(dāng)今數(shù)據(jù)驅(qū)動的時代,數(shù)據(jù)庫作為信息存儲和管理的核心,扮演著至關(guān)重要的角色,本文將探討如何通過一系列有效的策略來優(yōu)化 MySQL 數(shù)據(jù)庫的查詢效率,并實現(xiàn)定期處理數(shù)據(jù)的機制,以確保主表中的數(shù)據(jù)保持在合理范圍內(nèi),需要的朋友可以參考下
    2025-03-03
  • mysql數(shù)據(jù)庫插入速度和讀取速度的調(diào)整記錄

    mysql數(shù)據(jù)庫插入速度和讀取速度的調(diào)整記錄

    由于項目變態(tài)需求；需要在一個比較短時間段急劇增加數(shù)據(jù)庫記錄（兩三天內(nèi)，由于0增加至4億）。在整個過程調(diào)優(yōu)過程非常艱辛
    2012-07-07
  • MySQL不支持InnoDB的解決方法

    MySQL不支持InnoDB的解決方法

    在OpenSUSE下裝上MySQL后，發(fā)現(xiàn)無法選擇添加事務(wù)支持?jǐn)?shù)據(jù)引擎InnoDB。
    2009-11-11
  • mysql存儲過程原理與使用方法詳解

    mysql存儲過程原理與使用方法詳解

    這篇文章主要介紹了mysql存儲過程原理與使用方法,結(jié)合實例形式詳細(xì)分析了mysql存儲過程的優(yōu)缺點、定義、調(diào)用方法及相關(guān)操作注意事項,需要的朋友可以參考下
    2019-12-12
  • Mysql更換MyISAM存儲引擎為Innodb的操作記錄總結(jié)

    Mysql更換MyISAM存儲引擎為Innodb的操作記錄總結(jié)

    下面小編就為大家?guī)硪黄狹ysql更換MyISAM存儲引擎為Innodb的操作記錄總結(jié)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
    2017-03-03
  • MySQL的子查詢中FROM和EXISTS子句的使用教程

    MySQL的子查詢中FROM和EXISTS子句的使用教程

    這篇文章主要介紹了MySQL的子查詢中FROM和EXISTS子句的使用教程,子查詢是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下
    2015-12-12

最新評論