MySQL事务-索引-约束-视图

一、事务

1.1 事务定义

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务中，所有的SQL语句都成功执行时，整个事务成功！有一个SQL语句执行失败，整个事务都执行失败

注意：MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型不支持！

1.2 事务的原理

  数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有的SQL语句均正常结束(COMMIT),才会将回滚段中的数据同步到数据库。否则无论因为任何原因失败了，则整个事务回滚(ROLLBACK);

1.3 事务处理

可以确保非事务性单元的多个操作都能成功完成，否则不会更新数据资源。

数据库默认事务是自动提交的， 也就是发一条 sql 它就执行一条。如果想多条 sql 放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql 会自动回滚事务。或者我们使用 rollback 命令手动回滚事务。

优点：通过将一组操作组成一个，执行时，要么全部成功，要么全部失败的单元,使程序更可靠，简化错误恢复。

为了更好的说明，我们举一个经典的例子：

 A汇款给B 1000元

 A账户-1000

 B账户+1000

以上操作对应数据库为两个update。这两个操作属于一个事物。否则，可能会出现A账户钱少了，B账户钱没增加的情况。

1.4 事务四大特性

事务是必须满足4个条件（ACID）

1.4.1 原子性（Autmic）

事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！

1.4.2 一致性（Consistency）

事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。在事务开始之前和结束之后，数据库的完整性约束没有被破坏

1.4.3 隔离性（Isolation）

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰，这些通过锁来实现。

1.4.4 持久性（Durability）

指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障（比如说宕机等）不应该对其有任何影响。

事务的ACID特性可以确保银行不会弄丢你的钱，而在应用逻辑中，要实现这点非常难，甚至可以说是不可能完成的任务。

1.5 MySQL事务处理的方法

1.5.1 用BEGIN,ROLLBACK,COMMIT来实现事务处理
start transation   #开启一个事务
savepoint          #设置保存点
roallback to       #回退事务，回到保存点
rollback           #回退所有事务，到事务开启时的状态
commit             #提交事务，所有的操作生效，不能回退。然后删除之前保存的所有保存点
1.5.2 用set来改变mysql的自动提交模式

MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！

SET AUTOCOMMIT = {0 | 1} 设置事务是否自动提交，默认是自动提交的。

0：禁止自动提交
1：开启自动提交。
1.5.3 测试事务

1）定义事务

mysql> set autocommit=0;   ##关闭自动提交事务
mysql> delimiter //   ##指定语句结束符
mysql> start transaction; ##手动开启事务
    -> update books set bName="ccc" where bId=1;
    -> update books set bName="ddd" where bId=2;
-> commit;//    ##提交事务

2）测试，查看是否完成修改

mysql> select bName from books where bId=1 or bId=2;//
+-------+
| bName |
+-------+
| ccc   |
| ddd   |
+-------+
2 rows in set (0.00 sec)

我们测试回滚操作，首先看我们的数据库存储引擎是否为innodb，如果不是的话，则是不支持的

mysql> show create table books;//

image-20211122214119158

修改数据库存储引擎为innodb

mysql>alter table books engine=innodb;

mysql>alter table category engine=innodb;

3）重新开启事务，并测试回滚

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> start transaction;
    -> update books set bName="HA" where bId=1;
    -> update books set bName="LB" where bId=2;
    -> commit;//
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

4）回滚事务

mysql> rollback        ##回滚事务   
    -> ;//
Query OK, 0 rows affected (0.00 sec)

mysql> select bName from books where bId=1 or bId=2;    ##查看书名还是HA和LB，说明回滚失败
    -> //
+-------+
| bName |
+-------+
| HA    |
| LB    |
+-------+
2 rows in set (0.00 sec)

mysql>

注意：一旦事务被提交了，就无法回滚了。

5）接下来，我们测试不提交事务的场景

mysql> delimiter //
mysql> start transaction; update books set bName="AH" where bId=1; update books set bName="BL" where bId=2;//
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delimiter ;
mysql> select bName from books where bId=1 or bId=2;
+-------+
| bName |
+-------+
| AH    |
| BL    |
+-------+
2 rows in set (0.00 sec)

mysql>

6）回滚事务

mysql> rollback
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select bName from books where bId=1 or bId=2;
+-------+
| bName |
+-------+
| HA    |
| LB    |
+-------+
2 rows in set (0.00 sec)

mysql>

事务回滚成功了。

1.6 事务的隔离级别

1.6.1 隔离概念

数据库事务隔离级别主要作用是实现事务工作期间，数据库操作读的隔离特性，所谓读的操作就是将数据页可以调取到内存；然后可以读取数据页中相应数据行的能力，并且不同事务之间的数据页读操作相互隔离；

可以简单理解为：一个事务在对数据页中数据行做更新操作时，在没有更新提交前，另一个事务此时是不能读取数据页中数据行内容的

对于数据库存储事务隔离级别包括4种，可以通过操作命令查看获取当前使用的隔离级别:

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
1.6.2 常用的事务隔离级别类型

类型一：RU（READ-UNCOMMITTED 表示读未提交）

可以读取到事务未提交的数据，隔离性差，会出现脏读（当前内存读），不可重复读，幻读问题;

类型二：RC（READ-COMMITTED 表示读已提交）

可以读取到事务已提交的数据，隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题；

类型三：RR（REPEATABLE-READ 表示可重复读）默认

可以防止脏读（当前内存读），防止不可重复读问题，防止会出现的幻读问题，但是并发能力较差；

会使用next lock锁进制，来防止幻读问题，但是引入锁进制后，锁的代价会比较高，比较耗费CPU资源，占用系统性能；

类型四：SR（SERIALIZABLE 可串行化）

隔离性比较高，可以实现串行化读取数据，但是事务的并发度就没有了；

这是事务的最高级别，在每条读的数据上，加上锁，使之不可能相互冲突

事务隔离级别官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

image-20240127225200536

1.6.3 为什么默认RR隔离级别？

RR级别作为mysql事务默认隔离级别，是事务完全和性能的折中。SERIALIZABLE级别是悲观地认为幻读时刻都会发生，故会自动地隐式地对事务所需资源加排他锁，其他事务访问此资源会被阻塞等待，故事务是安全的，但需要认值考虑性能。

1.6.4 常用的事务隔离级别名词解释

在解释分析说明相应的隔离级别名词前，需要对数据库事务隔离级别进行调整，以及关闭自动提交功能：

# 设置事务隔离级别
mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> set global transaction_isolation='REPEATABLE-READ';

# 设置当前事务隔离级别
mysql> set  transaction_isolation='READ-UNCOMMITTED';
mysql> set  transaction_isolation='READ-COMMITTED';
mysql> set  transaction_isolation='REPEATABLE-READ';


# 查看事务隔离级别
mysql> select @@transaction_isolation;

# 临时关闭自动提交功能
mysql> set global autocommit=0;

注意：

1、设置全局的事务隔离级别，当前连接不受影响，需要退出数据库再次登录查看
2、设置当前事务隔离级别，当前连接收到影响。

创建隔离级别测试表

#创建测试表
mysql> create table t1 (id int not null primary key auto_increment,a int not null,b varchar(20) not null,c varchar(20) not null) charset=utf8mb4 engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| a     | int(11)     | NO   |     | NULL    |                |
| b     | varchar(20) | NO   |     | NULL    |                |
| c     | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)


mysql> begin;   
Query OK, 0 rows affected (0.00 sec)

#插入数据
mysql> insert into t1(a,b,c)values(5,'a','aa'),(7,'c','ab'),(10,'d','ae'),(13,'g','ag'),(14,'h','at'),(16,'i','au'),(20,'j','av'),(22,'k','aw'),(25,'l','ax'),(27,'o','ay'),(31,'p','az'),(50,'x','aze'),(60,'y','azb');
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

#提交数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)


#窗口1查看t1表；
mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 |  5 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 | 14 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.01 sec)

#窗口2查看t1表
mysql> select * from t1;
Empty set (0.00 sec)

#即不同的事务查看的数据是不一致的。
1.6.4.1 名词1：脏读

脏读主要表示在一个事务窗口中，没有数据修改提交操作前，另一个事务就可以看到内存中数据页的修改；

简单理解：在一个事务窗口中，可以读取到别人没有提交的数据信息；

案例演示：利用隔离级别RU解读：

1、先设置隔离级别是RU

mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> set global autocommit=0;

2、更新数据

# 数据库A会话窗口操作
mysql> begin;
mysql> update t1 set a=10 where id=1;  #只是在内存层面进行数据页中数据修改

# 数据库B会话窗口操作
mysql> begin;
mysql> select * from t1 where id=1;

image-20230608170258715

3、回滚

#窗口A进行事务回滚操作
mysql> rollback; #窗口A进行事务回滚操作

#窗口B查看又恢复了
mysql> select * from t1 where id=1;
+----+---+---+----+
| id | a | b | c  |
+----+---+---+----+
|  1 | 5 | a | aa |
+----+---+---+----+
1 row in set (0.00 sec)
1.6.4.2 名词2：不可重复读

不可重复读表示在一个事务中，利用相同的语句多次查询，获取的数据信息是不同的；

案例演示：利用隔离级别RC解读：

#A窗口执行
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> set global autocommit=0;
mysql> select * from t1 where id=1;

#B窗口执行
mysql> set  autocommit=0;
mysql> update t1 set a=10 where id=1;

image-20230609094132291

说明：当A窗口第一次查看数据的时候是原始的数据，B窗口更新完数据后，再次查看数据还是没有被更新，当B窗口提交后，A窗口的数据才发生了变化，结果 与上一步不一致，即产生了不可重复读的问题。

同一个事务中两次读取操作，读到的数据不一致，因为在并发环境下，有别的会话修改了数据。

解决不可重复读办法：repeatable read(MySQL默认隔离级别)，这时候能解决可重复读

image-20230609100402584

说明：

A窗口事务提交之后，B窗口事务查询信息和之前是相同的；-- 在RR级别状态下，同一窗口的事务生命周期下，每次读取相同数据信息是一样，避免了不可重复读问题
1.6.4.3 名词3：幻读

案例演示

1）第一次在A窗口查看t1表数据

mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 |  5 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 |  5 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)

2)在B窗口更新数据

mysql> insert into t1(a,b,c) values(8,'h','xy');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into t1(a,b,c) values(20,'z','xy');
Query OK, 1 row affected (0.00 sec)

3)在A窗口上再次查看

mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 |  5 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 |  5 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)

说明：查看还是一样的，说明并未出现脏读问题。

4)在B窗口上进行提交

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

5）在A窗口再次查看

mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 |  5 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 |  5 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
+----+----+---+-----+
13 rows in set (0.00 sec)

说明：B窗口提交后还是没有变化说明没有出现不可重读问题。

6）在A窗口上开始更新数据，如下：

mysql> update t1 set a=a+10;
Query OK, 15 rows affected (0.00 sec)
Rows matched: 15  Changed: 15  Warnings: 0

说明：发现有15条数据被更改，和原来表中的数据不一致了。

7）再次查看

mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 | 20 | a | aa  |
|  2 | 17 | c | ab  |
|  3 | 20 | d | ae  |
|  4 | 23 | g | ag  |
|  5 | 15 | h | at  |
|  6 | 26 | i | au  |
|  7 | 30 | j | av  |
|  8 | 32 | k | aw  |
|  9 | 35 | l | ax  |
| 10 | 37 | o | ay  |
| 11 | 41 | p | az  |
| 12 | 60 | x | aze |
| 13 | 70 | y | azb |
| 14 | 18 | h | xy  |
| 15 | 30 | z | xy  |
+----+----+---+-----+
15 rows in set (0.00 sec)

说明：此刻发现已经出现了15条数据，这就是幻读。

解决幻读的办法：串行化

1）打开一个窗口A，并设置当前事务模式Serializable,查询t1表的初始值：

mysql> set global transaction_isolation='SERIALIZABLE';
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+----+---+-----+
| id | a  | b | c   |
+----+----+---+-----+
|  1 | 10 | a | aa  |
|  2 |  7 | c | ab  |
|  3 | 10 | d | ae  |
|  4 | 13 | g | ag  |
|  5 |  5 | h | at  |
|  6 | 16 | i | au  |
|  7 | 20 | j | av  |
|  8 | 22 | k | aw  |
|  9 | 25 | l | ax  |
| 10 | 27 | o | ay  |
| 11 | 31 | p | az  |
| 12 | 50 | x | aze |
| 13 | 60 | y | azb |
| 14 |  8 | h | xy  |
| 15 | 20 | z | xy  |
+----+----+---+-----+
15 rows in set (0.00 sec)

2)打开一个B窗口，并设置当前事务模式为serializable，测试插入数据

mysql> set global transaction_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)


mysql>  insert into t1(a,b,c) values(20,'q','xq');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

插入一条记录报错表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到

二、索引

2.1 什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

image-20230609110544981

2.2 索引的作用

优点：

  • 为了加快搜索速度，减少查询时间 。

缺点：

  • 索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。
  • 索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。

2.3 在什么情况下需要建索引

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面，哪一些索引需要建立，哪一些所以是多余的。

2.3.1 适合创建索引的情况：
1. 主键会自动建立唯一索引，无需我们再手动建。
2. 频繁作为查询条件的字段应该创建索引。一般来说，在经常需要搜索的列上，可以加快索引的速度。
3. 查询中与其它表关联的字段，外键关系建立索引。在表与表的而连接条件上加上索引，可以加快连接查询的速度。
4. 单键/组合索引的选择问题， 组合索引性价比更高。
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。在经常需要排序(order by)、分组(group by)和distinct 的列上加索引，可以加快排序查询的时间,  (单独order by 用不了索引，索引考虑加where 或加limit)
6. 查询中统计或者分组字段。
7. 在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
8. 使用短索引。如果你的一个字段是Char(32)或者int(32)，在创建索引的时候可以指定前缀长度，比如前10个字符 (前提是多数值是唯一的..)，那么短索引可以提高查询速度，并且可以减少磁盘的空间，也可以减少I/0操作。
9. 选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快
2.3.2 不适合创建索引的情况：
1. 表记录太少
2. 经常增删改的表或者字段
3. Where 条件里用不到的字段不创建索引
4. 数据重复且分布平均的表字段不适合建索引。如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

2.4 索引的类型

  • 普通索引
  • 唯一性索引
  • 主键索引（主索引）
  • 复合索引
  • 全文索引
2.4.1 普通索引

最基本的索引，不具备唯一性，就是加快查询速度

2.4.1.1 创建普通索引

方法一：创建表时添加索引语法：

create table 表名（
	index 索引名称 （字段）
	index 索引名称 （字段）
）

注：可以使用key，也可以使用index ；index 索引名称 （字段） ，索引名称，可以加也可以不加，不加使用字段名作为索引名。

mysql> create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) );

注意：index和 key 是相同的

mysql> create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );

mysql> create table demo2( id int(4), name varchar(20), pwd varchar(20), key index_pwd(pwd) ); #加上名称

方法二： 当表创建完成后，使用alter为表添加索引

alter table 表名 add index 索引名称 （字段1，字段2.....）;
2.4.1.2 查看索引

image-20220322210846545

注：如果Key是MUL, 就是一般性索引，该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。就是表示是一个普通索引。

我们先删除索引

mysql> alter table demo drop key pwd; ##注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段

再用alter添加

mysql> alter table demo add key(pwd);
2.4.2 唯一索引

与普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，用来约束内容，字段值只能出现一次。应该加唯一索引。唯一性允许有NULL值<允许为空>。

2.4.2.1 创建唯一索引

方法一：创建表时加唯一索引

create table 表名(
	unique key 索引名 （字段）;
)

注意：常用在值不能重复的字段上，比如说用户名，电话号码，身份证号。

mysql> create table demo3(id int(4) auto_increment primary key, uName varchar(20), uPwd varchar(20), unique  index  (uName));
2.4.2.2 查看索引

image-20220322210939363

方法二：修改表时加唯一索引

alter table 表名 add unique 索引名 (字段);

mysql> alter table demo3 drop key uName;

mysql> alter table demo3 add unique(uName);
2.4.3 主键索引

查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，不允许为空

2.4.3.1 创建主键索引

方法一：创建表创建主键索引

create table 表名(
	primary key(字段));
)

mysql> create table demo5( id int(4) not null auto_increment, name varchar(20) default null,primary key(id));
2.4.3.2 查看索引

image-20220322211044739

mysql> show create table demo5;

mysql> show index from demo5 \G

image-20220322211106008

方法二：创建表后添加<不推荐>，如果生产的数据无法保证唯一，创建主键报错

先删除测试

删除遇到这种情况是auto_increment的原因

mysql> alter table demo5 change id id int(4) not null; 先取消自增长

mysql> alter table demo5 drop primary key; 再删除主键

mysql> alter table demo5 change id id int(4) not null primary key auto_increment;

总结：主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值

2.4.4 复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引

例： 创建一个表存放服务器允许或拒绝的IP和port，要记录中IP和port要唯一。

mysql> create table firewall ( host varchar(15) not null ,port smallint(4) not null ,access enum('deny','allow') not null, primary key (host,port));   #联合主键

image-20220322211151099

mysql> insert into firewall values('10.96.52.46',22,'deny');

mysql> insert into firewall values('10.96.52.46',21,'allow');

mysql> insert into firewall values('10.96.52.46',21,'allow');

报错：

ERROR 1062 (23000): Duplicate entry '10.96.52.46-21' for key 'PRIMARY'

插入一样的数据就报错，只能是唯一值。

总结：

建表的时候如果加各种索引，顺序如下：

create table 表名（字段定义，PRIMARYKEY  (`bId`),UNIQUE KEY `bi` (`bImg`),KEY `bn` (`bName`),KEY `ba` (`author`)）
2.4.5 全文索引 (FULLTEXT INDEX)

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果

目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题

方法一：创建表时创建

create table 表名（
	列定义
	fulltext key 索引名 （字段）;
）


mysql> create table u2(id int,name varchar(50), fulltext key full_key(name));

mysql> show create table u2\G
*************************** 1. row ***************************
       Table: u2
Create Table: CREATE TABLE `u2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  FULLTEXT KEY `full_key` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

方法二：修改表时添加

alter table 表名 add fulltext 索引名 （字段）;

ALTER TABLE `books` ADD FULLTEXT  [索引名] (`author` )

注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效,此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）Coreseek技术来处理中文。

一般交给第三方软件进行全文索引

http://sphinxsearch.com

2.5 删除索引

DROP INDEX [indexName] ON table_name;

示例：

mysql> drop index pwd on demo;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.6 查看索引

SHOW INDEX [indexName] FROM table_name;

示例：

mysql> show index from u2\G
*************************** 1. row ***************************
        Table: u2
   Non_unique: 1
     Key_name: full_key
 Seq_in_index: 1
  Column_name: name
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

三、字段修饰符

3.1 null和not null修饰符(约束)

我们通过这个例子来看看

mysql> create table worker(id int not null,name varchar(8) not null,pass varchar(20) not null);

mysql> insert into worker values(1,'HA','123456');
 
mysql> insert into worker values(1,'LB',null);

报错：ERROR 1048 (23000): Column 'pass' cannot be null不能为null

mysql>insert into worker values(2,'HPC','');

注：NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”。

3.2 null和not null区别

我们可能会有以下的疑问

1、字段类型是not null，为什么可以插入空值

2、为什么not null的效率比null高

3、判断字段不为空的时候，到底要  select * from table where column <> '' 还是要用 select * from table where column is not null 呢。

问题1：字段类型是not null，为什么可以插入空值？

首选先了解一个问题 “空值” 和 “NULL”有什么不一样？

1、空值是不占用空间的

2、mysql中的NULL其实是占用空间的，下面是来自于MYSQL官方的解释

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

翻译：

“空列需要行中的额外空间来记录其值是否为空。对于MyISAM表，每个NULL列需要一个额外的位，四舍五入到最接近的字节。

比如：一个杯子，空值''代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是里面是有空气的。

**问题2：为什么not null的效率比null高？ **

NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。

而且索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MyISAM中固定大小的索引变成可变大小的索引--------这也是《高性能mysql第二版》介绍的解读：“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理，所以使用not null 比null效率高

问题3:判断字段不为空的时候，到底要 select * from table where column <> '' 还是要用 select * from table where column is not null

举个例子说明：

mysql>create table test(col1 varchar(10) not null, col2 varchar(10) null)ENGINE=MyISAM;

mysql>insert into test values('',null);

mysql>insert into test values('1','2');

mysql>insert into test values('','1');

# 查询1
select * from test where col1 is not null		# 不能排除字段为空和值为空的值
# 查询2
select * from test where col1 <>'';		# 可以排除字段为空和值为空的值

image-20230609150042855

下面我分别用这两条语句查询看看

image-20230609150229122

说明：为空表示不占空间，null占用空间

3.3 default 设定字段的默认值（约束）

3.3.1 为字段指定默认的值
mysql>create table test2(name varchar(8) not null,dept varchar(25) default 'SOS');

mysql>insert into test2 (name) values ('kko');

image-20230609150603975

总结 ：

如果字段没有设定default ,mysql依据这个字段是null还是not null，如果为可以为null，则为null。如果不可以为null，报错。

如果时间字段，默认为当前时间 ，插入0时，默认为当前时间。

如果是enum 类型，默认为第一个元素。

3.4 auto_increment字段约束

3.4.1 自动增长

只能修饰 int字段。 表明mysql应该自动为该字段生成一个唯一没有用过的数（每次在最大ID值的基础上加1。特例：如果目前最大ID是34，然后删除34，新添加的会是35）。对于主键，这是非常 有用的。 可以为每条记录创建一个惟一的标识符

mysql>create table items ( id int not null auto_increment primary key , label varchar(20) not null);

mysql>insert into items (label) values ('aaba');

mysql>insert into items values (9,'aaba');

#再插入一条id将为多少?

mysql>insert into items (label) values ('abc');
image-20230609151019093 
#再次插入数据
mysql>insert into items values (9,'adl');

报错：ERROR 1062 (23000): Duplicate entry '9' for key 'PRIMARY'提示主键冲突，主键约束唯一。

3.4.2 指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加。

下面创建表 student，指定主键从 10 开始自增长。SQL 语句和运行结果如下：

mysql> CREATE TABLE student (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> PRIMARY KEY(ID)
    -> )AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.03 sec)

向 student 表中插入数据，并使用 SELECT 命令查询表中记录。

mysql> INSERT INTO student (name)VALUES('Java');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM student;
+-----+------+
| id  | name |
+-----+------+
| 10 | Java |
+-----+------+

mysql> INSERT INTO student (name)VALUES('Python');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
| 10 | Java   |
| 11 | Python |
+----+--------+
2 rows in set (0.00 sec)

3.5 清除表中的记录

3.5.1 清空表中所有记录

方法一:

delete 不加where条件，清空所有表记录。但是delete不会清零auto_increment 值

示例：

mysql> delete from items;     #删除表中记录
Query OK, 3 rows affected (0.00 sec)

mysql> select * from items;     #查看表中记录
Empty set (0.00 sec)

mysql> insert into items (label) values ("aaaa");   #插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from items;         #ID直接从10开始  
+----+-------+
| id | label |
+----+-------+
| 11 | aaaa  |
+----+-------+
1 row in set (0.00 sec)

方法二：删除表中所有记录，清auto_increment 值。

作用：删除表的所有记录，并清零auto_increment 值。新插入的记录从1开始。

语法：

truncate  table  name；

示例：

mysql> truncate table items;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into items values(null,'abv');
Query OK, 1 row affected (0.00 sec)

mysql> select * from items;
+----+-------+
| id | label |
+----+-------+
|  1 | abv   |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into items(label)values('hkuyb');
Query OK, 1 row affected (0.00 sec)

mysql> select * from items;
+----+-------+
| id | label |
+----+-------+
|  1 | abv   |
|  2 | hkuyb |
+----+-------+
2 rows in set (0.00 sec)
3.5.2 delete和truncate的区别
内存空间
truncate删除数据后重新写数据会从1开始，而delete删除数据后只会从删除前的最后一行续写；内存空间上，truncate省空间

处理速度
因为，truncate是直接从1开始，即全部清空开始，而delete需要先得到当前行数，从而进行续写；所以truncate删除速度比delete快；

语句类型
delete属于DML语句，而truncate和drop都属于DDL语句，这造成了它们在事务中的不同现象：

delete在事务中，因为属于DML语句，所以可以进行回滚和提交操作（由操作者）
truncate和drop则属于DDL语句，在事务中，执行后会自动commit，所以不可以回滚

image-20230609152548763

四、约束

4.1 约束含义

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

注意：约束是为了保证数据的完整性，索引是为了提高查询速度。

4.2 6大约束

mysql中存在的6大约束

4.2.1 主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。

主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

4.2.2 外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。

例如，一个水果摊，只有苹果、桃子、李子、西瓜 4 种水果，那么，你来到水果摊要买水果只能选择苹果、桃子、李子和西瓜，不能购买其它的水果。

4.2.3 唯一约束

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

4.2.4 检查约束
MySQL 5.7 不生效

检查约束是用来检查数据表中，字段值是否有效的一个手段。

例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是大学生，年龄一般应该在 18~30 岁之间。在设置字段的检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

4.2.5 非空约束

非空约束用来约束表中的字段不能为空。例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

4.2.5 默认值约束

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。

例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

4.3 主键约束

主键（PRIMARY KEY）的完整称呼是“主键约束”，是 MySQL中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。

主键分为单字段主键和多字段联合主键，本节将分别讲解这两种主键约束的创建、修改和删除。

使用主键应注意以下几点：

  • 每个表只能定义一个主键。
  • 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
  • 一个字段名只能在联合主键字段表中出现一次。
  • 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。
4.3.1 添加约束
4.3.1.1 在创建表时设置主键约束

在创建数据表时设置主键约束，既可以为表中的一个字段设置主键，也可以为表中多个字段设置联合主键。但是不论使用哪种方法，在一个表中主键只能有一个。下面分别讲解设置单字段主键和多字段联合主键的方法。

1) 设置单字段主键

在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。

在定义字段的同时指定主键，语法格式如下：

<字段名> <数据类型> PRIMARY KEY [默认值]

示例1：在 test_db 数据库中创建 tb_emp1 数据表，其主键为 id，SQL 语句和运行结果如下

mysql> CREATE TABLE tb_emp1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

或者是在定义完所有字段之后指定主键，语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

示例2:在 test_db 数据库中创建 tb_emp2 数据表，其主键为 id，SQL 语句和运行结果如下

mysql> CREATE TABLE tb_emp2
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

2）在创建表时设置联合主键

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。

实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

主键由多个字段联合组成，语法格式如下：

PRIMARY KEY [字段1，字段2，…,字段n]

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束

示例1：创建数据表 tb_emp3，假设表中没有主键 id，为了唯一确定一个员工，可以把 name、deptId 联合起来作为主键，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp3
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | NO   | PRI | NULL    |       |
| deptId | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.14 sec)
4.3.1.2 在修改表时添加主键约束

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。但是需要注意的是，设置成主键约束的字段中不允许有空值。

在修改数据表时添加主键约束的语法格式如下

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

示例：查看 tb_emp4 数据表的表结构，SQL 语句和运行结果如下所示。

mysql>  CREATE TABLE tb_emp4 (id INT(11) not null, name VARCHAR(25),deptId INT(11),salary FLOAT);
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_emp4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

修改数据表 tb_emp4，将字段 id 设置为主键，SQL 语句和运行结果如下。

mysql> ALTER TABLE tb_emp4
    -> ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tb_emp4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

通常情况下，当在修改表时要设置表中某个字段的主键约束时，要确保设置成主键约束的字段中值不能够有重复的，并且要保证是非空的。否则，无法设置主键约束。

4.3.2 删除主键约束

当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

删除主键约束的语法格式如下所示：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

删除 tb_emp4 表中的主键约束，SQL 语句和运行结果如下。

mysql> ALTER TABLE tb_emp4
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_emp4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

注意：由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束。

4.4 外键约束

4.4.1 添加约束
4.4.1.1在创建表时设置外键约束

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

示例：

为了展现表与表之间的外键关系，本例在 test_db 数据库中创建一个部门表 dept，表结构如下表所示。

字段名称数据类型备注
idINT(11)部门编号
nameVARCHAR(22)部门名称
locationVARCHAR(22)部门位置

创建 dept 的 SQL 语句和运行结果如下所示。

mysql> CREATE TABLE dept
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)

创建数据表 emp6，并在表 emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 dept 的主键 id，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> FOREIGN KEY(deptId) REFERENCES dept(id)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> desc emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

以上语句执行成功之后，在表 emp6 上添加了名称为 fk_emp_dept 的外键约束，外键名称为 deptId，其依赖于表 dept 的主键 id。

注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can't create table”错误。

插入数据

---dept表--
INSERT INTO dept VALUES(1,'sale','shanghai')
INSERT INTO dept VALUES(2,'it','wuhan')

---emp6表--
INSERT INTO emp6 VALUES(1,'zhangsan',1,'3500')
INSERT INTO emp6 VALUES(2,'lisi',2,'4500')
INSERT INTO emp6 VALUES(3,'wangwu',3,'5500')    #报错
INSERT INTO emp6 VALUES(4,'wangchao',NULL,'5500')   #成功，不指向任何主键

报错的原因在于已经超过了主表的约束了。

4.4.1.2 在修改表时添加外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

示例：

修改数据表 emp2，将字段 deptId 设置为外键，与数据表 tb_dept1 的主键 id 进行关联，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_emp1
    -> ADD CONSTRAINT fk_tb_dept
    -> FOREIGN KEY(deptId)
    -> REFERENCES dept(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  KEY `fk_tb_dept` (`deptId`),
  CONSTRAINT `fk_tb_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

注意：在为已经创建好的数据表添加外键约束时，要确保添加外键约束的列的值全部来源于主键列，并且外键列不能为空。外键的字段类型和主键的字段类型要一致，并且存储引擎必须是innodb

4.4.2 删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

示例：

删除数据表 tb_emp1 中的外键约束 fk_tb_dept，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_emp1
    -> DROP FOREIGN KEY fk_tb_dept;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  KEY `fk_tb_dept` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

可以看到，tb_emp1 中已经不存在 FOREIGN KEY，原有的名称为 fk_emp_dept 的外键约束删除成功。

4.5 唯一约束

MySQL唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。如果其中一条记录的 id 值为‘0001’，那么该表中就不能出现另一条记录的 id 值也为‘0001’。

唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

4.5.1 添加约束
4.5.1.1 在创建表时设置唯一约束

唯一约束可以在创建表时直接设置，通常设置在除了主键以外的其它列上。

在定义完列之后直接使用 UNIQUE 关键字指定唯一约束，语法格式如下：

<字段名> <数据类型> UNIQUE

示例：

创建数据表 dept2，指定部门的名称唯一，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE dept2
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) UNIQUE,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> DESC dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(40) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.08 sec)
4.5.1.2 在修改表时添加唯一约束

在修改表时添加唯一约束的语法格式为：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

示例：

修改数据表 dept，指定部门的名称唯一，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE dept
    -> ADD CONSTRAINT unique_name UNIQUE(name);
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4.5.2 删除唯一约束

在 MySQL 中删除唯一约束的语法格式如下：

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

示例：

删除数据表 dept 中的唯一约束 unique_name，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE dept
    -> DROP INDEX unique_name;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.6 MySQL检查约束

MySQL检查约束（CHECK）是用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

注意：MySQL5.7存在check约束，但是只是做语法校验，不会生效。

4.6.1 选取设置检查约束的字段

检查约束使用 CHECK 关键字，具体的语法格式如下：

CHECK <表达式>

其中，“表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。

若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。

在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

注意：若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件。

4.6.2 在创建表时设置检查约束

一般情况下，如果系统的表结构已经设计完成，那么在创建表时就可以为字段设置检查约束了。

创建表时设置检查约束的语法格式如下：

CHECK(<检查约束>)

示例：

在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 1000且小于 2000，,性别取值是male或者是famale,SQL 语句和运行结果如下所示。

mysql> CREATE TABLE emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> sex varchar(5) CHECK(sex in('male','female')),
    -> salary FLOAT,
    -> CHECK(salary>1000 AND salary<2000),
    -> );
Query OK, 0 rows affected (0.37 sec)

插入数据

 mysql> insert into t11 VALUES(1,'zhangsan','nan','3500')   #成功插入？？？？？
4.6.3 在修改表时添加检查约束

如果一个表创建完成，可以通过修改表的方式为表添加检查约束。

修改表时设置检查约束的语法格式如下：

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

示例：

修改 emp7 数据表，要求 id 字段值大于 0，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE emp7
    -> ADD CONSTRAINT check_id
    -> CHECK(id>10);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 插入数据
			 
mysql> insert into emp7 VALUES(2,'lisi','nan','3500')  #可以插入，说明并未做到检查约束的作用
4.6.4 删除检查约束

修改表时删除检查约束的语法格式如下：

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

示例：

删除 emp7 表中的 check_id 检查约束，SQL 语句和运行结果如下所示：

mysql> ALTER TABLE emp7
    -> DROP CONSTRAINT check_id;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

五、视图

5.1 什么是视图

视图就是一个存在于数据库中的虚拟表，视图本身没有数据，只是通过执行相应的select语句完成获得相应的数据。

5.2 为什么使用视图 ？

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

例如，下面的数据库中有一张公司部门表 department。表中包括部门号（d_id）、部门名称（d_name）、功能（function）和办公地址（address）。department 表的结构如下：

mysql> DESC department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   | PRI | NULL    |       |
| d_name   | varchar(20) | NO   | UNI    | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

还有一张员工表 worker。表中包含了员工的工作号（num）、部门号（d_id）、姓名（name）、性别（sex）、出生日期（birthday）和家庭住址（homeaddress）。worker 表的结构如下：

mysql> DESC worker;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num         | int(10)     | NO   | PRI | NULL    |       |
| d_id        | int(4)      | YES  |MUL     | NULL    |       |
| name        | varchar(20) | NO   |     | NULL    |       |
| sex         | varchar(4)  | NO   |     | NULL    |       |
| birthday    | datetime    | YES  |     | NULL    |       |
| homeaddress | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

由于各部门领导的权力范围不同，因此，各部门的领导只能看到该部门的员工信息；而且，领导可能不关心员工的生日和家庭住址。为了达到这个目的，可以为各部门的领导建立一个视图，通过该视图，领导只能看到本部门员工的指定信息。

例如，为生产部门建立一个名为 product view 的视图。通过视图 product view，生产部门的领导只能看到生产部门员工的工作号、姓名和性别等信息。这些 department 表的信息和 worker 表的信息依然存在于各自的表中，而视图 product_view 中不保存任何数据信息。当 department 表和 worker 表的信息发生改变时，视图 product_view 显示的信息也会发生相应的变化。

技巧：如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据。

MySQL 的视图不支持输入参数的功能，因此交互性上还有欠缺。但对于变化不是很大的操作，使用视图可以很大程度上简化用户的操作

5.3 视图和真实表的区别

视图并不同于数据表，它们的区别在于以下几点：

  • 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
  • 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
  • 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
  • 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
  • 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
  • 视图的建立和删除只影响视图本身，不影响对应的基本表。

5.4 视图的优点

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

  • 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

  • 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

  • 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

  • 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

  • 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

  • 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上

5.5 创建视图

5.5.1 语法
语法：create view 视图名称（即虚拟的表名） as select 语句。

语法说明如下

  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
  • <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
5.5.2 创建视图
mysql>  create view bc as select b.bName ,b.price ,c.bTypeName from books as b left join category as c  on b.bTypeId=c.bTypeId ;

5.6 查看视图

查看使用可以使用DESC进行查看，具体语法如下

DESC 视图名；

如果查看详细视图表，则使用如下语法：

SHOW CREATE VIEW 视图名称；

image-20230612104556541

查看创建视图中的详细定义。

查询视图中的数据

image-20220322224246876

5.7 更新或修改视图

5.7.1 更新视图语法
alter view视图名称（即虚拟的表名） as select 语句

update view视图名称 set 条件

语法说明如下：

  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
  • <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

注意：还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询，引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

示例：

mysql> alter  view bc as select b.bName ,b.publishing ,c.bTypeId from books as b left join category as c  on b.bTypeId=c.bTypeId ;

image-20220322224358305

更新视图表中的数据也会同步影响到逻辑表中的数据，举例参考：

示例：

1）创建新的视图

mysql> create or replace view v1 as select bName,price,publishing from books;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1 limit 1,5;     #查看视图内容

image-20220324164140292

2）更视图表中的数据

mysql> update v1 set bName='10W why?' where price=34;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3）查看视图数据

image-20220324164618756

4）查看逻辑表中的数据

image-20220324164725049

发现也更新了，说明视图和逻辑表中的数据是同步更新的。

5.8 视图中插入数据

5.8.1 语法
INSERT INTO 视图名（“字段”） values (“数据”)

示例：

mysql> insert into v1(bName,price,publishing) value("红楼梦",'110',"中国青年出版社");  #插入数据
Query OK, 1 row affected (0.00 sec)
5.8.2 查看数据

image-20220324165505039

5.9 删除视图内容

5.9.1 语法
delete from view 视图名 条件

示例：

mysql> delete from v1 where price=34;

image-20220324173425815

5.10 删除视图

5.10.1 语法
drop view 视图名

示例：

mysql> drop view v1;

image-20220324165548495

5.11 视图的其他案例应用

具备以下特点的视图不允许更新

  • 包含以下关键字的sql语句：分组函数，distinct，group，by ,having,union,union all等关键字
  • 视图中含SUM(),MAX(),MIN()等函数
  • join from 一个不能更新的视图
  • where子句的子查询引用了from字句中的表
案例1：创建了一个价格去重的视图
mysql> create or replace view v1 as select distinct(price),publishing,bName from books; 
Query OK, 0 rows affected (0.01 sec)

mysql> update v1 set bName='HA' where price=110;   #更新失败
ERROR 1288 (HY000): The target table v1 of the UPDATE is not updatable
mysql>

案例2：创建了一个联合查询视图
mysql> create or replace view v1 as select price,bName from books union select bTypeId,bTypeName from category;
Query OK, 0 rows affected (0.01 sec)

mysql> update v1 set bName='HA' where price=110;  #更新失败
ERROR 1288 (HY000): The target table v1 of the UPDATE is not updatable
mysql>

案例3：添加分组查询
mysql> create or replace view v1 as select bName,bTypeId from books group by bName;
Query OK, 0 rows affected (0.00 sec)

mysql> update v1 set bName='HA' where bTypeId=7;
ERROR 1288 (HY000): The target table v1 of the UPDATE is not updatable  #更新失败
mysql>

案例4：创建含有sum（）函数的查询
mysql> create or replace view v1 as select sum(price),bName,bTypeId from books;
Query OK, 0 rows affected (0.00 sec)

mysql> update v1 set bName='HA' where bTypeId=7;  #更新失败
ERROR 1288 (HY000): The target table v1 of the UPDATE is not updatable
mysql>

案例5：创建含有连接查询
mysql> create or replace view v1 as select b.bName ,b.publishing ,c.bTypeId from books as b left join category as c  on b.bTypeId=c.bTypeId ;
Query OK, 0 rows affected (0.00 sec)

mysql> update v1 set bName='HA' where price=34;
ERROR 1288 (HY000): The target table bc of the UPDATE is not updatable  #更新失败

附件：

shell
	第一步：创建表
	CREATE TABLE `app_user`(
    `id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
    `email` VARCHAR(50) NOT NULL COMMENT '邮箱',
    `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
    `gender` TINYINT DEFAULT '0' COMMENT '性别（0-男  ： 1-女）',
    `password` VARCHAR(100) NOT NULL COMMENT '密码',
    `age` TINYINT DEFAULT '0' COMMENT '年龄',
    `create_time` DATETIME DEFAULT NOW(),
    `update_time` DATETIME DEFAULT NOW(),
    PRIMARY KEY (`id`) 
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='app用户表'


	第二步：
SET GLOBAL log_bin_trust_function_creators=TRUE;  
  
  第三步：
DELIMITER $$
CREATE FUNCTION mock_data1() RETURNS INT  
BEGIN  
    DECLARE i INT DEFAULT 0; -- 循环计数器  
  
    WHILE i < 10000000 DO -- 设置循环次数为10,000,000  
        INSERT INTO app_user (`name`, `email`, `phone`, `gender`, `password`, `age`)  
        VALUES (  
            CONCAT('用户', i),  
            CONCAT('user_', LPAD(i, 8, '0'), '@example.com'), -- 使用LPAD确保email长度一致并唯一  
            CONCAT('18', LPAD(FLOOR(10000000 + RAND() * 8999999999), 10, '0')), -- 生成10位数的随机手机号  
            FLOOR(RAND() * 2), -- 随机生成0或1作为性别  
            UUID(), -- 生成UUID作为密码（实际应用中应更安全地生成密码）  
            FLOOR(RAND() * 100) -- 生成0-99的随机数作为年龄  
        );  
        SET i = i + 1; -- 循环计数器递增  
    END WHILE;  
  
    RETURN i; -- 返回循环计数器的值（也就是插入的记录数）  
END
$$
  
DELIMITER ;

第四步：
SELECT mock_data1();