sqlserver练习----涉及多个表的连接查询

等值联接

多表查询语句中的连接条件使用的是等号,例：Student.Sno=SC.Sno

例：

Student

学号 Sno	姓名 Sname	性别 Ssex	年龄 Sage	所在系 Sdept
202015121	李勇	男	20	CS
202015122	刘晨	女	10	CS
202015123	王敏	女	18	MA
202015125	张力	男	19	IS

SC:

学号 Sno	课程号 Cno	成绩 Grade
202015121	1	92
202015121	2	85
202015121	3	88
202015122	2	90
202015122	3	80

查询每个学生及其选修课程的情况

select Student.*,SC.*
from Student,SC
where Student.Sno=SC.Sno;

查询选修2号课程且成绩在88分以上的所有学生的学号和姓名

select Student.Sno,Sname
from Student,SC
where Student.Sno=SC.Sno
and SC.Cno='2'
and SC.Grade>'88';

对于”select Student.Sno,Sname“，Sno在两张表都出现了，所以必须加上表名或者别名前缀，Sname只在Student表中出现，引用时可以去掉。

等值连接和自然连接的区别：

1.自然连接一定是等值连接，但等值连接不一定是自然连接。

例如在数据库中：

S：

A	B
1	2
3	3
5	9

R:

B	C
2	1
7	2
3	5

等值连接（S.A=R.C）

A	S.B	R.B	C
1	2	2	1
5	9	3	5

等值连接（S.B=R.B）

A	S.B	R.B	C
1	2	2	1
3	3	3	5

自然连接

A	B	C
1	2	1
3	3	5

2.等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。

3.等值连接不把重复的属性除去；而自然连接要把重复的属性除去。所以等值连接将目标列重复的属性列去掉，就是自然连接

注：

两个关系R和S在做自然联接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，从而造成R中这些元组(悬挂元组)在操作时被舍弃了，这些被舍弃的元组称为悬浮元组，又称为悬挂元组。

例如：S中的5,9     R中的7,2

补充：

半连接：关系R和S的半连接是R和S的自然连接在关系R的属性集上的投影

关系R

A	B	C
a	b	c
d	b	c
b	b	f
c	a	d

关系S

B	C	D
b	c	d
b	c	e
a	d	b

A	B	C	D
a	b	c	d
a	b	c	e
d	b	c	d
d	b	c	e
c	a	d	b

 

A	B	C
a	b	c
d	b	c
c	a	d

B	C	D
b	c	d
b	c	e
a	d	b

 

非等值联接：多表查询语句中的连接条件不是使用等号

例如：下面的between..and...，就表示员工表（emp）中员工的工资，不能超过职级表（jg）中的最高工资，不能低于其最低工资。

SELECT emp.employee_id, emp.last_name, jg.grade_level
FROM employees emp, job_grades jg
WHERE emp.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

自身连接（自连接）

一个表与其自己进行连接，由于属性名都是同名属性，因此需要给表起别名前缀

SPJ表：

 检索至少使用了零件编号为P3,P5的工程编号JNO

法一:使用where

select distinct X.JNO
from SPJ AS X,SPJ AS Y
WHERE X.JNO=Y.JNO
AND X.PNO='P3'
AND Y.PNO='P5';

法二：使用 inner join ....on(也可以使用join.....on)

select distinct X.JNO
from SPJ X inner join SPJ Y
on X.JNO=Y.JNO
where X.PNO='P3'
and Y.PNO='P5';

外连接：(left/right/full)outer join...on

还是用开头的例子：

Student:

学号 Sno	姓名 Sname	性别 Ssex	年龄 Sage	所在系 Sdept
202015121	李勇	男	20	CS
202015122	刘晨	女	10	CS
202015123	王敏	女	18	MA
202015125	张力	男	19	IS

SC:

学号 Sno	课程号 Cno	成绩 Grade
202015121	1	92
202015121	2	85
202015121	3	88
202015122	2	90
202015122	3	80

left outer join....on

左外连接，LEFT OUTER JOIN，告诉DBMS生成的结果表中，除了包括匹配行外，还包括JOIN关键字（FROM子句中）左边表的不匹配行，即将左边表的悬浮元组保存在结果关系中，而其他属性填上空值。左外连接实际上可以表示为：

左外连接 = 内连接 + 左边表中失配的元组

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student LEFT OUTER JOIN SC
ON (Student.Sno=SC.Sno);

 right outer join..on

右外连接（RIGHT OUTER JOIN）告诉DBMS生成的结果表中，除了包括匹配行外，还包括JOIN关键字（FROM子句中）右边表的不匹配行，即将右边表中的悬浮元组保留下来，而在其他属性上填入空值。右外连接实际上可以表示为：

右外连接 = 内连接 + 右边表中失配的元组
 

full outer join..on

全外连接，FULL OUTER JOIN，告诉DBMS生成的结果表中，除了包括匹配行外，还包括JOIN关键字（FROM子句中）左边表和右边表的不匹配行，即把两张表的悬浮元组都保留下来，其他属性填为空值。全外连接实际上可以表示为：

全外连接 = 内连接 + 左边表中失配的元组 + 右边表中失配的元组。

 
这个例子展现的关系不明显，在举一个例子：

关系R：

A	B	C
a	b	c
b	b	f
c	a	d

关系S：

B	C	D
b	c	d
b	c	e
a	d	b
e	f	g

左外连接：

A	B	C	D
a	b	c	d
a	b	c	e
c	a	d	b
b	b	f	null

右外连接：

A	B	C	D
a	b	c	d
a	b	c	e
c	a	d	b
null	e	f	g

全外连接：

A	B	C	D
a	b	c	d
a	b	c	e
c	a	d	b
b	b	f	null
null	e	f	g

 

外部并：

两个关系R和S在做并操作时，构成的新关系属性由R和S的所有属性组成（公共属性只取一次），新关系的元组由属于R或属于S的元组构成，同时元组在新增加的属性上填空值。

A	B	C	D
a	b	c	null
a	b	c	null
c	a	d	null
null	b	c	d
null	b	c	e
null	a	d	b
null	e	f	g

交叉连接CROSS JOIN

结果包含所有连接表中的所有可能的行组合，交叉连接返回的是两个表的笛卡尔积

例：

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student CROSS JOIN SC

将会返回所有可能的组合

 如果加上“where (Student.Sno=SC.Sno);”

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student  cross JOIN SC
where (Student.Sno=SC.Sno);

结果：

 我们发现，将cross join换成“，”（逗号）效果相同