05--mysql约束条件 ； 表之间的关系 ； 修改表、复制表
目录
一、约束条件
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
not null与defaultdefault默认值
是否可空，null表示空，非字符串
not null ->不可空
null -> 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table t1(id int,name char(16));
insert t1(name id) values('egon',1);
create table t2(id int not null,name char(16),gender enum('male','female','others')default 'male');
unique唯一
# 单列唯一
create table t3(id int unique,name char(16));
insert t3 values(1,'egon'),(1,'jason');
insert t3 values(1,'egon'),(2,'jason');
# 联合唯一
ip和port 单个都可以重复，但是加载一起必须是唯一的
create table t4(id int,ip char(16),port int,unique(ip,port));
insert t4 values(1,'127.0.0.1','8080');
insert t4 values(2,'127.0.0.1','8081');
insert t4 values(3,'127.0.0.2','8080');
insert t4 values(4,'127.0.0.1','8080'); #报错
primary key主键
1，单单从约束效果上来看primary key 等价于 not null + unique非空且唯一！！！
create table t5(id int primary key);
insert t5 values(null); # 报错
insert t5 values(1),(1); #报错
insert t5 values(1),(2);
2，它除了有约束效果之外，它还是innodb存储引擎组织数据的依据
innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录，能够帮助提示查询效率并且也是建表的依据
# 1，一张表中有且只有一个主键，如果你没有设置主键，那么他会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(id int,name char(16),age int not all unique,addr char(32) not null unique);
# 2,如果表中没有主键也没有其他任何的非空且唯一字段，那么innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用它，就无法提示查询速度
# 3，一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段作为主键
#单个字段主键
create table t5(id int primary key,name char(16));
# 联合主键（了解）
create table t4(id int,ip char(16),port int,primary key(ip,port));
···也就意味着以后我们在创建表的时候id字段一定要加primary key
auto_increment自增
当编号特别多的时候 人为的去维护太麻烦
create table t8(id int primary key auto_increment,name char(16));
insert t8(name) values('egon'),('lili'),('jason');
# 注意auto_increment通常加在key主键上 不能给普通字段加
结论
以后在创建表的id（数据的唯一标识id，uid，sid）字段的时候要写
id int primary key auto_increment
补充：
delete from 在删除表中数据的时候，主键的自增不会停止
truncate t1 清空表数据并且重置主键
二、表与表之间建立关系
定义一张员工表，表中有很多字段
id name gender dep_name dep_desc
# 1 该表的组织结构不是很清晰（可忽视）
# 2 浪费硬盘空间（可忽视）
# 3 数据的扩展性极差（无法忽视）
# 如何优化？（类似于将所有的代码写在了一个py文件中）
拆分表
外键
外键就是用来帮助我们建立表与表之间关系的
foregin key
1.表与表的关系
一对多
判断表与表之间关系的时候，前期不熟悉的情况下，一定要换位思考，分别站在2张表的角度考虑
员工表与部门表为例
先站在员工表，思考一个员工能否对应多个部门（一条员工数据能否对应多条部门数据） 不能！
再站在部门表，思考一个部门能否对应多个员工（一个部门数据能否对应多条员工数据） 能！
得出结论：员工表与部门表是单向的一对多，所以关系就是一对多(不能说多对一，也没有多对一)
1,一对多表关系 外键字段在多的一方
2，在创建表的时候，一定要先建被关联表
3，在录入数据的时候，也必须先录入被关联表
foregin key
# sql语句建立表关系、
#多对一实例学生与班级的关系
##创建被关联表->班级表
create table class(
id int primary key auto_increment,
name varchar(20),
room int
);
##创建关联表->学生表
create table student(
id int primary key auto_increment,
name varchar(16),
age int,
gender enum("male","female"),
class_id int,
foreign key(class_id) references class(id) #设置外键
on update cascade #级联更新
on delete cascade #级联删除
);
##被关联表录入数据
insert class(name,room) values
("py1期",403),
("linux1",503),
("linux2",603);
##关联表录入数据
insert student(name,age,gender,class_id) values
("egon",18,"male",1),
("tom",19,"male",1),
("jack",20,"male",1),
("lili",19,"female",2),
("lxx",20,"female",2);
insert student(name,age,gender,class_id) values
("kk",21,"female",4444); # 被关联表没有4444的id，因此报错
insert student(name,age,gender,class_id) values
("kk",21,"female",3); # 正确的加入学生信息
多对多
#多对多：必须建立中间表，双向的fk
##创建多对多，作者与书本的实例
##创建书本表
create table book(
id int primary key auto_increment,
name varchar(20)
);
##创建作者表
create table author(
id int primary key auto_increment,
name varchar(16)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id) on delete cascade on update cascade,
foreign key(author_id) references author(id) on delete cascade on update cascade
);
#记录插入四个作者，id依次排开
insert into author(name) values('aaa'),('bbb'),('ccc'),('ddd');
#每个作者与自己的代表作如下
1 aaa:
1 九阳神功
2 九阴真经
3 九阴白骨爪
4 独孤九剑
5 降龙十巴掌
6 葵花宝典
2 bbb:
1 九阳神功
6 葵花宝典
3 ccc:
4 独孤九剑
5 降龙十巴掌
6 葵花宝典
4 ddd:
1 九阳神功
#！此步骤插入书本名字记录省略
#共同表的记录写入：
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
#中间那一张存放关系的表，对外关联的字段可以联合唯一
一对一
# 如果一个表的字段特别多，每次查询又不是所有的字段都能用到，将表一分为二
# 一对一 外键字段建在任意一方都可以，但是推荐建在查询频率比较高的表中
#实例
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学生，即一对一的关系
关联方式：foreign key+unique
#创建客户表
create table customer(
id int primary key auto_increment,
name varchar(20),
tel int
);
#创建学生表
create table student(
id int primary key auto_increment,
class varchar(10),
customer_id int unique,
foreign key(customer_id) references customer(id) on update cascade on delete cascade
);
2.修改表
mysql对大小写是不敏感的
1，修改表名
alter table 表名 rename 新表名；
2，增加字段
alter table 表名 add 字段名 字段类型（宽度） 约束条件；
alter table 表名 add 字段名 字段类型（宽度） 约束条件 first； # 字段在表前
alter table 表名 add 字段名 字段类型（宽度） 约束条件 after； # 字段在表尾
例： alter table t1 add name varchar(20) not null,add age int not nll;
alter table t1 add haha varchar(9) after id; #把haha字段加在id字段后面
3，删除字段
alter table 表名 drop 字段名；
4，修改字段
alter table 表名 modify 字段名 字段类型（宽度） 约束条件；
alter table 表名 change 旧字段名 新字段名 字段类型（宽度）约束条件；
例： alter table t1 change name new_name varchar(20) not null;
3.复制表
我们sql语句查询的结果其实也是一张虚拟表
create table 表名 select * from 旧表； # 其不能复制主键、外键..
create table new_dep2 select * from dep where id>3; #可以添加条件
#实例1
只复制表结构
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#实例2
1.先创建库db01，表t1
create database db01;
use db01;
create table t1(
id int primary key auto_increment,
name varchar(15),
email varchar(30),
age int,
born_year year,
reg_time datetime
);
insert t1(name,age,email,born_year,reg_time) values
("xx",18,"123@qq.com",1993,now()),
("yy",28,"666@qq.com",2001,now());
2.创建库db02,表t2
create database db02;
use db02;
create table t2(
id int primary key auto_increment,
name varchar(15),
email varchar(30),
reg_time datetime
);
#复制记录
insert db02.t2(id,name,email,reg_time) select id,name,email,reg_time from db01.t1;
4.删除表
drop table 表名；
