目录
1. 元组，码，候选码，主码，外码，主属性，非主属性
- 元组：元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
- 码：码就是能唯一标识实体的属性，对应表中的列。
- 候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
- 主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。
- 外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
- 主属性：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
- 非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。
2. ER图
ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。
ER 图由下面 3 个要素组成：
- 实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
- 属性：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
- 联系：即实体与实体之间的关系，在 ER 图中用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。
下图是一个学生选课的 ER 图，每个学生可以选若干门课程，同一门课程也可以被若干人选择，所以它们之间的关系是多对多（M: N）。另外，还有其他两种实体之间的关系是：1 对 1（1:1）、1 对多（1: N）。
3. 数据库范式
3.1 第一范式
属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。
3.2 第二范式
2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。
- 函数依赖（functional dependency）：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
- 部分函数依赖（partial functional dependency）：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖于（学号，身份证号）；
- 完全函数依赖(Full functional dependency)：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；
- 传递函数依赖：在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。（系名和系主任冗余了）
3.3 第三范式
3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求
4. 为什么不推荐使用外键和级联
- 增加了复杂性： a. 每次做 DELETE 或者 UPDATE 都必须考虑外键约束，会导致开发的时候很痛苦, 测试数据极为不方便; b. 外键的主从关系是定的，假如那天需求有变化，数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
- 增加了额外工作：数据库需要增加维护外键的工作，比如当我们做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，保证数据的的一致性和正确性，这样会不得不消耗资源；
- 对分库分表不友好：因为分库分表下外键是无法生效的。
外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。但同时外键也是有好处的，如果不涉及分库分表，并发量不是很高的情况下还是可以考虑使用的：
- 保证了数据库数据的一致性和完整性；
- 级联操作方便，减轻了程序代码量；
5. drop、delete、truncate的区别
1. 用法不同
drop(丢弃数据):drop table 表名，直接将表都删除掉，在删除表的时候使用。truncate(清空数据) :truncate table 表名，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。delete（删除数据） :delete from 表名 where 列名=值，删除某一行的数据，如果不加where子句和truncate table 表名作用类似。
注：truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行drop 之后对应的表不复存在。
2. 属于不同的数据库语言
truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。
注：DML和DDL的区别
- DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
- DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。
- 由于
select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言 DQL（Data Query Language）。
3. 执行速度不同
一般来说：drop > truncate > delete（这个我没有实际测试过）。
delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。drop命令会把表占用的空间全部释放掉。
6. 数据库设计流程
- 需求分析 : 分析用户的需求，包括数据、功能和性能需求。
- 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。
- 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
- 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
- 数据库实施 : 包括编程、测试和试运行
- 数据库的运行和维护 : 系统的运行与数据库的日常维护。
7. NOSQL
7.1 NOSQL概述
NoSQL（Not Only SQL 的缩写）泛指非关系型的数据库，主要针对的是键值、文档以及图形类型数据存储。并且，NoSQL 数据库天生支持分布式，数据冗余和数据分片等特性，旨在提供可扩展的高可用高性能数据存储解决方案。
一个常见的误解是 NoSQL 数据库或非关系型数据库不能很好地存储关系型数据。NoSQL 数据库可以存储关系型数据—它们与关系型数据库的存储方式不同。
NoSQL 数据库代表：HBase、Cassandra、MongoDB、Redis。
7.2 NOSQL和SQL的区别
7.3 NOSQL的优势
NoSQL 数据库非常适合许多现代应用程序，例如移动、Web 和游戏等应用程序，它们需要灵活、可扩展、高性能和功能强大的数据库以提供卓越的用户体验。
- 灵活性： NoSQL 数据库通常提供灵活的架构，以实现更快速、更多的迭代开发。灵活的数据模型使 NoSQL 数据库成为半结构化和非结构化数据的理想之选。
- 可扩展性： NoSQL 数据库通常被设计为通过使用分布式硬件集群来横向扩展，而不是通过添加昂贵和强大的服务器来纵向扩展。
- 高性能： NoSQL 数据库针对特定的数据模型和访问模式进行了优化，这与尝试使用关系数据库完成类似功能相比可实现更高的性能。
- 强大的功能： NoSQL 数据库提供功能强大的 API 和数据类型，专门针对其各自的数据模型而构建。
7.4 NOSQL数据库的类型
- 键值：键值数据库是一种较简单的数据库，其中每个项目都包含键和值。这是极为灵活的 NoSQL 数据库类型，因为应用可以完全控制 value 字段中存储的内容，没有任何限制。Redis 和 DynanoDB 是两款非常流行的键值数据库。
- 文档：文档数据库中的数据被存储在类似于 JSON（JavaScript 对象表示法）对象的文档中，非常清晰直观。每个文档包含成对的字段和值。这些值通常可以是各种类型，包括字符串、数字、布尔值、数组或对象等，并且它们的结构通常与开发者在代码中使用的对象保持一致。MongoDB 就是一款非常流行的文档数据库。
- 图形：图形数据库旨在轻松构建和运行与高度连接的数据集一起使用的应用程序。图形数据库的典型使用案例包括社交网络、推荐引擎、欺诈检测和知识图形。Neo4j 和 Giraph 是两款非常流行的图形数据库。
- 宽列：宽列存储数据库非常适合需要存储大量的数据。Cassandra 和 HBase 是两款非常流行的宽列存储数据库。
8. SQL语法
SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。SQL的语法要点如下：
- SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如：
SELECT与select、Select是相同的。 - 多条 SQL 语句必须以分号（
;）分隔。 - 处理 SQL 语句时，所有空格都被忽略。
8.1 插入
# 插入一行
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');
# 插入多行
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');
# 插入行的一部分
INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');8.2 更新
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
8.3 删除
# 删除表中指定的数据
DELETE FROM user
WHERE username = 'robot';
# 清空表中的数据
TRUNCATE TABLE user;
8.4 查询
# 查询单列
SELECT prod_name
FROM products;
# 查询多列
SELECT prod_id, prod_name, prod_price
FROM products;
# 查询所有列
SELECT *
FROM products;
# 查询所有列
SELECT DISTINCT
vend_id FROM products;
# 限制查询结果
-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;
8.5 排序
order by 用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序，如果需要按照降序对记录进行排序，可以使用 desc 关键字。
order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;
8.6 分组
group by相当于将表进行了压缩分组，将表按group by的参数进行分组。
group by子句将记录分组到汇总行中。group by为每个组返回一个记录。group by通常还涉及聚合count，max，sum，avg等，count这些函数相当于查询的字段。group by可以按一列或多列进行分组。group by按分组字段进行排序后，order by可以以汇总字段来进行排序。
// 将表按cust_name分组后查询cust_name和COUNT(cust_address)
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;
// HAVING子句在数据分组之后过滤组，要与GROUP BY连用，不能单独使用
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;
// WHERE子句在数据分组之前过滤组
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;8.7 子查询
子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 select 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。
子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。
子查询常用在 WHERE 子句和 FROM 子句后边：
- 当用于
WHERE子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为WHERE子句查询条件的值。 - 当用于
FROM子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合FROM后面是表的规则。这种做法能够实现多表联合查询。
# 用于 WHERE 子句的子查询的基本语法
select column_name [, column_name ]
from table1 [, table2 ]
where column_name operator
(select column_name [, column_name ]
from table1 [, table2 ]
[where])
- 子查询需要放在括号
( )内。 operator表示用于 where 子句的运算符。
# 用于 FROM 子句的子查询的基本语法
select column_name [, column_name ]
from (select column_name [, column_name ]
from table1 [, table2 ]
[where]) as temp_table_name
where condition
- 用于
FROM的子查询返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字。
# 子查询的子查询
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01'));
- 内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。
8.7.1 Where
WHERE子句用于过滤记录，即缩小访问数据的范围。WHERE后跟一个返回true或false的条件。WHERE可以与SELECT，UPDATE和DELETE一起使用。- 可以在
WHERE子句中使用的操作符。
# SELECT 语句中的 WHERE 子句
SELECT * FROM Customers
WHERE cust_name = 'Kids Place';
# UPDATE 语句中的 WHERE 子句
UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';
# DELETE 语句中的 WHERE 子句
DELETE FROM Customers
WHERE cust_name = 'Kids Place';
8.7.2 In和Between
IN操作符在WHERE子句中使用，作用是在指定的几个特定值中任选一个值。BETWEEN操作符在WHERE子句中使用，作用是选取介于某个范围内的值。
// IN 示例
SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');
// BETWEEN 示例
SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;
8.7.3 And、Or、Not
AND、OR、NOT是用于对过滤条件的逻辑处理指令。AND优先级高于OR，为了明确处理顺序，可以使用()。AND操作符表示左右条件都要满足。OR操作符表示左右条件满足任意一个即可。NOT操作符用于否定一个条件。
# AND 示例
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
# OR 示例
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
# NOT 示例
SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;
8.7.4 Like
LIKE操作符在WHERE子句中使用，作用是确定字符串是否匹配模式。- 只有字段是文本值时才使用
LIKE。 LIKE支持两个通配符匹配选项：%和_。- 不要滥用通配符，通配符位于开头处匹配会非常慢。
%表示任何字符出现任意次数。_表示任何字符出现一次。
# % 示例
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';
# _ 示例
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';
8.8 连接
JOIN 是“连接”的意思，顾名思义，SQL JOIN 子句用于将两个或者多个表联合起来进行查询。连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。
# JOIN 示例
select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;
当两个表中有同名的字段时，为了帮助数据库引擎区分是哪个表的字段，在书写同名字段名时需要加上表名。当然，如果书写的字段名在两个表中是唯一的，也可以不使用以上格式，只写字段名即可。
如果两张表的关联字段名相同，也可以使用 USING子句来代替 ON：
# join....on
select c.cust_name, o.order_num
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
order by c.cust_name;
# 如果两张表的关联字段名相同，也可以使用USING子句：join....using()
select c.cust_name, o.order_num
from Customers c
inner join Orders o
using(cust_id)
order by c.cust_name;
- 连接表时，SQL 会根据连接条件生成一张新的临时表。
ON就是连接条件，它决定临时表的生成。 WHERE是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。
所以总结来说就是：SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选。
SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接:
注：
- 如果不加任何修饰词，只写 JOIN，那么默认为 INNER JOIN
- CROSS JOIN 就是排列组合出两个表的所有可能
- FULL JOIN 返回左表（LEFT TABLE）和右表（RIGHT TABLE）中的所有行。如果某行在其中一个表中没有匹配项，则结果集中对应的列将包含 NULL 值。
- LEFT JOIN 和 RIGHT JOIN 在某行在其中一个表中没有匹配项，则结果集中对应的列将包含 NULL 值。
对于 INNER JOIN 来说，还有一种隐式的写法，称为 “隐式内连接”，也就是没有 INNER JOIN 关键字，使用 WHERE 语句实现内连接的功能
# 隐式内连接
select c.cust_name, o.order_num
from Customers c, Orders o
where c.cust_id = o.cust_id
order by c.cust_name;
# 显式内连接
select c.cust_name, o.order_num
from Customers c inner join Orders o
using(cust_id)
order by c.cust_name;
8.9 组合
UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，但不返回任何重复的行。这意味着，UNION 运算符会对结果集进行自动去重。其中包含来自 UNION 中参与查询的提取行。
UNION 基本规则：
- 所有查询的列数和列顺序必须相同。
- 每个查询中涉及表的列的数据类型必须相同或兼容。
- 通常返回的列名取自第一个查询。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
# 去重
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
# 不去重
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
JOIN和UNION的区别：
JOIN中连接表的列可能不同，但在UNION中，所有查询的列数和列顺序必须相同。UNION将查询之后的行放在一起（垂直放置），但JOIN将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。
8.10 函数
不同数据库的函数往往各不相同，因此不可移植。这里以MYSQL为例。
文本处理
其中，SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。如"meet"和"meat"发音相似，因此 SOUNDEX(meet) = SOUNDEX(meat)。
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('meet')
日期和时间处理
- 日期格式：
YYYY-MM-DD - 时间格式：
HH:MM:SS
数值处理
汇总
注：AVG() 会忽略 NULL 行。
DISTINCT 关键字通常用于去除结果集中的重复行
# 查询不重复的 col1 的平均值
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable
8.11 索引
索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。常见的数据库索引数据结构包括B树（B-Tree）和B+树（B+-Tree），哈希索引，位图索引，聚簇索引和非聚簇索引。
优点：
- 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要使用物理文件存储，也会耗费一定空间。
大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。
# 创建索引
CREATE INDEX user_index
ON user (id);
# 添加索引
ALTER table user ADD INDEX user_index(id)
# 创建唯一索引
CREATE UNIQUE INDEX user_index
ON user (id);
# 删除索引
ALTER TABLE user
DROP INDEX user_index;
8.12 约束
SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
约束类型：
NOT NULL- 指示某列不能存储 NULL 值。UNIQUE- 保证某列的每行必须有唯一的值。PRIMARY KEY- NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。FOREIGN KEY- 保证一个表中的数据匹配另一个表中的值的参照完整性。CHECK- 保证列中的值符合指定的条件。DEFAULT- 规定没有给列赋值时的默认值。
注：主键和唯一索引的区别
- 数量限制：
- 一个表只能有一个主键（Primary Key）。
- 一个表可以有多个唯一索引（Unique Index），但每个唯一索引必须包含不同的列组合。
- 索引类型：
- 主键通常自动创建唯一索引，以确保数据的唯一性。这意味着当你定义了一个主键，数据库系统会自动为这些列创建一个唯一索引。
- 唯一索引是一个独立的索引类型，用于确保索引列的值是唯一的。
- 空值（NULL）的处理：
- 主键列的值不能为NULL。因为主键的主要目的是唯一地标识表中的每一行，而NULL值表示“未知”或“缺少”数据，所以它们不能用于唯一标识。
- 唯一索引允许NULL值，但条件是NULL值在每列中只能出现一次。也就是说，如果你有一个唯一索引的列，并且已经有一个NULL值，那么你不能再插入另一个NULL值到该列中，但你可以在其他行中插入非NULL的唯一值。
8.13 事务处理
不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。
MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。
-- 开始事务
START TRANSACTION;
-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');
-- 创建保留点 updateA
SAVEPOINT updateA;
-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');
-- 回滚到保留点 updateA
ROLLBACK TO updateA;
-- 提交事务，只有操作 A 生效
COMMIT;
注：
START TRANSACTION- 指令用于标记事务的起始点。SAVEPOINT- 指令用于创建保留点。ROLLBACK TO- 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到START TRANSACTION语句处。COMMIT- 提交事务。
8.14 存储过程
存储过程可以看成是对一系列 SQL 操作的批处理。存储过程可以由触发器，其他存储过程以及 Java， Python，PHP 等应用程序调用。
使用存储过程的好处：
- 代码封装，保证了一定的安全性；
- 代码复用；
- 由于是预先编译，因此具有很高的性能。
# 创建存储过程
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
DECLARE c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END
;;
DELIMITER ;
# 使用存储过程
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;
注：阿里巴巴《Java 开发手册》强制禁止使用存储过程。因为存储过程难以调试和扩展，更没有移植性。
8.15 游标
游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
使用游标的几个明确步骤：
-
在使用游标前，必须声明(定义)它。这个过程实际上没有检索数据， 它只是定义要使用的
SELECT语句和游标选项。 -
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
-
对于填有数据的游标，根据需要取出(检索)各行。
-
在结束游标使用时，必须关闭游标，可能的话，释放游标(有赖于具
体的 DBMS)。
DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
DECLARE total INT;
-- 创建接收游标数据的变量
DECLARE sid INT;
DECLARE sname VARCHAR(10);
-- 创建总数变量
DECLARE sage INT;
-- 创建结束标志变量
DECLARE done INT DEFAULT false;
-- 创建游标
DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET total = 0;
OPEN cur;
FETCH cur INTO sid, sname, sage;
WHILE(NOT done)
DO
SET total = total + 1;
FETCH cur INTO sid, sname, sage;
END WHILE;
CLOSE cur;
SELECT total;
END $
DELIMITER ;
-- 调用存储过程
call getTotal();
8.16 触发器
触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。我们可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。
使用触发器的优点：
- SQL 触发器提供了另一种检查数据完整性的方法。
- SQL 触发器可以捕获数据库层中业务逻辑中的错误。
- SQL 触发器提供了另一种运行计划任务的方法。通过使用 SQL 触发器，您不必等待运行计划任务，因为在对表中的数据进行更改之前或之后会自动调用触发器。
- SQL 触发器对于审计表中数据的更改非常有用。
使用触发器的缺点：
- SQL 触发器只能提供扩展验证，并且不能替换所有验证。必须在应用程序层中完成一些简单的验证。例如，您可以使用 JavaScript 在客户端验证用户的输入，或者使用服务器端脚本语言（如 JSP，PHP，ASP.NET，Perl）在服务器端验证用户的输入。
- 从客户端应用程序调用和执行 SQL 触发器是不可见的，因此很难弄清楚数据库层中发生了什么。
- SQL 触发器可能会增加数据库服务器的开销。
# 创建触发器
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_history`(user_id, operate_type, operate_time)
VALUES (NEW.id, 'add a user', now());
END $
DELIMITER ;
# 查看触发器
SHOW TRIGGERS;
# 删除触发器
DROP TRIGGER IF EXISTS trigger_insert_user;
11万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
