SQLの基本操作(Oracleモード)

この記事では、OceanBaseデータベースのOracleモードにおける基本的なSQL操作について説明します。

テーブルの操作

このセクションでは、データベースでのテーブルの作成、表示、変更、削除に関する構文と例を示します。

テーブルの作成

CREATE TABLE文を使用して、データベースに新しいテーブルを作成します。

例：テーブルtestを作成します。

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

CREATE TABLE文に関する構文の詳細については、CREATE TABLEを参照してください。

テーブルの変更

ALTER TABLE文を使用して、既存のテーブルの構造を変更します。これには、テーブルおよびテーブル属性の変更、列の追加、列および属性の変更、列の削除などが含まれます。

例：

• テーブル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
  

• テーブル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
  

ALTER TABLE文に関する構文の詳細については、ALTER TABLEを参照してください。

テーブルの削除

DROP TABLE文を使用して、テーブルを削除します。

例：テーブルtestを削除します。

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

DROP TABLE文に関する構文の詳細については、DROP TABLEを参照してください。

インデックスの操作

インデックスは、テーブル上に作成されて、データベーステーブルの一列または複数列の値を並べ替えたものです。その主な役割は、クエリの速度を向上させ、データベースシステムのパフォーマンス負荷を低減することにあります。

インデックスの作成

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

CREATE INDEX文に関する構文の詳細については、CREATE INDEXを参照してください。

インデックスの確認

• ビューALL_INDEXESを使用して、テーブルのすべてのインデックスを確認します。

  obclient> SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME 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
  *************************** 2. row ***************************
                    OWNER: SYS
              INDEX_NAME: TEST_INDEX
              INDEX_TYPE: NORMAL
              TABLE_OWNER: SYS
              TABLE_NAME: TEST
  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

DROP INDEX文に関する構文の詳細については、DROP INDEXを参照してください。

データの挿入

INSERT文を使用して、テーブルに一つまたは複数のレコードを追加します。

例：

• CREATE TABLEを使用して、テーブルt1を作成し、1行のデータを挿入します。

  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
  

• サブクエリにデータを直接挿入します。

  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
  

• 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
  

INSERT文に関する構文の詳細については、INSERTを参照してください。

データの削除

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

DELETE文に関する構文の詳細については、DELETEを参照してください。

データの更新

UPDATE文を使用して、テーブルのフィールド値を変更します。

例:

• テーブル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
  

• 更新対象としてサブクエリを指定し、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
  

UPDATE文に関する構文の詳細については、UPDATE の章を参照してください。

データの照会

SELECT文を使用して、テーブルの内容を照会します。

例：

• 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
  

• クエリ結果の中から、nameの重複を除外します。

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

• テーブル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
  

SELECT文に関する構文の詳細については、SELECTを参照してください。

トランザクションのコミット

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> exit;
Bye

obclient> obclient -h127.0.0.1 -us**@oracle -P2881 -p******

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

トランザクション制御文に関する詳細は、トランザクション管理の概要を参照してください。