【MySQL进阶之路丨第十六篇】一文带你精通MySQL函数

引言

在上一篇中我们介绍了MySQL数据的导入与导出；在开发中，对MySQL函数的运用是十分重要的。这一篇我们使用命令行方式来帮助读者掌握MySQL中函数的操作。

上一篇链接：【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出

MySQL 函数

MySQL提供了很多内置函数，用于处理和操作数据库中的数据。本文给出部分函数描述及其实例。

MySQL 字符串函数

函数	描述	实例
ASCII(s)	返回字符串 s 的第一个字符的 ASCII 码。	返回 CustomerName 字段第一个字母的 ASCII 码： SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 QIUSHUO 的字符数 SELECT CHAR_LENGTH("QIUSHUO") AS LengthOfString;
CHARACTER_LENGTH(s)	返回字符串 s 的字符数，等同于 CHAR_LENGTH(s)	返回字符串 QIUSHUO 的字符数 SELECT CHARACTER_LENGTH("QIUSHUO") AS LengthOfString;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串 SELECT CONCAT("SQL ", "QIUSHUO ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2…sn)	同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符	合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", "SQL", "QIUSHUO", "ABC", "E")AS ConcatenatedString;
FIELD(s,s1,s2…)	返回第一个字符串 s 在字符串列表(s1,s2…)中的位置	返回字符串 c 在列表值中的位置： SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置	返回字符串 c 在指定字符串中的位置： SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n)	函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。	格式化数字 “#,###.##” 形式： SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串	从字符串第一个位置开始的 6 个字符替换为 QIUSHU： SELECT INSERT("google.com", 1, 6, "QIUSHU"); -- 输出：QIUSHU.com

MySQL 数字函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值： SELECT ABS(-1) -- 返回1
ACOS(x)	求 x 的反余弦值（单位为弧度），x 为一个数值	SELECT ACOS(0.25);
ASIN(x)	求反正弦值（单位为弧度），x 为一个数值	SELECT ASIN(0.25);
ATAN(x)	求反正切值（单位为弧度），x 为一个数值	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值（单位为弧度）	SELECT ATAN2(-0.8, 2);
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中Price 字段的平均值： SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) -- 返回2
CEILING(x)	返回大于或等于 x 的最小整数	SELECT CEILING(1.5); -- 返回2
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	SELECT COT(6);

MySQL 日期函数

函数名	描述	实例
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期	SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25
ADDTIME(t,n)	n 是一个时间表达式，时间 t 加上时间表达式 n	加 5 秒： SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒) 添加 2 小时, 10 分钟, 5 秒: SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26
CURDATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE(); -> 2018-09-19
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE_FORMAT(d,f)	按表达式 f的要求显示日期 d	SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。	Orders 表中 OrderDate 字段减去 2 天： SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
DAYNAME(d)	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期	SELECT MAKEDATE(2017, 3); -> 2017-01-03
MAKETIME(hour, minute, second)	组合时间，参数分别为小时、分钟、秒	SELECT MAKETIME(11, 35, 4); -> 11:35:04

MySQL 高级函数

函数名	描述	实例
BIN(x)	返回 x 的二进制编码	15 的 2 进制编码: SELECT BIN(15); -- 1111
CAST(x AS type)	转换数据类型	字符串日期转换为日期： SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
CONNECTION_ID()	返回唯一的连接 ID	SELECT CONNECTION_ID(); -> 4292835
CONV(x,f1,f2)	返回 f1 进制数变成 f2 进制数	SELECT CONV(15, 10, 2); -> 1111
CURRENT_USER()	返回当前用户	SELECT CURRENT_USER(); -> guest@%
DATABASE()	返回当前数据库名	SELECT DATABASE(); -> runoob
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,'正确','错误') ->正确
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL); ->1
SYSTEM_USER()	返回当前用户	SELECT SYSTEM_USER(); -> guest@%
USER()	返回当前用户	SELECT USER(); -> guest@%
VERSION()	返回数据库的版本号	SELECT VERSION() -> 5.6.34

实操

接下来让我们通过一些语句更好地了解函数的使用。

假设我们有一个名为 “students” 的表，其中包含学生的成绩信息。如下所示：

student_id	name	score
1	Alice	85
2	Bob	92
3	Charlie	78
4	David	88

如果我们想要计算所有学生的平均分数，我们可以使用 AVG() 函数，如下所示：

SELECT AVG(score) AS average_score FROM students;

这会返回一个结果，即所有学生的平均分数：

average_score
85.75

在这个例子中，AVG() 函数计算了 “score” 列中所有值的平均值，并将结果命名为 “average_score”。

假设我们有一个名为 “products” 的表，其中包含产品的信息，以及它们的价格和折扣情况。如下所示：

product_id	product_name	price	discounted
1	Laptop	1200	1
2	Smartphone	800	0
3	Tablet	500	1
4	Headphones	100	0

现在，假设我们想要创建一个新的列，用于显示每个产品的实际售价，如果该产品享受折扣，则应该显示折扣后的价格，否则显示原价。我们可以使用 IF() 函数来实现这一点，如下所示：

SELECT 
    product_name, 
    price, 
    discounted,
    IF(discounted = 1, price * 0.9, price) AS final_price
FROM 
    products;

在这个例子中，IF() 函数根据 “discounted” 列的值来判断是否应用折扣。如果 “discounted” 的值为 1，即享受折扣，那么使用折扣后的价格 (price * 0.9)，否则使用原价。最终查询的结果会包括产品名称、原价、折扣情况以及最终价格：

product_name	price	discounted	final_price
Laptop	1200	1	1080
Smartphone	800	0	800
Tablet	500	1	450
Headphones	100	0	100

通过这个例子，我们可以看到 IF() 函数的灵活性，可以根据条件返回不同的值，非常适合用于数据处理和报表生成等场景。

最后拿CONNECTION_ID()函数举例：假设我们正在管理一个书店的数据库，其中有一张表books，包含了书籍的标题(title)、作者(author)和价格(price)等信息。

CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    author VARCHAR(255),
    price DECIMAL(7,2)
);

INSERT INTO books (id, title, author, price)
VALUES 
(1, 'To Kill a Mockingbird', 'Harper Lee', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'One Hundred Years of Solitude', 'Gabriel Garcia Marquez', 12.99),
(4, 'Pride and Prejudice', 'Jane Austen', 9.99),
(5, 'The Great Gatsby', 'F. Scott Fitzgerald', 11.99);

现在，我们想找出所有标题长度超过10个字符的书籍。

查询语句如下：

SELECT title, author, price 
FROM books 
WHERE CHARACTER_LENGTH(title) > 10;

总结

以上为 【MySQL进阶之路丨第十六篇】，带领读者掌握 MySQL函数，通过具体实操实现对MySQL数据库的深度理解。

MySQL系列将持续更新，欢迎订阅收藏。