MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作實(shí)例詳解
一、增加表數(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é)號(hào)', -> name varchar(20) not null, -> telephone char(11) -> ); Query OK, 0 rows affected (0.05 sec)
1.全列插入與指定列插入
value_list的數(shù)量必須和表中定義的字段數(shù)量一樣的時(shí)候,才屬于全列插入,才可以省略指定插入元素的部分。因?yàn)槲覀冊(cè)O(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í)候也可以同時(shí)插入多條數(shù)據(jù),對(duì)于多行數(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
因?yàn)橐话惚碇卸紩?huì)有主鍵和唯一鍵的約束,那么我們?cè)诓迦氲臅r(shí)候如果出現(xiàn)唯一鍵和主鍵沖突的情況就會(huì)插入失敗,那么如果我們就想插入呢,那么可以使用更新或者替換語句,將數(shù)據(jù)更新成我們新插入的,或者整個(gè)替換一下。
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)致的插入失敗的時(shí)候,可以執(zhí)行duplicate key update進(jìn)行數(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,如果有沖突大的話,他會(huì)先刪除沖突數(shù)據(jù),然后再重新插入并返回2。
二、查看表數(shù)據(jù)
1.全列查詢與指定列查詢
SELECT [column, column] FROM table_name;
當(dāng)column不指定且設(shè)置為*的時(shí)候,就是全列查詢了,但是一般不建議使用全列查詢,因?yàn)椴樵兊牧性蕉?,意味著需要傳輸?shù)臄?shù)據(jù)量就越大,會(huì)影響效率。對(duì)于指定列查詢輸入的字段名稱不需要和定義的時(shí)候順序一樣,select關(guān)鍵字的作用相當(dāng)于是打印,而我們定義打印什么他就會(huì)顯示什么,定義什么順序顯示,他就會(huì)按什么順序顯示。
2.查詢表達(dá)式字段
上述也說了select是一個(gè)起到一個(gè)打印顯示的作用,而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條件
運(yùn)算符 | 說明 |
>, >=, <, <= | 沒有什么特殊含義,就是單純的比較 |
= | 等于, |
<=> | 等于 |
!=, <> | 不等于 |
BETWEEN x1 AND x2 | 進(jìn)行范圍匹配,如果一個(gè)數(shù)值再[x1, x2]之間,那么就返回true |
IN (option, ...) | 如果是option中的一個(gè),那么就返回ture |
IS NULL | 是NULL |
IS NOT NULL | 不是NULL |
LIKE | 模糊匹配。 %表示任意多個(gè)任意字符;_表示任意一個(gè)字符 |
運(yùn)算符 | 說明 |
AND | 多個(gè)條件都必須滿足才返回true |
OR | 任意滿足一個(gè)條件返回true |
NOT | 滿足條件的時(shí)候,返回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, '張文強(qiáng)', 98, 90, 79); Query OK, 1 row affected (0.01 sec) //查詢名字為2個(gè)字,還性張的同學(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 | 張文強(qiáng) | 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 | | 張文強(qiáng) | 98 | +-----------+---------+ 3 rows in set (0.00 sec)
=和<=>的區(qū)別
兩者都是判斷兩個(gè)值是否相等的,但是第一個(gè)屬于非安全的,如果用NULL去和任意值就行比較的話,都會(huì)返回NULL,因?yàn)镸ySQL中對(duì)于NULL代表的是未知的值,所以說比較的時(shí)候結(jié)果也是未知的。而<=>的話能處理NULL值的比較,會(huì)把NULL當(dāng)作一個(gè)值來看待,如果都是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ù)的值進(jìn)行升序或者降序排列顯示,ASC是默認(rèn)值,表示升序,DESC表示降序。對(duì)于NULL的話,看作比任何數(shù)據(jù)都小的一個(gè)值。還可以進(jìn)行多段排序規(guī)則的定義,如果兩個(gè)數(shù)值相等,那么第一個(gè)排序規(guī)則就無法排序,就會(huì)繼續(xù)按照第二個(gè)排序規(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, '王強(qiáng)', 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 | 張文強(qiáng) | 98 | 90 | 79 | | 5 | 田七 | 92 | 60 | 79 | | 3 | 王五 | 76 | 45 | 97 | | 1 | 張三 | 67 | 65 | 86 | | 8 | 王強(qiáng) | 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 | 張文強(qiáng) | 267 | | 7 | 哈哈 | 254 | | 2 | 李四 | 238 | | 4 | 趙六 | 233 | | 5 | 田七 | 231 | | 1 | 張三 | 218 | | 3 | 王五 | 218 | | 8 | 王強(qiáng) | 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;
在對(duì)未知的表就行查詢顯示的時(shí)候,最好加上LIMIT,避免表中大的數(shù)據(jù)量過大,查詢?nèi)韺?dǎo)致數(shù)據(jù)庫卡頓。如果查詢的數(shù)據(jù)不夠n個(gè)的話不會(huì)有任何的影響。
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 | 張文強(qiáng) | 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 | 王強(qiáng) | 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 | 張文強(qiáng) | 98 | 120 | 109 | +----+-----------+---------+------+---------+ 2 rows in set (0.01 sec)
9.group by子句
使用該子句可以將指定的列進(jìn)行分組查詢,對(duì)于使用分組查詢的時(shí)候,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, '王強(qiáng)', 50.4); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 //查看每個(gè)班級(jí)的最高分 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 | | 張文強(qiáng) | 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 | | 張文強(qiáng) | 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
四、刪除與截?cái)啾頂?shù)據(jù)
刪除語法:DELETE FROM table_name [....];
如果說不加任何范圍選擇條件的話,那么就相當(dāng)于是刪除整個(gè)表數(shù)據(jù)的操作了。
截?cái)嗾Z法:TRUNCATE [TABLE] table_name;
對(duì)于刪除操作來說,是將表單個(gè)或者多個(gè)數(shù)據(jù)進(jìn)行刪除,而截?cái)鄤t是對(duì)整個(gè)表進(jìn)行操作,會(huì)將整個(gè)表數(shù)據(jù)都清除。這樣的話可以通過釋放表的存儲(chǔ)空間來實(shí)現(xiàn)清空表的操作,而DELETE語句需要逐行刪除記錄,并且會(huì)記錄每一行的刪除操作到日志中。所以會(huì)比DELETE快很多。
如果表中有自增列,操作會(huì)將自增列的值重置為初始值(通常是 1)。而DELETE語句不會(huì)重置自增列的值。還有不可滾回和不處罰觸發(fā)器的特點(diǎn),以后在講述。
到此這篇關(guān)于MySQL數(shù)據(jù)庫表內(nèi)容的增刪查改操作大全的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)庫表增刪查改內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- mysql.user表查看數(shù)據(jù)庫所有用戶信息SQL語句
- Mysql數(shù)據(jù)庫如何使用DELETE語句從數(shù)據(jù)庫表中刪除數(shù)據(jù)(數(shù)據(jù)庫數(shù)據(jù)刪除)
- MySQL查詢數(shù)據(jù)庫所有表名以及表結(jié)構(gòu)其注釋(小白專用)
- MySQL數(shù)據(jù)庫中sql表設(shè)計(jì)的注意事項(xiàng)
- python 實(shí)現(xiàn)mysql增刪查改示例代碼
- PyMySQL實(shí)現(xiàn)增刪查改的簡單使用
- Python操作mysql數(shù)據(jù)庫實(shí)現(xiàn)增刪查改功能的方法
- Java通過MyBatis框架對(duì)MySQL數(shù)據(jù)進(jìn)行增刪查改的基本方法
- MySQL中對(duì)于索引的基本增刪查改操作總結(jié)
- MySQL的增刪查改語句用法示例總結(jié)
相關(guān)文章
CentOS6.5下RPM方式安裝mysql5.6.33的詳細(xì)教程
本文給大家詳細(xì)介紹CentOS6.5下RPM方式安裝mysql5.6.33的教程,本文分步驟給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友一起看看吧2016-10-10MySQL數(shù)據(jù)庫升級(jí)的一些"陷阱"
這篇文章主要介紹了MySQL數(shù)據(jù)庫升級(jí)需要注意的地方,幫助大家更好的理解和學(xué)習(xí),感興趣的朋友可以了解下2020-08-08安裝mysql-8.0.19-winx64遇到的問題:Can''t create directory ''xxxx\Da
這篇文章主要介紹了安裝mysql-8.0.19-winx64遇到的坑 ,Can't create directory 'xxxx\Database\',非常不錯(cuò),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-02-02