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 '學號',
-> 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ù)，如果說遇到主鍵或者唯一鍵的沖突而導致的插入失敗的時候，可以執(zhí)行duplicate key update進行數(shù)據(jù)的更新操作。
如圖可以看到返回值發(fā)生了變化，如果說插入的數(shù)據(jù)有沖突的但是沖突的數(shù)據(jù)和原數(shù)據(jù)是一樣的那么就相當于不做任何操作，返回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;
當column不指定且設(shè)置為*的時候，就是全列查詢了，但是一般不建議使用全列查詢，因為查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量就越大，會影響效率。對于指定列查詢輸入的字段名稱不需要和定義的時候順序一樣，select關(guān)鍵字的作用相當于是打印，而我們定義打印什么他就會顯示什么，定義什么順序顯示，他就會按什么順序顯示。
2.查詢表達式字段
上述也說了select是一個起到一個打印顯示的作用，而column from table_name，才是指定打印的內(nèi)容，那么打印的內(nèi)容可不可以不是表中的呢？或者打印的字段可以不可以是表達式呢？打印的字段可不可以用表中的字段作為參數(shù)的表達式呢？
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)
//顯示表達式數(shù)據(jù)
mysql> select 10 + 20;
+---------+
| 10 + 20 |
+---------+
| 30 |
+---------+
1 row in set (0.00 sec)
//顯示表中數(shù)據(jù)為參數(shù)的表達式數(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，相當于匹配不是該條件的內(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的使用
//查詢語文分數(shù)再80到100之間的同學 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的使用
//查詢英語分數(shù)為86或97的同學 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的使用
//查詢姓張和姓王的同學 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個字，還性張的同學 mysql> select id, name from exam where name like '張_'; +----+--------+ | id | name | +----+--------+ | 1 | 張三 | +----+--------+ 1 row in set (0.00 sec)
where與表達式混合使用
//總分大于230的同學 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混合使用
//語文分數(shù)大于90，不是不姓李的同學 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當作一個值來看待，如果都是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是默認值，表示升序，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ù)學按照升序排列 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)韺е聰?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 [....];
如果說不加任何范圍選擇條件的話，那么就相當于是刪除整個表數(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)文章
千萬級用戶系統(tǒng)SQL調(diào)優(yōu)實戰(zhàn)分享
這篇文章主要介紹了千萬級用戶系統(tǒng)SQL調(diào)優(yōu)實戰(zhàn)分享，用戶日活百萬級，注冊用戶千萬級，而且若還沒有進行分庫分表，則該DB里的用戶表可能就一張，單表上千萬的用戶數(shù)據(jù)，下面我們就來學習如何讓優(yōu)化,需要的朋友可以參考一下2022-03-03
MySQL報錯 table “xxx” doesn‘t exit的解決
本文主要介紹了MySQL報錯 table “xxx” doesn‘t exit的解決,主要原因是英文字母大小寫敏感導致,下面就來介紹一下解決方法,感興趣的可以了解一下2023-10-10
mysql格式化小數(shù)保留小數(shù)點后兩位(小數(shù)點格式化)
今天遇到一個問題，格式化浮點數(shù)的問題，用format（col,2）保留兩位小數(shù)點，出現(xiàn)一個問題，例如下面的語句，后面我們給出解決方法2013-12-12
本文主要介紹了mysql中InnoDB幻讀解決方案與機制,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2026-03-03
本文介紹了MySQL中FIND_IN_SET函數(shù)的基本語法和使用場景,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-01-01
MySQL數(shù)據(jù)庫中varchar類型的數(shù)字比較大小的方法
varchar類型的數(shù)據(jù)是不能直接比較大小的，那么MySQL數(shù)據(jù)庫中varchar類型如何進行數(shù)字比較大小的，本文就詳細的介紹一下2021-11-11
MySQL數(shù)據(jù)庫中提供了很豐富的函數(shù),下面這篇文章主要給大家介紹了關(guān)于Mysql庫函數(shù)的相關(guān)資料,文中通過代碼介紹的非常詳細,對大家學習或者使用Mysql具有一定的參考借鑒價值,需要的朋友可以參考下2024-04-04
MySQL大批量IN查詢優(yōu)化是一個非常經(jīng)典且棘手的高并發(fā)、大數(shù)據(jù)量場景下的問題,本文我將與大家探討最有效、最常用的優(yōu)化方案,并按推薦順序排列,需要的朋友可以參考下2025-09-09
這篇文章主要介紹了mysql中如何查詢多個表中的數(shù)據(jù)量問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
MySQL之Innodb_buffer_pool_size設(shè)置方式
這篇文章主要介紹了MySQL之Innodb_buffer_pool_size設(shè)置方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08