约束条件（续）

　　昨天的约束条件还没有讲完，今天继续补充几个相当重要的约束条件。

约束条件之主键

primary key

什么是主键？

　　首先说一说理论：

1.单从约束层面来讲，相当于not null+unique，即非空且唯一。

2.主键还是InnoDB存储引擎组织数据表的依据。这点又分为两个小点：

　　(1)InnoDB规定了一个表必须要有且只有一个主键。

　　(2)如果不指定主键，则会采用隐藏的字段作为主键。

　　主键是可以加快查询速度的，隐藏了主键意味着无法使用主键，也就是速度无法提升。

3.当表中没有主键但是有非空且唯一的字段时，该字段自动升级为主键（自上而下的第一个）。

结论：

　　在创建表时一般都需要有一个id字段，并且该字段应设置为表的主键字段。

联合主键

　　主键可以单列主键，也可以联合主键，不过联合主键的使用频率不高。

create table t3(id int,name char(16),primary key(id,name));

约束条件之自增

　　现在创建一张人名登记表，按照序号登记人名就可以。

create table t4(id int primary key,name char(16));
insert into t4 values(1,'joe');
insert into t4 values(2,'simon');
insert into t4 values(3,'frank');
...
select * from t4;

　　如果现在要在表中登记几百上千个人名，再挨个写序号就太麻烦了。所以为了减轻工作量，可以使用自增这一约束条件：

create table t5(id int primary key auto_increment,name char(16));
insert into t5(name) values('joe'),('simon'),('frank');
select * from t5;

insert into t5(name) values('jerry'),('eddie'),('mark');
select * from t5;

　　这样一来，不必每次都手动输入，程序就会自动填写序号。

　　如此便利的功能，自然经常会配合主键一起使用。所以以后创建拥有序号或类似计数方式的表时，主键字段可以采用一种固定的写法：

id int primary key auto_increment

　　不过，自增也不是没有缺点。

delete from t5 where id=6;
select * from t5;
insert into t5(name) values('tom');
select * from t5;

　　删掉第六条再插入新的记录，顺序却没有继承6而是另起炉灶用了7。主键自增不会随着删除记录而自动排序，而是顺着被删除的序号自增一位。

delete from t5;
select *from t5;
insert into t5(name) values('tom')

特性补充

　　1.主键自增不会因为delete操作而重置或回退（只删除数据，不影响表结构）

　　2.如果真的想重置需要清空表数据和表结构（既删除数据，又重置表结构）

　　需要注意的是：报错的命令还是会主键自增。

　　如果想要即删除数据，又不影响排序，应该怎么做呢？

truncate t5;
select * from t5;
insert into t5(name) values('tom');
select * from t5;

　　然而这么做会将表内的数据全部清空，全部木大，又得从头干起。

　　自增之所以会有这样不近人情的设定也是为了保留证据，可以避免有些别有用心之人胡乱篡改表内数据，如有删改，只消看一眼上下记录之间的序号就可以推断出数据之间的延续关系了。

约束条件之外键

什么是外键？

　　首先来举一个有点长的栗子：现在某工作室制作了一张员工登记表。

　　表内应该列有：序号，名字，年龄，部门，部门描述。

　　现在有以下这些员工及其信息亟需记录：

1,joe,18,公关部,对外宣传 2,simon,22,开发部,软件开发 3,frank,26,保安部,巡逻监视 4,jerry,26,开发部,软件开发 5,eddie,21,开发部,软件开发 6,mark,18,财务部,统筹资金

　　那么最后形成的表格应该是这样：

　　在MySQL中的格式是这样：id,name,age,dep_name,dep_desc

　　看起来没什么问题，但实际上还是有缺陷的： 1.表结构不清晰。（侧重点不清晰，可以忽略） 2.表数据重复。（存储时浪费空间，可以忽略） 3.数据扩展性极差（不便于修改数据，不可忽略）

　　最致命的软肋就是第三点：现在只登陆了6个员工，所以还看不出问题，但如果人数扩大到成百上千，除了名字年龄还要写上工作部门和部门职责，不光费时费力还占用大量硬盘容量。

　　解决这个缺陷的方式应该是：将员工表中的部门、部门职责拆出去独立编制一张部门表，去重之后给每个部门赋予序号；然后在员工表中设立一个新的列部门代码dep_code，将员工的部门代码和所属部门的id对号入座即可。

emp:id,name,age,dep_code dep:id,dep_name,dep_desc

　　这样一来既减轻了工作量，节约了硬盘空间，又能迅速关联想要的数据。

　　如范例中将员工的部门代码和部门序号相关联的做法，就被称作外键。外键就是记录表与表之间数据关系的字段。

表关系的种类

　　从表与表之间的关系来进行分了，外键可以分成3+1种情况。

1.一对多关系

2.多对多关系

3.一对一关系

4.没有关系

　　毫无疑问，第四种实际上就是凑数的，只是用来防杠精，不必花心思解释。接下来直接结合例题来说明前三种关系以及应该如何判定。

1.一对多关系

　　判断表与表之间的关系，有一个非常简单的口诀，只有四个字：换位思考！

　　就以上文工作室的员工表和部门表为例：

(1)先站在员工表的立场上，思考一个员工能否对应多个部门（不可）

(2)再站在部门表的立场上，思考一个部门能否对应多个员工（可）

(3)结论：一个可一个不可，那么员工表与部门表即为一对多（员工多部门一）

　　一对多表关系外键字段建在多的一方（本例中员工为多）

　　明确了双方关系，现在可以在MySQL中建表了。

代码技巧

　　在初学阶段，可以运用一下两点技巧辅助编程：

1.先写基本字段类型与约束条件。

2.之后再考虑外键。

　　外键的代码为：

foreign key(需要关联的列) references 被关联表(被关联的列)

　　带入到例题中即为：

create table emp(id int primary key auto_increment,
    name char(16),
    age int,
    dep_code int,
    foreign key(dep_code) references dep(id)
);
create table dep(id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
desc emp;
desc dep;

　　以上命令如果按顺序直接输入的话准会报错。

　　正确方式应该是先创建被关联的表（也就是没有外键字段的表），然后定义关联表。联系到范例中就是先创部门表再创员工表。

　　在插入数据时也是一样，先插入被关联表的数据，再插入关联表的（只能够填写被关联字段中出现的值）。

　　按这样的顺序操作，员工表和部门表才能创建出来 。

级联更新、级联删除

　　现在如果想要修改表中的数据却会发现出现了报错。

update dep set id=10 where id=1;
delete from dep where id=3;

　　正确方法应该是要在被关联的表中加上级联更新和级联删除的语句。

create table emp(id int primary key auto_increment,
    name chat(16),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

2.多对多关系

　　现在来做两张表，一张书籍表，一张作者表。

书籍表

id name price

1,python入门,298

2,linux入土,198

3，数据分析,999

作者表

id name age

1,joe,18

2,simon,22

　　还是使用换位思考：

(1)先以书籍表为基准，思考一个书籍能否对应多个作者（可）

(2)再以作者表为基准，思考一个作者能否对应多个书籍（可）

(3)结论：都可以，那么表关系就是多对多。

　　那么接下来可以如法炮制：

　　什么情况？为什么不能用？

　　在一对多关系中讲过，用外键建立关系是MySQL便会要求先创建被关联的表。而如果两张表互相关联对方的话，那就成了衔尾蛇，建头要求先建尾，建尾要求先建头，变成了逻辑bug。

　　所以，在多对多的情况下不能给两张表建立联系，而是创建第三张表为它们牵线搭桥。

create table book(
    id int primary key auto_increment,
    name char(16),
    price int
);
create table author(
    id int primary key auto_increment,
    name char(16),
    age int
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
desc book;
desc author;
desc book2author;

　　如此一来，就能正常运作了。

3.一对一关系

　　现在我们接到了鹅厂外包的活，给一部分QQ用户的数据创建用户表和用户详情表：

user id,name,qqn_num 1,jason,111111111 2,tony,222222222

user_detail id,phone 1,110 2,119

　　QQ信息可拆分成用户表和用户详情表。 用户表（热数据） id,name,qq_num 用户详情表（冷数据） id,phone 　　两表之间同样也是一对一关系。

　　依然使用换位思考方法：

(1)先站在用户表层面，一个用户能否对应多个用户详情（不可）

(2)再站在用户详情表层面，一个用户详情能否对应多个用户（不可）

(3)两边都不可，要么表关系要么没关系，要么是一对一关系。

　　在给一对一表关系创建关系时可以参考这条建议：外键建在任何一方都可以，但是推荐建立在用户频率高的一方。

create table user_detail(
    id int primary key auto_increment,
    phone bigint,
    addr varchar(16)
);
create table user(
    id int primary key auto_increment,
    name varchar(16),
    detail_id int unique,
    foreign key(detail_id) references user_detail(id)
    on update cascade
    on delete cascade
);
desc user;
desc user_detail;

外键字段的特性

　　经过对三种表关系的详细分析，我们可以得出一些结论：

1.在创建表时一定要先创建被关联表（自身没有外键字段的表）

2.在插入数据的时候也是先插入被关联表，再插入关联表（只能够填写被关联字段中出现的值）

3.被关联表中的数据无法自由删除和修改，必须通过级联更新、级联删除的方式才能修改或重置表

修改表相关SQL语句

1.修改表名
alter table 表名 rename 新表名；
2.增加字段
create table t6(id int primary key auto_increment,name char(16),age int);
alter table t6 add gender enum('male','female','others';)
desc t6;
​
（在开头）
alter table t6 add nid int first;
desc t6；
​
（在任意位置）
alter table t6 add pwd int after name;
desc t6;
​
3.删除字段
alter table t6 drop……
​
4.修改字段（modify只能改字段数据类型完整约束，不能改字段名，但是change可以）
alter table 表名 modify 字段名 数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];