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

一、Create

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

說明：

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

實例：

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

1.1 單行數據 + 全列插入

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

1.2 多行數據 + 指定列插入

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

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

1.3 插入否則更新

由于主鍵或者唯一鍵對應的值已經存在而導致插入失敗，可以選擇性的進行同步更新操作。

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

說明：

• INSERT … ：這部分是標準的插入語句
• ON DUPLICATE KEY UPDATE: 這是一個條件子句，它指定了當插入操作因為唯一鍵或主鍵沖突而失敗時應該執行的操作。
• column = value ：這里列出了在發生沖突時需要更新的列和它們的新值

下面先插入一行數據，然后再插入一行數據（主鍵/唯一鍵沖突），如果主鍵/唯一鍵沖突就將num和telephone修改為后面的值，我們看到表中的num和telephone確實發生了改變，但是id卻沒有改變，很明顯就是在原來數據的基礎上進行修改的。

命令執行完后，會顯示影響了多少行，不同的行數也代表著不同的含義。

• 0 row affected: 表中有沖突數據，但沖突數據的值和 update 的值相等
• 1 row affected: 表中沒有沖突數據，數據被插入
• 2 row affected: 表中有沖突數據，并且數據已經被更新

我們可以通過MySQL函數來查看影響數據的行數。

SELECT ROW_COUNT();

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

1.4 替換

• 當用戶插入數據時，主鍵或者唯一鍵沒有沖突，則直接插入
• 當用戶插入數據時，主鍵或者唯一鍵如果沖突，則刪除后再插入

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

二、Retrieve

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

說明：

• SELECT [DISTINCT]：

  • SELECT 關鍵字用于從數據庫中選擇數據。
  • DISTINCT 是一個可選的關鍵字，用于返回唯一不同的值，即去除重復的記錄。

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

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

• [FROM table_name]：

  • FROM 關鍵字指定了查詢將要從哪個表中檢索數據。
  • table_name 是表的名稱。

• [WHERE …]：

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

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

  • ORDER BY 子句是可選的，用于對結果集進行排序。
  • column 指定了排序依據的列。
  • ASC 表示升序（默認），DESC 表示降序。
  • 可以根據多個列進行排序，列之間用逗號分隔。

• LIMIT …：

  • LIMIT 子句用于限制返回的記錄數。
  • 可以指定一個數字來限制結果集的大小。
  • 例如，LIMIT 10 會返回前10條記錄。

例子：

-- 創建表結構
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同學姓名',
chinese float DEFAULT 0.0 COMMENT '語文成績',
math float DEFAULT 0.0 COMMENT '數學成績',
english float DEFAULT 0.0 COMMENT '英語成績'
);-- 插入測試數據
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孫悟空', 87, 78, 77),
('豬悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('劉玄德', 55, 85, 45),
('孫權', 70, 73, 78),
('宋公明', 75, 65, 30);

2.1 SELECT 列

2.1.1 全列查詢

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

1. 查詢的列越多，意味著需要傳輸的數據量越大
2. 可能會影響到索引的使用。（后面詳細講解索引）

2.1.2 指定列查詢

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

2.1.3 查詢字段為表達式

表達式的樣式有以下幾種：

• 表達式不包含字段
• 表達式包含一個字段
• 表達式包含多個字段

2.1.4 為查詢結果指定別名

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

說明：

• column：這里指的是你想要從table_name中檢索的列的名稱。
• [AS] alias_name：AS關鍵字可以省略，用于為column指定的列名創建一個別名
• […]：這里的省略號表示你可以繼續添加更多的列名及其可選的別名

2.1.5 結果去重

2.2 WHERE 條件

2.2.1 比較運算符

2.2.2 邏輯運算符

2.2.3 案例實操

2.2.3.1 英語不及格的同學及英語成績 ( < 60 )

select name,english from exam_result where english < 60;

2.2.3.2 語文成績在 [80, 90] 分的同學及語文成績

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 數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績

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 姓孫的同學 及 孫某同學

select name from exam_result where name like '孫%'

2.2.3.5 語文成績好于英語成績的同學

select name,chinese,english from exam_result where chinese > english;

2.2.3.6 總分在 200 分以下的同學

select name,chinese,math,english,chinese+math+english as total from exam_result where chinese+math+english<200;

2.2.3.7 語文成績 > 80 并且不姓孫的同學

select name,chinese from exam_result where chinese>80 and name not like '孫%';

2.2.3.8 孫某同學，否則要求總成績 > 200 并且 語文成績 < 數學成績 并且 英語成績 > 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 的查詢

下面我創建了一個表，并向表中插入了3條數據，一條name為正常名字，一條name為空字符串，一條name為NULL。通過下面的查找來看，空串并不等于NULL，NULL代表怎么都沒有，空串代表有但字符串中沒有內容。

2.3 結果排序

2.3.1 結果排序的語法

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

說明：

• ASC 為升序（從小到大）
• DESC 為降序（從大到小）
• 默認為 ASC

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

2.3.2 案例實操

2.3.2.1 同學及數學成績，按數學成績升序顯示

select name,math from exam_result order by math asc;

2.3.2.2 按同學姓名排序顯示

select * from test order by name asc; // 升序
select * from test order by name desc; // 降序

注意：在進行比較的時候，NULL比任何值都要小

2.3.2.3 查詢同學各門成績，依次按 數學降序，英語升序，語文升序的方式顯示

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

2.3.2.4 查詢同學及總分，由高到低

select name,chinese,math,english,chinese+math+english as total from exam_result order by total desc;

2.3.2.5 查詢姓孫的同學或者姓曹的同學數學成績，結果按數學成績由高到低顯示

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

2.4 篩選分頁結果

2.4.1 篩選分頁結果的語法

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

注意：對未知表進行查詢時，最好加一條 LIMIT 1，避免因為表中數據過大，查詢全表數據導致數據庫卡死

2.4.2 案例實操

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

// 方法1
select * from exam_result limit 0,3;
select * from exam_result limit 3,3
select * from exam_result limit 6,3select * 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 的語法

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

• table_name：要更新的表的名稱。
• column = expr：要更新的列及其新值。可以指定多個列及其新值，每個列賦值之間用逗號分隔。
• [WHERE …]：可選條件，用于指定哪些行應該被更新。如果省略，則表中的所有行都會被更新，更新全表的語句一定要慎用！
• [ORDER BY …]：可選條件，ORDER BY子句可以用來指定更新操作的順序
• [LIMIT …]：可選條件，LIMIT子句用于限制更新操作影響的行數。

3.2 案例實操

3.2.1 將孫悟空同學的數學成績變更為 80 分

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

3.2.2 將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分

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

3.2.3 將總成績倒數前三的 3 位同學的數學成績加上 30 分

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

3.2.4 將所有同學的語文成績更新為原來的 2 倍

update exam_result set chinese=chinese*2;

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

四、Delete

4.1 刪除數據（DELETE語法）

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

說明：

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

DELETE特點：

1. 不僅僅可以對整表操作，還可以針對部分數據操作；
2. 不會重置 AUTO_INCREMENT 項

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

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

4.1.2 刪除整張表數據

-- 準備測試表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);-- 插入測試數據
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

下面我創建了一個表，并向表中插入數據，然后查看AUTO_INCREMENT 值為4，當我刪除表中所有元素后，又插入一個元素，再次查看AUTO_INCREMENT 的值為5，顯然AUTO_INCREMENT 的值沒有被重置。

// 刪除整張表數據
delete from 表名;

4.2 截斷表

4.2.1 截斷表（TRUNCATE語法）

TRUNCATE [TABLE] table_name

1. 只能對整表操作，不能像 DELETE 一樣針對部分數據操作
2. 實際上 MySQL 不對數據操作，所以比 DELETE 更快，但是TRUNCATE在刪除數據的時候，并不經過真正的事務，所以無法回滾
3. 會重置 AUTO_INCREMENT 項

4.2.2 實操

-- 準備測試表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);-- 插入測試數據
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');

下面我創建了一個表，并向表中插入數據，然后查看AUTO_INCREMENT 值為4，當我刪除表中所有元素后，查看表創建命令時，發現沒有了AUTO_INCREMENT 選項，然后又插入一個元素，再次查看AUTO_INCREMENT 的值為2，顯然AUTO_INCREMENT 的值被重置。

五、插入查詢結果

5.1 插入查詢結果語法

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

說明：

• table_name: 指定了要插入數據的表。
• [(column [, column …])]: 這是可選的列列表，用于指定目標表中你想要插入數據的列。如果提供了列列表，SELECT 語句返回的列必須按照相同的順序與這些列匹配。如果沒有提供列列表，那么SELECT 語句返回的列將按順序對應于目標表中的列（前提是列的數量和類型兼容）。
• SELECT …: 這部分是一個SELECT語句，用于指定要從哪個表（或哪些表）中選擇數據，以及選擇哪些列的數據。SELECT語句可以包含任何有效的查詢條件，例如WHERE子句來過濾數據。

5.2 實操

刪除表中的的重復復記錄，重復的數據只能有一份。

下面我們創建了一個表duplicate_table，然后再向表中插入一些重復數據。現在我想將表中的重復數據只留一份，其余的重復數據全部刪除。需要注意的是，我們需要將原本進行修改，而不是顯示去重后的結果。

-- 創建原數據表
CREATE TABLE duplicate_table (id int, name varchar(20));-- 插入測試數據
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

這里我就來解決這個問題，首先我創建一個與duplicate_table表結構相同的空表no_duplicate_table，然后將duplicate_table去重后的數據插入到no_duplicate_table中，然后對兩個表的表名進行重命名，使表no_duplicate_table變為表no_duplicate_table。

六、聚合函數

6.1 常見聚合函數

6.2 案例 實操

6.2.1 統計班級共有多少同學

select count(*) from exam_result;

6.2.2 統計本次考試的數學成績分數個數

select count(math) from exam_result;

6.2.3 統計數學成績總分

select sum(math) from exam_result;

6.2.4 統計平均總分

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

6.2.5 返回英語最高分

select max(english) from exam_result;

6.2.6 返回 > 70 分以上的數學最低分

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

七、group by子句的使用

7.1 group by子句的語法

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

特點：

• 分組的目的就是為了進行分組后，方便的進行聚合統計。
• 指定列名，實際分組使用該列不同的行數據進行分組的。
• 分組條件，在組內一定是相同的，所以可以聚合壓縮。

7.2 案例實操

準備工作，創建一個雇員信息表（來自oracle 9i的經典測試表）

• 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 '雇員領導編號',`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);

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

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

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

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

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

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

7.3 having VS where

• WHERE：在聚合前過濾行（對原始數據生效）。
• HAVING：在聚合后過濾分組（對 GROUP BY 結果生效）。

八、OJ練習

8.1 SQL233 批量插入數據

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 找出所有員工當前薪水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 查找入職員工時間升序排名的情況下的倒數第三的員工所有信息

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

8.5 SQL206 查找薪水記錄超過15條的員工號emp_no以及其對應的記錄次數t

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

8.6 182. 查找重復的電子郵箱

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

8.7 595. 大的國家

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

8.8 177. 第N高的薪水

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

8.9 面試題：SQL查詢中各個關鍵字的執行先后順序

SQL查詢中各個關鍵字的執行先后順序 from > on> join > where > group by > with > having > select> distinct > order by > limit

結尾

如果有什么建議和疑問，或是有什么錯誤，大家可以在評論區中提出。
希望大家以后也能和我一起進步！！🌹🌹
如果這篇文章對你有用的話，希望大家給一個三連支持一下！！🌹🌹