day46 mysql进阶
目录
一、约束条件
1 default默认值
# 补充知识点，插入数据时可以指定字段插入
create table t1(
id int,
name char(16)
);
insert into t1(name,id) values('hz',1);
default使用
# 创建一张表 内含一个默认字段
create table t2(
id int,
sex enum('male','female','others')# 这里用什么类型都可以 enum只是举例
)
# 只对这个表的id字段插入数据可以验证默认值
insert into t2 (id) values(1);
insert into t2 values(1,'male');
2 unique唯一
2.1 单列唯一
# 创建一张表 id字段不能重复
create table t3(
id int unique,
name char(16)
)
# 尝试对这个表插入重复数据
insert into t3 values(1,'hz'),(1,'lxt');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
# 报错提示不能重复添加id等于1的数据
2.2 联合唯一
# 联合唯一的本质是，每一条自己是可以重复，但是连在一起就是唯一的
# 例如ip+port 各自都可以重复，但是ip+port合在一起指定的是一个唯一的基于网络通信的应用程序
# 创建一个ip+port表
create table t4(ip int,port int,unique(ip,port));
# 尝试向表内插入相同的数据
insert into t4 values(1,2),(1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ip'
# 尝试向表内插入单列相同，其他列不同的数据
insert into t4 values(1,2),(1,3);
# 成功
3 primary key主键
3.1 主键的基本使用
# 单单从约束条件来看primary key的效果等价于not null+unique
# 非空且唯一
# 创建一个含有主键的表
create table t4 (
id int primary key,
name char(16)
);
# 向表内插入数据验证主键的特性
insert into t4 values(1,'hz');
insert into t4 values(null,'lxt');
# 报错 主键不能为空
insert into t4 values(1,'lxt');
# 报错 主键不能重复
3.2 主键的特性
特性：主键是innodb储存引擎组织数据的依据
innodb存储引擎在创建表的时候必须有主键，我们在不写主键的时候，mysql检索我们创建的字段，把一个设置了not null+unique约束的字段升级成主键。如果没有这种字段，mysql会自己创建一个隐藏的主键，这种情况就无法实现主键的高效率的特性了。
主键的目的是为了索引的方便，类似于书的目录，提高了我们检索数据的效率。
单个字段主键
create table t5(
id int primary key,
name char(16)
);
联合主键（多个字段连起来的主键，本质上还是一个主键）
create table t5(
ip int ,
port int,
name char(4),
primary key(ip,pory)
);
总结：主键是一种提升检索效率的结构，我们应该在建表时为唯一不重复的字段设置成主键
4 auto_increment自增
特性：顾名思义会自动增加的字段
# 当我们设置一些编号是依次增加的情况，我们人为的每次去输入就会显得很多余，这个时候用auto_increment约束就可以让这个字段自动增加1
# 注意：自增约束只能设置给主键
# 创建一个id自增的表
create table t6(
id int primary key auto_increment,
name char(16)
);
# 只对name字段插入数据检验id是否自增
inster into t6 (name) values('hz'),('lxt');
# 我们也可以指定id插入，这之后的数据会在我们这条数据上自增
# 删除数据不会影响后面的自增
补充
delete from t6 # 后面可以跟条件，不跟默认清空整个表，但是自增计数不会清空
truncate t6 # 清空表数据并且重置自增计数
二、表于表之间关系
引子：
我们在定义一张学生表的时候，会定义很多字段
学生：姓名，年龄，年级，班级，课程，成绩，排名....
定义一个如此庞大的表会出现以下几个问题
- 表的组织结构不清晰
- 浪费硬盘空间
- 数据的可扩展性很差（重要）
这种合在一起写就像在python中把所有的代码都写在一个py文件中，虽然都可以实现效果，但是可读性和可扩展性都非常差
所以我们要根据种类，把不同的字段类型分成不同的表
这里可以把学生表分成学生基本信息表，学生成绩表
1 外键
定义：是用来帮助我们建立表和表之间的关系的
foreign key
2 表关系
表与表之间只有四种关系：
- 一对多
- 多对多
- 一对一
- 无关
2.1 一对多关系
在刚开始学习表关系的时候，我们要站在不同的表上考虑和对方的关系。
举例：学生表和班级表
- 学生角度
- 一个学生不能在多个班级
- 班级角度
- 一个班级可以有多个学生
结论：学生表和班级表是一对多关系
代码实现
# 代码中外键写在哪张表？
# 写在“多”的那张表，也就是必须要后创建的表
# 班级里可以有0~无数个学生，但是学生必须有一个班级，所以要县创建班级表
create table student(
id int primary key auto_increment,
name char(16),
class_id int,
foreign key(class_id) references class(cls_id)
);
create table class(
cls_id int primary key auto_increment,
level char(16)
);
# 向班级表插入数据，再向学生表插入数据
insert into class (level) values('first'),('second');
insert into student (name,class_id) values('hz',1),('lxt',2),('xxq',2);
# 这里的班级必须要把班级内的同学都删了才能删除班级，这样会显得操作非常繁琐，而且数据之间不能做到同步跟新同步删除
# 设置级联跟新，级联删除
create table student(
id int primary key auto_increment,
name char(16),
class_id int,
foreign key(class_id) references class(cls_id)
on update cascade
on delete cascade
);
create table class(
cls_id int primary key auto_increment,
level char(16)
);
2.2 多对多关系
多对多关系举例图书表和作者表
一本书可以有多个作者，一个作者可以写多本书
此时不能按照上面的方式去写，多对多的外键关联必须再添加一个表，在这个表中存放对应关系
create table book(
id int primary key auto_increment,
name char(16)
);
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
);
2.3 一对一关系
一对一的外键写在哪里都可以，不过通常写在查询频率较高的表中
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 # 同步删除
)
3 修改表(了解)
# 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 旧字段名 新字段名 字段类型(宽度) 约束条件;
"""
4 复制表(了解)
"""
我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表; 不能复制主键 外键 ...
create table new_dep2 select * from dep where id>3;
