数据库SQL语法教程

Oracle、MySQL/OBMySQL 数据库常用SQL语法教程

目录

1. 基础查询语句
2. 数据操作语句
3. 表结构操作
4. 索引操作
5. 视图操作
6. 存储过程和函数
7. 事务控制
8. 权限管理
9. 数据类型差异
10. 常用函数对比

---

基础查询语句

1. SELECT 查询

Oracle

-- 基本查询
SELECT column1, column2 FROM table_name WHERE condition;

-- 分页查询（Oracle 12c+）
SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT * FROM employees ORDER BY employee_id
    ) a WHERE ROWNUM <= 20
) WHERE rnum > 10;

-- 分页查询（Oracle 12c+ 使用 OFFSET FETCH）
SELECT * FROM employees 
ORDER BY employee_id 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

MySQL/OBMySQL

-- 基本查询
SELECT column1, column2 FROM table_name WHERE condition;

-- 分页查询
SELECT * FROM employees 
ORDER BY employee_id 
LIMIT 10 OFFSET 10;

-- 或者使用简写形式
SELECT * FROM employees 
ORDER BY employee_id 
LIMIT 10, 10;

2. 连接查询

Oracle

-- 内连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 左外连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 右外连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 全外连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

MySQL/OBMySQL

-- 内连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 左外连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 右外连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- MySQL不支持FULL OUTER JOIN，需要使用UNION模拟
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

3. 聚合函数

Oracle

-- 基本聚合
SELECT 
    COUNT(*),
    SUM(salary),
    AVG(salary),
    MAX(salary),
    MIN(salary)
FROM employees;

-- 分组聚合
SELECT 
    department_id,
    COUNT(*) as emp_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- 窗口函数
SELECT 
    employee_id,
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank,
    RANK() OVER (ORDER BY salary DESC) as salary_rank_with_ties,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank,
    LAG(salary, 1) OVER (ORDER BY employee_id) as prev_salary,
    LEAD(salary, 1) OVER (ORDER BY employee_id) as next_salary
FROM employees;

MySQL/OBMySQL

-- 基本聚合
SELECT 
    COUNT(*),
    SUM(salary),
    AVG(salary),
    MAX(salary),
    MIN(salary)
FROM employees;

-- 分组聚合
SELECT 
    department_id,
    COUNT(*) as emp_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- 窗口函数（MySQL 8.0+）
SELECT 
    employee_id,
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank,
    RANK() OVER (ORDER BY salary DESC) as salary_rank_with_ties,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank,
    LAG(salary, 1) OVER (ORDER BY employee_id) as prev_salary,
    LEAD(salary, 1) OVER (ORDER BY employee_id) as next_salary
FROM employees;

---

数据操作语句

1. INSERT 插入

Oracle

-- 单行插入
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', 'john.doe@example.com', SYSDATE);

-- 多行插入
INSERT ALL
    INTO employees VALUES (1002, 'Jane', 'Smith', 'jane.smith@example.com', SYSDATE)
    INTO employees VALUES (1003, 'Bob', 'Johnson', 'bob.johnson@example.com', SYSDATE)
SELECT * FROM dual;

-- 从其他表插入
INSERT INTO employees_backup
SELECT * FROM employees WHERE department_id = 10;

-- 条件插入
INSERT FIRST
    WHEN salary > 5000 THEN INTO high_salary_emp
    WHEN salary > 3000 THEN INTO medium_salary_emp
    ELSE INTO low_salary_emp
SELECT employee_id, first_name, salary FROM employees;

MySQL/OBMySQL

-- 单行插入
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', 'john.doe@example.com', NOW());

-- 多行插入
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES 
    (1002, 'Jane', 'Smith', 'jane.smith@example.com', NOW()),
    (1003, 'Bob', 'Johnson', 'bob.johnson@example.com', NOW());

-- 从其他表插入
INSERT INTO employees_backup
SELECT * FROM employees WHERE department_id = 10;

-- 条件插入（使用CASE WHEN）
INSERT INTO employees (employee_id, first_name, last_name, salary)
SELECT 
    employee_id,
    first_name,
    last_name,
    CASE 
        WHEN salary > 5000 THEN salary
        ELSE 3000
    END as salary
FROM temp_employees;

2. UPDATE 更新

Oracle

-- 基本更新
UPDATE employees 
SET salary = salary * 1.1, 
    last_update = SYSDATE
WHERE department_id = 10;

-- 使用子查询更新
UPDATE employees e
SET salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id
)
WHERE department_id IN (10, 20);

-- 使用MERGE语句（UPSERT）
MERGE INTO employees e
USING (SELECT 1001 as emp_id, 'John' as first_name, 'Doe' as last_name FROM dual) s
ON (e.employee_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.first_name = s.first_name, e.last_name = s.last_name
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name) VALUES (s.emp_id, s.first_name, s.last_name);

MySQL/OBMySQL

-- 基本更新
UPDATE employees 
SET salary = salary * 1.1, 
    last_update = NOW()
WHERE department_id = 10;

-- 使用子查询更新
UPDATE employees e
SET salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id
)
WHERE department_id IN (10, 20);

-- 使用INSERT ... ON DUPLICATE KEY UPDATE（UPSERT）
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (1001, 'John', 'Doe', 'john.doe@example.com')
ON DUPLICATE KEY UPDATE 
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    email = VALUES(email);

3. DELETE 删除

Oracle

-- 基本删除
DELETE FROM employees WHERE department_id = 10;

-- 使用子查询删除
DELETE FROM employees 
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

-- 删除重复记录
DELETE FROM employees e1
WHERE ROWID > (
    SELECT MIN(ROWID) 
    FROM employees e2 
    WHERE e1.employee_id = e2.employee_id
);

MySQL/OBMySQL

-- 基本删除
DELETE FROM employees WHERE department_id = 10;

-- 使用子查询删除
DELETE FROM employees 
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

-- 删除重复记录
DELETE e1 FROM employees e1
INNER JOIN employees e2 
WHERE e1.id > e2.id AND e1.employee_id = e2.employee_id;

---

表结构操作

1. CREATE TABLE 创建表

Oracle

-- 基本表创建
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) UNIQUE NOT NULL,
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4),
    CONSTRAINT emp_salary_min CHECK (salary > 0),
    CONSTRAINT emp_email_uk UNIQUE (email),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 创建表空间
CREATE TABLESPACE emp_data
DATAFILE 'emp_data01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 在指定表空间创建表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20)
) TABLESPACE emp_data;

MySQL/OBMySQL

-- 基本表创建
CREATE TABLE employees (
    employee_id INT(6) PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    email VARCHAR(25) UNIQUE NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    job_id VARCHAR(10) NOT NULL,
    salary DECIMAL(8,2),
    commission_pct DECIMAL(2,2),
    manager_id INT(6),
    department_id INT(4),
    CONSTRAINT emp_salary_min CHECK (salary > 0),
    CONSTRAINT emp_email_uk UNIQUE (email),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建分区表
CREATE TABLE employees_partitioned (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(20),
    hire_date DATE
) PARTITION BY RANGE (YEAR(hire_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2. ALTER TABLE 修改表

Oracle

-- 添加列
ALTER TABLE employees ADD (
    bonus NUMBER(8,2) DEFAULT 0,
    status VARCHAR2(10) DEFAULT 'ACTIVE'
);

-- 修改列
ALTER TABLE employees MODIFY (
    salary NUMBER(10,2),
    email VARCHAR2(50)
);

-- 删除列
ALTER TABLE employees DROP COLUMN bonus;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT emp_salary_max CHECK (salary <= 100000);

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT emp_salary_max;

-- 重命名表
ALTER TABLE employees RENAME TO employees_new;

MySQL/OBMySQL

-- 添加列
ALTER TABLE employees 
ADD COLUMN bonus DECIMAL(8,2) DEFAULT 0,
ADD COLUMN status VARCHAR(10) DEFAULT 'ACTIVE';

-- 修改列
ALTER TABLE employees 
MODIFY COLUMN salary DECIMAL(10,2),
MODIFY COLUMN email VARCHAR(50);

-- 删除列
ALTER TABLE employees DROP COLUMN bonus;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT emp_salary_max CHECK (salary <= 100000);

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT emp_salary_max;

-- 重命名表
ALTER TABLE employees RENAME TO employees_new;
-- 或者
RENAME TABLE employees TO employees_new;

3. DROP TABLE 删除表

Oracle

-- 删除表
DROP TABLE employees;

-- 删除表（如果存在）
DROP TABLE employees CASCADE CONSTRAINTS;

-- 删除表并释放空间
DROP TABLE employees PURGE;

MySQL/OBMySQL

-- 删除表
DROP TABLE employees;

-- 删除表（如果存在）
DROP TABLE IF EXISTS employees;

-- 删除表并释放空间
DROP TABLE employees;

---

索引操作

1. 创建索引

Oracle

-- 创建唯一索引
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- 创建复合索引
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary);

-- 创建函数索引
CREATE INDEX emp_upper_name_idx ON employees(UPPER(first_name));

-- 创建位图索引
CREATE BITMAP INDEX emp_dept_bitmap_idx ON employees(department_id);

-- 创建反向键索引
CREATE INDEX emp_id_reverse_idx ON employees(employee_id) REVERSE;

MySQL/OBMySQL

-- 创建唯一索引
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- 创建复合索引
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary);

-- 创建前缀索引
CREATE INDEX emp_name_prefix_idx ON employees(first_name(10));

-- 创建全文索引
CREATE FULLTEXT INDEX emp_name_fulltext_idx ON employees(first_name, last_name);

-- 创建空间索引
CREATE SPATIAL INDEX location_idx ON locations(coordinates);

2. 管理索引

Oracle

-- 重建索引
ALTER INDEX emp_email_idx REBUILD;

-- 重建索引（在线）
ALTER INDEX emp_email_idx REBUILD ONLINE;

-- 删除索引
DROP INDEX emp_email_idx;

-- 查看索引信息
SELECT index_name, table_name, uniqueness, status 
FROM user_indexes 
WHERE table_name = 'EMPLOYEES';

MySQL/OBMySQL

-- 重建索引
ALTER TABLE employees DROP INDEX emp_email_idx;
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- 分析索引
ANALYZE TABLE employees;

-- 删除索引
DROP INDEX emp_email_idx ON employees;

-- 查看索引信息
SHOW INDEX FROM employees;

---

视图操作

1. 创建视图

Oracle

-- 创建简单视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 创建只读视图
CREATE VIEW emp_readonly_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WITH READ ONLY;

-- 创建强制视图
CREATE FORCE VIEW emp_force_view AS
SELECT * FROM non_existent_table;

-- 创建物化视图
CREATE MATERIALIZED VIEW emp_salary_mv
REFRESH FAST ON COMMIT AS
SELECT department_id, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

MySQL/OBMySQL

-- 创建简单视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 创建只读视图
CREATE VIEW emp_readonly_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- 创建可更新视图
CREATE VIEW emp_updatable_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id IS NOT NULL
WITH CHECK OPTION;

2. 管理视图

Oracle

-- 修改视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 删除视图
DROP VIEW emp_dept_view;

-- 刷新物化视图
EXECUTE DBMS_MVIEW.REFRESH('emp_salary_mv');

MySQL/OBMySQL

-- 修改视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 删除视图
DROP VIEW emp_dept_view;

---

存储过程和函数

1. 存储过程

Oracle

-- 创建存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_employee_id IN NUMBER,
    p_new_salary IN NUMBER,
    p_result OUT VARCHAR2
)
IS
    v_count NUMBER;
BEGIN
    -- 检查员工是否存在
    SELECT COUNT(*) INTO v_count
    FROM employees
    WHERE employee_id = p_employee_id;
    
    IF v_count = 0 THEN
        p_result := 'Employee not found';
        RETURN;
    END IF;
    
    -- 更新工资
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
    
    p_result := 'Salary updated successfully';
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        p_result := 'Error: ' || SQLERRM;
        ROLLBACK;
END;
/

-- 调用存储过程
DECLARE
    v_result VARCHAR2(100);
BEGIN
    update_employee_salary(1001, 5000, v_result);
    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

MySQL/OBMySQL

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE update_employee_salary(
    IN p_employee_id INT,
    IN p_new_salary DECIMAL(8,2),
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result = CONCAT('Error: ', SQLERRM);
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- 检查员工是否存在
    SELECT COUNT(*) INTO v_count
    FROM employees
    WHERE employee_id = p_employee_id;
    
    IF v_count = 0 THEN
        SET p_result = 'Employee not found';
        ROLLBACK;
    ELSE
        -- 更新工资
        UPDATE employees
        SET salary = p_new_salary
        WHERE employee_id = p_employee_id;
        
        SET p_result = 'Salary updated successfully';
        COMMIT;
    END IF;
END //
DELIMITER ;

-- 调用存储过程
CALL update_employee_salary(1001, 5000, @result);
SELECT @result;

2. 函数

Oracle

-- 创建函数
CREATE OR REPLACE FUNCTION get_employee_salary(
    p_employee_id IN NUMBER
) RETURN NUMBER
IS
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    RETURN v_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE;
END;
/

-- 使用函数
SELECT employee_id, first_name, get_employee_salary(employee_id) as salary
FROM employees;

MySQL/OBMySQL

-- 创建函数
DELIMITER //
CREATE FUNCTION get_employee_salary(p_employee_id INT)
RETURNS DECIMAL(8,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_salary DECIMAL(8,2);
    
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    RETURN v_salary;
END //
DELIMITER ;

-- 使用函数
SELECT employee_id, first_name, get_employee_salary(employee_id) as salary
FROM employees;

---

事务控制

1. 事务管理

Oracle

-- 开始事务（隐式）
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
UPDATE departments SET budget = budget * 1.1 WHERE department_id = 10;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT before_salary_update;
UPDATE employees SET salary = salary * 1.2 WHERE department_id = 20;
SAVEPOINT after_salary_update;
UPDATE employees SET salary = salary * 0.9 WHERE department_id = 30;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT after_salary_update;
ROLLBACK TO SAVEPOINT before_salary_update;

MySQL/OBMySQL

-- 开始事务
START TRANSACTION;
-- 或者
BEGIN;

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
UPDATE departments SET budget = budget * 1.1 WHERE department_id = 10;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
START TRANSACTION;
SAVEPOINT before_salary_update;
UPDATE employees SET salary = salary * 1.2 WHERE department_id = 20;
SAVEPOINT after_salary_update;
UPDATE employees SET salary = salary * 0.9 WHERE department_id = 30;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT after_salary_update;
ROLLBACK TO SAVEPOINT before_salary_update;

2. 事务隔离级别

Oracle

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 设置只读事务
SET TRANSACTION READ ONLY;

-- 设置读写事务
SET TRANSACTION READ WRITE;

MySQL/OBMySQL

-- 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

---

权限管理

1. 用户管理

Oracle

-- 创建用户
CREATE USER new_user IDENTIFIED BY password;

-- 修改用户密码
ALTER USER new_user IDENTIFIED BY new_password;

-- 删除用户
DROP USER new_user CASCADE;

-- 锁定/解锁用户
ALTER USER new_user ACCOUNT LOCK;
ALTER USER new_user ACCOUNT UNLOCK;

MySQL/OBMySQL

-- 创建用户
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

-- 修改用户密码
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'new_user'@'localhost';

-- 锁定/解锁用户
ALTER USER 'new_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'new_user'@'localhost' ACCOUNT UNLOCK;

2. 权限管理

Oracle

-- 授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO new_user;
GRANT CREATE SESSION TO new_user;
GRANT CREATE TABLE TO new_user;

-- 授予角色
GRANT CONNECT, RESOURCE TO new_user;

-- 撤销权限
REVOKE SELECT ON employees FROM new_user;
REVOKE CREATE TABLE FROM new_user;

-- 创建角色
CREATE ROLE hr_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO hr_role;
GRANT hr_role TO new_user;

MySQL/OBMySQL

-- 授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO 'new_user'@'localhost';
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';

-- 撤销权限
REVOKE SELECT ON employees FROM 'new_user'@'localhost';
REVOKE ALL PRIVILEGES ON database_name.* FROM 'new_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 创建角色（MySQL 8.0+）
CREATE ROLE 'hr_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO 'hr_role';
GRANT 'hr_role' TO 'new_user'@'localhost';

---

数据类型差异

1. 数值类型

类型	Oracle	MySQL/OBMySQL
整数	NUMBER(10)	INT, BIGINT
小数	NUMBER(10,2)	DECIMAL(10,2)
浮点	BINARY_FLOAT, BINARY_DOUBLE	FLOAT, DOUBLE

2. 字符串类型

类型	Oracle	MySQL/OBMySQL
固定长度	CHAR(10)	CHAR(10)
可变长度	VARCHAR2(255)	VARCHAR(255)
大文本	CLOB	TEXT, LONGTEXT

3. 日期时间类型

类型	Oracle	MySQL/OBMySQL
日期	DATE	DATE
时间	TIMESTAMP	TIME
日期时间	TIMESTAMP	DATETIME, TIMESTAMP

4. 二进制类型

类型	Oracle	MySQL/OBMySQL
二进制	BLOB	BLOB, LONGBLOB
原始数据	RAW(1000)	VARBINARY(1000)

---

常用函数对比

1. 字符串函数

Oracle

-- 字符串连接
SELECT first_name || ' ' || last_name as full_name FROM employees;

-- 字符串长度
SELECT LENGTH(first_name) FROM employees;

-- 大小写转换
SELECT UPPER(first_name), LOWER(last_name) FROM employees;

-- 子字符串
SELECT SUBSTR(first_name, 1, 3) FROM employees;

-- 替换
SELECT REPLACE(email, '@', '[at]') FROM employees;

-- 去除空格
SELECT TRIM(' ' FROM first_name) FROM employees;

MySQL/OBMySQL

-- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;

-- 字符串长度
SELECT LENGTH(first_name) FROM employees;

-- 大小写转换
SELECT UPPER(first_name), LOWER(last_name) FROM employees;

-- 子字符串
SELECT SUBSTRING(first_name, 1, 3) FROM employees;

-- 替换
SELECT REPLACE(email, '@', '[at]') FROM employees;

-- 去除空格
SELECT TRIM(first_name) FROM employees;

2. 数值函数

Oracle

-- 四舍五入
SELECT ROUND(salary, 2) FROM employees;

-- 截断
SELECT TRUNC(salary, 2) FROM employees;

-- 取模
SELECT MOD(salary, 1000) FROM employees;

-- 绝对值
SELECT ABS(salary - 5000) FROM employees;

-- 幂运算
SELECT POWER(salary, 2) FROM employees;

MySQL/OBMySQL

-- 四舍五入
SELECT ROUND(salary, 2) FROM employees;

-- 截断
SELECT TRUNCATE(salary, 2) FROM employees;

-- 取模
SELECT MOD(salary, 1000) FROM employees;

-- 绝对值
SELECT ABS(salary - 5000) FROM employees;

-- 幂运算
SELECT POW(salary, 2) FROM employees;

3. 日期函数

Oracle

-- 当前日期
SELECT SYSDATE FROM dual;

-- 日期加减
SELECT SYSDATE + 7 FROM dual;
SELECT SYSDATE - INTERVAL '1' DAY FROM dual;

-- 日期格式化
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;

-- 字符串转日期
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM dual;

-- 日期差
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) FROM employees;

MySQL/OBMySQL

-- 当前日期
SELECT NOW() FROM dual;

-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) FROM dual;
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY) FROM dual;

-- 日期格式化
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') FROM employees;

-- 字符串转日期
SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d') FROM dual;

-- 日期差
SELECT DATEDIFF(NOW(), hire_date) FROM employees;

---

性能优化技巧

1. 查询优化

Oracle

-- 使用EXISTS代替IN
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.location_id = 1700
);

-- 使用UNION ALL代替UNION（如果不需要去重）
SELECT employee_id, first_name FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department_id = 20;

-- 使用ROWNUM限制结果集
SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT * FROM employees ORDER BY salary DESC
    ) a WHERE ROWNUM <= 10
) WHERE rnum > 0;

MySQL/OBMySQL

-- 使用EXISTS代替IN
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.location_id = 1700
);

-- 使用UNION ALL代替UNION（如果不需要去重）
SELECT employee_id, first_name FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department_id = 20;

-- 使用LIMIT限制结果集
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 10;

2. 索引优化

Oracle

-- 创建复合索引（最左前缀原则）
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary, hire_date);

-- 创建函数索引
CREATE INDEX emp_upper_name_idx ON employees(UPPER(first_name));

-- 分析表统计信息
ANALYZE TABLE employees COMPUTE STATISTICS;

MySQL/OBMySQL

-- 创建复合索引（最左前缀原则）
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary, hire_date);

-- 创建前缀索引
CREATE INDEX emp_name_prefix_idx ON employees(first_name(10));

-- 分析表统计信息
ANALYZE TABLE employees;

---

总结

本教程涵盖了Oracle、MySQL/OBMySQL数据库的常用SQL语法，包括：

1. 基础查询语句：SELECT、JOIN、聚合函数等
2. 数据操作语句：INSERT、UPDATE、DELETE等
3. 表结构操作：CREATE、ALTER、DROP等
4. 索引操作：创建、管理索引
5. 视图操作：创建、管理视图
6. 存储过程和函数：创建、调用
7. 事务控制：事务管理、隔离级别
8. 权限管理：用户、权限管理
9. 数据类型差异：不同数据库的数据类型对比
10. 常用函数对比：字符串、数值、日期函数
11. 性能优化技巧：查询优化、索引优化

主要差异总结：

1. 分页查询：Oracle使用ROWNUM或OFFSET FETCH，MySQL使用LIMIT
2. 字符串连接：Oracle使用||，MySQL使用CONCAT()
3. 日期函数：Oracle使用SYSDATE，MySQL使用NOW()
4. 事务控制：Oracle隐式事务，MySQL需要显式START TRANSACTION
5. 外连接：MySQL不支持FULL OUTER JOIN
6. 数据类型：Oracle使用NUMBER，MySQL使用INT/DECIMAL
7. 存储过程语法：Oracle使用IS/BEGIN/END，MySQL使用BEGIN/END

掌握这些差异和语法，可以帮助您在不同数据库之间进行开发和迁移工作。