MySQL?表查詢必備:基本操作詳解
表的增刪查改
表的增刪查改,簡(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)文章希望大家以后多多支持腳本之家!
- MySQL左聯(lián)多表查詢where條件寫(xiě)法示例
- MySQL聯(lián)表查詢的簡(jiǎn)單示例
- mysql嵌套查詢和聯(lián)表查詢優(yōu)化方法
- MySQL 多表查詢實(shí)現(xiàn)分析
- mysql使用from與join兩表查詢的區(qū)別總結(jié)
- MySql實(shí)現(xiàn)跨表查詢的方法詳解
- mysql從一張表查詢批量數(shù)據(jù)并插入到另一表中的完整實(shí)例
- MySQL數(shù)據(jù)庫(kù)高級(jí)查詢和多表查詢
- mysql中各種常見(jiàn)join連表查詢實(shí)例總結(jié)
- Mysql 如何實(shí)現(xiàn)多張無(wú)關(guān)聯(lián)表查詢數(shù)據(jù)并分頁(yè)
相關(guān)文章
MySQL存儲(chǔ)引擎MyISAM與InnoDB區(qū)別總結(jié)整理
今天小編就為大家分享一篇關(guān)于MySQL存儲(chǔ)引擎MyISAM與InnoDB區(qū)別總結(jié)整理,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-03-03mysql使用字符串字段判斷是否包含某個(gè)字符串的方法
在MySQL中,判斷字符串字段是否包含特定子字符串,可使用LIKE操作符、INSTR()函數(shù)、LOCATE()函數(shù)、POSITION()函數(shù)、FIND_IN_SET()函數(shù)以及正則表達(dá)式REGEXP或RLIKE,每種方法適用于不同的場(chǎng)景和需求,LIKE和INSTR()通常用于簡(jiǎn)單包含判斷2024-09-09將MySQL從MyISAM轉(zhuǎn)換成InnoDB錯(cuò)誤和解決辦法
原來(lái)自己用的是為了裝的, 所以在設(shè)置database usage(如下圖1)的時(shí)候按照discuz官方的建議,選的都是Non-Transactional Database Only(只支持MyISAM數(shù)據(jù)引擎的非事務(wù)數(shù)據(jù)庫(kù)),用MyISAM數(shù)據(jù)庫(kù),還沒(méi)涉及到需要InnoDB,因此打算直接不加載INNODB引擎。2011-09-09mysql遷移達(dá)夢(mèng)列長(zhǎng)度超出定義的簡(jiǎn)單解決方法
這篇文章主要介紹了mysql遷移達(dá)夢(mèng)列長(zhǎng)度超出定義解決方法的相關(guān)資料,,在達(dá)夢(mèng)數(shù)據(jù)庫(kù)中,字符串長(zhǎng)度的存儲(chǔ)方式與MySQL不同,導(dǎo)致遷移過(guò)程中出現(xiàn)數(shù)據(jù)長(zhǎng)度不足的錯(cuò)誤,解決方法包括在MySQL中將varchar類型修改為varchar(10char)以強(qiáng)制字符存儲(chǔ),需要的朋友可以參考下2024-12-12MySQL事務(wù)(transaction)看這篇就足夠了
M事務(wù)的實(shí)現(xiàn)是基于數(shù)據(jù)庫(kù)的存儲(chǔ)引擎,不同的存儲(chǔ)引擎對(duì)事務(wù)的支持程度不一樣,下面這篇文章主要給大家介紹了關(guān)于MySQL事務(wù)(transaction)的相關(guān)資料,需要的朋友可以參考下2022-11-11mysql不走索引的幾個(gè)問(wèn)題小結(jié)
MySQL中不走索引的問(wèn)題通常發(fā)生在查詢中使用了函數(shù),這會(huì)使索引失效,從而影響查詢性能,本文就介紹了mysql不走索引的幾個(gè)問(wèn)題小結(jié),感興趣的可以了解一下2023-08-08mysql啟用skip-name-resolve模式時(shí)出現(xiàn)Warning的處理辦法
在優(yōu)化MYSQL配置時(shí),加入 skip-name-resolve ,在重新啟動(dòng)MYSQL時(shí)檢查啟動(dòng)日志,發(fā)現(xiàn)有警告信息2012-07-07