mysql的数据库一些知识

存储过程

create procedure(创建)

格式： 

       create procedure 存储过程名（参数列表）

                  begin

                           sql语句代码块

                  end

作用：创建一个存储过程

例子：

        1.使用delimiter //更改语句定界符冲；变为//

        2.过程参数的列表总是存在，如果没有参数使用空括弧

        3.存储过程没有重载，只要存在名字一样的存储过程都是不合法的

         

alter procedure(修改)

格式：

       alter procedure  存储过程名称  [characteristic ...]

       characteristic:
                   { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
                       | SQL SECURITY { DEFINER | INVOKER }
                       | COMMENT 'string'

作用：改变一个存储过程特征 (只能改名字和定义不能改里面的内容)     

例子：  

      

drop procedure(删除)

格式：

        drop procedure if exists 存储过程名

作用：删除一个存在的存储过程

例子：

        

show create procedure(显示一个已存在的存储过程)

格式：

        show create procedure 存储过程名

作用：查看一个已经存在的存储过程

例子：

      

show procedure status(列出所有存储过程)

格式：

        show  procedure status

作用：列出所用的存储过程

例子：

         

call(存储过程的调用)

格式：call 存储过程名(参数列表)

作用：

          1.可以调用之前创建的存储过程

          2.可以用声明为out或inout的参数的参数给它的调用者传回值

          3.调用存储过程后面必须加括号，如果没有参数加空括号

例子：

       

declare (声明局部变量)

格式：declare 变量名   类型；

作用：声明局部变量，varchar在mysql中必须指定长度

例子：

     

     

函数

函数与存储过程的区别：

存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

    存储过程和函数存在以下几个区别：

    1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

    2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

    3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

    4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

格式：

create function 函数名(输入参数)

returns  返回值类型

begin

函数体

return 变量；

end

例子：

随机生成字符串：

游标

标在存储程序和函数内被支持

可以用在存储过程的SQL语句主要有以下类型:

1、 无返回结果语句，如：INSERT,UPDATE,DROP, DELETE等
2、 select语句返回单行变量并可传给本地变量(select ..into) 
3、 返回多行结果集的select语句,并可使用游标循环处理
注意，存储过程返回的多行结果集，可以被客户端程序（如php）所接收,但要在一个存储过程中接收另一个存储过程的结果集是不可能的，一般解决办法是存入临时表供其它过程共用

-- 定义本地变量
     DECLARE o varchar(128);
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT callee_name FROM account_tbl where acct_timeduration=10800;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
    SET no_more_departments=0;
    -- 打开游标
    OPEN ordernumbers;
    -- 循环所有的行
    REPEAT
        -- Get order number
       FETCH ordernumbers INTO o;
       update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@o;
  -- 循环结束
       UNTIL no_more_departments
      END REPEAT;
    -- 关闭游标
    CLOSE ordernumbers;

例子:

触发器

是一类特殊的存储过程，其执行不是由手工或自动执行，而是一系列事件触发执行的

创建触发器。创建触发器语法如下：

格式:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

其中trigger_name标识触发器名称，用户自行指定；
trigger_time标识触发时机，用before和after替换；
trigger_event标识触发事件，用insert，update和delete替换；
tbl_name标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt是触发器程序体；触发器程序可以使用begin和end作为开始和结束，中间包含多条语句

例子：

其他：

SHOW TRIGGERS;  显示定义的触发器

DROP TRIGGER 触发器名称;删除触发器

视图

视图是虚表，是从一个或几个基本表（或视图）中导出的表，在系统的数据字典中仅存放了视图的定义，
不存放视图对应的数据。 
作用：
     简单性：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。
         那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。 
    安全性：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。
        数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。
       通过视图，用户可以被限制在数据的不同子集上

格式：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEWview_name [(column_list)]
    ASselect_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

例子:

其他：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEWview_name [(column_list)]
    ASselect_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
SHOW CREATE VIEW 视图名称;显示创建的视图
DROP VIEW IF EXISTS 视图名称;删除视图

该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。

事件事件调度器（Event Scheduler）

在系统管理或者数据库管理中，经常要周期性的执行某一个命令或者SQL语句，Mysql在5.1以后推出了事件调度器(Event Scheduler)，在oracle可以通过编写job完成。
1.查看mysql上的事件是否开启
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;

如果为off说明没有开启
2.开启事件
   1）通过动态参数修改
     SET GLOBAL event_scheduler = ON;更改完这个参数就立刻生效了，但是mysql重启事件又会回到原来的状态
   2）更改配置文件然后重启
    my的配置文件部分添加event_scheduler=ON，然后重启mysql。
   3）通过制定事件参数启动，mysqld ... --event_scheduler=ON

3.创建事件
格式:
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}
参数说明：
DEFINER: 定义事件执行的时候检查权限的用户。
ON SCHEDULE schedule: 定义执行的时间和时间间隔。
ON COMPLETION [NOT] PRESERVE: 定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。
ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。
COMMENT 'comment': 定义事件的注释。
例子：

其他：
更改事件的语法
ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]
删除事件的语法
drop event [if exisis] event_name