SQL Server数据库第十三课：子查询

知识点：子查询

1、什么是子查询

1.1   子查询的定义：

　　子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。

　　子查询就是嵌套在select、delete、update、insert、from、where语句获其他子查询中的查询。实质，就是一个 select 查询的结果是另外一个语句的输入值。任何允许使用表达式的地方都可以使用子查询。 

　　在特定情况下，一个查询语句的条件需要另一个查询语句来获取，内层查询（inner query）语句的查询结果，可以为外层查询（outer query）语句提供查询条件。

1.2   子查询的特点（规范）

　　①子查询必须放在小括号中

　　②子查询一般放在比较操作符的右边，以增强代码可读性

　　③子查询(小括号里的内容)可出现在几乎所有的SELECT子句中(如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句……)

 1.3  子查询的执行过程

　　子查询的执行过程遵循“由里及外”的原则，即总是先执行最内层的子查询语句，执行完毕后将执行结果与外层的语句进行合并，依次逐层向外扩展并最终形成完整的SQL语句。

2、使用 IN 或 NOT IN 的子查询

在嵌套查询中，子查询的结果往往是一个集合。IN 子查询用于判断一个给定值是否在子查询结果集中，其语法格式为：

父查询  [NOT]  IN (子查询)

准备工作：准备数据信息（这里直接以SQL语句写出，复制以后，按照顺序依次执行即可）

--1、创建数据库StudentDB
create database StudentDB
--2、使用数据库StudentDB
use StudentDB
--3、创建学生信息表
create table student(
	stuID int primary key identity(1000,1),	--学生编号，主键，标识列
	stuNames nvarchar(20) not null,			--学生姓名，不允许为空
	stuGender char(2) not null,				--性别
	stuAge int not null						--年龄
);
--4、创建成绩表
create table score(
	scNO int primary key identity(1,1),			--编号，主键，标识列
	stuID int references student(stuID),		--学生编号，外键
	subNO int ,									--科目编号
	scores float not null						--科目成绩
);
--5、向学生表中插入信息
insert into student (stuNames,stuGender,stuAge)
select '张峰','男',18 union
select '李晓娜','女',17 union
select '马俊','女',18 union
select '乔世权','男',19 union
select '李慧娟','女',18 union
select '张杜娟','男',17 union
select '王慧玲','女',18 union
select '张华','男',17 union
select '徐冰','女',18 union
select '李晓','男',19 union
select '杜菲菲','女',18 
--查询表中信息
select * from student


--6、向成绩表中插入信息
insert into score(stuID,subNO,scores)
select 1000,101,58 union
select 1000,111,75 union
select 1001,111,65 union
select 1001,141,90 union
select 1003,101,90 union
select 1004,111,55 union
select 1004,131,85 union
select 1004,141,72 union
select 1005,111,90 union
select 1005,131,95 union
select 1007,101,78 union
select 1008,131,75 union
select 1008,101,78 union
select 1010,121,58 
--查询分数表中的信息
select * from score

示例练习：使用 IN 或 NOT IN 的子查询

示例代码如下：

--7、查询参加了考试的学生信息
--先在分数表里，去重查询学生
select stuID from score 
select distinct stuID from score    --使用 distinct 关键字去重

--然后在查询学生信息
select * from student where stuID in(select distinct stuID from score)

3、使用 EXISTS 的子查询

       EXISTS 谓词只注重子查询是否返回行。如果子查询返回一个或多个行，谓词评价为真，否则为假。EXISTS 搜索条件并不真正第使用子查询的结果，它仅仅测试子查询是否产生任何结果。

--8、使用exists子查询
--查询参加了考试的学生信息
select * from student where 
exists 
(select * from score where student.stuID=score.stuID )

说明：EXISTS 谓词子查询的 SELECT 子句中可以使用任何列名，也可以使用任意多个列，也可以只使用一个星号 *  。因为这种谓词只注重是否返回行，而不注重行的内容。

注意：一般来说 EXISTS 子查询和 IN 子查询，功能上来说一样，执行效率上有差异，处理大量数据时， EXISTS 子查询效率更高。

思考：使用 NOT EXISTS 子查询，查询没有考试成绩的学生信息。

4、使用比较运算符的子查询

       嵌套内层子查询通常作为搜索条件的一部分，呈现在 WHERE 或 HAVING 子句中。例如，把一个表达式的值和子查询生成的值相比较。这个测试类似于简单比较测试。

       子查询比较测试用到的运算符是：=、<>、<、>、<=、>=。子查询比较测试把一个表达式的值和由子查询产生值进行比较，这时子查询只能返回一个值，否则错误。  最后返回比较结果为TRUE 的记录。

示例练习：使用比较运算符的子查询

--9、使用比较运算符的子查询
--查询成绩是95的学生信息
select stuID from score where scores=95	--先查询95分的学生id
select * from student where stuID=(select stuID from score where scores=95)        --使用子查询

5、使用子查询实现分页查询

5.1  什么是分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1~100条记录作为第1页，显示第101~200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。

5.2  分页相关的准备

pageSize ：每一页显示多少条数据。

pageIndex ：当前显示第几页（页的索引）。

举例：

示例练习：使用分页查询，将学生表中的信息，分页。

要求：每页显示3条数据

--10、实现分页查询
--将原有的学生信息分页显示所有同学
--先查询一下同学的信息
select * from student where
--确定分页显示数据时，数据的排序依据（表里没有连续的数据？人为添加一个编号，相当于增加一个常量列）
select * ,ROW_NUMBER() over(order by stuID desc)  as rowIndex from student
--整理分页的公式，就是根据索引页和索引数据，确定对应的数据编号
select * from (select * ,ROW_NUMBER() over(order by stuID desc) as rowIndex from student)
as t1
where rowindex between 4 and 6

说明：ROW_NUMBER() OVER函数的基本用法：

简单的说 row_number() 从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY 列名 DESC) 是先把 指定的列 降序，再为降序以后的每条记录返回一个序号。

思考：将有考试成绩的学生信息，按照成绩从高往低，进行分页查询，要求每页显示3条数据，输出结果显示第二页的数据。

========这里是结束分割线========