MySQL单表查询
where条件
比较运算符
> < >= <= <> !=
select 字段 from 表 where 条件
select * from 表名 where 字段>范围;
between
between a and b # 查询a,b之间的所有内容(双闭合)
select * from 表名 where 字段 between a and b;
in
in(a,b,c) # 查询值为a或者b或者c的所有内容
select * from 表名 where 字段 in(a,b);
like
select * from 表名 where 字段 like '匹配内容%'；
%是一个通配符,表示任意长度的任意內容
select * from 表名 where 字段 like '程%';
_也是一个通配符,表示一个长度的任意内容
select * from 表名 where 字段 like '程咬_'
逻辑运算符 and or not
select * from 表名 where 字段 and 字段
select * from employee where age=18 and salary<10000;
select * from 表名 where 字段 or 字段
select * from employee where age=18 or salary<10000;
select * from 表名 where 字段 not in 字段
select * from employee where post not in ('teacher');
身份运算符 is null/ is not null
select * from 表名 where 字段 is null; 查询这个字段里所有为空的值
select * from employee where post_comment is null;
select * from 表名 where 字段 is not null; 查询这个字段中所有不为空的值
select * from employee where post is not null;
正则匹配
所有人的身份证号,匹配所有身份证号是15位的居民 ^\d{15}$
select 字段 from 表 where age regex '^\d{15}$';
group系列
group by分组
select * from 表名 group by 字段;
select * from employee group by post;
group_concat 查看组内的名字
select group_concat(emp_name) from employee group by sex;
group_count 计数
select sex,count(emp_name) from employee group by sex;
having 过滤
对分组进行条件过滤 总是和group by 连用,where中不能出现聚合函数,所以和聚合函数有关的条件筛选也只能用having
总是对分组之后的结果进行一个条件筛选的时候用having
查各个岗位的员工个数
select post,count(id) from employee group by post having count(id) <2
查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
先将岗位分组然后分别对每个岗位的薪资求平均值，然后在筛选平均薪资大于10000的
order by 排序
# 默认从小到大排序 升序
select * from employee order by age;
# 从大到小排序 desc 降序
select * from employee order by age desc;
聚合函数
先from找到表
再用where的条件约束去表中取出记录
然后进行分组group by，没有分组则默认一组
然后进行聚合
最后select出结果
示例：
select count(*) from employee;
select count(*) from employee where depart_id=1;
select max(salary) from employee;
select min(salary) from employee;
select avg(salary) from employee;
select sum(salary) from employee;
select sum(salary) form employee WHERE depart_id=3;
limit
limit 取前n个或者web开发中做分页功能
# 显示前n条 limit n
# 从第m+1条开始,显示n条 limit m,n
select * from employee order by age limit 1,6;
# 从第m+1条开始,显示n条 limit n offset m
select * from employee order by age limit 6 offset 10;
=========limit：限制打印几条=========
1.select * from employee limit 3;#打印前三条
2.像这样表示的：指的是从哪开始，往后取几条 (这样的操作一般用来分页)
select * from employee limit 0,3;
select * from employee limit 3,4;
select * from employee limit 6,3;
select * from employee limit 9,3;
3.select * from employee order by id desc limit 3; #查看后三条
练习：查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列；
select post,avg(salary) as avg_salary from employee group by post having avg(salary)>10000 order by avg_salary
sql的解析顺序
select distinct 字段 from 表 where 条件 group by 分组 having 过滤条件 order by 排序 limit n;
关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
