sql server多表的关系设置 sql server多表查询

先复习一下上节课实验报告最后的知识点：having语句只能使用在group by语句后，用于进一步筛选分组后的数据。

select Sdept,count(*) as 人数
from Student
group by Sdept; --各个专业的人数

select Sdept,count(*) as 人数
from Student
group by Sdept having count(*)>1 
order by 人数; --查询专业人数大于1的专业，按升序排序

• 1、连接查询

• 1.1 笛卡尔积
• 1.2 等值连接、非等值连接与多表连接
• 1.3 自身连接

• 2、外连接

• 2.1 左外连接
• 2.2 右外连接
• 2.3 全外连接

• 3、结束

1、连接查询

1.1 笛卡尔积

关系型数据库中，两个表的笛卡尔积包括了两表连接的所有形成的元组

select * from Student,Course; --两表用逗号分隔，不添加where选择条件

以上为部分数据，全部行数应为两表各自行数的乘积

需要使用where语句等进一步筛选，大部分时候笛卡尔积的连接是没有意义的

1.2 等值连接、非等值连接与多表连接

[表名1] <列名1> <比较运算符> [表名2] <列名2>

比较运算符使用 = 时为等值连接，使用其他运算符如 >、<、>=、<= 时非等值连接

SQL语句中，当涉及到多个表的列名时，需要指定该列在哪个表中（表名.列名）

--等值连接
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno; --查询选课学生所选课的信息

--非等值连接，多表连接
select Student.Sno,Student.Sname,Student.Sage,SC.Cno,Course.Cname
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Student.Sage>20; --查询20岁以上选课学生所选课的信息

1.3 自身连接

自身连接即一个表与其自己进行连接，如查询某一个课程先修课的先修课

使用自身连接相当于把一个表视为两份，需要为每份在from语句后起别名

select First_table.Cno,First_table.Cpno,second_table.Cpno as 先修的先修
from Course as First_table,Course as second_table
where First_table.Cpno=second_table.Cno; --每份表起别名

若去除空值，需要使用 is not null 语句，不能使用 !=null

2、外连接

多表在连接时，会因为数据在某表的不存在而形成悬浮元组，例如有些学生没有选课，选课表中没有他们的学号。外连接即如何处理这些悬浮元组

2.1 左外连接

[表1] left outer join [表2] on(连接条件)

T-SQL中 outer 可省略

左外连接保留左侧表（表1）的悬浮元组

--两个表左外连接
select Student.Sno,Student.Sname,SC.Cno
from Student left join SC on(Student.Sno=SC.Sno);

--三个表左外连接
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno) 
	left join Course on(SC.Cno=Course.Cno);

没有选课的学生也输出显示，缺失的属性赋值空值

2.2 右外连接

[表1] right outer join [表2] on(连接条件)

左外连接保留右侧表（表2）的悬浮元组

select Course.Cno,Course.Cname,SC.Sno
from SC right join Course on(SC.Cno=Course.Cno); --两个表右外连接

select Course.Cno,Course.Cname,SC.Sno,Student.Sname
from SC right join Course on(SC.Cno=Course.Cno)
	left join Student on(SC.Sno=Student.Sno); --先右外连接后左外连接

Sno属性为空表示该课没有学生选择

2.3 全外连接

[表1] full outer join [表2] on(连接条件)

保留表1和表2的所有悬浮元组，缺失的属性值赋值null

select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
	full join Course on(SC.Cno=Course.Cno); --左外连接，全外连接

select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
	left join Course on(SC.Cno=Course.Cno); --左外连接，左外连接

select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
	right join Course on(SC.Cno=Course.Cno); --左外连接，右外连接

选择不同而连接方式，即选择如何处理悬浮元组

3、结束

几个表的连接查询果然比简单的单表查询难，与前面学习的关系代数运算的理论知识联系密切，感觉那里的几种连接运算熟悉了，写成SQL代码也会很快理解