约束
索引
事务
视图View
视图的使用
创建视图
查看视图
查看视图名
删除视图
视图中可以再使用视图

约束

primary key(主键)

CREATE TABLE t17
    (id INT PRIMARY KEY, #表示id列是主键,主键列的值不可以重复，并且不能是NULL
    ...
    );

注：
1. 一张表最多有一个主键，但可以是复合主键

CREATE TABLE t17
    (id INT, 
    `name` VARCHAR(32) 
    PRIMARY KEY(id,`name`)   #复合主键，只有id和name都重复时才报错，只有一个重复不报错
    );

2. 主键指定方式有两种：A. 直接在字段名后指定 B. 在表定义的最后写
3. 使用 desc 表名，可以看到primary key 的情况

unique

加上unique后，值不能重复
1. 如果没有指定not null，那么unique字段可以有多个null
2. 如果指定了 not null，那么使用效果类似于 primary key
3. 一张表可以有多个unique字段

CREATE TABLE t17
    (id INT UNIQUE,
    `name` VARCHAR(32) 
    );

外键

#创建主表
CREATE TABLE my_class(
    id INT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL DEFAULT '');
#创建从表
CREATE TABLE my_stu(
    id INT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL DEFAULT '',
    class_id INT,
    #下面指定外键关系
    FOREIGN KEY(calss_id) REFERENCES my_class(id))

比如主表里有class 3，但是从表还没有class 3的学生。这时候可以删除主表的class 3。如果有，就不能删除了。

check

CREATE TABLE t23(
    id INT PRIMARY KEY,
    `name` VARCHAR(32),
    sex VARCHAR(6) CHECK(sex IN('man','woman')),
    sal DOUBLE CHECK (sal >10 AND sal<2000)
    );

索引

原理就是二叉搜索树。

#在emp表的 empno列创建索引
CREATE INDEX empno_index ON emp(empno)

添加索引

#查询表是否有索引
SHOW INDEXES FROM t25;
#添加唯一索引
CREATE UNIQUE INDEX id_index ON t25(id);
#添加普通索引
CREATE INDEX id_index ON t25(id);
ALTER TABLE t25 ADD INDEX id_index(id);
#如何选择：
#    如果某列的值是不会重复的，那么优先考虑使用unique索引，否则使用普通索引
#添加主键索引
CREATE TABLE t26(
    id INT,
    `name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY(id); #这个跟直接在CREATE写primary key效果相同

删除索引

#删除索引
DROP INDEX id_index ON t25
#删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY
#修改索引：先删除，再添加新的索引

查询索引

SHOW INDEX FROM t25

什么时候使用索引

第四条：也就是该字段用不到select。

事务

基本简介

数据库事务，其实就是数据库设计者为了方便起见，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为一个事务。

事务处理的原则：
保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来。要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

示意图

#事务的重要概念和具体操作
-- 1.创建一张测试表
CREATE TABLE t27(
    id INT,
    `name` VARCHAR(32));
-- 2.开始事务
START TRANSACTION
-- 3.设置保存点
SAVEPOINT a
-- 4.执行dml操作
INSERT INTO t27 VALUES(100,'Tom');
-- 5.回退到a
ROLLBACK TO a
-- 6.提交，此时dml才正式生效
COMMIT

注：
1. 如果不开始事务，默认情况下，dml执行后就自动提交了，不能回滚。
2. 如果开始一个事务没有创建保存点，可以执行rollback默认回退到事务开始的状态

事务隔离级别

赃写：对于两个事务A和B，如果事务A修改了未提交的事务B修改过的数据，那就是发生了赃写。

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。一个事务执行过程中看到的数据，总是跟这个数据启动时看到的数据一致，InnoDB 默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

事务隔离级别操作

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别
#隔离级别格式：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
#隔离级别格式：
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ

注：repeatable就是一个等级。这些语句需要在DOS下执行。注意第五条

这样可以修改默认隔离级别（但是没必要改）。

mysql事务ACID

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，要么全部成功后提交，要么全部失败后回滚，不存在一部分成功，一部分失败的情况。如果无法保证原子性就会出现数据不一致的情形，比如A账户减去100元，而B账户增加100元操作失败，系统将无故丢失100元。

一致性(Consistency)

根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。
那什么是合法的数据状态呢？这状态是由你自己来定义的（比如余额必须大于0等）。

如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态。

隔离性(Isolation)

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';

下面就是对B进行操作后，还没有进行刷盘时，另一个事务进行介入的后果：

持久性(Durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。
这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

事务的状态

活动的(active)
事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。

部分提交的(partially committed)
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。

失败的(failed)
当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

中止的(aborted)
如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

提交的(committed)
当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

显式事务

#步骤一：显式开启一个事务
mysql> BEGIN;
#或者
mysql> START TRANSACTION;
#步骤二：一系列事务中的操作（主要是DML，不含DDL）
#步骤三：提交事务 或 中止事务（即回滚事务）
# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;
# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;
# 将事务回滚到某个保存点。
mysql> ROLLBACK TO [SAVEPOINT]

隐式事务

MySQL中有一个系统变量 autocommit，默认是开启的，当然，如果我们想关闭这种自动提交的功能，可以使用下边两种方法之一：

显式的的使用START TRANSACTION或者 BEGIN语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
如下的设置：
```
SET autocommit = OFF;
#或
SET autocommit = 0;
```
能够触发隐式提交的语句：

视图View

注意是基表，不是基本
```html
视图的使用
— 创建一个视图 emp_view，只能查询emp表的某些信息 — (empno,ename,job,deptno)

创建视图

CREATE VIEW emp_view AS SELECT empno,ename,job,deptno FROM emp;

查看视图

DESC emp_view

查看视图名

SHOW CREATE VIEW emp_view

删除视图

DROP VIEW emp_view

视图中可以再使用视图

CREATE VIEW emp_view02 AS SELECT empno,ename FROM emp_view

![image.png](https://cdn.nlark.com/yuque/0/2022/png/23175776/1641964042579-c3138555-9e8d-4328-9504-8593245dae4a.png#clientId=u43422861-2739-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=ue0c1ba95&margin=%5Bobject%20Object%5D&name=image.png&originHeight=489&originWidth=1434&originalType=url&ratio=1&rotation=0&showTitle=false&size=840883&status=done&style=none&taskId=u6c11d923-639d-4a07-9590-fa933f4d030&title=)
<a name="ZV61o"></a>
### 视图练习
```html
#针对 emp, dept和salgrade 三张表，创建一个视图
#可以显示雇员编号，雇员名，雇员部门名称和薪水级别
#三表联合查询
CREATE VIEW emp_view
    AS
    SELECT empno,ename,dname,grade
    FROM emp,dept,salgrade
    WHERE emp.deptno = dept.deptno 
    AND (sal BETWEEN losal AND hisal)

MySQL数据库

约束、索引、事务、视图

约束