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

外连接查询
外连接语法结构
select <选择列表> From <表1> <left|right|full][outer] join <表2> On <表1>.<列名1>= <表2>.<列名2>
外连接查询
外连接（示例）
在学生基本情况表tblstudent和成绩表tblScore中查询学 生选课情况，如果学生没有选课，则在成绩表tblScore将没有 该学生的成绩信息，课程和成绩列用空值填充。（用左外连接）
连接查询
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.药品编号,数量 from 药品表 A full join 数量表 B on A.药品编号=B.药品编号 go select A.药品编号,药品名称,B.药品编号,数量 from 药品表 A cross join 数量表 B

交叉连接(CROSS JOIN)
1.1 交叉连接查询
1.交叉连接的连接过程
例如，有一个产品表和一个产品销售表，如表1、表2所示， 两个表交叉连接后产生的结果如表3所示，结果集是两个表记
产品名称
录的交叉乘积，列是两个表列的集合。
产品编号 0001 0002 产品名称
①
产品编号 0001
②
销量 25
1 连接查询

1.1 交叉连接查询 1.2 等值与非等值连接查询 1.3 自身连接查询 1.4 外连接查询 1.5 复合连接条件查询
1 连接查询

内连接(INNER JOIN) 外连接


——左外连接 (LEFT JOIN) ——右外连接 (RIGHT JOIN) ——完整外连接 (FULL JOIN)
select StudID,StuName,tblstudent.ClassID, EnrollYear,speciality tblclass from tblstudent ， join tblclass where tblstudent.ClassID=tblclass.ClassID on tblstudent.ClassID=tblclass.ClassID
数据检索（三）高级查询
连接查询
连接查询

实现从两个或两个以上表中检索数据且 结果集中出现的列来自于两个或两个以 上表中的检索操作被称为连接查询（连 接技术），或者说连接技术是指对两个 表或两个以上表中数据执行乘积运算的 技术。在设计表时，为了提高表的设计 质量，经常把相关数据分散到不同的表 中。但是，在使用数据时，需要把这些 数据集中在一个查询语句中。
INNER JOIN 数量 B 块剂板兰根 ON 3 A.药品编号 <> B.药品编号
4 5 数量 1000 40 500 23
猜一猜：这样写，返回的查询结果是一样的吗？
SELECT From INNER JOIN ON
查询结果 ,B.药品名称,B.数量 A.药品编号 药品编号 数量 数量 B 药品名称 药品表 A 1 1000 长白山人参 A.药品编号 = B.药品编号 2 40 清热凉茶
0003
0003 0005 0005 0005
30
30 35 35 35
表3
交叉连接查询
交叉连接语法结构
select <选择列表> From <表1> cross join <表2> On <表1>.<列名1>= <表2>.<列名2>
1.1 交叉连接查询
2.交叉连接的语法格式如下：
SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2
1.4 外连接查询
3. 全连接
例6 ：用全连接的方法连接产品表与产品销售表。 代码如下： USE STUDENT GO SELECT * FROM 产品 FULL OUTER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号 GO 练习：范例6-8
连接查询示例
create table 药品表 --建立药品表 （1）建立两个表：药品表和数量表。 (药品编号 int primary key, （2）输入药品表和数量表记录。 药品名称 varchar(20) not null） （3）对药品表和数量表实施内连接查询、左外连接查询、右外 insert 药品表 values(1,'长白山人参') --在药品表中插入数据 连接查询、全外连接查询。 insert 药品表 values(2,'清热凉茶') （4）对药品表和数量表实施交叉连接查询。 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 数量表 values(5,23)
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 产品.产品编号=产品销售.产品编号 GO 练习：范例6-7
内连接查询
内连接（示例）
在学生基本情况表tblstudent，班级表tblclass和系别表 tbldepart三表中查询学生信息，包括学生的学号、姓名、班级 名称和系别名称。
select StudID,StuName, ClassName, DepartName from tblstudent, tblclass, tbldepart where tblstudent.ClassID=tblclass.ClassID and tblclass.DepartID=tbldepart.DepartID
--内连接查询
--左外连接查询
--右外连接查询
--全外连接查询
--交叉连接查询
连接查询
内连接查询
左外连接查询
右外连接查询
全外连接查询
交叉连接查询结果
内连接查询结果
连接结果 数量表 再猜一猜：以下返回多少行？ SELECT A.药品编号,B.药品名称,B.数量 药品表 药品编号 From 药品表 A 药品编号 药品名称 1 SELECT A.药品编号 ,A.药品名称,B.数量 INNER JOIN 数量 B 1 长白山人参 2 From 药品表 A ON 2 A. 药品编号 = B. 药品编号 清热凉茶
1.2 等值与非等值连接查询（内连接）
例1 ：用等值连接的方法连接产品表和产 品销售表。代码如下：
USE STUDENT GO SELECT * FROM 产品 INNER JOIN 产品销售 ON 产 品.产品编号=产品销售.产品编号 GO
1.2 等值与非等值连接查询（内连接）
例 2 ：自然连接产品表和产品销售表。 代码如下：
左外连接查询结果
连接结果 数量表 再猜一猜：以下返回多少行？ SELECT A.药品编号,B.药品名称,B.数量 药品表 药品编号 From 药品表 A 药品编号 药品名称 1 SELECT A.药品编号 ,A.药品名称,B.数量 Left JOIN 数量 B 1 长白山人参 2 From 药品表 A ON 2 A. 药品编号 = B. 药品编号 清热凉茶
1.4 外连接查询
外连接有分为左外连接、右外连接、全外
连接三种。外连接除产生内连接生成的结果集 外，还可以使一个表（左、右外连接）或两个
表（全外连接）中的不满足连接条件的记录也
出现在结果集中。
外连接查询
外连接语法结构 select <选择列表> From <表1> <left|right|full][outer] join <表2> On <表1>.<列名1>= <表2>.<列名2> (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.ClassID<b.ClassID 注：a.ClassID<b.ClassID主要是为了避免交叉连接而出现无意义的行。
USE student GO SELECT DISTINCT a.学号,a.课程号 FROM 课程注册 AS a JOIN 课程注册 AS b ON a.学号=b.学号 AND a.课程号!=b.课程号 GO