功能 | 关键字 | 举例 |
查询 | select | select * from emp; |
新增 | insert | insert into dept_infoemp_test name, salary) value ('李四', 300); |
修改 | update | update dept_infoemp_test set salary = 8000.00 where id = 1; |
删除 | delete | delete from dept_infoemp_test where id = 2; |
重命名列 | as | select name as 名称,salary as 工资 from emp; |
列去重 | distinct | select distinct deptid from emp; |
筛选 | where | |
等值查询 | =、!= 、<> | // 等于
select from emp where deptid = 20;
// 不等于
select from emp where salary <> 1000;
select * from emp where salary != 1000; |
范围查询 | >、<、between A and B | select from emp where salary > 1000 and salary < 2000;
select from emp where salary between 1000 and 2000; |
多条件查询 | and
or
in | select from emp where deptid = 20 or job = 'SALESMAN';
select from emp where deptid in(10,20);
select * from emp where (deptid = 20 and salary > 1000) or job = 'SALESMAN'; |
模糊查询 | like | select * from emp where name like '%AM%'; |
排序 | order by asc（升序）
order by desc（降序） | select from emp order by salary desc;
select from emp order by salary asc; |
限制查询记录条数 | limit | // 查五条数据，等价于limit 0,5
select from emp order by salary desc limit 5;
// 跳过前10行，往后查5条数据，效果就是分页查询的第三页
select from emp order by salary desc limit 10,5; |
分组查询 | group by | |
分组函数 | count、sum、avg、max、min
group_concat、json_arrayagg | select deptid, avg(salary) as avg_salary from emp group by deptid;
select deptid, max(salary) as max_salary from emp group by deptid;
select deptid, min(salary) as min_salary from emp group by deptid;
select deptid, count(1) as emp_num from emp group by deptid; |
分组后条件筛选 | having | // 查询员工薪资大于500且部门平均薪资大于2000的部门id+部门平均薪资
select deptid, avg(salary) as avg_salary from emp
where salary > 500
group by deptid having avg_salary > 2000; |
内连接 | inner join（简写join） | // 员工表和部门表连起来，查询所有员工信息以及对应部门详细信息，如果员工没有找到对应部门，则无法查询出这个员工的所有数据，两条sql等价
select emp.*,dept.name,dept.workaddress from emp ,dept where emp.deptid = dept.id
select emp.*,dept.name,dept.workaddress from emp join dept on emp.deptid = dept.id |
外连接 | left join
right join | // 左外连接，查询所有员工信息以及对应部门信息，如果员工没有找到对应部门，该员工信息也能查询出来，但是他的部门信息是null
select emp.*,dept.name,dept.workaddress from emp left join dept on emp.deptid = dept.id
// 右外连接，查询所有部门信息以及对应员工信息，如果部门没有找到员工，该部门信息也能查询出来，但是他的员工信息是null
select emp.*,dept.name,dept.workaddress from emp right join dept on emp.deptid = dept.id |
日期函数 | YEAR：获取日期中的年
MONTH：获取日期中的月
DAY：获取日期中的天
CURDATE：获取当前日期
TIMESTAMPDIFF：计算两个日期之差
NOW：当前时间
DATE_FORMAT：日期格式化
STR_TO_DATE：字符串类型的日期 转 日期类型 | // TIMESTAMPDIFF(year,birthday,curdate())表示求当前时间和birthday之间的年差，就是年龄
select name,year(birthday),month(birthday),day(birthday),TIMESTAMPDIFF(year,birthday,curdate()) from emp;
DATE_FORMAT(NOW(), '%Y年%m月%d日') |
数学函数 | sqrt：开平方
abs：绝对值
pow：求次方
pi：派
round：四舍五入
ceilng：向上取整
floor：向下取整
mod：取余数 | |
窗口函数 | ROW_NUMBER：获取连续行号（必须排序，无重复值）
RANK：获取排名（重复排名后跳过重复排名的数量，比如1,1,3）
DENSE_RANK：获取排名（重复排名后不重复排名的数量，比如1,1,2）
PERCENT_RANK：获取排名百分比（如前百分之10）
FIRST_VALUE ：查找分组内的第一条记录（如最早订单、最低价格）
LAG：获取分组后数据的前数第n个值，计算环比（如销售额变化、增长率）、检测异常值（与前值对比）。
LEAD：获取分组后数据的后数第n个值，预测未来趋势（如基于下一次行为）、计算提前量（如库存预警） | select * ,
lag(salary,1) over(partition by deptid order by salary desc) as more_salary, -- 分组内当前行的前1行
lead(salary,1) over(partition by deptid order by salary desc) as less_salary, -- 分组内当前行的后1行
first_value(salary) over(partition by deptid order by salary desc) as max_salary,-- 分组内第一个
last_value(salary) over(partition by deptid order by salary desc) as min_salary, -- 分组内最后一个
sum(salary) over(partition by deptid) as sum_salary, -- 分组内所有行
sum(salary) over(partition by deptid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as to_now_sum_salary, -- 前面所有行和当前行
max(salary) over(partition by deptid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as recent_max_salary, -- 前后1行和当前行
min(salary) over(partition by deptid) as min_salary, -- 分组内最小的
avg(salary) over(partition by deptid order by salary RANGE BETWEEN 1500 PRECEDING AND 1500 FOLLOWING ) as recent_avg_salary, -- 与当前薪资差距1500以内所有人的平均薪资
row_number() over(partition by deptid order by salary desc) as row_id, -- 分组内薪资排序,123
rank() over(partition by deptid order by salary desc) as skip_rank_id, -- 分组内薪资排序,113
dense_rank() over(partition by deptid order by salary desc) as noskip_rank_id, -- 分组内薪资排序,112
percent_rank() over(partition by deptid order by salary desc) as percent_rank_id -- 分组内薪资百分比排序
from emp;
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 累计分组内所有行，可以忽略不写
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 累计到当前行
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING -- 累计当前行前2行到后3行共6行
-- 范围：当前日期及前3天内的所有行
RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW
SELECT
product,
price,
amount,
AVG(amount) OVER (
PARTITION BY product
ORDER BY price
-- 范围：当前价格±10元内的所有行
RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
) AS avg_within_10
FROM products; |
关键字书写顺序 | select > from > where > group by > having > order by > limit | select job,avg(salary) as avg_salary from emp
where deptid = 10
group by job
having avg_salary > 1000 order by avg_salary desc
limit 2 |
关键字执行顺序 | 1、FROM：先确定要查询的表（包括关联的表）。
2、JOIN：处理表之间的连接（如 INNER JOIN、LEFT JOIN），生成临时结果集。
3、WHERE：对连接后的结果集进行行过滤（排除不满足条件的行）。
4、GROUP BY：将结果集按指定列分组。
5、HAVING：对分组后的结果进行过滤（排除不满足条件的组）。
6、SELECT：提取需要的列或表达式（包括计算、DISTINCT 去重等）。
7、ORDER BY：对最终结果按指定列排序（此时可以使用 SELECT 中定义的别名）。
8、LIMIT：截取排序后的指定行数（分页）。 | |
