SQL - 内置函数

• 处理数值数据的函数
  • round(3.555,2) //四舍五入，第二个参数为精度值，为3.56
  • truncate(3.999,2) //用来截断数字，第二个参数为保留位数，为3.99
  • ceiling(7.1) //上限函数，返回不小于数字的最小整数，为8
  • foolr(7.9) //下限函数，返回不大于数字的最大整数，为7
  • abs(-5.2) //绝对值，为5.2
  • rand() //用来生成 0~1区间的浮点数
• 处理字符串值的函数
  • length('sky') //返回字符串长度,为3
  • upper('sky') //转化成大写，为'SKY'
  • lower('SKY') //转化成小写，为'sky'
  • trim(' sky ') //用来移除两边的空格
  • ltrim(' sky') //用来移除左边的空格
  • rtrim('sky ') //用来移除右边的空格
  • left('abcdefg',4) //返回字符串左边的几个字符，为'abcd'
  • rigth('abcdefg',4) //返回字符串右边的几个字符，为'defg'
  • substring('abcdefg',2,3) //返回字符串，第二个参数begin，第三个参数len，为'bcd'
  • locate('cd','abcdefg') //返回第一个字符或者一串字符匹配位置，为3
  • replace('abcdefg','efg','eee') //替换，为'abcdeee'
  • concat('abc',' ','xyz') //串联两个字符串，为'abc xyz'
• 处理日期和时间的函数
  • now() //调用当前的日期和时间
  • curdate() //只返回当前的日期
  • curtime() //只返回当前的时间
  • year('2024-01-01') //提取年份，为2024，同理month，day
  • hour('21:23:19') //提取小时，为21，同理minute，second
  • dayname('2024-08-17') //获得星期数，为字符串 'Saturday'
  • monthname('2024-08-17') //获取月份，为字符串 'August'
  • extract(day from now()) //用处之一，获取天数等

  • select *
    from orders
    where extract(year from order_date)='2018'
     
    select order_id,
    	ifnull(shipper_id,'not found')
    from orders

• 格式化日期和时间的函数
  • date_format('2019-01-01','%m %d %y') //日期格式函数，第一个参数日期值，第二个参数格式字符串
  • %y：两位数的年份；%Y：四位数的年份；%m：两位数的月份；%M：月份名称；%d：正常数；%D：12st
  • time_format('21:23:19','%H:%i:%p') //时间格式函数
• 计算日期和时间的函数
  • date_add('2024-08-17',interval 1 day) //为'2024-08-18'，过去的时间传负值，如-1
  • date_add('2024-08-17',interval 1 year) //为'2025-08-17'，同理可得month
  • date_sub('2024-08-17',interval 1 year) //过去的日期
  • datediff('2024-08-17','2023-08-17') //两个日期进行计算，返回结果为366
  • time_to_sec('09:00') //返回从00:00开始到该时间的秒数
• 其他函数
  • ifnull(列名，字符串) //该列名的值为空，返回该字符串，代替 null
  • coalesce(列名，列名，字符串)
    • //如果第一个列名为null，就返回第二个列名的值，如果第二个列名的值也是null，返回字符串，可以增加的第3个，等等

    • select concat(first_name,' ',last_name) as customer,
      	coalesce(phone,'Unknown') as phone
      from customers

  • if (expression，first，second)
    • 有时候需要测试条件，并根据条件的成立与否，返回不同值，调用 if 函数，输入一个测试用的表达式，如果表达式判定为true，函数就会返回第一个值，否则返回第二个值

    • select order_id,
      	order_date,
          if (year(order_date)='2019','now','past') as status
      from orders

  • case 运算符
    • 要有好几个表达式判断怎么办？使用case运算符，case when + 表达式 then 'something' …… end

    • select order_id,
      	case
      		when year(order_date)='2019' then 'now'
      		when year(order_date)='2018' then 'last year'
      		when year(order_date)<'2018' then 'pass'
      		else 'future'
      	end as statue
      from orders;
       
       
      select concat(first_name,' ',last_name) as customer,
      	points,
          case
      		when points>=3000 then 'Gold'
              when points>=2000 then 'Silver'
              else 'Bronze'
      	end as category
      from customers
      order by points desc