MySQL基本语法使用（二）

多表查询

原理

多个表的查询（两个表）：on对两个表进行连接形成虚拟结果集的表。

例：A B C
A和B连接ON，形成AB虚拟表，虚拟表和C，ON形成AC结果集和C的虚拟表

• 方向性：写在前面的为左表，写在后边的为右表。

表与表之间的关系

1. 一对一：A表的一行匹配B表中的一行。
2. 一对多：A表的一行匹配B表中的多行，B表中的一行只能对应A表中的一行。
3. 多对多：A表的一行匹配B表中的多行，反之亦然。
   多对多的关系需要第三张表，称之为结合表
   直接相连的表（通过外键相连）：只能是一对一或者一对多的关系

• 外键：两个表建立联系的一个桥梁，两个表之间的关系

例：
student表：学生id，姓名，学校，学院id，年纪，班级
学院表：学院id，学院名称，学院地址…
课程表：课程id，课程名称，课程简介…
成绩表：学生id，课程id，分数

分析：
成绩表中的 学生id（对应学生表的外键），可以和学生表建立连接。
成绩表中的 课程id（对应课程表的外键），可以和课程表建立连接。
学生表 和 课程表：多对多，需要中间表：成绩表
学生表 -> 成绩表：一对多(一个学生记录可以对应多个成绩记录)
课程表 -> 成绩表：一对多(一门课程可以有多个学生的成绩记录)

MySQL表连接方式

1. 内连接：等值查询，在连接条件中使用 “=” 运算符，查询结果集中列出被查询的所有符合的列，包括其中重复的列。

select 字段名 from 表1 [inner] join 表2 on 连接条件；

2. 笛卡尔积连接：
   表1检索出的表的行数乘以表2中的行数
   例如：表1的14条数据和表2中的7条数据，表一中每一列数据都乘以表2的所有数据。

select 字段名 from 表1,表2 where 筛选条件

别名：取一个新的名字（可以给表和属性取别名）
格式：表名/属性 [as] 别名

3. 左连接：将左表中所有的结果集返回，和游标相连接满足条件则返回游标关联的数据，左表存在而右表没有关联的数据则关联字段返回为null。在右表存在的数据而左表没有关联的数据则过滤掉不在结果集中。

select 字段名 from 表1 left jion 表2 on 连接条件；

4. 右连接：按照连接条件，返回两张表中满足条件的记录，以及右表中的所有记录，左表匹配不到显示为NULL

setlect 字段名 from 表1 right join 表2 on 连接条件；

查询举例

• 下面给出四个表，对这些表中的信息进行查询。
  思路：先分析题目中需要的信息所在的表，选择出最终需要的表，再进行查询。

1. 查询 "01"课程比"02"课程成绩高 的 学生的信息及课程分数。
2. 查询在sc表中不存在成绩的学生信息。
   
   本题中用到了子查询：先查询括号内部的子表，查询结果作为条件，再在外表进行处理。
   distinct：被distinct修饰的属性查询出的结果不重复。
3. 查询学过 "张三"老师授课的同学的信息。
   
   本题需要用中间表course来连接teacher表和SC表。
4. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
   
5. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。

过滤 -> 分组 -> 统计 -> 再判断

select s.SID,s.Sname,avg(c.score)
from student s,SC c
where s.SID=c.SID
and s.SID in(select SID from SC
where c.score<60
group by c.SID
having count(*)>1);

• 写法顺序：select–from–where–group by–having–order by
• 执行顺序：from–where–group by–having–select–order by
  select要放后面，如果有order by，则order by放最后，因为order by 是对结果进行排序。