牛客网数据库SQL实战答案解析下篇

部分答案来自牛客网讨论分析

• 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name, COUNT(fc.film_id) FROM
 (select category_id, COUNT(film_id) AS category_num FROM
     film_category  GROUP BY category_id HAVING count(film_id)>=5) AS cc,
 film AS f, film_category AS fc, category AS c
WHERE  f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id

答案来自牛客网，本题需用到虚表，先Mark，以后细读
1、找到对应电影数量>=5的所有分类，建立成虚表cc：(select category_id, count(film_id) as category_num from film_category group by category_id having count(film_id)>=5) as cc
2、设定限制条件 f.description like ‘%robot%’
3、在表cc、f、fc、c中查找包括robot的电影对应的分类名称和对应的电影数目。

• 使用join查询方式找出没有分类的电影id以及名称

SELECT f.film_id,f.title FROM film f LEFT JOIN film_category fc ON f.film_id = fc.film_id WHERE fc.category_id IS NULL;

解题思路是运用 LEFT JOIN 连接两表，用 IS NULL 语句限定条件：
1、用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影

• 使用子查询的方式找出属于Action分类的所有电影对应的title,description

SELECT f.title,f.description FROM category c INNER JOIN film_category fc ON c.category_id = fc.category_id INNER JOIN film f ON f.film_id = fc.film_id WHERE c.name = 'Action';

本题题目要求使用子查询，我上面写的是非子查询的语句，使用子查询如下：select f.title,f.description from film as f
where f.film_id in (select fc.film_id from film_category as fc
where fc.category_id in (select c.category_id from category as c
where c.name = ‘Action’));

• 获取select * from employees对应的执行计划

EXPLAIN SELECT * FROM employees;

easy的题，考的是explain关键字，在SQLite数据库中，可以用 “EXPLAIN” 关键字或 “EXPLAIN QUERY PLAN” 短语，用于描述表的细节

• 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

SELECT last_name||" "||first_name Name FROM employees;

注意：1、牛客网后台的判题系统使用的是SQLite数据库，该数据库不支持CONCAT内置函数。不同数据库连接字符串的方法不完全相同，MySQL、SQL Server、Oracle等数据库支持CONCAT方法，而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串
2、连接空格时注意要使用双引号“”而不是单引号‘’

• 创建一个actor表，包含如下列信息

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 timestamp NOT NULL DEFAULT (datetime('now','localtime'))
)

做这题的时候碰到一个问题，字段类型关键字smallint、varchar等使用大写字母时代码报错，使用SQLite定义字段类型时只能用小写。
根据题意，本题关键点是actor_id的主键设置与last_update的默认获取系统时间：
1、在actor_id字段末尾加上PRIMARY KEY是将该字段设置为主键，或者在表的最后一行加上PRIMARY KEY(actor_id)
2、在last_update末尾加上DEFAULT是为该字段设置默认值，且默认值为(datetime(‘now’,‘localtime’))，即获得系统时间，注意最外层的括号不可省略

• 对于表actor批量插入如下数据

INSERT INTO actor
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

• 对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

INSERT OR IGNORE INTO actor VALUES(3,'ED','CHASE','2006-02-15 12:34:33');

如果不存在则插入，如果存在则忽略
INSERT OR IGNORE INTO tablename VALUES(…);
如果不存在则插入，如果存在则替换
INSERT OR REPLACE INTO tablename VALUES(…);
这里指的存在表示的是unique属性的列值存在的情况下，unique表示键值唯一
因为题目判定系统使用的是sqlite3，所以必须按sqlite3的写法来做，如果是mysql，那么把or去掉，像下面这样

• 创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

CREATE TABLE actor_name
(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name VALUES ('PENELOPE','GUINESS'),('NICK','WAHLBERG');

• 针对如下表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 uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

• 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v

CREATE VIEW actor_name_view AS SELECT first_name first_name_v,last_name last_name_v FROM actor;

• 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005;

SQLite中，使用 INDEXED BY 语句进行强制索引查询，可参考：
http://www.runoob.com/sqlite/sqlite-indexed-by.html
MySQL中，使用 FORCE INDEX 语句进行强制索引查询，可参考：
http://www.jb51.net/article/49807.htm

• 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000 00:00:00’

ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

注意：本题题目中给出的datetime的默认值格式有误，应为’0000-00-00 00:00:00’

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

CREATE TRIGGER audit_log AFTER INSERT ON employees_test BEGIN INSERT INTO audit VALUES(NEW.ID,NEW.NAME); END;

• 删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM titles_test WHERE id NOT IN (SELECT MIN(id) FROM titles_test GROUP BY emp_no);

本题思路如下：先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM … WHERE … NOT IN … 语句删除 “非每个分组最小id对应的所有记录”

• 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

UPDATE titles_test SET to_date = NULL,from_date = '2001-01-01';

用 UPDATE 语句更新若干列的最基本用法，详细可参考：
http://www.w3school.com.cn/sql/sql_update.asp
https://www.w3schools.com/sql/sql_update.asp
另外要注意若干列 to_date = NULL 和 from_date = ‘2001-01-01’ 之间只能用逗号连接，切勿用 AND 连接。

• 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

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

• 将titles_test表名修改为titles_2017。

ALTER TABLE titles_test RENAME TO titles_2017;

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

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

由于SQLite中不能通过 ALTER TABLE … ADD FOREIGN KEY … REFERENCES … 语句来对已创建好的字段创建外键，因此只能先删除表，再重新建表的过程中创建外键。可参考：
http://www.sqlite.org/foreignkeys.html
https://zhidao.baidu.com/question/350164703.html

• 存在如下的视图：
  create view emp_v as select * from employees where emp_no >10005;
  如何获取emp_v和employees有相同的数据？

SELECT e.* FROM employees e,emp_v ev WHERE e.emp_no = ev.emp_no;

SELECT * FROM employees INTERSECT SELECT * FROM emp_v

注意：本题解法很多，最常用也最容易想到的两种如下：
1、用 WHERE 选取二者 emp_no 相等的记录
2、用 INTERSECT 关键字求 employees 和 emp_v 的交集

• 将所有获取奖金的员工当前的薪水增加10%。

UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN
(SELECT s.emp_no FROM salaries AS s INNER JOIN emp_bonus AS eb 
ON s.emp_no = eb.emp_no AND s.to_date = '9999-01-01')

最标准的写法

• 针对库中的所有表生成select count(*)对应的SQL语句

SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table'

注意：本题没看懂什么意思，答案来自牛客网，先Mark
本题主要有以下两个关键点：
1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 ‘table’，详情可参考：
http://blog.csdn.net/xingfeng0501/article/details/7804378

• 将employees表中的所有员工的last_name和first_name通过(’)连接起来。

SELECT last_name||"'"||first_name FROM employees;

这题没什么好说的，跟前面有一题类似，SQLite数据库中，只支持用连接符号"||"来连接字符串，不支持用函数连接

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

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt

技巧题，答案来自牛客网。
由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。详情请参考：
http://www.cnblogs.com/huangtailang/p/5cfbd242cae2bcc929c81c266d0c875b.html
http://sqlite.org/lang_corefunc.html#replace

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

SELECT first_name FROM employees ORDER BY SUBSTR(first_name,LENGTH(first_name)-1) ASC;

SELECT first_name FROM employees ORDER BY SUBSTR(first_name,-2);

以上两种写法均可，SUBSTR的参数不同而已，效果一样。

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

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

常规题，没什么好说的。

• 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

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

注意：牛客网讨论组反映此题有问题，有待观望。

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

SELECT * FROM employees LIMIT 5,5

注意：划重点，分页在数据库中的实现。经常用。
根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：
方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。
1
SELECT * FROM employees LIMIT 5 OFFSET 5
方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。
1
SELECT * FROM employees LIMIT 5,5

• 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示

SELECT em.emp_no, de.dept_no, eb.btype, eb.recevied
FROM employees AS em INNER JOIN dept_emp AS de
ON em.emp_no = de.emp_no
LEFT JOIN emp_bonus AS eb 
ON de.emp_no = eb.emp_no

注意：以上是标准写法。
本题严谨的思路为，先将 employees与dept_emp 用 INNER JOIN 连接，挑选出分配了部门的员工，再用 LEFT JOIN 连接 emp_bonus（在前面的题中可看到此表），分配了奖金的员工显示奖金类型和授予时间，没分配奖金的员工则不显示。

• 使用含有关键字exists查找未分配具体部门的员工的所有信息。

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

还可以使用NOT IN

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

• 获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

SELECT e.* FROM employees e,emp_v ev WHERE e.emp_no = ev.emp_no;

• 获取有奖金的员工相关信息。给出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, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

又是一道比较麻烦的题。老规矩，先Mark。答案来自牛客网。
本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。详细用法请参考：
http://www.sqlite.org/lang_expr.html 中的【The CASE expression】
http://www.2cto.com/database/201202/120267.html 中的【条件表达式】

• 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

SELECT s1.emp_no, s1.salary, 
(SELECT SUM(s2.salary) 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

本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。
1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = ‘9999-01-01’
又是一道比较典型的题目，需要理解记忆一下，答案来自牛客网，Mark。

• 对于employees表中，给出奇数行的first_name

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

又一道送命题，题目看起来异常简单，实现过程较为复杂。参见牛客网分析解答：
首先题目的叙述有问题，导致理解有误，输出的数据与参考答案不同。先给出正确的题目叙述：【对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name】。
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层（标号层）的桥梁。

至此，牛客网数据库专题已经走完一遍，感受下来就是数据库博大精深（一脸苦楚状。。。），这些题目中还没涉及到存储过程等高阶数据库操作，都是些数据库的基本操作，虽然如今实际应用中不一定会用到部分题目中那么复杂的数据库操作（现在基本上都会屏蔽数据库的复杂操作，将其放到后台代码逻辑中），但是这些基本的数据库操作还是需要熟悉了解。部分题目具有一定技巧性，值得细细体会。
PS：在刷题过程中遇到坑，看到别人的思路豁然开朗，牛人太多啊，要学的还有很多啊。。。谨以此文Mark一下我自己的数据库复习过程（虽然以前很水的学过。。。）。