MySQL?數(shù)據(jù)庫表操作完全指南:創(chuàng)建、讀取、更新與刪除實戰(zhàn)
MySQL系列
前言
本篇將介紹表的增刪查改（CURD），在數(shù)據(jù)庫技術(shù)與項目開發(fā)中，CURD 是對應 Create（創(chuàng)建）、Update（更新）、Read（讀?。?、Delete（刪除）四類數(shù)據(jù)處理動作的核心操作縮寫，也是貫穿各類項目開發(fā)全流程的基礎數(shù)據(jù)交互原子操作。
本篇內(nèi)容緊跟上篇，前半部分的操作比較基礎，之前的文章中你已經(jīng)見過了
一、Create(創(chuàng)建)并插入數(shù)據(jù)
創(chuàng)建一個用于測試的表結(jié)果：
CREATE TABLE students ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, sn INT NOT NULL UNIQUE COMMENT '學號', 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 (對應值列表)table_name：需插入數(shù)據(jù)的目標表名(列名列表)：需插入的具體屬性列（如id, name）VALUES (值列表)：與列名列表一一對應的數(shù)據(jù)值
- 關(guān)鍵語法細節(jié)：
INSERT后可加INTO關(guān)鍵字，也可直接省略（如INSERT table_name ...）。
- 全列插入場景：若不寫
(列名列表)，則默認需一次性插入表中所有屬性列，此時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ù)據(jù)使用，分割值列表：
insert into students (id, sn, name) values (102, 20001, '曹孟德'), (103, 20002, '孫仲謀');
1.3 插入沖突時同步更新
在插入數(shù)據(jù)時，若遇到主鍵沖突或唯一鍵沖突，可通過在 INSERT 語句后添加特定子句（如 ON DUPLICATE KEY UPDATE）實現(xiàn)沖突處理邏輯，確保即使存在沖突也能正常執(zhí)行操作，避免直接報錯導致插入失敗。
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = value1, -- 沖突時更新的字段及值
column2 = value2; -- 可指定多個更新字段
這里必須保障跟新后的逐漸和唯一鍵不能與表中已存在的產(chǎn)生沖突。
SELECT ROW_COUNT();
該函數(shù)可以查看你在執(zhí)行操作時，受到影響的行數(shù)。
1.4 沖突時替換
-- 主鍵 或者 唯一鍵 沒有沖突，則直接插入； -- 主鍵 或者 唯一鍵 如果沖突，則刪除后再插入 replace into students (sn, name) values (20001, '曹賊');
發(fā)生唯一鍵沖突時，將沖突行刪除后重新插入（id自增長變化）,不發(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語句可選項較多，接下我會根據(jù)實例來逐一介紹
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 '數(shù)學成績', english float default 0.0 comment '英語成績' );
測試數(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;
- 通常情況下不建議使用 * 進行全列查詢，查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大；
- 可能會影響到索引的使用。
2.2 查詢指定列
-- 指定列的順序不需要按定義表的順序來 SELECT id, name, english FROM exam_result;
可以根據(jù)需要查找任意列
2.3 查詢字段為表達式
select id,name,chinese+english,1 from exam_result;
select可跟任意合法表達式，并返回表達式計算的結(jié)果，圖中1就是表達式1計算的結(jié)果。
為查詢結(jié)果指定別名
select id,name,chinese+english as '語文+英語' from exam_result;
使用as可對表達式進行重命名，as可以省略
2.4 結(jié)果去重 DISTINCT
select distinct math from exam_result;
2.5 where條件篩選
比較運算符：
注意=不可直接用于NULL值比較，可以使用<=>進行比較
null不參與運算（前篇介紹了）
邏輯運算符：
接下來結(jié)合實例，了解運算符的使用
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、英語不及格的同學及英語成績 ( < 60 )
select name,english from exam_result where english <60;
4、語文成績在 [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;
注意使用between...and...查找的區(qū)間為閉區(qū)間
5、數(shù)學成績是 58 或者 59 或者 98 或者 99 分的同學及數(shù)學成績
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、姓孫的同學
LIKE 模糊匹配：% 表示任意多個（包括 0 個）任意字符；_ 表示任意一個字符
select id,name from exam_result where name like '孫%';
%表示可以匹配任意多字符。
7、孫某同學
select id,name from exam_result where name like '孫_';
_表示僅匹配一個字符。
8、語文成績好于英語成績的同學
select name,chinese,english from exam_result where chinese > english;
9、總分在 200 分以下的同學
select name,chinese+math+english from exam_result where chinese+math+english <200;
10、語文成績 > 80 并且不姓孫的同學
select name,chinese from exam_result where chinese >80 and name not like '孫%';
11、孫某同學，否則要求總成績 > 200 并且 語文成績 < 數(shù)學成績 并且 英語成績 > 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）（從大到小）
不做顯示聲明默認為 ASC方式排序并且沒有order by子句的查詢，返回的順序是未定義的，永遠不要依賴這個順序
MySQL中認為NULL值是最小的
SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];、
1、同學及數(shù)學成績，按數(shù)學成績升序顯示
select name,math from exam_result order by math asc;
2、查詢同學各門成績，依次按 數(shù)學降序，英語升序，語文升序的方式顯示
SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, -- 數(shù)學降序 english ASC, -- 英語升序（默認可省略 ASC） chinese ASC; -- 語文升序（默認可省略 ASC）
3、查詢同學及總分，由高到低
select name,chinese+math+english total from exam_result order by chinese+math+english desc;
這種寫法在標準sql中是不支持的，這里可以直接使用chinese+math+english的別名total進行排序操作，是因為該版本做了特殊處理，而在where的操作下則不被允許：
出現(xiàn)這種差異是因為sql語句的執(zhí)行順序造成的，where 子句在 select之前執(zhí)行，此時 total 別名尚未生成，因此無法引用。
標準 SQL 執(zhí)行順序（理論上會報錯）
- FROM exam_result
讀取表數(shù)據(jù)。 - WHERE（隱式）
無過濾條件，保留所有行。 - 計算表達式
計算chinese + math + english，但此時未命名為total。 - ORDER BY total DESC
報錯：total是SELECT中定義的別名，此時尚未生效。
MySQL 的實際執(zhí)行流程（允許 ORDER BY 引用別名）
- FROM + 計算表達式
讀取表數(shù)據(jù)，并計算chinese + math + english，暫存結(jié)果（未命名）。 - ORDER BY total DESC
MySQL 允許ORDER BY引用尚未正式定義的別名，實際使用步驟 1 中暫存的計算結(jié)果進行排序。 - SELECT name, … AS total
將排序后的結(jié)果命名為total，返回最終結(jié)果集。
可以對執(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ù)
可以看出表中起始行的下標是從0位置開始的。
select * from exam_result limit len offset pos;//從pos位置開始獲取len行
三、Update更新修改
UPDATE table_name
SET column1 = expr1, -- 要更新的列及值
column2 = expr2 -- 可同時更新多列
[WHERE condition] -- 過濾條件（必加！避免全量更新）
[ORDER BY column ASC/DESC] -- 可選：指定更新順序
[LIMIT row_count]; -- 可選：限制更新行數(shù)
1、將孫悟空同學的數(shù)學成績變更為 80 分
update exam_result set math=80 where name='孫悟空';
2、將曹孟德同學的數(shù)學成績變更為 60 分，語文成績變更為 70 分
update exam_result set math=60,chinese=70 where name='曹孟德';
3、將所有同學的數(shù)學成績+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、刪除孫悟空同學的考試成績
delete from exam_result where name='孫悟空';
2、 刪除整張表數(shù)據(jù)
準備測試表：
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;
可以看到，當我們刪除整張表達數(shù)據(jù)后，遞增關(guān)鍵字并不會被重置。
補充：截斷表TRUNCATE
TRUNCATE [TABLE] table_name
功能：徹底清空表中所有數(shù)據(jù)，保留表結(jié)構(gòu)（列定義、索引、約束等）。
不同直接delete的是：
- 只能對整表操作，不能像 DELETE 一樣可以針對部分數(shù)據(jù)操作；
- 實際上 MySQL 不對數(shù)據(jù)操作，所以比 DELETE 更快，但是TRUNCATE刪除數(shù)據(jù)的時候，并不經(jīng)過真正的事務，所以無法回滾。
- 會重置 AUTO_INCREMENT 項
- 并且不會記錄日志 bin log
五、插入查詢結(jié)果
在執(zhí)行此類操作時我們需要保證，整個操作過程要么完全成功并生效，要么完全失敗且不留下任何修改痕跡，絕對不允許出現(xiàn) “部分完成” 的中間狀，即原子性。
為什么需要保證原子性？
想象一個去重場景：假設表中有 1000 條重復數(shù)據(jù)，計劃刪除 900 條重復項。如果操作中途因停電、網(wǎng)絡中斷或 SQL 錯誤終止：
- 沒有原子性保障： 可能只刪除了 500 條，剩下 500 條重復數(shù)據(jù)未處理，導致數(shù)據(jù)處于 “半去重” 的混亂狀態(tài)，后續(xù)難以恢復；
- 有原子性保障： 無論中途發(fā)生什么，數(shù)據(jù)庫會自動回滾到操作前的狀態(tài)，數(shù)據(jù)仍保持 1000 條重復數(shù)據(jù)，不會留下中間痕跡。
原子性的本質(zhì)是 避免數(shù)據(jù)因意外中斷而損壞，確保數(shù)據(jù)始終處于安全且可預期的狀態(tài)。
INSERT INTO table_name [(column [, column ...])] SELECT ...
示例：刪除表中的的重復復記錄，重復的數(shù)據(jù)只能有一份
如果在原表中直接操作，當碰到特殊情況（如：操作執(zhí)行一般，斷網(wǎng)、斷電導致操作不完整，所有直接在原表中操作是不安全的）。
準備測試表 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 的去重數(shù)據(jù)插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
3、通過重命名表，實現(xiàn)原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;
到此這篇關(guān)于MySQL 數(shù)據(jù)庫表操作安全指南:創(chuàng)建、讀取、更新與刪除實戰(zhàn)的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)庫創(chuàng)建、讀取、更新與刪除內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
解決亂碼的方法是，在執(zhí)行SQL語句之前，將MySQL以下三個系統(tǒng)參數(shù)設置為與服務器字符集character-set-server相同的字符集2012-05-05
navicat連接Mysql數(shù)據(jù)庫報2013錯誤解決辦法
這篇文章主要介紹了navicat連接Mysql數(shù)據(jù)庫報2013錯誤的解決辦法,首先檢查MySQL是否安裝成功,然后修改配置文件,添加或注釋掉特定行,最后連接進入MySQL服務并執(zhí)行授權(quán)命令,需要的朋友可以參考下2025-02-02
完美解決MySQL數(shù)據(jù)庫服務器CPU飆升問題
這篇文章主要介紹了解決 MySQL 數(shù)據(jù)庫服務器 CPU 飆升的方法,包括定位問題（如使用工具監(jiān)控、查看慢查詢?nèi)罩荆?、?yōu)化 SQL 查詢、調(diào)整配置參數(shù)、優(yōu)化數(shù)據(jù)庫架構(gòu)、檢查硬件資源、處理鎖競爭問題等,還通過電商業(yè)務系統(tǒng)的案例進行了詳細分析及給出解決方法,最終優(yōu)化效果顯著2025-02-02
最近在項目中使用 mybatis 寫 SQL 使用了 where 1=1 來簡化多條件拼接的寫法,案例如下,借此聊聊多條件拼接的常見的一些寫法以及 where 1=1 是否存在性能影響,需要的朋友可以參考下2024-02-02
以mysql為例詳解ToplingDB?的?UintIndex
本文主要介紹了以mysql為例詳解ToplingDB的UintIndex，在ToplingDB的CO-Index(Compressed?Ordered?Index)家族中，Nest?Succinct?Trie是最通用的，更多相關(guān)內(nèi)容需要的朋友可以參考一下2022-08-08
這篇文章主要介紹了記一次因線上mysql優(yōu)化器誤判引起慢查詢事件的相關(guān)資料以及最終的解決方案，分享給大家，希望能夠給大家一點啟發(fā)。2017-02-02
本文給大家介紹ubuntu自啟動mysql的三種方式，一種方法通過執(zhí)行命令，也是最簡單的方法，第二種方法是使用sysv-rc-cont工具，第三種方法是使用chkconfig工具，關(guān)于以上三種方式的詳解請看下文2015-10-10
當unique列在一個UNIQUE鍵上插入包含重復值的記錄時，默認insert的時候會報1062錯誤，MYSQL有三種不同的處理方法，下面我們分別介紹。2012-09-09
mysql查詢優(yōu)化之100萬條數(shù)據(jù)的一張表優(yōu)化方案
這篇文章主要介紹了mysql查詢優(yōu)化之100萬條數(shù)據(jù)的一張表優(yōu)化方案,需要的朋友可以參考下2021-05-05
ARM64架構(gòu)下安裝mysql5.7.22的全過程
這篇文章主要介紹了ARM64架構(gòu)下安裝mysql5.7.22的全過程,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-07-07