SQL數據查詢之——單表查詢

一、SQL數據查詢的一般格式

數據查詢是資料庫的核心操作。SQL提供了SELECT語句進行數據查詢，其一般格式為：

SELECT [ALL | DISTINCT]<目標列表達式>[,<目標列表達式>]···
FROM<表名或視圖名>[,<表名或視圖名>···] | (SELECT語句>)[AS]<別名>
[WHERE<條件表達式>]
[GROUP BY<列名1>[HAVING<條件表達式>]]
[ORDER BY<列名2>[ASC | DESC]];

整個SELECT語句的含義是，根據WHERE子句的條件表達式從FROM子句指定的基本表、視圖或派生表中找出滿足條件的元組，再按SELECT子句中的目標列表達式選出元組中的屬性值形成結果表。

如果有GROUP BY子句，則將結果按<列名1>的值進行分組，該屬性列值相等的元組為一個組。通常會在每組中作用聚集函數。如果GROUP BY子句帶HAVING短語，則只有滿足指定條件的組才予以輸出。

如果有ORDER BY子句，則結果還要按<列名2>的值的升序或降序排序。

SELECT語句既可以完成簡單的單表查詢，也可以完成複雜的連接查詢和嵌套查詢。

二、SQL單表查詢（僅涉及一個表的查詢）

1.選擇表中的若幹列

（1）查詢指定列

查詢全體學生的學號與姓名

SELECT Sno,Sname
FROM Student;

查詢全體學生的姓名、學號、所在系

SELECT Sname,Sno,Sdept
FROM Student;

（2）查詢全部列

查詢全體學生的詳細記錄

    SELECT *
    FROM Students;
等價於
    SELECT Sno,Sname,Ssex,Sage,Sdept
    FROM Student;

（3）查詢經過計算的值

查詢全體學生的姓名及其出生年份

SELECT Sname,2014-Sage  //查詢結果的第2列是一個 算術表達式
FROM Student;

註意：用當時的年份（假設為2014年）減去學生的年齡，這樣所得的即是學生的出生年份。

查詢全體學生的姓名、出生年份和所在的院系，要求用小寫字母表示系名

SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)
FROM Student;

2.選擇表中的若幹組

（1）消除取值重覆的行

查詢選修了課程的學生學號

SELECT DISTINCT Sno
FROM SC;

（2）查詢滿足條件的元組

查詢滿足指定條件的元組可以通過WHERE子句實現。WHERE子句常用的查詢條件如下表所示

查詢電腦科學系全體學生的名單

SELECT Sname
FROM Student
WHERE Sdept='CS'

查詢所有年齡在20歲以下的學生姓名及其年齡

SELECT Sname,Sage
FROM Student
WHERE Sage<20;

查詢考試成績不合格的學生的學號

SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

查詢年齡在20~23歲之間的學生的姓名、系別和年齡

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23

查詢年齡在20~23歲之間的學生的姓名、系別和年齡

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23

查詢電腦科學系（CS）、數學系（MA）和信息系（IS）學生的姓名、系別和年齡

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN('CS','MA','IS');

查詢學號為201215121的學生的詳細情況

    SELECT *
    FROM Student
    WHERE Sno LIKE '201215121'
等價於
    SELECT *
    FROM Student
    WHERE Sno='201215121'

此處介紹下字元匹配

謂詞LIKE可以用來進行字元串的匹配。其一般語法格式如下：

[NOT] LIKE'<匹配串>' [ESCAPE '<換碼字元>']

其含義是查找指定的屬性列值與<匹配串>相匹配的元組。<匹配串>可以是一個完整的字元串，也可以含有通配符 % 和 _ 。其中：

• %（百分號）代表任意長度（長度可以為0）的字元串。例如：a%b 表示以a開頭，以b結尾的任意長度的字元串。如acb、addgb、ab等。
• _（下劃線）代表任意單個字元。例如：a_b表示以a開頭，以b結尾的長度為3的任意字元串。如acb、agb等。

查詢所有姓劉的學生的姓名、學號和性別

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '劉%';

查詢姓“歐陽”且全名為三個漢字的學生的姓名

SELECT Sname
FROM Student
WHERE Sname LIKE '歐陽_'

註意：資料庫字集為ASCII時一個漢字需要兩個_；當字元集為GBK時只需要一個。

查詢名字中第二個字為“陽”的學生的姓名和學號

SELECT Sname,Sno,
FROM Student
WHERE Sname LIKE '_陽%';

查詢所有不姓劉的學生的姓名、學號和性別

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '劉%';

 如果用戶要查詢的字元串本身就含有通配符%或_，這時就要使用 ESCAPE '<換碼字元>' 短語對通配符進行轉義了。

查詢DB_Design 課程的課程號和學分

SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB \ _Design' ESCAPE '\';

 ESCAPE '\' 表示 “\” 為換碼字元。這樣匹配串中緊跟在 “\” 後面的字元“_”不再具有通配符的含義，轉義為普通的“_”字元。

查詢以“DB_”開頭，且倒數第三個字元為i的課程的詳細情況

SELECT *
FROM Course
WHERE Cname LIKE 'DB \_%i__'ESCAPE '\';

某些學生選修課程後沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;  /*分數Grade是空值*/

 註意：這裡的“IS”不能用等號（=）代替。

查所有有成績的學生學號和課程號

SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

查詢電腦科學系年齡在20歲以下的學生姓名

SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

3.ORDER BY 子句

用戶可以用ORDER BY子句對查詢結果按照一個或多個屬性列的升序（ASC）或降序（DESC）排列，預設值為升序。

查詢選修了3號課程的學生的學號及其成績，查詢結果按分數的降序排列

SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列

SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

4. 聚集函數

為了進一步方便用戶，增強檢索功能，SQL提供了許多聚集函數，主要有：

如果指定 DISTINCT 短語，則表示在計算時要取消指定列中的重覆值。

如果不指定 DISTINCT 短語或指定 ALL 短語（ALL為預設值），則表示不取消重覆值。

查詢學生總人數

SELECT COUNT(*)
FROM Student;

查詢選修了課程的學生人數

SELECT COUNT(DISTINCT Sno)
FROM SC;

計算選修1號課程的學生平均成績

SELECT AVG(Grade)
FROM SC
WHERE Cno='1';

查詢選修1號課程的學生最高分數

SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

查詢學生201215012選修課程的總學分數

SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;

 註意：WHERE 子句中是不能用聚集函數作為條件表達式的。聚集函數只能用於SELECT子句和GROUP BY中的HAVING子句。

5.GROUP BY子句

將查詢結果按某一列或多列的值分組，值相等的為一組。

求各個課程號及相應的選課人數

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

查詢選修了三門以上課程的學生學號

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

查詢平均成績大於等於90分的學生學號和平均成績

    SELECT Sno,AVG(Grade)
    FROM SC
    WHERE AVG(Grade)>=90 （錯誤！）
    GROUP BY Sno;
因為WHERE子句中是不能用聚集函數作為條件表達式的，正確的查詢語句應該是：
    SELECT Sno,AVG(Grade)
    FROM SC
    GROUP BY Sno;    
    HAVING AVG(Grade)>=90;

三、總結

此次整理了SQL數據查詢中有關單表查詢的程式，其中應格外註意

• 字元匹配中 % 和 _ 的區別。
• ESCAPE 的 換碼操作。
• 聚集函數只能用於 SELECT子句 和 GROUP BY子句 中的 HAVING 子句。