数据库开发——MySQL——约束条件与表关系

一，介绍

约束条件与数据类型的宽度一样，都是任选参数。

作用：用于保证数据的一致和一致性。

主要划分为：

PRIMARY KEY（PK）标识该分区为该表的主键，可以唯一的标识记录    
FOREIGN KEY（FK）标识该为该表的外键    
NOT NULL标识该标识不能为空    
UNIQUE KEY（UK）标识该细分的值是    唯一的
AUTO_INCREMENT标识该细分的值自动增长（整体类型，而且主要键）    
DEFAULT为该分区设置值    

UNSIGNED无符号   
ZEROFILL使用0填充

二，not null与default

是否可空，null表示空，非字符串 not null- 不可空null- 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)
练习：
1.创建可以插入空值的表1：

 create table t1(id int);

 desc t1;

 insert into t1 values();

 select * from t1;

 mysql> create table t1(id int);
 Query OK, 0 rows affected (0.84 sec)

 mysql> desc t1;
 +-------+---------+------+-----+---------+-------+
 | Field | Type    | Null | Key | Default | Extra |
 +-------+---------+------+-----+---------+-------+
 | id    | int(11) | YES  |     | NULL    |       |
 +-------+---------+------+-----+---------+-------+
 1 row in set (0.13 sec)

 mysql> insert into t1 values();
 Query OK, 1 row affected (0.22 sec)

 mysql> select * from t1;
 +------+
 | id   |
 +------+
 | NULL |
 +------+
 1 row in set (0.00 sec)

2.创建不能插入空值的表2：

 create table t2(id int not null);

 desc t2;

 insert into t2 values();

执行结果为：

 mysql> create table t2(id int not null);
 Query OK, 0 rows affected (0.63 sec)

 mysql> desc t2;
 +-------+---------+------+-----+---------+-------+
 | Field | Type    | Null | Key | Default | Extra |
 +-------+---------+------+-----+---------+-------+
 | id    | int(11) | NO   |     | NULL    |       |
 +-------+---------+------+-----+---------+-------+
 1 row in set (0.00 sec)

 mysql> insert into t2 values();
 ERROR 1364 (HY000): Field 'id' doesn't have a default value

3.创建有默认值的表3：

create table t3(id int not null default 1);

desc t3;

insert into t3 values();
insert into t3 values(2);

select * from t3;

执行结果为：

mysql> create table t3(id int not null default 1);
Query OK, 0 rows affected (0.49 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t3 values();
Query OK, 1 row affected (0.47 sec)

mysql> insert into t3 values(2);
Query OK, 1 row affected (0.16 sec)

mysql> select * from t3;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

三，unique

设置唯一约束条件，设置后不可重复。
练习：

1.创建id唯一，学号唯一的表4：

create table t4(
	id int unique,
	name varchar(20),
	number char(10),
	unique(number));

insert into t4 values(1, "Alex", "18023300");
insert into t4 values(2, "Coco", "18023300");
insert into t4 values(2, "Coco", "19205200");

select * from t4;

执行结果为：

 mysql> create table t4(
     -> id int unique,
     -> name varchar(20),
     -> number char(10),
     -> unique(number));
 Query OK, 0 rows affected (0.93 sec)

 mysql> insert into t4 values(1, "Alex", "18023300");
 Query OK, 1 row affected (0.20 sec)

 mysql> insert into t4 values(1, "Coco", "18023300");
 ERROR 1062 (23000): Duplicate entry '1' for key 'id'
 mysql> insert into t4 values(2, "Coco", "18023300");
 ERROR 1062 (23000): Duplicate entry '1' for key 'number '
 mysql> insert into t4 values(2, "Coco", "19205200");
 Query OK, 1 row affected (0.09 sec)

 mysql> select * from t4;
 +------+------+----------+
 | id   | name | number   |
 +------+------+----------+
 |    1 | Alex | 18023300 |
 |    2 | Coco | 19205200 |
 +------+------+----------+
 2 rows in set (0.01 sec)

如果某些字段不为空且唯一，也就是不是null + unique，会定义为主键：

 mysql> create table t5(id int not null unique);
 Query OK, 0 rows affected (0.54 sec)

 mysql> desc t5;
 +-------+---------+------+-----+---------+-------+
 | Field | Type    | Null | Key | Default | Extra |
 +-------+---------+------+-----+---------+-------+
 | id    |  整数（11 ） |  否   | PRI |  NULL     |        
 +-------+---------+------+-----+---------+-------+