SQL基础随记2 视图 存储过程

 

View

  • CREATE/ALTER/DROP VIEW ViewName as SELECT(...)
    
  • 可以在视图的基础上继续创建视图,即,将之前创建的视图当做表名放在新视图的select语句中

    SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。
  • 利用视图进行数据格式化(就是对字段进行重新组合与拼接)

    CREATE VIEW View_player_team AS 
    SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team
    FROM player JOIN team WHERE player.team_id = team.team_id
    
  • 视图具有安全性,因为视图是虚拟表,即使对视图做出了修改,也不会影响底层数据。

 

PROCEDURE

  • 存储过程就像“自定义函数”,它是SQL语句和流控制语句的集合,它可以接受参数,也可以返回参数。

  • 存储过程可以(不是一定)直接对底层数据表进行操作

  • CREATE PROCEDURE name(参数)
    BEGIN
    	需要执行的过程
    END
    

    举例1,

    DELIMITER //
    
    CREATE PROCEDURE `存储过程名`(形参)
    BEGIN
    	循环体
    END //
    
    DELIMITER;
    

    调用存储过程举例

    CALL `存储过程名`(实参);
    select @(out参数)
    
    • DELIMITER是将分隔符变为 // (也可以使用其他的),因为流控制语句中的语句需要;分割,此举是避免混淆

    • 开头和结尾的DELIMITER都是另起一行mysql shell

    • 存储过程名用的不是单引号,是英文的 "顿点"

    • 流控制语句

      DECLIARE --- 声明变量
      SET --- 初始化,赋值
      IF...ELSE...
      
      REPEAT...UNTIL...END REPEAT --- 满足UNTIL后面的表达式则退出循环
      WHILE...DO...END WHILE --- 先判断条件,满足则循环
      CONTINUT;
      BREAK;
      RETURN;
      GOTO (label)
      WAITFOR TIME 'time' --- 在某个时刻执行
      WAITFOR DELAY 'time' --- 延迟某个时间执行
      SELECT...INTO... --- 将查询表中的结果放入变量中
      
  • 参数类型

    • IN --- 传入参数,无法返回,不写参数类型默认为IN
    • OUT --- 将存储过程的计算结果放入OUT参数中并用于返回
    • INOUT --- 既用于传入参数又可以将结果存入该参数中用于返回
  • PROCEDURE的优点

    • 安全性强,设定存储过程的时候可以设置对用户的使用权限
    • 执行效率快,在MySQL数据库服务器端执行
    • 通过封装,减少网络传输量
  • PROCEDURE的缺点 --- 阿里等公司不推荐使用的原因

    • 调试困难,仅少部分DBMS支持调试
    • 可移植性差
    • 版本管理困难
    • 不适合高并发场景 --- 高并发需要减小数据库压力,可能会采用分库分表的方式且对拓展性要求高。在这种情况下,存储过程变得难以维护且会增加数据库的压力

 

procedure 举例1 按某个字段对表进行查询

  • 查看已有测试表

    mysql> SELECT * FROM t; 
    +----+------+
    | Id | nums |
    +----+------+
    |  1 |    2 |
    +----+------+
    1 row in set (0.00 sec)
    

    创建存储过程

    mysql> DELIMITER //
    mysql> CREATE PROCEDURE `findByNums`(IN n int)
        -> BEGIN
        -> SELECT * FROM t WHERE nums = n;
        -> END //
    Query OK, 0 rows affected (0.03 sec)
    

    检验

    mysql> DELIMITER ;
    mysql> CALL findByNums(2);
    +----+------+
    | Id | nums |
    +----+------+
    |  1 |    2 |
    +----+------+
    1 row in set (0.03 sec)
    
    Query OK, 0 rows affected (0.03 sec)
    

    (传入不同参数)

    mysql> CALL findByNums(1);
    Empty set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    

     

  • 查询存储过程

    mysql> SELECT * FROM mysql.proc WHERE db = 'test' AND type = 'procedure';
    

 

procedure 举例2 计算累和结果

  • 使用存储过程传入/传出参数查询输出

    mysql> delimiter //
    mysql> create procedure `add_sum`(in n int, out result int)
        -> begin
        -> declare i int;
        -> declare sum int;
        -> set i = 1;
        -> set sum = 0;
        -> while i < n do
        -> set sum = sum + i;
        -> set i = i + 1;
        -> end while;
        -> end //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> call add_sum(50,@result);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @result;
    +---------+
    | @result |
    +---------+
    |    1225 |
    +---------+
    1 row in set (0.00 sec)
    
    
  • 无法直接@存储过程中的变量

    mysql> create procedure `add_sum_procedure`(in n int)
        -> begin
        -> declare i int;
        -> declare sum int;
        -> set i = 1;
        -> set sum = 0;
        -> while i < n do
        -> set sum = sum + i;
        -> set i = i + 1;
        -> end while;
        -> end //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> call add_sum_procedure(10);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select @sum;
    +------+
    | @sum |
    +------+
    | NULL |
    +------+
    1 row in set (0.01 sec)
    

查询所有存储过程/视图 等等

 

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄