数据检索三高级查询之连接查询

1、数据检索（三）高级查询 连接查询 实现从两个或两个以上表中检索数据且结果集 中出现的列来自于两个或两个以上表中的检索 操作被称为连接查询（连接技术），或者说连 接技术是指对两个表或两个以上表中数据执行 乘积运算的技术。在设计表时，为了提高表的 设计质量，经常把相关数据分散到不同的表中。 但是，在使用数据时，需要把这些数据集中在 一个查询语句中。 1.1 交叉连接查询 1.2 等值与非等值连接查询 1.3 自身连接查询 1.4 外连接查询 1.5 复合连接条件查询 1 连接查询 内连接(INNER JOIN) 外连接 左外连接 (LEFT JOIN) 右外连接 (RIGHT JOIN) 完整外连

2、接 (FULL JOIN) 交叉连接(CROSS JOIN) 1 连接查询 1.1 交叉连接查询 1.交叉连接的连接过程 例如，有一个产品表和一个产品销售表，如表1、表2所示， 两个表交叉连接后产生的结果如表3所示，结果集是两个表记 录的交叉乘积，列是两个表列的集合。 产品名称 产品 编号 0001 显示 器 0002键盘 0004鼠标 产品 编号 销量 000125 000330 000535 产品 名称 表1表2 产品编号产品名称产品编号销量 0001显示器000125 0002键盘000125 0004鼠标000125 0001显示器000330 0002键盘000330 0004鼠标0

3、00330 0001显示器000535 0002键盘000535 0004鼠标000535 表3 1.1 交叉连接查询 交叉连接语法结构 select From cross join On .= . 交叉连接查询 1.1 交叉连接查询 2.交叉连接的语法格式如下： SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 3.交叉查询产品表和产品销售表，代码如下： USE STUDENT GO SELECT * FROM 产品 CROSS JOIN 产品销售 GO 练习：范例6-1 select StudID,StuName,tblstudent.ClassID, EnrollY

4、ear,speciality from tblstudent ， tblclass where tblstudent.ClassID=tblclass.ClassID 内连接查询语法结构 (1)select From inner join On .= . (2)select From , On .= . 内连接（示例） select StudID,StuName,tblstudent.ClassID, EnrollYear,speciality from tblstudent join tblclass on tblstudent.ClassID=tblclass.ClassID 内连接查询

5、select StudID,StuName, ClassName, DepartName from tblstudent, tblclass, tbldepart where tblstudent.ClassID=tblclass.ClassID and tblclass.DepartID=tbldepart.DepartID 内连接（示例） 在学生基本情况表tblstudent，班级表tblclass和系别表 tbldepart三表中查询学生信息，包括学生的学号、姓名、班级名 称和系别名称。 内连接查询 1.2 等值与非等值连接查询（内连接） 例1 ：用等值连接的方法连接产品表和产品销售 表

6、。代码如下： USE STUDENT GO SELECT * FROM 产品 INNER JOIN 产品销售 ON 产 品.产品编号=产品销售.产品编号 GO 1.2 等值与非等值连接查询（内连接） 例2：自然连接产品表和产品销售表。 代码如下： SELECT 产品.产品编号,产品名称,销量 FROM 产品 JOIN 产品销售 ON 产品.产品编 号=产品销售.产品编号 GO 练习：范例6-2、 6-3、 6-4 1.3 自身连接查询 一个表的两个副本之间的连接 称为自身连接查询 例3 ：查询选修了两门或两门以上课程的学生的 学号和课程号。 代码如下： USE student GO SELEC

7、T DISTINCT a.学号,a.课程号 FROM 课程注册 AS a JOIN 课程注册 AS b ON a.学号=b.学号 AND a.课程号!=b.课程号 GO (1)select a.StudID,a.StuName,a.StuSex, year(EnrollDate),ClassID from tblstudent a,tblstudent b where b.StuName=刘备 and a.ClassID=b.ClassID and a.StuName刘备 (2)select a.ClassID,b.ClassID,a.EnrollYear from tblclass a,tb

8、lclass b where a.EnrollYear=b.EnrollYear and a.ClassIDb.ClassID 注：a.ClassIDb.ClassID主要是为了避免交叉连接而出现无意义的行。 自连接（示例） 在学生基本情况表tblstudent中 （1）查询刘备的同班同学的学号、姓名、性别、入学年份和班 级号(不含刘备本人)。 （2）查询入学年份为同一年的班级编号。 自连接查询 1.4 外连接查询 外连接有分为左外连接、右外连接、全外 连接三种。外连接除产生内连接生成的结果集 外，还可以使一个表（左、右外连接）或两个 表（全外连接）中的不满足连接条件的记录也 出现在结果集中。

9、 (1)select a.StudID,a.StuName,a.StuSex, year(EnrollDate),ClassID from tblstudent a,tblstudent b where b.StuName=刘备 and a.ClassID=b.ClassID and a.StuName刘备 (2)select a.ClassID,b.ClassID,a.EnrollYear from tblclass a,tblclass b where a.EnrollYear=b.EnrollYear and a.ClassIDb.ClassID 注：a.ClassIDb.ClassID

10、主要是为了避免交叉连接而出现无意义的行。 外连接语法结构 select From left|right|fullouter join On .= . 外连接查询 外连接语法结构 select From left|right|fullouter join On .= . 外连接查询 SELECT s.StudID，StuName, CourseID，Score from tblstudent s left join tblScore sc On s.StudID=sc.StudID order by StudID desc 外连接（示例） 在学生基本情况表tblstudent和成绩表tblSco

11、re中查询学生选 课情况，如果学生没有选课，则在成绩表tblScore将没有该学生 的成绩信息，课程和成绩列用空值填充。（用左外连接） 外连接查询 1.4 外连接查询 1. 左外连接 例4：将产品表左外连接产品销售表。 代码如下： USE STUDENT GO SELECT * FROM 产品 LEFT OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO 练习：范例6-6 1.4 外连接查询 2. 右外连接 例5 ：将产品表右外连接产品销售表。代码如下： USE STUDENT GO SELECT * FROM 产品 RIGHT OUTER JOIN 产品销售 ON

12、 产品.产品编号=产品销售.产品编号 GO 练习：范例6-7 1.4 外连接查询 3. 全连接 例6 ：用全连接的方法连接产品表与产品销售表。 代码如下： USE STUDENT GO SELECT * FROM 产品 FULL OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO 练习：范例6-8 （1）建立两个表：药品表和数量表。 （2）输入药品表和数量表记录。 （3）对药品表和数量表实施内连接查询、左外连接查询、右外 连接查询、全外连接查询。 （4）对药品表和数量表实施交叉连接查询。 连接查询示例 create table 药品表 -建立药品表 (药品编号 in

13、t primary key, 药品名称 varchar(20) not null） insert 药品表 values(1,长白山人参) -在药品表中插入数据 insert 药品表 values(2,清热凉茶) insert 药品表 values(3,块剂板兰根) go create table 数量表 -建立数量表 (药品编号 int primary key, 数量 bigint not null) go insert 数量表 values(1,1000) -在数量表中插入数据 insert 数量表 values(2,40) insert 数量表 values(4,500) insert 数

14、量表 values(5,23) go select A.药品编号,药品名称,B.药品编号,数量 -内连接查询 from 药品表 A, 数量表 B where A.药品编号=B.药品编号 go select A.药品编号,药品名称,B.药品编号,数量 -左外连接查询 from 药品表 A left join 数量表 B on A.药品编号=B.药品编号 go select A.药品编号,药品名称,B.药品编号,数量 -右外连接查询 from 药品表 A right join 数量表 B on A.药品编号=B.药品编号 select A.药品编号,药品名称,B.药品编号,数量 -全外连接查询 f

15、rom 药品表 A full join 数量表 B on A.药品编号=B.药品编号 go select A.药品编号,药品名称,B.药品编号,数量 -交叉连接查询 from 药品表 A cross join 数量表 B 连接查询 连接查询 内连接查询 左外连接查询 右外连接查询 全外连接查询 交叉连接查询结果 连接结果 再猜一猜：以下返回多少行？ SELECT A.药品编号,A.药品名 称,B.数量 From 药品表 A INNER JOIN 数量 B ON A.药品编号 B.药品编号 猜一猜：这样写，返回的查询结果是一样的吗？ SELECT A.药品编号,B.药品名称,B.数量 From

16、数量 B INNER JOIN 药品表 A ON A.药品编号 = B.药品编号 1 2 3 长白山人参 清热凉茶 块剂板兰根 药品表 1 2 4 5 1000 40 500 23 数 量 表 1长白山人参 2清热凉茶 1000 40 查询结果 内连接查询结果 SELECT A.药品编号,B.药品名称,B.数量 From 药品表 A INNER JOIN 数量 B ON A.药品编号 = B.药品编号 猜一猜：这样写，返回的查询结果是一样的吗？ SELECT A.药品编号,B.药品名称,B.数量 From 数量 B Left JOIN 药品表 A ON A.药品编号 = B.药品编号 1 2

17、3 长白山人参 清热凉茶 块剂板兰根 药品表 1 2 4 5 1000 40 500 23 数 量 表 1长白山人参 2清热凉茶 1000 40 3块剂板兰根null 查询结果 连接结果 再猜一猜：以下返回多少行？ SELECT A.药品编号,A.药品名 称,B.数量 From 药品表 A Left JOIN 数量 B ON A.药品编号 B.药品编号 SELECT A.药品编号,B.药品名称,B.数量 From 药品表 A Left JOIN 数量 B ON A.药品编号 = B.药品编号 左外连接查询结果 1 2 3 长白山人参 清热凉茶 块剂板兰根 药品表 连接结果 1 2 4 5 10

18、00 40 500 23 数 量 表 再猜一猜：以下返回多少行？ SELECT A.药品编号,A.药品名 称,B.药品编号,B.数量 From 药品表 A Right JOIN 数量 B ON A.药品编号 B.药品编号 SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 药品表 A Right JOIN 数量 B ON A.药品编号 = B.药品编号 猜一猜：这样写，返回的查询结果是一样的吗？ SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 数量 B Right JOIN 药品表 A ON A.药品编号 = B.药品编号 右外连接查询结果

19、1长白山人参 2清热凉茶 查询结果 1 2 nullnull4 1000 40 500 nullnull523 1 2 4 5 1000 40 500 23 数 量 表 1 2 3 长白山人参 清热凉茶 块剂板兰根 药品表 连接结果 猜一猜：这样写，返回的查询结果是一样的吗？ SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 数量 B Full JOIN 药品表 A ON A.药品编号 = B.药品编号 再猜一猜：以下返回多少行？ SELECT A.药品编号,A.药品名 称,B.药品编号,B.数量 From 药品表 A Full JOIN 数量 B ON A.药品编号

20、 B.药品编号 SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 药品表 A Full JOIN 数量 B ON A.药品编号 = B.药品编号 全外连接查询结果 1长白山人参 2清热凉茶 查询结果 1 2 3块剂板兰根 nullnull null 4 1000 40 null 500 nullnull523 猜一猜：这样写，返回的查询结果是一样的吗？ SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 数量 B Cross JOIN 药品表 A ON A.药品编号 = B.药品编号 1 2 4 5 1000 40 500 23 数量表 连接结

21、果 1 2 3 长白山人参 清热凉茶 块剂板兰根 药品表 1长白山人参 1长白山人参 查询结果 1 2 1长白山人参 1长白山人参 4 5 1000 40 500 23 2清热凉茶11000 2清热凉茶 2清热凉茶 2 4 2清热凉茶 3块剂板兰根 5 1 40 500 23 1000 3块剂板兰根240 3块剂板兰根4500 3块剂板兰根523 SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 药品表 A Cross JOIN 数量 B ON A.药品编号 = B.药品编号 交叉连接查询结果 1.5 复合连接条件查询 例7：查询成绩在75分以上的学生的学号、姓名、专业代码 和专业学级，选修课的学期、课程号、成绩，任课教师的教 师编号、姓名。范例6-9代码如下： USE student GO SELECT B.课程号,C.教师编号,C.姓名,A.学号,A.姓名,B.专业 代码,B.专业学级,B.学期,B.成绩 FROM 学生 AS A JOIN 课程注册 AS B ON A.学号=B.学号 AND B.成绩75 JOIN 教师 AS C ON B.教师编号=C.教师编号 GO 2 案例应用举例 2.1 自动注册必修课 2.2 重修未取得学分