目錄

1. Create

1.1 語法

1.2 單行數據 + 全列插入

1.3 多行數據 + 指定列插入

1.4 插入數據否則更新數據

1.5 替換

2. Retrieve

2.1 SELECT 列

2.1.1 全列查詢

2.1.2 指定列查詢

2.1.3 查詢字段為表達式

2.1.4 為查詢結果指定別名

2.1.5 結構去重

2.2 WHERE 條件

2.2.1 運算符介紹

2.2.2 案例

2.3 對篩選結果排序顯示?

2.3.1 案例

2.4 分頁顯示結果

3. Update

3.1 案例

4. Delete

4.1 案例

4.2 截斷表

5. 插入查詢結果

6. 聚合函數

6.1 案例

7. group by 子句的使用

7.1 案例

---

????????CRUD: create（創建），retrieve（讀取），Update（更新），delete（刪除）

1. Create

1.1 語法

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

? ? ? ? 插入數據的時候，value_list 中列的數量和順序要和 前面定義表([(column [, column] ...)]?)的列的一致。

? ? ? ? 這里先創建一張學生表，用于下列實驗。?

1.2 單行數據 + 全列插入

? ? ? ? 如果定義表中沒有指定列，表示全列插入。也可以在定義表中指定全部列表示全列插入。

1.3 多行數據 + 指定列插入

1.4 插入數據否則更新數據

? ? ? ? 由于在插入的時候會發生主鍵或者唯一鍵沖突，可以選擇性的進行同步更新操作：

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

? ? ? ? 如下圖中的語句，當插入的數據沒有主鍵或者唯一鍵沖突的時候插入數據，如果有主鍵或唯一鍵沖突則將原數據更新為 update 之后的數據。

1.5 替換

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

? ? ? ? 如果插入的數據與表中的數據沒沖突，則插入該數據；如果插入的數據與表中的數據有沖突，則將表中數據替換為該數據。?

2. Retrieve

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...DISTINCT：表示去重。
{* | {column [, column] ...}：*表示通配符，column表示要查看的列。
WHERE ...：后面跟篩選條件。
ORDER BY column [ASC | DESC], ...：按照 column 升序或降序排序。
LIMIT ...：限制條數。

? ? ? ? 下列是后續實驗使用的表的結構以及其數據。?

2.1 SELECT 列

2.1.1 全列查詢

? ? ? ? 通常情況下不建議使用 * 進行全列查詢，因為查詢的列越多，意味著需要傳輸的數據量越大，并且會影響到索引的使用。?

2.1.2 指定列查詢

? ? ? ? 指定列的順序不需要按定義表的順序來寫。可以指定想要查詢的若干列進行查詢。

2.1.3 查詢字段為表達式

? ? ? ? （1）表達式不包含字段

? ? ? ? （2）表達式包含一個字段

? ? ? ? （3）表達式包含多個字段?

2.1.4 為查詢結果指定別名

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

? ? ? ? as 可以省略。?

2.1.5 結構去重

2.2 WHERE 條件

2.2.1 運算符介紹

? ? ? ? 比較運算符：?

? ? ? ? '=' 不能用于判斷是否等于 NULL。?

? ? ? ? between 的區間是左閉右閉的。?

? ? ? ? like 前可以加 not 表示取反。

? ? ? ? 邏輯運算符：

2.2.2 案例

? ? ? ? （1）查看英語低于60分的同學。

? ? ? ? （2）查看語文成績在 [80, 90] 分的同學。?

? ? ? ? （3）數學成績是 98 或 99 的同學。?

? ? ? ? （4）篩選出姓孫的同學，孫某以及孫某某。

? ? ? ? （5）篩選語文成績好于英語成績的同學。?

? ? ? ? （6）篩選總分在 200 分一下的同學。?

? ? ? ? 從上圖可以看到，語句 1 可以正常執行，而語句 2 則會失敗。原因是因為 select 語句是有執行順序的。首先執行的是 from 表示從那個表中找，其次是 where 子句，表示篩選的條件，最后在是列的顯示。上述的語句 2 中，在執行 where 的時候還沒有進行重命名，所以使用 total 充當篩選條件是不可行的，其次，重命名也不能在 where 子句中進行。

2.3 對篩選結果排序顯示?

? ? ? ? order by 的執行順序在顯示列之后，所以可以使用重命名的列名進行排序。

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

? ? ? ? 注：沒有 order by 子句查詢出來的順序是未定義的。 NULL 視為比任何值都小。

2.3.1 案例

? ? ? ? （1）按數學成績升序或降序顯示同學及其數學成績。

? ? ? ? （2）?按數學降序，英語升序，語文升序的方式顯示各科成績。

? ? ? ? 上述語句表明的是，當數學相等的時候，按照英語成績升序排列，當數學成績相等并且英語成績相等的時候，按照語文成績相等來排。?

2.4 分頁顯示結果

-- 起始下標為 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 語句，避免因為表中數據過大，查詢全表數據導致數據庫卡死。

3. Update

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

? ? ? ? 對查詢到的結果進行列值更新，如果沒有 where 子句會對全表進行修改。

3.1 案例

? ? ? ? （1）將孫悟空的數學成績修改為 80 分。

? ? ? ? （2）將總分倒數前 3 的同學數學加 30 分。?

4. Delete

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

? ? ? ? 對查詢到的結果進行刪除。

4.1 案例

? ? ? ? （1）刪除孫悟空的成績。

? ? ? ? （2）刪除整張表的數據。?

? ? ? ? 整張表的數據刪除之后，并不會重置 auto_increment 項。

4.2 截斷表

TRUNCATE [TABLE] table_name;

? ? ? ? truncate 也是清空整張表的數據，只能對整張表操作，不能想 delete 一樣刪除部分數據。會重置 auto_increment 項。truncate 不對數據操作，直接把表清空，所以比 delete 快，但是 truncate 在刪除數據的時候，并不會記錄到日志當中，也不會經過真正的事務，所以無法回滾。

5. 插入查詢結果

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

? ? ? ? 案例：刪除表中的重復記錄。

? ? ? ? （1）創建原始數據表并插入測試數據。

? ? ? ? （2）創建一張空表，結構和原表一樣。?

? ? ? ? （3）將原表去重的數據插入到新表中。?

? ? ? ? （4）將原表重命名為其他，然后將新表重命名為原表。?

6. 聚合函數

6.1 案例

? ? ? ? （1）統計學生數學成績的平均值。

? ? ? ? （2）返回 > 70 分以上的最低數學成績。

7. group by 子句的使用

? ? ? ? 分組的目的是為了進行分組之后，方便進行聚合統計。

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

7.1 案例

? ? ? ? 創建一個雇員信息表（來自oracle 9i的經典測試表），EMP 員工表，DEPT 部門表，SALGRADE 工資等級表。

? ? ? ? （1）顯示每個部門的平均工資和最高工資。?

? ? ? ? （2）顯示每個部門的每種崗位的平均工資和最低工資。?

? ? ? ? （3）顯示平均工資低于 2000 的部門和它的平均工資。

? ? ? ? having 對聚合統計之后的結果進行條件篩選。?

知識點1：? ? ????

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

?