MySQL表的增刪查改及聚合函數(shù)/group?by子句的使用方法舉例
更新時(shí)間：2024年01月20日 11:55:36 作者：椿融雪
這篇文章主要給大家介紹了關(guān)于MySQL表的增刪查改及聚合函數(shù)/group?by子句的使用方法,在MySQL中可以使用聚合函數(shù)與GROUP BY語句可以對(duì)數(shù)據(jù)進(jìn)行分組并進(jìn)行聚合計(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é)號(hào)', name VARCHAR(20) NOT NULL, qq VARCHAR(20) );
插入兩條記錄，value_list 數(shù)量必須和定義表的列的數(shù)量及順序一致
注意，這里在插入的時(shí)候，也可以不用指定id(當(dāng)然，那時(shí)候就需要明確插入數(shù)據(jù)到那些列了)，那么mysql會(huì)使用默認(rèn)的值進(jì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.插入否則更新
由于 主鍵 或者 唯一鍵 對(duì)應(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'
可以選擇性的進(jìn)行同步更新操作語法
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的時(shí)候
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 '語文成績(jī)',
math float DEFAULT 0.0 COMMENT '數(shù)學(xué)成績(jī)',
english float DEFAULT 0.0 COMMENT '英語成績(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);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
1.SELECT列
1.1全列查詢
– 通常情況下不建議使用 * 進(jìn)行全列查詢
– 1. 查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大；
– 2. 可能會(huì)影響到索引的使用。
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查詢字段為表達(dá)式
– 表達(dá)式不包含字段
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)
– 表達(dá)式包含一個(gè)字段
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)
– 表達(dá)式包含多個(gè)字段
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 條件
比較運(yùn)算符：
| 運(yùn)算符 | |
|---|---|
| >, >=, <, <= | 大于，大于等于，小于，小于等于 |
| = | 等于，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 中的任意一個(gè)，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。% 表示任意多個(gè)（包括 0 個(gè)）任意字符；_ 表示任意一個(gè)字符 |
邏輯運(yùn)算符：
| 運(yùn)算符 | 說明 |
|---|---|
| AND | 多個(gè)條件必須都為 TRUE(1)，結(jié)果才是TRUE(1) |
| OR | 任意一個(gè)條件為 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é)及英語成績(jī) ( < 60 )
SELECT name, english FROM exam_result WHERE english < 60; +-----------+--------+ | name | english | +-----------+--------+ | 唐三藏 | 56 | | 劉玄德 | 45 | | 宋公明 | 30 | +-----------+--------+ 3 rows in set (0.01 sec)
2.語文成績(jī)?cè)?/strong> [80, 90] 分的同學(xué)及語文成績(jī)
– 使用 AND 進(jìn)行條件連接
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é)成績(jī)是58或者59或者98或者99分的同學(xué)及數(shù)學(xué)成績(jī)
– 使用 OR 進(jìn)行條件連接
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é)
– % 匹配任意多個(gè)（包括 0 個(gè)）任意字符
SELECT name FROM exam_result WHERE name LIKE '孫%'; +-----------+ | name | +-----------+ | 孫悟空 | | 孫權(quán) | +-----------+ 2 rows in set (0.00 sec)
– _ 匹配嚴(yán)格的一個(gè)任意字符
SELECT name FROM exam_result WHERE name LIKE '孫_'; +--------+ | name | +--------+ | 孫權(quán) | +--------+ 1 row in set (0.00 sec)
5.語文成績(jī)好于英語成績(jī)的同學(xué)
– WHERE 條件中比較運(yùn)算符兩側(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 條件中使用表達(dá)式
– 別名不能用在 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.語文成績(jī) > 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é)，否則要求總成績(jī) > 200 并且 語文成績(jī) < 數(shù)學(xué)成績(jī) 并且 英語成績(jī) > 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 號(hào)已知的同學(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)不要依賴這個(gè)順序
表的內(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é)成績(jī)，按數(shù)學(xué)成績(jī)升序顯示
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 號(hào)，按 qq 號(hào)排序顯示
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é)各門成績(jī)，依次按 數(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 中可以使用表達(dá)式 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é)成績(jī)，結(jié)果按數(shù)學(xué)成績(jī)由高到低顯示
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;
建議：對(duì)未知表進(jìn)行查詢時(shí)，最好加一條LIMIT 1，避免因?yàn)楸碇袛?shù)據(jù)過大，查詢?nèi)頂?shù)據(jù)導(dǎo)致數(shù)據(jù)庫(kù)卡死
按 id 進(jìn)行分頁，每頁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 個(gè)，不會(huì)有影響 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 ...]
對(duì)查詢到的結(jié)果進(jìn)行列值更新
案例：
1.將孫悟空同學(xué)的數(shù)學(xué)成績(jī)變更為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é)成績(jī)變更為 60 分，語文成績(jī)變更為 70 分
update exam_result set math = 60, chinese = 70 where name = '曹孟德'; -- 一次更新多個(gè)列 -- 查看原數(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.將總成績(jī)倒數(shù)前三的 3 位同學(xué)的數(shù)學(xué)成績(jī)加上 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 個(gè)么？
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)
-- 按總成績(jī)排序后查詢結(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é)的語文成績(jī)更新為原來的 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é)的考試成績(jī)
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)備測(cè)試表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入測(cè)試數(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
-- 查看測(cè)試數(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 在原值上增長(zhǎng)
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)，會(huì)有 AUTO_INCREMENT=n 項(xiàng)
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)刪除表之后，自增長(zhǎng)的數(shù)字依然在增加，并沒有重新從1開始增長(zhǎng)
2.截?cái)啾?/h3>
語法：
TRUNCATE [TABLE] table_name
注意：這個(gè)操作慎用
1.只能對(duì)整表操作，不能像 DELETE 一樣針對(duì)部分?jǐn)?shù)據(jù)操作；
2.實(shí)際上 MySQL 不對(duì)數(shù)據(jù)操作，所以比 DELETE 更快，但是TRUNCATE在刪除數(shù)據(jù)的時(shí)候，并不經(jīng)過真正的事物，所以無法回滾
3.會(huì)重置 AUTO_INCREMENT 項(xiàng)
創(chuàng)建表并插入數(shù)據(jù)
-- 準(zhǔn)備測(cè)試表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入測(cè)試數(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
-- 查看測(cè)試數(shù)據(jù)
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
截?cái)嗾頂?shù)據(jù)
-- 截?cái)嗾頂?shù)據(jù)，注意影響行數(shù)是 0，所以實(shí)際上沒有對(duì)數(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í)際上沒有對(duì)數(shù)據(jù)真正操作，但是表已經(jīng)刪除
-- 再插入一條數(shù)據(jù)，自增 id 在重新增長(zhǎng)
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
我們?cè)俨迦胍粭l數(shù)據(jù)，發(fā)現(xiàn)自增 id 在重新增長(zhǎng)
-- 查看數(shù)據(jù) SELECT * FROM for_truncate; +----+------+ | id | name | +----+------+ | 1 | D | +----+------+ 1 row in set (0.00 sec) -- 查看表結(jié)構(gòu)，會(huì)有 AUTO_INCREMENT=2 項(xiàng) 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整張表之后，自增長(zhǎng)的數(shù)據(jù)會(huì)繼續(xù)增長(zhǎng)，并不會(huì)繼續(xù)從1開始增長(zhǎng)
truncate整張表之后，沒有經(jīng)過事物，無法回滾，自增長(zhǎng)會(huì)從1開始增長(zhǎng)
五、插入查詢結(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) -- 插入測(cè)試數(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 的去重?cái)?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
通過重命名表，實(shí)現(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)計(jì)班級(jí)共有多少同學(xué)
select count(*) from students; -- 使用 * 做統(tǒng)計(jì)，不受 NULL 影響 SELECT COUNT(*) FROM students; +----------+ | COUNT(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) -- 使用表達(dá)式做統(tǒng)計(jì) SELECT COUNT(1) FROM students; +----------+ | COUNT(1) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
2.統(tǒng)計(jì)班級(jí)收集的 qq 號(hào)有多少
select count(qq) from students; -- NULL 不會(huì)計(jì)入結(jié)果 SELECT COUNT(qq) FROM students; +-----------+ | COUNT(qq) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
3.統(tǒng)計(jì)本次考試的數(shù)學(xué)成績(jī)分?jǐn)?shù)個(gè)數(shù)
select count(distinct math) from exam_result; -- COUNT(math) 統(tǒng)計(jì)的是全部成績(jī) SELECT COUNT(math) FROM exam_result; +---------------+ | COUNT(math) | +---------------+ | 6 | +---------------+ 1 row in set (0.00 sec) -- COUNT(DISTINCT math) 統(tǒng)計(jì)的是去重成績(jī)數(shù)量 SELECT COUNT(DISTINCT math) FROM exam_result; +------------------------+ | COUNT(DISTINCT math) | +------------------------+ | 5 | +------------------------+ 1 row in set (0.00 sec)
4.統(tǒng)計(jì)數(shù)學(xué)成績(jī)總分
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)計(jì)平均總分
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 子句可以對(duì)指定列進(jìn)行分組查詢
select column1, column2, .. from table group by column;
案例：
準(zhǔn)備工作，創(chuàng)建一個(gè)雇員信息表（來自oracle 9i的經(jīng)典測(cè)試表）
EMP員工表
DEPT部門表
SALGRADE工資等級(jí)表
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 '部門編號(hào)', `dname` varchar(14) DEFAULT NULL COMMENT '部門名稱', `loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點(diǎn)' ); DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號(hào)', `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名', `job` varchar(9) DEFAULT NULL COMMENT '雇員職位', `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)', `hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間', `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪', `comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金', `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號(hào)' ); DROP TABLE IF EXISTS `salgrade`; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等級(jí)', `losal` int(11) DEFAULT NULL COMMENT '此等級(jí)最低工資', `hisal` int(11) DEFAULT NULL COMMENT '此等級(jí)最高工資' ); 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)容放到一個(gè)文件中，然后使用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.如何顯示每個(gè)部門的平均工資和最高工資
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.顯示每個(gè)部門的每種崗位的平均工資和最低工資
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)計(jì)各個(gè)部門的平均工資
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配合使用，對(duì)group by結(jié)果進(jìn)行過濾
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搭配使用，作用是對(duì)分組進(jìn)行篩選，作用有些像where
總結(jié)
到此這篇關(guān)于MySQL表的增刪查改及聚合函數(shù)/group by子句的使用方法舉例的文章就介紹到這了,更多相關(guān)MySQL表增刪查改及聚合函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Windows下MySQL8.0.11社區(qū)綠色版安裝步驟圖解
在本教程中使用MySQL最新的MySQL服務(wù)8.0.11的社區(qū)綠色版本進(jìn)行安裝，綠色版為zip格式的包，安裝步驟分為四大步驟，具體哪四大步驟大家跟隨腳本之家小編一起學(xué)習(xí)吧2018-05-05
本文主要介紹了MySQL為什么臨時(shí)表可以重名，有的人可能會(huì)認(rèn)為，臨時(shí)表就是內(nèi)存表，其實(shí)是完全不同的，感興趣的可以一起來了解一下2022-03-03
基于sqlalchemy對(duì)mysql實(shí)現(xiàn)增刪改查操作
這篇文章主要介紹了基于sqlalchemy對(duì)mysql實(shí)現(xiàn)增刪改查操作,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-06-06
MySQL實(shí)現(xiàn)用逗號(hào)進(jìn)行拼接、以逗號(hào)進(jìn)行分割
這篇文章主要介紹了MySQL實(shí)現(xiàn)用逗號(hào)進(jìn)行拼接、以逗號(hào)進(jìn)行分割問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12
MySQL數(shù)據(jù)庫(kù)優(yōu)化與定期數(shù)據(jù)處理策略
在當(dāng)今數(shù)據(jù)驅(qū)動(dòng)的時(shí)代,數(shù)據(jù)庫(kù)作為信息存儲(chǔ)和管理的核心,扮演著至關(guān)重要的角色,本文將探討如何通過一系列有效的策略來優(yōu)化 MySQL 數(shù)據(jù)庫(kù)的查詢效率,并實(shí)現(xiàn)定期處理數(shù)據(jù)的機(jī)制,以確保主表中的數(shù)據(jù)保持在合理范圍內(nèi),需要的朋友可以參考下2025-03-03
mysql數(shù)據(jù)庫(kù)插入速度和讀取速度的調(diào)整記錄
由于項(xiàng)目變態(tài)需求；需要在一個(gè)比較短時(shí)間段急劇增加數(shù)據(jù)庫(kù)記錄（兩三天內(nèi)，由于0增加至4億）。在整個(gè)過程調(diào)優(yōu)過程非常艱辛2012-07-07
在OpenSUSE下裝上MySQL后，發(fā)現(xiàn)無法選擇添加事務(wù)支持?jǐn)?shù)據(jù)引擎InnoDB。2009-11-11
這篇文章主要介紹了mysql存儲(chǔ)過程原理與使用方法,結(jié)合實(shí)例形式詳細(xì)分析了mysql存儲(chǔ)過程的優(yōu)缺點(diǎn)、定義、調(diào)用方法及相關(guān)操作注意事項(xiàng),需要的朋友可以參考下2019-12-12
Mysql更換MyISAM存儲(chǔ)引擎為Innodb的操作記錄總結(jié)
下面小編就為大家?guī)硪黄狹ysql更換MyISAM存儲(chǔ)引擎為Innodb的操作記錄總結(jié)。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-03-03
這篇文章主要介紹了MySQL的子查詢中FROM和EXISTS子句的使用教程,子查詢是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-12-12