- 简单的使用
- 创建
DROP PROCEDURE IF EXISTS pro2;
DELIMITER // # $或 /
CREATE PROCEDURE pro2(OUT s INT)
BEGIN SELECT COUNT(*) INTO s FROM navi_issuer;
END $$
点:“DELIMITER //”语句的作用是将MYSQL的结束符设置为//,因为MYSQL默认的语句结束符为分号;
为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER 改变存储过程的结束符,并以“END//”结束存储过程。
存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符!!!!!!!!!!!
当使用DELIMITER命令时,应该避免使用反斜杠(\)字符,因为反斜杠是MYSQL的转义字符!!!
1) 调用
CALL pro1(@s);
SELECT @s;
2.关于参数
DELIMITER //
DROP PROCEDURE IF EXISTS `test_param_null` //
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_param_null`(IN inVal INT(4),OUT outVal INT(4))
BEGIN
DECLARE counter INT(2) DEFAULT 0;
SET counter = inVal;
#set outVal = 0;
WHILE counter <= 100 DO
SET outVal = outVal + counter;
SET counter = counter +1;
END WHILE;
END //
DELIMITER ;
其中in表示输入参数,out表示输出参数,inout表示既可以输入也可以输出,存储过程没有返回值,所以使用 参数 获取 程序中的值;
点:
变量使用前必须初始化--如果直接使用 CALL test_cursor(@count); 的话,得到的结果 是null,也就是说 输入的参数 是 null的话,相加多少 都是null
即 SELECT NULL + 1 + 1 +1; 怎么都是 null
所以在这里判断 如果 传入的是 null,赋值为0,这样才能有结果。
但是 为什么
SET @count = 1 ;
CALL test_param_null(10,@count);
不判断 这样调用 结果也是null呢,不是 传入值了吗?
SET @co = 1 ;
SELECT @co + 3;
的方式 是可以的啊
- 变量定义和赋值
DECLARE MYPARAM INT DEFAULT 100;
declare v int(2) default 1;DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;
点:
MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值
DECLARE NAME CHAR(50);
DECLARE id DECIMAL(8,2);
SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2;
附:
Mysql 常用数据类型
1.字符数据类型
CHAR | 1~255个字符的定长串,它的长度必须在创建时指定,否则MySQL假定为CHAR(1) |
VARCHAR | 可变长度,最多不超过255字节,如在创建时指定VARCHAR(n),则可存储0~n个字符的变长串 |
TINYTEXT | 同TEXT,最大长度为255字节 |
MEDUIMTEXT | 同TEXT,最大长度为16K |
TEXT | 最大长度为64K的变长文本 |
LONGTEXT | 同Text,最大长度为4GB(纯文本,一般不会到4G) |
ENUM | 接受最多64K个串组成的预定义集合的某个串 |
SET | 接受最多64K个串组成的预定义集合的零个或多个串
|
注意: 不管任何形式的串数据类型,串值都必须在引号内(通常单引号更好);
如果数值是计算中使用的数值,则应存储在数值数据类型列中,如果作为字符串使用(如电话号码、邮政编码)则应保存在串数据类型列中。
2.数值数据类型
类型说明 | 存储需求 | 取值范围 |
tinyint[(m)] | 1字节 | 有符号值:-128 到127(- 2^7 到2^7 – 1) 无符号值:0到255(0 到2^8 – 1) |
smallint[(m)] | 2字节 | 有符号值:-32768 到32767(- 2^15 到2^15 – 1) 无符号值:0到65535(0 到21 6 – 1) |
mediumint[(m)] | 3字节 | 有符号值:-8388608 到8388607(- 2^23 到2^23 – 1 ) 无符号值:0到16777215(0 到2^24 – 1) |
int[(m)] | 4字节 | 有符号值:-2147683648 到2147683647(- 2^31 到2^31- 1) 无符号值:0到4294967295(0 到2^32 – 1) |
bigint[(m)] | 8字节 | 有符号值:-9223372036854775808 到9223373036854775807(- 2^63到2^63-1) 无符号值:0到18446744073709551615(0到2^64 – 1) |
float[(m, d)] | 4字节 | 最小非零值:±1.175494351e – 38 |
double[(m, d)] | 8字节 | 最小非零值:±2.2250738585072014e – 308 |
decimal (m, d) | m字节(mysql < 3.23),m+2字节(mysql > 3.23 ) | 可变;其值的范围依赖于m 和d |
mysql提供了5种整型: tinyint、smallint、mediumint、int和bigint(字节数1、2、3、4、8),这些类型在可表示的取值范围上是不同的。整数列可定义为unsigned从而禁用负值;这使列的取值范围为0以上。
mysql 提供三种浮点类型: float、double和decimal。与整型不同,浮点类型不能是unsigned的,其取值范围也与整型不同,这种不同不仅在于这些类型有最大值,而且还有最小非零值。最小值提供了相应类型精度的一种度量,这对于记录科学数据来说是非常重要的(当然,也有负的最大和最小值)。
在选择了某种数值类型时,应该考虑所要表示的值的范围,只需选择能覆盖要取值的范围的最小类型即可。选择较大类型会对空间造成浪费,使表不必要地增大,处理起来没有选择较小类型那样有效。对于整型值,如果数据取值范围较小,如人员年龄或兄弟姐妹数,则tinyint最合适。mediumint能够表示数百万的值并且可用于更多类型的值,但存储代价较大。bigint在全部整型中取值范围最大,而且需要的存储空间是表示范围次大的整型int类型的两倍,因此只在确实需要时才用。对于浮点值,double占用float的两倍空间。除非特别需要高精度或范围极大的值,一般应使用只用一半存储代价的float型来表示数据。
在定义整型列时,可以指定可选的显示尺寸m。如果这样,m应该是一个1 到255的整数。它表示用来显示列中值的字符数。例如,mediumint(4)指定了一个具有4个字符显示宽度的mediumint列。如果定义了一个没有明确宽度的整数列,将会自动分配给它一个缺省的宽度。缺省值为每种类型的"最长"值的长度。如果某个特定值的可打印表示需要不止m个字符,则显示完全的值;不会将值截断以适合m个字符。需要注意的是,使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
对每种浮点类型,可指定一个最大的显示尺寸m 和小数位数d。m 的值应该取1 到255。d的值可为0 到3 0,但是不应大于m – 2(如果熟悉odbc 术语,就会知道m 和d 对应于odbc 概念的"精度"和"小数点位数")。m和d对float和double 都是可选的,但对于decimal是必须的。在选项m 和d时,如果省略了它们,则使用缺省值。
- 3.
日期和时间数据类型MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份,DATE表示日期,TIME表示时间,DATETIME和TIMESTAMP表示日期和实践。具体如下表:
数据类型 | 存储字节数 | 取值范围 |
DATE | 4 | 1000-01-01——9999-12-31 |
TIME | 3 | -838:59:59——838:59:59 |
DATETIME | 8 | 1000-01-01 00:00:00——9999-12-31 23:59:59 |
TIMESTAMP | 4 | 19700101080001——20380119111407 |
YEAR | 1 | 1901——2155 |
MySQL是以YYYY-MM-DD格式来显示DATE类型的值,插入数据时,数据可以保持这种格式。另外,MySQL还支持一些不严格的语法格式,分隔符"-"可以用"@"、"."等符号来替代。 在插入数据时,也可以使用"YY-MM-DD"格式,YY转化成对应的年份的规则与YEAR类型类似。如果我们想插入当前系统的时间,则可以插入CURRENT_DATE或者NOW()。
TIME类型表示为"时:分:秒",尽管小时范围一般是0~23,但是为了表示某些特殊时间间隔,MySQL将TIME的小时范围扩发了,而且支持负值。对TIME类型赋值,标准格式是'HH:MM:SS',但不一定非要是这种格式。 如果插入的是'D HH:MM:SS'格式,则类似插入了'(D*24+HH):MM:SS'。比如插入'2 23:50:50',相当于插入了'71:50:50'。如果插入的是'HH:MM'或'SS'格式,则效果是其他未被表示位的值赋为零值。比如插入'30',相当于插入了'00:00:30';如果插入'11:25',相当于插入了'11:25:00'。在MySQl中,对于'HHMMSS'格式,系统能够自动转化为标准格式。如果我们想插入当前系统的时间,则可以插入CURRENT_TIME或者NOW()。
DATETIME类型准格式为"YYYY-MM-DD HH:MM:SS",具体赋值方法与上面的方法相似。
TIMESTAMP的取值范围比较小,没有DATETIME的取值范围大,因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。那么TIMESTAMP类型如何插入当前时间?第一,可以使用CURRENT_TIMESTAMP;第二,输入NULL,系统自动输入当前的TIMESTAMP;第三,无任何输入,系统自动输入当前的TIMESTAMP。 另外有很特殊的一点:TIMESTAMP的数值是与时区相关。
给YEAR类型复制可以有三种方法: 第一种是直接插入4位字符串或者4位数字; 第二种是插入2位字符串,这种情况下如果插入'00'~'69',则相当于插入2000~2069;如果插入'70'~'99',则相当于插入1970~1999。第二种情况下插入的如果是'0',则与插入'00'效果相同,都是表示2000年; 第三种是插入2位数字,它与第二种(插入两位字符串)不同之处仅在于:如果插入的是一位数字0,则表示的是0000,而不是2000年。所以在给YEAR类型赋值时,一定要分清0和'0',虽然两者相差个引号,但实际效果确实相差了2000年。
数据类型 | 说明 |
TITYBLOB | 最大长度为255字节 |
BLOB | 最大长度为64KB |
MEDIUMBLOB | 最大长度为16MB |
LONGBLOB | 最大长度为4GB |
4.二进制数据类型:二进制类型可存储任何数据,如文字、图像、多媒体等。具体类型描述如下:
数据类型 | 说明 |
TITYBLOB | 最大长度为255字节 |
BLOB | 最大长度为64KB |
MEDIUMBLOB | 最大长度为16MB |
LONGBLOB | 最大长度为4GB |
- 流程控制语句 WHEN...Case
DROP PROCEDURE IF EXISTS `test_case`;
DELIMITER //CREATE PROCEDURE test_case(IN v INT(2),OUT n INT(2))
BEGIN
/*
CALL test_case(3,@n);
SELECT @n;
*/
#declare v int(2) default 1;
CASE v
WHEN 1 THEN
SET n = v*1;
WHEN 2 THEN
SET n = v*4;
WHEN 3 THEN
SET n = v*10;
SELECT n;
END CASE;
END //
- 流程控制语句 IF
DELIMITER $$
DROP PROCEDURE IF EXISTS `syntax_if`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `syntax_if`(IN id_p INT,OUT u_name VARCHAR(200))
BEGIN
DECLARE u_p_name VARCHAR(200) DEFAULT '-NULL-';
DECLARE num INT(2) DEFAULT 100;
#这是单行注释
/*
问题:
1.no data - zero rows fetched,selected,or processed.
使用 SELECT fullname INTO u_p_name FROM navi_issuer WHERE id = 1;
的语句 总是 出 no data - zero rows fetched,selected,or processed.
SELECT INTO 语句没有发挥作用。
后来改为
SELECT i.fullname INTO u_p_name FROM navi_issuer i WHERE i.id = 1;
基本可以确定 的原因是 参数名称和 要查询的字段 名称 的参数 重复,id 实际上是传入的参数。
这个字段名 和参数名 重复的问题一定要注意!易出错误且难以查找。
2.sql中拼接字符 没有 “+” 号,而是使用 CONCAT("11" ,"22","1111"....);
3.SELECT就算 打印了,但是 如果写了多行, 好像只能 打印出 第一行。
4.这样可以 动态的 调用存储过程--动态sql?
是否可以直接 写 sql 时定义语句,自己测试 总是 出错
DECLARE @verifyUserId INT(2) ;
SET @verifyUserId = 1;
DECLARE cashOutId INT DEFAULT 1;
DECLARE userIp VARCHAR(200) DEFAULT '127';
================√==============
SET @verifyUserId = 1;
SET @cashOutId = 1;
SET @userIp = '1111';
INSERT INTO navi_opt_log(muser_id,opt_content,ADDTIME,addip)
VALUES(verifyUserId,CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP()),',提现申请【',cashOutId,'】已经审核通过!管理员标识',verifyUserId),
UNIX_TIMESTAMP(),userIp);
*/
#SELECT id_p>0 AND id_p <num;
IF id_p>0 AND id_p > num THEN
SELECT shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 1;
SELECT u_p_name;
ELSEIF id_p>0 AND id_p <num THEN
SELECT i.shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 2;
#set u_p_name ='222222';
SELECT u_p_name;
ELSE
SELECT i.shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 3;
SELECT u_p_name;
END IF;#注意 enif 后面有分号。
#IF id_p>0 AND id_p > num THEN
#SELECT i.fullname INTO u_p_name FROM navi_issuer i WHERE i.id = 1;
#END IF;
#select count(*) into num from navi_issuer;
#SELECT id_p;
#select u_p_name;
SET u_name = CONCAT(u_p_name ,"TTT");
END$$
DELIMITER ;
附:
存储过程 操作符:
算术运算符
+ 加 SET var1=2+2; 4
- 减 SET var2=3-2; 1
* 乘 SET var3=3*2; 6
/ 除 SET var4=10/3; 3.3333
DIV 整除 SET var5=10 DIV 3; 3
% 取模 SET var6=10%3 ; 1
比较运算符
> 大于 1>2 False
< 小于 2<1 False
<= 小于等于 2<=2 True
>= 大于等于 3>=2 True
BETWEEN 在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN 在集合中 5 IN (1,2,3,4) False
NOT IN 不在集合中 5 NOT IN (1,2,3,4) True
= 等于 2=3 False
<>, != 不等于 2<>3 False
<=> 严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL 为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL True
逻辑运算符
AND OR XOR
位运算符
| 位或
& 位与
<< 左移位
>> 右移位
~ 位非(单目运算,按位取反)
- 流程控制语句 循环---Loop...ITERATE---LEAVE loop;While....do;repeat...until
#loop test 循环测试:loop;while;repeatDROP PROCEDURE IF EXISTS `syntax_loop`;
DELIMITER //
CREATE PROCEDURE syntax_loop(INOUT loop_param INT(4),INOUT while_param INT(4),INOUT repeat_param INT(4))
BEGIN
/*
loop1: LOOP
SET i=i+1;
IF i>=10 THEN
LEAVE loop1;
ELSEIF MOD(i,2)=0 THEN
ITERATE loop1;
END IF;
SELECT CONCAT(i,' is an odd number');
END LOOP loop1;
可以这样理解ITERATE就是我们程序中常用的contiune,而LEAVE 就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.
*/
# loop leave loop
/**/
loop1:LOOP
SET loop_param =2*loop_param;
IF(loop_param>100) THEN
LEAVE loop1;
END IF;###################### Attention:end if后面有分号。。。。。。
END LOOP loop1;
#while do
/**/
WHILE while_param <=100 DO
SET while_param = 3*while_param;
END WHILE;
#repeat until
REPEAT
SET repeat_param = 4*repeat_param;
UNTIL
repeat_param >100#Attention:until 后面的的条件 后面没有分号。
END REPEAT ;
/**/
SELECT loop_param;
SELECT while_param;
SELECT repeat_param;
/*
#http://www.blogjava.net/nonels/archive/2009/04/22/233324.html
动态的调用(InOut 参数的 形式)
SET @param_loop = 3;
SET @param_while = 2;
SET @param_repeat = 5;
CALL syntax_loop(@param_loop,@param_while,@param_repeat);
SELECT @param_loop;
SELECT @param_while;
SELECT @param_repeat;
*/END//
- 游标 (光标)cursor
DROP PROCEDURE IF EXISTS `test_cursor`;
#DROP 这里 绝对不能用 单引号,必须 用 [`]
DELIMITER //CREATE PROCEDURE test_cursor(OUT outVal INT(10))BEGIN
DECLARE cunter INT(2) DEFAULT 1;
DECLARE num INT(4) DEFAULT 4;
#定义游标
DECLARE cur CURSOR FOR SELECT id FROM navi_issuer;
/*
表示将从table1表中选取col1列的内容放到游标curl中,
即每次游标遍历的结果都放在curl中,要注意游标只能向前遍历,而不能向后,并且注意,游标不能更新,最后关闭游标。
*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;
#定义 当 CURSOR 没有数据返回的 时候的 操作;
#它的含义是:若没有数据返回,程序继续,并将变量 cunter 设置值 ,这种情况是出现在select XX into XXX from tablename的时候发生的。
#打开游标
OPEN cur;
#select cur;
/*
变量使用前初始化的问题--如果直接使用 CALL test_cursor(@count); 的话,
得到的结果 是null,也就是说 输入的参数 是 null的话,相加多少 都是null
即 SELECT NULL + 1 + 1 +1; 怎么都是 null
*/
IF outVal IS NULL THEN
SET outVal = 0;
END IF;
#select outVal;
WHILE cunter >0 DO
FETCH cur INTO num;
#select num;
IF num >0 THEN
#select num;
SET outVal = outVal + num;
END IF;
SELECT outVal;
END WHILE;
#关闭游标
CLOSE cur;
END //
点:
MYSQL中,光标只能在存储过程和函数中使用!!
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;
#定义 当 CURSOR 没有数据返回的 时候的 操作;
#它的含义是:若没有数据返回,程序继续,并将变量 cunter 设置值 ,这种情况是出现在select XX into XXX from tablename的时候发生的。
- 异常捕获和处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;
#定义 当 CURSOR 没有数据返回的 时候的 操作;
#它的含义是:若没有数据返回,程序继续,并将变量 cunter 设置值 ,这种情况是出现在select XX into XXX from tablename的时候发生的。
实际上 游标的 没有数据时 的定义 就是 捕获操作,意为 :
当发生了 某个异常,程序 继续,并且 赋值 给 某个变量
//方法一:
捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'SET @info='CAN NOT FIND';
//方法二:捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1148 SET @info='CAN NOT FIND';
//方法三:先定义条件,然后调用
DECLARE can_not_find CONDITION FOR 1146 ;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';
//方法四:
使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五:
使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';
//方法六:
使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
- 数据库交互(存储过程中的sql语句)
uINTO用于存储单行记录的查询结果
DECLARE total_sales NUMERIC(8, 2);
SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;
uCURSOR用于处理多行记录的查询结果
DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
CLOSE cur1;
uunbounded SELECT语句用于存储过程返回结果集( ?)
SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees
u其他的SQL语句 也可以 在存储过程中使用
UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里
DELIMITER $$
USE `mytest`$$
DROP PROCEDURE IF EXISTS `test_sql_in_procedure`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_sql_in_procedure`(IN add_title VARCHAR(500),IN upt_id INT(4),IN del_id INT(4),OUT outVal VARCHAR(10))
BEGIN
/*
CALL test_sql_in_procedure('13省养老金并轨.',1,295,@outVal);
SELECT @outVal;
*/
/*
动态sql 的编译和执行:
mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
mysql> SET @p='1';
mysql> SET @q='2';
mysql> EXECUTE prod USING @p,@q;
mysql> SET @name='3';
mysql> EXECUTE prod USING @p,@name;
mysql> DEALLOCATE PREPARE prod;
*/
DECLARE num INT(2) DEFAULT 0;
#insert
IF add_title IS NULL OR add_title ='' THEN
SET add_title = '空值。。。';
END IF;
INSERT INTO navi_new (title) VALUES(add_title);
#update
IF upt_id IS NULL OR upt_id < 1 THEN
SET upt_id = 1;
END IF;
UPDATE navi_issuer SET shortname = CONCAT(shortname,'_UPT_',NOW()) WHERE id = upt_id;
#delete
IF del_id IS NULL OR del_id <1 THEN
SET del_id = 295;
END IF;
DELETE FROM navi_issuer WHERE id = del_id;
SET num = 3;
SET outVal = num;
END$$
DELIMITER ;
u动态sql 的执行和引用
(怎么获取 update 语句的 影响了多少行 的那个 返回值?)
http://maoyifa100.iteye.com/blog/1900305
语法[sql]PREPARE statement_name FROM sql_text /*定义*/
EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/
DEALLOCATE PREPARE statement_name /*删除定义*/
例[sql]
mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
mysql> SET @p='1';
mysql> SET @q='2';
mysql> EXECUTE prod USING @p,@q;
mysql> SET @name='3';
mysql> EXECUTE prod USING @p,@name;
mysql> DEALLOCATE PREPARE prod;
1.用变量做表名: 简单的用set或者declare语句定义变量,然后直接作为sql的表名是不行的,mysql会把变量名当作表名。
在其他的sql数据库中也是如 此,mssql的解决方法是将整条sql语句作为变量,其中穿插变量作为表名,然后用sp_executesql调用该语句。
这在mysql5.0之前是不行的,5.0之后引入了一个全新的语句,可以达到类似sp_executesql的功能(仅对procedure有 效,function不支持动态查询):
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;
- 常用函数
CONCAT:SELECT CONCAT('FIRST ', 'SECOND',’1111’,......);
SET title=REPLACE(sendTitle,'webName',webName);
Now();
。。。。。
mysql常用函数 - sugang_ximi的专栏 - 博客频道
MySql 存储过程中要用到的运算符 - de.cel的日志 - 网易博客 http://de.cel.blog.163.com/blog/static/5145123620113201033629/
mysql存储过程基本函数
一.字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (STRING ,SUBSTRING ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,LENGTH ) //从string2中的左边起取length个字符
LENGTH (STRING ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (SUBSTRING , STRING [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,LENGTH ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,COUNT ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,LENGTH ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , POSITION [,LENGTH ]) //从str的position开始,取length个字符,
注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1
mysql> SELECT SUBSTRING(’abcd’,0,2);
+———————–+
| SUBSTRING(’abcd’,0,2) |
+———————–+
| |
+———————–+
1 ROW IN SET (0.00 sec)
mysql> SELECT SUBSTRING(’abcd’,1,2);
+———————–+
| SUBSTRING(’abcd’,1,2) |
+———————–+
| ab |
+———————–+
1 ROW IN SET (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,LENGTH) //取string2最后length个字符
SPACE(COUNT) //生成count个空格
二.数学类
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注:HEX()中可传入字符串,则返回其ASC-11码,如HEX(’DEF’)返回4142143
也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,POWER ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注:返回类型并非均为整数,如:
(1)默认变为整形值
mysql> SELECT ROUND(1.23);
+————-+
| ROUND(1.23) |
+————-+
| 1 |
+————-+
1 ROW IN SET (0.00 sec)
mysql> SELECT ROUND(1.56);
+————-+
| ROUND(1.56) |
+————-+
| 2 |
+————-+
1 ROW IN SET (0.00 sec)
(2)可以设定小数位数,返回浮点型数据
mysql> SELECT ROUND(1.567,2);
+—————-+
| ROUND(1.567,2) |
+—————-+
| 1.57 |
+—————-+
1 ROW IN SET (0.00 sec)
SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方
三.日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (DATETIME ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (DATETIME ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (DATE ) //返回日期的天
DAYNAME (DATE ) //英文星期
DAYOFWEEK (DATE ) //星期(1-7) ,1为星期天
DAYOFYEAR (DATE ) //一年中的第几天
EXTRACT (interval_name FROM DATE ) //从date中提取日期的指定部分
MAKEDATE (YEAR ,DAY ) //给出年及年中的第几天,生成日期串
MAKETIME (HOUR ,MINUTE ,SECOND ) //生成时间串
MONTHNAME (DATE ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (STRING ,FORMAT ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (TIME ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (DATETIME ) //年份
DAYOFMONTH(DATETIME) //月的第几天
HOUR(DATETIME) //小时
LAST_DAY(DATE) //date的月的最后日期
MICROSECOND(DATETIME) //微秒
MONTH(DATETIME) //月
MINUTE(DATETIME) //分
附:可用在INTERVAL中的类型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR
- TIPS
◆存储过程里面是可以调用其他存储过程的,使用CALL语句调用其他存储过程就可以了
◆存储过程参数列表里的参数名尽量不要和数据库中表的字段名一样,否则有可能出错
◆存储过程的参数可以使用中文,在定义存储过程的时候加上character set gbk就可以了
例如
CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) CHARACTER SET gbk,OUT u_age INT)
- 其他命令
SHOW FUNCTION STATUS LIKE '%name_from_t3%'; like 存储过程名称
SHOW CREATE FUNCTION name_from_t3;查看定义
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;
从information_schema.Routines表中查看存储过程和函数的信息
DROP FUNCTION IF EXISTS `name_from_t3`$$
- 试例
1.
CREATE PROCEDURE putting_it_all_together(in_department_id INT)
MODIFIES SQL DATA BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC(8,2);
DECLARE l_department_id INT;
DECLARE l_new_salary NUMERIC(8,2);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT employee_id, salary, department_id
FROM employees
WHERE department_id=in_department_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
(employee_id INT, department_id INT, new_salary NUMERIC(8,2));
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done=1 THEN /* No more rows */
LEAVE emp_loop;
END IF;
CALL new_salary(1_employee_id, l_new_salary); /* Get new salary */
IF (l_new_salary <> l_salary) THEN /* Salary changed */
UPDATE employees
SET salary=l_new_salary
WHERE employee_id=l_employee_id;
/* Keep track of changed salaries */
INSERT INTO emp_raises(employee_id, department_id, new_salary)
VALUES (l_employee_id, l_department_id, l_new_salary);
END IF:
END LOOP emp_loop;
CLOSE cur1;
/* Print out the changed salaries */
SELECT employee_id, department_id, new_salary from emp_raises
ORDER BY employee_id; END;
2015-10-28 16:18:18
DELIMITER $$
CREATE PROCEDURE pro2(OUT s INT)
BEGIN SELECT COUNT(*) INTO s FROM navi_issuer;
END $$