#-----------------------------------

#1.正则表达式检索 [RegexP]

#-----------------------------------

# * Binary字段可以支持大小写匹配

# * 文本开始^(集合中是否定) 结束$

select * from goods where bid RegexP Binary 'E|a' # * | 为或

select * from goods where bid RegexP Binary '[123]Ton' # * [123]Ton为匹配 1Ton 2Ton 3Ton

select * from goods where bid RegexP Binary '[1-3]Ton' * 同上

select * from goods where bid RegexP Binary '[^123]Ton' # * [123]Ton为不匹配 1Ton 2Ton 3Ton

select * from goods where bid RegexP Binary '\\.' # * 匹配特殊字符需要\\转义

* 也可以匹配元字符, \\f \\n \\r \\t \\v

select 'justalnums' REGEXP '[[:alpha:]]'; # * [:alpha:] 为任意字符(同[a-zA-Z])

* 其他character class:

alnum,blank,cntrl,digit,graph,lower,print,punct,space,upper,xdigit

select '***' RegexP '\\([0-9] sticks?)\\' #

select '***' RegexP '[[:digit:]]{4}' # 存在4位数字的

#-----------------------------------

#2.函数

#-----------------------------------

字符函数

* IFNull() = COALESCE()

时间函数

* now(),curdate(),curtime(),dayofweek(now()) *第一日是周日

* addDate(DataChange_CreateTime,-1),AddTime(DataChange_CreateTime,-1)

* Date(DataChange_CreateTime),year(DataChange_CreateTime),month(DataChange_CreateTime),day(DataChange_CreateTime)

* time(DataChange_CreateTime),hour(DataChange_CreateTime),minute(DataChange_CreateTime),second(DataChange_CreateTime)

* DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')

* date_add(DataChange_CreateTime, interval 1 DAY_MINUTE) * 增

* date_sub(DataChange_CreateTime, interval 1 DAY_MINUTE) * 减

* SELECT now(),unix_timestamp( now() ),from_unixtime(unix_timestamp( now() )) *UTC系统时间戳 与 当前时区timestamp 转换

数值函数

floor()向上取整 round()四舍五入 rand()随机数 ceil() ceiling()向下取整

#-----------------------------------

#3.游标

#-----------------------------------

delimiter //

CREATE PROCEDURE TestCursor()

BEGIN

DECLARE v_id INT;

DECLARE v_value VARCHAR(10);

-- 游标控制的标志

DECLARE no_more_departments INT;

-- 定义游标.

DECLARE c_test_main CURSOR

FOR

SELECT goodsid,cast(now() as CHAR) as value FROM goods;

-- 当游标没有数据的时候

-- 设置 no_more_departments = 1

DECLARE CONTINUE HANDLER

FOR

NOT FOUND SET no_more_departments=1;

-- 设置初始标志位，认为游标是有数据的.

SET no_more_departments=0;

-- 打开游标

OPEN c_test_main;

-- 获取游标数据

FETCH c_test_main INTO v_id, v_value;

-- 循环所有的行

WHILE no_more_departments = 0 DO

-- 输出调试信息

SELECT v_id, v_value;

-- 获取游标数据

FETCH c_test_main INTO v_id, v_value;

END WHILE;

-- 关闭游标

CLOSE c_test_main;

END

//

#drop procedure TestCursor;

CALL TestCursor();