MySQL表操作(下篇）--完整性约束

一.介绍

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

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

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

二，PRIMARY KEY (PK)

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

约束：not null unique
存储引擎（innodb):对于innodb存储引擎，一张表必须要有一个主键

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

不指定主键MYSQL会寻找一个不为空且唯一的值为主键

#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name 

constraint 是约束的意思，单独拿出来加约束条件

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

也称之为复合主键

三，FOREIGN KEY (FK)

标识该字段为该表的外键，理解为一个表关联另外一张表。

1.如何找出两张表的关系

分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：出版社  书
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

从两张表中找多对一的关系，只要有一个符合就新加一个字段关联到另一张表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

 2，建立表之间的关系

#多对一

 

#先建立被关联的表，并且保证被关联表唯一

create table dep(
id int primary key,
name char(16),
comment char(50)
);

#在建立关联表
create table emp(
id int primary key,
name char(16),
sex enum("male","female"),
dep_id int,
foreign key(dep_id)references dep(id)
on delete cascade
on update cascade # 在实际操作中尽量不要同步更新，影响扩展性
);

#插入数据
#先往被关联表插入记录

insert into dep values
(1,"IT","技术有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");

insert into emp values
(1,"egon","male",1),
(2,"alex","male",1),
(3,"wupeiqi","female",2),
(4,"yuanhao","male",3),
(5,"jingxin","male",2)

未加同步更新和删除
删除先删除关联表
delete from emp where dep_id=1;=
后删除被关联表
delete from dep where id=1;

加了同步更新
update dep set id=202 where id=2;

 #多对多

create table author(
    id int primary key auto_increment,
    name char(20)
    );

create table book(
    id int primary key auto_increment,
    name char(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 cascade
    on update cascade,
    constraint fk_book foreign key(book_id) references book(id)
    on delete cascade
    on update cascade,
    primary key(author_id,book_id)
    );
    
insert into author values
    (1,"egon"),
    (2,"alex"),
    (3,"wupeiqi"),
    (4,"yuanhao");

insert into book values
    (1,"python自动换开发"),
    (2,"liunx高级运维"),
    (3,"爬虫技术");

insert into author2book(author_id,book_id) values
    (1,1),
    (1,2),
    (2,1),
    (2,3),
    (3,1),
    (4,2);
    

 

#一对一

create table customer(
    id int primary key auto_increment,
    name char(20) not null,
    qq char(20) not null,
    phone char(20) not null
    );
    
create table student(
    id int primary key auto_increment,
    class_name char(20) not null,
    customer_id int unique, #该字段一定要是唯一的
    constraint fk_customer foreign key(customer_id) references customer(id) # 外键的字段也一定要保证唯一
    on delete cascade
    on update cascade
    );

insert into customer values
(1,"egon",123456,123456),
(2,"alex",2345,2345),
(3,"wupeiqi",3456,3456),
(4,"yuanhao",4567,4567);

insert into student(class_name,customer_id) values
("脱产3期",2),
("周末2期",1),
("周末2期",3);

 

 

 四，NOT NULL 和 DEFAULT 

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

DEFAULT默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

mysql> create table student(
    -> name varchar(20) not null,
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );

 

 

 五，UNIQUE KEY (UK)

============设置唯一约束 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)
);

联合唯一
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

 

 六，AUTO_INCREMENT

标识该字段的值自动增长（整数类型，而且为主键）

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

#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

 清空表

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

delete from t20 where id = 3;
insert into t20(name) values
("wxx");

truncate t20; #应该用它来彻底的清空表，

 

#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

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

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

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,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;

 

#了解补充
show variables like "auto_inc%"; #%指向任意字符

# 步长：
auto_increment_increment 默认为1
#起始偏移量
auto_increment_offset 默认为1

#设置步长
set session auto_increment_increment= 5; # 设置当前会话级别
set global auto_increment_increment=5; # 设置全局会话级别（对所有会话有效），必须退出重新加载一遍

# 设置起始偏移量
set global auto_increment_offset= 3
强调起始偏移量<=步长

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

 今天就到这里了，如果有什么不对的地方，一定要指出来，小木在这里谢谢了。