SQL常用查询

测试数据：
Course:

image.png

SC:

image.png

Student:

image.png
Teacher:
image.png

COUNT

查询每课程的选课人数：
SELECT Cid, count(*) from SC group by Cid

image.png

AVG

查询每门课程学生的平均分：
SELECT Cid, AVG(score) FROM SC GROUP BY Cid

image.png

SUM

查询每名学生的总分：
SELECT Sid, SUM(score) FROM SC GROUP BY Sid

image.png

JOIN

语法：SELECT ... FROM table1 ...JOIN table2 ON condition
内连接 INNER JOIN: 返回同时匹配两个表的数据
左外连接 LEFT OUTER JOIN：返回左表中所有数据，即使右表匹配不上某些项目
右外连接同左外连接，方向换了一下
全连接 FULL JOIN：两个表的全部数据都会返回
举例
查询所有课程的老师
SELECT * FROM Course LEFT OUTER JOIN Teacher ON Course.Tid = Teacher.Tid

image.png
SELECT * FROM Course RIGHT OUTER JOIN Teacher ON Course.Tid = Teacher.Tid
image.png
SELECT * FROM Course INNER JOIN Teacher ON Course.Tid = Teacher.Tid
image.png
体会一下区别，推测一下FULL JOIN会返回啥
由于MySQL不支持FULL JOIN，这里就不发结果了

多个表的联合查询同理，查询某个同学的所有课程成绩与老师姓名等：
SELECT Student.Sname as Sname, Course.Cname as Cname, SC.score, Teacher.Tname
FROM Student
left outer join SC on Student.Sid = SC.Sid
left outer join Course on SC.Cid = Course.Cid
left outer join Teacher on Course.Tid = Teacher.Tid
where Student.Sid = 01
结果：

image.png