MySQL数据库学习笔记

今日内容概要

• 约束条件之主键
• 约束条件之外键

今日内容详细

约束条件之主键

primary key 主键

1.单从约束角度上而言主键等价于非空且唯一(not null unique)

create table t1(
    id int primary key,
    name varchar(32)
);

insert into t1(name) value('tom');  # 没有传入id 报错
insert into t1 value(1,'tom');
insert into t1 value(1,'bob');  # id1已经存在  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

2.innodb存储引擎规定一张表必须有且只有一个主键

1.如果创建的表中没有主键也没有非空且唯一的字段，那么innodb存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询，比如新华字典的目录)
2.如果创建的表中没有主键但是有非空且唯一的字段，那么innodb存储引擎会自动将该字段设置为主键

create table t2(
    nid int not null unique,
    sid int not null unique,
    uid int not null unique,
    name varchar(32)
);

3.创建表的时候都应该有一个'id'字段并且该字段应该作为主键

uid、sid、pid、gid、cid、id

补充说明:

id int primary key  单列主键


sid int,
nid int,
primary key(sid,nid)  联合主键

auto_increment 自增

该约束条件不能单独使用，必须跟在键后面(主要配合主键一起使用)

create table t3(id int auto_increment); # 报错 there can be only one auto column and it must be defined as a key

create table t4(
    id int primary key auto_increment,
    name varchar(32)
);

自增的特点

自增的操作不会因为执行删除数据的操作而回退或者重置，只会前进
delete from 表名 where 筛选条件;
如果非要重置主键，需要格式化表
truncate 表名;  # 删除表数据并重置主键值

约束条件之外键

外键前戏

需要创建一张员工表
    id name gender dep_name dep_desc
上述表的缺陷
    1.表结构不清晰 到底是员工表还是部门表(不严重 无所谓)
    2.字段数据反复存取 浪费存储空间(不严重 无所谓)
    3.表的扩展性极差 牵一发动全身(很严重 效率极低)
优化操作>>>:拆表
    id name gender
    id dep_name dep_desc
  拆表之后解决了上述的三个问题 但是出现了一个致命的缺陷
解决措施
    id name gender dep_id
  添加一个部门编号字段填写部门数据的主键值
外键字段
    专门用于记录表与表之间数据的关系

foreign key 外键

1.先写普通字段
2.然后在写外键字段

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
"""
1.创建表的时候需要先创建被关联的表(没有外键)，然后再是关联表(有外键)
2.插入表数据的时候，针对外键字段只能填写被关联字段已经出现过的数据值
3.被关联字段无法修改和删除，有点不太好，操作限制性太强
"""

级联更新，级联删除

被关联数据一旦变动，关联的数据同步变动
create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade # 级联更新
    on delete cascade # 级联删除
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
      因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    我们为了能够描述出表数据的关系 又不想使用外键
      自己通过写SQL 建立代码层面的关系
"""

表关系之一对多

外键字段是用来记录表与表之间数据的关系，数据的关系有四种

1.一对多关系

2.多对多关系

3.一对一关系

4.没有关系

表数据关系的判定  >>>: '换位思考'
针对员工表和部门表判断数据关系
1.先站在员工表的角度
  问:一条员工数据能否对应多条部门数据
  翻:一名员工能否属于多个部门
  答:不可以
2.再站在部门表的角度
  问:一条部门数据能否对应多条员工数据
  翻:一个部门能否拥有多个员工
  答:可以
完成换位思考之后得出的答案 一个可以一个不可以
那么表关系就是"一对多",部门是一,员工是多
针对'一对多'的关系,外键字段建在多的一方 
ps:没有多对一 统一称为'一对多'

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);

表关系之多对多

以书籍表和作者表为例
1.先站在书籍表的角度
  问:一条书籍数据能否对应多条作者数据
  答:可以
2.再站在作者表的角度
  问:一条作者数据能否对应多条书籍数据
  答:可以
总结:两边都可以 那么表数据关系就是'多对多'
针对多对多表关系 外键字段不能建在任意一方!!!

create table book(
    id int primary key auto_increment,
    title varchar(32),
    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),
    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)
);
create table author(
    id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
    id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade
);

表关系之一对一

以用户表和用户详情表
1.先站在用户表的角度
  问:一条用户数据能否对应多条用户详情数据
  答:不可以
2.再站在用户详情表的角度
  问:一条用户详情数据能否对应多条用户数据
  答:不可以
总结:两边都不可以 那么先考虑是不是没有关系
    如果有关系那么肯定就是'一对一'
针对'一对一'的表关系 外键字段建在任何一张表都可以，但是建议你建在查询频率较高的表中便于后续查询

create table user(
    id int primary key auto_increment,
    name varchar(32),
    detail_id int unique,
    foreign key(detail_id) references userDetail(id) 
    on update cascade
    on delete cascade
);
create table userDetail(
     id int primary key auto_increment,
     phone bigint
);

ps:一对一关系与一对多关系的唯一不同是
    一对一关系里被关联表的id要做唯一值限制(unique)