mysql基础

-- 查询语句
select id,username,phone,total from userinfo
select*from userinfo
-- 基础条件查询语句
select id,username,phone,total from userinfo where id BETWEEN 5 and 8
select id,username,phone,total from userinfo where id in(2,5,8)
select id,username,phone,total from userinfo where id< 2 and total<400
select*from userinfo where id>15 or total>350
-- 模糊查询
Select id,username,phone,total from userinfo where username like"乔%"
select id,username,phone,total from userinfo where username like"%三"
select id,username,phone,total from userinfo where username like "_三"
--分页查询
select*from userinfo limit 0,5
select*from userinfo limit 3,8
select*from userinfo limit 2,6
-- 排序order by asc升序 desc降序
select*from userinfo order by total asc
select*from userinfo order by total desc
-- 聚合函数  count查询有多少条数据
select count(0) from userinfo
select count(phone)from userinfo
-- 总和
select sum(age) from userinfo
-- avg 平均年龄
select avg(total)from userinfo
-- min最小值 max最大值
select id,username,phone,MAX(total) from userinfo
select id,username,phone,min(total) from userinfo
--  分组查询 group by
select gender,count(0) avg(total)from userinfo group by gender
select gender,count(0) avg(total)from userinfo where gender>group by gender HAVING avg(gender)>20
-- 子查询把一个查询结果作为另一个查询条件 嵌套查询
select id,username,phone,total,gender where id in(select id from userinfo where age=75)