牛客SQL实战
文章目录
预备知识点
1. mysql常用方法
GROUP_CANCAT、LENGTH、SUBSTRING …
詳見:https://zhuanlan.zhihu.com/p/360367679
2. if语句
# postgre if判断语句
DO $$BEGIN
IF NOT EXISTS (
SELECT 1
FROM subsystems
WHERE identifier in ('ColdHeatSourceSystem')
) then
INSERT INTO public.subsystems (id, identifier, "name", model_status, create_user, create_time, update_user, update_time) VALUES('9c85bed1-5da1-43d0-806e-6cb7ff22fd4d'::uuid, 'ColdHeatSourceSystem', '冷热源系统', 1, '0ba6771a-d316-4c07-ae87-06eb6a49f381'::uuid, '2023-03-16 19:36:17.051', NULL, NULL);
END IF;
END$$;
# msyql if判断语句
IF condition THEN
statements;
ELSE
else-statements;
END IF;
# MySQL IGNORE关键字用于在执行INSERT语句时,如果遇到主键或唯一索引冲突,则忽略该条数据。
INSERT IGNORE INTO actor VALUES(4, 'ED', 'CHAS1E', '2006-02-15 12:34:33');
3. 索引
-- 创建普通索引
create index idx_lastname on actor(last_name);
-- 创建唯一索引
create unique index uniq_idx_firstname on actor(first_name);
-- 删除索引
drop index idx_lastname on actor;
-- 查看所有索引
show index from actor;
-- 使用强制索引
select *
from salaries s
force index(idx_emp_no) -- 前提:该索引已创建
where emp_no = 10005;
4. 视图
-- 创建视图:视图不存在则创建,存在则更新
create or replace view actor_name_view as
select first_name first_name_v, last_name last_name_v
from actor;
-- 修改视图
alter view actor_name_view as
select first_name
from actor;
-- 删除视图
drop view actor_name_view;
-- 查看视图详情
SHOW CREATE VIEW actor_name_view;
5. 触发器
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end
详见:https://cloud.tencent.com/developer/article/2232342
功能点
1.mysql分页
limit:一般用法为limit X,Y意思为跳过X条数据读取Y条数据(包括X)
select * from table limit 3,6; # 跳过前三条然后取6条数据,也就是取出4-9条数据
offset:一般用法为limit x offset y,表示从y后面开始读取x条数据(不包括y)
select * from table limit 6 offset 3; # 偏移量是3所以跳过前3条数据,然后再取出后面的6条数据。
2. 生成序列号
# ROW_NUMBER():将select查询到的数据进行排序,每一条数据加一个序号,当排序字段值相同,排序也是递增的
# 标准的序列号 1 2 3 4。
SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num
FROM salaries;
# RANK()函数:顾名思义排名函数,可以对某一个字段进行排名,当排序字段值相同时,序列号也是相同的
# 后续序号是跳跃不连续的,会累加 比如 1 2 2 4
SELECT *, rank() OVER (ORDER BY salary DESC) AS rank_num
FROM salaries;
# DENSE_RANK()函数:也是排名函数,当排序字段值相同时,序列号也是相同的
# 后续序号是连续的,不会累加 比如 1 2 2 3
SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM salaries;
3. 数据复制
表中数据复制,从一个表读数据复制到另一个表
insert into actor_name(first_name, last_name)
select first_name, last_name
from actor;
4. 格式化时间
-- mysql获取当前时间,可自定义格式
select curdate(); -- 2024-02-20
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') as current_datetime; -- 2024-02-20
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_datetime; -- 2024-02-20 18:10:01
-- postgre获取当前时间,可自定义格式
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS current_datetime;
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS current_datetime;
5. 累计求和
SQL281 最差是第几名(一)
-- 累计求和
select grade, sum(`number`) over (order by grade) t_rank
from class_grade cg
案例
案例一
SQL196 查找入职员工时间排名倒数第三的员工所有信息
# --有问题,只能找出一个排名倒数第三入职的员工,可能有多个入职时间相同的情况
# --select * from employees
# --order by hire_date desc
# --limit 2, 1
select *
from employees
where hire_date = (
select distinct # -- 精确查找倒数第三个员工的入职日期
hire_date
from employees
order by hire_date desc
limit 2, 1
)
案例二
SQL206 获取每个部门中当前员工薪水最高的相关信息
# 2.根据最大薪水、部门号,进行三表关联找最大薪水的人是谁
select distinct temp.dept_no, ss.emp_no, temp.maxSalary
from (
# 1.临时表,获取每个部门最大薪水,但不知道最大薪水的人是谁
select distinct de.dept_no, max(s.salary) maxSalary
from dept_emp de
left join salaries s on de.emp_no = s.emp_no
group by de.dept_no
) temp
left join salaries ss on temp.maxSalary = ss.salary
left join dept_emp de on de.emp_no = ss.emp_no
order by temp.dept_no asc;
案例三
SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
# 1. 关联查询第二高薪水的员工信息
select e.emp_no, s.salary, e.last_name, e.first_name
from employees e
left join salaries s on e.emp_no = s.emp_no
where s.salary = (
# 1. 第二高薪水具体值
select max(salary) from salaries
where salary < (select max(salary) from salaries)
);
案例四
SQL253 获取有奖金的员工相关信息
select e.emp_no ,e.first_name ,e.last_name , eb.btype , s.salary,
( # 2. 根据字段值写IF语句
case eb.btype
when 1 then round(s.salary * 0.1,1) # 1. 四舍五入,保存一位小数
when 2 then round(s.salary * 0.2,1)
else round(s.salary * 0.3,1)
end
) as bonus # 当前工资的比例,不是总工资
from employees e
inner join emp_bonus eb on e.emp_no = eb.emp_no # 3. 关联查询,主要和leftjoin的区别
inner join salaries s on e.emp_no = s.emp_no
where s.to_date ='9999-01-01'
order by e.emp_no asc
案例五
SQL279 实习广场投递简历分析(二)
select job,
date_format(`date`, '%Y-%m') mon, -- 根据月分组,不是日
sum(num) cnt
from resume_info ri
where date_format(`date`, '%Y') = '2025' -- 2025年的数据
group by job, mon
order by mon desc ,cnt desc
案例六
SQL285 获得积分最多的人(三)
SELECT u.id, u.name, SUM(if(type='add', grade_num, -grade_num)) as total_grade
FROM user u
JOIN grade_info g ON u.id = g.user_id
GROUP BY u.id, u.name
HAVING total_grade = (
-- 用户最高分
SELECT MAX(total_grade)
FROM (
-- 每个用户的分数之和,有加有减!!
SELECT SUM(if(type='add', grade_num, -grade_num)) as total_grade
FROM grade_info
GROUP BY user_id
) as subquery
)
ORDER BY u.id ASC;
案例七
SQL255 给出employees表中排名为奇数行的first_name
-- 不能直接where rank_num % 2 = 1;
-- SELECT emp_no, first_name, ROW_NUMBER() OVER (ORDER BY first_name asc) AS rank_num
-- FROM employees e
-- WHERE rank_num % 2 = 1;
-- 在查出来的结果基础上再封装一层
select first_name from (
SELECT emp_no, first_name, ROW_NUMBER() OVER (ORDER BY first_name asc) AS rank_num -- 排序
FROM employees e ) temp
where temp.rank_num % 2 = 1
order by emp_no;
原文地址:https://blog.csdn.net/qq_21880261/article/details/136341098
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!