【实战】mysql习题

-- 员工表
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 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 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,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,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 departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));

-- 职位表
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

-- test（触发器用）
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

-- test 删除重复记录
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

-- 成绩表
create table grade(
id int(11) not null,
number int(11) not null ,
primary key (id)
);

-- 刷题通过表

create table passing_number(
id int(11) not null,
number int(11) not null ,
primary key (id)
);

1. 查找最晚入职员工

select * from employees order by hire_date desc limit 1;
select * from employees where hire_date = (select max(hire_date) from employees)

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

select * 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.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列)
select b.*, a.dept_no
from dept_manager a
inner 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 asc;

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

select a.last_name,a.first_name,b.dept_no
from employees a
inner join dept_emp b
on a.emp_no = b.emp_no;

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

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

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

select a.emp_no, b.salary
from employees a
inner join salaries b
on a.emp_no = b.emp_no
where a.hire_date >= b.from_date and a.hire_date < b.to_date
order by emp_no desc;

※7.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

-- （1）where是分组前的条件，having是分组的条件 （2）where字句不能使用聚合函数，having可以使用聚合函数
select emp_no,count(emp_no) as t
from salaries
group by emp_no having t > 15

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

-- 去重(group by 比 distinct 效率高 )
select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc ;

9.获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary，输出结果按照dept_no升序排列(请注意，同一个人可能有多条薪水情况记录)

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

10.获取所有非manager的员工emp_no

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

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

select a.emp_no, b.emp_no as manager_no
from dept_emp a, dept_manager b
where a.to_date='9999-01-01' and b.to_date='9999-01-01' and a.dept_no = b.dept_no and a.emp_no != b.emp_no;

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

因为emp_no是随机返回的，而不是与最大工资相对应的那个
【知识点】
使用group by子句时，select子句中只能有聚合键、聚合函数、常数。
emp_no并不符合这个要求。
select r.dept_no, ss.emp_no, r.maxSalary
from (
select d.dept_no, max(s.salary) as maxSalary
from dept_emp d,
salaries s
where d.emp_no = s.emp_no
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by d.dept_no
) as r,
salaries ss,
dept_emp dd
where r.maxSalary = ss.salary
and r.dept_no = dd.dept_no
and dd.emp_no = ss.emp_no
and ss.to_date = '9999-01-01'
and dd.to_date = '9999-01-01'
order by r.dept_no asc;

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

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

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

注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。
select title, count(distinct emp_no) as t
from titles
group by title having t >= 2

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

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

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

select title, avg(salary)
from titles 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'
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 a.emp_no,a.salary,b.last_name,b.first_name
from salaries a
left join employees b
on a.emp_no = b.emp_no
where a.to_date = '9999-01-01'

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

select a.last_name,a.first_name,c.dept_name
from employees a
left join dept_emp b on a.emp_no = b.emp_no
left join departments c on b.dept_no = c.dept_no

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

SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth

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

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）
select a.emp_no,max(b.salary)-min(b.salary)
from employees a
inner join salaries b on a.emp_no = b.salary
where b.to_date != '9999-01-01'

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

select b.dept_no,c.dept_name,count(salary)as sum
from salaries a
inner join dept_emp b on a.emp_no =b.emp_no
inner join departments c on b.dept_no = c.dept_no
group by b.dept_no
order by dept_no asc

23.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

select emp_no,salary,(@rowno:=@rowno+1)as t_rank
from salaries ,(select @rowno:=0)t
where to_date='9999-01-01'
order by salary desc,emp_no asc

24.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

select a.emp_no,b.emp_no as manager_no,c1.salary as emp_salary,c2.salary as manager_salary
from dept_emp a
inner join dept_manager b on a.dept_no = b.dept_no
inner join salaries c1 on c1.emp_no = a.emp_no
inner join salaries c2 on c2.emp_no = b.emp_no
where c1.salary > c2.salary and c1.to_date = '9999-01-01' and c2.to_date = '9999-01-01'

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

(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)
select concat(last_name,' ',first_name) as name
from employees

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

列表 类型 是否为NULL 含义
actor_id smallint(5) not null 主键id
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
last_update date not null 日期
create table if not exists actor(
actor_id smallint(5) not null,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp not null default now(),
primary key (actor_id)
)

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

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

28.针对如下表actor结构创建索引： 对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);

29.针对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

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

create index idx_emp_no on salaries(emp_no);
select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005;

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

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

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

-- FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
CREATE TRIGGER audit_log AFTER INSERT
ON employees_test FOR EACH ROW
BEGIN
INSERT INTO audit VALUES (NEW.ID, NEW.NAME);
END;

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

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

34.将titles_test表名修改为titles_2017

ALTER TABLE titles_test RENAME TO titles_2017

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

ALTER TABLE audit ADD FOREIGN KEY (emp_no) REFERENCES employees_test (id)

36.查找字符串'10,A,B' 中逗号','出现的次数cnt

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

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

-- substr(字符串，起始位置，长度）
-- 起始位置:截取的子串的起始位置（注意:字符串的第一个字符的索引是1
select first_name
from employees
order by substr(first_name,length(first_name)-1,2)

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

-- group_concat()函数返回X的非null值的连接后的字符串。如果给出了参数Y，将会在每个X之间用Y作为分隔符。如果省略了Y，“，”将作为默认的分隔符。每个元素连接的顺序是随机的
select dept_no,group_concat(emp_no) as employees from dept_emp group by dept_no

39.查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary

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

40.分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5,5

??? 41.按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

select emp_no,salary,sum(salary) over (order by emp_no) as running_total
from salaries
WHERE to_date = '9999-01-01'

42.对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

select a.first_name
from employees a
where (select count(*) from employees b where b.first_name >= a.first_name) % 2 = 1
order by a.first_name asc

43.在牛客刷题的小伙伴们都有着牛客积分，积分(grade)表简化可以如下: id为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分，查询结果如下:

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

44.在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下:

select id,number,(select count(distinct b.number) from passing_number b where b.number >= a.number) as t_rank
from passing_number a
order by a.number desc,a.id asc;
-- 窗口函数
select id,number,dense_rank() over (order by number desc) as t_rank
from passing_number order by t_rank,id

45.牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天

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

46.牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备. login user client

CREATE TABLE IF NOT EXISTS login (
id int(11) NOT NULL,
user_id int(11) NOT NULL,
client_id int(11) NOT NULL,
date date DEFAULT NULL);

CREATE TABLE IF NOT EXISTS user (
id int(11) NOT NULL,
name varchar(50) NOT NULL);

CREATE TABLE IF NOT EXISTS client (
id int(11) NOT NULL,
name varchar(50) NOT NULL);

select user.name as u_n, client.name as c_n,
login.date
from login
join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by login.user_id )
order by user.name;