orcale 常用语句

Oracle数据库的常用语句可以分为几个主要类别，包括数据定义语言（DDL）、数据操纵语言（DML）、事务控制语言（TCL）、数据查询语言（DQL）和数据控制语言（DCL）。下面将分别介绍这些类别中的常用语句。

一、数据定义语言（DDL）

DDL用于定义或修改数据库的结构，主要包括以下几种语句：

• CREATE：用于创建新的数据库对象，如表、视图、索引等。

CREATE TABLE employee (  
    id NUMBER(4),  
    name VARCHAR2(20) NOT NULL,  
    gender CHAR(1) DEFAULT 'M',  
    birth DATE,  
    salary NUMBER(30),  
    job VARCHAR2(30),  
    deptno NUMBER(2)  
);

• ALTER：用于修改现有数据库对象的结构，如添加、删除或修改表的列。

ALTER TABLE employee ADD (termdate DATE DEFAULT SYSDATE);  
ALTER TABLE employee MODIFY (job VARCHAR2(40) DEFAULT 'CLERK');  
ALTER TABLE employee DROP COLUMN termdate;

• DROP：用于删除数据库对象，如表、视图等。

DROP TABLE employee;

• TRUNCATE：用于删除表中的所有数据，但保留表结构。

TRUNCATE TABLE employee;

• RENAME：用于重命名数据库对象。

RENAME employee TO myemp;

二、数据操纵语言（DML）

DML用于处理数据库中的数据，主要包括以下几种语句：

• INSERT：用于向表中插入新的数据行。

INSERT INTO myemp (id, name, salary, deptno) VALUES (1, 'John Doe', 6000, 10);

• UPDATE：用于修改表中已存在的数据。

UPDATE myemp SET salary = 7000 WHERE id = 1;

• DELETE：用于从表中删除数据行。

DELETE FROM myemp WHERE id = 1;

三、事务控制语言（TCL）

TCL用于管理数据库事务，确保数据的一致性和完整性，主要包括以下几种语句：

• COMMIT：用于提交当前事务，使自上次提交以来所做的所有更改成为永久性的。

COMMIT;

• ROLLBACK：用于回滚当前事务，取消自上次提交或回滚以来所做的所有更改。

ROLLBACK;

• SAVEPOINT：用于在事务中设置一个保存点，以便在需要时可以回滚到该点。

SAVEPOINT my_savepoint;  
ROLLBACK TO SAVEPOINT my_savepoint;

四、数据查询语言（DQL）

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

SELECT * FROM myemp;  
SELECT name, salary FROM myemp WHERE deptno = 10;

五、数据控制语言（DCL）

DCL用于定义数据库访问权限和安全级别，主要包括以下几种语句：

• GRANT：用于给用户或角色授予权限。

GRANT SELECT, INSERT ON myemp TO user1;

• REVOKE：用于收回用户或角色已有的权限。

REVOKE SELECT, INSERT ON myemp FROM user1;

其他常用语句和技巧

• 查看表结构：使用DESC语句查看表的结构。

DESC myemp;

• 添加注释：给表的列或表本身添加注释。

COMMENT ON COLUMN myemp.name IS '员工姓名';

• 字符串连接：使用||操作符连接字符串。

SELECT name || ' ' || job FROM myemp;

• 日期和时间函数：使用TO_DATE、TO_CHAR等函数处理日期和时间。

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

• 聚合函数：如SUM、AVG、MAX、MIN等，用于对数据进行汇总统计。

SELECT AVG(salary) AS average_salary FROM myemp;

• 分组和排序：使用`GROUP BY`和`ORDER BY`语句对数据进行分组和排序。

SELECT deptno, AVG(salary) AS avg_salary  
FROM myemp  
GROUP BY deptno  
ORDER BY avg_salary DESC;

• 子查询：在查询中嵌套另一个查询。

SELECT *  
FROM myemp  
WHERE salary > (SELECT AVG(salary) FROM myemp);

• 连接查询：使用JOIN语句连接两个或多个表。

SELECT e.name, d.dname  
FROM myemp e  
JOIN dept d ON e.deptno = d.deptno;

• 视图：使用CREATE VIEW语句创建视图，视图是基于SQL语句的结果集的可视化的表。

CREATE VIEW emp_view AS  
SELECT name, salary, deptno  
FROM myemp  
WHERE deptno = 10;

• 索引：使用CREATE INDEX语句创建索引，以提高查询效率。

CREATE INDEX idx_name ON myemp(name);

• 序列：用于生成唯一的数值，常用于自增主键。

CREATE SEQUENCE emp_seq  
START WITH 1  
INCREMENT BY 1  
NOCACHE;

• 触发器：在特定数据库事件发生时自动执行的SQL语句或存储过程。

CREATE OR REPLACE TRIGGER trg_before_insert_emp  
BEFORE INSERT ON myemp  
FOR EACH ROW  
BEGIN  
  IF :NEW.salary IS NULL THEN  
    :NEW.salary := 5000;  -- 假设默认值为5000  
  END IF;  
END;  
/

• 同义词：为数据库对象（如表、视图、序列等）创建别名，简化访问。

CREATE SYNONYM emp_syn FOR myemp;