【精选篇】对Oracle与MySQL不同的SQL命令、语法、事务的全面解析

1. 连接到数据库

• Oracle

# sqlplus 用户名/密码@主机IP:端口号/实例名
# sqlplus oracle/123456@192.168.2.1:1521/db_test
sqlplus username/password@database_name

• MySQL

# 本地连接
mysql -u username -p password
# 远程连接
mysql  -u username -p password -h mysql_server_ip_address

2. 获取当前日期和时间

• Oracle

 SELECT SYSDATE FROM DUAL; 

上述代码查询将返回当前日期和时间，以及其他相关的信息。

DUAL 是一个虚拟表，用于在没有实际表的情况下执行查询

可以将此查询嵌套在其他查询中，或者将其分配给变量，以便在存储过程或触发器中使用。
例如，如果要将当前日期和时间插入到表中，可以执行以下操作：

INSERT INTO table_name(date_column) VALUES (SYSDATE);

这将在名为 table_name的表的 date_column 列中插入当前日期和时间。

注意：SYSDATE 返回日期和时间，通常包括年、月、日、时、分、秒以及毫秒级精度，具体格式取决于数据库设置。可以根据需要对其进行格式化或进行其他操作。

• MySQL

SELECT NOW(); 

3. 日期和时间函数

Oracle和MySQL都支持日期和时间函数，但函数名称和语法可能略有不同。

-----------------------Orcale---------------------------
# 获取当前日期和时间
SELECT SYSDATE FROM DUAL;

# 获取当前日期
SELECT TRUNC(SYSDATE) FROM DUAL;

# 获取当前时间
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;

-----------------------MySQL---------------------------
# 获取当前日期和时间
SELECT CURRENT_TIMESTAMP;

# 获取当前日期
SELECT CURRENT_DATE;

# 获取当前时间
SELECT CURRENT_TIME;

4. 字符串连接

• Oracle: 使用 || 操作符来连接字符串，如下：

SELECT 'Hello ' || 'World' FROM DUAL;

• MySQL: 使用CONCAT()函数，如下：‘

 SELECT CONCAT('Hello ', 'World');

5. 自增主键

• Oracle:

（1） 使用序列（Sequence）和触发器（Triggers）来实现自增主键。

① 创建序列（Sequence）：

在Oracle中，序列是一种对象，用于生成唯一的递增或递减的数字。可以使用以下语法创建序列：

# 创建序列（Sequence）
CREATE SEQUENCE sequence_name
	START WITH initial_value
	INCREMENT BY increment_value
	MINVALUE min_value
	MAXVALUE max_value
	NOCACHE;

# sequence_name：序列的名称。
# initial_value：序列的初始值。
# increment_value：序列的递增值。
# min_value：序列的最小值。
# max_value：序列的最大值。
# NOCACHE：不缓存序列值。

② 创建触发器（Trigger）：

触发器是一种数据库对象，用于在表上定义自动执行的操作。可以使用以下语法创建触发器：

CREATE OR REPLACE TRIGGER trigger_name
	BEFORE INSERT ON table_name
	FOR EACH ROW
BEGIN
	SELECT sequence_name.NEXTVAL INTO :new.column_name FROM dual;
END;

# trigger_name：触发器的名称。
# table_name：表的名称。
# column_name：自增主键列的名称。

（2） 从 Oracle 12c开始，引入了IDENTITY列，可以用于自动递增的主键字段。使用 IDENTITY 列可以简化自增字段的实现。

CREATE TABLE table_name (
  primary_key_column NUMBER GENERATED ALWAYS AS IDENTITY,
  -- 其他列定义
);

上述代码在表的列定义中使用 GENERATED ALWAYS AS IDENTITY 来创建一个自增的主键字段。

• MySQL: 使用 AUTO_INCREMENT 关键字来定义自增主键列。

CREATE TABLE table_name (
  `id` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT,
  -- 其他类定义
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

6. 表字段类型的区别

• MySQL支持int、float、double等数值型，varchar、char字符型，date、datetime、time、year、timestamp等日期型。
• Oracle支持number数值型，varchar2、varchar、char字符型，date日期型等。

其中char(2)这样定义，这个单位在Oracle中2代表两个字节，mysql中代表两个字符，其中Varchar在mysql中必须给长度，比如：varchar(10) 。

7. LIMIT和ROWNUM（用于分页）

• Oracle：使用 ROWNUM 或ROWID来进行分页操作，通常需要子查询。
  需要用到伪劣rownum和嵌套查询，Oracle使用rownum字段表明位置，而且只能使用小于，不能使用大于。

假设有一个名为 employees 的表，我们要按照员工的姓名按字母顺序进行分页。以下是示例代码：

SELECT * FROM (
    SELECT e.*, ROWNUM AS rnum
    FROM employees e
    ORDER BY e.last_name
) 
WHERE rnum BETWEEN 11 AND 20;

这个查询的步骤如下：

内部子查询按照姓氏字母顺序对员工进行排序，并为每一行分配一个 ROWNUM。
外部查询选择 ROWNUM 从11到20的行，这就实现了分页，返回了第11到20行的数据。

• MySQL：使用 LIMIT 子句来限制结果集的行数，如下：

SELECT * FROM table_name LIMIT 10;

8. 获取最后插入的ID

• Oracle: 使用 RETURNING INTO 子句来获取最后插入的ID。

# 创建一个表
CREATE TABLE table_name (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    data VARCHAR2(50)
);

# 插入一行数据并获取插入的ID
DECLARE
    last_id NUMBER;
BEGIN
    INSERT INTO table_name (data) VALUES ('Some data')
    RETURNING id INTO last_id;
    DBMS_OUTPUT.PUT_LINE('Last Inserted ID: ' || last_id);
END;
/    # 用于执行之前定义的 PL/SQL 代码块

上述示例代码解释：

① 创建一个表 table_name，其中的 id 列使用 GENERATED ALWAYS AS IDENTITY 来指定自动生成唯一的ID。
② 插入一行数据，并使用RETURNING INTO子句将插入的ID存储在 last_id 变量中。

• MySQL: 使用 LAST_INSERT_ID() 函数来获取最后插入的ID。

# 创建一个表
CREATE TABLE table_name(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(50)
);

# 插入一行数据并获取插入的ID
INSERT INTO example_table (data) VALUES ('Some data');

# 获取最后插入的ID
SELECT LAST_INSERT_ID();

上述示例代码解释：

① 创建一个表 table_name，其中的id列使用AUTO_INCREMENT和PRIMARY KEY来指定自动生成唯一的ID。
② 当插入数据时，MySQL会自动分配下一个可用的ID。要获取最后插入的ID，使用 LAST_INSERT_ID() 函数。

9. 字符串比较

• Oracle默认是大小写的，需要使用 COLLATE 子句来执行大小写不敏感的比较。

-- 大小写敏感的比较（默认）
SELECT name
FROM table_name
WHERE name = 'test';

-- 大小写不敏感的比较（使用 COLLATE）
SELECT name
FROM table_name
WHERE name COLLATE BINARY_CI = 'test';			# BINARY_CI 表示大小写不敏感的比较

• MySQL默认是大小写不敏感的，但可以使用 COLLATE 子句来执行大小写敏感的比较。

-- 大小写不敏感的比较（默认）
SELECT name
FROM table_name
WHERE name = 'test';

-- 大小写敏感的比较（使用 COLLATE）
SELECT name
FROM table_name
WHERE name COLLATE utf8_bin = 'test';		# utf8_bin 是表示大小写敏感的比较

执行大小写不敏感的比较，可以使用 COLLATE 子句，并指定适当的 COLLATION
例如：

• BINARY_CI 是表示大小写不敏感的比较
• utf8_bin 是表示大小写敏感的比较

10. 外键约束

Oracle和MySQL都支持外键约束，但具体语法和操作可能有所不同。

• Oracle:

-- 创建父表
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

-- 创建子表，包含外键约束
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在Oracle中，外键约束通常是在创建表时使用 CONSTRAINT 关键字定义的，可以指定外键的名称（例如，fk_dept）以及外键引用的父表和列。
在上述示例中，employees 表的 department_id 列是一个外键，它引用了 departments 表的 department_id列。

• MySQL

-- 创建父表
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- 创建子表，包含外键约束
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在MySQL中，外键约束可以在创建表时内联到列定义中，不需要额外的 CONSTRAINT 关键字。
在上述示例中，employees 表的 department_id 列是一个外键，它引用了 departments 表的 department_id 列。

11. 事务提交

• Oracle默认不自动提交，需要手动提交，需要在写commit指令，或点击commit按钮。
• MySQL默认是自动提交，可以修改为手动提交。

以下是如何在这两个数据库中执行手动提交和将MySQL设置为手动提交的示例代码：

• Oracle

-- 创建一个示例表
CREATE TABLE table_name(
    id NUMBER PRIMARY KEY,
    data VARCHAR2(50)
);

-- 插入数据并手动提交
BEGIN
    INSERT INTO table_name(id, data) VALUES (1, 'Data 1');
    -- 手动提交事务
    COMMIT;
END;
/

在Oracle中，默认情况下是不自动提交的，需要使用 COMMIT 命令来手动提交事务。在上述示例中，我们插入数据后使用 COMMIT; 来手动提交事务。

• MySQL

# 切换为手动提交模式
SET autocommit = 0;

# 创建一个示例表
CREATE TABLE table_name(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(50)
);

# 插入数据
INSERT INTO table_name(data) VALUES ('Data 1');

# 手动提交事务
COMMIT;

在MySQL中，默认情况下是自动提交的，这意味着每个 SQL 语句都会被立即提交为一个单独的事务。要切换到手动提交模式，可以使用 SET autocommit = 0; 命令。然后，您可以使用 COMMIT; 命令来手动提交事务。

12. 事务隔离级别

• Oracle的默认隔离级别为repeatable read（可重复读）
• MySQL的默认隔离级别为read committed（读已提交）

以下是如何在Oracle和MySQL中设置和演示默认隔离级别的示例代码：

• Oracle

# 查询当前数据库的隔离级别
SELECT s.sid, s.serial#,
       CASE t.xidusn
           WHEN 0 THEN 'Read Committed'
           ELSE 'Serializable'
       END AS isolation_level
  FROM v$session s
       JOIN v$transaction t
       ON s.saddr = t.ses_addr
 WHERE s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

在Oracle中，默认的隔离级别是"Serializable"（可序列化）。上面的示例代码查询了当前会话的隔离级别，如果它为0，那么隔离级别是"Read Committed"（读已提交）。否则，它是"Serializable"（可序列化）。

• MySQL

# 查询当前数据库的隔离级别
SELECT @@global.tx_isolation AS isolation_level;

在MySQL中，默认的隔离级别是"REPEATABLE READ"（可重复读）。上面的示例代码查询了当前数据库的隔离级别，其默认值应为"REPEATABLE READ"。

13. 事务支持

• Oracle完全支持事务

# 启动事务
BEGIN
    # 插入数据
    INSERT INTO example_table (id, data) VALUES (1, 'Data 1');
    INSERT INTO example_table (id, data) VALUES (2, 'Data 2');

    # 提交事务
    COMMIT;
END;

在Oracle中，事务是默认支持的，可以使用 BEGIN 和 COMMIT 块来定义和提交事务，确保一组操作要么全部成功，要么全部失败。
上面的示例演示了一个包含插入操作的事务，如果其中一个插入失败，整个事务将回滚。

• MySQL在Innodb存储引擎的行级锁的情况下才可支持事务。

# 切换到 InnoDB 存储引擎
ALTER TABLE table_name ENGINE = InnoDB;

# 启动事务
START TRANSACTION;

# 插入数据
INSERT INTO table_name (id, data) VALUES (1, 'Data 1');
INSERT INTO table_name (id, data) VALUES (2, 'Data 2');

# 提交事务
COMMIT;

**在MySQL中，事务支持取决于存储引擎。**默认情况下，InnoDB 存储引擎支持事务，而 MyISAM 存储引擎不支持。上面的示例演示了如何使用 InnoDB 存储引擎的行级锁来执行事务。
通过使用 START TRANSACTION 和 COMMIT，可以定义和提交事务。

14. 并发性支持

• Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖于索引。
• MySQL 以表级锁为主，对资源锁定的粒度很大，虽然 InnoDB 引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，那么仍然使用表级锁。
  因此，MySQL为了实现行级锁，通常需要确保表有适当的索引。

所以Oracle对并发性的支持要好很多。