一：存储程序

什么是存储程序？

存储程序指的一组存储和执行在数据库服务器端的程序。存储程序总是在服务器的进程或者线程的内存中执行的。

类似于java中的方法，但是它不是在客户端存储，而是在服务器中的进程或者线程的内存中进行的，在客户端去使用。效率快。资源占用率大。

存储程序分为：存储过程，存储函数

（一） 存储过程

类似于方法,有过程名称，参数列表，过程体组成。

存储过程包括：过程创建（声明），过程调用。

存储过程中的参数有三种模式：

IN：默认。 由外部将参数传入过程。
OUT: 可以由存储过程将值传出。
INOUT: 可以传入也可以传出。
普通方法 IN示例：

-- 存储过程(根据员工编号查询员工姓名  in模式)
#声明
##声明结束标志
delimiter //
create PROCEDURE selEmpByNo(eno int)
begin
    select ename from emp where empno = eno;
end;
//
#调用
call selEmpByNo(7788);

普通方法 INOUT示例：

-- 存储过程(根据名称查询职位  inout模式)
delimiter //
create PROCEDURE selEmpByName(inout name_job varchar(20))
begin
    select job into name_job from emp where ename = name_job;
end;
//
#设置变量来传入和传出参数
set @v_name= 'SCOTT';
call selEmpByName(@v_name);
select @v_name;


分支示例：

-- （划分成绩等级）
delimiter //;
create procedure score_level(score int)

begin
declare v_level varchar(20);
    if score >= 90 then
        set v_level = 'A';
    elseif score >= 80 then
        set v_level = 'B';
    elseif score >= 70 then
        set v_level = 'C';
    else
        set v_level = 'D';
    end if;
    select v_level;

end;
//

call score_level(80);


循环 示例：

while 条件 do … end while;
-- 循环：1+2+3+....+100
#while循环
delimiter //
create procedure calc1()
begin
    declare i int;
    declare sum int;
    set i = 1;
    set sum = 0;
    while i <= 100 do
        set sum = sum + i;
        set i = i + 1 ;
    end while;
    select sum;
end;
//
call calc1();



loop … endloop;(死循环，中间需要if判断退出，用leave)
-- 循环：1+2+3+....+100
#loop循环
delimiter //
create procedure calc2()
begin
    declare i int;
    declare sum int;
    set i = 1;
    set sum = 0;
    lip:loop
        set sum = sum + i;
        set i = i + 1 ;
        if i > 100 then
            leave lip;
            end if;
    end loop;
    select sum;
end;
//
call calc2();


repeat .. end repeat;(死循环，util退出)

-- 循环：1+2+3+....+100
#repeat循环
delimiter //
create procedure calc3()
begin
    declare i int;
    declare sum int;
    set i = 1;
    set sum = 0;
    repeat
        set sum = sum + i;
        set i = i + 1 ;
        until i>100
    end repeat;
    select sum;
end;
//
call calc3();



（二）存储函数

最大的特点是有参数和返回值

-- 存储函数（根据eno查询ename）
delimiter //
create function fun_sel(eno int)
#声明返回值类型
returns varchar(20)
#确定的
DETERMINISTIC
begin
    declare v_name VARCHAR(20);
    select ename into v_name from emp where empno = emo;
    return v_name;

end;
//


select fun_sel(7788);


（三）存储过程和存储函数的区别

关键字不同
存储过程通过参数模式返回值，函数可以通过return返回值
存储过程可以作为独立调用的个体，函数必须作为sql胡一部分进行调用
（四）触发器

特点：不能手动调用，不能传递参数；由事件触发(增删改)

触发器结构

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


触发器示例

-- 触发器(把stu删除的行放到student表)
delimiter //
create trigger tri_stu
after delete
on stu for each row
begin
    insert into student (sname,cid,age) values(old.sname,old.cid,old.age);

end;
//

delete from stu where sid = 1;


二：视图和索引（重点）

视图

虚拟表，在数据库中没有物理结构存在
视图数据来自于基表，在执行过程中动态从基表中获取数据并进行展示
视图可以使用增删改查的功能，视图增删改功能会修改基表数据。视图不建议使用增删改
-- 视图
#创建
create view view_emp
as
select * from emp where deptno = 10 with check option;
#使用
select * from view_emp;
update view_emp set deptno = 20;
drop view;

视图的优点

简化开发。存储之前查找出来的集合。
安全。一张表并不都让人看到，只映射出一部分内容公开给别人看。
3.定制化数据。拆分或组合字段信息。
高效，简化开发
索引

索引是为提高查询效率设计的数据结构。本质需要文件存储，所以也需要维护。

索引添加规则

数据量够大
添加索引在高基数列（不一样的值尽可能多）
不能添加过多，因为需要维护
不能添加在过多增删改操作的表
先把数据插入完成，再去一次性添加索引 会比 先加索引效率高
默认采用B树索引。（位图索引）
create index  insex_sid on stu(sid);
drop index insex_sid on stu;

三：数据库设计（重点）

（一）：数据库三大范式

（尽量遵循，所用是减少字段冗余，有时为了增加查询效率可以不遵循）

1NF：数据的列都是不可分割的原子性。

假如设计表：ename，eno，job，tel，address。

地址字段内容是：北京市昌平区立水桥路155号；

如果要查找昌平区的所有人，利用like或函数去取子串，效率很低。
应该将字段拆成 市字段，区字段，路字段会更好管理和使用。

2NF：在满足第一范式的基础上，所有的非主键信息必须与主键信息相关，如果有联合主键，应与联合主键完全相关，不能是部分主键相关。

也就是说一张表只描述一个事物

3NF：所有非主键字段必须与主键直接相关，非主键字段之间不能直接相关。传递依赖不可取。

（二）：数据库设计阶段

1.需求分析阶段

了解与分析用户需求；是整个阶段过程的基础，是最困难，最花费时间的一步

2.概念结构设计阶段

是整个数据设计的关键。

设计数据库的e-r图，确认需求的正确性和完整性。

E-R图（实体联系图）
- 矩形：表示实体，在框中记录实体名字
- 菱形：表示联系，在框中记录联系名字
- 椭圆：表示实体的属性，在框中记录属性名字
- 连线：实体与联系之间，联系与属性之间，实体与属性之间用直线相连，并在直线上标注联系的类型

实体之间的关系：

一对一关系：如个人信息表和档案表
约束方式一：外键添加唯一约束
约束方式二：主键作为外键
一对多：如部门和员工
约束方式：直接添加外键（外键添加在多这一方）
多对多：如学生表和课程表
约束方式：需要增加关系管理表，并且设计联合主键
3.逻辑设计(创建ConceptualDataModel)

将E-R图转换成逻辑模型

4.物理设计

5.数据库实施

6.使用和维护

四： 数据库优化

1.Select子句后尽量不用*

索引失效的情况:

　① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。

　② 索引列上不要使用函数,

     SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = ‘ABC’（不）
     SELECT Col FROM tbl WHERE name LIKE ‘%ABC%’ （不）
     SELECT Col FROM tbl WHERE name LIKE ‘ABC%’ （使用）。

　③ 索引列上不能进行计算

    SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效
    应该改成SELECT Col FROM tbl WHERE col > 10 * 10

　④ 索引列上不要使用NOT （ != 、 <> ）

  如:SELECT Col FROM tbl WHERE col ! = 10 应该
    改成：union。

2.用UNION替换OR(适用于索引列)

** union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。 由于是追加操作，需要两个结果集的列数应该是相关的，并且相应列的数据类型也应该相当的。
union 返回两个结果集，同时将两个结果集重复的项进行消除。 如果不进行消除，用UNOIN ALL.
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.
注意, 以上规则只针对多个索引列有效.
如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. **

用EXISTS替代IN、用NOT EXISTS替代NOT IN

在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).

为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
高效: SELECT * FROM EMP (基础表) WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
低效: SELECT * FROM EMP (基础表) WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC =‘MELB’)

五：备份和还原

Mysqldump -uroot -proot dbname > d:/t.sql;
mysql -u root -p < C:\backup.sql

奈斯

牛牛牛！

优秀