一, 介绍
约束条件与数据类型的宽度一样, 都是可选参数
作用: 用于保证数据的完整性和一致性
主要分为:
- PRIMARY KEY (PK) #标识该字段为该表的主角按, 可以唯一的表示记录
- POREIGN KEY (FK) #标识该字段为该表的外键
- NOT NULL #表示该字段不能为空
- UNIQUE KEY (UK) #标识该字段的值是唯一的
- AUTO_INCREMENT #标识该字段的值自动增长 (整数类型, 而且为主键)
- DEFAULT #该字段设置默认值
- UNSIGNED #无符号
- ZEROFILL #使用 0 填充
说明:
- #1. 是否允许为空, 默认 NULL, 可设置 NOT NULL, 字段不允许为空, 必赋值
- #2. 字段是否有默认值, 缺省的默认值是 NULL, 如果想插入记录时不给字段赋值, 此字段使用默认值
- sex enum('male','female') not null default 'male'
- # 必须为正值 (无符号) 不允许为空 默认时 20
- age int unsigned NOT NULL default 20
- #30 是否时 key
主键 primary key
外键 foreign key
索引 (index,unique...)
二, not null 与 default
是否可空, null 标识空, 非字符串
not null - 不可空
null - 可空
默认值, 创建列时可以指定默认值, 当插入数据时, 如果未主动设置, 则自动添加默认值
- create table tb1(
- nid int not null default 2,
- num int not null
- );
验证:
- MySQL> create table t11(id int);# id 字段默认可以为空
- Query OK, 0 rows affected (0.05 sec)
- MySQL> desc t11;
- +-------+---------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+---------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- +-------+---------+------+-----+---------+-------+
- 1 row in set (0.03 sec)
- MySQL> insert into t11 values(); #给 t11 表插一个空的值
- Query OK, 1 row affected (0.00 sec)
- # 查询结果如下
- MySQL> select * from t11;
- +------+
- | id |
- +------+
- | NULL |
- +------+
- 1 row in set (0.00 sec)
验证 2:
- MySQL> create table t12(id int not null);# 设置字段 id 不为空
- Query OK, 0 rows affected (0.03 sec)
- MySQL> desc t12;
- +-------+---------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+---------+------+-----+---------+-------+
- | id | int(11) | NO | | NULL | |
- +-------+---------+------+-----+---------+-------+
- 1 row in set (0.01 sec)
- MySQL> insert into t12 values();# 不能插入空
- ERROR 1364 (HY000): Field 'id' doesn't have a default value
验证 3:
- MySQL> create table student2(
- -> id int not null,
- -> name varchar(50) not null,
- -> age int(3) unsigned not null default 18,
- -> sex enum('male','female') default 'male',
- -> fav set('smoke','drink','tangtou') default 'drink,tangtou'
- -> );
- Query OK, 0 rows affected (0.01 sec)
- # 只插入了 not null 约束条件的字段对应的值
- MySQL> insert into student2(id,name) values(1,'mjj');
- Query OK, 1 row affected (0.00 sec)
- # 查询结果如下
- MySQL> select * from student2;
- +----+------+-----+------+---------------+
- | id | name | age | sex | fav |
- +----+------+-----+------+---------------+
- | 1 | mjj | 18 | male | drink,tangtou |
- +----+------+-----+------+---------------+
- 1 row in set (0.00 sec)
- 3.unique
中文翻译: 不同的. 在 MySQL 中成为单列唯一
举例说明: 创建公司部门表 (每个公司都有唯一的部门).
- MySQL> create table department(
- -> id int,
- -> name char(10)
- -> );
- Query OK, 0 rows affected (0.01 sec)
- MySQL> insert into department values(1,'IT'),(2,'IT');
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- MySQL> select * from department;
- +------+------+
- | id | name |
- +------+------+
- | 1 | IT |
- | 2 | IT |
- +------+------+
- 2 rows in set (0.00 sec)
- # 发现: 同时插入两个 IT 部门也是可以的, 但这是不合理的, 所以我们要设置 name 字段为 unique 解决这种不合理的现象.
接下来, 使用约束条件 unique, 来对公司部门的字段进行设置.
- # 第一种创建 unique 的方式
- # 例子 1:
- create table department(
- id int,
- name char(10) unique
- );
- MySQL> insert into department values(1,'it'),(2,'it');
- ERROR 1062 (23000): Duplicate entry 'it' for key 'name'
- # 例子 2:
- create table department(
- id int unique,
- name char(10) unique
- );
- insert into department values(1,'it'),(2,'sale');
- # 第二种创建 unique 的方式
- create table department(
- id int,
- name char(10) ,
- unique(id),
- unique(name)
- );
- insert into department values(1,'it'),(2,'sale');
联合唯一:
- # 创建 services 表
- MySQL> create table services(
- -> id int,
- -> ip char(15),
- -> port int,
- -> unique(id),
- -> unique(ip,port)
- -> );
- Query OK, 0 rows affected (0.05 sec)
- MySQL> desc services;
- +-------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+----------+------+-----+---------+-------+
- | id | int(11) | YES | UNI | NULL | |
- | ip | char(15) | YES | MUL | NULL | |
- | port | int(11) | YES | | NULL | |
- +-------+----------+------+-----+---------+-------+
- 3 rows in set (0.01 sec)
- # 联合唯一, 只要两列记录, 有一列不同, 既符合联合唯一的约束
- MySQL> insert into services values
- -> (1,'192,168,11,23',80),
- -> (2,'192,168,11,23',81),
- -> (3,'192,168,11,25',80);
- Query OK, 3 rows affected (0.01 sec)
- Records: 3 Duplicates: 0 Warnings: 0
- MySQL> select * from services;
- +------+---------------+------+
- | id | ip | port |
- +------+---------------+------+
- | 1 | 192,168,11,23 | 80 |
- | 2 | 192,168,11,23 | 81 |
- | 3 | 192,168,11,25 | 80 |
- +------+---------------+------+
- 3 rows in set (0.00 sec)
- MySQL> insert into services values (4,'192,168,11,23',80);
- ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'
- 4.primary key
一个表可以:
单列做主键
多列做主键 (复合主键)
约束: 等价于 not null unique, 字段的值不能为空, 且唯一
存储引擎默认时 (innodb): 对于 innodb 存储引擎来说, 一张表必须有一个主键
单列主键
- # 创建 t14 表, 为 id 字段设置主键, 唯一的不同的记录
- create table t14(
- id int primary key,
- name char(16)
- );
- insert into t14 values
- (1,'xiaoma'),
- (2,'xiaohong');
- MySQL> insert into t14 values(2,'wxxx');
- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
- # not null + unique 的化学反应, 相当于给 id 设置 primary key
- create table t15(
- id int not null unique,
- name char(16)
- );
- MySQL> create table t15(
- -> id int not null unique,
- -> name char(16)
- -> );
- Query OK, 0 rows affected (0.01 sec)
- MySQL> desc t15;
- +-------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+----------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | char(16) | YES | | NULL | |
- +-------+----------+------+-----+---------+-------+
- 2 rows in set (0.02 sec)
复合主键:
- create table t16(
- ip char(15),
- port int,
- primary key(ip,port)
- );
- insert into t16 values
- ('1.1.1.2',80),
- ('1.1.1.2',81);
- 5.auto_increment
约束: 约束的字段为自动增长, 约束的字段必须同时被 key 约束
来源: http://www.bubuko.com/infodetail-2809221.html