申明:资料来源于网络及书本,通过理解、实践、整理成学习笔记。
数据库的约束
主键约束:(primary key) 默认不能重复不能为空。
- 在创建表的时候添加主键,通常情况都是在这个时候添加主键:
create table 表名(字段名1 数据类型 primary key auto_increment,字段名2 数据类型);mysql> create table userinfo_1( ->id int primary key auto_increment, ->name varchar(10) ->); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_1; 执行结果: #id的Null为NO表示数据不能为空 #id的Key为PRI表示id为主键 #id和name的Default为NULL表示id的默认值为空 #id的Extra为auto_increment表示该主键有自增属性 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
- 给现有表格添加主键,通过modify修改字段为主键:
alter table 表名 modify 字段名 数据类型 primary key auto_increment#先创建一个没有主键的表 mysql> create table userinfo_2( -> id int ,name varchar(10) -> ); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_2; 执行结果: +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) #给现有表格添加主键,通过modify修改字段为主键 mysql> alter table userinfo_2 modify id int primary key; 执行结果: Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_2; 执行结果: +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
- 创建联合主键:
create table 表名(字段名1 数据类型,字段名2 数据类型,primary key auto_increment(字段名1,字段名2))mysql> create table userinfo_3( ->id int,name varchar(10), ->primary key auto_increment(id,name) ->); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_3; 执行结果: #id和name的Null为NO表示数据不能为空 #id和name的Key为PRI表示id为主键 #id和name的Default为NULL表示id的默认值为空 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | NO | PRI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
- 删除主键:
如果有auto_increment,先删除之:alter table 表名 change 字段名 字段名 数据类型
如果没有,直接删除:alter table 表名 drop primary keymysql> describe userinfo_1; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> alter table userinfo_1 change id id int ; 执行结果: Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_1; 执行结果: +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table userinfo_1 drop primary key; 执行结果: Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_1; 执行结果: +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
唯一约束:(unique)不能重复可为空。
- 创建表格添加唯一约束:
create table 表名(字段1 数据类型 primary key auto_increment,字段名2 数据类型 unique)mysql> create table userinfo_4( -> id int primary key auto_increment, -> name varchar(10) unique); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_4; 执行结果: #name的Key为UNI,表示唯一约束 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 在现有表格中添加唯一约束:
alter table 表名 modify 字段名 数据类型 uniquemysql> create table userinfo_5( -> id int primary key auto_increment, -> name varchar(10)); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_5; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> alter table userinfo_5 modify name varchar(10) unique; 执行结果: Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_5; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 删除唯一约束:
alter table 表名 drop index 字段名mysql> describe userinfo_5; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> alter table userinfo_5 drop index name; 执行结果: Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_5; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
非空约束:(not null)可重复不能为空。
- 创建表格添加非空约束:
create table 表名(字段名1 数据类型 primary key auto_increment,字段名2 数据类型 not null)mysql> create table userinfo_6( -> id int primary key auto_increment, -> name varchar(10) not null); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_6; 执行结果: #name的Null为NO表示非空约束 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 在现有表格中添加非空约束:
alter table 表名 modify 字段名 数据类型 not nullmysql> create table userinfo_7( -> id int primary key auto_increment, -> name varchar(10)); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_7; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> alter table userinfo_7 modify name varchar(10) not null; 执行结果: Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_7; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 与unique配合,达到非空唯一效果:
alter table 表名 modify 字段名 数据类型 not null unique
默认值约束:(default)不插入值时会有自己的默认值。
- 创建表格时添加默认值约束:
create table 表名(字段名1 数据类型 primary key auto_increment,字段名2 数据类型 default 值)mysql> create table userinfo_8( -> id int primary key auto_increment, -> name varchar(10) default "路飞"); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_8; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | 路飞 | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 在现有表格中添加默认值约束:
alter table 表名 modify 字段名 数据类型 default 值mysql> create table userinfo_9( -> id int primary key auto_increment, -> name varchar(10)); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_9; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> alter table userinfo_9 modify name varchar(10) default "索隆"; 执行结果: Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe userinfo_9; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | 索隆 | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
外键约束:(foreign key)
外键约束涉及多表,主表中被非空、唯一修饰的字段,可以被从表引入为外键,被外键约束限制的字段,只能插入主表中已有的值,并且数据类型要和主表该字段保持一致。
前提:需要数据库支持INNODB引擎,否则无效
- 创建主表:userinfo_10为主表,
create table 表名(字段名1 数据类型 primary key auto_increment,字段名2 数据类型 not null unique)mysql> create table userinfo_10( -> id int primary key auto_increment, -> name varchar(10) not null unique); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_10; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | UNI | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 创建从表:userinfo_11为从表
create table 表名(字段名1 数据类型 primary key auto_increment,字段名2 数据类型, foreign key(字段2) references 主表名(主表字段名2))mysql> create table userinfo_11( -> id int primary key auto_increment, -> name varchar(10), -> foreign key(name) references userinfo_10(name)); 执行结果: Query OK, 0 rows affected (0.02 sec) mysql> describe userinfo_11; 执行结果: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 查找从表的约束名:userinfo_11_ibfk_1为本从表的约束名
show create table 从表名mysql> show create table userinfo_11; 执行结果: userinfo_11 | CREATE TABLE `userinfo_11` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`), CONSTRAINT `userinfo_11_ibfk_1` FOREIGN KEY (`name`) REFERENCES `userinfo_10` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 删除外键:
alter table 表名 drop foreign key 从表约束名mysql> alter table userinfo_11 drop foreign key userinfo_11_ibfk_1; 执行结果: Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
一个坚持学习,坚持成长,坚持分享的人,即使再不聪明,也一定会成为优秀的人!
整理不易,如果看完觉得有所收获的话,记得一键三连哦,谢谢!