【MySQL函数】掌握这些常用函数，让你的数据库操作如虎添翼！

瑶山

已于 2024-01-31 15:15:05 修改

阅读量1.6k

点赞数 24

分类专栏： Mysql 文章标签： mysql 数据库 database 函数

于 2024-01-09 19:18:54 首次发布

本文链接：https://blog.csdn.net/qq_44695727/article/details/122511432

版权

Mysql 专栏收录该内容

14 篇文章 2 订阅

订阅专栏

GROUP BY：根据指定的列对结果进行分组

HAVING：筛选分组后的结果

ORDER BY：按指定的列对结果进行排序

强制走索引

SELECT * FROM user u force index(idx_user_id_update_time) where u.id=100 order by u.update_time

字符串函数

通配符

%通配符，_占位符
and order_no like '%1_'
匹配倒数第二位是1的任意订单号

CONCAT：连接两个或多个字符串

select CONCAT("123-", 456, '-ABC');
...where name like concat('%', #{param.name,jdbcType=VARCHAR}, '%');

GROUP_CONCAT：查询结果转换为以逗号分隔的字符串

SELECT GROUP_CONCAT(name SEPARATOR ",") FROM user limit 10
SELECT GROUP_CONCAT(name) FROM user limit 10
SELECT GROUP_CONCAT(id, name) FROM user limit 10

LENGTH：返回字符串的长度

select LENGTH('123456');

LOWER：将字符串转换为小写

select LOWER('ABCoOpP');

UPPER：将字符串转换为大写

select UPPER('abcqwer');

TRIM：删除字符串开头和结尾的空格

select TRIM(' abcq wer ');

字符串转化为number

在MySQL中，可以使用CAST或CONVERT函数将字符串转换为数字。下面是使用这两个函数的示例：

使用CAST函数：

SELECT CAST('1234' AS SIGNED) AS number;

使用CONVERT函数：

SELECT CONVERT('1234', SIGNED) AS number;

ps 请确保字符串能够正确地转换为数字，否则可能会导致错误。

替换字符串

在MySQL中，可以使用REPLACE函数来替换字符串。REPLACE函数接受三个参数：原始字符串、要查找的字符串和要替换的字符串。

SELECT REPLACE('Hello, World!', 'World', 'Universe');

这将输出Hello, Universe!，它将原始字符串中的World替换为Universe。

还可以将REPLACE函数与列名一起使用，以在数据库表中替换字符串。例如，假设您有一个名为users的表，其中包含一个名为email的列，您可以使用以下语句将所有电子邮件地址中的"old.com"替换为"new.com"：

UPDATE users SET email = REPLACE(email, 'old.com', 'new.com');

这将更新users表中的所有行，将电子邮件地址中的"old.com"替换为"new.com"。

请注意，REPLACE函数是大小写敏感的。如果要执行不区分大小写的替换操作，可以使用REPLACE函数之前先使用LOWER或UPPER函数将字符串转换为小写或大写。

截取字符串

SUBSTRING：返回字符串的子串

select SUBSTRING('123456', 3);
-- 3456
select SUBSTRING('123456', 3, 2);
-- 34

SUBSTR

SELECT SUBSTR('Hello World', 2, 5);
--<ello > 

# 这里的SUBSTR函数中，第一个参数是待截取字符串，第二个参数是开始截取的位置，第三个参数是截取的长度。根据上面的语句，输出结果为“ello ”。

LEFT和RIGHT

LEFT(str,length);

str是要提取子字符串的字符串。
length是一个正整数，指定将从左边返回的字符数。

LEFT函数和RIGHT函数

SELECT LEFT('Hello World', 5);
SELECT RIGHT('Hello World', 5);

LEFT函数从字符串的左侧开始截取指定长度的字符；而RIGHT函数从字符串的右侧开始截取指定长度的字符。
因此第一个语句输出结果为“Hello”，第二个语句输出结果为“World”。

SUBSTRING_INDEX


SELECT SUBSTRING_INDEX('www.baidu.com', '.', 1);
SELECT SUBSTRING_INDEX('www.baidu.com', '.', 2);

这里的SUBSTRING_INDEX函数用于按指定分隔符“.”截取字符串，并且可以指定截取第几个分隔符之前或之后的内容。第一个语句输出结果为“www”，第二个语句输出结果为“www.baidu”。

数值函数

SUM：计算指定列的总和

SELECT SUM(trade_amount) FROM order;

AVG：计算指定列的平均值

SELECT AVG(trade_amount) FROM order;

MAX：查找指定列的最大值

SELECT MAX(trade_amount) FROM order;

MIN：查找指定列的最小值

SELECT MIN(trade_amount) FROM order;

COUNT：计算指定列的行数

SELECT COUNT(trade_amount) FROM order;

日期和时间函数

NOW：返回当前日期和时间

SELECT NOW();
-- 2023-09-18 21:55:12

DATE：返回日期部分

SELECT DATE(NOW());
-- 2023-09-18

TIME：返回时间部分

SELECT TIME(NOW());
-- 21:55:52

YEAR：返回年份部分

SELECT YEAR(NOW());
-- 2023

MONTH：返回月份部分

SELECT MONTH(NOW());
-- 9

DAY：返回天数部分

SELECT DAY(NOW());
-- 18

条件函数

IF：根据条件返回不同的值

IF(condition, true_value, false_value)

condition：要测试的条件表达式。
true_value：如果条件为真，则返回此值。
false_value：如果条件为假，则返回此值

SELECT name, score, IF(score >= 90, '优秀', IF(score >= 70, '良好', IF(score >= 50, '及格', '不及格'))) as grade FROM students;

UPDATE orders SET status = IF(total_price > 1000, '高价', '低价') WHERE id = 1;

CASE：在查询中执行条件逻辑

SELECT name, 
       CASE 
           WHEN age >= 18 THEN 'Adult'
           WHEN age >= 13 THEN 'Minor'
           ELSE 'Unknown'
       END as age_group FROM users;

SELECT name, 
       CASE age 
           WHEN 18 THEN 'Adult'
           WHEN 13 THEN 'Minor'
           ELSE 'Unknown'
       END as age_group FROM users;

聚合函数：

GROUP BY：根据指定的列对结果进行分组

SELECT department, COUNT(*) as number_of_employees FROM table GROUP BY department

HAVING：筛选分组后的结果

SELECT department, COUNT(*) as number_of_employees FROM table
GROUP BY department HAVING number_of_employees > 10;

ORDER BY：按指定的列对结果进行排序

SELECT department, COUNT(*) as number_of_employees FROM table
GROUP BY department ORDER BY number_of_employees DESC;

LIMIT：限制返回的行数

SELECT id, name FROM user limit 1, 10

其他函数：

MD5：计算字符串的MD5哈希值

SELECT MD5('Hello World');

-- b10a8db164e0754105b7a99be72e3fe5

RAND：生成随机数

-- 生成一个 0 到 1 之间的随机浮点数：
SELECT RAND();
-- 0.07283980964947474

-- 生成一个 100 到 200 之间的随机整数：
SELECT FLOOR(100 + RAND() * 101);
-- 187

ROUND：对数值进行四舍五入

语法：ROUND(number, decimals)
参数：

number：要四舍五入的数字。
decimals：小数点后的位数。如果为正数，则表示小数点后的位数；如果为负数，则表示小数点前的位数。

SELECT version();  -- 结果为 5.7.40

-- 将数字 123.4567 四舍五入到小数点后两位：
SELECT ROUND(123.4567, 2);  -- 结果为 123.46

-- 将数字 12345 四舍五入到小数点前一位：
SELECT ROUND(12345, -1);  -- 结果为 12350

SELECT ROUND(12345, 0);  -- 结果为 12345

CEILING：向上取整

CEILING(number)
返回大于或等于给定数字的最小整数

-- 返回大于或等于 5.6 的最小整数：
SELECT CEILING(5.6);  -- 返回 6

-- 返回大于或等于 -3.1 的最小整数：
SELECT CEILING(-3.1);  -- 返回 -3

FLOOR：向下取整

-- 返回小于或等于 5.6 的最大整数：
SELECT FLOOR(5.6);  -- 返回 5

-- 返回小于或等于 -3.1 的最大整数：
SELECT FLOOR(-3.1);  -- 返回 -4

EXISTS()

MySQL的EXISTS函数用于测试子查询是否返回任何结果。如果子查询返回至少一行结果，EXISTS函数返回TRUE，否则返回FALSE。

语法：EXISTS (subquery)

其中，subquery是一个子查询，它可以是任何有效的SQL查询。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 是否存在至少一个订单
SELECT EXISTS (SELECT 1 FROM orders);
-- 下过单的用户
SELECT * FROM customers cus where EXISTS (SELECT 1 FROM orders o where o.customer_id = cus.customer_id);