■SQLメモ(4)
1.直接オリジナルのデータに手を加えるのは危険なのでVIEWを作成する。CDテーブルからタイトルと値段だけを抽出し、VIEWを作成する。
SQL文
CREATE VIEW cdview AS SELECT title, price FROM cdtable;
結果
title | price
----------------------+-------
Truth | 2446
Lune | 2446
manabee Happy Wave | 2440
Angel Fish | 1050
Lrycal Step | 1300
chara de Rie | 1701
彼女はゴキゲンななめ | 1890
魂 | 2100
みずうみ | 2905
ビタミン パンチ | 3000
(10 行)
2.さらに、声優も追加してVIEWを作成する。ただし、声優は声優テーブルから選ぶ。
SQL文
CREATE VIEW cdview2 AS SELECT title,name,price FROM cdtable, vatable WHERE cdtable.va = vatable.name;
結果
title | name | price
--------------------+--------------+-------
みずうみ | 井上喜久子 | 2905
魂 | 宮村優子 | 2100
manabee Happy Wave | 水野愛日 | 2440
Angel Fish | 清水愛 | 1050
Truth | 折笠愛 | 2446
Lune | 折笠富美子 | 2446
Lrycal Step | 田村ゆかり | 1300
chara de Rie | 田中理恵 | 1701
ビタミン パンチ | 國府田マリ子 | 3000
(9 行)
3.上のVIEWでさらに、若手だけのものにする。
SQL文
CREATE VIEW cdview3 AS SELECT title,name,price FROM cdtable, vatable WHERE cdtable.va = vatable.name AND vatable.age <= 30;
結果
title | name | price
-------------------+------------+-------
manabee Happy Wave | 水野愛日 | 2440
Angel Fish | 清水愛 | 1050
Lune | 折笠富美子 | 2446
Lrycal Step | 田村ゆかり | 1300
chara de Rie | 田中理恵 | 1701
(5 行)
4.というわけなのでcdview2はいらなくなった。削除したい。
SQL文
DROP VIEW cdview2;
結果
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+------------+----------+--------
public | cdtable | テーブル | kyoko
public | cdview | ビュー | kyoko
public | cdview3 | ビュー | kyoko
public | newcdtable | テーブル | kyoko
public | vatable | テーブル | kyoko
(5 行)
削除されている。
5.インデックスをつけて検索を速くする。
SQL文
CREATE INDEX vaindex ON vatable(name);
結果(\diで見る)
リレーションの一覧
スキーマ | 名前 | 型 | 所有者 | テーブル
--------+---------+-----------+--------+-------
public | vaindex | インデックス | hoge | vatable
(1 行)
6.やっぱりいらない。削除したい。
SQL文
DROP INDEX vaindex;
\diの結果
リレーションがありません
7.制約条件をつけて、Kanonテーブルを作成する。
- id
- キャラクターID。固定長3文字。制約は主キーで、NULLでない。
- character
- キャラクター名。テキスト。制約は初期値が未登録。
- cv
- 声優。テキスト。制約なし。
SQL文
CREATE TABLE kanon_table (
id CHAR(3) NOT NULL PRIMARY KEY,
character TEXT DEFAULT '未登録',
cv TEXT );
結果
テーブル "public.kanon_table"
カラム | 型 | 修飾語
-----------+--------------+------------------------
id | character(3) | not null
character | text | default '未登録'::text
cv | text |
インデックス:
"kanon_table_pkey" プライマリキー btree (id)
8.値の代入。(出典:Kanon公式HP)
SQL文
INSERT INTO kanon_table VALUES('001', '月宮あゆ', '堀江由衣');
INSERT INTO kanon_table VALUES('002', '水瀬名雪', '国府田マリ子');
INSERT INTO kanon_table VALUES('003', '沢渡真琴', '飯塚雅弓');
INSERT INTO kanon_table VALUES('004', '川澄舞', '田村ゆかり');
INSERT INTO kanon_table VALUES('005', '美坂栞', '佐藤朱');
INSERT INTO kanon_table VALUES('006', '水瀬秋子', '皆口裕子');
INSERT INTO kanon_table VALUES('007', '美坂香里', '川澄綾子');
INSERT INTO kanon_table VALUES('008', '天野美汐', '坂本真綾');
INSERT INTO kanon_table VALUES('009', '倉田佐祐里', '川上とも子');
結果
id | character | cv
-----+------------+--------------
001 | 月宮あゆ | 堀江由衣
002 | 水瀬名雪 | 国府田マリ子
003 | 沢渡真琴 | 飯塚雅弓
004 | 川澄舞 | 田村ゆかり
005 | 美坂栞 | 佐藤朱
006 | 水瀬秋子 | 皆口裕子
007 | 美坂香里 | 川澄綾子
008 | 天野美汐 | 坂本真綾
009 | 倉田佐祐里 | 川上とも子
(9 行)
※ここで、INSERT INTO kanon_table VALUES( '相沢祐一', '私市淳');などとすると制限のため、エラーになる。
また、INSERT INTO kanon_table VALUES('010')とすると、
id | character | cv
-----+------------+--------------
001 | 月宮あゆ | 堀江由衣
002 | 水瀬名雪 | 国府田マリ子
003 | 沢渡真琴 | 飯塚雅弓
004 | 川澄舞 | 田村ゆかり
005 | 美坂栞 | 佐藤朱
006 | 水瀬秋子 | 皆口裕子
007 | 美坂香里 | 川澄綾子
008 | 天野美汐 | 坂本真綾
009 | 倉田佐祐里 | 川上とも子
010 | 未登録 |
(10 行)
となる。
9.そろそろ、データのバックアップがしたい。ファイルにnewcdtableを書き出すには?。
SQL文(?)
\COPY newcdtable TO '/tmp/newcdtable.csv'
結果
001 Truth 折笠愛 1957 G01 3
002 Lune 折笠富美子 1957 G01 2
003 manabee Happy Wave 水野愛日 1952 G01 5
004 Angel Fish 清水愛 840 K01 7
005 Lrycal Step 田村ゆかり 1040 K01 2
006 chara de Rie 田中理恵 1361 V01 1
010 魂 宮村優子 1680 V01 4
009 みずうみ 井上喜久子 2324 P01 2
008 ビタミン パンチ 國府田マリ子 2400 K01 7
10.新たに制限を次の制限をつけてlimit_cdtableを作成したい。
- id
- CDのID。固定長3文字。制約は主キーで、NULLでない。
- title
- CDのタイトル。テキスト。制約はNULLでない。
- va
- 声優。テキスト。制約は同じものが存在しない。
- price
- 値段。整数。制約は0円以上。
- labelid
- レーベルのID。固定長3文字。制約なし。
- stock
- 在庫。整数。制約は0以上。
SQL文
CREATE TABLE limit_cdtable (
id CHAR(3) NOT NULL PRIMARY KEY,
title TEXT NOT NULL,
va TEXT NOT NULL UNIQUE,
price INTEGER CHECK( price > 0 ),
labelid CHAR(3),
stock INTEGER CHECK( stock > 0 ) );
結果
テーブル "public.limit_cdtable"
カラム | 型 | 修飾語
---------+--------------+----------
id | character(3) | not null
title | text | not null
va | text | not null
price | integer |
labelid | character(3) |
stock | integer |
インデックス:
"limit_cdtable_pkey" プライマリキー btree (id)
"limit_cdtable_va_key" ユニーク, btree (va)
CHECK 制約:
"limit_cdtable_price" CHECK (price > 0)
"limit_cdtable_stock" CHECK (stock > 0)
11.元のデータのコピーしたい。
SQL文
\COPY limit_cdtable FROM '/tmp/newcdtable.txt'
結果
id | title | va | price | labelid | stock
-----+--------------------+--------------+-------+---------+-------
001 | Truth | 折笠愛 | 1957 | G01 | 3
002 | Lune | 折笠富美子 | 1957 | G01 | 2
003 | manabee Happy Wave | 水野愛日 | 1952 | G01 | 5
004 | Angel Fish | 清水愛 | 840 | K01 | 7
005 | Lrycal Step | 田村ゆかり | 1040 | K01 | 2
006 | chara de Rie | 田中理恵 | 1361 | V01 | 1
010 | 魂 | 宮村優子 | 1680 | V01 | 4
009 | みずうみ | 井上喜久子 | 2324 | P01 | 2
008 | ビタミン パンチ | 國府田マリ子 | 2400 | K01 | 7
(9 行)
このテーブルには制限がついているため、こんなことはできない。
INSERT INTO limit_cdtable VALUES('DEAD SET', 'Angela',1050,'K01',3); → IDがないためエラー
INSERT INTO limit_cdtable VALUES('009','DEAD SET', 'Angela',1050,'K01',-1); →在庫が-1なのでエラー。うっかりミス防止。
12.声優テーブルをKanonテーブルに対応させるため、新たにnew_vatableを作成し、データを追加する。
SQL文
CREATE TABLE new_vatable(
id char(4) NOT NULL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
age INTEGER,
birthday DATE );
INSERT INTO new_vatable VALUES('V001', '折笠愛', 41, '1963-12-12');
INSERT INTO new_vatable VALUES('V002', '折笠富美子', 30, '1974-12-27');
INSERT INTO new_vatable VALUES('V003', '水野愛日', 28, '1977-07-27');
INSERT INTO new_vatable VALUES('V004', '清水愛', 24, '1981-03-26');
INSERT INTO new_vatable VALUES('V005', '田村ゆかり', 29, '1976-02-27');
INSERT INTO new_vatable VALUES('V006', '田中理恵', 26, '1979-01-03');
INSERT INTO new_vatable VALUES('V007', '國府田マリ子', 36, '1969-09-05');
INSERT INTO new_vatable VALUES('V008', '井上喜久子', 41, '1964-09-25');
INSERT INTO new_vatable VALUES('V009', '宮村優子', 32, '1972-12-04');
INSERT INTO new_vatable VALUES('V010','堀江由衣',29,'1976-09-20');
INSERT INTO new_vatable VALUES('V011','飯塚雅弓',28,'1977-01-03');
INSERT INTO new_vatable VALUES('V012','佐藤朱',25,'1980-05-16');
INSERT INTO new_vatable VALUES('V013','皆口裕子',39,'1966-6-26');
INSERT INTO new_vatable VALUES('V014','川澄綾子',29,'1976-3-30');
INSERT INTO new_vatable VALUES('V015','坂本真綾',25,'1980-3-31');
INSERT INTO new_vatable VALUES('V016','川上とも子',34,'1971-4-25');
結果
id | name | age | birthday
-----+--------------+-----+------------
V001 | 折笠愛 | 41 | 1963-12-12
V002 | 折笠富美子 | 30 | 1974-12-27
V003 | 水野愛日 | 28 | 1977-07-27
V004 | 清水愛 | 24 | 1981-03-26
V005 | 田村ゆかり | 29 | 1976-02-27
V006 | 田中理恵 | 26 | 1979-01-03
V007 | 國府田マリ子 | 36 | 1969-09-05
V008 | 井上喜久子 | 41 | 1964-09-25
V009 | 宮村優子 | 32 | 1972-12-04
V010 | 堀江由衣 | 29 | 1976-09-20
V011 | 飯塚雅弓 | 28 | 1977-01-03
V012 | 佐藤朱 | 25 | 1980-05-16
V013 | 皆口裕子 | 39 | 1966-06-26
V014 | 川澄綾子 | 29 | 1976-03-30
V015 | 坂本真綾 | 25 | 1980-03-31
V016 | 川上とも子 | 34 | 1971-04-25
(16 行)
13.Kanonテーブルを作り直す。ただし、制限を加える。
- id
- キャラクターID。固定長3文字。制約は主キーで、NULLでない。
- character
- キャラクター名。テキスト。制約は同一のものが存在せず、NULLでない。
- cvid
- 声優ID。固定長4文字。外部キー。声優テーブルと連動して削除される。
SQL文
CREATE TABLE new_kanon_table(
id CHAR(3) NOT NULL PRIMARY KEY,
character TEXT NOT NULL UNIQUE,
cvid CHAR(4),
FOREIGN KEY(cvid) REFERENCES new_vatable(id) ON DELETE CASCADE );
結果
テーブル "public.new_kanon_table"
カラム | 型 | 修飾語
----------+--------------+----------
id | character(3) | not null
character | text | not null
cvid | character(4) |
インデックス:
"new_kanon_table_pkey" プライマリキー btree (id)
"new_kanon_table_character_key" ユニーク, btree ("character")
外部キー制約:
"$1" FOREIGN KEY (cvid) REFERENCES new_vatable(id) ON DELETE CASCADE
次に、値を挿入する。
SQL文
INSERT INTO new_kanon_table VALUES('001', '月宮あゆ', 'V010');
INSERT INTO new_kanon_table VALUES('002', '水瀬名雪', 'V007');
INSERT INTO new_kanon_table VALUES('003', '沢渡真琴', 'V011');
INSERT INTO new_kanon_table VALUES('004', '川澄舞', 'V005');
INSERT INTO new_kanon_table VALUES('005', '美坂栞', 'V012');
INSERT INTO new_kanon_table VALUES('006', '水瀬秋子', 'V013');
INSERT INTO new_kanon_table VALUES('007', '美坂香里', 'V014');
INSERT INTO new_kanon_table VALUES('008', '天野美汐', 'V015');
INSERT INTO new_kanon_table VALUES('009', '倉田佐祐里', 'V016');
結果
id | character | cvid
----+------------+------
001 | 月宮あゆ | V010
002 | 水瀬名雪 | V007
003 | 沢渡真琴 | V011
004 | 川澄舞 | V005
005 | 美坂栞 | V012
006 | 水瀬秋子 | V013
007 | 美坂香里 | V014
008 | 天野美汐 | V015
009 | 倉田佐祐里 | V016
(9 行)
14.このKanonテーブルで「坂本真綾」を消してみる。
SQL文
DELETE FROM new_vatable WHERE name = '坂本真綾';
結果
new_vatable
id | name | age | birthday
-----+--------------+-----+------------
V001 | 折笠愛 | 41 | 1963-12-12
V002 | 折笠富美子 | 30 | 1974-12-27
V003 | 水野愛日 | 28 | 1977-07-27
V004 | 清水愛 | 24 | 1981-03-26
V005 | 田村ゆかり | 29 | 1976-02-27
V006 | 田中理恵 | 26 | 1979-01-03
V007 | 國府田マリ子 | 36 | 1969-09-05
V008 | 井上喜久子 | 41 | 1964-09-25
V009 | 宮村優子 | 32 | 1972-12-04
V010 | 堀江由衣 | 29 | 1976-09-20
V011 | 飯塚雅弓 | 28 | 1977-01-03
V012 | 佐藤朱 | 25 | 1980-05-16
V013 | 皆口裕子 | 39 | 1966-06-26
V014 | 川澄綾子 | 29 | 1976-03-30
V016 | 川上とも子 | 34 | 1971-04-25
(15 行)
new_kanon_table
id | character | cvid
----+------------+------
001 | 月宮あゆ | V010
002 | 水瀬名雪 | V007
003 | 沢渡真琴 | V011
004 | 川澄舞 | V005
005 | 美坂栞 | V012
006 | 水瀬秋子 | V013
007 | 美坂香里 | V014
009 | 倉田佐祐里 | V016
(8 行)
みっしーが一緒に削除されている。
最終更新:2005年10月26日 17:29