MySQL系列

前言

本篇將介紹表的增刪查改（CURD），在數(shù)據(jù)庫技術(shù)與項(xiàng)目開發(fā)中，CURD 是對(duì)應(yīng) Create（創(chuàng)建）、Update（更新）、Read（讀?。?、Delete（刪除）四類數(shù)據(jù)處理動(dòng)作的核心操作縮寫，也是貫穿各類項(xiàng)目開發(fā)全流程的基礎(chǔ)數(shù)據(jù)交互原子操作。

本篇內(nèi)容緊跟上篇，前半部分的操作比較基礎(chǔ)，之前的文章中你已經(jīng)見過了

一、Create(創(chuàng)建)并插入數(shù)據(jù)

創(chuàng)建一個(gè)用于測(cè)試的表結(jié)果：

CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '學(xué)號(hào)',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

插入語法：

INSERT [INTO] table_name
    [(column1 [, column2, ...])]
VALUES
    (value1 [, value2, ...]),
    [(value1 [, value2, ...]), ...];

[]中的內(nèi)容都是可自主選擇的填寫字段

• 核心結(jié)構(gòu)：INSERT [INTO] table_name [(列名列表)] VALUES (對(duì)應(yīng)值列表)
  • table_name：需插入數(shù)據(jù)的目標(biāo)表名
  • (列名列表)：需插入的具體屬性列（如 id, name）
  • VALUES (值列表)：與列名列表一一對(duì)應(yīng)的數(shù)據(jù)值
• 關(guān)鍵語法細(xì)節(jié)：
  • INSERT 后可加 INTO 關(guān)鍵字，也可直接省略（如 INSERT table_name ...）。
• 全列插入場(chǎng)景：若不寫 (列名列表)，則默認(rèn)需一次性插入表中所有屬性列，此時(shí) VALUES 后的值需按表定義的列順序完整提供。

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

insert into students values (100, 10000, '唐三藏', null);
insert students values (101, 10001, '孫悟空', '11111');

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

插入數(shù)據(jù)時(shí)，使用，分割列名列表，多行數(shù)據(jù)使用，分割值列表：

insert into students (id, sn, name) values
(102, 20001, '曹孟德'),
(103, 20002, '孫仲謀');

1.3 插入沖突時(shí)同步更新

在插入數(shù)據(jù)時(shí)，若遇到主鍵沖突或唯一鍵沖突，可通過在 INSERT 語句后添加特定子句（如 ON DUPLICATE KEY UPDATE）實(shí)現(xiàn)沖突處理邏輯，確保即使存在沖突也能正常執(zhí)行操作，避免直接報(bào)錯(cuò)導(dǎo)致插入失敗。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = value1,  -- 沖突時(shí)更新的字段及值
    column2 = value2;  -- 可指定多個(gè)更新字段

這里必須保障跟新后的逐漸和唯一鍵不能與表中已存在的產(chǎn)生沖突。

SELECT ROW_COUNT();

該函數(shù)可以查看你在執(zhí)行操作時(shí)，受到影響的行數(shù)。

1.4 沖突時(shí)替換

-- 主鍵 或者 唯一鍵 沒有沖突，則直接插入；
-- 主鍵 或者 唯一鍵 如果沖突，則刪除后再插入
replace into students (sn, name) values (20001, '曹賊');

發(fā)生唯一鍵沖突時(shí)，將沖突行刪除后重新插入（id自增長(zhǎng)變化）,不發(fā)生沖突則直接插入。

二、Retireve讀取數(shù)據(jù)

SELECT [DISTINCT]
    {*, column1, column2, ...}  -- 選擇列（*表示所有列）
FROM table_name
[WHERE condition]              -- 篩選條件
[ORDER BY column1 [ASC|DESC],  -- 排序規(guī)則
          column2 [ASC|DESC]]
[LIMIT count];                 -- 限制返回行數(shù)

該sql語句可選項(xiàng)較多，接下我會(huì)根據(jù)實(shí)例來逐一介紹

create table exam_result (
id int unsigned primary key auto_increment,
name varchar(20) not null comment '同學(xué)姓名',
chinese float default 0.0 comment '語文成績(jī)',
math float default 0.0 comment '數(shù)學(xué)成績(jī)',
english float default 0.0 comment '英語成績(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 * FROM exam_result;

• 通常情況下不建議使用 * 進(jìn)行全列查詢，查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大；
• 可能會(huì)影響到索引的使用。

2.2 查詢指定列

-- 指定列的順序不需要按定義表的順序來
SELECT id, name, english FROM exam_result;

可以根據(jù)需要查找任意列

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

select id,name,chinese+english,1 from exam_result;

select可跟任意合法表達(dá)式，并返回表達(dá)式計(jì)算的結(jié)果，圖中1就是表達(dá)式1計(jì)算的結(jié)果。

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

select id,name,chinese+english as '語文+英語' from exam_result;

使用as可對(duì)表達(dá)式進(jìn)行重命名，as可以省略

2.4 結(jié)果去重 DISTINCT

select distinct math from exam_result;

2.5 where條件篩選

比較運(yùn)算符：

注意=不可直接用于NULL值比較，可以使用<=>進(jìn)行比較

null不參與運(yùn)算（前篇介紹了）

邏輯運(yùn)算符：

接下來結(jié)合實(shí)例，了解運(yùn)算符的使用

1、查找students表中qq為空的

select name,qq from students where qq<=>null;

select name,qq from students where qq is null;

2、查找students表中qq不為空的

select name,qq from students where qq is not null;

3、英語不及格的同學(xué)及英語成績(jī) ( < 60 )

select name,english from exam_result where english <60;

4、語文成績(jī)?cè)?[80, 90] 分的同學(xué)及語文成績(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;

注意使用between...and...查找的區(qū)間為閉區(qū)間

5、數(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=99 or math=98;

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

6、姓孫的同學(xué)

LIKE 模糊匹配：% 表示任意多個(gè)（包括 0 個(gè)）任意字符；_ 表示任意一個(gè)字符

select id,name from exam_result where name like '孫%';

%表示可以匹配任意多字符。

7、孫某同學(xué)

select id,name from exam_result where name like '孫_';

_表示僅匹配一個(gè)字符。

8、語文成績(jī)好于英語成績(jī)的同學(xué)

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

9、總分在 200 分以下的同學(xué)

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

10、語文成績(jī) > 80 并且不姓孫的同學(xué)

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

11、孫某同學(xué)，否則要求總成績(jī) > 200 并且 語文成績(jī) < 數(shù)學(xué)成績(jī) 并且 英語成績(jī) > 80

select name,chinese,math,english,chinese+math+english '總分' 
from exam_result where name like '孫_' or (chinese+math
+english >200 and chinese <math and english > 80); 

2.6 order by語句（結(jié)果排序）

• ASC 為升序（Ascending）（從小到大）
• DESC 為降序（Descending）（從大到小）

不做顯示聲明默認(rèn)為 ASC方式排序并且沒有order by子句的查詢，返回的順序是未定義的，永遠(yuǎn)不要依賴這個(gè)順序
MySQL中認(rèn)為NULL值是最小的

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

1、同學(xué)及數(shù)學(xué)成績(jī)，按數(shù)學(xué)成績(jī)升序顯示

select name,math from exam_result order by math asc;

2、查詢同學(xué)各門成績(jī)，依次按 數(shù)學(xué)降序，英語升序，語文升序的方式顯示

SELECT name, math, english, chinese 
FROM exam_result 
ORDER BY 
  math DESC,    -- 數(shù)學(xué)降序
  english ASC,  -- 英語升序（默認(rèn)可省略 ASC）
  chinese ASC;  -- 語文升序（默認(rèn)可省略 ASC）

3、查詢同學(xué)及總分，由高到低

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

這種寫法在標(biāo)準(zhǔn)sql中是不支持的，這里可以直接使用chinese+math+english的別名total進(jìn)行排序操作，是因?yàn)樵摪姹咀隽颂厥馓幚?，而?code>where的操作下則不被允許：

出現(xiàn)這種差異是因?yàn)?code>sql語句的執(zhí)行順序造成的，where 子句在 select之前執(zhí)行，此時(shí) total 別名尚未生成，因此無法引用。

標(biāo)準(zhǔn) SQL 執(zhí)行順序（理論上會(huì)報(bào)錯(cuò)）

1. FROM exam_result
   讀取表數(shù)據(jù)。
2. WHERE（隱式）
   無過濾條件，保留所有行。
3. 計(jì)算表達(dá)式
   計(jì)算 chinese + math + english，但此時(shí)未命名為 total。
4. ORDER BY total DESC
   報(bào)錯(cuò)：total 是 SELECT 中定義的別名，此時(shí)尚未生效。

MySQL 的實(shí)際執(zhí)行流程（允許 ORDER BY 引用別名）

1. FROM + 計(jì)算表達(dá)式
   讀取表數(shù)據(jù)，并計(jì)算 chinese + math + english，暫存結(jié)果（未命名）。
2. ORDER BY total DESC
   MySQL 允許 ORDER BY 引用尚未正式定義的別名，實(shí)際使用步驟 1 中暫存的計(jì)算結(jié)果進(jìn)行排序。
3. SELECT name, … AS total
   將排序后的結(jié)果命名為 total，返回最終結(jié)果集。

可以對(duì)執(zhí)行順序理解為：1、from:先確定操作表 2、where:確定執(zhí)行條件 3、根據(jù)執(zhí)行條件去表中篩選

2.6 limit的使用（篩選分頁結(jié)果）

select * from exam_result limit N;//N表示行數(shù)

select * from exam_result limit pos,len;//從pos行開始，篩選len行數(shù)據(jù)

可以看出表中起始行的下標(biāo)是從0位置開始的。

select * from exam_result limit len offset pos;//從pos位置開始獲取len行

三、Update更新修改

UPDATE table_name
SET column1 = expr1,      -- 要更新的列及值
    column2 = expr2       -- 可同時(shí)更新多列
[WHERE condition]         -- 過濾條件（必加！避免全量更新）
[ORDER BY column ASC/DESC] -- 可選：指定更新順序
[LIMIT row_count];        -- 可選：限制更新行數(shù)

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

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

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

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

3、將所有同學(xué)的數(shù)學(xué)成績(jī)+30分

update exam_result set math=math+30;

這里可以配合上面介紹的各種方法來完成操作，大家自己嘗試吧

四、Delete刪除

DELETE FROM table_name
[WHERE condition]         -- 過濾條件（必加！避免全量刪除）
[ORDER BY column ASC/DESC] -- 可選：指定刪除順序
[LIMIT row_count];        -- 可選：限制刪除行數(shù)

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

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

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

準(zhǔn)備測(cè)試表：

CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

delete from for_delete;

可以看到，當(dāng)我們刪除整張表達(dá)數(shù)據(jù)后，遞增關(guān)鍵字并不會(huì)被重置。

補(bǔ)充：截?cái)啾鞹RUNCATE

TRUNCATE [TABLE] table_name

功能：徹底清空表中所有數(shù)據(jù)，保留表結(jié)構(gòu)（列定義、索引、約束等）。

不同直接delete的是：

• 只能對(duì)整表操作，不能像 DELETE 一樣可以針對(duì)部分?jǐn)?shù)據(jù)操作；
• 實(shí)際上 MySQL 不對(duì)數(shù)據(jù)操作，所以比 DELETE 更快，但是TRUNCATE刪除數(shù)據(jù)的時(shí)候，并不經(jīng)過真正的事務(wù)，所以無法回滾。
• 會(huì)重置 AUTO_INCREMENT 項(xiàng)
• 并且不會(huì)記錄日志 bin log

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

在執(zhí)行此類操作時(shí)我們需要保證，整個(gè)操作過程要么完全成功并生效，要么完全失敗且不留下任何修改痕跡，絕對(duì)不允許出現(xiàn) “部分完成” 的中間狀，即原子性。

為什么需要保證原子性？
想象一個(gè)去重場(chǎng)景：假設(shè)表中有 1000 條重復(fù)數(shù)據(jù)，計(jì)劃刪除 900 條重復(fù)項(xiàng)。如果操作中途因停電、網(wǎng)絡(luò)中斷或 SQL 錯(cuò)誤終止：

• 沒有原子性保障： 可能只刪除了 500 條，剩下 500 條重復(fù)數(shù)據(jù)未處理，導(dǎo)致數(shù)據(jù)處于 “半去重” 的混亂狀態(tài)，后續(xù)難以恢復(fù)；
• 有原子性保障： 無論中途發(fā)生什么，數(shù)據(jù)庫會(huì)自動(dòng)回滾到操作前的狀態(tài)，數(shù)據(jù)仍保持 1000 條重復(fù)數(shù)據(jù)，不會(huì)留下中間痕跡。

原子性的本質(zhì)是 避免數(shù)據(jù)因意外中斷而損壞，確保數(shù)據(jù)始終處于安全且可預(yù)期的狀態(tài)。

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

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

如果在原表中直接操作，當(dāng)碰到特殊情況（如：操作執(zhí)行一般，斷網(wǎng)、斷電導(dǎo)致操作不完整，所有直接在原表中操作是不安全的）。

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

思路：

1、 創(chuàng)建一張空表 no_duplicate_table，結(jié)構(gòu)和 duplicate_table 一樣

CREATE TABLE no_duplicate_table LIKE duplicate_table;

2、 將 duplicate_table 的去重?cái)?shù)據(jù)插入到 no_duplicate_table

INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;

3、通過重命名表，實(shí)現(xiàn)原子的去重操作

RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;

到此這篇關(guān)于MySQL 數(shù)據(jù)庫表操作安全指南:創(chuàng)建、讀取、更新與刪除實(shí)戰(zhàn)的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)庫創(chuàng)建、讀取、更新與刪除內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論