mySQL 教程 第4章 資料查詢

標籤：

mySQL運算子

這些運算子在SQL查詢中用得到。

算數運算子

+ 加

- 減

* 乘

/ DIV 除

% MOD 取餘數

比較子

= 等於

<> != 不等於

<

<=

>

>=

Between 指定範圍

In 存在於集合

Is null 為NULL

Is not null 不為空白

Like 萬用字元

Regexp rlike Regex

比如

比較子，返回的結果 真為1，假為0

運算子優先順序

最高------------------------------------------à最低

！ () * / div % mod - + = <>= like between case when then else not and or

單表查詢

查詢所有列所有行

select * from TStudent

查詢指定列

select studentID,Sname,Sex,cardID from TStudent

為列指定別名

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼 from TStudent

指定查詢條件

查詢條件中可以使用< > <= >= != 比較子

1. 查詢網路班學生

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級

from TStudent where class=‘網路與網站開發‘

2. 查詢網路班性別是女的學生

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級

from TStudent where class=‘網路與網站開發‘ and sex=‘女‘

3. 尋找軟體測試班或性別是女的學生

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級 from TStudent where class=‘軟體測試‘ or Sex=‘女‘

4. 使用like模糊查詢

使用字元比較符 like

% 0個或多個字串

_ 任何單個的字元

[]在指定地區或集合內的任何單個字元

[^]不在指定地區或集合內的任何單個字元

尋找姓名中含有“立”字的學生

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級 from TStudent where Sname like ‘%茂%‘

思考：尋找姓名最後一個字是“茂”的同學，寫出SQL語句。

5. 尋找姓名是“韓立剛”，“韓旭”的學生

記下上面查詢結果的兩個名字。尋找這兩個名字的記錄。

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級 from TStudent where Sname in (‘趙俊茂‘,‘魏清茂‘)

6. 尋找出1975年到1980年出生的學生

使用比較操作符 = > < >= <= <>

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級,Birthday 生日

from TStudent where Birthday>‘1980‘ and Birthday<‘1985‘

7. 使用關係運算子

關係運算子優先順序 not and or 如下面的例子 去掉括弧結果一樣

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級,Birthday 生日

from TStudent where Birthday>‘19820101‘ and Birthday<‘19841230‘ or sex=‘女‘

通過使用括弧更運算順序

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級,Birthday 生日

from TStudent where Birthday>‘19820101‘ and (Birthday<‘19841230‘ or sex=‘女‘)

8. 查詢在一定範圍內的值

尋找生日在1985年到1990年之間的學生

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級,Birthday 生日

from TStudent where Birthday BETWEEN ‘19850000‘ and ‘19860000‘

尋找不在1985年到1986年出生的學生

select studentID 學號,Sname 姓名,Sex 性別,cardID 社會安全號碼,class 班級,Birthday 生日

from TStudent where Birthday not BETWEEN ‘19850000‘ and ‘19860000‘

盡量使用between而不使用and和比較操作符表示的運算式

如果想返回不在指定地區的行時，使用not between。這樣會降低資料查詢的速度。

9. 查詢空值

insert into TStudent (studentid,sname) values (‘19999‘,‘張永超‘)

select * from TStudent where studentid=‘19999‘

可以看到沒有插入的列值為Null

select * from TStudent where cardID is NULL

你絕不要這樣寫

select * from TStudent where cardID=‘‘

使用is not null來查詢指定列中非空的行

select * from TStudent where cardID is not NULL

格式化結果集

1. 排序

預設是降序 desc升序 asc 降序

select * from TStudent order by cardID

select * from TStudent order by cardID asc

按兩列排序 先按班級排序 再按學號排序

select * from TStudent order by Class,studentid desc

以下命令就是按第7列和第1列排序，輸出結果和以上的一樣

select * from TStudent order by 7,1 desc

2. 消除重複的行

以下命令查看有幾個班

select DISTINCT class 班級 from TStudent

多表查詢

在TStudent表插入兩個學生

insert into TStudent values (‘90006‘,‘張勇‘,‘男‘,‘132302198903044565‘,‘19880203‘,‘[email protected]‘,‘JAVA‘,‘20120803‘);

insert into TStudent values (‘90007‘,‘趙潔‘,‘女‘,‘132302198905044565‘,‘19880503‘,‘[email protected]‘,‘JAVA‘,‘20120803‘)

這兩個學生沒有成績

3. 使用Join內串連多張表

內串連使用inner關鍵字，可以省去。

沒有成績的學生沒有列出來。

使用外鍵匹配可以講多個表組成一張大表，可以看到組成的大表記錄條數4個學生*2科

select a.*,b.*,c.* 分數 FROM `TStudent` a inner join `TScore` b on a.`StudentID`=b.`StudentID` inner join `TSubject` c on b.`subJectID`=c.`subJectID`

從以上三個表組成的大表找到電腦網路課程分數大於80分的記錄

select sname 姓名,sex 性別,c.`subJectName` 學科,b.`mark` 分數 FROM `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID` where c.`subJectName`=‘電腦網路‘ and b.`mark`>80

可以看到輸出結果來自三張表

4. 左串連

做串連使用left有串連使用right

select a.*,b.* FROM `TStudent` a left join `TScore` b on a.`StudentID`=b.`StudentID`使用左串連可以看到沒有成績學生也出現在表中

思考：左串連

5. 自串連

自己串連自己，比如尋找重名的學生

select a.studentid,a.sname,b.studentid,b.sname from TStudent a join TStudent b on a.sname=b.sname where a.studentid<>b.studentid

6. 使用子查詢

使用子查詢，輸出結果只能來自一張表，其他表作為查詢的條件。

為什麼使用子查詢---子查詢可以把一個複雜的查詢分解成一系列邏輯步驟，這樣就可以用一個單個的語句解決複雜的查詢問題。

為什麼使用串連而不使用子查詢---執行效力差不多，子查詢可能要求查詢最佳化工具執行額外的操作，比如排序，而這些操作將會影響查詢的處理策略。

select sname 姓名 from `TStudent` where Studentid in (select StudentID from `TScore` where mark>90)

以下SQL語句子查詢中又嵌套了子查詢，查詢電腦網路，輸出結果只能來自一個表。

select sname 姓名 from `TStudent` where Studentid in (select StudentID from `TScore` where mark>90 and subjectID in (select subjectID from `TSubject` where subJectName=‘電腦網路‘))

7. 使用any關鍵字

建立兩個表

create table tb1 (num int not null);

create table tb2 (num int not null);

插入資料

insert into tb1 VALUES (2),(5),(13),(25),(32);

insert into tb2 VALUES (6),(8),(20),(43),(70),(4);

尋找tb1，條件是只要值大於tb2的任何一個值就可以

select num from tb1 where num>any(select num from tb2)

8. 使用關鍵字all

返回tb2所有值都小的值

select num from tb1 where num<all(select num from tb2)

9. 使用exist關鍵字

如果成績表中的分數有大於80分的記錄，就返回TStudent表中的第一條記錄

當然也可以使用not exist

select * from `TStudent` where EXISTS (select * from `TScore` where mark>80) limit 1

有大於100分的學生，就查出學生記錄。

select * from `TStudent` where EXISTS (select * from `TScore` where mark>100) limit 1

10. 使用union合并查詢結果

尋找tb1和tb2大於40的資料

Select num from tb1 where num>40 union select num from tb2 where num>40

資料分組和匯總

11. Group by進行資料匯總

如果使用聚集合函式，則將對錶中的所有記錄的某個欄位進行匯總，然後產生單個的值。如果想產生多個匯總值，同時使用聚集合函式和group by 語句，聯合使用having和group by子句能夠使結果集只包含滿足條件的記錄。

計算各個班“電腦網路”平均分

select a.Class 班級,AVG(b.`mark`) 電腦網路平均分 from `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID` where c.`subJectName`=‘電腦網路‘ group by a.`Class`

Group by 必須和集合函數結合使用

12. 聯合使用group by子句與having子句

Having 相當於條件

尋找電腦網路課程平均分大於80分的班級

select a.Class 班級,AVG(b.`mark`) 電腦網路平均分 from `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID` where c.`subJectName`=‘電腦網路‘ group by a.`Class` having AVG(b.`mark`)>75

13. 思考題：統計各個班級所有科目的總分。

14. 查詢指定的行數

使用limit顯示前2行資料

select StudentID,Sname,sex,cardID from `TStudent` limit 2

顯示從第2條記錄後面的3條記錄

select StudentID,Sname,sex,cardID from `TStudent` limit 2,3

15. 使用聚集合函式統計行數

Count函數不統計Null記錄

select count(*) from `TStudent`;

select count(cardID) 登記了身份證的學生數量 from `TStudent`，沒有登記身份證不統計。

16. 思考:統計男生數量

使用Regex

一個Regex中的可以使用以下保留字  

符號	說明
^	所匹配的字串以後面的字串開頭
$	所匹配的字串以前面的字串結尾
.	匹配任何字元（包括新行
a*	匹配任意多個a（包括空串)
a+	匹配任意多個a（不包括空串)
a?	匹配一個或零個a
de|abc	匹配de或abc
(abc)*	匹配任意多個abc（包括空串)
[a-dx]	匹配“a”、“b”、“c”、“d”或“x”
[^a-dx]	匹配除“a”、“b”、“c”、“d”、“x”以外的任何字元。“[”、“]”必須成對使用

 

17. 查詢特定字元或字串開頭的記錄

select sname,email from `TStudent` where email REGEXP ‘^KY‘

18. 查詢以特定字元或字串結尾的記錄

select sname,email from `TStudent` where sname REGEXP ‘富$‘

19. 使用*和+來匹配字串中的多個字元

*匹配前面字元任意多次，包括0次，+匹配前面字元至少一次。

select sname,email from `TStudent` where email REGEXP ‘^TB*‘

select sname,email from `TStudent` where email REGEXP ‘^TB+‘

20. 匹配指定字串

select sname,email from `TStudent` where email REGEXP ‘BZ‘

21. 匹配字串的任意一個

select sname,email from `TStudent` where emai

mySQL 教程 第4章 資料查詢