目录
4. 全外连接 (FULL OUTER JOIN) - MySQL不支持，可用UNION模拟
10.2 INTERSECT (MySQL 8.0.31+)
10.3 EXCEPT/MINUS (MySQL 8.0.31+)
(2)不可重复读 (Non-repeatable Read)
一、MySQL 常用函数
MySQL 提供了丰富的内置函数，可以用于数据处理、计算和转换。这些函数主要分为以下几类：
1. 字符串函数
1.1 基本字符串操作
-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World'); -- 输出: Hello World
SELECT CONCAT_WS('-', '2023', '01', '01'); -- 用分隔符连接: 2023-01-01
-- 字符串长度
SELECT LENGTH('MySQL'); -- 字节数: 5
SELECT CHAR_LENGTH('MySQL'); -- 字符数: 5
-- 大小写转换
SELECT UPPER('mysql'); -- MYSQL
SELECT LOWER('MySQL'); -- mysql1.2 字符串截取与处理
-- 截取字符串
SELECT SUBSTRING('MySQL', 2, 3); -- ySQ (从第2个字符开始，取3个)
SELECT LEFT('MySQL', 2); -- My
SELECT RIGHT('MySQL', 3); -- SQL
-- 去除空格
SELECT TRIM(' MySQL '); -- 'MySQL'
SELECT LTRIM(' MySQL'); -- 'MySQL'
SELECT RTRIM('MySQL '); -- 'MySQL'
-- 填充字符串
SELECT LPAD('5', 3, '0'); -- 005
SELECT RPAD('Hi', 5, '!'); -- Hi!!!1.3 字符串搜索与替换
-- 查找位置
SELECT INSTR('MySQL', 'SQL'); -- 3 (SQL在MySQL中的位置)
SELECT LOCATE('SQL', 'MySQL'); -- 3
-- 替换字符串
SELECT REPLACE('MySQL', 'SQL', 'Database'); -- MyDatabase
-- 正则表达式
SELECT 'MySQL' REGEXP '^My'; -- 1 (匹配成功)
SELECT REGEXP_REPLACE('abc123', '[0-9]', 'X'); -- abcXXX2. 数值函数
2.1 基本数学运算
-- 四舍五入
SELECT ROUND(3.14159, 2); -- 3.14
SELECT ROUND(123.456, -1); -- 120 (小数点左侧舍入)
-- 取整
SELECT CEIL(3.14); -- 4 (向上取整)
SELECT FLOOR(3.14); -- 3 (向下取整)
SELECT TRUNCATE(3.14159, 2); -- 3.14 (截断)
-- 绝对值
SELECT ABS(-10); -- 102.2 数学计算
-- 幂运算
SELECT POWER(2, 3); -- 8 (2的3次方)
SELECT SQRT(16); -- 4 (平方根)
-- 对数
SELECT LOG(2, 8); -- 3 (以2为底8的对数)
SELECT LN(10); -- 自然对数
SELECT LOG10(100); -- 2
-- 三角函数
SELECT SIN(PI()/2); -- 1
SELECT COS(0); -- 1
SELECT TAN(PI()/4); -- 约等于12.3 随机数与符号
-- 随机数
SELECT RAND(); -- 0到1之间的随机数
SELECT FLOOR(1 + RAND() * 10); -- 1-10的随机整数
-- 符号判断
SELECT SIGN(-10); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(10); -- 13. 日期时间函数
3.1 获取当前时间
SELECT NOW(); -- 当前日期和时间 (2023-01-01 12:34:56)
SELECT CURDATE(); -- 当前日期 (2023-01-01)
SELECT CURTIME(); -- 当前时间 (12:34:56)
SELECT UNIX_TIMESTAMP(); -- 当前UNIX时间戳3.2 日期时间计算
-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 加1天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 减1个月
SELECT NOW() + INTERVAL 1 HOUR; -- 加1小时
-- 日期差
SELECT DATEDIFF('2023-12-31', '2023-01-01'); -- 364 (天数差)
SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2023-12-31'); -- 11 (月数差)3.3 日期时间提取
-- 提取日期部分
SELECT YEAR(NOW()); -- 2023
SELECT MONTH(NOW()); -- 1-12
SELECT DAY(NOW()); -- 1-31
SELECT HOUR(NOW()); -- 0-23
SELECT MINUTE(NOW()); -- 0-59
SELECT SECOND(NOW()); -- 0-59
-- 星期和季度
SELECT DAYNAME(NOW()); -- Monday
SELECT DAYOFWEEK(NOW()); -- 1=周日, 2=周一,...,7=周六
SELECT QUARTER(NOW()); -- 1-43.4 日期时间格式化
-- 格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2023-01-01
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); -- 12:34:56
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y'); -- Sunday, January 1, 2023
-- 解析日期字符串
SELECT STR_TO_DATE('01-01-2023', '%d-%m-%Y'); -- 2023-01-014. 条件函数
4.1 流程控制函数
-- IF函数
SELECT IF(1 > 0, 'True', 'False'); -- True
SELECT IFNULL(NULL, 'Default'); -- Default
SELECT NULLIF(10, 10); -- NULL (两值相等返回NULL)
-- CASE WHEN
SELECT
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'D'
END AS grade
FROM students;4.2 聚合函数
-- 基本聚合
SELECT COUNT(*) FROM users; -- 记录数
SELECT AVG(price) FROM products; -- 平均值
SELECT SUM(quantity) FROM order_items; -- 总和
SELECT MAX(salary) FROM employees; -- 最大值
SELECT MIN(age) FROM customers; -- 最小值
-- 分组聚合
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;5. 窗口函数 (MySQL 8.0+)
-- 排名函数
SELECT
name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
-- 分区计算
SELECT
department_id, name, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;
-- 累计计算
SELECT
order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;6. JSON 函数 (MySQL 5.7+)
-- 创建JSON
SELECT JSON_OBJECT('name', 'John', 'age', 30);
SELECT JSON_ARRAY(1, 'a', TRUE, NULL);
-- 提取JSON值
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- "John"
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "John"}', '$.name')); -- John
-- 修改JSON
SELECT JSON_SET('{"name": "John"}', '$.age', 30); -- {"name": "John", "age": 30}
SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age'); -- {"name": "John"}7. 系统信息函数
-- 数据库信息
SELECT DATABASE(); -- 当前数据库名
SELECT USER(); -- 当前用户
SELECT VERSION(); -- MySQL版本
-- 连接信息
SELECT CONNECTION_ID(); -- 连接ID
SELECT LAST_INSERT_ID(); -- 最后插入的ID
-- 性能分析
SELECT BENCHMARK(1000000, MD5('test')); -- 执行MD5 100万次8. 加密函数
-- 哈希函数
SELECT MD5('password'); -- 32字符MD5哈希
SELECT SHA1('password'); -- 40字符SHA1哈希
SELECT SHA2('password', 256); -- 64字符SHA256哈希
-- 加密解密
SELECT AES_ENCRYPT('secret', 'key');
SELECT AES_DECRYPT(encrypted_data, 'key');
-- 密码函数
SELECT PASSWORD('mypass'); -- 已废弃，不推荐使用二、MySQL 约束
约束是用于限制表中数据类型的规则，确保数据的准确性和可靠性。MySQL 支持多种约束类型，以下是简要介绍：
1. 主键约束 (PRIMARY KEY)
1.1 基本用法
-- 创建表时定义单列主键
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NO
最低0.47元/天 解锁文章
扫一扫
1379
到【灌水乐园】发言
