排序，去重，分组，作业

all：所有的意思
in：代表在某些参数范围之内的都符合条件【in（）】括号里面写参数，相当于多个OR
select *from 文件名 where 表名 in （）
not：起到修饰的作用，取反，写在in前面
select *from 文件名 where 表名 not in （）
between and 表示在某个范围之内，相当于>= <=
select *from 文件名 where ids>500 and ids<505
select *from 文件名 where ids between 500 and 505
模糊查询，行业里面带国家两个字
select *from 文件名 where name like'%国家%'
select *from 文件名 where name like'国家'
select *from 文件名 where name like'%机构'
select *from 文件名 where name not like'%国家%'

select *from 文件名 where ids > all
(
select ids from 文件名 where ids >1190 and ids <1995

)
any：大于小的，小于大的（大于里面的一个）
查出一列来当做参数来使用

 

怎么排序
--select *from 文件名 order by ids表里面的列
asc：升序的意思
desc：从大往小排
对两列进行排序
select *from 文件名 order by parent，ids
parent，ids：为列名

 

分组：把一列里面相同的分为一组
select  列名 from 文件名 group by 列名
select code from 文件名 where parent='m'

去重
select distinct parent from category
前多少条数据
select top 5 *from category
select top 5 *from category order by ids
select top 5 *from dategory where ids>900 and ids<950 order by ids

 

 

 

 

--学生信息表
--学号，姓名，班级，性别，教师，出生日期，身高
添加20条虚拟数据
查询男同学身高170以上的
查询姓王的同学信息
查询一共有几个班级
查询女同学里身高在168,170,172折三个数的信息
create table xueshengxinxibiao
( 
     xuehao int,
     name  varchar(50),
     class varchar(50),
     xingbie varchar(50),
     jiaoshi varchar(50),
     chushengriqi varchar(50),
     shengao varchar(50)
)
insert into xueshengxinxibiao values(1,'乔峰','一年级一班','男','天龙','1988-03-14','181cm')
insert into xueshengxinxibiao values(2,'阿朱','一年级一班','女','天龙','1988-04-05','168cm')
insert into xueshengxinxibiao values(3,'段誉','一年级一班','男','天龙','1988-12-09','178cm')
insert into xueshengxinxibiao values(4,'王语嫣','一年级一班','女','天龙','1988-05-06','173cm')
insert into xueshengxinxibiao values(5,'虚竹','一年级一班','男','天龙','1988-06-04','178cm')
insert into xueshengxinxibiao values(6,'梦姑','一年级一班','女','天龙','1988-07-19','172cm')
insert into xueshengxinxibiao values(7,'慕容复','一年级一班','男','天龙','1988-12-18','175cm')
insert into xueshengxinxibiao values(8,'阿碧','一年级一班','女','天龙','1988-09-01','165cm')
insert into xueshengxinxibiao values(9,'王夫人','一年级一班','女','天龙','1988-08-08','165cm')
insert into xueshengxinxibiao values(10,'钟灵','一年级一班','女','天龙','1988-06-01','160cm')
insert into xueshengxinxibiao values(11,'段正淳','一年级一班','男','天龙','1988-03-04','176cm')
insert into xueshengxinxibiao values(12,'杨过','一年级二班','男','神雕','1988-10-01','180cm')
insert into xueshengxinxibiao values(13,'时迁','一年级三班','男','水浒','1988-09-25','170cm')
insert into xueshengxinxibiao values(14,'宋江','一年级三班','男','水浒','1988-05-23','173cm')
insert into xueshengxinxibiao values(15,'林冲','一年级三班','男','水浒','1988-11-11','183cm')
insert into xueshengxinxibiao values(16,'孙二娘','一年级三班','女','水浒','1988-02-18','174cm')
insert into xueshengxinxibiao values(17,'小龙女','一年级二班','女','神雕','1988-06-19','168cm')
insert into xueshengxinxibiao values(18,'王蓉','一年级二班','女','神雕','1988-08-23','165cm')
insert into xueshengxinxibiao values(19,'郭靖','一年级二班','男','神雕','1988-08-13','175cm')
insert into xueshengxinxibiao values(20,'洪七公','一年级二班','男','神雕','1988-08-29','170cm')
select *from xueshengxinxibiao
select *from xueshengxinxibiao where xingbie='男'and shengao > '170cm'
select *from xueshengxinxibiao where name like '%王%'
select class from xueshengxinxibiao group by class
select *from xueshengxinxibiao where xingbie='女'and (shengao ='168cm'or shengao='170cm'or shengao='172cm')
--查男同学的身高高于所有女同学的同学信息
select top 3 *from xueshengxinxibiao where shengao not in (select top 5 shengao from xueshengxinxibiao order by shengao desc,xuehao desc) order by shengao desc

--查男同学的身高高于所有女同学的同学信息
select *from xueshengxinxibiao where xingbie='男' and shengao>all(select shengao from xueshengxinxibiao where xingbie='女')