mysql 学习

#-----------------------------------
#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();