表的增刪查改

表的增刪查改，簡稱表的 CURD 操作 : Create(創(chuàng)建)，Update(更新)，Retrieve(讀取)，Delete（刪除）.

下面我們逐一進(jìn)行介紹。

1. Create

語法：

INSERT [INTO] table_name[(column [, column] ...)]VALUES (value_list) [, (value_list)] ...value_list: value, [, value] ...

例如創(chuàng)建一張學(xué)生表：

create table students( -> id int unsigned primary key auto_increment, -> stunum int not null unique comment '學(xué)號', -> name varchar(20) not null, -> email varchar(20) -> );

（1）單行數(shù)據(jù) + 全列插入

接下來我們插入兩條記錄，其中 value_list 數(shù)量必須和定義表的列的數(shù)量及順序一致：

例如插入一個數(shù)據(jù)：

insert into students values(1, 1001, 'Smith', '11111');

其中 into 可以省略；示例如下：

（2）多行數(shù)據(jù) + 指定列插入

我們可以在表的名字后面帶上需要插入的字段，并且 value_list 數(shù)量必須和指定字段數(shù)量及順序一致，例如，我們只想插入 stunum 和name 字段：

insert into students(stunum, name) values(1003, 'Mike');

示例如下：

（3）插入否則更新

由于主鍵或者唯一鍵對應(yīng)的值已經(jīng)存在會而導(dǎo)致插入失敗。

我們先查看我們當(dāng)前表的數(shù)據(jù)：

其中 id 為主鍵，stunum 為唯一鍵，所以我們分別嘗試插入主鍵和唯一鍵沖突的數(shù)據(jù)：

如上圖，插入失敗。所以我們可以選擇性的進(jìn)行同步更新操作語法：

insert... on duplicate key update column = value [, column = value] ...

例如我們想插入 Jane 這位同學(xué)的數(shù)據(jù)，但是我們并不知道這位同學(xué)是否存在于這張表中，所以我們使用上面的語法，假設(shè)不存在，就按照我們的數(shù)據(jù)插入數(shù)據(jù)；否則我們將她的 stunum 更新為 1010：

insert into students(id, stunum, name) values(2, 1010, 'Jane') -> on duplicate key update stunum = 1010, name = 'Jane';

如下：

我們可以觀察到當(dāng)插入成功后，會有 2 rows affected (0.00 sec) 這樣的提示，即：

這個提示的含義如下：

0 row affected: 表中有沖突數(shù)據(jù)，但沖突數(shù)據(jù)的值和 update 的值相等1 row affected: 表中沒有沖突數(shù)據(jù)，數(shù)據(jù)被插入2 row affected: 表中有沖突數(shù)據(jù)，并且數(shù)據(jù)已經(jīng)被更新 （4）替換

替換的本質(zhì)：主鍵或者唯一鍵沒有沖突，則直接插入；主鍵 或者 唯一鍵 如果沖突，則刪除后再插入。

替換將 insert 換成 replace 使用即可。示例如下：

2. Retrieve

Retrieve 指的是表的讀取。

語法：

SELECT[DISTINCT] {* | {column [, column] ...}[FROM table_name][WHERE ...][ORDER BY column [ASC | DESC], ...]LIMIT ...

我們先創(chuàng)建一個表結(jié)構(gòu)：

			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ù)學(xué)成績',
			    -> english float default 0.0 comment '英語成績'
			    -> );

接下來插入一些數(shù)據(jù)：

			insert into exam_result(name, chinese, math, english) values
			    -> ('Smith', 67, 98, 62),
			    -> ('Jane', 64, 78, 79),
			    -> ('Tiny', 76, 87, 77),
			    -> ('John', 89, 78, 90),
			    -> ('Mike', 90, 91, 93),
			    -> ('Lily', 82, 83, 89),
			    -> ('Klin', 60, 53, 59);

（1）select 列

a. 全列查詢

語法：SELECT * FROM 表名;

通常情況下不建議使用 * 進(jìn)行全列查詢，因?yàn)椋?/p>

查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大； 可能會影響到索引的使用。（索引待后面講解）

例如：

b. 指定列查詢

指定列的順序不需要按定義表的順序來，語法就是在 select 后跟上指定的字段列即可。例如我們只需要查詢 id、name、math，如下：

select id, name, math from exam_result;

c. 查詢字段為表達(dá)式 表達(dá)式不包含字段：select id, name, 10 from exam_result; - - - 顯示 10

表達(dá)式包含一個字段：select id, name, chinese+10 from exam_result; - - - 使語文成績 + 10 分

表達(dá)式包含多個字段：select id, name, chinese + math + english from exam_result; - - - 統(tǒng)計總成績

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

語法：SELECT column [AS] alias_name […] FROM table_name;

例如將上面統(tǒng)計的總成績的字段改為總分：select id, name, chinese + math + english as 總分 from exam_result; - - - as 可以加也可以不加

e. 結(jié)果去重

去重使用關(guān)鍵字 DISTINCT，直接加在 select 后即可。例如 math 中 78 的分?jǐn)?shù)重復(fù)了：

結(jié)果去重：select distinct math from exam_result;

（2）where 條件 比較運(yùn)算符：

邏輯運(yùn)算符：

使用 where 篩選條件只需將 where 放在語句最后，后面跟上條件即可，下面舉一些實(shí)例：

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

select name, english from exam_result where english < 60;

語文成績在 [80, 90] 分的同學(xué)及語文成績

使用 and 進(jìn)行條件連接

select name, chinese from exam_result where chinese >= 80 and chinese <= 90;

使用 between … and … 條件

select name, chinese from exam_result where chinese between 80 and 90;

數(shù)學(xué)成績是 53 或者 91 或者 98 分的同學(xué)及數(shù)學(xué)成績

使用 or 進(jìn)行條件連接

select name, math from exam_result -> where math = 53 -> or math = 91 -> or math = 98;

使用 in 條件

select name, math from exam_result where math in(53, 91, 98);

以字母 J 開頭的同學(xué)

% 匹配任意多個（包括 0 個）任意字符

select name from exam_result where name like 'J%';

_ 匹配嚴(yán)格的一個任意字符

例如需要查找 J 某同學(xué)：

select name from exam_result where name like 'J_';

英語成績好于數(shù)學(xué)成績的同學(xué)

where 條件中比較運(yùn)算符兩側(cè)都是字段

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

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

where 條件中使用表達(dá)式

別名不能用在 where 條件中，因?yàn)橛?where 語句的語句中，先篩選 where 語句的條件，再讀取篩選后的表

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

英語成績 > 80 并且姓名首字母不以 M 開頭的同學(xué)

and 于 not 的使用

  		select name, english from exam_result 
  		    -> where english > 80 and name not like 'M%';

名字首字母以 T 開頭的同學(xué)，否則要求總成績 > 200 并且 語文成績 < 數(shù)學(xué)成績 并且 英語成績 > 80

綜合性查詢

  		select name, chinese, math, english, chinese+math+english total from exam_result
  		    -> where name like 'T%'
  		    -> or chinese+math+english > 200
  		    -> and chinese < math
  		    -> and english > 80;

null 的查詢

接下來我們切回 students 表進(jìn)行查詢：

查詢 email 已知的同學(xué)姓名

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

NULL 和 NULL 的比較，= 和 <=> 的區(qū)別

select null = null, null = 1, null = 0;

select NULL <=> NULL, NULL <=> 1, NULL <=> 0

（3）結(jié)果排序

語法：

asc 為升序（從小到大）

desc 為降序（從大到?。?/p>

默認(rèn)為 asc

select ... from table_name [where ...] order by column [asc|desc], [...];

注意：沒有 order by 子句的查詢，返回的順序是未定義的，永遠(yuǎn)不要依賴這個順序。

下面看實(shí)例：

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

select name, math from exam_result order by math asc;

注：asc 可以省略

同學(xué)及 email，按 email 排序顯示

select name, email from students order by email asc;

注意：NULL 視為比任何值都小，升序出現(xiàn)在最上面。

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

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

多字段排序，排序優(yōu)先級隨書寫順序：

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

order by 子句中可以使用列別名，因?yàn)?order by 子句是最后執(zhí)行的，要先有合適的數(shù)據(jù)，才能排序。

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

查詢姓名首字母為 J 的同學(xué)或者為 S 的同學(xué)數(shù)學(xué)成績，結(jié)果按數(shù)學(xué)成績由高到低顯示

結(jié)合 where 子句 和 order by 子句：

				select name, math from exam_result 
				    -> where name like 'J%' or name like 'S%'
				    -> order by math desc;

（4）篩選分頁結(jié)果

語法：

			起始下標(biāo)為 0
			
			從 0 開始，篩選 n 條結(jié)果
			select... from table_name [where ...] [order by ...] limit n;
			
			從 s 開始，篩選 n 條結(jié)果
			select... from table_name [where ...] [order by ...] limit s, n;
			
			從 s 開始，篩選 n 條結(jié)果，比第二種用法更明確，建議使用
			select... from table_name [where ...] [order by ...] limit n offset s;

建議：對未知表進(jìn)行查詢時，最好加一條 limit 1，避免因?yàn)楸碇袛?shù)據(jù)過大，查詢?nèi)頂?shù)據(jù)導(dǎo)致數(shù)據(jù)庫卡死。

實(shí)例：

按 id 進(jìn)行分頁，每頁 3 條記錄，分別顯示 第 1、2、3 頁

第一頁：

select name, chinese, math, english from exam_result -> order by id limit 3 offset 0;

第二頁：

select name, chinese, math, english from exam_result order by id limit 3 offset 3;

第三頁，如果結(jié)果不足 3 個，不會有影響：

select name, chinese, math, english from exam_result order by id limit 3 offset 6;

3. Update

語法：

update table_name set column = expr [, column = expr ...][where ...] [order by ...] [limit ...];

實(shí)例：

將 Jane 同學(xué)的數(shù)學(xué)成績變更為 80 分

查看原數(shù)據(jù)：

更新數(shù)據(jù)：

update exam_result set math=80 where name='Jane';

查看更新后數(shù)據(jù)：

將 John 同學(xué)的語文成績變更為 80 分，英語成績變更為 70 分 一次更新多個列

查看原數(shù)據(jù)：

更新數(shù)據(jù)：

update exam_result set chinese=80, english=70 where name='John';

查看更新后數(shù)據(jù)：

將總成績倒數(shù)前三的 3 位同學(xué)的數(shù)學(xué)成績加上 30 分

查看原數(shù)據(jù)：

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

更新數(shù)據(jù)：

update exam_result set math = math + 30 order by chinese+math+english limit 3;

查看更新后的數(shù)據(jù)，注意不能再用查看總分倒數(shù)前三的方式，因?yàn)榻o他們加上 30 分之后，他們就有可能不是倒數(shù)前三了，要單獨(dú)去查看他們?nèi)齻€人的成績：

select name, math, chinese+math+english total from exam_result where name in('Klin', 'Jane', 'Smith');

將所有同學(xué)的英語成績更新為原來的 2 倍

查看原數(shù)據(jù)：

更新數(shù)據(jù)：

update exam_result set english = english * 2;

查看更新后的數(shù)據(jù)：

注意：更新全表的語句慎用！

4. Delete

（1）刪除數(shù)據(jù)

語法：

delete from table_name [where ...] [order by ...] [limit ...];

實(shí)例：

刪除 Lily 同學(xué)的考試成績

查看原數(shù)據(jù)：

刪除數(shù)據(jù)：

delete from exam_result where name='Lily';

查看刪除后的數(shù)據(jù)：

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

我們查看當(dāng)前庫的表：

可以看到有一張 employee 表，我們查看這張表的數(shù)據(jù)如下：

再查看表的結(jié)構(gòu)：

然后我們把整表數(shù)據(jù)刪除：

delete from employee;

再查看就發(fā)現(xiàn)數(shù)據(jù)全沒了：

再插入一條數(shù)據(jù)，自增 id 在原值上繼續(xù)增長：

注意：刪除整表操作要慎用！

（2）截斷表

語法：

truncate [table] table_name

注意：這個操作慎用

只能對整表操作，不能像 delete 一樣針對部分?jǐn)?shù)據(jù)操作；實(shí)際上 MySQL 不對數(shù)據(jù)操作，所以比 delete 更快，但是 truncate 在刪除數(shù)據(jù)的時候，并不經(jīng)過真正的事務(wù)，所以無法回滾；會重置 auto_increme 項(xiàng)

接下來我們重新向 employee 表重新插入數(shù)據(jù)：

截斷整表數(shù)據(jù)，注意影響行數(shù)是 0，所以實(shí)際上沒有對數(shù)據(jù)真正操作：

truncate employee;

再插入一條數(shù)據(jù)，自增 id 在重新增長：

5. 插入查詢結(jié)果

語法：

insert into table_name [(column [, column ...])] select ...

實(shí)例：創(chuàng)建一個新表，插入一個舊表中查詢到的去重后的數(shù)據(jù)

先創(chuàng)建一個舊表，并插入數(shù)據(jù)：

  		mysql> create table duplicate_table (id int, name varchar(20));

  		
  		mysql> INSERT INTO duplicate_table VALUES
  		    -> (100, 'aaa'),
  		    -> (100, 'aaa'),
  		    -> (200, 'bbb'),
  		    -> (200, 'bbb'),
  		    -> (200, 'bbb'),
  		    -> (300, 'ccc');

創(chuàng)建一個新表，和舊表的結(jié)構(gòu)一樣

create table no_duplicate_table like duplicate_table;

將舊表的去重數(shù)據(jù)插入到新表中

insert into no_duplicate_table select distinct * from duplicate_table;

最后查看新表的數(shù)據(jù)

6. 聚合函數(shù)

實(shí)例：

統(tǒng)計班級共有多少同學(xué)

select count(*) from students;

使用 * 做統(tǒng)計，不受 NULL 影響

統(tǒng)計班級收集的 email 有多少

select count(email) from students;

NULL 不會計入結(jié)果

統(tǒng)計本次考試的英語成績分?jǐn)?shù)個數(shù)

select count(english) from exam_result;

count(english) 統(tǒng)計的是全部成績

count(distinct math) 統(tǒng)計的是去重成績數(shù)量

統(tǒng)計數(shù)學(xué)成績總分

select sum(math) from exam_result;

統(tǒng)計平均總分

select avg(chinese+math+english) 平均總分 from exam_result;

返回語文最高分

select max(chinese) from exam_result;

返回 > 70 分以上的語文最低分

select min(chinese) from exam_result where chinese > 70;

7. group by 子句的使用

在 select 中使用 group by 子句可以對指定列進(jìn)行分組查詢。

語法：

select column1, column2, .. from table group by column;

分組的目的是為了分組之后，方便進(jìn)行聚合統(tǒng)計。分組的本質(zhì)就是把一組按照條件拆分成了多個組，進(jìn)行各自組內(nèi)的統(tǒng)計！分組其實(shí)也是"分表"，就是把一張表按照條件在邏輯上拆分成了多個子表，然后分別對各自的子表進(jìn)行聚合統(tǒng)計！

實(shí)例，需要準(zhǔn)備一張雇員信息表（來自oracle 9i的經(jīng)典測試表），表的鏈接：oracle 9i的經(jīng)典測試表 包括：

emp 員工表dept 部門表salgrade 工資等級表

查看 emp 表數(shù)據(jù)：

查看 dept 表的數(shù)據(jù)：

查看 salgrade 表的數(shù)據(jù)：

接下來開始使用分組操作：

顯示每個部門的平均工資和最高工資

很明顯，顯示每個部門的平均工資和最高工資，是需要按部門分組，分別顯示部門號、平均工資和最高工資：

select deptno, avg(sal), max(sal) from emp group by deptno;

顯示每個部門的每種崗位的平均工資和最低工資

每個部門的每種崗位，即要按照部門和崗位進(jìn)行分組：

select deptno, job, avg(sal), min(sal) from emp group by deptno, job;

顯示平均工資低于2000的部門和它的平均工資

先統(tǒng)計各個部門的平均工資

select deptno, avg(sal) from emp group by deptno;

使用 having 對分組進(jìn)行篩選

select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;

注意：having 和 group by 配合使用，對 group by 結(jié)果進(jìn)行過濾，having 經(jīng)常和 group by 搭配使用，作用是對分組進(jìn)行篩選，作用有些像 where.

having 是對聚合后的統(tǒng)計數(shù)據(jù)進(jìn)行條件篩選。其中 having 和 where 的區(qū)別在于：

執(zhí)行的順序不一樣，where 是對任意列進(jìn)行條件篩選，一般是最先執(zhí)行；having 是對分組聚合后的結(jié)果進(jìn)行條件篩選，一般是最后才執(zhí)行！ 8. 相關(guān)題目練習(xí)

Nowcoder：批量插入數(shù)據(jù)

Nowcoder：找出所有員工當(dāng)前薪水salary情況

Nowcoder：查找最晚入職員工的所有信息

Nowcoder：查找入職員工時間排名倒數(shù)第三的員工所有信息

Nowcoder：查找薪水記錄超過15條的員工號emp_no以及其對應(yīng)的記錄次數(shù)t

Nowcoder：獲取所有部門當(dāng)前manager的當(dāng)前薪水情況，給出dept_no, emp_no 以及 salary, 當(dāng)前表示 to_date = ‘9999-01-01’

Nowcoder：從titles表獲取按照title進(jìn)行分組

Leetcode：查找重復(fù)的電子郵箱

Leetcode：大的國家

Leetcode：第N高的薪水

注意：SQL 查詢中各個關(guān)鍵字的執(zhí)行先后順序：

from > on> join > where > group by > with > having > select > distinct > order by > limit

到此這篇關(guān)于MySQL 表查詢必備：基本操作詳解的文章就介紹到這了,更多相關(guān)MySQL 表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論