Mysql练习——牛客网练习题

1查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,  -- '员工编号'
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

select * from employees order by hire_date desc limit 0,1

2题目描述
查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

select distinct * from employees order by  hire_date desc limit 2,1;

3题目描述
查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列)

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL, -- '员工编号',
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, --  '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

select a.emp_no,a.salary,a.from_date,a.to_date,b.dept_no from salaries a join 
dept_manager b on a.emp_no=b.emp_no
where  a.to_date='9999-01-01' and b.to_date='9999-01-01' order by a.emp_no ;

4题目描述
查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

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

5题目描述
查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

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

6题目描述
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

select emp_no,salary from  salaries  
 group by emp_no having min(from_date)
order by emp_no desc

7题目描述
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

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

8题目描述
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

select distinct salary from salaries where to_date='9999-01-01' order by 
salary desc

9题目描述
获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

select d.dept_no,d.emp_no,s.salary from dept_manager d join salaries  s
on   d.emp_no=s.emp_no where d.to_date
='9999-01-01' and s.to_date='9999-01-01' 

10题目描述
获取所有非manager的员工emp_no

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

select e.emp_no from  dept_manager d  right join employees e 
on d.emp_no=e.emp_no  where d.dept_no is null

11题目描述
获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL, -- '所有的员工编号'
`dept_no` char(4) NOT NULL, -- '部门编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '经理编号'
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

select e.emp_no,m.emp_no from dept_emp e join dept_manager m 
on e.dept_no=m.dept_no  where   e.emp_no
<>m.emp_no  and m.to_date='9999-01-01' and m.to_date=e.to_date

12题目描述
获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。

select e.dept_no,e.emp_no,s.salary from dept_emp e 
join salaries s on e.emp_no=s.emp_no and e.to_date='9999-01-01' and s.to_date='9999-01-01'
where s.salary 
in (select max(s1.salary) from salaries s1 join dept_emp e1 on s1.emp_no=e1.emp_no and 
   e1.to_date='9999-01-01' and s1.to_date='9999-01-01' and e1.dept_no=e.dept_no)
 order by e.dept_no

13题目描述
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

select title,count(title) t from titles  group by title having t>=2  

14、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

select distinct title,count(1)t
from (select distinct emp_no,title from titles)
group by title having t>=2

15、查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

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

16、统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

select t.title,avg(s.salary)
from salaries s join titles t
on s.emp_no=t.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by title

17、获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary from salaries
where to_date='9999-01-01'
order by salary desc limit 1,1

18、查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e join salaries s
on e.emp_no=s.emp_no
where s.salary=(select max(salary) from salaries where salary<
               (select max(salary) from salaries where to_date='9999-01-01')
               and to_date='9999-01-01') and to_date='9999-01-01'

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

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

20、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

select sum(s1.salary-s2.salary)as growth
from salaries s1 left join salaries s2
on s1.from_date=s2.to_date
where s1.emp_no=10001

21、查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

select s1.emp_no,(s1.salary-e1.salary) as growth
from
(select emp_no,salary from salaries
where to_date='9999-01-01') as s1
inner join
(select s.emp_no,s.salary from salaries as s
inner join employees as e
on s.emp_no = e.emp_no and s.from_date=e.hire_date) as e1
on s1.emp_no=e1.emp_no
order by growth

22、统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum

select d.dept_no,d.dept_name,count(1) as sum
from salaries s join dept_emp de on s.emp_no=de.emp_no
join departments d on d.dept_no=de.dept_no
group by d.dept_no

23、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

select de.dept_no,a.emp_no,s.salary
from(select emp_no from employees
where emp_no not in (select emp_no from dept_manager)) a
join dept_emp de on a.emp_no=de.emp_no
join salaries s on a.emp_no=s.emp_no
where s.to_date='9999-01-01'

24、获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水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.salary>s2.salary
and s2.to_date='9999-01-01' and s1.to_date='9999-01-01';

25、汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count
(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)

select de.dept_no as dept_no,d.dept_name as dept_name,
t.title as title,count(*) as `count`
from(select * from dept_emp as de1 where de1.to_date='9999-01-01') de,
    (select * from titles as t1 where t1.to_date='9999-01-01') t,
departments as d
where d.dept_no = de.dept_no and de.emp_no = t.emp_no
group by d.dept_no, t.title

26、给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

select s1.emp_no, s1.from_date, (s1.salary - s2.salary) as salary_growth
from salaries s1, salaries s2
where s1.emp_no = s2.emp_no and s1.from_date = s2.to_date
and (s1.salary - s2.salary) > 5000
order by salary_growth desc;

27、 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

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

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

select f.film_id as'电影id',f.title as'名称'
from film f left join film_category fc
on f.film_id=fc.film_id
where fc.category_id is null

29、你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

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

30、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)

select last_name||' '||first_name as Name
from employees

31、创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))

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 timestamp not null default(datetime('now','localtime'))
);

32、对于表actor批量插入如下数据(不能有2条insert语句哦!)

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

33、对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

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

34、请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

create table actor_name
as select first_name,last_name
from actor

35、针对如下表actor结构创建索引：
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作)

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 timestamp NOT NULL DEFAULT (datetime('now','localtime')))

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
(请先创建唯一索引，再创建普通索引)

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

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

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

37、现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000-00-00 00:00:00’

alter table actor add create_date datetime not null default '0000-00-00 00:00:00'

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

create trigger audit_log after insert on employees_test
begin
    insert into audit values(new.id,new.name);
end

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

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

40、将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

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

41、将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

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

42、将titles_test表名修改为titles_2017。

alter table titles_test rename to titles_2017;

43、在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(audit已经创建，需要先drop)

drop table audit;
create table audit(
    EMP_no int not null,
    create_date datetime not null,
    foreign key(EMP_no)
    references employees_test(ID)
);

44、请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

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

45、针对库中的所有表生成select count(*)对应的SQL语句，如数据库里有以下表，
(注:在 SQLite 中用 “||” 符号连接字符串，无法使用concat函数)

select 'select count(*) from' || name ||';' as cnts
from sqlite_master where type='table'

46、查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

select length('10,A,B')-length(replace('10,A,B',',','')) as cnt;

47、获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列。

select first_name from employees
order by substr(first_name,-2)
  

48、按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

select dept_no,group_concat(emp_no,",")
from dept_emp group by dept_no
  

49、查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。

select avg(salary) as avg_salary
from salaries
where to_date='9999-01-01'
and salary!=(select max(salary) from salaries where to_date='9999-01-01')
and salary!=(select min(salary) from salaries where to_date='9999-01-01');

50、将employees表中的所有员工的last_name和first_name通过(’)连接起来。

select last_name||"'"||first_name as name from employees