Sql - 视图 - 函数 - 存储过程 - 触发器

视图

• 视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户可以不看整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。

• 创建视图的语法

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

• 栗子

    // usrs表结构如下
    mysql> desc users;
    +-------------------+-----------------+------+-----+---------+----------------+
    | Field             | Type            | Null | Key | Default | Extra          |
    +-------------------+-----------------+------+-----+---------+----------------+
    | id                | bigint unsigned | NO   | PRI | NULL    | auto_increment |
    | name              | varchar(255)    | NO   |     | NULL    |                |
    | email             | varchar(255)    | NO   | UNI | NULL    |                |
    | email_verified_at | timestamp       | YES  |     | NULL    |                |
    | password          | varchar(255)    | NO   |     | NULL    |                |
    | remember_token    | varchar(100)    | YES  |     | NULL    |                |
    | created_at        | timestamp       | YES  |     | NULL    |                |
    | updated_at        | timestamp       | YES  | MUL | NULL    |                |
    +-------------------+-----------------+------+-----+---------+----------------+
    8 rows in set (0.00 sec)
  
    // addrs表结构如下
    mysql> desc addrs;
    +---------+--------------+------+-----+---------+-------+
    | Field   | Type         | Null | Key | Default | Extra |
    +---------+--------------+------+-----+---------+-------+
    | id      | int          | YES  |     | NULL    |       |
    | user_id | int          | YES  |     | NULL    |       |
    | addrs   | varchar(255) | YES  |     | NULL    |       |
    +---------+--------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

  此时我们想要查询用户的地址信息时就需要联表查询，此时我们可以创建视图

    // 创建 user_addrs
    create view user_addrs as select u.name,a.addrs from users u left join addrs a on u.id=a.user_id;
    // 查看user_addrs
    mysql> desc user_addrs;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | name  | varchar(255) | NO   |     | NULL    |       |
    | addrs | varchar(255) | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)

函数

• 函数一般用于计算和返回一个值

• 创建语句如下

    CREATE
        [DEFINER = user]
        PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
        [characteristic ...] routine_body
  
    CREATE
        [DEFINER = user]
        FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
        RETURNS type
        [characteristic ...] routine_body
  
    proc_parameter:
        [ IN | OUT | INOUT ] param_name type
  
    func_parameter:
        param_name type
  
    type:
        Any valid MySQL data type
  
    characteristic: {
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    }
  
    routine_body:
        Valid SQL routine statement

• 栗子

    mysql> create function hello (s char(20)) 
                returns char(50) deterministic 
                return concat('hello, ', s,'!');
    Query OK, 0 rows affected (0.02 sec)
  
    mysql> select hello('nn');
    +-------------+
    | hello('nn') |
    +-------------+
    | hello, nn!  |
    +-------------+
    1 row in set (0.00 sec)
  
    mysql> drop function hello;
    Query OK, 0 rows affected (0.01 sec)

存储过程

• 存储过程一般用来执行一组特定的sql，可以有返回值，也可以没有返回值，需要使用call 来执行，创建语句与函数类似

• 栗子

    // 从多个数据表中删除指定的邮箱
    mysql> delimiter $$
    mysql> create PROCEDURE delete_email(IN user_email varchar(50))
        -> begin
        -> delete from users where email = user_email;
        -> delete from subscribe where email = user_email;
        -> end$$
    Query OK, 0 rows affected (0.02 sec)
  
    //使用
    call delete_email('alex@email');

触发器

• SQL触发器是存储在数据库目录中的一组SQL语句。每当与表相关联的事件发生时，就会执行或触发SQL触发器，例如插入，更新或删除。

• 创建语句

    CREATE
        [DEFINER = user]
        TRIGGER [IF NOT EXISTS] 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

• 栗子

    mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
    Query OK, 0 rows affected (0.03 sec)
  
    mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
           FOR EACH ROW SET @sum = @sum + NEW.amount;
    Query OK, 0 rows affected (0.01 sec)
  
    mysql>  SET  @sum  =  0;  
    mysql>  INSERT  INTO  account  VALUES(137,14.98),(141,1937.50),(97,-100.00);  
    mysql>  SELECT  @sum  AS  'Total amount inserted';  
    +-----------------------+  
    | Total amount inserted |  
    +-----------------------+  
    |               1852.48 |  
    +-----------------------+

本作品采用《CC 协议》，转载必须注明作者和本文链接