SQL语句（三）——存储过程与存储函数

Posted on | In ,

常用函数

日期函数

1）CURDATE()：返回当前日期
2）CURTIME()：返回当前时间
3）MONTH(d)：返回日期d中的月份，范围1~12
//从t_ta表中获取当前日期，时间，月份（月份设置别名m）
SELECT CURDATE(), CURTIME(), MONTH(birthday) AS m FROM t_ta;

字符串函数

1）CHAR_LENGTH(s)：返回字符串s的字符数
SELECT CHAR_LENGTH(userName) FROM t_book;
2）UPPER(s)：把字符串s中所有字母变成大写
3）LOWER(s)

数学函数

1）ABS(x)
SELECT ABA(num) FROM t_book;
2）SQRT(x)
3）MOD(x,y)：求余

加密函数

1）PASSWORD(str)：对用户的密码进行加密，不可逆
INSERT INTO t_book VALUES (NULL,.., PASSWORD(‘123456’));
2）MD5(str)：普通加密，不可逆
3）ENCODE(str, pswd_str)：加密函数，结果是一个二进制数，必须使用BLOB类型的字段来保存它。pswd_str为加密解密的钥匙
4）DECODE(crypt_str, pswd_str)：解密函数

存储过程和存储函数

存储过程和存储函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句，而且它们是在Mysql服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

创建存储过程

1
2
CREATE PROCEDURE sp_name ([pro_parameter[,…]])
	[存储过程的特性] SQL代码内容;

其中
代码内容可以用BEGIN…END来标识SQL代码内容的开始和结束。
pro_parameter存储过程参数列表，其中的每个参数由3部分组成：输入输出类型、参数名称和参数类型。其格式为[IN, OUT|INOUT] param_name type。

1
2
3
4
5
6
7
8
9
10
11
12
13
//传入bookTypeId，查当前图书类别下有多少图书
DELIMITER && //begin end中有多条语句时，为了防止遇到分号就执行，需要有定界符（分隔符）
CREATE PROCDURE pro_getBookNumByBookTypeId ( IN bTI INT, OUT count_num INT)
	READ SQL DATA
	BEGIN
		SELECT COUNT(*) INTO count_num FROM t_book WHRER bookTypeId = bTI;
	END
	&&
DELIMETER ; //此行必须与第一行的DELIMETER对齐，且与分号间有空格，否则报错。
//调用procedure
CALL pro_book(1, @total); //输入参数和输出参数，@表示全局变量
//显示遍历变量
SELECT @total;

创建存储函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION sp_name ([func_parameter[,…]])
	RETURNS 类型
	[存储过程的特性] SQL代码内容;
//查找id为bookId的图书名
DELIMETER &&
CREATE FUNCTION func_book (bookId INT)
	RETURN VACHAR(2)
BEGIN
		RETURN (SELECT bookName FROM t_book WHERE id = bookId);
END
&&
DELIMETER ;
//调用
SELECT func_book(1);

1）变量的使用
（1）定义变量

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE var_name […] type [DEFAULT value];
//例
DELIMTER &&
CREATE PROCEDURE pro_user()
		BEGIN
			DECLARE a, b VARCHAR(20);
			SET a = ‘数学’, b = ‘123456’;
			INSERT INTO t_book VALUES (NULL, a, b);
		END
		&&
DELIMETER ;
CALL pro_user;

（2）为变量赋值

1
2
3
4
SET var_name = expr [var_name = expr]
		FROM 表名 WHERE 条件表达式;
SELECT col_name[,…] INTO var_name [,…]
	 	FROM 表名 WHERE 条件表达式;

2）游标的使用
查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之前。
（1）声明游标
DECLARE cursor_name CURSOR FOR 选择语句;
（2）打开游标
OPEN cursor_nam;
（3）使用游标
FETCH cursor_name INTO var_name [,var_name,…];
（4）关闭游标
18-19讲

调用

CALL sp_name ([parameter[,…]]);
func_name ([parameter[,…]]);

查看

1）查看状态
SHOW {PROCEDUR|FUNCTION} STATUS [LIKE ‘pattern’];
2）查看定义
SHOW CREATE {PROCEDUR|FUNCTION} sp_name;
3）从information_schema.Routines表中查看存储过程和函数信息。

修改

1
2
3
4
5
ALTER {PROCEDURE|FUNCTION} sp_name [characteristic…]
characteristic:
{CONSTAINT SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|CONMMENT ‘string’

删除

DROP {PROCEDURE|FUNCTION} sp_name;

数据备份与还原

数据备份

1）用命令
（2）先打开命令窗口cmd，切换到mysqldum.exe所在文件夹：
cd D:\Software\MySQL\MySQL Server 5.1\bin
（2）输入导出命令
mysqldump –u username –p dbname table1 table2 … > BackupName.sql;
例：输入 mysqldump –u root –p db_book > d:\db_book.sql
2）使用SQLyog
右击数据库-> 备份/导出 -> 备份数据库，存储到SQL

数据还原

1）
2）使用SQLyog还原数据库
先创建一个与需要导入的数据库同名的空数据库；
右击root@localhost或右击任一数据库 -> 导入 -> 执行SQL脚本；
若没有导入成功，可用Notepad++打开.sql文件，查看以下两行中的数据库名是否与所创建的空数据库同名。