本文介绍并分析了 100 道常见 SQL 面试题，主要分为三个模块:SQL 初级查询、SQL 高级查询以及数据库设计与开发。
本文主要使用三个示例表:员工表 (employee)、部门表 (department) 和职位表 (job)。
下面是这些示例表的结构：
答案:
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 等，就可以完成大部分的数据操作。
答案:
SELECT emp_name, sex FROM employee;
解析:SQL 使用 SELECT 和 FROM 查询表中的字段，多个字段使用逗号分隔。
答案:
SELECT * FROM employee;
或者:
SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email FROM employee;
解析:SQL 查询中的星号 (*) 表示查询所有字段，可以方便快速查询数据;但是在产品中不推荐使用，因为星号可能带来不 确定性。
答案:
SELECT emp_name, salary * 12 + COALESCE(bonus, 0) FROM employee;
解析:查询结果中可以使用各种运算、函数以及表达式。COALESCE 函数用于将空值转换为 0。
答案:
SELECT emp_name AS "姓名", salary * 12 + COALESCE(bonus, 0) "年薪" FROM employee;
解析:SQL 中的别名可以为查询中的表或结果指定一个临时名称。别名使用关键字 AS 表示，可以省略。
答案:
SELECT *
FROM employee WHERE sex = '女';
解析:SQL 中使用 WHERE 子句指定过滤条件，只有满足条件的数据才会返回。除了等于 (=)，还可以使用大于 (>)、大 于等于 (>=)、小于 (<)、小于等于 (<=) 以及不等于 (!= 或者 ) 这些比较运算符作为过滤条件。
答案:
SELECT *
FROM employee
WHERE salary BETWEEN 8000 AND 12000;
解析:BETWEEN 用于查找范围值，包含两端的值。
答案:
SELECT *
FROM employee
WHERE emp_name IN ('张三', '李四', '张飞');
解析:IN 用于查找列表中的任意值。
答案:
SELECT *
FROM employee
WHERE emp_name LIKE '% 云%';
解析:SQL 中的 LIKE 运算符用于字符串的模式匹配。LIKE 支持两个通配符:% 匹配任意多个字符，_ 匹配单个字符。 Oracle 区分大小写，MySQL 不区分大小写。
答案:
SELECT emp_name,
bonus
FROM employee
WHERE bonus IS NOT NULL;
解析:SQL 中的 NULL 表示空值，意味着缺失或者未知数据。判断空值不能直接使用等于或不等于，而需要使用特殊的 IS NULL 和 IS NOT NULL。
答案:
SELECT emp_name, sex, hire_date FROM employee
WHERE sex = '女'
AND hire_date > DATE '2010-01-01';
解析:AND、OR 和 NOT 表示逻辑与、逻辑或和逻辑非，可以用于构造复杂的查询条件。
SELECT *
FROM employee
WHERE 1 = 0 AND 1/0 = 1;
答案:不会出错，但是查不到任何数据。
解析:因为 SQL 对于逻辑运算符 AND 和 OR 使用短路运算 (short-circuit evaluation)。也就是说，只要前面的表达式能够 决定最终的结果，不执行后面的计算。
答案:
SELECT DISTINCT sex FROM employee;
解析:DISTINCT 用于消除查询结果中的重复值，上面的查询只返回两个不同的性别记录。
答案:
SELECT *
FROM employee
ORDER BY salary DESC;
解析:ORDER BY 子句用于对查询结果进行排序;ASC 表示升序，DESC 表示降序。
答案:
SELECT *
FROM employee
ORDER BY salary DESC, bonus DESC;
解析:按照多个字段排序时，使用逗号分隔;排序时先按照第一个条件排列，对于排名相同的数据，再按照第二个条件排列， 以此类推。
答案:
-- 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 可以指定排 序规则。
SELECT *
FROM employee ORDER BY bonus;
没有奖金的员工排在最前面还是最后面? 答案:取决于数据库的实现。
解析:对于 MySQL ，升序时 NULL 值排在最前面，降序时 NULL 值排在最后面。对于 Oracle，默认升序排序时时 NULL 值 排在最后面，降序时 NULL 值排在最前面;还可以使用 NULLS FIRST 或 NULLS LAST 指定 NULL 值排在最前或最后。
-- 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。
答案:
-- Oracle 12c 实现
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH NEXT 3 ROWS WITH TIES;
解析:FETCH 子句支持 WITH TIES 选项，用于返回更多排名相同的数据。另外，还可以使用 PERCENT 按照百分比返回数 据。
-- 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 关键字指定一个偏移量，表示忽略前面多少行数据，然后返回结果。
答案:函数是一种功能模块，可以接收零个或多个输入值，并且返回一个输出值。
在 SQL 中，函数主要分为两种类型:标量函数 (scalar function) 和聚合函数 (aggregate function)。标量函数针对每一行 输入参数，返回一行输出结果。例如，ABS 函数可以计算绝对值。聚合函数针对一组数据进行操作，并且返回一个汇总结 果。例如，AVG 函数可以计算一组数据的平均值。
答案:
SELECT emp_name, length(email) FROM employee;
解析:length 函数用于返回字符长度。需要注意的是，Oracle 是按照字符数量计算，lengthb 按照字节计算;MySQL 是按照 字节数量计算，char_length 按照字符数量计算。对于汉字这种多字节字符需要注意区分。
答案:
SELECT emp_name, email
FROM employee
WHERE UPPER(email) = 'GUANXING@SHUGUO.COM';
解析:UPPER 函数用于将字符串转换为大写形式。另外，LOWER 函数用于将字符串转换为小写形式。
答案:
-- MySQL 实现
SELECT CONCAT_WS(',' emp_name, sex, salary) FROM employee;
-- Oracle 实现
SELECT emp_name||','||sex||','||salary FROM employee;
解析:CONCAT 函数用于连接两个字符串。MySQL 中的 CONCAT_WS 扩展了该功能;Oracle 支持使用 || 连接字符串。
答案:
SELECT emp_name, SUBSTR(email, 1, INSTR(email,'@') - 1) FROM employee;
解析:此处使用了两个字符串函数，INSTR 函数查找 @ 符号的位置，SUBSTR 函数获取该位置之前的子串。
答案:
SELECT emp_name, REPLACE(EMAIL, '.com','.net') FROM employee;
解析:REPLACE 函数用于替换字符串中的字串。另外，TRIM 函数用于截断字符串。
答案:
-- 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 函数。
答案:
SELECT CEILING(1.1), FLOOR(1.1), ROUND(1.1) FROM employee
WHERE emp_id = 1;
解析:CEILING 向上取整，FLOOR 向下取整，ROUND 四舍五入。Oracle 中使用 CEIL 函数替代 CEILING。
答案:
SELECT student_id, GREATEST(chinese, math, english, history) FROM score;
解析:GREATEST 函数用于返回列表中的最大值，LEAST 函数用于返回列表中的最小值。
答案:
SELECT emp_name, EXTRACT( year FROM CURRENT_DATE) - EXTRACT( year FROM HIRE_DATE) FROM employee;
解析:CURRENT_DATE 函数返回当前日期，EXTRACT 函数可以提取日期数据中的各个部分，本例中使用 year 参数获取年 份信息。
答案:
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 进行数据分析的必备技能之一。
答案:
SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employee;
解析:聚合函数针对一组数据计算出单个结果值。常见的聚会函数包括:
AVG – 计算一组值的平均值。 COUNT – 统计某个字段的行数。 MIN – 返回一组值中的最小值。 MAX – 返回一组值中的最大值。 SUM – 计算一组值的和值。
答案:结果不同，COUNT() 返回 25 条记录，COUNT(bonus) 返回 9 条记录。
解析:除了 COUNT () 之外，其他聚合函数都会忽略字段中的 NULL 值。另外，聚合函数中的 DISTINCT 选项可以在计算之 前排除重复值。
答案:
-- MySQL 实现
SELECT GROUP_CONCAT(email SEPARATOR ';') FROM employee;
-- Oracle 实现
SELECT LISTAGG(email, '; ') WITHIN GROUP (ORDER BY NULL) FROM employee;
解析:使用字符串的聚合函数将多个字符串合并成一个。MySQL 中使用 GROUP_CONCAT 函数，Oracle 使用 LISTAGG 函 数。
答案:
SELECT dept_id, COUNT(*), AVG(salary) FROM employee
GROUP BY dept_id;
解析:SQL 中使用 GROUP BY 进行数据的分组，结合聚合函数可以获得分组后的统计信息。另外，可以使用多个字段分成 更多的组。
SELECT dept_id, COUNT(*), emp_name FROM employee
GROUP BY dept_id;
答案:不能运行。
解析:使用了 GROUP BY 分组之后，SELECT 列表中只能出现分组字段和聚合函数，不能再出现其他字段。上面的语句中， 按照部门分组后，再查看员工姓名的话，存在逻辑上的错误。因为每个部门有多个员工，应该显示哪个员工呢?
答案:
SELECT dept_id, AVG(salary) FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 10000;
解析:HAVING 子句用于对分组后的结果进行过滤，它必须跟在 GROUP BY 之后。
答案:
SELECT dept_id, COUNT() FROM employee
WHERE salary > 5000 GROUP BY dept_id HAVING COUNT() > 5;
解析:WHERE 用于对表中的数据进行过滤，HAVING 用于对分组后的数据进行过滤，两者可以结合使用。
答案:
连接 (join) 查询是基于两个表中的关联字段将数据行拼接到一起，可以同时返回两个表中的数据。SQL 支持以下连 接:
内连接 (INNER JOIN)，用于返回两个表中满足连接条件的数据行。
左外连接 (LEFT OUTER JOIN)，返回左表中所有的数据行;对于右表中的数据，如果没有匹配的值，返回空值。 右外连接 (RIGHT OUTER JOIN)，返回右表中所有的数据行;对于左表中的数据，如果没有匹配的值，返回空值。 全外连接 (FULL OUTER JOIN)，等价于左外连接加上右外连接，返回左表和右表中所有的数据行。MySQL 不支持全外连 接。
交叉连接 (CROSS JOIN)，也称为笛卡尔积 (Cartesian product)，两个表的笛卡尔积相当于一个表的所有行和另一个表的 所有行两两组合，结果的数量为两个表的行数相乘。
自连接 (Self Join)，是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接。自连接主要用于处理那些对自己 进行了外键引用的表。
答案:可以使用以下两种连接语句:
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 的连接方式，语义上更清晰。
答案:
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;
解析:由于某些部门可能还没有员工，不能使用内连接，而需要使用左外连接或者右外连接;否则可能缺少某些部门的结果。
答案:
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;
解析:通过自连接关联两个员工表，使用左连接是因为有一个员工没有上级，他就是公司的最高领导。
答案:
SQL 中提供了以下三种集合运算:
并集运算 (UNION、UNION ALL)，将两个查询结果合并成一个结果集，包含了第一个查询结果以及第二个查询结果中的数 据。 交集运算 (INTERSECT)，返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据。 MySQL 不支持 INTERSECT。 差集运算 (EXCEPT)，返回出现在第一个查询结果中，但不在第二个查询结果中的数据。MySQL 不支持 EXCEPT，Oracle 使用 MINUS 替代 EXCEPT。
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 MINUS SELECT id FROM t2;
答案:
结果分别为 (1、2、3)、(1、1、2、3)、(1) 以及 (2)。
解析:UNION 的结果集中删除了重复的数据，UNION ALL 保留了所有的数据。
答案:
-- 使用连接查询实现交集运算
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;
解析:交集运算等价于基于所有字段的内连接查询，差集运算等价于左连接中右表字段为空的结果。
答案:
子查询 (subquery) 是指嵌套在其他语句 (SELECT、INSERT、UPDATE、DELETE、MERGE) 中的 SELECT 语 句。子查询中也可以嵌套另外一个子查询，即多层子查询。
子查询可以根据返回数据的内容分为以下类型:
标量子查询 (scalar query):返回单个值 (一行一列) 的子查询。上面的示例就是一个标量子查询。 行子查询 (row query):返回包含一个或者多个值的单行结果 (一行多列)，标量子查询是行子查询的特例。 表子查询 (table query):返回一个虚拟的表 (多行多列)，行子查询是表子查询的特例。
基于子查询和外部查询的关系，也可以分为以下两类:关联子查询 (correlated subqueries) 和非关联子查询 (non- correlated subqueries)。关联子查询会引用外部查询中的列，因而与外部查询产生关联;非关联子查询与外部查询没有关 联。
答案:
SELECT emp_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
解析:使用子查询获得平均月薪，然后在外部查询中的 WHERE 条件中使用该值。这是一个非关联的标量子查询。
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 用于查询不在列表中的值。
答案:
SELECT emp_name, salary
FROM employee e
WHERE salary > (SELECT AVG(salary) FROM employee
WHERE dept_id = e.dept_id);
解析:使用关联子查询获取每个员工所在部门的平均月薪，然后传递给外部查询进行判断。
答案:
SELECT emp_name,
(SELECT COUNT(*)
FROM employee
WHERE dept_id = e.dept_id) AS dept_count FROM employee e;
解析:SELECT 列表中同样可以使用关联子查询。
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) 中的数据。不过，使
用横向 (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);
答案:
SELECT *
FROM department d WHERE EXISTS (SELECT 1 FROM employee e
WHERE e.sex ='女'
AND e.dept_id = d.dept_id);
解析:EXISTS 运算符用于检查子查询中结果的存在性。针对外部查询中的每条记录，如果子查询存在结果 (部门中存在女性 员工)，外部查询即返回结果。NOT EXISTS 执行相反的操作。
答案:
-- MySQL 实现
SELECT dept_id, job_id, COUNT()
FROM employee
GROUP BY dept_id, job_id WITH ROLLUP;
-- Oracle 实现
SELECT dept_id, job_id, COUNT() FROM employee
GROUP BY ROLLUP (dept_id, job_id);
解析:GROUP BY 支持扩展的 ROLLUP 选项，可以生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总 计。MySQL 中使用 WITH ROLLUP，与 SQL 标准不太一致。
解析:CUBE 用于生成多维立方体式的汇总统计。例如，以下查询统计不同部门和职位员工的数量，同时统计部门所有职位的
员工数据，加上所有职位的员工数据，以及整个公司的员工数量。
-- Oracle 实现
SELECT dept_id, job_id, COUNT() 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;
查询结果如下图所示。
解析:GROUPING 函数用于判断某个统计结果是否与该字段有关。如果是，函数返回 0;否则返回 1。比如第 3 行数据是所
答案:
-- 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 即可。
答案:
-- 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;
查询结果如下 (显示部分内容): 解析:同样是利用递归通用表表达式实现数据的遍历。Oracle 中省略 RECURSIVE 即可。
通用表表达式是 SQL 中非常强大的功能，可以帮助我们简化复杂的连接查询和子查询，并且可以完成递归处理和层次遍历。
答案:窗口函数 (Window function) 也称为分析函数。与聚合函数类似，窗口函数也是基于一组数据进行分析;但是，窗口 函数针对每一行数据都会返回一个结果。窗口函数为 SQL 提供了强大的数据分析功能。
专用窗口函数主要包括 ROW_NUMBER、RANK、DENSE_RANK、PERCENT_RANK、CUME_DIST、NTH_VALUE、 NTILE、FIRST_VALUE、LAST_VALUE、LEAD 以及 LAG 等。
答案:
SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id) FROM employee;
解析:窗口函数 AVG 基于部门 (dept_id) 分组后的数据计算平均月薪，为每个员工返回一条记录。窗口函数中的 PARTITION BY 作用类似于 GROUP BY 子句。虽然也可以使用关联子查询与聚合函数实现相同的功能，显然窗口函数更加简 单易懂。
答案:
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)。
答案:
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 用于返回排名中相对于当前行的指定偏移量之前和之后的数据。
答案:
SELECT emp_name, dept_id, salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY NULL ROWS UNBOUNDED PRECEDING) FROM employee;
解析:窗口函数支持定义窗口范围，UNBOUNDED PRECEDING 表示从分组内的第一行到当前行，可以用于计算累计值。
答案:
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 也增加了对于窗口函数的支持。
设计与开发
答案:
数据库 (Database) 是各种数据的集合，按照一定的数据结构进行存储和管理;数据库管理系统 (Database Management System) 是用于管理数据库的软件，负责数据库的创建、查询、修改等管理操作。这两者共同构成了数据库系 统 (Database System)。应用程序或者最终用户通过 DBMS 访问和管理数据库。
答案:
关系数据库是指基于关系模型的数据库。在关系模型中，用于存储数据的逻辑结构就是二维表 (Table)。表由行和列 组成，行也称为记录，代表了单个实体;列也称为字段，代表了实体的某些属性。关系数据库使用 SQL 作为标准语言，执行 数据的增删改查以及各种管理操作。关系数据库还定义了三种约束完整性:实体完整性、参照完整性以及用户定义完整性。
大多数主流数据库都属于关系数据库，例如 Oracle、MySQL、SQL Server 以及 PostgreSQL 等。另外，数据库领域还存在 一些非关系模型的数据库 (NoSQL )，例如 Mongodb、Redis、Cassandra 等。
答案:
关系数据库定义了以下约束:
非空约束 (NOT NULL)，用于限制字段不会出现空值。比如员工姓名不能为空。
唯一约束 (UNIQUE)，用于确保字段中的值不会重复。例如，每个员工的电子邮箱不能重复。每个表可以有多个唯一约束。
主键约束 (Primary Key)，主键是唯一标识表中每一行的字段。例如员工编号，部门编号等。主键字段必须唯一且非空，每 个表可以有且只能有一个主键。
外键约束 (FOREIGN KEY)，用于表示两个表之间的引用关系。例如，员工属于部门，因此员工表中的部门编号字段可以定 义为外键，它引用了部门信息表中的主键。
检查约束 (CHECK)，可以定义更多用户自定义的业务规则。例如，薪水必须大于 0 ，性别只能是男和女等。 默认值 (DEFAULT)，用于向字段中插入默认数据。
答案:
OLTP OLAP 在线事务处理系统 在线分析处理系统 专注于事务数据的增删改，事务相对简单但频繁，要求响应时间快 专注于决策数据分析，查询通常比 较复杂，处理时间长 数据来源于在线业务 数据来源于各种 OLTP 通常采用规范化的设计，需要保证数据的完整性 不需要太多规范化，可以存储冗余信 息，采用多维数据模型 常见应用包括银行 ATM、在线订票系统、网上商城 常见应用包括数据仓库、报表分析、商务智能
答案:
数据库规范化是一种数据库设计的方法，用于有效地组织数据，减少数据的冗余和相互之间的依赖，增加数据的一致
性。由于非规范化的数据库存在冗余，可能导致数据的插入、删除、修改异常等问题，因此引入了规范化过程。 数据库规范化的程度被称为范式 (Normal Form)，目前已经存在第一范式到第六范式，每个范式都是基于前面范式的增强。
第一范式 (First Normal Form)，表中的每个属性都是单值属性，每个记录都唯一，也就是需要主键。举例来说，如果员工 存在工作邮箱和个人邮箱，不能都放到一个字段，而需要拆分成两个字段;
第二范式 (Second Normal Form)，首先需要满足第一范式，且不包含任何部分依赖关系。举例来说，如果将学生信息和选 课信息放在一起，学号和课程编号可以作为复合主键;但此时学生的其他信息依赖于学号，即主键的一部分。通常使用单列主 键可以解决部分依赖问题;
第三范式 (Third Normal Form)，首先需要满足第二范式，并且不存在传递依赖关系。举例来说，如果将部门信息存储在每 个员工记录的后面，那么部门名称依赖部门编号，部门编号又依赖员工编号，这就是传递依赖。解决的方法就是将部门信息单 独存储到一个表中;
更高的范式包括 Boyce-Codd 范式、第四范式、第五范式以及第六范式等，不过很少使用到这些高级范式。对于大多数系统而 言，满足第三范式即可。
另外，反规范化 (Denormalization) 是在完成规范化之后执行的相反过程。反规范化通过增加冗余信息，减少 SQL 连接查询 的次数，从而减少磁盘 IO 来提高查询时的性能。但是反规范化会导致数据的重复，需要更多的磁盘空间，并且增加了数据维 护的复杂性。
数据库的设计是一个复杂的权衡过程，需要综合考虑各方面的因素。
答案:实体关系图是一种用于数据库设计的结构图，它描述了数据库中的实体，以及这些实体之间的相互关系。实体代表了一 种对象或者概念。例如，员工、部门和职位可以称为实体。每个实体都有一些属性，例如员工拥有姓名、性别、工资等属性。
关系用于表示两个实体之间的关联。例如，员工属于部门。三种主要的关系是一对一、一对多和多对多关系。例如，一个员工 只能属于一个部门，一个部门可以有多个员工，部门和员工是一对多的关系。
ERD 也可以按照抽象层次分为三种:
概念 ERD，即概念数据模型。概念 ERD 描述系统中存在的业务对象以及它们之间的关系。
逻辑 ERD，即逻辑数据模型。逻辑 ERD 是对概念数据模型进一步的分解和细化，明确定义每个实体中的属性并描述操作和事 务。
物理 ERD，即物理数据模型。物理 ERD 是针对具体数据库的设计描述，需要为每列指定类型、长度、可否为空等属性，为表
增加主键、外键以及索引等约束。
下图是我们使用的三个示例表的物理 ERD(基于 MySQL 实现):
答案:
表 (Table)、视图 (View)、序列 (Sequence)、索引 (Index)、存储过程 (Stored Procedure)、触发器 (Trigger)、用户 (User) 以及同义词 (Synonym) 等等。其中，表是关系数据库中存储数据的主要形式。
答案:
SQL 定义了大量的数据类型，其中最常见的类型包括字符类型、数字类型、日期时间类型和二进制数据类型。
日期时间类型，分为日期 DATE、时间 TIME 以及时间戳 TIMESTAMP 。 二进制数据类型，主要是 BLOB。用于存储图片、文档等二进制数据。
主流的数据库都支持这些常见的数据类型，但是在类型名称和细节上存在一些差异。另外，SQL 还提供其他的数据类型，例 如 XML、JSON 以及自定义的数据类型。
答案:
CAHR 是固定长度的字符串，如果输入的内容不够使用空格进行填充，通常用于存储固定长度的编码;VARCHAR 是
可变长度的字符串，通常用于存储姓名等长度不一致的数据。Oracle 中使用 VARCHAR2 表示变长字符串。
答案:
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_name VARCHAR(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) );
答案:
使用以下语句可以基于已有的表或者查询语句复制一个表:
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;
答案:自增列 (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 | 王五
插入数据时，不需要为自增列提供输入值，系统自动生成一个增长的数字序列。
答案:
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 支持级联删除选项，同时删 除父表和子表:
-- Oracle 实现
DROP TABLE department CASCADE CONSTRAINTS;
答案:
DROP TABLE 用于从数据库中删除表，包括表中的数据和表结构自身。同时还会删除与表相关的的所有对象，包括索 引、约束以及访问该表的授权。TRUNCATE TABLE 只是快速删除表中的所有数据，回收表占用的空间，但是会保留表的结 构。
答案:在数据库中，事务 (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，持久性。已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库都不会丢失数据。数据库通常 使用重做日志 (redo log) 来保证数据的持久性。
答案:
当数据库存在并发访问时，可能导致以下问题:
更新丢失，当两个事务同时更新某一数据时，后者会覆盖前者的结果; 脏读，当一个事务正在操作某些数据但并未提交时，如果另一个事务读取到了未提交的结果，就出现了脏读; 不可重复读，第一个事务第一次读取某一记录后，该数据被另一个事务修改提交，第一个事务再次读取该记录时结果发生了改 变; 幻象读，第一个事务第一次读取数据后，另一个事务增加或者删除了某些数据，第一个事务再次读取时结果的数量发生了变 化。
为了解决并发访问可能导致的问题，数据库提供了不同的事务隔离级别:
脏读 不可重复读 幻读 读未提交 可能 可能 可能 读已提交 不会 可能 可能 可重复读 不会 不会 可能 序列化 不会 不会 不会
Oracle 默认的隔离级别为 READ COMMITTED，MySQL 中 InnoDB 存储引擎的默认隔离级别为 REPEATABLE READ。
答案:MySQL 的一大特点就是支持不同的存储引擎，存储引擎用于管理表中的数据并提供数据操作接口。MySQL 使用以下命 令查看系统支持的存储引擎:
SHOW ENGINES;
主要的存储引擎包括 InnoDB 和 MyISAM。自从 MySQL 5.5 版本之后，默认使用 InnoDB 存储引擎。
InnoDB 存储引擎支持事务 (ACID)，提供了事务提交、回滚以及故障恢复能力，能够确保数据不会丢失。InnoDB 支持行级 锁和多版本一致性的非锁定读取，能够提高并发访问和性能。InnoDB 使用聚集索引存储数据，能够减少使用主键查找时的磁 盘 I/O。另外，InnoDB 还支持外键约束，能够维护数据的完整性。
MyISAM 存储引擎数据文件占用的空间更小。MyISAM 采用表级锁，限制了同时读写的性能，通常用于只读或者以读为主的应 用。
下表是两者对于各种功能特性的支持比较。
特性 MyISAM InnoDB B 树索引 支持 支持 备份/时间点恢复 支持 支持 聚集索引 不支持 支持 压缩数据 支持 支持 数据缓存 不支持 支持 加密数据 支持 支持 外键支持 不支持 支持 全文搜索索引 支持 支持 空间数据类型 支持 支持 空间数据索引 支持 支持 哈希索引 不支 持 不支持 索引缓存 支持 支持 锁定级别 表级 行级 MVCC 不支持 支持 复制 支持 支持 存储限制 256TB 64TB 数据库事务 不支持 支 持
一般情况下，使用默认的 InnoDB 存储引擎即可，除非是有特殊的需求和应用场景。
答案:
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 中。
答案:SQL 中的 UPDATE 语句用于更新表中的数据:
UPDATE table_name SET column1 = expr1, column2 = expr2,
...
[WHERE condition];
其中，table_name 是要更新的表名;SET 子句指定了要更新的列和更新后的值，多个字段使用逗号进行分隔;满足 WHERE 条件的数据行才会被更新，如果没有指定条件，将会更新表中所有的行。以下语句为表 emp_new 中的员工 “赵云” 增加 1000 元的月薪:
UPDATE emp_new
SET salary = salary + 1000 WHERE emp_name = '赵云';
答案:
SQL 中用于删除数据的命令主要是 DELETE 语句。
DELETE FROM table_name [WHERE conditions];
DELETE 语句删除满足条件的数据;如果不指定 WHERE 子句，将会删除表中的所有数据。以下语句将会清空表 emp_new 中的所有数据:
DELETE FROM emp_new;
Oracle 中可以省略 FROM 关键字。
DELETE TRUNCATE 用于从表中删除指定的数据行。 用于删除表中的所有行，并释放包含该表的存储空间。 删除数据后，可以提交或者回滚。 操作无 法回滚。 属于数据操作语言 (DML)。 属于数据定义语言 (DDL)。 删除数据较多时比较慢。 执行速度很快。
通常来说，使用 DELETE 语句删除数据时需要指定一个 WHERE 条件，否则会删除表中所有的数据;使用 TRUNCATE 语句 需要小心，因为它会直接清空数据。
答案:
MERGE 是 SQL:2003 标准中引入的一个新的数据操作命令，它可以同时完成 INSERT 和 UPDATE 的操作，甚至
DELETE 的功能。
基本的 MERGE 语句如下:
MEGRE INTO target_table [AS t_alias] USING source_table [AS s_alias] 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,
...;
答案:
索引 (Index) 是一种数据结构，主要用于提高查询的性能。索引类似于书籍最后的索引，它指向了数据的实际存储位 置;索引需要占用额外的存储空间，在进行数据的操作时需要额外的维护。另外，索引也用于实现约束，例如唯一索引用于实 现唯一约束和主键约束。
不同的数据库支持的索引不尽相同，但是存在一些通用的索引类型，主要包括:
B/B+ 树索引，使用平衡树或者扩展的平衡树结构创建索引。这是最常见的一种索引，几乎所有的数据库都支持。这种索引通 常用于优化 =、<、、BETWEEN、IN 以及字符串的前向匹配查询。
Hash 索引，使用数据的哈希值进行索引。主要用于等值 (=) 和 IN 查询。 聚集索引，将表中的数据按照索引的结构 (通常是主键) 进行存储。MySQL 中称为聚集索引，Oracle 中称为索引组织表 (IOT)。
非聚集索引，也称为辅助索引。索引与数据相互独立，MySQL InnoDB 中的索引存储的是主键值，Oracle 中存储的时物理地 址。
全文索引，用于支持全文搜索。 唯一索引与非唯一索引。唯一索引可以确保被索引的数据不会重复，可以实现数据的唯一性约束。非唯一索引仅仅用于提高查 询的性能。
单列索引与多列索引。基于多个字段创建的索引称为多列索引，也叫复合索引。 函数索引。基于函数或者表达式的值创建的索引。
索引是优化 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) 进行查询。
另外，也可以通过一些图形工具或者数据库提供的其他方式查看 SQL 语句的执行计划。
CREATE INDEX idx ON test (col); SELECT COUNT()
FROM test
WHERE col * 12 = 2400;
答案:
不会。
解析:针对索引字段进行运算或者使用函数之后，会导致无法使用索引。可以将运算改到操作符的右边:
SELECT COUNT() FROM test
WHERE col = 2400 / 12;
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%’ 无法使用索引。
答案:
数据库在实际执行连接查询时，可以采用以下三种物理方式:
嵌套循环连接 (Nested Loop Join)，针对驱动表中的每条记录，遍历另一个表找到匹配的数据，相当于两层循环。Nested Loop Join 适用于驱动表数据比较少，并且连接的表中有索引的时候。
排序合并连接 ( Sort Merge Join)，先将两个表中的数据基于连接字段进行排序，然后合并。Sort Merge Join 通常用于没有 索引，并且数据已经排序的情况，比较少见。
哈希连接 (Hash Join)，将一个表的连接字段计算出一个哈希表，然后从另一个表中一次获取记录并计算哈希值，根据两个 哈希值来匹配符合条件的记录。Hash Join 对于数据量大，且没有索引的情况下可能性能更好。
MySQL 目前只支持 Nested Loop Join，不建议使用多个表的连接查询，因为多层循环嵌套会导致查询性能的急剧下降。
答案:
视图 (View) 是一个存储在数据库中的 SELECT 语句。视图也被称为虚表，在许多情况下可以当作表来使用。视图与 表最大的区别在于它自身不包含数据，数据库中存储的只是视图的定义语句。
视图具有以下优点:
替代复杂查询，减少复杂性;
提供一致性接口，实现业务规则;
控制对于表的访问，提高安全性。
但是，使用视图也需要注意以下问题: 不当使用可能会导致查询的性能问题;
可更新视图 (Updatable View) 需要满足许多限制条件。
答案:
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 = '法正';
解析:视图的定义中可以像其他查询语句一样包含任意复杂的多表连接、子查询、以及集合操作等。
答案:
可更新视图是指可以通过对视图的 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 = '张飞';
答案:
存储过程 (Stored Procedure) 是存储在数据库中的程序，它是数据库对 SQL 语句的扩展，提供了许多过程语言的功 能，例如变量定义、条件控制语句、循环语句、游标以及异常处理等等。一旦创建之后，应用程序 (Java、C++ 等) 可以通 过名称调用存储过程。
存储过程的优点包括:
提高应用的执行效率。存储过程经过编译之后存储在数据库中，执行时可以进行缓存，可以提高执行的速度; 减少了应用与数据库之间的数据传递。调用存储过程时，只需要传递参数，业务代码已经存在数据中; 存储过程可以实现代码的重用。不同的应用可以共享相同的存储过程; 存储过程可以提高安全性。存储过程实现了代码的封装，应用程序通过存储过程进行数据访问，而不需要之间操作数据表。
另一方面，存储过程也存在一些缺点:
不同数据库的实现不同，Oracle 中称为 PL/SQL，MySQL 中称为 PSM，其他数据库也都有各自的实现; 存储过程需要占用数据库服务器的资源，包括 CPU、内存等，而数据库的扩展性不如应用; 存储过程的开发和维护需要专业的技能。
是否使用存储过程需要考虑具体的应用场景。对于业务变化快的互联网应用，通常倾向于将业务逻辑放在应用层，便于扩展; 而对于传统行业的应用，或者复杂的报表分析，合理使用存储过程可以提高效率。
答案:使用 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');
答案:
使用 DROP PROCEDURE 命令删除存储过程，使用 DROP FUNCTION 命令删除存储函数。以下语句删除存储过程 insert_employee:
DROP PROCEDURE insert_employee;
答案:
触发器 (Trigger) 是一种特殊的存储过程，当某个事件发生的时候自动执行触发器中的操作。最常见的触发器是基于 表的触发器，包括 INSERT、UPDATE 和 DELETE 语句触发器。根据触发的时间，又可以分为 BEFORE 和 AFTER 触发 器。另外，根据触发的粒度，又可以分为行级触发器和语句级触发器。
触发器典型的应用场景包括:
审计表的数据修改。某些表中可能包含敏感信息，比如员工的薪水，要求记录所有的修改历史。这种需求可以通过创建针对员 工表的 语句级 UPDATE 触发器实现。
实现复杂的业务约束。在触发器中增加业务检查和数据验证，阻止非法的业务操作。
不过，触发器也可能带来一些问题。比如增加数据库服务器的压力;逻辑隐藏在数据库内部，应用端无法进行控制。 触发器的管理主要包括创建和删除:
CREATE TRIGGER 用于创建触发器。
DROP TRIGGER 用于删除触发器。
另外，Oracle 还支持 DDL 触发器和系统事件触发器。
答案:
CREATE TABLE employee_audit
( emp_id INTEGER NOT NULL
, salary_old NUMERIC(8,2) NOT NULL , salary_new NUMERIC(8,2) NOT NULL , update_ts TIMESTAMP 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;