部门表（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）。主键为员工编号，部门编号字段是引用部门表的外键，职位编号字段是引用职位表的外键，经理编号字段是引用员工表自身的外键。
SQL 初级查询
DQL，数据查询语言。这个就是 SELECT 语句，用于查询数据库中的数据和信息。
DML，数据操作语言。包括 INSERT、UPDATE、DELETE 和 MERGE 语句，主要用于数据的增加、修改和删除。
DDL，数据定义语言。主要包括 CREATE、ALTER 和 DROP 语句，用于定义数据库中的对象，例如表和索引。
TCL，事务控制语言；主要包括 COMMIT、ROLLBACK 和 SAVEPOINT 语句，用于管理数据库的事务。
DCL，数据控制语言。主要包括 GRANT 和 REVOKE 语句，用于控制对象的访问权限。
SELECT emp_name, sex FROM employee;
SELECT * FROM employee;
SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email
FROM employee;
SELECT emp_name, salary * 12 + COALEASE(bonus, 0)
FROM employee;
SELECT emp_name AS "姓名", salary * 12 + COALEASE(bonus, 0) "年薪"
FROM employee;
SELECT *
FROM employee
WHERE sex = '女';
SELECT *
FROM employee
WHERE salary BETWEEN 10000 AND 15000;
SELECT *
FROM employee
WHERE emp_name IN ('张三', '李四', '张飞');
SELECT *
FROM employee
WHERE emp_name LIKE '%云%';
SELECT emp_name,
bonus
FROM employee
WHERE bonus IS NOT NULL;
SELECT emp_name, sex, hire_date
FROM employee
WHERE sex = '女'
AND hire_date > DATE '2010-01-01';
SELECT *
FROM employee
WHERE 1 = 0 AND 1/0 = 1;
SELECT DISTINCT sex
FROM employee;
SELECT *
FROM employee
ORDER BY salary 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');
SELECT *
FROM employee
ORDER BY bonus;
-- 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;
SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH NEXT 3 ROWS WITH TIES;
-- 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;
SELECT emp_name, length(email)
FROM employee;
SELECT emp_name, email
FROM employee
WHERE UPPER(email) = 'GUANXING@SHUGUO.COM';
-- MySQL 实现
SELECT CONCAT_WS(',' emp_name, sex, salary)
FROM employee;
-- Oracle 实现
SELECT emp_name||','||sex||','||salary
FROM employee;
SELECT emp_name, SUBSTR(email, 1, INSTR(email,'@') - 1)
FROM employee;
SELECT emp_name, REPLACE(EMAIL, '.com','.net')
FROM employee;
-- MySQL 实现
SELECT emp_name, RAND()
FROM employee
ORDER BY RAND();
-- Oracle 实现
SELECT emp_name, DBMS_RANDOM.VALUE
FROM employee
ORDER BY DBMS_RANDOM.VALUE;
SELECT CEILING(1.1), FLOOR(1.1), ROUND(1.1)
FROM employee
WHERE emp_id = 1;
SELECT student_id, GREATEST(chinese, math, english, history)
FROM score;
SELECT emp_name, EXTRACT( year FROM CURRENT_DATE) - EXTRACT( year FROM HIRE_DATE)
FROM employee;
SELECT emp_name,
CASE WHEN salary < 10000 THEN '低收入'
WHEN salary < 20000 THEN '中等收入'
ELSE '高收入'
END "薪水等级"
FROM employee;
SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employee;
AVG - 计算一组值的平均值。
COUNT - 统计某个字段的行数。
MIN - 返回一组值中的最小值。
MAX - 返回一组值中的最大值。
SUM - 计算一组值的和值。
SELECT COUNT(*), COUNT(bonus)
FROM employee;
-- MySQL 实现
SELECT GROUP_CONCAT(email SEPARATOR ';')
FROM employee;
-- Oracle 实现
SELECT LISTAGG(email, '; ') WITHIN GROUP (ORDER BY NULL)
FROM employee;
SELECT dept_id, COUNT(*), AVG(salary)
FROM employee
GROUP BY dept_id;
SELECT dept_id, COUNT(*), emp_name
FROM employee
GROUP BY dept_id;
SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > 10000;
SELECT dept_id, COUNT(*)
FROM employee
WHERE salary > 5000
GROUP BY dept_id
HAVING COUNT(*) > 5;
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;
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;
并集运算（UNION、UNION ALL），将两个查询结果合并成一个结果集，包含了第一个查询结果以及第二个查询结果中的数据。
交集运算（INTERSECT），返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据。MySQL 不支持 INTERSECT。
差集运算（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 EXCEPT SELECT id FROM t2;
-- 使用连接查询实现交集运算
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;
标量子查询（scalar query）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
行子查询（row query）：返回包含一个或者多个值的单行结果（一行多列），标量子查询是行子查询的特例。
表子查询（table query）：返回一个虚拟的表（多行多列），行子查询是表子查询的特例。
SELECT emp_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
SELECT *
FROM employee
WHERE dept_id = (SELECT dept_id FROM department);
SELECT *
FROM employee
WHERE dept_id IN (SELECT dept_id FROM department);
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 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);
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);
-- 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);
-- Oracle 实现
SELECT dept_id, job_id, COUNT(*),GROUPING(dept_id)
FROM employee
GROUP BY CUBE (dept_id, job_id);
--
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;
-- 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;
-- 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;
SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id)
FROM employee;
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;
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;
SELECT emp_name, dept_id, salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY NULL ROWS UNBOUNDED PRECEDING)
FROM employee;
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;
设计与开发
非空约束（NOT NULL），用于限制字段不会出现空值。比如员工姓名不能为空。
唯一约束（UNIQUE），用于确保字段中的值不会重复。例如，每个员工的电子邮箱不能重复。每个表可以有多个唯一约束。
主键约束（Primary Key），主键是唯一标识表中每一行的字段。例如员工编号，部门编号等。主键字段必须唯一且非空，每个表可以有且只能有一个主键。
外键约束（FOREIGN KEY），用于表示两个表之间的引用关系。例如，员工属于部门，因此员工表中的部门编号字段可以定义为外键，它引用了部门信息表中的主键。
检查约束（CHECK），可以定义更多用户自定义的业务规则。例如，薪水必须大于 0 ，性别只能是男和女等。
默认值（DEFAULT），用于向字段中插入默认数据。
| OLTP | OLAP |
|---|---|
| 在线事务处理系统 | 在线分析处理系统 |
| 专注于事务数据的增删改，事务相对简单但频繁，要求响应时间快 | 专注于决策数据分析，查询通常比较复杂，处理时间长 |
| 数据来源于在线业务 | 数据来源于各种 OLTP |
| 通常采用规范化的设计，需要保证数据的完整性 | 不需要太多规范化，可以存储冗余信息，采用多维数据模型 |
| 常见应用包括银行 ATM、在线订票系统、网上商城 | 常见应用包括数据仓库、报表分析、商务智能 |
第一范式（First Normal Form），表中的每个属性都是单值属性，每个记录都唯一，也就是需要主键。举例来说，如果员工存在工作邮箱和个人邮箱，不能都放到一个字段，而需要拆分成两个字段；
第二范式（Second Normal Form），首先需要满足第一范式，且不包含任何部分依赖关系。举例来说，如果将学生信息和选课信息放在一起，学号和课程编号可以作为复合主键；但此时学生的其他信息依赖于学号，即主键的一部分。通常使用单列主键可以解决部分依赖问题；
第三范式（Third Normal Form），首先需要满足第二范式，并且不存在传递依赖关系。举例来说，如果将部门信息存储在每个员工记录的后面，那么部门名称依赖部门编号，部门编号又依赖员工编号，这就是传递依赖。解决的方法就是将部门信息单独存储到一个表中；
更高的范式包括 Boyce-Codd 范式、第四范式、第五范式以及第六范式等，不过很少使用到这些高级范式。对于大多数系统而言，满足第三范式即可。
概念 ERD，即概念数据模型。概念 ERD 描述系统中存在的业务对象以及它们之间的关系。
逻辑 ERD，即逻辑数据模型。逻辑 ERD 是对概念数据模型进一步的分解和细化，明确定义每个实体中的属性并描述操作和事务。
物理 ERD，即物理数据模型。物理 ERD 是针对具体数据库的设计描述，需要为每列指定类型、长度、可否为空等属性，为表增加主键、外键以及索引等约束。
字符数据类型，分为固定长度的 CHAR(n) 、可变长度的 VARCHAR(n) 以及字符大对象 CLOB。
数字数据类型，分为精确数字 INTEGER、BIGINT、NUMERIC 以及近似数字 FLOAT、DOUBLE PRECISION 等。
日期时间类型，分为日期DATE、时间TIME以及时间戳TIMESTAMP 。
二进制数据类型，主要是 BLOB。用于存储图片、文档等二进制数据。
CREATE TABLE table_name
(
column_1 data_type column_constraint,
column_2 data_type,
...,
table_constraint
);
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 ...;
CREATE TABLE emp_new
AS
SELECT *
FROM employee
WHERE 1=0;
CREATE TABLE emp_copy
LIKE employee;
-- 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 |王五
ALTER TABLE table_name action;
ALTER TABLE emp_new
ADD weight NUMERIC(4,2) DEFAULT 60 NOT NULL;
DROP TABLE table_name;
DROP TABLE department CASCADE CONSTRAINTS;
查询 A 账户的余额是否足够；
从 A 账户减去 1000 元；
往 B 账户增加 1000 元；
记录本次转账流水。
Atomic，原子性。一个事务包含的所有 SQL 语句要么全部成功，要么全部失败。例如，某个事务需要更新 100 条记录，但是在更新到一半时，系统出现故障，数据库必须保证能够回滚已经修改过的数据，就像没有执行过该事务一样。
Consistency，一致性。事务开始之前，数据库位于一致性的状态；事务完成之后，数据库仍然位于一致性的状态。例如，银行转账事务中，如果一个账户扣款成功，但是另一个账户加钱失败，那么就会出现数据不一致（此时需要回滚已经执行的扣款操作）。另外，数据库还必须保证满足完整性约束，比如账户扣款之后不能出现余额为负数（可以在余额字段上添加检查约束）。
Isolation，隔离性。隔离性与并发事务有关，一个事务的影响在其完成之前对其他事务不可见，多个并发的事务之间相互隔离。例如，账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目；如果多人同时向账户 B 转账，结果也应该保持一致性，就像依次转账的结果一样。
Durability，持久性。已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库都不会丢失数据。
更新丢失，当两个事务同时更新某一数据时，后者会覆盖前者的结果；
脏读，当一个事务正在操作某些数据但并未提交时，如果另一个事务读取到了未提交的结果，就出现了脏读；
不可重复读，第一个事务第一次读取某一记录后，该数据被另一个事务修改提交，第一个事务再次读取该记录时结果发生了改变；
幻象读，第一个事务第一次读取数据后，另一个事务增加或者删除了某些数据，第一个事务再次读取时结果的数量发生了变化。
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| 读未提交 | 可能 | 可能 | 可能 |
| 读已提交 | 不会 | 可能 | 可能 |
| 可重复读 | 不会 | 不会 | 可能 |
| 序列化 | 不会 | 不会 | 不会 |
SHOW ENGINES;
| 特性 | MyISAM | InnoDB |
|---|---|---|
| B 树索引 | 支持 | 支持 |
| 备份/时间点恢复 | 支持 | 支持 |
| 聚集索引 | 不支持 | 支持 |
| 压缩数据 | 支持 | 支持 |
| 数据缓存 | 不支持 | 支持 |
| 加密数据 | 支持 | 支持 |
| 外键支持 | 不支持 | 支持 |
| 全文搜索索引 | 支持 | 支持 |
| 空间数据类型 | 支持 | 支持 |
| 空间数据索引 | 支持 | 支持 |
| 哈希索引 | 不支持 | 不支持 |
| 索引缓存 | 支持 | 支持 |
| 锁定级别 | 表级 | 行级 |
| MVCC | 不支持 | 支持 |
| 复制 | 支持 | 支持 |
| 存储限制 | 256TB | 64TB |
| 数据库事务 | 不支持 | 支持 |
INSERT INTO … VALUES …
INSERT INTO … SELECT …
INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部');
-- MySQL 实现
INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部'), (2, '人力资源部'), (3, '财务部');
INSERT INTO emp_new
SELECT * FROM employee;
UPDATE table_name
SET column1 = expr1,
column2 = expr2,
...
[WHERE condition];
UPDATE emp_new
SET salary = salary + 1000
WHERE emp_name = '赵云';
DELETE FROM table_name
[WHERE conditions];
DELETE FROM emp_new;
| DELETE | TRUNCATE |
|---|---|
| 用于从表中删除指定的数据行。 | 用于删除表中的所有行，并释放包含该表的存储空间。 |
| 删除数据后，可以提交或者回滚。 | 操作无法回滚。 |
| 属于数据操作语言（DML）。 | 属于数据定义语言（DDL）。 |
| 删除数据较多时比较慢。 | 执行速度很快。 |
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, ...);
INSERT INTO target_table (column1, column2, ...)
SELECT col1, col2, ...
FROM source_table s
ON DUPLICATE KEY UPDATE
column1 = s.col1,
column2 = s.col2,
...;
B/B+ 树索引，使用平衡树或者扩展的平衡树结构创建索引。这是最常见的一种索引，几乎所有的数据库都支持。这种索引通常用于优化 =、<、<=、>、BETWEEN、IN 以及字符串的前向匹配查询。
Hash 索引，使用数据的哈希值进行索引。主要用于等值（=）查询。
聚集索引，将表中的数据按照索引的结构（通常是主键）进行存储。MySQL 中称为聚集索引，Oracle 中称为索引组织表（IOT）。
非聚集索引，也称为辅助索引。索引与数据相互独立，MySQL 中的 InnoDB 存储的是主键值，Oracle 中存储的时物理地址。
全文索引，用于支持全文搜索。
唯一索引与非唯一索引。唯一索引可以确保被索引的数据不会重复，可以实现数据的唯一性约束。非唯一索引仅仅用于提高查询的性能。
单列索引与多列索引。基于多个字段创建的索引称为多列索引，也叫复合索引。
函数索引。基于函数或者表达式的值创建的索引。
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| |
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) |
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';
CREATE INDEX idx ON test (col2, col1);
SELECT *
FROM employee e
WHERE email LIKE 'zhang%';
嵌套循环连接（Nested Loop Join），针对驱动表中的每条记录，遍历另一个表找到匹配的数据，相当于两层循环。Nested Loop Join 适用于驱动表数据比较少，并且连接的表中有索引的时候。
排序合并连接（ Sort Merge Join），先将两个表中的数据基于连接字段进行排序，然后合并。Sort Merge Join 通常用于没有索引，并且数据已经排序的情况，比较少见。
哈希连接（Hash Join），将一个表的连接字段计算出一个哈希表，然后从另一个表中一次获取记录并计算哈希值，根据两个哈希值来匹配符合条件的记录。Hash Join 对于数据量大，且没有索引的情况下可能性能更好。
替代复杂查询，减少复杂性；
提供一致性接口，实现业务规则；
控制对于表的访问，提高安全性。
不当使用可能会导致查询的性能问题；
可更新视图（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 = '法正';
聚合函数，例如 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;
UPDATE emp_devp
SET email = 'zhaoyun@sanguo.net'
WHERE emp_name = '赵云';
UPDATE emp_devp
SET email = 'zhangfei@sanguo.net'
WHERE emp_name = '张飞';
提高应用的执行效率。存储过程经过编译之后存储在数据库中，执行时可以进行缓存，可以提高执行的速度；
减少了应用与数据库之间的数据传递。调用存储过程时，只需要传递参数，业务代码已经存在数据中；
存储过程可以实现代码的重用。不同的应用可以共享相同的存储过程；
存储过程可以提高安全性。存储过程实现了代码的封装，应用程序通过存储过程进行数据访问，而不需要之间操作数据表。
不同数据库的实现不同，Oracle 中称为 PL/SQL，MySQL 中称为 PSM，其他数据库也都有各自的实现；
存储过程需要占用数据库服务器的资源，包括 CPU、内存等，而数据库的扩展性不如应用；
存储过程的开发和维护需要专业的技能。
-- 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 insert_employee;
审计表的数据修改。某些表中可能包含敏感信息，比如员工的薪水，要求记录所有的修改历史。这种需求可以通过创建针对员工表的 语句级 UPDATE 触发器实现。
实现复杂的业务约束。在触发器中增加业务检查和数据验证，阻止非法的业务操作。
CREATE TRIGGER 用于创建触发器。
DROP TRIGGER 用于删除触发器。
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;
