T-SQL复习06--高级查询

• UNION(ALL)
• 联接查询
  • 联接查询需要满足的条件
  • 连接查询分类
• INNER JOIN内连接
• 外连接(LEFT/RIGHT/FULL JOIN)
  • 左外连接
  • 右外连接
  • 全外连接
  • 交叉连接(很少用)
• 聚合函数
  • SUM
    • SUM(ALL|DISTINCT 列名)
    • SUM(列名) OVER(PARTITION BY 列名 ORDER BY 列名)
  • AVG
    • AVG(ALL|DISTINCT 列名)
    • AVG(列名) OVER(PARTITION BY 列名 ORDER BY 列名)
  • COUNT
    • COUNT(ALL|DISTINCT 列名)
    • COUNT(列名) OVER(PARTITION BY 列名 ORDER BY 列名)
    • COUNT(*)返回表中的行数，包括空值和重复项
  • MIN
    • MIN(列名)
    • MIN(列名) OVER(PARTITION BY 列名 ORDER BY 列名)
  • MAX
    • MAX(列名)
    • MAX(列名) OVER(PARTITION BY 列名 ORDER BY 列名)
  • GROUP BY
  • HAVING
    • WHERE和HAVING区别
  • ROLLUP、CUBE
  • 子查询
    • 使用IN或NOT IN的子查询
    • 使用ANY、SOME或ALL的子查询
    • 使用EXISTS或NOT EXISTS的子查询
    • 在UPDATE、DELETE和INSERT中使用子查询
    • 使用嵌套查询返回特定排名的数据
    • 不用聚合函数求最大值

UNION(ALL)

将两个或更多个查询结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行

使用UNION合并两个查询结果集的基本准则：

1. 所有查询中的列数和列的顺序必须相同
2. 数据类型必须兼容

ALL将全部行并入结果中，其中包括重复行。如果没有ALL则删除重复行

示例：

-- 使用UNION合并两个学生表的学生信息
NAME,SEX,AGE FROM dbo.STUDENT
UNION (ALL)
SELECT ID,NAME,SEX,AGE FROM dbo.STUDENT02

-- 将SELECT INTO与UNION一起使用
SELECT NAME,AGE,SEX 
INTO STUDENT_UNION 
FROM STUDENT 
UNION ALL 
SELECT NAME,AGE,SEX FROM dbo.STUDENT02

联接查询

根据数据库表之间的逻辑关系，从两个或多个数据库表中检索数据

联接查询需要满足的条件

所要连接的表必须拥有相同的字段值，且数据类型类似。尽量在表的主键上的基础上，指定连接条件

连接查询分类

1. 内部链接：基本内连接，多表连接
2. 外部连接：左外连接，右外连接，全外连接
3. 交叉连接

INNER JOIN内连接

只返回两个表中连接字段相等的行，用...INNER JOIN...ON...语句进行连接，JOIN指定需要连接的数据表，ON指定连接条件(INNER可省略)

基本语法：

SELECT 字段1,字段2,...
FROM 表1
INNER JOIN 表2 ON 关联条件
INNER JOIN 表3 ON 关联条件
...
WHERE 筛选条件
GO

示例：

-- 查询学生信息及成绩
SELECT ST.NAME,ST.SEX,GR.SCORE FROM dbo.STUDENT ST INNER JOIN dbo.GRADE GR ON ST.ID=GR.STUDENTID

-- 查询学生信息、成绩及课程名称
SELECT ST.NAME,ST.SEX,CO.NAME,GR.SCORE FROM dbo.STUDENT ST 
INNER JOIN dbo.GRADE GR ON ST.ID=GR.STUDENTID
INNER JOIN dbo.COURSE CO ON  GR.COURSEID=CO.ID

外连接(LEFT/RIGHT/FULL JOIN)

基本语法：

USE 数据库名
SELECT 字段1,字段2,...
FROM 表1 LEFT OUTER JOIN 表2  --左外连接 OUTER通常省略
        RIGHT OUTER JOIN 表2  --右外连接 OUTER通常省略
        FULL OUTER JOIN 表2   --全连接 OUTER通常省略
ON 表1.字段=表2.字段
GO

左外连接

LEFT OUTER JOIN以左表为基表，结果集返回左表的所有行，而右表只返回符合连接条件的记录，右表中不足的地方显示为NULL

示例：

-- 查询所有学生信息及参加考试的学生成绩
SELECT ST.NAME,ST.SEX,ST.AGE,GR.SCORE FROM dbo.STUDENT ST LEFT OUTER JOIN dbo.GRADE GR ON ST.ID=GR.STUDENTID

右外连接

RIGHT OUTER JOIN以右表为基表，结果集返回右表的所有行，而左表只返回符合连接条件的记录，左表中不足的地方显示为NULL

示例：

-- 安徽查询所有课程信息及参加该考试的学生成绩
SELECT CO.ID,CO.NAME,GR.SCORE FROM dbo.GRADE GR RIGHT OUTER JOIN dbo.COURSE CO ON GR.COURSEID=CO.ID

全外连接

FULL OUTER JOIN结果集返回左表和右表的所有行

示例：

-- 查找素有的学生信息及所有参加该考试的学生成绩
SELECT ST.NAME,ST.SEX,ST.AGE,GR.SCORE FROM dbo.STUDENT ST FULL OUTER JOIN dbo.GRADE GR ON ST.ID=GR.STUDENTID

交叉连接(很少用)

CROSS JOIN将从被连接的表中返回所有可能的记录组合(如：一个表8条记录，另一个表9条记录，结果将返回72条记录)，交叉连接的结果集是量表行数乘积的集合，也被称为笛卡尔积

基本语法：

USE 数据库名
SELECT 表1.字段,表2.字段,...
FROM 表1 CROSS JOIN 表2
GO

注：交叉连接不需要使用ON连接条件

示例：

SELECT ST.NAME,ST.AGE,ST.SEX,GR.SCORE FROM dbo.STUDENT ST CROSS JOIN dbo.GRADE GR

聚合函数

1. SUM()：计算表达式中所有值得和，忽略空值，这些表达式通常是字段名或包含字段名的表达式
2. AVG()：计算表达式的平均值，忽略空值，这些表达式通常是字段名或包含字段名的表达式
3. COUNT(列名)：计算表达式中非空值的数量，即忽略空值
4. COUNT(*)：计算表达式中所有值的数量，包括空值
5. MIN()：返回表达式中所有值的最小值，这些表达式通常是字段名或包含字段名的表达式
6. MAX()：返回表达式中所有值的最大值，这些表达式通常是字段名或包含字段名的表达式

SUM

示例：

SELECT SUM(SCORE) FROM dbo.GRADE

SUM(ALL|DISTINCT 列名)

返回表达式中所有值的和或仅非重复值的和。SUM只能用于数字列，将忽略NULL值。

• ALL表示所有值求和，默认是ALL
• DISTINCT表示对唯一值求和

SUM(列名) OVER(PARTITION BY 列名 ORDER BY 列名)

在SUM后面使用OVER子句可对表数据分区再求和，PARTITION BY指定分区列，ORDER BY指定在分区内按照某列的排序顺序在求和

示例：

-- 对课程ID分区后对学生分数求和
SELECT STUDENTID,COURSEID,SCORE,SUM(SCORE) OVER(PARTITION BY COURSEID) AS SUM_PAR FROM dbo.GRADE

-- 按照学生编号排序对学生分数求和
SELECT STUDENTID,COURSEID,SCORE,SUM(SCORE) OVER(ORDER BY STUDENTID) SUM_ORDER FROM dbo.GRADE ORDER BY STUDENTID

-- 对课程分区并按照学生编号排序后对学生分数求和
SELECT STUDENTID,COURSEID,SCORE,SUM(SCORE) OVER(PARTITION BY COURSEID ORDER BY STUDENTID) SUM_PAR_ORDER FROM dbo.GRADE

AVG

示例：

SELECT AVG(SCORE) FROM dbo.GRADE

AVG(ALL|DISTINCT 列名)

返回组中各值得平均值，空值将被忽略，后面可以跟随OVER子句

• ALL表示对所有值求平均值，默认是ALL
• DISTINCT表示对唯一值求平均值

AVG(列名) OVER(PARTITION BY 列名 ORDER BY 列名)

在AVG使用OVER子句可对表数据分区再求平均值，PARTITION BY指定分区列，ORDER BY指定在分区内按照某列排序再求平均值

示例：

-- 对课程ID分区后对学生分数求平均值
SELECT STUDENTID,COURSEID,SCORE,AVG(SCORE) OVER(PARTITION BY COURSEID) AVG_PAR FROM dbo.GRADE

-- 按照学生编号排序对学生分数求平均值
SELECT STUDENTID,COURSEID,SCORE,AVG(SCORE) OVER(ORDER BY STUDENTID) AVG_ORDER FROM dbo.GRADE

-- 对课程分区并按照学生编号排序后对学生分数求平均值
SELECT STUDENTID,COURSEID,SCORE,AVG(SCORE) OVER(PARTITION BY COURSEID ORDER BY STUDENTID) AVG_PAR_ORDER FROM dbo.GRADE

COUNT

示例：

SELECT COUNT(NAME) FROM dbo.STUDENT

COUNT(ALL|DISTINCT 列名)

返回组中的项数，不包含空值

• ALL对所有值计数，不包括空值，默认是ALL
• DISTINCT计算唯一非空值数量

COUNT(列名) OVER(PARTITION BY 列名 ORDER BY 列名)

在COUNT后使用OVER子句可对表数据分区后计数，PARTITION BY指定分区列，ORDER BY指定在分区内按照某列排序再计数

示例：

SELECT NAME,SEX,AGE,COUNT(NAME) OVER(PARTITION BY SEX) COUNT_PAR FROM dbo.STUDENT
SELECT NAME,SEX,AGE,COUNT(NAME) OVER(ORDER BY AGE) COUNT_ORDER FROM dbo.STUDENT
SELECT NAME,SEX,AGE,COUNT(NAME) OVER(PARTITION BY SEX ORDER BY AGE) FROM dbo.STUDENT

COUNT(*)返回表中的行数，包括空值和重复项

SELECT COUNT(*) FROM dbo.STUDENT
SELECT COUNT(1) FROM dbo.STUDENT

MIN

SELECT MIN(AGE) FROM dbo.STUDENT

MIN(列名)

返回族中最小的值，忽略空值

MIN(列名) OVER(PARTITION BY 列名 ORDER BY 列名)

在MIN后使用OVER子句可对表数据分区后求最小值，PARTITION BY指定分区列，ORDER BY指定在分区内按照某列排序在求最小值

示例：

SELECT NAME,SEX,AGE,MIN(AGE) OVER(PARTITION BY SEX) MIN_PAR FROM dbo.STUDENT
SELECT NAME,SEX,AGE,MIN(AGE) OVER(ORDER BY AGE) MIN_ORDER FROM dbo.STUDENT
SELECT NAME,SEX,AGE,MIN(AGE) OVER(PARTITION BY SEX ORDER BY AGE) MIN_PAR_ORDER FROM dbo.STUDENT

MAX

SELECT MAX(AGE) FROM dbo.STUDENT

MAX(列名)

返回族中最大的值，忽略空值

MAX(列名) OVER(PARTITION BY 列名 ORDER BY 列名)

在MAX后使用OVER子句可对表数据分区后求最大值，PARTITION BY指定分区列，ORDER BY指定在分区内按照某列排序在求最小值

示例：

SELECT NAME,SEX,AGE,MAX(AGE) OVER(PARTITION BY SEX) MAX_PAR FROM dbo.STUDENT
SELECT NAME,SEX,AGE,MAX(AGE) OVER(ORDER BY AGE) MAX_ORDER FROM dbo.STUDENT
SELECT NAME,SEX,AGE,MAX(AGE) OVER(PARTITION BY SEX ORDER BY AGE) MAX_PAR_ORDER FROM dbo.STUDENT

GROUP BY

GROUP BY：对结果集分组，针对每一组返回一行

若SELECT子句中同时包含聚合函数和GROUP BY，则聚合函数对每个组聚合计算

若指定GROUP BT时，GROUP BY后的所有字段都要出现在SELECT列表中

GROUP BY后不能使用数据类型为text、ntext、image的字段

示例：

-- 简单GROUP BY示例
SELECT SEX FROM dbo.STUDENT GROUP BY SEX

-- 聚合函数中使用GROUP BY
SELECT SEX,AVG(AGE) 平均年龄 FROM dbo.STUDENT GROUP BY SEX ORDER BY 平均年龄

HAVING

使用HAVING过滤分组后的数据，因此HAVING要用在GROUP BY之后

示例：

-- 按照性别分区，求学生平均年龄
SELECT SEX,AVG(AGE) 平均年龄 FROM dbo.STUDENT GROUP BY SEX

-- 按照性别分区，求学生平均年龄大于12岁的学生性别
SELECT SEX,AVG(AGE) 平均年龄 FROM dbo.STUDENT GROUP BY SEX HAVING AVG(AGE)>12

WHERE和HAVING区别

1. WHERE语句在GROUP BY语句之前，SQL会在分组之前计算WHERE语句
2. HAVING语句在GROUP BY语句之后，SQL会在分组之后计算HAVING语句
3. 在一个SQL语句中可以同时有WHERE子句和HAVING子句，HAVING与WHERE子句类似，均用于设置限定条件
4. WHERE子句的作用时在对查询结果进行分组前，将不符合WHERE条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数，使用WHERE条件显示特定的行
5. HAVING子句的作用时筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用HAVING条件显示特定的组，也可以使用多个分组标准进行分组

示例：

-- 查询年龄大于11岁各性别学生平均年龄大于13岁的学生信息
SELECT SEX,AVG(AGE) 平均年龄 FROM dbo.STUDENT WHERE AGE>11 GROUP BY SEX HAVING AVG(AGE)>13

ROLLUP、CUBE

在GROUP BY后使用ROLLUP、CUBE子句对分组数据汇总

ROLLUP就是将GROUP BY后面的第一个字段的各个值分组在聚合运算，而其他列并不要求，其中ROLLUP包含仅使用GROUP BY的结果集

而CUBE则会将GROUP BY后面的每一个字段的各个值分组在聚合运算，其中CUBE结果集包含ROLLUP结果集

注：ROLLUP、CUBE在后续SQL SERVER版中将删除

示例：

SELECT SEX,NAME,AVG(AGE) FROM dbo.STUDENT GROUP BY SEX,NAME WITH ROLLUP ORDER BY SEX,NAME
SELECT SEX,NAME,AVG(AGE) FROM dbo.STUDENT GROUP BY SEX,NAME WITH CUBE ORDER BY SEX,NAME

子查询

在WHERE子句中包含的SELECT查询，就成为子查询或嵌套查询。而包含子查询的语句称为外部查询或外部选择。子查询可以嵌套在外部SELECT、INSERT、UPDATE或DELETE语句的WHERE或HAVING子句内，也可以嵌套在其他子查询内。

子查询类型：

1. 使用IN或NOT IN的子查询(用的比较多)
2. 使用ANY、SOME或ALL的子查询
3. 使用EXISTS或NOT EXISTS的子查询(用的比较多)

注：子查询通常用()括起来

使用IN或NOT IN的子查询

示例：

-- 使用IN的子查询
SELECT NAME,SEX,AGE FROM dbo.STUDENT WHERE ID IN (SELECT STUDENTID FROM dbo.GRADE WHERE COURSEID='k01' AND SCORE>60)

-- 使用NOT IN的子查询
SELECT NAME,SEX,AGE FROM dbo.STUDENT WHERE ID NOT IN (SELECT STUDENTID FROM dbo.GRADE WHERE COURSEID='k01' AND SCORE>60)

-- 使用联接查询
SELECT ST.NAME,ST.SEX,ST.AGE FROM dbo.STUDENT ST INNER JOIN dbo.GRADE GR ON ST.ID=GR.STUDENTID WHERE GR.COURSEID='k01' AND GR.SCORE>60

联接总是可以表示为子查询，子查询经常(但不总是)可以表示为联接。这是因为联接时对称的，无论以何种顺序联接表A或B，都将得到相同的结果

使用联接而不是用子查询的一个同之处在于，联接使您可以在结果中显示多个表中的列

使用ANY、SOME或ALL的子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。ANY与SOME完全等效

ANY任一个：

• >ANY：表示至少大于一个值，即大于最小值。因此>ANY(1,2,3)表示大于1
• <ANY：表示至少小于一个值，即小于最大值。因此<ANY(1,2,3)表示小于3
• =ANY：表示至少等于一个值，因此=ANY(1,2,3)表示等于1或等于2或等于3，=ANY等效于IN

ALL所有：

• >ALL：表示大于每一个值，即大于最大值。因此>ALL(1,2,3)表示大于3
• <ALL：表示小于每一个值，即小于最小值。因此<ALL(1,2,3)表示小于1

示例：

-- 使用ANY的子查询
SELECT * FROM dbo.STUDENT WHERE ID>ANY(SELECT STUDENTID FROM dbo.GRADE WHERE SCORE>80)

-- 使用ALL的子查询
SELECT * FROM dbo.STUDENT WHERE ID>ALL(SELECT STUDENTID FROM dbo.GRADE WHERE SCORE>80)

使用EXISTS或NOT EXISTS的子查询

使用EXISTS的子查询，只进行存在测试，外部查询的WHERE子句测试子查询返回的行是否存在，子查询实际上不产生任何数据，它时返回TRUE或FALSE值

使用EXISTS引入的子查询的语法：

WHERE [NOT] EXISTS (subquery)

• EXISTS关键字前面没有列名、常量或其他表达式
• 由EXISTS引入的子查询的选择列通常几乎都是由星号(*)组成，由于这是测试是否存在符合子查询指定条件的行，因此不必列出列名

示例：

-- 使用EXISTS的子查询
SELECT * FROM dbo.STUDENT WHERE [NOT] EXISTS (SELECT * FROM dbo.GRADE WHERE dbo.STUDENT.ID=dbo.GRADE.STUDENTID AND COURSEID='k02')

-- 使用IN替换的子查询
SELECT * FROM dbo.STUDENT WHERE ID IN (SELECT STUDENTID FROM dbo.GRADE WHERE COURSEID='k01')

-- 使用联接查询替换
SELECT ST.* FROM dbo.STUDENT ST INNER JOIN dbo.GRADE GR ON ST.ID=GR.STUDENTID WHERE GR.COURSEID='k01'

在UPDATE、DELETE和INSERT中使用子查询

示例：

-- 在DELETE语句中使用NOT IN子查询
DELETE FROM dbo.GRADE WHERE STUDENTID NOT IN (SELECT ID FROM dbo.STUDENT)

-- 在DELETE语句中使用NOT EXISTS子查询
DELETE FROM dbo.GRADE WHERE NOT EXISTS(SELECT * FROM dbo.STUDENT WHERE STUDENTID=dbo.GRADE.STUDENTID)

-- 在UPDATE语句中使用IN子查询
UPDATE dbo.GRADE SET SCORE=SCORE+5 WHERE STUDENTID IN (SELECT ID FROM dbo.STUDENT WHERE SEX='女')

-- 使用联接的等效UPDATE语句
UPDATE dbo.GRADE SET SCORE=SCORE+5 FROM dbo.GRADE INNER JOIN dbo.STUDENT ON dbo.GRADE.STUDENTID=dbo.STUDENT.ID WHERE dbo.STUDENT.SEX='女'

使用嵌套查询返回特定排名的数据

示例：

-- 查询成绩表中排名名词在中间的信息和成绩
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY SCORE) 排名,* FROM dbo.GRADE WHERE COURSEID='k01') A INNER JOIN dbo.STUDENT B ON A.STUDENTID=B.ID WHERE A.排名=4

不用聚合函数求最大值

示例：

-- 使用聚合函数求学生最大年龄
SELECT MAX(AGE) FROM dbo.STUDENT

-- 不适用聚合函数求学生最大年龄
SELECT * FROM dbo.STUDENT A WHERE A.AGE NOT IN 
(
SELECT B.AGE FROM dbo.STUDENT B INNER JOIN dbo.STUDENT c ON B.AGE<C.AGE
)