MySQL函数大全

傷城~ 2021-12-10 00:43 295阅读 0赞

# 数学函数 #

*\#* *绝对值函数**abs(X)*  
**select** *abs*(-100);  
  
*\#* *圆周率函数**pi()*  
**select** *pi*();  
  
*\#* *平方根函数**sqrt(X)*  
**select** *sqrt*(9);  
  
*\#* *求余函数**mod(x, y)*  
**select** *mod*(31, 7);  
  
*\#* *获取整数的函数**ceil(x) ceiling(x) floor(x)*  
**select** *ceil*(-4.6), *ceiling*(3.66), *floor*(3.55);  
  
*\#* *获取随机数函数**rand() rand(x)*  
**select** *rand*(), *rand*(10);  
  
*\#* *四舍五入函数**round(x) round(x, y) truncate(x, y)*  
**select** *round*(-1.145), *round*(-1.145, 2), *truncate*(1.99, 1);  
  
*\#* *符号函数**sign(x)*  
**select** *sign*(-20), *sign*(0), *sign*(20);  
  
*\#* *幂运算函数**pow(x, y) power(x, y) exp(x)*  
**select** *pow*(2, 2), *power*(2, 2), *exp*(3);  
  
*\#* *对数运算函数**log(x) log10(x)*  
**select** *log*(3), *log10*(3);  
  
*\#* *角度与弧度互换函数**radians(x) degrees(x)*  
**select** *radians*(90), *degrees*(*pi*() / 2);  
  
*\#* *正弦和反弦函数**sin(x) asin(x)*  
**select** *sin*(1), *asin*(1);  
  
*\#* *余弦与反余弦函数**cos(x) acos(x)*  
**select** *cos*(1), *acos*(10);  
  
*\#* *正切**/**反正切和余切函数*  
**select** *tan*(0.3), *atan*(100), *cot*(500);

## 字符串函数 ##

# 字符串字符数和长度函数char_length(x) length(x) select char_length('aciifj'), length('sifjls');
    
    
    
    # 合并字符串函数concat(s1, s2......)/concat_ws(x, s1, s2......) select concat('MySQL', ' ', '5.7'), concat_ws(',', '1', '2', '3');
    
    
    
    # 替换字符串函数insert(s1, x, len, s2) select insert('AAAAAAA', 2, 4, 'BBBBBBB');
    
    
    
    # 字母大小写转换lower(x), lcase(x), upper(x), ucase(x) select lower('AAAAAAA'), lcase('AAAbbbb'), upper('jack'), ucase('rose');
    
    
    
    # 获取指定长度的字符串函数left(s, n) right(s, n) select left('ABCDEFG', 5), right('ABCDEFG', 5);
    
    
    
    # 填充字符串的函数lpad(s1, len, s2) rpad(s1, len, s2) select lpad('hello', 7, '*'), rpad('hello', 7, '*');
    
    
    
    # 删除空格函数ltrim(s) rtrim(s) trim(s) select ltrim(' hello world '), rtrim(' hello world '), trim(' hello world ');
    
    
    
    # 删除指定字符串的函数trim(s1 from s) select trim('xy' from 'xy hello xy world xy');
    
    
    
    # 重复生成字符串的函数repeat(s, n) select repeat('MySQL', 2);
    
    
    
    # 空格函数space(n) 替换函数replace(s, s1, s2) select space(6), replace('yyy.bying.com', 'y', 'w');
    
    
    
    # 比较字符串大小的函数strcmp(s1, s2) select strcmp('txt', 'txt2'), strcmp('txt2', 'txt'), strcmp('txt2', 'txt2');
    
    
    
    # 获取子串函数substring(s, n, len) mid(s, n, len) select substring('aaabbbccc', 6), substring('aaabbbccc', 4, 3), substring('aaabbbccc', -3, 3);
    
    select mid('aaabbbccc', 6), mid('aaabbbccc', 4, 3), mid('aaabbbccc', -3, 3);
    
    
    
    # 匹配子串开始位置函数locate(s1, s) position(s1 in s) instr(s, s1) select locate('bb', 'aabbcc'), position('bb' in 'aabbcc'), instr('aabbcc', 'bb');
    
    
    
    # 字符串逆序函数reverse(s) select reverse('aabbcc');
    
    
    
    # 返回指定位置的字符串函数elt(n, s1, s2, s3) select elt(3, '1', '2', '3'), elt(3, '1', '2');
    
    
    
    # 返回指定字符串位置的函数field(s, s1, s2......) select field('aa', 'cc', 'bb', 'aa');
    
    
    
    # 返回子串位置的函数find_in_set(s1, s2) select find_in_set('aa', 'cc,bb,aa');
    
    
    
    # 选取字符串的函数make_set(x, s1, s2) select make_set(1, 'a', 'b', 'c'), make_set(1 | 3, 'a', 'b', 'c'), make_set(0, 'a', 'b', 'c');

## 日期和时间函数 ##

# 获取当前日期和当前时间 select curdate(), current_date();
    
    select CURTIME(), current_time();
    
    
    
    # 获取当前日期时间 select current_timestamp(), localtime, now(), sysdate();
    
    
    
    # unix时间戳函数 select unix_timestamp(), unix_timestamp(now());
    
    
    
    # 时间戳转换为时间格式from_unixtime(date) select from_unixtime(1563121659);
    
    
    
    # 获取utc日期和和utc时间 select utc_date(), utc_time();
    
    
    
    # 获取月份month(date) monthname(date) select month('2019-09-09'), monthname('2019-09-09');
    
    
    
    # 获取星期函数dayname(d), dayofweek(d), weekday(d) select dayname('2019-09-09'), dayofweek('2019-09-09'), weekday('2019-09-09');
    
    
    
    # 获取星期函数week(d) weekofyear(d)

select week('2019-09-09'), week('2019-09-09', 0), week('2019-09-09', 1);
    
    select weekofyear('2019-09-09');
    
    
    
    # 获取天数的函数dayofyear(d) dayofmonth(d) select dayofyear('2019-09-09 05:06:07'), dayofmonth('2019-09-09');
    
    
    
    # 获取年份 季度 小时 分钟和秒的函数 select year('2019-09-09 05:06:07'), quarter('2019-09-09 05:06:07'), minute('2019-09-09 05:06:07'), second('2019-09-09 05:06:07');
    
    
    
    # 获取日期的指定值函数extract(type of date) select extract(YEAR from '2019-09-09 05:06:07');
    
    
    
    # 时间和秒转换的函数 select time_to_sec('06:07:08');
    
    select sec_to_time(22028);
    
    
    
    # 日期和时间格式化 select date_format(now(), '%W %M %Y');
    
    select time_format(now(), '%H %k %h %I %l');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 1][]

## 条件判断函数 ##

# 判断函数if(v1, v2) select if (1 < 2, 'true', 'false');
    
    
    
    # 判空函数ifnull(v1, v2) select ifnull(null, 'yes'), ifnull('1', '2');
    
    
    
    # 多条件函数 select case 2 when 1 then 1 when 2 then 2 else 3 end;

## 加/解密函数 ##

# password(s) select Password('word'), Password(null);
    
    # md5(s) select MD5('word');
    
    # encode(str, pswd_str) select encode('word', 'str');
    
    # decode(crypt_str, pswd_str) select decode(encode('word', 'str'), 'str')

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70]: /images/20211209/9b40aaae7d7a43ce8702d7baf65ae207.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 1]: /images/20211209/f01408d4c6cf41029abe4ca4acfc7a9a.png