(1)student表
Sno Sname Ssex Sage Sdept nativeplace
200215121 李勇 男 20 CS NULL
200215123 王蓝 女 18 MA NULL
200215122 刘晨 女 19 CS NULL
(2)course表
Cno Cname Cpno
1 数据库 5
2 数学
3 信息系统 1
4 操作系统 6
5 数据结构 7
6 数据处理
7 Pascal 6
(3)sc表
Sno Cno Grade
200215121 1 92
200215121 2 85
200215121 3 88
200215122 2 90
200215122 3 80
3.4.3连接查询
一、等值与非等值连接
1、连接查询的where子句中用来连接两个表的条件称为连接谓词或者连接条件。
格式1:[表名1.]<列名1><比较运算符>[]<表名2.><列名2>
比较运算符:=,>,<,>=,<=,!=(<>)等。
格式2:[表名1.]<列名1>between[表名2.]<列名2>and[表名2.]<列名3>
2、当连接运算符为=时称之为等值连接,其他为非等值连接。
连接谓词中的列名称称为连接字段。
连接条件中的各连接类型必须是可比的,但名字不必相同。
问题1:查询每个学生及其选修课程的情况?
格式:
select student.*,sc.*
from student,sc
where student.Sno=sc.Sno
-----------------------------------------------------------------------------
Sno Sname Ssex Sage Sdept nativeplace Sno Cno Grade
200215121 李勇 男 20 CS NULL 200215121 1 92
200215121 李勇 男 20 CS NULL 200215121 2 85
200215121 李勇 男 20 CS NULL 200215121 3 88
200215122 刘晨 女 19 CS NULL 200215122 2 90
200215122 刘晨 女 19 CS NULL 200215122 3 80
说明:
select/where子句属性名前都加了表名前缀,是为了区分以免混淆。
若参加连接的各表中属性名唯一,可以省略前缀。
过程:
首先在student表中找到第一个元祖,然后从头开始扫描sc表,逐一查找一student第一个
元组的Sno相等的sc元组,找到后就将两个元组连接起来。形成一个元祖。sc全部查找完后。
再找student中第二个元祖,重复。找完为止。这就是嵌套循环的基本思想。
补充:
如果在sc表Sno上建立索引的话,就不用了每次扫面全表sc,而是根据索引找到相应的sc元组。
用索引查询sc中满足条件的元组一般会比扫描全表快。
若在等值连接中把目标列中重复的属性列去掉则成为自然连接。
问题2:自然连接(同上问题)
格式:
select student.Sno,Sname,Ssex,Sage,Sdept,nativeplace,sc.Cno,Grade
from student,sc
where student.Sno=sc.Sno
----------------------------------------------------------------------------
Sno Sname Ssex Sage Sdept nativeplace Cno Grade
200215121 李勇 男 20 CS NULL 1 92
200215121 李勇 男 20 CS NULL 2 85
200215121 李勇 男 20 CS NULL 3 88
200215122 刘晨 女 19 CS NULL 2 90
200215122 刘晨 女 19 CS NULL 3 80
说明:Sname,Ssex,Sage,Sdept,nativeplace这五个属性两个表都有所以不用前缀。
问题3:查询选修二号课程且成绩在85分以上的所有学生的学号和姓名
格式:
select student.Sno,Sname
from student,sc
where student.Sno=sc.Sno and Cno = '2' and Grade>85
二、自身连接
自身连接:连接操作不仅是两个表之间的连接,也可以是一个表与其自身连接。
问题:找出course先修课的先修课
select first.Cno,second.Cpno
from course first,course second
where first.Cpno=second.Cno
说明:首先要为course表取两个别名,一个first一个second
三、外连接
背景:通常连接操作中,只有满足连接条件的元组才被连接展现出来。
如student200215123学生没有选课连接时没有相对应选课记录,
故将其舍弃。
要想把那个学生信息显示在结果中,需要用到外连接。
空信息仍为null。
问题:((**左外连接**))(完全连接)
select student.*,sc.*
from student left outer join sc on(student.Sno=sc.Sno)
-------------------------------------------------------------------------------------------
Sno Sname Ssex Sage Sdept nativeplace Sno Cno Grade
200215121 李勇 男 20 CS NULL 200215121 1 92
200215121 李勇 男 20 CS NULL 200215121 2 85
200215121 李勇 男 20 CS NULL 200215121 3 88
200215122 刘晨 女 19 CS NULL 200215122 2 90
200215122 刘晨 女 19 CS NULL 200215122 3 80
200215123 王敏 女 18 MA NULL NULL NULL NULL
说明:即使sc表中不存在student的Sno=200215123属性,也可以连接。
四、多表查询
查询每个学生的学号,姓名,课程名,成绩。
问题:
select student.Sno ,Sname,course.Cname,sc.Grade
from student,course,sc
where student.Sno =sc.Sno and sc.Cno =course.Cno
----------------------------------------------
Sno Sname Cname Grade
200215121 李勇 数据库 92
200215121 李勇 数学 85
200215122 刘晨 数学 90
200215121 李勇 信息系统 88
200215122 刘晨 信息系统 80
说明:依次用and连接。
五、嵌套查询
嵌套查询:使用户多个可以用多个简单查询构成复杂查询,通过层层嵌套,体现出SQL的结构化查询语言。
问题1:查询选择二号课程的学生信息?
select *
from student
where Sno in(
select Sno
from sc
where Cno='2');
----------------------------------------------------------------------
Sno Sname Ssex Sage Sdept nativeplace
200215121 李勇 男 20 CS NULL
200215122 刘晨 女 19 CS NULL
说明:SQL语句允许多层嵌套。子查询结果不能用order by,order by只对最终查询结果排序。
@@、带有in谓词的子查询
在嵌套查询中,子查询结果往往是一个集合。所以嵌套中常用谓词in。
问题2:查询刘晨所在系的学生信息?
select *
from student
where Sdept in
(select Sdept
from student
where Sname='刘晨');
说明:子查询的查询条件不依赖于父查询,称为不相关子查询。
(1)间接结果:
select s1.*,s2.*
from student s1,student s2
where s1.Sdept=s2.Sdept --and s2.Sname='刘晨'
Sage Sdept nativeplace Sno Sname Ssex Sage Sdept
李勇 男 20 CS NULL 李勇 男 20 CS NULL
刘晨 女 19 CS NULL 李勇 男 20 CS NULL
李勇 男 20 CS NULL 刘晨 女 19 CS NULL
刘晨 女 19 CS NULL 刘晨 女 19 CS NULL
王敏 女 18 MA NULL 王敏 女 18 MA NULL
小呆 女 21 MA 陕西西安 王敏 女 18 MA NULL
王敏 女 18 MA NULL 小呆 女 21 MA 陕西西安
小呆 女 21 MA 陕西西安 小呆 女 21 MA 陕西西安
(2)最终结果:
select s1.*
from student s1,student s2
where s1.Sdept=s2.Sdept and s2.Sname='刘晨'
200215121 李勇 男 20 CS NULL 软件工程
200215122 刘晨 女 19 CS NULL 大数据
问题3:查询选修课程名为数学的学生信息和学号?
select *
from sc,student,course
where sc.Cno = course.Cno and student.Sno = sc.Sno
Sno Cno Grade Sno Sname Ssex Sage Sdept nativeplace xibie Cno Cname Cpno
200215121 1 92 200215121 李勇 男 20 CS NULL 软件工程 1 数据库 5
200215121 2 85 200215121 李勇 男 20 CS NULL 软件工程 2 数学
200215122 2 90 200215122 刘晨 女 19 CS NULL 大数据 2 数学
200215121 3 88 200215121 李勇 男 20 CS NULL 软件工程 3 信息系统 1
200215122 3 80 200215122 刘晨 女 19 CS NULL 大数据 3 信息系统 1
-----------------------------
select *
from student
where Sno in
(select Sno
from sc
where Cno in
(select Cno
from course
where Cname='数学'));
Sno Sname Ssex Sage Sdept nativeplace xibie
200215121 李勇 男 20 CS NULL 软件工程
200215122 刘晨 女 19 CS NULL 大数据