【MySQL】MySQL表的增刪改查（CRUD） —

MySQL表的增刪改查（CRUD）

1. 新增（Create）/插入數據?

1.1 單行數據 + 全列插入 insert into 表名 values(值, 值......);

1.2?單行數據 + 指定列插入

1.3?多行數據 + 指定列插入

1.4?關于時間日期（datetime）類型的數據插入

2. 查詢（Retrieve）

2.1 全列查詢（select * from 表名;）

2.2 指定列查詢

2.3 查詢字段(列)為表達式

2.4 指定別名查詢（as 別名）

2.5 去重查詢（distinct）

2.6 查詢結果排序（order by）

2.7?條件查詢（where 條件）

基本條件查詢

and與or

范圍查詢（between and），(in)

模糊查詢? （like）

null 的查詢? （<=>，is noll）

2.8 分頁查詢（limit）

3. 修改（Update）

4. 刪除（Delete）

總結語句

MySQL表的增刪改查（CRUD）

這個章節是mysql最核心的部分。這里涉及到的SQL都是工作中最常用到的，這些東西掌握了，覆蓋日常工作80%＋以上的內容了。

CRUD:

注釋：在SQL中可以使用“ --空格+描述 ”來表示注釋說明
CRUD 即增加(Create)、查詢(Retrieve)、更新(Update)、刪除(Delete)四個單詞的首字母縮寫

1. 新增（Create）/插入數據?

1.1 單行數據 + 全列插入 insert into 表名 values(值, 值......);

into可以省略

其中括號里值的個數，類型，順序要和表頭結構匹配，
SQL沒有字符型， ' ' 和 " " 都可以表示字符串

錯誤的插入方式：

個數不匹配

類型不匹配

此處還有一種錯誤，數據庫字符集沒有正確配置引起的，數據庫不做任何修改，默認情況下創建的數據庫字符集是“拉丁文"字符集，不能表示中文。MySQL 5.7 及更早版本。

此時要做的事情，讓數據庫的字符集和你輸入的文字的字符集匹配。如果你輸入的文字是utf8，就得在創建數據庫的時候設置成utf8。
刪除該數據庫重新創建數據庫，在創建數據庫的時候指定對應字符集。一般來說，咱們的終端是utf8的，但是也可能有同學是gbk

創建了數據庫之后也能更改字符集，但是比較麻煩。

1.2?單行數據 + 指定列插入

只給某些指定的列進行插入數據，其他的列將按照默認值的方式填充。
例如：插入一行，指定插后兩列

1.3?多行數據 + 指定列插入

一次插入多行數據，values后面寫多個() 通過 , 隔開。

例如：插入兩行，指定插后兩列。

這里的提示，就是反饋效果。客戶端給服務器發起插入請求，服務器要返回這次插入是否成功了。
一行受到影響就是成功插入了一行，兩行受到影響就是成功插入了兩行。可能插入10行，但是顯示5行成功插入。

一次插入多行記錄，相比于一次插入一行，分多次插入，要快不少。

mysql是一個客戶端服務器結構的程序

分多次插入，客戶端與服務器交互多次，此處的成本不僅僅是網絡交互，服務器處理數據插入自身也有一系列成本（加鎖等操作）；一次性插入，只需客戶端與服務器交互一次，所以效率會更高一些。
雖然一次插入多行記錄體積比較大，但對效率上影響比較小；而三次網絡交互，影響效率比較大。所以比較推薦使用一次插入多行記錄。

1.4?關于時間日期（datetime）類型的數據插入

插入時間的時候，是通過固定格式的字符串來表示時間日期的。例如： '2000-06-26?8:15:26'

假設我現在想把這個時間日期設置成當前時刻，sql提供了一個特殊的函數now()

2. 查詢（Retrieve）

2.1 全列查詢（select * from 表名;）

把表中的所有行和所有列都查詢出來。?* 表示所有的列，這種特殊含義的符號，計算機中叫做“通配符”。

這里的表是查詢出來之后，服務器通過網絡把這些數據返回給客戶端的，并且在客戶端以表格的形式，打印出來。

select * 操作，其實也是一個危險操作。當前階段，怎么寫都沒事，為了方便觀察。在公司中，針對數據量比較大的生產環境不能隨便select *?

mysql是一個"客戶端–服務器"結構的程序。通過網絡進行通信。
客戶端這里進行的操作，就都會通過請求發送給服務器。服務器查詢的結果也就會通過響應返回給客戶端。

如果數據庫當前這個表中的數據特別多，就可能會產生問題：

讀取硬盤。把硬盤的IO給跑滿了，此時程序的其他部分想訪問硬盤，就會非常慢。
操作網絡。可能把網卡的帶寬也跑滿，此時其他客戶端想通過網絡訪問服務器，也會非常慢。

這樣的擁堵，就可能導致客戶端無法順利訪問到數據庫。進一步的也就對整個系統造成影響(相當于數據庫服務器掛了)。
得出結論，執行select * 操作，可能很危險，如果數據量有幾億，幾十億操作就麻煩了，瞬間吃滿硬盤帶寬和網絡帶寬，就可能導致其他程序無法使用硬盤或者使用網絡。
當前階段，怎么寫都沒事，為了方便觀察。在公司中，針對數據量比較大的生產環境(也叫線上環境)不能隨便select *?

2.2 指定列查詢

按需進行查詢

指定列的順序不需要按定義表的順序來。
注意這里類型沒括號

2.3 查詢字段(列)為表達式

查詢過程中，可以做一些簡單的加減乘除之類的運算。
會把當前表的每一行對應列進行計算，是進行列和列之間的運算
null參與各種運算，結果還是null

可以在查詢的時候，針對分數進行變換。比如讓查詢的 math 成績都在原來基礎上＋10分

上述這樣的查詢，數據庫服務器硬盤的數據，是否發生了改變？并沒有改變。再次查詢math，此時的結果是＋10之前的數據。

msyql是一個“客戶端-服務器”結構的程序！！！
用戶在客戶端輸入的sql，通過請求發送給服務器，服務器解析并執行sql把查詢的結果，從硬盤讀取出來通過網絡響應返回給客戶端，客戶端把這些數據以臨時表的形式展示出來。只是在客戶端這里顯示一下的臨時表。（顯示一下就銷毀了）和服務器那邊的硬盤上的表沒啥關系。
上上圖中為什么會出現108.0這樣的數字，出現在臨時表里和原始的表沒啥關系。decimal(3,1)這個類型是原始的表的類型不能約束臨時表。
那臨時表的類型是什么：就是一個單純的 double / decimal不是一個帶有有效數字的版本了。

查詢計算每個同學的總成績

表達式查詢，是讓列和列之間進行運算，而不是行和行之間，(后面還會學習一個聚合查詢，是行和行之間運算)
按照表達式查詢，臨時表的列名和表達式一樣的，很多時候表達式的含義是不直觀的。如求平方差等，很復雜的計算。所以就引出了：查詢的時候給列/表達式指定別名(給表也能指定別名)

2.4 指定別名查詢（as 別名）

查詢的時候指定別名，指定別名相當于是起了個"小名"/外號，更方便的來理解含義。
as 別名，可以是針對表達式，列，表名

as 關鍵字，as可以省略，但是不建議。math + chinese + english? total
一個不留神，total當成了要＋的一個列或者當成前面有個，total是一個單獨的列。
查詢結果的臨時表中，列名就是剛才的別名。

2.5 去重查詢（distinct）

使用 distinct 關鍵字對某列/多個列/表達式?數據進行去重，把重復的行只保留一個。
distinct 指定多個列的時候，要求這些列的值都相同，才視為重復。

2.6 查詢結果排序（order by）

order by 子句，按照?某些列/表達式/別名進行排序。以行為單位。
asc升序，desc降序，descend的縮寫不是describe。如果省略默認升序排序。

mysql是一個客戶端服務器結構的程序，
把請求發給服務器之后，服務器進行查詢數據，并且把查詢到的結果進行排序之后，再組織成響應數據返回給客戶端。
排序仍然是針對臨時數據來展開的，此處的排序，不影響原有數據在mysql服務器上存儲的順序。

如果一個sql 不加 order by 此時查詢的結果數據的順序是?不確定的/無序的/未定義的/不可預期的，永遠不要依賴這個順序 —— 亂紀元。在代碼中不能依賴上述的順序來展開一些代碼邏輯。
order by 指定的列，如果 select 的時候沒有把這一列顯示出來，也不影響排序。
order by 還可以針對表達式以及別名進行排序。

null數據排序，視為比任何值都小。升序出現在最上面，降序出現在最下面。null參與各種運算，結果還是null
可以對多個字段進行排序，order by后面寫多個列，使用 , 來分割開。排序優先級（主次關系）隨書寫順序。

例如，查詢同學各門成績，先按照數學成績降序，如果數學成績相同，再按照語文降序的方式顯示。

2.7?條件查詢（where 條件）

指定具體的條件，按照條件針對數據進行篩選。
遍歷這個表的每一行記錄，把每一行的數據分別帶入到條件中，如果條件成立，這個記錄就會被放入結果集合中。如果條件不成立，這個記錄就pass
sql通過一系列的運算符來表示條件。

比較運算符：

運算符	說明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的結果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的結果是 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)，結果才是 TRUE(1)
OR	任意一個條件為 TRUE(1), 結果為 TRUE(1)
NOT	條件為 TRUE(1)，結果為 FALSE(0)

=? 在 sql 表數據條件查詢中是比較相等，在sql 表數據修改中又表示賦值。sql 中沒有 ==。
and 相當于 &&， or相當于||， not相當于 !
通過where子句，搭配上條件表達式，就可以完成條件查詢。

where條件可以使用表達式，但不能使用別名。
and的優先級高于or，在同時使用時，需要使用小括號()包裹優先執行的部分。

基本條件查詢

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

where子句搭配上條件查詢，其實相當于，針對數據庫的表進行遍歷，取出每一行數據，把數據代入到條件中看條件是否符合，如果是真，這個記錄就保留作為結果集的一部分，如果是假，這個記錄就pass，繼續下一條。
這里如果數據量過大，服務器是不是要崩了。
數據量大了，硬盤讀寫開銷,，是避免不了的。但是由于有條件限制，還有很多數據都沒有作為結果集。最終給客戶端返回的數據量是更可控的。(具體是否可控，也取決于條件是咋寫的，至少比select * 好不少)

查詢語文成績好于英語成績的同學，拿兩個列進行比較

查詢總分在 200 分以下的同學，使用表達式來作為條件

這里null要注意了，null參與各種運算，結果都是null。?null < 200 結果是null，所以條件不成立 false。

條件為表達式以及別名，會出現問題：

此處total別名不能作為where條件，和當前sql的執行順序有關，這也是mysql對于語法規定的一部分。執行where的時候，total還處于"未定義"的狀態。

寫下一個sql不是從前往后的執行，執行順序是有特定的規則的。

在上述代碼中，select條件查詢執行的順序：

遍歷表中的每個記錄。
把這一行當前記錄的值，帶入到 where 的條件中，根據條件進行篩選。
如果這個記錄條件成立，就要保留，進行列上的表達式計算。
如果有 order by 會在所有的行都被獲取到之后(表達式也算完了)，再針對所有的結果進行排序。

實現sql解析引擎的時候，其實是完全可以做到把這里的別名預先的定義好，然后再執行123，保證執行到where 的時候也能訪問到別名。但是mysql 當前沒有這樣實現，可能是歷史遺留問題吧。

and與or

查詢語文成績大于80分，且英語成績大于80分的同學

查詢語文成績大于80分，或英語成績大于80分的同學

觀察 AND 和 OR 的優先級：sql中 and 的運算符優先級更高。

范圍查詢（between and），(in)

between...and...? 約定的是一個?左閉右閉區間 (包含兩側邊界)
大部分區間是采用左閉右開的。例如：String類的有些方法，就是按照區間來指定的 substring 等方法。List類的有些方法也是按照區間subList 等方法

查詢語文成績在 [80, 90] 分的同學及語文成績：

使用and也能實現，這兩種寫法本質一樣，沒啥區別。

in??使用in來表示一個"離散"的集合

查詢數學成績是 78?或者 98?或者 84?或者 85?分的同學及數學成績

使用or也能實現，這兩種寫法本質一樣，沒啥區別。

模糊查詢? （like）

like 模糊匹配(模糊匹配字符串)，不要求字符串完全相同，只要滿足一定的規則就可以了。搭配通配符使用，通配符就是一些特殊的字符，能夠表示特定的含義。
?通配符，類似于撲克中"會兒”或者叫做"賴子"的特殊牌，可以用來代替任意的點數和花色。
正則表達式(也是模糊匹配)，通過特殊符號來描述一個字符串的特征用這個來匹配字符串。

like功能比正則表達式簡單很多，只支持兩個用法：

使用 % 代表任意N個字符（包含0個字符）
使用 _ 代表任意1個字符

like '孫%’ 查詢孫開頭的
like '%孫’ 查詢孫結尾的
like '%孫%' 查詢包含孫的

此處模糊查詢的功能是有限的，在計算機中，進行模糊匹配字符串還有“正則表達式" 這樣的方式來進行實現。
這里mysql中不用正則表達式模糊匹配，而是用like模糊匹配，是因為正則表達式，匹配的效率是很慢的，mysql本身也不快，慢上加慢。

null 的查詢? （<=>，is noll）

null和其他數值進行運算，結果還是null，null結果在條件中，相當于false
null = null => 結果還是 null => false? 所以沒有查詢結果。
sql不區分大小寫的，NULL/null表示表格里的這一項是空著的。
<=> 使用這個比較相等運算，就可以處理null的比較。可以針對兩個列比較的。

還有一種方法 is null ，意思為：是null。只能看到一個列?

2.8 分頁查詢（limit）

使用select *這種方式查詢，是比較危險的，需要保證一次查詢，不要查出來的東西太多。

有的時候數據非常多，一次全都顯示出來，會影響到效率，也會不方便用戶去看。

limit 可以限制查詢最多能查出來多少個結果。

limit 可以分別搭配 where 和 order by 使用，
where 或者 order by 是在 limit子句前面書寫的，一般limit子句是在整個sql語句最后書寫的。

limit還可以搭配 offset，聲明從哪一條開始查詢(從0開始計數)
解讀，limit 3 offset 6; 從第6條記錄開始，查詢3條記錄。
limit 和 offset 是用下標計算的。offset意為偏移量，從0開始
limit 限制的是最多是多少數據，小于或等于limit不受影響。

limit 3 offset 6; 等價于 limit 6,3;(不太推薦這么寫)，容易混淆。

只查詢操作，查詢到的結果都是臨時表/臨時數據，對這些臨時表/臨時數據進行修改，并不會影響在mysql服務器硬盤上存儲的原始數據。例如：列的表達式計算，去重查詢，查詢結果排序。
只要是查詢，都不會影響原始數據。

3. 修改（Update）

where 條件，是限制這次操作具體要修改哪些行的數據。
這里的 = 又相當于賦值了，注意跟 where 條件中的 = (表示比較相等) 區別。

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

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

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

總成績：表達式。? 倒數：排序 order by。? 前三：limit

這里必須寫作math = math + 30 不能寫作 math += 30 sql中沒有這個種語句。
這里‘唐三藏’的數學成績加30變成128.0，超出了表中該字段類型設置的范圍decimal(3,1)，失敗報錯，此時修改不會生效。

將所有同學的語文成績更新為原來的 2 倍。update后面不寫任何條件，就是針對所有行進行修改。

同樣也是超出了表中該字段類型設置的范圍。

改為將所有同學的語文成績更新為原來的 0.5?倍。?

如上如所示：matched：8 所有行數就是8，找到了 8行數據匹配；Changed顯示只改變了7行；Warnings警告 2行，意為代碼有問題，但是問題不大，還能接受。
show warnings 查看警告原因：發現發生截斷了，小數點后位數不夠了，只能發生截斷。是發生在第二行和第五行的 chinese 列，查看修改前的情況，

這一列字段的類型的范圍是decimal(3,1)，?3位有效數字，小數點后保留1位。超出了這個范圍，按照四舍五入的方式把數據截斷了。
只修改了7行原因是：第8行的‘唐三藏’的語文成績是null，null參與各種運算，結果還是null。相當于沒有修改。

修改如果修改后超出范圍，會失敗報錯；如果修改后小數點位過長了，它會截斷，會有警告信息。

update操作非常危險

撤回不了。
測試只能測試個大概，有可能你的bug是一個小概率觸發的情況。

4. 刪除（Delete）

delete 刪除記錄，把符合條件的行，從表中刪除掉。

刪除孫悟空同學考試成績

這里就是把條件匹配出來的結果，都刪掉了

刪除整張表數據。不指定任何條件，就是刪除整個表。

drop table是刪除了表，也刪除了表里的記錄。
delete from 只刪除了表中的記錄，表還在(空表)

truncate也能清空表的內容，和delete from差不多。

delete from 是一條一條刪，刪的慢。truncate 直接一下就刪沒了。

刪除的時候可以取消，但沒有撤銷操作。如果執行的sql時間很長，隨時可以按ctrl +c取消。

delete和update都是很危險的操作。delete 一旦刪除的條件沒設置好，就可能把不該刪除的給刪掉了。

這里的修改/刪除是持久生效的，都會影響到數據庫服務器硬盤中的數據。

總結語句

對表中數據進行操作

新增

insert into 表名 values (值, 值....); 插入

查詢：

select * from 表名; 查詢
distinct 去重查詢
order by 子句; 查詢結果排序
where 條件查詢
and與or 和與或
between and 范圍條件? in ‘離散’ 集合
like 模糊匹配
limit 分頁查詢

修改

update 表名 set 列名 = 值, 列名 = 值..... where 條件;? ?

刪除

delete from 表名 where 條件;? ? 刪除