MySQL的多表查询及习题练习

目录

多表查询

1. 笛卡尔积

2. 内连接

2.1 等值连接

2.2 自然连接

3. 外连接

3.1 左连接

3.2 右连接

习题练习

题目

用到的表(Student表和SC表上面有)

答案

---

多表查询

表与表存在关系：一对一关系，一对多关系和多对多的关系。

示例：

多表联合查询： 给定学生表，Student和成绩表SC

mysql> select * from Student;
+------+-------+------+------+
| SID  | Sname | Sage | Ssex |
+------+-------+------+------+
|    1 | 赵雷  |   20 | 男   |
|    2 | 钱电  |   20 | 男   |
|    3 | 孙风  |   21 | 男   |
|    4 | 吴兰  |   18 | 女   |
|    5 | 孙兰  |   17 | 女   |
+------+-------+------+------+
5 rows in set (0.00 sec)


mysql> select * from SC;
+------+------+-------+
| SID  | CID  | score |
+------+------+-------+
|    1 |    1 |    80 |
|    1 |    2 |    71 |
|    1 |    3 |    87 |
|    2 |    1 |    88 |
|    2 |    2 |    70 |
|    2 |    3 |    89 |
|    3 |    1 |    68 |
|    3 |    2 |    78 |
|    3 |    3 |    87 |
|    4 |    1 |    67 |
|    4 |    2 |    58 |
|    4 |    3 |    89 |
|    5 |    1 |    56 |
|    5 |    2 |    89 |
|    6 |    3 |    38 |
+------+------+-------+
15 rows in set (0.00 sec)

分析：Student表中的每一条数据对应SC表中的多条数据，反之，SC表中的每一条数据对应Student中的一条学生信息，故而两表存在一对多的关系。

1. 笛卡尔积

将表A中数据的行数乘以表B中的数据的行数。

假如表A(m行数据)，表B(n行数据)，则笛卡尔积的结果m*n行结果。

示例：

select * from Student,SC;

（部分结果截图 ）

2. 内连接

内连接是将符合条件的结果集返回（两个表中都存在的数据），内连接分为等值连接和自然连接。

2.1 等值连接

当条件有 “=” 的连接称为等值连接，其结果是连接表的所有列（包含重复列）。

select * from Student s ,SC sc where s.SID=sc.SID;

显性连接：[inner] join XXX on XXX

select * from Student s inner join SC sc on s.SID=sc.SID;

运行结果：（运行结果相同）

2.2 自然连接

自然连接不仅要求连接的两个字段值必须相同，并且要求对结果中重复的属性去重.

示例： （这里对重复属性SID进行去重）

select * from Student natural join SC;

3. 外连接

外连接，不仅包含符合条件的结果，还包括左表(左连接)或者右表(右连接)中所有数据返回。

3.1 左连接

left join :左表记录的所有数据全部显示处理，而右表只会显示符合条件的记录，右表记录不足的地方均为null。

示例：

查询所有同学的课程成绩：

select * from Student left joinSC on Student.SID = sc.SID;

3.2 右连接

right join :左表记录的显示符合条件的记录，而右表显示全部的数据结果，左表表记录不足的地方均为null。

示例：

查询所有的成绩的学生信息

select * from Student s right join SC sc on s.SID=sc.SID;

---

习题练习

常用基本函数：

count():计数

avg():平均值

max():最大值

min():最小值

sum():求和

题目

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
3、查询在sc表中不存在成绩的学生信息。
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
5、查询"李"姓老师的数量
6、查询学过"张三"老师授课的同学的信息
7、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
8、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

用到的表(Student表和SC表上面有)

1、学生表
Student(SID,Sname,Sage,Ssex) 
--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别
2.课程表
Course(CID,Cname,TID) 
--CID --课程编号,Cname 课程名称,TID 教师编号
3.教师表
Teacher(TID,Tname) 
--TID 教师编号,Tname 教师姓名
4.成绩表
SC(SID,CID,score) 
--SID 学生编号,CID 课程编号,score 分数
课程表：
课程号  课程名称  教师编号
1       语文     2
2       数学     1
3       英语     3
教师表：
教师编号   教师名
1         张三
2         李四
3         王五

⭐小tips：Sublime text可以对数据进行批量操作哦~ 如下所示，特别方便数据的插入。

答案

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

3、查询在sc表中不存在成绩的学生信息

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

5、查询"李"姓老师的数量

6、查询学过"张三"老师授课的同学的信息

7、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

8、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 

---

以上都是学习过程中的知识点总结，如果有错误或者有疑问，欢迎一起交流吖~~    /手动抱拳/