第4週-SELECT NULL COUNT GROUP BY

1. SELECT * FROM stmd WHERE address IS NULL;

   查詢 stmd 欄位 address IS NULL紀錄

2. SELECT * FROM stmd WHERE address IS NOT NULL;

   查詢 stmd 欄位 address IS NOT NULL紀錄

3. SELECT * FROM stmd WHERE address="";

   查詢 stmd 欄位 address=""紀錄

4. SELECT * FROM stmd WHERE address<>"";

   查詢 stmd 欄位 address<>""紀錄

 

NULL 和 "" 不一樣

 

 

5. SELECT COUNT(*) FROM stmd;

   查詢 stmd 紀錄筆數

6. SELECT COUNT(depart_class) FROM stmd;

   相當於

   SELECT COUNT(depart_class) FROM stmd WHERE depart_class IS NOT NULL;

   查詢 stmd 欄位 depart_class 不為NULL的筆數

7. SELECT COUNT(depart_class) FROM stmd WHERE depart_class IS NULL;

   查詢 stmd 欄位 depart_class 不為NULL的筆數

8. SELECT DISTINCT depart_class FROM stmd;

   查詢 stmd 欄位 depart_class 唯一者

9. SELECT COUNT(DISTINCT depart_class) FROM stmd;

   查詢 stmd 欄位 depart_class 唯一者的筆數

10.SELECT * FROM stmd GROUP BY depart_class

   查詢 stmd 欄位 以depart_class分群 只會出現每個班級的第一筆

11.SELECT depart_class,COUNT(*) FROM stmd GROUP BY depart_class

   查詢 stmd 欄位 以depart_class分群每一分群的筆數

12.SELECT * FROM stmd GROUP BY depart_class ORDER BY depart_class

   查詢 stmd 所有欄位 以depart_class分群 按 depart_class由小到大

13.SELECT * FROM stmd GROUP BY depart_class,student_no 

   查詢 stmd 欄位 以depart_class,student_no分群

14.SELECT * FROM stmd WHERE student_no LIKE "101%" GROUP BY depart_class,student_no 

   查詢 stmd 欄位 以depart_class,student_no分群 條件為學號為101帶頭者

14-1 select depart_class from stmd group by depart_class having count(*)>10;
查詢班級人數>10之班級

15.查詢 stmd 欄位 以depart_class,student_no分群，條件為班級代碼為164D者

16.查詢 stmd 欄位 以depart_class,student_no分群，條件為一年級者

17.查詢 stmd 條件為一年級的學生人數

18.查詢 stmd 條件為一年級的台北市學生人數

19.查詢 stmd 條件為一年級的台北市學生資料

20.查詢 stmd 條件為一年級的台北市學生資料，以depart_class,student_no分群

 

21.查詢 stmd 條件為台北校區一年級住台北市學生資料，以depart_class,student_no分群

22.查詢 stmd 條件為台北校區資管系所有不住台北市學生資料，以depart_class,student_no分群

23.查詢 stmd 條件為台北校區的班級

24.查詢 stmd 條件為全校的班級

25.查詢 stmd 條件為台北校區一年級的班級