一、字段约束
字段约束的作用是什么？
设置在表头上 ，用来限制字段赋值
字段都有哪些约束？（每一种约束都有各自的功能）
字段约束分类：
1、 PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
2、NOT NULL ：非空，用于保证该字段的值不能为空。
3、DEFAULT：默认值，用于保证该字段有默认值。
4、UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
5、 FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
查看表的字段约束条件使用的命令
mysql>desc 库.表； 比如 desc db1.t3;
mysql>desc db1.t3;
字段名 数据类型 |-------->约束条件<------------|
空 键值 默认值 额外设置
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name | char(50) | YES | | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(200) | YES | | NULL | |
| homedir | varchar(60) | YES | | NULL | |
| shell | varchar(30) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql>
字段约束的使用？
建表时给表头设置默认和不允许赋null值
mysql> desc db1.t3 ;
ERROR 1146 (42S02): Table 'db1.t3' doesn't exist
mysql> desc db1.t3 ;
ERROR 1146 (42S02): Table 'db1.t3' doesn't exist
mysql> desc db1.t31 ;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name | char(10) | NO | | NULL | |
| class | char(7) | YES | | qq | |
| likes | set('money','game','film','music') | NO | | film,music | |
+-------+------------------------------------+------+-----+------------+-------+
3 rows in set (0.00 sec)
验证默认值和不允许为null
表头name赋null值 报错
mysql> insert into db1.t31 values (null, null , null);
ERROR 1048 (23000): Column 'name' cannot be null
表头likes赋null值 报错
mysql> insert into db1.t31 values ("bob", null , null);
ERROR 1048 (23000): Column 'likes' cannot be null
符合约束不报错
mysql> insert into db1.t31 values ("bob",null,"money,game,film");
Query OK, 1 row affected (0.06 sec)
不赋值的表头使用默认值赋值
mysql> insert into db1.t31(name) values("jim");
根据需要自定义表头的值
mysql> insert into db1.t31 values ("lucy","ns","game,film");
mysql> select * from db1.t31;
------+---------+-----------------+
| name | class | likes |
+------+---------+-----------------+
| bob | NULL | money,game,film |
| jim | ns | film,music |
| lucy | ns | game,film |
+------+---------+-----------------+
3 rows in set (0.01 sec)
唯一索引 （unique）
约束的方式：表头值唯一 , 但可以赋null 值
例子
mysql> create database if not exists DB1;
create table DB1.t43 (姓名 char(10) , 护照 char(18) unique );
mysql> desc DB1.t43 ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 护照 | char(18) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
赋null值 可以
mysql> insert into DB1.t43 values("bob",null);
mysql> insert into DB1.t43 values("tom","666888");
表头值重复不可以
mysql> insert into DB1.t43 values("jim","666888");
ERROR 1062 (23000): Duplicate entry '666888' for key 'hz_id'
不重复 可以
mysql> insert into DB1.t43 values("jim","766888");
查看表记录
mysql> select * from DB1.t43;
+------+--------+
| 姓名 | 护照 |
+------+--------+
| bob | NULL |
| tom | 666888 |
| jim | 766888 |
+------+--------+
3 rows in set (0.00 sec)
mysql>
主键 (primary key)
约束方式：表头值不允许重复，且不允许赋NULL值
使用说明：
1、表中只能有一个主键表头
2、多个表头做主键，称为复合主键，必须一起创建
3、主键标志PRI
4、主键通常与auto_increment 连用
5、通常把表中唯一标识记录的表头设置为主键[记录编号表头]
建表时，创建主键 （2种语法格式 要记住）
语法格式1
create table 库.表（ 表头名 数据类型 primary key , 表头名 数据类型 , ..... ）;
例子
mysql> create table db1.t35(
name char(10) ,
hz_id char(10) primary key ,
class char(10)
);
查看表头
mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | | 姓名
| hz_id | char(10) | NO | PRI | NULL | | 身份证号
| class | char(10) | YES | | NULL | | 班级
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
语法格式2
create table 库.表（ 字段名 类型 , 字段名 类型 , primary key（字段名） ）;
例子
mysql> create table db1.t36(
name char(10) ,
hz_id char(10) ,
class char(10),
primary key(hz_id)
);
查看表头
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| hz_id | char(10) | NO | PRI | NULL | |
| class | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除主键
命令格式
mysql> alter table 库.表 drop primary key ;
例子
mysql> alter table db1.t36 drop primary key ;
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| hz_id | char(10) | NO | | NULL | |
| class | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
添加主键
命令格式
mysql> alter table 库.表 add primary key(表头名);
例子
mysql> alter table db1.t36 add primary key(hz_id);
mysql> desc db1.t36;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| hz_id | char(10) | NO | PRI | NULL | |
| class | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
使用t35表 验证主键约束
查看主键表头
mysql> desc db1.t35;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| hz_id | char(10) | NO | PRI | NULL | |
| class | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
插入第1条记录
mysql> insert into db1.t35 values ("bob","888","ns");
Query OK, 1 row affected (0.05 sec)
空不可以
mysql> insert into db1.t35 values ("john",null,"ns");
ERROR 1048 (23000): Column 'hz_id' cannot be null
mysql>
与第1条重复不可以
mysql> insert into db1.t35 values ("john","888","ns");
ERROR 1062 (23000): Duplicate entry '888' for key 'PRIMARY'
不重复也不是null可以
mysql> insert into db1.t35 values ("john","988","ns");
Query OK, 1 row affected (0.07 sec)
查看表记录
mysql> select * from db1.t35;
主键与auto_increment连用:
表头设置了auto_increment属性后，
插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值
要想让表头有自增长 表头必须有主键设置才可以
查看表结构时 在 Extra （额外设置） 位置显示
建表时 创建有auto_increment 属性的表头。实现的效果如下：
行号 姓名 班级 住址
1 bob ns bj
2 bob ns bj
3 bob ns bj
4 bob ns bj
例子
create table db1.t38 (
行号 int primary key auto_increment ,
姓名 char(10) ,
班级 char(7) ,
住址 char(10)
);
或
create table db1.t39 (
行号 int auto_increment ,
姓名 char(10) , 班级 char(7) , 住址 char(10) ,
primary key(行号)
);
查看表头
mysql> desc db1.t38;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| 行号 | int(11) | NO | PRI | NULL | auto_increment |
| 姓名 | char(10) | YES | | NULL | |
| 班级 | char(7) | YES | | NULL | |
| 住址 | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
mysql> desc db1.t39;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| 行号 | int(11) | NO | PRI | NULL | auto_increment |
| 姓名 | char(10) | YES | | NULL | |
| 班级 | char(7) | YES | | NULL | |
| 住址 | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
插入表记录 不给自增长表头赋值
mysql> insert into db1.t39(姓名,班级,住址)values("bob","ns","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("bob","ns","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("tom","ns","bj");
查看表记录
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 1 | bob | ns | bj |
| 2 | bob | ns | bj |
| 3 | bob | ns | bj |
自增长使用注意事项
给自增长字段的赋值
mysql> insert into db1.t39(行号,姓名,班级,住址)values(5,"lucy","ns","bj");
Query OK, 1 row affected (0.26 sec)
不赋值后 用最后1条件记录表头的值+1结果赋值
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","ns","bj");
Query OK, 1 row affected (0.03 sec)
删除所有行
mysql> delete from db1.t39 ；
再添加行 继续行号 而不是从 1 开始
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","ns","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","ns","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","ns","bj");
查看记录
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 8 | lucy | ns | bj |
| 9 | lucy | ns | bj |
| 10 | lucy | ns | bj |
+--------+--------+---------+--------+
3 rows in set (0.01 sec)
truncate删除行 再添加行 从1开始
mysql> truncate table db1.t39;
Query OK, 0 rows affected (2.66 sec)
插入记录
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","ns ","bj");
Query OK, 1 row affected (0.04 sec)
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","ns ","bj");
Query OK, 1 row affected (0.30 sec)
查看记录
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 1 | lucy | ns | bj |
| 2 | lucy | ns | bj |
+--------+--------+---------+--------+
2 rows in set (0.01 sec)
mysql>
给已有表添加行号字段（通常把表中唯一标识记录的字段作为主键 就是行号字段）
没加行号前查看
mysql> select * from db1.t3;
alter table deb1.t3 dorp 行号
添加自增长表头id
mysql> alter table db1.t3
add
id int primary key auto_increment first;
添加后查看
mysql> select * from db1.t3;
查找用户使用行号做查询条件 可以快速查找到指定的行
mysql> select * from db1.t3 where id <= 3;
复合主键
表中的多个表头一起做主键
复合主键的约束方式: 多条记录 主键的值不允许同时相同
例子
创建复合主键
create table db1.t39(
cip varchar(15) ,
port smallint ,
status enum("deny","allow") ,
primary key(cip,port)
);
插入记录验证
insert into db1.t39 values ("1.1.1.1",22,"deny");
insert into db1.t39 values ("1.1.1.1",22,"deny"); 同时相同报错
insert into db1.t39 values ("1.1.1.1",80,"deny"); 可以了
删除复合主键
mysql> alter table db1.t39 drop primary key;
查看表头
mysql> desc db1.t39;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip | varchar(15) | NO | | NULL | |
| port | smallint(6) | NO | | NULL | |
| status | enum('deny','allow') | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
没有复合主键约束后 ，插入记录不受限制了
mysql> insert into db1.t39 values("2.1.1.1",80,"allow");
mysql> insert into db1.t39 values("2.1.1.1",80,"deny");
查看表记录
mysql> select * from db1.t39;
+---------+------+--------+
| cip | port | status |
+---------+------+--------+
| 1.1.1.1 | 22 | allow |
| 1.1.1.1 | 80 | deny |
| 2.1.1.1 | 80 | allow |
| 2.1.1.1 | 80 | allow |
| 2.1.1.1 | 80 | deny |
+---------+------+--------+
5 rows in set (0.00 sec)
添加复合主键时 字段下的数据与主键约束冲突 不允许添加
mysql> alter table db1.t39 add primary key(cip,port);
ERROR 1062 (23000): Duplicate entry '2.1.1.1-80' for key 'PRIMARY'
删除重复的数据
mysql> delete from db1.t39 where cip="2.1.1.1";
mysql> select * from db1.t39;
+---------+------+--------+
| cip | port | status |
+---------+------+--------+
| 1.1.1.1 | 22 | allow |
| 1.1.1.1 | 80 | deny |
+---------+------+--------+
2 rows in set (0.00 sec)
再次添加复合主键
mysql> alter table db1.t39 add primary key(cip,port);
查看表头
mysql> desc db1.t39;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip | varchar(15) | NO | PRI | NULL | |
| port | smallint(6) | NO | PRI | NULL | |
| status | enum('deny','allow') | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
