Oracle数据库基础学习笔记汇总

1. 创建表 CREATE TABLE

1.1. 要在Oracle数据库中创建一个新表，可以使用`CREATE TABLE`语句。

CREATE TABLE schema_name.table_name 
( column_1 data_type column_constraint, 
 column_2 data_type column_constraint, 
 ... table_constraint );

以下示例显示如何在`ot`模式中创建一个名称为`persons`的新表：

-- oracle 12c写法
CREATE TABLE ot.persons(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);

在这个例子中，`persons`表有三列：`person_id`，`first_name`和`last_name`。

`person_id`是标识表中唯一行的标识列。`person_id`列的数据类型是`NUMBER`。子句`GENERATED BY DEFAULT AS IDENTITY`指示 Oracle生成一个新的整数，并在列中插入新行时将其用于列。

`first_name`列的数据类型为`VARCHAR2`，最大长度为`50`。这意味着不能在`first_name`列中插入长度大于`50`的字符。另外，`NOT NULL`列约束防止`first_name`列具有`NULL`值。

`last_name`列具有与`first_name`列相同的特征。

`PRIMARY KEY`子句将`person_id`列指定为主键列，用于标识个人表中的唯一行。

 1.2. `CREATE TABLE AS`语句用于通过复制现有表的列从现有表来创建新表。

注意：如果以这种方式创建表，则新表将包含现有表中的记录。

--复制另一个表的所有列
CREATE TABLE new_table AS (SELECT * FROM old_table); 
--复制另一个表的所有列
CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table); 
--从多个表复制选定的列
CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table_1, old_table_2, ... old_table_n);

2. 修改表结构 ALTER TABLE

--要将新列添加到表中，请使用以下语法：
ALTER TABLE table_name ADD column_name type constraint; 
--要同时向表中添加多列，请按如下所示将新列置于括号内：
ALTER TABLE table_name ADD 
( column_name type constraint, column_name type constraint, ... ); 
--要修改列的属性，请使用以下语法：
ALTER TABLE table_name MODIFY column_name type constraint;
--要修改多个列，请使用以下语法：
ALTER TABLE table_name MODIFY ( column_1 type constraint, column_1 type constraint, ...); 
--要从表中删除现有的列，请使用以下语法：
ALTER TABLE table_name DROP COLUMN column_name; 
--要同时删除多个列，请使用以下语法：
ALTER TABLE table_name DROP (column_1,column_2,...); 
--Oracle添加了一个用于重命名列的子句，如下所示：
ALTER TABLE table_name RENAME COLUMN column_name TO new_name;
--要将一个表重命名为一个新的`name`的表名，使用下面的语法：
ALTER TABLE table_name RENAME TO new_table_name; 
--`ALTER TABLE ADD`列语句向表中添加一列或多列
ALTER TABLE table_name ADD column_name data_type constraint;

 3. 删除表DROP TABLE

要将表移动到回收站或将其从数据库中完全删除，请使用`DROP TABLE`语句：

DROP TABLE schema_name.table_name [CASCADE CONSTRAINTS | PURGE];

4. Oracle使用SET UNUSED COLUMN子句逻辑删除列

从大表中删除列的过程可能耗费时间和资源。因此，通常使用`ALTER TABLE SET UNUSED COLUMN`语句来逻辑删除列，如下所示： 

ALTER TABLE table_name SET UNUSED COLUMN column_name;

当执行了该语句，该列就不再可见。在非高峰时段，可以使用以下语句在物理上删除未使用的列：

ALTER TABLE table_name DROP UNUSED COLUMNS;

如果要减少累积的撤消日志量，可以使用`CHECKPOINT`选项，该选项在指定的行数已被处理后强制检查点。

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

4.1. Oracle使用DROP COLUMN子句从物理表删除列

要从物理表中删除列，请使用以下语句：

ALTER TABLE table_name DROP COLUMN column_name;

要删除多个列，请使用以下语句：

ALTER TABLE table_name DROP ( column_name_1, column_name_2 );

5. Oracle修改列定义

要更改表中列的定义，请按如下所示使用`ALTER TABLE MODIFY`列语法：

ALTER TABLE table_name MODIFY column_name action;

语句很直接。要修改表的列，需要指定要执行的列名，表名和操作。

Oracle允许执行多种操作，但以下是主要常用的操作：

-   修改列的可见性

-   允许或不允许`NULL`值

-   缩短或扩大列的大小

-   更改列的默认值

-   修改虚拟列的表达式

要修改多个列，请使用以下语法：

ALTER TABLE table_name 
MODIFY ( column_name_1 action, column_name_2 action, ... );

6. Oracle截断表

如果要从表中删除所有数据，可以使用不带`WHERE`子句的`DELETE`语句，如下所示：

DELETE FROM table_name;

对于有少量行记录的表，`DELETE`语句做得很好。但是，当拥有大量行记录的表时，使用`DELETE`语句删除所有数据效率并不高。

Oracle引入了`TRUNCATE TABLE`语句，用于删除大表中的所有行。

以下说明了Oracle `TRUNCATE TABLE`语句的语法：

TRUNCATE TABLE schema_name.table_name [CASCADE] [[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]] [[ DROP | REUSE]] STORAGE ]

默认情况下，要从表中删除所有行，请指定要在`TRUNCATE TABLE`子句中截断的表的名称：

TRUNCATE TABLE table_name;

在这种情况下，因为我们没有明确指定模式名称，所以Oracle假定从当前的模式中截断表。  

如果表通过外键约束与其他表有关系，则需要使用`CASCADE`子句：

TRUNCATE TABLE table_name
CASCADE;

在这种情况下，`TRUNCATE TABLE CASCADE`语句删除`table_name`表中的所有行，并递归地截断链中的关联表。

请注意，`TRUNCATE TABLE CASCADE`语句需要使用`ON DELETE CASCADE`子句定义的外键约束才能工作。

通过`MATERIALIZED VIEW LOG`子句，可以指定在表上定义的物化视图日志是否在截断表时被保留或清除。默认情况下，物化视图日志被保留。

`STORAGE`子句允许选择删除或重新使用由截断行和关联索引(如果有的话)释放的存储。默认情况下，存储被删除。

请注意，要截断表，它必须在您自己的模式中，或者必须具有`DROP ANY TABLE`系统权限。

 7. Oracle重命名表

要重命名表，可以使用以下Oracle `RENAME`表语句，如下所示：

RENAME table_name TO new_name;

8. Oracle插入数据

要将新行插入到表中，请按如下方式使用Oracle `INSERT`语句：

INSERT INTO table_name (column_1, column_2, column_3, ... column_n)
VALUES( value_1, value_2, value_3, ..., value_n);

在这个声明语句中：

-   首先，指定要插入的表的名称 - `table_name`。

-   其次，在圆括号内指定逗号分隔列名的列表。

-   第三，指定对应于列列表的逗号分隔值列表。

如果值列表与表列具有相同的顺序，则可以跳过不指定列的列表，但这不被认为是一种好的做法：

INSERT INTO table_name
VALUES (value_1, value_2, value_3, ..., value_n);

如果从Oracle `INSERT`语句中排除一列或多列，则必须指定列列表，因为Oracle需要它与值列表中的值相匹配。

在`INSERT`语句中省略的列将使用缺省值(如果可用)或者如果列接受`NULL`值，则使用`NULL`值。

 9. Oracle选择插入数据

有时候，想要将从其它表中[选择数据]并将其[插入]到另一个表中。要做到这一点，可使用Oracle `INSERT INTO SELECT`语句，如下所示：

INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;

Oracle `INSERT INTO SELECT`语句要求源表和目标表匹配数据类型。

10. Oracle插入多行到多表

 10.1. 无条件的Oracle INSERT ALL语句

将多行插入到表中，要将多行插入到表中，请使用以下Oracle `INSERT ALL`语句：

INSERT ALL
    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

在这个语句中，每个值表达式值：`val1`，`val2`或`val3`必须引用由子查询的选择列表返回的列对应的值。

如果要使用文字值而不是子查询返回的值，请使用以下子查询：

SELECT * FROM dual;

将多行插入到多个表中，还可以使用`INSERT ALL`语句将行插入到多个表中，如下所示。

INSERT ALL
    INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

10.2. 有条件的Oracle INSERT ALL语句

条件多项插入语句允许根据指定的条件将行插入到表中。

以下显示了条件多项插入语句的语法：

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
    ELSE
        INTO table_3(column_list ) VALUES (value_list)
Subquery

如果指定了`ALL`关键字，则Oracle将在`WHEN`子句中评估每个条件。如果条件评估/计算为`true`，则Oracle执行相应的`INTO`子句。

但是，当指定`FIRST`关键字时，对于由子查询返回的每一行，Oracle都会从`WHEN`子句的上下方向评估每个条件。如果Oracle发现条件的计算结果为`true`，则执行相应的`INTO`子句并跳过给定行的后续`WHEN`子句。

请注意，单条件多项式插入语句最多可以有`127`个`WHEN`子句。

11. 数据更新 UPDATE

语法：UPDATE 表名称 SET 列名称 = 新值 <WHERE 条件>

UPDATE table_name 
SET 
column1 = value1, 
column2 = value2, 
column3 = value3,
... 
WHERE condition;

在原有字段值上追加内容，不是替换；

--1、给某个字段值前面增加字符

UPDATE 表名 SET 列名='要添加的字符'||列名

--2、给某列后面增加字符

UPDATE 表名 SET 列名=列名||'要添加的字符'

--连接操作符||，用于将两个或多个字符串合并成一个字符串

12. 删除表数据

要从表中删除一行或多行，请按如下所示使用Oracle `DELETE`语句：

DELETE
FROM
    table_name
WHERE
    condition;

在这个语句中，

-   首先，指定要从中删除数据的表的名称。

-   其次，通过使用`WHERE`子句中的条件指定应该删除哪些行。如果省略了`WHERE`子句，则Oracle `DELETE`语句将从表中删除所有行。

请注意，使用[TRUNCATE TABLE]语句删除大型表中的所有行会更快更高效。

 13. Oracle Distinct子句 去重

`SELECT DISTINCT`语句从表中查询不同(过滤相同值)的数据。

在SELECT语句中使用`DISTINCT`子句来过滤结果集中的重复行。它确保在`SELECT`子句中返回指定的一列或多列的值是唯一的。

以下说明了`SELECT DISTINCT`语句的语法：

SELECT DISTINCT
  column_1
FROM
  table_name;