sql erver 高级子查询

1.子查询
    概念：当由where子句指定的搜索条件指向另一张表时，就需要使用子查询或嵌套查询。
    
    子查询是一个嵌套在select、insert、update或者delete语句或其它子查询中的查询。任何允许使用表达式的地方都可以使用子查询。
    
    语法规则：
        ①子查询的select查询总使用圆括号括起来
        ②任何可以使用表达式的地方都可以使用子查询，主要它返回的是单个值

求年龄比李斯文大的学生姓名
查询笔试成绩大于全班笔试平均分的学生姓名  嵌套子查询
------exists子查询  exists存在
查询班上有没有不及格的学生信息 如果有则打印'有' 无则打印'无'
if exists(select * from stuMarks where writtenExam<70)
begin
    print '有'
end
else
begin
    print '无'
end

2.in子查询

3.not in子查询
any    some

4.exists子查询  存在

5.not exists子查询

isnull  cast

1.复制表以及其数据
    select * into newT from stuInfo

2.只要表结构不要数据
    select * into newTT from stuInfo where 1=2
    select * from newTT

子查询的基本用法 和概念

子查询：
1.概念：将一个select查询的结果当做条件在另外一个select语句进行相关操作
称为子查询

2.子查询的规则：
	子查询的select必须包裹在小括号中。
	子查询的分类：运算符的子查询|带in的子查询
	语法规则：
		select * from tb_表 where 某属性 = (子查询);
		select * from tb_表 where 某属性 in (子查询);
	

--12、求梁山伯今年发了几次工资，即为uid为1且xid为1的记录记数
select * from tb_users where uname = '梁山伯'--1
select * from tb_inoutfield where xname = '工资';--1

select COUNT(*) from tb_inoutInfo where uid = 1 and xid = 1;

--使用子查询完善
select COUNT(*) from tb_inoutInfo 
where uid = 
(
	select uid from tb_users where uname = '梁山伯'
)
and 
xid = 
(
	select xid from tb_inoutfield where xname = '工资'
);

-----------------------------------------------------------------
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
)

select * from stuInfo;
select * from stuMarks;


--[经典案例]
--1.查询年龄比梅超风大的学员信息
select * from stuInfo where stuAge >
(
	select stuAge from stuInfo where stuName = '梅超风'
)
--2.查询笔试成绩大于70分的学员信息（显示学号和姓名以及性别）
--联表查询实现
select a.stuName,a.stuSex,a.stuAge from 
stuInfo a join stuMarks b
on a.stuNo = b.stuNo
where b.writtenExam >70
--子查询
select a.stuName,a.stuSex,a.stuAge from stuInfo a
where a.stuNo in
(
	select b.stuNo from stuMarks b where writtenExam >70

)
--3.查询笔试成绩大于全班笔试平均分的学生姓名  嵌套子查询
select * from stuInfo where stuNo in
(
	select stuNo from stuMarks where writtenExam >
	(
		select AVG(writtenExam) from stuMarks
	)
)
--补充知识点
--1.if exists  判断数据库是否存在  数据表是否存在  数据是否存在等等操作。
/*
	if exists ( select语句)

		begin
			print '结果1'
		end
	
	else
		begin
			print '结果2'
		end
*/
--查询成绩表中的机试成绩，如果有不及格的打印砍死 
--都及格 放假
if exists (select * from stuMarks where LabExam < 60 )
	begin
		print '砍死'
	end
else
	begin
		print '放假'
	end


--NOT IN 的使用
--查询没有参加考试的人
select * from stuInfo where stuNo not in
(
	select stuNo from stuMarks
);
-------------------------------------------------------------
--表的赋值
select * from stuInfo
--员工表（没有创建）通过复制的语法将学生信息表中的所有数据赋值到员工表
--复制表中的数据时，表的结构复制了，表的数据复制了
--select [属性1,属性2,属性3...] into 新表名称 from 旧表名称 where 1=2; 

select stuName,stuSex,stuAge into tb_emp
from stuInfo

select stuName,stuSex,stuAge into tb_emp2
from stuInfo where 1=1

select * from tb_emp2;

--数据不需要复制，结构需要  只要给定一个永远不成立的条件即可。
select stuName,stuSex,stuAge into tb_emp3
from stuInfo where '张三' = '李四'
select * from tb_emp3

--当表存在时，复制数据
--insert into table1(属性1,属性2,属性3...)select [属性1,属性2,属性3...] from table2
--emp3没有数据  emp2有数据
insert into tb_emp3(stuName,stuSex,stuAge)
select stuName,stuSex,stuAge from tb_emp2;

select * from tb_emp3

--多行数据的插入
insert into tb_表
select 值1,值2,值3 
union all --联合
select 值1,值2,值3 ;

select LEN('adshkjsad')

select 'hello'
union all
select 'world'


insert into tb_emp3
select 'sb123','张三1',18
union all
select 'sb124','张三2',18
union all
select 'sb125','张三3',18
--5.将没有参加考试的人，在成绩表中：笔试0分处理，机试缺考
select a.stuName,
ISNULL(b.writtenExam,0) as '笔试成绩',
ISNULL(CONVERT(varchar(100) ,b.LabExam ),'缺考') as '机试成绩'
from stuInfo a left outer join stuMarks b
on a.stuNo = b.stuNo;


--可以将一个查询的结果当做一个新表继续查询。必须起别名


select * from
(
	select * from stuInfo where stuSex = '男'
) a where a.stuAge = 85




--6.查询学员信息表以及成绩表：  总人数 ，参考人数，缺考人数

select COUNT(*) as '总人数' from stuInfo;
select COUNT(*) as '参考人数' from stuMarks;
---------------------------------------------------
select a.总人数,b.参考人数,(a.总人数 - b.参考人数) as '缺考人数' from 
(
	select COUNT(*) as '总人数' from stuInfo--总人数
) a ,
(
	select COUNT(*) as '参考人数' from stuMarks--参考人数
)b

--7.求总人数 通过人数 通过率

select COUNT(*) from stuInfo;
select COUNT(*) from stuMarks where writtenExam >70

select 
aa.总人数,
bb.通过人数,
CONVERT(varchar(100),(bb.通过人数*100/aa.总人数))+'%' as '通过率'
from 
(
	select COUNT(*) as '总人数' from stuInfo
)aa,
(
	select COUNT(*) as '通过人数' from stuMarks 
	where writtenExam >70
)bb