MySQL多表查询及子查询

1. MySQL数据库执行查询操作时的查询流程：

请求-->查询缓存
请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应

mysql查询流程.png

客户端通过mysql协议连接至mysql server，mysql server接收到请求后，在建立线程连接之后，通过连接线程接收客户发出的查询请求语句，并且接收后尝试在缓存中是否能命中，如果能命中则直接返回结果，所以后面的流程都省去了；所以对mysql而言，内部的重要组件叫query cache查询缓存；
如果缓存未命中，查询语句交有解析器进行解析，并生成一个解析树，然后解析器被预处理器预处理，生成了可以访问数据的路径，由优化器评估众多路径中，哪条时最优，并尝试对最优路径进行改写，以加速其执行性能；优化完成以后，将优化后的执行请求提交给查询执行计划，排队，每一个执行计划，有查询执行引擎负责；这包括通过API调用存储引擎从磁盘上取到相应的数据，在执行查询引擎上做过滤等等；之后再把结果返回给客户端，返回客户端之前，如果程序结果是确定的而且符合缓存条件的测保存在缓存中。

2. SELECT语句的执行流程：

FROM --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit
一个select语句启动时，会首先判断from子句，决定从哪张表中完成查询操作，这个关键词被分析完成后，经过第一步分析from以确定对应表；确定完表以后，要从表上通过where子句来指明过滤条件，挑选出需要获取数据的行，所有第二步是分析where子句；分析完成where子句后，要把查询到的所有结果做分组，所有接下来非是group by子句，做分组；分组完成后做having，也就是所谓对分组的结果指明过滤条件再一次进行过滤；此处group by和having可以省略，之后对后续进行排序操作order by；排序后挑选出期望用到的列也叫字段，最终是要limit限制整体的输出结果中需要用到多少行，最终得到结果。

3. 单表查询

语法：

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
  [CHARACTER SET charset_name]
  export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

常用选项：
  DISTINCT：数据去重；
  SQL_CACHE：显示指定存储查询结果于缓存之中；
  SQL_NO_CACHE：显示查询结果不予缓存；

 常用功能：
   query_cache_type的值为ON时，查询缓存功能打开；
   SELECT的结果符合缓存条件即会缓存，否则，不予缓存；
                  如果显示指定SQL_NO_CACHE，则不予缓存；
    query_cache_type的值为DEMAND时，表示查询缓存功能按需进行；
                  显示指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存；

字段显示可用使用别名：
          col1 AS alias1，col2 AS alias2，...

WHERE 子句：指明过滤条件以实现选择的功能；

  算术表达式：+，-，*，/，%
  比较操作符：=，!=，<>（不等于），<=>（跟空值比较不报错），>，>=，<，<=

  BETWEEN min_num AND max_num：在两值的范围之间；
  IN(element1, element2, ...)：多值等于；
  IS NULL：取值为空；
  IS NOT NULL
  LIKE：模糊匹配
            %：任意长度的任意字符；
            _（下划线）：任意单个字符；
  ELIKE：表示后面匹配条件可使用正则表达式；
  REGEXP：匹配字符串可用正则表达式书写模式；最好不用；

  逻辑操作符：
              NOT：非，单目操作符；
              AND：与，双目操作符；
              OR：或，双目操作符；
              XOR：异或，双目操作符；不同为真，相同为假；

          注意：MySQL自己有很多操作函数；

GROUP: 根据指定的条件把查询结果进行分组，以用于做聚合运算；
- avg() ：求平均值
- max()：最大值
- min()：最小值
- count()：求数量
- sum()：求和
HAVING：对分组聚合运算后的结果指定过滤条件；
ORDER BY：根据指定的字段对查询结果进行排序；
- 升序：ASC,默认方式
- 降序：DESC; 在指定的字段后面写明即可；
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制；
- offset ：标识偏移量
- row_count：行数

4. 多表查询

连接操作：
- 交叉连接：笛卡尔乘积（多项式相乘）；
- 内连接：
  - 等值连接：让表之间的字段以等值建立连接关系
  - 不等值连接：
  - 自然连接
  - 自连接
```
SELECT s.Name,t.Name FROM students AS s,students AS t WHERE s.TeacherID=t.StuID
```
- 外链接：
  - 左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col左表中的每一项都有保证出现；而右表中没有对应时留空；
```
SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID;
```
  - 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
```
SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.ClassID;
```

5. 子查询：在查询语句中嵌套着查询语句；即基于某语句的查询结果再次进行的查询；

用在WHERE子句中的子查询：
（1）用于比较表达式中的子查询；子查询仅能返回单个值；
例如： SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
（2）用在IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表；
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
（3）用EXISTS；用在where子句中；（没介绍）
用于FROM子句中的子查询：
- 使用格式：SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE Clause;不建议使用；
- 大多数from子句都可拆为多表查询；
- 联合查询：UNION
- 把两个select语句查询结果合并成一个；

6. 练习：

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

SELECT Name,Age,Gender FROM students WHERE Age>25 AND Gender='M';

(2) 以ClassID为分组依据，显示每组的平均年龄；

SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID;

(3) 显示第2题中平均年龄大于30的分组及平均年龄；

SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID HAVING aage >30;

(4) 显示以L开头的名字的同学的信息；

SELECT * FROM students WHERE Name LIKE 'L%';

(5) 显示TeacherID非空的同学的相关信息；

SELECT * FROM students WHERE TeacherID IS NOT NULL;

(6) 以年龄排序后，显示年龄最大的前10位同学的信息；

SELECT * FROM students ORDER BY Age DESC LIMIT 10;

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

SELECT * FROM students WHERE Age>=20 HAVING Age<25; 
SELECT * FROM students WHERE Age>=20 AND Age<25;
SELECT * FROM (SELECT * FROM students WHERE Age>=20) AS s WHERE s.Age<25;

7. 练习：

1、以ClassID分组，显示每班的同学的人数；

SELECT count(ClassID),ClassID FROM students GROUP BY ClassID;

2、以Gender分组，显示其年龄之和；

SELECT Gender,sum(Age) FROM students GROUP BY Gender;

3、以ClassID分组，显示其平均年龄大于25的班级；

SELECT ClassID,avg(Age) AS a FROM students GROUP BY ClassID HAVING a>25;

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

SELECT sum(Age),Gender FROM (SELECT Age,Gender FROM students WHERE Age>25) AS a GROUP BY Gender;

SELECT sum(Age),Gender FROM students WHERE Age IN (SELECT Age FROM students WHERE Age>25);

练习：导入hellodb.sql，完成以下题目：
1、显示前5位同学的姓名、课程及成绩；

SELECT s.Name,c.Course,e.Score FROM students AS s,courses AS c,scores AS e WHERE s.StuID=e.StuID AND c.CourseID=e.CourseID AND s.StuID<=5;

2、显示其成绩高于80的同学的名称及课程；

    SELECT c.Course,d.Name,d.Score FROM courses AS c RIGHT JOIN (SELECT s.Name,a.CourseID,a.Score FROM students AS s LEFT JOIN (SELECT StuID,CourseID,Score FROM scores WHERE Score>80) AS a ON s.StuID=a.StuID WHERE CourseID IS NOT NULL) AS d ON d.CourseID=c.CourseID; 
    SELECT s.Name,c.Course,b.Score FROM students as s,courses as c,scores as b WHERE s.StuID=b.StuID AND c.CourseID=b.CourseID AND b.Score>80;

3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

SELECT StuID,CourseID,avg(Score) FROM scores WHERE StuID<=8 GROUP BY StuID ORDER BY avg(Score)DESC;
SELECT Name,avg(Score) FROM students as s,scores as a WHERE s.StuID=a.StuID AND s.StuID<=8 GROUP BY Name ORDER BY avg(Score) DESC;

4、显示每门课程课程名称及学习了这门课的同学的个数；

SELECT c.Course,c.CourseID,count(s.Name) FROM students as s,courses as c,coc WHERE s.ClassID=coc.ClassID AND c.CourseID=coc.CourseID GROUP BY c.CourseID;

思考：

1、如何显示其年龄大于平均年龄的同学的名字？

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);

2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？

SELECT s.Name,c.CourseID FROM students AS s,coc AS c WHERE s.ClassID=c.ClassID AND c.CourseID IN (1,2,4,7);

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

SELECT s.Name,cvg.ClassID,s.Age,cvg.ag FROM students AS s,(SELECT ClassID,count(Name),avg(Age) AS ag FROM students GROUP BY ClassID HAVING count(Name)>=3) AS cvg WHERE s.ClassID=cvg.ClassID AND s.Age>cvg.ag AND s.ClassID ;

4、统计各班级中年龄大于全校同学平均年龄的同学。

SELECT Name,Age FROM students WHERE age>(SELECT avg(Age) FROM students);