表的完整性约束
表的完整性约束
约束条件和宽度一样，都是可选参数。用于保证数据的完整性和一致性。
分类
1PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
2FOREIGN KEY (FK) 标识该字段为该表的外键
3NOT NULL 标识该字段不能为空
4UNIQUE KEY (UK) 标识该字段的值是唯一的
5AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
6DEFAULT 为该字段设置默认值
7UNSIGNED 无符号
8ZEROFILL 使用0填充
详细说明
11. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
22. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
3 sex enum('male','female') not null default 'male'
4 age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
53. 是否是key
6 主键 primary key
7 外键 foreign key
8 索引 (index,unique...)
not null 与 default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(id int not null defalut 2,num int not null)
1==================not null====================
2mysql> create table t1(id int); #id字段默认可以插入空
3mysql> desc t1;
4+-------+---------+------+-----+---------+-------+
5| Field | Type | Null | Key | Default | Extra |
6+-------+---------+------+-----+---------+-------+
7| id | int(11) | YES | | NULL | |
8+-------+---------+------+-----+---------+-------+
9mysql> insert into t1 values(); #可以插入空
10mysql> create table t2(id int not null); #设置字段id不为空
11mysql> desc t2;
12+-------+---------+------+-----+---------+-------+
13| Field | Type | Null | Key | Default | Extra |
14+-------+---------+------+-----+---------+-------+
15| id | int(11) | NO | | NULL | |
16+-------+---------+------+-----+---------+-------+
17mysql> insert into t2 values(); #不能插入空
18ERROR 1364 (HY000): Field 'id' doesn't have a default value
19
1==================default====================
2#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
3mysql> create table t3(id int default 1);
4mysql> alter table t3 modify id int not null default 1;
1==================综合练习====================
2mysql> create table student(
3 -> name varchar(20) not null,
4 -> age int(3) unsigned not null default 18,
5 -> sex enum('male','female') default 'male',
6 -> hobby set('play','study','read','music') default 'play,music'
7 -> );
8mysql> desc student;
9+-------+------------------------------------+------+-----+------------+-------+
10| Field | Type | Null | Key | Default | Extra |
11+-------+------------------------------------+------+-----+------------+-------+
12| name | varchar(20) | NO | | NULL | |
13| age | int(3) unsigned | NO | | 18 | |
14| sex | enum('male','female') | YES | | male | |
15| hobby | set('play','study','read','music') | YES | | play,music | |
16+-------+------------------------------------+------+-----+------------+-------+
17mysql> insert into student(name) values('egon');
18mysql> select * from student;
19+------+-----+------+------------+
20| name | age | sex | hobby |
21+------+-----+------+------------+
22| egon | 18 | male | play,music |
23+------+-----+------+------------+
24
unique
限制字段的值唯一
1#单列唯一
2create table t16(
3 id int unique,
4 name char(16)
5);
1# 联合唯一
2create table server(
3 id int unique,
4 ip char(15),
5 port int,
6 unique(ip,port)
7);
primary key
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表单单从约束角度去看，primary key就等同于not null unique
强调
1、一张表中必须有，并且只能有一个主键
2、一张表中都应该有一个id字段，而且应该把id字段做成主键
1#单列主键
2create table t17(
3 id int primary key,
4 name char(16),
5 age int,
6 sex char(6)
7)engine=innodb;
1#多列主键(联合主键)
2create table t19(
3 ip char(15),
4 port int,
5 primary key(ip,port)
6);
auto_increment
约束字段为自动增长，被约束的字段必须同时被key约束
auto_increment注意点：
1、通常与primary key连用，而且通常是给id字段加
2、auto_increment只能给被定义成key（unique key，primary key）的字段加
1create table student(
2id int primary key auto_increment,
3name varchar(20),
4sex enum('male','female') default 'male'
5);
6mysql> desc student;
7+-------+-----------------------+------+-----+---------+----------------+
8| Field | Type | Null | Key | Default | Extra |
9+-------+-----------------------+------+-----+---------+----------------+
10| id | int(11) | NO | PRI | NULL | auto_increment |
11| name | varchar(20) | YES | | NULL | |
12| sex | enum('male','female') | YES | | male | |
13+-------+-----------------------+------+-----+---------+----------------+
14mysql> insert into student(name) values
15 -> ('egon'),
16 -> ('alex')
17 -> ;
18mysql> select * from student;
19+----+------+------+
20| id | name | sex |
21+----+------+------+
22| 1 | egon | male |
23| 2 | alex | male |
24+----+------+------+
1#也可以指定id
2mysql> insert into student values(4,'asb','female');
3Query OK, 1 row affected (0.00 sec)
4mysql> insert into student values(7,'wsb','female');
5Query OK, 1 row affected (0.00 sec)
6mysql> select * from student;
7+----+------+--------+
8| id | name | sex |
9+----+------+--------+
10| 1 | egon | male |
11| 2 | alex | male |
12| 4 | asb | female |
13| 7 | wsb | female |
14+----+------+--------+
1#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
2mysql> delete from student;
3Query OK, 4 rows affected (0.00 sec)
4mysql> select * from student;
5Empty set (0.00 sec)
6mysql> insert into student(name) values('ysb');
7mysql> select * from student;
8+----+------+------+
9| id | name | sex |
10+----+------+------+
11| 8 | ysb | male |
12+----+------+------+
1#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
2mysql> truncate student;
3Query OK, 0 rows affected (0.01 sec)
4mysql> insert into student(name) values('egon');
5Query OK, 1 row affected (0.01 sec)
6mysql> select * from student;
7+----+------+------+
8| id | name | sex |
9+----+------+------+
10| 1 | egon | male |
11+----+------+------+
121 row in set (0.00 sec)
13
