表——完整约束性规则（键）

发布时间：2020-12-12 00:02:51 所属栏目：MySql教程来源：网络整理

导读：一介绍约束条件与数据类型的宽度一样，都是可选参数作用：用于保证数据的完整性和一致性主要分为： unsigned 无符号 zerofill 使用0填充说明： 1 2span style="color: #000000;". 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，

一介绍

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

作用：用于保证数据的完整性和一致性
主要分为：

unsigned 无符号
zerofill 使用0填充

说明：

12. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male' #设置了不能为空，为空时默认使用male

age int unsigned NOT NULL default 20 必须为正值（无符号）不允许为空默认是20

3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

二 not null与default

是否可空，null表示空，非字符串
not null - 不可空
null - 可空

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
id int not null defalut 2,
num int not null
)

==================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===============20100方法二：
create table department2(
id int,name varchar(20),comment varchar(100),constraint uk_name unique(name) # 给UNIQUE KEY设置名字：uk，不设置系统也会给它命一个命称
);

mysql> insert into department1 values(1,'IT','技术');
Query OK,1 row affected (0.00 sec)

mysql> insert into department1 values(1,'技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name' #因为设置了唯一，但是赋值时重复了

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

15) mysql> insert into service values
-> (1,'nginx','192.168.0.10',80),-> (2,'haproxy','192.168.0.20',-> (3,'mysql','192.168.0.30',3306)
-> ;
Query OK,3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into service(name,host,port) values('nginx',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

四 primary key

primary key字段的值不为空且唯一

一个表中可以：

单列做主键
多列做主键（复合主键），有多列唯一的效果

但一个表内只能有一个主键primary key

============单列做主键===============

 null unique,
name varchar(20) 100mysql><span style="color: #000000;"> desc department1;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id      | int(11)      | NO   | PRI | NULL    |       |

| name    | varchar(20)  | NO   | UNI | NULL    |       |

| comment | varchar(100) | YES  |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+<span style="color: #000000;">

rows <span style="color: #0000ff;">in set (0.01<span style="color: #000000;"> sec)
<span style="color: #008000;">#<span style="color: #008000;">方法二：在某一个字段后用primary key

<span style="color: #000000;">create table department2(

id int primary key,<span style="color: #008000;">#<span style="color: #008000;">主键

name varchar(20<span style="color: #000000;">),comment varchar(100<span style="color: #000000;">)

);
mysql><span style="color: #000000;"> desc department2;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id      | int(11)      | NO   | PRI | NULL    |       |

| name    | varchar(20)  | YES  |     | NULL    |       |

| comment | varchar(100) | YES  |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+<span style="color: #000000;">

rows <span style="color: #0000ff;">in set (0.00<span style="color: #000000;"> sec)
<span style="color: #008000;">#<span style="color: #008000;">方法三：在所有字段后单独定义primary key

<span style="color: #000000;">create table department3(

id int,constraint pk_name primary key(id); <span style="color: #008000;">#<span style="color: #008000;">创建主键并为其命名pk_name

<span style="color: #000000;">

mysql><span style="color: #000000;"> desc department3;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id      | int(11)      | NO   | PRI | NULL    |       |

| name    | varchar(20)  | YES  |     | NULL    |       |

| comment | varchar(100) | YES  |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+<span style="color: #000000;">

rows <span style="color: #0000ff;">in set (0.01 sec)

mysql> insert into service values
-> ('172.16.45.10','3306','mysqld'),-> ('172.16.45.11','mariadb')
-> ;
Query OK,2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into service values ('172.16.45.10','nginx'); #重复了，报错
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

五 auto_increment

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

mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | egon | male |
| 2 | alex | male |
+----+------+------+

#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK,1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb',1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | egon | male |
| 2 | alex | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+------+--------+

#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK,4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 8 | ysb | male |
+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK,0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK,1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)

mysql>->-> name varchar(20) default ->mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 3 | egon | male |
+----+------+------+
row in set (0.00 sec)

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,'female') default 'male'
)auto_increment=3;

#设置步长
sqlserver：自增步长
基于表级别
create table t1（
id int。。。
）engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长：
show session variables like 'auto_inc%';

</span><span style="color: #008000;"&gt;#</span><span style="color: #008000;"&gt;基于会话级别</span>
set session auth_increment_increment=2 <span style="color: #008000;"&gt;#</span><span style="color: #008000;"&gt;修改会话级别的步长</span>

<span style="color: #008000;"&gt;#</span><span style="color: #008000;"&gt;基于全局级别的</span>
set <span style="color: #0000ff;"&gt;global</span> auth_increment_increment=2 <span style="color: #008000;"&gt;#</span><span style="color: #008000;"&gt;修改全局级别的步长（所有会话都生效）</span>

#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment,the value of auto_increment_offset is ignored.
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
比如：设置auto_increment_offset=3，auto_increment_increment=2

mysql> set global auto_increment_increment=5;
Query OK,0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK,0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+

create table student(
id int primary key auto_increment,'female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 3 | egon1 | male |
| 8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+

六 foreign key

CREATE TABLE 时的 SQL FOREIGN KEY 约束

下面的 SQL 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束：

MySQL：

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Orders
(
O_Id int NOT NULL,
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

ALTER TABLE 时的 SQL FOREIGN KEY 约束

当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access：

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

员工信息表有三个字段：工号姓名部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：

我们完全可以定义一个部门表

然后让员工信息表关联该表，如何关联，即foreign key

#！！！先建被关联的表,并且被关联的字段必须唯一
create table department(
id int primary key,name varchar(20) not null
)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除

create table employee(
id int primary key,name varchar(20) not null,dpt_id int,constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade
)engine=innodb;

#先往父表department中插入记录
insert into department values
(1,'欧德博爱技术有限事业部'),(2,'艾利克斯人力资源部'),(3,'销售部');

#再往子表employee中插入记录
insert into employee values
(1,'egon',1),'alex1',2),'alex2',(4,'alex3',(5,'李坦克',3),(6,'刘飞机',(7,'张火箭',(8,'林子弹',(9,'加特林',3)
;

#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | egon | 1 |
| 2 | alex1 | 2 |
| 3 | alex2 | 2 |
| 4 | alex3 | 2 |
+----+-------+--------+

#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | egon | 1 |
| 3 | alex2 | 22222 |
| 4 | alex3 | 22222 |
| 5 | alex1 | 22222 |
+----+-------+--------+

利用foreign key的原理我们可以制作两张表的多对多，一对一关系
多对多：
表1的多条记录可以对应表2的一条记录
表2的多条记录也可以对应表1的一条记录

一对一：
表1的一条记录唯一对应表2的一条记录，反之亦然

分析时，我们先从按照上面的基本原理去套，然后再翻译成真实的意义，就很好理解了


一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key
建立多对一的关系需要注意

1<span style="color: #000000;"> 先建立被关联的表，被关联的字段必须保证是唯一的

2<span style="color: #000000;"> 再创建关联的表，关联的字段，一定要保证是可以重复的

ps：关联的字段一定是来自于表关联的表对应字段的值

=====================多对一=====================create table press(
id int primary key auto_increment,#被关联的字段必须保证是唯一的
name varchar(20)
);

再创建关联的表

create table book(
id int primary key auto_increment,press_id int not null,#关联的字段，一定要保证是可以重复的
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);

insert into press(name) values
('北京工业地雷出版社'),('人民音乐不好听出版社'),('知识产权没有用出版社')
;

insert into book(name,press_id) values
('九阳神功',1),#关联的字段一定是来自于表关联的表对应字段的值
('九阴真经',('九阴白骨爪',('独孤九剑',('降龙十巴掌',('葵花宝典',3)
;


多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

　　

关联方式：foreign key+一张新的表

=====================多对多=====================20#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了

create table author2book(
id int primary key auto_increment,author_id int not null,book_id int not null,foreign key(author_id) references author(id) on delete cascade on update cascade,foreign key(book_id) references book(id)
on delete cascade
on update cascade,unique (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,4),5),6),1)
;


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

create table customer(
id int primary key auto_increment,name varchar(20) not null
);

create table student(
id int primary key auto_increment,class_name varchar(20) not null default 'python自动化',level int default 1,customer_id int unique,#该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);

#增加客户
insert into customer(name) values
('李飞机'),('王大炮'),('守榴弹'),('吴坦克'),('赢火箭'),('战地雷')
;

#增加学生
insert into student(name,customer_id) values
('李飞机',('王大炮',2)
;

多对一（一对多）、一对一、多对多的关系：

foreign key(表1_字段名) references 表2(字段名);

在表2的字段名是主键的情况下（即不为空，且唯一）

若表1的字段名不唯一，则是多对一

若表1的字段名唯一（unique），则是一对一

多对多则需要另建一个表来单独存放多对多的关系（即有关联的字段），

foreign key(新表_字段名1) references 表1(字段名)

foreign key(新表_字段名2) references 表1(字段名) on delete cascadeon update cascade;

外键对应的字段数据类型必须一致，两张表的存储引擎必须一致，否则会出错

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

表——完整约束性规则（键）

一 介绍

二 not null与default

三 unique

四 primary key

五 auto_increment

六 foreign key

CREATE TABLE 时的 SQL FOREIGN KEY 约束

ALTER TABLE 时的 SQL FOREIGN KEY 约束

撤销 FOREIGN KEY 约束

一介绍