牛客-SQL-刷题（上）
牛客 SQL题目
SQLite数据库 3.7.9
细节：SQL对大小写不敏感，但在大型ERP系统开发时显得重要，操作系统会将所有字符转换为大写
所以在平时习惯用大写
1. 分页查询employees表，每5行一页，返回第2页的数据
分析：第2页数据，即第6-10条
SELECT * FROM employees LIMIT 5,5;
- LIMIT n,m：从第n条记录开始，返回m条记录；第1条记录是0
2. 查找字符串'10,A,B' 中逗号','出现的次数cnt
分析：统计子串出现次数问题
SELECT (LENGTH('10,A,B')-LENGTH(REPLACE('10,A,B',',','')))/LENGTH(',') cnt;
- ( LENGTH(str)-LENGTH(REPLACE(str,substr,’ ’)) )/ LENGTH (substr)
- REPLACE( str, substr, replacestr) 在str中，用replacestr替换subst
3. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现.
分析：更新titles_test的行数据 （题目中已显示所有插入数据信息）
REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
- REPLACE INTO table VALUES(…) 重新插入一条完整的新纪录，SQL会将id相同的记录自动替换
UPDATE titles_test SET emp_no=REPLACE(emp_no,10001,10005) WHERE id=5;
- UPDATE 表名 SET colum=[new value] WHERE …
4. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
分析：考察SUBSTR()
SELECT first_name FROM employees ORDER BY SUBSTR(first_name,-2);
- SUBSTR(str, start, length)：在str中从start位置开始截取长度为length的子串
- start参数：必选，第一个位置是1；倒数第一个位置是-1；length参数：可选，缺省默认到末尾字符
5. 将titles_test表名修改为titles_2017
分析：修改表名
ALTER TABLE titles_test RENAME TO titles_2017;
- 加上一个column：ALTER TABLE 表名 ADD 列名 列种类
- 删去一个column：ALTER TABLE 表名 DROP 列名
- 改变column名称：ALTER TABLE 表名 CHANGE 旧列名 新列名 新列种类
- 改变column种类：ALTER TABLE 表名 MODIFY 列名 新列种类
- 该表table名称：ALTER TABLE 旧表名 RENAME TO 新表名
6. 删除emp_no重复的记录，只保留最小的id对应的记录
分析：找到emp_no重复的记录 --> GROUP BY emp_no HAVING COUNT(emp_no)>1
注意点1：因为这里要保留最小的id，即需要用到MIN(id)，所以一定要有GROUP BY语句
注意点2：在找要删除的id时，不能使用MAX(id)，因为这样默认只有2条重复的语句
注意点3：子查询表的结果时也要加重命名
正向思维：
1. 找到emp_no重复、且id最小的记录
SELECT emp_no, MIN(id) id FROM titles_test GROUP BY emp_no HAVING COUNT(emp_no)>1
2. 联合之前的表，找到需要删除的id
SELECT a.id FROM titles_test a, (SELECT emp_no, MIN(id) id FROM titles_test GROUP BY emp_no HAVING COUNT(emp_no)>1) b
WHERE a.emp_no=b.emp_no AND a.id>b.id
3. 根据id删除行
DELETE FROM titles_test WHERE id IN (SELECT * FROM (SELECT a.id FROM titles_test a, (SELECT emp_no, MIN(id) id FROM titles_test GROUP BY emp_no HAVING COUNT(emp_no)>1) b WHERE a.emp_no=b.emp_no AND a.id>b.id) c);
逆向思维：
删除重复的、不是最小id的记录
优化：不需要HAVING子句
DELETE FROM titles_test WHERE id NOT IN (SELECT * FROM (SELECT MIN(id) id FROM titles_test GROUP BY emp_no) a);
7. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
分析：知识盲区：聚合函数GROUP_CONCAT(X,Y)，必须与GROUP BY联合使用
- GROUP_CONCAT(X,Y)： X必选，是需要连接的字段；Y可选，是连接的符号，缺省是默认为逗号
SELECT dept_no, GROUP_CONCAT(emp_no) employees FROM dept_emp GROUP BY dept_no;
8. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
分析：基本操作：GROUP BY 联合HAVING COUNT(*)
优化：可以写成HAVING t>=2
SELECT title, COUNT(*) t FROM titles GROUP BY title HAVING COUNT(title)>=2;
9. 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01
分析：考察UPDATE语句：用逗号连接，不是用AND
UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01' WHERE to_date = '9999-01-01';
10. 对于表actor批量插入如下数据
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| 1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
分析：要求在一条语句中执行完成
知识盲区：在VALUES语句后用','隔开不同的插入记录；第9题的更新多条记录也是如此
INSERT INTO actor VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
11. 对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
分析：知识盲区：INSERT OR IGNORE INTO 表名 VALUES(...)
INSERT OR IGNORE INTO actor VALUES(3,'ED','CHASE','2006-02-15 12:34:33');
12. 将employees表中的所有员工的last_name和first_name通过(')连接起来
分析：要按SQLite数据库中的连接符 || 来连接字符
MySQL中可以：SELECT CONCAT(last_name,"'",first_name) name FROM employees
SELECT last_name|| "'"|| first_name name FROM employees;
--代码颜色没有分好...
13. 针对库中的所有表生成select count(*)对应的SQL语句
对应输出应为：
| cnts |
|---|
| select count(*) from employees; |
| select count(*) from departments; |
| select count(*) from dept_emp; |
| select count(*) from dept_manager; |
| select count(*) from salaries; |
| select count(*) from titles; |
| select count(*) from emp_bonus; |
分析：知识盲区：SQLite数据库的内置表sqlite_master
参考：https://blog.csdn.net/jingcheng345413/article/details/70155254
表结构为：
CREATE TABLE sqlite_master ( type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT );
应用场景1：查询表信息：返回的sql字段为创建此表的sql语句
SELECT * FROM sqlite_master WHERE type='table' and name='表名'；
应用场景2：查询索引信息：返回的tbl_table为该索引所在的表，sql字段为创建此索引的sql语句
SELECT * FROM sqlite_master WHERE type='index' and name='索引名'；
本题解法：
SELECT 'select count(*) from ' || name || ';' cnts FROM sqlite_master WHERE type='table';
14. 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v
分析：创建视图的基础操作 CREATE VIEW 视图名 AS ...
CREATE VIEW actor_name_view AS SELECT first_name first_name_v, last_name last_name_v FROM actor;
15. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
分析：用DISTINCT语句去重
SELECT DISTINCT(salary) salary FROM salaries WHERE to_date='9999-01-01' ORDER BY salary DESC;
16. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
分析：涨幅即为一个周期内的最大绝对值，个人觉得即使降薪，也不需要按照日期排序
另外，SELECT 数1- 数2 growth，后面不需要FROM salaries，否则会返回n条记录
SELECT MAX(salary)-MIN(salary) growth FROM salaries WHERE emp_no=10001;
17. 将所有获取奖金的员工(bonus表)当前的薪水(salaries表)增加10%。
分析：用WHERE emp_no IN ... 语句，不能直接上来就等于
是要找两张表中emp_no一样的记录，取个交集，用表A INNER JOIN 表B ON...
且to_date='9999-01-01'表示当前的
UPDATE salaries SET salary=salary*1.1 WHERE emp_no IN (SELECT a.emp_no FROM salaries a INNER JOIN emp_bonus b ON a.emp_no=b.emp_no and a.to_date='9999-01-01');
18. 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
分析：用WHERE子句查询的基本操作，奇数用%判断 num%2=1，或者位运算 num&1=1
SELECT * FROM employees WHERE emp_no%2=1 AND last_name !='Mary' ORDER BY hire_date DESC;
19. 使用子查询的方式找出属于Action分类的所有电影对应的title,description
| 字段 | 说明 |
| film_id | 电影id |
| title | 电影名称 |
| description | 电影描述信息 |
| 字段 | 说明 |
| category_id | 电影分类id |
| name | 电影分类名称 |
| last_update | 电影分类最后更新时间 |
| 字段 | 说明 |
| film_id | 电影id |
| category_id | 电影分类id |
| last_update | 电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (film_id smallint(5) NOT NULL,category_id tinyint(3) NOT NULL, `last_update` timestamp);
分析：
根据category中的name='Action',找film表中的title和description
要通过category表的category_id --> film_category表的category_id
通过film_category表的film_id --> film表的film_id
SELECT title,description FROM film WHERE film_id IN (SELECT a.film_id FROM film_category a INNER JOIN category b ON a.category_id=b.category_id and b.name='Action');
20. 针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
分析：知识小盲区：创建唯一索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名)
注意一句sql结束后有分号';'
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name); CREATE INDEX idx_lastname ON actor(last_name);
21. 对于如下表actor，其对应的数据为:
| actor_id | first_name | last_name | last_update |
|---|---|---|---|
| 1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
| 列表 | 类型 | 是否为NULL | 含义 |
|---|---|---|---|
| first_name | varchar(45) | not null | 名字 |
| last_name | varchar(45) | not null | 姓氏 |
分析：从actor表中选出first_name和last_name这两列，创建为actor_name表
CREATE TABLE actor_name AS SELECT first_name, last_name FROM actor;
22. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
分析：理解题意，相同的emp_no，有一条记录，就算一次薪水涨幅变动
注意是COUNT(emp_no)，而不是COUNT(salary)
SELECT emp_no, COUNT(emp_no) t FROM salaries GROUP BY emp_no HAVING t > 15;
23. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
注意对于重复的emp_no进行忽略
分析：注意与22题的区别：22题是按emp_no分组后，统计每组的个数
这题是按照title分组后，再按一定要求统计个数，即统计同一个title中不重复的emp_no的记录个数，所以用COUNT(DISTINCT emp_no)
SELECT title, COUNT(DISTINCT emp_no) t FROM titles GROUP BY title HAVING t>=2;
24. 构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中
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, NAME TEXT NOT NULL);
分析：用触发器同步两张表的数据更新
知识盲区：
- 构造触发器：CREATE TRIGGER 语句，用BEFORE或AFTER指定在执行后面的SQL语句之前或之后触发TRIGGER
- BEFORE/AFTER [INSERT/UPDATE/ADD] ON 表名1
- BEGIN和END语句之间：写触发器执行的内容，主要用分号‘;’
- 触发器中NEW关键字：触发事件之后对应的表名1的相关列的值；OLD关键字：触发事件之前对应的表名1的相关列的值
注意：这里是NEW.ID，ID是employees_test表中的列名，插入到audit中作为EMP_no的值
CREATE TRIGGER audit_log AFTER INSERT ON employees_test BEGIN INSERT INTO audit VALUES(NEW.ID, NEW.NAME); END;
25. 查找最晚入职员工的所有信息
分析：就是hire_date最大的对应的值，用个子查询语句实现
SELECT * FROM employees WHERE hire_date=( SELECT max(hire_date) FROM employees);
26. 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引
分析：知识盲区：
- SQLite的强制索引查询：INDEXED BY 索引名;
- MySQL的强制索引查询使用：FORCE INDEX(索引名);
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no=10005;
27. 使用含有关键字exists查找未分配具体部门的员工的所有信息
分析：emp_no在employees表中, 但不在dept_emp表中
用NOT IN方法：
SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp)
知识盲区：EXIST语句
- EXIST/ NOT EXIST返回：根据子查询语句的空或非空结果集返回一个BOOL值
- 外查询表的每一行都带入内查询表作为检验
此题，在 employees表中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录
在employees表中逐条判断，有返回结果则为TRUE，否则为FALSE
比如employees表中的第一条emp_no=10001, 那么如果dept_emp表中，没有一条记录的emp_no=10001，那么就返回True，外表查询输出该条记录；
如果dept_emp表中，有一条记录的emp_no=10001，就返回False，外表查询不输出
SELECT * FROM employees WHERE NOT EXISTS(SELECT emp_no FROM dept_emp WHERE emp_no=employees.emp_no)
应用场景：NOT EXIST语句防止重复插入
参考：http://blog.sina.com.cn/s/blog_5564eb640100i42t.html
- 从suppliers表中选取多条数据插入clients表(主键为client_id)中
INSERT INTO clients(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising' FROM suppliers
WHERE NOT EXISTS (SELECT * FROM clients
WHERE clients.client_id = suppliers.supplier_id);
- 插入单条数据到clients表(主键为client_id)中
INSERT INTO clients(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising' FROM dual
WHERE NOT EXISTS (SELECT * FROM clients
WHERE clients.client_id = 10345);
注意：这里使用虚拟表名dual，可在SELECT之后直接写需要插入的数据
28. 获取所有非manager的员工emp_no
分析：那就是emp_no在employees表中，不在dept_manager表中
注意：SQLite不支持MINUS，可以使用EXCEPT进行集合的差集运算
SELECT emp_no FROM employees EXCEPT SELECT emp_no FROM dept_manager;
SELECT结果集的列个数，对应的列种类必须相同
- 并集：[SQL语句1] UNION [SQL语句2] 去重
- [SQL语句1] UNION ALL [SQL语句2] 不去重
- 交集：[SQL语句1] INTERSECT [SQL语句2]
- 差集：[SQL语句1] EXCEPT [SQL语句2]
用NOT IN方法：
SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
用LEFT JOIN方法：注意有dept_manager.emp_no is NULL
SELECT a.emp_no FROM employees a LEFT JOIN dept_manager b ON a.emp_no=b.emp_no WHERE b.emp_no is NULL
29. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
分析：可以用LIMIT 1,1选择第二多的记录，但要注意去重的情况，比如第一多的有重复两个，那么LIMIT 1,1选的还是薪水第一多的记录
原始SQL（没有考虑重复的情况）
SELECT emp_no, salary FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC LIMIT 1,1
用DISTINCT去重：子查询中也增加了当前 to_date的限制
SELECT emp_no, salary FROM salaries WHERE to_date='9999-01-01' AND salary=(SELECT DISTINCT(salary) FROM salaries WHERE to_date='9999-01-01' ORDER BY salary DESC LIMIT 1,1);
用GROUP BY去重：
SELECT emp_no,salary FROM salaries WHERE to_date='9999-01-01' GROUP BY salary ORDER BY salary DESC LIMIT 1,1;
DISTINCT去重需要扫描全表，效率较低
30. 查找入职员工时间排名倒数第三的员工所有信息
分析：同29题，用DISTINCT去重+LIMIT使用
SELECT * FROM employees WHERE hire_date=(SELECT DISTINCT(hire_date) FROM employees ORDER BY hire_date DESC LIMIT 2,1);
同样可以用GROUP BY去重：
SELECT * FROM employees WHERE hire_date GROUP BY hire_date ORDER BY hire_date DESC LIMIT 2,1;
