已知是以下4張表：
–1.學(xué)生表
Student(s_id, s_name, s_birth, s_sex) –學(xué)生編號,學(xué)生姓名, 出生年月,學(xué)生性別
–2.課程表
Course(c_id, c_name, t_id) – –課程編號, 課程名稱, 教師編號
–3.教師表
Teacher(t_id, t_name) –教師編號,教師姓名
–4.成績表
Score(s_id, c_id, s_score) –學(xué)生編號,課程編號,分數(shù)
首先，我們需要搞清楚這4張表的關(guān)聯(lián)關(guān)系，這樣有助于編寫對應(yīng)的SQL語句。下面是我做的關(guān)于4個表的關(guān)聯(lián)圖：
搞清表之間的關(guān)聯(lián)關(guān)系后，開始創(chuàng)建數(shù)據(jù)庫和表，我用的是navicat客戶端。
1.創(chuàng)建表：
(1)學(xué)生表：
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT ' ',
`s_birth` VARCHAR(20) NOT NULL DEFAULT ' ',
`s_sex` VARCHAR(10) NOT NULL DEFAULT ' ',
PRIMARY KEY(`s_id`)
);
(2)成績表：
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
(3)課程表：
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT ' ',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
(4)教師表：
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT ' ',
PRIMARY KEY(`t_id`)
);
2.插入數(shù)據(jù)
(1)插入學(xué)生表數(shù)據(jù)：
insert into Student values('01','趙雷','1990-01-01','男');
insert into Student values('02','錢電','1990-12-21','男');
insert into Student values('03','孫風(fēng)','1990-05-20','男');
insert into Student values('04','李云','1990-08-06','男');
insert into Student values('05','周梅','1991-12-01','女');
insert into Student values('06','吳蘭','1992-03-01','女');
insert into Student values('07','鄭竹','1989-07-01','女');
insert into Student values('08','王菊','1990-01-20','女');
(2)插入成績表數(shù)據(jù)：
insert into Score values('01','01',80);
insert into Score values('01','02',90);
insert into Score values('01','03',99);
insert into Score values('02','01',70);
insert into Score values('02','02',60);
insert into Score values('02','03',80);
insert into Score values('03','01',80);
insert into Score values('03','02',80);
insert into Score values('03','03',80);
insert into Score values('04','01',50);
insert into Score values('04','02',30);
insert into Score values('04','03',20);
insert into Score values('05','01',76);
insert into Score values('05','02',87);
insert into Score values('06','01',31);
insert into Score values('06','03',34);
insert into Score values('07','02',89);
insert into Score values('07','03',98);
(3)插入課程表數(shù)據(jù)：
insert into Course values('01','語文','02');
insert into Course values('02','數(shù)學(xué)','01');
insert into Course values('03','英語','03');
(4)插入教師表數(shù)據(jù)：
insert into Teacher values('01','張三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');
以上4張表已創(chuàng)建完成并插入數(shù)據(jù)，下面詳細解析經(jīng)典題型
****-- 1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分數(shù)
理解題意：查詢學(xué)生信息及01、02課程的成績在'01'課程分數(shù)大于'02'課程條件下
分析思路：由于要查詢學(xué)生信息和分數(shù)，那么需要用2個表，即"Student"和"Score"表，多表查詢需要用到Join語句連接，要使用子查詢查詢各課程的成績。
方法一：
#查詢'01'課程的成績
SELECT s_id, s_score as s1 From score where c_id='01';
#查詢'02'課程的成績
SELECT s_id, s_score as s2 From score where c_id='02';
（注：s1,s2 分別是'01'和'02'課程對應(yīng)分數(shù)的別名，方便作比較）
#查詢'01'課程分數(shù)>'02'課程分數(shù)的s_id,s1,s2
SELECT r1.s_id, s1,s2 From?
(SELECT s_id, s_score as s1 From score where c_id='01')r1,
(SELECT s_id, s_score as s2 From score where c_id='02')r2?
where r1.s_id=r2.s_id and s1>s2;
得到的結(jié)果集如下：
#使用JOIN連接"student"表,得到學(xué)生信息及課程成績
SELECT * FROM Student s Right Join
(SELECT r1.s_id, s1,s2 From?
(SELECT s_id, s_score as s1 From score where c_id='01')r1,
(SELECT s_id, s_score as s2 From score where c_id='02')r2?
where r1.s_id=r2.s_id and s1>s2)R
On s.s_id=r.s_id;
最終結(jié)果應(yīng)該是這樣的：
方法二：
#使用INNER JOIN 連接子查詢，將滿足條件的s_id, s1,s2查詢出來
SELECT r1.s_id, s1,s2 from
(SELECT s_id, s_score as s1 from score where c_id='01')r1
INNER JOIN
(SELECT s_id, s_score as s2 from score where c_id='02')r2
on r1.s_id=r2.s_id and s1>s2
#再使用INNER JOIN 連接Student表和上面的臨時表R
SELECT s.* ,R.s1,R.s2 from student s
INNER JOIN
(SELECT r1.s_id, s1,s2 from
(SELECT s_id, s_score as s1 from score where c_id='01')r1
INNER JOIN
(SELECT s_id, s_score as s2 from score where c_id='02')r2
on r1.s_id=r2.s_id and s1>s2)R
on s.s_id=R.s_id;
得到如下結(jié)果：
總結(jié)：方法一和方法二都能得到正確的結(jié)果，相較于方法二，方法一邏輯更清晰，語句較短，比較推薦。
-- 2、查詢"01"課程比"02"課程成績低的學(xué)生的信息及課程分數(shù)
解題思路與上題一致。
只需將上題的查詢語句稍加改變即可，如下：
SELECT * FROM Student s Right Join
(SELECT r1.s_id, s1,s2 From
(SELECT s_id, s_score as s1 From score where c_id='01')r1,
(SELECT s_id, s_score as s2 From score where c_id='02')r2?
where r1.s_id=r2.s_id and s1<s2)R
On s.s_id=r.s_id;
結(jié)果集：
-- 3、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
分析思路：要使用AVG(),那么就要用到group by，結(jié)果要有學(xué)生姓名，那么需要用的join連接student表
方法一：子查詢
#查詢平均成績大于等于60的學(xué)生編碼和平均成績
SELECT s_id,AVG(s_score) avs from score GROUP BY s_id HAVING avs>=60;
#把學(xué)生姓名加進來
SELECT s.s_id, s_name, r.avs from student s right join
(SELECT s_id,AVG(s_score) avs from score GROUP BY s_id HAVING avs>=60)r?
on s.s_id=r.s_id ;
方法二：不使用join語句
SELECT s.s_id, s_name, r.avs from student s ，
(SELECT s_id,AVG(s_score) avs from score GROUP BY s_id HAVING avs>=60)r?
where s.s_id=r.s_id ;
結(jié)果和上面一樣。
-- 4、查詢平均成績小于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績(包括有成績的和無成績的)
分析思路：有成績的且平均分小于60的學(xué)生和沒有成績的同學(xué)，分2部再連接在一起
#沒成績的學(xué)生編碼，姓名和平均成績（null）
SELECT s_id, s_name, null from student where s_id not in(SELECT DISTINCT s_id from score) ;??
#查詢平均成績低于60分的學(xué)生編碼，姓名和平均成績
SELECT r.s_id, s_name, r.avs from student s,
(SELECT s_id, ROUND(avg(s_score),2) avs from score?
GROUP BY s_id HAVING avs<60)r?
where s.s_id=r.s_id;
用了ROUND函數(shù)，使平均成績保留2位小數(shù)。
#最后使用UNION合并到一起
SELECT r.s_id, s_name, r.avs from student s, (SELECT s_id,ROUND(avg(s_score),2) avs from score GROUP BY s_id HAVING avs<60)r where s.s_id=r.s_id
UNION
SELECT s_id,s_name,null from student where s_id not in(SELECT DISTINCT s_id from score)
-- 5、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績
分析思路：要用到COUNT(),SUM(), group by 分組，student表和score表，join連接，注意是所有學(xué)生，也就是包括沒成績的，所以要以student表為基準(zhǔn)表
SELECT r.s_id, s.s_name, r.count_course, r.total_score from student s
LEFT JOIN
(SELECT s_id, count(*) as count_course, sum(s_score) as total_score from score GROUP BY s_id)r on s.s_id=r.s_id;
還有另一種寫法：
select s.s_id, s.s_name, count(sc.c_id) as count_course, sum(sc.s_score) as sum_score from student s
left join score sc on s.s_id=sc.s_id? GROUP BY s.s_id, s.s_name;
-- 6、查詢"李"姓老師的數(shù)量
分析思路:想到用LIKE，count()函數(shù)，teacher表
SELECT count(*) from teacher where t_name like "李%";
-- 7、查詢學(xué)過"張三"老師授課的同學(xué)的信息
分析思路：4張表都要用到，需要從teacher先找到張三老師的編號，在course表中找到教的課程，在從score表找選修這門課程的學(xué)生編碼，在連接student表查詢出學(xué)生信息。
SELECT * from student where s_id in
(SELECT s_id from score where c_id=
(SELECT c_id from course where t_id=
(SELECT T_id from teacher where t_name='張三')))
這還真是一層一層的查（嵌套查詢）
還有一種方法:（聯(lián)合查詢）
SELECT student.* from student where s_id in
(SELECT s_id from score sc,course c,teacher t
where sc.c_id=c.c_id
and c.t_id=t.t_id
and t_name='張三');
結(jié)果是一樣的。
-- 8、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息
分析思路：首先要知道張三老師教的哪門課程，然后逆向思維，先找到學(xué)過張三老師課程的學(xué)生，用not in找到?jīng)]學(xué)過的學(xué)生信息
SELECT * from student where s_id not in
(SELECT s_id from score where c_id in
(SELECT c_id from course where t_id in
(SELECT t_id from teacher where t_name='張三')));
還有一種方法：
SELECT * from student where s_id not in
(SELECT s_id from score sc, course c, teacher t
where sc.c_id = c.c_id?
and c.t_id=t.t_id?
and t_name='張三')
相較第一種，第二種更簡潔些。和7題相反，邏輯相似。
-- 9、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息
分析思路：分別查詢學(xué)習(xí)2門課程的學(xué)生編號，再找到相同的，最后連接student表查詢。
SELECT r.s_id,s_name,s_birth,s_sex from student
right join (SELECT s1.s_id from (SELECT s_id from score where c_id='01')s1
INNER JOIN (SELECT s_id from score where c_id='02')s2 on s1.s_id=s2.s_id)r
on student.s_id=r.s_id;
還有一種簡潔的查詢語句：
SELECT s.* from student s, score a, score b
where s.s_id = a.s_id? and s.s_id = b.s_id and a.c_id='01' and b.c_id='02';
-- 10、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
分析思路：查詢學(xué)生編號在學(xué)習(xí)01課程的學(xué)生編號但不在學(xué)習(xí)02課程的編號里。
SELECT s.* from student s where s.s_id in
(SELECT s_id from score where c_id='01')?
and s.s_id not in (SELECT s_id from score where c_id='02');
以上10個題，其中1，3，4，8，9，10題很重要，也有些難度，需要搞清邏輯，當(dāng)然方法不是唯一的，我相信還有其他解法的，學(xué)習(xí)sql一定要會做這幾道題哦，大家相互參考，共同學(xué)習(xí)。
