Mysql的联表查询

一、关联查询

1、最常用的联表查询有左关联（left join）、右关联（right join）、内关联（inner join），但实际上关联查询一共有七种，另外四种是在这三种基础上衍生出来的。如下图所示。

               

 

 

 

2、关联查询一般的操作步骤

（1）分析需求，分析查询的字段来自哪些表。

（2）确定使用哪种连接查询方式。（七种任选）

（3）确定交叉点（即两张表中，那个数据是相同的），即确定判断条件

 

3、三种连接查询示例

　　注：左表是result，右表是student。

（1）内连接。

　　任务：在students表和result表中studentNo相等的数据中，将student表中的studentNo、studentName、studentResult 和result表中的subjectNo返回。

　因为studentNo在两张表中都有数据，因此需要声明这个字段来自于哪张表。

SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo     ----也可以使用where代替on，但是join后面一般用on

 

（2）右连接

　　任务：在student表中，查询与result表中studentNo的相等的数据，并将student表中的studentNo、studentName、studentResult 和result表中的subjectNo返回。

SELECT s.studentNo,studentName,subjiectNo,studentResult
FROM result AS r
RIGHT JOIN  student AS s
ON s.studentNo = r.studentNo

 

（3）左连接

　　任务：在result表中，查询与result表中studentNo的相等的数据，并将student表中的studentNo、studentName、studentResult 和result表中的subjectNo返回。

SELECT s.studentNo,studentName,subjiectNo,studentResult
FROM result AS r
LEFT JOIN  student AS s
ON s.studentNo = r.studentNo

　　左连接、右连接、内连接的区别

操作	描述
inner join	如果两张表中有一个满足条件，就返回结果
left join	会将右表中所有值返回，即使在右表中没有匹配
right join	会将左表中所有值返回，即使左表中没有匹配

 

4、多表联合查询

　　多表联合查询的核心是将多张表两两进行联合，并将每两张表查询的结果作为左表或者右表。

 

二、自连接查询

1、 即在同一张表中查询信息，但是将一张表当做两张表使用。通常适用于表信息互相耦合、嵌套的情况。

 

三、分页和排序

1、排序关键字

　　升序：asc

　　降序：desc

2、分页关键字

　　limit a,b  --a：起始行，b：页面大小，a和b满足：假如是显示第n页，则应为 limit （n-1）b, b；

 

四、子查询

1、子查询的本质：在where语句中嵌套一个子查询语句

2、例子

　　任务：查询 “数据库结构-1”的所有考试结果（包括学号、科目编号、成绩），降序排列

　　（1）如果使用连接查询，SQL语句如下

SELECT studentNo,r.subjectNo,studentResult
FROM `result` AS r
INNER JOIN `subject` AS s
ON r.subjectNo = s.subjectNo
WHERE studentNme = '数据库结构-1'
ORDER BY studentRuslt DESC

　　（2）如果使用子查询，SQL语句如下

SELECT studentNo,subjectNo,studentResult
FROM result
WHERE subjectNo = (
    SELECT subjectNo FROM `subject` WHERE subjectName = '数据库结构-1'
)

　　子查询执行的顺序是：由内而外

3、子查询一般效率较低，在使用的时候要考虑性能问题。

 

五、MySQL的函数

1、常用函数

　　可以参考官方文档。此处只列举常用函数。

（1）数学类&字符串类
　　略，可参考官方文档。
（2）时间类
　　CURRENT_DATE（） --获取当前日期
　　NOW（） --获取当前时间
　　LOCALTIME（） --获取本地时间

 

2、聚合函数

（1）常用函数

1、统计函数
　　COUNT（列名）    --统计函数，会过滤列中的空值
　　COUNT（1）       --不会过滤空值
　　COUNT（*）　　　　--不会过滤空值
　　　　三者的区别：当列为主键时，COUNT（列名）的效率最高；当列不是主键主键时，OCUNT（1）效率最高；当表中只有一列时，COUNT（*）效率最高。
2、求平均、和、最高、最低
　　SUN（）、AVG（）、MAX（）、MIN（）

 

（2）分组函数

　　GROUP BY

　　表结构：学科表（subject），成绩表（result）,关联的主键（subjectNo--学科编号）

　　任务：以学科为分组，将平均分大于80的学科的学科编号、平均分、最高分、最低分查出来。

SELECT subjectNo,AVG(studentRusult) as 平均分,MAX(studentResult) as 最高分,MIN(studentResult) as 最低分
FROM result as r
INNER JOIN `subject` as sub
ON r.`subejectNo`=sub.`subjectNo`
GROUP BY r.subjectNo   --通过这个字段来分组
HAVING 平均分>80

　　注意：where使用在GROUP BY之前，HAVING使用在GROUP BY之后，两者都是实现过滤功能。

 

六、SELECT汇总