MySQL数据库SQL之查询语句进阶

查询指定字段
select name,age from students;
使用 as 给字段起别名
select id as 序号, name as 名字, gender as 性别 from students;
消除重复行
select distinct gender from students;
条件查询
select * from students where id=1;
模糊查询 like %表示任意多个任意字符 _表示一个任意字符
select * from students where name like '黄%';
范围查询
select * from students where id in(1,3,8);
select * from students where id between 3 and 8;
select * from students where (id between 3 and 8) and gender=1;
空判断
select * from students where height is null;
select * from students where height is not null and gender=1;
排序查询
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
select * from students  order by age desc,height desc;
分页查询
select * from 表名 limit start=0,count
select * from students where gender=1 limit 0,3;
聚合函数
count(*) 表示计算总行数，括号中写星与列名，结果是相同的
select count(*) from students;
max(列) 表示求此列的最大值
select max(id) from students where gender=2;
min(列) 表示求此列的最小值
select min(id) from students where is_delete=0;
sum(列) 表示求此列的和
select sum(age)/count(*) from students where gender=1;
avg(列) 表示求此列的平均值
select avg(id) from students where gender=2;