1 约束
约束对应的英语单词:constraint
在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性。
约束包括:
- 非空约束:not null
- 唯一性约束: unique
- 主键约束: primary key (简称PK)
- 外键约束:foreign key(简称FK)
- 检查约束:check(mysql不支持,oracle支持)
1.1 非空约束:not null
非空约束not null约束的字段不能为NULL。
注意:not null只有列级约束,没有表级约束
mysql> drop table if exists t_vip;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> create table t_vip(
-> id int,
-> name varchar(255) not null
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t_vip(id,name) value(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(id,name) value(2,'lisi');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(id) value(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql>
1.2 唯一性约束: unique
唯一性约束unique约束的字段不能重复,但是可以为NULL。
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t_vip(
-> id int,
-> name varchar(255) unique,
-> email varchar(255)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t_vip(id,name,email) value(1,'zhangsan','123@123.com');
Query OK, 1 row affected (0.01 sec)
#不能重复
mysql> insert into t_vip(id,name,email) value(2,'zhangsan','123@123.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_vip.name'
#可以为null
mysql> insert into t_vip(id,email) value(3,'lisi@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(id) value(4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_vip;
+------+----------+--------------+
| id | name | email |
+------+----------+--------------+
| 1 | zhangsan | 123@123.com |
| 3 | NULL | lisi@123.com |
| 4 | NULL | NULL |
+------+----------+--------------+
3 rows in set (0.00 sec)
新需求:name和email两个字段联合起来具有唯一性
- 约束直接添加到列后面的,叫做列级约束。如:
name varchar(255) unique
- 约束没有添加在列的后面,这种约束被称为表级约束。如:
unique(name,email)
什么时候使用表级约束呢?需要给多个字段联合起来添加某一个约束的时候,需要使用表级约束。
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.04 sec)
mysql> create table t_vip(
-> id int,
-> name varchar(255),
-> email varchar(255),
-> unique(name,email)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t_vip(id,name,email) values(1,'zhangsan','123@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(id,name,email) values(2,'lisi','123@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(id,name,email) values(2,'zhangsan','111@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(id,name,email) values(3,'zhangsan','123@123.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-123@123.com' for key 't_vip.name'
mysql>
在mysql当中,如果一个字段同时被not null和unique约束的话,该字段自动变成主键字段。(注意:oracle中不一样!)
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t_vip(
-> id int,
-> name varchar(255) not null unique
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(255) | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
1.3 主键约束:primary key
1.主键约束的相关术语
- 主键约束:就是一种约束。
- 主键字段:该字段上添加了主键约束,这样的字段叫做:主键字段
- 主键值:主键字段中的每一个值都叫做:主键值。
2.什么是主键?有啥用?
- 主键值是每一行记录的唯一标识。
- 主键值是每一行记录的身份证号!!!
记住:任何一张表都应该有主键,没有主键,表无效
主键的特征:not null + unique 即:主键值不能是NULL,同时也不能重复
3.注意:
- 单一主键:1个字段做主键;复合主键:两个字段联合起来做主键,如:
primary key(id,name)
。在实际开发中不建议使用复合主键。建议使用单一主键 - 可以使用列级约束添加主键
id int primary key
,也可以使用表级约束添加主键primary key(id)
- 一张表,主键只能有1个
- 主键值建议使用:int、bigint、char等类型。不建议使用:varchar来做主键。主键值一般都是数字,一般都是定长的
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.06 sec)
mysql> create table t_vip(
-> id int primary key,
-> name varchar(255)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t_vip(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(1,'lisi');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,'lisi')' at line 1
4.主键除了单一主键和复合主键之外,还可以分为自然主键和业务主键
- 自然主键:主键值是一个自然数,和业务没关系。
- 业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。这就是业务主键!
在实际开发中使用业务主键多,还是使用自然主键多一些?
自然主键使用比较多,因为主键只要做到不重复就行,不需要有意义。
业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。
5.在mysql当中,有一种机制,可以帮助我们自动维护一个主键值id int primary key auto_increment //auto_increment表示自增,从1开始,以1递增
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t_vip(
-> id int primary key auto_increment,
-> name varchar(255)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 3 | zhangsan |
| 4 | zhangsan |
+----+----------+
4 rows in set (0.00 sec)
1.4 外键约束:foreign key
1.外键约束的相关术语
- 外键约束:一种约束(foreign key)
- 外键字段:该字段上添加了外键约束
- 外键值:外键字段当中的每一个值。
2.业务背景:请设计数据库表,来描述“班级和学生”的信息?
第一种方案:班级和学生存储在一张表中???
t_student
no(pk) name classno classname
----------------------------------------------------------------------------------
1 jack 100 北京市大兴区亦庄镇第二中学高三1班
2 lucy 100 北京市大兴区亦庄镇第二中学高三1班
3 lilei 100 北京市大兴区亦庄镇第二中学高三1班
4 hanmeimei 100 北京市大兴区亦庄镇第二中学高三1班
5 zhangsan 101 北京市大兴区亦庄镇第二中学高三2班
6 lisi 101 北京市大兴区亦庄镇第二中学高三2班
7 wangwu 101 北京市大兴区亦庄镇第二中学高三2班
8 zhaoliu 101 北京市大兴区亦庄镇第二中学高三2班
分析以上方案的缺点:数据冗余,空间浪费
第二种方案:班级一张表、学生一张表
t_class 班级表
classno(pk) classname
------------------------------------------------------
100 北京市大兴区亦庄镇第二中学高三1班
101 北京市大兴区亦庄镇第二中学高三1班
t_student 学生表
no(pk) name cno(FK引用t_class这张表的classno)
----------------------------------------------------------------
1 jack 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 zhaoliu 101
当cno字段没有任何约束的时候,可能会导致数据无效。可能出现一个102,但是102班级不存在。所以为了保证cno字段中的值都是100和101,需要给cno字段添加外键约束。那么:cno字段就是外键字段。cno字段中的每一个值都是外键值。
3.注意:t_class是父表,t_student是子表
删除表的顺序?先删子,再删父。
创建表的顺序?先创建父,再创建子。
删除数据的顺序?先删子,再删父。
插入数据的顺序?先插入父,再插入子。
4.子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
不一定是主键,但至少具有unique约束。
5.外键值可以为NULL。
2 存储索引
2.1 存储引擎的使用
存储引擎是MySQL中特有的一个术语,其它数据库中没有。(Oracle中有,但是不叫这个名字)
存储引擎是一个表存储/组织数据的方式,不同的存储引擎,表存储数据的方式不同。
数据库中的各表均被(在创建表时)指定的存储引擎来处理。 服务器可用的引擎依赖于以下因素:
- MySQL 的版本
- 服务器在开发时如何被配置
- 启动选项
为了解当前服务器中有哪些存储引擎可用,可使用 SHOW ENGINES 语句:
mysql> SHOW ENGINES\G
在创建表时,可使用 ENGINE
选项为 CREATE TABLE 语句显式指定存储引擎。
CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;
如果在创建表时没有显式指定存储引擎,则该表使用当前默认的存储引擎
默认的存储引擎可在 my.ini 配置文件中使用 default-storage-engine 选项指定。
现有表的存储引擎可使用 ALTER TABLE
语句来改变: ALTER TABLE TABLENAME ENGINE = INNODB;
为确定某表所使用的存储引擎,可以使用 SHOW CREATE TABLE
或 SHOW TABLE STATUS
语句:
mysql> SHOW CREATE TABLE emp\G
mysql> SHOW TABLE STATUS LIKE 'emp'\G
2.2 常用的存储引擎
2.2.1 MyISAM 存储引擎
MyISAM 存储引擎是 MySQL 最常用的引擎。它管理的表具有以下特征:
- 使用三个文件表示每个表:
• 格式文件 — 存储表结构的定义(mytable.frm)
• 数据文件 — 存储表行的内容( mytable.MYD)
• 索引文件 — 存储表上索引( mytable.MYI)索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。对于一张表来说,只要是主键,或者加有unique约束的字段上会自动创建索引。 - 灵活的
auto_increment
字段处理 - 可被转换为压缩、只读表来节省空间,这是它的优势
- 不支持事务机制,安全性低。
2.2.2 InnoDB 存储引擎
这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。
它管理的表具有下列主要特征:
- 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
- InnoDB 表空间 tablespace 被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、 SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本( MVCC)和行级锁定
- 支持外键及引用的完整性,包括级联删除和更新
InnoDB最大的特点就是支持事务,以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,不能很好的节省存储空间。
2.2.3 MEMORY 存储引擎
使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征:
- 在数据库目录内,每个表均以.frm 格式的文件表示。
- 表数据及索引被存储在内存中。
- 表级锁机制。
- 不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为 HEAP 引擎。
MEMORY引擎优点:查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点:不安全,关机之后数据消失。因为数据和索引都是在内存当中。
• MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。 MyISAM 表的另一种适用情形是使用压缩的只读表。
• 如果查询中包含较多的数据更新操作,应使用 InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合
操作提供了良好的并发机制。
• 可使用 MEMORY 存储引擎来存储非永久需要的数据,或者是能够从基于磁盘的表中重新生成的数据。
3 事务
3.1 概述
事务可以保证多个操作原子性,要么全成功,要么全失败。对于数据库来说事务保证批量的 DML 要么全成功,要么全失败。事务具有四个特征 ACID
- 原子性( Atomicity)
整个事务中的所有操作,必须作为一个单元全部完成(或全部取消)。 - 一致性( Consistency)
所有事务要求,在同一个事务当中,所有操作必须同时成功,或者同时失败,以保证数据的一致性。
比方说A转给B 100块,A的账户必然减少100块,B的账户必然增加100块。如果A给B转了100之后系统宕机,A减少100块,B却没有增加100块,这就是数据的不一致性。 - 隔离性(Isolation)
一个事务不会影响其他事务的运行。 - 持久性(Durability)
在事务完成以后,该事务对数据库所作的更改将持久地保存在数据库之中,并不会被回滚。
事务中存在一些概念:
- 事务( Transaction):一批操作(一组 DML)
- 开启事务( Start Transaction)
- 回滚事务( rollback)
将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
回滚事务标志着,事务的结束。并且是一种全部失败的结束。 - 提交事务( commit)
清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中。
提交事务标志着,事务的结束。并且是一种全部成功的结束。
e) SET AUTOCOMMIT:禁用或启用事务的自动提交模式
当执行 DML 语句是其实就是开启一个事务
关于事务的回滚需要注意:事务只对 DML 有效果,即只能回滚 insert
、 delete
、update
语句,不能回滚 select(回滚 select 没有任何意义),对于create、 drop、 alter 这些无法回滚。
3.2 事务的提交与回滚演示
3.2.1 创建表
mysql> create table dept_bak as select * from dept;
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> delete from dept_bak;
Query OK, 4 rows affected (0.01 sec)
mysql> select * from dept_bak;
Empty set (0.00 sec)
3.2.2 回滚演示
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | tj |
| 10 | abc | tj |
+--------+-------+------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
Empty set (0.00 sec)
3.2.3 提交演示
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | tj |
+--------+-------+------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | tj |
| 10 | abc | tj |
| 10 | abc | tj |
| 10 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | tj |
| 10 | abc | tj |
| 10 | abc | tj |
| 10 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
mysql>
3.3 自动提交模式
自动提交模式用于决定新事务如何及何时启动。
-
启用自动提交模式:
– 如果自动提交模式被启用,则单条 DML 语句将缺省地开始一个新的事务。
– 如果该语句执行成功,事务将自动提交,并永久地保存该语句的执行结果。
– 如果语句执行失败,事务将自动回滚,并取消该语句的结果。
– 在自动提交模式下,仍可使用 START TRANSACTION 语句来显式地启动事务。这时,一个事务仍可包含
多条语句,直到这些语句被统一提交或回滚。 -
禁用自动提交模式:
– 如果禁用自动提交,事务可以跨越多条语句。
– 在这种情况下,事务可以用 COMMIT 和 ROLLBACK 语句来显式地提交或回滚。
自动提交模式可以通过服务器变量 AUTOCOMMIT 来控制。
例如:
mysql> SET AUTOCOMMIT = OFF;
mysql> SET AUTOCOMMIT = ON;
或
mysql> SET SESSION AUTOCOMMIT = OFF;
mysql> SET SESSION AUTOCOMMIT = ON;
show variables like '%auto%';
– 查看变量状态
3.4 事务的隔离级别
3.4.1 并发异常
• 当多个客户端并发地访问同一个表时,可能出现下面的一致性问题:
- 脏读取( Dirty Read)
又称无效数据的读出,是指在数据库访问中, 事务 T1将某一值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致了T2所读取到的数据是无效的 - 不可重复读( Non-repeatable Read)
在同一个事务中,同一个读操作对同一个数据的前后两次读取产生了不同的结果,这就是不可重复读。 - 幻像读( Phantom Read)
幻像读是指在同一个事务中以前没有的行,由于其他事务的提交而出现的新行。
3.4.2 四个隔离级别
InnoDB 实现了四个隔离级别,用以控制事务所做的修改,并将修改通告至其它并发的事务:
- 读未提交( READ UMCOMMITTED) 没有提交就读到了
导致了:脏读 - 读已提交( READ COMMITTED) 提交之后才能读到
解决了:脏读
导致了:不可重复读
在事务开启之后,第一次读到的数据是3条,当前事务还没有结束,可能第二次再读取的时候,读到的数据是4条,3不等于4,称为不可重复读取。 - 可重复读( REPEATABLE READ) 提交之后也读不到,永远读取的都是刚开启事务时的数据
该隔离级别为 InnoDB 的缺省设置。mysql中默认的事务隔离级别就是这个
解决了:不可重复读
导致了:幻读
早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样。读到的是假象。不够绝对的真实。 - 串行化( SERIALIZABLE) 【序列化】
这是最高隔离级别,效率最低。解决了所有的问题。这种隔离级别表示事务排队,不能并发。每一次读取到的数据都是最真实的,并且效率是最低的。
3.4.3 演示隔离级别
查看隔离级别
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
提示:提示:在MySQL 8.0.3 中,tx_isolation
变量被 transaction_isolation
变量替换了
验证read uncommited
mysql> set global transaction isolation level read uncommitted;
验证:read commited
mysql> set global transaction isolation level read committed;
验证:repeatable read
mysql> set global transaction isolation level repeatable read;
验证:serializable
mysql> set global transaction isolation level serializable;