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) 实体之间的关系
一对一: 个人信息和档案
- 外键添加唯一约束
- 主键做外键
一对多(多对一): 雇员和部门
多对多: 学生和课程
添加第三张关系表
设置外键以及联合主键
5. 数据库优化
sql优化:
1) 尽量减少*的使用(*转换为列名,然后再查询)
11) 索引失效的状况:
- 索引列不要使用is not null/is null
- 索引列不要使用函数
- 索引列不要计算
- 索引列不要使用not(!=/<>)
- 索引列不要使用or(union替换)
- 索引列不要使用包含(like ‘%s%’)
12) exists和in的选择
exists先执行主查询
in先执行子查询
先过滤再关联
6. 备份和还原
Mysqldump -uroot -proot dbname > d:/t.sql;
mysql -u root -p < C:\backup.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;
