【SQL】【基礎】 CREATE TABLE AS SELECT 副問い合わせを使って表を作成 ORACLEデータベース

副問い合わせを使って表を作成する方法を記載します。

1.CREATE TABLE AS SELECTの説明

副問い合わせの結果を使って表の作成ができます。

表の作成、作成した表に検索行の挿入、検索した列の表定義(列のデータ型、サイズ、NOT NULL制約)のコピーをすることができます。

※PRIMARY KEY、DEFAULTなどの制約はコピーされません。

2.CREATE TABLE AS SELECTの構文

CREATE TABLE AS SELECTの構文は以下の通りです。

CREATE TABLE [スキーマ.]テーブル名

[(

  列名1 データ型(サイズ) [制約]

[,列名2 データ型(サイズ) [制約]

・・・・・・・・]

)]

AS

SELECT ・・・・;

注意点

SELECT文は()で囲まない。

列名1など列定義をした場合、副問い合わせ(SELECT文)で取得する列数も同数にする必要があります。

列名を定義しない場合は、副問い合わせ(SELECT文)で取得する列名で定義されます。

3.CREATE TABLE AS SELECTの練習

sample1：副問い合わせで表の作成

CREATE TABLE AS SELECTを使って既存の表から新しい表を作成します。それと合わせて、NOT NULL以外の制約がコピーされないことも確認します。

まずは副問い合わせでデータを取得するための表を作ります。

＜テーブルの作成＞

CREATE TABLE testdata
(
  testno NUMBER(6) PRIMARY KEY,
  id VARCHAR2(10) NOT NULL,
  email VARCHAR2(15),
  salary NUMBER(10) CONSTRAINT ck1 CHECK(salary >= 10),
  ymd DATE DEFAULT SYSDATE NOT NULL
);

次に上で作成したtestdataテーブルに2レコードを挿入します。

＜行の挿入＞

INSERT INTO testdata (testno,id,email,salary)
VALUES('1','A1111',NULL,500);
INSERT INTO testdata (testno,id,email,salary)
VALUES('2','A1112',NULL,510);

念のためSELECT文で確認。

＜検索＞

select * from testdata;

これで副問い合わせするテーブルができました。

次にCREATE TABLE AS SELECT文で新しいtestempテーブルを作成します。

testdataテーブルと全く同じtestempテーブルを作成。

＜表の作成＞

CREATE TABLE testemp
AS
SELECT *
FROM testdata;

 

作成後にtestempテーブルを確認。

＜検索＞

select * from testemp;

 

A5SQL-mk2でNOT NULL制約のみがtestempテーブルに設定されていることを確認。

PRIMARY KEY、DEFAULTは定義されません。

 

sample2：表定義だけをコピーする

testdataを表定義だけをコピーしたtestdata2を作成します。

WHERE句を1=0などFALSEとなる条件とすることで、表定義だけをコピーできます。

＜表の作成＞

CREATE TABLE testdata2
AS
SELECT * FROM testdata
WHERE 1 = 0;

 

＜検索＞

select * from testdata2;