-- 單表查詢

SELECT sc.*FROM sc

SELECT * FROM course

-- 分頁? LIMIT 從0開始檢索

SELECT * FROM course LIMIT 0,3

SELECT * FROM course limit 3,3

SELECT * FROM course LIMIT 6,1

-- 多表連接查詢

-- 1.等值與非等值連接查詢

SELECT * FROM student;

SELECT * FROM course;

SELECT * FROM sc;

-- 笛卡兒積

SELECT * FROM student,sc;

SELECT * FROM student,sc WHERE student.Sno=sc.Sno

SELECT * FROM student,sc WHERE student.sno<>sc.sno

SELECT A.*,B.cno,B.Grade FROM student as A,SC as B WHERE A.sno=B.sno;

SELECT A.*,B.cno,C.Cname,B.grade FROM student A,sc B,course C

WHERE A.Sno=B.Sno AND B.cno=C.cno;

SELECT A.sno,A.Sname,A.Ssex,C.cname,B.grade FROM student A,sc B,course C

WHERE A.sno=B.sno and B.cno=C.cno;

-- 自身連接

SELECT * FROM course;

SELECT * FROM course A,course B;

-- 選擇直接先修課

SELECT A.cno,A.cname,A.cpno,B.cname FROM course A,course B

WHERE A.Cpno=B.Cno AND A.cno=1;

-- 選擇間接先修課

SELECT A.cno,A.cname,B.cpno FROM course A,course B

WHERE A.cpno=B.cno and A.cno=1;

-- FIRST SECOND

SELECT FIRST.cno, FIRST.cname, SECOND.cpno

FROM course FIRST,course SECOND

WHERE FIRST.cpno=second.cno AND FIRST.cno=1;

-- 選擇pascal語言

SELECT A.cno,A.cname,B.cpno,C.cname FROM course A,course B,course C

WHERE A.cpno=B.Cno AND B.cpno=C.Cno;

-- 外連接

-- 查詢每個學生的選課情況

SELECT * FROM student;

SELECT * FROM course;

SELECT * FROM sc;

-- LEFT JOIN: 以左表為基準，和右邊的表連接，右邊記錄沒用的以null代替

SELECT * FROM student A LEFT JOIN sc B on A.sno=B.sno;

-- 查找選過課程的學生的選課情況

SELECT * FROM student A RIGHT JOIN sc B on A.sno=B.sno;

SELECT * FROM sc A LEFT JOIN student B on A.sno=B.sno;

-- 查找每個學生的選課情況，要顯示課程名稱，三個表

SELECT * FROM student A LEFT JOIN sc B on A.sno=B.sno;

-- 三表連接 INNER JOIN(內連接，或等值連接)：獲取兩個表中字段匹配關系的記錄

SELECT A.*,B.*,C.* FROM student A JOIN sc B INNER JOIN course C

ON A.sno=B.Sno and B.cno=C.Cno;

SELECT A.*,B.cno,C.cname,B.grade FROM student A INNER JOIN Sc B INNER JOIN course C

ON A.sno=B.sno AND B.Cno=C.Cno;

-- INNER JOIN/join(內連接,或等值連接)：獲取兩個表中字段匹配關系的記錄。

-- LEFT JOIN(左連接)：獲取左表所有記錄，即使右表沒有對應匹配的記錄。

-- RIGHT JOIN(右連接)： 與 LEFT JOIN 相反，用于獲取右表所有記錄，即使左表沒有對應匹配的記錄。

-- 復合條件連接

-- 嵌套查詢 IN

SELECT * FROM student;

SELECT * FROM student WHERE Sage in (18,19);

-- 男生的選課情況

SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE Ssex='男');

SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE Ssex in ('男'));