3. 人力资源数据（二）（SQL39~SQL61）

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005
思路：考察用 FORCE INDEX 语句强制使用索引

SELECT *
FROM salaries
FORCE INDEX(idx_emp_no)
-- 注意位置：FROM之后，WHERE之前
WHERE emp_no = 10005;

注意：题干表述有误，听上去好像要自己先创建索引一样……实际是要你强制使用已经存在的索引

SQL40 在last_update后面新增加一列名字为create_date
存在actor表，现在在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' 
        AFTER last_update;

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

CREATE TRIGGER audit_log
    AFTER INSERT ON employees_test
    FOR EACH ROW
BEGIN
    INSERT INTO audit
        VALUES(NEW.id, NEW.name);
END;

SQL42 删除emp_no重复的记录，只保留最小的id对应的记录
mysql 中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了），而 sqllite 允许这样做；mysql 中可以再嵌套一层查询来解决：

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT * 
    FROM(
        SELECT MIN(id)
        FROM titles_test
        GROUP BY emp_no
    ) a  -- 把得出的表重命名那就不是原表了（机智.jpg
);

小结：关键是如何将题目的含义 转化 为 SQL 能听懂的含义，比如这个题就是：将 "删除emp_no重复的记录，只保留最小的id对应的记录" 转化为 "删除掉那些不是 【相同emp_no中的最小id】的那些记录"，这样就能通过 NOT IN + 子查询来解决
SQL43 将所有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';

SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现
注意 REPLACE 和 INSERT INTO 一样是整行插入或整行替换，而题目只想替换 id 和 emp_no 两个字段，其他字段保持不变，一种方法：
https://www.yiibai.com/mysql/replace.html

REPLACE INTO titles_test
VALUES ('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01');

但这种方法下，其他字段的值也必须清晰地写出来，还有一种方法：

REPLACE INTO titles_test
SELECT 5, 10005, 【title, from_date, to_date】
FROM titles_test
WHERE id = 5;

通过这种 SELECT + REPLACE INTO 的方法，后面三个保持不变的字段不用把具体的值写出来
SQL45 将titles_test表名修改为titles_2017
https://www.yiibai.com/mysql/rename-table.html
RENAME TABLE语句不能用于重命名临时表，可以使用ALTER TABLE语句来重命名一个临时表。

-- 法1：
RENAME TABLE titles_test TO titles_2017;

-- 法2：
ALTER TABLE titles_test
RENAME TO titles_2017;

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

ALTER TABLE audit 
    ADD FOREIGN KEY fk_audit_employees_test (emp_no)
    REFERENCES employees_test (id)
        ON UPDATE CASCADE
        ON DELETE NO ACTION;

SQL48 将所有获取奖金的员工当前的薪水增加10%
写出更新语句，将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
法一：连接

UPDATE salaries AS s 
JOIN emp_bonus AS e ON s.emp_no=e.emp_no
SET salary=salary*1.1
WHERE to_date='9999-01-01'

法二：子查询

UPDATE salaries
SET salary=salary*1.1
WHERE to_date='9999-01-01'
AND emp_no IN(select emp_no FROM emp_bonus);

SQL50 将employees表中的所有员工的last_name和first_name通过(')连接起来。
主要考查用转义字符 —— 反斜线 \ 或用双引号"'"

SELECT CONCAT(last_name, '\'', first_name)
FROM employees;

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

SELECT
    LENGTH('10,A,B') - LENGTH(REPLACE('10,A,B', ',', '')) AS cnt;

REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。

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

SELECT first_name
FROM employees
ORDER BY RIGHT(first_name, 2);

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

SELECT 
    dept_no,
    GROUP_CONCAT(emp_no) employees
FROM dept_emp
GROUP BY dept_no;

SQL54 查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary
注意：排除的最大最小工资也要是当前的(to_date = '9999-01-01' )

SELECT AVG(salary) avg_salary
FROM salaries
WHERE salary NOT IN (
    SELECT MIN(salary)
    FROM salaries WHERE to_date = '9999-01-01'
    )
    AND salary NOT IN (
    SELECT MAX(salary)
    FROM salaries WHERE to_date = '9999-01-01'
    )
AND to_date = '9999-01-01'

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

SELECT *
FROM employees
LIMIT 5,5

limit(x,y) 从第x条记录（不包含x）后开始，返回y条数据

SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息。
EXISTS() 和 ISNULL() 很类似，只是后者只能判断一个单元格的数据是否为空，前者是判断一个结果（可能是一个单元格、一个列、一个表）是否为空，即前者能判断的范围更广。结果为布林值 TRUE/FALSE，自然也就可以在前面加 NOT 来取反
法1. NOT EXISTS + 关联子查询：

SELECT *
FROM employees e
WHERE NOT EXISTS(
    SELECT emp_no 
    FROM dept_emp 
    WHERE emp_no = e.emp_no
);

如果不要求用 EXISTS 关键字的话，还有两种更常用的方法

法2. NOT IN + 简单子查询实现：

SELECT *
FROM employees e
WHERE emp_no NOT IN(
    SELECT emp_no 
    FROM dept_emp 
);

法3. LEFT JOIN + IS NULL 实现：

SELECT e.*
FROM employees e
LEFT JOIN dept_emp d USING(emp_no)
WHERE d.dept_no IS NULL;

SQL59 获取有奖金的员工相关信息。
获取有奖金的员工相关信息。给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'

SELECT
    e.emp_no,
    first_name,
    last_name,
    btype,
    salary,
    CASE
        WHEN btype = 1 THEN salary * 0.1
        WHEN btype = 2 THEN salary * 0.2
        ELSE salary * 0.3
    END AS bonus
FROM employees e
JOIN emp_bonus eb USING(emp_no)
JOIN salaries s 
    ON e.emp_no = s.emp_no
    AND s.to_date = '9999-01-01';

☆SQL60 统计salary的累计和running_total
按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。
法1. 窗口函数

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

法2. 子查询

MySQL 8.0 之后才能使用窗口函数，如果是 5.7 及以下版本的话，就要用子查询法，所以两种方法都要掌握

SELECT
    emp_no,
    salary,
    (SELECT SUM(salary) 
    FROM salaries
    WHERE emp_no <= s.emp_no
    【AND to_date = '9999-01-01'】) AS running_total
    /* 别忘了子查询内部也要有对to_date的筛选条件
    （子查询和外层查询是两个查询，WHERE筛选条件各论各的）*/
FROM salaries s
WHERE to_date = '9999-01-01'
ORDER BY emp_no;

SQL61 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name，且输出时不需排序
法1. 窗口函数法

注意：从题干其实看不出相同 first_name 名次是否要并列，所以不知道要用 ROW_NUMBER() 还是 RANK() ，实验发现两种均可，应该是不考虑 first_name 相同的问题
说的是不用排序，但只有按 emp_no 排序才能通过

SELECT first_name
FROM(
    SELECT 
        emp_no,
        first_name,
        RANK() OVER(ORDER BY first_name) AS rk
    FROM employees
) AS a
WHERE rk % 2 = 1
ORDER BY emp_no;

法2. 子查询法

SELECT first_name
FROM employees e
WHERE (
    SELECT COUNT(*) 
    FROM employees
    WHERE first_name <= e.first_name
    /* 
    如果 first_name 里可能用空值，这里还要加上一句：
    OR e.first_name IS NULL AND first_name IS NOT NULL
    但 first_name 里应该没有空值
    */
) % 2 = 1;