[大数据]MySQL-(3)

内容概览

• 约束条件
  • 主键
  • 外键
    • 一对多
    • 多对多
    • 一对一

约束条件

主键

1.primary key主键
# 单从约束角度上看,主键相当于是非空且唯一
create table t1(id int primary key,name varchar(32));
desc t1;  # null是no key是pri
insert into t1 values(1,'jason');
insert into t1 values(1,'keven');  # 报错,证明是唯一
# InnoDB存储引擎规定一张表必须有且只有一个主键(主键可以加快数据查询)
	# 1.如果表里没有主键也没有非空且唯一的字段,那么InnoDB存储引擎会自动使用一个隐藏的字段作为主键
	# 2.如果表里没有主键但有非空且唯一的字段,那么InnoDB会将第一个查询到的该字段作为主键
create table t2(
	id int not null unique,
	uid int not null unique,
	pid int not null unique,
	name varchar(32)
);
desc t2;  # id的key为pri
# 创建表的时候都应该有一个类似ID的字段,作为主键
# 联合主键：
create table t3(
	uid int,
	pid int,
	primary key(uid,pid)
);
desc t3;
# 联合主键还是看做一个主键,在单个字段不能唯一标识时使用
2.auto_increment自增
# 此约束条件必须跟在键后使用(主要配合主键)
# 不配合键使用：
create table t4(id int auto_increment);  # 报错:Incorrect table definition; there can be only one auto column and it must be defined as a key
# 配合主键使用
create table t5(id int primary key auto_increment,name varchar(32));
insert into t5(name) values('jason'),('keven');
select * from t5;  # 不用自己写,每次自增1
# 自增的特点:自增的操作不会因为执行删除数据的操作回退或重置
# 如果想要重置主键,需要格式化表:truncate 表名;

外键

• 推导流程：

  1. 创建一张员工表，字段有：id、name、gender、dep_name、dep_desc
  2. 上述表的缺点：表结构不清晰，可以说是员工表也可以说是部门表；字段反复存取，浪费存储空间；扩展性差，修改一个部门下边所有都需要修改
  3. 解决方案：拆表，但是拆表后两张表的数据没有了关联
  4. 添加部门编号字段记录部门数据的主键值

  外键字段:专门用于记录表与表之间数据的关系

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

  1. 一对多
  2. 多对多
  3. 一对一
  4. 没有关系

  表数据关系的判定

  1. 先站在其中一个表的角度看，自身的一条数据是否对应对方多条数据(一名员工是否属于多个部门)
  2. 再站在另一个表的角度看，自身的一条数据是否对应对方多条数据(一个部门是否包含多个员工)

  如果是一个可以一个不可以，则是一对多，如两个都可以，则是多对多，如两个都不可以，则是一对多或者没有关系
  没有多对一的说法，统称一对多

一对多

# 先写普通字段,然后再写外键字段
# Foreign key外键字段
create table emp(
	id int primary key auto_increment,
	name varchar(32),
	gender enum('male','female','other') default 'other',
	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)
	);
# 一对多外键一般键在多的一方
# 创建表的时候需要先创建被关联的表(没有外键),然后再创建关联表(有外键),关联表在创建时没有被关联的表会报错
# 插入表数据时,针对外键字段只能写被关联表字段已有的数据值
# 被关联的字段无法修改与删除,被关联的表也无法删除

# 级联更新、级联删除：被关联的数据一旦变动,关联的数据同步变动
create table emp1(
	id int primary key auto_increment,
	name varchar(32),
	gender enum('male','female','other'),
	dep_id int,
	foreign key(dep_id) references dep(id)
	on update cascade  # 级联更新
	on delete cascade  # 级联删除
	);
create table dep1(
	id int primary key auto_increment,
	dep_name varchar(32),
	dep_desc varchar(32)
	);

在实际工作中，很多时候可能不会使用外键
因为外键增加了表与表之间的耦合度，不便于单独操作，资源消耗高
我们为了能够描述出表数据的关系，又不想使用外键，可以自己写SQL建立代码层面的关系

多对多

以书籍表与作者表为例
一本书籍可以是多位作者编写
一位作者可以编写多本书籍
那么两边都可以以一条数据对应多条数据，那么表数据关系就是多对多
针对多对多表关系，外键字段不能建在任意一方

create table book(
	id int primary key auto_increment,
	title varchar(32)
	# author_id int,
	# foreign key(author) 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 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
	);

表关系之一对一

以用户表与用户详情表为例
一位用户只能对应自己的用户详情
一份用户详情也只能对应相应的用户
那么首先查看两边是否有关系，如果有，那就是一对一
针对于一对一的表关系，外键字段建在哪边都可以，但是建议建在查询频率更高的表中

create table user(
	id int primary key auto_increment,
	name varchar(32),
	detail_id int unique,  # 需要限制唯一,否则就变成一对多了
	foreign key(deteil_id) references userDetail(id)
	on update cascade
	on delete cascade
	);
create table userDetail(
	id int primary key auto_increment,
	phone bigint,
	addr varchar(32)
	);