ビュー
# ビューの作成 create view [view] as select...; # ビューの更新 update [view] set [col] = [new value] where ...; # ビューの確認 # テーブルへの操作と同じやり方で確認できる． show tables; desc [view]; show create view [view];
ビューとビューの元になったテーブルの関係
- 元になったテーブルを変更するとビューも変更される
- ビューに insert とすると，元のテーブルも変わる
- join で作られたビューには追加できない
ビューへのレコード追加に条件を指定する
create view ... with check option;
with check option を使わないとき
mysql> select * from member; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | 佐藤 | 30 | | 2 | 伊藤 | 25 | | 3 | 佐々木 | 19 | | 4 | 鈴木 | 18 | +----+-----------+------+ 4 rows in set (0.00 sec) # 年齢20歳以上を抽出したビュー mysql> create view over20 as select name, age from member where age >= 20; Query OK, 0 rows affected (0.00 sec) mysql> select * from over20; +--------+------+ | name | age | +--------+------+ | 佐藤 | 30 | | 伊藤 | 25 | +--------+------+ 2 rows in set (0.00 sec) # ビューの抽出条件を満たさないデータをビューに追加 mysql> insert into over20 values ('井上', 17); Query OK, 1 row affected (0.00 sec) # ビューには追加されない mysql> select * from over20; +--------+------+ | name | age | +--------+------+ | 佐藤 | 30 | | 伊藤 | 25 | +--------+------+ 2 rows in set (0.00 sec) # 元のテーブルに追加される mysql> select * from member; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | 佐藤 | 30 | | 2 | 伊藤 | 25 | | 3 | 佐々木 | 19 | | 4 | 鈴木 | 18 | | 5 | 井上 | 17 | +----+-----------+------+ 5 rows in set (0.00 sec)
with check option を使うとき
mysql> create view over20with as select name, age from member where age >= 20 with check option; # ビューの抽出条件に合わないレコードは追加できない mysql> insert into over20with values ('若者', 17); ERROR 1369 (HY000): CHECK OPTION failed 'example.over20with'
ビューの上書き
# 同名のビューが存在しないときは新規作成 # 存在するときは上書き（or replace がないときはエラー） create or replace view [view] as select ....;
ビューのカラム構造を変更
create するときと同じイメージで．
alter view [view] as select [col] from [tab];
ビューの削除
drop view [view]; # 削除対象がなくてもエラーを出さない drop view if exists [view];
忘れてたこと
レコードの更新
# 変更 update [tab] set [col] = [new-value] (where ...); # 削除 delete from [tab] where ...;
内部結合
内部結合はあるカラムをキーにして2つのテーブルのデータを結合するため，
キーの設定が必要．
mysql> select * from member; +----+-----------+ | id | name | +----+-----------+ | 1 | 佐藤 | | 2 | 伊藤 | | 3 | 佐々木 | | 4 | 鈴木 | +----+-----------+ 4 rows in set (0.00 sec) mysql> select * from score; +------+-------+ | id | score | +------+-------+ | 1 | 80 | | 2 | 60 | | 3 | 100 | | 4 | 70 | +------+-------+ 4 rows in set (0.00 sec) # キーは on / using で指定する # on を使うときは，tab1.col = tab2.col のようにテーブル名をつける mysql> select * from member as a join score as b on a.id = b.id; +----+-----------+------+-------+ | id | name | id | score | +----+-----------+------+-------+ | 1 | 佐藤 | 1 | 80 | | 2 | 伊藤 | 2 | 60 | | 3 | 佐々木 | 3 | 100 | | 4 | 鈴木 | 4 | 70 | +----+-----------+------+-------+ 4 rows in set (0.00 sec) # using は 2つのテーブルに同名のカラムがあるときのみ使える． # よって，on のときのようなテーブル指定(tab.col)は必要ない． mysql> select * from member as a join score as b using(id); +----+-----------+-------+ | id | name | score | +----+-----------+-------+ | 1 | 佐藤 | 80 | | 2 | 伊藤 | 60 | | 3 | 佐々木 | 100 | | 4 | 鈴木 | 70 | +----+-----------+-------+ 4 rows in set (0.00 sec)
having: グループ化したものに対する条件
where はグループ化したものを対象にできないので having を使う
# 平均点が60以上の人を取り出す select name, avg(point) from member group by name having avg(point) >= 60;
