（十三）基本常用查询

--select
select * from classes;
 
--all 查询所有
select all name from classes;
 
--distinct 过滤重复
select distinct name from classes;
 
--count 统计
select count(*) from classes;
select count(name) from classes;
select count(distinct name) from classes;
 
--top 取前N条记录
select top 3 * from classes;
 
--alias column name 列重命名
select id as 编号, name '名称', createDate 创建时间 from classes;
 
--alias table name 表重命名
select id, name, s.id, s.name from classes s;
 
--column 列运算
select (age + id) col from classes;
select s.name + '-' + c.name from classes c, studentB s where s.cid = c.id;
 

--where条件
select * from classes where id=2;
select * from classes where id>7;
select * from classes where id<3;
select * from classes where id>=3;
select * from classes where id <=5;
select * from classes where id !>3;
select * from classes where id !<5;
select * from classes where id <>3;--这条语句的意思是不等于3，相当于"!="

--and并且
select * from classes where id>3 and age=10;

--or或者
select * from classes where id=3 or age=2;

--between...and...相当于并且
select * from classes where id between 2 and 5;
select * from classes where id not between 2 and 5;

--like模糊查询
select * from classes where name like '%o%';--查询中间带有o的字符
select * from classes where name like '%[a][o]%';--查询包含a和o的字符串，且a和o相邻
select * from classes where name not like '%a%';--查询不包含a的字符串
select * from classes where name like 'ja%';--查询以ja开头的字符串
select * from classes where name not like '%[j,n]%';--查询不包含j,n的字符串
select * from classes where name like '%[j,n,a]%';--查询包含j/n/a的字符串
select * from classes where name not like '%ja%' and name not like '%as%' and name not like '%on%';--查询"排除name字段中包含ja/as/on的记录
select * from classes where name like '%ja%' or name like '%on%';--查询匹配name字段中包含ja或者on的记录

--in子查询
select * from classes where id in (1,2);

--not in 不在其中
select * from classes where id not in(1,2);

--is null 是空
select * from classes where age is null;

--is not null 不为空
select * from classes where age is not null;

--order by排序
select * from classes order by id;--默认升序排列
select * from classes order by id desc;--设置降序排列
select * from classes order by id asc;--设置升序排列

--group by分组
--按照年龄进行分组统计
select count(age),age from classes group by age;
--按照性别进行分组统计
select count(*),sex from studentB group by sex;
--按照年龄和性别组合分组统计，并降序
select count(*),sex from studentB group by sex,age order by age;
--按照性别分组，并且是id大于2的记录最后按照性别排序
select count(*),sex from classes where id>2 group by sex order by sex;
--查询id大于2的数据，并完成运算后的结果进行分组和排序
select count(*),(sex * id) new from studentB where id>2 group by sex * id order by sex * id;


--group by 所有分组
--按照年龄分组，是所有的年龄
select count(*),age from classes group by all age;

--having 分组过滤条件
--按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息
select count(*),age from classes group by age having age is not null;
--按照年龄和id组合分组，过滤条件是id大于1的记录
select count(*),id,sex from classes group by id,sex having id>1;
--按照年龄分组，过滤条件是分组后的记录条数大于等于2
select count(*),age from classes group by age having count(age)>=2;
--按照id和性别组合分组，过滤条件是id大于1，id的最大值大于2
select count(*),id,sex from classes group by id,sex having id>1 and max(id)>2;