Oracle基础语法 #

SQL语句分类
├── DDL（数据定义语言）
│   ├── CREATE - 创建对象
│   ├── ALTER - 修改对象
│   ├── DROP - 删除对象
│   ├── TRUNCATE - 截断表
│   └── RENAME - 重命名
├── DML（数据操作语言）
│   ├── SELECT - 查询数据
│   ├── INSERT - 插入数据
│   ├── UPDATE - 更新数据
│   └── DELETE - 删除数据
├── DCL（数据控制语言）
│   ├── GRANT - 授予权限
│   └── REVOKE - 撤销权限
├── TCL（事务控制语言）
│   ├── COMMIT - 提交事务
│   ├── ROLLBACK - 回滚事务
│   └── SAVEPOINT - 设置保存点
└── DQL（数据查询语言）
    └── SELECT - 查询语句

-- SQL语句以分号结尾
SELECT * FROM employees;

-- PL/SQL块以斜杠结尾
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello Oracle');
END;
/

-- 这是单行注释
SELECT employee_id, last_name FROM employees;

SELECT employee_id,    -- 员工ID
       last_name       -- 姓氏
FROM employees;

/*
 * 这是多行注释
 * 可以跨越多行
 * 用于详细说明
 */
SELECT * FROM employees;

/*
    查询所有员工信息
    包括员工ID、姓名、部门
*/
SELECT employee_id, first_name, last_name, department_id
FROM employees;

-- 好的注释示例

-- 查询薪资大于10000的员工
-- 按部门分组统计人数和平均薪资
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE salary > 10000
GROUP BY department_id
ORDER BY avg_salary DESC;

-- 避免无意义的注释
SELECT * FROM employees;  -- 查询所有员工（这种注释是多余的）

标识符命名规则：
├── 必须以字母开头
├── 可包含字母、数字、下划线(_)、美元符号($)
├── 长度不超过30个字符（12c R2后支持128字符）
├── 不能是Oracle保留字
├── 不区分大小写（默认）
└── 不能包含空格或特殊字符

-- 合法的标识符
employees
employee_id
first_name
salary$
dept_2024

-- 不合法的标识符
123employees    -- 以数字开头
employee-id     -- 包含连字符
employee name   -- 包含空格
select          -- 保留字

-- 使用双引号可以突破命名规则

-- 包含特殊字符
CREATE TABLE "my table" (
    id NUMBER
);

-- 区分大小写
CREATE TABLE "Employees" (
    id NUMBER
);

-- 使用保留字
CREATE TABLE "table" (
    id NUMBER
);

-- 查询时也需要使用双引号
SELECT * FROM "my table";
SELECT * FROM "Employees";  -- 必须完全匹配大小写

-- 常见保留字（不能用作标识符）
SELECT, FROM, WHERE, AND, OR, NOT
INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
TABLE, INDEX, VIEW, PROCEDURE, FUNCTION
NUMBER, VARCHAR2, DATE, TIMESTAMP
PRIMARY, FOREIGN, KEY, UNIQUE, CHECK
GRANT, REVOKE, COMMIT, ROLLBACK

-- 查看所有保留字
SELECT * FROM V$RESERVED_WORDS ORDER BY KEYWORD;

-- 查看是否为保留字
SELECT KEYWORD, RESERVED 
FROM V$RESERVED_WORDS 
WHERE KEYWORD = 'SELECT';

-- 不推荐：使用保留字作为列名
CREATE TABLE test (
    select NUMBER,    -- 不推荐
    from VARCHAR2(50) -- 不推荐
);

-- 推荐：添加前缀或后缀
CREATE TABLE test (
    select_type NUMBER,
    from_location VARCHAR2(50)
);

-- 如果必须使用，需要双引号
CREATE TABLE test (
    "SELECT" NUMBER,
    "FROM" VARCHAR2(50)
);

-- 表名使用复数形式
employees      -- 推荐
employee       -- 不推荐

-- 使用下划线分隔单词
order_items    -- 推荐
OrderItems     -- 不推荐
order-items    -- 错误

-- 添加业务前缀
hr_employees
fin_accounts
inv_products

-- 示例
CREATE TABLE hr_employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- 主键命名：表名_id 或 id
employee_id
id

-- 外键命名：关联表名_id
department_id
manager_id

-- 布尔类型：is_xxx 或 has_xxx
is_active
is_deleted
has_permission

-- 日期类型：xxx_date 或 xxx_time
create_date
update_time
hire_date

-- 示例
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER(10,2),
    is_paid NUMBER(1) DEFAULT 0,
    create_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 索引命名：idx_表名_列名
CREATE INDEX idx_employees_last_name ON employees(last_name);

-- 唯一索引：uk_表名_列名
CREATE UNIQUE INDEX uk_employees_email ON employees(email);

-- 复合索引：idx_表名_列名1_列名2
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 主键索引：pk_表名
ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

-- 外键索引：fk_表名_关联表名
ALTER TABLE employees ADD CONSTRAINT fk_employees_departments 
FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 序列：seq_表名 或 seq_表名_id
CREATE SEQUENCE seq_employees START WITH 1 INCREMENT BY 1;

-- 视图：v_视图名
CREATE VIEW v_employee_details AS
SELECT * FROM employees;

-- 存储过程：sp_过程名 或 proc_过程名
CREATE PROCEDURE sp_update_salary(p_emp_id NUMBER) AS
BEGIN
    NULL;
END;
/

-- 函数：fn_函数名 或 func_函数名
CREATE FUNCTION fn_get_salary(p_emp_id NUMBER) RETURN NUMBER AS
BEGIN
    RETURN 0;
END;
/

-- 触发器：trg_表名_操作
CREATE TRIGGER trg_employees_insert
BEFORE INSERT ON employees
BEGIN
    NULL;
END;
/

-- 包：pkg_包名
CREATE PACKAGE pkg_employee AS
    PROCEDURE update_salary(p_emp_id NUMBER);
END;
/

-- 数值类型
NUMBER          -- 任意精度数值
NUMBER(p)       -- 整数，p位精度
NUMBER(p,s)     -- 定点数，p位精度，s位小数
INTEGER         -- 整数
FLOAT           -- 浮点数

-- 字符类型
CHAR(n)         -- 定长字符串
VARCHAR2(n)     -- 变长字符串
NCHAR(n)        -- 定长Unicode字符串
NVARCHAR2(n)    -- 变长Unicode字符串
CLOB            -- 大文本

-- 日期时间类型
DATE            -- 日期时间
TIMESTAMP       -- 时间戳
TIMESTAMP WITH TIME ZONE  -- 带时区时间戳

-- 其他类型
BLOB            -- 二进制大对象
XMLTYPE         -- XML数据
JSON            -- JSON数据（21c+）

CREATE TABLE data_type_examples (
    -- 数值类型
    id NUMBER PRIMARY KEY,
    age NUMBER(3),
    salary NUMBER(10,2),
    
    -- 字符类型
    code CHAR(10),
    name VARCHAR2(100),
    description CLOB,
    
    -- 日期类型
    birth_date DATE,
    create_time TIMESTAMP,
    
    -- 其他类型
    photo BLOB
);

-- 加减乘除
SELECT 10 + 5 FROM DUAL;    -- 15
SELECT 10 - 5 FROM DUAL;    -- 5
SELECT 10 * 5 FROM DUAL;    -- 50
SELECT 10 / 5 FROM DUAL;    -- 2

-- 实际应用
SELECT 
    employee_id,
    salary,
    salary * 12 AS annual_salary,
    salary + 1000 AS new_salary
FROM employees;

-- 等于、不等于
SELECT * FROM employees WHERE salary = 10000;
SELECT * FROM employees WHERE salary != 10000;
SELECT * FROM employees WHERE salary <> 10000;

-- 大于、小于
SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE salary < 10000;
SELECT * FROM employees WHERE salary >= 10000;
SELECT * FROM employees WHERE salary <= 10000;

-- BETWEEN...AND
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

-- IN
SELECT * FROM employees WHERE department_id IN (10, 20, 30);

-- LIKE
SELECT * FROM employees WHERE last_name LIKE 'S%';

-- IS NULL
SELECT * FROM employees WHERE manager_id IS NULL;

-- AND
SELECT * FROM employees 
WHERE salary > 10000 AND department_id = 10;

-- OR
SELECT * FROM employees 
WHERE salary > 10000 OR department_id = 10;

-- NOT
SELECT * FROM employees 
WHERE NOT (salary > 10000);

-- 组合使用
SELECT * FROM employees 
WHERE (salary > 10000 OR department_id = 10)
AND hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');

-- 使用 || 连接字符串
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

-- 使用 CONCAT 函数
SELECT CONCAT(first_name, last_name) AS full_name
FROM employees;

-- 多个字符串连接
SELECT first_name || ' ' || last_name || ' - ' || job_id AS employee_info
FROM employees;

-- NULL表示未知或缺失的值
-- NULL不等于0或空字符串

-- NULL参与运算结果为NULL
SELECT NULL + 10 FROM DUAL;      -- NULL
SELECT NULL * 10 FROM DUAL;      -- NULL
SELECT NULL || 'text' FROM DUAL; -- text（字符串连接例外）

-- NULL比较
SELECT * FROM employees WHERE manager_id = NULL;   -- 错误，不会返回结果
SELECT * FROM employees WHERE manager_id IS NULL;  -- 正确

-- NVL函数：如果为NULL则返回默认值
SELECT NVL(commission_pct, 0) FROM employees;

-- NVL2函数：根据是否为NULL返回不同值
SELECT NVL2(commission_pct, '有提成', '无提成') FROM employees;

-- COALESCE函数：返回第一个非NULL值
SELECT COALESCE(manager_id, department_id, 0) FROM employees;

-- NULLIF函数：如果相等则返回NULL
SELECT NULLIF(salary, 0) FROM employees;

-- LNNVL函数：条件取反（包含NULL）
SELECT * FROM employees WHERE LNNVL(salary > 10000);

-- DUAL是Oracle提供的单行单列表
-- 常用于计算、获取系统信息等

SELECT * FROM DUAL;

D
-
X

-- 计算表达式
SELECT 1 + 1 FROM DUAL;

-- 获取系统日期
SELECT SYSDATE FROM DUAL;

-- 获取当前用户
SELECT USER FROM DUAL;

-- 获取序列值
SELECT seq_employees.NEXTVAL FROM DUAL;

-- 调用函数
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 类型转换测试
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;

-- 关键字大写
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 10000
ORDER BY salary DESC;

-- 缩进对齐
SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    department_id
FROM 
    employees
WHERE 
    salary > 10000
    AND department_id IN (10, 20, 30)
ORDER BY 
    salary DESC,
    employee_id ASC;

-- 每个子句独占一行
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000
ORDER BY e.salary DESC;

-- 使用表别名
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;

-- 使用列别名
SELECT 
    employee_id AS "员工ID",
    first_name || ' ' || last_name AS "姓名",
    salary AS "薪资"
FROM employees;

-- 复杂条件使用括号
SELECT *
FROM employees
WHERE (department_id = 10 OR department_id = 20)
AND salary > (SELECT AVG(salary) FROM employees);