Mysql---完整性约束
完整性约束---用于保证数据的完整性和一致性
KEY POINT:
- not null 与 default
- unique
- primary key
- auto_increment
- foreign key
一 not null 与 default
1.是否可空
null空
not null不可空
2.默认值default,当不设置时，默认值为null,当设置默认值时，默认值为设置的默认值
a.默认值可以为空:
b.设置not null，插入值时不能为空
c.设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
二 unique
1.单列唯一
2.联合唯一
3.组合唯一
三 primary key
1.主键 相当于 not null +unique 字段的值唯一且不为空
mysql> create table t10( -> id int primary key, -> name char(16) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into t10 values -> (1,"xiaoma"), -> (2,"alex"), -> (3,"wusir"); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t10; +----+--------+ | id | name | +----+--------+ | 1 | xiaoma | | 2 | alex | | 3 | wusir | +----+--------+ 3 rows in set (0.00 sec) mysql> insert into t10 values -> (1,"tailiang"); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> desc t10; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
2.一个表中可以有单列做主键，也可以有多列做主键
mysql> create table t12( -> ip char(15), -> port int, -> primary key(ip,port) -> ); Query OK, 0 rows affected (0.05 sec)
3.存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。
四 auto_increment 约束的字段为自动增长，约束的字段必须同时被key约束
1.不指定id,则自动增长
mysql> create table t14 -> ( -> id int primary key auto_increment, -> name varchar(15) not null); Query OK, 0 rows affected (0.05 sec) mysql> insert into t14 values ("太白"),("太黑"); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into t14(name) values ("太白"),("太黑"); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t14; +----+--------+ | id | name | +----+--------+ | 1 | 太白 | | 2 | 太黑 | +----+--------+ 2 rows in set (0.00 sec)
2.也可指定id
3.对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
4.注意：
a.步长auto_increment_increment,默认为1
b.起始的偏移量auto_increment_offset, 默认是1
可通过设置以上两个值改变默认的步长和起始偏移量
mysql> use db1 Database changed #创建表t16 mysql> create table t16( -> id int primary key auto_increment, -> name varchar(10) not null -> ); Query OK, 0 rows affected (0.04 sec) #插入数据 mysql> insert into t16 values -> (1,"alex"), -> (2,"wusir"), -> (3,"egon"), -> (4,"xiaomage"); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 #显示t16详细数据 mysql> select * from t16; +----+----------+ | id | name | +----+----------+ | 1 | alex | | 2 | wusir | | 3 | egon | | 4 | xiaomage | +----+----------+ 4 rows in set (0.00 sec) #删除 id=2的字段记录 mysql> delete from t16 where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from t16; +----+----------+ | id | name | +----+----------+ | 1 | alex | | 3 | egon | | 4 | xiaomage | +----+----------+ 3 rows in set (0.00 sec) #删除 id=4的字段记录 mysql> delete from t16 where id=4; Query OK, 1 row affected (0.01 sec) mysql> select * from t16; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | egon | +----+------+ 2 rows in set (0.00 sec) #插入新的纪录 mysql> insert into t16 values -> ("haha"); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into t16(name) values -> ("haha"); Query OK, 1 row affected (0.01 sec) mysql> select * from t16; +----+------+ | id | name | +----+------+ | 1 | alex | | 3 | egon | | 5 | haha |#新插入的字段依然按照之前顺序排列 +----+------+ 3 rows in set (0.00 sec) #查看可用的 开头auto_inc的词 mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | #步长默认为1 | auto_increment_offset | 1 | #偏移量默认为1 +--------------------------+-------+ 2 rows in set, 1 warning (0.02 sec) mysql> set session auto_increment_increment=5; #设置步长为5 Query OK, 0 rows affected (0.01 sec) mysql> set session auto_increment_offset=2; #设置偏移量为2 Query OK, 0 rows affected (0.00 sec) #确认设置成功 mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 5 | | auto_increment_offset | 2 | +--------------------------+-------+ 2 rows in set, 1 warning (0.00 sec) #重新插入新纪录 mysql> insert into t16(name) values -> ("灌灌灌灌"); Query OK, 1 row affected (0.00 sec) #查看新纪录id mysql> select * from t16; +----+--------------+ | id | name | +----+--------------+ | 1 | alex | | 3 | egon | | 5 | haha | | 7 | 灌灌灌灌 | +----+--------------+ 4 rows in set (0.00 sec) mysql> insert into t16(name) values -> ("丰富"); Query OK, 1 row affected (0.01 sec) mysql> select * from t16; +----+--------------+ | id | name | +----+--------------+ | 1 | alex | | 3 | egon | | 5 | haha | | 7 | 灌灌灌灌 | | 12 | 丰富 | +----+--------------+ 5 rows in set (0.00 sec)
五 foreign key
六 delete 和 truncate的区别
