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;