当前位置：Gxlcms > 数据库问题 > python-day48--mysql之视图、触发器、事务、存储过程、函数

python-day48--mysql之视图、触发器、事务、存储过程、函数

时间：2021-07-01 10:21:17 帮助过：24人阅读

两张有关系的表 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | | 4 | 美术 | 2 | +-----+--------+------------+ rows in set (0.00 sec) mysql> select * from teacher; +-----+-----------------+ | tid | tname | +-----+-----------------+ | 1 | 张磊老师 | | 2 | 李平老师 | | 3 | 刘海燕老师 | | 4 | 朱云海老师 | | 5 | 李杰老师 | +-----+-----------------+ rows in set (0.00 sec) #查询李平老师教授的课程名 mysql> select cname from course where teacher_id = (select tid from teacher where tname=‘李平老师‘); +--------+ | cname | +--------+ | 物理 | | 美术 | +--------+ rows in set (0.00 sec) #子查询出临时表，作为teacher_id等判断依据 select tid from teacher where tname=‘李平老师‘ View Code

1 创建视图

 

技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000">语法：CREATE VIEW 视图名称 AS  SQL语句</span>
  2. create view teacher_view as select tid <span style="color: #0000ff">from</span> teacher where tname=<span style="color: #800000">‘</span><span style="color: #800000">李平老师</span><span style="color: #800000">‘</span><span style="color: #000000">;
  3. </span><span style="color: #008000">#</span><span style="color: #008000">于是查询李平老师教授的课程名的sql可以改写为</span>
  4. mysql> select cname <span style="color: #0000ff">from</span> course where teacher_id = (select tid <span style="color: #0000ff">from</span><span style="color: #000000"> teacher_view);
  5. </span>+--------+
  6. | cname  |
  7. +--------+
  8. | 物理   |
  9. | 美术   |
  10. +--------+<span style="color: #000000">
  11. rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  12. </span><span style="color: #008000">#</span><span style="color: #008000">！！！注意注意注意：</span><span style="color: #008000">
  13. #</span><span style="color: #008000">1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高</span>
  14. <span style="color: #008000">#</span><span style="color: #008000">2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便</span>
View Code

 

2 使用视图

 

技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000">修改视图，原始表也跟着改</span>
  2. mysql> select * <span style="color: #0000ff">from</span><span style="color: #000000"> course;
  3. </span>+-----+--------+------------+
  4. | cid | cname  | teacher_id |
  5. +-----+--------+------------+
  6. |   1 | 生物   |          1 |
  7. |   2 | 物理   |          2 |
  8. |   3 | 体育   |          3 |
  9. |   4 | 美术   |          2 |
  10. +-----+--------+------------+<span style="color: #000000">
  11. rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  12. mysql</span>> create view course_view as select * <span style="color: #0000ff">from</span> course; <span style="color: #008000">#</span><span style="color: #008000">创建表course的视图</span>
  13. Query OK, 0 rows affected (0.52<span style="color: #000000"> sec)
  14. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> course_view;
  15. </span>+-----+--------+------------+
  16. | cid | cname  | teacher_id |
  17. +-----+--------+------------+
  18. |   1 | 生物   |          1 |
  19. |   2 | 物理   |          2 |
  20. |   3 | 体育   |          3 |
  21. |   4 | 美术   |          2 |
  22. +-----+--------+------------+<span style="color: #000000">
  23. rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  24.  
  25. mysql</span>> update course_view set cname=<span style="color: #800000">‘</span><span style="color: #800000">xxx</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">更新视图中的数据</span>
  26. Query OK, 4 rows affected (0.04<span style="color: #000000"> sec)
  27. Rows matched: </span>4  Changed: 4<span style="color: #000000">  Warnings: 0
  28. mysql</span>> insert into course_view values(5,<span style="color: #800000">‘</span><span style="color: #800000">yyy</span><span style="color: #800000">‘</span>,2); <span style="color: #008000">#</span><span style="color: #008000">往视图中插入数据</span>
  29. Query OK, 1 row affected (0.03<span style="color: #000000"> sec)
  30. mysql</span>> select * <span style="color: #0000ff">from</span> course; <span style="color: #008000">#</span><span style="color: #008000">发现原始表的记录也跟着修改了</span>
  31. +-----+-------+------------+
  32. | cid | cname | teacher_id |
  33. +-----+-------+------------+
  34. |   1 | xxx   |          1 |
  35. |   2 | xxx   |          2 |
  36. |   3 | xxx   |          3 |
  37. |   4 | xxx   |          2 |
  38. |   5 | yyy   |          2 |
  39. +-----+-------+------------+<span style="color: #000000">
  40. rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
View Code

 

我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的，如下图

技术分享

 

 

3 修改视图

 

技术分享 
  1. <span style="color: #000000">语法：ALTER VIEW 视图名称 AS SQL语句
  2. mysql</span>> alter view teacher_view as select * <span style="color: #0000ff">from</span> course where cid>3<span style="color: #000000">;
  3. Query OK, 0 rows affected (</span>0.04<span style="color: #000000"> sec)
  4. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> teacher_view;
  5. </span>+-----+-------+------------+
  6. | cid | cname | teacher_id |
  7. +-----+-------+------------+
  8. |   4 | xxx   |          2 |
  9. |   5 | yyy   |          2 |
  10. +-----+-------+------------+<span style="color: #000000">
  11. rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
View Code

 

4 删除视图

技术分享 
  1. <span style="color: #000000">语法：DROP VIEW 视图名称
  2. DROP VIEW teacher_view</span>
View Code

二、触发器（不推荐使用）

1）.使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

一 创建触发器

技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000"> 插入前</span>
  2. <span style="color: #000000">CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
  3. BEGIN
  4.     ...
  5. END
  6. </span><span style="color: #008000">#</span><span style="color: #008000"> 插入后</span>
  7. <span style="color: #000000">CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
  8. BEGIN
  9.     ...
  10. END
  11. </span><span style="color: #008000">#</span><span style="color: #008000"> 删除前</span>
  12. <span style="color: #000000">CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
  13. BEGIN
  14.     ...
  15. END
  16. </span><span style="color: #008000">#</span><span style="color: #008000"> 删除后</span>
  17. <span style="color: #000000">CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
  18. BEGIN
  19.     ...
  20. END
  21. </span><span style="color: #008000">#</span><span style="color: #008000"> 更新前</span>
  22. <span style="color: #000000">CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
  23. BEGIN
  24.     ...
  25. END
  26. </span><span style="color: #008000">#</span><span style="color: #008000"> 更新后</span>
  27. <span style="color: #000000">CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
  28. BEGIN
  29.     ...
  30. END</span>
View Code 技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000">准备表</span>
  2. <span style="color: #000000">CREATE TABLE cmd (
  3.     id INT PRIMARY KEY auto_increment,
  4.     USER CHAR (</span>32<span style="color: #000000">),
  5.     priv CHAR (</span>10<span style="color: #000000">),
  6.     cmd CHAR (</span>64<span style="color: #000000">),
  7.     sub_time datetime, </span><span style="color: #008000">#</span><span style="color: #008000">提交时间</span>
  8.     success enum (<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span>) <span style="color: #008000">#</span><span style="color: #008000">0代表执行失败</span>
  9. <span style="color: #000000">);
  10. CREATE TABLE errlog (
  11.     id INT PRIMARY KEY auto_increment,
  12.     err_cmd CHAR (</span>64<span style="color: #000000">),
  13.     err_time datetime
  14. );
  15. </span><span style="color: #008000">#</span><span style="color: #008000">创建触发器</span>
  16. delimiter //<span style="color: #000000">
  17. CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
  18. BEGIN
  19.     IF NEW.success </span>= <span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span> THEN <span style="color: #008000">#</span><span style="color: #008000">等值判断只有一个等号</span>
  20.             INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; <span style="color: #008000">#</span><span style="color: #008000">必须加分号</span>
  21.       END IF ; <span style="color: #008000">#</span><span style="color: #008000">必须加分号</span>
  22. END//<span style="color: #000000">
  23. delimiter ;
  24. </span><span style="color: #008000">#</span><span style="color: #008000">往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志</span>
  25. <span style="color: #000000">INSERT INTO cmd (
  26.     USER,
  27.     priv,
  28.     cmd,
  29.     sub_time,
  30.     success
  31. )
  32. VALUES
  33.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">ls -l /etc</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span><span style="color: #000000">),
  34.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">cat /etc/passwd</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span><span style="color: #000000">),
  35.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">useradd xxx</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span><span style="color: #000000">),
  36.     (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">ps aux</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span><span style="color: #000000">);
  37. </span><span style="color: #008000">#</span><span style="color: #008000">查询错误日志，发现有两条</span>
  38. mysql> select * <span style="color: #0000ff">from</span><span style="color: #000000"> errlog;
  39. </span>+----+-----------------+---------------------+
  40. | id | err_cmd         | err_time            |
  41. +----+-----------------+---------------------+
  42. |  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
  43. |  2 | useradd xxx     | 2017-09-14 22:18:48 |
  44. +----+-----------------+---------------------+<span style="color: #000000">
  45. rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
View Code

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

二 使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

三 删除触发器

技术分享 
  1. drop trigger tri_after_insert_cmd;
View Code

三 事务（要会）

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

技术分享 
  1. <span style="color: #000000">create table user1(
  2. id int primary key auto_increment,
  3. name char(</span>32<span style="color: #000000">),
  4. balance int
  5. );
  6. insert into user1(name,balance) values
  7. (</span><span style="color: #800000">‘</span><span style="color: #800000">庞文废</span><span style="color: #800000">‘</span>,200<span style="color: #000000">),
  8. (</span><span style="color: #800000">‘</span><span style="color: #800000">萧pt</span><span style="color: #800000">‘</span>,200<span style="color: #000000">),
  9. (</span><span style="color: #800000">‘</span><span style="color: #800000">八哥</span><span style="color: #800000">‘</span>,200<span style="color: #000000">);
  10. start transaction;
  11. update user1 set balance</span>=100 where name=<span style="color: #800000">‘</span><span style="color: #800000">庞文废</span><span style="color: #800000">‘</span><span style="color: #000000">;
  12. update user1 set balance</span>=210 where name=<span style="color: #800000">‘</span><span style="color: #800000">萧pt</span><span style="color: #800000">‘</span><span style="color: #000000">;
  13. updsate user1 set balance</span>=290 where name=<span style="color: #800000">‘</span><span style="color: #800000">八哥</span><span style="color: #800000">‘</span><span style="color: #000000">;
  14. rollback; </span><span style="color: #008000">#</span><span style="color: #008000">如果任意一条sql出现异常都应该回滚到初始状态</span>
  15. commit; <span style="color: #008000">#</span><span style="color: #008000">如果所有的sql都正常，应该执行commit</span>
View Code

疑问：如何捕捉异常，在下方存储过程中解决

四 存储过程（不推荐使用）

一 介绍

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

使用存储过程的优点：

  1. <span style="color: #008000">#<span style="color: #008000">1. 用于替代程序写的SQL语句，实现程序与sql解耦
  2. <span style="color: #008000">#<span style="color: #008000">2. 基于网络传输，传别名的数据量小，而直接传sql数据量大</span></span></span></span>

使用存储过程的缺点：

  1. <span style="color: #008000">#<span style="color: #008000">1. 执行效率低
  2. <span style="color: #008000">#<span style="color: #008000">2. 程序员扩展功能不方便</span></span></span></span>

补充：程序与数据库结合使用的两种方式

技术分享 
  1. <span style="color: #008000">#<span style="color: #008000">方式一：
  2. <span style="color: #000000">    MySQL：存储过程
  3.     程序：调用存储过程
  4. <span style="color: #008000">#<span style="color: #008000">方式二：
  5. <span style="color: #000000">    MySQL：
  6.     程序：纯SQL语句
  7. </span></span></span></span></span></span>

二 创建简单存储过程（无参）

技术分享 
  1. delimiter //<span style="color: #000000">
  2. create procedure p1()
  3. BEGIN
  4.     select </span>* <span style="color: #0000ff">from</span><span style="color: #000000"> blog;
  5.     INSERT into blog(name,sub_time) values(</span><span style="color: #800000">"</span><span style="color: #800000">xxx</span><span style="color: #800000">"</span><span style="color: #000000">,now());
  6. END </span>//<span style="color: #000000">
  7. delimiter ;
  8. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
  9. <span style="color: #000000">call p1() 
  10. </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用</span>
  11. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p1</span><span style="color: #800000">‘</span><span style="color: #000000">) 
  12. </span><span style="color: #0000ff">print</span>(cursor.fetchall())
View Code

三 创建存储过程（有参）

技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000">===============================================</span><span style="color: #008000">
  2. #</span><span style="color: #008000">创建有参存储过程之in的使用</span>
  3. delimiter //<span style="color: #000000">
  4. create procedure p2(
  5.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> m int,
  6.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> n int,
  7. )
  8. begin
  9.     select </span>* <span style="color: #0000ff">from</span> test where id between m <span style="color: #0000ff">and</span><span style="color: #000000"> n;
  10. end </span>//<span style="color: #000000">
  11. delimiter ;
  12. </span><span style="color: #008000">#</span><span style="color: #008000">调用存储过程</span>
  13. call p2(3,7); <span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
  14. cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p2</span><span style="color: #800000">‘</span>,args=(3,7)) <span style="color: #008000">#</span><span style="color: #008000">在python中通过pymysql模块调用</span>
  15. <span style="color: #008000">#</span><span style="color: #008000">===============================================</span><span style="color: #008000">
  16. #</span><span style="color: #008000">创建有参存储过程之out的使用</span>
  17. delimiter //<span style="color: #000000">
  18. create procedure p3(
  19.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> m int,
  20.     </span><span style="color: #0000ff">in</span><span style="color: #000000"> n int,
  21.     out res int
  22. )
  23. begin
  24.     select </span>* <span style="color: #0000ff">from</span> test where id between m <span style="color: #0000ff">and</span><span style="color: #000000"> n;
  25.     set res</span>=1<span style="color: #000000">;
  26. end </span>//<span style="color: #000000">
  27. delimiter ;
  28. </span><span style="color: #008000">#</span><span style="color: #008000">调用存储过程</span><span style="color: #008000">
  29. #</span><span style="color: #008000">在mysql中</span>
  30. set @x=11111111111<span style="color: #000000">
  31. call p3(</span>3,7,@x); <span style="color: #008000">#</span><span style="color: #008000">在mysql中调用, 查看结果：select @x;</span>
  32. <span style="color: #008000">#</span><span style="color: #008000">在python中</span>
  33. res=cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p3</span><span style="color: #800000">‘</span>,args=(3,7,123)) <span style="color: #008000">#</span><span style="color: #008000">@_p3_0=3,@_p3_1=7,@_p3_2=123</span>
  34. <span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">只是拿到存储过程中select的查询结果</span>
  35. cursor.execute(<span style="color: #800000">‘</span><span style="color: #800000">select @_p3_0,@_p3_1,@_p3_2</span><span style="color: #800000">‘</span><span style="color: #000000">)
  36. </span><span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">可以拿到的是返回值</span>
  37. <span style="color: #008000">#</span><span style="color: #008000">===============================================</span><span style="color: #008000">
  38. #</span><span style="color: #008000">创建有参存储过程之inout的使用</span>
  39. <span style="color: #000000">
  40. delimiter </span>//<span style="color: #000000">
  41. create procedure p4(
  42.     inout m int
  43. )
  44. begin
  45.     select </span>* <span style="color: #0000ff">from</span> test where id ><span style="color: #000000"> m;
  46.     set m</span>=1<span style="color: #000000">;
  47. end </span>//<span style="color: #000000">
  48. delimiter ;
  49. </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中</span>
  50. set @x=2<span style="color: #000000">;
  51. call p4(@x);
  52. select @x;</span>
View Code

四 删除存储过程

 

技术分享 
  1. drop procedure proc_name;
View Code

 

五  捕捉异常+事务

技术分享 
  1. delimiter //<span style="color: #000000">
  2. create procedure p5(
  3.     inout m int
  4. )
  5. begin
  6.     select </span>* <span style="color: #0000ff">from</span> test11111111 where id ><span style="color: #000000"> m;
  7.     set m</span>=1<span style="color: #000000">;
  8. end </span>//<span style="color: #000000">
  9. delimiter ;
  10. set @x</span>=2<span style="color: #000000">;
  11. call p5(@x);
  12. select @x;</span>
引子 技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000">====================捕捉异常+事务===========================</span>
  2. delimiter //<span style="color: #000000">
  3. create PROCEDURE p6(
  4.     OUT p_return_code tinyint
  5. )
  6. BEGIN
  7.     DECLARE exit handler </span><span style="color: #0000ff">for</span><span style="color: #000000"> sqlexception
  8.     BEGIN
  9.         </span>--<span style="color: #000000"> ERROR
  10.         set p_return_code </span>= 1<span style="color: #000000">;
  11.         rollback;
  12.     END;
  13.     DECLARE exit handler </span><span style="color: #0000ff">for</span><span style="color: #000000"> sqlwarning
  14.     BEGIN
  15.         </span>--<span style="color: #000000"> WARNING
  16.         set p_return_code </span>= 2<span style="color: #000000">;
  17.         rollback;
  18.     END;
  19.     START TRANSACTION;
  20.         insert into test(username,dep_id) values(</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,1<span style="color: #000000">);
  21.         DELETE </span><span style="color: #0000ff">from</span> tb1111111; <span style="color: #008000">#</span><span style="color: #008000">执行失败</span>
  22. <span style="color: #000000">    COMMIT;
  23.     </span>--<span style="color: #000000"> SUCCESS
  24.     set p_return_code </span>= 0; <span style="color: #008000">#</span><span style="color: #008000">0代表执行成功</span>
  25. <span style="color: #000000">
  26. END </span>//<span style="color: #000000">
  27. delimiter ;
  28. </span><span style="color: #008000">#</span><span style="color: #008000">用python模拟</span>
  29. <span style="color: #0000ff">try</span><span style="color: #000000">:
  30.     START TRANSACTION;
  31.         DELETE </span><span style="color: #0000ff">from</span> tb1; <span style="color: #008000">#</span><span style="color: #008000">执行失败</span>
  32.         insert into blog(name,sub_time) values(<span style="color: #800000">‘</span><span style="color: #800000">yyy</span><span style="color: #800000">‘</span><span style="color: #000000">,now());
  33.     COMMIT;
  34.     set p_return_code </span>= 0; <span style="color: #008000">#</span><span style="color: #008000">0代表执行成功</span>
  35. <span style="color: #0000ff">except</span><span style="color: #000000"> sqlexception:
  36.     set p_return_code </span>= 1<span style="color: #000000">;
  37.     rollback;
  38. </span><span style="color: #0000ff">except</span><span style="color: #000000"> sqlwaring:
  39.     set p_return_code </span>= 2<span style="color: #000000">;
  40.     rollback;</span>
View Code

五、函数  （注意，必须在sql语句中执行函数）

MySQL中提供了许多内置函数，例如：

技术分享 
  1. <span style="color: #000000">CHAR_LENGTH(str)
  2.         返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
  3.         对于一个包含五个二字节字符集, LENGTH()返回值为 </span>10<span style="color: #000000">, 而CHAR_LENGTH()的返回值为5。
  4.     CONCAT(str1,str2,...)
  5.         字符串拼接
  6.         如有任何一个参数为NULL ，则返回值为 NULL。
  7.     CONCAT_WS(separator,str1,str2,...)
  8.         字符串拼接（自定义连接符）
  9.         CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
  10.     CONV(N,from_base,to_base)
  11.         进制转换
  12.         例如：
  13.             SELECT CONV(</span><span style="color: #800000">‘</span><span style="color: #800000">a</span><span style="color: #800000">‘</span>,16,2<span style="color: #000000">); 表示将 a 由16进制转换为2进制字符串表示
  14.     FORMAT(X,D)
  15.         将数字X 的格式写为</span><span style="color: #800000">‘</span><span style="color: #800000">#,###,###.##</span><span style="color: #800000">‘</span><span style="color: #000000">,以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
  16.         例如：
  17.             SELECT FORMAT(</span>12332.1,4); 结果为： <span style="color: #800000">‘</span><span style="color: #800000">12,332.1000</span><span style="color: #800000">‘</span><span style="color: #000000">
  18.     INSERT(str,pos,len,newstr)
  19.         在str的指定位置插入字符串
  20.             pos：要替换位置其实位置
  21.             len：替换的长度
  22.             newstr：新字符串
  23.         特别的：
  24.             如果pos超过原字符串长度，则返回原字符串
  25.             如果len超过原字符串长度，则由新字符串完全替换
  26.     INSTR(str,substr)
  27.         返回字符串 str 中子字符串的第一个出现位置。
  28.     LEFT(str,len)
  29.         返回字符串str 从开始的len位置的子序列字符。
  30.     LOWER(str)
  31.         变小写
  32.     UPPER(str)
  33.         变大写
  34.     LTRIM(str)
  35.         返回字符串 str ，其引导空格字符被删除。
  36.     RTRIM(str)
  37.         返回字符串 str ，结尾空格字符被删去。
  38.     SUBSTRING(str,pos,len)
  39.         获取字符串子序列
  40.     LOCATE(substr,str,pos)
  41.         获取子序列索引位置
  42.     REPEAT(str,count)
  43.         返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
  44.         若 count </span><=<span style="color: #000000"> 0,则返回一个空字符串。
  45.         若str 或 count 为 NULL，则返回 NULL 。
  46.     REPLACE(str,from_str,to_str)
  47.         返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
  48.     REVERSE(str)
  49.         返回字符串 str ，顺序和字符顺序相反。
  50.     RIGHT(str,len)
  51.         从字符串str 开始，返回从后边开始len个字符组成的子序列
  52.     SPACE(N)
  53.         返回一个由N空格组成的字符串。
  54.     SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
  55.         不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
  56.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Quadratically</span><span style="color: #800000">‘</span>,5<span style="color: #000000">);
  57.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ratically</span><span style="color: #800000">‘</span><span style="color: #000000">
  58.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">foobarbar</span><span style="color: #800000">‘</span> FROM 4<span style="color: #000000">);
  59.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barbar</span><span style="color: #800000">‘</span><span style="color: #000000">
  60.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Quadratically</span><span style="color: #800000">‘</span>,5,6<span style="color: #000000">);
  61.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ratica</span><span style="color: #800000">‘</span><span style="color: #000000">
  62.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span>, -3<span style="color: #000000">);
  63.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ila</span><span style="color: #800000">‘</span><span style="color: #000000">
  64.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span>, -5, 3<span style="color: #000000">);
  65.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">aki</span><span style="color: #800000">‘</span><span style="color: #000000">
  66.         mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span> FROM -4 FOR 2<span style="color: #000000">);
  67.             </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ki</span><span style="color: #800000">‘</span><span style="color: #000000">
  68.     TRIM([{BOTH </span>| LEADING |<span style="color: #000000"> TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
  69.         返回字符串 str ， 其中所有remstr 前缀和</span>/<span style="color: #000000">或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。
  70.         mysql</span>> SELECT TRIM(<span style="color: #800000">‘</span><span style="color: #800000">  bar   </span><span style="color: #800000">‘</span><span style="color: #000000">);
  71.                 </span>-> <span style="color: #800000">‘</span><span style="color: #800000">bar</span><span style="color: #800000">‘</span><span style="color: #000000">
  72.         mysql</span>> SELECT TRIM(LEADING <span style="color: #800000">‘</span><span style="color: #800000">x</span><span style="color: #800000">‘</span> FROM <span style="color: #800000">‘</span><span style="color: #800000">xxxbarxxx</span><span style="color: #800000">‘</span><span style="color: #000000">);
  73.                 </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barxxx</span><span style="color: #800000">‘</span><span style="color: #000000">
  74.         mysql</span>> SELECT TRIM(BOTH <span style="color: #800000">‘</span><span style="color: #800000">x</span><span style="color: #800000">‘</span> FROM <span style="color: #800000">‘</span><span style="color: #800000">xxxbarxxx</span><span style="color: #800000">‘</span><span style="color: #000000">);
  75.                 </span>-> <span style="color: #800000">‘</span><span style="color: #800000">bar</span><span style="color: #800000">‘</span><span style="color: #000000">
  76.         mysql</span>> SELECT TRIM(TRAILING <span style="color: #800000">‘</span><span style="color: #800000">xyz</span><span style="color: #800000">‘</span> FROM <span style="color: #800000">‘</span><span style="color: #800000">barxxyz</span><span style="color: #800000">‘</span><span style="color: #000000">);
  77.                 </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barx</span><span style="color: #800000">‘</span>
View Code 技术分享 
  1. <span style="color: #008000">#</span><span style="color: #008000">1 基本使用</span>
  2. mysql> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2009-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%W %M %Y</span><span style="color: #800000">‘</span><span style="color: #000000">);
  3.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">Sunday October 2009</span><span style="color: #800000">‘</span><span style="color: #000000">
  4. mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2007-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%H:%i:%s</span><span style="color: #800000">‘</span><span style="color: #000000">);
  5.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">
  6. mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1900-10-04 22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">,
  7.     </span>->                 <span style="color: #800000">‘</span><span style="color: #800000">%D %y %a %d %m %b %j</span><span style="color: #800000">‘</span><span style="color: #000000">);
  8.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">4th 00 Thu 04 10 Oct 277</span><span style="color: #800000">‘</span><span style="color: #000000">
  9. mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1997-10-04 22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">,
  10.     </span>->                 <span style="color: #800000">‘</span><span style="color: #800000">%H %k %I %r %T %S %w</span><span style="color: #800000">‘</span><span style="color: #000000">);
  11.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">22 22 10 10:23:00 PM 22:23:00 00 6</span><span style="color: #800000">‘</span><span style="color: #000000">
  12. mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1999-01-01</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%X %V</span><span style="color: #800000">‘</span><span style="color: #000000">);
  13.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">1998 52</span><span style="color: #800000">‘</span><span style="color: #000000">
  14. mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2006-06-00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%d</span><span style="color: #800000">‘</span><span style="color: #000000">);
  15.         </span>-> <span style="color: #800000">‘</span><span style="color: #800000">00</span><span style="color: #800000">‘</span>
  16. <span style="color: #008000">#</span><span style="color: #008000">2 准备表和记录</span>
  17. <span style="color: #000000">CREATE TABLE blog (
  18.     id INT PRIMARY KEY auto_increment,
  19.     NAME CHAR (</span>32<span style="color: #000000">),
  20.     sub_time datetime
  21. );
  22. INSERT INTO blog (NAME, sub_time)
  23. VALUES
  24.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第1篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2015-03-01 11:31:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
  25.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第2篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2015-03-11 16:31:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
  26.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第3篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-01 10:21:31</span><span style="color: #800000">‘</span><span style="color: #000000">),
  27.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第4篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-22 09:23:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
  28.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第5篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-23 10:11:11</span><span style="color: #800000">‘</span><span style="color: #000000">),
  29.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第6篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-25 11:21:31</span><span style="color: #800000">‘</span><span style="color: #000000">),
  30.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第7篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2017-03-01 15:33:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
  31.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第8篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2017-03-01 17:32:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
  32.     (</span><span style="color: #800000">‘</span><span style="color: #800000">第9篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2017-03-01 18:31:21</span><span style="color: #800000">‘</span><span style="color: #000000">);
  33. </span><span style="color: #008000">#</span><span style="color: #008000">3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组</span>
  34. SELECT DATE_FORMAT(sub_time,<span style="color: #800000">‘</span><span style="color: #800000">%Y-%m</span><span style="color: #800000">‘</span>),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,<span style="color: #800000">‘</span><span style="color: #800000">%Y-%m</span><span style="color: #800000">‘</span><span style="color: #000000">);
  35. </span><span style="color: #008000">#</span><span style="color: #008000">结果</span>
  36. +-------------------------------+----------+
  37. | DATE_FORMAT(sub_time,<span style="color: #800000">‘</span><span style="color: #800000">%Y-%m</span><span style="color: #800000">‘</span>) | COUNT(1) |
  38. +-------------------------------+----------+
  39. | 2015-03                       |        2 |
  40. | 2016-07                       |        4 |
  41. | 2017-03                       |        3 |
  42. +-------------------------------+----------+<span style="color: #000000">
  43. rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
需要掌握函数：date_format

更多：

 

技术分享

技术分享

一 自定义函数

技术分享 
  1. <span style="font-size: 15px">delimiter //<span style="color: #000000">
  2. create function f1(
  3.     i1 int,
  4.     i2 int)
  5. returns int
  6. BEGIN
  7.     declare num int;
  8.     set num </span>= i1 +<span style="color: #000000"> i2;
  9.     </span><span style="color: #0000ff">return</span><span style="color: #000000">(num);
  10. END </span>//<span style="color: #000000">
  11. delimiter ;</span></span>
View Code

二 删除函数

技术分享 
  1. <span style="font-size: 15px">drop function func_name;</span>
View Code

三 执行函数

技术分享 
  1. <span style="font-size: 15px"><span style="color: #008000">#</span><span style="color: #008000"> 获取返回值</span>
  2. select UPPER(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">) into @res;
  3. SELECT @res;
  4. </span><span style="color: #008000">#</span><span style="color: #008000"> 在查询中使用</span>
  5. select f1(11,nid) ,name <span style="color: #0000ff">from</span> tb2;</span>
View Code

六、流程控制

一 条件语句

技术分享 
  1. <span style="font-size: 15px">delimiter //<span style="color: #000000">
  2. CREATE PROCEDURE proc_if ()
  3. BEGIN
  4.     
  5.     declare i int default 0;
  6.     </span><span style="color: #0000ff">if</span> i = 1<span style="color: #000000"> THEN
  7.         SELECT </span>1<span style="color: #000000">;
  8.     ELSEIF i </span>= 2<span style="color: #000000"> THEN
  9.         SELECT </span>2<span style="color: #000000">;
  10.     ELSE
  11.         SELECT </span>7<span style="color: #000000">;
  12.     END IF;
  13. END </span>//<span style="color: #000000">
  14. delimiter ;</span></span>
if条件语句

二 循环语句

技术分享 
  1. delimiter //<span style="color: #000000">
  2. CREATE PROCEDURE proc_while ()
  3. BEGIN
  4.     DECLARE num INT ;
  5.     SET num </span>=<span style="color: #000000"> 0 ;
  6.     WHILE num </span>< 10<span style="color: #000000"> DO
  7.         SELECT
  8.             num ;
  9.         SET num </span>= num + 1<span style="color: #000000"> ;
  10.     END WHILE ;
  11. END </span>//<span style="color: #000000">
  12. delimiter ;</span>
while循环

 

python-day48--mysql之视图、触发器、事务、存储过程、函数

标签：width   title   splay   closed   流程控制   基本   tran   values   row   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！