select * from user; 查询所有数据
select name,class from user; 取多个字段值
select * from user where name=’zhou12'; 查询name等于zhou2的所有数据
select * from user where name !='zhou12'; 查询不等于name不等于'zhou'的所有数据
select * from user where name <>'zhou12'; 查询不等于name不等于'zhou12'的所有数据
select * from user where class=1001 and phone=12133333333; 查询class为1001 且phone为12133333333的所有数据
select * from user where class=1001 or phone=12133333333; 查询class为1001 或者phone为12133333333的所有数据
select * from user where id>4; 查询id大于4的数据
select * from user where id>=3 and id<=6; 查询id大于等于3.小于等于6的数据
select * from user where id between 3 and-6 查询id大于等于3并且小于等于6的数据
select * from user where class=1001 or class=1002
select * from user where class in(1001,1002) 查询class 为1001或1002 的数据
select * from user where id not in(1,2); 查询id不为1或2的数据
select * from user where class is null; 查询class 为空的数据
select * from user where class is not null;
select * from+表名where字段like"li%'; 查询字段
select * from user where name like ”%li%"; 查询name 字段所有包含li的数据
select * from user where name like ”li%" 查询name 字段以li结尾的数据
select * from user where name like ”%li" 查询name 字段以li开头的数据
limit m,n (m指下标值,n为查询行数) 下标值从0开始
select * from user id limit 1,4; 查询2到5行数据
select * from user id limit 0,5; 查询前5行数据
排序
从小到大——升序 asc
select * from +表名order by 字段asc
select * from xiuxiu order by phone asc;
从大到小——降序
select * from +表名 order by 字段 desc
select * from xiuxiu order by phone desc;
分组
select * from user group by class 通过class字段进行分组
select class,count(*) from user group by class; 通过class字段进行分组然后求出没组对应人数
select class,count(*) from xiuxiu group by class having class is not null; 通过class字段进行分组然后加条件class不为null
注意 :1,group by 分组之后,只能使用having条件进行筛选
2,使用group by 分组后,仅有分组字段和函数可以放到from前面
聚合函数
count 统计
sum 求和
avg 求平均值
max 求最大值
min 求最小值
distinct 去重
select count(*) from xiuxiu; 统计iuxiu表中数据条数
select sum(id) from xiuxiu; 计算id字段数值之和 select sum(id) from xiuxiu where class=1001;
select avg(id) from xiuxiu where class=1002; 求id的平均值
select max(id) from xiuxiu where class=1002; 求id的最大值
select min(id) from xiuxiu where class=1001; 求id最小值
select distinct(phone) from xiuxiu; 对phone字段去掉重复数据
|
|