MySQL视图、存储过程、游标和触发器

本文只介绍基础、常用的 MySQL 语法语句，更详细的语法语句请移步这里。

视图

1 CREATE VIEW 语法

语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

1.1 示例

CREATE VIEW cs_view
    AS
    SELECT Sno, Sname, Ssex, Sage
    FROM Student
    WHERE Sdept = 'CS';

2 ALTER VIEW 语法

语法

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

2.1 示例

 ALTER VIEW cs_view
    AS
    SELECT Sno, Sname, Ssex, Sage
    FROM Student
    WHERE Sdept = 'CS'
    AND sage <= 20;

3 SHOW CREATE VIEW 语法

语法

SHOW CREATE VIEW view_name

4 DROP VIEW 语法

语法

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

4.1 删除 cs_view 视图

DROP VIEW IF EXISTS cs_view;

存储过程

MySQL 5.0 版本开始支持存储过程。
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

示例

 DELIMITER //
CREATE PROCEDURE static_score(
        OUT min INT,
        OUT max INT,
        OUT ave DECIMAL(8,2)
        )
        BEGIN
    SELECT MIN(Grade)
    INTO min
    FROM SC;
    SELECT MAX(Grade)
    INTO max
    FROM SC;
    SELECT AVG(Grade)
    INTO ave
    FROM SC;
    END //

调用它

 CALL static_score(@scorelow, @scorehigh, @scoreaverage);

检索获得的值

SELECT @scorelow, @scorehigh, @scoreaverage;

游标

语法

1. 游标的声明

DECLARE cursor_name CURSOR FOR select_statement;

2. 游标的打开

OPEN cursor_name;

3. 游标的使用

FETCH cursor_name INTO var_name [,var_name]...

4. 游标的关闭

CLOSE cursor_name;

示例

DELIMITER //
CREATE PROCEDURE processorders()
    BEGIN
    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    -- Declare the cursor
    DECLARE sno_idx CURSOR
    FOR
    SELECT Sno FROM Student;
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    -- Open the cursor
    OPEN sno_idx;
    -- Loop through all rows
    REPEAT
    -- Get Sno
    FETCH sno_idx INTO o;
    -- End of loop
    UNTIL done END REPEAT;
    -- Close the cursor
    CLOSE sno_idx;
    END //

触发器

语法

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END;

示例

 CREATE TRIGGER newstudent AFTER INSERT ON Student
    FOR EACH ROW SELECT 'stuent added' INTO @asd;

向 Student 表插入数据后再查看

参考

1. MySQL 存储过程
2. MySQL游标（Cursor）的定义及使用
3. MySQL触发器创建