SQL 子查询后出现exists,select,from 简单用法

if exists(select * from sys.databases where name ='stuDB1')
	drop database stuDB1
go
create database stuDB1
go
use stuDB1
go
--创建两张表以stuNo作为组外键关联
create table stuInfo13
(
	stuNo int primary key  identity  ,
	stuName nvarchar(16),
	sttuAge int,
	stratTime datetime default(getdate())
)
go
create table grade1
(
	gNo int primary key  identity  ,
	courName nvarchar(16),
	scoer float,
	stuNo int 
)
go
alter table grade1
	add constraint FK_grade1_stuInfo13 foreign key(stuNo) references stuInfo13(stuNo)
go
insert into stuInfo13 values('夏冬',60,'2019-10-18 00:00')
insert into stuInfo13 values('夏伟',88,'2019-12-18 00:00')
insert into stuInfo13 values('周瑜',96,'2019-1-18 00:00')
insert into stuInfo13 values('黄盖',18,'2018-10-18 00:00')
insert into stuInfo13 values('乐进',540,'2016-10-18 00:00')
insert into stuInfo13 values('李典',18,'2019-10-25 00:00')
insert into stuInfo13 values('太史慈',5,'2019-9-18 00:00')
insert into stuInfo13 values('潘璋',10,'2019-9-8 00:00')
go
insert into grade1 values('C#',99,1)
insert into grade1 values('C++',80,3)
insert into grade1 values('JAVA',69,5)
insert into grade1 values('HTML',68,7)
insert into grade1 values('PS',72,8)
insert into grade1 values('SQL',89,2)
insert into grade1 values('JQ',92,4)
insert into grade1 values('JS',96,6)
insert into grade1 values('JQ',95,1)
insert into grade1 values('C#',98,4)

go

--查询同时学习了JQ和C# 的人名字 子查询
--分析，因为人名在stuInfo13，故外层查询应该是查stuInfo13，需要的是一个集合故用in
--内层明显是查grade1，筛选出关键字段外键，通过外键查询主表stuInfo13
select   stuName from stuInfo13  where stuNO in
(
	select stuNo from grade1 where courName in('C#','JQ')
)
--转化为连接查询
--需要去重复不然返回的是重复相同的结果因为
--执行select stuNo from grade1 where courName in('C#','JQ')返回的是1,1,4,4这四个结果
--而子查询用了in 则不会查四次，
select distinct stuName from stuInfo13 inner join grade1 on
	stuInfo13.stuNo=grade1.stuNo where courName in('JQ','C#')


--selet直接出现from 把结果当做一张虚拟表处理
--查询每门课程学习人的平均年龄，必须C#有10个人学习，则计算这10人的平均年龄
select courName '科目',AVG(sttuAge) '平均年龄' from
(
	select s.stratTime,s.sttuAge,s.stuName,s.stuNo,g.courName,g.gNo,g.scoer 
		from stuInfo13 s inner join grade1 g 
		on s.stuNo=g.stuNo
) as temp_stu  --必须这样写表示查询出的数据到临时表中
  group by courName--查询结果重点看C#和JQ的
--子查询后出现exists
--exists 返回的结果是真或假，为真时查询stuInfo13，为假时只查询出表结构
--not exists 意思相反
select * from stuInfo13 where exists
(
	select * from grade1 where courName='HTML'
)

--子查询后出现 select,把结果当作一个列来使用
select (select MAX(sttuAge) from stuInfo13) as '最大年龄'
select * from stuInfo13
select * from grade1