表的增刪查改

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

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

1. Create

語(yǔ)法：

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é)號(hào)', -> name varchar(20) not null, -> email varchar(20) -> );

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

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

例如插入一個(gè)數(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）插入否則更新

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

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

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

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

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

例如我們想插入 Jane 這位同學(xué)的數(shù)據(jù)，但是我們并不知道這位同學(xué)是否存在于這張表中，所以我們使用上面的語(yǔ)法，假設(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)插入成功后，會(huì)有 2 rows affected (0.00 sec) 這樣的提示，即：

這個(gè)提示的含義如下：

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

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

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

2. Retrieve

Retrieve 指的是表的讀取。

語(yǔ)法：

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

我們先創(chuàng)建一個(gè)表結(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 '語(yǔ)文成績(jī)',
			    -> math float default 0.0 comment '數(shù)學(xué)成績(jī)',
			    -> english float default 0.0 comment '英語(yǔ)成績(jī)'
			    -> );

接下來(lái)插入一些數(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. 全列查詢

語(yǔ)法：SELECT * FROM 表名;

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

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

例如：

b. 指定列查詢

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

select id, name, math from exam_result;

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

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

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

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

語(yǔ)法：SELECT column [AS] alias_name […] FROM table_name;

例如將上面統(tǒng)計(jì)的總成績(jī)的字段改為總分：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 放在語(yǔ)句最后，后面跟上條件即可，下面舉一些實(shí)例：

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

select name, english from exam_result where english < 60;

語(yǔ)文成績(jī)?cè)?[80, 90] 分的同學(xué)及語(yǔ)文成績(jī)

使用 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é)成績(jī)是 53 或者 91 或者 98 分的同學(xué)及數(shù)學(xué)成績(jī)

使用 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 開(kāi)頭的同學(xué)

% 匹配任意多個(gè)（包括 0 個(gè)）任意字符

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

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

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

select name from exam_result where name like 'J_';

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

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

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

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

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

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

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

英語(yǔ)成績(jī) > 80 并且姓名首字母不以 M 開(kāi)頭的同學(xué)

and 于 not 的使用

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

名字首字母以 T 開(kāi)頭的同學(xué)，否則要求總成績(jī) > 200 并且 語(yǔ)文成績(jī) < 數(shù)學(xué)成績(jī) 并且 英語(yǔ)成績(jī) > 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 的查詢

接下來(lái)我們切回 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é)果排序

語(yǔ)法：

asc 為升序（從小到大）

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

默認(rèn)為 asc

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

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

下面看實(shí)例：

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

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é)各門(mén)成績(jī)，依次按 數(shù)學(xué)降序，英語(yǔ)升序，語(yǔ)文升序的方式顯示

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

多字段排序，排序優(yōu)先級(jí)隨書(shū)寫(xiě)順序：

查詢同學(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é)成績(jī)，結(jié)果按數(shù)學(xué)成績(jī)由高到低顯示

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

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

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

語(yǔ)法：

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

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

實(shí)例：

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

第一頁(yè)：

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

第二頁(yè)：

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

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

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

3. Update

語(yǔ)法：

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

實(shí)例：

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

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

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

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

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

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

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

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

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

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

將總成績(jī)倒數(shù)前三的 3 位同學(xué)的數(shù)學(xué)成績(jī)加上 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)齻€(gè)人的成績(jī)：

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

將所有同學(xué)的英語(yǔ)成績(jī)更新為原來(lái)的 2 倍

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

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

update exam_result set english = english * 2;

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

注意：更新全表的語(yǔ)句慎用！

4. Delete

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

語(yǔ)法：

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

實(shí)例：

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

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

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

delete from exam_result where name='Lily';

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

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

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

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

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

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

delete from employee;

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

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

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

（2）截?cái)啾?/h4>

語(yǔ)法：

truncate [table] table_name

注意：這個(gè)操作慎用

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

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

截?cái)嗾頂?shù)據(jù)，注意影響行數(shù)是 0，所以實(shí)際上沒(méi)有對(duì)數(shù)據(jù)真正操作：

truncate employee;

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

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

語(yǔ)法：

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

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

先創(chuàng)建一個(gè)舊表，并插入數(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)建一個(gè)新表，和舊表的結(jié)構(gòu)一樣

create table no_duplicate_table like duplicate_table;

將舊表的去重?cái)?shù)據(jù)插入到新表中

insert into no_duplicate_table select distinct * from duplicate_table;

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

6. 聚合函數(shù)

實(shí)例：

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

select count(*) from students;

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

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

select count(email) from students;

NULL 不會(huì)計(jì)入結(jié)果

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

select count(english) from exam_result;

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

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

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

select sum(math) from exam_result;

統(tǒng)計(jì)平均總分

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

返回語(yǔ)文最高分

select max(chinese) from exam_result;

返回 > 70 分以上的語(yǔ)文最低分

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

7. group by 子句的使用

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

語(yǔ)法：

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

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

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

emp 員工表dept 部門(mén)表salgrade 工資等級(jí)表

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

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

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

接下來(lái)開(kāi)始使用分組操作：

顯示每個(gè)部門(mén)的平均工資和最高工資

很明顯，顯示每個(gè)部門(mén)的平均工資和最高工資，是需要按部門(mén)分組，分別顯示部門(mén)號(hào)、平均工資和最高工資：

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

顯示每個(gè)部門(mén)的每種崗位的平均工資和最低工資

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

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

顯示平均工資低于2000的部門(mén)和它的平均工資

先統(tǒng)計(jì)各個(gè)部門(mén)的平均工資

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

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

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

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

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

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

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

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

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

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

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

Nowcoder：獲取所有部門(mén)當(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：大的國(guó)家

Leetcode：第N高的薪水

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

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

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

最新評(píng)論