SQL常见100面试题解析
内容简介本文介绍并分析了常见的 100 道 SQL 面试题，重要分为三个模块：SQL 低级查询、SQL 高级查询以及数据库计划与开发。内容结构如下图所示：
https://img-blog.csdnimg.cn/20190824204908934.png
本文重要利用三个示例表：员工表（employee）、部门表（department）和职位表（job）。下面是这些示例表的结构图：
https://img-blog.csdnimg.cn/20190823211802564.png?
[*]部门表（department），包罗部门编号（dept_id）和部门名称（dept_name）字段，主键为部门编号。
[*]职位表（job），包罗职位编号（job_id）和职位名称（job_title）字段，主键为职位编号。
[*]员工表（employee），包罗员工编号（emp_id）、员工姓名（emp_name）、性别（sex）、部门编号（dept_id）、经理编号（manager）、入职日期（hire_date）、职位编号（job_id）、月薪（salary）、奖金（bonus）以及电子邮箱（email）。主键为员工编号，部门编号字段是引用部门表的外键，职位编号字段是引用职位表的外键，经理编号字段是引用员工表自身的外键。
创建示例表和初始化数据的脚本可以从 GitHub 上举行下载。所有示例都可以在 Oracle 12c 和 MySQL 8.0 中通用，除非另有阐明。
SQL 低级查询
1. 什么是 SQL？SQL 有哪些功能？
答案：SQL 代表结构化查询语言，它是访问关系数据库的通用语言，支持数据的各种增删改查操作。SQL 语句可以分为以下子类：
[*]DQL，数据查询语言。这个就是 SELECT 语句，用于查询数据库中的数据和信息。
[*]DML，数据操作语言。包括 INSERT、UPDATE、DELETE 和 MERGE 语句，重要用于数据的增加、修改和删除。
[*]DDL，数据界说语言。重要包括 CREATE、ALTER 和 DROP 语句，用于界说数据库中的对象，例如表和索引。
[*]TCL，事务控制语言；重要包括 COMMIT、ROLLBACK 和 SAVEPOINT 语句，用于管理数据库的事务。
[*]DCL，数据控制语言。重要包括 GRANT 和 REVOKE 语句，用于控制对象的访问权限。
解析：SQL 是一种声明性的编程语言，只需要告诉盘算机想要什么内容（what），不需要指定具体怎么实现（how）。通过几个简朴的英文单词，例如 SELECT、INSERT、UPDATE、CREATE、DROP 等，就可以完成大部门的数据操作。
2. 如何检察员工表中的姓名和性别？
答案：
SELECT emp_name, sex FROM employee;
解析：SQL 利用 SELECT 和 FROM 查询表中的字段，多个字段利用逗号分隔。
3. 如何检察员工表中的所有字段？
答案：
SELECT * FROM employee;
大概：
SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email
FROM employee;
解析：SQL 查询中的星号（*）体现查询所有字段，可以方便快速查询数据；但是在产物中不保举利用，因为星号大概带来不确定性。
4. 如何知道每个员工一年的总收入？
答案：
SELECT emp_name, salary * 12 + COALEASE(bonus, 0)
FROM employee;
解析：查询结果中可以利用各种运算、函数以及表达式。COALEASE 函数用于将空值转换为 0。
5. 如何为查询结果指定一个容易理解标题？
答案：
SELECT emp_name AS "姓名", salary * 12 + COALEASE(bonus, 0) "年薪"
FROM employee;
解析：SQL 中的别名可以为查询中的表或结果指定一个暂时名称。别名利用关键字 AS 体现，可以省略。
6. 怎么检察女性员工的信息？
答案：
SELECT *
FROM employee
WHERE sex = '女';
解析：SQL 中利用 WHERE 子句指定过滤条件，只有满足条件的数据才会返回。除了等于（=），还可以利用大于（>）、大于等于（>=）、小于（<）、小于等于（<=）以及不等于（!= 大概 <>）这些比较运算符作为过滤条件。
7. 如何检察月薪范围位于 8000 到 12000 之间的员工？
答案：
SELECT *
FROM employee
WHERE salary BETWEEN 10000 AND 15000;
解析：BETWEEN AND 用于查找范围值，而且包罗两头值。
8. 确认员工中有没有叫做“张三”、“李四” 或“张飞”的人，有的话查出他们的信息。
答案：
SELECT *
FROM employee
WHERE emp_name IN ('张三', '李四', '张飞');
解析：IN 用于查找列表中的任意值。
9. 只知道某个员工的姓名里有个“云”字，但不知道具体名字，怎么样检察有哪些如许的员工？
答案：
SELECT *
FROM employee
WHERE emp_name LIKE '%云%';
解析：SQL 中的 LIKE 运算符用于字符串的模式匹配。LIKE 支持两个通配符：% 匹配任意多个字符，_ 匹配单个字符。Oracle 区分大小写，MySQL 不区分大小写。
10. 有些员工有奖金（bonus），另一些没有。怎么检察哪些员工有奖金？
答案：
SELECT emp_name,
bonus
FROM employee
WHERE bonus IS NOT NULL;
解析：SQL 中的 NULL 体现空值，意味着缺失大概未知数据。判断空值不能直接利用等于或不等于，而需要利用特殊的 IS NULL 和 IS NOT NULL。
11. 在前面我们知道了如何查询女员工，如何检察 2010 年 1 月 1 日之后入职的女员工呢？
答案：
SELECT emp_name, sex, hire_date
FROM employee
WHERE sex = '女'
AND hire_date > DATE '2010-01-01';
解析：AND、OR 和 NOT 体现逻辑与、逻辑或和逻辑非，可以用于构造复杂的查询条件。
12. 以下查询会不会堕落，为什么？
SELECT *
FROM employee
WHERE 1 = 0 AND 1/0 = 1;
答案：不会堕落，但是查不到任何数据。
解析：因为 SQL 对于逻辑运算符 AND 和 OR 利用短路运算（short-circuit evaluation）。也就是说，只要前面的表达式可以或许决定最终的结果，不实行后面的盘算。
13. 如何去除查询结果中的重复记录，比返回如员工性别的不同取值？
答案：
SELECT DISTINCT sex
FROM employee;
解析：DISTINCT 用于消除查询结果中的重复值，上面的查询只返回两个不同的性别记录。
14. 检察员工信息的时候，想要按照薪水从高到低体现，怎么实现？
答案：
SELECT *
FROM employee
ORDER BY salary DESC;
解析：ORDER BY 子句用于对查询结果举行排序；ASC 体现升序，DESC 体现降序。
15. 在上面的排序结果中，有些人的薪水一样多；对于这些员工，希望再按照奖金的多少举行排序，又怎么实现？
答案：
SELECT *
FROM employee
ORDER BY salary DESC, bonus DESC;
解析：按照多个字段排序时，利用逗号分隔；排序时先按照第一个条件分列，对于排名相同的数据，再按照第二个条件分列，以此类推。
16. 员工的姓名是中文，如何按照姓名的拼音次序举行排序？
答案：
-- MySQL 实现
SELECT emp_name
FROM employee
WHERE emp_id <= 10
ORDER BY CONVERT(emp_name USING GBK);
-- Oracle 实现
SELECT emp_name
FROM employee
WHERE emp_id <= 10
ORDER BY NLSSORT(emp_name,'NLS_SORT = SCHINESE_PINYIN_M');
解析：中文可以按照拼音举行排序，大概按照偏旁部首举行排序。MySQL 中的 GBK 编码支持拼音排序，Oracle 可以指定排序规则。
17. 由于许多人没有奖金，bonus 字段为空，对于下面的查询：
答案：
SELECT *
FROM employee
ORDER BY bonus;
没有奖金的员工排在最前面照旧最后面？
答案：取决于数据库的实现。
解析：对于 MySQL ，升序时 NULL 值排在最前面，降序时 NULL 值排在最后面。对于 Oracle，可以利用 NULLS FIRST 和 NULLS LAST 举行控制；默认升序排序时时 NULL 值排在最后面，默认降序时 NULL 值排在最前面。
18. 薪水最高的 3 位员工都有谁？
答案：
-- Oracle 12c 实现
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH NEXT 3 ROWS ONLY;
-- MySQL 实现
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
LIMIT 3;
解析：SQL 中用于限制返回数据的关键字是 FETCH，MySQL 利用 LIMIT。
19. 在上面的问题中，如果有 2 个人的排名都是第 3 位，怎么才能都返回（一共 4 条数据）？
答案：
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH NEXT 3 ROWS WITH TIES;
解析：FETCH 子句支持 WITH TIES 选项，用于返回更多排名相同的数据。另外，还可以利用 PERCENT 按照百分比返回数据。
20. 怎么返回第 11 名到 15 名，也就是实现分页体现的结果？
答案：
-- Oracle 12c 实现
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;
-- MySQL 实现
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
LIMIT 5 OFFSET 10;
解析：OFFSET 关键字指定一个偏移量，体现跳过前面多少行数据，然后再返回后续的结果。
21. 什么是函数？SQL 中的函数有哪些分类？
答案：函数是一种功能模块，可以接收零个或多个输入值，而且返回一个输出值。
在 SQL 中，函数重要分为两种范例：标量函数（scalar function）和聚合函数（aggregate function）。标量函数针对每一行输入参数，返回一行输出结果。例如，ABS 函数可以盘算绝对值。聚合函数针对一组数据举行操作，而且返回一个结果。例如，AVG 函数可以盘算一组数据的平均值。
22. 如何知道每个员工的邮箱长度？
答案：
SELECT emp_name, length(email)
FROM employee;
解析：length 函数用于返回字符长度。需要注意的是，Oracle 是按照字符数目盘算，lengthb 按照字节盘算；MySQL 是按照字节数目盘算，char_length 按照字符数目盘算。对于汉字这种多字节字符需要注意区分。
23. 如何确认谁的邮箱是“GUANXING@SHUGUO.COM”？
答案：
SELECT emp_name, email
FROM employee
WHERE UPPER(email) = 'GUANXING@SHUGUO.COM';
解析：UPPER 函数用于将字符串转换为大写形式。另外，LOWER 函数用于将字符串转换为小写形式。
24. 以 CSV （逗号分隔符）格式体现员工的姓名、性别、薪水信息，如何写 SQL 查询语句？
答案：
-- MySQL 实现
SELECT CONCAT_WS(',' emp_name, sex, salary)
FROM employee;
-- Oracle 实现
SELECT emp_name||','||sex||','||salary
FROM employee;
解析：CONCAT 函数用于毗连两个字符串，MySQL 中的 CONCAT_WS 扩展了该功能；Oracle 支持利用 || 毗连字符串。
25. 如何获取员工邮箱中的用户名部门（ @ 符号之前的字符串）？
答案：
SELECT emp_name, SUBSTR(email, 1, INSTR(email,'@') - 1)
FROM employee;
解析：此处利用了两个字符串函数，INSTR 函数查找 @ 符号的位置，SUBSTR 函数获取该位置之前的子串。
26. 将员工邮箱中的“.com”替换为“.net”，写出 SQL 语句？
答案：
SELECT emp_name, REPLACE(EMAIL, '.com','.net')
FROM employee;
解析：REPLACE 函数用于替换字符串中的字串。另外，TRIM 函数用于截断字符串。
27. 如何返回随机排序的员工信息？
答案：
-- MySQL 实现
SELECT emp_name, RAND()
FROM employee
ORDER BY RAND();
-- Oracle 实现
SELECT emp_name, DBMS_RANDOM.VALUE
FROM employee
ORDER BY DBMS_RANDOM.VALUE;
解析：利用生成随机数的函数举行排序，MySQL 利用 RAND 函数，Oracle 利用 DBMS_RANDOM.VALUE 函数。
28. 数学函数 CEILING、FLOOR 和 ROUND 有什么区别？
答案：
SELECT CEILING(1.1), FLOOR(1.1), ROUND(1.1)
FROM employee
WHERE emp_id = 1;
解析：CEILING 向上取整，FLOOR 向下取整，ROUND 四舍五入。Oracle 中利用 CEIL 函数替换 CEILING。
29. 下图是一个学生成绩表（score），如何知道每个学生的最高得分？
https://img-blog.csdnimg.cn/20190705093207460.png
答案：
SELECT student_id, GREATEST(chinese, math, english, history)
FROM score;
解析：GREATEST 函数用于返回列表中的最大值，LEAST 函数用于返回列表中的最小值。
30. 如何知道每个员工的工作年限？
答案：
SELECT emp_name, EXTRACT( year FROM CURRENT_DATE) - EXTRACT( year FROM HIRE_DATE)
FROM employee;
解析：CURRENT_DATE 函数返回当前日期，EXTRACT 函数可以提取日期中的各个部门，本例中利用 year 参数获取年份信息。
31. 工资信息比较敏感，不宜直接体现。按照范围体现收入程度，小于 10000 体现为“低收入”，大于等于 10000 而且小于 20000 体现为“中等收入”，大于 20000 体现为“高收入”。如何利用 SQL 实现？
答案：
SELECT emp_name,
CASE WHEN salary < 10000 THEN '低收入'
WHEN salary < 20000 THEN '中等收入'
ELSE '高收入'
END "薪水等级"
FROM employee;
解析：CASE 表达式可以类似于 IF-THEN-ELSE 的逻辑处理惩罚。SQL 支持简朴 CASE 和搜刮 CASE，可以为查询增加基于逻辑的复杂分析功能。掌握好 CASE 表达式是利用 SQL 举行数据分析的必备技能之一。
32. 如何统计员工的数目、平均月薪、最高月薪、最低月薪以及月薪的总和？
答案：
SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employee;
解析：聚合函数针对一组数据盘算出单个结果值。常见的聚会函数包括：
[*]AVG - 盘算一组值的平均值。
[*]COUNT - 统计某个字段的行数。
[*]MIN - 返回一组值中的最小值。
[*]MAX - 返回一组值中的最大值。
[*]SUM - 盘算一组值的和值。
33. 以下两个 COUNT 函数返回的结果是否相同？
SELECT COUNT(*), COUNT(bonus)
FROM employee;
答案：结果不同，COUNT(*) 返回 25 条记录，COUNT(bonus) 返回 9 条记录。
解析：除了 COUNT (*) 之外，其他聚合函数都会忽略字段中的 NULL 值。另外，聚合函数中的 DISTINCT 选项可以在盘算之前排除重复值。
34. 群发邮件时，多个邮件地址利用分号举行分隔。如何获取所有员工的群发邮件地址？
答案：
-- MySQL 实现
SELECT GROUP_CONCAT(email SEPARATOR ';')
FROM employee;
-- Oracle 实现
SELECT LISTAGG(email, '; ') WITHIN GROUP (ORDER BY NULL)
FROM employee;
解析：利用字符串的聚合函数将多个字符串合并成一个。MySQL 中利用 GROUP_CONCAT 函数，Oracle 利用 LISTAGG 函数。
35. 如何获取每个部门的统计信息，比如员工的数目、平均月薪？
答案：
SELECT dept_id, COUNT(*), AVG(salary)
FROM employee
GROUP BY dept_id;
解析：SQL 中利用 GROUP BY 举行数据的分组，结合聚合函数可以获得分组后的统计信息。另外，可以利用多个字段分成更多的组。
36. 以下语句能否正常运行，为什么？
SELECT dept_id, COUNT(*), emp_name
FROM employee
GROUP BY dept_id;
答案：不能运行。
解析：利用了 GROUP BY 分组之后，SELECT 列表中只能出现分组字段和聚合函数，不能再出现其他字段。上面的语句中，按照部门分组后，再检察员工姓名的话，存在逻辑上的错误。因为每个部门有多个员工，应该体现哪个员工呢？
37. 如果只想检察平均月薪大于 10000 的部门，怎么实现？
答案：
SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 10000;
解析：HAVING 子句用于对分组后的结果举行过滤，它必须跟在 GROUP BY 之后。
38. 如果想要知道哪些部门月薪超过 5000 的员工数目大于 5，如何写 SQL 查询？
答案：
SELECT dept_id, COUNT(*)
FROM employee
WHERE salary > 5000
GROUP BY dept_id
HAVING COUNT(*) > 5;
解析：WHERE 用于对表中的数据举行过滤，HAVING 用于对分组后的数据举行过滤，两者可以结合利用。
SQL 高级查询
39. 什么是毗连查询？SQL 中有哪些毗连查询？
答案：毗连（join）查询是基于两个表中的关联字段将数据行拼接到一起，可以同时返回两个表中的数据。SQL 支持以下毗连：
[*]内毗连（INNER JOIN），用于返回两个表中满足毗连条件的数据行。
[*]左外毗连（LEFT OUTER JOIN），返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值，返回空值。
[*]右外毗连（RIGHT OUTER JOIN），返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值，返回空值。
[*]全外毗连（FULL OUTER JOIN），等价于左外毗连加上右外毗连，返回左表和右表中所有的数据行。MySQL 不支持全外毗连。
[*]交叉毗连（CROSS JOIN），也称为笛卡尔积（Cartesian product），两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果的数目为两个表的行数相乘。
[*]自毗连（Self Join），是指毗连操作符的两边都是同一个表，即把一个表和它自己举行毗连。自毗连重要用于处理惩罚那些对自己举行了外键引用的表。
40. 如何通过内毗连返回员工所在的部门名称？
答案：可以利用以下两种毗连语句：
SELECT d.dept_id,
d.dept_name,
e.emp_name
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);
SELECT d.dept_id,
d.dept_name,
e.emp_name
FROM employee e, department d
WHERE e.dept_id = d.dept_id;
解析：利用两个表的部门编号（dept_id）举行毗连，可以获得员工所在的部门信息。保举利用第一种语句，即 JOIN 和 ON 的毗连方式，语义上更清楚。
41. 统计每个部门的员工数目，同时体现部门名称信息。如何利用毗连查询实现？
答案：
SELECT d.dept_name, COUNT(e.emp_name)
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;
解析：由于某些部门大概还没有员工，不能利用内毗连，而需要利用左外毗连大概右外毗连；否则大概缺少某些部门的结果。
42. 如何知道每个员工的经理姓名（manager）？
答案：
SELECT e.emp_name AS "员工姓名",
m.emp_name AS "经理姓名"
FROM employee e
LEFT JOIN employee m ON (m.emp_id = e.manager)
ORDER BY e.emp_id;
解析：通过自毗连关联两个员工表，利用左毗连是因为有一个员工没有上级，他就是公司的最高领导。
43. SQL 支持哪些集合运算？
答案：SQL 中提供了以下三种集合运算：
[*]并集运算（UNION、UNION ALL），将两个查询结果合并成一个结果集，包罗了第一个查询结果以及第二个查询结果中的数据。
[*]交集运算（INTERSECT），返回两个查询结果中的共同部门，即同时出现在第一个查询结果和第二个查询结果中的数据。MySQL 不支持 INTERSECT。
[*]差集运算（EXCEPT），返回出现在第一个查询结果中，但不在第二个查询结果中的数据。MySQL 不支持 EXCEPT，Oracle 利用 MINUS 替换 EXCEPT。
44. 假设存在以下两个表：
CREATE TABLE t1(id int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
CREATE TABLE t2(id int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
下列查询的结果分别是什么？
-- Oracle 实现
SELECT id FROM t1 UNION SELECT id FROM t2;
SELECT id FROM t1 UNION ALL SELECT id FROM t2;
SELECT id FROM t1 INTERSECT SELECT id FROM t2;
SELECT id FROM t1 EXCEPT SELECT id FROM t2;
答案：结果分别为（1、2、3）、（1、1、2、3）、（1）以及（2）。
解析：UNION 的结果集中删除了重复的数据，UNION ALL 保留了所有的数据。
45. 对于 MySQL 而言，如何实现上题中的交集运算和差集运算结果？
答案：
-- 使用连接查询实现交集运算
SELECT t1.id FROM t1 JOIN t2 ON (t1.id = t2.id);
-- 使用左连接查询实现差集运算
SELECT t1.id FROM t1
LEFT JOIN t2 ON (t1.id = t2.id)
WHERE t2.id IS NULL;
解析：交集运算等价于基于所有字段的内毗连查询，差集运算等价于左毗连中右表字段为空的结果。
46. 什么是子查询？子查询有哪些范例？
答案：子查询（subquery）是指嵌套在其他语句（SELECT、INSERT、UPDATE、DELETE、MERGE）中的 SELECT 语句。子查询中也可以嵌套另外一个子查询，即多层子查询。
子查询可以根据返回数据的内容分为以下范例：
[*]标量子查询（scalar query）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
[*]行子查询（row query）：返回包罗一个大概多个值的单行结果（一行多列），标量子查询是行子查询的特例。
[*]表子查询（table query）：返回一个虚拟的表（多行多列），行子查询是表子查询的特例。
基于子查询和外部查询的关系，也可以分为以下两类：关联子查询（correlated subqueries）和非关联子查询（non-correlated subqueries）。关联子查询会引用外部查询中的列，因而与外部查询产生关联；非关联子查询与外部查询没有关联。
47. 如何找出月薪大于平均月薪的员工？
答案：
SELECT emp_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
解析：利用子查询获得平均月薪，然后在外部查询中的 WHERE 条件中利用该值。这是一个非关联的标量子查询。
48. 以下查询语句的结果是什么？
SELECT *
FROM employee
WHERE dept_id = (SELECT dept_id FROM department);
答案：实行堕落。
解析：外部查询的 WHERE 条件利用了等于号，但是子查询返回了多个值，此时需要利用 IN 来举行匹配。正确的查询语句如下：
SELECT *
FROM employee
WHERE dept_id IN (SELECT dept_id FROM department);
另外，NOT IN 用于查询不在列表中的值。
49. 哪些员工的月薪高于本部门的平均值？
答案：
SELECT emp_name, salary
FROM employee e
WHERE salary > (SELECT AVG(salary)
FROM employee
WHERE dept_id = e.dept_id);
解析：利用关联子查询获取每个员工所在部门的平均月薪，然后传递给外部查询举行判断。
50. 体现员工信息时，增加一列，用于体现该员工所在部门的人数。如何编写 SQL 查询？
答案：
SELECT emp_name,
(SELECT COUNT(*)
FROM employee
WHERE dept_id = e.dept_id) AS dept_count
FROM employee e;
解析：SELECT 列表中同样可以利用关联子查询。
51. 以上问题能否利用下面的查询实现？
SELECT emp_name,
dept_count
FROM employee e
JOIN (SELECT COUNT(*) AS dept_count
FROM employee
WHERE dept_id = e.dept_id) d
ON (1=1);
答案：该语句实行堕落。
解析：FROM 子句中不能直接利用关联子查询，因为子查询和查询处于相同的层级，不能引用前表（e）中的数据。不过，Oracle 中支持横向（LATERAL）子查询，可以实现该功能：
SELECT emp_name,
dept_count
FROM employee e
JOIN LATERAL (SELECT COUNT(*) AS dept_count
FROM employee
WHERE dept_id = e.dept_id) d
ON (1=1);
52. 找出哪些部门中有女性员工？
答案：
SELECT *
FROM department d
WHERE EXISTS (SELECT 1
FROM employee e
WHERE e.sex ='女'
AND e.dept_id = d.dept_id);
解析：EXISTS 运算符用于检查子查询中结果的存在性。针对外部查询中的每条记录，如果子查询存在结果（部门中存在女性员工），外部查询即返回结果。NOT EXISTS 实行相反的操作。
53. 按照部门和职位统计员工的数目，同时统计部门所有职位的员工数据，再加上整个公司的员工数目。如何用一个查询实现？
答案：
-- MySQL 实现
SELECT dept_id, job_id, COUNT(*)
FROM employee
GROUP BY dept_id, job_id WITH ROLLUP;
-- Oracle 实现
SELECT dept_id, job_id, COUNT(*),GROUPING(dept_id)
FROM employee
GROUP BY ROLLUP (dept_id, job_id);
解析：GROUP BY 支持扩展的 ROLLUP 选项，可以生成按照层级举行汇总的结果，类似于财务报表中的小计、合计和总计。
54. GROUP BY 中的另一个选项 CUBE 的作用是什么？
解析：CUBE 用于生成多维立方体式的汇总统计。例如，以下查询统计不同部门和职位员工的数目，同时统计部门所有职位的员工数据，加上所有职位的员工数据，以及整个公司的员工数目。
-- Oracle 实现
SELECT dept_id, job_id, COUNT(*),GROUPING(dept_id)
FROM employee
GROUP BY CUBE (dept_id, job_id);
另外，GROUPING SETS 用于指定更加复杂的自界说分组方式。MySQL 暂未支持 CUBE 和 GROUPING SETS。
55. 利用扩展分组时，会产生一些 NULL 值，如何确认这些 NULL 值代表的意义？
答案：利用 GROUPING 函数，例如：
--
SELECT CASE GROUPING(dept_id) WHEN 1 THEN '所有部门' ELSE dept_id END,
CASE GROUPING(job_id) WHEN 1 THEN '所有职位' ELSE job_id END,
COUNT(*)
FROM employee
GROUP BY dept_id, job_id WITH ROLLUP;
查询结果如下图所示。
https://img-blog.csdnimg.cn/20190706112043641.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly90b255ZG9uZy5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
解析：GROUPING 函数用于判断某个统计结果是否与该字段有关。如果是，函数返回 0；否则返回 1。比如第 3 行数据是所有职位的统计，与职位无关。然后利用 CASE 表达式举行转换体现。
56. 如何利用 SQL 查询生成以下连续的数字序列？
https://img-blog.csdnimg.cn/20190706112920926.png
答案：
-- MySQL 实现
WITH RECURSIVE cte (n) AS
(
SELECT 1 FROM dual
UNION ALL
SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;
-- Oracle 实现
WITH cte (n) AS
(
SELECT 1 FROM dual
UNION ALL
SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;
解析：通用表表达式（WITH 子句）是一个在语句级别的暂时结果集。界说之后，相当于有了一个表变量，可以在语句中多次引用该通用表表达式。递归（RECURSIVE）形式的通用表表达式可以用于生成序列，遍历条理数据或树状结构的数据。Oracle 中省略 RECURSIVE 即可。
57. 如何获取员工在公司组织结构中的结构图，也就是从最高领导到员工的管理路径？
答案：
-- MySQL 实现
WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS
(
SELECT emp_id, emp_name, CAST(emp_name AS CHAR(200))
FROM employee
WHERE manager IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)
FROM employee_paths ep
JOIN employee e
ON ep.emp_id = e.manager
)
SELECT * FROM employee_paths ORDER BY path;
查询结果如下（体现部门内容）：
https://img-blog.csdnimg.cn/20190706114219329.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly90b255ZG9uZy5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
解析：同样是利用递归通用表表达式实现数据的遍历。Oracle 中省略 RECURSIVE 即可。通用表表达式是 SQL 中非常强盛的功能，可以帮助我们简化复杂的毗连查询和子查询，而且可以完成递归处理惩罚和条理遍历。
58. 什么是窗口函数？有哪些常见的窗口函数？
答案：窗口函数（Window function）也称为分析函数，与聚合函数类似，也是基于一组数据举行分析；但是，窗口函数针对每一行数据都会返回一个结果。窗口函数为 SQL 提供了强盛的数据分析功能。
常见的窗口函数包括聚合窗口函数和专用的窗口函数。前者就是将聚合函数作为窗口函数利用，包括：COUNT、MIN、MAX、AVG 以及 SUM 等。专用窗口函数重要包括 ROW_NUMBER、RANK、DENSE_RANK、PERCENT_RANK、CUME_DIST、NTH_VALUE、NTILE、FIRST_VALUE、LAST_VALUE、LEAD 以及 LAG 等。
59. 查询员工的月薪，同时返回该员工所在部门的平均月薪。如何利用聚合函数实现？
答案：
SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id)
FROM employee;
解析：窗口函数 AVG 基于部门（dept_id）分组后的数据盘算平均月薪，为每个员工返回一条记录。窗口函数不需要和 GROUP BY 一起利用。固然也可以利用关联子查询与聚合函数实现相同的功能，显然窗口函数更加简朴易懂。
60. 查询员工的月薪，同时盘算其月薪在部门内的排名？
答案：
SELECT emp_name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC),
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC),
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)
FROM employee;
解析：ROW_NUMBER、RANK 和 DENSE_RANK 都可以用于盘算排名。它们不同之处在于对排名相同的数据处理惩罚方式不一样。比如说 10、9、9、8 这四个数，ROW_NUMBER 肯定会排出不同的名次（1、2、3、4）；RANK 对于相同的数据排名相同（1、2、2、4）；DENSE_RANK 对于相同的数据排名相同，而且后面的排名不会跳跃（1、2、2、3）。
61. 查询员工的入职日期，同时盘算其部门内在该员工之前一个和之后一个入职的员工？
答案：
SELECT emp_name, dept_id, hire_date,
LAG(emp_name, 1) OVER (PARTITION BY dept_id ORDER BY hire_date),
LEAD(emp_name, 1) OVER (PARTITION BY dept_id ORDER BY hire_date)
FROM employee;
解析：LAG 和 LEAD 用于返回排名中相对于当前行的指定偏移量之前和之后的数据。
62. 查询员工的月薪，同时盘算其部门内到该员工为止的累计总月薪？
答案：
SELECT emp_name, dept_id, salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY NULL ROWS UNBOUNDED PRECEDING)
FROM employee;
解析：窗口函数支持界说窗口范围，UNBOUNDED PRECEDING 体现从分组内的第一行到当前行，可以用于盘算累计值。
63. 查询员工的月薪，同时盘算其部门内按照月薪排序后，前一个员工、当前员工以及后一个员工的平均月薪？
答案：
SELECT emp_name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM employee;
解析：BETWEEN N PRECEDING AND M FOLLOWING 界说了一个随着当前行移动的窗口，可以用于盘算移动平均值。
窗口函数为我们带来了强盛的数据分析和报表生成功能，MySQL 8.0 也增加了对于窗口函数的支持。
计划与开发
64. 什么是数据库（Database）？什么是数据库管理系统（DBMS）？
答案：数据库（Database）是各种数据的集合，按照肯定的数据结构举行存储和管理；数据库管理系统（Database Management System）是用于管理数据库的软件，负责数据库的创建、查询、修改等管理操作。这两者共同构成了数据库系统（Database System）。应用步伐大概最终用户通过 DBMS 访问和管理数据库。
https://img-blog.csdnimg.cn/20190710084232834.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly90b255ZG9uZy5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70#pic_center
65. 什么是关系数据库？
答案：关系数据库是指基于关系模型的数据库。在关系模型中，用于存储数据的逻辑结构就是二维表（Table）。表由行和列组成，行也称为记录，代表了单个实体；列也称为字段，代表了实体的某些属性。关系数据库利用 SQL 作为标准语言，实行数据的增删改查以及各种管理操作。关系数据库还界说了三种束缚完整性：实体完整性、参照完整性以及用户界说完整性。
大多数主流数据库都属于关系数据库，例如 Oracle、MySQL、SQL Server 以及 PostgreSQL 等。另外，数据库领域还存在一些非关系模型的数据库（NoSQL ），例如 Mongodb、Redis、Cassandra 等。
66. 关系数据库有哪些完整性束缚？
答案：关系数据库界说了以下束缚：
[*]非空束缚（NOT NULL），用于限制字段不会出现空值。比如员工姓名不能为空。
[*]唯一束缚（UNIQUE），用于确保字段中的值不会重复。例如，每个员工的电子邮箱不能重复。每个表可以有多个唯一束缚。
[*]主键束缚（Primary Key），主键是唯一标识表中每一行的字段。例如员工编号，部门编号等。主键字段必须唯一且非空，每个表可以有且只能有一个主键。
[*]外键束缚（FOREIGN KEY），用于体现两个表之间的引用关系。例如，员工属于部门，因此员工表中的部门编号字段可以界说为外键，它引用了部门信息表中的主键。
[*]检查束缚（CHECK），可以界说更多用户自界说的业务规则。例如，薪水必须大于 0 ，性别只能是男和女等。
[*]默认值（DEFAULT），用于向字段中插入默认数据。
67. OLTP 和 OLAP 的区别？
答案：
OLTPOLAP在线事务处理惩罚系统在线分析处理惩罚系统专注于事务数据的增删改，事务相对简朴但频仍，要求相应时间快专注于决策数据分析，查询通常比较复杂，处理惩罚时间长数据来源于在线业务数据来源于各种 OLTP通常采用规范化的计划，需要保证数据的完整性不需要太多规范化，可以存储冗余信息，采用多维数据模型常见应用包括银行 ATM、在线订票系统、网上商城常见应用包括数据仓库、报表分析、商务智能 68. 什么是数据库规范化，有哪些常见的数据库范式？
答案：数据库规范化是一种数据库计划的方法，用于有效地组织数据，淘汰数据的冗余和相互之间的依赖，增加数据的一致性。由于非规范化的数据库存在冗余，大概导致数据的插入、删除、修改异常等问题，因此引入了规范化过程。
数据库规范化的程度被称为范式（Normal Form），目前已经存在第一范式到第六范式，每个范式都是基于前面范式的增强。
[*]第一范式（First Normal Form），表中的每个属性都是单值属性，每个记录都唯一，也就是需要主键。举例来说，如果员工存在工作邮箱和个人邮箱，不能都放到一个字段，而需要拆分成两个字段；
[*]第二范式（Second Normal Form），首先需要满足第一范式，且不包罗任何部门依赖关系。举例来说，如果将学生信息和选课信息放在一起，学号和课程编号可以作为复合主键；但此时学生的其他信息依赖于学号，即主键的一部门。通常利用单列主键可以办理部门依赖问题；
[*]第三范式（Third Normal Form），首先需要满足第二范式，而且不存在传递依赖关系。举例来说，如果将部门信息存储在每个员工记录的后面，那么部门名称依赖部门编号，部门编号又依赖员工编号，这就是传递依赖。办理的方法就是将部门信息单独存储到一个表中；
[*]更高的范式包括 Boyce-Codd 范式、第四范式、第五范式以及第六范式等，不过很少利用到这些高级范式。对于大多数系统而言，满足第三范式即可。
另外，反规范化（Denormalization）是在完成规范化之后实行的相反过程。反规范化通过增加冗余信息，淘汰 SQL 毗连查询的次数，从而淘汰磁盘 IO 来提高查询时的性能。但是反规范化会导致数据的重复，需要更多的磁盘空间，而且增加了数据维护的复杂性。
数据库的计划是一个复杂的衡量过程，需要综合考虑各方面的因素。
69. 什么是实体关系图（ERD）？
答案：实体关系图是一种用于数据库计划的结构图，它描述了数据库中的实体，以及这些实体之间的相互关系。实体代表了一种对象大概概念。例如，员工、部门和职位可以称为实体。每个实体都有一些属性，例如员工拥有姓名、性别、工资等属性。
关系用于体现两个实体之间的关联。例如，员工属于部门。三种重要的关系是一对一、一对多和多对多关系。例如，一个员工只能属于一个部门，一个部门可以有多个员工，部门和员工是一对多的关系。
ERD 也可以按照抽象条理分为三种：
[*]概念 ERD，即概念数据模型。概念 ERD 描述系统中存在的业务对象以及它们之间的关系。
[*]逻辑 ERD，即逻辑数据模型。逻辑 ERD 是对概念数据模型进一步的分解和细化，明白界说每个实体中的属性并描述操作和事务。
[*]物理 ERD，即物理数据模型。物理 ERD 是针对具体数据库的计划描述，需要为每列指定范例、长度、可否为空等属性，为表增加主键、外键以及索引等束缚。
下图是我们利用的三个示例表的物理 ERD（基于 MySQL 实现）：
https://img-blog.csdnimg.cn/20190703215516941.png
70. 数据库常见对象有哪些？
答案：表（Table）、视图（View）、序列（Sequence）、索引（Index）、存储过程（Stored Procedure）、触发器（Trigger）、用户（User）以及同义词（Synonym）等等。此中，表是关系数据库中存储数据的重要形式。
71. 常见 SQL 数据范例有哪些？
答案：SQL 界说了大量的数据范例，此中最常见的范例包括字符范例、数字范例、日期时间范例和二进制数据范例。
[*]字符数据范例，分为固定长度的 CHAR(n) 、可变长度的 VARCHAR(n) 以及字符大对象 CLOB。
[*]数字数据范例，分为正确数字 INTEGER、BIGINT、NUMERIC 以及近似数字 FLOAT、DOUBLE PRECISION 等。
[*]日期时间范例，分为日期 DATE、时间 TIME 以实时间戳 TIMESTAMP 。
[*]二进制数据范例，重要是 BLOB。用于存储图片、文档等二进制数据。
主流的数据库都支持这些常见的数据范例，但是在范例名称和细节上存在一些差异。另外，SQL 还提供其他的数据范例，例如 XML、JSON 以及自界说的数据范例。
72. CHAR 和 VARCHAR 范例的区别？
答案：CAHR 是固定长度的字符串，如果输入的内容不够利用空格举行添补，通常用于存储固定长度的编码；VARCHAR 是可变长度的字符串，通常用于存储姓名等长度不一致的数据。Oracle 中利用 VARCHAR2 体现变长字符串。
73. 如何创建一个表？
答案：SQL 中创建表的基本语句如下：
CREATE TABLE table_name
(
column_1 data_type column_constraint,
column_2 data_type,
...,
table_constraint
);
此中 table_name 指定了表的名称，括号内是字段的界说，创建表时可以指定字段级别的束缚（column_constraint）和表级别的束缚（table_constraint）。以下是员工表（employee）的创建语句：
CREATE TABLE employee
( emp_id INTEGER NOT NULL PRIMARY KEY
, emp_nameVARCHAR(50) NOT NULL
, sex VARCHAR(10) NOT NULL
, dept_id INTEGER NOT NULL
, manager INTEGER
, hire_date DATE NOT NULL
, job_id INTEGER NOT NULL
, salary NUMERIC(8,2) NOT NULL
, bonus NUMERIC(8,2)
, email VARCHAR(100) NOT NULL
, CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
, CONSTRAINT ck_emp_salary CHECK (salary > 0)
, CONSTRAINT uk_emp_email UNIQUE (email)
, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
, CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
, CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
) ;
74. 如何基于已有的表复制一个表？
答案：利用以下语句可以基于已有的表大概查询语句复制一个表：
CREATE TABLE table_name
AS
SELECT ...;
查询的结果也会复制到新的表中，如果在查询中利用 WHERE 子句指定一个永不为真的条件，可以创建只有结构的空表。例如，以下语句基于 employee 表创建一个空的新表：
CREATE TABLE emp_new
AS
SELECT *
FROM employee
WHERE 1=0;
MySQL 还支持以下语句复制一个空表：
CREATE TABLE emp_copy
LIKE employee;
75. 什么是自增列？
答案：自增列（auto increment），也称为标识列（identity column），用于生成一个自动增长的数字。它的重要用途就是为主键提供唯一值。Oracle 利用标准 SQL 中的 GENERATED ALWAYS AS IDENTITY 体现自增列，MySQL 利用关键字 AUTO_INCREMENT 体现自增列。以下示例演示了自增列的利用：
-- Oracle 实现
CREATE TABLE emp_identity(
emp_id INT GENERATED ALWAYS AS IDENTITY,
emp_name VARCHAR(50) NOT NULL,
PRIMARY KEY (emp_id)
);
-- MySQL 实现
CREATE TABLE emp_identity(
emp_id INT AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
PRIMARY KEY (emp_id)
);
INSERT INTO emp_identity(emp_name) VALUES ('张三');
INSERT INTO emp_identity(emp_name) VALUES ('李四');
INSERT INTO emp_identity(emp_name) VALUES ('王五');
SELECT * FROM emp_identity;
emp_id |emp_name
------------------
1 |张三
2 |李四
3 |王五
插入数据时，不需要为自增列提供输入值，系统自动生成一个增长的数字序列。
76. 如何修改表的结构？
答案：SQL 提供了 ALTER TABLE，用于修改表的结构：
ALTER TABLE table_name action;
此中，action 体现要实行的修改操作，常见的操作包括增加列，修改列，删除列；增加束缚，修改束缚，删除束缚等。例如，以下语句可以为 emp_new 表增加一列：
ALTER TABLE emp_new
ADD weight NUMERIC(4,2) DEFAULT 60 NOT NULL;
不同的数据库实现了各自支持的修改操作，具体实现可以检察产物的文档。
77. 如何删除一个表？
答案：SQL 中删除表的命令如下：
DROP TABLE table_name;
如果被删除的表是其他表的外键引用表，比如部门表（department），需要先删除子表。Oracle 支持级联删除选项，同时删除父表和子表：
DROP TABLE department CASCADE CONSTRAINTS;
78. DROP TABLE 和 TRUNCATE TABLE 的区别？
答案：DROP TABLE 用于从数据库中删除表，包括表中的数据和表结构自身。同时还会删除与表相关的的所有对象，包括索引、束缚以及访问该表的授权。TRUNCATE TABLE 只是快速删除表中的所有数据，回收表占用的空间，但是会保留表的结构。
79. 什么是数据库事务？
答案：在数据库中，事务（Transaction）是指一个或一组相关的操作（SQL 语句），它们在业务逻辑上是一个原子单元。一个最常见的数据库事务就是银行账户之间的转账操作。比如从 A 账户转出 1000 元到 B 账户，此中就包罗了多个操作：
[*]查询 A 账户的余额是否充足；
[*]从 A 账户减去 1000 元；
[*]往 B 账户增加 1000 元；
[*]记录本次转账流水。
数据库必须保证所有的操作要么全部成功，要么全部失败。如果从 A 账户减去 1000 元成功实行，但是没有往 B 账户增加 1000 元，意味着客户将会丧失 1000 元。用数据库中的术语来说，这种情况导致了数据库的不一致性。
数据库事务拥有以下 4 个特性：原子性、一致性、隔离性以及长期性（ACID）。
[*]Atomic，原子性。一个事务包罗的所有 SQL 语句要么全部成功，要么全部失败。例如，某个事务需要更新 100 条记录，但是在更新到一半时，系统出现故障，数据库必须保证可以或许回滚已经修改过的数据，就像没有实行过该事务一样。
[*]Consistency，一致性。事务开始之前，数据库位于一致性的状态；事务完成之后，数据库仍旧位于一致性的状态。例如，银行转账事务中，如果一个账户扣款成功，但是另一个账户加钱失败，那么就会出现数据不一致（此时需要回滚已经实行的扣款操作）。另外，数据库还必须保证满足完整性束缚，比如账户扣款之后不能出现余额为负数（可以在余额字段上添加检查束缚）。
[*]Isolation，隔离性。隔离性与并发事务有关，一个事务的影响在其完成之前对其他事务不可见，多个并发的事务之间相互隔离。例如，账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目；如果多人同时向账户 B 转账，结果也应该保持一致性，就像依次转账的结果一样。
[*]Durability，长期性。已经提交的事务必须永久生效，纵然发生断电、系统崩溃等故障，数据库都不会丢失数据。
80. 数据库事务支持哪些隔离级别？
答案：当数据库存在并发访问时，大概导致以下问题：
[*]更新丢失，当两个事务同时更新某一数据时，后者会覆盖前者的结果；
[*]脏读，当一个事务正在操作某些数据但并未提交时，如果另一个事务读取到了未提交的结果，就出现了脏读；
[*]不可重复读，第一个事务第一次读取某一记录后，该数据被另一个事务修改提交，第一个事务再次读取该记录时结果发生了改变；
[*]幻象读，第一个事务第一次读取数据后，另一个事务增加大概删除了某些数据，第一个事务再次读取时结果的数目发生了变化。
为相识决并发访问大概导致的问题，数据库提供了不同的事务隔离级别：
脏读不可重复读幻读读未提交大概大概大概读已提交不会大概大概可重复读不会不会大概序列化不会不会不会 Oracle 默认的隔离级别为 READ COMMITTED，MySQL 中 InnoDB 存储引擎的默认隔离级别为 REPEATABLE READ。
81. MySQL 中的 InnoDB 和 MyISAM 存储引擎有什么区别？
答案：MySQL 的一大特点就是支持不同的存储引擎，存储引擎用于管理表中的数据并提供 SQL 操作接口。MySQL利用以下命令检察系统支持的存储引擎：
SHOW ENGINES;
重要的存储引擎包括 InnoDB 和 MyISAM。自从 MySQL 5.5 版本之后，默认利用 InnoDB 存储引擎。
InnoDB 存储引擎支持事务（ACID），提供了事务提交、回滚以及故障恢复本领，可以或许确保数据不会丢失。InnoDB 支持行级锁和多版本一致性的非锁定读取，可以或许提高并发访问和性能。InnoDB 利用聚集索引存储数据，可以或许淘汰利用主键查找时的磁盘 I/O。另外，InnoDB 还支持外键束缚，可以或许维护数据的完整性。
MyISAM 存储引擎数据文件占用的空间更小。MyISAM 采用表级锁，限制了同时读写的性能，通常用于只读大概以读为主的应用。
下表是两者对于各种功能特性的支持比较。
特性MyISAMInnoDBB 树索引支持支持备份/时间点恢复支持支持聚集索引不支持支持压缩数据支持支持数据缓存不支持支持加密数据支持支持外键支持不支持支持全文搜刮索引支持支持空间数据范例支持支持空间数据索引支持支持哈希索引不支持不支持索引缓存支持支持锁定级别表级行级MVCC不支持支持复制支持支持存储限制256TB64TB数据库事务不支持支持 一样平常情况下，利用默认的 InnoDB 存储引擎即可，除非是有特殊的需求和应用场景。
82. 如何插入数据？
答案：SQL 重要提供了两种数据插入的方式：
[*]INSERT INTO … VALUES …
[*]INSERT INTO … SELECT …
第一种方式通过提供字段的值插入数据，例如：
INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部');
MySQL 支持一次提供多个记录值的方式插入多条记录：
-- MySQL 实现
INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部'), (2, '人力资源部'), (3, '财务部');
第二种方式利用查询的结果值插入多条数据，例如：
INSERT INTO emp_new
SELECT * FROM employee;
以上查询将员工表种的所有数据插入表 emp_new 中。
83. 如何修改数据？
答案：SQL 中的 UPDATE 语句用于更新表中的数据：
UPDATE table_name
SET column1 = expr1,
column2 = expr2,
...
;
此中，table_name 是要更新的表名；SET 子句指定了要更新的列和更新后的值，多个字段利用逗号举行分隔；满足 WHERE 条件的数据行才会被更新，如果没有指定条件，将会更新表中所有的行。以下语句为表 emp_new 中的员工“赵云”增加 1000 元的月薪：
UPDATE emp_new
SET salary = salary + 1000
WHERE emp_name = '赵云';
84. 如何删除数据？
答案：SQL 中用于删除数据的命令重要是 DELETE 语句。
DELETE FROM table_name
;
DELETE 语句删除满足条件的数据；如果不指定 WHERE 子句，将会删除表中的所有数据。以下语句将会清空表 emp_new 中的所有数据：
DELETE FROM emp_new;
Oracle 中可以省略 FROM 关键字。
85. 删除数据时，DELETE 和 TRUNCATE 语句的区别？
DELETETRUNCATE用于从表中删除指定的数据行。用于删除表中的所有行，并开释包罗该表的存储空间。删除数据后，可以提交大概回滚。操作无法回滚。属于数据操作语言（DML）。属于数据界说语言（DDL）。删除数据较多时比较慢。实行速率很快。 通常来说，利用 DELETE 语句删除数据时需要指定一个 WHERE 条件，否则会删除表中所有的数据；利用 TRUNCATE 语句需要警惕，因为它会直接清空数据。
86. 什么是 MERGE 大概 UPSERT 操作？
答案：MERGE 是 SQL:2003 标准中引入的一个新的数据操作命令，它可以同时完成 INSERT 和 UPDATE 的操作，乃至 DELETE 的功能。
基本的 MERGE 语句如下：
MEGRE INTO target_table
USING source_table
ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = expr_1,
column2 = expr_2,
...
WHEN NOT MATCHED THEN
INSERT (column1, column2, ...)
VALUES (expr_1, expr_2, ...);
此中，target_table 是合并的目标表；USING 指定了数据的来源，可以是一个表大概查询结果集；ON 指定了合并操作的判断条件，对于数据源中的每一行，如果在目标表中存在满足条件的记录，实行 UPDATE 操作更新目标表中对应的记录；如果不存在匹配的记录，实行 INSERT 在目标表中插入一条新记录。
Oracle 提供了 MERGE 语句的支持，MySQL 利用另一种专用的 UPSERT 语法：
INSERT INTO target_table (column1, column2, ...)
SELECT col1, col2, ...
FROM source_table s
ON DUPLICATE KEY UPDATE
column1 = s.col1,
column2 = s.col2,
...;
87. 什么是索引？有哪些范例的索引？
答案：索引（Index）是一种数据结构，重要用于提高查询的性能。索引类似于书籍最后的索引，它指向了数据的实际存储位置；索引需要占用额外的存储空间，在举行数据的操作时需要额外的维护。另外，索引也用于实现束缚，例如唯一索引用于实现唯一束缚和主键束缚。
不同的数据库支持的索引不尽相同，但是存在一些通用的索引范例，重要包括：
[*]B/B+ 树索引，利用平衡树大概扩展的平衡树结构创建索引。这是最常见的一种索引，险些所有的数据库都支持。这种索引通常用于优化 =、<、<=、>、BETWEEN、IN 以及字符串的前向匹配查询。
[*]Hash 索引，利用数据的哈希值举行索引。重要用于等值（=）查询。
[*]聚集索引，将表中的数据按照索引的结构（通常是主键）举行存储。MySQL 中称为聚集索引，Oracle 中称为索引组织表（IOT）。
[*]非聚集索引，也称为辅助索引。索引与数据相互独立，MySQL 中的 InnoDB 存储的是主键值，Oracle 中存储的时物理地址。
[*]全文索引，用于支持全文搜刮。
[*]唯一索引与非唯一索引。唯一索引可以确保被索引的数据不会重复，可以实现数据的唯一性束缚。非唯一索引仅仅用于提高查询的性能。
[*]单列索引与多列索引。基于多个字段创建的索引称为多列索引，也叫复合索引。
[*]函数索引。基于函数大概表达式的值创建的索引。
索引是优化 SQL 查询的一个有效方法，但是索引本身也需要付出肯定的代价，过渡的索引大概给系统带来负面的影响。
88. 如何检察 SQL 语句的实行计划？
答案：查询计划是数据库实行 SQL 的具体方式。包括读取表的方式，利用全表扫描照旧利用索引；表的毗连方式；预计占用的 CPU、IO 等资源。检察查询计划是举行 SQL 性能诊断和优化的基础。所有主流的数据库都提供了类似的检察实行计划的方式：EXPLAIN 命令。
MySQL 检察实行计划：
EXPLAIN
SELECT *
FROM employee e
WHERE emp_id = 5;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra|
--|-----------|-----|----------|-----|-------------|-------|-------|-----|----|--------|-----|
1|SIMPLE |e | |const|PRIMARY |PRIMARY|4 |const| 1| 100| |
由于 emp_id 是主键，实行计划体现通过主键索引（PRIMARY）举行查询。
Oracle 检察实行计划：
EXPLAIN PLAN FOR
SELECT *
FROM employee e
WHERE emp_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------|
Plan hash value: 897659145 |
|
-------------------------------------------------------------------------------------------|
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 56 | 1 (0)| 00:00:01 ||
| 1 |TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 56 | 1 (0)| 00:00:01 ||
|*2 | INDEX UNIQUE SCAN | SYS_C007418 | 1 | | 0 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("EMP_ID"=5) |
同样体现利用了索引唯一扫描（INDEX UNIQUE SCAN）的方式。
另外，也可以通过一些图形工具大概数据库的其他方式检察 SQL 语句的实行计划。
89. 以下查询语句会不会利用索引？
CREATE INDEX idx ON test (col);
SELECT COUNT(*)
FROM test
WHERE col * 12 = 2400；
答案：不会。
解析：针对索引字段举行运算大概利用函数之后，会导致无法利用索引。可以将运算改到操作符的右边：
SELECT COUNT(*)
FROM test
WHERE col= 2400 / 12；
90. 针对以下查询，如何创建索引？
SELECT *
FROM test
WHERE col1 = 100
AND col2 = 'SQL'
SELECT *
FROM test
WHERE col2 = 'NoSQL';
答案：创建一个复合索引，而且将 col2 放在前面：
CREATE INDEX idx ON test (col2, col1);
解析：创建复合索引时需要注意字段的次序。当查询条件利用索引左侧的字段时，可以有效的利用索引。
91. 员工表的 email 字段上存在唯一索引，以下查询会不会利用该索引？
SELECT *
FROM employee e
WHERE email LIKE 'zhang%';
答案：会。
解析：对于 LIKE 运算符，如果通配符不在最左侧，可以利用索引。但是 ‘%zhang’ 和 ‘%zhang%’ 无法利用索引。
92. 多表毗连查询有哪三种实行方式？
答案：数据库在实际实行毗连查询时，可以采用以下三种物理方式：
[*]嵌套循环毗连（Nested Loop Join），针对驱动表中的每条记录，遍历另一个表找到匹配的数据，相当于两层循环。Nested Loop Join 实用于驱动表数据比较少，而且毗连的表中有索引的时候。
[*]排序合并毗连（ Sort Merge Join），先将两个表中的数据基于毗连字段举行排序，然后合并。Sort Merge Join 通常用于没有索引，而且数据已经排序的情况，比较少见。
[*]哈希毗连（Hash Join），将一个表的毗连字段盘算出一个哈希表，然后从另一个表中一次获取记录并盘算哈希值，根据两个哈希值来匹配符合条件的记录。Hash Join 对于数据量大，且没有索引的情况下大概性能更好。
MySQL 目前只支持 Nested Loop Join，不建议利用多个表的毗连查询，因为多层循环嵌套会导致查询性能的急剧下降。
93. 什么是视图？
答案：视图（View）是一个存储在数据库中的 SELECT 语句。视图也被称为虚表，在许多情况下可以当作表来利用。视图与表最大的区别在于它自身不包罗数据，数据库中存储的只是视图的界说语句。
视图具有以下优点：
[*]替换复杂查询，淘汰复杂性；
[*]提供一致性接口，实现业务规则；
[*]控制对于表的访问，提高安全性。
但是，利用视图也需要注意以下问题：
[*]不妥利用大概会导致查询的性能问题；
[*]可更新视图（Updatable View）需要满足许多限制条件。
94. 创建一个视图，包罗员工所在部门、所属职位、姓名、性别以及邮箱信息？
答案：
CREATE OR REPLACE VIEW emp_info
AS
SELECT d.dept_name,j.job_title, e.emp_name, e.sex, e.email
FROM employee e
JOIN department d ON (d.dept_id = e.dept_id)
JOIN job j ON (j.job_id = e.job_id);
SELECT *
FROM emp_info
WHERE emp_name = '法正';
解析：视图的界说中可以像其他查询语句一样包罗任意复杂的多表毗连、子查询、以及集合操作等。
95. 什么是可更新视图？
答案：可更新视图是指可以通过对视图的 INSERT、UPDATE、DELETE 等操作，实现对视图对应的基础表的数据修改。通常来说，可更新视图必须是简朴的查询语句，不能包罗以下内容：
[*]聚合函数，例如 SUM、AVG 以及 COUNT 等；
[*]DISTINCT 关键字；
[*]GROUP BY 大概 HAVING 子句；
[*]集合操作符 UNION 等；
[*]不同的数据库特定的限制
简朴来说，大概导致无法通过视图找到对应基础表中的数据的操作都不允许。以下语句创建了一个简朴的视图，只包罗了开发部门的员工信息，而且隐藏了工资等敏感信息：
CREATE OR REPLACE VIEW emp_devp
AS
SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email
FROM employee
WHERE dept_id = 4
WITH CHECK OPTION;
此中的 WITH CHECK OPTION 确保无法通过视图修改超出其可见范围之外的数据。以下是通过该视图修改员工信息的操作：
UPDATE emp_devp
SET email = 'zhaoyun@sanguo.net'
WHERE emp_name = '赵云';
如果尝试更新非开发部门的员工，不会更新到任何数据：
UPDATE emp_devp
SET email = 'zhangfei@sanguo.net'
WHERE emp_name = '张飞';
96. 什么是存储过程？
答案：存储过程（Stored Procedure）是存储在数据库中的步伐，它是数据库对 SQL 语句的扩展，提供了许多过程语言的功能，例如变量界说、条件控制语句、循环语句、游标以及异常处理惩罚等等。一旦创建之后，应用步伐（Java、C++ 等）可以通过名称调用存储过程。
存储过程的优点包括：
[*]提高应用的实行效率。存储过程经过编译之后存储在数据库中，实行时可以举行缓存，可以提高实行的速率；
[*]淘汰了应用与数据库之间的数据传递。调用存储过程时，只需要传递参数，业务代码已经存在数据中；
[*]存储过程可以实现代码的重用。不同的应用可以共享相同的存储过程；
[*]存储过程可以提高安全性。存储过程实现了代码的封装，应用步伐通过存储过程举行数据访问，而不需要之间操作数据表。
另一方面，存储过程也存在一些缺点：
[*]不同数据库的实现不同，Oracle 中称为 PL/SQL，MySQL 中称为 PSM，其他数据库也都有各自的实现；
[*]存储过程需要占用数据库服务器的资源，包括 CPU、内存等，而数据库的扩展性不如应用；
[*]存储过程的开发和维护需要专业的技能。
是否利用存储过程需要考虑具体的应用场景。对于业务变化快的互联网应用，通常倾向于将业务逻辑放在应用层，便于扩展；而对于传统行业的应用，大概复杂的报表分析，合理利用存储过程可以提高效率。
97. 如何创建存储过程？
答案：利用 CREATE PROCEDURE 语句创建存储过程，不同的数据库存在一些实现上的差异。以下语句创建了一个为员工表增加员工的存储过程：
-- MySQL 实现
DELIMITER $$
CREATE PROCEDURE insert_employee(IN pi_emp_id INT,
IN pi_emp_name VARCHAR(50),
IN pi_sex VARCHAR(10),
IN pi_dept_id INT,
IN pi_manager INT,
IN pi_hire_date DATE,
IN pi_job_id INT,
IN pi_salary NUMERIC,
IN pi_bonus NUMERIC,
IN pi_email VARCHAR(100))
BEGIN
DECLARE EXIT HANDLER FOR 1062
SELECT CONCAT('Duplicate employee: ', pi_emp_id);
INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager,
hire_date, job_id, salary, bonus, email)
VALUES(pi_emp_id, pi_emp_name, pi_sex, pi_dept_id, pi_manager,
pi_hire_date, pi_job_id, pi_salary, pi_bonus, pi_email);
END$$
DELIMITER ;
-- Oracle 实现
CREATE OR REPLACE PROCEDURE insert_employee(IN pi_emp_id INT,
IN pi_emp_name VARCHAR2,
IN pi_sex VARCHAR2,
IN pi_dept_id INT,
IN pi_manager INT,
IN pi_hire_date DATE,
IN pi_job_id INT,
IN pi_salary NUMERIC,
IN pi_bonus NUMERIC,
IN pi_email VARCHAR2)
BEGIN
INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager,
hire_date, job_id, salary, bonus, email)
VALUES(pi_emp_id, pi_emp_name, pi_sex, pi_dept_id, pi_manager,
pi_hire_date, pi_job_id, pi_salary, pi_bonus, pi_email);
EXCEPTION
WHEN dup_val_on_index THEN
RAISE_APPLICATION_ERROR(SQLCODE, 'Duplicate employee: '||pi_emp_id);
WHEN OTHERS THEN
RAISE;
END;
然后可以调用存储过程增加新的员工：
CALL (26, '张三', '男', 5, 2, CURRENT_DATE, 10, 5000, NULL, 'zhangsan@shuguo.com');
98. 如何删除存储过程？
答案：利用 DROP PROCEDURE 命令删除存储过程，利用 DROP FUNCTION 命令删除存储函数。以下语句删除存储过程 insert_employee：
DROP PROCEDURE insert_employee;
99. 什么是触发器？
答案：触发器（Trigger）是一种特殊的存储过程，当某个事件发生的时候自动实行触发器中的操作。最常见的触发器是基于表的触发器，包括 INSERT、UPDATE 和 DELETE 语句触发器。根据触发的时间，又可以分为 BEFORE 和 AFTER 触发器。另外，根据触发的粒度，又可以分为行级触发器和语句级触发器。
触发器范例的应用场景包括：
[*]审计表的数据修改。某些表中大概包罗敏感信息，比如员工的薪水，要求记录所有的修改汗青。这种需求可以通过创建针对员工表的 语句级 UPDATE 触发器实现。
[*]实现复杂的业务束缚。在触发器中增加业务检查和数据验证，制止非法的业务操作。
不过，触发器也大概带来一些问题。比如增加数据库服务器的压力；逻辑隐藏在数据库内部，应用端无法举行控制。
触发器的管理重要包括创建和删除：
[*]CREATE TRIGGER 用于创建触发器。
[*]DROP TRIGGER 用于删除触发器。
另外，Oracle 还支持 DDL 触发器和系统事件触发器。
100. 为员工表创建一个审计表和审计触发器，记录每次修改员工月薪的操作。
答案：
CREATE TABLE employee_audit
( emp_id INTEGER NOT NULL
, salary_old NUMERIC(8,2) NOT NULL
, salary_new NUMERIC(8,2) NOT NULL
, update_tsTIMESTAMP NOT NULL
);
-- MySQL 实现
DELIMITER $$
CREATE TRIGGER employee_audit
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO employee_audit(emp_id, salary_old, salary_new, update_ts)
VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END IF;
END$$
DELIMITER ;
-- Oracle 实现
CREATE OR REPLACE TRIGGER employee_audit
BEFORE UPDATE ON employee
FOR EACH ROW
DECLARE
BEGIN
IF :OLD.salary <> :NEW.salary THEN
INSERT INTO employee_audit(emp_id, salary_old, salary_new, update_ts)
VALUES(:OLD.emp_id, :OLD.salary, :NEW.salary, CURRENT_TIMESTAMP);
END IF;
END;
不同的数据库在语法上存在一些差异，但是基本的原理相同。然后可以修改员工的月薪，而且检察审计的结果：
UPDATE employee
SET salary = salary + 1000
WHERE emp_name = '张飞';
SELECT * FROM employee_audit;
总结
作为一份 SQL 常见面试题解析，本文重要给各人梳理了 SQL 中常见的各种数据操作以及数据库计划和开发的基本知识，并提供了 Oracle 和 MySQL 中的具体实现。SQL 是一门面向集合的编程语言，通过简朴的声明就可以完成各种数据的操作；但是这种简朴性也有大概导致性能问题。因此，理解数据库的原理和性能优化是学习进阶的必要技能。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]