牛客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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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;
1
2
3
4
5
6
7
8
9
10
11
12
13

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;
1
2
3
4
5
6
7
8
9
10
11
12

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
2
3
4
5
6
7

功能点

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条数据。
1
2
3
4

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3. 数据复制

表中数据复制，从一个表读数据复制到另一个表
insert into actor_name(first_name, last_name)
select first_name, last_name
from actor;
1
2
3
4

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;
1
2
3
4
5
6
7
8

5. 累计求和

SQL281 最差是第几名(一)

-- 累计求和
select grade, sum(`number`) over (order by grade) t_rank
from class_grade cg 
1
2
3
4
5

案例

案例一

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
)
1
2
3
4
5
6
7
8
9
10
11
12
13

案例二

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;
1
2
3
4
5
6
7
8
9
10
11
12

案例三

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)
);
1
2
3
4
5
6
7
8
9

案例四

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 
1
2
3
4
5
6
7
8
9
10
11
12
13

案例五

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
1
2
3
4
5
6
7

案例六

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

案例七

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;

1
2
3
4
5
6
7
8
9
10
11
12