MySQL/dbonline/view

Last-modified: 2010-10-21 (木) 18:36:49

ビュー

　・ビューとは
　　SELECT文を使う時に条件を指定することで取得するカラムを絞ったり
　　取得するデータの条件を指定することができるが、
　　テーブルから一部を取り出したものに名前を付け、あたかも
　　独立したテーブルにように扱うようにしたものがビューである
　　（表示用の仮想テーブルのようなもの）
　
　・使い道
　　selectを毎回使うと大変な時に使う。
　　テーブルを結合して、１時的に参照する場合などに使ってもＯＫ

ビューの作成

　・書式

　　CREATE VIEW db_name.view_name AS select_statement;

　　つまり、ASの後ろは普通のselect文を書けばＯＫ
　　ちなみに、use db_name済みの場合は、view_nameだけでおｋ

作成したビューの扱い

　　（１）テーブルとまったく同じもの

　　　select * from hogehoge_view;
　　　show tables;

　
　　（２）コマンド名が違うが、同じもの

　　　show create view
　　　drop view

　
　　（３）テーブルと違うもの

　　　ALTER VIEW view_name[(column_list)] AS select_statement;

　　　これは、create or replace viewと同じ意味。
　　　作成or置き換えで、なければ作成し、あれば、新しいものに置き換える
　　　alter table ～のようにビューを一部変更するものではない

トリガ

　・トリガとは
　　テーブルに対してデータの追加や更新などが行われた場合に、
　　自動的に別のSQL文を実行するように設定するもの
　
　・使用例
　　あるテーブルのカラムの値を更新すると、自動的に他のテーブルの
　　カラムの値を同じ値に更新するといった処理が行える
　　たとえば、ログの追加などには効果的

トリガの作成

トリガの作成 

　　CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
　　ON tbl_name FOR EACH ROW trigger_stmt

　　※INSERTとあるが、テーブルに行が挿入されるときにSQL文が実行されるもので、
　　　INSERT文だけではなく、REPLACE文にも反応する
　　※UPDATEは行の修正時　→　UPDATE文に反応
　　※DELETEは、行の削除時。→DELETEやREPLACE文

複数文の実行

　　trigger_stmtには、通常のSQL文を入れればＯＫ。ただし、
　　1つのテーブルに対して同じ種類のトリガを複数作成することはできない
　　そこで、以下のようにして、複数トリガを設定できるようになっている。
　　
　　１）create triggerを delimiter $$　～　$$ delimiter;でくくる
　　　　※ちなみにDELIMITER　＝　区切り文字
　　２）複数のSQL文をBEGIN　～　ENDでくくる

　　DELIMITER $$

　　CREATE TRIGGER trigger1 AFTER DELETE ON table1 FOR EACH ROW
　　BEGIN
　　　DELETE FROM table2 ... ;
　　　INSERT INTO table3 ... ;
　　END;
　　$$

　　DELIMITER ;

　　※DELIMITERを使う意義
　　　SQL文の最後には、;を付けるから、トリガ用のSQL文にも;が付いている。
　　　しかし、これだとCREATE TRIGGER文が ; で終わってしまう。
　　　だから、DELIMITERを使って ; ではなく、別の文字列（ここでは$$）が
　　　来るまでを１つの文として扱えるようにしている。

　　　ただし、最後に文の区切り文字を ; に戻すのを忘れずに！

FOR EACH ROU

　　　MySQLのトリガではFOR EACH ROWのみがサポートされている。
　　　対象テーブルに対しデータが1行処理される度にトリガが1回実行されることを意味する。

　　　※１文につき１回トリガ実行のFOR EACH STATEMENTというのもがあるが、MySQLでは不可

トリガの表示 

　SHOW TRIGGERS;
　SHOW TRIGGERS db_name;

　LIKE句を使った絞り込みも可能だが、トリガ名ではなく、
　トリガの対象となるテーブルにマッチさせるようにする

　SHOW TRIGGERS LIKE 'pattern';
　SHOW TRIGGERS db_name LIKE 'pattern';

　例）userというテーブルがある場合
　　　show triggers like 'user';

トリガ中にトリガ実行前の値と実行後の値を両方ゲットしたい場合 

　NEW.カラム名 INSERT及びUPDATEで使用可能
　OLD.カラム名 DELETE及びUPDATEで使用可能

　例）goodsテーブルにトリガをかましたが、
　　　goodsテーブルのnameカラムの新しいほう（INSERTorUPDATE後のデータ）をlistテーブルに追加したいとき

　　DELIMITER $$

　　CREATE TRIGGER trigger1 AFTER INSERT ON goods FOR EACH ROW
　　 BEGIN
　　 INSERT INTO list values(NEW.name);
　 　END;
　　$$

　　DELIMITER ;

　・ちょっと高度な使い方

　　mysql> delimiter $$

　　mysql> create trigger trigger_update after update on user for each row
　　 　　-> begin
　 　 　 -> update address set name = new.name where name = old.name;
　　 　　-> end;
　 　 　 -> $$

　　mysql> delimiter ;

　　日本語訳）userテーブルをUPDATEしたときに、
　　　　　　　addressテーブルのnameが、(userの)古いnameカラムと同じなら
　　　　　　　addressテーブルのnameを、(userの)新しいnameカラムにしろ
　
　　→結果的に、userテーブルとaddressテーブルのnameカラムに対して、
　　　自動で同期をとることができる

トリガの削除 

　DROP TRIGGER trigger_name

　※IF NOT EXISTSを使うと（ｒｙ