MySQL语法-数据库编程

10.1 函数

函数指的是一段用于完成特定功能的代码。

10.1.1内置函数

MySQL提供的内置函数，也可以称为系统函数，大致可以分为数学函数、数据类型转换函数、字符串函数、日期和时间函数、加密函数、系统信息函数、JSON函数以及其它常用函数。

10.1.1.1数学函数

根据其使用范围不同，大致可以分为三角函数、指数函数、对数函数、求近似值函数、进制函数等，具体如下表。

分类	函数名称	描述
三角函数	PI()	计算圆周率
	RADIANS(x)	将角度x转化为弧度
	DEGREES(x)	将弧度x转换为角度
	SIN(x)	正弦函数
	COS(x)	余弦函数
	TAN(x)	正切函数
	COT(x)	余切函数
	ASIN(x)	反正弦函数
	ACOS(x)	反余弦函数
	ATAN(x)	反正切函数
指数函数	SQRT(x)	求x的平方根
	POW(x,y) 或POWER(x,y)	幂运算函数(计算x的y次方)
	EXP(x)	计算e(自然对数的底约为2.71828)的x次方
对数函数	LOG(x)	计算x的自然对数
	LOG10(x)	计算以10为底的对数
求近似值函数	ROUND(x[,y])	计算离x最近的整数，若设置y则与FORMAT(x,y)功能相同
	TRUNCATE(x,y)	返回小数点后保留y位的x(舍弃多余小数位，不四舍五入)
	FORMAT(x,y)	返回小数点后保留y位的x(进行四舍五入)
	CEIL(x)或CEILING(x)	返回大于等于x的最小整数
	FLOOR(x)	返回大于等于x的最大整数
进制函数	BIN(x)	返回x的二进制数
	OCT(x)	返回x的八进制数
	HEX(x)	返回x的十六进制数
	ASCII(c)	返回字符c的ASCII码值
	CHAR(c$_1$,c$_2$,c$_3$,...)	将c$_1$,c$_2$,c$_3$,...的ASCII码转换为字符，然后返回这些字符组成的字符串
	CONV(x,code$_1$,code$_2$)	将code$_1$的进制变为code$_2$的进制
其它函数	RAND()	默认返回[0,1]之间的随机数
	ABS(x)	获取x的绝对值
	MOD(x,y)	求模运算，与x%y的功能相同

其中需要注意的事项在第五章中以及讲解，这里不再赘述第五章单表操作

10.1.1.2数据类型转换函数

对指定得到数据类型进行转换。

函数名称	描述
CONVERT(x,type)	以type类型返回x
CONVERT(x USING 字符集)	以指定字符集返回x数据
CAST(x AS type)	以type类型返回x
UNHEX(x)	将x转为十六进制数字，然后再转为由数字表示的字符

10.1.1.3字符串函数

函数名称	描述
CHAR_LENGTH()	获取字符串长度
LENGTH()	获取字符串占用的字节数
REPEAT()	重复指定次数的字符串，并保存到一个新字符串中
SPACE()	重复指定次数的空格，并保存到一个新字符串中
UPPER()	全部转换为大写与UCAS()等价
LOWER()	全部转换为小写与LCAS()等价
STRCMP()	比较两个字符串的大小
REVERAE()	颠倒字符串的顺序
SUBSTRING()	从字符串的指定位置截取指定长度的字符串，与MID()等价
LEFT()	截取并返回字符串左侧的指定个的字符
RIGHT()	截取并返回字符串右侧的指定个的字符
LPAD()	按照指定限定字符从左到右截取字符串，当字符串的长度小于限定长度时在左侧填充指定的字符
RPAD()	按照指定限定字符从左到右截取字符串，当字符串的长度小于限定长度时在右侧填充指定的字符
INSTR()	返回子串在一个字符串中第一次出现的位置
FIND_IN_SET()	获取字串在含有英文逗号分隔的字符串中的开始位置
LTRIM()	删除字符串左侧的空格
RTRIM()	删除字符串右侧的空格
TRIM()	删除字符串左右两侧的空格
INSERT()	替换字符串函数
REPLACE()	字符串替换函数，返回替换后的新字符串
CONCAT()	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
CONCAT_WS()	用指定分隔符将参数连接成一个新的字串

10.1.1.4日期和时间函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

10.1.1.5加密和散列函数

函数名称	作用
MD5()	使用MD5计算并返回一个32位的散列字符串
AES_ENCRYPT()	使用密钥对字符串进行加密，默认返回一个128位的二进制
AES_DECRYPT()	使用密钥对密码解密
SHA1()或SHA()	使用安全散列算法SHA-1计算字符串，返回40个十六进制数字组成的字符串
SHA2()	使用安全散列算法SHA-2计算字符串
ENCODE()	使用密钥对字符串进行编码，默认返回一个二进制
DECODE()	使用密钥对密码解码
PASSWORD()	计算并返回一个41位的密码字符串

10.1.1.6系统信息函数

便于查看MySQL服务器的信息。

函数名称	作用
VERSION()	获取当前MySQL服务实例使用的MySQL版本号
DATABASE()	获取当前操作的数据库与SCHEMA()等价
USER()	获取当前登录服务器的主机地址以及用户名，与SYSTEM_USER()和SESSION_USER等价
CURRENT_USER()	用于获取该账号名允许通过哪些登录主机连接MySQL服务器
CONNECTION_ID()	获取当前MySQL服务器的连接ID
BENCHMARK()	重复执行一个表达式
LAST_INSERT_ID()	获取当前会话中最后一个插入的AUTO_INCREMENT列的值

10.1.1.7JSON函数

略。

10.1.1.8其它常用函数

略。

10.1.2自定义函数

10.1.2.1自定义函数语法

CREATE FUNCTION 函数名([参数名 数据类型,...]) RETURNS 返回值类型 [DETERMINISTIC]# 确定性函数DETERMINISTIC
[BEGIN]
    #函数体
    RETURN 返回值数据;# 必须与结构中定义的返回值类型一致
[END];

10.1.2.2查看函数

SHOW CREATE FUNCTION 函数名;

10.1.2.3调用函数

SELECT 函数名1(实参列表),函数名2(实参列表),...;

10.1.2.4删除函数

DROP FUNCTION [IF EXISTS] 函数名;

10.2存储过程

10.2.1存储过程的概念

对于SQL编程来说，存储过程是数据中的一个重要对象。它是在大型数据库系统中一组为了来完成特定功能的SQL语句集，第一次使用经过编译后就不需要重复编译，因此执行效率比较高。它与函数在数据库中的差异如下。

相同点：目的都是为了可重复地执行数据库SQL语句的集合，并且都是经过一次编译后，后面再次需要时直接执行。

不同点：

标识符不一样，存储过程使用PROCEDURE ，函数使用FUNCTION 。
存储过程在创建时没有返回值，而函数在定义时必须设置值返回值。
存储过程没有返回值类型，且不能将结果直接赋值给变量；而函数定义时需要设置返回值类型，且在调用时必须将返回值赋给变量。
存储过程必须通过CALL 调用，不能使用SELECT 调用；而函数则可在SELECT语句中直接使用。

10.2.2存储过程的创建与运行

10.2.2.1创建存储过程

若在命令行中使用需要修改语句结束符号DELIMITER 新结束符号

DELIMITER 新结束符号
CREATE PROCEDURE 过程名称([[ IN | OUT | INOUT ] 参数名称 参数类型],...)
BEGIN
    过程体
END
新结束符号
DELIMITER ;

IN(输入参数) OUT(输出参数) INOUT(输入输出参数)

10.2.2.2查看存储过程

# 查看存储过程的创建语句
SHOW CREATE PROCEDURE 过程名;
# 根据指定模式查看所有符合条件的存储过程
SHOW PROCEDURE STATUS [LIKE 匹配模式];

10.2.2.3调用存储过程

CALL 数据库名.存储过程名([实参列表]);

10.2.3存储过程的修改与删除

ALTER PROCEDURE 存储过程名 [特征];
-- 需要注意的是，ALTER PROCEDURE不能更改存储过程的参数或主体

特征选项	描述
COMMENT '注释内容'	为存储过程设置注释信息
LANGUAGE SQL	存储过程体是SQL语句，未来可能支持其他类型
CONTAINS SQL	表示子程序中包含除读或写的SQL语句
NO SQL	表示子程序中不包含SQL语句
READS SQL DATA	表示子程序包含读取数据的语句
MONDIFIES SQL DATA	表示子程序包含写数据的语句
SQL SECURITY DEFINER	表示只有定义者才有权执行存储过程
SQL SECURITY INVOKER	表示调用者有权执行存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名;

10.2.4存储过程的错误处理

在存储过程执行期间，可以对某些特定的错误代码、警告或者异常进行定义，然后再针对这些错误添加处理程序进行处理。例如，退出当前程序块或继续执行。

10.2.4.1自定义错误名称

在编写存储过程时，可以使用DECLARE语句为指定的错误声明一个名称。

DECLARE 错误名称 CONDITION FOR [错误类型];

略

10.2.4.2错误的处理程序

为错误代码命名后，接下来需要使用MySQL提供的DECLARE...HANDLER 语句为其设置处理程序。

DECLARE 错误处理方式 HANDLERFOR 错误类型 [,错误类型]...;程序语句段# 错误处理方式-- comtinue遇到错误不处理，继续执行-- exit遇到错误马上退出# 错误类型-- 使用DECLARE...HANDLER FOR语句声明的错误代码名称-- SQL WARNING表示所有以01开头的SQLSTATE错误代码-- SQL FOUND表示所有以02开头的SQLSTATE错误代码-- SQLEXCEPYION表示除01或02开头之外的所有SQLSTATE错误代码

10.3变量

系统变量又可称为全局变量，指的是MySQL系统内部定义的变量，对于所有MySQL客户端都有效。

10.3.1系统变量

10.3.1.1查看系统所有变量

SHOW [global | session] VARIABLES [LIKE '匹配模式' | WHERE表达式];-- session为默认值，可省略

以@@ 开头的变量在MySQL会被识别为全局变量或系统变量。

10.3.1.2修改系统变量的值

局部修改，修改后只在本次连接中有效，不影响其它连接MySQL服务器得到客户端使用。语法SET 变量名=值
全局修改，对正在连接的客户端无效，只针对新连接的客户端永久生效。语法SET GLOBAL 变量名=值或SET @@GLOBAL 变量名=值

10.3.2会话变量

会话变量也可称为用户变量，指的是用户自定义的变量，仅针对当前用户使用的客户端生效。

# 1.使用SET赋值SET @name='Tom';# 2.在SELECT中使用赋值符号":="SELECT @price :price FROM sh_goods LIMIT 2;-- price的值为所有记录最后结果# 3.使用SELECT...INTO语句SELECT id,name,price FROM sh_goods LIMIT 1INTO @ids,@names,@prices;-- 查询结果为一行记录，记录个数必须与变量个数相同

10.3.2局部变量

局部变量的作用范围仅在复合语句语法BEGIN和END语句之间使用，保证局部变量在除了BEGIN和END之间以外任何地方都不能被获取和修改。

DECLARE 变量名1 [,变量名2] ... 数据类型 [DEFAULT 默认值];-- 用于初始化变量的默认值(默认为NULL)

10.4流程控制

10.4.1判断语句

IF语句

适用于SQL语句的IF语法

IF(条件表达式，表达式1，表达式2);-- 为true时返回表达式1，FALSE为表达式2

适用于SQL程序的IF语法

IF 条件表达式1 THEN  语句列表[ELSEIF 条件表达式2 THEN 语句列表]...[ELSE 语句列表]END IF;

CASE语句

适用于SQL语句的CASE语法

# 语法1CASE 条件表达式 WHEN 表达式1 THEN 结果1[WHEN 表达式2 THEN 结果2]...[ELSE 结果] END;#语法2CASE WHEN 条件表达式1 THEN 结果1[WHEN 条件表达式2 THEN 结果2]...[ELSE 结果] END;

适用于SQL程序的CASE语法

# 语法1CASE 条件表达式 WHEN 表达式1 THEN 语句列表[WHEN 表达式2 THEN 语句列表]...[ELSE 语句列表]END CASE;#语法2CASE WHEN 条件表达式1 THEN 语句列表[WHEN 条件表达式2 THEN 语句列表]...[ELSE 语句列表]END CASE;

10.4.2循环语句

10.4.2.1LOOP语句

用于实现一个简单的循环。

[标签:] LOOP  语句列表    [LEAVE 标签] -- 提前退出循环    语句列表END LOOP [标签];

无LEAVE则死循环

10.4.2.2REPEAT语句

用于执行符合条件表达式的语句列表。

[标签:] REPEAT    语句列表UNTIL 条件表达式 END REPEAT [标签];

先执行再判断，表达式为真结束循环。

10.4.2.3WHILE语句

带条件判断的循环。

[标签:] WHILE 条件表达式 DO    语句列表END WHILE [标签];

条件为真执行循环。

10.4.3跳转语句

跳转语句实现程序执行过程中的流程跳转。常用的跳转语句有LEAVE 和ITERATE 。

{LEAVE | ITERATE} 标签名;# LEAVE       跳出循环体# ITERATE  结束本轮循环

ITERATE只能应用再上述3个语句中使用。

LEAVE 不仅可以在循环结构上使用，还可以在BEGIN...END 中使用。

10.5游标

10.5.1游标的作用

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

简单理解：游标是指向指定结果中任意位置的指针。

资料来源

10.5.2游标的操作流程

10.5.2.1定义游标

只能在存储过程中定义

游标在使用之前，必须通过定义，让其与指定的SELECT 语句关联,目的就是确定游标要操作的SELECT结果集对象。

ly.初始化一个游标的指向对象。

DECLARE 游标名称 CURSOR FOR SELECT语句;

游标名称唯一，是区分不同游标的标识。游标定义只能在程序处理语句之前，局部变量定义之后。SELECT 语句中不能出现INTO 。

需要注意的是，游标在定义后，关联的SELECT 语句并没有执行，内存中没有关联的SELECT 语句的结果集。

10.5.2.2打开游标

打开游标，使SELECT 语句根据查询条件将数据存储到MySQL服务器的内存中。

OPEN 游标名称;

10.5.2.3利用游标检索数据

使用FETCH 来获取一行记录，获取数据后游标的内部指针就会移动到下一条记录，保证每次获取的数据都不同。

FETCH [[NEXT] FROM] 游标名称 INTO 变量名 [,变量名] ...;

当下一条记录不存在时报错ERROR 1329(02000);

10.5.2.4关闭游标

CLOSE 游标名称;

10.5.3使用游标检索数据

DELIMITER $$;CREATE PROCEDURE sh_goods_proc_cursor()BEGIN   DECLARE mark,cur_id,cur_num INT DEFAULT 0;  # 定义游标  DECLARE cur CURSOR FOR  SELECT id,stock FROM sh_goods WHERE score = 5;  # 自定义错误处理程序，结束游标的遍历 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET mark = 1; # 打开游标  OPEN cur;   # 遍历游标  REPEAT      # 利用游标获取一行数据        FETCH cur INTO cur_id,cur_num;      # 处理游标检索的数据     IF cur_num >= 0 && cur_num <= 400 THEN          SET cur_num = 1500;         UPDATE sh_goods SET stock = cur_num WHERE id = cur_id;      END IF; UNTIL mark END REPEAT;  # 关闭游标  CLOSE cur;END$$DELIMITER;

10.6触发器

可以被视为一种特殊的存储过程，不同的是触发器是只有在预先定义好的事件发生时才会被MySQL自动调用。

10.6.1触发器的基本操作

10.6.1.1创建触发器

在创建触发器需要指定触发器的操作对象——数据表，且该数据表不能是临时表或视图。

CREATE TRIGGER 触发器名 触发时机 触发事件 ON 表 FOR EACH ROW [触发顺序]BEGIN 操作的内容END;

指定数据库下的触发器名称必须唯一。

触发时机表示数据表在变化前后的两种状态，触发事件指的是激活触发器的操作类型，触发顺序指的是同一个表中多个触发器的执行顺序，默认情况下按创建顺序激活。

选项	可选值	描述
触发时机	BEFORE	在数据改变之前的状态
	AFTER	在数据改变之后的状态
触发事件	INSERT	插入操作
	UPDATE	更新操作
	DELETE	删除操作
触发顺序	FOLLOWS 其它触发器名称	新触发器在现有触发器之后激活
	PRECEDES 其它触发器名称	新触发器在现有触发器之前激活

对于每张数据表来说，每个触发事件只允许创建一个触发器。因此，一张数据表根据触发时机的不同最多支持6个触发器。

补充

可以使用new 关键字获取插入或更新时产生的新值，old关键字获取删除或更新之前的值使用方法为new.字段名

10.6.1.2查看触发器

若要查看数据库中已存在的触发器语句、状态等信息可以通过两种方法实现。

使用SHOW TRIGGERS 另一种则是使用SELECT 直接查询informationn_schema 数据库下的triggers 数据表。

SHOW TRIGGERS [{FROM | IN} 数据库名称] [LIKE '匹配模式' | WHERE 条件表达式];

指定数据库时查看的是该库下的触发器，LIKE 子句匹配的是触发器作用的数据表，而非触发器名称，WHERE 指定触发器的条件。

10.6.1.3触发器的触发

当对数据表有批量操作时，每一条数据的变化都会触发预先定义的触发器语句。

10.6.1.4删除触发器

DROP TRIGGER [IF EXISTS] [数据库,]触发器名称;

Hey, IO0288!