E-R模型及语句优化

一、E-R模型

（一）什么E-R模型 entity - Relationship 模型

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

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

（二）概念

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

　　图形表示：矩形框

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

　　图形表示：椭圆形

3.关系： - 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 * ，使用具体的字段代替 * ，不要返回用不到的任何字段