【Oracle】テーブルを作成しよう！

前回は、Oracleの学習を進めていくための環境を紹介させていただきました。今回は、Oracleのデータを扱うために必要なテーブルの作成について説明していきます！

【Oracle】Webで学ぶSQL(環境構築不要)

今回は、環境構築不要で SQL(Oracle Database) を学習する方法を説明します！

gptblog.jp

2024.03.19

テーブルの作成

テーブルを作成するには、CREATE TABLE文を使います。

CREATE TABLE テーブル名 {
    列名1 列の型名,
    列名1 列の型名,
    :
    列名X 列Xの型名,
}

今回は家計簿テーブルということで、テーブル名は「household_account1」「household_account2」を作りました。

CREATE TABLE household_account1 (
    id INT PRIMARY KEY,
    transaction_date DATE,
    expense VARCHAR2(20),
    memo VARCHAR2(80),
    deposit INT,
    withdrawal INT
);

CREATE TABLE household_account2 (
    id INT PRIMARY KEY,
    transaction_date DATE,
    expense VARCHAR2(20),
    memo VARCHAR2(80),
    deposit INT,
    withdrawal INT
);

各カラムの説明は下記

1. id: 整数型(INT)の列(カラム)で、各レコードの一意の識別子として使用します。
2. transaction_date: 日付型(DATE)の列(カラム)で、取引の日付を表します。
3. expense: 文字列型(VARCHAR2)の列(カラム)で、取引の費目を表します。文字数は全角10文字、半角20文字までです。
4. memo: 文字列型(VARCHAR2)の列(カラム)で、取引に関するメモや説明を表します。文字数は全角40文字、半角80文字です。
5. deposit: 整数型(INT)の列(カラム)で、入金額を表します。
6. withdrawal: 整数型(INT)の列(カラム)で、出金額を表します。

テーブルの確認

では、作ったテーブルを確認してみましょう。テーブルを確認するには、user_tablesを使います。*を指定すると不要な列(カラム)まで取得できてしまうので、table_nameを指定しテーブル名を取得します。

SELECT table_name FROM user_tables;

作ったテーブルが確認できました。TABLE_NAMEだとわかりにくいので、AS句で別名をつけてみたいと思います。

SELECT table_name AS "作成したテーブル" FROM user_tables;

AS句を使ったので別名で表が取得できました。2つテーブルを作成したので、１つは削除したいと思います。

テーブルの削除

テーブルを削除するには、DROP TABLE文を使います。

DROP TABLE テーブル名

テーブルを削除し、テーブルを確認したいと思います。

-- 不要なテーブルの削除
DROP TABLE household_account2;

--作成したテーブルの確認
SELECT table_name AS "作成したテーブル" FROM user_tables;

削除できているのが確認できました。次は、テーブルの更新を行いたいと思います。

テーブルの更新

家計簿テーブルには、日付、費目、メモ、入金額、出勤額がありました。ここに残高という列(カラム)を追加したいと思います。テーブル定義の変更で列(カラム)の追加や削除は、ALTER TABLE文を使います。

-- 列(カラム)の追加
ALTER TABLE テーブル名 ADD 列名 型

--列(カラム)の削除
ALTER TABLE テーブル名 DROP COLUMN 列名

列(カラム)の追加と削除を行う前に、作った家計簿テーブル「household_account1」にデータを追加してみます。データを追加する時はINSERT文を使います。※INSERT文は別記事で詳しく説明します

--データの追加
INSERT INTO
  household_account1
VALUES
  (
    1,
    TO_DATE('2024-03-19', 'YYYY-MM-DD'),
    '食料品',
    'スーパーで購入',
    0,
    2000
  );

--データの取得
SELECT
  TO_CHAR(transaction_date, 'YYYY/MM/DD') AS "日付",
  expense AS "費目",
  memo AS "メモ",
  deposit AS "入金額",
  withdrawal AS "出金額"
FROM
  household_account1;

データを追加し、取得しました。

では、家計簿テーブルに残高という列(カラム)を追加します。DEFAULTというキーワードで残高に10000円入ってることにしてみます。

--balance（残高)を追加
ALTER TABLE household_account1 ADD balance INT DEFAULT 10000;

--データの取得
SELECT
  TO_CHAR(transaction_date, 'YYYY/MM/DD') AS "日付",
  expense AS "費目",
  memo AS "メモ",
  deposit AS "入金額",
  withdrawal AS "出金額",
  balance AS "残高"
FROM
  household_account1;

あれ？でも残高って変動するし、やっぱりDEFAULTで10000円入ってるっておかしいんじゃないか…そこで追加した列ですが、やっぱり要らないので削除します。

--balance（残高)の削除
ALTER TABLE household_account1 DROP COLUMN balance 

----データの取得
SELECT
  *
FROM
  household_account1;

残高の列(カラム)を削除し、データをシンプルに * で取得してみました。

制約

予期せぬデータがテーブルに登録されないように、テーブルを定義する時に制約を設けることができます。ここでは22つの制約を紹介します。書き方は下記になります。※ここで紹介しなかった制約については別記事で紹介します

CREATE TABLE テーブル名 {
    列名 型 制約の指定,
　   ：
}

NOT NULL 制約

データを登録する時に、NULLを入れられなくするのが NOT NULL 制約です。

CREATE TABLE household_account1 (
    id INT,
    --日付にはNULLを入れることを許可しない。
    transaction_date DATE NOT NULL,
    expense VARCHAR2(20),
    memo VARCHAR2(80),
    deposit INT,
    withdrawal INT
);

上記で作られたテーブルにデータを追加する時、NULLを入れようとすると下記のエラーが起こります。

--データの追加
INSERT INTO
  household_account1
VALUES
  (
    1,
    NULL ,
    '食料品',
    'スーパーで購入',
    0,
    2000
  );

ORA-01400: NULLは挿入できません。

CHECK制約

家計簿テーブルの列(カラム)には入金額(deposit)と出勤額(withdrawal)があります。金額が0以下になることはないので、CHECK制約で0以上の指定をしたいと思います。またNULLも許容しません。

CREATE TABLE household_account1 (
    id INT,
    transaction_date DATE NOT NULL,
    expense VARCHAR2(20),
    memo VARCHAR2(80),
    deposit INT,
    withdrawal INT,
    CONSTRAINT chk_deposit CHECK (deposit NOT NULL OR deposit >= 0),
    CONSTRAINT chk_withdrawal CHECK (withdrawal NOT NULL OR withdrawal >= 0)
);

上記で作られたテーブルにデータを追加する時、0より下の数値がマイナスだと下記のエラーが起こります。

INSERT INTO
  household_account1
VALUES
  (
    1,
    TO_DATE('2024-03-19', 'YYYY-MM-DD'),
    '食料品',
    'スーパーで購入',
    0,
    -2000
  );

ORA-02290：チェック制約違反

まとめ

ちょっと長くなってしまったのですがテーブルの作成についてはなんとなく、わかったかと思います。次はSQLの基本となる「4大命令」について説明したいと思います！