MySQL(四)

数据库

发布日期: 2020-10-18

更新日期: 2020-10-18

文章字数: 3.6k

阅读时长: 14 分

阅读次数:

1.约束(Constraint)

参考文章：MySQL——约束(constraint)详解

1-1.定义及分类

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

常见的约束：

非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：…（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。
default：默认值。

添加约束的时机：

创建表时
修改表时

约束的添加分类：

列级约束

六大约束语法上都支持，但是，外键约束没有效果
表级约束

除了非空，默认，其他的都支持

1-2.非空约束 not null

drop table if exists t_user;
---------------------------------------------------------------------------------
create table t_user(
    id int,
    username varchar(255) not null,
    password varchar(255)
);
---------------------------------------------------------------------------------
insert into t_user(id,password) values(1,'123');

ERROR 1364 (HY000): Field ‘username’ doesn’t have a default value

insert into t_user(id,username,password) values(1,'lisi','123');

1-3.唯一性约束 unique

唯一性约束修饰的字段具有唯一性，不能重复，但是，可以为NULL。

案例：给某一列添加unique。

drop table if exists t_user;
---------------------------------------------------------------------------------
create table t_user(
    id int,
    username varchar(255) unique  /*列级约束*/ 
);
---------------------------------------------------------------------------------
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');

ERROR 1062 (23000): Duplicate entry ‘zhangsan’ for key ‘username’

案例：给两个列或者多个列添加unique

drop table if exists t_user;
---------------------------------------------------------------------------------
create table t_user(
    id int, 
    usercode varchar(255),
    username varchar(255),
    unique(usercode,username) /*多个字段联合起来添加1个约束unique，即表级约束*/
);
---------------------------------------------------------------------------------
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
---------------------------------------------------------------------------------
insert into t_user values(4,'111','zs');

ERROR 1062 (23000): Duplicate entry ‘111-zs’ for key ‘usercode’

注意：not null约束只有列级约束。没有表级约束。

表级约束：

约束01

1-4.主键约束

1-4-1.添加主键约束

drop table if exists t_user;
---------------------------------------------------------------------------------
create table t_user(
    id int primary key,  /*列级约束*/ 
    username varchar(255),
    email varchar(255)
    /*constraint [键名] primary key(email) 这是完整写法*/
);
---------------------------------------------------------------------------------
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
---------------------------------------------------------------------------------
select * from t_user;

+—-+———-+————+
| id | username | email |
+—-+———-+————+
| 1 | zs | zs@123.com |
| 2 | ls | ls@123.com |
| 3 | ww | ww@123.com |
+—-+———-+————+

insert into t_user(id,username,email) values(1,'jack','jack@123.com');

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

insert into t_user(username,email) values('jack','jack@123.com');

ERROR 1364 (HY000): Field ‘id’ doesn’t have a default value

根据以上的测试得出：id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。

主键的特点：不能为NULL，也不能重复。

1-4-2.主键相关的术语

主键约束 : primary key
主键字段 : id字段添加primary key之后，id叫做主键字段
主键值 : id字段中的每一个值都是主键值

1-4-3.主键作用

表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键
主键的作用：主键值是这行记录在这张表当中的唯一标识（就像一个人的身份证号码一样）

1-4-4.主键的分类

根据主键字段的字段数量
- 单一主键（推荐的，常用的）
- 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式）
根据主键性质
- 自然主键：主键值最好就是一个和业务没有任何关系的自然数（这种方式是推荐的）
- 业务主键：主键值和系统的业务挂钩。例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键（不推荐用）
  
  最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

注意：一张表的主键约束只能有1个

1-4-5.定义主键(使用表级约束方式)

drop table if exists t_user;
---------------------------------------------------------------------------------
create table t_user(
    id int,
    username varchar(255),
    primary key(id)
);
---------------------------------------------------------------------------------
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
insert into t_user(id,username) values(4,'cs');

insert into t_user(id,username) values(4,'cx');

ERROR 1062 (23000): Duplicate entry ‘4’ for key ‘PRIMARY’

MySQL提供主键值自增(非常重要)

drop table if exists t_user;
---------------------------------------------------------------------------------
create table t_user(
    id int primary key auto_increment, /*id字段自动维护一个自增的数字，从1开始，以1递增*/ 
    username varchar(255)
);
---------------------------------------------------------------------------------
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');

提示：Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

1-5.外键约束

外键约束支持列级约束，但是没有效果。

1-5-1.相关术语

外键约束：foreign key
外键字段：添加有外键约束的字段
外键字段：添加有外键约束的字段

要求在从表设置外键关系，从表的外键列的类型和主表的关联列的类型要求是一致或兼容的，主表的关联列必须是一个key（一般是主键或唯一）。

1-5-2.业务背景

案例：请设计数据库表，用来维护学生和班级的信息

方案一：一张表存储所有数据

no(pk)	name	classno	classname
1	zs	12	二中高三1班
2	ls	12	二中高三1班
3	ww	13	二中高三2班
4	ll	13	二中高三2班

缺点：数据冗余

方案二：两张表（班级表和学生表）

t_class 班级表

cno(pk)	cname
12	二中高三1班
13	二中高三2班

t_student 学生表

sno(pk)	sname	classno(该字段添加外键约束fk)
1	zs	12
2	ls	12
3	ww	13
4	ll	13

将方案二的语句写出来：

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求：

删除数据的时候，先删除子表，再删除父表
添加数据的时候，先添加父表，在添加子表
创建表的时候，先创建父表，再创建子表
删除表的时候，先删除子表，在删除父表

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
    cno int,
    cname varchar(255),
    primary key(cno)
);

create table t_student(
    sno int,
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_class(cno)
);

insert into t_class values(12,'二中高三1班');
insert into t_class values(13,'二中高三2班');

insert into t_student values(1,'zs',12);
insert into t_student values(2,'ls',12);
......

外键值可以为NULL。

问题：外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

答：被引用的字段不一定是主键，但至少具有unique约束。

check约束

gender char(1) check(gender in ('男','女')),

查看索引

show index from [表名]

查看 [表名] 里所有的索引，包括主键，外键，唯一。

修改表时添加约束

-- 列级约束
alter table [表名] modify column [列名] 数据类型 default 值/not null/primary key
-- 表级约束
alter table [表名] add 约束类型(字段名)

2.事务(Transaction)

2-1.什么是事务

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = ‘act-001’;
update t_act set balance = balance + 10000 where actno = ‘act-002’;

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

注意：和事务相关的语句只有DML语句(insert delete update)，因为它们这三个语句都是和数据库表当中的“数据”相关的。而事务的存在是为了保证数据的完整性，安全性。

问题：假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

答：不需要事务。但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

事务01

提交事务：commit；回滚事务：rollback。

事务中，还有 save point 语句，即保存点，相当于游戏里的存档，当执行rollback时，不再重头开始，而是从保存点开始回滚事务。

2-2.事务四大特性ACID

A：原子性，即事务是最小的工作单元，不可再分。
C: 一致性，即事务必须保证多条DML语句同时成功或者同时失败。
I：隔离性，即事务A与事务B之间具有隔离。
D：持久性，即最终数据必须持久化到硬盘文件中，事务才算成功的结束。

2-3.事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交(read uncommitted)

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读(Dirty Read)现象，表示读到了脏的数据。
第二级别：读已提交(read committed)

对方事务提交之后的数据我方可以读取到。这种隔离级别解决了脏读现象。读已提交存在的问题是：不可重复读。
第三级别：可重复读(repeatable read)

这种隔离级别解决了不可重复读问题。这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别：序列化读/串行化读(serializable)

解决了所有问题。但是，效率低，需要事务排队。

oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。

2-4.演示事务

MySQL事务默认情况下是自动提交的。

什么是自动提交？只要执行任意一条DML语句则提交一次。

怎么关闭自动提交？

start transaction;

2-4-1.准备表

drop table if exists t_user;
creat table t_use(
    id int primary key auto_increment;
    username varchar(255)
)

2-4-2.演示一

MySQL中的事务是支持自动提交的，只要执行一条DML，则提交一次。

mysql> insert into t_user(username) values('zs');

Query OK, 1 row affected (0.03 sec)

mysql> select * from t_user;

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 row in set (0.00 sec)

mysql> rollback; /*回滚事务*/

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 row in set (0.00 sec)

上述演示一中，MySQL没有关闭自动提交机制，所以即使使用rollback，进行回滚事务，也是没用的

2-4-3.演示二

mysql> start transaction; /*使用start transaction;关闭自动提交机制，标志一个事务开启了*/

mysql> insert into t_user(username) values('lisi');
mysql> insert into t_user(username) values('wangwu');
mysql> select * from t_user;

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 2 | lisi |
| 3 | wangwu |
+—-+———-+
3 rows in set (0.00 sec)

mysql> rollback; /*回滚事务*/
mysql> select * from t_user;

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 row in set (0.00 sec)

此时数据回滚了，只显示了一条记录。

2-4-4.演示三

mysql> start transaction; /*演示二中的rollback事务已经结束，还得重新写这一行*/
---------------------------------------------------------------------------------
mysql> insert into t_user(username) values('wangwu');
mysql> insert into t_user(username) values('rose');
mysql> insert into t_user(username) values('jack');
mysql> select * from t_user; /*1*/
---------------------------------------------------------------------------------
mysql> commit; /*提交，已经将数据持续化到硬盘里了*/
mysql> select * from t_user; /*2*/
---------------------------------------------------------------------------------
mysql> rollback; /*回滚到上一次的提交点，相当于没用*/
mysql> select * from t_user; /*3*/

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+—-+———-+
4 rows in set (0.00 sec) —1

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+—-+———-+
4 rows in set (0.00 sec) —2

+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+—-+———-+
4 rows in set (0.00 sec) —3

id属性使用 auto-incremant机制，自增的2,3在之前用过了。

3.存储引擎（了解）

3-1.完整的建表语句

CREATE TABLE `t_x` (
    `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

从上述的完整的建表语句，可以看出，建表的时候可以指定存储引擎，也可以指定字符集。

MySQL默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8。

3-2.什么是存储引擎

存储引擎这个名字只有在MySQL中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

3-3.查看当前MySQL支持的存储引擎

show engines \G

3-4.常见的存储引擎

Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO

MyISAM这种存储引擎不支持事务
MySQL最常用的存储引擎，但是这种引擎不是默认的
MyISAM采用三个文件组织一张表：
- xxx.frm（存储格式的文件）
- xxx.MYD（存储表中数据的文件）
- xxx.MYI（存储表中索引的文件）
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率
缺点：不支持事务

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制
InnoDB支持级联删除和级联更新

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的
优点：查询速度最快
以前叫做HEPA引擎

LogicVan

http://LogicVan.github.io/2020/10/18/mysql-si/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 LogicVan !

数据库 MySQL

MySQL(五)

MySQL(第五弹):索引，视图和三范式

2020-10-18 数据库

数据库 MySQL

MySQL(三)

MySQL(第三弹):DQL(3)，DDL和DML

2020-10-18 数据库

数据库 MySQL

1.约束(Constraint)

1-1.定义及分类

1-2.非空约束 not null

1-3.唯一性约束 unique

1-4.主键约束

1-4-1.添加主键约束

1-4-2.主键相关的术语

1-4-3.主键作用

1-4-4.主键的分类

1-4-5.定义主键(使用表级约束方式)

1-5.外键约束

1-5-1.相关术语

1-5-2.业务背景

check约束

查看索引

修改表时添加约束

2.事务(Transaction)

2-1.什么是事务

2-2.事务四大特性ACID

2-3.事务之间的隔离性

2-4.演示事务

2-4-1.准备表

2-4-2.演示一

2-4-3.演示二

2-4-4.演示三

3.存储引擎（了解）

3-1.完整的建表语句

3-2.什么是存储引擎

3-3.查看当前MySQL支持的存储引擎

3-4.常见的存储引擎

你的赏识是我前进的动力