《SQL面试50题》刷题笔记 day3（知识点：通配符过滤、分组函数group by）

问题3 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

select s.sid, s.sname, count(sc.cid) as num, sum(sc.sscore) as total
from student s 
join score sc 
on s.sid=sc.sid
group by sc.sid;

这个语句中group by 放在外面了，先联结表后筛选

SELECT s.sid,s.sname,scs.countcourse,scs.sumscore
from student s,
(SELECT sc.sid,COUNT(sc.cid) as countcourse,SUM(sc.sscore) as sumscore FROM score sc GROUP BY sc.sid) scs
where s.sid=scs.sid;

运行结果：

+-----+--------+-----+-------+
| sid | sname  | num | total |
+-----+--------+-----+-------+
| 01  | 赵雷   |   3 |   269 |
| 02  | 钱电   |   3 |   210 |
| 03  | 孙风   |   3 |   240 |
| 04  | 李云   |   3 |   100 |
| 05  | 周梅   |   2 |   163 |
| 06  | 吴兰   |   2 |    65 |
| 07  | 郑竹   |   2 |   187 |
+-----+--------+-----+-------+

问题4 查询「李」姓老师的数量

select count(tname) from teacher where tname like '李%';

运行结果：

+--------------+
| count(tname) |
+--------------+
|            1 |
+--------------+

问题5 查询学过「张三」老师授课的同学的信息

方法一：内联结

mysql> select s.* from student s
    -> inner join score sc on s.sid=sc.sid
    -> inner join course c on sc.cid= c.cid
    -> inner join teacher t on t.tid=c.tid
    -> where t.tname like '张三';

方法二：where子句建立联结

SELECT DISTINCT S.* from student S,score SC,course C,teacher T WHERE
T.tid = C.tid and C.cid = SC.cid and SC.sid = S.sid and T.tname = "张三";

运行结果：

+-----+--------+------------+------+
| sid | sname  | sbirth     | ssex |
+-----+--------+------------+------+
| 01  | 赵雷   | 1990-01-01 | 男   |
| 02  | 钱电   | 1990-12-21 | 男   |
| 03  | 孙风   | 1990-05-20 | 男   |
| 04  | 李云   | 1990-08-06 | 男   |
| 05  | 周梅   | 1991-12-01 | 女   |
| 07  | 郑竹   | 1989-07-01 | 女   |
+-----+--------+------------+------+