mysql04

1. 存储程序

1) 触发器

存储程序，存储在服务器端。

由事件(增删改)调用,不能传参。

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name

    trigger_time trigger_event

    ON tbl_name FOR EACH ROW

    [trigger_order]

    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

delimiter //;

create trigger tri_user

after delete

on userinfo for each row

begin

# old  new

  insert into user_bak values(old.uid,old.uname,old.password);

end;

触发器:  不要添加过多触发器,降低效率

        存储程序中不能使用事务控制。

2. 视图

1)  什么是视图

数据库对象,他是一个虚拟表,底层并非以键值形式保存数据,在执行过程中动态从基表中获取数据。(视图以表为底层来查询)

2) 视图基本语法

Create view view_name

As

查询

create or replace view view_emp

as

select * from emp where deptno = 10 with check option;

with check option:保护视图数据。只能在视图范围之内进行数据的修改。

修改视图会修改底层基表的数据。

视图一般只执行查询，不执行增删改。

销毁视图

Drop view view_emp;

3) 视图优点

安全性: 保密敏感数据。

高效性: 提高查询效率。

定制化数据: 可以将多张表中经常展示数据放置在视图中快速查询。

3. 索引

1) 什么是索引

提升查询效率而创建数据结构。

4) 索引分类

B-Tree:默认

mysql中默认采用B+树方法来维护索引列数据。

B+树是一个多路搜索树。

不同的存储引擎对索引的存储策略不一致。

MyIsam:叶子节点中保存记录的地址。

 Innodb：叶子节点中直接保存相应数据。

位图索引

哈希索引

5) 索引的创建

单列索引:

普通索引:

create index index_name on 表(字段);

唯一索引:

create unique index index_name on 表(字段);

全文索引:

空间索引:

复合索引:多列作为索引字段

删除索引:drop index 名字 on 表;

6) 索引的选择

索引需要单独文件来保存维护。

表数据发生变化需要维护索引表。

适合添加索引:

表数据量足够大;

增删改操作较少;

高基数列

索引说明:

索引不适合添加过多;

   经常作为查询条件的列适合作为索引列；

   但是某些特殊情况下索引会失效:

4. 数据库的设计

1) 数据库设计的含义

查询和项目的基础(基石),数据库的优化前提就是数据库设计

7) 数据库设计步骤

需求分析

概念结构模式: E-R模型图

关系模型: 将E-R转换为表，设置外模式

物理设计: 视图，索引

调试:

运行和维护

8) 数据库设计的范式

1NF:所有域都是原子性的。域中的数据不可分割。

2NF:非主键字段必须与主键相关(每张表只描述一列事物),而不能与主键部分相关(联合主键)

3NF:非主键字段必须与主键直接相关,非主键字段之间不直接相关

9) E-R图(实体关系图)

客观世界抽象的模型展示。

实体: 矩形框

关系: 菱形框

属性: 椭圆形框

连线:

n 1

10) 实体之间的关系

一对一:  个人信息和档案

1. 外键添加唯一约束
2. 主键做外键

一对多(多对一): 雇员和部门

多对多: 学生和课程

添加第三张关系表

  设置外键以及联合主键

5. 数据库优化

sql优化: 

1) 尽量减少*的使用(*转换为列名,然后再查询)

11) 索引失效的状况:

1. 索引列不要使用is not null/is null
2. 索引列不要使用函数
3. 索引列不要计算
4. 索引列不要使用not(!=/<>)
5. 索引列不要使用or(union替换)
6. 索引列不要使用包含(like ‘%s%’)

12) exists和in的选择

exists先执行主查询

in先执行子查询

先过滤再关联

6. 备份和还原

Mysqldump -uroot -proot dbname > d:/t.sql;

   mysql -u root -p < C:ackup.sql

 _________________________________________________________________________________________________________________________________

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　练习

#复制表结构
create table user_bak select * from userinfo where 0;

#创建触发器
delimiter //;
create trigger tri_user
after delete
on userinfo for each row
begin
# old new
insert into user_bak values(old.uid,old.uname,old.password);
end;

delete from userinfo where uid = 1;

# 查询10号部门所有员工信息
create view view_emp
as
select * from emp where deptno = 10;

#执行增删改查操作和表一致
select * from view_emp;

#不应该出现
update view_emp set ename='zs';

create or replace view view_emp
as
select * from emp where deptno = 10 with check option;

#索引
explain select * from emp where ename like '%s%';

#给emp表中ename字段添加索引
create index index_ename on emp(ename);

#主键，唯一约束就是索引 特殊的唯一索引
explain select * from emp where empno = 7788;