MySQL常用函数，mysql函数

MySQL常用函数，mysql函数



1.1    函数

1.31 截取函数

1.311 substring

MySQL的substring函数简析：

mysql> SELECTSUBSTRING(‘Quadratically’,5);

-> ‘ratically’

mysql> SELECT SUBSTRING(‘foobarbar’ FROM4);

-> ‘barbar’

mysql> SELECTSUBSTRING(‘Quadratically’,5,6);

-> ‘ratica’

mysql> SELECT SUBSTRING(‘Sakila’, -3);

-> ‘ila’

mysql> SELECT SUBSTRING(‘Sakila’, -5,3);

-> ‘aki’

mysql> SELECT SUBSTRING(‘Sakila’ FROM -4FOR 2);

-> ‘ki’

1.312 substring_index

MySQL的substring_index函数简析：

假定有一张表，其中有个字段是 email 保存了电子邮箱，然后，想统计每种邮箱的占比情况。

当知道电子信箱的格式是： account@domain ，要实现上面的统计，必须取得 email

字符串中 @ 后面的域名部分。

MySQL提供了一个名为substring_index 的函数，能够满足需求：

mysql> SELECTSUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);

-> ‘www.mysql’

mysql> SELECTSUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);

-> ‘mysql.com’

例子：

select substring_index(mail,'163.com',2) asmail from member

显示出所有的163.com邮箱

1.32 日期

dayofweek('2014-06-08') 查询日期是当周的第几天，周日为1

mysql的时间相减是做了一个隐式转换操作，将时间转换为整数，但并不是用unix_timestamp转换，而是直接把年月日时分秒拼起来，要得到正确的时间相减秒值，有以下3种方法t2-t1：

　　1、time_to_sec(timediff(t2, t1)),

　　2、timestampdiff(second, t1, t2),

　　3、unix_timestamp(t2)-unix_timestamp(t1)

 

DAY改为MONTH就变成加减月

指定当月第几日，如20号：

date_format(subdate(curdate(),DAYOFMONTH(curdate())-20),'%Y%m%d')

当前时间减几个月：

DATE_SUB(curdate(),INTERVAL  6 month)

Dayname 日期是星期几  monthname 月份英文  dayofyear 该日期是该年的第几天

7天后: adddate(now(),interval 7 day)

两个日期或时间的差值：Datediff(d1,d2)

select last_day(curdate()); #获取本月最后一天

select date_add(curdate(), interval -day(curdate()) + 1 day); #获取本月第一天

select date_add(curdate() - day(curdate()) +1, interval 1 month); #获取下个月第一天

select day(last_day(curdate())); #获取本月天数

select date_sub(curdate(), interval 1 month);#获取一个月前那一天

select datediff(curdate(), date_sub(curdate(),interval 1 month)); #获取当前时间与一个月之间的天数

根据年份的第几周获取当周的周三日期：

1月1号+22*7天

然后根据1月1号礼拜几，加几天，或者减几天

如果1月1号是礼拜三，那就直接是1月1号+22*7

如果是礼拜二，那就1月1号+22*7+1

如：date_format(date_add('2013-01-01', interval 统计周*7+1 day),'%Y%m%d')

1.33 个别函数注意点

1、case when语句，用于计算条件列表并返回多个可能结果表达式之一。

mysql中使用case when 的时候不能判断null再处理的，case when null then 后面的赋值是不能正常处理的，应使用ifnull

方法一：

CASEinput_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

方法二：

CASE   
WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

1.34 创建函数格式

DELIMITER //   -- 定义结束用‘//’

DROP FUNCTION IF EXISTS  fun_BDSL_ZDBOARD_zh;

CREATE FUNCTION fun_BDSL_ZDBOARD_zh(onuid DECIMAL(24,0))

RETURNS bigint(11)

BEGIN

DECLARE s_grade int;  -- 声明变量

DECLARE p_aid bigint;

DECLARE l_aid bigint;

SELECT grade,parentid,id INTO s_grade,p_aid,l_aid  FROM addr partition(cz) where id=said;

WHILE s_grade>p_grade DO

SELECT id,parentid ,GRADE INTO l_aid,p_aid,s_grade FROM  addr partition(cz) where id=p_aid;

END WHILE;

RETURN l_aid;

END //

DELIMITER ; -- 返回定义结束用‘;’

 

 

1.35 A表字段likeB表字段

A表字段like B表字段

oracle：select * from A A,B B where A.字段名称A like '%' || B.字段名称B || '%';

mysql:select a.*,b.* from A,A left join B B on A.字段名称A like concat("%",B.字段名称B,"%");

 

1.36 Coalesce=ifnull

1.37 cast expression 类型转换表达式

Cast(‘123’ as singed integer) 转换成整数类型integer

Cast(1234 as time ) 转换成时间类型time

Cast( ‘1908-01-10’ as date) 转成日期date

Cast(123 as char) 转换成文本类型alphaumeric