印刻数据库

外观

Oracle SQL基础 ​

SQL（Structured Query Language）是用于管理关系型数据库的标准语言，Oracle数据库完全支持ANSI SQL标准，并扩展了许多专有特性。本文将介绍Oracle SQL的基础知识，包括SQL分类、基本语法、数据类型和常用操作。

SQL概述 ​

SQL分类 ​

根据功能不同，SQL可以分为以下几类：

数据查询语言（DQL） ​

用于查询数据库中的数据，主要是SELECT语句。

数据操纵语言（DML） ​

用于修改数据库中的数据，包括INSERT、UPDATE、DELETE语句。

数据定义语言（DDL） ​

用于定义数据库结构，包括CREATE、ALTER、DROP、TRUNCATE语句。

数据控制语言（DCL） ​

用于控制数据库访问权限，包括GRANT、REVOKE语句。

事务控制语言（TCL） ​

用于管理数据库事务，包括COMMIT、ROLLBACK、SAVEPOINT语句。

Oracle SQL特点 ​

  • 支持ANSI SQL标准
  • 提供丰富的扩展功能
  • 支持复杂查询和分析
  • 支持多种数据类型
  • 支持PL/SQL编程语言

数据类型 ​

Oracle支持多种数据类型，用于存储不同类型的数据。

数值类型 ​

数据类型描述示例
NUMBER(p,s)数值类型，p为精度（最多38位），s为小数位数NUMBER(10,2) 表示最大99999999.99
INTEGER整数类型（NUMBER的子类）INTEGER 表示整数
FLOAT浮点数类型FLOAT(126) 表示双精度浮点数

字符类型 ​

数据类型描述示例
CHAR(n)固定长度字符，n为长度（1-2000）CHAR(10) 表示10个字符的固定长度字符串
VARCHAR2(n)可变长度字符，n为最大长度（1-4000）VARCHAR2(100) 表示最大100个字符的可变长度字符串
NVARCHAR2(n)支持Unicode的可变长度字符NVARCHAR2(100) 表示最大100个Unicode字符
CLOB字符大对象，用于存储大量文本（最多4GB）CLOB 用于存储长文本

日期和时间类型 ​

数据类型描述示例
DATE日期和时间，包含世纪、年、月、日、时、分、秒SYSDATE 返回当前日期和时间
TIMESTAMP包含小数秒的日期和时间SYSTIMESTAMP 返回当前时间戳
INTERVAL YEAR TO MONTH表示年和月的间隔INTERVAL '1-3' YEAR TO MONTH 表示1年3个月
INTERVAL DAY TO SECOND表示日、小时、分钟和秒的间隔INTERVAL '2 12:30:45' DAY TO SECOND 表示2天12小时30分钟45秒

二进制类型 ​

数据类型描述示例
BLOB二进制大对象，用于存储二进制数据（最多4GB）BLOB 用于存储图片、音频、视频等
RAW(n)可变长度二进制数据，n为最大长度（1-2000）RAW(100) 表示最大100字节的二进制数据
LONG RAW可变长度二进制数据（最多2GB），已被BLOB替代不推荐使用

数据查询语言（DQL） ​

SELECT语句基本语法 ​

sql
SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...];

常用查询示例 ​

1. 基本查询 ​

sql
-- 查询所有员工信息
SELECT * FROM employees;

-- 查询员工姓名和薪资
SELECT first_name, last_name, salary FROM employees;

2. 条件查询 ​

sql
-- 查询薪资大于5000的员工
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 5000;

-- 查询部门ID为10且薪资大于5000的员工
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 10 AND salary > 5000;

3. 排序查询 ​

sql
-- 按薪资降序排序
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

-- 先按部门ID升序，再按薪资降序排序
SELECT first_name, last_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

4. 聚合查询 ​

sql
-- 计算员工总数
SELECT COUNT(*) AS total_employees FROM employees;

-- 计算平均薪资
SELECT AVG(salary) AS avg_salary FROM employees;

-- 计算每个部门的平均薪资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 计算平均薪资大于6000的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;

5. 连接查询 ​

sql
-- 内连接：查询员工姓名和所属部门名称
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 左连接：查询所有员工及其所属部门名称（包括没有部门的员工）
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 右连接：查询所有部门及其员工（包括没有员工的部门）
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

数据操纵语言（DML） ​

INSERT语句 ​

用于向表中插入数据。

sql
-- 插入完整行
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
VALUES (207, 'John', 'Doe', 'johndoe@example.com', SYSDATE, 'IT_PROG', 6000, 60);

-- 插入部分列（其他列为NULL）
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (208, 'Jane', 'Smith', 'janesmith@example.com', SYSDATE, 'IT_PROG');

-- 从另一个表插入数据
INSERT INTO employees_backup (employee_id, first_name, last_name, email)
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE department_id = 60;

UPDATE语句 ​

用于更新表中的数据。

sql
-- 更新单个列
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 207;

-- 更新多个列
UPDATE employees
SET salary = salary * 1.1, job_id = 'IT_MANAGER'
WHERE employee_id = 207;

-- 更新所有行（谨慎使用）
UPDATE employees
SET last_name = UPPER(last_name);

DELETE语句 ​

用于删除表中的数据。

sql
-- 删除单行
DELETE FROM employees
WHERE employee_id = 207;

-- 删除符合条件的多行
DELETE FROM employees
WHERE department_id = 60 AND salary < 5000;

-- 删除所有行（谨慎使用）
DELETE FROM employees_backup;

数据定义语言（DDL） ​

CREATE语句 ​

用于创建数据库对象，如表、视图、索引等。

sql
-- 创建表
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id NUMBER(6),
    location_id NUMBER(4)
);

-- 创建索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

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

ALTER语句 ​

用于修改数据库对象的结构。

sql
-- 添加列
ALTER TABLE employees
ADD phone_number VARCHAR2(20);

-- 修改列类型
ALTER TABLE employees
MODIFY phone_number VARCHAR2(30);

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

-- 添加约束
ALTER TABLE employees
ADD CONSTRAINT emp_email_uk UNIQUE(email);

DROP语句 ​

用于删除数据库对象。

sql
-- 删除表
DROP TABLE employees_backup;

-- 删除索引
DROP INDEX emp_last_name_idx;

-- 删除视图
DROP VIEW emp_dept_view;

TRUNCATE语句 ​

用于删除表中的所有数据，但保留表结构，比DELETE语句更高效。

sql
-- 清空表数据
TRUNCATE TABLE employees_backup;

数据控制语言（DCL） ​

GRANT语句 ​

用于授予用户权限。

sql
-- 授予SELECT权限
GRANT SELECT ON employees TO scott;

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON employees TO scott;

-- 授予权限并允许传播
GRANT SELECT ON employees TO scott WITH GRANT OPTION;

REVOKE语句 ​

用于撤销用户权限。

sql
-- 撤销INSERT和UPDATE权限
REVOKE INSERT, UPDATE ON employees FROM scott;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON employees FROM scott;

事务控制语言（TCL） ​

COMMIT语句 ​

用于提交事务，将修改永久保存到数据库。

sql
-- 提交事务
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (209, 'Mike', 'Johnson', 'mikejohnson@example.com', SYSDATE, 'IT_PROG');
COMMIT;

ROLLBACK语句 ​

用于回滚事务，撤销未提交的修改。

sql
-- 回滚事务
UPDATE employees
SET salary = salary * 2;
ROLLBACK;

SAVEPOINT语句 ​

用于创建保存点，可以回滚到特定的保存点。

sql
-- 使用保存点
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (210, 'Sarah', 'Williams', 'sarahwilliams@example.com', SYSDATE, 'IT_PROG');
SAVEPOINT sp1;

UPDATE employees
SET salary = salary * 1.5
WHERE employee_id = 210;
SAVEPOINT sp2;

-- 回滚到保存点sp1
ROLLBACK TO sp1;

-- 提交事务
COMMIT;

版本差异 ​

Oracle 12c ​

  • 引入了新的数据类型：JSON
  • 支持IDENTITY列（自动递增）
  • 支持LIMIT子句（用于限制结果集）

Oracle 18c ​

  • 增强了JSON支持
  • 支持DEFAULT ON NULL子句

Oracle 19c ​

  • 长期支持版本
  • 增强了SQL性能
  • 支持更多的ANSI SQL标准

Oracle 21c ​

  • 支持FETCH FIRST n ROWS ONLY语法（ANSI标准）
  • 增强了JSON功能
  • 支持MERGE INTO语句的增强功能

常见问题（FAQ） ​

Q: SELECT * 和 SELECT 列名 有什么区别？ ​

A: SELECT * 会返回表中的所有列，而SELECT 列名 只返回指定的列。在生产环境中，建议使用SELECT 列名，因为：

  • 可以减少网络传输的数据量
  • 可以避免因表结构变化导致的应用程序错误
  • 可以提高查询性能

Q: INSERT INTO table VALUES(...) 和 INSERT INTO table(column1, column2, ...) VALUES(...) 有什么区别？ ​

A: 第一种语法依赖于表的列顺序，如果表结构发生变化（如添加或删除列），语句可能会失败。第二种语法指定了列名，更加安全和灵活，推荐使用。

Q: DELETE 和 TRUNCATE 有什么区别？ ​

A:

  • DELETE是DML语句，可以回滚，会记录日志，逐行删除数据
  • TRUNCATE是DDL语句，不能回滚，只记录少量日志，效率更高
  • DELETE可以使用WHERE子句删除部分数据，TRUNCATE只能删除所有数据
  • TRUNCATE会重置表的高水位线，释放存储空间

Q: 什么是事务？事务的ACID特性是什么？ ​

A: 事务是一组逻辑上相关的SQL语句，要么全部执行成功，要么全部失败。事务的ACID特性包括：

  • 原子性（Atomicity）：事务是一个不可分割的工作单位
  • 一致性（Consistency）：事务执行前后，数据库状态保持一致
  • 隔离性（Isolation）：多个事务并发执行时，彼此隔离
  • 持久性（Durability）：事务提交后，修改永久保存到数据库

Q: 如何查看表的结构？ ​

A: 可以使用以下命令查看表结构：

sql
DESCRIBE employees;
-- 或
SELECT column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

Q: 如何查找表中的重复数据？ ​

A: 可以使用GROUP BY和HAVING子句查找重复数据：

sql
SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

Q: 如何在Oracle中实现分页查询？ ​

A: 在Oracle 12c之前，可以使用ROWNUM实现分页：

sql
SELECT * FROM (
    SELECT e.*, ROWNUM rn
    FROM employees e
    WHERE ROWNUM <= 20
) WHERE rn > 10;

在Oracle 12c及以后，可以使用FETCH子句：

sql
SELECT * FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

总结 ​

Oracle SQL是管理和操作Oracle数据库的核心语言，掌握SQL基础知识对于Oracle开发和运维至关重要。本文介绍了SQL的分类、基本语法、常用操作和版本差异，涵盖了数据查询、操纵、定义、控制和事务控制等方面。

在实际生产环境中，应注意：

  • 遵循SQL编写规范，提高代码可读性和可维护性
  • 优化SQL语句，提高查询性能
  • 谨慎使用DML和DDL语句，避免误操作
  • 合理使用事务，确保数据一致性
  • 定期备份数据，防止数据丢失

随着Oracle版本的更新，SQL功能不断增强，如JSON支持、IDENTITY列、FETCH子句等，开发人员应关注新版本的特性，提高开发效率和性能。