SQL学习-unit7(内置函数）

1-数值函数|Numeric Functions

round(a,b):四舍五入数字,b为四舍五入精度，默认不保留小数；

select round(5.73) -- 6 select round(5.7345,2) --5.73

truncate():截断数字；

select truncate(5.7365,2) -- 5.73

ceiling():返回大于或等于这个数字的最小整数；

select ceiling(5.2) -- 6

floor():返回小于或等于这个数字的最大整数；

select floor(5.2) -- 5

abs():计算绝对值;

select abs(-5.2) -- 5.2

rand():用来生成0-1区间的随机浮点数;...

select rand()

数值函数的完整榜单:mysql numeric functions

2-字符串函数|String Functions

length():字符串中的字符数；

select length('sky') -- 3

upper():将字符串转化成大写字母；lower():将字符串转化成小写字母；

select upper('sky'); select lower('SKY')

删除字符串中不需要的空格：

ltrim():left trim(左修整的缩写)；

rtrim():right trim(右修整的缩写);

trim():删除所有前导或者尾随空格；

select ltrim(' sky'); select ltrim('sky '); select ltrim(' sky ')

left():返回字符串左侧的几个字符；right():返回字符串右侧的几个字符；

select left('Kindergarten',4); -- 'Kind' select right('Kindergarten',6) -- 'garten'

substring():

字符截断函数；

可以得到一个字符串中任何位置的字符，从1开始编号；

第三个参数时可选的,不指定默认返回从起始位置到字符串最后的所有字符；

select substring('Kindergarten',3,5) -- 'nderg'； select substring('Kindergarten',3) -- 'ndergarten'

locate():返回一个字符的位置或者一串字符第一次出现的位置;查找不分大小写；

select locate('n','Kindergarten'); -- 3 select locate('N','Kindergarten'); -- 3 select locate('q','Kindergarten'); -- 0 select locate('garten','Kindergarten') -- 7

replace():可以替换一个字符或者一串字符；

select replace('Kindergarten','garten','garden') -- 'Kindergarden'

concat():串联两个字符串；

select concat('first','last'); -- 'firstlast'

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

所有字符串函数:mysql string functions

3-MySQL中的日期函数|Date Functions

now():调用当前的日期和时间；

select now() -- '2026-04-23 10:14:22'

curdate():当前日期的缩写(current date),只返回当前日期；

curtime():当前时间的缩写(current time),只返回当前时间;

select curdate(),curtime() -- '2026-04-23', '10:17:39'

year(),month(),day(),hour(),minute(),second():获取当前年份,月份,天数,时刻,分,秒;

都返回整数值;

select year(now())； -- '2026' select month(now())； -- '4' select day(now()); -- '23' select hour(now()); -- '10' select minute(now()); -- '25' select second(now()) -- '24'

dayname()，Monthname():获取字符串格式的星期数,月份;

select dayname(now()); -- 'Thursday' select monthname(now()) -- 'April'

extract():调用这个函数,输入想获取的单位,from关键字,日期时间值;

select extract(year from now()); -- '2026' select extract(day from now()) -- '23'

• 练习：

select * from orders where year(order_date) = year(now())

4-格式化日期和时间|Formatting Dates and Times

date_format():日期格式函数,要用到两个参数,一个是日期值,一个是格式化字符串;

select date_format(now(),'%y'); -- '26' select date_format(now(),'%Y'); -- '2026' select date_format(now(),'%m %Y'); -- '04 2026' select date_format(now(),'%M %Y'); -- 'April 2026' select date_format(now(),'%M %d %Y') -- 'April 23 2026'

所有格式说明符(%a,%b,...):mysql date format string

time_format():时间格式函数；

select time_format(now(),'%H:%i %p') -- '11:01 AM'

5-计算日期和时间|Calculating Dates and Times

date_add():给日期时间值添加日期成分；

select date_add(now(),interval 1 day); -- '2026-04-24 11:11:07' select date_add(now(),interval 1 year); -- '2027-04-23 11:11:38' select date_add(now(),interval -1 year) -- '2025-04-23 11:12:07'

date_sub():在一个日期时间值上减去日期部分；

select date_sub(now(),interval 1 year) -- '2025-04-23 11:15:01'

datediff():计算两个日期的间隔;

select datediff('2019-01-05','2019-01-01'); -- '4' select datediff('2019-01-05 09:00','2019-01-01 17:00');-- '4' select datediff('2019-01-01 17:00','2019-01-05 09:00') -- '-4'

time_to_sec():计算时间间隔,会返回从零点计算的秒数；

select time_to_sec('09:00'); -- '32400' select time_to_sec('09:00') - time_to_sec('09:02') -- '-120'

6-IFNULL和COALESCE函数|The Ifnull and Coalesce Functions

ifnull():可以用其他内容替换空值；

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

将shipper_id列的空值改为Not assigned

coalesce():我们提供一堆值，返回这堆值中的第一个非空值

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

coalesce函数中，我们首先想返回shipper_id列的值，如果这个值是空的，就返回comments列的值,如果comments列也是空的,那就返回Not assigned;

• 小练习:

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

7-IF函数|The IF Function

有时候我们需要测试条件，并根据条件的成立与否，返回不同值；

if(expression,first,second):如果输入的表达式（expression）被判定为真，这个函数会返回第一个值（first)，否则就会返回第二个值（second）；

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

用union也可以做,if()更简单:

select order_id, order_date, 'Active' as category from orders where year(order_date) = year(now()) union select order_id, order_date, 'Acchived' as category from orders where not year(order_date) = year(now())

• 练习:

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,name

8-CASE运算符|The CASE Operator

如果我们有好几个表达式需要测试怎么办？

在有多个测试表达式且想要针对每个测试表达式返回不同值的时候,可以使用case运算符;

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

• 小练习（之前用union完成）：

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