4-3多表查询视频在线视频

同学们好

今天我们学习第4章

查询与视图

本次课的学习内容是

多表查询

前面我们学习了

select语句的基本语法和单表查询

但在实际的业务处理中

每一个数据库中都有多个表

经常会遇到需要查询

多个表中数据的情况

如在stu数据库中

要查询姓名 课程名 成绩

那么这三个字段来自于三个表

那么该如何来完成这个查询呢

要查询多个表中的数据

就需要连接多个表

在MySQL中

select语句多表连接的语法是这样的

SELECT 子句

FROM 表名1

[CROSS|INNER|LEFT|RIGHT] JOIN 表名2

ON 表名1.字段=表名2.字段

WHERE 子句

Group by 子句

Order子句

Limit子句

语句跟之前的单表查询相比

增加了连接的语法

多表之间的连接方式有三种

交叉连接 内连接和外连接

Cross表示是交叉连接

inner 表示内连接

left和right是外连接的两种类型

多表查询按照连接方式分为

交叉连接查询

内连接查询和外连接查询

接下来我们就分别来学习交叉连接

内连接和外连接的含义与使用方法

我们先来学习交叉连接

交叉连接的语法是

SELECT 子句

FROM 表名1 CROSS JOIN 表名2

交叉连接是按照笛卡尔乘积的形式

连接两个表中的数据

也就是表1中的每一条记录

和表2 的所有记录都连接一次

来看案例1

查询学生表student中

所有学生的所有选课可能

也就是student表中

每个学生可以对

course表中的每门课程进行选修

SELECT sid，sname,cno,cname

FROM student cross JOIN course

运行一下

可以看到

student表中的每一条记录

都与course表中的记录连接一次

查询结果的行数

是student表中的记录行数

乘以course表中的记录行数

交叉连接还有另外的实现方法

SELECT sid，sname，cno，cname

FROM student，course

运行

可以看到运行结果是一样的

交叉连接查询

在日常事务处理中较少使用

语法也比较简单

下面我们来学习内连接

内连接只查询输出

按照连接条件匹配的记录

连接语法是

SELECT 子句

FROM 表名1

inner JOIN 表名2

ON 表名1.字段=表名2.字段

其中的inner可以省略

来看案例

案例2

查询选修了课程的学号

姓名 课程号 成绩

在做多表查询之前

我们需要熟悉表的结构和字段类型

找出表与表之间的共同字段

对于stu数据库

我们简单地表达表与表之间的联系

Student通过sid与score表建立练习

score表通过cno与course表建立联系

再来看这个查询

学号和姓名属于student表

课程号和成绩属于score表

这两个表之间有共同字段sid

因此 查询可以这样写

Select sid,sname,cno,result

From student inner join score

On student.sid=score.sid

完整语句是

Select sid,sname,cno,result

From student inner join score

On student.sid=score.sid

来执行

出现一个错误

错误原因是

select后的sid未指明所属表

请大家在写查询语句时注意

如果某个字段属于多个表的共同字段

那么就需要指明这个字段所属的表

表名长书写不方便

我们可以给表起别名

把语句可以改写成这样

a.sid student a score b a.sid b.sid

语句就是这样的

Select a.sid,sname,cno,result

From student a

score b

where a.sid=b.sid

执行

可以看到两种查询方式结果相同

再来看案例3

查询选修课程的学号

姓名 课程名 成绩

来分析这个查询需求

学号sid和姓名sname属于student表

课程名cname属于course表

成绩result属于score表

这三个表之间的关系是

Student通过sid与score联系

score通过cno与course联系

因此查询可以这样写

Select a.sid,sname,cname,result

From student a inner join score b

On a.sid=b.sid

Inner course c

On b.cno=c.cno

执行

查出学号 姓名 课程名 成绩

还可以写成这样

Select student.sid,sname,cno,result

From student a ，score b,course c

where a.sid=b.sid

and b.cno=c.cno

执行

结果是一样的

来看案例4

查询选修了体育和大学英语的

课程的学生姓名 课程名 成绩

并按科目和成绩高低排序

这个查询也同样需要

使用到三个表中的数据

查询语句可以这样写

Select a.sid

sname,cname,result

From student a

inner join score b

On a.sid=b.sid

Inner course c

On b.cno=c.cno

那接下来呢有条件

Where cname in （‘体育’，‘大学英语’）

Order by cno,result desc

执行

可以看到查询结果

按照科目和成绩高低排序

内连接只能查询出

按照连接条件匹配的记录

如果想输出

不能匹配的记录怎么办呢

数据库中提供了另外的一种连接形式

外连接

下面来学习外连接的使用方法

外连接的语法形式是

SELECT 子句

FROM 表名1

LEFT|RIGHT JOIN 表名2

ON 表名1.字段=表名2.字段

WHERE 条件表达式

注意 使用left join时

left左边表的记录全部输出

使用right join时

右边表记录全部输出

来看案例

案例5

查询所有学生学号

姓名 课程号 成绩

没有选修课的学生也输出

查询语句可以这样来写

Select a.sid,sname,cno,result

From student a left join score b

On a.sid=b.sid

执行

来看查询结果

可以看到

没有选课的学生记录

也被查询输出了

他的相关的课程号和课程名

以空值输出

也可以用右连接来实现

SELECT a.sid,sname,cno,result

FROM score a RIGHT JOIN student b

ON a.sid=b.sid

来看查询结果

与刚刚上面的查询结果是一样的

再来看案例6

查询信息工程系所有的学号

姓名 课程号 成绩

没有选修课的学生也输出

这个查询在案例5的基础上增加了条件

因此语句可以这样写

Where department=‘信息工程系’

执行

查询出信息工程系所有学生

姓名 课程号 成绩

没有选修课的学生也输出了

本次课的学习内容就是这些

我们来对本次课做个小结

本次课我们学习了多表查询

对于多表查询我们需要掌握

表与表之间的连接方式

其基本语法是

FROM 表名1

[CROSS|INNER|LEFT|RIGHT] JOIN 表名2

在实际的应用时有三种形式

第一 交叉连接

其连接语法是

FROM 表名1 CROSS JOIN 表名2

第二 内连接

FROM 表名1 [INNER] JOIN 表名2

ON 表名1.字段=表名2.字段

三 外连接

FROM 表名1 LEFT|RIGHT JOIN 表名2

ON 表名1.字段=表名2.字段

请理解left和right的含义

注意所有记录都输出的表的位置

请在资料下载练习4-3

并完成操作练习

本次课就讲到这里

谢谢 再见