《SQL Server 2008从入门到精通》--20180703

2023-08-24 08:47:16

SELECT操作多表数据

关于连接的问题，在《SQL必知必会》学习笔记中已经讲到过，但是没有掌握完全，所以再学一下。

JOIN连接

首先我们先来看一下最简单的连接。Products表和Vendors表的连接

示例1

Products表数据如下

Vendors表数据如下

将这两张表通过以下语句连接

USE test

SELECT prod_name,Products.vend_id,vend_name

FROM Products,Vendors

WHERE Products.vend_id=Vendors.vend_id;

--连接条件为两张表的vend_id相同

连接结果如下

当用户所需数据不在一个表中，而在多个表中，就需要使用多表连接，将多个表中的数据组合到一起，使数据更直观地体现出来。在上述语句中，通过JOIN我们可以从结果中很明确地知道产品及其供应商。

内连接

内连接是一种比较常用的数据连接查询方式，上述语句就是内连接。下面将给出另一种内连接的语法。两者的查询结果是一样的。

--内连接INNER JOIN

USE test

SELECT prod_name,Products.vend_id,vend_name

FROM Products INNER JOIN Vendors--和上面语句差异之处

ON Products.vend_id=Vendors.vend_id;

注：使用INNER JOIN时，关键字INNER可省略，但是连接条件必须用ON连接，不能用FROM了。

内连接分为等值连接，非等值连接和自然连接三种。

等值连接

连接条件用等号相连，查询结果中列出被连接表中的所有列，包括重复列。为了更加形象地体现“列出重复列”这个特点，接下来还是用Products和Vendors表来举例。逻辑是：一种产品可以对应多个供应商，一个供应商可以生产多种产品。

示例2

Products表数据如下

Vendors表数据如下

然后执行等值连接的代码

SELECT * FROM Vendors,Products

WHERE Vendors.vend_id=Products.vend_id;

结果如图所示

可以看到，在结果中vend_id列重复。

注：等值连接要满足2个条件，连接条件用=号，查询结果中包含被连接表中所有列，包含重复列。

非等值连接

连接条件中使用除等号以外的比较运算符来比较连接列的列值。例如使用>，<，>=，<=，<>等，也可以使用范围运算符BETWEEN。下面用stu表和class表来示范非等值连接

示例3

新建stu表并插入数据，stu表用来表示学生和该学生的年级总成绩

	CREATE TABLE stu(

	sto VARCHAR(8) NOT NULL,

	stname VARCHAR(20) NOT NULL,

	stsex CHAR(2),

	stgrade FLOAT,

	CONSTRAINT pk_sto PRIMARY KEY(sto),

	CONSTRAINT ck_stsex CHECK(stsex in('男','女'))

	);

	INSERT INTO stu(sto,stname,stsex,stgrade)

	VALUES('20180101','李华','男','568'),

	('20180102','张三','男','627'),

	('20180103','孙丽','女','339'),

	('20180104','袁康','男','482'),

	('20180105','王婷','女','761'),

	('20180106','赵四','男','568'),

	('20180107','周其','女','348.5'),

	('20180108','吴伟','男','528.5'),

	('20180109','甄诚','女','702');

	SELECT * FROM stu;

stu表数据如下

新建class表并插入数据，class表用来表示分班情况和年级总分之间的关系

	CREATE TABLE class(

	cla_no CHAR(2),

	cl CHAR(1),

	flag CHAR(6),

	gradelevel_h INT,

	gradelevel_l INT,

	CONSTRAINT pk_cla_no PRIMARY KEY(cla_no),

	CONSTRAINT ck_cl CHECK(cl in('A','B','C','D'))

	);

	INSERT INTO class(cla_no,cl,flag,gradelevel_l,gradelevel_h)

	VALUES('01','A','创新班','700','750'),

	('02','B','重点','650','700'),

	('03','B','重点','600','650'),

	('04','C','提高','500','600'),

	('05','C','提高','400','500'),

	('06','D','补习','300','400'),

	('07','D','补习','0','300');

	SELECT * FROM class;

Cla表数据如下

现在要通过查询直观体现出学生分班情况

	SELECT sto,stname,cla_no,flag,stgrade

	FROM stu INNER JOIN class

	ON stu.stgrade<=class.gradelevel_h AND stu.stgrade>class.gradelevel_l

	ORDER BY sto;

结果如图所示

自然连接

要求连接条件是相同的属性组，且用等号连接，并在结果中把重复的属性列去掉。自然连接是去掉重复属性列的等值连接。

在SQL Server 2008中，不支持NATURAL JOIN关键字，在其他数据库中可以使用关键字NATURAL JOIN进行连接。

SELECT * FROM Vendors NATURAL JOIN Products;

上面的三个连接示例中，示例1是自然连接，示例2因为有重复列vend_id因此不满足自然连接的定义。示例3因为连接条件不是用等号连接，且连接条件属性列不同，因此不满足自然连接的定义。

注：对等值连接和自然连接的定义不是很清晰，后续弄清楚了会有补充。

主要问题：

1.一个连接满足等值连接所有条件，唯独查询结果中不包含被连接表的所有列，而只包含部分列，是否是等值连接。

2.等值连接是否必须包含重复列，不包含重复列但满足等值连接其他条件，是否就不是等值连接？网上说自然连接是特殊的等值连接，按照自然连接不包含重复列的定义，那么等值连接也可以不包含重复列。

3.自然连接是否要求连接条件的属性列列名和数据类型都相同？如果仅列名不同，其他条件都满足自然连接，是否可以判断为自然连接。百度自然连接的定义说列名不一定相同，但网上也有人说列名必须相同。（答案是列名必须相同。自然连接会自动匹配列名相同列，如果列名相同，数据类型不同，能进行隐式转换就能匹配成功，不能进行隐式转换就会报错）

Natural Join: Guidelines

The associated tables have one or more pairs of identically named columns.
The columns must be the same data type.
Don’t use ON clause in a natural join.

注：关于两表中含有多个相同列，如何选择部分列作为连接字段进行自然连接，在其他文章中再详述。

外连接

与内连接的区别在于，假如表中有数据在另一张表中找不到连接对象，内连接的做法是在结果中忽略这些数据不打印，而外连接的做法是打印这些数据，而这些数据在另一张表中的连接对象则打印成NULL。

参与外连接的表有主从之分，根据表的位置和语句中指定的关键字，外连接可分为左外连接，右外连接，完全连接。

左外连接（LEFT OUTER JOIN）

返回JOIN左侧表所有行，且返回JOIN右侧表中能匹配上的行并对两个表的行进行匹配。

右外连接（RIGHT OUTRE JOIN）

返回JOIN右侧表所有行，且返回JOIN左侧表中能匹配上的行并对两个表的行进行匹配。

完全连接（FULL OUTER JOIN）

返回JOIN左右两侧表所有能匹配的行和不能匹配的行。

下面依次举例

示例4：Student表和Stu_Grade表

Student表数据如下

Stu_Grade表数据如下

对Student表和Stu_Grade表的左外连接

SELECT* FROM Student--左外连接，Student表为主，Stu_Grade表为从

LEFT OUTER JOIN Stu_Grade

ON Student.stu_id=Stu_Grade.stu_id;

查询结果如图所示

图中显示了主表（Student）的所有行和从表（Stu_Grade）的部分行。

对Student表和Stu_Grade表的右外连接

SELECT* FROM Student--右外连接，Student表为从，Stu_Grade表为主

RIGHT OUTER JOIN Stu_Grade

ON Student.stu_id=Stu_Grade.stu_id;

查询结果如图所示

图中显示了主表（Stu_Grade）的所有行和从表（Student）的部分行。

对Student表和Stu_Grade表的完全连接

SELECT* FROM Student--完全连接

FULL OUTER JOIN Stu_Grade

ON Student.stu_id=Stu_Grade.stu_id;

查询结果如图所示

图中显示了Student表和Stu_Grade表的所有行。

交叉连接

交叉连接将生成来自两个基表所有可能的组合。不使用WHERE子句，交叉连接的结果是两个基表所有行的笛卡尔积，使用WHERE子句，交叉连接的结果满足WHERE子句条件的数据行数的笛卡尔积

示例5：Student表和Course表演示交叉连接

Student表如图所示，为了更直观地看到数据，我缩减了部分Student表的数据

Course表如图所示

对Student和Course表进行无WHERE子句的交叉连接

SELECT * FROM Student

CROSS JOIN Course;

查询结果如图所示

共24条数据，正是Student表和Course表的笛卡尔积。

对Student表和Course表进行有WHERE子句的交叉连接

SELECT * FROM Student

CROSS JOIN Course

WHERE Student.stu_id='20180101';

查询结果如图所示

Student表中满足WHERE子句的只有1条数据，查询结果正是这条数据和Course表的笛卡尔积。

自连接

一个表与自身相连接的查询。主要是用以处理列与列之间的关系。以PCourse表为例，CNumber代表该课程的课程号，PCNumber代表该课程的先修课课程号。

示例6：PCourse表的自连接查询

PCourse表的数据如图所示

对PCourse表进行自连接查询

SELECT A.PCNumber,A.PCName,B.CNumber,B.CName

FROM PCourse A,PCourse B

WHERE A.CNumber=B.PCNumber;

查询结果如图所示

码农公寓