数据库查询最核心最常用语句 --- Join联合查询

1 引言

说到数据库操作，作为软件测试人员最基本的应用就是增删改查--即编写相应的Insert、delete、update、select等sql语句。

这其中，查询select又是最最常用的操作，除了基本的条件判断语句，就是多表的联合查询了。

如果，你在开发或者测试中，不怎么接触数据库，时间长了，多表join就忘了。比如我在存储行业测试中，很少接触数据库，过了7,8年，join查询语句都不会写了。之前在银行测试多少是会写点查询语句的，但是最近几年一条也没写过。

还好有点基础，看看文章多做几个查询测试就熟悉了。

今天拿一个最长举例的学生表做例子，顺便帮自己回忆一下。来解释一些常用的查询操作，主要是Join多表联合查询，因为所有的实际项目中，不可能只有简单的条件查询，有的数据库系统里会有成千上万的表，比如银行系统等。

2 创建学生表

创建4张表，为了方便书写都用小写，分别是学生表student、课程表course、成绩表score、老师表teacher。

其中：

student表有四个字段：sid、sname、sage、ssex

course表有三个字段：cid、cname、tid

score表有三个字段：sid、cid、score

teacher表有两个字段：tid、tname

student	course	score	teacher
sid	cid	sid	tid
sname	cname	cid	tname
sage	tid	score
ssex

3 多表联合查询

3.1 基础Join

查询所有学生的姓名、课程名称和成绩

SELECT stu.sname, c.cname, sc.score

FROM student stu

JOIN score sc ON sc.sid=stu.sid

JOIN course c ON c.cid=sc.cid;

3.2 多表Join

查询"王强"老师所教课程的学生姓名和成绩

SELECT stu.sname, sc.score

FROM student stu

JOIN score sc ON sc.sid=stu.sid

JOIN course c ON c.cid=sc.cid

JOIN teacher t ON t.tid=c.tid

WHERE t.tname="王强";

3.3 左连接 Left Join

查询所有学生的选课情况（包括没有选课的学生）

SELECT stu.sname, c.cname, sc.score

FROM student stu

LEFT JOIN score sc ON sc.sid=stu.sid

LEFT JOIN course c ON c.cid=sc.cid;

3.4 内连接 Inner Join

查询至少有两门课程成绩在90分以上的学生姓名

SELECT DISTINCT stu.sname

FROM score sc1

JOIN score sc2 ON sc1.sid = sc2.sid AND sc1.cid<>sc2.cid

JOIN student stu ON stu.sid=sc1.sid

WHERE sc1.score>90 AND sc2.score>90;

3.5 复杂 JOIN

查询每个学生选修的课程数量、平均分，并按平均分降序排列

SELECT stu.sname,COUNT(c.cid) as course_count, AVG(sc.score) as avg_score

FROM student stu

JOIN course c ON c.sid=stu.sid

JOIN score sc ON sc.cid=c.cid

GROUP BY stu.sid, stu.sname

ORDER BY Aavg_score DESC;

3.6 多条件 JOIN

查询选修了"叶平"老师所教课程且成绩大于80分的男生信息

SELECT stu.sid, stu.sname, c.cname, sc.score

FROM student stu

JOIN score sc ON sc.sid=stu.sid

JOIN course c ON c.cid=sc.cid

JOIN teacher t ON t.tid=c.tid

WHERE t.tname="叶平" AND sc.score>80 AND stu.ssex='male';

3.7 三表 JOIN 与聚合

查询每位老师所教课程的平均分，并显示老师姓名和课程名称

SELECT AVG(sc.score) AS avg_score, t.tname, c.cname

FROM course c

JOIN teacher t ON t.tid=c.tid

JOIN score sc ON sc.cid=c.cid

GROUP BY t.tid, t.tname, c.cid, c.cname;

或者：

SELECT AVG(sc.score) AS avg_score, t.tname, c.cname

FROM teacher t

JOIN course c ON c.tid=t.tid

LEFT JOIN score sc ON sc.cid=c.cid

GROUP BY t.tid, t.tname, c.cid, c.cname;

4 后记

数据库是一项非常专业化的软件和产品，它在现代化的各种系统中，都是偏低层的应用，是最终数据的储存空间。当然，它还会被存储到存储系统中被保护和备份起来。

数据库的增删改查语句是非常基础，也是使用非常频繁的知识点，我们一定要理解其中的含义，只有理解底层的逻辑才能更好的理解数据库的意义。

——————————————The End ——————————————————

欢迎关注大滨，坐标北京、天津：

18+软件开发测试经验；前8年银行系统（中行、民生、农行等）功能、性能和自动化测试，尤其是性能测试；后10年存储行业(CA， Dell-EMC)自动化和性能测试。

也欢迎大家点赞关注我的微信公众号：大滨读书健身（全网同名）

会分享读书、健身、投资和个人成长等心得体会，人生除了代码还有生活，与君同行！