4. 主键外键以及表操作

约束（默认、唯一、主键、自增）

'''
前面说了几个约束条件：
	not null
	unsigned
	zerofill
下面详细介绍一下约束条件：
'''
1 default 默认值
create table t2(
	id int,
    name char(16),
    gender enum('male','female','others') default 'male'
);
insert into t2(id,name) values(1,'jason');  # 在插入的时候可以在括号中指定字段
insert into t2 values(2,'egon','female');

2 unique 唯一: 指的是字段中的内容不能重复（例如学号），存在两种唯一的情况
# 单列唯一：
create table t3(
	id int unique,
    name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');
insert into t3 values(1,'jason'),(2,'egon');

# 联合唯一
"""
ip和port
单个都可以重复 但是两个组合在一起必须是唯一的
"""
create table t4(
	id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080);  报错

3 primary key主键
# 单单从约束效果上来看primary key等价于not null + unique非空且唯一
create table t5(id int primary key);
insert into t5 values(null);  报错
insert into t5 values(1),(1);  报错
insert into t5 values(1),(2); 
# 它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据，Innodb存储引擎在创建表的时候必须要有primary key，因为它类似于书的目录，能够帮助提高查询效率并且也是建表的依据
"""
# 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(
	id int,
    name char(16),
    age int not null unique,
    addr char(32) not null unique
);

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

# 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t5(
    id int primary key
	name char(16)
);
# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t7(
    ip char(16),
    port int,
    primary key(ip,port)
);

"""
# 以后我们在创建表的时候id字段一定要加primary key

3 auto_increment自增
# 当编号特别多的时候，人为的去添加太麻烦，这里就需要Excel那种自动填充的功能
create table t8(
	id int primary key auto_increment,
    name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');

# 注意auto_increment通常都是加在主键上的，不能给普通字段加
create table t9(
	id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
    
"""
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
"""

补充

delete from t1  # 删除表中数据后 主键的自增不会停止
truncate t1  # 清空表数据并且重置主键

外键与建立表关系

"""
1 外键
就是用来建立表与表之间关系的，关键字是foreign key
2 表关系
表与表之间最多只有四种关系：一对多关系（MySQL中没有多对一）、多对多关系、一对一关系、没有关系
举例：一对多就像是班级与学生，班级可以有多个学生，但是学生不能有多个班级，多对多就像选课，一个课程有多个学生，一个学生可以选多个课程，一对一最好解释，就像学生与学号的关系
"""
##########  一对多关系  ##########
foreign key
	1 一对多表关系   外键字段建在多的一方
    2 在创建表的时候 一定要先建被关联表 
    3 在录入数据的时候 也必须先录入被关联表
# SQL语句建立表关系
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

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 dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

# 修改dep表里面的id字段
update dep set id=200 where id=2;  不行
# 删除dep表里面的数据
delete from dep;  不行   
# 真正做到数据之间有关系————级联
	更新就同步更新
    删除就同步删除
"""
级联更新   >>>   同步更新
级联删除   >>>   同步删除
"""
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

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 dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

##########  多对多  ##########
"""
针对多对多字段表关系 不能在两张原有的表中创建外键（会直接失败）
需要你单独再开设一张 专门用来存储两张表数据之间的关系，再从第三个表上分别级联
"""
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 book_author(
	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  # 同步删除
);

##########  一对一  ##########
'''id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
这有点像什么呢，就是进入QQ时会先给你展示用户的昵称，性别，但是出生年月什么的不会先显示，等你点击查询的时候才会将生日、出生地、出生年月、爱好什么的显示出来，实际上这都是个人的信息，但是还是将他们分开，实际上这就是一对一的关系，你的信息只对应你的身份（或者说是账号）
'''
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
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  # 同步删除
)

修改表

# 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 旧表;  
create table new_dep2 select * from dep where id>3;