表与表之间的关系,修改表,复制表

表与表之间的关系

外键

外键就是从来帮助我们建立表与表之间关系的
foreign key

表关系

表与表只有四种关系
一对多关系
多对多关系
一对一关系
多对一关系

一对多

SQL语句建立表关系
1. 一对多表关系 外键字段建在多的一方
2. 在创建表的时候 一定要先建被关联表
3. 在录入数据的时候 也必须先录入被关联表


create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限'); 

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

#修改emp里面的dep_id字段或者dep表里的id字段
update dep set id =200 where id=2; #不行,外键限制
#删除dep表里的数据
delete from dep; #不行

#可行的修改和删除的方法:
#1.先删除教学部对应的员工数据,再删除部门
	操作太过繁琐
#2.真正做到数据之间有关系
	同步更新,删除
引出概念
#级联更新,级联删除
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限'); 

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade #同步更新
    on delete cascade #同步删除
);
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

update dep id=200 where id=2;
delete from dep where id=1;

多对多

图书与作者
针对多对多字段表关系 不能再两张原有的表中创建外键
需要你单独开设一张 专门用来存储两张表之间的数据关系

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int
);
create table author(
	id int primary key auto_increment,
    name varchar(32),
    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
);

insert into book(title,price) values('jpm',6699),('lzzy',9966),('tpt',6666);
insert into author(name,age) values('jason',18),('egon',73);
insert into book2author(author_id,book_id) values(1,1),(1,2),(2,3);
#当直接删除author里面的作者的时候
#中间表对应的数据也变化
delete from author where id=1;

一对一

一对一外键字段建在任意一方都可以,推荐建在查询频率高的表中

create table authordetail(
	id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id)
    on update cascade
    on delete cascade
);

总结

表关系的建立需要用到foreign key
一对多 外键字段建在多的一方
多对多 自己开设第三张存储
一对一 建在任意一张都可以,但是推荐你建在查询频率较高的表中

修改表

#mysql 对大小写不敏感

#1修改表名
alter table 表名 rename 新表名;
#2增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件; #默认最后
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; #添加在最前面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; #跟在谁的后面
#3删除字段
alter table 表名 drop 字段名;
#4修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

复制表

#sql语句的结果其实也是一张虚拟表
create table 新表 select * from 旧表; #不能复制主键外键索引,只复制表结构和数据