SELECT SQL語法總整理

web - 5月 21, 2015


SELECT的基本語法如下~ 依照給予的條件，抓出資料。

SELECT * | {[DISTINCT, DISTINCTROW] field_list}
FROM table_list
WHERE conditions
GROUP BY {field_list} [ASC,DESC]
HAVING conditions
ORDER BY {field_list} [ASC,DESC]

假設有以下表單
student(studno, studname, deptid) 學生資料表
course(yearsemester, courseid, studno, score) 選課單
coursemain(courseid, coursename, coursecredit, courseflag, deptid) 課程基本資料表
teacher(teano, teaname, deptid) 教師資料表
courseopen(yearsemester, courseid, teano, coursetime, room) 教師開課表
dept(deptid, deptname) 科系資料表

1. SELECT * FROM student; 從student表單抓出所有資料

2. SELECT studname FROM student; 從student表單抓出所有學生姓名

3. SELECT DISTINCT studname FROM student; 從student表單抓出所有不重複的學生姓名

4. SELECT studname, studno FROM student; 從student表單抓出所有學生姓名、學號

5. SELECT DISTINCTROW studname, studno FROM student; 從student表單抓出所有不重複列的學生姓名、學號

DISTINCT與 DISTINCTROW差異是什麼呢? DISTINCT指某欄位不要重複，DISTINCTROW指整個列不要重複。

6. SELECT * FROM student, course; 從student,course表單抓出所有資料
但是以上這個SQL句子其實沒有意義，所以必須加上條件，變成下面的句子。

7. SELECT * FROM student, course WHERE course.studno = student.studno;
抓出符合條件[course.studno = student.studno]的所有資料
但是因為使用 *列出全部資料，資料會很雜亂，所以要再改為以下句子。

WHERE後面的conditions條件，就是一個邏輯判斷

例如

SELECT * FROM course WHERE courseid>'C004';

SELECT * FROM course WHERE score>60 AND courseid='C005';

SELECT * FROM course WHERE courseid='C005' AND NOT score>60;

SELECT * FROM course WHERE score IS NULL;

SELECT * FROM course WHERE score IS NOT NULL;

SELECT * FROM coursemain WHERE coursename LIKE 'data%';

SELECT * FROM coursemain WHERE coursename LIKE 'data%' OR coursename LIKE '%mgm';

SELECT * FROM coursemain WHERE courseid IN ('C001','C003','C005');

SELECT * FROM coursemain WHERE courseid NOT IN ('C001','C003','C005');

SELECT * FROM course WHERE score BETWEEN 60 AND 100;

8. SELECT student.studname, student.studno, course.courseid FROM student, course WHERE  course.studno = student.studno;

9. 可以再簡化成為

SELECT s.studname, s.studno, c.courseid FROM student s, course c WHERE  c.studno = s.studno;

10. 還可以再簡化成為

SELECT studname, s.studno, courseid FROM student s, course c WHERE  c.studno = s.studno;

因為studname只存在student，courseid只存在course，沒特別註明也不會弄錯
但是studno存在兩個表單，所以必須註明 s.studno與c.studno

11. 如果希望排序，則可以再改成

SELECT studname, s.studno, courseid FROM student s, course c WHERE  c.studno = s.studno
ORDER BY studname;

12. 如果希望以群組的方式列出，就可以使用

SELECT studname, s.studno, courseid FROM student s, course c WHERE  c.studno = s.studno
GROUP BY s.studno;

13. 如果要列出課程平均分數60分以上的學生學號，使用如下指令

SELECT studno, AVG(score) as ss FROM course GROUP BY studno HAVING ss>60;

14. 模擬SELECT INTO語法 (MYSQL不支援SELECT INTO)

INSERT INTO mycourse (cid,sno) SELECT courseid, studno FROM course;

15. 選出的欄位可以透過函數表示，例如

SELECT COUNT(*) FROM student;

SELECT COUNT(score) FROM course;

SELECT COUNT(score) AS a FROM course;

SELECT AVG(score) FROM course WHERE studno='S0004';

SELECT MAX(score) FROM course WHERE studno='S0004';

16. SELECT * FROM student WHERE studname LIKE [conditions]

[conditions]
'A%' 以A開頭的所有字串
'%s' 以s結尾的所有字串
'%in%' 在字串任意位置有in的所有字串
'_ _ _ _' 字串長度為4的所有字串
'Qua_' 以Qua開頭的任一長度為4的所有字串
'_re_' 以re為第2, 3字元的任一長度為4的所有字串
'_re%' 以re為第2, 3字元開頭，並至少長度為3的所有字串
'%re_' 以re為倒數第2, 3字元結尾，並至少長度為3的所有字串

17. 關於排序 ORDER BY

SELECT * FROM course ORDER BY score;
SELECT * FROM course ORDER BY CASE WHEN score>60 THEN courseid ELSE studno END;

18. escape字元 (!)

因為%跟_都有特殊意義，但是如果我們要判斷字串中有%或是_，怎麼辦?
可以使用如下: 使用 '100!%' 來比對 100%這個字串; '!_op' 來比對 _op 這個字串;

19. pattern 比對

'[a-c]at' 表示 bat, cat都符合，但是fat不符合。
'[bcf]at 表示 bat, cat, fat都符合。
'[^c]at' 表示只有cat不符合。
'se[^n]%' 表示以se開頭，並且第3字元不能是n。

20. BETWEEN使用 (適用於數字/字串/日期)

SELECT * FROM product WHERE prod_inventory BETWEEN 100 AND 1000;
SELECT * FROM product WHERE prod_inventory NOT BETWEEN 100 AND 1000;

21. IN的使用

SELECT * FROM product WHERE prod_id IN (1,2,3);
SELECT * FROM product WHERE prod_no IN ('1','2','3');
或是
SELECT * FROM product WHERE prod_id IN (另外一個SELECT子句)

22. NULL的使用

SELECT * FROM product WHERE prod_id IS NULL;
SELECT * FROM product WHERE prod_id IS NOT NULL;

23. CASE的使用

CASE 
   WHEN condition1 THEN result1 
   WHEN condition2 THEN result2 
   WHEN conditionN THEN resultN 
   ELSE result
END;

SELECT OrderID, Quantity, 
(CASE 
WHEN Quantity > 30 THEN 'The quantity is greater than 30' 
WHEN Quantity = 30 THEN 'The quantity is 30' 
ELSE 'The quantity is under 30' 
END AS QuantityText) 
FROM OrderDetails;



標籤 :

張貼留言

0 留言