MySQL | 數據庫的管理和操作【表的增刪改查】

系列文章目錄

第一章：MySQL | 數據庫的管理和操作(基本介紹)

第二章：MySQL | 數據庫的基本操作和表的基本操作

第三章：MySQL | 數據庫的表的增刪改查

第四章：MySQL | 數據庫的表的增刪改查【進階】【萬字詳解】

本節(jié)目標

• CRUD : Create, Retrieve，Update，Delete

• 新增數據

• 查詢數據

• 修改數據

• 刪除數據

• 注釋：在SQL中可以使用“–空格+描述”來表示注釋說明

• CRUD 即增加(Create)、查詢(Retrieve)、更新(Update)、刪除(Delete)四個單詞的首字母縮寫。

一、新增（Create）

語法：

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

案例：

insert into student valuse (1,'張三');

• 這里的個數，類型，順序和表頭結構匹配
• SQL沒有字符類型，‘’和“”都可以表示字符串
• 這里數據庫在插入的有的時候是報錯，這是因為在數據庫不做任何修改，默認情況下創(chuàng)建數據庫的字符集是拉丁文字符集，不能表示中文

• 此時我們要做的事情就是讓我們的數據庫，字符集和你輸入的時候設置生utf-8

create database test charset utf8;

• 現在就可以了，一般來說，我們的終端是utf8的，但是有的同學是gbk的；
• 其中utf8正常保存數據是沒事的，帶上表情，就有可能出錯了，而utf8mb4是更完整的utf8

指定列插入 + 全列插入

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

insert into student (name,gender) values('張三','男');

此時：values后面的內容就是和前面的（）的內容匹配

• 要想看到結果，就要使用查詢語句,
• 可以看到，id是空著的，別的都有，這就是指定列插入
• 這里的這個語句下面詳細介紹

select * from student;

多次插入

• 這里也可以一次性插入多條數據，那就是一次插入

insert into student values(2,'李四','男'),(3,'王五','男');

• 一次插入n個記錄，比一次插入一個數據，分n次插入，效率要高

時間日期類型，數據的插入

create table homework (id int ,creatTime datetime);

• 插入時間的時候，是通過特定格式的字符串來表示時間日期的，如‘2023-02-17 21:25:00’

insert into homework values(1,'2023-02-17 21:25:00');

• 那么我現在就想把這個時間日期設置成當前時刻，咋辦？sql提供了一個特殊的函數，now()

insert into homework values(1,now());

二、查詢（Retrieve）

全列查找

全列查找（查找整個表的所有行，所有列）

select * from 表名

• 這里面的*表示所有的列，這種特殊含義的符號，計算機中叫做【通配符】

• 但是這里執(zhí)行select *操作，肯會非常危險
• 如果數據量就幾百幾千，就沒有什么事，如果數據量有幾億，幾十億select *操作就很麻煩了，這個操作會瞬間吃滿硬盤帶寬和網絡帶寬，就可能導致其他程序無法使用硬盤或者使用網絡

那么怎么辦呢？有辦法嗎？有的！

當你需要操作的時候，一定要拉上一個人和你一起操作O(∩_∩)O哈哈~

指定列查詢

-- 指定列的順序不需要按定義表的順序來 
select 列名,列名...from 表名;

• 我們需要哪些列就查詢哪些列

select name,gender from student;

表達式查詢

• 查詢過程中，可以做一些簡單的運算~~
• 這個是進行列和列之間的運算

create table exam_result (id int,name varchar(20),chinese decimal(3,1),math decimal(3,1),english decimal(3,1));

insert into exam_result (id,name,chinese,math,english) values 
	(1,'唐三藏', 67, 98, 56), 
	(2,'孫悟空', 87.5, 78, 77), 
	(3,'豬悟能', 88, 98.5, 90), 
	(4,'曹 孟德', 82, 84, 67), 
	(5,'劉玄德', 55.5, 85, 45), 
	(6,'孫權', 70, 73, 78.5), 
	(7,'宋公明', 75, 65, 30);

• 接下來就看看查詢字段為表達式
• 可以在查詢的時候，針對分數進行變換；比如讓查詢的math成績都在原來的基礎上 + 10分；

select name,math + 10 from exam_result;

• 這個結果都是在原有的分數上 + 10的

• 上述這樣的查詢，數據庫服務器硬盤的數據，是否發(fā)生了改變？

• 如果我們再次查詢math，此時的結果是 + 10之前的還是 + 10之后的呢？

我們再次操作一下~~

小結一下：

• 我們要要牢記一句話，mysql是一個“客戶端-服務器”結構的程序?。?！
• 用戶在客戶端輸入的sql，通過請求發(fā)送給服務器，服務器解析并執(zhí)行sql，把查詢的結果從硬盤讀取出來，通過網絡響應還給客戶端，客戶端把這些數據以臨時表的形式展現出來
• 這只是在客戶端這里顯示一下臨時表，和服務器那邊的硬盤上的表沒啥關系

我們還可以查看總成績，那么怎么查看呢？接著往下看~~

select name,math + chinese + english from exam_result;

• 表達式查詢是讓列和列之間進行運算而不是行和行之間

后面還會學一個聚合查詢，是行和行之間的運算~~

這就引出了一個查詢的時候指定別名~~

指定別名查詢

• 指定別名，就相當于是 起了個“小名”更方便的來理解含義
• 我們可以使用as關鍵字來查詢
• 下面進行演示：

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

• 可以看到，我們查詢出來的名字發(fā)生了變化~~

• 其中 as可以省略，但是不建議~~

去重

• 我們可以使用distinct關鍵字進指定列進行去重，把重復的行只保留一個
• 接著看~~

select distinct math from exam_result;

• distinct指定多個列的時候，要求這些列的值都相同，才視為重復

查詢結果排序

• 使用了order by子句，指定某些列進行排序~~，排序可能是升序，也可能是降序

• order by 是可以根據多個列進行排序~~

• 比如說按照數學成績進行升序排序

select name,math from exam_result order by math;

• 對于MySQL來說，如果一個sql沒有指定order by此時查詢的結果的順序，是不可預期的

• 代碼邏輯中，不能依賴這里的查詢順序的

剛剛是升序排序的，那么怎么降序排序呢？只需要在后面加上desc就可以了

select * from exam_result order by math desc;

• 此處的desc是descend單詞的縮寫,不是describe

• 還可以使用asc表示升序排序，但是省略不寫默認就是升序

• 還可以指定多個列來排序，多個列之間使用，來分割 ，這個列越靠前，就是越關鍵的排序依據~~

• 先按照第一列排序，如果第一列的值相同了，再按照第二列排序

select * from exam_result order by math desc,chinese desc;

條件查詢

在查詢的時候指定篩選條件

• 需要先描述條件，怎么描述條件呢？
• sql通過一系列的運算符來表示條件~~

比較運算符：

邏輯運算符：

• 通過where子句，再搭配上條件表達式，就可以完成條件查詢~~

select * from exam_result where english < 60;

• 我們來看一下

• 其中的where english < 60相當于針對數據庫的表進行遍歷，取出每一行數據，把數據代入條件中，看條件是否符合
• 如果是真，這個記錄就保留，作為結果集的一部分
• 如果是 假，這個記錄就pass，下一條繼續(xù)~~

• 條件查詢，可以直接拿兩個列進行比較~~

select * from exam_result where chinese > english;

• 這里和上面的where english < 60一樣，都是取出每一行數據，把數據代入條件中，看條件是否符合

• 條件查詢，可以使用表達式來作為條件

select * from exam_result where chinese + english + math < 200;

• 這樣寫是不是不明顯，那我們還可以下面這樣寫

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

• 這樣寫是不是更加直觀~~
• 還記得嗎？有一個as的關鍵詞~~
• 那么是不是可以下面這樣寫？

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

• 可以看到是報錯了~~
• 在上面的代碼中，寫下一個sql，不是從前往后的執(zhí)行，執(zhí)行順序是有特定的規(guī)則的
  執(zhí)行規(guī)則：

1. 遍歷每一行
2. 把這一行代入where的條件中
3. 符合條件的結果，再根據select這里指定的列，再進行查詢/計算

注意：

此處的total別名不能作為where條件，和當前sql的執(zhí)行順序有關，當然，這也是mysql對于語法規(guī)定的一部分~~

• 所以只能寫成原有的表達式~

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

select * from exam_result where chinese > 80 and english > 80;

select * from exam_result where chinese > 80 or english > 80;

• 如果一個where中既存在and有存在or，那么它們的優(yōu)先級是先執(zhí)行and后執(zhí)行or

范圍查詢

BETWEEN … AND …

• 約定的一個前閉后閉區(qū)間（包含兩側邊界）

select * from exam_result where chinese >= 80 and chinese <= 90;

• 或者也可以寫成下面的代碼，這兩種寫法本質上是一樣的~~

select * from exam_result where chinese between 80 and 90;

select * from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

• 或者也可以這樣寫：

select * from exam_result where math in(58,59,98,99);

模糊查詢

like

• 模糊匹配，不要求元素完全相同，只要滿足一定的規(guī)則就可以了

• like 功能比正則表達式簡單的多，

只支持兩個用法：

1. 使用%代表任意0個字符或者N個字符
2. 使用_代表任意1個字符

• 列如：
• 查詢姓孫的同學

select * from exam_result where name like '孫%';

• 可以看到查詢出來了，列如用_來模糊查找：

• 還可以下面這樣寫~~
• like '%孫'查詢結尾的
• like'%孫%'查詢包含孫的

• mysql效率比較低的，很容易稱為性能瓶頸，模糊匹配更是比較低效的寫法，如果這里支持的功能更復雜，反而更拖慢數據庫的效率~~
• 使用數據庫，就算優(yōu)化出來，也達不到要求，我們的做法是不用數據庫，數據都放在內存中搜索~~

NULL 的查詢

我們要想查詢?yōu)榭眨╪ull）的值，那么怎么查詢嗎？是下面這樣嗎？

select * from exam_result where chinese = null;

為什么會出現這樣的情況？

• null和其他數值進行運算，結果還是null；
• null結果在條件中，想當于false
• 所以null = null 結果等于null --> false;

針對這樣的問題怎么解決呢？

• 在sql里提供了這樣的一個比較相等的<=>,使用這個比較相等運算，就可以處理null的比較~
• 可以看到就可以查詢成功了

select * from exam_result where chinese <=> null

• 或者也可以下面這樣寫，對空值進行判定~~

select * from exam_result where chinese is null;

分頁查詢

這里所用到的關鍵字是limit

select * from exam_result limit 3;

• 可以看到后面這里加上3，就是只顯示3條

• limit還可以搭配offset，聲明從那一條開始查詢（從0開始計數）
• 下面進行代碼演示~~

select * from exam_result limit 3 offset 3;

• 還有一種寫法：limit 3 offset 6等價于 limit 6,3

• 這種寫法不太推薦，很容易混淆~~

• limit 這個東西是可以和前面的那些查詢搭配使用的~~

列如：查詢總分前三名的同學的信息：

1. 計算每個同學的總成績（表達式）
2. 按照成績排序（降序）
3. 取前三條記錄

代碼操作：

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

• 注意這里的order by是可以使用別名的

查詢基礎部分暫時告一段落~~

三、修改（Update）

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

• 我們接著看練習
  

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

• 我們再進行查看，可以看到已經修改成功了~~

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

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

• 首先我們查看有哪些同學是倒數三名的~~
• null在排序的時候，視為最小的值~~

• 然后我們接下來再進行將總成績倒數前三的 3 位同學的數學成績加上 30 分

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

• 如果要加的的數字超出范圍了，就會報錯，原來的成績不會修改

• 我們加上10應該就不會報錯了~~

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

• 可以看到，已經加成功了~~

• 這里如果要改成兩倍的話，就會超出范圍，我們就修改成0.5倍~

update exam_result set chinese = chinese / 2;

• 我們這里可以查看警告

show warnings;

• 其中里面的truncated意思就是截斷！
• 小數點后位數不夠了，只能截斷了~

• 這兩個成績已經是截斷后的成績了~~

四、刪除（Delete）

delete刪除記錄（行）

delete from 表名 where 條件;

• 刪除姓孫的考試成績

下面進行代碼演示：

delete from exam_result where name like '孫%';

• 這里就是把條件匹配出來的結果，都刪掉了?。?/li>

• 那么有同學說表名后面不加條件，會不會里面的內容都會刪除？會的?。。?/code>

delete from exam_result;

-- 單行插入
insert into 表(字段1, ..., 字段N) values (value1, ..., value N);
-- 多行插入
insert into 表(字段1, ..., 字段N) values
(value1, ...),
(value2, ...),
(value3, ...);

-- 全列查詢
select * from 表
-- 指定列查詢
select 字段1,字段2... from 表
-- 查詢表達式字段
select 字段1+100,字段2+字段3 from 表
-- 別名
select 字段1 別名1, 字段2 別名2 from 表
-- 去重DISTINCT
select distinct 字段 from 表
-- 排序ORDER BY
select * from 表 order by 排序字段
-- 條件查詢WHERE：
-- (1)比較運算符 (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR 
(8)NOT
select * from 表 where 條件

update 表 set 字段1=value1, 字段2=value2... where 條件

delete from 表 where 條件