where条件筛选记录
select id,username,age from uesr where id=5;
alter table user add userDesc varchar(100);
update user set userDesc="This is a test" where id<=5;
select id,username,age,userDesc from user where userDesc<=>null; -- <=>检测null
--is [not] null 检测null
select id,username,age,userDesc from user where userDesc is null;
--[not] between ... and 选定范围
select id,username,age,sex from user where age between 18 and 20;
--[not] in(值...) 指定集合
select id,username,age,sex from user where id in(1,3,5,7,9);
--and / or
select id,username,age,sex from user where sex="男" and age>=20;
select id,username,age,sex from user where salary between 20000 and 100000 and sex="男";
select id,username,age,sex from user where id=1 or username="queen";
--[not] like 匹配字符
select id,username,age,sex from user where username like "queen";
--模糊查询 通配符 %任意长度的字符串 _任意一个字符
select id,username,age,sex from user where username like '%三%'; --查询username中有三的用户
select id,username,age,sex from user where username like "___"; --三个_匹配三个字符
select id,username,age,sex from user where username like "张_%"; --查询以张开头的最少两个字符大小
--默认忽略大小写
group by 对记录进行分组
--把值相同放到一个组中，最终查询出的结果只会显示组中一条记录
select id,username,age,sex from user group by sex;
--分组配合group_concat()查看组中某个字段的详细信息
select id,group_concat(username),age,sex from user group by sex;
--显示
+----+----------------------------------------------+-----+-----+
| id | group_concat(username) | age | sex |
+----+----------------------------------------------+-----+-----+
| 1 | king,张三,张子枫,刘德华,吴亦凡,张阿文,经过历 | 23 | 男 |
| 2 | queen,imooc,子怡,王菲 | 27 | 女 |
+----+----------------------------------------------+-----+-----+
--配合聚合函数使用 count() sum() max() min() avg()
select count(*) as total_users from user; --得到总记录数，null也统计，别名total_users
select count(userDesc) from user; --null不统计
--按照sex分组，得到用户名详情，并且分别统计组中的总人数
select group_concat(username) as usersDetail,sex,addr,count(*) as totalUsers from user group by sex;
--显示
+----------------------------------------------+-----+------+------------+
| usersDetail | sex | addr | totalUsers |
+----------------------------------------------+-----+------+------------+
| king,张三,张子枫,刘德华,吴亦凡,张阿文,经过历 | 男 | 上海 | 7 |
| queen,imooc,子怡,王菲 | 女 | 上海 | 4 |
+----------------------------------------------+-----+------+------------+
--按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值
select addr,
group_concat(username) as usersDetail,
count(*) as totalUsers,
sum(age) as ageSum,
min(age) as ageMin,
avg(age) as ageAvg
from user
group by addr;
--显示
+------+-------------------------------+------------+--------+--------+---------+
| addr | usersDetail | totalUsers | ageSum | ageMin | ageAvg |
+------+-------------------------------+------------+--------+--------+---------+
| 上海 | king,queen,张三,张子枫,吴亦凡 | 5 | 161 | 23 | 32.2000 |
| 北京 | imooc,子怡 | 2 | 56 | 25 | 28.0000 |
| 南京 | 刘德华 | 1 | 14 | 14 | 14.0000 |
| 广州 | 王菲 | 1 | 62 | 62 | 62.0000 |
| 湖南 | 经过历 | 1 | 25 | 25 | 25.0000 |
| 西安 | 张阿文 | 1 | 14 | 14 | 14.0000 |
+------+-------------------------------+------------+--------+--------+---------+
--配合with rollup 会在记录末尾添加一条记录，是上面所有记录的总和
select group_concat(username) as usersDetail,
count(*) as totalUsers
from user
group by sex
with rollup;
--按照字段位置来分组(addr)
select addr,
group_concat(username) as usersDetail,
count(*) as totalUsers
from user
group by 1;
--查询age>=30的用户并且按照sex分组
select age,
group_concat(username) as usersDetail,
count(*) as totalUsers
from user
where age>=30
group by sex;
--having子句对分组结果进行二次筛选
select addr,
group_concat(username) as usersDetail,
count(*) as totalUsers
from user
group by addr
having count(*)>=3;
--having totalUsers>=3; 也可以通过别名的形式二次筛选
--having后面可以加通过聚合函数操作的列(如：sum(),max())或者select查询到的列
--having进行分组条件的指定时，一定要保证分组条件要么为聚合函数，要么条件中的字段必须出现在当前的select语句中
order by 实现排序效果
order by 字段名称 asc|desc --升序|降序
--测试
select id,username,age
from user
order by id desc;
--order by age; --按照age升序
select id,username,age
from user
order by age asc,id asc; --按照多个字段排序，先age,相同部分id
select id,username,age
from user
where age>=30
order by age desc; --有条件的字段排序
select rand(); --产生(0-1)随机数
--实现随机记录
select id,username,age
from user
order by rand(); --每次产生的结果都不同
limit限制结果集的显示条数
limit 值 --显示结果集的前几条记录
limit offset,row_count --从offset开始，显示row_count条记录
--测试
--方式一
select id,username,age,sex from user
limit 5;
--方式二
select id,username,age,sex from user
limit 1,5; --offset从0开始，用这种方式实现分页
--显示
+----+----------+-----+-----+
| id | username | age | sex |
+----+----------+-----+-----+
| 2 | queen | 27 | 女 |
| 3 | imooc | 31 | 女 |
| 4 | 张三 | 38 | 男 |
| 5 | 张子枫 | 38 | 男 |
| 6 | 子怡 | 25 | 女 |
+----+----------+-----+-----+
--1.更新user表中的前三条记录，将age加5
update user set age=age+5 limit 3;
--2.将user表中id字段降序排列，更新前三条记录，将age减10
update user set age=age-10 order by id desc limit 3;
--3.删除user表中的前三条记录
delete from user limit 3;
--4.删除user表中id字段降序排列的前三条记录
delete from user order by id desc limit 3;
--update或者delete时limit只支持一个参数形式
单表查询完整select语句的形式
select addr,
group_concat(username) as usersDetail,
count(*) as toTalUsers,
sum(age) as sum_age,
max(age) as max_age,
min(age) as min_age,
avg(age) as avg_age
from user
where id>=2
group by addr
having sum_age>=25
order by sum_age
limit 2;
