👀 MySQL和Oracle的区别
1. 总体比较
特点 | MySQL | Oracle |
---|---|---|
所有权和许可 | 开源 (GPL 许可证),但商业版本由 Oracle 管理 | 专有,需要购买许可证;提供免费的轻量级版本 Oracle Express Edition (XE) |
性能和可扩展性 | 适用于小到中等规模应用 | 适合大型企业级应用 |
特性和工具 | 提供基本的数据库功能和工具 | 提供高级特性,如分区、高级安全性等 |
SQL 变种 | 使用简化的 SQL 变种 | 使用 PL/SQL |
成本 | 基础版本开源,高级特性/支持可能付费 | 企业版本有较高的许可和支持费用 |
社区支持 | 强大的开源社区,提供插件、工具等 | 开源社区不如 MySQL |
安全性 | 提供基本的安全功能 | 提供高级安全选项,如审计、透明数据加密等 |
存储引擎 | 支持多种,如 InnoDB、MyISAM 等 | 通常只有一个高度优化和集成的存储引擎 |
优点 | 开源、社区活跃、灵活、成本低 | 高性能、高可用性、强大的功能集、企业级支持 |
缺点 | 对于非常大的数据集可能不是最佳选择、某些高级功能可能缺失 | 高成本、复杂的配置和管理 |
使用场景 | 网站、小型到中型应用、开源项目 | 大型企业、高可用性需求、大型数据仓库、金融机构 |
2. 功能比较
特点 | MySQL | Oracle |
---|---|---|
存储引擎 | 支持多种存储引擎,如 InnoDB、MyISAM 等 | 主要使用一个统一的存储引擎,但提供了多种存储选项如自动存储管理等 |
事务支持 | 主要依赖于存储引擎(例如,InnoDB 支持,MyISAM 不支持) | 原生支持事务 |
分区 | 支持基本的表分区 | 提供更高级和灵活的分区选项 |
SQL & PL | 使用标准 SQL 和自己的过程扩展 | 使用 SQL 和 PL/SQL(过程语言扩展) |
触发器 | 支持基本的触发器功能 | 提供更高级的触发器功能,包括多种触发器类型和更丰富的事件 |
序列 | 不直接支持,但可以使用 AUTO_INCREMENT | 使用 SEQUENCE 对象来创建自动增长的数字 |
外键 | 在某些存储引擎(如 InnoDB)中支持 | 原生支持 |
安全性 | 基于角色的权限管理 | 更为复杂和强大的权限和角色管理系统 |
索引 | 支持 BTREE 和 HASH 索引 | 支持多种索引类型,包括 B-tree、Bitmap、Function-based 等 |
备份和恢复 | 提供物理和逻辑备份选项,例如 mysqldump | 提供更高级的备份恢复工具,如 RMAN |
性能调优 | 提供 Explain 查询分析和慢查询日志 | 提供更高级的性能调优工具,如 AWR、SQL Trace 和 Oracle Enterprise Manager |
3. 与标准SQL比较
特点 | SQL | PL/SQL | MySQL |
---|---|---|---|
定义 | 查询和操作关系数据库的语言 | 基于 SQL 的 Oracle 过程式扩展 | 开源的关系型数据库管理系统 |
标准化 | ANSI 标准 | 专为 Oracle 设计的 SQL 扩展 | 基于 ANSI,但有自己的扩展 |
控制结构 | 主要依赖 SQL 语句 | 提供完整的编程结构(如循环、条件) | 提供过程式控制结构(如条件、循环) |
使用场景 | 适用于所有关系数据库 | 专用于 Oracle 数据库 | 广泛用于网站和应用程序 |
扩展性 | 依据数据库方言 | 可嵌入逻辑,进行复杂操作 | 丰富的内置函数和存储过程 |
与其他语言的集成 | 通常嵌入其他语言中使用 | 主要与 Oracle 工具和应用集成 | 多语言支持(如 PHP、Python) |
侧重点 | - 数据查询和操作 | - 复杂的业务逻辑处理 - 数据完整性和验证 - 高性能数据操作 | - Web 应用后端支持 - 灵活性和可扩展性 |
相同点 | - 所有都支持 SQL 标准查询 - 数据库操作能力 | - 基于 SQL - 用于后端数据操作和逻辑处理 | - 支持标准 SQL 查询 - 用于后端数据操作和逻辑处理 |
👀 字符串字面值
特性/关键字 | 中文描述 | MySQL | Oracle | 作用描述 |
---|---|---|---|---|
引号 | 字符串界定符 | 单引号 (' ) | 单引号 (' ) | 在两个数据库系统中,字符串字面值都是用单引号界定的。 |
双引号 | 对象名称 | 可以,但需要特定设置 | 是 | 在 Oracle 中,双引号用于界定对象名称,如表名或列名。在 MySQL 中,默认情况下双引号被视为字符串界定符,但可以通过设置 SQL 模式将其用作对象名称。 |
转义字符 | 特殊字符 | 反斜杠 (\ ) | 双单引号 ('' ) | 为了在字符串中包含特殊字符,例如单引号,两个数据库系统都提供了转义机制。在 MySQL 中,常用的转义字符是反斜杠,而在 Oracle 中,使用连续的两个单引号来表示一个单引号。 |
字符串连接 | 合并字符串 | CONCAT(str1, str2) 或 str1 双竖杠 str2 | str1 双竖杠 str2 | 虽然两者都支持使用 双竖杠 进行字符串连接,但 MySQL 默认并不启用这种行为。在 MySQL 中,通常使用 CONCAT 函数进行字符串连接。 |
1. 字符串界定符
在大多数数据库中,字符串字面值通常用单引号 ('
) 包围。这在 MySQL 和 Oracle 中都是一样的。
-
MySQL:
SELECT 'Hello, MySQL!';
-
Oracle:
SELECT 'Hello, Oracle!' FROM DUAL;
2. 使用双引号
-
MySQL: 默认情况下,MySQL 将双引号 (
"
) 视为字符串界定符,与单引号功能相同。但是,如果设置了ANSI_QUOTES
SQL 模式,MySQL 会将双引号视为对象名称(例如表名或列名)的界定符。### 📌默认情况 SELECT "Hello, MySQL!"; ### 📌ANSI_QUOTES 模式下 SELECT column_name AS "Alias" FROM table_name;
-
Oracle: Oracle 总是将双引号用作对象名称的界定符。如果表名或列名是大写的,但您想在查询中使用小写或混合大小写,就需要使用双引号。
SELECT "columnName" FROM "tableName";
3. 转义字符
当您需要在字符串中包含特殊字符(例如单引号)时,必须使用转义字符。
-
MySQL: 使用反斜杠 (
\
) 作为默认的转义字符。SELECT 'It\'s a sunny day in MySQL!';
-
Oracle: 使用两个连续的单引号来表示一个单引号。
SELECT 'It''s a sunny day in Oracle!' FROM DUAL;
4. 字符串连接
连接字符串意味着将两个或多个字符串组合成一个字符串。
-
MySQL: 你可以使用
CONCAT
函数或||
运算符(如果PIPES_AS_CONCAT
模式被启用)。SELECT CONCAT('Hello', ', ', 'MySQL!');
-
Oracle: 使用
||
运算符连接字符串。SELECT 'Hello' || ', ' || 'Oracle!' FROM DUAL;
👀 字符串函数
功能/函数 | MySQL 示例 | Oracle 示例 | 描述 |
---|---|---|---|
长度 | LENGTH('string') | LENGTH('string') | 返回字符串的长度 |
字符连接 | CONCAT('str1', 'str2') | 'str1' 双竖杠 'str2' | 连接两个或多个字符串 |
子字符串 | SUBSTRING('string', start, length) | SUBSTR('string', start, length) | 从字符串中提取子字符串 |
位置 | LOCATE('substr', 'string') | INSTR('string', 'substr') | 返回子字符串在字符串中的位置 |
替换 | REPLACE('string', 'old', 'new') | REPLACE('string', 'old', 'new') | 在字符串中替换所有出现的子字符串 |
转大写 | UPPER('string') | UPPER('string') | 将字符串转换为大写 |
转小写 | LOWER('string') | LOWER('string') | 将字符串转换为小写 |
首字母大写 | CONCAT(UPPER(LEFT('string', 1)), SUBSTRING('string', 2)) | INITCAP('string') | 将字符串的首字母转为大写 |
去除空格 | TRIM('string') | TRIM('string') | 从字符串的两端删除空格 |
左填充 | LPAD('string', length, 'char') | LPAD('string', length, 'char') | 使用指定的字符左填充字符串到给定长度 |
右填充 | RPAD('string', length, 'char') | RPAD('string', length, 'char') | 使用指定的字符右填充字符串到给定长度 |
👀 空值
特点/操作 | MySQL | Oracle |
---|---|---|
表示空值 | NULL | NULL |
字符串连接 | CONCAT('Hello', NULL, 'World') 返回 'HelloWorld' | 'Hello' 双竖杠 NULL 双竖杠 'World' 返回 NULL |
空字符串与 NULL | '' 与 NULL 是不同的 | '' 被视为 NULL |
判断空值 | column IS NULL | column IS NULL |
空值的算术运算 | NULL + 10 返回 NULL | NULL + 10 返回 NULL |
处理空值的函数 | IFNULL(column, 'default') | NVL(column, 'default') |
排序中的空值 | ORDER BY column DESC 将 NULL 置于首位 | ORDER BY column DESC NULLS FIRST 将 NULL 置于首位 |
创建表时的默认值与非空约束 | 必须为 NOT NULL 列提供默认值 | 可以为 NOT NULL 列定义默认值,但不是必需的 |
👀 日期和时间函数
1.函数汇总
功能/函数 | MySQL 示例或描述 | Oracle 示例或描述 | 描述 |
---|---|---|---|
获取当前日期和时间 | NOW() | SYSTIMESTAMP | 返回当前的日期和时间 |
获取当前日期 | CURDATE() | SYSDATE | 返回当前日期 |
获取当前时间 | CURTIME() | CURRENT_TIMESTAMP | 返回当前时间 |
获取年份 | YEAR(date) | EXTRACT(YEAR FROM date) | 提取日期的年份 |
获取月份 | MONTH(date) | EXTRACT(MONTH FROM date) | 提取日期的月份 |
获取日期中的日 | DAY(date) | EXTRACT(DAY FROM date) | 提取日期的日子 |
日期加天数 | DATE_ADD(date, INTERVAL n DAY) | date + n | 在日期上添加天数 |
日期减天数 | DATE_SUB(date, INTERVAL n DAY) | date - n | 从日期中减去天数 |
两个日期之间的天数差 | DATEDIFF(date1, date2) | date1 - date2 | 返回两个日期之间的天数差异 |
格式化日期 | DATE_FORMAT(date, format) | TO_CHAR(date, format) | 将日期格式化为给定的格式 |
从字符串中解析日期 | STR_TO_DATE(string, format) | TO_DATE(string, format) | 根据给定的格式从字符串中解析日期 |
将日期转化为字符串 | DATE_FORMAT(date, format) | TO_CHAR(date, format) | 将日期转换为指定格式的字符串 |
获取小时 | HOUR(time) | EXTRACT(HOUR FROM time) | 从时间值中提取小时 |
获取分钟 | MINUTE(time) | EXTRACT(MINUTE FROM time) | 从时间值中提取分钟 |
获取秒 | SECOND(time) | EXTRACT(SECOND FROM time) | 从时间值中提取秒 |
日期的第一天 | DATE_FORMAT(date, '%Y-%m-01') | TRUNC(date, 'MONTH') | 返回给定日期月份的第一天 |
日期的最后一天 | LAST_DAY(date) | LAST_DAY(date) | 返回给定日期月份的最后一天 |
添加月份 | DATE_ADD(date, INTERVAL n MONTH) | ADD_MONTHS(date, n) | 在日期上添加指定的月数 |
时间戳 | UNIX_TIMESTAMP() / FROM_UNIXTIME(timestamp) | DBMS_UTILITY.GET_TIME / TO_DATE('1970-01-01','YYYY-MM-DD') + ( timestamp / 86400 ) | 获取 Unix 时间戳或将 Unix 时间戳转换为日期格式 |
2.日期格式化符号
功能/描述 | MySQL 格式化符号 | 示例 | Oracle 格式化符号 | 示例 |
---|---|---|---|---|
4位数的年份 | %Y | 2023 | YYYY | 2023 |
2位数的年份 | %y | 23 | YY | 23 |
月份(数字,带前导零) | %m | 01 | MM | 01 |
完整的英文月份名称 | %M | January | MONTH | JANUARY |
日期(数字,带前导零) | %d | 09 | DD | 09 |
带有英文后缀的日 | %D | 1st, 2nd | - | - |
24小时制的小时 | %H | 23 | HH24 | 23 |
12小时制的小时 | %h | 11 | HH or HH12 | 11 |
分钟(数字,带前导零) | %i | 45 | MI | 45 |
秒(数字,带前导零) | %s | 59 | SS | 59 |
24小时制的完整时间 | %T | 23:45:59 | - | - |
12小时制的完整时间 | %r | 11:45:59 PM | - | - |
3.常用的
描述 | MySQL 格式化字符串 | Oracle 格式化字符串 |
---|---|---|
完整的日期和时间 (4位年) | %Y-%m-%d %H:%i:%s | YYYY-MM-DD HH24:MI:SS |
完整的日期和时间 (2位年) | %y-%m-%d %h:%i:%s | YY-MM-DD HH:MI:SS |
完整的日期 (4位年) | %Y-%m-%d | YYYY-MM-DD |
完整的日期 (2位年) | %y-%m-%d | YY-MM-DD |
👀 LIKE
子句
特性/符号 | 中文描述 | 作用 | MySQL 示例 | Oracle 示例 |
---|---|---|---|---|
% | 通配符 | 匹配字符串中的0个或多个字符。 | SELECT * FROM t WHERE name LIKE 'A%'; | SELECT * FROM t WHERE name LIKE 'A%'; |
_ | 单字符通配符 | 匹配字符串中的单个字符。 | SELECT * FROM t WHERE name LIKE 'A_'; | SELECT * FROM t WHERE name LIKE 'A_'; |
ESCAPE | 转义字符 | 在搜索模式中定义转义字符,使你能够搜索特殊字符% 和_ 。 | SELECT * FROM t WHERE name LIKE '%\%%' ESCAPE '\'; | SELECT * FROM t WHERE name LIKE '%\%%' ESCAPE '\'; |
-
基本用法:
- 在MySQL和Oracle中,
LIKE
子句的基本用法是相同的。你可以使用%
来匹配任意数量的字符,使用_
来匹配单个字符。
- 在MySQL和Oracle中,
-
转义字符:
- 在MySQL和Oracle中,如果你想在搜索模式中搜索特殊字符
%
和_
,你需要使用ESCAPE
子句来定义一个转义字符。
- 在MySQL和Oracle中,如果你想在搜索模式中搜索特殊字符
-
大小写敏感性:
- MySQL的
LIKE
子句默认是大小写不敏感的(但这取决于所使用的collation)。 - Oracle的
LIKE
子句默认是大小写敏感的。
- MySQL的
👀 排序
特性/关键字 | 中文描述 | MySQL 示例 | Oracle 示例 | 作用描述 |
---|---|---|---|---|
ORDER BY | 排序 | SELECT name FROM employees ORDER BY name ASC; | SELECT name FROM employees ORDER BY name ASC; | 按姓名升序排列员工。 |
ASC | 升序排序 | SELECT name FROM employees ORDER BY name ASC; | SELECT name FROM employees ORDER BY name ASC; | 按姓名升序排列员工。 |
DESC | 降序排序 | SELECT name FROM employees ORDER BY name DESC; | SELECT name FROM employees ORDER BY name DESC; | 按姓名降序排列员工。 |
NULLS FIRST | 空值首位 | 不直接支持。需使用额外条件或函数。 | SELECT name, age FROM employees ORDER BY age NULLS FIRST; | 将年龄为 NULL 的员工放在列表的开始位置。 |
NULLS LAST | 空值末位 | 默认行为。 | SELECT name, age FROM employees ORDER BY age NULLS LAST; | 将年龄为 NULL 的员工放在列表的末尾。 |
特性描述:
-
基本排序:
- 在MySQL和Oracle中,
ORDER BY
的基本用法都是相同的。您可以按一个或多个列进行排序,并使用ASC
或DESC
关键字指定排序的方向。
- 在MySQL和Oracle中,
-
NULL 值的排序:
- 在 Oracle 中,您可以使用
NULLS FIRST
或NULLS LAST
来明确指定如何对 NULL 值进行排序。这在某些业务场景中是很有用的。在 Oracle 中,当使用 ORDER BY 子句进行升序排序(ASC)时,默认情况下 NULL 值会排在结果集的末尾。相反,当进行降序排序(DESC)时,NULL 值会排在结果集的首位。 - 在 MySQL 中,默认情况下,NULL 值总是出现在排序的最后,无论是升序还是降序。要改变这种行为(例如,将 NULL 值放在开始位置),您可能需要使用一些额外的条件或函数。
- 在 Oracle 中,您可以使用
-
排序稳定性:
- 在多列排序中,Oracle 和 MySQL 的行为都是相同的。如果第一列中的值相同,那么会使用第二列的值进行排序,依此类推。
👀 分组
MySQL 与 Oracle 的 GROUP BY
子句对比
特性/关键字 | 中文描述 | 作用 | MySQL 示例 | Oracle 示例 | 作用描述 |
---|---|---|---|---|---|
GROUP BY | 分组 | 将选定的行按给定的列的值进行分组。 | SELECT dept, COUNT(*) FROM employees GROUP BY dept; | SELECT dept, COUNT(*) FROM employees GROUP BY dept; | 根据部门对员工进行分组,并计算每个部门的员工数量。 |
HAVING | 分组过滤条件 | 与 WHERE 子句类似,但它作用在已经分组的数据上。用于过滤满足特定条件的组。 | SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 10; | SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 10; | 仅选择员工数量超过10的部门。 |
ROLLUP | 分组汇总 | 在常规的分组结果基础上,生成汇总的小计和总计行。 | SELECT dept, job, COUNT(*) FROM employees GROUP BY ROLLUP(dept, job); | SELECT dept, job, COUNT(*) FROM employees GROUP BY ROLLUP(dept, job); | 对部门和工作职位进行分组,并生成小计和总计。 |
CUBE | 多维分组 | 为表中的每个可能的组合生成一个聚合结果。 | 在MySQL中不直接支持。 | SELECT dept, job, COUNT(*) FROM employees GROUP BY CUBE(dept, job); | 对部门和工作职位进行所有可能组合的分组。 |
GROUPING SETS | 分组子集 | 允许对多个分组集进行分组,这比使用多个独立的GROUP BY子句更为灵活和有效。 | 在MySQL中不直接支持。 | SELECT dept, job, COUNT(*) FROM employees GROUP BY GROUPING SETS (dept, job); | 按照指定的分组集进行分组。 |
高级分组:
- Oracle 提供了
ROLLUP
,CUBE
, 和GROUPING SETS
等高级的分组功能,这些在 MySQL 中并不直接支持。
选定列的严格性:
- 在 Oracle 中,
SELECT
子句中列出的每个非聚合列都必须在GROUP BY
子句中明确指定。而在 MySQL 中,尽管建议按这种方式操作,但它允许您省略某些非聚合列,MySQL 会为您选择一个值。
函数的严格性:
- Oracle 不允许使用非确定性函数或子查询在
GROUP BY
子句中,而 MySQL 则没有这种限制。
虚拟列:
- 在 Oracle 中,您可以在
GROUP BY
子句中使用别名或计算列,但前提是它们在SELECT
子句中定义得很清楚。而在 MySQL 中,您可以直接使用别名。
潜在问题与好处:
-
MySQL:
- 不确定性:可能会导致每次查询结果的不一致性。
- 可能的逻辑错误:开发者可能会不小心忽略某些非聚合列。
- 性能问题:可能导致不必要的性能开销。
-
Oracle:
- 确定性:每次查询都会返回相同的结果。
- 防止逻辑错误:严格的规则可以确保开发者不会无意中遗漏非聚合列。
- 清晰性:使 SQL 语句更加清晰,其他开发者在阅读代码时更容易理解查询的意图。
👀 连接
Full Outer Join: MySQL不直接支持FULL OUTER JOIN,但可以使用联合查询模拟此效果。
Using Clause: 当两个表有相同名称的列时,可以使用USING子句简化JOIN条件。
Natural Join: 根据两个表中所有具有匹配列名的列进行JOIN。
Non-ANSI JOIN: 这是传统的方式来写JOIN语句,但现在已被ANSI JOIN语法所取代。在Oracle中,非ANSI JOIN已被弃用。
中文名称 | 类型/特点 | 作用 | MySQL 示例 | Oracle 示例 |
---|---|---|---|---|
内连接 | INNER JOIN | 返回两个表中存在匹配的行。 | SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id; | SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id; |
左外连接 | LEFT JOIN (or LEFT OUTER JOIN) | 返回左表的所有行,即使右表没有匹配的行。 | SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id; | SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id; |
右外连接 | RIGHT JOIN (or RIGHT OUTER JOIN) | 返回右表的所有行,即使左表没有匹配的行。 | SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id; | SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id; |
全外连接 | FULL JOIN (or FULL OUTER JOIN) | 返回两个表中存在匹配的行,如果左边或右边的表中没有匹配的行,则会返回NULL。 | 不支持 | SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id; |
交叉连接 | CROSS JOIN | 返回表的笛卡尔积,即每个表中的每一行与另一个表中的每一行组合。 | SELECT * FROM t1 CROSS JOIN t2; | SELECT * FROM t1 CROSS JOIN t2; |
使用USING子句的连接 | Using JOIN USING clause | 根据USING列表中的所有列来比较行,该列表中的列必须在两个表中都存在。 | SELECT * FROM t1 JOIN t2 USING (id); | SELECT * FROM t1 JOIN t2 USING (id); |
自然连接 | Natural JOIN | 返回两个表中所有匹配的行,此连接类型基于两个表中名称相同的所有列来进行连接。 | SELECT * FROM t1 NATURAL JOIN t2; | SELECT * FROM t1 NATURAL JOIN t2; |
自连接 | Self JOIN | 表与自身连接,通常用于查找表中的相关行。 | SELECT * FROM t1 A, t1 B WHERE A.id = B.id; | SELECT * FROM t1 A, t1 B WHERE A.id = B.id; |
非ANSI连接 | Non ANSI JOIN (Deprecated in Oracle) | 早期的连接语法,现已不推荐使用。 | SELECT * FROM t1, t2 WHERE t1.id = t2.id; | SELECT * FROM t1, t2 WHERE t1.id = t2.id; (Not recommended) |
👀 WITH子句
- Oracle和MySQL的WITH子句(公共表表达式)在语法上基本相同,主要区别在于查询优化和性能方面。
WITH cte1 AS (SELECT * FROM table1),
cte2 AS (SELECT * FROM table2)
SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;
-
CTE(Common Table Expressions,常用表达式)允许你在查询中定义临时的、可重复使用的结果集。在MySQL中,你可以使用递归CTE来生成序列。
-
以下是一个示例,展示如何使用递归CTE在MySQL中生成1到10的序列:
WITH RECURSIVE sequence AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM sequence WHERE number < 10 ) SELECT number FROM sequence;
在这个示例中:
- 我们首先定义了一个叫做
sequence
的CTE。 - 在CTE的基础查询中,我们选择数字1。
- 在递归查询部分,我们从CTE
sequence
中选择当前数字加1,并继续这样做,直到数字小于10。
当我们从CTE
sequence
中选择数据时,它会返回数字1到10的序列。注意:要小心确保递归查询有一个明确的终止条件,否则它可能会无限循环。在上述示例中,
WHERE number < 10
就是我们的终止条件。 - 我们首先定义了一个叫做
-
👀 变量处理
-
MySQL 和 Oracle 在变量处理上有着明显的不同。让我们先看一下两者的区别:
-
MySQL
- 在 MySQL 中,您可以使用 @ 前缀来声明会话变量。您可以在查询中直接使用它们,通常用于生成行号、累积求和等操作。在您给出的示例中,您正是使用了这种方法。
-- 创建一个示例表格
CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
score INT
);
-- 插入示例数据
INSERT INTO student (name, score) VALUES ('Alice', 90), ('Bob', 85), ('Charlie', 92), ('David', 88);
-- 使用会话变量生成行号
SET @rownum := 0;
SELECT (@rownum := @rownum + 1) AS row_number, name, score
FROM student
ORDER BY score DESC;
- Oracle
- 在 Oracle 中,您不能在 SQL 查询中直接声明和使用变量。但是,PL/SQL(Oracle 的过程性扩展)允许您在代码块中使用变量。对于像生成行号这样的常见任务,Oracle 提供了专门的函数,如 ROW_NUMBER(),而不需要依赖变量。
-- 创建一个示例表格
CREATE TABLE students (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
score NUMBER
);
-- 插入示例数据
INSERT INTO students (id, name, score) VALUES (1, 'Alice', 90);
INSERT INTO students (id, name, score) VALUES (2, 'Bob', 85);
INSERT INTO students (id, name, score) VALUES (3, 'Charlie', 92);
INSERT INTO students (id, name, score) VALUES (4, 'David', 88);
-- 使用 ROW_NUMBER() 函数生成行号
SELECT
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
name,
score
FROM students;
-- 创建一个匿名块
DECLARE
v_counter NUMBER := 0; -- 声明并初始化一个变量
BEGIN
-- 在此处编写您的代码
-- 递增变量的值
v_counter := v_counter + 1;
-- 输出变量的值
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
END;
/
-- 创建一个匿名块
DECLARE
v_counter NUMBER := 0; -- 声明并初始化一个变量
-- 定义一个游标
CURSOR result_cursor IS
SELECT * FROM your_table WHERE some_condition; -- 您的查询条件
-- 声明一个记录类型,用于存储查询结果的行
TYPE result_record IS RECORD (
column1 your_table.column1%TYPE, -- 请替换为实际列名和数据类型
column2 your_table.column2%TYPE
-- 添加其他列
);
v_result result_record; -- 声明一个记录变量,用于存储查询结果的行
BEGIN
-- 打开游标
OPEN result_cursor;
-- 循环处理游标中的每一行
LOOP
-- 从游标中获取下一行数据
FETCH result_cursor INTO v_result;
-- 退出循环如果没有更多行
EXIT WHEN result_cursor%NOTFOUND;
-- 在此处可以使用变量 v_result,执行其他操作
-- 例如,可以输出结果行的特定列的值
DBMS_OUTPUT.PUT_LINE('Column1: ' || v_result.column1 || ', Column2: ' || v_result.column2);
-- 递增变量的值
v_counter := v_counter + 1;
END LOOP;
-- 关闭游标
CLOSE result_cursor;
-- 输出变量的值
DBMS_OUTPUT.PUT_LINE('Total Rows: ' || v_counter);
END;
/
👀 MySQL窗口函数
窗口函数(Window Functions)在SQL中提供了一种方法,允许你在一组相关的行(称为窗口)上执行计算,并为每一行返回一个值。这与聚合函数不同,聚合函数为每个分组返回一个值,而窗口函数为每一行返回一个值。
以下是一些常用的窗口函数及其描述和示例:
1. ROW_NUMBER()
为每一行分配一个唯一的连续整数。
SELECT name, age,
ROW_NUMBER() OVER (ORDER BY age) AS row_num
FROM users;
2. RANK()
为每一行分配一个唯一的排名。如果两行或多行有相同的值,它们将获得相同的排名。
SELECT name, score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;
3. DENSE_RANK()
与 RANK()
类似,但不会跳过任何排名。
SELECT name, score,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;
4. LAG()
返回指定的前一行的值。
SELECT date, sales,
LAG(sales) OVER (ORDER BY date) AS prev_day_sales
FROM daily_sales;
5. LEAD()
返回指定的后一行的值。
SELECT date, sales,
LEAD(sales) OVER (ORDER BY date) AS next_day_sales
FROM daily_sales;
6. FIRST_VALUE() 和 LAST_VALUE()
返回窗口内的第一个和最后一个值。
SELECT date, sales,
FIRST_VALUE(sales) OVER (ORDER BY date) AS first_sale,
LAST_VALUE(sales) OVER (ORDER BY date) AS last_sale
FROM daily_sales;
7. SUM(), AVG(), MIN(), MAX()
这些聚合函数也可以作为窗口函数使用,为窗口内的每一行提供累计值。
SELECT date, sales,
SUM(sales) OVER (ORDER BY date) AS cumulative_sales
FROM daily_sales;
8. PARTITION BY
在窗口函数中,PARTITION BY
子句允许你将结果集划分为多个小组或“分区”,然后对每个分区单独应用窗口函数。你可以把它看作是窗口函数版本的 GROUP BY
,但与 GROUP BY
不同,PARTITION BY
不会减少结果集的行数。相反,它为每个分区中的每一行提供一个计算值。
📌示例:
假设我们有一个 sales
表,记录了每个销售员在不同日期的销售额:
| date | salesperson | amount |
|------------|-------------|--------|
| 2023-01-01 | Alice | 100 |
| 2023-01-01 | Bob | 150 |
| 2023-01-02 | Alice | 200 |
| 2023-01-02 | Bob | 250 |
| 2023-01-03 | Alice | 300 |
| 2023-01-03 | Bob | 350 |
我们想要计算每个销售员的累计销售额:
SELECT date, salesperson, amount,
SUM(amount) OVER (PARTITION BY salesperson ORDER BY date) AS cumulative_sales
FROM sales;
结果会是:
| date | salesperson | amount | cumulative_sales |
|------------|-------------|--------|------------------|
| 2023-01-01 | Alice | 100 | 100 |
| 2023-01-02 | Alice | 200 | 300 |
| 2023-01-03 | Alice | 300 | 600 |
| 2023-01-01 | Bob | 150 | 150 |
| 2023-01-02 | Bob | 250 | 400 |
| 2023-01-03 | Bob | 350 | 750 |
注意,cumulative_sales
列是按销售员(由 PARTITION BY
定义)分开的。每个销售员的累计销售额是单独计算的。
📌使用 PARTITION BY
的优势:
- 灵活性:它允许你在各个分区内独立地应用窗口函数,而不必将数据分为多个独立的查询或子查询。
- 简洁性:使用
PARTITION BY
可以使你的查询更简洁,更易于阅读和维护。 - 性能:在某些情况下,使用
PARTITION BY
可以提高查询性能,因为数据库可以在一个查询操作中同时处理多个分区。
总之,PARTITION BY
是窗口函数中的一个强大工具,它使得对数据的复杂分析变得简单和高效。
👀 MYSQL函数备忘录
MySQL DATE_ADD() 函数
-- 添加天数
SELECT DATE_ADD('2023-09-20', INTERVAL 5 DAY) AS 'Add 5 Days';
-- 添加月份
SELECT DATE_ADD('2023-09-20', INTERVAL 2 MONTH) AS 'Add 2 Months';
-- 添加年份
SELECT DATE_ADD('2023-09-20', INTERVAL 1 YEAR) AS 'Add 1 Year';
-- 添加小时
SELECT DATE_ADD('2023-09-20 12:00:00', INTERVAL 3 HOUR) AS 'Add 3 Hours';
-- 添加分钟
SELECT DATE_ADD('2023-09-20 12:00:00', INTERVAL 30 MINUTE) AS 'Add 30 Minutes';
-- 添加秒
SELECT DATE_ADD('2023-09-20 12:00:00', INTERVAL 45 SECOND) AS 'Add 45 Seconds';
MySQL DATE_SUB() 函数
-- 减去天数
SELECT DATE_SUB('2023-09-20', INTERVAL 5 DAY) AS 'Subtract 5 Days';
-- 减去月份
SELECT DATE_SUB('2023-09-20', INTERVAL 2 MONTH) AS 'Subtract 2 Months';
-- 减去年份
SELECT DATE_SUB('2023-09-20', INTERVAL 1 YEAR) AS 'Subtract 1 Year';
-- 减去小时
SELECT DATE_SUB('2023-09-20 12:00:00', INTERVAL 3 HOUR) AS 'Subtract 3 Hours';
-- 减去分钟
SELECT DATE_SUB('2023-09-20 12:00:00', INTERVAL 30 MINUTE) AS 'Subtract 30 Minutes';
-- 减去秒
SELECT DATE_SUB('2023-09-20 12:00:00', INTERVAL 45 SECOND) AS 'Subtract 45 Seconds';
MySQL DATEDIFF() 函数
-- 计算两个日期之间的差异
SELECT DATEDIFF('2023-09-20', '2023-09-10') AS 'Difference in Days (Result: 10)';
-- 如果日期1在日期2之前
SELECT DATEDIFF('2023-09-10', '2023-09-20') AS 'Negative Difference (Result: -10)';
-- 计算两个相同的日期之间的差异
SELECT DATEDIFF('2023-09-20', '2023-09-20') AS 'Same Date Difference (Result: 0)';
MySQL DATE_FORMAT() 函数
-- 格式化日期为 YYYY-MM-DD
SELECT DATE_FORMAT('2023-09-20', '%Y-%m-%d') AS 'Formatted Date (Result: 2023-09-20)';
-- 格式化日期和时间为 YYYY-MM-DD HH:mm:ss
SELECT DATE_FORMAT('2023-09-20 15:30:45', '%Y-%m-%d %H:%i:%s') AS 'Formatted DateTime (Result: 2023-09-20 15:30:45)';
-- 格式化日期为 YY-M-D
SELECT DATE_FORMAT('2023-09-20', '%y-%m-%d') AS 'Short Formatted Date (Result: 23-09-20)';
-- 显示月和年
SELECT DATE_FORMAT('2023-09-20', '%M %Y') AS 'Month and Year (Result: September 2023)';
-- 12小时制的时间
SELECT DATE_FORMAT('2023-09-20 15:30:45', '%h:%i:%s %p') AS '12-hour Time (Result: 03:30:45 PM)';
MySQL STR_TO_DATE() 函数
SELECT
-- 示例 1: 转换标准日期格式 (YYYY-MM-DD)
STR_TO_DATE('2023-09-20', '%Y-%m-%d') AS formatted_date_1,
-- 示例 2: 转换日期格式 (MM/DD/YYYY)
STR_TO_DATE('09/20/2023', '%m/%d/%Y') AS formatted_date_2,
-- 示例 3: 转换日期和时间格式 (YYYY-MM-DD HH:MI:SS)
STR_TO_DATE('2023-09-20 14:30:45', '%Y-%m-%d %H:%i:%s') AS formatted_datetime_1,
-- 示例 4: 转换其他自定义格式 (DD Month YYYY)
STR_TO_DATE('20 September 2023', '%d %M %Y') AS formatted_date_3;
MySQL NOW() 、CURDATE()、 CURTIME() 函数
-- 获取当前的日期和时间
SELECT NOW() AS 'Current DateTime';
-- 获取当前的日期
SELECT CURDATE() AS 'Current Date';
-- 获取当前的时间
SELECT CURTIME() AS 'Current Time';
MySQL DATE() 其他函数
MYSQL
%Y%m%d
%Y-%m-%d
%Y-%m-%d %H:%I:%S
%Y%m%d%H%i%S
Oracle
YYYY-MM-DD HH24:MI:ss
-- 从日期时间值中提取日期
SELECT DATE('2023-09-20 15:30:45') AS 'Date Part (Result: 2023-09-20)';
-- 获取当前的日期(不含时间)
SELECT DATE(NOW()) AS 'Current Date';
-- 将 Unix 时间戳转换为日期时间
SELECT FROM_UNIXTIME(1632075000); -- 输出:'2021-09-20 12:10:00'
-- 将日期时间字符串转换为 Unix 时间戳
SELECT UNIX_TIMESTAMP('2021-09-20 12:10:00'); -- 输出:1632075000
MySQL EXTRACT() 函数
-- 从日期中提取年份
SELECT EXTRACT(YEAR FROM '2023-09-20') AS 'Year (Result: 2023)';
-- 从日期中提取月份
SELECT EXTRACT(MONTH FROM '2023-09-20') AS 'Month (Result: 9)';
-- 从日期中提取日期
SELECT EXTRACT(DAY FROM '2023-09-20') AS 'Day (Result: 20)';
-- 从日期时间中提取小时
SELECT EXTRACT(HOUR FROM '2023-09-20 15:30:45') AS 'Hour (Result: 15)';
-- 从日期时间中提取分钟
SELECT EXTRACT(MINUTE FROM '2023-09-20 15:30:45') AS 'Minute (Result: 30)';
MySQL 字符串连接函数
📌CONCAT
-
使用方法:
- CONCAT(str1,str2,…)
- 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。
-
注意:
- 如果所有参数均为非二进制字符串,则结果为非二进制字符串。
- 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
-- 一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast
SELECT CONCAT(CAST(int_col AS CHAR), char_col)
-- 连接两个字符串
SELECT CONCAT('Hello', ' World!') AS 'Concatenated String (Result: Hello World!)';
-- 连接多个字符串
SELECT CONCAT('MySQL', ' ', 'is', ' ', 'great!') AS 'Multiple Concatenation (Result: MySQL is great!)';
-- 连接数字和字符串
SELECT CONCAT('The answer is: ', 42) AS 'Number and String Concatenation (Result: The answer is: 42)';
-- 在查询中使用 CONCAT
-- 假设有一个表 "users",其中有名为 "first_name" 和 "last_name" 的列
-- SELECT CONCAT(first_name, ' ', last_name) AS 'Full Name' FROM users;
-- 随机时间
select concat(floor(1990 + (rand() * 25)),'-',lpad(floor(1 + (rand() * 12)),2,0),'-',lpad(floor(3 + (rand() * 8)),2,0))
📌CONCAT_WS
- 使用方法:
-
CONCAT_WS(separator,str1,str2,…)
-
CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。
-
- 注意:
- 如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
-- 使用逗号作为分隔符连接字符串
SELECT CONCAT_WS(',', 'Apple', 'Banana', 'Cherry') AS 'Concatenated String (Result: Apple,Banana,Cherry)';
-- 使用空格作为分隔符连接字符串
SELECT CONCAT_WS(' ', 'John', 'Doe') AS 'Full Name (Result: John Doe)';
-- 连接多个字符串并在它们之间插入自定义分隔符
SELECT CONCAT_WS(' - ', 'Product Code', '001', 'Description', 'Sample Product') AS 'Product Info (Result: Product Code - 001 - Description - Sample Product)';
📌GROUP_CONCAT
- group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
- select id,group_concat(distinct name order by name desc separator ‘;’) from aa group by id;
| order_id | product |
|---------|---------|
| 1 | Apple |
| 1 | Banana |
| 2 | Orange |
| 3 | Cherry |
| 3 | Grape |
-- 使用默认的逗号作为分隔符
SELECT order_id, GROUP_CONCAT(product) AS products
FROM orders
GROUP BY order_id;
| order_id | products |
|---------|---------------|
| 1 | Apple,Banana |
| 2 | Orange |
| 3 | Cherry,Grape |
-- 使用自定义分隔符
SELECT order_id, GROUP_CONCAT(product, '|') AS products
FROM orders
GROUP BY order_id;
| order_id | products |
|---------|-------------------|
| 1 | Apple|Banana |
| 2 | Orange |
| 3 | Cherry|Grape |
📌REPEAT
- 用来复制字符串,如下’ab’表示要复制的字符串,2表示复制的份数
##这将返回一个重复了3次的字符串,即 'Hello Hello Hello World'。
SELECT CONCAT(REPEAT('Hello ', 3), 'World') AS 'Repeated String';
MySQL 字符串截取函数
📌SUBSTRING
-- 从第 2 个字符开始提取
SELECT SUBSTRING('Hello World!', 2) AS 'Substring (Result: ello World!)';
-- 从第 2 个字符开始,提取4个字符
SELECT SUBSTRING('Hello World!', 2, 4) AS 'Substring with Length (Result: ello)';
-- 从倒数第 3 个字符开始提取(使用负数表示从字符串的尾部开始)
SELECT SUBSTRING('Hello World!', -3) AS 'Substring from End (Result: ld!)';
-- 在查询中使用 SUBSTRING
-- 假设有一个表 "products",其中有一个名为 "product_code" 的列,该列的格式为 "XXX-YYYY"
-- 提取前三个字符
-- SELECT SUBSTRING(product_code, 1, 3) AS 'Product Prefix' FROM products;
📌LEFT
-- 从字符串的左边提取前 3 个字符
SELECT LEFT('Hello World', 3) AS 'Left (Result: Hel)';
-- 从字符串的左边提取前 5 个字符
SELECT LEFT('MySQL is great', 5) AS 'Left (Result: MySQL)';
-- 在查询中使用 LEFT
-- 假设有一个表 "employees",其中有一个名为 "full_name" 的列,包含员工的全名
-- 提取员工名字的前三个字符
-- SELECT LEFT(full_name, 3) AS 'Employee Name' FROM employees;
📌RIGHT
-- 从字符串的右边提取后 3 个字符
SELECT RIGHT('Hello World', 3) AS 'Right (Result: rld)';
-- 从字符串的右边提取后 7 个字符
SELECT RIGHT('MySQL is great', 7) AS 'Right (Result: is great)';
-- 在查询中使用 RIGHT
-- 假设有一个表 "file_names",其中有一个名为 "file_name" 的列,包含文件名
-- 提取文件扩展名的后三个字符
-- SELECT RIGHT(file_name, 3) AS 'File Extension' FROM file_names;
📌SUBSTRING_INDEX
-- 从左往右提取第一个分隔符前的子字符串
SELECT SUBSTRING_INDEX('Hello,World,How,Are,You', ',', 1) AS 'Substring (Result: Hello)';
-- 从左往右提取第二个分隔符前的子字符串
SELECT SUBSTRING_INDEX('Hello,World,How,Are,You', ',', 2) AS 'Substring (Result: Hello,World)';
-- 从右往左提取第一个分隔符后的子字符串
SELECT SUBSTRING_INDEX('Hello,World,How,Are,You', ',', -1) AS 'Substring (Result: You)';
-- 从右往左提取第二个分隔符后的子字符串
SELECT SUBSTRING_INDEX('Hello,World,How,Are,You', ',', -2) AS 'Substring (Result: Are,You)';
-- 假设有一个表 "file_paths" 包含文件的完整路径,您可以使用 SUBSTRING_INDEX() 来提取文件名和扩展名:
SELECT
SUBSTRING_INDEX(file_path, '/', -1) AS 'File Name',
SUBSTRING_INDEX(SUBSTRING_INDEX(file_path, '/', -1), '.', -1) AS 'File Extension'
FROM file_paths;
📌SUBSTR
-- 从第 2 个字符开始提取
SELECT SUBSTR('Hello World!', 2) AS 'Substr (Result: ello World!)';
-- 从第 2 个字符开始,提取4个字符
SELECT SUBSTR('Hello World!', 2, 4) AS 'Substr with Length (Result: ello)';
-- 从倒数第 3 个字符开始提取(使用负数表示从字符串的尾部开始)
SELECT SUBSTR('Hello World!', -3) AS 'Substr from End (Result: ld!)';
-- 在查询中使用 SUBSTR
-- 假设有一个表 "products",其中有一个名为 "product_code" 的列,该列的格式为 "XXX-YYYY"
-- 提取前三个字符
-- SELECT SUBSTR(product_code, 1, 3) AS 'Product Prefix' FROM products;
MySQL 字符串填充函数
SELECT LPAD('42', 5, '0') AS 'Padded String'; -- 结果:'00042'
SELECT RPAD('Hello', 8, '-') AS 'Padded String'; -- 结果:'Hello---'
#一、前补0(左补0)
select lpad( @a := @a + 1, 6, 0 ) as time from (select @a := 0) as i
#二、后补0(右补0)
select rpad( @a := @a + 1, 6, 0 ) as time from (select @a := 0) as i
MySQL 字符串其他函数
-- LENGTH() 函数示例
-- CHAR_LENGTH(str):返回字符串 str 的字符个数
-- LENGTH(STR):返回字符串 str 的字节个数
SELECT LENGTH('Hello World') AS 'Length'; -- 结果:11
-- UPPER() 和 LOWER() 函数示例
SELECT UPPER('Hello') AS 'Uppercase', LOWER('World') AS 'Lowercase';
-- TRIM() 函数示例
SELECT TRIM(' Hello ') AS 'Trimmed String';
select trim(leading 'x' from 'xxxbarxxx'); --删除指定的首字符 x,'barxxx'
select trim(both 'x' from 'xxxbarxxx'); --删除指定的首尾字符 x,'bar'
select trim(trailing 'xyz' from 'barxxyz'); --删除指定的尾字符 x,'barx'
-- 使用 REPLACE() 函数将 'Hello' 替换为 'Hi'
SELECT REPLACE('Hello, World!', 'Hello', 'Hi') AS 'Replaced String';
-- 使用 LOCATE() 函数查找 'World' 在 'Hello World!' 中的位置
SELECT LOCATE('World', 'Hello World!') AS 'Location';
-- 使用 CONVERT() 函数将字符串 '文本' 从当前字符集转换为 UTF-8
SELECT CONVERT('文本' USING utf8) AS 'Converted String';
-- 使用 FORMAT() 函数将数字格式化为带有两位小数的货币值
SELECT FORMAT(1234567.89, 2) AS 'Formatted Value';
-- 设置 CONCAT_NULL_YIELDS_NULL 选项为 OFF,不将 NULL 视为空字符串
SET CONCAT_NULL_YIELDS_NULL = OFF;
MySQL 数据类型转换函数
-- 将字符串 '123' 转换为有符号整数
SELECT CAST('123' AS SIGNED);
-- 将浮点数 45.67 转换为整数
SELECT CAST(45.67 AS UNSIGNED);
-- 将日期字符串 '2023-09-30' 转换为日期类型
SELECT CAST('2023-09-30' AS DATE);
-- 将日期时间字符串 '2023-09-30 14:30:00' 转换为日期时间类型
SELECT CAST('2023-09-30 14:30:00' AS DATETIME);
-- 将字符串 '456' 转换为整数
SELECT CONVERT('456', SIGNED);
-- 将浮点数 78.9 转换为整数
SELECT CONVERT(78.9, UNSIGNED);
-- 将日期字符串 '2023-10-15' 转换为日期类型
SELECT CONVERT('2023-10-15', DATE);
-- 将日期时间字符串 '2023-10-15 09:45:30' 转换为日期时间类型
SELECT CONVERT('2023-10-15 09:45:30', DATETIME);
MySQL 聚合函数
函数 | 描述 | 示例 |
---|---|---|
count(*) | 计算表中的行数 | SELECT count(*) FROM users; |
count(col) | 计算表中具有非NULL值的指定列的数量 | SELECT count(name) FROM users; |
sum(col_name) | 计算指定数值列的合计值 | SELECT sum(age) FROM users; |
avg(col_name) | 计算指定数值列的平均值 | SELECT avg(age) FROM users; |
MAX(col_name) | 计算表中指定列的最大值 | SELECT MAX(salary) FROM employees; |
MIN(col_name) | 计算表中指定列的最小值 | SELECT MIN(salary) FROM employees; |
MySQL FORMAT()函数
FORMAT()
函数在MySQL中用于格式化数字。它可以帮助你将数字转换为易读的格式,特别是当数字很大时。
以下是基于您提供的内容的MySQL FORMAT()
函数的表格:
函数 | 描述 | 示例 |
---|---|---|
FORMAT(number, decimals) | 格式化数字,并返回一个带有小数点后指定位数的字符串 | SELECT FORMAT(12345.6789, 2); 结果是 ‘12,345.68’ |
在上述示例中,FORMAT()
函数将数字 12345.6789
格式化为字符串,并将其舍入为两位小数,即 12,345.68
。
注意:FORMAT()
函数还可以接受一个可选的第三个参数,用于指定要使用的区域设置。这决定了如何显示数字的千位分隔符和小数点。如果省略,它将使用默认的区域设置。
MySQL 秒小时互转
函数 | 描述 | 示例 |
---|---|---|
SEC_TO_TIME() | 将秒数转换为 ‘HH:MM:SS’ 格式的时间 | SELECT SEC_TO_TIME(3661); 结果是 ‘01:01:01’ |
TIME_TO_SEC() | 将 ‘HH:MM:SS’ 格式的时间转换为秒数 | SELECT TIME_TO_SEC('01:01:01'); 结果是 3661 |
MySQL MD5 函数
当然,MD5()
函数在MySQL中用于计算字符串的MD5哈希值。这通常用于加密数据,特别是密码,但请注意,MD5现在被认为是不安全的,因为它容易受到暴力攻击和哈希冲突。
以下是基于您提供的内容的MySQL MD5()
函数的表格:
函数 | 描述 | 示例 |
---|---|---|
MD5(str) | 返回字符串 str 的 MD5 哈希值 | SELECT MD5('hello'); |
使用上述示例,MD5()
函数将为字符串 “hello” 返回其MD5哈希值。
注意:尽管MD5仍广泛使用,但由于其已知的安全性问题,建议在安全性要求更高的应用中使用更强的哈希函数,如 SHA-256
。
MySQL 数学函数
-- ABS() 函数示例:返回一个数的绝对值
SELECT ABS(-10) AS 'Absolute Value'; -- 结果:10
-- ROUND() 函数示例:将一个数四舍五入到指定的小数位数
SELECT ROUND(9.45, 1) AS 'Rounded Value'; -- 结果:9.5
-- MOD() 函数示例:返回两个数的余数
SELECT MOD(10, 3) AS 'Modulus'; -- 结果:1
-- FLOOR() 函数示例:将一个数向下取整为最接近的整数
SELECT FLOOR(9.8) AS 'Floor Value'; -- 结果:9
-- CEIL() / CEILING() 函数示例:将一个数向上取整为最接近的整数
SELECT CEIL(9.2) AS 'Ceiling Value'; -- 结果:10
-- POWER() 函数示例:返回一个数的指定次幂
SELECT POWER(2, 3) AS '2^3'; -- 结果:8
-- SQRT() 函数示例:返回一个数的平方根
SELECT SQRT(25) AS 'Square Root'; -- 结果:5
-- TRUNCATE() 函数示例:截断一个数到指定的小数位数
SELECT TRUNCATE(9.9876, 2) AS 'Truncated Value'; -- 结果:9.98
-- RAND() 函数示例:返回一个随机浮点数,范围在 0 到 1 之间
SELECT RAND() AS 'Random Number';
-- 整除示例:使用 "//" 运算符进行整数除法
SELECT 10 // 3 AS 'Integer Division'; -- 结果:3
-- 除法示例:使用 "/" 运算符进行普通除法
SELECT 10 / 3 AS 'Regular Division'; -- 结果:3.3333...
👀 Oracle函数备忘录
Oracle 字符串函数
-- SUBSTR 函数示例:从第 2 个字符开始提取 4 个字符
SELECT SUBSTR('Oracle Database', 2, 4) AS "Substring" FROM dual; -- 结果:"racle"
-- INSTR 函数示例:查找 'Database' 在 'Oracle Database' 中的位置
SELECT INSTR('Oracle Database', 'Database') AS "Location" FROM dual; -- 结果:8
-- CONCAT 函数示例:连接字符串
SELECT CONCAT('Hello', ' World!') AS "Concatenated String" FROM dual; -- 结果:"Hello World!"
-- || 运算符示例:连接字符串
SELECT 'Hello' || ' World!' AS "Concatenated String" FROM dual; -- 结果:"Hello World!"
-- TRIM 函数示例:删除字符串的前导和尾随空格
SELECT TRIM(' Oracle ') AS "Trimmed String" FROM dual; -- 结果:"Oracle"
-- UPPER 和 LOWER 函数示例:转换字符串大小写
SELECT UPPER('oracle') AS "Uppercase", LOWER('ORACLE') AS "Lowercase" FROM dual;
-- REPLACE 函数示例:替换子字符串
SELECT REPLACE('Oracle SQL', 'SQL', 'PL/SQL') AS "Replaced String" FROM dual; -- 结果:"Oracle PL/SQL"
-- LENGTH 函数示例:计算字符串长度
SELECT LENGTH('Oracle') AS "Length" FROM dual; -- 结果:6
-- 向字符串 'Oracle' 的右侧添加空格,使其总长度为 10 个字符
SELECT RPAD('Oracle', 10) AS "Padded String" FROM dual; -- 结果:"Oracle "
-- 向字符串 '123' 的右侧添加 0,使其总长度为 6 个字符
SELECT RPAD('123', 6, '0') AS "Padded String" FROM dual; -- 结果:"123000"
-- 向字符串 'Oracle' 的左侧添加空格,使其总长度为 10 个字符
SELECT LPAD('Oracle', 10) AS "Padded String" FROM dual; -- 结果:" Oracle"
-- 向字符串 '123' 的左侧添加 0,使其总长度为 6 个字符
SELECT LPAD('123', 6, '0') AS "Padded String" FROM dual; -- 结果:"000123"
Oracle 日期函数
-- 获取当前日期和时间
SELECT CURRENT_TIMESTAMP AS "Current Date and Time" FROM dual;
-- 结果示例:2023-09-20 13:45:30
-- 将字符日期转换为日期类型
SELECT TO_DATE('2023-09-20', 'YYYY-MM-DD') AS "Converted Date" FROM dual;
-- 结果示例:2023-09-20
-- 将日期转换为字符日期
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS "Formatted Date and Time" FROM dual;
-- 结果示例:2023-09-20 13:45:30
-- 截断日期,将时间部分设置为零
SELECT TRUNC(SYSDATE) AS "Truncated Date" FROM dual;
-- 结果示例:2023-09-20
-- 添加月份到日期
SELECT ADD_MONTHS(SYSDATE, 3) AS "Three Months Later" FROM dual;
-- 结果示例:2023-12-20
-- 计算两个日期之间的月数
SELECT MONTHS_BETWEEN('2023-12-20', '2023-09-20') AS "Months Difference" FROM dual;
-- 结果示例:3
-- 返回月份的最后一天
SELECT LAST_DAY('2023-09-20') AS "Last Day of Month" FROM dual;
-- 结果示例:2023-09-30
-- 返回下一个特定日期的日期
SELECT NEXT_DAY('2023-09-20', 'SUNDAY') AS "Next Sunday Date" FROM dual;
-- 结果示例:2023-09-24
-- 从日期中提取年份
SELECT TO_CHAR(SYSDATE, 'YYYY') AS "Current Year" FROM dual;
-- 结果示例:2023
-- 四舍五入到指定的日期部分
SELECT ROUND(SYSDATE, 'HH') AS "Rounded to Hour" FROM dual;
-- 结果示例:2023-09-20 14:00:00
-- 返回日期中的最大值和最小值
SELECT MAX(SYSDATE) AS "Max Date", MIN(SYSDATE) AS "Min Date" FROM dual;
-- 结果示例:
-- Max Date: 2023-09-20
-- Min Date: 2023-09-15