数据库技术（三）

数据库技术（三）

using 子句和 on 子句的使用

1. 使用 using 子句进行连接时，结果中用于连接的列不会重复出现；而 on 子句的结果中，在不做干预的情况下，用于连接的列会出现两次。
2. 使用 using 子句时，连接的表必须有相同的字段；而 on 子句可以不相同，使用比较灵活。

外连接

外连接分为左外连接和右外连接（MySQL 中没有全连接）。此外还有一种特殊的连接叫交叉连接，即笛卡尔积。

左外连接

左外连接简称左连接，是指在两个表进行连接时，返回左表的全部记录及右表中符合条件的记录，右表没有匹配的记录用 null 补全

右外连接

右外连接简称右连接，右连接刚好和左连接相反，返回右表的全部记录及左表中符合条件的记录，左表没有匹配的记录用 null 补全

笛卡尔积

笛卡尔积也叫交叉连接，原理就是一张表中的每一条记录都要和另一张表中的所有记录进行连接。如果两张表分别有 n 和 m 条记录，进行笛卡尔积的结果有 n*m 条记录

简单来说，笛卡尔积是没有条件的连接，这会导致乘法效应，产生的数据量远远超过需要的数据，且多数配对不符合业务逻辑。应尽量避免连接查询中笛卡尔积的出现。

子查询

• in：等于多行子查询返回的结果中的任意一个即可；
• any：和多行子查询返回的某一个值进行比较即可；
• all：和多行子查询返回的所有值进行比较；

相关子查询 exists

不相关子查询：子查询的查询条件不依赖于父查询的称为不相关子查询。 相关子查询：子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询，带 EXISTS 的子查询就是相关子查询。

EXISTS 表示存在量词：带有 EXISTS 的子查询不返回任何记录的数据，只返回逻辑值 True 或 False。

Oracle 数据库支持 full join 进行全连接，而 MySQL 不支持 full join

• 数据操纵语言 DML
• 事务处理语言 TCL
• 数据定义语言 DDL
• MySQL 约束控制

数据库操作语言 DML 包括 INSERT 语句、UPDATE 语句以及 DELETE 语句

事务处理语言 TCL

事务（Transaction）是数据库操作的最小工作单元，是作为单个逻辑执行的一系列操作（比如上述中的输入取款金额，得到现金，取回磁卡等操作）的集合；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。事务是不可再分割的。

事务具有四大特征，即常说的 ACID：

1. 原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。
2. 一致性（Consistency）：事务执行的前后数据都处于合法状态，不会违背任何的数据完整性和逻辑的正确性，这就是“一致”的意思。以转账为例，转出账户的钱减少，但转入账户的钱没有增加，就不符合一致性。再以转账为例，无论多少个账户，多少个并行事务，其总数必然是一致的。原子性有助于保证数据的一致性，但不能完全保证。
3. 隔离性（Isolation）：一个事务的执行不能被其它事务所干扰，即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。换句话说，事务之间感知不到彼此的存在。
4. 持续性（Durability）：又叫永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

并发事务的隔离挑战

事务并发是指多个事务同时对同一个数据进行操作。并发事务未做到隔离性，会带来以下问题：

脏读

一个事务读取到另一事务未提交的更新数据。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中。这时，另外一个事务也访问这个数据，然后读到了修改后的数据，这个数据就是脏数据，依据脏数据所做的操作也是不正确的。例如 T1 开启了一个长事务，在较早的时间删除了一条记录，此时事务 T2 正好要统计表中记录总数，会少统计一条，随后 T1 事务发现删错了，进行了回滚操作，再随后 T2 事务再次统计，发现前后数据不一致。这显然违背了隔离性原则。

简单来说，脏读是指读到了未持久化的数据。

不可重复读

在同一事务中，多次读取同一数据返回的结果有所不同。换句话说，后续读取读到的是另一事务已提交的更新数据。例如 T1 开启了一个较长的事务，在较早的时间读取了一个数据，在中间另一个事务 T2 更改了数据并提交，随后 T1 再次读取但获得了新版本的数据，这时在同一个事务中多次读取同一数据却得到不同结果，这种现象就是不可重复读。显然，这也不符合隔离性原则。

幻读

在同一个事务中，以同样的条件进行范围查询，两次获得的记录数不一样。事务 T1 先执行了一次查询，然后事务 T2 新插入一行记录，接着 T1 使用相同的查询再次对表进行检索时，会发现这条出来的记录。这突然出现的记录就如一个“幻像”。 与“不可重复读”不同的是，幻读专指新插入的行。

事务的隔离级别

MySQL 约束控制

数据的完整性约束（简称“约束”）是在表和字段上强制执行的数据检测规则，是为了防止不规范的数据进入数据库。当我们对数据进行 DML 操作时，数据库管理系统（DBMS）会自动按照我们设置的约束条件对数据进行检测，以保证数据存储的完整性和准确性。

完整性约束分为 4 类：实体完整性约束、域完整性约束、参照完整性约束、用户自定义完整性约束。

• 实体完整性约束：用来标识表中的每一条记录都代表一个实体，如主键约束；
• 域完整性约束：用来针对单元格的约束，如非空约束等；
• 参照完整性约束：指多表之间的对应关系，在一张表中执行数据插入、更新、删除等操作时，DBMS 都会跟另一张表进行对照，避免不规范的操作，以确保数据存储的完整性，如外键约束；
• 用户自定义完整性约束：用户根据实际的要求来定义，在执行数据插入、更新等操作时，DBMS 会检查数据是否满足检查约束中的限定条件，避免不符合条件的操作，以保证数据存储的准确性，如检查约束（MySQL 不支持，Oracle 支持）。

在 MySQL 中支持的约束有 6 种，分别是：非空约束、主键约束、默认值约束、唯一约束、外键约束和自定义检查约束。

根据约束添加的位置，我们可以把约束分为两类：

• 列级约束：即直接在定义的字段名和类型后面追加约束。但该方式只支持默认值约束、非空约束、主键约束、唯一约束。
• 表级约束：即在各个字段定义完后进行添加。表级约束不支持非空约束和默认值约束。