mysql 5.0总结偷得

mysql 5.0存储过程学习总结

一.创建存储过程

1.基本语法：

 

create procedure sp_name()
begin
………
end

2.参数传递

二.调用存储过程

1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程

1.基本语法：
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.区块，条件，循环

1.区块定义，常用
begin
……
end;
也可以给区块起别名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出区块，执行区块以后的代码
2.条件语句

 

if  条件  then
statement
else
statement
end   if ;

3.循环语句
(1).while循环

[ label: ]   WHILE  expression DO

statements

END   WHILE   [ label ]  ;

 

(2).loop循环

 

[ label: ]  LOOP

statements

END  LOOP  [ label ] ;

 

(3).repeat until循环

 

[ label: ]  REPEAT

statements

UNTIL expression

END  REPEAT  [ label ]  ;

 

五.其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
2.show create procedure sp_name
显示某一个存储过程的详细信息

 

 

mysql存储过程中要用到的运算符

mysql存储过程学习总结－操作符

算术运算符

+     加   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)

 

 

 

 

 

 

   

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	NULL
NULL	NULL	NULL	NULL

 

或(OR)

 

 

 

 

 

   

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

 

异或(XOR)

 

 

 

 

 

   

XOR	TRUE	FALSE	NULL
TRUE	FALSE	TRUE	NULL
FALSE	TRUE	FALSE	NULL
NULL	NULL	NULL	NULL

 

位运算符

|   位或
&   位与
<<  左移位
>>  右移位
~   位非(单目运算，按位取反)

 

mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。

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 

对于每个类型拥有的值范围以及并且指定日期何时间值的有效格式的描述见7.3.6 日期和时间类型。 

这里是一个使用日期函数的例子。下面的查询选择了所有记录，其date_col的值是在最后30天以内： 

mysql> SELECT something FROM table 
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; 

DAYOFWEEK(date) 
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 
mysql> select DAYOFWEEK('1998-02-03'); 
-> 3 

WEEKDAY(date) 
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 
mysql> select WEEKDAY('1997-10-04 22:23:00'); 
-> 5 
mysql> select WEEKDAY('1997-11-05'); 
-> 2 

DAYOFMONTH(date) 
返回date的月份中日期，在1到31范围内。 
mysql> select DAYOFMONTH('1998-02-03'); 
-> 3 

DAYOFYEAR(date) 
返回date在一年中的日数, 在1到366范围内。 
mysql> select DAYOFYEAR('1998-02-03'); 
-> 34 

MONTH(date) 
返回date的月份，范围1到12。 
mysql> select MONTH('1998-02-03'); 
-> 2 

DAYNAME(date) 
返回date的星期名字。 
mysql> select DAYNAME("1998-02-05"); 
-> 'Thursday' 

MONTHNAME(date) 
返回date的月份名字。 
mysql> select MONTHNAME("1998-02-05"); 
-> 'February' 

QUARTER(date) 
返回date一年中的季度，范围1到4。 
mysql> select QUARTER('98-04-01'); 
-> 2 

WEEK(date) 
　 
WEEK(date,first) 
对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许
你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，
从星期一开始。 
mysql> select WEEK('1998-02-20'); 
-> 7 
mysql> select WEEK('1998-02-20',0); 
-> 7 
mysql> select WEEK('1998-02-20',1); 
-> 8 

YEAR(date) 
返回date的年份，范围在1000到9999。 
mysql> select YEAR('98-02-03'); 
-> 1998 

HOUR(time) 
返回time的小时，范围是0到23。 
mysql> select HOUR('10:05:03'); 
-> 10 

MINUTE(time) 
返回time的分钟，范围是0到59。 
mysql> select MINUTE('98-02-03 10:05:03'); 
-> 5 

SECOND(time) 
回来time的秒数，范围是0到59。 
mysql> select SECOND('10:05:03'); 
-> 3 

PERIOD_ADD(P,N) 
增加N个月到阶段P（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。 
mysql> select PERIOD_ADD(9801,2); 
-> 199803 

PERIOD_DIFF(P1,P2) 
返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。 
mysql> select PERIOD_DIFF(9802,199703); 
-> 11 

DATE_ADD(date,INTERVAL expr type) 
　 
DATE_SUB(date,INTERVAL expr type) 
　 
ADDDATE(date,INTERVAL expr type) 
　 
SUBDATE(date,INTERVAL expr type) 
这些功能执行日期运算。对于MySQL 3.22，他们是新的。ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的同义词。
在MySQL 3.23中，你可以使用+和-而不是DATE_ADD()和DATE_SUB()。（见例子）date是一个指定开始日期的
DATETIME或DATE值，expr是指定加到开始日期或从开始日期减去的间隔值一个表达式，expr是一个字符串；它可以以
一个“-”开始表示负间隔。type是一个关键词，指明表达式应该如何被解释。EXTRACT(type FROM date)函数从日期
中返回“type”间隔。下表显示了type和expr参数怎样被关联： type值 含义 期望的expr格式 
SECOND 秒 SECONDS 
MINUTE 分钟 MINUTES 
HOUR 时间 HOURS 
DAY 天 DAYS 
MONTH 月 MONTHS 
YEAR 年 YEARS 
MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS" 
HOUR_MINUTE 小时和分钟 "HOURS:MINUTES" 
DAY_HOUR 天和小时 "DAYS HOURS" 
YEAR_MONTH 年和月 "YEARS-MONTHS" 
HOUR_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS" 
DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES" 
DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS" 

MySQL在expr格式中允许任何标点分隔符。表示显示的是建议的分隔符。如果date参数是一个DATE值并且你的计算仅仅
包含YEAR、MONTH和DAY部分(即，没有时间部分)，结果是一个DATE值。否则结果是一个DATETIME值。 

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 
-> 1998-01-01 00:00:00 
mysql> SELECT INTERVAL 1 DAY + "1997-12-31"; 
-> 1998-01-01 
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND; 
-> 1997-12-31 23:59:59 
mysql> SELECT DATE_ADD("1997-12-31 23:59:59", 
INTERVAL 1 SECOND); 
-> 1998-01-01 00:00:00 
mysql> SELECT DATE_ADD("1997-12-31 23:59:59", 
INTERVAL 1 DAY); 
-> 1998-01-01 23:59:59 
mysql> SELECT DATE_ADD("1997-12-31 23:59:59", 
INTERVAL "1:1" MINUTE_SECOND); 
-> 1998-01-01 00:01:00 
mysql> SELECT DATE_SUB("1998-01-01 00:00:00", 
INTERVAL "1 1:1:1" DAY_SECOND); 
-> 1997-12-30 22:58:59 
mysql> SELECT DATE_ADD("1998-01-01 00:00:00", 
INTERVAL "-1 10" DAY_HOUR); 
-> 1997-12-30 14:00:00 
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); 
-> 1997-12-02 
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02"); 
-> 1999 
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); 
-> 199907 
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); 
-> 20102 

如果你指定太短的间隔值(不包括type关键词期望的间隔部分)，MySQL假设你省掉了间隔值的最左面部分。例如，
如果你指定一个type是DAY_SECOND，值expr被希望有天、小时、分钟和秒部分。如果你象"1:10"这样指定值，
MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说，"1:10" DAY_SECOND以它等价于"1:10" MINUTE_SECOND
的方式解释，这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性。如果你使用确实不正确的日期，
结果是NULL。如果你增加MONTH、YEAR_MONTH或YEAR并且结果日期大于新月份的最大值天数，日子在新月用最大的天调整。 

mysql> select DATE_ADD('1998-01-30', Interval 1 month); 
-> 1998-02-28 

注意，从前面的例子中词INTERVAL和type关键词不是区分大小写的。 
TO_DAYS(date) 
给出一个日期date，返回一个天数(从0年的天数)。 
mysql> select TO_DAYS(950501); 
-> 728779 
mysql> select TO_DAYS('1997-10-07'); 
-> 729669 

TO_DAYS()不打算用于使用格列高里历(1582)出现前的值。 

FROM_DAYS(N) 
给出一个天数N，返回一个DATE值。 
mysql> select FROM_DAYS(729669); 
-> '1997-10-07' 

TO_DAYS()不打算用于使用格列高里历(1582)出现前的值。 

DATE_FORMAT(date,format) 
根据format字符串格式化date值。下列修饰符可以被用在format字符串中： %M 月名字(January……December) 
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
%Y 年, 数字, 4 位 
%y 年, 数字, 2 位 
%a 缩写的星期名字(Sun……Sat) 
%d 月份中的天数, 数字(00……31) 
%e 月份中的天数, 数字(0……31) 
%m 月, 数字(01……12) 
%c 月, 数字(1……12) 
%b 缩写的月份名字(Jan……Dec) 
%j 一年中的天数(001……366) 
%H 小时(00……23) 
%k 小时(0……23) 
%h 小时(01……12) 
%I 小时(01……12) 
%l 小时(1……12) 
%i 分钟, 数字(00……59) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%T 时间,24 小时(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 一个星期中的天数(0=Sunday ……6=Saturday ） 
%U 星期(0……52), 这里星期天是星期的第一天 
%u 星期(0……52), 这里星期一是星期的第一天 
%% 一个文字“%”。 

所有的其他字符不做解释被复制到结果中。 

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); 
-> 'Saturday October 1997' 
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); 
-> '22:23:00' 
mysql> select DATE_FORMAT('1997-10-04 22:23:00', 
'%D %y %a %d %m %b %j'); 
-> '4th 97 Sat 04 10 Oct 277' 
mysql> select DATE_FORMAT('1997-10-04 22:23:00', 
'%H %k %I %r %T %S %w'); 
-> '22 22 10 10:23:00 PM 22:23:00 00 6' 
MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。 

TIME_FORMAT(time,format) 
这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。
其他修饰符产生一个NULL值或0。 
CURDATE() 
　 
CURRENT_DATE 
以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。 
mysql> select CURDATE(); 
-> '1997-12-15' 
mysql> select CURDATE() + 0; 
-> 19971215 

CURTIME() 
　 
CURRENT_TIME 
以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。 
mysql> select CURTIME(); 
-> '23:50:26' 
mysql> select CURTIME() + 0; 
-> 235026 

NOW() 
　 
SYSDATE() 
　 
CURRENT_TIMESTAMP 
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的
上下文被使用。 
mysql> select NOW(); 
-> '1997-12-15 23:50:26' 
mysql> select NOW() + 0; 
-> 19971215235026 

UNIX_TIMESTAMP() 
　 
UNIX_TIMESTAMP(date) 
如果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一
个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME
字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。 
mysql> select UNIX_TIMESTAMP(); 
-> 882226357 
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00'); 
-> 875996580 

当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的“string-to-unix-timestamp”变换。 

FROM_UNIXTIME(unix_timestamp) 
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值，取决于函数是在一个字符串
还是或数字上下文中被使用。 
mysql> select FROM_UNIXTIME(875996580); 
-> '1997-10-04 22:23:00' 
mysql> select FROM_UNIXTIME(875996580) + 0; 
-> 19971004222300 

FROM_UNIXTIME(unix_timestamp,format) 
返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条
目同样的修饰符。 
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), 
'%Y %D %M %h:%i:%s %x'); 
-> '1997 23rd December 03:43:30 x' 

SEC_TO_TIME(seconds) 
返回seconds参数，变换成小时、分钟和秒，值以'HH:MM:SS'或HHMMSS格式化，取决于函数是在一个字符串还是在数字
上下文中被使用。 
mysql> select SEC_TO_TIME(2378); 
-> '00:39:38' 
mysql> select SEC_TO_TIME(2378) + 0; 
-> 3938 

TIME_TO_SEC(time) 
返回time参数，转换成秒。 
mysql> select TIME_TO_SEC('22:23:00'); 
-> 80580 
mysql> select TIME_TO_SEC('00:39:38'); 
-> 2378