reponの忘備録

「喉まででかかってる」状態を解消するためのメモ

LAMPのお勉強日記 (15)

LAMP環境のお勉強日記

目的

  • 自分が何をやったのか記録する
  • 自分が何を出来るのか公開する

経過

まず、MySQLの勉強をする(その後、PHPとの連携の勉強に移る予定)。

XAMPPにより、コマンドプロンプトで作業する。

リレーショナルデータベースとは(再掲)



はじめに

MySQLモニタに入る。
コマンドプロンプト

>mysql -u root -p
Enter password:[パスワード]
mysql>

使用するデータベースを指定する

mysql>use db1

今回学んだこと

  • 「ストアドプロシージャ」について
  • 「ストアドファンクション」について
  • 「トリガ」について

「ストアドプロシージャ」

いくつかのSQL文をまとめて、1コマンドで実行できるようにするもの。

  • 「create procedure [ストアドプロシージャ名]() begin [sql文] end」
  • ただし、sql文を入力する際にデリミタ「;」を入力するとその段階で実行されてしまうので、この文の前後に

「delimiter //」〜「delimiter ;」を付ける。

  • 「call [ストアドプロシージャ名]()」で実行
ex)
mysql>delimiter //
mysql>create procedure pr1()
    ->begin
    ->select * from tb;
    ->select * from tb1;
    ->end
    ->//
mysql>delimiter ;
mysql>call pr1();
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| a103 |  101 |    4 |
| a102 |   54 |    5 |
| a104 |  181 |    4 |
| a101 |  184 |    4 |
| a103 |   17 |    5 |
| a101 |  300 |    5 |
| a102 |  205 |    6 |
| a104 |   93 |    5 |
| a103 |   12 |    6 |
| a107 |   87 |    6 |
+------+------+------+
10 rows in set (0.20 sec)

+------+------------+------+
| bang | nama       | tosi |
+------+------------+------+
| a101 | shunin-sat |   40 |
| a102 | takahashi  |   28 |
| a103 | nakagawa   |   20 |
| a104 | watanabe   |   23 |
| a105 | nishizawa  |   35 |
| NULL | baito-isid |   18 |
+------+------------+------+
6 rows in set (0.25 sec)
引数を指定して実行するストアドプロシージャ

「procedure [ストアドプロシージャ名(引数名 データ型);」

ex)
mysql>delimiter //
mysql>create procedure pr2(d int)
    ->begin
    ->select * from tb where uria>=d;
    ->end
    ->//
mysql>delimiter ;
mysql>call pr2(200);
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| a101 |  300 |    5 |
| a102 |  205 |    6 |
+------+------+------+
ストアドプロシージャの内容を表示・削除

表示「show create procedure [プロシージャ名];」

ex)
mysql>show create procedure pr1;
+-----------+----------+--------------------------------------------------------
--------------------------------------------+----------------------+------------
----------+--------------------+
| Procedure | sql_mode | Create Procedure
                                            | character_set_client | collation_c
onnection | Database Collation |
+-----------+----------+--------------------------------------------------------
--------------------------------------------+----------------------+------------
----------+--------------------+
| pr1       |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `pr1`()
begin
select * from tb;
select * from tb1;
end | sjis                 | sjis_japanese_ci     | latin1_swedish_ci  |
+-----------+----------+--------------------------------------------------------
--------------------------------------------+----------------------+------------
----------+--------------------+


削除drop procedure [プロシージャ名]」

mysql>drop procedure pr1;
mysql>drop procedure pr2;

「ストアドファンクション」

「ストアドプロシージャ」とほぼ同じだが、戻り値がある点が異なる。

ちょうど関数のようなもの。ユーザ定義関数と呼ばれることもある。

  • 「create function [ストアドファンクション名(引数 データ型)] returns [返す値のデータ型] begin [SQL文] return [返す値・式] end]
  • 実行は、「select [ストアドファンクション名(引数)]
例1−標準体重を返すストアドファンクション
ex)
mysql>delimiter //
mysql>create function fu1(sintyo int) returns double
    ->begin 
    ->return sintyo * sintyo * 22/10000;
    ->end
    ->//
mysql>delimiter ;
mysql> select fu1(172);
+----------+
| fu1(172) |
+----------+
|  65.0848 |
+----------+
例2−レコードの平均値を返すストアドファンクション
  • 変数の定義「declare 変数名 データ型」
ex)
mysql>delimiter //
mysql>create function fu2() returns double
    ->begin
    ->declare r double;
    ->select avg(uria) into r from tb;
    ->return r;
    ->end
    ->//
mysql>delimiter ;
mysql>select fu2();
+-------+
| fu2() |
+-------+
| 123.4 |
+-------+
ストアドファンクションの内容を表示・削除


表示「show create function [ストアドファンクション名];」

ex)
mysql>show create function fu1;
+----------+----------+---------------------------------------------------------
-------------------------------------------------------------------+------------
----------+----------------------+--------------------+
| Function | sql_mode | Create Function
                                                                   | character_s
et_client | collation_connection | Database Collation |
+----------+----------+---------------------------------------------------------
-------------------------------------------------------------------+------------
----------+----------------------+--------------------+
| fu1      |          | CREATE DEFINER=`root`@`localhost` FUNCTION `fu1`(sintyo
int) RETURNS double
begin
return sintyo * sintyo * 22 / 10000;
end | sjis                 | sjis_japanese_ci     | latin1_swedish_ci  |
+----------+----------+---------------------------------------------------------
-------------------------------------------------------------------+------------
----------+----------------------+--------------------+


削除drop function [ストアドファンクション名]」

mysql>drop function fu1;
mysql>drop function fu2;

「トリガ」

テーブルに対してある処理が行われると、それが引き金(トリガ)となってコマンドが実行される仕組み。


たとえば、「insert」「update」「delete」などのコマンドに対応させておくと便利。

  • 「create trigger [トリガ名] before・after [deleteなどのコマンド] on [テーブル名] for each row begin [更新前(old.カラム名)又は更新後(new.カラム名)を使った処理] end」


トリガが呼び出されるタイミングは、

コマンド 実行前(old.カラム名)※「before」を使う 実行後(new.カラム名)※「after」を使う
insert
×
delete
×
update

例1−削除したデータを保存する
ex)
mysql>create table tb1_back like tb1;
mysql>delimiter //
mysql>create trigger tr1 before delete on tb1 for each row
    ->begin
    ->insert into tb1_back values(old.bang,old.nama,old.tosi);
    ->end
    ->//
mysql>delimiter ;
mysql>select * from tb1;
+------+------------+------+
| bang | nama       | tosi |
+------+------------+------+
| a101 | shunin-sat |   40 |
| a102 | takahashi  |   28 |
| a103 | nakagawa   |   20 |
| a104 | watanabe   |   23 |
| a105 | nishizawa  |   35 |
| NULL | baito-isid |   18 |
+------+------------+------+
mysql>select * from tb1_back;
Empty set (0.00 sec)
mysql>delete from tb1;
mysql> select * from tb1;
Empty set (0.00 sec)
mysql> select * from tb1_back;
+------+------------+------+
| bang | nama       | tosi |
+------+------------+------+
| a101 | shunin-sat |   40 |
| a102 | takahashi  |   28 |
| a103 | nakagawa   |   20 |
| a104 | watanabe   |   23 |
| a105 | nishizawa  |   35 |
| NULL | baito-isid |   18 |
+------+------------+------+

deleteされた「tb1」の内容が「tb_back」に挿入されている。

トリガの確認・削除

確認「show triggers;」

mysql>show triggers;
+---------+--------+-------+----------------------------------------------------
----------------+--------+---------+----------+----------------+----------------
------+----------------------+--------------------+
| Trigger | Event  | Table | Statement
                | Timing | Created | sql_mode | Definer        | character_set_c
lient | collation_connection | Database Collation |
+---------+--------+-------+----------------------------------------------------
----------------+--------+---------+----------+----------------+----------------
------+----------------------+--------------------+
| tr1     | DELETE | tb1   | begin
insert into tb1_back values(old.bang,old.nama,old.tosi);
end | BEFORE | NULL    |          | root@localhost | sjis                 | sjis
_japanese_ci     | latin1_swedish_ci  |
+---------+--------+-------+----------------------------------------------------
----------------+--------+---------+----------+----------------+----------------
------+----------------------+--------------------+


削除drop trigger [トリガ名];」


今回はここまで。

次回は「トランザクション」の予定。

おしらせ

つながり合うSNS


http://survive-sns.jp/


ぜひお気軽にご参加ください。


サーバを新たに立て、一新した「サバイブSNS2.0」を、どうぞ一度覗いてみてください。

人生の「旅の仲間」が見つかるかも知れませんよ。