规则网站建设,网络营销做得好的品牌,东莞响应式网站制作,有没有可以在线做化学实验的网站基础篇 MySQL系列专栏(持续更新中 …)1『 MySQL篇 』#xff1a;库操作、数据类型2『 MySQL篇 』#xff1a;MySQL表的CURD操作3『 MySQL篇 』#xff1a;MySQL表的相关约束文章目录 1 . 非空约束 (not null)2 . 唯一性约束(unique)3 . check约束4 . 默认约束(default)5 . 主…基础篇 MySQL系列专栏(持续更新中 …)1『 MySQL篇 』库操作、数据类型2『 MySQL篇 』MySQL表的CURD操作3『 MySQL篇 』MySQL表的相关约束文章目录 1 . 非空约束 (not null)2 . 唯一性约束(unique)3 . check约束4 . 默认约束(default)5 . 主键约束(primary key)6 . 外键约束(foreign key)
)概述 : 约束是作用于表中字段上的限制,用于限制存储在表中的数据 目的: 保证数据库中的数据的正确 , 有效性 和完整性 分类
约束描述关键字非空约束限制该字段的数据不能为 nullNOT NULL唯一性约束保证该字段的所有数据都是唯一. 不重复的UNIQUE主键约束主键是一行数据唯一的表示 , 要求非空且唯一PRIMARY KEY默认约束保存数据时 ,如果未指定该字段的值 , 则采用默认值DEFAULT检查约束(8.0.16版本之后)保证字段值满足某一个条件CHECK外键约束用来让两张表的数据之间建立连接 , 保证数据的一致性和完整性FOREIGN KEY
1 . 非空约束 (not null)
创建表时进行使用 , 指定某一列的值不为空
create table 表名(列名 类型 not null);案例 创建表中包含 字段名为 name 字段类型为 varchar(10) , name 不允许为空值 mysql create table user(name varchar(10) not null,gender char(1));
Query OK, 0 rows affected (0.03 sec)mysql desc user;
------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------
| name | varchar(10) | NO | | NULL | |
| gender | char(1) | YES | | NULL | |
------------------------------------------------
2 rows in set (0.01 sec)
-- 插入姓名为空值时
mysql insert into user values(null,男);
ERROR 1048 (23000): Column name cannot be null
mysql insert into user values (张三,男);
Query OK, 1 row affected (0.00 sec)2 . 唯一性约束(unique)
在创建表时 , 对某一列使用唯一性约束 , 则该列的值不能重复 .
create table 表名(列名 类型 unique);案例 创建表中包含字段名为 name , 字段类型为 varchar(10) , name 不允许为空值 . 且name 为唯一 mysql create table user(name varchar(20)not null unique);
-- name 列不能重复且非空
Query OK, 0 rows affected (0.05 sec)mysql desc user;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| name | varchar(20) | NO | PRI | NULL | |
-----------------------------------------------
1 row in set (0.00 sec)
#第一次直接插入
mysql insert into user values(张三);
Query OK, 1 row affected (0.00 sec)
#当插入重复值时 , 已存在不能重复添加
mysql insert into user values(张三);
ERROR 1062 (23000): Duplicate entry 张三 for key name
mysql如果没有指定非空 , 那么unique 字段可以有多个 null , 一张表中可以有多个unique 字段 如果此时user 列已经创建 , 要将 唯一性约束添加到 name 列 , 则使用
# 修改user表中的约束
alter table user modify age int not null unique;3 . check约束
用于强制行数据必须满足的条件 .
Oracle 和SQL sever均支持check , 但是mysql 5.7 目前还不支持check , 只进行语法检验 ,并不会生效 .
4 . 默认约束(default)
在创建表的时候可以使用 , 可以设置列的默认值
create table 表名(列名 类型 default 默认值..);案例 创建表中包含字段名为sex , 字段类型为char( 1 ) , 约束条件为 默认值为 ‘男’ mysql create table user(id int , gender char(1) default 男);
Query OK, 0 rows affected (0.01 sec)mysql desc user;
--------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------
| id | int(11) | YES | | NULL | |
| gender | char(1) | YES | | 男 | |
--------------------------------------------
2 rows in set (0.00 sec)5 . 主键约束(primary key) 主键与外键之间的区别 ? 主键(主码) 主键用于唯一标识一个元组不能有重复不允许为空。一个表只能有一个主键。 外键(外码) 外键用来和其他表建立联系用外键是另一表的主键外键是可以有重复的可以是空值。一个表可以有多个外键。 primary key 是主键约束 , 表由列和行组成。 通常表具有一列或多列列的值唯一地标识表中的每一行。 此列或多列称为主键。由两列或更多列组成的主键也称为复合主键。
create table 表名(列名 类型 primary key);primary key 相当于not null和 unique结合的效果 .在实际开发当中, 每个表通常都会设置一个主键 , 但允许多个列放到一起共同作为一个主键(联合主键) 案例 创建表中包含字段名为 id , 字段类型为int , 其中 id为主键 ,不为空且唯一 mysql create table user(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)mysql desc user;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.01 sec)
# 当插入两条主键相同的数据时,
mysql insert into user values(12,张),(12,李);
ERROR 1062 (23000): Duplicate entry 12 for key PRIMARY设置自增主键
MySQl 当中支持自增主键 , 在插入数据时 ,未设置初始值的情况下 , 会自动从 1 开始自增
create table 表名(列名 类型 primary key auto_increment, ...);案例
#将user表中的主键id 设置为自增主键
mysql create table user(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.05 sec)mysql desc user;
--------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
--------------------------------------------------------
2 rows in set (0.00 sec)mysql insert into user values(null,李),(null,张),(null,王);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 自增主键设置成功
mysql select * from user;
----------
| id | name |
----------
| 1 | 李 |
| 2 | 张 |
| 3 | 王 |
----------
3 rows in set (0.00 sec)# id列的null并不是将id设置为null, 而是将id的赋值交给数据库来使用自增主键MySQL当中数据量较大的时候 , 就需要使用多个主机进行储存, 本质上就是分表然后每个数据库服务器只储存一部分数据 , 这种情况下式就涉及到了分布式系统中唯一id生成算法 , 利用不同时间下的时间戳和主机编号并结合随机因子 , 就可以得到唯一的id. 6 . 外键约束(foreign key)
外键用来让两张表的数据之间建立连接 , 从而保证数据的一致性和完整性 .
create table 表名(字段名 数据类型,foreign key(本表字段名) renferences 主表名(主键名或unique字段名)
)alter table 添加外键的表名 add constraint 外键名称 foreign key(从表字段名) renferences 主表名(主表字段名)外键约束常用来定义主表和从表的关系 , 外键约束定义在从表上 ,主表上必须具有主键约束或是unique约束 外键约束演示
# 创建主班级表
mysql create table class(id int primary key comment班级编号,name varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)# 创建学生表并建立外键约束
mysql create table student(id int primary key comment 学生编号,name varchar(20) not null,class_id int, -- 学生所在的班级编号foreign key(class_id) references class(id));
Query OK, 0 rows affected (0.01 sec)# 主表副表结构
mysql desc class;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql desc student;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
--------------------------------------------------
3 rows in set (0.00 sec)#班级表中插入数据
mysql INSERT INTO class values(100,java),(200,web);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from class;
-----------
| id | name |
-----------
| 100 | java |
| 200 | web |
-----------#学生表插入数据
mysql insert into student values(1,tom,100);
Query OK, 1 row affected (0.02 sec)mysql insert into student values(2,jack,200);
Query OK, 1 row affected (0.02 sec)#添加失败,300号班级在class表中不存在,由于外键约束的作用,插入失败
mysql insert into student values(3,frx,300);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (mytestdb.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (id))删除更新行为
行为说明当在父表中删除更新对应的记录时NO ACTION检查该记录是否有对应外籍如果有则不允许删除更新RESTRICT检查该记录是否有对应的外键有则不允许删除更新CASCADE(级联)检查该记录是否有对应的外键如果有则也删除更新外键在子表中的记录SET NULL检查该记录是否有对应的外键如果有则设置子表中该外键的值为NULLSET DEFAULT父表有变更时,子表将外键列设置成一个默认的值(innodb不支持)
格式
alter table 子表 add constraint 外键名 foreign key (字段名) references 父表(字段名) on update 行为 on delete 行为;示例 将 student 表中的外键删除更新行为设置为 update set null 和 delete set null #设置 student 表中的外键
mysql alter table student add constraint class_id foreign key(class_id) references class(id) on update set null on delete set null;Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0#更新父表中的 id
mysql update class set id 300 where name web;Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0#查询子表中的全部信息
mysql select * from student;
--------------------
| id | name | class_id |
--------------------
| 1 | tom | 100 |
| 2 | jack | NULL |
--------------------
2 rows in set (0.00 sec)删除外键约束
定义的任何约束可以使用带有DROP CONSTRAINT选项的ALTER TABLE命令删除 ,
ALTER TABLE table_name DROP CONSTRAINT fk_name;如果使用的是MySQL则可以使用更清晰的语法如下所示
ALTER TABLE table_name DROP FOREIGN KEY fk_name;示例:
-#删除外键约束
mysql alter table student drop foreign key class_id;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0#删除外键约束后,主表与从表再无关联,成功插入
mysql insert into student values(100,张,550);
Query OK, 1 row affected (0.00 se实际开发中 , 为什么不推荐使用外键 与级联? 一方面 , 外键的使用保证了数据库中数据的一致性和完整性 , 并且级联操作方便,减少了程序的代码量 另一方面 , 每次进行 update 和 delete 都需要考虑外键约束 , 导致测试数据极不方便 , 主从关系是确定的,如果哪天需求发生变化 , 会增加很多麻烦 . 所以 , 外键与级联更新适用于单机低并发不适合分布式、高并发集群; 级联更新是强阻塞存在数据库更新风暴的风 险; 外键影响数据库的插入速度 , 并发量不是很高的情况下可以使用外键
