【MySQL】常用内置函数：数值函数 / 字符串函数 / 日期函数 / 其他函数

数值函数

• 可查看所有的数值函数

round()：四舍五入

• 参数1

select round(5.37)

运行结果

• 参数2，可指定四舍五入的精度

select round(5.37,1)

运行结果

ceiling()：上限函数

• 返回大于或等于这个数字的最小整数

select ceiling(5.1)

运行结果

floor()：地板函数

• 返回小于或等于这个数字的最大整数

select floor(5.8);

运行结果

abs()：计算绝对值

select abs(-3.5);

运行结果

rand()：生成0-1的随机浮点数

select rand()

运行结果

字符串函数

• 可查看所有的字符串函数

length()：获取字符串中的字符数

select length('xuwuuuu')

运行结果

upper() / lower()：将字符串转化成大小写

select upper('xuwuuuu');

运行结果

select lower('XUWUUUU')

运行结果

trim/ltrim/rtrim：删除字符串中不需要的空格

• ltrim()：移除字符串左侧的空白字符或其他预定义字符

select ltrim('   xuwuuu')

• rtirm()：移除字符串右侧的空白字符或其他预定义字符

select rtrim('xuwuuu   ');

• trim()：删除所有前导或尾随空格，但不能删除字符串中间的空格

select trim('  xuwuuu  ')

left /right /substr：返回字符串相应位置字符

• left()：返回字符串左侧的几个字符

select left('xuwuuuuu',3)

• right()：返回字符串右侧的几个字符
• substr()：字符截取函数，可以得到任何位置的字符；
  • 第二个参数是起始位置，第三个参数是长度；
  • 也可以不写第三个参数，这样就返回从起始位置到字符串最后的所有字符。

select substr('xuwuuuuu',3,2);

locate：返回第一个字符或一串字符匹配位置

• 这个函数不区分大小写
• 返回指定的字符在字符串中第一次出现的位置。

select locate('u', 'xuwuuuu')

• 如果指定的字符在字符串中没有，会返回0

select locate('y', 'xuwuuuu')

• 还可以搜索一串字符串
• 同样，要搜索的字符串不存在时，也会返回0

select locate('uuuu', 'xuwuuuu')

replace：替换字符或字符串

• 第一个参数：完整的字符串；第二个参数：要被替换掉的字符或字符串；第三个参数：新的字符或字符串

select replace('xuwuuuuu', 'xu', 'st')

• 一个字符替换一个字符串也是可以的，一个字符串替换一个字符也可以

select replace('xuwuuuuu', 'xu', 's')

select replace('xuwuuuuu', 'x', 'st')

concat：串联两个字符串

select concat('xu', 'wuuuu')

• 练习：把表中的名和姓串联起来

use sql_store;
select concat(first_name, ' ', last_name) as full_name
from customers

日期函数

now() / curdate() / curtime()：返回当前的日期和时间 / 返回当前日期 / 返回当前时间

select now(), curdate(), curtime();

year(now()) / month() / day() / hour() / minute() / second() 提取特定日期或时间

• 返回整数值

select year(now()), month(now()), day(now()),
       minute(now()), second(now())

dayname(now()) / monthname()：返回字符串类型的星期和月份

• 返回字符串

select dayname(now()), monthname(now())

extract(day from now())：返回指定的时间或日期

• 要定制单位，如年、月、日期、秒等

select extract(day from now()),
       extract(year from now()),
       extract(month from now())

练习1

• 筛选当前年份的订单。把当前时间的年份提取出来，再把订单日期的年份提出来，筛选两个年份相等的数据。

use sql_store;
select *
from orders
where year(order_date) = year(now())

date_format()：日期格式函数

• 格式说明符查询连接
  

• 两个参数。

• 一个是日期值

• 一个是格式字符串

  • %y：两位数的年份
  • %Y：四位数的年份
  • %m：两位数的月份
  • %M：字符串的月份
  • %d：获取当前日期

select date_format(now(), '%M %d %Y')

time_format()：时间格式函数

• 两个参数；一个是时间值，一个是格式字符串。

select time_format(curtime(), '%H:%i %p')

date_add()：给日期时间值增加日期，写负值也能减

• 想在当前日期时间上增加一天
• 第二个参数
  • interval 1 day；
  • interval 3 year；
  • interval -1 year；获取过去的时间

select date_add(now(), interval 1 day),
       date_add(now(), interval -3 year)

返回了明天的同一时间（写码当前是2023-11-29）,返回3年后的同一时间

date_sub()：给日期时间值减去日期，写负值也能加

select date_sub(now(), interval 4 day),
       date_sub(now(), interval -3 year)

datediff()：返回两个日期的天数间隔

• 只返回天数的间隔，不考虑时间的间隔
• 两个时间的顺序调换，会得到正值或负值

select datediff('2023-11-29 22：02', '2022-11-29 21：02'),
       datediff('2022-11-29 22：02', '2023-11-29 21：02')

time_to_sec()：返回从零点计算的秒数

• 返回从零点计算的秒数

select time_to_sec('00:30'),
       time_to_sec(curtime())

• 返回两个时间间隔的秒数

select time_to_sec('22:10') - time_to_sec('22:00')

其他函数

ifnull()

• 有些订单的发货id是空值。如果想让用户看到的是“未分配”而不是空值，可以调用ifnull()函数，把发货id替换成别的。
  

use sql_store;
select order_id,
       ifnull(shipper_id, 'Not assigned') as shipper
from orders

coalesce()

• 如果参数1是null，就返回参数2；参数2为空，就返回参数3
• 写一堆参数，coalesce函数会返回这堆参数中第一个非空值。

use sql_store;
select order_id,
       coalesce(shipper_id, comments, 'Not assigned') as shipper
from orders

练习1

• 返回客户的姓名和电话，没有电话的显示Unknown
• concat（）函数串联字符串！

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

if()：单一表达式

• if(expression，first，second)：如果表达式为真，则返回first，否则返回second
• 把订单分类，如果订单是今年的，就放在“活跃类别”；否则就放入“归档类别”
  • 可以用union，筛选活跃类别的，再筛选归档类别的，用union连接起来
  • 可以用if函数，达到同样效果

select order_id,
       order_date,
       if(year(order_date) = '2019', 'Active', 'Archived') as category
from orders

练习2

• count（*）按照product_id进行计数，就能得到每个id对应有多少数据。

select product_id,
       name,
       count(*) as orders,
       if(count(*) > 1, 'Many times', 'Once') as frequency
from products
join order_items using (product_id)
group by product_id

case运算符：多个表达式

• 在有多个表达式且想针对每个表达式返回不同值时，适用case运算符
• case
  when …… then
  when …… then
  when …… then
  else
  end as ……

select order_id,
       order_date,
       case
           when year(order_date) = '2019' then 'Active'
           when year(order_date) = '2018' then 'Last Year'
           when year(order_date) < '2018' then 'Archived'
           else 'Future'
       end as category
from orders;

练习3

• 按照积分给客户分为3类。

select concat(first_name, ' ', last_name) as customer,
       points,
       case
           when points > '3000' then 'Gold'
           when points between '2000' and '3000' then 'Sliver'
           when points < '2000' then 'Bronze'
       end as category
from customers
order by points desc

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;