CH07_高级查询与T-SQL

本章目标

  • 掌握简单子查询的用法
  • 掌握IN子查询的用法
  • 掌握EXISTS子查询的用法
  • 应用SQL进行综合查询
  • 掌握T-SQL 常用脚本

子查询

什么是子查询？

​ SQL Server中的子查询（Subquery）是一种嵌套在其他SQL查询中的查询。它可以出现在SELECT、FROM、WHERE、GROUP BY、HAVING以及ORDER BY等所有位置。子查询可以看作是主查询的一个组件，用于提供数据或作为查询的条件。

​ 子查询可以分为相关子查询和非相关子查询。相关子查询的执行依赖于外部查询的数据，每当外部查询返回一行，子查询就会执行一次。而非相关子查询则独立于外部查询，它总共执行一次，执行完毕后将结果传递给外部查询。

​ 此外，根据返回的结果集，子查询还可以分为单值子查询、多值子查询等。单值子查询只返回一行一列的数据，通常用在比较操作符（如=、!=、<、<=、>、>=）之后。而多值子查询可以返回多行数据，通常与IN运算符一起使用。

​ 在SQL Server中，子查询可以嵌套在其他子查询中，但嵌套层数最多为32层。子查询也可以作为数据源使用，嵌套在SQL语句的FROM子句之后，或者与其他查询通过JOIN子句连接。

​ 子查询在SQL Server中是一种非常有用的功能，它可以帮助我们将复杂的查询分解为一系列的逻辑步骤，提高查询的灵活性和效率。然而，使用子查询时也需要注意一些规则，如子查询必须括在圆括号中，SELECT列表中不能包含对特定类型值的引用等。

子查询语法

1
2
SELECT column1, column2, (SELECT aggregate_function(column) FROM table) AS alias  
FROM table;
  • 案例：查询当天考试的考生信息
1
SELECT * FROM Student WHERE StudentNo = (SELECT StudentNo FROM Result WHERE ExamDate=CONVERT(varchar(10), GETDATE(), 120))

1708396033925

IN子查询

  • 案例：查询2013-01-22号当天考试的所有考生信息
1
SELECT * FROM Student WHERE StudentNo IN (SELECT StudentNo FROM Result WHERE ExamDate='2013-01-22')；

1708395635180

EXISTS子查询

EXISTS 子查询在 SQL 中用于检查子查询是否至少返回一个记录。如果子查询返回至少一个记录，则 EXISTS 条件为真，否则为假。EXISTS 子查询通常与 WHERE 子句一起使用，以根据子查询的结果过滤外部查询的记录。

  • 案例1：查询参加考试的学生信息
1
2
3
4
5
6
7
8
9
SELECT *
FROM Student a
WHERE EXISTS (  
    SELECT 1  
    FROM Result b 
    WHERE b.StudentNo = a.StudentNo
);
--验证结果SQL语句
SELECT DISTINCT StudentNo FROM Result

1708397862603

  • 案例2：查询未参加考试的学生信息
1
2
3
4
5
6
7
SELECT *
FROM Student a
WHERE NOT EXISTS (  
    SELECT 1  
    FROM Result b 
    WHERE b.StudentNo = a.StudentNo
);

1708397999849

优化性能

当使用 EXISTS 时，通常比使用 IN 更为高效，尤其是当子查询返回大量结果时。这是因为 EXISTS 一旦找到匹配的行就会停止搜索，而 IN 会检查所有返回的行。

注意事项

  • EXISTS 子查询通常只关心是否存在至少一个匹配的行，而不关心具体有多少行匹配。因此，子查询中的 SELECT 语句通常使用 1 或其他常量，而不是实际的列名。
  • 子查询中的列必须与外部查询中的列进行匹配，以便正确评估 EXISTS 条件。
  • EXISTS 和 NOT EXISTS 通常用于关联查询，以根据相关联的表中的数据来过滤结果。

在设计查询时，理解 EXISTS 和 NOT EXISTS 的行为以及它们如何影响查询性能是非常重要的。在复杂的查询中，使用这些子查询可以有效地减少需要处理的数据量，从而提高查询效率

变量

变量定义

1
declare @var_name data_type

变量赋值

1
2
3
4
--set赋值： 
set @var_num = value --不可多赋值
--select赋值：
select @var_name = value... from table_name where 唯一条件 --可多赋值

全局变量

名称 释义
@@error 最后一个Sql错误的错误号
@@identity 最后一次插入的标示值
@@language 当前使用的语言的名称
@@max_connections 可以创建的同时连接的最大数目
@@rowcount 受上一个Sql语句影响的行数
@@servername 本地服务器的名称
@@transcount 当前连接打开的事物数
@@version SqlServer的版本信息

案例

全局变量：

1
2
3
4
select 
	@@SERVERNAME 服务器名,
	@@LANGUAGE 语言,
	@@VERSION 版本

用户变量：

1
2
3
4
5
6
7
declare @userName varchar(20)
declare @age int

set @userName='张三'
set @age=18

select @userName 姓名,@age 年龄

选择结构

IF

1
2
3
4
5
6
7
8
9
10
11
12
declare @num int

set @num=18

if @num%2=0
begin
	print '偶数'
end
else
begin
	print '奇数'
end

IIF

1
2
3
4
5
declare @num int

set @num=10

SELECT IIF(@num%2=0,'偶数','奇数') 结果

CASE

1
2
3
4
5
6
7
select 
	case
		when StudentResult<60 then '不及格'
		when StudentResult<80 then '及格'
		else '优秀'
	end 级别
from Result

循环结构

WHILE

1
2
3
4
5
6
7
8
9
declare @cnt int

set @cnt=0

while @cnt<10
begin 
	print 'hello'+convert(varchar(20),@cnt)
	set @cnt+=1
end

课后作业

1.查询设计基础班和前端中级班学员人数

2.查询考试分数超过总平均分

3.删除最低分数考试信息

4.查询考试分数大于任何学员的学员信息

5.查询统计住在北京的男学员人数

6.查询年龄低于28岁女学员的家庭住址

7.用变量存储一个1-100之间的随机数，并输出。

8.生成一个1-100之间的随机数，判断其是奇数还是偶数。

9.查询参加”html”科目考试的所有学生信息

10.查询未参加”html”科目考试的所有学生信息

11.查询科目详细信息，且显示科目名称级别：
科目名长度为1-3 :短
科目名长度为4-6 :中
科目名长度>6 :长

12.查询姓名为”金蝶”的同学参加过的所有考试信息

13.查询”初一”年级所有同学参加的考试记录信息

14.查询”初一”年级所有参加过考试的同学信息

15.查询”初一”年级所有未参加过考试的同学信息

16.查询”初一”年级所有男同学参加的考试记录信息

17.查询”初一”年级所有参加过考试的男同学信息

18.查询”初一”年级所有未参加过考试的男同学信息

19.查询考试成绩表中的考试最高分的学生，如果其是男生就奖励一台摩托车，如果其是女生就奖励一个LV手提包。

20.查询考试成绩表中的考试最低分的学生，如果其是男生就罚做50个FWC，如果其是女生就罚做20SXD.

21.查询女生中考试成绩最低的记录，如果分数为奇数就做5个FWC,如果分数为偶数就做5个SXD.

参考答案