数据库编程

数据库编程

过程化SQL

单行注释，使用“##” 或 “–”；多行注释，C语言风格的注释：

/* **/*

SQL语句的结束标记，默认为：分号 ;

可使用DELIMITER <符号>修改SQL语句的结束标记

<符号>可以是：$$、或%%等。

例如：DELIMITER $$

避免使用反斜杠“/”字符，因为它是MySQL的转义字符。

程序块

BEGIN

SQL语句 ｜ SQL语句块

END

BEGIN…END语句块包含了该程序块的所有处理操作，允许语句块嵌套。

在MySQL中单独使用BEGIN…END语句块没有任何意义，只有将其封装在函数、存储过程、触发器等存储程序内部才有意义

常量

类型
字符串常量	单引号或双引号括起来的字符序列
数值常量	整数常量，例如：10；小数常量，例如：12.8
日期和时间常量	用特定格式的字符日期值表示，用单引号括起来例如：’2020-10-29’
布尔常量	true和false，SQL命令运行结果用1代表true，用0代表false
NULL值	适用于各种字段类型，通常表示“不确定的值”；NULL值参与的运算，结果仍为NULL值

变量

系统变量

系统定义，MySQL服务器层面，用户不能定义只能使用

可进一步分为以下两类：

全局变量global variable

会话变量session variable

用户变量

用户定义

局部变量 (存储程序中声明的变量)

用户定义，只在存储程序内部有效

查看服务器上有哪些系统变量

show [global | session] variables;

show global variables # 查看全局变量

show session variables # 查看会话变量

show variables; #不写，默认为session级别

Like模糊匹配查看系统变量

show [global | session] like ‘%变量名%’;

查看指定系统变量的值

select @@[global. | session.] 系统变量名;

系统变量以@@为前缀，不指定类型

常见系统全局变量

全局变量名称	说明
@@back_log	MySQL主要连接请求的数量
@@basedir	MySQL安装基准目录
@@license	服务器的许可类型
@@port	服务器侦听TCP/IP连接所用端口
@@storage_engine	存储引擎
@@version	服务器版本号

赋值（修改系统变量取值）

set [global | session] 系统变量名=值;

set @@[global. | session.] 系统变量名=值;

用户变量声明并初始化

set @变量名 = 值;

set @变量名 := 值;

select @变量名 := 值;

以@为前缀，不指定类型

声明时必须初始化

赋值(或用户变量值更新)

set @变量名 = 值;

set @变量名 := 值;

select @变量名 := 值;

select 属性 into @变量名 from 表名;

局部变量声明

declare 变量名 变量类型;

declare 变量名 变量类型 [default 默认值];

局部变量前面没有@符号

局部变量赋值

set 局部变量名 = 值;

set 局部变量名 := 值;

select 局部变量名 := 值;

select 属性 into 局部变量名 from 表名;

使用(查看变量值)

select 局部变量名;

存储函数function

定义函数

CREATE FUNCTION 函数名([参数名 参数数据类型[,…]])

RETURNS 返回值的数据类型

[函数选项]

BEGIN

​ 函数体;

​ RETURN 语句;

END

函数选项

DETERMINISTIC：用于指示该程序具有确定性。也就是说，如果给定相同的输入，程序将始终产生相同的输出；

NO SQL：用于指示该程序将不会读取或更改任何数据库表（或数据）；

READS SQL DATA：表示程序将读取数据库表；不同于NO SQL，因为如果程序读取数据，则 MySQL需要将其缓存，并在每次调用程序时更新缓存。

调用函数

SELECT 函数名([参数值[,…]])；

删除函数

DROP FUNCTION 函数名;

查看定义的函数

SHOW FUNCTION STATUS;

SHOW FUNCTION STATUS LIKE ‘%func%’;

SHOW FUNCTION STATUS WHERE db=’company’;

查看函数的创建语句

SHOW CREATE FUNCTION 函数名;

流程控制语句

分支语句

if-then-else

IF 条件 THEN SQL语句块1;

[ELSEIF 条件2 THEN SQL语句块2;]

[ELSE SQL语句块2; ]

END IF;

case-when-then

CASE 表达式

​ WHEN 表达式返回值1 THEN SQL语句块1

​ WHEN 表达式返回值2 THEN SQL语句块2

​ ……

​ WHEN 表达式返回值n THEN SQL语句块n

​ [ELSE SQL语句块n+1]

END CASE

CASE

​ WHEN 条件1 THEN SQL语句块1

​ WHEN 条件2 THEN SQL语句块2

​ ……

​ WHEN 条件n THEN SQL语句块n

​ ELSE SQL语句块n+1

END CASE

循环语句

LOOP

[<标签>:] loop

​ <SQL 语句块>

​ if 条件表达式 then leave [<标签>];

​ END if;

END loop [<标签>];

使用LEAVE子句跳出循环

标签：可以用来标记一个LOOP语句

WHILE

[<标签>:] while <条件表达式> DO

​ <SQL 语句块>

END while [<标签>];

REPEAT

[<标签>:] repeat

​ <SQL 语句块>

​ until <条件表达式>

END repeat [<标签>];

可嵌套使用

存储过程procedure

存储程序（stored program）

为了便于代码维护，提高代码复用性，将频繁使用的业务逻辑封装成存储程序，被数据库服务器所存储和执行，包含四类：

Functions 函数

Stored procedures 存储过程

Triggers 触发器

Events 事件

创建存储过程

CREATE PROCEDURE 存储过程名(

​ [IN|OUT|INOUT] 参数1 数据类型,

​ [IN|OUT|INOUT] 参数2 数据类型,

​ ……)

[存储过程选项] #同函数选项

BEGIN

​ 过程体；

END

调用存储过程

CALL 存储过程名();

删除存储过程

DROP PROCEDURE 存储过程名;

查看创建的存储过程

SHOW PROCEDURE STATUS; ## 查询数据库服务器上定义的所有存储过程

show procedure status where db=‘company’; ## 查询某个数据库上定义的存储过程

SHOW PROCEDURE STATUS LIKE ‘%emp%’; ## 模糊匹配查询

触发器trigger

触发器是用户定义在关系表上的一类由事件驱动的特殊过程

定义的触发器存储在数据库服务器中

事件驱动：指定的数据更新操作，包括：Insert; delete; update

定义触发器

DELIMITER $

CREATE TRIGGER <触发器A>

[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON <表名>

FOR EACH ROW

[FOLLOWS|PRECEDES] <触发器B>

BEGIN

​ <触发器主体>

END$

DELIMITER ;

命名约定格式

同一表上定义的触发器，命名要互不相同；不同表上定义的触发器，命名可以相同。

推荐采用以下格式：

(BEFORE | AFTER)tableName_(INSERT| UPDATE | DELETE)

或 tablename_(BEFORE | AFTER)__(INSERT| UPDATE | DELETE)

触发器执行顺序

FOLLOWS <触发器B>：在触发器B之后执行

PRECEDES <触发器B>：在触发器B之前执行

碎碎念

MySQL 5.7.2+开始，可以为同一触发事件和时间定义多个触发器

MySQL版本5.1.4开始，触发器可以调用存储过程或存储函数

REPLACE 或 LOAD DATA语句调用了insert，也会激活表关联的触发器

查看触发器

SHOW TRIGGERS [FROM|IN] 数据库名 [LIKE expr | WHERE expr];

SHOW TRIGGERS; #查看当前数据库中的触发器

SHOW TRIGGERS FROM mysqldemo; #查看数据库mysqldemo中的触发器

SHOW TRIGGERS FROM mysqldemo WHERE ‘table’ = ‘employees’; # 查看employees表的触发器

删除触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

例如：DROP TRIGGER before_products_update_2;

修改触发器

先删除，再重建

查看给定数据库中的触发器

SELECT *

FROM information_schema.triggers

WHERE trigger_schema = ‘数据库名’;

查看数据库中特定表的触发器

SELECT *

FROM information_schema.triggers

WHERE trigger_schema = ‘数据库名’

AND event_object_table = ‘表名’;

游标cursor

程序设计语言不能处理以集合形式返回的数据，SQL提供游标（Cursor）机制，一行一行地遍历查询结果

游标通常在存储过程或函数中使用

一次定义，多次使用

一个begin end中只能声明一个游标

游标中有个指针，打开游标时，才会执行游标中的select语句，这时该指针会指向select结果中的第一行记录

游标声明

DECLARE 游标名 CURSOR FOR SELECT_语句;

游标必须声明在变量声明之后，并始终与SELECT语句关联

打开游标

OPEN 游标名;

遍历数据

FETCH 游标名 INTO 变量列表;

获取一行数据并同时将游标移到下一行

如果当前行无数据，会引发mysql内部的NOT FOUND错误

关闭游标

CLOSE 游标名;

NOT FOUND 声明

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

fetch找不到数据时执行的动作