数据库必看--浅谈Oracle与MySQL的SQL语句区别

Oracle和MySQL是两种广泛使用的数据库管理系统，它们各自在语法、功能以及使用场景上存在差异。这些差异在编写SQL语句时尤为明显。本文将详细探讨Oracle与MySQL在数据库操作、表操作、列操作以及数据查询等方面的主要区别。

一、数据库操作

在数据库创建与管理方面，Oracle和MySQL的SQL语句存在显著区别。

• 创建数据库：MySQL允许通过CREATE DATABASE语句来创建数据库，并且可以指定字符集，如CREATE DATABASE j0815_1 CHARACTER SET utf8;。然而，Oracle并不直接支持创建数据库的SQL语句，而是通过创建实例来管理数据库。

MySQL 创建数据库：

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• 查看与删除数据库：MySQL中，可以使用SHOW DATABASES;来查看所有数据库，使用DROP DATABASE来删除数据库。而在Oracle中，这些操作更多是通过数据库管理工具或命令行工具进行，而不是直接的SQL语句。

二、表操作

在表管理方面，Oracle和MySQL在数据类型、自增长字段以及表结构的修改等方面存在诸多不同。

• 数据类型：MySQL支持VARCHAR、INT、DOUBLE等数据类型，而Oracle则使用VARCHAR2代替VARCHAR，使用NUMBER代替INT和DOUBLE，且小数类型默认为FLOAT。
• 自增长字段：MySQL中可以使用AUTO_INCREMENT来声明自增长字段，但在Oracle中，主键自带自增长功能，无需显式声明。
• 添加、删除列：在添加和删除列时，Oracle和MySQL的语法有所不同。例如，Oracle在添加多列时需要使用括号包围所有列定义，如ALTER TABLE emp ADD (lovename VARCHAR2(50), loveage INT);，而MySQL则允许在单个ALTER TABLE语句中重复使用ADD COLUMN。

MySQL 创建表示例：

CREATE TABLE users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    username VARCHAR(50) NOT NULL,  
    email VARCHAR(100)  
);

Oracle 创建表（使用序列和触发器实现自增长）示例：

CREATE TABLE users (  
    id NUMBER PRIMARY KEY,  
    username VARCHAR2(50) NOT NULL,  
    email VARCHAR2(100)  
);  
  
-- 创建一个序列  
CREATE SEQUENCE users_seq START WITH 1 INCREMENT BY 1;  
  
-- 创建一个触发器来自动填充ID  
CREATE OR REPLACE TRIGGER users_trg  
BEFORE INSERT ON users  
FOR EACH ROW  
BEGIN  
  SELECT users_seq.NEXTVAL  
  INTO   :NEW.id  
  FROM   dual;  
END;  
/

三、列操作

对于列的操作，如修改列名、修改列类型等，Oracle和MySQL也有不同的语法规则。

• 修改列名：MySQL使用ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新数据类型;来修改列名和数据类型，而Oracle则使用ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;来修改列名，并通过ALTER TABLE 表名 MODIFY (列名 新数据类型);来修改数据类型（注意，Oracle在列有数据时无法直接修改列类型）。
• 修改列类型：MySQL中无论列是否有数据都可以修改列类型，但Oracle在列有数据时无法直接修改类型，需要通过添加临时列、转换数据后删除原列等方式进行。

四、数据查询与操作

在数据查询和操作方面，Oracle和MySQL在分页查询、模糊查询等方面也存在差异。

• 分页查询：MySQL支持直接使用LIMIT语句进行分页查询，如SELECT * FROM 表名 LIMIT x, y;。而Oracle则需要使用伪列ROWNUM和嵌套查询来实现分页功能。
• 模糊查询：在进行模糊查询时，MySQL可以使用CONCAT('%', 变量, '%')，而Oracle则使用'%' || 变量 || '%'进行字符串拼接。

MySQL 分页查询：

SELECT * FROM users LIMIT 10 OFFSET 20; -- 跳过前20条记录，查询接下来的10条

Oracle 分页查询：

SELECT * FROM (  
    SELECT u.*, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM users u  
) WHERE rn BETWEEN 21 AND 30; -- 跳过前20条，查询第21到30条

五、总结

Oracle和MySQL在SQL语句上的区别主要体现在数据库操作、表操作、列操作以及数据查询等多个方面。这些差异要求开发者和数据库管理员在编写SQL语句时，需要根据所使用的数据库系统选择合适的语法和规则。通过深入理解这些差异，可以更有效地进行数据库管理和应用开发。