MySql-第六章-MySql工具技巧

• 视图 VIEW

  是什么

        将一段查询sql封装为一个虚拟的表。

        这个虚拟表只保存了sql逻辑，不会保存任何查询结果。

  作用

        1、封装复杂sql语句，提高复用性

        2、逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活

 好处

    提供了另外一种级别的表安全性:隐藏了一些关键的字段

    简化的用户的SQL命令

    隔离基表结构的改变

  适用场景

        很多地方可以共用的一组查询结果

        报表

  语法

        创建 

            create or replace view 视图名

            As

            查询语句

        使用

  注意事项

        mysql的视图中不允许有from 后面的子查询，但oracle可以

• 存储过程

    一组预先编译的sql语句的集合

  可以包含逻辑判断的 sql 语句集合。

　　是经过预编译，存在于数据库中。

　　通过调用指定存储过程的名字（可有参，可无参）来执行。

好处：

   简化了复杂的业务逻辑，根据需要可重复使用

　　屏蔽了底层细节，不暴露表信息即可完成操作

　　降低网络的通信量，多条语句可以封装成一个存储过程来执行

　　设置访问权限来提高安全性

　　提高执行效率，因为它是预编译以及存储在数据库中 

语法：

    delimiter $;

    create procedure 存储过程名（in|out|inout 参数名 参数类型）

    begin

        存储过程体

    end

调用  

    call 存储过程名（实参列表）

    使用@a，@b进行赋值   set @a=10$    set @b=20$

查看存储过程的状态

    show procedure status like 存储过程名

• 函数

语法

    create function 函数名（参数名 参数类型,...） returns 返回类型

    begin

        函数体 

        return

    end

调用

    select 函数名(实参列表)

函数只能有一个返回值，而存储过程可以有0个或多个

• 触发器 Trigger

          根据某张表发生的事件比如：insert,update,delete，来触发执行某些处理

  适用场景

        冗余数据的同步

  语法

        DELIMITER $$

    

        CREATE   TRIGGER   tr_afterupdate_t_dept

        AFTER UPDATE ON t_dept

        FOR EACH ROW

        BEGIN

        UPDATE  t_emp a

        LEFT JOIN  t_dept b ON a.`deptId` = b.`id`

        LEFT JOIN  t_emp c ON b.`CEO` = c.`id`

        SET a.`ceo_name`=c.name

        WHERE b.id=new.id;  #new 或old 指向当前正在更新的行

        END $$

        DELIMITER ;

  注意事项

    1、trigger触发器虽然方便但是非常不利于维护

    2、影响数据库性能

    3、这种非程序触发的写操作很难被日志追踪

    4、触发了某事件的表，不能利用触发器对自己做操作

• 事件 event

  作用

        用来做定时任务

  语法

    CREATE  

        [DEFINER = { user | CURRENT_USER }]  

        EVENT  

        [IF NOT EXISTS]  

        event_name  

        ON SCHEDULE schedule  

        [ON COMPLETION [NOT] PRESERVE]  

        [ENABLE | DISABLE | DISABLE ON SLAVE]  

        [COMMENT 'comment']  

        DO event_body;  

      

    schedule:  

        AT timestamp [+ INTERVAL interval] ...  

      | EVERY interval  

        [STARTS timestamp [+ INTERVAL interval] ...]  

        [ENDS timestamp [+ INTERVAL interval] ...]  

      

    interval:  

        quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |  

              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |  

              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}  

  

Demo

#创建更新实体表的存储过程

delimiter $$

create  procedure  proc_mv_top_second()

begin

  truncate table mv_top_second;

  SET @rank=0;

  SET @last_deptid=0;

  INSERT INTO  mv_top_second

    SELECT a.deptid,a.name,a.age

  FROM (

  SELECT

   t.*,

   IF (@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS  rk,

    @last_deptid:=deptid AS last_deptid

   FROM t_emp  t

   ORDER BY deptid ,age  DESC

   ) a WHERE a.rk=2;

end $$

#创建定时任务

DELIMITER $$

CREATE EVENT ev_onemin_job

ON SCHEDULE EVERY 1 MINUTE STARTS NOW()  # 从现在开始  每分钟执行1次

ON COMPLETION PRESERVE ENABLE   # COMPLETION [NOT] PRESERVE  结束的时候 是否还保存该事件  ENABLE/DISABLE 结束后是否还启用该事件  

DO

BEGIN

   call  proc_mv_top_second();

end$$

    开启定时事件的开关

SHOW  VARIABLES  LIKE '%event_scheduler%'

SET GLOBAL event_scheduler =1;

  事件案例

DELIMITER &&

CREATE event  even_update_name_age_rk

ON SCHEDULE EVERY 1 MINUTE STARTS NOW()

ON COMPLETION PRESERVE ENABLE  #结束后保存

DO

BEGIN

CALL proc_update_name_age_rk();

END &&

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE proc_update_name_age_rk()

BEGIN

TRUNCATE TABLE name_age_rk;

SET @rank=0;

SET @last_age=0;

SET @last_deptid=0;

INSERT INTO name_age_rk

SELECT NAME,age,rk

FROM(

SELECT *,IF (@last_deptid = deptid,IF (@last_age=age,@rank,@rank:=@rank+1),@rank:=1) AS rk,@last_age:=age last_age,

    @last_deptid:=deptid last_deptid FROM t_emp

ORDER BY deptid,age DESC

) a;

END $$

• 定时备份数据库

  需求

    每日凌晨2点备份mydb数据库脚本。并以年月为目录，以年月日为文件名，放到/backup/mysql下

  备份

    导出语句

         mysqldump命令

        导出 sql文件

        格式：  mysqldump -u[用户名] -p[密码] [要导出的数据库]>[导出的文件.sql]

        例如：

        mysqldump -uroot -p123123 mydb>/backup/mysql/mydb_2017050X.sql

    shell脚本

         #! /bin/sh

        DIR=/backup/mysql/`date +%Y%m`

        DATABASE=mydb

        if [ ! -d $DIR ]

        then

           mkdir -p $DIR

        else

           echo "already has dir"

        fi

            echo "start to backup"

        mysqldump -uroot -p123123 $DATABASE>$DIR'/'$DATABASE`date +%Y%m%d`'.sql'

        echo $DIR'/'$DATABASE`date +%Y%m%d`'.sql'

  定时任务

    crond和crontab

crond是linux定时任务服务。

通过service crond start/stop来控制服务的启动关闭。

crond是每分钟检查一次事件是否触发。所以linux定时任务最小的执行频率是1分钟。

crontab 是设定、管理定时任务的命令行工具。

通过crontab -e 来定制定时任务

基本格式 :

30　　2　　1　　12　　*　　command

30　　2　　*　　*　　*　　command

分　时　日　月　周　命令

第1列表示分钟1～59 每分钟用*或者 */1表示

第2列表示小时1～23（0表示0点）

第3列表示日期1～31

第4列表示月份1～12

第5列标识号星期0～6（0表示星期天）

第6列要运行的命令

crontab文件的一些例子：

30 21 * * *  service mysql restart  

上面的含义是 每天21点30分执行重启mysql

定时执行脚本就是   0 2 * * * sh /backup/mysql/backup.sh