SQL Server(三)-查询数据（3）

--多表连接查询和子查询

● 两表连接查询
● 多表连接查询
● 左外、右外、全外连接查询
● 组合查询
● 子查询
● 在SSMS的查询设计器中设计查询
1、 连接查询
（1） 使用无连接规则连接两表（不设置WHERE子句）
所谓无连接规则连接，就是指连接两表的SELECT语句中不设置任何连接条件，这样得到的连接结果是第一个表中的每一行都会和第二个表中的所有行进行连接，即得到一个笛卡尔积。

SELECT  *(或字段列表)
FROM  表名1,表名2

其中，FROM子句中的表名1和表名2是要连接的两个表的名称，用逗号（,）将其隔开。如果SELECT子句中使用星号（*），则查询结果中显示两个表的所有字段。
下面使用具体的例子说明连接的方法。假设有如图所示的数据表T1和T2。

SELECT  *
FROM  T1,T2

（2） 使用有连接规则连接两表（设置WHERE子句）
有连接规则连接，其实就是在无连接规则的基础上，加上WHERE子句指定连接规则的连接方法。

SELECT  *(或字段列表)
FROM  表名1,表名2
WHERE  连接规则

SELECT  *
FROM   T1,T2
WHERE  T1.职工号=T2.职工号

这种使用等于号组成的连接，实际上叫等值连接。需要说明的一点是，只有两表有共同的字段时才可以使用等值连接。
技巧：在多表连接时，即使不要求在表独有的字段前加表名，但笔者还是建议加上表名，因为这样可以很清楚地表示哪个字段属于哪个表，这将对以后的维护起到很好的作用。
【例1】查询名叫“张三”的学生的所有课程的平时成绩和考试成绩，并按考试成绩降序排序。
分析：stu_info表中有学生姓名，但没有成绩，而存储成绩的score表中有成绩，但没有姓名，不过这两个表都有一个共同字段——学号（sno），所以可以将这两个表连接起来进行查询。

SELECT  stu_info.sno,stu_info.sname,score.cno,score.usually,score.exam
FROM   stu_info,score
WHERE  stu_info.sname='张三'
AND    stu_info.sno=score.sno
ORDER BY score.exam DESC

其中，WHERE子句中的条件表达式使用逻辑运算符“AND”，将查询条件（stu_info.sname='张三'）和连接规则（stu_info.sno=score.sno）整合为一体。
（3） 使用多表连接查询数据

【例2】查询名叫“张三”的学生的所有课程的平时成绩和考试成绩，课程用课程名表示，并按考试成绩降序排序，当考试成绩相同时，用平时成绩降序排序。
分析：在上一题中，已经知道了stu_info和score表可以用共同拥有的学号字段进行连接。接下来的问题是将course表连接到上述两个表上。由于stu_info表和course表没有共同字段，所以不能连接，但是score表和course表有共同字段——课号（cno），因此score表和course表可以连接，如此经过score表的搭桥，上述三个表就可以连接了。

SELECT  stu_info.sno,stu_info.sname,course.cname,score.usually,score.exam
FROM   stu_info,score,course
WHERE  stu_info.sname='张三'
AND    stu_info.sno=score.sno
AND    score.cno=course.cno
ORDER BY score.exam DESC,score.usually DESC

首先，SELECT子句中的课号（cno）被换成了课名（cname），其次，FROM子句中列出了需要连接的3个表的名称，最后，WHERE子句中又多了一个“AND”，用来整合score表和course表连接的规则（score.cno=course.cno）。
（4） 使用表别名简化语句
下面的查询语句使用表别名简化了【例2】的查询语句。
 

SELECT  a.sno,a.sname,c.cname,b.usually,b.exam
FROM   stu_info  AS  a,
score   AS  b,
course  AS  c
WHERE  a.sname='张三'
AND    a.sno=b.sno
AND    b.cno=c.cno
ORDER BY b.exam DESC,b.usually DESC

使用表别名不仅可以简化SQL语句，还可以在单条查询语句中多次使用同一个表，这对于自连接查询是非常重要的前提条件。
说明：与设置字段别名相同，设置表别名时，可以省略“AS”关键字。
（5） 使用INNER JOIN连接查询
INNER JOIN进行多表连接，这样WHERE子句中只放置查询条件就可以了。

SELECT  *(或字段列表)
FROM  表名1
INNER JOIN 表名2
ON  连接规则1
INNER JOIN 表名3
ON  连接规则2
……
INNER JOIN表名n
ON  连接规则n

【例3】查询所有考过“心理学”课程的学生的学号、姓名、系别及“心理学”的平时成绩和考试成绩。
 

SELECT  st.sno, st.sname, st.depart, s.usually, s.exam
FROM   score AS s
        INNER JOIN course AS c
        ON s.cno=c.cno
        INNER JOIN stu_info AS st
        ON st.sno= s.sno
WHERE  c.cname='心理学'
ORDER BY s.exam DESC

说明：使用INNER JOIN的连接，通常被人们称为内部连接或内连接。
（6） 连接查询实例
【例4】创建如下图书信息表和出版社信息表两张数据表。
                                                           图书信息表（BOOKINFO）


                                                              出版社信息表（PUBLISHERINFO）

（1）查询图书信息表和出版社信息表使其产生一个笛卡尔积。
（2）根据两张表中的信息，查询出所有图书名称和出版社名称。
（3）查询出所有会计类图书的图书名称、出版社名称及作者信息。
（4）使用INNER JOIN连接查询，查询所有“机械工业出版社”的图书信息。
【解析】
（1）笛卡尔积就是当两个表在无条件查询时产生的，查询语句如下所示：

SELECT * FROM BOOKINFO, PUBLISHERINFO

（2）要查询图书名和出版社名称，由于在图书信息表中只存放了出版社的编号而图书出版社名称是存放在出版社信息表中的，因此要用到两个表的联合查询。代码如下所示：

SELECT  A.BOOKNAME,B.PUBLISHERNAME 
FROM  BOOKINFO A,publisherinfo B
WHERE  A.BOOKPUBLISHERID=B.PUBLISHERID

（3）要查询所有会计类图书是在（2）的基础上再加上一个查询条件得到的，代码如下所示：

SELECT A.BOOKNAME,B.PUBLISHERNAME 
FROM  BOOKINFO A,publisherinfo B
WHERE A.BOOKPUBLISHERID=B.PUBLISHERID 
and A.BOOKNAME like '%会计%'

（4）使用INNER JOIN查询的语句所示：

SELECT A.BOOKNAME,B.PUBLISHERNAME 
FROM BOOKINFO A
INNER JOIN PUBLISHERINFO B
ON A.BOOKPUBLISHERID = B.PUBLISHERID
WHERE B.PUBLISHERNAME = '机械工业出版社'

2、 高级连接查询
（1） 自连接查询：表自身与自身进行连接。

示例stu_info表：


【例5】从stu_info表中，查询“张三”所在院系的所有学生的信息。
分析：按照以前所学的知识，完成本例的查询任务需要两次查询，首先查询“张三”所在的院系，其次才能查询属于该院系的所有学生的信息。

（1）查询“张三”所在的院系名称。
SELECT  depart
FROM  stu_info
WHERE  sname='张三'

（2）根据上面的查询，知道了“张三”在中文系学习，下面查询“中文系”所有学生的信息。
SELECT  *
FROM  stu_info
WHERE depart='中文系'

遇到类似本例的查询任务，应当首选自连接查询。因为自连接查询可以用一条SELECT语句完成本例的查询任务。

SELECT  st1.*
FROM  stu_info AS st1, stu_info AS st2
WHERE  st1.depart = st2.depart
AND   st2.sname = '张三'

上面SELECT子句中“st1.*”的意思是，要显示st1表的所有字段，如果将其改为“*”，则会显示st1和st2表的所有字段
（2） 内连接查询
  内连接包括等值连接、自然连接和不等值连接三种。内连接最大的特点是只返回两个表中互相匹配的记录，而那些不能匹配的记录就被自动去除了。
1．等值连接，连接规则由等于号（=）组合而成
2．自然连接，不将相同的字段显示两次，即在SELECT子句中列出需要显示的字段列表。
3．不等值连接，连接规则由等于号以外的运算符组成，例如，由>、>=、<、<=、<>或BETWEEN等。

下面通过一个示例介绍不等值连接的使用方法。

CREATE TABLE nddzb
(
  起始年份date,
  终止年份date,
  年代     char(6)
)
INSERT INTO nddzb(起始年份,终止年份,年代) VALUES ('1960-1-1','1969-12-31','60年代')
INSERT INTO nddzb(起始年份,终止年份,年代) VALUES ('1970-1-1','1979-12-31','70年代')
INSERT INTO nddzb(起始年份,终止年份,年代) VALUES ('1980-1-1','1989-12-31','80年代')

【例6】从stu_info表中，查询所有学生的出生年代。
分析：要完成此查询任务，需要将stu_info表和nddzb连接起来，但是这两个表没有共同字段，所以没办法使用等值连接，而根据题意可以使用不等值连接。连接规则是如果stu_info表的出生日期在nddzb的起始年份和终止年份之间就可以连接。

SELECT  st.姓名,st.出生日期,n.年代
FROM    stu_info AS st,nddzb AS n
WHERE  st.birth BETWEEN n.起始年份 AND n.终止年份

（3） 左外连接查询：

外连接：所有记录都被包含进去，即使没能匹配的记录也被查询结果集包含在内。

左外连接的规则是将左外连接运算符（LEFT OUTER JOIN）左侧表的所有记录都包含到结果集中，而只将右边表中有匹配的记录包含进结果集

SELECT  *
FROM  t1
LEFT OUTER JOIN t2
ON  t1.职工号=t2.职工号

（4） 右外连接（RIGHT OUTER JOIN）右边表的所有记录都包含到结果集中，而左边表中有匹配的记录才包含进结果集，

SELECT  *
FROM  t1
RIGHT OUTER JOIN t2
ON  t1.职工号 = t2.职工号

右外连接时，会将右边表的所有记录都包含到查询结果中，这时，那些没有匹配的右边表的记录会与全部是NULL值的记录连接。
（5） 全外连接

SELECT  *
FROM  t1
FULL OUTER JOIN t2
ON  t1.职工号=t2.职工号

（6） 交叉连接查询
交叉连接其实就是前面介绍的无连接规则的连接，这种连接有两种表示方法。
1．用逗号隔开表名
 

SELECT  *
FROM   t1,t2

2．用CROSS JOIN关键字连接表名
 

SELECT  *
FROM  t1  CROSS JOIN  t2

上面的两种SELECT语句完全等同，交叉连接的返回结果是一个笛卡尔积，即两个表中的每一行都互相连接。
【例7】请使用交叉连接的方法得到下表所示的课程表（KCB）。


（1）建立两个临时数据表A和B。

                                           数据表A（左侧）和B（右侧）的内容
（2）对A、B两个表进行交叉连接操作，并将结果保存到KCB表，其语句如下所示。

SELECT  *
INTO   kcb
FROM  a  CROSS JOIN  b

（7） 连接查询中使用聚合函数
 聚合函数不仅可以用于单表查询中，还可以用在多表连接查询中。
【例8】统计没有考过任何考试的学生人数。
分析：stu_info表中存放的是所有学生的记录，score表中存放的是考过试的学生的成绩。要完成本例的要求，则应当用stu_info表左外连接score表，这样stu_info表中没有考过任何考试的学生就与全部是NULL值的记录连接，而后统计score表部分“sno”（学号）为NULL值的记录个数就能得到没有考过任何考试的学生人数。

SELECT  st.sno,st.sname,s.sno,s.cno,s.exam
FROM   stu_info AS st
        LEFT OUTER JOIN score AS s
        ON st.sno=s.sno
ORDER BY  s.sno

SELECT  COUNT(*) AS 没有考任何考试的人数
FROM   stu_info AS st
        LEFT OUTER JOIN score AS s
        ON st.sno=s.sno
WHERE  s.sno IS NULL

（8） 高级连接查询实例
【例9】仍然使用在【例4】中使用的图书信息表和出版社信息表，完成下列高级连接查询的练习。
（1）查询出图书作者相同、出版社不同的图书信息。
（2）使用左连接查询出图书名称和出版社名称。
（3）使用右连接查询出图书名称和出版社名称。
（4）统计出每个出版社出版的图书数量。
【解析】
（1）图书作者信息都是在图书信息表中的，所以要使用自连接查询。

SELECT A.BOOKNAME,C.PUBLISHERNAME,A.BOOKAUTHOR
FROM BOOKINFO A,BOOKINFO B,PUBLISHERINFO C
WHERE  A.BOOKAUTHOR = B.BOOKAUTHOR
      AND A.BOOKPUBLISHERID <> B.BOOKPUBLISHERID
      AND A.BOOKPUBLISHERID = C.PUBLISHERID

（2）通过左连接得到的查询结果是左表中的全部内容加上两张表中满足条件的内容。这里，使用图书信息表为左表，出版社信息表为右表。为了能够看出查询效果，在图书信息表中添加一个出版社信息表中没有的记录。

SELECT A.BOOKNAME,B.PUBLISHERNAME
FROM BOOKINFO A LEFT JOIN PUBLISHERINFO B
ON A.BOOKPUBLISHERID = B.PUBLISHERID

（3）通过右连接查询出来的记录是右表中的全部记录加上两张表中满足条件的内容。为了能够体现出查询效果，在出版社信息表中添加一条没有被图书信息表引用的出版社信息。

SELECT A.BOOKNAME,B.PUBLISHERNAME
FROM BOOKINFO A RIGHT JOIN PUBLISHERINFO B
ON A.BOOKPUBLISHERID = B.PUBLISHERID

（4）统计图书的数量要使用聚合函数COUNT。

SELECT COUNT(A.BOOKPUBLISHERID),B.PUBLISHERNAME
FROM BOOKINFO A , PUBLISHERINFO B
WHERE A.BOOKPUBLISHERID=B.PUBLISHERID
GROUP BY B.PUBLISHERNAME

3、 组合查询
使用UNION关键字将多个SELECT语句组合起来，将多个SELECT语句的查询结果显示到一个结果集中。组合查询与连接查询不同的是，前者将多个表的查询结果，竖着组合，而后者是将查询结果横着连接

组合查询与连接查询区别示意图
（1） 使用组合查询:使用UNION关键字将多个SELECT查询语句组合起来查询，以一个查询结果集的形式显示出来。

SELECT语句1
UNION
SELECT语句2
UNION
SELECT语句3
……
UNION
SELECT语句n

【例9】从stu_info表中查询所属院系为“计算机系”，或者年龄大于30岁的学生的信息。

SELECT  *
FROM   stu_info
WHERE  depart='计算机系'
UNION
SELECT  *
FROM   stu_info
WHERE  DATEDIFF(year,birth,GETDATE())>30

实际上，上面的示例也可以使用OR运算符完成查询任务。

SELECT   *
FROM    stu_info
WHERE   depart='计算机系'
OR      DATEDIFF(year,birth,GETDATE())>30

虽然使用OR运算符可以达到使用UNION运算符的效果，但是，使用UNION和使用OR得到的结果还是有一点差别，就是UNION会将结果集中相同的记录自动去掉，而OR则保留相同记录。
说明：使用UNION时，如果希望不删除重复值，则可以在UNION后加上ALL关键字。例如，下面的语句不删除重复值记录。
 

SELECT  ……
……
UNION ALL
SELECT  ……

（2） 使用UNION的规则
使用UNION组合查询语句时，应当注意两条最重要的规则。
1．每个单独的SELECT查询语句应当有相同数量的字段,如果不同则会出现错误。

SELECT  sno,sname
FROM   stu_info
WHERE  sex='男'
UNION
SELECT  sno,sname,birth
FROM   stu_info
WHERE  depart='计算机系'

运行结果如下所示。
消息205，级别16，状态1，第1行

注意：使用UNION、INTERSECT或EXCEPT运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
技巧：当独立查询语句的字段个数不同时，可以在字段个数不够的地方使用常量补位。例如，在上面的第一个SELECT子句中补上一个NULL值，就可以避免错误。

SELECT  sno,sname,null
FROM   stu_info
WHERE  sex='男'
UNION
SELECT  sno,sname, birth
FROM   stu_info
WHERE  depart='计算机系'

2．每个查询语句中相应的字段的类型必须相互兼容

SELECT  sno,sname, depart
FROM   stu_info
WHERE  sex='男'
UNION
SELECT  sno,sname,birth
FROM   stu_info
WHERE  depart ='计算机系'

运行结果如下所示。
消息 241，级别 16，状态 1，第 1 行

从字符串转换日期和/或时间时，转换失败。
其中，错误的原因是，第一个SELECT语句中的“depart”（所属院系）字段的类型为字符型，而第二个SELECT语句中相应的字段“birth”（出生日期）为日期型字段。

技巧：当相应位置的字段类型不同时，可以使用类型转换函数强制转换字段类型。
（3） 使用UNION得到复杂的统计汇总样式
联合UNION、GROUP BY和聚合函数三者会得到具有很棒的统计汇总样式的查询结果，这就是OR所不能替代的一个例子。例如，下面的语句会得到一个具有复杂统计汇总样式的查询结果集。

SELECT sno,cno,exam
FROM  score
UNION
SELECT sno,'总分：',SUM(exam)
FROM  score
GROUP BY sno
UNION
SELECT sno,'平均分：',AVG(exam)
FROM  score
GROUP BY sno

（4） 排序组合查询的结果
虽然组合查询中可以有多个单独的SELECT语句，而且每个独立的SELECT语句又都可以拥有自己的WHERE子句、GROUP BY子句和HAVING子句，但是，整个语句中却只能出现一个ORDER BY子句，而且它的位置必须在整个语句的末尾，就是说只能对组合查询最后的结果进行排序，而并不能只对某个单独的SELECT语句的结果进行排序。
【例10】从stu_info表中查询所属院系为“计算机系”或者年龄大于30岁的学生的信息，并按照出生日期进行升序排序。
 

SELECT  *
FROM   stu_info
WHERE  depart = '计算机系'
UNION
SELECT  *
FROM   stu_info
WHERE  DATEDIFF(year,birth,GETDATE())>30
ORDER BY birth

因为组合查询结果集的字段名列表是根据第一个SELECT子句的字段名列表而定的，所以在使用ORDER BY时，应当注意这一点。组合查询其实存在一个很有意思的排序问题。当没有ORDER BY子句时，查询结果会根据第一个SELECT子句中字段名列表升序排序。
（5) 组合查询的实例
【例11】仍然使用【例4】中创建的图书信息表和出版社信息表，完成下列查询。
（1）使用UNION关键字查询图书信息表中的图书名称和出版社信息表中的出版社名称。
（2）查询出图书名称是会计类的或者图书价格大于30的图书，并按图书价格排序。
【解析】
（1）使用UNION进行组合查询，要求使用UNION联合的几个查询语句（SELECT语句）中，查询的字段个数要一致，字段类型要兼容。查询语句如下：

SELECT BOOKNAME FROM BOOKINFO
UNION
SELECT PUBLISHERNAME FROM PUBLISHERINFO

（2）使用组合查询排序查询结果，查询语句如下所示：

SELECT * FROM BOOKINFO WHERE BOOKNAME LIKE '%会计%'
UNION
SELECT * FROM BOOKINFO WHERE BOOKPRICE>30
ORDER BY BOOKPRICE

4、 子查询
嵌入另一个SELECT语句中的SELECT语句被称为子查询。目前，子查询能完成的工作，通过表连接几乎也都可以完成，而在过去，因为内连接的运行效率比较差，外连接又不能使用，所以子查询被运用得非常广。但是，近些年来由于对SQL Server的优化，使得内连接的运行效率明显高于子查询，而外连接也被开发了出来，所以用户开始丢掉那些比较难理解的子查询语句，而改用相对容易理解的表连接查询语句。
(1) 使用返回单值的子查询
如果子查询返回单值，则可以使用关系运算符，例如，等于（=）、不等于（<>）等，将其与主查询结合起来。
【例12】查询所有选修“心理学”并有考试成绩的学生的考试成绩，并按降序排序考试成绩。
分析：考试成绩在score表中，而该表中没有课名只有课号，所以首先必须从course表中查询“心理学”的课号，然后再从score表中根据查到的课号查询考试成绩。下面列出使用子查询完成查询任务的语句。

SELECT  sno,exam
FROM   score
WHERE  cno=(SELECT cno
          FROM course
          WHERE cname='心理学')
ORDER BY exam DESC

下面通过分析SQL Server在内部执行子查询的步骤，让读者更好地掌握编写子查询的方法。
1 处理子查询，得到“心理学”的课号。
 

SELECT cno
FROM  course
WHERE cname='心理学'

2 将子查询的查询结果，即课号“002”，放到主查询中。

SELECT  sno,exam
FROM   score
WHERE  cno=’002’
ORDER BY exam DESC

3 执行结合好的主查询，得出最后的运行结果。
实际上，本例也可以通过内连接查询语句完成任务。

SELECT s.sno ,s.exam
FROM  score AS s,course AS c
WHERE  c.cname='心理学'
AND   s.cno=c.cno
ORDER BY s.exam DESC

或者： 

SELECT s.sno ,s.exam
FROM  score AS s
INNER JOIN  course AS c
ON s.cno = c.cno
WHERE  c.cname = '心理学'
ORDER BY s.exam DESC

(2) 子查询与聚合函数的配合使用
【例13】查询出生日期最小的学生的所有信息。

SELECT  *
FROM  stu_info
WHERE  birth=(SELECT MIN(birth)
              FROM stu_info)

(3) 子查询的实例
【例14】仍然使用【例4】中创建的图书信息表和出版社信息表，完成下列查询。
（1）使用子查询，查询出“北京大学出版社”的图书信息。
（2）使用子查询，查询出价格最低的图书的出版社名称。
【解析】
1 首先需要查询出北京大学出版社的出版社编号，然后根据出版社编号查询出图书的信息。查询语句如下所示：

SELECT * FROM BOOKINFO
WHERE BOOKPUBLISHERID=
    (SELECT PUBLISHERID  FROM PUBLISHERINFO
    WHERE PUBLISHERNAME='北京大学出版社')

2 首先需要查询出价格最低的图书的出版社编号，然后根据出版社编号查询出出版社名称。查询语句如下所示：

SELECT B.PUBLISHERNAME FROM BOOKINFO A,PUBLISHERINFO B
WHERE  BOOKPRICE=(SELECT MIN(BOOKPRICE) FROM BOOKINFO)
      AND A.BOOKPUBLISHERID=B.PUBLISHERID

5、 在SSMS查询设计器中设计查询
   当使用“查询设计器”设计完查询后，就会得到一个自动生成的SELECT语句。
   但不应该太依赖“查询设计器”设计查询，而忽视手动编写查询语句，因为编写SELECT语句是开发人员的必备能力。
6、 综合练习
1．查询“信息基础”课程的考试成绩大于等于90分的学生的学号、姓名、系别和考试成绩，并按考试成绩降序排序。
分析：课程名称在course表中，成绩在score表中，而姓名、系别在stu_info表中，因此要想得到本例要求的结果，则必须对course、score和stu_info三个表进行连接查询。

SELECT  st.学号, st.姓名, st.所属院系, s.考试成绩
FROM   score AS s,
course AS c,
stu_info AS st
WHERE  c.课名 = '信息基础'
AND  s.考试成绩 >= 90
AND  s.课号 = c.课号
AND  st.学号 = s.学号
ORDER BY s.考试成绩 DESC

2．从stu_info表中，查询与学生“马六”相同院系的所有学生的学号、姓名和联系方式。

SELECT  st1.sno,st1.sname,st1.telephone
FROM  stu_info AS st1,stu_info AS st2
WHERE  st2.sname = '马六'
AND   st1.depart = st2.depart