数据库基础--约束--表与表之间的关联

约束条件：

与数据类型的宽度一样，都是 可选参数作用：用于保证数据的完整性和一致性
主要分为：  

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 必须为正值（无符号） 不允许为空 默认是203. 是否是key主键 primary key外键 foreign key索引 (index,unique...)

 

not null与default:

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

==================not null====================mysql> create table t1(id int); #id字段默认可以插入空mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | YES  | | NULL||+-------+---------+------+-----+---------+-------+mysql> insert into t1 values(); #可以插入空mysql> create table t2(id int not null); #设置字段id不为空mysql> desc t2;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+mysql> insert into t2 values(); #不能插入空ERROR 1364 (HY000): Field 'id' doesn't have a default value==================default====================#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值mysql> create table t3(id int default 1);mysql> alter table t3 modify id int not null default 1;

 

unique:

============设置唯一约束 UNIQUE===============方法一：create table department1(id int,name varchar(20) unique,comment varchar(100));方法二：create table department2(id int,name varchar(20),comment varchar(100),constraint uk_name unique(name));mysql> insert into department1 values(1,'IT','技术');Query OK, 1 row affected (0.00 sec)mysql> insert into department1 values(1,'IT','技术');ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

============not null+unique===============

mysql> create table t1(id int not null unique);Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| PRI | NULL||+-------+---------+------+-----+---------+-------+

=================== 联合唯一 ==================

create table service(id int primary key auto_increment,name varchar(20),host varchar(15) not null,port int not null,unique(host,port) #联合唯一);mysql> insert into service values-> (1,'nginx','192.168.0.10',80),-> (2,'haproxy','192.168.0.20',80),-> (3,'mysql','192.168.0.30',3306)-> ;Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Waings: 0mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

 

primary key:

primary key字段的值不为空且唯一一个表中可以：单列做主键多列做主键（复合主键）但一个表内只能有一个主键primary key

===================单列做主键===================#方法一：not null+uniquecreate table department1(id int not null unique, #主键name varchar(20) not null unique,comment varchar(100));

==================多列做主键================create table service(ip varchar(15),port char(5),service_name varchar(10) not null,primary key(ip,port));

 

 auto_increment:

约束字段为自动增长，被约束的字段必须同时被key约束

1 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

  应该用 truncate 清空表 ，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

2 步长increment  起始偏移量offset:  auto_increment_increment,auto_increment_offset

基于会话级别set session auth_increment_increment=2 #修改会话级别的步长基于全局级别的 set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）注意

如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 比如：设置auto_increment_offset=3，auto_increment_increment=2

 

foreign key:

用于 表与表 之间 字段的关联

 

一对多或称为多对一
三张表：出版社，作者信息，书一对多（或多对一）：一个出版社可以出版多本书关联方式：foreign key

=====================多本书对一个出版社=====================create table press(id int primary key auto_increment,name varchar(20));create table book(id int primary key auto_increment,name varchar(20),press_id int not null,foreign key(press_id) references press(id)on delete cascadeon update cascade);insert into press(name) values('A出版社'),('B出版社'),('C出版社');insert into book(name,press_id) values('1.txt',1),('2.666',2),('ooo',2),('xxx',3),('rrr',2),('tttt',3);

 

多对多
三张表：出版社，作者信息，书多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多　　关联方式：foreign key+一张新的表

=====================多对多=====================create table author(id int primary key auto_increment,name varchar(20));#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,constraint fk_author foreign key(author_id) references author(id)on delete cascadeon update cascade,constraint fk_book foreign key(book_id) references book(id)on delete cascadeon update cascade,primary key(author_id,book_id));#插入四个作者，id依次排开insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每个作者与自己的代表作如下1 egon:1 九阳神功  2 九阴真经  3 九阴白骨爪  4 独孤九剑  5 降龙十巴掌  6 葵花宝典2 alex:1 九阳神功  6 葵花宝典3 yuanhao:  4 独孤九剑  5 降龙十巴掌  6 葵花宝典4 wpq:  1 九阳神功insert into author2book(author_id,book_id) values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);

 

一对一

两张表：学生表和客户表一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系关联方式：foreign key+unique

#一定是student来foreign key表customer，这样就保证了：#1 学生一定是一个客户，#2 客户不一定是学生，但有可能成为一个学生create table customer(id int primary key auto_increment,name varchar(20) not null);create table student(id int primary key auto_increment,name varchar(20) not null,class_name varchar(20) not null default 'python自动化',level int default 1,customer_id int unique, #该字段一定要是唯一的foreign key(customer_id) references customer(id) #外键的字段一定要保证uniqueon delete cascadeon update cascade);#增加客户insert into customer(name) values('李飞机'),('王大炮'),('守榴弹'),('吴坦克'),('赢火箭'),('战地雷');#增加学生insert into student(name,customer_id) values('李飞机',1),('王大炮',2);