今日内容概要

• 约束条件
• 表与表之间建立关系
• 修改表的完整语法大全
• 复制表

今日内容详细

约束条件

default默认值

#  插入数据的时候可以指定字段
create table t1(
    id int,
    name char(16));
insert into t1(name,id)values('jason',1);

create table t2(
    id int,
    name char(16),
    gender enum('male','female','others')default 'male');
insert into t2(id,name) values(1,'张三');
insert into t2 values(2,'李四','female')；

unique唯一

l# 单列唯一
create table t3(
    id int unique,
    name char(16));
insert into t3 values(1,'wxx'),(1,'lxx');  报错
insert into t3 values(1,'wxx'),(2,'lxx');

# 联合唯一
"""
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);  报错

primary key主键

"""
1、从约束效果上来看primary key等价于not null + unique
非空且唯一！

"""
create table t5(id int primary key);
insert into t5 values(null);  报错
insert into t5 values(1),(1);  报错
isnert into t4 values(1),(2);

"""
2、还是Innodb存储引擎组织数据的依据
Innodb存储引擎在船建表的时候必须要有primary key
因为它类似于书的目录，能够帮助提示查询效率并且也是建表的依据

"""
# 1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(id int,
               name cahr(16),
               age int not null 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 t7(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 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);  报错

# 补充
delete from t1  删除表中数据后 主键的自增不会停止

truncate t1  清空表数据并且充值主键

表与表之间建立关系

# 1 组织结构清晰
# 2 节省硬盘空间
# 3 数据的扩展性好

一对多关系

"""
判断表与表之间的关系的时候 换位思考 分别站在两张表的角度考虑

员工表与部门表为例
    先站在员工表
        思考一个员工能否对应多个部门
    在站在部门表
        思考一个部门能否对应多个员工
    得出结论
        员工表与部门表是单向的一对多

"""
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','other')default 'male',
                dep_id int,
                foreign key(dep_id) references dep(id));
insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('指挥部','指挥方针'),('技术部','技术部分')；
insert into emp(name,dep_id) values('wxx',2),('lxx',1),('zxx',1),('hxx',3);


# 修改dep表里面的id字段
updatae 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));
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('教学部','教书育人'),('指挥部','指挥方针'),('技术部','技术部分')；
insert into emp(name,dep_id) values('wxx',2),('lxx',1),('zxx',1),('hxx',3);

多对多

"""
图书表和作者表
"""
create table book(id int primary key auto_increment,
                 title varchar(32),
                 price int,
                 author_id int,
                 foreign key(author_id) references author(id)
                 on update cascade  # 同步更新
                 on delect cascade  # 同步删除
 );
create table autor(id int primary key auto_increment,
                  name varchar(32),
                  age int,
                  book_id int,
                  foreign key(book_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 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 # 同步删除                        
)；

一对一

一对一 外键字段在任意一方都可以 但是推荐建在查询频率比较高的表中
create table authordatail(id int primary ket 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(quthordetail_id) references authordetail(id)
                   on update cascade  # 同步更新
                   on delete cascade  # 同步删除
                   )

修改表

"""
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 旧字段名 新字段名 字段类型(宽度) 约束条件;
    

"""

复制表

create table 表名 select * from 旧表; 不能复制主键 外键...

create table new_dep2 select * from dep where id>3;