《sql常用查询语句》由会员分享，可在线阅读，更多相关《sql常用查询语句（25页珍藏版）》请在金锄头文库上搜索。
1、SQL基本常用查询语句-select select * from student; -all 查询所有 select all sex from student; -distinct 过滤重复 select distinct sex from student; -count 统计 select count(*) from student; select count(sex) from student; select count(distinct sex) from student; -top 取前N条记录 select top 3 * from student; -alias column na
2、me 列重命名 select id as 编号, name 名称, sex 性别 from student; -alias table name 表重命名 select id, name, s.id, s.name from student s; -column 列运算 select (age + id) col from student; select s.name + - + c.name from classes c, student s where s.cid = c.id; -where 条件 select * from student where id = 2; select *
3、from student where id 7; select * from student where id 3; select * from student where id 3; select * from student where id = 3; select * from student where id 3; select * from student where id ! 2 and sex = 1; -or 或者 select * from student where id = 2 or sex = 1; -between . and . 相当于并且 select * fro
4、m student where id between 2 and 5; select * from student where id not between 2 and 5; -like 模糊查询 select * from student where name like %a%; select * from student where name like %ao%; select * from student where name not like %a%; select * from student where name like ja%; select * from student wh
5、ere name not like %j,n%; select * from student where name like %j,n,a%; select * from student where name like %ja,as,on%; select * from student where name like %ja_on%; -in 子查询 select * from student where id in (1, 2); -not in 不在其中 select * from student where id not in (1, 2); -is null 是空 select * f
6、rom student where age is null; -is not null 不为空 select * from student where age is not null; -order by 排序 select * from student order by name; select * from student order by name desc; select * from student order by name asc; -group by 分组 按照年龄进行分组统计 select count(age), age from student group by age;
7、按照性别进行分组统计 select count(*), sex from student group by sex; 按照年龄和性别组合分组统计，并排序 select count(*), sex from student group by sex, age order by age; 按照性别分组，并且是id大于2的记录最后按照性别排序 select count(*), sex from student where id 2 group by sex order by sex; 查询id大于2的数据，并完成运算后的结果进行分组和排序 select count(*), (sex * id) ne
8、w from student where id 2 group by sex * id order by sex * id; -group by all 所有分组 按照年龄分组，是所有的年龄 select count(*), age from student group by all age; -having 分组过滤条件 按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息 select count(*), age from student group by age having age is not null; 按照年龄和cid组合分组，过滤条件是cid大于1的记录 sele
9、ct 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; 嵌套子查询 子查询是一个嵌套在
10、select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。 # from (select table)示例 将一个table的查询结果当做一个新表进行查询 select * from ( select id, name from student where sex = 1 ) t where t.id 2; 上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句： 1、 包含常规选择列表组件的常规select查询 2
11、、 包含一个或多个表或视图名称的常规from语句 3、 可选的where子句 4、 可选的group by子句 5、 可选的having子句 # 示例 查询班级信息，统计班级学生人生 select *, (select count(*) from student where cid = classes.id) as num from classes order by num; # in, not in子句查询示例 查询班级id大于小于的这些班级的学生信息 select * from student where cid in ( select id from classes where id 2
12、 and id 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; selec
13、t count(sex), count(distinct sex) from student; 2、 compute和compute by汇总查询 对年龄大于的进行汇总 select age from student where age 20 order by age compute sum(age) by age; 对年龄大于的按照性别进行分组汇总年龄信息 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); 按照年龄分组，年龄汇总
