mysql03--约束关系、表关系

每日测验

• 你都了解哪些MySQL存储引擎，简单说说它们的特点
• 创建表的完整语法及注意事项
• MySQL基本数据类型有哪些

昨日内容回顾

• 存储引擎

  # 针对不同的数据应该有不同的处理机制
  
  存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
  因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）
  
  # 查看MySQL所有的存储引擎
  show engines;
  
  Innodb
      MySQL5.5版本之后默认的存储引擎
      支持事务 行锁 外键    数据更加的安全
      创建表会生成两个文件
          表结构文件
          表数据文件
  MyIsam
      MySQL5.5版本之前的默认的存储引擎
      不支持事务 表锁设计  但支持全文索引	它虽然在数据安全上没有Innodb可靠 但是查询速度上较Innodb更快
      创建表会生成三个文件
          表结构文件
          表数据文件 
          表索引文件
  memory
      内存引擎
      临时数据存储 
      创建表会生成一个文件
          表结构文件
  blackhole
      黑洞
      创建表会生成一个文件
          表结构文件
  

• 创建表的完整语法

  # 语法
  create table 表名(
      字段名1 字段类型(宽度) 约束条件,
      字段名2 字段类型(宽度) 约束条件,
      字段名3 字段类型(宽度) 约束条件
  )
  
  # 注意
    1 在同一张表中字段名不能重复
    2 宽度和约束条件是可选的 字段名和字段类型是必须的
    3 最后一个字段后面不能加逗号
  
  
  # 宽度一般情况下是对存储数据的限制
  # 约束条件可以有多个 是额外的限制
  

• 严格模式

  # 使用数据库的时候 应该尽量让数据库少干活！！！
  
  show variables like '%mode';
  
  set session
  set global sql_mode = 'STRICT_TRANS_TABLES';
  

• 基本数据类型

  # 整型
      TINYINT INT BIGINT
      默认都是有符号的
  	
      int(4)
      针对整型括号内的数字并不是用来限制存储的
      不够四位默认用空格填充
      够四位有几位存几位 
  	
      zerofill
  	
      # 总结: 以后在定义整型字段的时候 其实都不需要自己加宽度
        因为整型都自带宽度 完全足够展示所有的数字
  	
  	
  # 浮点型
  
      float(255,30)
      double(255,30)
      decimal(65,30)
      只要是浮点型 括号后面有两个数字
          第一个数字表示的总位数
          第二个数字表示的小数位
  	
      精确度
      float < double < decimal
  	
      # 扩展:其实在实际生产中 很多看似需要用整型或者浮点型存储的数据
        内部可能都是用的字符类型存储
  	
  # 字符类型
  
      char(4)  定长
          只能存储最大四个字符 超出报错 不超默认用空格补全
      varchar(4)  变长
          只能存储最大四个字符 超出报错 不超有几个存几个
  	
      char Vs varchar
      char
          浪费空间
          存取方便 简单暴力 直接按照固定的位数存取即可
  	
      varchar
          节省空间
          但是相较于char 它的存取速度较慢
          因为它在存取数据的时候需要有一个 报头
  	
      以前用char很多 现在varchar很多
  	
  # 时间类型
  
      date  年月日
      datetime  年月日时分秒
      time  时分秒
      year  年(有范围限制)
  
  # 枚举与集合类型
  
      枚举enum
          多选一  
      集合set
          多选多(也可以单选)
          
      gender enum('male','female','others')
      hobby set('read','sangna','DBJ','hecha')
      你的数据只能来源于字段提供的选择项 不能凭空捏造
  

• 约束条件

  zerofill  用0填充
  unsigned  无符号
  not null  不为空
  

今日内容详细

一、约束条件

1.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,'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主键

# 1 作用

1 单单从约束效果上来看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); 

2 它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据

    
    
# 2 主键设置

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

4.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
);

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  清空表数据并且重置主键

二、表与表之间建关系

# 定义一张员工表 employee 表中有很多字段
  id name gender dep_name dep_desc

    
# 1 该表的组织结构不是很清晰(可忽视)
  字段比较多，且分不清重点员工信息还是部门信息

# 2 浪费硬盘空间(可忽视)
  若数据特别多，有同个部门的信息，就一直在重复

# 3 数据的扩展性极差(无法忽视的)
  若修改部门信息，则全部相关的数据都需要修改

    
# 如何优化？  上述问题就类似于你将所有的代码都写在了一个py文件中

将员工表拆分： 员工表和部门表

1.外键

# 外键就是用来帮助我们建立表与表之间关系的
  foreign key

2.表关系

# 表与表之间最多只有四种关系
一对多关系
    在MySQL的关系中没有多对一一说
    一对多 多对一 都叫一对多！！！
    
多对多关系

一对一关系

没有关系

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','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;  不行

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

4.多对多

"""
图书表和作者表
"""
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 delete cascade  # 同步删除
);
create table author(
    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  # 同步删除
);

5.一对一

id name age addr phone hobby email........
# 如果一个表的字段特别多 每次查询又不是所有的字段都能用得到

将表一分为二  
    用户表
        用户表
            id name age
        用户详情表
            id addr phone hobby email........
	
    站在用户表
        一个用户能否对应多个用户详情   不能！！！
    站在详情表
        一个详情能否属于多个用户      不能！！！
		
    # 结论:单向的一对多都不成立 那么这个时候两者之间的表关系
        就是一对一
        或者没有关系(好判断)

客户表和学生表
    在你们报名之前你们是客户端
    报名之后是学生(期间有一些客户不会报名)


# 一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
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  # 同步删除
)

总结

# 表关系的建立需要用到foreign key
    一对多
        外键字段建在多的一方
    多对多
        自己开设第三张存储
    一对一
        建在任意一方都可以 但是推荐你建在查询频率较高的表中

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

三、修改表(了解)

# MySQL对大小写是不敏感的


# 1 修改表名
    alter table 表名 rename 新表名;

# 2 增加字段
    alter table 表名 add 字段名 字段类型(宽度)  约束条件;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;

# 3 删除字段
    alter table 表名 drop 字段名;

# 4 修改字段
    # modify 一般是修改字段类型
    alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
    # change 一般是修改字段名
    alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

四、复制表(了解)

"""
我们sql语句查询的结果其实也是一张虚拟表
"""
# 只能将查询到的虚拟表，创建真实的表，但是只能复制表结构，不能复制主键、外键等
create table 表名 select * from 旧表;  不能复制主键 外键 ...

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

作业

练习：账号信息表，用户组，主机表，主机组

#用户表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);

#用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);

#主机表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);

#业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);

#建关系：user与usergroup

create table user2usergroup(
id int not null unique auto_increment,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);

#建关系：host与business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);

#建关系：user与host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);

练习:

# 班级表
cid	caption
# 学生表
sid sname gender class_id
# 老师表
tid	tname
# 课程表
cid	cname	teacher_id
# 成绩表
sid	student_id course_id number