オーロラさんの勉強帳

33歳、未経験からIT業界へ。SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【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;