MYSQL表关系及优化

一、外键（Foreign Key）

1、**作用：**约束当前表的某列值必须取自于另一张表的主键列值

外键所在的列称之为“外键列”

外键所在的表称之为“外键表”或“子表”

被外键列所引用的表称之为“主表”或“主键表”

2、语法：

1.创建表的同时指定外键

create table xxx(

字段 类型,

…,

constraint 外键名 foreign key(字段)

references 主键表(主键列)

)

案例演示

-- 创建course表：id,cname,cduration
 create  table course(
    id int primary key auto_increment,
    cname varchar(30) not null,
    cduration int not null
 )
-- 创建teacher表：id,name,age,gender,hobby,course
-- course_id是外键，引用自course表的主键id
 create table teacher(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int not null,
    gender varchar(2) not null,
    hobby varchar(50) not null,
    course_id int,
    -- 外键约束
    constraint fk_course_teacher
    foreign key(course_id) -- 设置外键
    references course(id) -- 参照主键(参照course的id值)参照谁就写谁
 )

3、对已有表增加外键

alter table 表名

** 　　add constraint 外键名**

** 　　foreign key(字段)**

**　　 references 主键表(主键)**

-- 创建student表：id,name,age,gender,school,class_id,major_id
    create table student(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int not null,
    gender varchar(2) not null,
    school varchar(100) not null,
    class_id int not null,
    major_id int not null
 );
-- 创建classinfo表：id,classname,status
    create table classinfo(
    id int primary key auto_increment,
    classname varchar(30) not null,
    status varchar(2) not null
    );
-- 更新student表结构,增加外键在class_id,引用子classinfo表的主键id
    alter table student
    add constraint fk_class_student
    foreign key(class)
    references classinfo(id)

4、删除外键

alter table 表名 drop foreign key 外键名;

4.查看外键名

show create table 表名;

二、级联操作

1.语法：

alter table 表名

add constraint 外键名

foreign key(字段)

references 主键表(主键)

on delete 级联操作

on update 级联操作

-- 为score表中的stu_id增加外键，并设置级联操作
    alter table score
    add constraint fk_student_score
    foreign key(stu_id)
    references student(id)
    on delete cascade
    on update cascade

2.级联操作取值

(1)cascade

数据级联删除、更新(主表有啥动作，子表跟着有啥动作)

(2)restrict(默认)

子表中有关联数据，那么主表中就不允许做删除、更新。

(3)set null

主表删除数据时子表中的相关数据会设置为null

三、E-R模型

1、什么E-R模型 entity - Relationship 模型

在数据库审计阶段一定会使用到

以图形的方式展示数据库中的表以及表关系

2、概念

① 实体 - Entity：表示数据库中的一个表

图形表示：矩形框

② 属性：表示某试题中的某一特性，即表的字段

图形表示：椭圆形

③ 关系： - Relationship

表示实体与实体之间的关联关系

1、一对一关系(1:1)

A表中的一条记录只能关联到B表中的一条记录上

B表中的一条记录只能关联到A表中的一条记录上

在数据库中的实现手段：

在任何的一张表中增加：

1.外键，并引用子另一张主键

2.唯一索引/约束 unique

案例演示

-- 创建wife表 目的实现与teacher之间的一对一的关系
create table wife(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int not null,
    teacher_id int unique, -- teacher_id数据唯一性
    constraint fk_wife_teacher
    foreign key(teacher_id) -- 设置外键
    references teacher(id) -- 参照主键(参照course的id值)参照谁就写谁
    -- unique(teacher_id) 上面和下面一个地方加unique 即可
);
insert into wife values
(null,'wife1',28,1),
(null,'wife2',76,3),
(null,'wife3',23,2);
-- teacher_id 插入数据不能重复（符合唯一索引要求），插入数据必须在teacher.id的范围内（约束外键插入）

2、一对多关系(1:m) 出现的场合是最多的

A表中的一条记录能关联到B表中的多条记录上

B表中的一条记录只能关联到A表中的一条记录上

在数据库中的实现手段：

在‘多’表中增加：外键，引用‘一’表的主键

3、多对多关系(m:m)

A表中的多条记录能关联到B表中的多条记录上

B表中的多条记录能关联到A表中的多条记录上

在数据库中的实现手段：

依托于第三张关联表，来实现多对多

1.创建第三张表

2.一个主键，俩外键，外键分别引用自关联的两张表的主键

案例演示

-- 创建goods表 
create table goods(
    id int primary key auto_increment,
    gname varchar(30) not null,
    gprice int not null
);
insert into goods values
(null,'ipone',10000),
(null,'ipad',5000),
(null,'华为',3000);
-- 创建ShoppingCart表 第三张表 目的实现与teacher、goods之间的多对多的关系
create table ShoppingCart(
    id int primary key auto_increment,
    t_id int,
    g_id int,
    count int,
    constraint fk_sc_teacher
    foreign key(t_id)
    references teacher(id),
    constraint fk_sc_goods
    foreign key(g_id)
    references goods(id)
);

-- 练习：1、将shoppingcart文字化
select s.id,t.name,g.gname,g.gprice,s.count
from ShoppingCart as s
inner join goods as g
on g.id=s.g_id
inner join teacher as t
on t.id=s.t_id;

-- 练习：2、查询计算每位老师共花了多少钱
select t.name,sum(g.gprice*s.count) as 购物总价
from ShoppingCart as s
inner join goods as g
on g.id=s.g_id
inner join teacher as t
on t.id=s.t_id
group by t.name;

四、SQL语句优化

1、索引：经常select,where,order by 的字段应该建立索引

2、单调查询语句最后添加 limit 1(有了limit 会停止全表扫描)

3、where子句中尽量不使用 != , 否则就放弃索引全表扫描

4、尽量避免null值判断,否则放弃索引全表扫描

5、尽量避免 or 连接条件，否则放弃索引全表扫描

6、模糊查询尽量避免使用前置%,否则全面扫描

7、尽量避免使用in 和not in， 否则全面扫描

8、尽量避免使用select * ，使用具体的字段代替 * ，不要返回用不到的任何字段