1.为什么需要表的约束
定义字段的时候,需要有一个类型,这样一个类型,有时候并不能满足我们对一个表的约束
比如:
表字段是否可以为NULL,有没有默认值,表字段的解释能不能加上
对于数字类型的字段可不可以指定默认表示的位数
可不可以将这个字段设置称为唯一标识该行的数据
这些都不是数据的类型能够约束的
1.空属性
NULL:表示当前字段可以为空
NOT NULL:表示当前字段不可以为空,在插入数据的时候,如果不插入该列数据则会报错,如果插入的时候,指定了值,则采用指定的值
//创建一个表,两个属性:名字、学号,其中指定学号不能为空
MariaDB [test4]> create table t1(name varchar(10),id int NOT NULL);
//查看表字段,可以看到id列显示不能为空
MariaDB [test4]> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| id | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
//单独插入id,成功
MariaDB [test4]> insert into t1(id) values(123);
//单独插入姓名,报错,这是因为id的属性为不能为空
MariaDB [test4]> insert into t1(name) values('张三');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
MariaDB [test4]> select * from t1;
+------+-----+
| name | id |
+------+-----+
| NULL | 123 |
+------+-----+
2.默认值
default:可以在创建表字段的时候,指定一个默认值,当我们插入数据的时候,没有插入该字段,就采用默认属性
//创建一个表,表两个字段:名字 和性别,性别默认值设置为女
MariaDB [test4]> create table t2(name char(10),sex char(10) default '女');
//往表中名字列添加名字 -> 小红
MariaDB [test4]> insert into t2 (name) value('小红');
//可以看到,sex列并没有添加属性,但是采用了默认属性 -> 女
MariaDB [test4]> select *from t2;
+--------+------+
| name | sex |
+--------+------+
| 小红 | 女 |
+--------+------+
3.列描述
comment:相当于列的备注,主要是给查看表的用户看的,该字段是什么含义,对于存储数据而言,并没有实质的约束,只是供操作者来查看该列的含义
MariaDB [test4]>
create table t3(name varchar(10) not null comment '这一列表示姓名,并且不能为空',id int comment'这一列表示学号');
//查看表的创建过程
MariaDB [test4]> show create table t3;
4.zerofill
约束了数字的宽度,如果宽度不够,则用0进行填充 -> eg: 5 -> 宽度为5 -> 表示为 00005
如果指定了zerofill,则默认会加上无符号属性(unsigned),在数据库中存储的还是原来的值,只不过在查询的时候,会按照宽度进行输出,宽度不够则用0填充 -> 相当于格式化输出
如果没有zerofill则类型后面的数字没有任何意义
//创建表,并且约束列 id1,zerofill
MariaDB [test4]> create table t4(id1 int(5) zerofill ,id2 int(5));
//插入数据
MariaDB [test4]> insert into t4 values(5,5);*
//查看表字段,可以看到id1后面有zerofill
MariaDB [test4]> desc t4;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id1 | int(5) unsigned zerofill | YES | | NULL | |
| id2 | int(5) | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
//可以看到id1,按照5的字符来进行输出,id2没有指定zerofill,则类型后面的数字没有任何含义
MariaDB [test4]> select *from t4;
+-------+------+
| id1 | id2 |
+-------+------+
| 00005 | 5 |
+-------+------+
5.主键
1.对于主键而言,并不是重新开辟了一个新的列,而是表中的一列被设置的属性,这种属性称之为主键;
2.主键的属性保证了该列的数据不能为空,不能重复
3.一个表中只能有一个主键
4.通常情况下,主键在设置的时候,都是整形类型,主键这一列数据可以唯一标识一行的数据
5.如何设置主键
a.在创建表的时候,直接在字段的后面加上关键字 “primary key” ,就可以设置该字段为主键
//创建表,并且将id字段设置为主键
MariaDB [test4]> create table t5(name char(10),id int primary key);
//查看表字段 primary key 缩写 -> PRI
MariaDB [test4]> desc t5;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
//查看创建过程,可以看到id被设置为NOT NULL
MariaDB [test4]> show create table t5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`name` char(10) DEFAULT NULL,
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
//插入数据
MariaDB [test4]> insert into t5 values('小明','123');
//插入相同的ID,报错,不允许插入相同的ID
MariaDB [test4]> insert into t5 values('小红','123');
ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'
b.在创建表的时候,将所有的字段指定完毕后,在所有的字段后面加上关键字,并且加上指定段的名称
//在所有字段的后面加上关键字
MariaDB [test4]> create table t6(name char(10),id int ,primary key(id));
MariaDB [test4]> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| id | int(11) | NO | PRI | 0 | |
+-------+----------+------+-----+---------+-------+
c.在创建表的时候可以不指定,创建完成之后,使用alter关键字来增加主键
//创建表
MariaDB [test4]> create table t7(name char(10),id int);
//添加主键
MariaDB [test4]> alter table t7 add primary key(id);
MariaDB [test4]> desc t7;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| id | int(11) | NO | PRI | 0 | |
+-------+----------+------+-----+---------+-------+
6.如何删除主键
使用alter关键字+drop可以删除
//删除主键
MariaDB [test4]> alter table t7 drop primary key;
//可以看到主键PRI被删除,但是还是不能为空,只不过给了默认值0
MariaDB [test4]> desc t7;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| id | int(11) | NO | | 0 | |
+-------+----------+------+-----+---------+-------+
MariaDB [test4]> show create table t7;
+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| t7 | CREATE TABLE `t7` (
`name` char(10) DEFAULT NULL,
`id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
7.主键索引
通过主键构建一颗B+树,在用户进行检索的时候,如果约束条件(查找条件)为主键,则会大大的提高搜索效率,因为B+树的搜索效率高,可以找到主键对应的一行数据
6.自增长
1.当我们设置一个字段为自增长的时候,如果插入数据的时候,不给该列数据,则系统会自动填写一个比上一行数值+1的数值进去
2.通常自增长搭配主键使用,主键不可以重复,每次自增长,会给新数据分配上一行数值+1
3.条件:
a.首先字段必须是一个整数,字段类型需要是一个整数类型
b.一张表当中最多只能有一个自增长的列
c.搭配主键来使用
4.使用auto_increment关键字
//创建表,并且将id设置为主键在自增长
MariaDB [test4]> create table t1 (name char(10),id int primary key auto_increment);
//插入名字,id虽然是主键,不能为空,但是由于自增长的存在,所以会自动分配
MariaDB [test4]> insert into t1(name) values('小虫');
MariaDB [test4]> insert into t1(name) values('小龙');
//展示
MariaDB [test4]> select * from t1;
+--------+----+
| name | id |
+--------+----+
| 小虫 | 1 |
| 小龙 | 2 |
+--------+----+
5.last_insert_id()
获取上次增加的id的值,只能拿到系统自己增加的值
7.唯一建
1.关键字unique,也是给某个字段设置一个属性,该属性约束字段内容不能重复,但是可以为空
2.主键比唯一键多了一个约束,即字段不能为NULL。但是一张表中可以有多个唯一建
//设置两个字段:name、id -> id设置约束唯一建
MariaDB [test1]> create table t1(name char(10),id int unique);
//插入内容
MariaDB [test1]> insert into t1 values('小西',123);
//插入相同的id不被允许
MariaDB [test1]> insert into t1 values('小东',123);
ERROR 1062 (23000): Duplicate entry '123' for key 'id'
//可以为空
MariaDB [test1]> insert into t1 (name) values('小东');
//id被设置为UNI
MariaDB [test1]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| id | int(11) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
MariaDB [test1]> select *from t1;
+--------+------+
| name | id |
+--------+------+
| 小西 | 123 |
| 小东 | NULL |
+--------+------+
//设置多个唯一建
MariaDB [test1]> alter table t1 add unique (name);
MariaDB [test1]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | UNI | NULL | |
| id | int(11) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
8.外键
用于定义主表和从表的一个约束关系
1.主表:存放基础属性的表,一般用来描述当前表中的基础属性
2.从表:存放具体的用户生成的信息
3.定义主表当中可能成为其它表外键的字段,一定需要将该字段设置成为主键或者唯一建,因为主键不能重复,不能为空,唯一标识一行数据
4.定义从表的时候,设置外键其实就是设置了一个关系,当前从表的字段和主表当中字段的关系,在从表当中插入数据的时候,会对插入数据进行校验,校验插入的数据是否存在于主表字段当中,外键就是增加了表和表之间的约束关系
5.格式:foreign key [从表的字段名称] references[主表的字段名称]
eg:
外表中基础信息为班级,并且用班级作为约束
从表信息为学生的名字和班级,如果学生信息中填了不存在的班级就无法插入
//设置主表,其中列为班级信息,并且是主键
MariaDB [test4]> create table t2(class_id int primary key );
//插入数据
MariaDB [test4]> insert into t2 values(100);
MariaDB [test4]> insert into t2 values(102);
MariaDB [test4]> select * from t2;
+----------+
| class_id |
+----------+
| 100 |
| 102 |
+----------+
//设置从表,约束关系为id
MariaDB [test4]> create table t3(name char(10),id int,foreign key (id) references t2(class_id));
//插入成功
MariaDB [test4]> insert into t3 values('小囧','100');
Query OK, 1 row affected (0.00 sec)
//111不在主表之中,因此不能插入
MariaDB [test4]> insert into t3 values('大囧','111');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
MariaDB [test4]> select * from t3;
+--------+------+
| name | id |
+--------+------+
| 小囧 | 100 |
+--------+------+