本文记录了牛客网sql全部题目的答案与难题解析，部分题目包含多种解法，并且涵盖了开窗函数等各种语法点的理解
标题中高亮的题目，是易错题
牛客网刷题链接：牛客网sql在线练习
本文所有语句使用mysql8.0参考教程资源：
mysql教程1
mysql教程2
select * from employees
order by hire_date desc
limit 0,1;
select
*
from employees
where hire_date =
(
select
max(hire_date)
from employees
)
select
*
from employees
where hire_date =
(
select
distinct hire_date
from employees
order by hire_date desc
limit 2,1
)
select
b.*,a.dept_no
from dept_manager a left join salaries b
on a.emp_no = b.emp_no
where a.to_date='9999-01-01'
and b.to_date='9999-01-01'
ORDER BY b.emp_no
select last_name
,first_name
,dept_no
from employees inner join dept_emp
on employees.emp_no = dept_emp.emp_no
select last_name
,first_name
,dept_no
from employees left join dept_emp
on employees.emp_no = dept_emp.emp_no
-- 筛选变动次数超过15次的员工
select
emp_no
,count()
from salaries
group by emp_no
having count()>15
-- 筛选加薪次数超过15次的员工
select
a.emp_no
, count(*) as t
from salaries a
inner join salaries b
on a.emp_no=b.emp_no
where a.salaries < b.salaries
group by a.emp_no
having t>15
--使用distinct进行去重
select
distinct salary
from salaries
where to_date = '9999-01-01'
order by salary desc
-- 使用groupby进行去重
select
salary
from salaries
where to_date = '9999-01-01'
group by salary
order by salary desc
-- 使用join+is null
select
employees.emp_no
from employees
left join dept_manager
on employees.emp_no = dept_manager.emp_no
where dept_manager.dept_no is null
-- 使用子查询
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
is null / is not null,不能使用!=null
select
a.emp_no
,b.emp_no
from dept_emp a inner join dept_manager b
on a.dept_no = b.dept_no
where a.emp_no != b.emp_no
and a.to_date = '9999-01-01'
AND b.to_date = '9999-01-01'
-- 使用子查询方法
select
d1.dept_no
,d1.emp_no
,s1.salary
from salaries s1 inner join dept_emp d1
on s1.emp_no = d1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
where s1.salary in
(
select max(s2.salary)
from salaries s2 inner join dept_emp d2
on s2.emp_no = d2.emp_no
AND d2.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND d2.dept_no = d1.dept_no -- 利用内查询获取外查询的部门编号
)
order by d1.dept_no asc
方法2
-- 使用开窗函数
select
t.dept_no
,t.emp_no
,t.salary
from
(
select
d.dept_no
,d.emp_no
,s.salary
,dense_rank() over (partition by d.dept_no order by s.salary desc) as rk
from dept_emp d inner join salaries s
on d.emp_no = s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
) t
where t.rk = 1
select
*
from employees
where emp_no%2=1
and last_name != ‘Mary’
order by hire_date desc
```
select
title
,avg(salary)as avg_salary
from salaries s inner join titles t
on s.emp_no = t.emp_no
where s.to_date='9999-01-01'
group by title
order by avg_salary
select
emp_no
,salary
from salaries
order by salary desc
limit 1,1
-- 使用子查询
select
s.emp_no
,s.salary
,e.last_name
,e.first_name
from employees e
inner join salaries s
on e.emp_no = s.emp_no
where s.salary =
(
select
max(salary) -- 2 第二高工资
from salaries
where salary<>
(
select max(salary) -- 1查出最高工资
from salaries
where to_date='9999-01-01'
)
)
and s.to_date = '9999-01-01'
-- 使用自连接
select s.emp_no
, s.salary
, e.last_name
, e.first_name
from salaries s
join employees e
on s.emp_no = e.emp_no
where s.salary =
(
select s1.salary
from salaries s1 join salaries s2 -- 自连接查询
on s1.salary <= s2.salary
group by s1.salary -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
having count(distinct s2.salary) = 2 -- (去重之后的数量就是对应的名次)
)
and s.to_date = '9999-01-01'
select last_name
,first_name
,dept_name
from employees e
left join dept_emp d
on e.emp_no = d.emp_no
left join departments
on d.dept_no = departments.dept_no
select
a.emp_no
,b.salary - a.salary as growth
from salaries a
inner join employees e
on e.emp_no=a.emp_no
and e.hire_date = a.from_date
inner join salaries b
on a.emp_no = b.emp_no
where b.to_date= '9999-01-01'
order by growth
第一次join筛选出入职时的工资
第二次自连接筛选出，通过where筛选出当前的工资
select
de.dept_no
,de.dept_name
,count(*) as sum
from salaries s
join dept_emp d
on s.emp_no = d.emp_no
right join departments de
on d.dept_no = de.dept_no
group by de.dept_name,de.dept_no
order by de.dept_no
select
emp_no
,salary
,dense_rank() over(order by salary desc) t_rank
from salaries
order by t_rank,emp_no
select
d.dept_no
,d.emp_no
,s.salary
from salaries s
join dept_emp d
on s.emp_no = d.emp_no
and s.to_date = '9999-01-01'
where s.emp_no not in
(
select
emp_no
from dept_manager de
where de.to_date = '9999-01-01'
)
select
emp_no
,manager_no
,emp_salary
,manager_salary
from
(
select
s1.emp_no as emp_no
,s1.salary as emp_salary
,dep1.dept_no
from salaries s1
join dept_emp dep1
on s1.emp_no = dep1.emp_no
where s1.emp_no not in
(
select emp_no
from dept_manager der
where der.to_date = '9999-01-01'
)
and s1.to_date = '9999-01-01'
)tem1-- 非领导的工资
join
(
select
s2.emp_no as manager_no
,s2.salary as manager_salary
,dep2.dept_no
from salaries s2
join dept_emp dep2
on dep2.emp_no = s2.emp_no
where s2.emp_no in
(
select emp_no
from dept_manager der
where der.to_date = '9999-01-01'
)
and s2.to_date = '9999-01-01'
) tem2 -- 部门领导的工资
on tem1.dept_no = tem2.dept_no -- 按部门进行连接
where emp_salary>manager_salary -- 筛选工资高低
select de.emp_no,
dm.emp_no as manager_no,
s1.salary as emp_salary,
s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'
and s1.salary>s2.salary
select de.dept_no
,dep.dept_name
,title
,count(*)
from dept_emp de
join titles ti
on ti.emp_no = de.emp_no
and de.to_date = '9999-01-01'
join departments dep
on de.dept_no = dep.dept_no
group by de.dept_no,title
order by de.dept_no
select
c.name
,count(f.film_id) as film_num
from film_category fc
join category c
on fc.category_id = c.category_id
join film f
on fc.film_id = f.film_id
where f.description like '%robot%'
and fc.category_id in
(
select category_id
from film_category
group by category_id
having count(*)>=5
)
group by c.name
SELECT
c.name
, COUNT(fc.film_id)
FROM
(
select
category_id
, COUNT(film_id) AS category_num
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5
) AS cc
,film AS f
, film_category AS fc
, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
select
f.film_id
,title
from film f
left join film_category fc
on f.film_id = fc.film_id
where category_id is null
select
title
,description
from film f
join film_category fc
on f.film_id = fc.film_id
join category c
on c.category_id = fc.category_id
where name = 'Action'
select
concat(last_name,' ' ,first_name) name
from employees
CREATE TABLE
IF NOT EXISTS actor ( -- 判断是否已存在
actor_id smallint(5) NOT NULL PRIMARY KEY, -- 设置主键
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update date not null default current_timestamp -- 设置默认时间
)
当前日期：CURDATE(),CURRENT_DATE
当前时间：now()语句开始执行的时间,sysdate()语句执行到的时间
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')
insert ignore into actor(
actor_id
,first_name
,last_name
,last_update)
values
(
'3','ED','CHASE','2006-02-15 12:34:33'
)
create table if not exists actor_name
(
first_name varchar(45) not null comment '名称',
last_name varchar(45) not null comment '姓氏'
);
insert into actor_name(first_name
,last_name)
(select
first_name
,last_name
from actor )
alter table actor
add unique index uniq_idx_firstname(first_name);
alter table actor
add index idx_lastname(last_name)
-- 1通过create创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE] -- 索引数据类型，可以省略
ON table_name (column_name [(length)] [ASC | DESC],...) -- 可以添加多个字段，并指定排序方式
-- 索引的类型 PRIMARY KEY，KEY，UNIQUE或INDEX，当创建表时，若声明了主键或者unique，会自动生成索引
-- 2 通过alter table创建索引
ALTER TABLE tbl_name ADD [PRIMARY KEY/UNIQUE INDEX indexname/fulltext indexname] (col_list);
-- 3删除索引
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;
CREATE VIEW actor_name_view
AS
SELECT first_name as first_name_v
, last_name as last_name_v
FROM actor
CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name]
AS
[SELECT statement]
-- 使用条件：
-- SELECT语句可以在WHERE子句中包含子查询，但FROM子句中的不能包含子查询。
-- SELECT语句不能引用任何变量，包括局部变量，用户变量和会话变量。
-- SELECT语句不能引用准备语句的参数。
SELECT
*
FROM salaries
FORCE INDEX(idx_emp_no )
WHERE emp_no = 10005
FORCE INDEX(idx_name )可以指定查询时使用的索引alter table actor
add column create_date datetime not null default '2020-10-1 00:00:00'
create trigger audit_log
after insert
on employees_test
for each row
begin
insert into audit(EMP_no,NAME)
values(NEW.ID,NEW.NAME);-- 注意加分号，表示语句的结束
end
delete
from titles_test
where id not in
(
select id
from
(
select min(id) as id
from titles_test
group by emp_no
) as a
)
注意，在删除或者跟更新时，条件中的子查询不能使用更新或者删除的表本身，必须使用别名的方式，将子查询存储为新表
DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;
使用order by 和limit也可以约束删除的范围
当删除表中全部数据时，使用truncate table table_name更加高效
update titles_test
set to_date = null,
from_date='2001-01-01'
where to_date = '9999-01-01'
UPDATE titles_test
SET emp_no =
REPLACE(emp_no, 10001, 10005)
WHERE id = 5
replace into titles_test
select
5
, 10005
, title
, from_date
, to_date
from titles_test
where id = 5;
REPLACE INTO table_name(column_list)
VALUES(value_list)
REPLACE INTO table
SET column1 = value1,
column2 = value2
REPLACE INTO cities(name,population)
SELECT name,population FROM cities
WHERE id = 1
alter table titles_test
rename to titles_2017
alter的常用方法：
ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
ALTER TABLE 表名 RENAME TO/AS 新表名;
ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
ALTER TABLE 表名 CHANGE 列名和定义都可以改变。
alter table audit
add foreign key fk (emp_no)
references employees_test(id)
外键简介：https://www.yiibai.com/mysql/foreign-key.html
外键约束语法：
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
action可以是CASCADE创建级联删除或者级联更新
action是RESTRICT或者no action时，不允许删除或者更新
action是SET NULL时，删除或更新父表时，子表被设置为null
update salaries
set
salary=1.1*salary
where
to_date = '9999-01-01'
and emp_no in
(
select emp_no
from emp_bonus
)
select concat(last_name,'\'',first_name)
from employees
select
char_length('A,10,B')- char_length(
replace('10,A,B',',','')
);
select first_name
from employees
order by right(first_name,2)
常见的字符串函数
字符串处理函数
Lower(列名）
Upper(列名）
POSITION(字符 IN 列名) mysql
PATINDEX(’%s1%’, s2) sqlserver
CONCAT(列名, ’ is in ', 列名…)
replace(列名，旧字符串，新字符串）
left (列名，位数） 返回字符串列中左边多少位的字符
使用转义字符+特殊字符
SUBSTRING(列名 , 开始位 ,字符长度 ) mysql
select
dept_no
,group_concat(emp_no separator ',')
from dept_emp
group by dept_no
order by dept_no
group_concat函数使用方法：链接
使用语法：
GROUP_CONCAT(DISTINCT expression
ORDER BY expression
SEPARATOR sep);
按照orderby的顺序，用sep分隔符，连接每个group中的字符串
select
avg(salary) as 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'
)
SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary
FROM salaries
where to_date = '9999-01-01';
select
*
from
employees
order by emp_no
limit 5,5
select
distinct
e.emp_no
,d.dept_no
,btype
,received
from employees e
inner join dept_emp d
on e.emp_no = d.emp_no
left join emp_bonus b
on d.emp_no = b.emp_no
select
*
from employees e
where
not exists
(
select *
from dept_emp d
where d.emp_no = e.emp_no
)
select
b.emp_no
,first_name
,last_name
,btype
,salary
, case when btype = 1
then salary*0.1
when btype = 2
then salary*0.2
else salary * 0.3
end
as bonus
from emp_bonus b
left join employees e
on b.emp_no = e.emp_no
join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
case语法
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
END CASE
select emp_no
,salary
,sum(salary) over(order by emp_no) as running_total
from salaries
where to_date = '9999-01-01'
sum(xx) over (order by xx)
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;
select
number
from
grade
group by number
having count(*)>2
select
id
,number
,dense_rank() over(order by number desc) as t_rank
from passing_number
order by t_rank,id
select
p.id
,name
,content
from person p
left join task t
on p.id = t.person_id
select
e.date
, round(AVG(
case e.type when 'no_completed' then 1
else 0
end
)
,3
)as p
from email e
join `user` u1
on e.receive_id = u1.id
join `user` u2
on e.send_id = u2.id
where u1.is_blacklist = 0
and u2.is_blacklist = 0
group by e.date
order by e.date
select
user_id
,max(`date`) as d
from login
group by user_id
order by user_id
select distinct
user_id,
last_value(date)
over(partition by user_id -- 按照user_id进行分组
order by date -- 组内按照date进行排序
rows between current row and unbounded following -- 计算时，从当前行到组内最后一行
) as d
from login;
window_function_name(expression)
OVER (
[partition_defintion]
[order_definition]
[frame_definition]
)
frame_definition就是在partition内，取每一行的时候，在组内计算时，选择的计算范围
/*开窗函数分组求最大时间，然后用子查询筛选*/
select n.un u_n, n.cn c_n,n.d
from(
select u.name un
, c.name cn
, l.date,
(max(l.date) over(partition by l.user_id)) d
from login l,user u,client c
where l.user_id=u.id
and c.id=l.client_id
) n
where n.date=n.d
order by u_n;
select
round(
(
select
count(*) -- 通过where找到第二天仍然登陆的用户记录数
from
(
select -- 1找到初始登陆日期
user_id
,l.date
,min(date) over(partition by user_id) as first_date
from login l
) as temp1
where temp1.date = date_add(first_date,interval 1 day)
)
/
(
select -- 统计用户总数
count(distinct user_id)
from login
)
,3)
as p
SELECT
ROUND(
COUNT(DISTINCT user_id)*1.0
/( -- 总用户量
SELECT
COUNT(DISTINCT user_id)
FROM login
)
, 3)
FROM login
WHERE (user_id, date) -- 筛选出所有第二天登陆的用户
IN
( -- 如果第二天登陆的日期和id
SELECT user_id
, DATE_ADD(MIN(date),INTERVAL 1 DAY)
FROM login GROUP BY user_id
);
date_add函数的使用：链接
DATE_ADD(start_date, INTERVAL expr unit)
常用unit
second,minute,hour,day,month,year
select
date
,sum(
case tem.rk
when tem.rk = 1 then 1
else 0
end
) as new
from
(
select
user_id
,date
,row_number() over(partition by user_id order by `date`) as rk
from login
) as tem
group by tem.date
select
log_today.date
,round(count(distinct log_nextday.user_id)-- 当天新用户的数量
/
count(distinct log_today.user_id)-- 第二天重新登录的新用户数量
,3) as p
from login as log_today
left join(-- 注意要使用左连接
select -- 1 将日期向前移动一天，从而进行表连接，判断第二天留存率
user_id
,date_sub(date,interval 1 day) as date
from login
) as log_nextday
on log_today.date = log_nextday.date
and log_today.user_id = log_nextday.user_id
where (log_today.user_id,log_today.date) -- 筛选每天登陆的新用户，就是这里把没有新用户登陆的天给筛选掉了，因此需要union
in (
select user_id
,min(date)
from login
group by user_id
)
group by log_today.date
union -- 将没有新用户的登陆的天，补充留存率为零
select date
,0.000 as p
from login
where date not in
(
select
min(date)
from login
group by user_id
)
order by date
select
u.name as u_n
,login.date
,ps_num
from
(
select
user_id
,date
,sum(number) over(partition by user_id order by date) as ps_num
from passing_number
) as pas
join login
on login.user_id = pas.user_id
and login.date = pas.date
join `user` u
on u.id = login.user_id
order by date,u_n
select
job
,round(avg(score),3) as avg_score
from
grade
group by job
order by avg_score desc
select
g1.*
from
grade g1
join (
select
id,
avg(score) over(partition by job) avg_score
from grade
) g2
on g1.id = g2.id
where g1.score > g2.avg_score
order by id
select
g.id
,l.name
,g.score
from grade g
join `language` l
on g.language_id = l.id
join (
select
id
,dense_rank() over(partition by language_id order by score desc) as rk
from grade
) as tem
on tem.id = g.id
where tem.rk in (1,2)
order by name asc,score desc
select
job
,floor(( count(*) + 1 )/ 2 ) AS `start`
,floor(( count(*) + 2 )/ 2 ) AS 'end'
from grade
group by job
order by job
select
g1.id
,g1.job
,g1.score
,g2.rk as t_rank
from grade g1
join (
select
id
,row_number() over(partition by job order by score desc) as rk-- 每条记录的排序
,count(id) over(partition by job rows between unbounded preceding and unbounded following) as num -- 每个job的记录数量
from grade
) as g2
on g1.id = g2.id
where -- 判断中位数
g2.rk = floor((g2.num + 1 )/ 2 )
or g2.rk = floor((g2.num + 2 )/ 2 )
order by id
er_id
order by date,u_n
## 72普通分组求平均
```mysql
select
job
,round(avg(score),3) as avg_score
from
grade
group by job
order by avg_score desc
select
g1.*
from
grade g1
join (
select
id,
avg(score) over(partition by job) avg_score
from grade
) g2
on g1.id = g2.id
where g1.score > g2.avg_score
order by id
select
g.id
,l.name
,g.score
from grade g
join `language` l
on g.language_id = l.id
join (
select
id
,dense_rank() over(partition by language_id order by score desc) as rk
from grade
) as tem
on tem.id = g.id
where tem.rk in (1,2)
order by name asc,score desc
select
job
,floor(( count(*) + 1 )/ 2 ) AS `start`
,floor(( count(*) + 2 )/ 2 ) AS 'end'
from grade
group by job
order by job
select
g1.id
,g1.job
,g1.score
,g2.rk as t_rank
from grade g1
join (
select
id
,row_number() over(partition by job order by score desc) as rk-- 每条记录的排序
,count(id) over(partition by job rows between unbounded preceding and unbounded following) as num -- 每个job的记录数量
from grade
) as g2
on g1.id = g2.id
where -- 判断中位数
g2.rk = floor((g2.num + 1 )/ 2 )
or g2.rk = floor((g2.num + 2 )/ 2 )
order by id