資料庫MySQL-查詢語句
- 2020 年 3 月 27 日
- 筆記
1.5 查詢語句
語法：select [選項] 列名 [from 表名] [where 條件] [group by 分組] [order by 排序][having 條件] [limit 限制]
1.5.1 欄位表達式
-- 可以直接輸出內容 mysql> select '鋤禾日當午'; +------------+ | 鋤禾日當午 | +------------+ | 鋤禾日當午 | +------------+ 1 row in set (0.00 sec) -- 輸出表達式 mysql> select 10*10; +-------+ | 10*10 | +-------+ | 100 | +-------+ 1 row in set (0.00 sec) mysql> select ch,math,ch+math from stu; +------+------+---------+ | ch | math | ch+math | +------+------+---------+ | 80 | NULL | NULL | | 77 | 76 | 153 | | 55 | 82 | 137 | | NULL | 74 | NULL | -- 表達式部分可以用函數 mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.6669325378415478 | +--------------------+ 1 row in set (0.00 sec)
通過as給欄位取別名
mysql> select '鋤禾日當午' as '標題'; -- 取別名 +------------+ | 標題 | +------------+ | 鋤禾日當午 | +------------+ 1 row in set (0.00 sec) mysql> select ch,math,ch+math as '總分' from stu; +------+------+------+ | ch | math | 總分 | +------+------+------+ | 80 | NULL | NULL | | 77 | 76 | 153 | | 55 | 82 | 137 | | NULL | 74 | NULL | -- 多學一招：as可以省略 mysql> select ch,math,ch+math '總分' from stu; +------+------+------+ | ch | math | 總分 | +------+------+------+ | 80 | NULL | NULL | | 77 | 76 | 153 | | 55 | 82 | 137 | | NULL | 74 | NULL |
1.5.2 from子句
from：來自，from後面跟的是數據源。數據源可以有多個。返回笛卡爾積。
插入測試表
create table t1( str char(2) ); insert into t1 values ('aa'),('bb'); create table t2( num int ); insert into t2 values (10),(20);
測試
-- from子句 mysql> select * from t1; +------+ | str | +------+ | aa | | bb | +------+ 2 rows in set (0.00 sec) -- 多個數據源,返回笛卡爾積 mysql> select * from t1,t2; +------+------+ | str | num | +------+------+ | aa | 10 | | bb | 10 | | aa | 20 | | bb | 20 | +------+------+ 4 rows in set (0.00 sec)
1.5.3 dual表
dual表是一個偽表。在有些特定情況下，沒有具體的表的參與，但是為了保證select語句的完整又必須要一個表名，這時候就使用偽表。
mysql> select 10*10 as 結果 from dual; +------+ | 結果 | +------+ | 100 | +------+ 1 row in set (0.00 sec)
1.5.4 where子句
where後面跟的是條件，在數據源中進行篩選。返回條件為真記錄
MySQL支援的運算符
-- 比較運算符 > 大於 < 小於 >= 大於等於 <= 小於等於 = 等於 != 不等於 -- 邏輯運算符 and 與 or 或 not 非 -- 其他 in | not in 欄位的值在枚舉範圍內 between…and|not between…and 欄位的值在數字範圍內 is null | is not null 欄位的值不為空
例題：
-- 查找語文成績及格的學生 mysql> select * from stu where ch>=60; -- 查詢語文和數學都及格的學生 mysql> select * from stu where ch>=60 and math>=60; -- 查詢語文或數學不及格的學生 mysql> select * from stu where ch<60 or math<60;
思考：如下語句輸出什麼？
mysql> select * from stu where 1; -- 輸出所有數據 mysql> select * from stu where 0; -- 不輸出數據
思考：如何查找北京和上海的學生
-- 通過or實現 mysql> select * from stu where stuaddress='北京' or stuaddress='上海'; -- 通過in語句實現 mysql> select * from stu where stuaddress in ('北京','上海'); -- 查詢不是北京和上海的學生 mysql> select * from stu where stuaddress not in ('北京','上海');
思考：查找年齡在20~25之間
-- 方法一： mysql> select * from stu where stuage>=20 and stuage<=25; -- 方法二： mysql> select * from stu where not(stuage<20 or stuage>25); -- 方法三：between...and... mysql> select * from stu where stuage between 20 and 25; -- 年齡不在20~25之間 mysql> select * from stu where stuage not between 20 and 25;
思考：
-- 查找缺考的學生 mysql> select * from stu where ch is null or math is null; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25301 | 張秋麗 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25304 | 歐陽俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 | +--------+----------+--------+--------+---------+------------+------+------+ -- 查找沒有缺考的學生 mysql> select * from stu where ch is not null and math is not null; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25305 | 諸葛麗麗 | 女 | 23 | 7 | 河南 | 72 | 56 | | s25318 | 爭青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | | s25319 | 梅超風 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 7 rows in set (0.00 sec) -- 查找需要補考的學生 mysql> select * from stu where ch<60 or math<60 or ch is null or math is null; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25301 | 張秋麗 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25304 | 歐陽俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 | | s25305 | 諸葛麗麗 | 女 | 23 | 7 | 河南 | 72 | 56 | +--------+----------+--------+--------+---------+------------+------+------+ 4 rows in set (0.00 sec)
練習：
-- 1、查找學號是s25301,s25302,s25303的學生 mysql> select * from stu where stuno in ('s25301','s25302','s25303'); +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 張秋麗 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | +--------+---------+--------+--------+---------+------------+------+------+ 3 rows in set (0.00 sec) -- 2、查找年齡是18~20的學生 mysql> select * from stu where stuage between 18 and 20; +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 張秋麗 | 男 | 18 | 1 | 北京 | 80 | NULL | +--------+---------+--------+--------+---------+------------+------+------+ 1 row in set (0.00 sec)
1.5.5 group by 【分組查詢】
將查詢的結果分組，分組查詢目的在於統計數據。
-- 查詢男生和女生的各自語文平均分 mysql> select stusex,avg(ch) '平均分' from stu group by stusex; +--------+---------+ | stusex | 平均分 | +--------+---------+ | 女 | 72.2500 | | 男 | 77.0000 | +--------+---------+ 2 rows in set (0.00 sec) -- 查詢男生和女生各自多少人 mysql> select stusex,count(*) 人數 from stu group by stusex; +--------+------+ | stusex | 人數 | +--------+------+ | 女 | 4 | | 男 | 5 | +--------+------+ 2 rows in set (0.00 sec) -- 查詢每個地區多少人 mysql> select stuaddress,count(*) from stu group by stuaddress; +------------+----------+ | stuaddress | count(*) | +------------+----------+ | 上海 | 1 | | 北京 | 3 | | 天津 | 2 | | 河北 | 2 | | 河南 | 1 | +------------+----------+ 5 rows in set (0.00 sec) -- 每個地區的數學平均分 mysql> select stuaddress,avg(math) from stu group by stuaddress; +------------+-----------+ | stuaddress | avg(math) | +------------+-----------+ | 上海 | 76.0000 | | 北京 | 74.5000 | | 天津 | 83.0000 | | 河北 | 72.0000 | | 河南 | 56.0000 | +------------+-----------+ 5 rows in set (0.00 sec)
查詢欄位是普通欄位，只取第一個值
通過group_concat()函數將同一組的值連接起來顯示
mysql> select group_concat(stuname),stusex,avg(math) from stu group by stusex; +-------------------------------------+--------+-----------+ | group_concat(stuname) | stusex | avg(math) | +-------------------------------------+--------+-----------+ | 李斯文,諸葛麗麗,梅超風,Tabm | 女 | 70.5000 | | 張秋麗,李文才,歐陽俊雄,爭青小子,Tom | 男 | 77.2500 | +-------------------------------------+--------+-----------+ 2 rows in set (0.00 sec)
多列分組
mysql> select stuaddress,stusex,avg(math) from stu group by stuaddress,stusex; +------------+--------+-----------+ | stuaddress | stusex | avg(math) | +------------+--------+-----------+ | 上海 | 男 | 76.0000 | | 北京 | 女 | 82.0000 | | 北京 | 男 | 67.0000 | | 天津 | 男 | 83.0000 | | 河北 | 女 | 72.0000 | | 河南 | 女 | 56.0000 | +------------+--------+-----------+ 6 rows in set (0.00 sec)
小結：
1、如果是分組查詢，查詢欄位是分組欄位和聚合函數。 2、查詢欄位是普通欄位，只取第一個值 3、group_concat()將同一組的數據連接起來
1.5.6 order by排序
asc：升序【默認】
desc：降序
-- 按年齡的升序排列 mysql> select * from stu order by stuage asc; mysql> select * from stu order by stuage; -- 默認是升序 -- 按總分降序 mysql> select *,ch+math '總分' from stu order by ch+math desc;
多列排序
-- 年齡升序，如果年齡一樣，按ch降序排列 mysql> select * from stu order by stuage asc,ch desc;
思考如下程式碼表示什麼含義
select * from stu order by stuage desc,ch desc; #年齡降序，語文降序 select * from stu order by stuage desc,ch asc; #年齡降序，語文升序 select * from stu order by stuage,ch desc; #年齡升序、語文降序 select * from stu order by stuage,ch; #年齡升序、語文升序
1.5.7 having條件
having：是在結果集上進行條件篩選
例題
-- 查詢女生 mysql> select * from stu where stusex='女'; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25305 | 諸葛麗麗 | 女 | 23 | 7 | 河南 | 72 | 56 | | s25319 | 梅超風 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 4 rows in set (0.00 sec) -- 查詢女生 mysql> select * from stu having stusex='女'; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25305 | 諸葛麗麗 | 女 | 23 | 7 | 河南 | 72 | 56 | | s25319 | 梅超風 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 4 rows in set (0.00 sec) -- 查詢女生姓名 mysql> select stuname from stu where stusex='女'; +----------+ | stuname | +----------+ | 李斯文 | | 諸葛麗麗 | | 梅超風 | | Tabm | +----------+ 4 rows in set (0.00 sec) -- 使用having報錯，因為結果集中沒有stusex欄位 mysql> select stuname from stu having stusex='女'; ERROR 1054 (42S22): Unknown column 'stusex' in 'having clause'
小結：having和where的區別：
where是對原始數據進行篩選，having是對記錄集進行篩選。
1.5.8 limit
語法：limit [起始位置]，顯示長度
-- 從第0個位置開始取，取3條記錄 mysql> select * from stu limit 0,3; -- 從第2個位置開始取，取3條記錄 mysql> select * from stu limit 2,3; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | | s25304 | 歐陽俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 | | s25305 | 諸葛麗麗 | 女 | 23 | 7 | 河南 | 72 | 56 | +--------+----------+--------+--------+---------+------------+------+------+ 3 rows in set (0.00 sec)
起始位置可以省略，默認是從0開始
mysql> select * from stu limit 3; +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 張秋麗 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | +--------+---------+--------+--------+---------+------------+------+------+ 3 rows in set (0.00 sec)
例題：找出班級總分前三名
mysql> select *,ch+math total from stu order by (ch+math) desc limit 0,3; +--------+----------+--------+--------+---------+------------+------+------+-------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | total | +--------+----------+--------+--------+---------+------------+------+------+-------+ | s25318 | 爭青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | 178 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | 165 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | 153 | +--------+----------+--------+--------+---------+------------+------+------+-------+ 3 rows in set (0.00 sec)
多學一招：limit在update和delete語句中也是可以使用的。
-- 前3名語文成績加1分 mysql> update stu set ch=ch+1 order by ch+math desc limit 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 -- 前3名刪除 mysql> delete from stu order by ch+math desc limit 3; Query OK, 3 rows affected (0.00 sec)
1.5.9 查詢語句中的選項
查詢語句中的選項有兩個：
1、 all：顯示所有數據 【默認】
2、 distinct：去除結果集中重複的數據
mysql> select all stuaddress from stu; +------------+ | stuaddress | +------------+ | 北京 | | 北京 | | 天津 | | 河南 | | 河北 | | 北京 | +------------+ 6 rows in set (0.00 sec) -- 去除重複的項 mysql> select distinct stuaddress from stu; +------------+ | stuaddress | +------------+ | 北京 | | 天津 | | 河南 | | 河北 | +------------+ 4 rows in set (0.00 sec)
