create table zuoye--学号，姓名，班级，性别，教师，出生日期，身高
(
xuehao int,
xingming varchar(50),
banji varchar(50),
xingbing varchar(50),--这里应该是‘xingbie’打错了不好意思
jiaoshi varchar(50),
shengri date,
shengao int,
)
--20条虚拟数据
insert into zuoye values(1,'王大锤','三班','男','李阳','19901116',176)
insert into zuoye values(2,'石头','三班','男','李阳','19910615',169)
insert into zuoye values(3,'张三','三班','男','李阳','19960513',180)
insert into zuoye values(4,'李晓琪','三班','女','李阳','19900913',168)
insert into zuoye values(5,'赵子龙','三班','男','李阳','19980619',179)
insert into zuoye values(6,'李斯','一班','男','赵阳','19870113',189)
insert into zuoye values(7,'王五','一班','男','赵阳','19860316',176)
insert into zuoye values(8,'诸葛','一班','男','赵阳','19830618',183)
insert into zuoye values(9,'貂蝉','一班','女','赵阳','19820319',170)
insert into zuoye values(10,'曹操','一班','男','赵阳','19980820',181)
insert into zuoye values(11,'大乔','二班','女','王阳','19860326',172)
insert into zuoye values(12,'小乔','二班','女','王阳','19880620',173)
insert into zuoye values(13,'刘备','二班','男','王阳','19850129',176)
insert into zuoye values(14,'吕布','二班','男','王阳','18990608',179)
insert into zuoye values(15,'蒋小鱼','二班','男','王阳','18690613',178)
insert into zuoye values(16,'张角','四班','男','孙阳','19850613',188)
insert into zuoye values(17,'孙权','四班','男','孙阳','19850519',179)
insert into zuoye values(18,'雷霆','四班','男','孙阳','16981216',168)
insert into zuoye values(19,'汪大东','四班','男','孙阳','19681326',190)
insert into zuoye values(20,'徐茂怀','四班','男','孙阳','15981213',196)
--男生身高在170以上的
select *from zuoye where xingbing='男' and shengao>170
--查询姓孙的同学
select *from zuoye where xingming like '孙%'
--一共有几个班
select distinct banji from zuoye
--身高为168.170.172（cm）的女生
select *from zuoye where xingbing='女' and shengao in ( '168' ,'170','172')
--查询教师为李阳的班级，学生名字，跟出生日期
select banji,xingming,shengri from zuoye where jiaoshi='李阳'
--学号为升序排列
select *from zuoye order by xuehao asc
--身高为升序排列
select *from zuoye order by shengao asc
--身高在170cm以上的同学并且身高按升序排列
select *from zuoye where shengao>'170'order by shengao asc
--查询三班同学的信息且身高为降序排列
select *from zuoye where banji='三班' order by shengao desc
--查询第5-8条数据，按身高排序后的
select top 3 *from zuoye where xuehao not in (select top 5 xuehao from zuoye order by shengao desc,xuehao desc)
--查男同学的身高高于所有女同学的同学信息
select *from zuoye where xingbing='男' and shengao>all(select shengao from zuoye where xingbing='女')
