当由where子句指定的搜索条件指向另外一张表时，就需要使用子查询或嵌套查询。

一、子查询概述

        子查询是一个嵌套在select、insert、update或delete语句或其它子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

子查询的语法

select * from tb_表名 where 查询表达式 [NOT] IN (子查询);

select * from tb_表名 where 查询表达式 比较运算符 (子查询);

语法规则

  • 子查询的selecct查询总使用圆括号括起来。
  • 任何可以使用表达式的地方都可使用子查询，只要它返回的是单个值。
create database db_stu
use db_stu;
--创建学生信息表
create table stuInfo
(
	stuNo varchar(6) not null primary key,
	stuName varchar(10) not null,
	stuSex varchar(2) not null,
	stuAge int not null,
	stuSeat int not null identity(1, 1),
	strAddress varchar(255) default('地址不详')
)

go

--创建学生成绩表
create table stuMarks
(
	ExamNo varchar(7) not null primary key,
	stuNo varchar(6) not null references stuInfo(stuNo),
	writtenExam int null,
	LabExam int null
)

go

--往学生信息表内插入测试数据
insert into stuInfo(stuNo, stuName, stuSex, stuAge, strAddress)
select 's25301', '张秋丽', '男', 18, '北京海淀' union
select 's25303', '李斯文', '女', 22, '河阳洛阳' union
select 's25302', '李文才', '男', 85, '地址不详' union
select 's25304', '欧阳俊雄', '男', 28, '新疆' union
select 's25318', '梅超风', '女', 23, '地址不详'

go

--往学生成绩表内插入测试数据
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59 union
select 's271813', 's25302', 63, 91 union
select 's271816', 's25301', 90, 83 union
select 's271817', 's25318', 63, 53

go

二、带比较运算符的子查询

子查询比较测试用到的运算符是=、<>、<、>、<=、>=。子查询比较测试把一个表达式的值和由子查询产生的一个值进行比较，返回比较结果为true的记录。

--查看年龄比“李斯文”大的学员
select * from stuInfo where stuAge >
(
	select stuAge from stuInfo where stuName = '李斯文'
);

--查看与张秋丽同性别的学员信息有哪些
select * from stuInfo where stuSex = 
(
    select stuSex from stuInfo where stuName = '张秋丽'
);

三、带in或者not in的子查询

一些嵌套内层的子查询会产生一个值，也有一些子查询会返回一列值，即子查询不能返回带几行和几列数据的表。此时适用于in的子查询。

--2.查询笔试成绩大于70分的学员信息（显示学号和姓名以及性别）
select * from stuInfo where stuInfo.stuNo in
(
	select stuMarks.stuNo from stuMarks where stuMarks.writtenExam > 70
);

--3.查询笔试成绩大于全班笔试平均分的学生姓名  嵌套子查询
select stuName from stuInfo where stuNo in
(
    select stuNo from stuMarks where  writtenExam >
	(
		select avg(writtenExam) from stuMarks
	)
);

--没有参加考试的有哪些人？  
select * from stuInfo where stuNo not in
(
	select distinct stuNo from stuMarks
)

四、拓展

4.1 if exists (查询语句)

专用于判断数据库是否存在，判断表是否存在，判断存储过程是否存在，判断临时表是否存在，判断视图是否存在，判断函数是否存在等等。

--判断是否存在不及格？
if exists(select * from stuMarks where stuMarks.writtenExam < 60)
	 begin
		print '有'
	 end
else
	begin
		print '无'
	end

4.2 表复制

select 字段 into  新表名  from  旧表

（1）只复制结构

--给定不成立的条件即只复制结构
select * into tb_sbb2 from stuInfo where 1=0;

（2）复制结构的同事复制数据

--给定恒成立的条件即复制结构及数据
select * into tb_sbb2 from stuInfo where 1=1;

4.3 isnull('值',指定的结果)函数

判断字段值如果为null  就用指定的值替代。

--将没有参加考试的人，在成绩表中：笔试0分处理，机试缺考
select a.*,isnull(b.ExamNo,'无编号') '考试编号',isnull(b.stuNo,a.stuNo) 
,isnull(b.writtenExam,0),isnull(convert(varchar,b.LabExam),'缺考')
from stuInfo a left outer join stuMarks b
on a.stuNo = b.stuNo

--查询学员信息表以及成绩表：  总人数 ，参考人数，缺考人数
--缺考人数 = 总人数 - 参考人数
select a.总人数,b.参考人数,a.总人数 - b.参考人数 '缺考人数' from 
(
	select COUNT(*) '总人数' from stuInfo
)a ,( 
	select COUNT(*) '参考人数' from stuMarks
) b where 1 = 1

4.4 将查询的结果可以当做一张新表继续查询

--结果的字段必须有名字
select * from
(
	select * from stuInfo where stuSex = '男'
) a where a.stuAge = 85

--求总人数 通过人数 通过率   通过人数/总人数 * 100%
select sb.总人数,sa.通过人数 , 
convert(varchar,sa.通过人数 * 100/sb.总人数)+'%' '通过率' from 
(
	select count(*) '总人数' from stuInfo
) sb ,
(
	select count(*) '通过人数' from stuMarks a where a.writtenExam > 70
) sa where 1=1