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 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!