SQL 基础操作（Oracle 模式）

分布式数据库爱好者 2022-11-19

271

本节主要介绍 OceanBase 数据库 Oracle 模式下的一些 SQL 基本操作。

表操作

本节主要提供数据库中表的创建、查看、修改和删除的语法和示例。

创建表

使用 CREATE TABLE 语句在数据库中创建新表。

示例：创建表 test。

obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected

修改表

使用 ALTER TABLE 语句来修改已存在的表的结构，包括修改表及表属性、新增列、修改列及属性、删除列等。

示例 1：修改表 test 的字段 c2 的字段类型。

obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| FIELD | TYPE        | NULL | KEY | DEFAULT | EXTRA |
+-------+-------------+------+-----+---------+-------+
| C1    | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(3) | YES  | NULL| NULL    | NULL  |
+-------+-------------+------+-----+---------+-------+
2 rows in set

obclient> ALTER TABLE test MODIFY c2 CHAR(10);
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
| C2    | CHAR(10)   | YES  | NULL| NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
2 rows in set

示例 2：在表 test 中增加、删除列。

obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
| C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(38) | YES  | NULL | NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
3 rows in set

obclient> ALTER TABLE test DROP COLUMN c3;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
| C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
2 rows in set

删除表

使用 DROP TABLE 语句删除表。

示例：删除表 test。

obclient> DROP TABLE test;
Query OK, 0 rows affected

索引操作

索引是创建在表上并对数据库表中一列或多列的值进行排序的一种结构。其作用主要在于提高查询的速度，降低数据库系统的性能开销。

创建索引

使用 CREATE INDEX 语句创建表的索引。

示例：创建表 test 的索引。

obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
| C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
2 rows in set 

obclient> CREATE INDEX test_index ON test (c1, c2);
Query OK, 0 rows affected

查看索引

通过视图 ALL_INDEXES 查看表的所有索引。示例如下：

obclient> SELECT * FROM ALL_INDEXES WHERE table_name='TEST'\G
*************************** 1. row ***************************
                  OWNER: SYS
             INDEX_NAME: TEST_OBPK_1664353339491130
             INDEX_TYPE: NORMAL
            TABLE_OWNER: SYS
             TABLE_NAME: TEST
             TABLE_TYPE: TABLE
             UNIQUENESS: UNIQUE
            COMPRESSION: ENABLED
          PREFIX_LENGTH: NULL
        TABLESPACE_NAME: NULL
              INI_TRANS: NULL
              MAX_TRANS: NULL
         INITIAL_EXTENT: NULL
            NEXT_EXTENT: NULL
            MIN_EXTENTS: NULL
            MAX_EXTENTS: NULL
           PCT_INCREASE: NULL
          PCT_THRESHOLD: NULL
         INCLUDE_COLUMN: NULL
              FREELISTS: NULL
        FREELIST_GROUPS: NULL
               PCT_FREE: NULL
                LOGGING: NULL
                 BLEVEL: NULL
            LEAF_BLOCKS: NULL
          DISTINCT_KEYS: NULL
AVG_LEAF_BLOCKS_PER_KEY: NULL
AVG_DATA_BLOCKS_PER_KEY: NULL
      CLUSTERING_FACTOR: NULL
                 STATUS: VALID
               NUM_ROWS: NULL
            SAMPLE_SIZE: NULL
          LAST_ANALYZED: NULL
                 DEGREE: 1
              INSTANCES: NULL
            PARTITIONED: NO
              TEMPORARY: NULL
              GENERATED: NULL
              SECONDARY: NULL
            BUFFER_POOL: NULL
            FLASH_CACHE: NULL
       CELL_FLASH_CACHE: NULL
             USER_STATS: NULL
               DURATION: NULL
      PCT_DIRECT_ACCESS: NULL
             ITYP_OWNER: NULL
              ITYP_NAME: NULL
             PARAMETERS: NULL
           GLOBAL_STATS: NULL
          DOMIDX_STATUS: NULL
        DOMIDX_OPSTATUS: NULL
         FUNCIDX_STATUS: NULL
             JOIN_INDEX: NO
IOT_REDUNDANT_PKEY_ELIM: NULL
                DROPPED: NO
             VISIBILITY: VISIBLE
      DOMIDX_MANAGEMENT: NULL
        SEGMENT_CREATED: NULL
       ORPHANED_ENTRIES: NULL
               INDEXING: NULL
                   AUTO: NULL
*************************** 2. row ***************************
                  OWNER: SYS
             INDEX_NAME: TEST_INDEX
             INDEX_TYPE: NORMAL
            TABLE_OWNER: SYS
             TABLE_NAME: TEST
             TABLE_TYPE: TABLE
             UNIQUENESS: NONUNIQUE
            COMPRESSION: ENABLED
          PREFIX_LENGTH: NULL
        TABLESPACE_NAME: NULL
              INI_TRANS: NULL
              MAX_TRANS: NULL
         INITIAL_EXTENT: NULL
            NEXT_EXTENT: NULL
            MIN_EXTENTS: NULL
            MAX_EXTENTS: NULL
           PCT_INCREASE: NULL
          PCT_THRESHOLD: NULL
         INCLUDE_COLUMN: NULL
              FREELISTS: NULL
        FREELIST_GROUPS: NULL
               PCT_FREE: NULL
                LOGGING: NULL
                 BLEVEL: NULL
            LEAF_BLOCKS: NULL
          DISTINCT_KEYS: NULL
AVG_LEAF_BLOCKS_PER_KEY: NULL
AVG_DATA_BLOCKS_PER_KEY: NULL
      CLUSTERING_FACTOR: NULL
                 STATUS: VALID
               NUM_ROWS: NULL
            SAMPLE_SIZE: NULL
          LAST_ANALYZED: NULL
                 DEGREE: 1
              INSTANCES: NULL
            PARTITIONED: NO
              TEMPORARY: NULL
              GENERATED: NULL
              SECONDARY: NULL
            BUFFER_POOL: NULL
            FLASH_CACHE: NULL
       CELL_FLASH_CACHE: NULL
             USER_STATS: NULL
               DURATION: NULL
      PCT_DIRECT_ACCESS: NULL
             ITYP_OWNER: NULL
              ITYP_NAME: NULL
             PARAMETERS: NULL
           GLOBAL_STATS: NULL
          DOMIDX_STATUS: NULL
        DOMIDX_OPSTATUS: NULL
         FUNCIDX_STATUS: NULL
             JOIN_INDEX: NO
IOT_REDUNDANT_PKEY_ELIM: NULL
                DROPPED: NO
             VISIBILITY: VISIBLE
      DOMIDX_MANAGEMENT: NULL
        SEGMENT_CREATED: NULL
       ORPHANED_ENTRIES: NULL
               INDEXING: NULL
                   AUTO: NULL
2 rows in set

通过 USER_IND_COLUMNS 查看表索引的详细信息。示例如下：

obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='TEST'\G
*************************** 1. row ***************************
        INDEX_NAME: TEST_OBPK_1664353339491130
        TABLE_NAME: TEST
       COLUMN_NAME: C1
   COLUMN_POSITION: 1
     COLUMN_LENGTH: 22
       CHAR_LENGTH: 0
           DESCEND: ASC
COLLATED_COLUMN_ID: NULL
*************************** 2. row ***************************
        INDEX_NAME: TEST_INDEX
        TABLE_NAME: TEST
       COLUMN_NAME: C1
   COLUMN_POSITION: 1
     COLUMN_LENGTH: 22
       CHAR_LENGTH: 0
           DESCEND: ASC
COLLATED_COLUMN_ID: NULL
*************************** 3. row ***************************
        INDEX_NAME: TEST_INDEX
        TABLE_NAME: TEST
       COLUMN_NAME: C2
   COLUMN_POSITION: 2
     COLUMN_LENGTH: 10
       CHAR_LENGTH: 10
           DESCEND: ASC
COLLATED_COLUMN_ID: NULL
3 rows in set

删除索引

使用 DROP INDEX 语句删除表的索引。

示例：删除索引 test_index。

obclient> DROP INDEX test_index;
Query OK, 0 rows affected

插入数据

使用 INSERT 语句添加一个或多个记录到表中。

示例 1：通过 CREATE TABLE 创建表 t1，并向表 t1 中插入一行数据。

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected

obclient> SELECT * FROM t1;
Empty set

obclient> INSERT INTO t1 VALUES(1,1);
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set

示例 2：直接向子查询中插入数据。

obclient> INSERT INTO (SELECT * FROM t1) VALUES(2,2);
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set

示例 3：包含 RETURNING 子句的数据插入。

obclient> INSERT INTO t1 VALUES(3,3) RETURNING c1;
+----+
| C1 |
+----+
|  3 |
+----+
1 row in set

obclient> SELECT * FROM t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set

删除数据

使用 DELETE 语句删除数据。

示例：删除表 t1 中 c1=2 的行。

obclient> DELETE FROM t1 WHERE c1 = 2;
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
+----+------+
2 rows in set

更新数据

使用 UPDATE 语句修改表中的字段值。

示例 1：将表 t1 中 t1.c1=1 对应的那一行数据的 c2 列值修改为 100。

obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |  100 |
|  3 |    3 |
+----+------+
2 rows in set

示例 2：直接操作子查询，将子查询中 v.c1=3 对应的那一行数据的 c2 列值修改为 300。

obclient> UPDATE (SELECT * FROM t1) v SET v.c2 = 300 WHERE v.c1 = 3;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |  100 |
|  3 |  300 |
+----+------+
2 rows in set

查询数据

使用 SELECT 语句查询表中的内容。

示例 1：通过 CREATE TABLE 创建表 t2。从表 t2 中读取 name 的数据。

obclient> CREATE TABLE t2 (id INT, name VARCHAR(50), num INT);
Query OK, 0 rows affected

obclient> INSERT INTO t2 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t2;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

obclient> SELECT name FROM t2;
+------+
| NAME |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set

示例 2：在查询结果中对 name 进行去重处理。

obclient> SELECT DISTINCT name FROM t2;
+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set

示例 3：从表 t2 中根据筛选条件 name = 'a' ，输出对应的 id 、name 和 num。

obclient> SELECT id, name, num FROM t2 WHERE name = 'a';
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+
2 rows in set

提交事务

使用 COMMIT 语句提交事务。

在您提交事务之前，您的修改只对当前会话可见，对其他数据库会话是不可见的；您的修改没有持久化，可以用 ROLLBACK 语句撤销修改。

在您提交事务之后，您的修改对所有数据库会话可见。您的修改结果持久化成功，不可以用 ROLLBACK 语句回滚修改。

示例：通过 CREATE TABLE 创建表 t_insert。使用 COMMIT 语句提交事务。

obclient> CREATE TABLE t_insert(
     id number NOT NULL PRIMARY KEY, 
     name varchar(10) NOT NULL, 
     value number NOT NULL, 
     gmt_create date NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected

obclient> INSERT INTO t_insert(id, name, value, gmt_create) VALUES(1,'CN',10001, sysdate),(2,'US',10002, sysdate),(3,'EN',10003, sysdate);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 22-AUG-22  |
|  2 | US   | 10002 | 22-AUG-22  |
|  3 | EN   | 10003 | 22-AUG-22  |
+----+------+-------+------------+
3 rows in set

obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004);
Query OK, 1 row affected

obclient> COMMIT;
Query OK, 0 rows affected

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 22-AUG-22  |
|  2 | US   | 10002 | 22-AUG-22  |
|  3 | EN   | 10003 | 22-AUG-22  |
|  4 | JP   | 10004 | 22-AUG-22  |
+----+------+-------+------------+
4 rows in set

回滚事务

使用 ROLLBACK 语句可以回滚事务。

回滚一个事务指将事务的修改全部撤销。可以回滚当前整个未提交的事务，也可以回滚到事务中任意一个保存点。如果要回滚到某个保存点，必须结合使用 ROLLBACK和 TO SAVEPOINT 语句。

其中：

如果回滚整个事务，则：
- 事务会结束
- 所有的修改会被丢弃
- 清除所有保存点
- 释放事务持有的所有锁
如果回滚到某个保存点，则：
- 事务不会结束
- 保存点之前的修改被保留，保存点之后的修改被丢弃
- 清除保存点之后的保存点（不包括保存点自身）
- 释放保存点之后事务持有的所有锁

示例：回滚事务的全部修改。

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 29-SEP-22  |
|  2 | US   | 10002 | 29-SEP-22  |
|  3 | EN   | 10003 | 29-SEP-22  |
|  4 | JP   | 10004 | 29-SEP-22  |
+----+------+-------+------------+
4 rows in set

obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 22-AUG-22  |
|  2 | US   | 10002 | 22-AUG-22  |
|  3 | EN   | 10003 | 22-AUG-22  |
|  4 | JP   | 10004 | 22-AUG-22  |
|  5 | FR   | 10005 | 22-AUG-22  |
|  6 | RU   | 10006 | 22-AUG-22  |
+----+------+-------+------------+
6 rows in set

obclient> ROLLBACK;
Query OK, 0 rows affected

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 29-SEP-22  |
|  2 | US   | 10002 | 29-SEP-22  |
|  3 | EN   | 10003 | 29-SEP-22  |
|  4 | JP   | 10004 | 29-SEP-22  |
+----+------+-------+------------+
3 rows in set

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者