首页 > 数据库 >MySQL(三)

MySQL(三)

时间：2022-11-04 12:12:15浏览次数：42  
标签：语句 存储 变量 -- MySQL SQL 函数

存储过程与存储函数

1.存储过程

1.1介绍

  • 含义:就是一组经过预先编译的SQL语句的封装.
  • 执行过程:存储过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要想服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行

好处

  1. 简化操作,提高了SQL语句的重要性,减少了开发程序员的压力
  2. 减少操作过程中的失误,提高效率
  3. 减少网络传输量
  4. 减少了SQL语句暴露在网上的风险,也提高了数据查询的安全性

1.2创建

  • 存储过程在参数类型可以是IN,OUT和IN OUT
    1. 没有参数(无参数无返回)
    2. 仅带IN类型(有参数无返回)
    3. 仅带OUT类型(无参数有返回)
    4. 既有IN又有OUT(有参数有返回)
    5. 带IN OUT(有参数有返回)
  • IN,OUT,IN OUT都可以在一个存储过程中带多个.
  1. -- 语法
create procedure 存储过程名(IN|OUT|IN OUT 参数名 参数类型...)
[characteristics ...]
BEGIN
	查询语句体
END

  2. IN:当前参数为输入参数,也就是表示入参

    • 存储过程只是读取这个参数的值,如果没有定义参数种类,默认就是IN,表示输入参数

  3. OUT:当前参数为输出函数,也就是表示出参

    • 执行完成之后,调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了.

  4. INOUT:当前参数既可以为输入参数,也可以为输出参数.

  5. 形参类型可以是MySQL数据库中的任意类型

  6. characteristics表示创建存储过程时指定的存储过程的约束条件

  • LANGUAGE SQL
[NOT] DETERMINISTIC
{} CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
SQL SECURITY { DEFINER | INVOKER }
COMMENT 'string'

  • LANGUAGE SQL :说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

  • ==[NOT] DETERMINISTIC ===:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。 每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

  • =={ CONTAINS SQL | NO SQLI READS SQL DATA | MODIFIES SQL DATA } ===:指明子程序使用SQL语句
    的限制。

    1. CONTAINS SQL表示当前存储过程的子程序包含SQL语句,但是并不包含读写数据的SQL语句
    2. NO SQL表示当前存储过程的子程序中不包含任何SQL语句
    3. READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句
    4. MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    5. 默认情况下，系统会指定为CONTAINS SQL。

  • SQL SECURITY { DEFINER| INVOKER } :执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

    1. == DEFINER==表示只有当前存储过程的创建者或者定义者才能执行当前存储过程;
    2. INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
    3. 如果没有设置相关的值,则MySQL默认指定值为DEFINER。

  • COMMENT ''string': 注释信息，可以用来描述存储过程。

  1. 存储过程体中可以有多条SQL语句，如果仅仅一条SQL语句，则可以省略BEGIN和END编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的SQL语句。

    • 1.BEGIN....END: BEGIN...END 中间包含了多个语句，每个语句都以(;)号为结束符。
2. DECLARE: DECLARE用来声明变量，使用的位置在于BEGIN ...END语句中间，而且需要在其他语句使用之前进行变量的声明
3. SET:赋值语句，用于对变量进行赋值。
4.SELECT_INTO:把从数据表中查询的结果存放到变量中，也就足为变址赋值。

  2. 需要设置新的结束标记

    • DELIMITER新的结束标记

  3. 因为MySQL默认的语句结束符号为分号';'。为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符。

  4. 比如:“DELIMITER //”语句的作用是将MySQL的结束符设置为//,并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

  5. 当使用DELIMITER命令时，应该避免使用反斜杠(") 字符，因为反斜线是MySQL的转义字符。

  • -- 例
DELIMITER $ -- 或者//
create procedure test(IN test2 double)
BEGIN
select * from tests;
END$
DELIMITER;

1.3调用

  • -- 调用
call 存储过程名([$]实参);

1.3查看

  1. -- 方式1
show create procedure 存储过程名

  2. -- 方式2
show procedure status [LIKE '存储过程名'];

1.4修改

  • alter procedure 存储过程名[characteristic];

1.5删除

  • drop procedure if exists 存储过程名

2.存储函数

  • 自定义的函数

2.1创建

  • create function 函数名(参数名 参数类型...)
returns 返回值类型
[characteristics ...]
BEGIN
	查询语句体
	-- 必须有return语句
END

    -- 例
DELIMITER //
create procedure test()
returns double
BEGIN
	select * from tests;
END//
DELIMITER;
  1. 参数列表:指定参数为IN,OUT或者INOUT只对procedure合法的,function中总是默认为IN参数.
  2. returns type语句表示函数返回数据的类型
    • returns子句只能对function做指定,对函数而言这是强制的,它用来指定函数的返回类型,而且函数体必须包含一个return value语句
  3. characteristic创建函数时值的对函数的约束,取值与创建存储过程时相同.

2.2调用

  • -- 调用
select 函数名([$]实参类型)

2.3查看

  1. -- 方式1
show create function 函数名

  2. -- 方式2
show function status [LIKE '函数名'];

2.4修改

  • alter function 函数名[characteristic];

2.5删除

  • drop function if exists 函数名

3.存储过程vs存储函数

关键字 调用语法 返回值 应用场景
procedure call 存储过程() 理解为有0个或者多个 一般用于更新
function select 函数() 只能是一个 一般用于查询结构为一个值并返回时
  • 存储函数可以放在查询语句中使用,存储过程不行,

4.总结

4.1优点

  1. 存储过程可以一次编译多次使用.
  2. 可以减少开发工作量.
  3. 存储过程的安全性强
  4. 可以减少网络传输量.
  5. 良好的封装性

4.2缺点

  1. 可移植性差
  2. 调式困难
  3. 存储过程的版本管理很困难
  4. 它不适合高并发的场景

4.3结

  • 存储过程既方便,但又有局限性,尽管不同的公司对存储过程的态度不一,但是对我们开发人员来说,不论怎么样,掌握存储过程都是必备的技能之一.

变量,流程控制与游标

1.变量

1.1系统变量

  • 系统变量分为全局系统变量和会话系统变量
    • 全局系统变量针对于所有会话(连接)有效,但不能跨重启.
    • 会话系统变量仅针对于当前会话(连接)有效,会话期间,当前会话对某个会话系统变量值的修改,不会影响其他会话同一个会话系统变量的值.

查看系统变量

  • -- 查看所有全局变量
SHOW GLOBAL VARIABLES;

-- 查看所有会话变量
SHOW SESSION VARIABLES;
SHOW VARIABLES;

  • -- 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '标识符';

-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '标识符';

查看指定系统变量

  • MySQL中的系统变量以两个@开头,其中@@global仅用于标记全局系统变量,@@session仅用于标记会话系统变量.

  • @@首先标记会话系统变量,如果不存在,则标记全局系统变量.

  • -- 查看指定的系统变量的值
SELECT @@global.变量名;

-- 查看值的会话变量的值
SELECT @@session.变量名;
SELECT @@变量名;

修改指定系统变量

  • 方式1:修改MySQL配置文件,继而修改MySQL系统变量的值(需要重启服务);

  • 方式2:在MySQL服务运行期间,使用'set'命令重新设置系统变量的值.

  • -- 为某个系统变量赋值
-- 方式1
set @@global.变量名=变量值;
-- 方式2
set global 变量名=变量值;

-- 为某个会话变量赋值
-- 方式1
set @@session.变量名=变量值;
-- 方式2
set session 变量名=变量值;

1.2用户变量

  • 用户变量是用户自己定义的,MySQL中的用户变量以一个@开头.
    • 会话用户变量:作用域和会话变量也要,只对当前连接会话有效
    • 局部变量:只在BEGIN和END语句块中有效,局部变量只能在存储过程和存储函数中使用

会话用户变量

  • 定义

  • -- 方式1
set @用户变量 = 值; 
set @用户变量 := 值; 

-- 方式2
select @用户变量 := 表达式 [FROM 等子句];
select @用户变量 INTO 表达式 [FROM 等子句];

局部变量

  • 定义:可以使用declare语句定义一个局部变量

  • 作用域:仅仅在定义它的BEGIN...END中有效

  • 位置:只能放在BEGIN....END中,而且只能放在第一句

  • -- 例
BEGIN
	-- 声明局部变量
	DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
	DECLARE 变量名2,变量名3...... 变量数据类型 [DEFAULT 变量默认值];
	
	-- 为局部变量赋值
	SET 变量名1 = 值;
	select 值 INTO 变量名2 [FROM 子句];
	
	-- 查看局部变量的值
	select 变量1,变量2,变量3;
	
END

  • -- 定义变量
DECLARE 变量名 数据类型 [DEFAULT 变量默认值];
-- 如果没有default子句,初始值为NULL

  • -- 变量赋值
-- 方式1
set 变量名=值;
set 变量名:=值;

-- 方式2
select 字段名(表达式) INTO 变量名 FROM 表;

  • -- 使用变量
select 局部变量名;

1...3异常处理

  • -- 定义处理程序
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

  • 处理方式

    • CONTINUE:不是遇到错误不处理,继续执行
    • EXIT:表示遇到错误马上退出
    • UNDD:表示遇到错误后撤回之前的操作(MySQL不支持)

  • 错误类型

    • SQLstate '字符串错误码':表示长度为5的SQLstate_value类型的错误代码
    • MySQL_error_code:匹配数值类型错误代码
    • 错误名称:表示declare...condition定义的错误条件名称
    • SQLwarning:匹配所有以01开头的SQLstate错误代码
    • not found:匹配所有以02开头的SQLstate错误代码
    • SQL exception:匹配所有没有被SQLwarning或者not found捕获的SQLstate错误代码

  • 处理语句

    • 如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句.语句可以是像set 变量=值只有简单的语句,也可以使用BEGIN...END编写的复合语句

2.流程控制

  • SQL中流程语句的分类
    • 顺序结构:程序从上往下依次执行
    • 分支结构:程序按条件进行选择执行,从两条或多条路径中选择一条执行
    • 循环结构:程序满足一定条件下,重复执行一组语句
  • MySQL中的流程语句分类(只能用于存储程序)
    • 条件判断语句;IF语句和CASE语句
    • 循环语句:LOOP,WHILE和REPEAT语句
    • 跳转语句:ITERATE和LEAVE语句

2.1IF

  • -- 语法
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]....
[ELSE 操作N]
END IF

2.2CASE

  • -- 方式1
CASE 表达式
WHEN 值1 THEN 结果1或语句1(语句需要加分号)
WHEN 值2 THEN 结果2或语句2(语句需要加分号)
...
ELSE 结果n或语句n(语句需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)

  • -- 方式2
CASE
WHEN 条件1 THEN 结果1或语句1(语句需要加分号)
WHEN 条件2 THEN 结果2或语句2(语句需要加分号)
...
ELSE 结果n或语句n(语句需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)

2.3LOOP

  • [loop_label:] LOOP 
	循环执行的语句
END LOOP [loop_label]

  • loop_label为标签名,可写可不写

2.4WHILE

  • [while+label:] WHILE 循环条件 DO
	循环体
END WHILE [while_label];

  • while_label为标签名,可写可不写

2.5REPEAT

  • [repeat_label:] REPEAT 
	循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label] -- 相当于do...while

  • while_label为标签名,可写可不写

  • repeat无论如何都执行一次

2.6LEAVE

  • LEAVE 标记名;	-- 相当于break

2.7ITERATE

  • ITERATE 标签名;  -- 相当于continue

3.游标

3.1声明游标

  • declare cursor_name cursor for select_statement;

  • select_name代表的是select语句,返回一个用于创建游标的结果集

3.2打开游标

  • open cursor_name

  • 打开游标的时候select语句的查询结果集就会送到游标工作区,为后面游标的逐条读取结果集中的记录做准备;

3.3使用游标

  • fetch cursor_name into var_name [,var_name] ...

  • 游标的查询结果集中的字段数,必须跟into后面的变量数一致,否则在存储过程执行的时候,MySQL会提示错误

3.4关闭游标

  • close cursor_name

  • 游标会占用系统资源,如果不及时关闭,游标会一直保持到存储过程结束,影响系统运行的效率

  • 关闭游标之后,我们就不能再检索查询结果中的数据行,如果需要检索只能再次打开游标

触发器

1.创建

  • create trigger 触发器名称
{before|after} (insert|update|delete} on 表名
for each row

  • 表名:表示触发器监控的对象.

  • before|alter:表示触发的时间.before表示在事件之前触发:alter表示在事件之前触发.

  • insert|update|delete:表示触发的事件

    • insert表示插入记录时触发.
    • update表示更新记录时触发
    • delete表示删除记录时触发

  • 触发器执行的语句块:可以是单条SQL语句.也可以是由BEGIN...END结构组成的复合语句块.

2.查看

  • -- 方式1
show triggers;
-- 方式2
show create trigger 触发器名称;
-- 方式3
select * form information_schema.triggers;

3.删除

  • drop trigger 触发器名称;

8.0新特性

1.窗口函数

  • 分类

    • 静态窗口函数的窗口大小是固定的,不会因为记录的不同而不同
    • 动态窗口函数的窗口大小会随机记录的不同而变化

  • 语法

    • -- 结构1
函数 over ([partition by 字段名 order by 字段名 排序规则])
-- 结构2
函数 over 窗口名 _ WINDOW 窗口名 AS ([partition by 字段名 order by 字段名 排序规则])

    • over关键字指定函数窗口的范围

      • 如果省略括号后面的内容,则窗口会包含满足where条件的所有记录,窗口函数会基于所有满足where条件的记录进行计算
      • 如果over关键字后面的括号不为空,则基于使用如下语法设置窗口

    • 窗口名:为窗口设置一个别名,用来标识窗口

    • partition by子句:指定窗口函数按照哪些字段进行分组.分组后窗口函数可以在每个分组中分别执行

    • order by子句:指定窗口函数按照哪些字段进行排序.执行排序操作窗口函数按照排序后的数据记录的顺序进行编号

    • frame子句:为分区中的某个子集定义规则,可以用来作为滑动窗口使用.

1.1序号函数

1.1.1row_number()

  • 对数据中的序号进行顺序显示

1.1.2rank()

  • 对序号进行并列排序,并且会跳过重复的序号

1.1.3dense_rank()

  • 对序号进行并列排序,不 会跳过重复的序号

1.2分布函数

1.2.1percent_rank()

  • 等级值百分比函数,按照下面方式进行计算

  • (rank-1)/(rows-1)

  • rank的值为使用rank函数产生的序号,rows的值为当前窗口的总记录数

1.2.2cume_dist()

  • 主要用于查询小于或者等于某个值的比例

1.3前后函数

1.3.1lag(expr,n)

  • 返回当前行的前n行的expr值

1.3.2lead(expr,b)

  • 返回当前行的后n行的expr值

1.4首尾函数

1.4.1first_value(expr)

  • 返回第一个expr的值

1.4.2last_name(expr)

  • 返回最后一个expr的值

1.5其他函数

1.5.1nth_value(expr,n)

  • 返回地n个expr的值

1.5.2ntile(n)

  • 将分区中的有序数据分为n个桶,记录桶编号

2.公用表表达式

2.1普通公用表表达式

  • with 公用表名称
as	(子查询)
select|delete|update 语句;

  • 普通公用表表达式类似于子查询,跟子查询不同的是它可以被多次引用,二群可以被其他公用表表达式所引用.

2.2递归公用表表达式

  • with recursive
公用表名称  as  (子查询)
select|delete|update 语句;

  • 递归公用表表达式由两部分组成,分别是种子查询和递归查询,中间通过关键字union [all]进行连接,这

    • 种子查询:获得递归的初始值,这个查询只会运行一次,以创建初始数据集,只会递归查询会一直执行,直到没有任何新的查询数据产生,递归返回.

标签：语句,存储,变量,--,MySQL,SQL,函数
From： https://www.cnblogs.com/Myvlog/p/16857320.html

相关文章

  • mysql数据库慢SQL优化
    mysql数据库慢SQL优化优化来源：阿里云云数据库RDS慢sql或者CAT监控系统中的TransactionSQLorURL根据平均时间反馈来排查，决定是否增加索引，或者调整业务逻辑代码。1.......
  • mysql旧版本实现row_number
    mysql8.0已经有row_number了，但是很多同学用的还是老版本，当需要用到row_number的功能时，老版本也是有解决方案的。比如我们有一张表sync_task_log,有三个字段ds_id,state,sy......
  • prometheus 监控案例-Tomcat、Redis、Mysql、Haproxy、Nginx
    服务监控  可以根据prometheus官网找到对应服务的exporter  https://prometheus.io/docs/instrumenting/exporters/监控Tomcat容器  基于第三方exporter实现......
  • libmysqlclient.so.18: cannot open shared object file: No such file or directory
    DownloadTypeURLMirrorrepo.mysql.comBinaryPackage​​mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm​​SourcePackage​​mysql-community-5.7.26-1.el7.src.r......
  • MYSQL and vs
    vs2008与MYSQL​​http://wenku.baidu.com/link?url=47GBXOMUkNoEYGkQuw-Mv4fhsqKKtCuBHJ0cp8-uwbxP-J0t1YzwqZjaKBfP1prsU7BxO4cMcfHV2E9vyYz23s7nrL737wlk0sWKF4Li_0​......
  • mysql全量备份+binlog日志完整恢复数据
    首先，数据库在误操作之前必须已经开启了binlog日志功能，且binlog日志的保存周期必须大于全备份的时间周期！所谓恢复，就是让将全备份的数据全部恢复后，再使用mysqlbinlog命令把b......
  • SpringCloud (五) - 云服务器Centos7.6，安装JDK,Maven,Mysql,Redis
    1、购买云服务器购买地址：https://cloud.tencent.com/act/pro/2022double11_warmup后面的环境都是基于此环境Centos7.6；2、安装SecureCRT和SecureFX2.1SecureCRT教......
  • 安装mysql
    //-----------------*****本文章只做学习参考，侵权立删*****-----------------\\转载链接：https://www.downkuai.com/soft/161548.html总结概要如下：1、完成安装包下载后，点......
  • 04_Linux完全卸载安装Mysql
    1.Linux环境完全卸载mysql相关文件：完全卸载mysql相关文件：      yumremovemysqlmysql-servermysql-libscompat-mysql      rm-rf/var/......
  • MySql服务优化之脏页
    计算脏页比例selectVARIABLE_VALUEinto@afromperformance_schema.global_statuswhereVARIABLE_NAME='Innodb_buffer_pool_pages_dirty';selectVARIABLE_VALUE......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99