Mysql-常用函数及其用法总结
1、字符串函数
测试用例如下：
1.1 CONCAT()
将多个字符串连接成一个字符串。
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- 期望结果：'John Doe', 'Jane Smith', 'Michael Johnson'
1.2 SUBSTRING()
提取子字符串
SELECT SUBSTRING(column_name, start_pos, length) FROM table_name;
SELECT SUBSTRING(first_name, 1, 3) AS short_name FROM users;
-- 期望结果：'Joh', 'Jan', 'Mic'
1.3 UPPER()
将字符串转换为大写
SELECT UPPER(column_name) FROM table_name;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- 期望结果：'John Doe', 'Jane Smith', 'Michael Johnson'
1.4 LOWER()
将字符串转换为小写
SELECT LOWER(column_name) FROM table_name;
SELECT LOWER(last_name) AS lower_name FROM users;
-- 期望结果：'doe', 'smith', 'johnson'
1.5 LENGTH()
返回字符串的长度
SELECT LENGTH(column_name) FROM table_name;
SELECT LENGTH(first_name) AS name_length FROM users;
-- 期望结果：4, 4, 7
1.6 TRIM()
去除字符串两端的空格或指定字符
SELECT TRIM(' ' FROM column_name) FROM table_name;
SELECT TRIM(' ' FROM ' John Doe ') AS trimmed_name;
-- 期望结果：'John Doe'
1.7 REPLACE()
替换字符串中的字串
SELECT REPLACE(column_name, 'old_string', 'new_string') FROM table_name;
SELECT REPLACE(first_name, 'J', 'M') AS replaced_name FROM users;
-- 期望结果：'Monn', 'Mane', 'Michael'
2、数值函数
测试用例如下：
2.1 SUM()
计算数值列的总和。
SELECT SUM(column_name) FROM table_name;
SELECT SUM(quantity) AS total_quantity FROM sales;
-- 期望结果：23
2.2 AVG()
计算数值列的平均值
SELECT AVG(column_name) FROM table_name;
SELECT AVG(unit_price) AS average_price FROM sales;
-- 期望结果：16.083333
2.3 COUNT()
计算行数或者非NULL值的数量
SELECT COUNT(*) FROM table_name;
SELECT COUNT(*) AS total_records FROM sales;
-- 期望结果：3
2.4 MAX()
返回数值列的最大值
SELECT MAX(column_name) FROM table_name;
SELECT MAX(unit_price) AS max_price FROM sales;
-- 期望结果：20.00
2.5 MIN()
返回数值列的最小值
2.6 ROUND()
对小数四舍五入到指定位数
SELECT ROUND(column_name, decimals) FROM table_name;
SELECT ROUND(unit_price, 1) AS rounded_price FROM sales;
-- 期望结果：15.5, 20.0, 12.8
