MySQL学习之SQL函数

SQL函数

SQL 函数
任何一个数据库系统都内置了数量相当可观的又非常实用的小函数

这些函数可以根据实现功能的不同划分为不同的类，当然，除了很明显的日期时间和字符串函数两大类外

可以把这些函数归纳为两大类： Aggregate 函数 和 Scalar 函数

Aggregate函数

avg()
count()
max()
min()
sum()
聚合函数中的distinct

SQL Aggregate 函数
    SQL Aggregate 函数用于计算从列中取得的值，并返回一个单一的值

常用的 Aggregate 函数(聚合函数)有：

    函数	       说明
    AVG()	    返回平均值
    COUNT()	    返回行数
    MAX()	    返回最大值
    MIN()	    返回最小值
    SUM()	    返回总和

# 聚合函数：max() / min() / sum() / avg() / count()

    avg() 忽略列值为NULL的行

    count()
        根据参数的不同，COUNT() 大致有三种用法

        1. COUNT ( column_name )
            COUNT(column_name) 函数返回指定列的值的数目，NULL 值除外

            SELECT COUNT(column_name) FROM table_name;

        2. COUNT(*)
            COUNT(*) 函数返回表中的记录数，包括 NULL 值

            SELECT COUNT(*) FROM table_name;

        3. COUNT(DISTINCT column_name )
            COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

            SELECT COUNT(DISTINCT column_name) FROM table_name;

        # 统计一个表有多少行：
        # select count(*) from t11;  # 不写group的话，默认这个表整体为一组
        # count(1) / count(主键) 效率高
        # count(* | 1) 统计行数，不会忽略列值为NULL的行
        # count(column) 会忽略列值为NULL的行

# max() 会忽略列值为NULL的行
# min() 会忽略列值为NULL的行
# sum(column) or sum(column * column) 会忽略列值为NULL的行

# 聚集函数的默认行为是对所有行进行计算，也就是ALL
# 如果想改变这个行为，只想对不同的值进行计算，用DISTINCT
# DISTINCT 不能用于count(*)
# 虽然也可以用于max / min, 但是最后的结果与不用distinct是相同的。
# distinct 只能用于列，不能用于表达式

Scalar函数

SQL Scalar 函数基于输入值，返回一个单一的值

   charset()
   bin()
   oct()
   hex()
   binary()
   cast()
   concat()
   concat_ws()
   group_concat()
   
length()
char_length()

soundex()

conv()
format()

ucase()
lcase()
upper()
lower()

ltrim()
rtrim()
trim()

repeat()
space()
reverse()

instr()
locate()

insert()
replace()

left()
right()
mid()
substring()

数值处理函数
  sin()
  cos()
  tan()
  abs()
  sqrt()
  exp()
  pow()
  log()
  log10()
  round()
  rand()
  pi()

加密函数
  md5()
  password()
  
时间日期函数
  获取
    curdate() / current_date()
    curtime() / current_time()
    current_timestamp()
    now()
  提取
    extract()
    year()
    month()
    day()
    hour()
    minute()
    second()
    microsecond()
    date()
    time()
    week()
    quarter()
    monthname()
    dayname()
    dayofyear()
    dayofweek()
    dayofmonth()
  增
    date_add()
  减
    date_sub()
  获取两个日期间的天数
    datediff()
  时间格式化
    date_format()

# 内置函数
user()
database()

bin()
hex()
oct()


soundex()
binary()  区分大小写

charset()
char_length() 以字符为单位
length() 以字节为单位

concat() 字符串拼接，如果有一个参数为NULL，则返回值为NULL
concat_ws() 可以指定连接符，忽略NULL，但不会忽略任何空字符串; concat_ws('-', 1, 2, 3)
group_concat()

cast()  例如: cast(num as char)
conv(num, c_sys, to_sys) 进制转换
format(n,d) 将数字n以'#,###,###.###'的字符串形式返回，以四舍五入的方式保留小数点后d位，若d为0，则表示结果没有小数部分

upper()  将文本的字母部分转化为大写
lower()  将文本的字母部分转化为小写
ucase()
lcase()

insert(str, pos, len, newstr)
# 第一个字符的索引为1
# 如果pos超过str的长度，则直接返回原str；
# 如果len超过str的长度，则从pos开始由newstr完全替换
# 如果newstr长度小于len，则多余的被删除
replace(str, oldstr, newstr)  返回str中的oldstr被newstr替换后的最终字符串。如果oldstr不在里面，返回原str

left(str, len)  返回从str开头开始的len长度字符串  left('abc', 2) --> ab
right(str, len)  返回从str末尾开始的len长度字符串 right('abc', 2) --> bc

mid()
substring(str, pos, len)  取str的pos位置开始返回len长度的字符串
    substring(str from pos for len)
    substring(str from -pos for len)
    substring(str, pos)
    substring(str from pos)
    substring(str from -pos)

# 第一个字符的索引为1
instr(str, substr) 返回substr在str中出现的第一个位置。若没有，返回0
locate(substr, str, pos)  返回substr在str中从pos开始的第一个索引位置，pos可以没有;如果没有，返回0

repeat(str, count) 返回count*str组成的字符串。如果count或者str为NULL，结果为NULL；如果count为0，结果为空字符串
space(n)  返回一个由n个空格组成的字符串
reverse(str)  返回倒序的str

ltrim()  剔除字符串左边的空白字符
rtrim()  剔除字符串右边的空白字符
trim()   剔除字符串两边的空白字符 =
trim(both ' ' from str)
trim(leading 'x' from str)
trim(trailing 'x' from str)

#############################################################################

# 数值处理函数
# sin() / cos() / tan() / abs() / sqrt() / exp() / pow() / pi() /
# log() / -- 默认以e为底; log(2, 4), 表示以2为底
# log10() /
# round(x, y) 机制跟python中的差不多，忽略5那个特性
# RAND()
  返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

################################################################################

########################################################################################

加密函数
    MD5()
        计算字符串str的MD5校验和
    PASSWORD(str)
        返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

############################################################################################

# 获取日期时间
curdate() / current_date()  # 返回当前日期
curtime() / current_time()  # 返回时间
current_timestamp()
now()  # 返回当前日期和时间
    例子： create table employee(d date, t time, dt datetime);
            insert employee values(now(), now(), now());

# 提取
EXTRACT() 函数
    MySQL EXTRACT() 函数返回日期/时间的单独部分，比如年、月、日、小时、分钟
        EXTRACT(unit FROM date):
        参数	说明
        date	一个合法的日期表达式
        unit	返回值的类型

        unit 参数可以是以下值
        值	                    说明
        MICROSECOND	            毫秒
        SECOND	                秒
        MINUTE	                分
        HOUR	                时
        DAY	                    天
        WEEK	                周
        MONTH	                月
        QUARTER	                季度
        YEAR	                年
        SECOND_MICROSECOND	    秒.毫秒
        MINUTE_MICROSECOND	    分.秒.毫秒
        MINUTE_SECOND	        分.秒
        HOUR_MICROSECOND	    小时.分.秒.毫秒
        HOUR_SECOND	            小时.分.秒
        HOUR_MINUTE	            小时.分
        DAY_MICROSECOND	        小时.分.秒.毫秒
        DAY_SECOND	            小时.分.秒
        DAY_MINUTE	            小时.分钟
        DAY_HOUR	            小时
        YEAR_MONTH	            年.月
        忽略说明中的点号 (.)

        当 unit 为复合类型时，它就是把这些元数据简单的拼接在一起

DATE_ADD() 函数
    MySQL DATE_ADD() 函数用于向日期添加指定的时间间隔
    DATE_ADD(date,INTERVAL expr type)
    参数	说明
    date	合法的日期表达式
    expr	希望添加的时间间隔
    type	时间间隔的类型
    type 参数可选值如下
    值	                    说明
    MICROSECOND	            毫秒数
    SECOND	                秒数
    MINUTE	                分钟数
    HOUR	                小时数
    DAY	                    天数
    WEEK	                周数
    MONTH	                月数
    QUARTER	                季度数
    YEAR	                年数
    SECOND_MICROSECOND	    秒.豪秒
    MINUTE_MICROSECOND	    分.豪秒
    MINUTE_SECOND	        分.秒
    HOUR_MICROSECOND	    小时.豪秒
    HOUR_SECOND	            小时.秒
    HOUR_MINUTE	            小时.分
    DAY_MICROSECOND	        天.豪秒
    DAY_SECOND	            天.秒
    DAY_MINUTE	            天.分钟
    DAY_HOUR	            天.小时
    YEAR_MONTH	            年.月

    例如:
        1. SELECT DATE_ADD(NOW(),INTERVAL 30 DAY );

        2. 给当前时间加上 1天又1小时
           SELECT NOW(), DATE_ADD(NOW(),INTERVAL 1.1 DAY_HOUR );

DATE_SUB() 函数
    MySQL DATE_SUB() 函数从日期减去指定的时间间隔
    DATE_SUB(date,INTERVAL expr type)

DATEDIFF() 函数
    MySQL DATEDIFF() 函数返回两个日期之间的天数，准确的说，是返回两个日期时间午夜 ( 00:00:00 ) 的数目
    DATEDIFF(enddate,startdate)
        如果 enddate 大于 startdate ，那么返回的是正数，反之，返回的是负数
    参数说明
    参数	            说明
    enddate	            开始时间，必须是合法的日期或日期/时间表达式
    startdate	        结束时间，必须是合法的日期或日期/时间表达式

    例如:
        1. 假设我们要返回日期 2017-06-19 到 2017-05-18 之间的天数，
           SELECT DATEDIFF('2017-06-19','2017-05-18') AS days;

           如果我们把两个时间对调一下，那么返回的结果就是负数
           SELECT DATEDIFF('2017-05-18','2017-06-19') AS days;

year()
month()
day()
hour()
minute()
second()
microsecond()
date()
time()
week()  # 一年第几周
quarter()  # 季度
monthname()  # 返回当前月的英文名
dayname()
dayofyear()
dayofweek()  返回日期星期几的索引 (1 = Sunday, 2 = Monday, ., 7 = Saturday)；这些索引值对应于ODBC标准。
dayofmonth()


# 日期时间格式化：(重点)
date_format(timestr, 日期时间占位符)
    参数	说明
    date	合法的日期
    format	日期/时间的输出格式

    format 参数可使用的元字符有
    元字符	                说明
    %Y	                    年，4 位
    %y	                    年，2 位

    %m	                    月，数值 ( 00-12 )
    %c	                    月，数值 ( 0 - 12)
    %d	                    月的天，数值 ( 00-31 )
    %e	                    月的天，数值 ( 0-31 )
    %D	                    带有英文后缀的月中的天

    %H	                    小时 ( 00-23 )
    %h	                    小时 ( 01-12 )
    %I	                    小时 ( 01-12 )
    %k	                    小时 ( 0-23 )
    %l	                    小时 ( 1-12 )
    %i	                    分钟，数值 ( 00-59 )
    %S	                    秒 ( 00-59 )
    %s	                    秒 ( 00-59 )
    %f	                    微秒
    %p	                    AM 或 PM
    %r	                    时间，12-小时 ( hh:mm:ss AM 或 PM )
    %T	                    时间, 24-小时 ( hh:mm:ss )

    %j	                    年的天 ( 001-366 )
    %w	                    周的天 ( 0=星期日, 6=星期六 )

    %a	                    缩写星期名
    %W	                    全称星期名
    %b	                    缩写月名
    %M	                    全称月名

    %U	                    周 ( 00-53 )星期日是一周的第一天
    %u	                    周 ( 00-53 )星期一是一周的第一天
    %V	                    周 ( 01-53 )星期日是一周的第一天，与 %X 使用
    %v	                    周 ( 01-53 )星期一是一周的第一天，与 %x 使用

    %X	                    年，其中的星期日是周的第一天，4 位，与 %V 使用
    %x	                    年，其中的星期一是周的第一天，4 位，与 %v 使用

    例子：博客园中的博客多少年多少月发布了几篇博客
            select data_format(sub_time, '%Y-%m'), count(1) from blog group by data_format(sub, '%Y-%m');
            占位符一部分跟Python里面的一样
            https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format


更多信息：
https://dev.mysql.com/doc/refman/5.7/en/functions.html

执行函数

select upper('egon');

获取函数返回值

select UPPER('egon') into @res;
SELECT @res;