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);

推荐阅读更多精彩内容