MySQL?表查詢必備:基本操作詳解
表的增刪查改
表的增刪查改,簡稱表的 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)行全列查詢,因為:
查詢的列越多,意味著需要傳輸?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 條件 比較運算符:
邏輯運算符:
使用 where 篩選條件只需將 where 放在語句最后,后面跟上條件即可,下面舉一些實例:
英語不及格的同學(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 條件中比較運算符兩側(cè)都是字段
select name, math, english from exam_result where english > math;
總分在 200 分以下的同學(xué)
where 條件中使用表達(dá)式
別名不能用在 where 條件中,因為有 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)不要依賴這個順序。
下面看實例:
同學(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 子句中可以使用列別名,因為 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,避免因為表中數(shù)據(jù)過大,查詢?nèi)頂?shù)據(jù)導(dǎo)致數(shù)據(jù)庫卡死。
實例:
按 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 ...];
實例:
將 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ù)前三的方式,因為給他們加上 30 分之后,他們就有可能不是倒數(shù)前三了,要單獨去查看他們?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 ...];
實例:
刪除 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ù)操作;實際上 MySQL 不對數(shù)據(jù)操作,所以比 delete 更快,但是 truncate 在刪除數(shù)據(jù)的時候,并不經(jīng)過真正的事務(wù),所以無法回滾;會重置 auto_increme 項
接下來我們重新向 employee 表重新插入數(shù)據(jù):
截斷整表數(shù)據(jù),注意影響行數(shù)是 0,所以實際上沒有對數(shù)據(jù)真正操作:
truncate employee;
再插入一條數(shù)據(jù),自增 id 在重新增長:
5. 插入查詢結(jié)果
語法:
insert into table_name [(column [, column ...])] select ...
實例:創(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ù)
實例:
統(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)計!分組其實也是"分表",就是把一張表按照條件在邏輯上拆分成了多個子表,然后分別對各自的子表進(jìn)行聚合統(tǒng)計!
實例,需要準(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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL存儲引擎MyISAM與InnoDB區(qū)別總結(jié)整理
今天小編就為大家分享一篇關(guān)于MySQL存儲引擎MyISAM與InnoDB區(qū)別總結(jié)整理,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2019-03-03將MySQL從MyISAM轉(zhuǎn)換成InnoDB錯誤和解決辦法
原來自己用的是為了裝的, 所以在設(shè)置database usage(如下圖1)的時候按照discuz官方的建議,選的都是Non-Transactional Database Only(只支持MyISAM數(shù)據(jù)引擎的非事務(wù)數(shù)據(jù)庫),用MyISAM數(shù)據(jù)庫,還沒涉及到需要InnoDB,因此打算直接不加載INNODB引擎。2011-09-09MySQL事務(wù)(transaction)看這篇就足夠了
M事務(wù)的實現(xiàn)是基于數(shù)據(jù)庫的存儲引擎,不同的存儲引擎對事務(wù)的支持程度不一樣,下面這篇文章主要給大家介紹了關(guān)于MySQL事務(wù)(transaction)的相關(guān)資料,需要的朋友可以參考下2022-11-11mysql啟用skip-name-resolve模式時出現(xiàn)Warning的處理辦法
在優(yōu)化MYSQL配置時,加入 skip-name-resolve ,在重新啟動MYSQL時檢查啟動日志,發(fā)現(xiàn)有警告信息2012-07-07