Part 1 —— 视图、触发器trigger、存储过程、函数

今日内容概要

  1. 1、'视图(了解)'
  2. 2、'触发器trigger'
  3. 3、事务
  4. 4、'存储过程procedure'
  5. 5、函数
  6. 6、流程控制
  7. 7、函数
  8. 8、流程控制
  9. 9、索引与慢查询优化
  10. 10、索引分类
  12. 1.自行百度搜索并提炼"数据库设计三大范式"
  13.     分别是哪三大以及各自有何特征
  14. 2.整理今日内容及博客
  15. 3.自行复习python基础

第七章 · part1-视图、触发器、存储过程、函数 - 图1

储备:

1、delimiter

  1. delimiter $$        # 临时修改语句结束符为 :  $$
  2. delimiter ;            # 修改语句结束符为： ;

2、mysql变量

  1. 定义：
  2.     set [变量名]=[变量值];
  3. 查看变量值:
  4.     select @[变量名];
  6. 案例：
  7. set res=10;    #创建一个res变量，变量值为10。
  8. select @res;    #查看res的变量值，以表格的形式输出

一、视图view(了解)

  1. 视图是一个'虚拟表'（非真实存在），其本质是"根据SQL语句获取动态的数据集，并为其命名",用户使用时只需使用"名称"即可获取结果集，可以将该结果集当做表来使用。
  3. 使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用
  4. 将SQL语句的查询结果当做虚拟表实体化保存起来，以后可以反复使用
  6. #1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高
  8. #2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便
  9. # 视图使用频率不高，了解即可
  11. 1、创建
  12. create view teacher2course as
  13. select * from teacher inner join course on teacher.tid = course.teacher_id;
  15. 2、删除
  16. drop view teacher2course;

二、触发器trigger(类似于后台进程)

1、介绍

  1. 触发器:满足特点条件之后自动执行"增、删、改"操作时"前后"的行为，注意：没有查询
  2. 在MySQL只有三种情况下可以触发
  3.     1.针对表的增
  4.         增前 增后
  5.     2.针对表的改
  6.         改前 改后
  7.     3.针对表的删
  8.         删前 删后

2、语法格式和使用方法

  1. 1、'完整语法结构'
  2. delimiter $$
  3. create trigger [触发器名字] [before/after] [insert/update/delete] on 表名 for each row
  4. begin
  5.     sql语句;    # NEW，OLD关键字填写在sql内
  6. end $$
  7. delimiter ;
  9. 2、'查看触发器'
  10. show triggers;
  12. 3、'删除触发器'
  13. drop trigger tri_after_insert_cmd;

3、mysql触发器内( NEW与OLD )解析

  1. mysql触发器中， NEW关键字，和 MS SQL Server 中的 INSERTED 和 DELETED 类似，MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。
  3. 具体地：
  4. 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
  5. 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
  6. 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
  7. 使用方法： NEW.columnName （columnName 为相应数据表某一列名）
  8. 另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用。

4、注意点

  1. -----------------------------------"注意点"---------------------------------------
  2. 1、"触发器的命名规范"
  3.     tri_after_insert_t1
  4.     # 或者
  5.     tri_before_delete_t1
  7. 2、# 在MySQL中NEW特指数据对象可以通过点的方式获取字段对应的数据
  8. 如果有一个表为:
  9.     id    name    pwd  hobby
  10.     1     jason  123  read
  11. mysql>NEW.name
  13. 3、# 临时修改SQL语句的结束符，仅在当前窗口有效
  14. """
  15. 需要注意： 在书写sql代码的时候结束符是 ; 而整个触发器的结束也需要分号 ; 
  16. 这就会出现语法冲突 需要我们临时修改结束符号
  17. """

5、触发器案例

  1. # 案例：创建一个记录错误日志的表
  3. ----------------------------------'创建cmd表和errlog表'---------------------------------
  4. CREATE TABLE cmd (
  5.     id INT PRIMARY KEY auto_increment,
  6.     USER CHAR (32),
  7.     priv CHAR (10),
  8.     cmd CHAR (64),
  9.     sub_time datetime, #提交时间
  10.     success enum ('yes', 'no') #0代表执行失败
  11. );
  13. CREATE TABLE errlog (
  14.     id INT PRIMARY KEY auto_increment,
  15.     err_cmd CHAR (64),
  16.     err_time datetime
  17. );
  18. ----------------------------------'创建触发器'---------------------------------
  19. delimiter $$  # 将mysql默认的结束符由;换成$$
  20. create trigger tri_after_insert_cmd after insert on cmd for each row
  21. begin
  22.     if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
  23.         insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
  24.     end if;
  25. end $$
  26. delimiter ;  
  28. ------------------------------'向cmd插入数据，触发器起作用'-------------------------------
  29. #往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
  30. INSERT INTO cmd (
  31.     USER,
  32.     priv,
  33.     cmd,
  34.     sub_time,
  35.     success
  36. )
  37. VALUES
  38.     ('tony','0755','ls -l /etc',NOW(),'yes'),
  39.     ('tony','0755','cat /etc/passwd',NOW(),'no'),
  40.     ('tony','0755','useradd xxx',NOW(),'no'),
  41.     ('tony','0755','ps aux',NOW(),'yes');
  43. # 查询errlog表记录
  44. select * from errlog;        # 只向cmd表插入数据，但是触发器起作用， errlog表顺利记录2条success=no
  45. ----------------------------------'其他操作'---------------------------------
  46. # 查看触发器
  47. show triggers;
  48. # 删除触发器
  49. drop trigger tri_after_insert_cmd;

三、存储过程 procedure(mysql自定义函数) (开发不用写，DBA写)

1、介绍

  1. # 类似于python中的自定义函数
  2. # 操作和定义函数，调用函数一样
  3. # 所以必须知道如何定义一个全局变量，函数的参数规范

2、语法格式和使用方法

定义：

  1. 1、'无参存储过程'
  2.     |delimiter $$
  3.     |create procedure p1()
  4.     |begin
  5.     |    select * from cmd;
  6.     |end $$
  7.     |delimiter ;
  9. 2、'有参存储过程'
  10.     |delimiter $$
  11.     |"定义函数名，以及定义形参"
  12.     |create procedure p2(    # p2是函数名
  13.     |    [in/out/inout] [形参名1] [数据类型],    
  14.     |    [in/out/inout] [形参名1] [数据类型],    
  15.     |)
  16.     |begin
  17.     |    'sql语句';    # 就相当于函数体代码
  18.     |end $$
  19.     |delimiter ;
  21. '对于存储过程，可以接收参数，其参数有三类':
  22.     in          仅用于传入参数用
  23.     out        仅用于返回值用
  24.     inout     既可以传入又可以当作返回值

存储过程方法

  1. 1、调用
  2.     call [存储过程名] (参数);
  3. 2、查看某个具体存储过程
  4.     show create procedure [存储过程名];
  5. 3、查看所有的存储过程
  6.     show procedure status;
  7. 4、删除某个存储过程
  8.     drop procedure [存储过程名];

3、案例

  1. --------------------------------------"基础表"--------------------------------------
  2. mysql> select * from userinfo;
  3. +----+----------+------+--------+-----------+
  4. | id | password | name | money  | food_menu |
  5. +----+----------+------+--------+-----------+
  6. | 1  | 111      | aaa  |  10000 |           |
  7. | 2  | 222      | bbb  |  10000 |           |
  8. | 3  | 333      | ccc  |  10000 |           |
  9. | 4  | 123      | yly  | 999999 |           |
  10. | 5  | 321      | zpx  | 999999 |           |
  11. | 7  | 123      | ddd  |  10000 |           |
  12. | 8  | 111      | nnn  |      0 |           |
  13. +------+----------+----+--------+-----------+
  14. 7 rows in set (0.00 sec)
  17. ---------------------------------------"案例"-------------------------------------
  18. # 1.定义无参存储过程
  19.     delimiter $$
  20.     create procedure p1()
  21.     begin
  22.         select * from cmd;
  23.     end $$
  24.     delimiter ;
  25.     # 调用
  26.     call p1()
  29. # 2.定义有参存储过程
  30. # mysql客户端
  31.     delimiter $$
  32.     "定义函数名，以及定义形参"
  33.     create procedure p2(    # p2是函数名
  34.         in m int,  # in表示这个参数必须只能是传入不能被返回出去
  35.         in n int,  # n为形参，必须用
  36.         out res int  # out表示这个参数可以被返回出去
  37.         # 形参还有一个inout表示即可以传入也可以被返回出去
  38.     )
  39.     begin
  40.         'sql语句 = 函数体代码'
  41.         select * from user where id > m and id < n;
  42.         set res=0;  # 用来标志存储过程是否执行(也就是看是否调用函数，能否将res=10改为res=0)
  43.     end $$
  44.     delimiter ;
  46.     "调用函数"
  47.     set res=10;    # 用来标志存储过程是否执行
  48.     select @res;    #查看现在的变量res为10
  49.     call p2(2,5,@res);        #将一个变量res传入函数
  50.     select @res;            # 此时res变为了0
  54. # python代码操作存储过程
  55.     import pymysql
  56.     conn = pymysql.connect(
  57.         host='127.0.0.1',
  58.         port=3306,
  59.         user='root',
  60.         passwd='123',
  61.         db='db6',
  62.         charset='utf8',
  63.         autocommit=True
  64.     )
  65.     cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
  66.     cursor.callproc('p2',(1,3,10))
  67.     # @_p1_0=1,@_p1_1=3,@_p1_2=10;
  68.     print(cursor.fetchall())
  71. """
  72. 查看存储过程具体信息
  73.     show create procedure [存储过程名];
  74. 查看所有存储过程
  75.     show procedure status;
  76. 调用存储过程
  77.     call [存储过程名](    里面为参数    )
  78. 删除存储过程
  79.     drop procedure [存储过程名];
  80. """

拓展 —— 三种开发过程

第一种

  1. """
  2. 应用程序：只需要开发应用程序的逻辑        (开发人员)
  3. mysql：编写好存储过程，以供应用程序调用    (DBA人员)
  4. 优点：开发效率，执行效率都高
  5. """

第二种

  1. """
  2. 应用程序：除了开发应用程序的逻辑，还需要编写原生sql        (开发人员)
  3. (无DBA人员)
  4. 优点：比方式1，拓展性高(非技术性的)
  5. 缺点:
  6.     1、开发效率，执行效率都不如方式1
  7.     2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
  8. """

第三种

  1. """
  2. 应用程序：开发应用程序的逻辑，不需要编写原生sql，是基于别人写好的框架来处理数据:ORM    (django)        
  4. 优点：
  5.     不用再编写纯生sql，这意味着开发效率比方式2高，同事兼容方式2扩展性高的好处
  6. 缺点:
  7.     执行效率连方式2都比不过
  8. """

四、函数(mysql内置方法)

  1. # 相当于python中内置函数
  2. # mysql有很多很多函数，这里只介绍5种，之前也讲过select last_insert_id();
  3.     1、Trim、LTrim、RTrim
  4.     2、lower、upper
  5.     3、Left、Right
  6.     4、Soundex
  7.     5、日期
  8. "ps:可以通过help 函数名    查看帮助信息!"
  9. 1、移除指定字符
  10.     Trim、LTrim、RTrim
  11.      案例:
  12.          SELECT TRIM('  bar   ');
  13.          -> 'bar'
  14.         SELECT TRIM(leading 'x' FROM 'xxxbarxxx');
  15.          -> 'barxxx'
  16.         SELECT TRIM(both 'x' FROM 'xxxbarxxx');
  17.          -> 'bar'
  18.         SELECT TRIM(trailing 'xyz' FROM 'barxxyz');
  19.          -> 'barx'
  22. 2、大小写转换
  23.     Lower、Upper
  24.         案例:
  25.             SELECT UPPER('Hej');
  26.              -> 'HEJ'
  27.              SELECT LOWER('QUADRATICALLY');
  28.              -> 'quadratically'
  30. 3、获取左右起始指定个数字符
  31.     Left、Right
  32.         案例:
  33.              SELECT LEFT('foobarbar', 5);
  34.               -> 'fooba'
  35.               SELECT RIGHT('foobarbar', 4);
  36.              -> 'rbar'
  38. 4、返回读音相似值(对英文效果)
  39.     Soundex
  40. """
  41. eg:客户表中有一个顾客登记的用户名为J.Lee
  42.         但如果这是输入错误真名其实叫 J.Lie,可以使用soundex匹配发音类似的
  43.         where Soundex(name)=Soundex('J.Lie')
  44. """
  46. 5、日期格式:date_format
  47.     '''在MySQL中表示时间格式尽量采用2022-11-11形式'''
  48.     CREATE TABLE blog (
  49.         id INT PRIMARY KEY auto_increment,
  50.         NAME CHAR (32),
  51.         sub_time datetime
  52.     );
  53.     INSERT INTO blog (NAME, sub_time)
  54.     VALUES
  55.         ('第1篇','2015-03-01 11:31:21'),
  56.         ('第2篇','2015-03-11 16:31:21'),
  57.         ('第3篇','2016-07-01 10:21:31'),
  58.         ('第4篇','2016-07-22 09:23:21'),
  59.         ('第5篇','2016-07-23 10:11:11'),
  60.         ('第6篇','2016-07-25 11:21:31'),
  61.         ('第7篇','2017-03-01 15:33:21'),
  62.         ('第8篇','2017-03-01 17:32:21'),
  63.         ('第9篇','2017-03-01 18:31:21');
  64.     select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
  66.     1.where Date(sub_time) = '2015-03-01'
  67.     2.where Year(sub_time)=2016 AND Month(sub_time)=07;
  68.     # 更多日期处理相关函数 
  69.         adddate    增加一个日期 
  70.         addtime    增加一个时间
  71.         datediff计算两个日期差值
  72.       ...