MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作實例詳解
更新時間：2025年04月08日 16:25:22 作者：北顧南梔傾寒
對于刪除操作來說,是將表單個或者多個數(shù)據(jù)進行刪除,而截斷則是對整個表進行操作,會將整個表數(shù)據(jù)都清除,本文給大家介紹MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作大全,感興趣的朋友一起看看吧
一、增加表數(shù)據(jù)
語法：INSERT [INTO] table_name [column , column...] VALUES (value_list) [ ,(value_list)];
mysql> create table students (
-> id int unsigned primary key auto_increment,
-> number int not null unique comment '學(xué)號',
-> name varchar(20) not null,
-> telephone char(11)
-> );
Query OK, 0 rows affected (0.05 sec)1.全列插入與指定列插入
value_list的數(shù)量必須和表中定義的字段數(shù)量一樣的時候，才屬于全列插入，才可以省略指定插入元素的部分。因為我們設(shè)置了自增屬性，所以可以不用給id設(shè)定值，但是這樣的話，就不屬于全列插入了就必須指定插入的字段是哪些了。
//全列插入 insert into students values(1, 202501, '張三', '15812345678'); //指定列插入 insert into students (number, name, telephone) values(202503, '王五', '17712345678');
2.多行數(shù)據(jù)插入
在插入數(shù)據(jù)的時候也可以同時插入多條數(shù)據(jù)，對于多行數(shù)據(jù)的插入也滿足全列插入與指定列插入的規(guī)則。
//全列多行插入 insert into students values(4, 202504, '趙六', '12312345678'), (5, 202505, '田七', '12345656789'); //指定列多行插入 insert into students (number, name, telephone) values(202506, '你好', '12312345678'), (202507, '哈哈', '12345656789');
3.更新與替換插入
更新語法：ON DUPLICATE KEY UPDATE
替換語法：REPLACE
因為一般表中都會有主鍵和唯一鍵的約束，那么我們在插入的時候如果出現(xiàn)唯一鍵和主鍵沖突的情況就會插入失敗，那么如果我們就想插入呢，那么可以使用更新或者替換語句，將數(shù)據(jù)更新成我們新插入的，或者整個替換一下。
mysql> insert into students (id, number, name) values(1, 202501, '北顧') on duplicate key update number=202510, name = '北顧'; Query OK, 2 rows affected (0.00 sec) mysql> select * from students; +----+--------+--------+-------------+ | id | number | name | telephone | +----+--------+--------+-------------+ | 1 | 202510 | 北顧 | 15812345678 | | 2 | 202502 | 李四 | 17712345678 | | 3 | 202503 | 王五 | 17712345678 | | 4 | 202504 | 趙六 | 12312345678 | | 5 | 202505 | 田七 | 12345656789 | | 6 | 202506 | 你好 | 12312345678 | | 7 | 202507 | 哈哈 | 12345656789 | +----+--------+--------+-------------+ 7 rows in set (0.00 sec)
上面的代碼為插入否則更新的操作，先使用insert插入數(shù)據(jù)，如果說遇到主鍵或者唯一鍵的沖突而導(dǎo)致的插入失敗的時候，可以執(zhí)行duplicate key update進行數(shù)據(jù)的更新操作。
如圖可以看到返回值發(fā)生了變化，如果說插入的數(shù)據(jù)有沖突的但是沖突的數(shù)據(jù)和原數(shù)據(jù)是一樣的那么就相當(dāng)于不做任何操作，返回0；如果說沒有數(shù)據(jù)沖突的話，直接插入返回1；如果有數(shù)據(jù)沖突，并執(zhí)行了數(shù)據(jù)更新操作的話，就返回2。
mysql> replace into students (number, name) value(202510, 'hello'); Query OK, 2 rows affected (0.01 sec) mysql> select * from students; +----+---------+--------+-------------+ | id | number | name | telephone | +----+---------+--------+-------------+ | 2 | 202502 | 李四 | 17712345678 | | 3 | 202503 | 王五 | 17712345678 | | 4 | 202504 | 趙六 | 12312345678 | | 5 | 202505 | 田七 | 12345656789 | | 6 | 202506 | 你好 | 12312345678 | | 7 | 202507 | 哈哈 | 12345656789 | | 8 | 202510 | hello | NULL | +----+---------+--------+-------------+ 9 rows in set (0.00 sec)
上面的代碼則是替換代碼，他的操作是如果沒有沖突那么就直接插入并返回1，如果有沖突大的話，他會先刪除沖突數(shù)據(jù)，然后再重新插入并返回2。
二、查看表數(shù)據(jù)
1.全列查詢與指定列查詢
SELECT [column， column] FROM table_name;
當(dāng)column不指定且設(shè)置為*的時候，就是全列查詢了，但是一般不建議使用全列查詢，因為查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量就越大，會影響效率。對于指定列查詢輸入的字段名稱不需要和定義的時候順序一樣，select關(guān)鍵字的作用相當(dāng)于是打印，而我們定義打印什么他就會顯示什么，定義什么順序顯示，他就會按什么順序顯示。
2.查詢表達(dá)式字段
上述也說了select是一個起到一個打印顯示的作用，而column from table_name，才是指定打印的內(nèi)容，那么打印的內(nèi)容可不可以不是表中的呢？或者打印的字段可以不可以是表達(dá)式呢？打印的字段可不可以用表中的字段作為參數(shù)的表達(dá)式呢？
mysql> create table exam (
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null,
-> chinese float default 0.0,
-> math float default 0.0,
-> english float default 0.0
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into exam (name, chinese, math, english) values
-> ('張三', 67, 65, 86),
-> ('李四', 98, 56, 84),
-> ('王五', 76, 45, 97),
-> ('趙六', 99, 43, 91);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
//顯示非表中的數(shù)據(jù)
mysql> select 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
//顯示表達(dá)式數(shù)據(jù)
mysql> select 10 + 20;
+---------+
| 10 + 20 |
+---------+
| 30 |
+---------+
1 row in set (0.00 sec)
//顯示表中數(shù)據(jù)為參數(shù)的表達(dá)式數(shù)據(jù)
mysql> select id + 10 from exam;
+---------+
| id + 10 |
+---------+
| 11 |
| 12 |
| 13 |
| 14 |
+---------+
4 rows in set (0.00 sec)3.為查詢結(jié)果起別名
語法：SELECT column [AS] alias_name [...] FROM table_name;
mysql> select 10 + 20 as '總數(shù)'; +--------+ | 總數(shù) | +--------+ | 30 | +--------+ 1 row in set (0.00 sec) mysql> select id, name, chinese + math + english as '總分' from exam; +----+--------+--------+ | id | name | 總分 | +----+--------+--------+ | 1 | 張三 | 218 | | 2 | 李四 | 238 | | 3 | 王五 | 218 | | 4 | 趙六 | 233 | +----+--------+--------+ 4 rows in set (0.00 sec)
4.結(jié)果去重
語法: SELECT DISTINCT column FROM table_name;
5.WHERE條件
| 運算符 | 說明 |
| >, >=, <, <= | 沒有什么特殊含義，就是單純的比較 |
| = | 等于， |
| <=> | 等于 |
| !=, <> | 不等于 |
BETWEEN x1 AND x2 | 進行范圍匹配，如果一個數(shù)值再[x1, x2]之間，那么就返回true |
| IN (option, ...) | 如果是option中的一個，那么就返回ture |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| LIKE | 模糊匹配。 %表示任意多個任意字符；_表示任意一個字符 |
| 運算符 | 說明 |
| AND | 多個條件都必須滿足才返回true |
| OR | 任意滿足一個條件返回true |
| NOT | 滿足條件的時候，返回false，相當(dāng)于匹配不是該條件的內(nèi)容 |
BETWEEN x1 AND x2
下面是操作符的一些使用案例：
基本比較的使用
mysql> select * from exam; +----+--------+---------+------+---------+ | id | name | chinese | math | english | +----+--------+---------+------+---------+ | 1 | 張三 | 67 | 65 | 86 | | 2 | 李四 | 98 | 56 | 84 | | 3 | 王五 | 76 | 45 | 97 | | 4 | 趙六 | 99 | 43 | 91 | +----+--------+---------+------+---------+ 4 rows in set (0.00 sec) mysql> select id, name, math from exam where math < 60; +----+--------+------+ | id | name | math | +----+--------+------+ | 2 | 李四 | 56 | | 3 | 王五 | 45 | | 4 | 趙六 | 43 | +----+--------+------+ 3 rows in set (0.00 sec)
AND與BETWENN AND的使用
//查詢語文分?jǐn)?shù)再80到100之間的同學(xué) mysql> select id, name, chinese from exam where chinese >= 80 and chinese <= 100; +----+--------+---------+ | id | name | chinese | +----+--------+---------+ | 2 | 李四 | 98 | | 4 | 趙六 | 99 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql> select id, name, chinese from exam where chinese between 80 and 100; +----+--------+---------+ | id | name | chinese | +----+--------+---------+ | 2 | 李四 | 98 | | 4 | 趙六 | 99 | +----+--------+---------+ 2 rows in set (0.00 sec)
OR與IN的使用
//查詢英語分?jǐn)?shù)為86或97的同學(xué) mysql> select id, name, english from exam where english=86 or english=97; +----+--------+---------+ | id | name | english | +----+--------+---------+ | 1 | 張三 | 86 | | 3 | 王五 | 97 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql> select id, name, english from exam where english in(86, 97); +----+--------+---------+ | id | name | english | +----+--------+---------+ | 1 | 張三 | 86 | | 3 | 王五 | 97 | +----+--------+---------+ 2 rows in set (0.00 sec)
LIKE的使用
//查詢姓張和姓王的同學(xué) mysql> select id, name from exam where name like '張%' or name like '王%'; +----+--------+ | id | name | +----+--------+ | 1 | 張三 | | 3 | 王五 | +----+--------+ 2 rows in set (0.00 sec) mysql> insert into exam values(5, '張文強', 98, 90, 79); Query OK, 1 row affected (0.01 sec) //查詢名字為2個字，還性張的同學(xué) mysql> select id, name from exam where name like '張_'; +----+--------+ | id | name | +----+--------+ | 1 | 張三 | +----+--------+ 1 row in set (0.00 sec)
where與表達(dá)式混合使用
//總分大于230的同學(xué) mysql> select id, name, chinese + math + english as '總分' from exam where chinese + math + english > 230; +----+-----------+--------+ | id | name | 總分 | +----+-----------+--------+ | 2 | 李四 | 238 | | 4 | 趙六 | 233 | | 5 | 田七 | 231 | | 6 | 張文強 | 267 | +----+-----------+--------+ 4 rows in set (0.00 sec)
AND與NOT混合使用
//語文分?jǐn)?shù)大于90，不是不姓李的同學(xué) mysql> select name, chinese from exam where chinese > 90 and name not like '李_'; +-----------+---------+ | name | chinese | +-----------+---------+ | 趙六 | 99 | | 田七 | 92 | | 張文強 | 98 | +-----------+---------+ 3 rows in set (0.00 sec)
=和<=>的區(qū)別
兩者都是判斷兩個值是否相等的，但是第一個屬于非安全的，如果用NULL去和任意值就行比較的話，都會返回NULL，因為MySQL中對于NULL代表的是未知的值，所以說比較的時候結(jié)果也是未知的。而<=>的話能處理NULL值的比較，會把NULL當(dāng)作一個值來看待，如果都是NULL就返回1，不是返回0。
6.結(jié)果排序
語法：SELECT ... FROM table_name ... ORDER BY column [ASC|DESC], [...];
使用案例：select * from tset_table order by xxxx;
操作是將select選出的顯示數(shù)據(jù)，按照column數(shù)據(jù)的值進行升序或者降序排列顯示，ASC是默認(rèn)值，表示升序，DESC表示降序。對于NULL的話，看作比任何數(shù)據(jù)都小的一個值。還可以進行多段排序規(guī)則的定義，如果兩個數(shù)值相等，那么第一個排序規(guī)則就無法排序，就會繼續(xù)按照第二個排序規(guī)則繼續(xù)排序了。
mysql> insert into exam values(7, '哈哈', 98, 77, 79); Query OK, 1 row affected (0.01 sec) mysql> insert into exam values(8, '王強', NULL, 79, 59); Query OK, 1 row affected (0.01 sec) //語文按照降序，數(shù)學(xué)按照升序排列 mysql> select * from exam order by chinese desc, math; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 4 | 趙六 | 99 | 43 | 91 | | 2 | 李四 | 98 | 56 | 84 | | 7 | 哈哈 | 98 | 77 | 79 | | 6 | 張文強 | 98 | 90 | 79 | | 5 | 田七 | 92 | 60 | 79 | | 3 | 王五 | 76 | 45 | 97 | | 1 | 張三 | 67 | 65 | 86 | | 8 | 王強 | NULL | 79 | 59 | +----+-----------+---------+------+---------+ 8 rows in set (0.00 sec) //排序總分, order by中可以使用列名稱的別名 mysql> select id, name, chinese + math + english as 總分 from exam order by 總分 desc; +----+-----------+--------+ | id | name | 總分 | +----+-----------+--------+ | 6 | 張文強 | 267 | | 7 | 哈哈 | 254 | | 2 | 李四 | 238 | | 4 | 趙六 | 233 | | 5 | 田七 | 231 | | 1 | 張三 | 218 | | 3 | 王五 | 218 | | 8 | 王強 | NULL | +----+-----------+--------+ 8 rows in set (0.00 sec)
7.篩選分頁結(jié)果
//從0開始篩選n條結(jié)果
SELECT ... FROM table_name [...] LIMIT n;
//從s開始篩選n條結(jié)果
SELECT ... FROM table_name [...] LIMIT s, n;
SELECT ... FROM table_name [...] LIMIT n OFFSET S;
在對未知的表就行查詢顯示的時候，最好加上LIMIT，避免表中大的數(shù)據(jù)量過大，查詢?nèi)韺?dǎo)致數(shù)據(jù)庫卡頓。如果查詢的數(shù)據(jù)不夠n個的話不會有任何的影響。
mysql> select * from exam limit 3; +----+--------+---------+------+---------+ | id | name | chinese | math | english | +----+--------+---------+------+---------+ | 1 | 張三 | 67 | 65 | 86 | | 2 | 李四 | 98 | 56 | 84 | | 3 | 王五 | 76 | 45 | 97 | +----+--------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from exam limit 3, 3; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 4 | 趙六 | 99 | 43 | 91 | | 5 | 田七 | 92 | 60 | 79 | | 6 | 張文強 | 98 | 90 | 79 | +----+-----------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from exam limit 6, 3; +----+--------+---------+------+---------+ | id | name | chinese | math | english | +----+--------+---------+------+---------+ | 7 | 哈哈 | 98 | 77 | 79 | | 8 | 王強 | NULL | 79 | 59 | +----+--------+---------+------+---------+ 2 rows in set (0.00 sec) mysql>
8.插入查詢的結(jié)果
mysql> create table exam_zhang (
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null,
-> chinese float default 0.0,
-> math float default 0.0,
-> english float default 0.0
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into exam_zhang select distinct * from exam where name like '張%';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from exam_zhang;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 張三 | 100 | 130 | 116 |
| 6 | 張文強 | 98 | 120 | 109 |
+----+-----------+---------+------+---------+
2 rows in set (0.01 sec)9.group by子句
使用該子句可以將指定的列進行分組查詢，對于使用分組查詢的時候，select顯示的列必須是出現(xiàn)在group by子句中，或者說是聚合函數(shù)才可以。
mysql> create table student (
-> class_id int not null,
-> name varchar(10) not null,
-> score float default 0.0
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student values
-> (1, '張三', 98.7),
-> (1, '李四', 97.2),
-> (1, '王五', 88.6),
-> (2, '趙六', 79.4),
-> (2, '田七', 99.9),
-> (2, '王強', 50.4);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
//查看每個班級的最高分
mysql> select class_id, max(score) from student group by class_id;
+----------+------------+
| class_id | max(score) |
+----------+------------+
| 1 | 98.7 |
| 2 | 99.9 |
+----------+------------+
2 rows in set (0.00 sec)
//必須是聚合函數(shù)，或者是group by里出現(xiàn)的列
mysql> select class_id, name, max(score) from student group by class_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by三、修改表數(shù)據(jù)
語法：UPDATE table_name SET column=xx [, column=xx, ...] [WHERE ...];
//將總分最高的前三名英語成績提供30分 mysql> select name, english, math+chinese+english as 總分 from exam order by 總分 desc limit 3; +-----------+---------+--------+ | name | english | 總分 | +-----------+---------+--------+ | 張三 | 86 | 286 | | 張文強 | 79 | 267 | | 哈哈 | 79 | 254 | +-----------+---------+--------+ 3 rows in set (0.00 sec) mysql> update exam set english = english + 30 order by math+english+chinese desc limit 3; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select name, english, math+chinese+english as 總分 from exam order by 總分 desc limit 3; +-----------+---------+--------+ | name | english | 總分 | +-----------+---------+--------+ | 張三 | 116 | 316 | | 張文強 | 109 | 297 | | 哈哈 | 109 | 284 | +-----------+---------+------- //全列更改--非常不建議這樣做 mysql> update exam set math = math + 30; Query OK, 8 rows affected (0.00 sec) Rows matched: 8 Changed: 8 Warnings: 0
四、刪除與截斷表數(shù)據(jù)
刪除語法：DELETE FROM table_name [....];
如果說不加任何范圍選擇條件的話，那么就相當(dāng)于是刪除整個表數(shù)據(jù)的操作了。
截斷語法：TRUNCATE [TABLE] table_name;
對于刪除操作來說，是將表單個或者多個數(shù)據(jù)進行刪除，而截斷則是對整個表進行操作，會將整個表數(shù)據(jù)都清除。這樣的話可以通過釋放表的存儲空間來實現(xiàn)清空表的操作，而DELETE語句需要逐行刪除記錄，并且會記錄每一行的刪除操作到日志中。所以會比DELETE快很多。
如果表中有自增列，操作會將自增列的值重置為初始值（通常是 1）。而DELETE語句不會重置自增列的值。還有不可滾回和不處罰觸發(fā)器的特點，以后在講述。
到此這篇關(guān)于MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作大全的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)庫表增刪查改內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MAC下Mysql5.7+ MySQL Workbench安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了MAC下Mysql5.7+ MySQL Workbench安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-06-06
Mysql的root賬戶密碼忘記了怎么解決(百分百教會你重置！)
mysql是常用的數(shù)據(jù)庫,很多程序員在使用的過程中會出現(xiàn)root用戶密碼忘記的事情,這篇文章主要給大家介紹了關(guān)于Mysql的root賬戶密碼忘記了該怎么解決的相關(guān)資料,文中介紹的方法百分百教會你如何重置,需要的朋友可以參考下2024-05-05
這篇文章主要介紹了MySQL解析JSON格式字段并取出部分值方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
虛擬機Centos7安裝MySQL數(shù)據(jù)庫實踐
用戶分享在虛擬機安裝MySQL的全過程及常見問題解決方案,包括處理GPG密鑰、修改密碼策略、配置遠(yuǎn)程訪問權(quán)限及防火墻設(shè)置,最終通過關(guān)閉防火墻和停止NetworkManager解決網(wǎng)絡(luò)連接異常問題2025-07-07
這篇文章主要介紹了MySQL 5.7 zip版本(zip版)安裝配置步驟詳解,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-02-02
這篇文章主要介紹了MySQL如何解決DOS窗口亂碼問題，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-11-11
這篇文章主要介紹了MySQL占用CPU過高排查過程及可能優(yōu)化方案,具有很好的參考價值,希望對大家的學(xué)習(xí)或工作有所幫助,感興趣的朋友可以參考下2024-01-01
當(dāng)數(shù)據(jù)庫中表的數(shù)量比較多時，不利于維護，本文將以此問題進行詳細(xì)介紹如何維護mysql表，與如何修改mysql表2012-11-11
在本文里我們給大家整理了關(guān)于Mysql的MERGE存儲引擎的相關(guān)知識點內(nèi)容，有需要的讀者們學(xué)習(xí)下。2019-02-02
MySQL實現(xiàn)分詞搜索(FULLTEXT)的方法
這篇文章主要介紹了MySQL實現(xiàn)分詞搜索(FULLTEXT)的方法,包括全文搜索的簡單使用，建表添加FULLTEXT索引使用該技術(shù)非常簡單，首先需要有一張表，我建立了一張圖書表并插入了兩條數(shù)據(jù)，需要的朋友可以參考下2022-10-10