mysql做试题汇总

来源

试题取自牛客网在线编程–sql篇

试题

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

select * from employees 
where hire_date = (select max(hire_date) from employees)

这里关键函数max()

查找入职员工时间排名倒数第三的员工所有信息

select * from employees
where hire_date=(select distinct hire_date from employees 
order by hire_date desc
limit 2,1
)

这里关键点distinct代表不重复，order by代表有序排列、desc代表倒序、limit限制查找结果，一个数字代表从第一个结果开始的结果数量；两个数字代表第开头数字个结果开始的结果结尾数字数量。

查找当前薪水详情以及部门编号

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

这里题意是关键，目标表的结果只能从例子中看出，示例1中看出emp_no顺序排序。连接两表这里采用where的方式。

查找所有已经分配部门的员工的信息

select e.last_name,e.first_name,d.dept_no
from employees e,dept_emp d
where e.emp_no=d.emp_no

还是看清示例要求，理解输出的表是啥，这里注意last与first是互换的，比较容易出错。

查找所有员工的last_name和first_name以及对应部门编号

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

这里是左外连接，以from表为根本，连接表不足的数据以null显示。
外连接与内连接不同是对数据的全面显示，空的要补。

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

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

这里是group by聚合，去重，聚合后条件判断使用having，count()统计行数的函数，缺省代表统计聚合总行数。

找出所有员工当前薪水salary情况

select distinct salary
from salaries
order by salary desc

这里就是查找salary，根据示例，理解到需要去重和逆序。

获取所有非manager的员工emp_no

select emp_no
from employees e
where emp_no not in (select emp_no from dept_manager)

这里关键是不包含，not in的应用，或者可以外连接进行限制结果。

获取所有员工当前的manager

select e.emp_no,m.emp_no
from dept_emp e,dept_manager m
where e.dept_no=m.dept_no and e.emp_no!=m.emp_no

这里是内连接，直接条件判断。

获取每个部门中当前员工薪水最高的相关信息

select d.dept_no,s.emp_no,s.salary
from salaries s,dept_emp d
where s.emp_no=d.emp_no
and (d.dept_no,s.salary) in(
select d.dept_no,max(s.salary)
from salaries s, dept_emp d 
where s.emp_no=d.emp_no
group by d.dept_no)
order by d.dept_no

这里的关键是对group by的理解，统计是合并相同的项目，其他项目则选择第一个保留，这里的第一个是在统计时where条件执行完毕的统计时会对除开重复项和函数计算项的其他项升序排序并取第一个，因此无论怎么做都不可更改group by的统计，除非将其他项隔离开group by。而题目要求部门最高薪水的同时还要求最高薪水的成员。因为判定条件是s.emp_no=d.emp_no，若是group by与select s.emp_no一起执行就会导致可能dept_no第一个emp_no薪水不是最高但会先被显示。

查找employees表的员工信息

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

这里需要注意细节，比如Mary需要’'等

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

select t.title,avg(s.salary)
from titles t,salaries s
where t.emp_no=s.emp_no
group by t.title

这里是对group by的基本应用。

获取当前薪水第二多的员工emp_no以及其薪水单表

select emp_no,salary
from salaries
where salary=
(select salary
from salaries
order by salary DESC
limit 1,1)

这里必须分开查询，先保证薪水再查需要的。

获取当前薪水第二多的员工emp_no以及其薪水多表

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e,salaries s
where e.emp_no=s.emp_no and s.salary=
(select max(salary)
from salaries
where salary!=(select max(salary) from salaries))

这里不能用order by排序所以薪水的查询只能采用max函数的嵌套的形式。

查找所有员工的信息

select e.last_name,e.first_name,m.dept_name
from employees e
left outer join (
select p.emp_no,d.dept_name
from departments d,dept_emp p
where d.dept_no=p.dept_no) m
on e.emp_no=m.emp_no

这里是查找包含没有部门的员工，使用外连接。

查找在职员工自入职以来的薪水涨幅情况

select f.emp_no,(t.salary-f.salary) growth
from
(select emp_no,salary
from salaries
where to_date='9999-01-01') t,
(select s.emp_no,s.salary
from salaries s,employees e
where e.hire_date=s.from_date) f
where f.emp_no=t.emp_no
order by growth

这里要仔细分析题意，入职以来，那么锁定入职时间和离职时间将新水表分成两个新表；难度也在创建新表上，不能直接拿结果；然后可以列表达式来计算结果，最后题目要求薪水升序需要留意。

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

select dept_no,dept_name,count(dept_no)
from (select *
from departments m,dept_emp e,salaries s
where m.dept_no=e.dept_no and s.emp_no=e.emp_no
order by dept_no)
group by dept_no

题意是通过连接三个表之后，统计dept_no重复数就算工资的记录数。

对所有员工的薪水按照salary降序进行1-N的排名

select emp_no, salary,
dense_rank() over (order by salary desc) as 'rank'
from salaries

使用窗口函数计算排序次数。
窗口函数：
rank() 相同位次存在跳过，比如：1,1,3
dense_rank() 相同位次不跳过，比如：1,1,2
row_number() 不存在相同位次，比如：1,2,3
<窗口函数> OVER ( [PARTITION BY <列清单> ] ORDER BY <排序用列清单> ）

获取所有非manager员工当前的薪水情况

select e.dept_no,e.emp_no,s.salary
from dept_emp e,salaries s
where e.emp_no=s.emp_no and e.emp_no not in
(select e.emp_no
from dept_emp e,dept_manager m
where e.emp_no=m.emp_no and e.dept_no=m.dept_no)

这里要防止混淆表别名等细节，对于题意就是除去部门的emp_no取留下的。

获取员工其当前的薪水比其manager当前薪水还高的相关信息

select distinct e.emp_no,m.emp_no,e.salary as emp_salary,m.salary as manager_salary
from (select e.emp_no,e.dept_no,s.salary
from dept_emp e,dept_manager m,salaries s
where e.emp_no!=m.emp_no and e.emp_no=s.emp_no) e,
(select m.emp_no,m.dept_no,s.salary
from dept_manager m,salaries s
where m.emp_no=s.emp_no) m
where e.dept_no=m.dept_no and e.salary>m.salary

这里采用分而治之的方式，先员工及工资形成一表，管理及工资形成一表，然后再进行比较，去重。
如果想一次查询，则需要将salaries表分成两份。

汇总各个部门当前员工的title类型的分配数目

select d.dept_no,m.dept_name,t.title,count(t.title)
from departments m,dept_emp d,titles t
where d.emp_no=t.emp_no and m.dept_no=d.dept_no
group by d.dept_no,t.title
order by d.dept_no

这里使用多表查询，因此不可以不带表前缀，同时单使用count()，最后的结果只会是1条，但是题目要求统计的title有相同的部分，但是分开了，因此需要group by来限制统计，将统计限制在dept_no和title的综合统计中，这样可以排除dept_no的重复与title重复导致的合并。

查找描述信息中包含robot的电影对应的分类名称以及电影数目

select c.name,count(f.film_id)
from category c,film_category fc,film f
where c.category_id in (select fc.category_id
from film_category fc
group by fc.category_id
having count(fc.category_id)>=5)
and f.description like "%robot%"
and f.film_id=fc.film_id
and fc.category_id=c.category_id

这里题意查找是分开进行的从表fc中查出符合count(fc.category_id)>=5的部分，从表f中查出f.description like "%robot%"的部分然后连接。连接的时候特别注意group by若是放外面必定会影响整个结果，因此不可以外放，因此查出group by结果再将结果当条件。然后是名称中包含robot的形式需要用like。

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

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

这里采用左外连接，on后面接连接条件，where接判断条件。is代表属性与=不同，可以接null，=则不能接null。

使用子查询方式找属Action分类全电影的title,description

select title,description
from film
where film_id in
(select film_id
from film_category
where category_id in
(select category_id
from category
where name='Action'))

这里使用的子查询就是使用()嵌套的形式查询。

将employees表的所有员工的last_name和first_name拼接起来作为Name

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

select concat_ws(' ',last_name,first_name)
from employees

select last_name||' '||first_name
from employees

关于拼接字符串，使用的函数concat()、concat_ws()、group_concat()。group_concat()属于同一列拼接。
sqlite特殊管道操作符 | |也可以拼接。

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

create table actor(
actor_id smallint(5) primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null
);

创建表注意主键primary key，格式。

批量插入数据

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

插入表注意格式和’ '。