字段详细操作、多表关系、外键关联和级联关系以及增、删、改的操作 0924 字段详细操作、多表关系、外键关联和级联关系以及增、删、改的操作

一、字段详细操作

create table tf1(
	id int primary key auto_increment,
    x int,
    y int
);

# 修改
mysql> alter table tf1 modify x char(4) default '';
mysql> alter table tf1 change y m char(4) default '';

# 增加
mysql> alter table 表名 add 新字段名 类型(长度) 约束;   # 在所有字段末尾增加
eg> alter table tf1 add z int unsigned; 

mysql> alter table 表名 add 新字段名 类型(长度) 约束 first; # 在所有字段头部增加
eg> alter table tf1 add a int unsigned first;

mysql> alter table 表名 add 新字段名 类型(长度) 约束 after 旧字段名; # 在某个字段后面增加
eg> alter table tf1 add d int unsigned after x; 

mysql> alter table 表名 drop 字段名; # 删除字段
eg> alter table tf1 drop a;

二、多表关系

"""
一对一：丈夫-妻子，用户-身份证，作者-作者详情
一对多（多对一）：部门-员工，班级-学生，书-出版社
多对多：老师-班级，课程-学生，出版社-作者
"""

# 书-出版社-作者-作者详情 外键分布
# 外键是建立表与表关联的字段，通常一个表的外键是另一个表的主键（唯一键也可以）

1、一对一：外键在任何一方都可以，此时外键要设置唯一键
"""
作者(author)：id,name,sex,age,mobile
作者详情(author_detail)：id,info,address,author_id(外键)
--------------------------------------------------
作者(author)：id,name,sex,age,mobile,detail_id(外键)
作者详情(author_detail)：id,info,address
"""

2、一对多：外键必须放在多的一方，此时外键值不唯一
"""
书(book)：id,name,price,publish_id(外键 )
出版社(publish)：id,name,address,phone
"""

3、多对多：一定要创建第三张表（关系表），每一个外键值不唯一，但可以多个外键建立联合唯一
"""
作者(auther):id,name,age
出版社(publish):id,name,address
作者与出版社关系表：
id      author_id     publish_id
1           1             1
2           1             2
3           2             1
4           2             2
"""

三、外键关联与级联关系

# 作者(author)：id,name,sex,age,mobile,detail_id(外键)
# 作者详情(author_detail)：id,info,address

1、外键的字段名可以自定义（名字随意），通常命名规范（关联表_关联字段）
2、外键要通过 foreign key 语法建立表与表之间的关联
3、foreign key(所在表的外键字段) references 关联表(关联字段)
# eg: foreign key(detail_id) references author_detail(id)

4、级联关系
# 	级联更新：on update cascade
#	级联删除：on delete cascade

重点：外键字段本身可以唯一或不唯一，但是外键关联的字段必须唯一。

四、一对一：没有级联关系

# 作者详情表(被关联表)
create table author_detail(
	id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

# 作者表(关联表)
create table author(
	id int primary key auto_increment,
    name varchar(64) not null, 
    mobile char(11) unique not null,  # 唯一键，不能为空
    sex enum('男','女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
);
# 必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql> insert into author_detail(info,address) values('Tom_info','Tom_address');
msyql> insert into author(name,mobile,detail_id) values('Tom','12233445566',1); 
mysql> insert into author_detail(info,address) values('Bob_info','Bob_address');
msyql> insert into author(name,mobile,detail_id) values('Bob','11223344556',2); 

# 修改关联表author
mysql> insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql> update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
# 删除关联表author
mysql> delete from author where detail_id=3;  # 直接删除

# 修改被关联表author_detail
mysql> update author_detail set id=10 where id=1;  # 无法修改
# 删除被关联表
mysql> delete from author_detail where id=1;  # 无法删除

"""
总结：
没有级联关系下：
	增加：先增加被关联表记录，再增加关联表记录
	删除：先删除关联表记录，再删除被关联表记录
	更新：关联与被关联表都无法完成关联的外键和主键的数据更新--（如果被关联表记录没有被绑定，可以修改）
"""

五、一对一：有级联关系

# 作者详情表(被关联表)
create table author_detail(
	id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

# 作者表(关联表)
create table author(
	id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,  # 唯一键，不能为空
    sex enum('男','女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id) 
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

# 增加数据，必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql> insert into author_detail(info,address) values('Tom_info','Tom_address');
msyql> insert into author(name,mobile,detail_id) values('Tom','12233445566',1); 
mysql> insert into author_detail(info,address) values('Bob_info','Bob_address');
msyql> insert into author(name,mobile,detail_id) values('Bob','11223344556',2); 

# 修改关联表author
mysql> update author set detail_id=3 where detail_id=2;  # 失败，3详情不存在
mysql> insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql> update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
# 删除关联表author
mysql> delete from author where detail_id=3;  # 直接删除

# 修改被关联表author_detail
mysql> update author_detail set id=10 where id=1;  # 级联修改，同步更新关联表外键
# 删除被关联表
mysql> delete from author where detail_id=10;  # 可以删除，对被关联表无影响
msyql> insert into author(name,mobile,detail_id) values('Tom','12233445566',10);  
mysql> delete from author_detail where id=10;  # 可以删除，将关联表的记录级联删除掉

六、一对多：级联关系

# 一对多：外键必须放在多的一方，此时外键值不唯一
# 出版社(publish)：id,name,address,phone
create table publish(
	id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 书(book)：id,name,price,publish_id(外键)
create table book(
	id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5,2) default 0,
    publish_id int,      # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id) 
    on update cascade 
    on delete cascade
);

1、增加数据
# 先增加被关联表publish数据
mysql> insert into publish(name,address,phone) values('人民出版社','北京','010-110'),
('西交大出版社','西安','010-119'),('老男孩出版社','上海','010-120');
# 再增加关联表book数据
mysql> insert into book(name,price,publish_id) values('西游记',6.66,1),
('东游记',8.66,1),('python从入门到放弃',2.66,2),('论程序员修养之道',3.66,3),
('好好活着',88.88,3);

2、更新数据：
# 直接更新被关联表(publish)的主键，关联表(book)的外键会级联更新
mysql> update publish set id=10 where id=1;
# 直接更新关联表(book)的外键，修改的值对应的被关联表(publish)的主键如果存在，可以更新成功，反之失败
mysql> update book set publish_id=2 where id=4;  # 成功
mysql> update book set publish_id=1 where id=4;  # 失败

3、删除数据
# 删除被关联表，关联表会被级联删除
mysql> delete from publish where id=2;
# 删除关联表，被关联表不会发生变化
mysql> delete from book where publish_id=3;

# 假设书与作者也是一对多关系，一个作者可以出版多本书
create table book(
	id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5,2) default 0,
    publish_id int,      # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id) 
    on update cascade 
    on delete cascade
    
    # 建立与作者一对多的外键关联
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade
);

七、多对多：级联关系

# 多对多：一定要创建第三张表（关系表），每一个外键值不唯一，但可以多个外键建立联合唯一

# 作者(auther):id,name,age
create table author(
	id int primary key auto_increment,
    name varchar(64),
    age int unsigned default 0
);

# 出版社(publish):id,name,address
create table publish(
	id int primary key auto_increment,
    name varchar(64),
    address varchar(256)
);

# 作者与出版社关系表：id,author_id,publish_id
create table author_publish(
	id int primary key auto_increment,
    # 关系表一定有多个外键关联着多张表
    # 关联作者表
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade,
    # 关联出版社表
    publish_id int,
    foreign key(publish_id) references publish(id) 
    on update cascade 
    on delete cascade,
    # 建立两个字段的联合唯一
    unique(author_id,publish_id)
);

# 注意：关系表关联着作者和出版社两张表，在表结构上作者与出版社两表间没有任何关系

1、增：两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行关系匹配
mysql> insert into author(name,age) values('ruakei',67),('egon',76),('nick',38);
mysql> insert into publish(name,address) values('老男孩出版社','上海'),('小女孩出版社','北京');
# 增加关系表数据
mysql> insert into author_publish(author_id,publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

"""
1、操作关系表：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响
2、操作两张被关系表：
	增：不会影响关系表
	mysql> insert into publish(name,address) values('西交大出版社','西安');
	改：关系表都会级联更新
	mysql> update publish set id=10 where id=1;
	删：关系表都会级联删除
	mysql> delete from author where name='ruakei';
"""