テーブル
テーブルとは、データベースにおいてデータを記録するために用意する表のようなものです。
データベースに記録するデータはテーブルに収めます。
テーブルは表計算のような行列の概念になっています。テーブルの作成 CREATE TABLE
では列の各カラム（項目）の定義をします。
例えば以下のようにテーブルを定義します。
CREATE TABLE sales_slip ( id SERIAL PRIMARY KEY, /* 行番号 */ date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* 日付 */ goods_name VARCHAR(30), /* 商品名 */ unit_price INT, /* 単価 */ amount INT, /* 数量 */ price BIGINT GENERATED ALWAYS AS ( amount * unit_price ) STORED /* 金額 */ );
テーブルに格納されるデータは行と列として扱います。
ただし、データベースではデータの扱いがテキストベースで行われ、データがどのように扱われているかは想像力を働かせる必要があります。
テーブルの作成
テーブルとは、データベースに記録するデータを収めるためのものです。
データベースに複数のスキーマーがあり、その中に複数のテーブルがあります。そして、各テーブルにデータが記録されています。
テーブルには表計算のような行列の概念があり、項目（列）を定義をします。
CREATE TABLE table_name ( id SERIAL PRIMARY KEY, /* 行番号 */ date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* 日付 */ name VARCHAR(30) /* 名称 */ );
テーブルの作成例
例えば表計算ソフトで売上記録を作成するとします。売上記録のために以下のような項目を作成します。
行番号、取引日、商品名、商品単価、売上数量、単価 ✕ 数量である金額といった項目を設けます。このような表を作成すれば何がいくつ売れたか、売上が幾らかを記録することができます。
このような売上記録をデータベースで作成してみます。
データを格納するための表としてテーブルを作成します。テーブルの作成には CREATE TABLE
SQL スキーマ文を使用します。
例えば上記の表計算ソフトのように行番号、取引日、商品名、商品単価、売上数量といった項目を定義するには以下のようにします。
CREATE TABLE sales_slip ( id SERIAL PRIMARY KEY, /* 行番号 */ date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* 日付 */ goods_name VARCHAR(30), /* 商品名 */ unit_price INT, /* 単価 */ amount INT, /* 数量 */ );
テーブルは必ず任意のデータベースの下に作成してください。
mysql> SELECT databse();
データベースについてはデータベースの作成
ページを参照してください。
テーブルには行列の概念があります。表計算ソフトと違うところは、データを収めるための項目（列）を定義しおき、データは行として後から順次追加します。
カラム名に使える文字やデータタイプについて各データベースの仕様を参照してください。
単価 ✕ 数量である金額のためのカラムは生成列を作成すれば簡単です。しかし、生成列は MySQL と PostgreSQL Ver.12 以降で使用できます。
CREATE TABLE sales_slip (
id SERIAL PRIMARY KEY, /* 行番号 */
date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* 日付 */
goods_name VARCHAR(30), /* 商品名 */
unit_price INT, /* 単価 */
amount INT, /* 数量 */
price BIGINT GENERATED ALWAYS AS ( amount * unit_price ) STORED /* 金額 */
);
PostgreSQL ver.12 以前ではビューやスクリプトを作成する必要があります。
データタイプ
データタイプ（データ型）には主に文字列型、数値型、日付・時刻型などがあります。
これらのデータタイプはテーブルの定義にも利用します。 テーブルの定義においてカラム名とともにそのデータタイプを決めておかなればなりません。
データタイプには文字列、数値、日付・時刻の他に、通貨や幾何データタイプ、画像や音といったバイナリデータを扱う型などもありますがデータベースによって違いがありますので詳しくは調べてみてください。 MySQL や PostgreSQL のデータタイプも参照してみてください。
以下は MySQL を参考に主なデータタイプの一部を示しています。
| タイプ名 | 概要 |
|---|---|
| CHAR() | 固定長。255 文字まで。引数に文字数を指定。指定数の容量を消費する |
| VARCHAR() | 可変長。255 文字まで。引数に文字数を指定。入るデータ分のみ容量を消費 |
| TEXT | 65,535 文字まで |
文字数が決まっているような場合は CHAR() または VARCHAR()、255 文字を超えるような場合は TEXT を使用するとよいでしょう。
CHAR() は固定長で指定された文字数の容量を確保します。VARCHAR() は可変長で保存される文字数によって確保される容量が変わります。VARCHAR() を使用した方がよい場合が多いようです
文字列データは引用符'
または"
で囲んで扱います。
詳しくは文字列型 CHAR、VARCHAR、TEXT
ページを参照してください。
数値型
数値を表すには主に以下のようなデータタイプを使用します。
浮動小数点型では DECIMAL や REAL や DOUBLE などがあります。通貨といった正確性が必要な場合では DECIMAL を使用します。
| タイプ名 | 概要 |
|---|---|
| INT | SIGNED、-2,147,483,648 〜 2,147,483,647。UNSIGNED、0 〜 4,294,967,295 |
| BIGINT | SIGNED、-9,223,372,036,854,775,808 〜 9,223,372,036,854,775,807。UNSIGNED、0 〜 18,446,744,073,709,551,615 |
| DECIMAL | 通貨に用いる。ユーザ指定精度、正確。小数点前までは131072桁、小数点以降は16383桁。MySQL では 65 桁 |
| REAL | 可変精度。不正確。6桁精度 |
| DOUBLE PRECISION | 可変精度。不正確。15桁精度 |
| SERIAL | 連番型。自動増分。1 ~ 4,294,000,000 |
詳しくは数値データ型 INT, DECIMAL, SERIAL
ページを参照してください。
日付・時刻型
日付や時刻には DATE や TIMESTAMP、TIME といったデータタイプが用意されています。
| タイプ名 | 概要 |
|---|---|
| DATE | YYYY-MM-DD。1000-01-01 〜 9999-12-31 |
| TIMESTAMP | YYYY-MM-DD HH:MI:SS。1970-01-01 00:00:00 〜 2037-12-31 23:59:59 |
| TIME | HHH:MI:SS。-838:59:59 〜 838:59:59 |
詳しくは日付・時刻型 DATE, TIMESTAMP, TIME
ページを参照してください。
テーブル一覧
データベース内のテーブルを確認する方法はデータベースによって違いがあります。
MySQL
MySQL では SHOW TABLES クエリーを実行します。FULL オプションを付けてテーブルの種類も確認できた方がよいでしょう。
mysql=> SHOW FULL TABLES;
PostgreSQL
PostgreSQL では \dt コマンドを使用します。
postgres=# \dt
または pg_tables システムカタログを SELECT 文で閲覧します。
postgres=# select * from table_name;
テーブルの内容確認
テーブル内容の確認をする方法もデータベースによって違いがあります。
MySQL
MySQL でテーブルの内容を確認するには DESC クエリーでテーブルを指定します。
mysql=> DESC table_name;
詳細まで確認するには以下の SHOW クエリーを実行します。
mysql=> SHOW CREATE TABLE table_name;
PostgreSQL
PostgreSQL では \dt コマンドでテーブル名を指定します。
postgres=# \dt table_name
テーブルの内容を詳しく知るためには \d コマンドを使用します。
postgres=# \d table_name
テーブルの削除
テーブルの削除には DROP コマンドを使います。
DROP TABLE table_name;
存在しないテーブルの作成の削除や他の TABLE、VIEW、関数などに関連付けされている場合はエラーが出ます。
存在しないテーブルの作成には DROP TABLE IF EXISTS というコマンドで対処します。
関連付けによる場合はCASCADEコマンドを付加します。関連付けられたものも削除する可能性があるので注意が必要です。
テーブルの変更
テーブルの変更には ALTER コマンドを使用します。詳しくは「テーブル内容の変更」を参照してください。
カラムのデータタイプを変更
例えば、カラムのデータタイプを変更するには以下のようにします。
ALTER TABLE table_name ALTER COLUMN column_name TYPE column_type;
詳しくは 列のデータタイプを変更 ALTER TABLE、ALTER COLUMN、TYPE
を参照してください。
テーブルからカラムを削除
テーブルからカラムを削除するには以下のようにします。
ALTER TABLE table_name DROP COLUMN column_name;
詳しくは テーブルから列を削除 ALTER TABLE、DROP COLUMN
を参照してください。
テーブルにカラムを追加
テーブルにカラムを追加するには以下のようにします。
ALTER TABLE table_name ADD COLUMN column_name column_type;
詳しくは テーブルに列を追加 ALTER TABLE、ADD COLUMN
を参照してください。
外部キー制約（ FOREIGN KEY ）
外部キー制約（ FOREIGN KEY ）とは、指定された別テーブルのカラムデータ以外が入力できないようにする機能です。
このことでデータの整合性を保ちます。整合性のあるデータベースをリレーショナルデータベースといいます。
例えば以下のようなリレーションのあるテーブルを作成するとします。
外部キー制約の作成例
まず、商品名が入るテーブルを定義します。
CREATE TABLE goods (
id SERIAL PRIMARY KEY, /* ID */
name VARCHAR(30), /* 商品名 */
unit_price INT /* 参考単価 */
);
行ごとに id を付けます。この id は PRIMARY KEY によって重複しません。
name は商品名、unit_price は参考単価です。
さらに sales_slip テーブルの goods_name を外部キー制約
によってこの商品名テーブルを参照するように定義し直します。
外部キー制約
は FOREIGN KEY( 対象カラム名 ) REFERENCES 外部テーブル( 外部テーブルのカラム名 ) で定義します。
SERIAL を設定した id を外部キー制約で指定するには MySQL では、以下のように BIGINT UNSIGNED に指定しなければなりません。
SERIAL の値は BIGINT UNSIGNED であるためです。
売上を記録する sales_slip テーブルの goods_name を外部キー制約によって予め決められた商品名のみが入力するようにするには以下のようにします。
CREATE TABLE sales_slip ( id SERIAL PRIMARY KEY, /* 行番号 */ date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* 日付 */ goods_id BIGINT UNSIGNED, /* 商品名id */ unit_price INT, /* 単価 */ amount INT, /* 数量 */ FOREIGN KEY(goods_id) REFERENCES goods( id ) );
PostgreSQL では UNSIGNED がないために上記のままではエラーになります。INT が対応できるので以下のように定義しています。
CREATE TABLE sales_slip ( id SERIAL PRIMARY KEY, /* 行番号 */ date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* 日付 */ goods_id INT, /* 商品名id */ unit_price INT, /* 単価 */ amount INT, /* 数量 */ FOREIGN KEY(goods_id) REFERENCES goods( id ) );
＊PostgreSQL では user_id
は BIGINT または INT です。
sales_slip テーブルの外部キー制約は FOREIGN KEY と REFERENCES 句によって定義します。その制約によって上記の goods テーブルに記録されたデータ以外を入力できないようになります。
外部キー制約は指定された主テーブルデータを参照します。参照されているテーブルにないデータを入力しようとするとエラーが出ます。
ここでは外部キー制約に id を使用しています。goods テーブルの name といった文字列を使用することもできます。
外部キー制約は文字列よりも数値の方が処理するスピードで優位性があります。そのため、name といった文字列を使用するのは規模の小さなデータベースで向いています。
データの入力と閲覧
作成したテーブルにデータを新規入力、そしてそのデータを閲覧するには INSERT、SELECT クエリーを使用します。
テーブルにデータを入力
テーブルにデータを新規入力するには以下のように INSERT 文を使用します。
INSERT INTO goods( name, unit_price ) VALUES ( 'candy', 3 );
以下のように表示されていたら入力ができています。
Query OK, 1 row affected (0.00 sec)
さらに幾つかのデータを入力してみます。
INSERT INTO goods( name, unit_price ) VALUES ( 'snack', 5 ); INSERT INTO goods( name, unit_price ) VALUES ( 'chocolate', 8 ); INSERT INTO goods( name, unit_price ) VALUES ( 'ice', 8 ); INSERT INTO goods( name, unit_price ) VALUES ( 'lollipop candy', 15 );
データの新規入力に関してはINSERT クエリー
を参照してください。
テーブルのデータを閲覧
入力したデータを閲覧するには SELECT 文を使用します。以下のように goods テーブルに保存されたデータを閲覧してみましょう。
SELECT * FROM goods; +----+----------------+------------+ | id | name | unit_price | +----+----------------+------------+ | 1 | candy | 3 | | 2 | snack | 5 | | 3 | chocolate | 8 | | 4 | ice | 8 | | 5 | lollipop candy | 15 | +----+----------------+------------+ 5 rows in set (0.00 sec)
*
はすべてのカラムデータを表示する指定です。
想定通りなら id は上記のようになります。以降でこの id を使用して外部キー制約を行います。
salses_slip テーブルにも幾つかのデータを入力してみます。
INSERT INTO sales_slip( goods_id, unit_price, amount ) VALUES ( 001, 3, 100 ); INSERT INTO sales_slip( goods_id, unit_price, amount ) VALUES ( 002, 8, 50 ); INSERT INTO sales_slip( goods_id, unit_price, amount ) VALUES ( 002, 5, 30 ); INSERT INTO sales_slip( goods_id, unit_price, amount ) VALUES ( 001, 5, 80 ); INSERT INTO sales_slip( goods_id, unit_price, amount ) VALUES ( 003, 10, 10 );
salses_slip テーブルに入力されたデータも閲覧してみます。
SELECT * FROM sales_slip; +----+---------------------+----------+------------+--------+ | id | date_time | goods_id | unit_price | amount | +----+---------------------+----------+------------+--------+ | 1 | 2023-09-26 22:29:57 | 1 | 3 | 100 | | 2 | 2023-09-26 22:29:58 | 2 | 8 | 50 | | 3 | 2023-09-28 22:42:10 | 2 | 5 | 30 | | 4 | 2023-09-28 22:42:10 | 1 | 5 | 80 | | 5 | 2023-09-28 22:42:53 | 3 | 10 | 10 | +----+---------------------+----------+------------+--------+
想定通りにデータが入力できたでしょうか。
内部結合
salses_slip テーブルデータを閲覧すると goods_id が表示されますが商品名が表示されません。商品を表示するには内部結合を使用することによって salses_slip と goods テーブルを組み合わせて閲覧します。
内部結合は INNER JOIN 句を使用します。
SELECT * FROM sales_slip INNER JOIN goods ON sales_slip.goods_id = goods.id; +----+---------------------+----------+------------+--------+----+-----------+------------+ | id | date_time | goods_id | unit_price | amount | id | name | unit_price | +----+---------------------+----------+------------+--------+----+-----------+------------+ | 1 | 2023-09-26 22:29:57 | 1 | 3 | 100 | 1 | candy | 3 | | 2 | 2023-09-26 22:29:58 | 2 | 8 | 50 | 2 | snack | 5 | | 3 | 2023-09-28 22:42:10 | 2 | 5 | 30 | 2 | snack | 5 | | 4 | 2023-09-28 22:42:10 | 1 | 5 | 80 | 1 | candy | 3 | | 5 | 2023-09-28 22:42:53 | 3 | 10 | 10 | 3 | chocolate | 8 | +----+---------------------+----------+------------+--------+----+-----------+------------+
内部結合は２つのテーブルデータのうち条件に一致するものだけを抽出します。
上記の例では*
によってすべてのカラムデータが結合順に並んでいます。
見た目に分かり難いのでカラムの並びと名称を明確にしてみます。
SELECT s.id, s.date_time, s.goods_id, g.name AS goods_name, s.unit_price, g.unit_price AS reference_price, s.amount, ( s.unit_price * s.amount ) AS price FROM sales_slip AS s INNER JOIN goods AS g ON s.goods_id = g.id; +----+---------------------+----------+------------+------------+-----------------+--------+-------+ | id | date_time | goods_id | goods_name | unit_price | reference_price | amount | price | +----+---------------------+----------+------------+------------+-----------------+--------+-------+ | 1 | 2023-09-26 22:29:57 | 1 | candy | 3 | 3 | 100 | 300 | | 2 | 2023-09-26 22:29:58 | 2 | snack | 8 | 5 | 50 | 400 | | 3 | 2023-09-28 22:42:10 | 2 | snack | 5 | 5 | 30 | 150 | | 4 | 2023-09-28 22:42:10 | 1 | candy | 5 | 3 | 80 | 400 | | 5 | 2023-09-28 22:42:53 | 3 | chocolate | 10 | 8 | 10 | 100 | +----+---------------------+----------+------------+------------+-----------------+--------+-------+
別名は AS 句で定義します。これで goods_id ごとの goods の名称を知ることができます。
このような複雑な SQL 文はビューとして作成しておくとそのビューを実行するだけで済みます。
データの閲覧に関してはSELCT クエリー
を参照してください。