本帖最后由 武汉18期-杨铭 于 2022-8-6 21:20 编辑
- 单表查询
- select * from ym_user;==》查询表中所有的数据
- select name,class from ym_user; ==> 取多个字段
- select * from ym_user where 条件1 and 条件2; ==>查询条件1和条件2的数据
- select * from ym_user where 条件1 or 条件2; ==》查询条件1或条件2的所有数据
- select * from ym_user where 字段 between 数值; ==》查询之间的所有数据
- select * from ym_user where 字段 in(值,值) ==》 查询指定集合中字段为值
- 值不能为空 不能写字段=null 要写字段 is null
- select * from ym_user where 字段 not in(值,值) ==》 查询指定集合中字段不为值
- 值不能为空 不能写字段!=null 要写字段 is not null
- select * from ym_user where 字段 like "%值%“ ==》查询某个字段模糊匹配的数据
- select * from ym_user where 字段 limit m,n;
- *
- select * from ym_user where name="xiaoyang"; ==》查询name等于xiaoyang的所有数据;
- select * from ym_user where name !="xiaozhou"; ==》查询name不等于xiaozhou的所有数据
- select * from ym_user where class=1001 and name="xiaoyang"; ==》查询class为1001且name为xiaoyang的所有数据
- select * from ym_user where class=1001 or name="xiaoyang"; ==》查询class为1001或者name为xiaoyang的所有数据
- *
- select * from ym_user where id>3; ==》查询id大于3的数据
- select * from ym_user where id>=3 and id<=5; ==》查询id大于等于3且小于等于5的数据
- select * from ym_user where id between 3 and 5; ==》查询id大于等于3且小于等于5的数据
- *
- select * from ym_user where class=1001 or class=1002 ==》查询class为1001或1002的数据
- select * from ym_user where id not in(1,2); ==》 查询id不为1或2的数据
- select * from ym_user where class is null; ==》查询class 为空的数据
- select * from ym_user where class is not null; ==》查询class不为空的数据
- select * from ym_user where name like "%ya%"; 询name字段包含ya的所有数据
- select * from ym_user where name like "ya%"; ==>查询name字段为ya开头的数据
- select * from ym_user where name like "%ya"; ==>查询name字段为ya结尾的数据
- *
- select * from user id limit 1,4; ==》 查询2到5行数据
- select * from ym_user where class in(1001,1002) 查询class 为1001或1002 的数据
- select * from user id limit 0,5; ==》 查询前5行数据
- 排序
- 从小到大——升序 asc
- select * from +表名order by 字段asc
- select *from user order by class asc; ==>查询class从小到大排序
- 从大到小—降序 desc
- select * from 表名 order by 字段desc;
- select * from user order by class desc; ==> 查询class从大到小排序
- 分组
- select * from ym_user group by class; ==》通过class字段进行分组
- select class,count(*) from ym_user group by class; ==》 通过class字段进行分析然后求出每组对应人数;
- sslect class,count(*) from ym_user group by class having class!=1000; ==》通过class字段进行分组然后加条件class不为1000
- 注意:
- 1、group by分组之后,只能使用having进行条件筛选
- 2、使用group gy 分组后,仅有分组字段和函数可以放到from前面
- 聚合函数
- count() ==> 统计
- select count(*) from ym_user;
- sun() ==> 求和
- select sum(id) from ym_user;
- avg ==> 求平均值
- select avg(id) from user where class=1001; ==》求id的平均值
- max ==> 求最大值
- select max(id) from user where class=1001;==》求id最大值
- min ==>求最小值
- select min(id) from user where class=1001;==》求id最小值
- distinct ==> 去重
- select distinct(phone) from user;==》对phone字段去掉重复数据
|
|