2020-10-11

MySQL练习（1）——牛客网[xiaoshun]

一、牛客网网址

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 employeeswhere 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 employeeswhere 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_nofrom salaries as sainner join dept_manager as dmon dm.emp_no = sa.emp_nowhere 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_nofrom employees einner join dept_emp don e.emp_no = d.emp_nowhere 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_nofrom employees eleft join dept_emp don 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.salaryfrom employees einner join salaries son e.emp_no = s.emp_nowhere s.from_date = e.hire_dateorder by e.emp_no desc;# 1.有涨薪或者降薪，说明salaries表中的每一个emp_no至少有一个，薪水变过就有重复值。# 2.只要from_date等于入职日期hire_date，薪水就是入职时的薪水。

（2）分组之后求日期最小值

select emp_no,salaryfrom salariesgroup by emp_nohaving min(from_date)order by emp_no desc;# 1.先用emp_no分组，得到每个员工分组数据# 2.再用having对每个分组求最早日期，即刚入职的日期，不能求最小工资，因为员工有可能被降薪。# 3.数据量大时，不建议用分组查询，因为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 tfrom salariesgroup by emp_nohaving 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 salaryfrom salarieswhere to_date='9999-01-01'order by salary desc;# distinct 多列去重时，只有所有列的信息完全一致才认为时重复的。

（2）用group by去重，据说数据量很大时，效率比较高

select salaryfrom salarieswhere to_date='9999-01-01'group by salaryorder 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,salaryfrom dept_manager dinner join salaries son d.emp_no = s.emp_nowhere 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_nofrom employees eleft join dept_manager don e.emp_no = d.emp_nowhere d.dept_no is null;# 1.假如员工不是manager，那么dept_no字段应该为null# 2.通过左连接的方式，使不是manager的dept_no字段为null，再筛选即可

（2）子查询

select emp_nofrom employeeswhere emp_no not in( select d.emp_no from dept_manager d inner join employees e on d.emp_no = e.emp_no);# 1.先用子查询查出是manger的emp_no# 2.再判断员工emp_no不在manger的emp_no里面即可# 3.子查询可以直接用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_nofrom dept_emp deinner join dept_manager dmon dm.dept_no = de.dept_nowhere de.emp_no <> dm.emp_noand dm.to_date='9999-01-01'and de.to_date='9999-01-01';# 1.两表内连接后，只有manager的de.emp_no和dm.emp_no是一样的，用where筛选掉即可。# 2.因为部门的manager有可能会离职或者更换，要保证manage和普通员工都在同一时间在同一部门才能构成从属关系。两个跨时空的人恋爱会很惨的！！！

 未完待补充

原文转载：http://www.shaoqun.com/a/480931.html

史泰博：https://www.ikjzd.com/w/2112

tiki：https://www.ikjzd.com/w/2053

活动树：https://www.ikjzd.com/w/1518


一、牛客网网址https://www.nowcoder.com/ta/sql，一共76道真题。二、题目1、查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)CREATETABLE`employees`(`emp_no`int(11)NOTNULL,--'员工编号'`birth_date
askme：https://www.ikjzd.com/w/2459
DMM：https://www.ikjzd.com/w/2026
黑五备战—最新prime day欧美九国TOP热卖listing数据：https://www.ikjzd.com/tl/5963
3步到位！速卖通直通车爆款打造：https://www.ikjzd.com/home/19418
土耳其市场潜力巨大，速卖通携手iyzico打造无缝支付体：https://www.ikjzd.com/home/11634
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)