MySQL第七章 内置函数

# 第七章 内置函数
# 1、数值函数（完整版可搜索"mysql numeric functions"）
select round(5.734, 1); #四舍五入至保留几位小数
select truncate(5.734, 2); #截断指定位数
select ceiling(5.7); #6——向上取整
select floor(5.7); #5——向下取整
select abs(-8.2); #绝对值
select rand(); #0-1之间的随机数

#2、字符串函数（完整版可搜索"mysql string functions"）
select upper('sky'); #返回大写
select lower('SKY'); #返回小写
select ltrim('   sky'); #left trim——移除字符串左侧的空白字符
select rtrim('sky       '); #right trim
select trim('   sky   '); #left+right
select left('kindergarten', 4); #kind——返回字符串左侧的几个字符
select right('kindergarten', 6); #garten——右侧
select substring('kindergarten', 3, 5); #nderg——返回从第3个开始的5个字符（第二个参数可选，若不写则表示从第3个开始到末尾）
select locate('n', 'kindergarten'); #3——返回第一个匹配位置（查找不区分大小写，若未找到则返回0）
select locate('garten', 'kindergarten'); #7
select replace('kindergarten', 'garten', 'garden'); #kindergarden——三个参数：字符串，想替换什么，想替换成什么
select concat('first', 'last'); #firstlast——连接多个字符串

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


#3、日期函数
#返回整数
select now(), curdate(), curtime(); #2024-04-14 15:55:53----2024-04-14----15:55:53
select year(now()), month(now()), day(now()), minute(now()), second(now()); #2024--4--14--58--6
#返回字符串
select dayname(now()), monthname(now()); #Sunday--April
select extract(day from now()); #14——想获取的单位，from，时间日期值（extract是标准sql语言的一部分！！！）

select *
from orders
where order_date >= concat(year(now()),'-',month(now()),'-',day(now())); #2024-4-14
select *
from orders
where year(order_date) = year(now()); #返回当年订单


#4、格式化日期和时间（完整版可搜索"mysql date format string"）
select date_format(now(), '%Y'); #2024
select date_format(now(), '%y'); #24
select date_format(now(), '%M'); #April
select date_format(now(), '%m'); #04
select date_format(now(), '%M %d %Y'); #April 14 2024
select date_format(now(), '%M %D %Y'); #April 14th 2024

select time_format(now(), '%H:%i %p'); #16:11 PM


#5、计算时间和日期
select date_add(now(), interval 1 day); #2024-04-16 10:13:54——返回了明天的同一时间
select date_add(now(), interval 1 year); #2025-04-15 10:13:54——返回了明年的同一时间
select datediff('2024-04-05 10:13:54','2024-04-16 12:13:54'); #-11
select time_to_sec('00:5:54'); #354——返回从零点计算的秒数
select time_to_sec('09:00') - time_to_sec('09:02'); #-120


#6、if null和coalesce function
select order_id,
       ifnull(shipper_id, 'Not assigned') as shipper #用其他内容替换空值
from orders;

select order_id,
       coalesce(shipper_id, comments, 'Not assigned') as shipper #如果shipper_id是null，就返回comments的值，如果comments也是null就显示‘Not assigned’——返回参数中第一个非空值
from orders;

select concat(customers.first_name,' ', customers.last_name) as customer,
       ifnull(phone, 'unknown') as phone
from customers;


#7、if function
select product_id,
       name,
#        也可以用子查询
#        if((select count(o.order_id)  group by product_id, name) > 1, 'Many times', 'Once') as frequency,
       count(*) as orders,
       if(count(*) > 1, 'Many times', 'Once') as frequency
from orders o
join order_items using (order_id)
join products using (product_id)
group by product_id, name; #不能加括号！！！group by (product_id, name)，会报错Operand should contain 1 column(s)


#8、case运算符---要用end关键字关闭case语句块
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 category;