数据库查询汇总

• • 转载
  • 原文地址:http://blog.csdn.net/liuxinmingcode/article/details/51554061

• • 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 name 列重命名
  •  
    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 * 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 <= 5;
  •  
    select * from student where id !> 3;
  •  
    select * from student where id !< 5;
  •  
     
  •  
    and 并且
  •  
    select * from student where id > 2 and sex = 1;
  •  
     
  •  
    or 或者
  •  
    select * from student where id = 2 or sex = 1;
  •  
     
  •  
    between … and … 相当于并且
  •  
    select * from 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 ‘%[a][o]%’;
  •  
    select * from student where name not like ‘%a%’;
  •  
    select * from student where name like ‘ja%’;
  •  
    select * from student where 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 * from 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;
  •  
    按照性别进行分组统计
  •  
    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) new 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;

数据库查询汇总

原文：https://www.cnblogs.com/woyaozilv/p/10703088.html