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

MySql基本查詢之表的增刪查改+聚合函數(shù)案例詳解

 更新時(shí)間：2025年07月14日 11:38:51   作者：是阿建吖!  
本文詳解SQL的CURD操作INSERT用于數(shù)據(jù)插入（單行/多行及沖突處理）,SELECT實(shí)現(xiàn)數(shù)據(jù)檢索（列選擇、條件過(guò)濾、排序分頁(yè)）,UPDATE修改數(shù)據(jù),DELETE刪除數(shù)據(jù)并對(duì)比TRUNCATE,涵蓋聚合函數(shù)、GROUP BY分組統(tǒng)計(jì)及經(jīng)典練習(xí)題,強(qiáng)調(diào)操作注意事項(xiàng)與執(zhí)行順序,感興趣的朋友一起看看吧

CURD : Create(創(chuàng)建)，Update(更新)，Retrieve(讀取)，Delete（刪除）

一、Create

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

說(shuō)明：

  • INSERT [INTO]：INTO 是可選的，但通常會(huì)被包含，以提高語(yǔ)句的可讀性。
  • table_name：這是你想要插入數(shù)據(jù)的表的名稱。
  • (column1,column2, …)：這是可選的列名列表。如果提供了這個(gè)列表，那么 VALUES 中的值將按照這里指定的順序被插入到相應(yīng)的列中。如果省略了這個(gè)列表，那么 VALUES 中的值將按照表中列的順序被插入，這要求 VALUES 中的值的順序必須與表中列的順序完全一致。
  • VALUES：這是你要插入的值，每個(gè)值之間用逗號(hào)分隔。如果一次插入多行數(shù)據(jù)，每行的值應(yīng)該用括號(hào)括起來(lái)，并且各行的值列表之間用逗號(hào)分隔。

實(shí)例：

create table if not exists stu(
	id int unsigned primary key auto_increment,
	num int not null unique comment '學(xué)號(hào)',
	name varchar(10) not null comment '姓名',
	telephone varchar(11) unique comment '電話號(hào)碼'
);

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

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

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

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

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

1.3 插入否則更新

由于主鍵或者唯一鍵對(duì)應(yīng)的值已經(jīng)存在而導(dǎo)致插入失敗，可以選擇性的進(jìn)行同步更新操作。

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

說(shuō)明：

  • INSERT … ：這部分是標(biāo)準(zhǔn)的插入語(yǔ)句
  • ON DUPLICATE KEY UPDATE: 這是一個(gè)條件子句，它指定了當(dāng)插入操作因?yàn)槲ㄒ绘I或主鍵沖突而失敗時(shí)應(yīng)該執(zhí)行的操作。
  • column = value ：這里列出了在發(fā)生沖突時(shí)需要更新的列和它們的新值

下面先插入一行數(shù)據(jù)，然后再插入一行數(shù)據(jù)（主鍵/唯一鍵沖突），如果主鍵/唯一鍵沖突就將num和telephone修改為后面的值，我們看到表中的num和telephone確實(shí)發(fā)生了改變，但是id卻沒(méi)有改變，很明顯就是在原來(lái)數(shù)據(jù)的基礎(chǔ)上進(jìn)行修改的。

命令執(zhí)行完后，會(huì)顯示影響了多少行，不同的行數(shù)也代表著不同的含義。

  • 0 row affected: 表中有沖突數(shù)據(jù)，但沖突數(shù)據(jù)的值和 update 的值相等
  • 1 row affected: 表中沒(méi)有沖突數(shù)據(jù)，數(shù)據(jù)被插入
  • 2 row affected: 表中有沖突數(shù)據(jù)，并且數(shù)據(jù)已經(jīng)被更新

我們可以通過(guò)MySQL函數(shù)來(lái)查看影響數(shù)據(jù)的行數(shù)。

SELECT ROW_COUNT();

由于我上一條命令是查詢語(yǔ)句，并未影響表中數(shù)據(jù)，所以函數(shù)的返回值為-1。

1.4 替換

  • 當(dāng)用戶插入數(shù)據(jù)時(shí)，主鍵或者唯一鍵沒(méi)有沖突，則直接插入
  • 當(dāng)用戶插入數(shù)據(jù)時(shí)，主鍵或者唯一鍵如果沖突，則刪除后再插入

下面我插入了兩次數(shù)據(jù)，第一次沒(méi)有沖突就直接插入了，第二次唯一鍵沖突了，刪除數(shù)據(jù)后，再插入，由于這里的id值發(fā)生了改變，所以這里可以確定數(shù)據(jù)確實(shí)是被刪除后，再插入的。

二、Retrieve

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

說(shuō)明：

  • SELECT [DISTINCT]：

    • SELECT 關(guān)鍵字用于從數(shù)據(jù)庫(kù)中選擇數(shù)據(jù)。
    • DISTINCT 是一個(gè)可選的關(guān)鍵字，用于返回唯一不同的值，即去除重復(fù)的記錄。

  • { * | {column [, column] …}}：

    • 星號(hào)（*）表示選擇所有列。
    • 如果不使用星號(hào)，可以指定一個(gè)或多個(gè)列名，用逗號(hào)分隔，以選擇特定的列。

  • [FROM table_name]：

    • FROM 關(guān)鍵字指定了查詢將要從哪個(gè)表中檢索數(shù)據(jù)。
    • table_name 是表的名稱。

  • [WHERE …]：

    • WHERE 子句是可選的，用于過(guò)濾記錄。
    • 可以在這里指定條件，只有滿足條件的記錄才會(huì)被選中。

  • [ORDER BY column [ASC | DESC], …]：

    • ORDER BY 子句是可選的，用于對(duì)結(jié)果集進(jìn)行排序。
    • column 指定了排序依據(jù)的列。
    • ASC 表示升序（默認(rèn)），DESC 表示降序。
    • 可以根據(jù)多個(gè)列進(jìn)行排序，列之間用逗號(hào)分隔。

  • LIMIT …：

    • LIMIT 子句用于限制返回的記錄數(shù)。
    • 可以指定一個(gè)數(shù)字來(lái)限制結(jié)果集的大小。
    • 例如，LIMIT 10 會(huì)返回前10條記錄。

例子：

-- 創(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 '語(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);

2.1 SELECT 列

2.1.1 全列查詢

通常情況下不建議使用 * 進(jìn)行全列查詢

  1. 查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大
  2. 可能會(huì)影響到索引的使用。（后面詳細(xì)講解索引）

2.1.2 指定列查詢

指定列查詢并不需要按照表中列的順序來(lái)，任意順序都可以。

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

表達(dá)式的樣式有以下幾種：

  • 表達(dá)式不包含字段
  • 表達(dá)式包含一個(gè)字段
  • 表達(dá)式包含多個(gè)字段

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

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

說(shuō)明：

  • column：這里指的是你想要從table_name中檢索的列的名稱。
  • [AS] alias_name：AS關(guān)鍵字可以省略，用于為column指定的列名創(chuàng)建一個(gè)別名
  • […]：這里的省略號(hào)表示你可以繼續(xù)添加更多的列名及其可選的別名

2.1.5 結(jié)果去重

2.2 WHERE 條件

2.2.1 比較運(yùn)算符

運(yùn)算符說(shuō)明
>, >=, <, <=大于，大于等于，小于，小于等于
=等于，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è)字符

2.2.2 邏輯運(yùn)算符

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

2.2.3 案例實(shí)操

2.2.3.1 英語(yǔ)不及格的同學(xué)及英語(yǔ)成績(jī) ( < 60 )
select name,english from exam_result where english < 60;

2.2.3.2 語(yǔ)文成績(jī)?cè)?[80, 90] 分的同學(xué)及語(yǔ)文成績(jī)
select name,chinese from exam_result where chinese >= 80 and chinese <= 90;
select name,chinese from exam_result where chinese between 80 and 90

2.2.3.3 數(shù)學(xué)成績(jī)是 58 或者 59 或者 98 或者 99 分的同學(xué)及數(shù)學(xué)成績(jī)
select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
select name,math from exam_result where math in(58,59,98,99);

2.2.3.4 姓孫的同學(xué) 及 孫某同學(xué)
select name from exam_result where name like '孫%'

2.2.3.5 語(yǔ)文成績(jī)好于英語(yǔ)成績(jī)的同學(xué)
select name,chinese,english from exam_result where chinese > english;

2.2.3.6 總分在 200 分以下的同學(xué)
select name,chinese,math,english,chinese+math+english as total from exam_result where chinese+math+english<200;

2.2.3.7 語(yǔ)文成績(jī) > 80 并且不姓孫的同學(xué)
select name,chinese from exam_result where chinese>80 and name not like '孫%';

2.2.3.8 孫某同學(xué)，否則要求總成績(jī) > 200 并且 語(yǔ)文成績(jī) < 數(shù)學(xué)成績(jī) 并且 英語(yǔ)成績(jī) > 80
select name,chinese,math,english,chinese+math+english as total from exam_result where (chinese+math+english>200 and chinese<math and englissh > 80) or name like '孫_';

2.2.3.9 NULL 的查詢

下面我創(chuàng)建了一個(gè)表，并向表中插入了3條數(shù)據(jù)，一條name為正常名字，一條name為空字符串，一條name為NULL。通過(guò)下面的查找來(lái)看，空串并不等于NULL，NULL代表怎么都沒(méi)有，空串代表有但字符串中沒(méi)有內(nèi)容。

2.3 結(jié)果排序

2.3.1 結(jié)果排序的語(yǔ)法

SELECT ... FROM table_name [WHERE ...]
	ORDER BY column [ASC|DESC], [...];

說(shuō)明：

  • ASC 為升序（從小到大）
  • DESC 為降序（從大到?。?/li>
  • 默認(rèn)為 ASC

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

2.3.2 案例實(shí)操

2.3.2.1 同學(xué)及數(shù)學(xué)成績(jī)，按數(shù)學(xué)成績(jī)升序顯示
select name,math from exam_result order by math asc;

2.3.2.2 按同學(xué)姓名排序顯示
select * from test order by name asc; // 升序
select * from test order by name desc; // 降序

注意：在進(jìn)行比較的時(shí)候，NULL比任何值都要小

2.3.2.3 查詢同學(xué)各門(mén)成績(jī)，依次按 數(shù)學(xué)降序，英語(yǔ)升序，語(yǔ)文升序的方式顯示
select name,chinese,math,english from exam_result order by math desc,english asc,chinese asc;

2.3.2.4 查詢同學(xué)及總分，由高到低
select name,chinese,math,english,chinese+math+english as total from exam_result order by total desc;

2.3.2.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;

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

2.4.1 篩選分頁(yè)結(jié)果的語(yǔ)法

-- 起始下標(biāo)為 0
-- 從 s 開(kāi)始，篩選 n 條結(jié)果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 從 0 開(kāi)始，篩選 n 條結(jié)果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 從 s 開(kāi)始，篩選 n 條結(jié)果，比第二種用法更明確，建議使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

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

2.4.2 案例實(shí)操

2.4.2.1 按 id 進(jìn)行分頁(yè)，每頁(yè) 3 條記錄，分別顯示 第 1、2、3 頁(yè)
// 方法1
select * from exam_result limit 0,3;
select * from exam_result limit 3,3
select * from exam_result limit 6,3
// 方法2
select * from exam_result limit 3 offset 0;
select * from exam_result limit 3 offset 3;
select * from exam_result limit 3 offset 6;

三、Update

3.1 UPDATE 的語(yǔ)法

UPDATE table_name SET column = expr [, column = expr ...]
	[WHERE ...] [ORDER BY ...] [LIMIT ...]
  • table_name：要更新的表的名稱。
  • column = expr：要更新的列及其新值?？梢灾付ǘ鄠€(gè)列及其新值，每個(gè)列賦值之間用逗號(hào)分隔。
  • [WHERE …]：可選條件，用于指定哪些行應(yīng)該被更新。如果省略，則表中的所有行都會(huì)被更新，更新全表的語(yǔ)句一定要慎用！
  • [ORDER BY …]：可選條件，ORDER BY子句可以用來(lái)指定更新操作的順序
  • [LIMIT …]：可選條件，LIMIT子句用于限制更新操作影響的行數(shù)。

3.2 案例實(shí)操

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

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

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

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

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

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

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

update exam_result set chinese=chinese*2;

注意：更新全表的語(yǔ)句一定要慎用！

四、Delete

4.1 刪除數(shù)據(jù)（DELETE語(yǔ)法）

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

說(shuō)明：

  • table_name：指定要從中刪除記錄的表的名稱。
  • [WHERE …]：可選條件，用于指定哪些行應(yīng)該被刪除。如果省略，則表中的所有行都會(huì)被刪除，刪除全表的語(yǔ)句一定要慎用！
  • [ORDER BY …]：可選條件，ORDER BY子句用于指定刪除記錄的順序。
  • [LIMIT …]：可選條件，LIMIT子句用于限制刪除操作影響的行數(shù)。

DELETE特點(diǎn)：

  1. 不僅僅可以對(duì)整表操作，還可以針對(duì)部分?jǐn)?shù)據(jù)操作；
  2. 不會(huì)重置 AUTO_INCREMENT 項(xiàng)

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

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

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

-- 準(zhǔn)備測(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');

下面我創(chuàng)建了一個(gè)表，并向表中插入數(shù)據(jù)，然后查看AUTO_INCREMENT 值為4，當(dāng)我刪除表中所有元素后，又插入一個(gè)元素，再次查看AUTO_INCREMENT 的值為5，顯然AUTO_INCREMENT 的值沒(méi)有被重置。

// 刪除整張表數(shù)據(jù)
delete from 表名;

4.2 截?cái)啾?/h3>

4.2.1 截?cái)啾恚═RUNCATE語(yǔ)法）

TRUNCATE [TABLE] table_name
  1. 只能對(duì)整表操作，不能像 DELETE 一樣針對(duì)部分?jǐn)?shù)據(jù)操作
  2. 實(shí)際上 MySQL 不對(duì)數(shù)據(jù)操作，所以比 DELETE 更快，但是TRUNCATE在刪除數(shù)據(jù)的時(shí)候，并不經(jīng)過(guò)真正的事務(wù)，所以無(wú)法回滾
  3. 會(huì)重置 AUTO_INCREMENT 項(xiàng)

4.2.2 實(shí)操

-- 準(zhǔn)備測(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');

下面我創(chuàng)建了一個(gè)表，并向表中插入數(shù)據(jù)，然后查看AUTO_INCREMENT 值為4，當(dāng)我刪除表中所有元素后，查看表創(chuàng)建命令時(shí)，發(fā)現(xiàn)沒(méi)有了AUTO_INCREMENT 選項(xiàng)，然后又插入一個(gè)元素，再次查看AUTO_INCREMENT 的值為2，顯然AUTO_INCREMENT 的值被重置。

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

5.1 插入查詢結(jié)果語(yǔ)法

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

說(shuō)明：

  • table_name: 指定了要插入數(shù)據(jù)的表。
  • [(column [, column …])]: 這是可選的列列表，用于指定目標(biāo)表中你想要插入數(shù)據(jù)的列。如果提供了列列表，SELECT 語(yǔ)句返回的列必須按照相同的順序與這些列匹配。如果沒(méi)有提供列列表，那么SELECT 語(yǔ)句返回的列將按順序?qū)?yīng)于目標(biāo)表中的列（前提是列的數(shù)量和類型兼容）。
  • SELECT …: 這部分是一個(gè)SELECT語(yǔ)句，用于指定要從哪個(gè)表（或哪些表）中選擇數(shù)據(jù)，以及選擇哪些列的數(shù)據(jù)。SELECT語(yǔ)句可以包含任何有效的查詢條件，例如WHERE子句來(lái)過(guò)濾數(shù)據(jù)。

5.2 實(shí)操

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

下面我們創(chuàng)建了一個(gè)表duplicate_table，然后再向表中插入一些重復(fù)數(shù)據(jù)?，F(xiàn)在我想將表中的重復(fù)數(shù)據(jù)只留一份，其余的重復(fù)數(shù)據(jù)全部刪除。需要注意的是，我們需要將原本進(jìn)行修改，而不是顯示去重后的結(jié)果。

-- 創(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');

這里我就來(lái)解決這個(gè)問(wèn)題，首先我創(chuàng)建一個(gè)與duplicate_table表結(jié)構(gòu)相同的空表no_duplicate_table，然后將duplicate_table去重后的數(shù)據(jù)插入到no_duplicate_table中，然后對(duì)兩個(gè)表的表名進(jìn)行重命名，使表no_duplicate_table變?yōu)楸韓o_duplicate_table。

六、聚合函數(shù)

6.1 常見(jiàn)聚合函數(shù)

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

6.2 案例 實(shí)操

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

select count(*) from exam_result;

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

select count(math) from exam_result;

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

select sum(math) from exam_result;

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

select sum(chinese+math+english)/count(chinese+math+english) from exam_result;
select avg(chinese+math+english) from exam_result;

6.2.5 返回英語(yǔ)最高分

select max(english) from exam_result;

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

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

七、group by子句的使用

7.1 group by子句的語(yǔ)法

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

特點(diǎn)：

  • 分組的目的就是為了進(jìn)行分組后，方便的進(jìn)行聚合統(tǒng)計(jì)。
  • 指定列名，實(shí)際分組使用該列不同的行數(shù)據(jù)進(jìn)行分組的。
  • 分組條件，在組內(nèi)一定是相同的，所以可以聚合壓縮。

7.2 案例實(shí)操

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

  • EMP員工表
  • DEPT部門(mén)表
  • 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 '部門(mén)編號(hào)',
  `dname` varchar(14) DEFAULT NULL COMMENT '部門(mén)名稱',
  `loc` varchar(13) DEFAULT NULL COMMENT '部門(mén)所在地點(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 '部門(mén)編號(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);

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

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

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

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

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

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

7.3 having VS where

  • WHERE：在聚合前過(guò)濾行（對(duì)原始數(shù)據(jù)生效）。
  • HAVING：在聚合后過(guò)濾分組（對(duì) GROUP BY 結(jié)果生效）。

八、OJ練習(xí)

8.1 SQL233 批量插入數(shù)據(jù)

insert into actor values 
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

8.2 SQL207 找出所有員工當(dāng)前薪水salary情況

select distinct salary from salaries 
order by salary desc;

8.3 SQL200 查找最晚入職員工的所有信息

select * from employees 
where hire_date = (select max(hire_date) from employees);

8.4 SQL201 查找入職員工時(shí)間升序排名的情況下的倒數(shù)第三的員工所有信息

select * from employees 
where hire_date = 
(select distinct hire_date from employees 
order by hire_date desc limit 1 offset 2)

8.5 SQL206 查找薪水記錄超過(guò)15條的員工號(hào)emp_no以及其對(duì)應(yīng)的記錄次數(shù)t

select emp_no,count(emp_no) mycnt 
from salaries group by emp_no having mycnt > 15;

8.6 182. 查找重復(fù)的電子郵箱

select email from Person 
group by email having count(email) >= 2;

8.7 595. 大的國(guó)家

select name,population,area 
from World where area >= 3000000 or population >= 25000000;

8.8 177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;
  RETURN (
    select distinct salary from Employee order by salary desc limit 1 offset N
  );
END

8.9 面試題：SQL查詢中各個(gè)關(guān)鍵字的執(zhí)行先后順序

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

結(jié)尾

到此這篇關(guān)于MySql基本查詢之表的增刪查改+聚合函數(shù)案例詳解的文章就介紹到這了,更多相關(guān)mysql表增刪查改內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評(píng)論