■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 行)

みっしーが一緒に削除されている。


タグ:

+ タグ編集
  • タグ:

このサイトはreCAPTCHAによって保護されており、Googleの プライバシーポリシー利用規約 が適用されます。

最終更新:2005年10月26日 17:29