MySQL练习题

有三个表，表S（学生表），C（课程表），SSC（学生选课表）

•         S（SNO，SNAME）代表（学号，姓名）
•         C（CNO,CNAME,CTEACHER）代表（课号，课名，老师）
•         SSC（SNO,CNO,SCGRADE）代表（学号，课号，成绩）

问题：

•         1.找出没有选过’‘黎明’'老师的所有学生姓名
•         2.列出2门以上（含2门）不及格学生姓名及平均成绩
•         3.既学过1号课程又学过2号课程的所有的学生姓名

CREATE TABLE SSC
(
 SNO VARCHAR(200),
 CNO VARCHAR(200),
 SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
 SNO VARCHAR(200 ),
 SNAME VARCHAR(200)
);
CREATE TABLE C
(
 CNO VARCHAR(200),
 CNAME VARCHAR(200),
 CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4'); 
commit;
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SSC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit;

 问题1.找出没有选过’‘黎明’'老师的所有学生姓名：

        步骤①、先找出黎明老师的所有课号：

                select c.cno from c where c.cteacher = '黎明';

        步骤②、找出选过①中课号的所有学号：（易错点：不能找没选过1中课号的所有学号） 

    select ssc.sno from ssc where ssc.cno in (select c.cno from c where c.cteacher = '黎明'); 

         步骤③、在S表中排除②中的学号：

        select s.sname

        from s

        where s.sno not in

   (select ssc.sno from ssc where ssc.cno in (select c.cno from c where c.cteacher = '黎明'));

正确结果：

 问题1.找出没有选过’‘黎明’'老师的所有学生姓名易错点 ：

正确思路是： 应该先找出选过’‘黎明’'老师的所有学生学号，然后最终再排除这些学号

我的错误思路：这四种思路全部错误，尤其是第三、四种，在开发中一般是不能用多表联查的

    ①：先从C表中找出不是黎明老师的课号，然后再通过获得的课号从SSC表中找出对应的学号

select c.cno from c where c.cteacher !='黎明';

select ssc.sno from ssc where ssc.cno in (select c.cno from c where c.cteacher !='黎明');

 例如黎明老师课号为5，其余课号为1、2、3、4

在SSC表中的以下三条记录：

        SNO  CNO SCGRADE

        李四     5

        李四     1   （会被选中）

        李四     2   （会被选中）

        明明李四选了黎明老师课号，但是还是被选中了

    ②：先从C表中找出黎明老师的所有课号，再从SSC表中排除掉选了这些课号的学号：

       #错误sql：

           select ssc.sno from ssc

           where ssc.cno not in

                 (select c.cno from c where c.cteacher = '黎明');

    ③、④：三张表进行连接：S表和SSC表通过学号连接，SSC表和C表通过课号连接，过滤条件为老师不是黎明

#错误sql：
SELECT S.SNAME
FROM S
WHERE S.SNO IN (
    SELECT SSC.SNO
    FROM SSC
    JOIN C ON SSC.CNO = C.CNO
    WHERE C.CTEACHER != '黎明'
);

#这个也是错误的：
 SELECT S.SNAME
    -> FROM S
    -> JOIN SSC ON S.SNO = SSC.SNO
    -> JOIN C ON SSC.CNO = C.CNO
    -> WHERE C.CTEACHER != '黎明';




#实在想要连表查询，只能这样写才正确：

SELECT S.SNAME
FROM S
WHERE S.SNO NOT IN (
    SELECT SSC.SNO
    FROM SSC
    JOIN C ON SSC.CNO = C.CNO
    WHERE C.CTEACHER = '黎明'
);