目录
表结构
mysql> create table t1(id int,name char(6),sex enum('male','female'));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(6) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
约束条件与数据类型的宽度一样,都是可选参数
作用:用于保证数据的完整性和一致性
主要分为:
- PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
- FOREIGN 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'
age int unsigned NOT NULL default 20 必须为正值(无符号) 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
〔1〕null and deault (空和默认值)
create table t1(id int not null)
create table t1(id int not null default 0)
not null :插入内容时不能为空,否则为报错
default :如何插入时为空默认值为0
〔2〕unique (设置唯一约束)
单列唯一
方式一
mysql> create table t3(id int,name char(6)unique);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(6) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t3 values(1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(2,'one');
ERROR 1062 (23000): Duplicate entry 'one' for key 'name'
方式二
mysql> create table t3(id int,name char(6),unique(name));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(6) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
创建的时候name字段加了unique(唯一性)后,下面在第二次插入相同内容时就报错了。
联合唯一:
mysql> create table t4(id int,name char(6),unique(id,name));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t4 values(1,'one');
Query OK, 1 row affected (0.00 sec)
插入成功
mysql> insert into t4 values(1,'one');
ERROR 1062 (23000): Duplicate entry '1-one' for key 'id'
插入失败,联合唯一,id,name 同时和表内的id,name相同
mysql> insert into t4 values(1,'two');
Query OK, 1 row affected (0.00 sec)
插入成功,因为是联合唯一,单列id相同没事
〔3〕primary key (主键)
mysql> create table t5(id int primary key);
Query OK, 0 rows affected (0.08 sec)
mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t5 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t5 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)
从约束角度来看:只是单纯的一个不能为空并且唯一的字段
默认引擎:intodb 他是需要一个主键的,如果没有设置他会自己去寻找一个字段唯一的,如果没有他会使用隐藏字段就没有意义了。
也是可以设置联合的
〔4〕auto_increment 自动递增
mysql> create table t6(id int auto_increment,name char(6));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key
# 自动递增的字段必须是唯一的,加个nuique或者primary都可以
mysql> create table t6(id int primary key auto_increment,name char(6));
Query OK, 0 rows affected (0.10 sec)
mysql> desc t6;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(6) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into t6(name) values('one'),('two');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
#一次性指定name插入了两个内容,效果如下:id自动递增。
mysql> select * from t6;
+----+------+
| id | name |
+----+------+
| 1 | one |
| 2 | two |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into t6 values(5,'five');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6(name) values('six');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t6;
+----+------+
| id | name |
+----+------+
| 1 | one |
| 2 | two |
| 5 | five |
| 6 | six |
+----+------+
4 rows in set (0.00 sec)
# 如果指定了id传值,下次递增起点就是你传值的那个位置。
mysql> truncate t6;
Query OK, 0 rows affected (0.02 sec)
# 清空的时候不要用delete,只会清空内容,不会充值auto_increment的初始值。
使用truncate会全部清空并且重置
〔4〕foreign key 表之间的关联
创建被关联表(必须先创建)
mysql> create table f1(id int primary key,name char(6));
Query OK, 0 rows affected (0.16 sec)
# 被绑定的字段必须唯一性,unique或者primary key 都可以
mysql> insert into f1 values(1,'IT'),(2,'sale');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
#插入内容,效果如下:
mysql> select * from f1;
+----+------+
| id | name |
+----+------+
| 1 | IT |
| 2 | sale |
+----+------+
2 rows in set (0.00 sec)
创建关联表
mysql> create table f2(id int,name char(6),dep_id int,foreign key(dep_id) references f1(id) on delect cascade on update cascade);
Query OK, 0 rows affected (0.03 sec)
# 绑定格式 删除同步 更新同步
mysql> insert into f2 values(1,'one',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into f2 values(2,'two',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into f2 values(3,'three',2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from f2;
+------+-------+--------+
| id | name | dep_id |
+------+-------+--------+
| 1 | one | 1 |
| 2 | two | 1 |
| 3 | three | 2 |
+------+-------+--------+
3 rows in set (0.00 sec)
# 这就是算绑定成功了
虽然确实绑定在一起了,但若是后期扩展的时候有时候会出现麻烦,可以单纯逻辑上同步。