MySQL常用函数大全（sql基础语法、示例）

星辰大海844

已于 2024-08-01 20:38:52 修改

阅读量762

点赞数 3

文章标签： sql mysql

于 2024-08-01 20:37:32 首次发布

本文链接：https://blog.csdn.net/weixin_56023591/article/details/140856755

版权

MySQL 基础知识

引言
MySQL 是一个流行的开源关系型数据库管理系统，广泛用于各种应用，从小型网站到大型企业应用。它以其高性能、可靠性和易用性而闻名。MySQL 的历史可以追溯到 1995 年，最初由瑞典的 MySQL AB 公司开发。2008 年被 Sun Microsystems 收购，随后在 2010 年被 Oracle 收购。尽管经历了多次所有权变更，MySQL 依然保持其开源特性，并且是许多开发者和企业的首选数据库之一。

一、数据查询与操作函数

SELECT

基本语法：

SELECT column1, column2, ...
FROM table_name;

使用场景：用于从数据库中检索数据。
示例：假设有一个名为 employees 的表，包含员工的姓名、职位和薪水。要检索所有员工的姓名和薪水，可以使用以下 SQL 语句：

SELECT name, salary
FROM employees;

创建表结构：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT

基本语法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

使用场景：用于向数据库表中插入新的数据行。
注意事项：
- 确保插入的数据类型与表定义的列类型相匹配。
- 如果插入的列不包括所有列，未指定的列将使用默认值或 NULL。
示例：要向 employees 表中插入一个新员工，可以使用以下 SQL 语句：

INSERT INTO employees (name, position, salary)
VALUES ('John Doe', 'Software Engineer', 75000.00);

UPDATE

基本语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

使用场景：用于修改数据库表中的数据。
注意事项：
- 必须指定 WHERE 子句，否则将更新表中的所有行。
- 使用 LIMIT 可以限制更新的行数。
示例：假设需要将 employees 表中某个员工的薪水增加 5%，可以使用以下 SQL 语句：

UPDATE employees
SET salary = salary * 1.05
WHERE id = 1;

DELETE

基本语法：

DELETE FROM table_name
WHERE condition;

使用场景：用于从数据库表中删除数据。
注意事项：
- 必须指定 WHERE 子句，否则将删除表中的所有行。
- 使用 LIMIT 可以限制删除的行数。
示例：假设需要从 employees 表中删除一个不再工作的员工，可以使用以下 SQL 语句：

DELETE FROM employees
WHERE id = 2;

二、聚合函数

COUNT：用于统计表中行的数量或非空值的数量

基本语法：

COUNT(column_name)

示例：假设有一个名为 orders 的表，包含订单的 ID 和日期。要统计订单的总数，可以使用以下 SQL 语句：

SELECT COUNT(*) FROM orders;

要统计非空的订单日期数量，可以使用：

SELECT COUNT(order_date) FROM orders;

SUM：计算数值字段的总和

基本语法：

SUM(column_name)

示例：假设 orders 表中还有一个 amount 字段，表示订单金额。要计算所有订单的总金额，可以使用：

SELECT SUM(amount) FROM orders;

AVG：计算数值字段的平均值

基本语法：

AVG(column_name)

示例：继续使用 orders 表，要计算订单的平均金额，可以使用：

SELECT AVG(amount) FROM orders;

MAX：查找数值字段的最大值

基本语法：

MAX(column_name)

示例：要找出 orders 表中的最大订单金额，可以使用：

SELECT MAX(amount) FROM orders;

MIN：查找数值字段的最小值

基本语法：

MIN(column_name)

示例：要找出 orders 表中的最小订单金额，可以使用：

SELECT MIN(amount) FROM orders;

GROUP_CONCAT：将多个行的字符串值连接成一个字符串

基本语法：

GROUP_CONCAT([DISTINCT] column_name [SEPARATOR 'separator_string'])

示例：假设 orders 表中有一个 product_name 字段，表示订单中的产品名称。要将每个订单中的产品名称连接成一个字符串，可以使用：

SELECT order_id, GROUP_CONCAT(product_name SEPARATOR ', ') AS products
FROM order_details
GROUP BY order_id;

**具体示例：**点击

三、字符串函数

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

基本语法：

CONCAT(string1, string2, ...)

示例：假设我们有两个字符串变量 name = 'John' 和 surname = 'Doe'，我们可以使用 CONCAT 函数将它们连接成一个全名：

SELECT CONCAT(name, ' ', surname) AS full_name FROM users;

结果：full_name 将显示为 'John Doe'。

SUBSTRING：从字符串中提取子字符串

基本语法：

SUBSTRING(string, start, length)

示例：如果我们有一个字符串 text = 'Hello World' 并想提取从第 7 个字符开始的 5 个字符：

SELECT SUBSTRING(text, 7, 5) AS extracted_text FROM (SELECT 'Hello World' AS text);

结果：extracted_text 将显示为 'World'。

LENGTH：返回字符串的长度

基本语法：

LENGTH(string)

示例：如果我们有一个字符串 text = 'Hello' 并想知道其长度：

SELECT LENGTH(text) AS text_length FROM (SELECT 'Hello' AS text);

结果：text_length 将显示为 5。

UPPER：将字符串转换为大写

基本语法：

UPPER(string)

示例：如果我们有一个字符串 text = 'hello' 并想将其转换为大写：

SELECT UPPER(text) AS uppercase_text FROM (SELECT 'hello' AS text);

结果：uppercase_text 将显示为 'HELLO'。

LOWER：将字符串转换为小写

基本语法：

LOWER(string)

示例：如果我们有一个字符串 text = 'HELLO' 并想将其转换为小写：

SELECT LOWER(text) AS lowercase_text FROM (SELECT 'HELLO' AS text);

结果：lowercase_text 将显示为 'hello'。

TRIM：删除字符串两端的空格

基本语法：

TRIM(string)

示例：如果我们有一个字符串 text = ' Hello ' 并想删除两端的空格：

SELECT TRIM(text) AS trimmed_text FROM (SELECT '  Hello   ' AS text);

结果：trimmed_text 将显示为 'Hello'。

REPLACE：替换字符串中的字符或子字符串

基本语法：

REPLACE(string, search, replace)

示例：如果我们有一个字符串 text = 'Hello World' 并想将其中的 'World' 替换为 'Earth'：

SELECT REPLACE(text, 'World', 'Earth') AS replaced_text FROM (SELECT 'Hello World' AS text);

结果：replaced_text 将显示为 'Hello Earth'。

INSTR：返回字符串中子字符串首次出现的索引

基本语法：

INSTR(string, substring)

示例：如果我们有一个字符串 text = 'Hello World' 并想找到 'World' 首次出现的索引：

SELECT INSTR(text, 'World') AS index FROM (SELECT 'Hello World' AS text);

结果：index 将显示为 7。

LEFT：从字符串左侧提取指定长度的子字符串

基本语法：

LEFT(string, length)

示例：如果我们有一个字符串 text = 'Hello World' 并想提取左侧的 5 个字符：

SELECT LEFT(text, 5) AS left_text FROM (SELECT 'Hello World' AS text);

结果：left_text 将显示为 'Hello'。

RIGHT：从字符串右侧提取指定长度的子字符串

基本语法：

RIGHT(string, length)

示例：如果我们有一个字符串 text = 'Hello World' 并想提取右侧的 5 个字符：

SELECT RIGHT(text, 5) AS right_text FROM (SELECT 'Hello World' AS text);

结果：right_text 将显示为 'World'。

LPAD：在字符串左侧填充字符

基本语法：

LPAD(string, length, pad)

示例：如果我们有一个字符串 text = 'Hello' 并想在左侧填充空格使其总长度为 10：

SELECT LPAD(text, 10, ' ') AS padded_text FROM (SELECT 'Hello' AS text);

结果：padded_text 将显示为 ' Hello'。

RPAD：在字符串右侧填充字符

基本语法：

RPAD(string, length, pad)

示例：如果我们有一个字符串 text = 'Hello' 并想在右侧填充空格使其总长度为 10：

sql
SELECT RPAD(text, 10, ' ') AS padded_text FROM (SELECT 'Hello' AS text);

结果：padded_text 将显示为 'Hello '。

REPEAT：重复字符串指定次数

基本语法：

REPEAT(string, count)

示例：如果我们有一个字符串 text = 'Hello' 并想重复 3 次：

SELECT REPEAT(text, 3) AS repeated_text FROM (SELECT 'Hello' AS text);

结果：repeated_text 将显示为 'HelloHelloHello'。

REVERSE：反转字符串

基本语法：

REVERSE(string)

示例：如果我们有一个字符串 text = 'Hello' 并想反转它：

SELECT REVERSE(text) AS reversed_text FROM (SELECT 'Hello' AS text);

结果：reversed_text 将显示为 'olleH'。

SPACE：返回指定长度的空格字符串

基本语法：

SPACE(length)

示例：如果我们想生成一个长度为 10 的空格字符串：

SELECT SPACE(10) AS space_text;

结果：space_text 将显示为 ' '。

STRCMP：比较两个字符串

基本语法：

STRCMP(string1, string2)

示例：如果我们想比较两个字符串 text1 = 'Hello' 和 text2 = 'hello'：

SELECT STRCMP(text1, text2) AS comparison_result FROM (SELECT 'Hello' AS text1, 'hello' AS text2);

结果：comparison_result 将显示为 0（如果字符串相等）。

CONCAT_WS：使用分隔符连接字符串

基本语法：

CONCAT_WS(separator, string1, string2, ...)

示例：假设我们有两个字符串变量 name = 'John' 和 surname = 'Doe'，我们可以使用 CONCAT_WS 函数将它们用逗号连接成一个全名：

SELECT CONCAT_WS(', ', name, surname) AS full_name FROM (SELECT 'John' AS name, 'Doe' AS surname);

结果：full_name 将显示为 'John, Doe'。

FORMAT：格式化数字为字符串

基本语法：

FORMAT(number, decimals)

示例：如果我们有一个数字 number = 1234.56 并想将其格式化为两位小数：

SELECT FORMAT(number, 2) AS formatted_number FROM (SELECT 1234.56 AS number);

结果：formatted_number 将显示为 ‘1234.5’

四、数学函数

ABS：返回一个数的绝对值

基本语法：

sql
ABS(number)

示例：计算 ABS(-10)：

sql
SELECT ABS(-10) AS absolute_value;

结果：absolute_value 将显示为 10。

CEIL：向上取整

基本语法：

CEIL(number)

示例：计算 CEIL(3.7)：

SELECT CEIL(3.7) AS ceiling_value;

结果：ceiling_value 将显示为 4。

FLOOR：向下取整

基本语法：

FLOOR(number)

示例：计算 FLOOR(3.7)：

SELECT FLOOR(3.7) AS floor_value;

结果：floor_value 将显示为 3。

ROUND：四舍五入到指定的小数位数

基本语法：

ROUND(number, decimals)

示例：计算 ROUND(3.7, 1)：

SELECT ROUND(3.7, 1) AS rounded_value;

结果：rounded_value 将显示为 3.7。

MOD：取模运算，返回除法的余数

基本语法：

MOD(number1, number2)

示例：计算 MOD(10, 3)：

SELECT MOD(10, 3) AS remainder;

结果：remainder 将显示为 1。

RAND：生成一个 0 到 1 之间的随机数

基本语法：

RAND()

示例：生成一个随机数：

SELECT RAND() AS random_value;

结果：random_value 将显示为一个 0 到 1 之间的随机数。

SIGN：返回一个数的符号

基本语法：

SIGN(number)

示例：计算 SIGN(-10)：

SELECT SIGN(-10) AS sign_value;

结果：sign_value 将显示为 -1。

TRUNCATE：截断数字到指定的小数位数

基本语法：

TRUNCATE(number, decimals)

示例：计算 TRUNCATE(3.7, 1)：

SELECT TRUNCATE(3.7, 1) AS truncated_value;

结果：truncated_value 将显示为 3.7。

POW：计算一个数的幂

基本语法：

POW(base, exponent)

示例：计算 POW(2, 3)：

SELECT POW(2, 3) AS power_value;

结果：power_value 将显示为 8。

SQRT：计算一个数的平方根

基本语法：

SQRT(number)

示例：计算 SQRT(16)：

SELECT SQRT(16) AS square_root;

结果：square_root 将显示为 4

EXP：计算自然对数的幂（e^x）

基本语法：

EXP(number)

示例：计算 EXP(1)：

SELECT EXP(1) AS exponential_value;

结果：exponential_value 将显示为 2.71828（自然对数 e 的值）。

LN：计算自然对数（以 e 为底）

基本语法：

LN(number)

示例：计算 LN(2.71828)：

SELECT LN(2.71828) AS natural_log;

结果：natural_log 将显示为 1。

LOG：计算以 10 为底的对数

基本语法：

LOG(number)

示例：计算 LOG(100)：

SELECT LOG(100) AS logarithmic_value;

结果：logarithmic_value 将显示为 2。

GREATEST：返回一组数中的最大值

基本语法：

GREATEST(number1, number2, ...)

示例：计算 GREATEST(10, 20, 5)：

SELECT GREATEST(10, 20, 5) AS greatest_value;

结果：greatest_value 将显示为 20。

LEAST：返回一组数中的最小值

基本语法：

LEAST(number1, number2, ...)

示例：计算 LEAST(10, 20, 5)：

SELECT LEAST(10, 20, 5) AS least_value;

结果：least_value 将显示为 5。

五、日期和时间函数

NOW：返回当前日期和时间

基本语法：

NOW()

示例：获取当前日期和时间：

SELECT NOW() AS current_datetime;

结果：current_datetime 将显示为当前的日期和时间，例如：'2024-07-28 12:34:56'。

CURDATE：返回当前日期

基本语法：

CURDATE()

示例：获取当前日期：

SELECT CURDATE() AS current_date;

结果：current_date 将显示为当前的日期，例如：'2024-07-28'。

CURTIME：返回当前时间

基本语法：

CURTIME()

示例：获取当前时间：

SELECT CURTIME() AS current_time;

结果：current_time 将显示为当前的时间，例如：'12:34:56'。

DATE：提取日期或日期时间值中的日期部分

基本语法：

DATE(date_time)

示例：从日期时间值中提取日期：

SELECT DATE('2024-07-27 14:30:00') AS date_part;

结果：date_part 将显示为：'2024-07-27'。

TIME：提取日期时间值中的时间部分

基本语法：

TIME(date_time)

示例：从日期时间值中提取时间：

SELECT TIME('2024-07-27 14:30:00') AS time_part;

结果：time_part 将显示为：'14:30:00'。

YEAR：提取日期或日期时间值中的年份

基本语法：

YEAR(date_time)

示例：提取年份：

SELECT YEAR('2024-07-27') AS year_part;

结果：year_part 将显示为：2024。

MONTH：提取日期或日期时间值中的月份

基本语法：

MONTH(date_time)

示例：提取月份：

SELECT MONTH('2024-07-27') AS month_part;

结果：month_part 将显示为：7。

DAY：提取日期或日期时间值中的日

基本语法：

DAY(date_time)

示例：提取日：

SELECT DAY('2024-07-27') AS day_part;

结果：day_part 将显示为：27。

HOUR：提取时间值中的小时

基本语法：

HOUR(time)

示例：提取小时：

SELECT HOUR('14:30:00') AS hour_part;

结果：hour_part 将显示为：14。

MINUTE：提取时间值中的分钟

基本语法：

MINUTE(time)

示例：提取分钟：

SELECT MINUTE('14:30:00') AS minute_part;

结果：minute_part 将显示为：30。

SECOND：提取时间值中的秒

基本语法：

SECOND(time)

示例：提取秒：

SELECT SECOND('14:30:10') AS second_part;

结果：second_part 将显示为：10。

DATE_ADD：给日期或日期时间值增加指定的时间间隔

基本语法：

DATE_ADD(date_time, INTERVAL value unit)

示例：给日期增加 10 天：

SELECT DATE_ADD('2024-07-27', INTERVAL 10 DAY) AS new_date;

结果：new_date 将显示为：'2024-08-06'。

DATE_SUB：从日期或日期时间值减去指定的时间间隔

基本语法：

DATE_SUB(date_time, INTERVAL value unit)

示例：从日期减去 5 天：

SELECT DATE_SUB('2024-07-27', INTERVAL 5 DAY) AS new_date;

结果：new_date 将显示为：'2024-07-22'。

DATEDIFF：计算两个日期之间的天数差

基本语法：

DATEDIFF(date_time1, date_time2)

示例：计算两个日期之间的天数差：

SELECT DATEDIFF('2024-08-27', '2024-07-27') AS days_difference;

结果：days_difference 将显示为：31。

TIMEDIFF：计算两个时间或日期时间值之间的时间差

基本语法：

TIMEDIFF(time1, time2)

示例：计算两个时间之间的时间差：

SELECT TIMEDIFF('14:30:00', '13:30:00') AS time_difference;

结果：time_difference 将显示为：'01:00:00'。

STR_TO_DATE：将字符串转换为日期或日期时间值

基本语法：

STR_TO_DATE(string, format)

示例：将字符串转换为日期：

SELECT STR_TO_DATE('2024-07-27', '%Y-%m-%d') AS date_value;

结果：date_value 将显示为日期：'2024-07-27'。

DATE_FORMAT：将日期或日期时间值格式化为字符串

基本语法：

DATE_FORMAT(date_time, format)

示例：将日期格式化为字符串：

SELECT DATE_FORMAT('2024-07-27', '%Y年%m月%d日') AS formatted_date;

结果：formatted_date 将显示为：'2024年07月27日'。

ADDDATE：给日期增加指定的天数

基本语法：

ADDDATE(date, days)

示例：给日期增加 5 天：

SELECT ADDDATE('2024-07-27', 5) AS new_date;

结果：new_date 将显示为：'2024-08-01'。

SUBDATE：从日期减去指定的天数

基本语法：

SUBDATE(date, days)

示例：从日期减去 5 天：

SELECT SUBDATE('2024-07-27', 5) AS new_date;

结果：new_date 将显示为：'2024-07-22'。

MAKEDATE：将年、月和日组合成日期

基本语法：

MAKEDATE(year, day)

示例：制作日期：

SELECT MAKEDATE(2024, 27) AS date_value;

结果：date_value 将显示为：'2024-01-27'。

MAKETIME：将小时、分钟和秒组合成时间

基本语法：

MAKETIME(hour, minute, second)

示例：制作时间：

SELECT MAKETIME(14, 30, 0) AS time_value;

结果：time_value 将显示为：'14:30:00'。

FROM_DAYS：将天数转换为日期

基本语法：

FROM_DAYS(days)

示例：将天数转换为日期：

SELECT FROM_DAYS(TO_DAYS('2024-07-27')) AS date_value;

结果：date_value 将显示为：'2024-07-27'。

TO_DAYS：将日期转换为天数

基本语法：

TO_DAYS(date)

示例：将日期转换为天数：

SELECT TO_DAYS('2024-07-27') AS day_number;

结果：day_number 将显示为从 0000-00-00 到 2024-07-27 的天数。

WEEK：返回日期是一年中的第几周

基本语法：

WEEK(date)

示例：获取日期是一年中的第几周：

SELECT WEEK('2024-07-27') AS week_number;

结果：week_number 将显示为：30。

WEEKDAY：返回日期是一周中的第几天（0 = 星期一，1 = 星期二，…）

基本语法：

WEEKDAY(date)

示例：获取日期是一周中的第几天：

SELECT WEEKDAY('2024-07-27') AS weekday_number;

结果：weekday_number 将显示为：6（星期六）。

DAYOFWEEK：返回日期是一周中的第几天（1 = 星期日，2 = 星期一，…）

基本语法：

DAYOFWEEK(date)

示例：获取日期是一周中的第几天：

SELECT DAYOFWEEK('2024-07-27') AS weekday_number;

结果：weekday_number 将显示为：7（星期六）。

DAYOFYEAR：返回日期是一年中的第几天

基本语法：

DAYOFYEAR(date)

示例：获取日期是一年中的第几天：

SELECT DAYOFYEAR('2024-07-27') AS day_of

结果：day_of_year 将显示为：209。

LAST_DAY：返回日期所在月份的最后一天

基本语法：

sql
LAST_DAY(date)

示例：获取日期所在月份的最后一天：

sql
SELECT LAST_DAY('2024-07-27') AS last_day_of_month;

结果：last_day_of_month 将显示为：'2024-07-31'。

EXTRACT：从日期或日期时间值中提取子字段

基本语法：

sql
EXTRACT(unit FROM date_time)

示例：提取年份：

sql
SELECT EXTRACT(YEAR FROM '2024-07-27') AS year_part;

结果：year_part 将显示为：2024。

UNIX_TIMESTAMP：将日期时间值转换为 Unix 时间戳

基本语法：

UNIX_TIMESTAMP(date_time)

示例：转换日期时间为 Unix 时间戳：

SELECT UNIX_TIMESTAMP('2024-07-27 14:30:00') AS unix_timestamp;

结果：unix_timestamp 将显示为对应的 Unix 时间戳。

FROM_UNIXTIME：将 Unix 时间戳转换为日期时间值

基本语法：

FROM_UNIXTIME(unix_timestamp)

示例：转换 Unix 时间戳为日期时间值：

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2024-07-27 14:30:00')) AS datetime_value;

结果：datetime_value 将显示为：'2024-07-27 14:30:00'。

六、条件判断函数

IF：如果条件为真，则返回第一个表达式的值，否则返回第二个表达式的值

基本语法：

IF(condition, true_value, false_value)

示例：检查年龄是否大于 18：

SELECT IF(age > 18, 'Adult', 'Minor') AS age_group FROM users WHERE user_id = 1;

结果：如果 age 大于 18，则 age_group 显示为 'Adult'，否则显示为 'Minor'。

CASE：类似于多条件的 IF，可以有多个条件分支

基本语法：

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

示例：根据年龄返回不同的组别：

SELECT
  CASE age
    WHEN 18 THEN 'Adult'
    WHEN 21 THEN 'Senior'
    ELSE 'Minor'
  END AS age_group
FROM users WHERE user_id = 1;

结果：根据 age 的值返回 'Adult'、'Senior' 或 'Minor'。

COALESCE：返回第一个非 NULL 表达式的值

基本语法：

COALESCE(value1, value2, ...)

示例：返回第一个非 NULL 的值：

SELECT COALESCE(NULL, 'Default Value', 'Another Value') AS non_null_value;

结果：non_null_value 将显示为：'Default Value'。

NULLIF：如果两个值相等，则返回 NULL，否则返回第一个值

基本语法：

NULLIF(value1, value2)

示例：如果两个值相等则返回 NULL：

SELECT NULLIF(100, 100) AS null_value;

结果：null_value 将显示为：NULL。

IFNULL：如果第一个表达式为 NULL，则返回第二个表达式的值，否则返回第一个表达式的值

基本语法：

IFNULL(value, alternative_value)

示例：如果 name 为 NULL，则返回 'Unknown'：

SELECT IFNULL(name, 'Unknown') AS name_or_default FROM users WHERE user_id = 1;

结果：如果 name 为 NULL，则 name_or_default 显示为 'Unknown'，否则显示 name 的值。

七、其他常用函数

COALESCE：返回第一个非 NULL 表达式的值

基本语法：

COALESCE(value1, value2, ...)

示例：

SELECT COALESCE(NULL, 'Default Value', 'Another Value') AS non_null_value;

结果：non_null_value 将显示为 'Default Value'。

NULLIF：如果两个值相等，则返回 NULL，否则返回第一个值

基本语法：

NULLIF(value1, value2)

示例：

SELECT NULLIF(100, 100) AS null_value;

结果：null_value 将显示为 NULL。

IFNULL：如果第一个表达式为 NULL，则返回第二个表达式的值，否则返回第一个表达式的值

基本语法：

IFNULL(value, alternative_value)

示例：

SELECT IFNULL(NULL, 'Unknown') AS default_value;

结果：default_value 将显示为 'Unknown'。

FORMAT：将数字格式化为字符串，保留指定的小数位数

基本语法：

FORMAT(number, decimals)

示例：

SELECT FORMAT(1234.5678, 2) AS formatted_number;

结果：formatted_number 将显示为 '1234.57'。

CAST：将一个值转换为指定的数据类型

基本语法：

CAST(expression AS data_type)

示例：

SELECT CAST('123' AS UNSIGNED) AS int_value;

结果：int_value 将显示为 123（转换为 UNSIGNED INT 类型）。

CONVERT：类似于 CAST，将一个值转换为指定的数据类型

基本语法：

CONVERT(expression, data_type)

示例：

SELECT CONVERT('2024-07-28', DATE) AS date_value;

结果：date_value 将显示为 '2024-07-28'（转换为 DATE 类型）。

八、系统信息函数

DATABASE()：返回当前数据库的名称

基本语法：

DATABASE()

示例：

SELECT DATABASE() AS current_db;

结果：current_db 将显示为当前数据库的名称。

USER()：返回连接到 MySQL 服务器的当前用户的用户名

基本语法：

USER()

示例：

SELECT USER() AS current_user;

结果：current_user 将显示为当前连接到服务器的用户的用户名。

VERSION()：返回 MySQL 服务器的版本号

基本语法：

VERSION()

示例：

SELECT VERSION() AS mysql_version;

结果：mysql_version 将显示为 MySQL 服务器的版本号，例如 '8.0.26'。

CONNECTION_ID()：返回当前连接的连接 ID

基本语法：

CONNECTION_ID()

示例：

SELECT CONNECTION_ID() AS connection_id;

结果：connection_id 将显示为当前连接的唯一标识符。

九、JSON 函数

JSON_EXTRACT：从 JSON 对象或数组中提取值

基本语法：

JSON_EXTRACT(json_string, path[, path...])

示例：从 JSON 对象中提取 name 键的值：

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') AS name;

结果：name 将显示为 'John'。

JSON_UNQUOTE：去除 JSON 字符串的引号

基本语法：

JSON_UNQUOTE(json_string)

示例：去除 JSON 字符串的引号：

SELECT JSON_UNQUOTE('"John"') AS unquoted_name;

结果：unquoted_name 将显示为 'John'。

JSON_ARRAY：创建一个 JSON 数组

基本语法：

JSON_ARRAY(value1, value2, ...)

示例：创建一个 JSON 数组：

SELECT JSON_ARRAY('John', 'Doe') AS json_array;

结果：json_array 将显示为 ["John", "Doe"]。

JSON_OBJECT：创建一个 JSON 对象

基本语法：

JSON_OBJECT(key1, value1[, key2, value2...])

示例：创建一个 JSON 对象：

SELECT JSON_OBJECT('name', 'John', 'age', 30) AS json_object;

结果：json_object 将显示为 {"name": "John", "age": 30}。

JSON_INSERT：向 JSON 对象或数组中插入值

基本语法：

JSON_INSERT(json_string, path, value)

示例：向 JSON 对象中插入一个新的键值对：

SELECT JSON_INSERT('{"name": "John"}', '$.age', 30) AS updated_json;

结果：updated_json 将显示为 {"name": "John", "age": 30}。

JSON_REPLACE：在 JSON 对象或数组中替换值

基本语法：

JSON_REPLACE(json_string, path, new_value)

示例：替换 JSON 对象中的值：

SELECT JSON_REPLACE('{"name": "John"}', '$.name', 'Jane') AS updated_json;

结果：updated_json 将显示为 {"name": "Jane"}。

JSON_SET：类似于 `JSON_REPLACE`，但可以处理更深的路径

基本语法：

JSON_SET(json_string, path, value)

示例：设置 JSON 对象中深层路径的值：

SELECT JSON_SET('{"person": {"name": "John"}}', '$.person.age', 30) AS updated_json;

结果：updated_json 将显示为 {"person": {"name": "John", "age": 30}}。

JSON_REMOVE：从 JSON 对象或数组中删除值

基本语法：

JSON_REMOVE(json_string, path)

示例：从 JSON 对象中删除键：

SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age') AS updated_json;

结果：updated_json 将显示为 {"name": "John"}。

JSON_CONTAINS：检查 JSON 文档中是否存在指定的路径或值

基本语法：

JSON_CONTAINS(json_string, path[, value])

示例：检查 JSON 对象中是否存在 age 键：

SELECT JSON_CONTAINS('{"name": "John", "age": 30}', '$.age') AS contains_age;

结果：contains_age 将显示为 1（如果存在）或 0（如果不存在）。

JSON_LENGTH：返回 JSON 数组的长度或 JSON 对象的键值对数量

基本语法：

JSON_LENGTH(json_string[, path])

示例：获取 JSON 数组的长度：

SELECT JSON_LENGTH('[1, 2, 3]') AS array_length;

结果：array_length 将显示为 3。

十、窗口函数

窗口函数是一种特殊类型的函数，用于在数据库查询中对数据集的子集（称为窗口）执行计算，而不会将行合并成单个输出行。以下是一些常用的窗口函数：

ROW_NUMBER：为每个窗口内的行分配一个唯一的连续整数

基本语法：

ROW_NUMBER() OVER(partition_by_clause order_by_clause)

示例：为每个部门内的员工按薪资排序，并分配行号：

SELECT employee_id, salary, 
       ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

RANK：为每个窗口内的行分配一个排名，相同值会有间隔

基本语法：

RANK() OVER(partition_by_clause order_by_clause)

示例：为每个部门内的员工按薪资排名：

SELECT employee_id, salary, 
       RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK：为每个窗口内的行分配一个排名，相同值不会有间隔

基本语法：

DENSE_RANK() OVER(partition_by_clause order_by_clause)

示例：为每个部门内的员工按薪资排名，不跳过排名：

SELECT employee_id, salary, 
       DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

NTILE：将窗口内的行分成指定数量的组，每组大约有相同数量的行

基本语法：

NTILE(number_of_buckets) OVER(partition_by_clause order_by_clause)

示例：将每个部门的员工按薪资分成四组：

SELECT employee_id, salary, 
       NTILE(4) OVER(PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

LAG：访问窗口中当前行的前一行值

基本语法：

LAG(expression [, offset] [, default]) OVER(partition_by_clause order_by_clause)

示例：获取每个员工的前一个员工的薪资：

SELECT employee_id, salary, 
       LAG(salary) OVER(PARTITION BY department_id ORDER BY employee_id) AS prev_salary
FROM employees;

LEAD：访问窗口中当前行的后一行值

基本语法：

LEAD(expression [, offset] [, default]) OVER(partition_by_clause order_by_clause)

示例：获取每个员工的下一个员工的薪资：

SELECT employee_id, salary, 
       LEAD(salary) OVER(PARTITION BY department_id ORDER BY employee_id) AS next_salary
FROM employees;

FIRST_VALUE：访问窗口中的第一行值

基本语法：

FIRST_VALUE(expression) OVER(partition_by_clause order_by_clause)

示例：获取每个部门的第一个员工的薪资：

SELECT employee_id, salary, 
       FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY employee_id) AS first_salary
FROM employees;

LAST_VALUE：访问窗口中的最后行值

基本语法：

LAST_VALUE(expression) OVER(partition_by_clause order_by_clause)

示例：获取每个部门的最后一个员工的薪资：

SELECT employee_id, salary, 
       LAST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary
FROM employees;

十一、条件查询函数

IN：检查某个值是否包含在指定的列表中

基本语法：

column IN (value1, value2, ...)

示例：

SELECT * FROM users WHERE user_id IN (1, 2, 3);

结果：返回 users 表中 user_id 为 1、2 或 3 的所有记录。

BETWEEN：检查某个值是否位于指定的范围内（包含边界）

基本语法：

column BETWEEN value1 AND value2

示例：

SELECT * FROM products WHERE price BETWEEN 10.00 AND 20.00;

结果：返回 products 表中 price 在 10.00 到 20.00 之间的所有记录。

LIKE：使用通配符（`%` 和 `_`）进行模式匹配

基本语法：

scolumn LIKE 'pattern'

示例：

SELECT * FROM users WHERE email LIKE '%@example.com';

结果：返回 users 表中 email 以 @example.com 结尾的所有记录。

IS NULL：检查某个值是否为 NULL

基本语法：

column IS NULL

示例：

SELECT * FROM orders WHERE customer_id IS NULL;

结果：返回 orders 表中 customer_id 为 NULL 的所有记录。

IS NOT NULL：检查某个值是否不为 NULL

基本语法：

column IS NOT NULL

示例：

SELECT * FROM orders WHERE customer_id IS NOT NULL;

结果：返回 orders 表中 customer_id 不为 NULL 的所有记录。

EXISTS：检查子查询是否返回任何行

基本语法：

EXISTS (subquery)

示例：

SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.id);

结果：返回 customers 表中至少有一个相关联的 orders 记录的所有客户。

NOT EXISTS：检查子查询是否没有返回任何行

基本语法：

NOT EXISTS (subquery)

示例：

SELECT * FROM customers WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.id);

结果：返回 customers 表中没有任何相关联的 orders 记录的所有客户。

星辰大海844

关注

3
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫