数据检索(三)高级查询之连接查询课件.ppt

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

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

3、5350004鼠标000535表表31.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-1select StudID,StuName,tblstudent.ClassID,EnrollYear,specialityfrom tblstudent ，tblclasswher

4、e tblstudent.ClassID=tblclass.ClassID内连接查询语法结构(1)select From inner join On .=.(2)select From,On .=.内连接（示例）内连接（示例）select StudID,StuName,tblstudent.ClassID,EnrollYear,specialityfrom tblstudent join tblclasson tblstudent.ClassID=tblclass.ClassID内连接查询select StudID,StuName,ClassName,DepartNamefrom tblstu

5、dent,tblclass,tbldepartwhere tblstudent.ClassID=tblclass.ClassID and tblclass.DepartID=tbldepart.DepartID内连接（示例）示例）在学生基本情况表在学生基本情况表tblstudent，班级表，班级表tblclass和系别表和系别表tbldepart三表中查询学生信息，包括学生的学三表中查询学生信息，包括学生的学号、姓名、班级号、姓名、班级名称和系别名称名称和系别名称。内连接查询1.2 等值与非等值连接查询（内连接）例1：用等值连接的方法连接产品表和产品销售表。代码如下：USE STUDENT G

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

7、OIN 课程注册 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,tblclass b where a.EnrollYear=b.EnrollYe

8、ar 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.Cl

10、assIDb.ClassID注：注：a.ClassIDb.ClassID主要是主要是为了避免交叉连接而出现无意义的行。为了避免交叉连接而出现无意义的行。外连接语法结构select From left|right|fullouter join On .=.外连接查询外连接语法结构select From left|right|fullouter join On .=.外连接查询SELECT s.StudID，StuName,CourseID，Scorefrom tblstudent s left join tblScore scOn s.StudID=sc.StudID order by Stud

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

12、.4 外连接查询 2.右外连接 例5：将产品表右外连接产品销售表。代码如下：USE STUDENT GO SELECT*FROM 产品 RIGHT OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO练习：范例6-71.4 外连接查询 3.全连接 例6：用全连接的方法连接产品表与产品销售表。代码如下：USE STUDENT GO SELECT*FROM 产品 FULL OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO练习：范例6-8（1）建立两个表：）建立两个表：药品表和数量表药品表和数量表。（2）输入药品表和数量表记录。）输入药品表和数

13、量表记录。（3）对药品表和数量表实施）对药品表和数量表实施内连接内连接查询、查询、左外连接左外连接查询、查询、右外右外连接连接查询、查询、全外连接全外连接查询。查询。（4）对药品表和数量表实施）对药品表和数量表实施交叉连接交叉连接查询。查询。连接查询示例create table 药品表药品表 -建立药品表建立药品表(药品编号药品编号 int primary key,药品名称药品名称 varchar(20)not null）insert 药品表药品表 values(1,长白山人参长白山人参)-在药品表中插入数据在药品表中插入数据insert 药品表药品表 values(2,清热凉茶清热凉茶)in

14、sert 药品表药品表 values(3,块剂板兰根块剂板兰根)gocreate table 数量表数量表 -建立数量表建立数量表(药品编号药品编号 int primary key,数量数量 bigint not null)goinsert 数量表数量表 values(1,1000)-在数量表中插入数据在数量表中插入数据insert 数量表数量表 values(2,40)insert 数量表数量表 values(4,500)insert 数量表数量表 values(5,23)goselect A.药品编号药品编号,药品名称药品名称,B.药品编号药品编号,数量数量 -内连接查询内连接查询from

15、 药品表药品表 A,数量表数量表 B where A.药品编号药品编号=B.药品编号药品编号goselect A.药品编号药品编号,药品名称药品名称,B.药品编号药品编号,数量数量 -左外连接查询左外连接查询from 药品表药品表 A left join 数量表数量表 B on A.药品编号药品编号=B.药品编号药品编号goselect A.药品编号药品编号,药品名称药品名称,B.药品编号药品编号,数量数量 -右外连接查询右外连接查询from 药品表药品表 A right join 数量表数量表 B on A.药品编号药品编号=B.药品编号药品编号select A.药品编号药品编号,药品名称药

16、品名称,B.药品编号药品编号,数量数量 -全外连接查询全外连接查询from 药品表药品表 A full join 数量表数量表 B on A.药品编号药品编号=B.药品编号药品编号go select A.药品编号药品编号,药品名称药品名称,B.药品编号药品编号,数量数量 -交叉连接查询交叉连接查询from 药品表药品表 A cross join 数量表数量表 B连接查询连接查询内连接查询左外连接查询右外连接查询全外连接查询交叉连接查询结果连接结果再猜一猜：以下返回多少行？SELECT A.药品编号,A.药品名称,B.数量 From 药品表 AINNER JOIN 数量 BON A.药品编号 B

17、.药品药品编号猜一猜：这样写，返回的查询结果是一样的吗？SELECT A.药品编号,B.药品名称,B.数量 From 数量 BINNER JOIN 药品表 AON A.药品编号=B.药品药品编号123长白山人参清热凉茶块剂板兰根药品表药品表124510004050023数量表数量表1长白山人参2清热凉茶100040查询结果查询结果内连接查询结果SELECT A.药品编号,B.药品名称,B.数量 From 药品表 AINNER JOIN 数量 BON A.药品编号=B.药品药品编号猜一猜：这样写，返回的查询结果是一样的吗？SELECT A.药品编号,B.药品名称,B.数量 From 数量 BLe

18、ft JOIN 药品表 AON A.药品编号=B.药品药品编号123长白山人参清热凉茶块剂板兰根药品表药品表124510004050023数量表数量表1长白山人参2清热凉茶1000403块剂板兰根null查询结果查询结果连接结果再猜一猜：以下返回多少行？SELECT A.药品编号,A.药品名称,B.数量 From 药品表 ALeft JOIN 数量 BON A.药品编号 B.药品药品编号SELECT A.药品编号,B.药品名称,B.数量 From 药品表 ALeft JOIN 数量 BON A.药品编号=B.药品药品编号左外连接查询结果123长白山人参清热凉茶块剂板兰根药品表药品表连接结果12

19、4510004050023数量表数量表再猜一猜：以下返回多少行？SELECT A.药品编号,A.药品名称,B.药品编号,B.数量 From 药品表 ARight JOIN 数量 BON A.药品编号 B.药品药品编号SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 药品表 ARight JOIN 数量 BON A.药品编号=B.药品药品编号猜一猜：这样写，返回的查询结果是一样的吗？SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 数量 BRight JOIN 药品表 AON A.药品编号=B.药品药品编号右外连接查询结果1长白山人参2清热凉茶

20、查询结果查询结果12nullnull4100040500nullnull523124510004050023数量表数量表123长白山人参清热凉茶块剂板兰根药品表药品表连接结果猜一猜：这样写，返回的查询结果是一样的吗？SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 数量 BFull JOIN 药品表 AON A.药品编号=B.药品药品编号再猜一猜：以下返回多少行？SELECT A.药品编号,A.药品名称,B.药品编号,B.数量 From 药品表 AFull JOIN 数量 BON A.药品编号 B.药品药品编号SELECT A.药品编号,B.药品名称,B.药品编号,B

21、.数量 From 药品表 AFull JOIN 数量 BON A.药品编号=B.药品药品编号全外连接查询结果1长白山人参2清热凉茶查询结果查询结果123块剂板兰根块剂板兰根nullnullnull4100040null500nullnull523猜一猜：这样写，返回的查询结果是一样的吗？SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 数量 BCross JOIN 药品表 AON A.药品编号=B.药品药品编号124510004050023数量表数量表连接结果123长白山人参清热凉茶块剂板兰根药品表药品表1长白山人参长白山人参1长白山人参长白山人参查询结果查询结果12

22、1长白山人参长白山人参1长白山人参长白山人参45100040500232清热凉茶清热凉茶110002清热凉茶清热凉茶2清热凉茶清热凉茶242清热凉茶清热凉茶3块剂板兰根块剂板兰根51405002310003块剂板兰根块剂板兰根2403块剂板兰根块剂板兰根45003块剂板兰根块剂板兰根523SELECT A.药品编号,B.药品名称,B.药品编号,B.数量 From 药品表 ACross JOIN 数量 BON A.药品编号=B.药品药品编号交叉连接查询结果1.5 复合连接条件查询 例例7：查询成绩在查询成绩在75分以上的学生的学号、姓名、专业代码分以上的学生的学号、姓名、专业代码和专业学级，选修

23、课的学期、课程号、成绩，任课教师的教和专业学级，选修课的学期、课程号、成绩，任课教师的教师编号、姓名。范例师编号、姓名。范例6-9代码如下：代码如下：USE studentGOSELECT 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.教师编号GO2 案例应用举例 n2.1 自动注册必修课n2.2 重修未取得学分的必修课n2.3 查询学生各门课程的成绩 n2.4 查询学生的总学分附件n-

24、(1)使用使用“学生学生”表生成表生成“学生学生1”表的表结构。表的表结构。USE studentGOSELECT 学号学号,姓名姓名 INTO 学生学生1FROM 学生学生WHERE 1=2GO-(2)使用使用“课程注册课程注册”表生成表生成“单科成单科成绩绩”表的表结构。表的表结构。SELECT 学号学号,成绩成绩 INTO 单科成绩单科成绩FROM 课程注册课程注册WHERE 1=2GO-(3)向向“学生学生1”表和表和“单科成绩单科成绩”表中添加数据。表中添加数据。INSERT INTO 学生学生1(学号学号,姓名姓名)VALUES(11,杨杨文生文生)INSERT INTO 学生学生

25、1(学号学号,姓名姓名)VALUES(12,唐唐国栋国栋)INSERT INTO 学生学生1(学号学号,姓名姓名)VALUES(14,梁梁明亮明亮)GOINSERT INTO 单科成绩单科成绩(学号学号,成成绩绩)VALUES(11,80)INSERT INTO 单科成绩单科成绩(学号学号,成成绩绩)VALUES(13,98)INSERT INTO 单科成绩单科成绩(学号学号,成成绩绩)VALUES(15,76)GO-(4)交叉连接查询。交叉连接查询。SELECT*FROM 学生学生1 CROSS JOIN 单科成绩单科成绩GO附件1左外连接将“学生1”表左外连接“单科成绩”表。代码如下：USE student GOSELECT*FROM 学生1 LEFT OUTER JOIN 单科成绩 ON 学生1.学号=单科成绩.学号GO附件2右外连接将“学生1”表右外连接“单科成绩”表。代码如下：USE student GOSELECT*FROM 学生1 RIGHT OUTER JOIN 单科成绩 ON 学生1.学号=单科成绩.学号GO附件3全外连接用全连接的方法连接“学生1”表与“单科成绩”表。代码如下：USE studentGOSELECT*FROM 学生1 FULL OUTER JOIN 单科成绩 ON 学生1.学号=单科成绩.学号GO