MySQL数据库

外键约束和表关联关系

外键约束

约束 : 约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。
foreign key 功能 : 建立表与表之间的某种约束的关系,由于这种关系的存在,能够让表与表之间的数据,更加的完整,关连性更强,为了具体说明创建如下部门表和人员表。

创建部门

CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

创建人员

CREATE TABLE person (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint DEFAULT 0,
sex enum(‘m’,‘w’,‘o’) DEFAULT ‘o’,
salary decimal(8,2) DEFAULT 250.00,
hire_date date NOT NULL,
dept_id int DEFAULT NULL
) ;

上面两个表中每个人员都应该有指定的部门,但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门,这显然是不合理的。当然并不是任何情况都需要建立外键关系,如果没有这种约束时也可以不建立,但是当表与表之间存在这种约束时最好建立。

主表和从表:若同一个数据库中,B表的外键与A表的主键相对应,则A表为主表,B表为从表。

foreign key 外键的定义语法:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)

REFERENCES tbl_name (index_col_name, …)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用,如果不指定CONSTRAINT symbol,mysql会自动生成一个名字,可以通过show create table [tb]命令查看。

建立表时直接建立外键关联,注意本表的外键列类型与指定的主表列相同,且主表指定列需为主键。

CREATE TABLE person (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint DEFAULT 0,
sex enum(‘m’,‘w’,‘o’) DEFAULT ‘o’,
salary decimal(10,2) DEFAULT 250.00,
hire_date date NOT NULL,
dept_id int DEFAULT NULL,
constraint dept_fk foreign key(dept_id) references dept(id));

建立表后增加外键

alter table person add constraint dept_fk foreign key(dept_id) references dept(id);

通过外键名称解除外键约束

alter table person drop foreign key dept_fk;

删除外键后发现desc查看索引标志还在,其实外键也是一种索引,需要将外键名称的索引删除之后才可以。

restrict(默认) : on delete restrict on update restrict
当主表删除记录时,如果从表中有相关联记录则不允许主表删除
当主表更改主键字段值时,如果从表有相关记录则不允许更改
cascade :数据级联更新 on delete cascade on update cascade
当主表删除记录或更改被参照字段的值时,从表会级联更新
set null : on delete set null on update set null
当主表删除记录时,从表外键字段值变为null
当主表更改主键字段值时,从表外键字段值变为null
no action
同 restrict,都是立即检查外键限制

表关联设计

当我们应对复杂的数据关系的时候,数据表的设计就显得尤为重要,认识数据之间的依赖关系是更加合理创建数据表关联性的前提。常见的数据关系如下:

一对一关系

一张表的一条记录一定只能与另外一张表的一条记录进行对应,反之亦然。

举例 : 学生信息和学籍档案,一个学生对应一个档案,一个档案也只属于一个学生

create table student(id int primary key auto_increment,name varchar(50) not null);

create table record(id int primary key auto_increment,
comment text not null,
st_id int unique,
foreign key(st_id) references student(id)
on delete cascade
on update cascade
);

一对多关系

一张表中有一条记录可以对应另外一张表中的多条记录;但是反过来,另外一张表的一条记录只能对应第一张表的一条记录,这种关系就是一对多或多对一

举例: 一个人可以拥有多辆汽车,每辆车登记的车主只有一人。

create table person(
id varchar(32) primary key,
name varchar(30),
sex char(1),
age int
);

create table car(
id varchar(32) primary key,
name varchar(30),
price decimal(10,2),
pid varchar(32),
constraint car_fk foreign key(pid) references person(id)
);

多对多关系

一对表中(A)的一条记录能够对应另外一张表(B)中的多条记录;同时B表中的一条记录
也能对应A表中的多条记录

举例:一个运动员可以报多个项目,每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

CREATE TABLE athlete (
id int NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
age tinyint NOT NULL,
country varchar(30) NOT NULL,
description varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);

CREATE TABLE item (
id int NOT NULL AUTO_INCREMENT,
rname varchar(30) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE athlete_item (
aid int NOT NULL,
tid int NOT NULL,
PRIMARY KEY (aid,tid),
CONSTRAINT athlete_fk FOREIGN KEY (aid) REFERENCES athlete (id),
CONSTRAINT item_fk FOREIGN KEY (tid) REFERENCES item (id)
);

E-R模型

定义

E-R模型(Entry-Relationship)即 实体-关系 数据模型,用于数据库设计
用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系

实体、属性、关系

实体

1、描述客观事物的概念
2、表示方法 :矩形框
3、示例 :一个人、一本书、一杯咖啡、一个学生

属性

1、实体具有的某种特性
2、表示方法 :椭圆形
3、示例
学生属性 :学号、姓名、年龄、性别、专业 …
感受属性 :悲伤、喜悦、刺激、愤怒 …

关系

1、实体之间的联系
2、一对一关联(1:1)
3、一对多关联(1:n)
4、多对多关联(m:n)

ER图的绘制

矩形框代表实体,菱形框代表关系,椭圆形代表属性

表关联查询

如果多个表存在一定关联关系,可以多表在一起进行查询操作,其实表的关联查询与外键约束之间并没有必然联系,但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。

多表查询

多个表数据可以联合查询,语法格式如下:

select
字段1,字段2… from 表1,表2… [where 条件]

e.g.
select * from dept,person where dept.id = person.dept_id;

内连接

内连接查询只会查找到符合条件的记录,其实结果和表关联查询是一样的,官方更推荐使用内连接查询。

SELECT 字段列表
FROM 表1 INNER JOIN 表2
ON 表1.字段 = 表2.字段;

select * from person inner join dept on person.dept_id =dept.id;

笛卡尔积

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。

select * from person inner join dept;

外链接

左连接 : 左表为主表,显示右表中与左表匹配的项

SELECT 字段列表
FROM 表1 LEFT JOIN 表2
ON 表1.字段 = 表2.字段;

select * from person left join dept on person.dept_id =dept.id;

右连接 :右表为主表,显示左表中与左表匹配的项

SELECT 字段列表
FROM 表1 RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

select * from person right join dept on person.dept_id =dept.id;

注意:我们尽量使用数据量大的表作为基准表,即左连接做左表,右连接做右表