第四模塊MySQL50題作業，以及由作業引申出來的一些高端玩法

一、表關係

先參照如下表結構創建7張表格，並創建相關約束

1. 班級表class

create table class
    (
    cid int primary key auto_increment,
    caption char(10),
    grade_id int
    );

insert into class values
(1,'少一一班',1),
(2,'少二一班',2),
(3,'少三二班',3),
(4,'少四一班',4),
(5,'少五三班',5);

2. 學生表student

create table student
    (
    sid int primary key auto_increment,
    sname char(10),
    gender enum('男','女') not null,
    class_id int
    );

insert into student values
(1,'喬丹','女',1),
(2,'艾弗森','女',1),
(3,'科比','男',2),
(4,'葫蘆娃','男',3),
(5,'張三豐','男',5),
(6,'洞房不敗','男',4),
(7,'櫻木花道','男',2),
(8,'松島菜菜子','女',3),
(9,'洞房不敗','女',5);

3. 老師表teacher

create table teacher
    (
    tid int primary key auto_increment,
    tname char(10)
    );

insert into teacher values
(1,'張三'),
(2,'李四'),
(3,'王五'),
(4,'蕭峰'),
(5,'一休哥'),
(6,'諸葛'),
(7,'李四');

4. 課程表course

create table course
(
cid int primary key auto_increment,
cname char(10),
teacher_id int
);

insert into course values
(1,'生物',1),
(2,'體育',1),
(3,'物理',2),
(4,'數學',3),
(5,'語文',4),
(6,'英語',2),
(7,'土遁?沙地送葬',5),
(8,'夏日喂蚊子大法',3),
(9,'麻將牌九撲克千術',6);

5. 成績表score

create table score
(
sid int primary key auto_increment,
student_id int,
course_id int,
score int
);

insert score values
(1,1,1,60),
(2,1,2,21),
(3,2,2,99),
(4,3,3,56),
(5,4,1,56),
(6,5,3,94),
(7,5,4,40),
(8,6,4,80),
(9,7,3,37),
(10,8,5,100),
(11,8,6,89),
(12,8,7,0),
(13,3,8,45),
(14,7,1,89),
(15,2,7,89),
(16,2,1,61);

6. 年級表class_grade

create table class_grade
    (
    gid int primary key auto_increment,
    gname char(10)
    );

insert class_grade values
(1,'少一年級'),
(2,'少二年級'),
(3,'少三年級'),
(4,'少四年級'),
(5,'少五年級');

7. 班級任職表teach2cls

create table teach2cls
    (
    tcid int primary key auto_increment,
    tid int,
    cid int
    );

insert into teach2cls values
(1,1,1),
(2,1,2),
(3,2,1),
(4,3,2),
(5,4,5),
(6,5,3),
(7,5,5),
(8,6,2),
(9,6,4),
(10,6,3),
(11,4,1),
(12,1,4);

二、操作表

★註：由於樣本數量有限，為了能夠得到足夠的查詢結果，所有題目中涉及到“超過”或“以上”字樣的，均預設為包含該值

（例如：查詢教授課程超過2門的老師的id和姓名，視作教授課程數>=2）

1、自行創建測試數據；

（創建語句見"一、表關係"）

2、查詢學生總人數

select 
    count(*) as 學生總人數 
from 
    student;

3、查詢“生物”課程和“物理”課程成績都及格的學生id和姓名

【查法1——子查詢】

select 
    sid, sname
from 
    student 
where 
    sid in
    (
    select 
        student_id
    from 
        score
    where 
        student_id in
            (select 
                student_id
            from 
                score
            where 
                course_id = (select cid from course where cname = '生物') 
                and 
                score >= 60)
        and 
            course_id = 
            (select cid from course where cname = '物理') 
        and 
            score >= 60
    );

【查法2——聯表】

select 
    sid, 
    sname 
from 
    student
where 
    sid in 
    (
    select 
        t1.student_id 
    from 
        (
        select 
            student_id 
        from 
            score
        where 
            course_id = (select cid from course where cname = '生物') 
            and 
            score >= 60
        ) as t1
        inner join 
            (
            select 
                student_id 
            from 
                score 
            where 
                course_id = (select cid from course where cname = '物理') 
                and 
                score >= 60
            ) as t2
        on 
            t1.student_id=t2.student_id
    );

4、查詢每個年級的班級數，取出班級數最多的前三個年級

select 
    class.grade_id, 
    class_grade.gname, 
    count(class.cid) as 班級數
from 
    class inner join class_grade 
    on class.grade_id=class_grade.gid
group by 
    class.grade_id
order by 
    count(class.cid) desc
limit 3;

5、查詢平均成績最高和最低的學生的id和姓名以及平均成績

select 
    stu.sid, 
    stu.sname, 
    avg(score) as 平均成績
from 
    student as stu inner join score as sco
    on stu.sid = sco.student_id
group by 
    stu.sid
having 
    avg(score) = 
        (
        select 
            avg(score) 
        from 
            score 
        group by 
            student_id
        order by 
            avg(score) desc
        limit 1
        ) 
    or 
        avg(score) = 
            (
            select 
                avg(score) 
            from 
                score 
            group by 
                student_id
            order by 
                avg(score) asc
            limit 1
            );

6、查詢每個年級的學生人數

select 
    t1.gname, 
    count(s.sid) as 學生人數
from 
    (
    select 
        * 
    from 
        class as c inner join class_grade as g 
        on c.grade_id = g.gid
    ) as t1
    inner join 
        student as s 
    on 
        t1.cid = s.class_id
group by 
    t1.gid;

7、查詢每位學生的學號，姓名，選課數，平均成績

select 
    stu.sid as 學號,
    stu.sname as 姓名,
    count(sco.course_id) as 選課數,
    avg(sco.score) as 平均成績
from 
    student as stu left join score as sco 
    on stu.sid = sco.student_id
group by 
    sco.student_id;

8、查詢學生編號為“2”的學生的姓名、該學生成績最高的課程名、成績最低的課程名及分數

select 
    t1.sname as 姓名,
    t2.cname as 課程名,
    t1.score as 分數
from 
    (select 
        stu.sid, stu.sname, sco.course_id, sco.score 
    from 
        student as stu inner join score as sco 
        on stu.sid = sco.student_id 
        where stu.sid=2) as t1
    inner join
        course as t2 
    on 
        t1.course_id = t2.cid
group by 
    t2.cid
having 
    score in (max(score),min(score));

9、查詢姓“李”的老師的個數和所帶班級數；

select 
    count(te.tid) as 姓李老師個數,
    count(tc.cid) as 所帶班級數
from 
    teacher as te inner join teach2cls as tc
    on te.tid = tc.tid
where 
    te.tname regexp "^李.*"
group by 
    te.tid;

10、查詢班級數小於5的年級id和年級名；

select 
    c.grade_id as 年級id,
    g.gname as 年級名
from 
    class as c inner join class_grade as g
    on c.grade_id = g.gid
group by 
    c.grade_id
having 
    count(c.cid)<5;

11、查詢班級信息，包括班級id、班級名稱、年級、年級級別(12為低年級，34為中年級，56為高年級)，示例結果如下；

select 
    cid as 班級id,
    caption as 班級名稱,
    gname as 年級,
    case
        when g.gid in (1,2) then '低年級'
        when g.gid in (3,4) then '中年級'
        when g.gid in (5,6) then '高年級'
        else '其他' 
    end as 年級級別
from 
    class as c inner join class_grade as g
    on c.grade_id = g.gid;

12、查詢學過“張三”老師2門課以上的同學的學號、姓名；

select 
    stu.sid as 學號,
    stu.sname as 姓名
from 
    student as stu inner join score as sco 
    on stu.sid = sco.student_id
where 
    sco.course_id in 
        (
        select 
            c.cid
        from 
            teacher as t inner join course as c
            on t.tid = c.teacher_id
        where 
            t.tname = '張三'
        )
group by 
    stu.sid
having 
    count(sco.course_id) >= 2;

13、查詢教授課程超過2門的老師的id和姓名；

select
    tid as id,
    tname as 姓名
from 
    teacher as t inner join course as c 
    on t.tid = c.teacher_id
group by 
    c.teacher_id
having 
    count(c.cid) >= 2;

14、查詢學過編號“1”課程和編號“2”課程的同學的學號、姓名；

select 
    sid as 學號,
    sname as 姓名
from 
    student
where 
    sid in 
        (
        select 
            student_id 
        from 
            score
        where 
            student_id in 
                (
                select 
                    student_id 
                from 
                    score
                where 
                    course_id = 1
                )
            and 
                course_id = 2
        );

15、查詢沒有帶過高年級的老師id和姓名；

select 
    tid as 老師id,
    tname as 姓名
from 
    teacher
where 
    tid not in 
        (
        select 
            tc.tid
        from 
            class as c inner join teach2cls as tc 
            on c.cid = tc.cid
        where 
            c.grade_id in (5,6)
        );

16、查詢學過“張三”老師所教的所有課的同學的學號、姓名；

select 
    distinct
    stu.sid as 學號,
    stu.sname as 姓名
from 
    student as stu inner join score as sco 
    on stu.sid = sco.student_id
where 
    sco.course_id in 
        (
        select 
            c.cid 
        from 
            teacher as t inner join course as c 
            on t.tid = c.teacher_id
        where 
            t.tname = "張三"
        );

17、查詢帶過超過2個班級的老師的id和姓名；

select 
    tid as id,
    tname as 姓名
from 
    teacher
where 
    tid in 
        (
        select 
            tid 
        from 
            teach2cls
        group by 
            tid
        having 
            count(cid) >= 2
        );

18、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學的學號、姓名；

select 
    sid as 學號,
    sname as 姓名
from 
    student
where 
    sid in
        (
        select 
            t1.student_id
        from 
            (
            select 
                * 
            from 
                score
            where 
                course_id = 1
            ) as t1
            inner