SELECT敘述 NULL COUNT DISTINCT GROUP BY NULL是一種特殊的欄位值，NULL和""(兩個雙引號連在一起)不一樣 CREATE TABLE stmd ( depart_class CHAR(6), student_no CHAR(9), student_name VARCHAR(20), address VARCHAR(60) ); INSERT INTO stmd VALUES("164D11","10114D001","張大一",NULL); INSERT INTO stmd VALUES("164D11","10114D002","張大一","");
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<>""紀錄 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 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帶頭者
