【牛客网】刷题【SQL篇】

一、入门

• 查找最晚入职员工的所有信息

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)

【注】limit m,n 表示找到第m+1条开始的n条记录。（只有一个参数时，limit n 表示找到排序之后的前n条）

• 查找所有已经分配部门的员工的last_name和first_name以及dept_no

select e.last_name , e.first_name , d.dept_no from employees e
inner join dept_emp d
on d.emp_no=e.emp_no

• 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

select emp_no,count(salary) t from salaries group by emp_no having t>15

• 找出所有员工具体薪水salary情况

select distinct salary from salaries order by salary desc 

•  找出所有非部门领导的员工emp_no

select e.emp_no from employees e left join dept_manager d
on  e.emp_no=d.emp_no
where dept_no is Null

• 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees e
where emp_no % 2 = 1 and last_name <> 'Mary' order by hire_date desc

• 获取薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary from salaries order by salary desc limit 1,1

• 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

select concat(last_name,' ',first_name) Name from employees

【注】concat()函数 ，连接多个字符串（字符串要加 ' ' ，也可以是连接两个列，此时不用加 ' ' ）

• 批量插入数据

insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
                        (2,'NICK','WAHLBERG','2006-02-15 12:34:33')

• 删除emp_no重复的记录，只保留最小的id对应的记录

delete from titles_test where id not in(
select * from (select min(id) from titles_test
group by emp_no) a)

【注】1.mysql不允许在查询的同时删除原始数据，需要给他们取别名之后再删除

           2.本题，min()函数返回一组值中的最小值，与group by结合使用，可以获得每个组的最小             值，保留最小的id对应的记录，其他全部删除

• 将所有to_date为9999-01-01的全部更新为NULL，且from_date更新为2021-01-01

update titles_test set
to_date=NULL ,from_date='2001-01-01'
where to_date='9999-01-01'

• 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不不变，使用replace实现，直接使用update会报错

update titles_test set emp_no= replace(emp_no,10001,10005)
where id=5

【注】使用replace()函数更新表，有三个参数：字段名称，需要被修改值，修改后的值

• 将title_test表名修改为titles_2017

alter table titles_test rename to titles_2017

【注】rename as 也可以

• 查询出现三次以及三次以上的积分

select number from grade 
group by number having count(number)>=3

• 找到每个人的任务

select p.id,p.name,t.content from person p left join task t
on p.id=t.person_id

•   牛客每个人最近的登录日期（一）

select user_id,max(date) d from login  group by user_id order by user_id

• 考试分数（一）

select job,round(avg(score),3) avg from grade 
group by job order by avg desc

 【注】round()函数，四舍五入的函数，两个参数：列名，小数位数（也可以直接一个参数，表示对这个数四舍五入的一个整数）

• 牛客的课程订单分析（一）

select * from order_info where status='completed' 
and product_name in ('C++','java','python') and date > '2025-10-15'
order by id 

•  实习广场投递简历分析（一）

select job,sum(num) cnt from resume_info
where date < '2026-01-01' and date > '2024-12-31'
group by job
order by cnt desc

三、中等

• 查找各个部门领导当前薪水详情以及对应部门编号dept_no，输出结果以salaries.emp_no升序排序，dept_no列是最后一列

select s.*,d.dept_no from salaries s,dept_manager d
where s.to_date='9999-01-01' and d.to_date='9999-01-01'
and d.emp_no=s.emp_no
order by s.emp_no

• 查找所有已经分配部门的员工的last_name和first_name以及dept_no，包括暂时没有分配具体部门的员工

select e.last_name , e.first_name , d.dept_no
from employees e left join dept_emp d
on e.emp_no=d.emp_no

• 获取所有员工当前的manager，如果员工本身是经理的话则不显示

select e.emp_no,m.emp_no manager from dept_emp e inner join dept_manager m
on e.dept_no=m.dept_no
where e.emp_no<>m.emp_no

• 统计出各个title类型对应的员工薪水对应的平均工资avg

select t.title,avg(s.salary) from titles t left join salaries s
on t.emp_no=s.emp_no
group by t.title
order by avg(s.salary)

• 查找所有员工的last_name和first_name以及对应dept_name，也包括暂时没有分配部门的员工

select last_name, first_name, dept_name
from employees e left join (select emp_no, dept_name
                            from dept_emp de left join departments d
                            on de.dept_no = d.dept_no) a
on e.emp_no = a.emp_no

//还可以用两次left join
select last_name, first_name, 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

 【注】做多表查询时，产生的新表必须要有一个自己的别名

• 统计各个部门的工资记录数

select d.dept_no , dept_name , count(salary) sum from departments d
left join ( select de.emp_no,de.dept_no,salary from dept_emp de
            right join salaries s
            on de.emp_no = s.emp_no) a
on d.dept_no = a.dept_no
group by dept_no
order by dept_no

• 使用join查询方式找出没有分类的电影id以及名称

select f.film_id,f.title from film f
left join film_category fc on f.film_id = fc.film_id
where fc.category_id is NULL

• 使用子查询的方式找出属于Action分类的所有电影对应的title，description

select title,description from film f
where f.film_id in (select fc.film_id from film_category fc join category c
                    on fc.category_id = c.category_id
                    where name='Action')

• 创建一个actor表，包含如下列信息

create table 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)

• 批量插入数据，如果数据已经存在，请忽略

insert ignore into actor value(3,'ED','CHASE','2006-02-15 12:34:33')

【注】insert ignore into 插入时会忽略数据库中已经存在的数据，可以保证主键的唯一性

• 创建一个actor_name表，将actor表中的所有first_name以及last_name导入该表

//方法一：先创建表，再查找actor表
create table if not exists actor_name (
first_name varchar(45) not null,
last_name varchar(45) not null
)
select first_name,last_name from actor

//方法二：直接创建表时，复制其他表的结构
create table actor_name as select first_name ,last_name from actor

• 对first_name创建唯一索引 uniq_idx_firstname，对last_name创建普通索引 idx_lastname

alter table actor add UNIQUE uniq_idx_firstname(first_name);
alter table actor add INDEX idx_lastname(last_name);

• 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name为last_name_v

create view actor_name_view as select first_name as first_name_v,last_name as last_name_v from actor;

• 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引 

select * from salaries force index(idx_emp_no)
where emp_no=10005

 【注】使用强制索引：force index（当查询数据较慢时，用explain查看sql索引，发现添加的索引没有生效，那么就可以使用强制索引）

• 在last_update后面新增加一列名字为create_date，类型为datetime，NOT NULL，默认值为'2020-10-01 00:00:00'

alter table actor add column create_date datetime not null default'2020-10-01 00:00:00';

• 构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中

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;

• 在audit表上创建外键约束，其emp_no对应employees_test表的主键id

alter table audit add foreign key audit(emp_no) references employees_test(id);

• 将所有获取奖金的员工当前的薪水增加10%

update salaries set salary=salary*1.1 where to_date='9999-01-01' and emp_no in(
select emp_no from emp_bonus)

• 将employees表中的所有员工的last_name和first_name通过（'）连接起来

select concat(last_name,"'",first_name) as name from employees

【注】mysql支持concat函数，sqlite不支持concat函数

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

四、较难

 

五、困难