46----MySQL表的进阶

目录

• 一 约束条件
  • 1.1 default默认值
    • 1.1.1 补充知识点
    • 1.1.2 默认值---default
    • 1.1.3 unique唯一
    • 1.1.4 primary key主键
• 二 表与表之间建立关系（约束）
  • 2.1 excel模拟建立表存在的问题
  • 2.2 优化2.1的问题
  • 2.3 外键foreign key
    • 2.3.1 外键
    • 2.3.2 表关系
    • 2.3.3 表关系总结
• 三 修改表的完整语法
• 四 复制表

一 约束条件

1.1 default默认值

1.1.1 补充知识点

插入数据的时候可以指定字段

create table t1(
	id int,
	name cahr(16)
);
insert into t1(name,id) values ('jason',1);

1.1.2 默认值---default

create table t(
	id int,
	name char(16) not null,
	gender enum('male','female','others') default 'male'
);
insert into t(id,name) values(1,'jason');
insert into t(id,name,gender) values(2,'egon','female')

1.1.3 unique唯一

• 单列唯一

create table t(
	id int unique,
	name char(16)
);
insert into t values(1,'jsaon'),(1,'egon');
insert into t values(1,'jsaon'),(2,'egon');

• 联合唯一

例子：ip和port单个都可以重复，但是加起来必须是唯一的

create table t(
	id int unique,
	ip char(16),
	port int
	unique(ip,port)
);

insert into t values(1,'127.0.0.1',8080);
insert into t values(2,'127.0.0.1',8081);
insert into t values(3,'127.0.0.2',8080);
insert into t values(4,'127.0.0.1',8080);

1.1.4 primary key主键

• 1.单从约束效果上来看primary key等价于 not null + unique

非空且唯一！！！

create table t(id int primary key);

insert into t values(null);  # 报错
insert into t values(1)(1);  # 报错

• 2. 除了有约束效果之外，还是innodb存储引擎组织数据的依据.

     innodb存储引擎在创建表的时候必须要有primary key.

     因为它类似与书的目录，能够帮助提示查询效率并且也是建表的依据

     注意：

#一张表中有且只有一个主键，如果你没有设置主键，那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t(
	id int,
    name char(16),
    age int not null unique,
    addr char(32) not null unique
);
desc t;

如果表内没有主键也没有其他任何的非空且唯一的字段，那么innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味无法使用，就无法提示查询速度

一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段作为主键

# 单个主键
create table t(
    id int primary key
    name char(4)
);

# 联合主键
create table t(
    ip char(16),
    port int,
    primary key(ip,port)
);

• 小拓展---auto_increment自增

注意：auto_increment通常加在主键上的，不能给普通字段加

create table t(
id int primary key auto_increment,
name char(4)
);
insert into t(name) values('jason'),('egon'),('tank');

delete from t;在删除表中数据的时候，主键的自增不会停止

truncate t;清空表数据并重置主键。

• 总结

在创建表的时候id(数据的唯一标识)字段一定要加primary key 以及auto_increment

二 表与表之间建立关系（约束）

2.1 excel模拟建立表存在的问题

• 1.组织结构不是很清晰
• 2.重复数据，浪费硬盘空间
• 3.数据的扩展性极差，无法忽视的问题

2.2 优化2.1的问题

• 上述问题就类似与将所有代码都写在了一个py文件中
• 解决方案

将一个表拆分成不同的表,在两个表中建立联系

2.3 外键foreign key

2.3.1 外键

就是用来帮助我们建立表与表之间的关系

2.3.2 表关系

在确定表与表之间的关系的时候，一定要换位思考
分别站在两张表的角度考虑

• 一对多关系---员工与部门表

先站在员工表：
	员工表里面的一个员工能否对应部门表里面的多个部门 ---不能
再站在部门表
	部门表里面的一个部门能否对应员工表里面的多个员工----可以
	
结论：员工表与部门表只是单向的一对多成立，那么员工表和部门表就是 一对多 表关系

sql语句建立一对多的表关系

• 在MySQL的关系在没有多对一的概念，一对多和多对一都是一对多。

• 一对多表关系，外键字段建在数据多的一方

• 在创建表的时候， 一定要先建被关联表

• 在录入数据的时候也必须先录入被关联表

# 部门表
create table dep(
	id int primary key auto_increment,
	def_name char(16),
	def_desc char(32)
);

# 员工表
creat table emp(
	id int primary key auto_increment,
    name char(16);
    gender enum('male','famle') defalut 'male',
    dep_id int,
    foreign key(dep_ip) references dep(id)
)

insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('外交部','多人外交')；
insert into emp(name,dep_id) values('jason',1),('tank',2)

# 修改emp里面的dem_id字段或者修改tep表的id字段
上述代码的情况下是无法进行修改的，因为两个表是相互关联的。
update dep set id=200 where id=2;  # 报错

同步修改数据的方式
1 先删除教学部对应的员工数据，之后删除部门
	操作太过繁琐
2 真正做到数据之间有关系
	更新同步更新
	删除就同步删除

# 级联更新(同步更新) 级联删除（同步删除）
# 部门表
create table dep(
	id int primary key auto_increment,
	def_name char(16),
	def_desc char(32)
);

# 员工表
creat table emp(
	id int primary key auto_increment,
    name char(16);
    gender enum('male','famle') defalut 'male',
    dep_id int,
    foreign key(dep_ip) references dep(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
)
insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('外交部','多人外交')；
insert into emp(name,dep_id) values('jason',1),('tank',2)

update dep set id=200 where id=1;

• 多对多关系---书籍表与作者表

先站在书籍表
	一本书可不可以有多个作者---可以
	一个作者可不可以写多本书---可以
结论：
	书籍表与作者表是双向的一对多
	那么表关系就是多对多

sql语句建立多对多关系

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
create table auth(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(auth_id) references book(id)
    on update cascade
    on delete cascade
);

按照上述的方式创建表，一个都别想成功
针对多字段表关系，不能在两张原有的表中创建外键
需要单独开设一张表，专门用来存储两张表数据之间的关系

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int,
  
);
create table auth(
	id int primary key auto_increment,
    name varchar(32),
    age int,
   
);
create table book2auth(
	id int primary key auto_increment,
    auth_id int,
    book_id int,
    foreign key(auth_id) references auth(id)
    on update cascade
    on delete cascade,
    
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

• 一对一关系

如果一个表的字段特别多，每次查询又不是所有的字段都用的到
将表一分为二
例如用户信息表
id name age addr hobby .......
用户表
	用户表
		id name age
	用户详情表
		phone addr hobby......
		
站在用户表：
	一个用户能否对应多个用户详情 ---不能
站在详情表
	一个详情表能否书育多个用户
	
结论：单向的一对多都不成立，那么两者之间的表关系就是一对一或者没有关系

sql语句建立一对一表关系

一对一关系：外键关键字建立在任意一方都可以，但是推荐建在查询频率较高的表中
create table authordetail(
	id int primary key auto_increment,
	phone int,
	addr varchar(64)
);

create table auth(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetali_id int unique,
    foreign key(authordetali_id) references authordetali(id)
    on update cascade
    on delete cascade
);

• 没有关系

2.3.3 表关系总结

表关系建立用到foreign key

	一对多
		外键字段建在多的一方
	多对多
		自己开设第三张存储
	一对一
		建在任意一方都可以 但是推荐你建在查询频率较高的表中

判断表之间关系的方式
	换位思考！！！
		员工与部门
	
		图书与作者
	
		作者与作者详情

三 修改表的完整语法

MySQL对大小写不敏感

• 修改表名

alter table 表名 rename 新表名；

• 增加字段

alter table 表名 add 字段名 字段类型（宽度） 约束条件；
alter table 表名 add 字段名 字段类型（宽度） 约束条件 first;
alter table 表名 add 字段名 字段类型（宽度） 约束条件 after 字段名;

• 删除字段

alter table 表名 drop 字段名；

• 修改字段

alter table 表名 modify 字段名 字段类型（宽度） 约束条件；
alter table 表名 change 旧字段名 新字段名 字段类型（宽度） 约束条件；

四 复制表

• sql语句查询的结果其实也是一张虚拟表

# 不能复制主键 外键等...,只能复制表结构和数据
creat table 新表名 select * from 被复制表名；
# 可以加条件
creat table 新表名 select * from 被复制表名 ;