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

2024-01-09 20:29:12

强制走索引

字符串函数

通配符

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}, '%');

?

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);

其他

提升数据库性能的关键所在！Mysql执行计划解析-CSDN博客

Mysql 触发器-两个数据库的表数据同步-步骤与问题解决_两个mysql数据库实现其中两张表增量同步脚本如何编写-CSDN博客

MySQL语法、UNION合并查询结果集一条数据拆分成多条和查询结果中增加一个自定义字段_union语句拆分多个查询汇总-CSDN博客

数据库优化要点，让你的数据运行如风！

文章来源:https://blog.csdn.net/qq_44695727/article/details/122511432
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！