基础查询

-- distinct 过滤重复 (常用语检查一列数据是否有异常值)

select distinct sex from student;

-- count 统计

select count(distinct sex) from student;

-- top 取前N条记录

select top 3 * from student;

-- limit是mysql的语法
select * from table limit m,n

其中m是指记录开始的index，从0开始，表示第一条记录

n是指从第m+1条开始，取n条。

-- column 列运算

select (age + id) col from student;

select s.name + '-' + c.name from classes c, student s where s.cid = c.id;

-- having 分组过滤条件

-- 按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息

select count(*), age from student group by age having age is not null;

-- 按照年龄和cid组合分组，过滤条件是cid大于1的记录

select count(*), cid, sex from student group by cid, sex having cid > 1;

-- 按照年龄分组，过滤条件是分组后的记录条数大于等于2

select count(*), age from student group by age having count(age) >= 2; 

-- 按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2

select count(*), cid, sex from student group by cid, sex having cid > 1 and max(cid) > 2;

嵌套子查询

子查询是一个嵌套在select、insert、update 或 delete 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。

将一个 table 的查询结果当做一个新表进行查询

select * from（

    select id, name from student where sex = 1

) t where t.id > 2;

上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句：

 1、 包含常规选择列表组件的常规select查询

 2、 包含一个或多个表或视图名称的常规from语句

 3、 可选的where子句

 4、 可选的group by子句

 5、 可选的having子句

查询班级信息，统计班级学生人生

select *, (select count(*) from student where cid = classes.id) as num 

from classes order by num;

查询班级id大于小于X的这些班级的学生信息

select * from student where cid in (

    select id from classes where id > 2 and id < 4

);

– 查询不是X班的学生信息

select * from student where cid not in (

    select id from classes where name = '2班'

)

all,any,some

select * from student where cid = 5 and age > all (

    select age from student where cid = 3

);



select * from student where cid = 5 and age > any (

    select age from student where cid = 3

);



select * from student where cid = 5 and age > some (

    select age from student where cid = 3

);

聚合查询

1、 distinct去掉重复数据

select distinct sex from student;

select count(sex), count(distinct sex) from student;

2、 compute和compute by汇总查询

– 对年龄大于20的进行汇总

select age from student where age > 20 order by age compute sum(age) by age;

– 对年龄大于20的按照性别进行分组汇总年龄信息

select id, sex, age from student where age > 20 order by sex, age compute sum(age) by sex;

按照年龄分组汇总

select age from student where age > 20 order by age, id compute sum(age);

– 按照年龄分组，年龄汇总，id找最大值

select id, age from student where age > 20 order by age compute sum(age), max(id);

compute 进行汇总，前面是查询的结果，后面一条结果集就是汇总的信息。compute 子句中可以添加多个汇总表达式，可以添加的信息如下：

 a、 可选by关键字。它是每一列计算指定的行聚合

 b、 行聚合函数名称。包括sum、avg、min、max、count等

 c、 要对其执行聚合函数的列

 compute by适合做先分组后汇总的业务。compute by 后面的列一定要是 order by 中出现的列。

3、 cube 汇总

cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。

select count(*), sex from student group by sex with cube;

select count(*), age, sum(age) from student where age is not null group by age with cube;

cube要结合group by语句完成分组汇总

排序函数

排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如：

1、 对某张表进行排序，序号需要递增不重复的

2、 对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的

3、 在某些排序的情况下，需要跳空序号，虽然是并列

– 基本语法

– 排序函数 over([分组语句] 排序子句[desc][asc])

– 排序子句 order by 列名, 列名

– 分组子句 partition by 分组列, 分组列

– row_number 函数

– 根据排序子句给出递增连续序号,按照名称排序的顺序递增

select s.id, s.name, cid, c.name, row_number() over(order by c.name) as number

from student s, classes c where cid = c.id;