牛客SQL总结
建表
SQL36
![image-20210929111953766](http://jason-qianhao.xyz/image-20210929111953766.png)
create table actor_name
(
first_name varchar(45) not null,
last_name varchar(45) not null
) as
select first_name,last_name
from actor
SQL39
![image-20210929112125985](http://jason-qianhao.xyz/image-20210929112125985.png)
select *
from salaries
force index (idx_emp_no)
where emp_no=10005
SQL40
![image-20210929112210893](http://jason-qianhao.xyz/image-20210929112210893.png)
ALTER TABLE actor ADD create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00'
SQL41(触发器)
![image-20211008125722460](http://jason-qianhao.xyz/image-20211008125722460.png)
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end
SQL46
![image-20211008145453608](http://jason-qianhao.xyz/image-20211008145453608.png)
ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);
查询
最值
SQL2
![image-20210928160517266](http://jason-qianhao.xyz/image-20210928160517266.png)
select
*
from
employees
order by
hire_date desc
limit 2, 1;
主页limit第一参数为(页数 - 1)*每页数量
去重
SQL8
![image-20210928161305190](http://jason-qianhao.xyz/image-20210928161305190.png)
SELECT
DISTINCT salary
FROM
salaries
ORDER BY
salary desc
多表查询
SQL10
![image-20210928161526148](http://jason-qianhao.xyz/image-20210928161526148.png)
SELECT
employees.emp_no
FROM
employees
left JOIN
dept_manager
ON
dept_manager.emp_no = employees.emp_no
WHERE
dept_manager.emp_no is null
SQL11
![image-20210928161633124](http://jason-qianhao.xyz/image-20210928161633124.png)
SELECT
e.emp_no,
m.emp_no as manager
FROM
dept_emp e
JOIN
dept_manager m
on
e.dept_no = m.dept_no
WHERE
e.emp_no != m.emp_no
group BY
e.dept_no
SQL12
![image-20220308115000735](http://jason-qianhao.xyz/image-20220308115000735.png)
![image-20220308115020098](http://jason-qianhao.xyz/image-20220308115020098.png)
select
t.dept_no,
s.emp_no,
t.maxSalary
from
(
select
d.dept_no,
max(s.salary) as maxSalary
from
dept_emp d,
salaries s
where
d.emp_no = s.emp_no
group by
d.dept_no
) as t,
salaries s,
dept_emp d
where
t.maxSalary = s.salary and
t.dept_no = d.dept_no and
d.emp_no = s.emp_no
order by
t.dept_no asc
SQL19
![image-20210928162304345](http://jason-qianhao.xyz/image-20210928162304345.png)
SELECT
e.last_name,
e.first_name,
d.dept_name
FROM
employees e
LEFT JOIN
dept_emp de
ON
e.emp_no = de.emp_no
LEFT JOIN
departments d
ON
de.dept_no = d.dept_no
SQL21
![image-20210929105618744](http://jason-qianhao.xyz/image-20210929105618744.png)
-- 本题应该考虑降薪的问题:题中没有讲员工不降薪,所以不能直接使用最大薪水-最小薪水求值
SELECT
s1.emp_no,
s2.salary - s1.salary AS growth
FROM
(SELECT
e.emp_no,
s.salary
FROM
employees e
LEFT JOIN salaries s
ON e.emp_no = s.emp_no
WHERE e.hire_date = s.from_date) AS s1 -- 入职薪水
INNER JOIN
(SELECT
e.emp_no,
s.salary
FROM
employees e
LEFT JOIN salaries s
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
) AS s2 -- 现在薪水
ON s1.emp_no = s2.emp_no
ORDER BY growth
SQL22
![image-20210929105949261](http://jason-qianhao.xyz/image-20210929105949261.png)
SELECT
d.dept_no,
d.dept_name,
t.sum
FROM
(
SELECT
de.dept_no,
COUNT(*) as "sum"
FROM
dept_emp de
JOIN
salaries s
ON
de.emp_no = s.emp_no
GROUP BY
de.dept_no
) as t
JOIN
departments d
ON
d.dept_no = t.dept_no
SQL25
![image-20210929110544985](http://jason-qianhao.xyz/image-20210929110544985.png)
SELECT
de.emp_no,
t.manager_no,
s.salary as emp_salary,
t.manager_salary
from
dept_emp de
JOIN
(
SELECT
dm.emp_no as manager_no,
dm.dept_no,
s.salary as manager_salary
FROM
dept_manager dm
JOIN
salaries s
ON
dm.emp_no = s.emp_no
) as t
ON
t. dept_no = de.dept_no
JOIN
salaries s
ON
de.emp_no = s.emp_no
WHERE
s.salary > t.manager_salary
SQL57
![image-20211014110619032](http://jason-qianhao.xyz/image-20211014110619032.png)
select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);
SQL59
![image-20211014110731335](http://jason-qianhao.xyz/image-20211014110731335.png)
SELECT
e.emp_no,
e.first_name,
e.last_name,
eb.btype,
s.salary,
CASE
WHEN eb.btype = 1 THEN s.salary * 0.1
WHEN eb.btype = 2 THEN s.salary * 0.2
ELSE s.salary * 0.3
END bonus
FROM
emp_bonus AS eb,
employees AS e,
salaries AS s
WHERE
eb.emp_no = e.emp_no AND
s.emp_no = e.emp_no AND
s.to_date='9999-01-01';
SQL64
![image-20211014111431630](http://jason-qianhao.xyz/image-20211014111431630.png)
SELECT
p.id,
p.name,
t.content
from
person p
left join
task t
on
p.id = t.person_id
SQL82
![image-20211019162627904](http://jason-qianhao.xyz/image-20211019162627904.png)
SELECT
o.id,
o.is_group_buy,
case o.is_group_buy
when 'No' then c.name
else 'None'
end client_name
FROM
order_info as o
JOIN
(
SELECT
user_id
FROM
order_info
where
date > '2025-10-15' and
status = 'completed' and
product_name in ('C++', 'Java', 'Python')
GROUP by
user_id
HAVING
COUNT(*) >= 2
) as t
ON
o.user_id = t.user_id
left join
client as c
on
o.client_id = c.id
where
o.date > '2025-10-15' and
o.status = 'completed' and
o.product_name in ('C++', 'Java', 'Python')
SQL83
![image-20211019162736374](http://jason-qianhao.xyz/image-20211019162736374.png)
select
case o.client_id
when 0 then 'GroupBuy'
else c.name
end source,
o.cnt
FROM
(
select
client_id,
count(*) cnt
from
(
SELECT
user_id
FROM
order_info
WHERE
date > '2025-10-15' and
status = 'completed' and
product_name in ('C++', 'Java', 'Python')
GROUP BY
user_id
HAVING
COUNT(*) >= 2
) as t,
order_info
where
t.user_id = order_info.user_id and
order_info.date > '2025-10-15' and
order_info.status = 'completed' and
order_info.product_name in ('C++', 'Java', 'Python')
group by
order_info.client_id
) o
left JOIN
client as c
ON
o.client_id = c.id
order BY
source
SQL86
![image-20211019163146236](http://jason-qianhao.xyz/image-20211019163146236.png)
-- 查询在2025年投递简历的每个岗位,每个月内收到简历的数量和,对应的2026年的同月同岗位收到简历的数量,最后的结果先按first_year_mon月份降序,再按job降序排序显示
SELECT h1.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
FROM
-- 2025年的
(SELECT job,DATE_FORMAT(DATE,'%Y-%m') AS first_year_mon,SUM(num) AS first_year_cnt
FROM resume_info
WHERE DATE LIKE '2025%' -- 符合最左前缀匹配原则,也走索引
GROUP BY job,first_year_mon) AS h1
-- inner join 假装优化下,inner可以省略
INNER JOIN
-- 2026年的
(SELECT job,DATE_FORMAT(DATE,'%Y-%m') AS second_year_mon,SUM(num) AS second_year_cnt
FROM resume_info
WHERE DATE LIKE '2026%' -- 符合最左前缀匹配原则,也走索引
GROUP BY job,second_year_mon) AS h2
-- 表连接条件:两表job相同且月份相同,
-- 因date日期类型经过 DATE_FORMAT()后变成 字符串,所以使用right()函数取后两位即为月数
ON h1.job=h2.job AND right(first_year_mon,2)=right(second_year_mon,2)
-- 排序
ORDER BY first_year_mon DESC,h1.job DESC;
SQL90
![image-20211021110055820](http://jason-qianhao.xyz/image-20211021110055820.png)
select
user.id,
user.name,
t.grade_sum
from
(
select
user_id,
sum(grade_num) as grade_sum
from
grade_info
group by
user_id
having
grade_sum =(
select
sum(grade_num) as grade_sum
from
grade_info
group by
user_id
order by
grade_sum desc limit 1
)
) t
join
user
on t.user_id=user.id
order by
user.id
SQL91
![image-20211021110222209](http://jason-qianhao.xyz/image-20211021110222209.png)
select
user.id,
user.name,
t.grade_sum
from
(
select
user_id,
sum(case type when 'add' then grade_num else -grade_num end) as grade_sum
from
grade_info
group by user_id
having
grade_sum=(
select
sum(case type when 'add' then grade_num else -grade_num end ) as grade_sum
from
grade_info
group by
user_id
order by
grade_sum desc
limit 1)
) t
join user
on t.user_id=user.id
order by user.id
排名序号
SQL23
![image-20210929110105165](http://jason-qianhao.xyz/image-20210929110105165.png)
SELECT
s1.emp_no,
s1.salary,
COUNT(DISTINCT s2.salary) as t_rank
FROM
salaries s1
JOIN
salaries s2
ON
s1.salary <= s2.salary
GROUP BY
s1.emp_no
ORDER BY
t_rank
SQL63
![image-20211014111320786](http://jason-qianhao.xyz/image-20211014111320786.png)
SELECT
p1.id,
p1.number,
count(distinct p2.number) as t_rank
FROM
passing_number p1
JOIN
passing_number p2
ON
p1.number <= p2.number
GROUP BY
id, number
order BY
t_rank
SQL81
![image-20211019162524334](http://jason-qianhao.xyz/image-20211019162524334.png)
SELECT
a.user_id ,
a.first_buy_date ,
c.date ,
a.cnt
FROM
(
SELECT
user_id ,
MIN(date) first_buy_date ,
COUNT(*) cnt
FROM
order_info
WHERE
date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++' , 'Java' , 'Python')
GROUP BY
user_id
HAVING
COUNT(*) >= 2
) a
JOIN
(
SELECT
user_id ,
date
FROM
(
SELECT
user_id ,
date ,
RANK() OVER(PARTITION BY user_id ORDER BY date) rnk
FROM
order_info
WHERE
date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++' , 'Java' , 'Python')
) b
WHERE
b.rnk = 2
) c
ON
a.user_id = c.user_id
ORDER BY
a.user_id;
SQL87
![image-20211019163337353](http://jason-qianhao.xyz/image-20211019163337353.png)
select grade, sum(number) over(order by grade) t_rank
from class_grade
order by grade;
函数
聚合函数
SQL16
![image-20210928162059085](http://jason-qianhao.xyz/image-20210928162059085.png)
SELECT
t.title,
avg(s.salary)
FROM
titles t
JOIN
salaries s
ON
t.emp_no = s.emp_no
GROUP BY
t.title
ORDER BY
avg(s.salary)
SQL54
![image-20211014110031225](http://jason-qianhao.xyz/image-20211014110031225.png)
SELECT
AVG(salary)
FROM
salaries
WHERE
to_date = '9999-01-01' and
salary not in (
select
max(salary)
from
salaries
where to_date = '9999-01-01'
) and
salary not in(
select
min(salary)
from
salaries
where to_date = '9999-01-01'
)
SQL62
![image-20211014111141842](http://jason-qianhao.xyz/image-20211014111141842.png)
select number from grade group by number having count(*)>=3
系统函数
SQL32
![image-20210929111619847](http://jason-qianhao.xyz/image-20210929111619847.png)
SELECT
CONCAT(last_name, ' ', first_name)
from
employees
SQL51
![image-20211012164126524](http://jason-qianhao.xyz/image-20211012164126524.png)
SELECT
LENGTH("10,A,B") - LENGTH(REPLACE("10,A,B", ",", "")) as cnt;
SQL52
![image-20211012164214515](http://jason-qianhao.xyz/image-20211012164214515.png)
SELECT
first_name
FROM
employees
ORDER BY
SUBSTR(first_name, LENGTH(first_name) - 1, LENGTH(first_name));
SQL53
![image-20211014105909840](http://jason-qianhao.xyz/image-20211014105909840.png)
select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no
SQL60
![image-20211014110905479](http://jason-qianhao.xyz/image-20211014110905479.png)
# 方法一
SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary)
FROM salaries AS s2
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no
# 方法二
select
emp_no,
salary,
sum(salary) over(order by emp_no) as running_total
from
salaries
where
to_date = '9999-01-01'
SQL65
![image-20211014111602913](http://jason-qianhao.xyz/image-20211014111602913.png)
select
email.date,
round(
sum(
case email.type
when'completed' then 0
else 1
end) * 1.0 / count(email.type), 3) as p
from
email
join
user as u1
on
email.send_id=u1.id and
u1.is_blacklist=0
join
user as u2
on
email.receive_id=u2.id and
u2.is_blacklist=0
group by
email.date
order by
email.date;
SQL69
![image-20211018142049652](http://jason-qianhao.xyz/image-20211018142049652.png)
select
distinct date,
sum(
case
when (user_id,date) in
(select
user_id,
min(date)
from
login
group by user_id)
then 1
else 0
end)
from login
group by date
order by date;
SQL70
![image-20211018142212720](http://jason-qianhao.xyz/image-20211018142212720.png)
select
date,
ifnull(
round(
(sum(
case
when (user_id,date) in
(select
user_id,
date_add(date,interval -1 day)
from login) and
(user_id,date)in
(select
user_id,
min(date)
from login
group by user_id)
then 1
else 0
end))/
(sum(
case
when (user_id,date)in
(select
user_id,
min(date)
from login
group by user_id)
then 1
else 0
end)),3),0)as p
from login
group by date
order by date;
SQL85
![image-20211019162856125](http://jason-qianhao.xyz/image-20211019162856125.png)
-- 按月统计数量并排序
select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where date like '2025%' -- 符合最左前缀匹配原则,也走索引
group by job,mon
order by mon desc,cnt desc;
窗口函数
SQL61
![image-20211014111026521](http://jason-qianhao.xyz/image-20211014111026521.png)
SELECT
e.first_name
FROM employees e JOIN
(
SELECT
first_name
, ROW_NUMBER() OVER(ORDER BY first_name ASC) AS r_num
FROM employees
) AS t
ON e.first_name = t.first_name
WHERE t.r_num % 2 = 1;
SQL68
![image-20211018141757866](http://jason-qianhao.xyz/image-20211018141757866.png)
select
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);
SQL71
![image-20211018142622482](http://jason-qianhao.xyz/image-20211018142622482.png)
select u.name as u_n
,p.date as date
,sum(p.number)over(partition by u.name order by date)as ps_num
from passing_number p,user u
where p.user_id=u.id
order by p.date,u.name;
SQL74
![image-20211018142935185](http://jason-qianhao.xyz/image-20211018142935185.png)
SELECT a.id, l.name, a.score
FROM
language AS l
JOIN
(SELECT id, language_id, score, dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num
FROM grade)a
ON l.id = a.language_id
WHERE rank_num <=2
ORDER BY l.name, a.score DESC, a.id;
SQL76
![image-20211018143208316](http://jason-qianhao.xyz/image-20211018143208316.png)
select
id
,g.job as job
,score
,rk
from (
select
id
,job
,score
,row_number() over(
partition by job
order by score desc
) as rk
from grade
) g
join (
select
job
,floor((count(id) + 1)/ 2) as m1
,ceiling((count(id) + 1)/ 2) as m2
from grade
group by job
) m
on g.job = m.job
where rk between m1 and m2 -- 过滤,保证row_number处于中位数位置
order by id;
SQL88
![image-20211021105800133](http://jason-qianhao.xyz/image-20211021105800133.png)
select grade
from
(select grade,
(select sum(number) from class_grade) as total,
sum(number)over(order by grade) a, -- 求正序
sum(number)over(order by grade desc) b -- 求逆序
from class_grade
order by grade)t
where a >= total/2 and b >= total/2 -- 正序逆序均大于整个数列数字个数的一半
order by grade;
SQL89
![image-20211021105926913](http://jason-qianhao.xyz/image-20211021105926913.png)
select
u.name,
g.grade
from
(
select
user_id,
sum(grade_num)over(partition by user_id) grade
from
grade_info
order by
grade desc
limit 1
)g
join
user as u
on g.user_id = u.id
分页
SQL55
![image-20211014110439716](http://jason-qianhao.xyz/image-20211014110439716.png)
SELECT
*
FROM
employees
limit 5, 5
插入
SQL34
![image-20210929111755733](http://jason-qianhao.xyz/image-20210929111755733.png)
INSERT INTO actor(actor_id,
first_name,
last_name,
last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
SQL35
![image-20210929111844515](http://jason-qianhao.xyz/image-20210929111844515.png)
# mysql中常用的三种插入数据的语句:
# insert into表示插入数据,数据库会检查主键,如果出现重复会报错;
# replace into表示插入替换数据,需求表中有PrimaryKey,
# 或者unique索引,如果数据库已经存在数据,则用新数据替换,如果没有数据效果则和insert into一样;
# insert ignore表示,如果中已经存在相同的记录,则忽略当前新数据;
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");
删除
SQL42
![image-20211008143658302](http://jason-qianhao.xyz/image-20211008143658302.png)
DELETE
t2.*
FROM
`titles_test` t1,
`titles_test` t2
WHERE
t1.emp_no = t2.emp_no and
t1.id < t2.id
更新
SQL48
![image-20211008145613875](http://jason-qianhao.xyz/image-20211008145613875.png)
UPDATE
salaries
SET
salary = salary + salary * 0.1
WHERE
emp_no in (
select
emp_no
from
emp_bonus
) and
to_date = '9999-01-01'