SQL 基本语法及数据库对象

---

一、SQL 简介

在实际工作中，可以把常用 SQL 大致分为以下几类：

类型	作用	常见关键字
DDL	定义数据库对象	CREATE、ALTER、DROP、TRUNCATE
DML	操作表中的数据	INSERT、UPDATE、DELETE
DQL	查询数据	SELECT
DCL	权限控制	GRANT、REVOKE
TCL	事务控制	COMMIT、ROLLBACK、SAVEPOINT

在 DM 中执行 SQL 时，建议养成以下几个习惯：

1. 对生产环境中的 UPDATE、DELETE 语句先执行对应的 SELECT，确认影响范围。
2. 涉及批量数据修改时合理使用事务，确认无误后再 COMMIT。
3. 对表、索引、用户、表空间等对象操作前确认当前用户和当前模式。
4. 修改数据库参数前先确认参数属性，是动态参数、静态参数还是只读参数。
5. 生产环境开启 SQL 跟踪日志时要控制记录范围，避免日志量过大影响性能和磁盘空间。

---

二、SQL 基本语法

2.1 查询数据

最基本的查询语法如下：

SELECT 列名
FROM 表名
WHERE 条件
ORDER BY 列名;

例如：

SELECT ID, USER_NAME, CREATE_TIME
FROM TEST_USER
WHERE STATUS = 1
ORDER BY CREATE_TIME DESC;

查询全部列：

SELECT * FROM TEST_USER;

条件查询

SELECT *
FROM TEST_USER
WHERE AGE >= 18
  AND STATUS = 1;

常见条件操作符包括：

=       -- 等于
<>      -- 不等于
>       -- 大于
<       -- 小于
>=      -- 大于等于
<=      -- 小于等于
LIKE    -- 模糊匹配
IN      -- 在指定集合中
BETWEEN -- 范围查询
IS NULL -- 判断空值

例如：

SELECT *
FROM TEST_USER
WHERE USER_NAME LIKE 'ZHANG%';

SELECT *
FROM TEST_USER
WHERE ID IN (1, 2, 3);

SELECT *
FROM TEST_USER
WHERE CREATE_TIME BETWEEN
      '2026-01-01 00:00:00'
      AND '2026-12-31 23:59:59';

---

2.2 插入数据

INSERT INTO TEST_USER
(
    ID,
    USER_NAME,
    AGE,
    STATUS
)
VALUES
(
    1,
    'ZHANGSAN',
    25,
    1
);

---

2.3 更新数据

UPDATE TEST_USER
SET AGE = 26
WHERE ID = 1;

生产环境执行更新前，建议先查询：

SELECT *
FROM TEST_USER
WHERE ID = 1;

确认无误后再执行：

UPDATE TEST_USER
SET AGE = 26
WHERE ID = 1;

COMMIT;

如果发现更新错误，并且事务尚未提交：

ROLLBACK;

---

2.4 删除数据

删除满足条件的数据：

DELETE FROM TEST_USER
WHERE ID = 1;

清空整张表：

DELETE FROM TEST_USER;

或者：

TRUNCATE TABLE TEST_USER;

DELETE 属于 DML，可以配合事务使用；TRUNCATE 更偏向对象级快速清理操作，在生产环境使用前应特别谨慎。

---

2.5 聚合查询

SELECT COUNT(*)
FROM TEST_USER;

SELECT STATUS, COUNT(*) AS CNT
FROM TEST_USER
GROUP BY STATUS;

使用 HAVING 对分组结果过滤：

SELECT STATUS, COUNT(*) AS CNT
FROM TEST_USER
GROUP BY STATUS
HAVING COUNT(*) > 10;

---

2.6 多表连接

INNER JOIN

SELECT
    A.ID,
    A.USER_NAME,
    B.DEPT_NAME
FROM TEST_USER A
JOIN TEST_DEPT B
  ON A.DEPT_ID = B.ID;

LEFT JOIN

SELECT
    A.ID,
    A.USER_NAME,
    B.DEPT_NAME
FROM TEST_USER A
LEFT JOIN TEST_DEPT B
  ON A.DEPT_ID = B.ID;

---

2.7 事务控制

INSERT INTO TEST_USER(ID, USER_NAME)
VALUES(10, 'TEST01');

COMMIT;

回滚：

ROLLBACK;

保存点：

SAVEPOINT SP1;

UPDATE TEST_USER
SET STATUS = 0
WHERE ID = 10;

ROLLBACK TO SP1;

---

三、DM 数据库模式对象

数据库对象可以大致分成模式对象和非模式对象。

模式对象一般归属于某个 Schema，例如：

• 表；
• 视图；
• 索引；
• 序列；
• 存储过程；
• 函数；
• 触发器等。

---

四、表的基本操作

4.1 创建表

CREATE TABLE TEST_USER
(
	ID		INT PRIMARY KEY,
	USER_NAME	VARCHAR(100) NOT NULL,
	AGE		INT,
	STATUS		INT DEFAULT 1,
	CREATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP
);

指定表空间：

CREATE TABLE TEST_USER
(
    ID        INT,
    USER_NAME VARCHAR(100)
)
TABLESPACE MAIN;

---

4.2 查看表结构

在 DM 管理工具中可以直接查看对象结构，也可以通过系统视图查询。

例如查询当前用户的表：

SELECT *
FROM USER_TABLES;

查询指定表的列：

SELECT *
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TEST_USER';

4.3 修改表

增加字段：

ALTER TABLE TEST_USER
ADD EMAIL VARCHAR(200);

修改字段：

ALTER TABLE TEST_USER
MODIFY EMAIL VARCHAR(300);

删除字段：

ALTER TABLE TEST_USER
DROP COLUMN EMAIL;

增加主键：

ALTER TABLE TEST_USER
ADD CONSTRAINT PK_TEST_USER
PRIMARY KEY(ID);

增加唯一约束：

ALTER TABLE TEST_USER
ADD CONSTRAINT UK_TEST_USER_NAME
UNIQUE(USER_NAME);

---

4.4 删除表

DROP TABLE TEST_USER;

操作前应确认对象是否仍被视图、存储过程、外键或业务程序依赖。

---

五、视图的基本操作

视图可以理解为保存起来的查询定义。视图本身通常不单独保存业务数据，而是通过查询基表得到结果。

5.1 创建视图

CREATE VIEW V_TEST_USER AS
SELECT
    ID,
    USER_NAME,
    STATUS
FROM TEST_USER
WHERE STATUS = 1;

DM 还支持 CREATE OR REPLACE VIEW：

CREATE OR REPLACE VIEW V_TEST_USER AS
SELECT
    ID,
    USER_NAME,
    AGE,
    STATUS
FROM TEST_USER
WHERE STATUS = 1;

---

5.2 查询视图

SELECT *
FROM V_TEST_USER;

---

5.3 创建只读视图

CREATE VIEW V_TEST_USER_READONLY AS
SELECT
    ID,
    USER_NAME
FROM TEST_USER
WITH READ ONLY;

---

5.4 删除视图

DROP VIEW V_TEST_USER;

视图常用于：

• 简化复杂查询；
• 对业务层隐藏底层表结构；
• 限制用户可访问的列；
• 封装固定查询逻辑；
• 兼容旧系统的数据访问接口。

---

六、索引的基本操作

索引的主要作用是提高数据检索效率，但索引并不是越多越好。索引会占用存储空间，同时会增加 INSERT、UPDATE、DELETE 的维护成本。

6.1 创建普通索引

CREATE INDEX IDX_TEST_USER_NAME
ON TEST_USER(USER_NAME);

---

6.2 创建联合索引

CREATE INDEX IDX_TEST_USER_STATUS_TIME
ON TEST_USER(STATUS, CREATE_TIME);

联合索引字段顺序十分重要，应结合实际 SQL 的过滤条件、选择性和执行计划设计。

---

6.3 创建唯一索引

CREATE UNIQUE INDEX UK_IDX_TEST_USER_NAME
ON TEST_USER(USER_NAME);

---

6.4 删除索引

DROP INDEX IDX_TEST_USER_NAME;

---

6.5 查看索引

SELECT *
FROM USER_INDEXES
WHERE TABLE_NAME = 'TEST_USER';

索引列信息可以结合相关系统视图进一步查询。

日常优化中，不要看到慢 SQL 就直接创建索引。建议先看执行计划，再结合过滤条件、返回数据量、表数据分布和已有索引进行判断。

---

七、DM 非模式对象

常见非模式对象主要包括：

• 用户；
• 表空间；
• 角色；
• 权限等。

这些对象更多涉及数据库安全、资源规划和账号管理。

---

八、用户管理

8.1 创建用户

CREATE USER APP_USER
IDENTIFIED BY "Dm@123456";

可以指定默认表空间：

CREATE USER APP_USER
IDENTIFIED BY "Dm@123456"
DEFAULT TABLESPACE APP_DATA;

---

8.2 修改用户密码

ALTER USER APP_USER
IDENTIFIED BY "NewDm@123456";

---

8.3 锁定用户

ALTER USER APP_USER ACCOUNT LOCK;

解锁：

ALTER USER APP_USER ACCOUNT UNLOCK;

---

8.4 删除用户

DROP USER APP_USER;

如果用户下存在对象，删除前要确认对象处理策略，避免误删业务对象。

---

九、表空间管理

表空间是数据库逻辑存储管理的重要组成部分，底层由数据文件提供实际存储空间。

9.1 创建表空间

示例：

CREATE TABLESPACE APP_DATA
DATAFILE 'D:\dmdbms\data\DAMENG\APP_DATA01.DBF'
SIZE 1024;

实际语法和数据文件扩展策略应根据数据库版本及生产规划设置。

---

9.2 查询表空间

SELECT *
FROM V$TABLESPACE;

查询数据文件：

SELECT *
FROM V$DATAFILE;

---

9.3 扩展表空间

可以根据环境增加数据文件或扩展已有数据文件。

生产环境需要重点关注：

• 表空间总大小；
• 已使用空间；
• 剩余空间；
• 数据文件增长策略；
• 文件系统剩余容量；
• 是否存在单文件过大风险。

---

十、角色管理

角色可以理解为一组权限的集合。相比直接给每个用户逐项授权，通过角色管理权限更加清晰。

10.1 创建角色

CREATE ROLE APP_ROLE;

---

10.2 给角色授权

GRANT SELECT ON SYSDBA.TEST_USER
TO APP_ROLE;

GRANT INSERT, UPDATE, DELETE
ON SYSDBA.TEST_USER
TO APP_ROLE;

---

10.3 把角色授予用户

GRANT APP_ROLE
TO APP_USER;

---

10.4 回收角色

REVOKE APP_ROLE
FROM APP_USER;

---

10.5 删除角色

DROP ROLE APP_ROLE;

---

十一、权限管理

DM 中常见权限可以按数据库权限、对象权限、角色权限、模式权限等进行管理。

11.1 对象授权

GRANT SELECT
ON SYSDBA.TEST_USER
TO APP_USER;

多个权限：

GRANT SELECT, INSERT, UPDATE, DELETE
ON SYSDBA.TEST_USER
TO APP_USER;

---

11.2 回收权限

REVOKE DELETE
ON SYSDBA.TEST_USER
FROM APP_USER;

---

11.3 授权原则

生产环境建议遵循最小权限原则：

1. 应用账号只授予业务所需权限；
2. 不建议应用长期使用高权限管理员账号；
3. 查询账号和写入账号可以根据安全要求拆分；
4. 批处理程序使用单独账号；
5. 权限优先通过角色统一管理；
6. 定期检查长期不用的账号、角色和授权。