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

2024-01-09 20:29:12

目录

强制走索引

字符串函数

通配符

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

LENGTH:返回字符串的长度

LOWER:将字符串转换为小写

UPPER:将字符串转换为大写

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

字符串转化为number

替换字符串

截取字符串

SUBSTRING:返回字符串的子串

SUBSTR

LEFT和RIGHT

SUBSTRING_INDEX

数值函数

SUM:计算指定列的总和

AVG:计算指定列的平均值

MAX:查找指定列的最大值

MIN:查找指定列的最小值

COUNT:计算指定列的行数

日期和时间函数

NOW:返回当前日期和时间

DATE:返回日期部分

TIME:返回时间部分

YEAR:返回年份部分

MONTH:返回月份部分

DAY:返回天数部分

条件函数

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

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

聚合函数:

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

HAVING:筛选分组后的结果

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

LIMIT:限制返回的行数

其他函数:

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

RAND:生成随机数

ROUND:对数值进行四舍五入

CEILING:向上取整

FLOOR:向下取整

EXISTS()


强制走索引

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中,可以使用CASTCONVERT函数将字符串转换为数字。下面是使用这两个函数的示例:

使用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函数之前先使用LOWERUPPER函数将字符串转换为小写或大写。

截取字符串

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
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。