みんなの為のMySQLまとめ（７）

■ストアドプロシージャの作成

ストアドプロシージャの作成はCREATE PROCEDUREを使います。
CREATE PROCEDUREは、「CREATE PROCEDURE」から「END;//」までが一つの文になる。「BEGIN」と「END」の間に命令文を書きます。
参考
http://ryus.co.jp/modules/d3blog/details.php?bid=92

DELIMITER //
CREATE PROCEDURE sample1()
BEGIN
SELECT * FROM testm;
END
//
　
DELIMITER //

■ビューの作成

テーブルの中身を取り出し、名前を付け独立したテーブルのように扱うことができる。
参考
http://www.dbonline.jp/mysql/view/index1.html

create view test1
as select * from testm;

・次はビューを作成し「testm」テーブルの中の「data1」カラムの値が'1'のものだけを取得するビューを作成してみます。

create view test2
as select key1,data1,data2,data3 from testm where data1 = '1';

■トリガの作成

トリガを作成するとテーブルに対してデータの追加や更新などが行われた場合に、自動的に別のSQL文を実行するように設定することができます。例えばあるテーブルのカラムの値を更新すると、自動的に他のテーブルのカラムの値を同じ値に更新するといった処理が行えます。
参考
http://www.dbonline.jp/mysql/trigger/index1.html

・ここではトリガの動作を確認するための簡単なサンプルを試してみます。次のような2つのテーブルを作成します。

まずINSERTトリガを一つ作成します。

delimiter //
create trigger trigger_insert after insert on user for each row
bigin
insert into log(logtext) values ('INSERT');
end;
//
　
delimiter ;

続いてDELETEトリガを一つ作成します。

delimiter //
create trigger trigger_delete after delete on user for each row
begin
insert into log(logtext) values ('DELETE');
end;
//
　
delimiter;

「user」テーブルにデータを追加します。

insert into user values(1,'星野')

「user」テーブルにはINSERTトリガが作成されていますので「user」テーブルにデータを追加すると「log」テーブルにも自動的にデータが追加されます。では「log」テーブルを確認してみます。

このように「log」テーブルにデータが追加されていることが確認できます。
さらに、３件追加してみる。

「log」テーブルを確認してみます。

・「user」テーブルにはDELETEトリガも作成されていますので「user」テーブルからデータを削除すると「log」テーブルにも自動的にデータが追加されます。では「user」テーブルからデータを2つ削除してみます。

delete from user where id >= 3;

「log」テーブルを確認します。

「log」テーブルにデータが2つ追加されていることが確認できます。DELETE文は一度しか実行されていませんが、DELETE文によって2つのデータが削除されているため2回トリガが実行されています。

■CSVファイル入出力

参考
http://sasuke.main.jp/sqlcsv.html

・入力 LOAD DATA INFILE
「,」などで区切られたCSVファイルを用意することにより、MySQLに高速にデータを入力することができます。

構文
LOAD DATA INFILE "ファイル名" INTO TABLE テーブル名
FIELDS TERMINATED BY ',区切り文字' ENCLOSED BY '"';

ファイルの中身

a005,1,2,3
a006,1,2,3
a007,1,2,3

実際に打ったコマンド
LOAD DATA INFILE "test.csv" INTO TABLE testm FIELDS TERMINATED BY ',' ENCLOSED BY '''';

・出力　INTO OUTFILE
「SELECT」を実施する際に、「INTO OUTFILE」コマンドを追記することで、CSVファイルにデータを出力することが出来ます。

構文
SELECT * FROM テーブル名 INTO OUTFILE "ファイル名" FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '囲み文字の指定';

「testm」表をファイルに出力した例。

出力したファイルの中身

'a001',1,2,3
'a011',1,2,3
'b002',10,20,30
'c003',100,200,300
'a005',1,2,3
'a006',1,2,3
'a007',1,2,3