Sql Server的艺术（五） SQL UNION与UNION JOIN运算符

学习本节所用表：

CREATE TABLE TEACHER
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主键，自增长
    TNO INT NOT NULL, --教工号
    TNAME CHAR(10) NOT NULL, --教师姓名
    CNO INT NOT NULL, --课程号
    SAL INT, --工资
    DNAME CHAR(10) NOT NULL, --所在系
    TSEX CHAR(2) NOT NULL, --性别
    AGE INT NOT NULL --年龄
)
INSERT INTO dbo.TEACHER VALUES( 1,'王军',4,400,'数学','男',32)
INSERT INTO dbo.TEACHER VALUES( 2,'李彤',5,6600,'生物','女',54)
INSERT INTO dbo.TEACHER VALUES( 3,'王永军',1,1000,'计算机','女',45)
INSERT INTO dbo.TEACHER VALUES( 4,'刘晓婧',2,8000,'计算机','女',23)
INSERT INTO dbo.TEACHER VALUES( 5,'高维',8,6000,'电子工程','男',54)
INSERT INTO dbo.TEACHER VALUES( 6,'李伟',7,230,'机械工程','女',23)
INSERT INTO dbo.TEACHER VALUES( 7,'刘辉',3,0,'生物','女',65)
INSERT INTO dbo.TEACHER VALUES( 8,'刘伟',9,500,'计算机','男',23)
INSERT INTO dbo.TEACHER VALUES( 9,'刘静',12,0,'经济管理','男',45)
INSERT INTO dbo.TEACHER VALUES( 10,'刘奕锴',13,70000,'计算机','女',65)
INSERT INTO dbo.TEACHER VALUES( 11,'高维',14,70000,'经济管理','男',61)

CREATE TABLE COURSE
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主键，自增长
    CNO INT NOT NULL, --课程号
    CNAME CHAR(30) NOT NULL, --课程名称
    CTIME INT NOT NULL, --学时
    SCOUNT INT NOT NULL, --容纳人数
    CTEST SMALLDATETIME NOT NULL, --考试时间
)
INSERT INTO dbo.COURSE VALUES( 4,'应用数学基础',48,120,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 5,'生物工程概论',32,80,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 1,'计算机软件基础',32,70,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 2,'计算机硬件基础',24,80,'2006-6-28')
INSERT INTO dbo.COURSE VALUES( 8,'模拟电路设计',28,90,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 7,'机械设计实践',48,68,'2006-7-14')
INSERT INTO dbo.COURSE VALUES( 3,'生物化学',32,40,'2006-7-2')
INSERT INTO dbo.COURSE VALUES( 9,'数据库设计',16,80,'2006-7-1')
INSERT INTO dbo.COURSE VALUES( 6,'设计理论',28,45,'2006-6-30')
INSERT INTO dbo.COURSE VALUES( 10,'计算机入门',25,150,'2006-6-29')
INSERT INTO dbo.COURSE VALUES( 11,'数字电路设计基础',30,125,'2006-6-20')
CREATE TABLE STUDENT
(
    ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    SNO CHAR(4) NOT NULL,    --学号
    SNAME CHAR(10) NOT NULL, --姓名
    DNAME CHAR(10) NOT NULL, --系
    SSEX CHAR(2) NOT NULL,   --性别
    CNO INT ,                --课程号
    MARK DECIMAL(3,1),       --成绩
    TYPE CHAR(4)             --课程类型
)
INSERT INTO dbo.STUDENT VALUES('9701','刘建国','管理工程','男',4,82.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9701','刘建国','管理工程','男',10,70,'选修')
INSERT INTO dbo.STUDENT VALUES('9701','刘建国','管理工程','男',1,78.5,'选修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','环境工程','女',5,63,'必修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','环境工程','女',10,58,'选修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',5,48.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',2,86,'选修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',4,76,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',1,92,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',2,89,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',9,80,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',8,70,'选修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',8,79,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',1,59,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',11,52,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',6,68,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',13,93,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',12,88.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',1,78,'选修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',10,76,'选修')

数据表

表数据

1、关系的集合运算

　　集合的3个最普通的运算是并、交和差。对于任意集合R和S（当然，这里的R和S可以是表R和表S），这些运算定义如下。

　　R并S，R或S或两者中元素的集合。一个元素在并集中只出现一次，即使它在R和S中都存在。

　　R交S，R和S中都存在的元素的集合。

　　R差S，在R中而不在S中的元素的集合。注意R差S不同于S差R，后者是在S中而不在R中的元素的集合。
　　关于交、并、差运算的示意图如图所示。

　　

　　标准SQL中，可以使用UNION运算符实现集合并的运算，但是没有直接提供集合交和集合差操作，可用其他方法来实现。

 

2、UNION运算符

使用UNION运算符执行集合并运算　 
　　 --首先选出CNO=1的学生，作为一个集合，然后在选出SNO=10的学生，作为一个集合，最后对两个集合用UNION，即运算，得到最终结果
    SELECT SNO,SNAME,DNAME FROM dbo.STUDENT WHERE CNO=1 UNION SELECT SNO,SNAME,DNAME FROM dbo.STUDENT WHERE CNO=10　--选择1或10的学生部分信息
    --注意：如果用UNION，SELECT后的子句不写字段，则结果会出现跟下面语句一样（就是没有消除重复行）
    --下面两条语句得到的结果是一样的，同样也可以用OR来实现
    SELECT * FROM dbo.STUDENT WHERE CNO=1 UNION SELECT * FROM dbo.STUDENT WHERE CNO=10
    SELECT * FROM dbo.STUDENT WHERE CNO=1 OR CNO=10
    --注意：UNION运算符执行集合并运算，自动从结果表中消除重复的行
　　--使用UNION运算符操作后，要保留重复元组的话，必须在UNION运算符后使用ALL关键字指明（UNION改为UNION　ALL）
　　--参与运算的两个集合必须选择同样数量的列，并且相应的列必须具有相同的类型

　　   

对不同表不同字段采用UNION运算符，并用ORDER BY子句排序
    SELECT SNAME,CNO,DNAME FROM dbo.STUDENT WHERE CNO=1 OR CNO=10 UNION SELECT TNAME,CNO,DNAME FROM dbo.TEACHER WHERE CNO=1 OR CNO=10 ORDER BY CNO DESC

　　  

　　　　由此可见，UNION运算符只要求列的类型匹配即可，而对应的列的列名可以使不同的。

　　　 两条SELECT语句的字段都不一样，SQL　Server将使用UNION运算中第一条SELECT语句中的列名作为结果表中的列名。

　　　　UNION运算符组合起来的SELECT语句中，不能有ORDER　BY子句，但是可以放在最后一条SELECT语句后面。

 

 对多表进行UNION运算

　　　　SELECT CNO AS 学号 FROM dbo.STUDENT WHERE SSEX='男' UNION SELECT CNO AS 学号 FROM dbo.TEACHER WHERE TSEX='女' UNION SELECT CNO AS 学号 FROM dbo.COURSE WHERE CTIME>20   --所有女同学选修的或者男教师开设的或者学时多于20的课程号

连接表进行聚合运算
　　 --演示的是内连接，外连接 左右连接，原理一样
    SELECT t.CNO,COUNT(s.CNO) AS aa FROM dbo.TEACHER AS t INNER JOIN dbo.STUDENT AS s ON t.CNO=s.CNO GROUP BY t.CNO  --学生表中修过这门课程的学生的数目

多表连接综合运用
    SELECT t.TNAME,t.DNAME,c.CNAME,s.SNAME,s.MARK FROM dbo.TEACHER AS t LEFT OUTER JOIN dbo.COURSE AS c ON c.CNO = t.CNO INNER JOIN dbo.STUDENT AS s ON s.CNO = c.CNO ORDER BY t.TNAME

    SELECT s.SNO,s.SNAME,c.CNAME,s.MARK FROM dbo.STUDENT AS s INNER JOIN dbo.COURSE AS c ON c.CNO = s.CNO WHERE c.CNAME='计算机入门'
    UNION
    SELECT s.SNO,s.SNAME,c.CNAME,s.MARK FROM dbo.STUDENT AS s INNER JOIN dbo.COURSE AS c ON c.CNO = s.CNO WHERE c.CNAME='生物工程概论' ORDER BY s.SNO

 

连接多个表时，我们可以认为他们被连成了一个表。尽管没有创建一个物理表，SQL引擎创建了很多虚拟表，当连接表时，可以在每个表中选择任何一列。 关于连接表的数量问题。连接表的数量取决于具体的数据库管理系统，有的规定为最多25个，有的则没有限制。使用时，我们需要查看具体的数据库运行环境。但要注意的是，连接的表越多，响应的时间就越长。 进行多表连接时，我们要特别注意不要忘记查询条件，特别是连接多个表且记录数目较多时。因为如果不指明连接条件，系统将对多表进行笛卡尔连接，会产生庞大的数据。 根据不同的应用的需求，我们要注意连接方式的选择。