牛客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）！