SQL SELECT語法整理

假設有以下資料表 class, classmain, teacher, student, dept, tea, stud ~

(1) SELECT * FROM CLASS;

(2) SELECT sid, cid FROM CLASS;

(3) SELECT sid AS s, cid AS c FROM CLASS;

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

INSERT INTO myclass (class_no,class_name)
SELECT cid, classname FROM classmain;

(5) SELECT * FROM classmain WHERE cid>'C004';

(6) SELECT * FROM class WHERE score>60 AND cid='C005';

(7) SELECT * FROM class WHERE cid='C005' AND NOT score>60;

(8) SELECT * FROM class WHERE score IS NULL;

(9) SELECT * FROM class WHERE score IS NOT NULL;

(10) SELECT * FROM classmain WHERE classname LIKE 'data%';

(11) SELECT * FROM classmain WHERE classname LIKE 'data%' OR classname LIKE '%mgm';

(12) SELECT * FROM classmain WHERE cid IN ('C001','C003','C005');

(13) SELECT * FROM classmain WHERE cid NOT IN ('C001','C003','C005');

(14) SELECT * FROM class WHERE score BETWEEN 60 AND 100;

(15) SELECT COUNT(*) FROM student;

(16) SELECT COUNT(score) FROM class;
COUNT(score)只計算score非NULL

(17) SELECT COUNT(score) AS a FROM class;

(18) SELECT AVG(score) FROM class WHERE sid='S0004';

(19) SELECT MAX(score) FROM class WHERE sid='S0004';

(20) SELECT MIN(score) FROM class WHERE sid='S0004';

(21) SELECT SUM(score) FROM class WHERE sid='S0004';

(22) SELECT * FROM class ORDER BY score ASC;

(23) SELECT * FROM class ORDER BY score DESC;

(24) SELECT * FROM class ORDER BY sid,score;

(25) SELECT * FROM class ORDER BY sid ASC,score DESC;

(26) SELECT * FROM class ORDER BY score LIMIT 2;

(27) SELECT * FROM class ORDER BY score LIMIT 0,10;

(28) SELECT DISTINCT sid FROM class;

(29) SELECT * FROM class GROUP BY sid;

(30) SELECT sid, AVG(score) FROM class GROUP BY sid;

(31) SELECT sid, AVG(score) as a FROM class GROUP BY sid HAVING a>80;