【Ryo】MySQL：刷题训练牛客篇（五）

数据库是进行数据分析的“源泉”，对冗杂的数据进行整理和编排是数据分析的首要任务。同时将来职业生活中，对数据库系统进行DB操作也会是重要任务之一，因此掌握数据库语言刷题刷起来，至少对语言运用和数据库管理有所了解是题中之义。
本篇为牛客题霸的SQL训练题目练习记录（五），也是牛客刷题的最后一篇。SQL刷题整体上都是基础语法，最主要还是连接和逻辑的运用。因此题目难度并不能体现实际项目，后期会继续记录力扣刷题和一些实战项目的训练，这样SQL才算入门了。

研究内容	语言	日期
数据库	sql	2021年2月7日

△△△△△本文为个人项目练习，仅供参考，如有不足欢迎讨论 △△△△△

1. 更新表

题目描述：
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

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

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'); 

个人解答：

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

2. 筛选排序【难】

题目描述：
对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
牛客讨论：首先题目的叙述有问题，导致理解有误，输出的数据与参考答案不同。先给出正确的题目叙述：【对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name】。

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`));
如，输入为：
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

牛客解答： 1、本题用到了三层 SELECT 查询，为了便于理解，采用缩进方式分层，且最外层对应e1，最内层对应e3；
2、在e3层中，采用 COUNT() 函数对 e2.first_name 进行排名标号，即在给定 e2.first_name的情况下，不大于 e2.first_name 的 e3.first_name 的个数有多少，该个数刚好与 e2.first_name 的排名标号匹配，且将该值命名为 rowid；注意：排名标号后并未排序，即[Bob, Carter, Amy]的排名是[2,3,1]，选取奇数排名后输出[Carter, Amy]，所以可见参考答案中的first_name并未按字母大小排序
3、在e1层中，直接在限定条件 e1.rowid % 2 = 1 下，代表奇数行的 rowid，选取对应的e1.first_name；
4、e2层则相当于连接e1层（选取表示层）与e3层（标号层）的桥梁。

SELECT e1.first_name FROM 
  (SELECT e2.first_name, 
    (SELECT COUNT(*) FROM employees AS e3 
     WHERE e3.first_name <= e2.first_name) 
   AS rowid FROM employees AS e2) AS e1
WHERE e1.rowid % 2 = 1

另一种简化的大神代码：

SELECT e1.first_name FROM
employees e1
WHERE 
(SELECT count(*) FROM employees e2 
WHERE e1.first_name <=e2.first_name)%2=1;

3. 计算/窗口 【三种方法】

题目描述：
按照salary的累计和running_total，其中running_total为前N个当前( 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、个人解答：

SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary)                  #前N个的计算
FROM salaries AS s2
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no

2、窗口函数法：

SELECT emp_no,salary,
SUM(salary) OVER (ORDER BY emp_no) AS running_total
FROM salaries
WHERE to_date = '9999-01-01';

3、常规思路——表连接：

#把所有小于等于当前编号的表s1和当前编号表s2联立起来，然后按照当前编号分组，计算出所有小于等于当前标号的工资总数
SELECT s2.emp_no,s2.salary,SUM(s1.salary) AS running_total
FROM salaries AS s1 INNER JOIN salaries AS s2 
ON s1.emp_no <= s2.emp_no
WHERE 
s1.to_date = "9999-01-01"
AND s2.to_date = "9999-01-01"
GROUP BY s2.emp_no

4. 约束

题目描述：
在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(以下2个表已经创建了)

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,
create_date datetime NOT NULL
);

个人解答：创建外键语句结构为
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY (<列名>)
REFERENCES <关联表>（关联列）

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

5. 关联表查询【难】

题目描述：

获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

牛客解答：

#联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from ( #查询员工当前工资表
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
)as emp_sal
inner join( #查询经理当前工资表
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;

6.创建表

题目描述：

个人解答：

create table actor(
actor_id smallint(5) not null PRIMARY KEY,
first_name  varchar(45) not null,
last_name  varchar(45) not null,
last_update date not null )

后记

  第一轮的SQL题目个人记录+分享就到这结束了。五篇一共30题，强度不大比较有意义。我认为，无论是sql也好还是Python也好在数分道路上都是工具，核心能力依然是逻辑思考，数据库管理+数据处理的模式能解决很多问题，这些问题用SPSS、R同样能解决。因此道路千万条，融会贯通各种数分工具但不被其限制，才是更好解决问题的途径。