MySQL Functions 常用函数汇总

Aggregate Functions (聚合函数)

numeric functions

• round(5.73,1)
  -- 保留一位小数，做四舍五入结果5.7
  truncate(5.6789,2)
  -- 截断至小数点后两位 结果 5.67
  ceiling(5.7)
  -- 天花板，高度，上限   返回大于或等于这个数字的最小整数  返回 6
  floor(5.2)
  -- 与上同理  结果 5
  abs(-5.7);
  -- 结果位5.7，转为正数
  rand()
  -- 生成随机的小数
  
  

• 学习其他numeric函数可以搜索，进官网看

• mysql numeric functions
  

string functions

• length("symtax")
  -- 长度
  upper("symtax")
  -- 大写
  lower("string") 
  -- 小写
  
  ltrim("    string")
  -- left trim 忽略左侧多余空格
  -- trim 切除
  -- 输出string
  rtrim() 
  -- 同理
  left("string",3)
  -- 返回左侧三个字符
  -- right 同理
  
  substring('kindergarten',3,5)
  -- 字符截取函数
  -- 得到字符串中任何位置的字符
  -- 从左边第三个开始，获取5个字符
  -- 超出范围就只显示现有字符   
  
  
  locate("e","kindergarten")
  -- locate 位于，查找
  -- 查找e ，会返回e的第一个位置 是5
  -- 大小写都是一样，找不到返回0
  -- 可以查找字符串
  -- select locate("garten","kindergarten") 返回7
  
  replace("kindergarten","garten","garden")
  -- 替换，garten 替换成 garden
  -- 返回 kindergarden
  
  concat("firstname","lastname")
  -- 合并多个数组，合并多个字符串
  -- 返回firstnamelastname
   
  

• 学习其他string functions 搜索

• mysql string functions
  

date functions

• now()
  -- 现在的日期时间
  
  year(now())
  -- 现在的年份
  month(now())
  monthname(now())
  day(now())
  hour(now())
  minute(now())
  second(now())
  -- 单个year(),无法执行报告语法错误
  
  -- 获取date数据的年数据与今年数据比对，判断是否是今年
  where year(order_date)  = year(now())
  
  
  extract(year from now())
  extract(year from order_date)
  -- extract 截取
  
  
  curdate()
  -- current date 现在的日期
  curtime()
  -- current time 现在的时间
  
  formatting dates and times
  -- 格式化日期和时间
  
  

formatting dates and times

• 格式化日期和时间

• 

• 根据格式字符串格式化日期值，这个直接从官方文档记下来的，如果标识符不常用平时也记不住，所以要学习看官方文档

• mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
          -> 'Sunday October 2009'
  mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
          -> '22:23:00'
  mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
      ->                 '%D %y %a %d %m %b %j');
          -> '4th 00 Thu 04 10 Oct 277'
  mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
      ->                 '%H %k %I %r %T %S %w');
          -> '22 22 10 10:23:00 PM 22:23:00 00 6'
  mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
          -> '1998 52'
  mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
          -> '00'
  

calculating dates and times

• 计算机日期和时间

• mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
          -> '2018-05-02'
          
          -- 在基础上增加1天
          -- interval 间隔，间距
  mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
          -> '2017-05-01'
          -- 减少
          
  mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
          -> 1
  mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
          -> -31
         -- 相差  
         
  select time_to_sec("09:00")
  ->32400
  -- 从零点到输入时间的秒数，可以用于计算两个时间相差多少秒
         
  mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
      ->                 INTERVAL 1 SECOND);
          -> '2021-01-01 00:00:00'
  mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
      ->                 INTERVAL 1 DAY);
          -> '2019-01-01 23:59:59'
  mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
      ->                 INTERVAL '1:1' MINUTE_SECOND);
          -> '2101-01-01 00:01:00'
  mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
      ->                 INTERVAL '1 1:1:1' DAY_SECOND);
          -> '2024-12-30 22:58:59'
  mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
      ->                 INTERVAL '-1 10' DAY_HOUR);
          -> '1899-12-30 14:00:00'
  mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
          -> '1997-12-02'
  mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
      ->            INTERVAL '1.999999' SECOND_MICROSECOND);
          -> '1993-01-01 00:00:01.000001'
  

Other functions

• Ifnull(columnName,"…");

• ifnull(columnName,"not assigned")
  -- 如果是null值，就显示not assigned，但是不改变原来的null
  
  use sql_store;
  SELECT 
      order_id, IFNULL(shipper_id, 'not assigned')
  FROM
      orders
  
  
  

• coalesce（value，value。。。）

• use sql_store;
  SELECT 
      order_id, 
      coalesce(shipper_id,comments,order_date,"not assigned")
  FROM
      orders
  

• 

• 如果coalesce里面可以填一堆候选值，如，shipper_id为null，就会选comments的数据，comments为null又会继续往下选，这一个非空值返回