MySQL练习(1)——牛客网(xiaoshun)

时间:2020-10-11 18:57:00 来源:互联网 作者: 神秘的大神 字体:

一、牛客网网址

https://www.nowcoder.com/ta/sql,一共76道真题。

二、题目

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`));

解题:

(1)假设员工入职的日期都不是同一天,日期降序排序后,直接截取第一条数据即可。

select * from employees order by hire_date desc limit 1;

  limit m,n:m为起始索引位置(索引从0开始),往后获取n行数据。如limit 6,10 获取第7行到16行数据。另一种写法:limit n offset m

  limit 语句运行顺序排到最后,因为它是从结果集中截取部分行数。

(2)假设员工入职的日期有可能同一天。

select *
from employees
where hire_date = (
    select max(hire_date)
    from employees);
# 1.用子查询获取最迟入职日期;
# 2.把入职日期等于最迟入职日期的所有全找出来。

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`));

解题:

(1)假设员工入职的日期都不是同一天

select * from employees order by hire_date desc limit 2,1;
#直接降序排序获取第三行记录即可

  

(2)假设员工入职的日期有可能同一天

select *
from employees
where hire_date=(
    select distinct hire_date
    from employees
    order by hire_date desc
    limit 1 offset 2
);
#1.先用distinct把重复的日期去重,得到的日期都是唯一的
#2.再排序后,用limit获取倒数第三天入职的日期
#3.最后把入职日期等于倒数第三天日期的员工信息全部获取

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 sa.*, dm.dept_no
from  salaries as sa
inner join dept_manager as dm
on dm.emp_no = sa.emp_no
where sa.to_date='9999-01-01'
and dm.to_date='9999-01-01'
order by sa.emp_no asc;
# 考察表的内连接和一些实际情况业务的了解

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
inner join dept_emp d
on e.emp_no = d.emp_no
where dept_no is not null;
#考察表内连接,不用指定dept_no是否为null,因为内连接中,任何一边有缺失数据就不会显示。

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 e
left join dept_emp d
on e.emp_no = d.emp_no;
# 即使没有分配部门的员工也要显示,意味着employees表中所有emp_no都要显示,不管dept_emp表的对应dept_no是否缺失。考察左连接

多表连接查询知识点总结:

表1 inner join 表2  两边表同时有对应的数据,即任何一边有缺失数据就不显示;

主 left join 从    左边主表的数据全部读取,右边从边无对应数据的填充NULL值;

从 right join 主    反之;

 

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`));

解题:

(1)表连接

select e.emp_no, s.salary
from employees e
inner join salaries s
on e.emp_no = s.emp_no
where s.from_date = e.hire_date
order by e.emp_no desc;
#有涨薪或者降薪,说明salaries表中的每一个emp_no至少有一个,薪水变过就有重复值。
#只要from_date等于入职日期hire_date,薪水就是入职时的薪水。

(2)分组之后求日期最小值

select emp_no,salary
from salaries
group by emp_no
having min(from_date)
order by emp_no desc;
#先用emp_no分组,得到每个员工分组数据
#再用having对每个分组求最早日期,即刚入职的日期,不能求最小工资,因为员工有可能被降薪。
#数据量大时,不建议用分组查询,因为having之后,程序会在每个分组表中在遍历一遍,可能导致性能不佳。

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(*) as t
from salaries
group by emp_no
having count(*) > 15;
# 考察点是分组统计查询,

本题一些思考:一天之内多次变动,from_date和to_date均一样,count(distinct from_date)就不太合适,但这种情况实际发生的概率应该很少;

       录入时数据粗心大意,可能导致salary一样,count(distinct salary)也不太合适,这种情况有可能发生,但是员工发工资时应该会发现;

       假如统计的是涨工资次数,而不是变动次数,应该用自连接来判断工资是涨还是降。

分组统计查询知识点归纳:

  • 统计的字段最好和用来分组的字段是一样的,假如不一样,统计字段也必须和分组字段存在一一映射(值可以重复);
  • 统计时,最好用count(*),效率最高,(可能无法用count(*),因为欲统计的字段有重复值,需要count(distinct  字段))
  • where是分组前筛选原始表,having是分组后筛选每个分组表,所有能在where前筛选就不要分组后再筛选。
  • 分组函数做条件筛选肯定放在having语句中,where用一些逻辑判断筛选即可。

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`));

解题:

(1)distinct关键字排除重复

select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc;
#distinct 多列去重时,只有所有列的信息完全一致才认为时重复的。

(2)用group by去重,据说数据量很大时,效率比较高

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(请注意,同一个人可能有多条薪水情况记录)
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 dept_no,d.emp_no,salary
from dept_manager 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';

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`));
解题:
(1)左外连接
select e.emp_no
from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where d.dept_no is null;
#假如员工不是manager,那么dept_no字段应该为null
#通过左连接的方式,使不是manager的dept_no字段为null,再筛选即可

(2)子查询

select emp_no
from employees
where emp_no not in(
    select d.emp_no
    from dept_manager d
    inner join employees e
    on d.emp_no = e.emp_no
);
# 先用子查询查出是manger的emp_no
# 再判断员工emp_no不在manger的emp_no里面即可
# 子查询可以直接用select emp_no from dept_manager,毕竟dept_manager这个表存的是manage的信息。

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 de.emp_no, dm.emp_no as manager_no
from dept_emp de
inner join dept_manager dm
on dm.dept_no = de.dept_no
where de.emp_no <> dm.emp_no
and dm.to_date='9999-01-01'
and de.to_date='9999-01-01';

 未完待补充