MySQL018 トランザクション
chap13
■ストレージエンジン
MySQLには大きく２つの機能に分かれる。
1)フロント処理（「接続する」「SQL内容を事前調査する」など）
2)バック処理（フロントの指示により、実際の検索やファイル操作などを行う）
２の部分をストレージエンジンという。
■ストレージエンジンの種類
テーブルごとにエンジンの種類を指定可能
・MyISAM(ver5.1までのデフォ。トランザクション未対応)
・InnoDB(5.5以降。トランザクション対応。) ★ほぼこれ利用
・ISAM(MyISAMの原型。古い。)
・MEMORY(データを全てメモリ上に管理。動作が高速)
・MERGE(MyISAMの複数テーブルを１つのものにして扱う)
・利用できるエンジンの確認
show engines;
■コマンド
・ストレージエンジンの確認
show create table tb10; //ENGINE=InnoDB
・ストレージエンジンの変更
alter table tb10 ENGINE=MyISAM;
alter table tb10 ENGINE=InnoDB;
■トランザクション
・複数の処理をまとめて扱う機能のこと。
・結果をDB反映させることを「コミット」
・結果を反映せず戻すことを「ロールバック」
・トランザクション開始
start transaction;
・ロールバック
rollback;
・コミット
commit;
■自動コミット機能
基本はONになっているため、常にコマンド後にcommitが実行されてる。
・offにする
set autocommit=0;
・onにする
set autocommit=1;
・途中終了した場合
create table tran (a int);
insert into tran values(100);
select * from tran;
start transaction;
update tran set a=777;
MySQLモニタ終了
→値が100に戻ってることを確認
■参考
本：基礎からのMySQL
MySQL017 トリガー
chap12
■トリガーとは
・MySQL5.0以降で利用可能。
・テーブルに対しある処理が行われると、コマンドが実行される仕組み。
■事前準備
・tb11と同じ構造で空のテーブルを用意
create table tb11m like tb11;
■トリガー作成
==============
タイミング
・before
・after
カラム値
・old.clmName
・new.clmName
注意（コマンドにより取り出せないものあり）
・INSERT 実行前× 実行後○
・DELETE 実行前○ 実行後×
・UPDATE 実行前○ 実行後○
==============
delimiter //
create trigger tr1
before delete on tb11 for each row #tb11が削除された時
begin
insert into tb11m values(old.bang, old.nama, old.tosi); #tb11mへ値を入れる
end //
delimiter ;
delete from tb11;
select * from tb11; //確認
select * from tb11m; //確認
■確認・削除
show triggers; //一覧で表示。sをつける。
drop trigger tr1;
・練習
月を入れると、その月の売上合計を返す関数を作成
delimiter //
create function f_uria(t int) returns int
begin
declare u int;
select sum(uria) into u from tb10 where tuki=t;
return u;
end //
delimiter ;
select f_uria(4);
select f_uria(5);
select f_uria(6);
select f_uria(2);
■参考
本：基礎からのMySQL
MySQL016 ストアドファンクション
chap12
■ストアドファンクション(Stored function)とは
・MySQL5.0以降で利用可能。
・プロシージャーとほぼ同じ。
・違いは「実行時に値を返す」ところ
・要するに「事前に関数を作っておく」ということ
■create/execute
作成
delimiter //
create function func1(sincho int) returns double
begin
return sincho * sincho *22/10000;
end //
delimiter ;
実行
select func1(174);
■レコードの平均値を返す関数
・declareで変数宣言
delimiter //
create function func2() returns double
begin
declare r double;
select avg(uria) into r from tb10;
return r;
end //
delimiter ;
select func2();
■display/delete
表示
show create function func2;
削除
drop function func2;
■参考
本：基礎からのMySQL
MySQL015 ストアドプロシージャー
chap12
■ストアドプシージャー(Stored Procedure)とは
・MySQL5.0以降で利用可能。
・バッチ処理のようなもの。
・定義済みのものは「CALL xxx」で呼び出す。
・デリミタの開始終了設定に注意 ★
■ストアドプロシージャーの作成
CREATE PROCEDURE
1~3step
1)delimiter //
2)
create procedure pr1()
begin
select * from tb10;
select * from tb11;
select * from tb12;
end //
3)delimiter ;
4)call pr1;
■引数を持つプロシージャー
・売上がx万円以上のものを抽出
delimiter //
create procedure pr2(x int)
begin
select * from tb10 where uria>=x;
end //
delimiter ;
call pr2(100);
call pr2(200);
■内容の表示・削除
表示
show create procedure pr1;
削除
drop procedure pr1;
■参考
本：基礎からのMySQL
MySQL014 ビュー（書き込み制限・変更）
chap11
■書き込み制限
・ビューへのinsertには制限がある。
union,join,サブクエリなどで複数テーブルが複合されたものはできない。
純粋な１テーブルなら可能。ただし、必須カラムがあると多分エラーになる。
insert into v1 values('アルバイト・石田',18);
・ビューの条件に合っていなくても、元テーブルには挿入される(売上100万以上)
create view v3 as
select bang,uria from tb10 where uria>=100;
insert into v3 values('意地悪',50);
・ビューの条件に合っていない場合はエラーとする。(売上100万未満はエラー)
WITH CHECK OPTION
create view v4 as
select bang,uria from tb10
where uria>100 with check option;
insert into v4 values('意地悪',50);
//ERROR 1369 check option failed 'db1.v4'
■ビューの上書き・変更・削除
・上書き(OR REPLACE)
create or replace view v1 as select now();
・カラム構造を変更(ALTER VIEW)
alter view v1 as select nama,tosi from tb11;
・削除
drop view if exists v1;
//dropでは対象ビューが存在しないとエラーになるため、if existsを付与
・練習
create view v_uria as
select bang,avg(uria) from tb10
where uria>=50
group by bang
having avg(uria)>=120
order by avg(uria) desc;
■レプリケーションとは？
レプリカ、DBの複製品(コピー)のこと。マスター/スレーブ。
ホットスタンバイの場合、マルチマスターとする。
また、処理効率化のため、書き込み系(update,insert)はマスターで、
読み込み系(select)はスレーブに担当させるなどもある。
同期に関しては、マスターから「バイナリログ（処理の記録）」を
スレーブに送信し内容を一致させることができる。
※同期/非同期の"タイミング"には注意が必要。
MySQLのレプリケーションは非同期だが、
ver5.5からは一部の過程を同期することが可能。
ただし、実行完了！の表示の後に、バイナリログのスレーブ
書き込みが行われるため、準同期と呼ばれる。
■参考
本：基礎からのMySQL
MySQL013 ビュー
chap11
■ビューについて
・ビューはmysql5.0以上から利用可能
select version(); //5.6.35
・selectした結果をテーブルのように残しておいたもの
・ビューに対し、select,updateも可能。
・ビューの値を更新すれば、元テーブルの値も更新される。
■ビューの定義
[create view viewName as select ~]
create view v1 as select nama,tosi from tb11;
select * from v1;
show tables;
■値の変更
update v1 set nama='主任・佐藤' where nama='佐藤';
select * from v1;
select * from tb11;
※viewの条件がxx以上の場合、元データ変更でxx以上になったものは、
ビューにも表示される。
■ビューの情報表示
desc v1;
show create view v1;
■参考
本：基礎からのMySQL
MySQL012 データの編集（サブクエリ）
chap10 つづき
■副問い合わせ（サブクエリ）
ークエリで取り出されたデータをつかいクエリを発行する。
ー１つ目の処理を()で囲む。
＜値の返却＞
・最大値をもつものを表示
select * from tb10 where uria=max(uria); //エラー
select max(uria) from tb10; //最大値の取得
select * from tb10 where uria in (select max(uria) from tb10);
//where ~ in ~
・平均以上のレコードを抽出
平均年齢以上の人
select * from tb11 where tosi >=(select avg(tosi) from tb11);
＜カラムの返却＞
・INを使う
200万以上の売上をもつ社員を抽出
select bang from tb10 where uria >= 200; //200万以上のレコード抽出
select * from tb11 where bang in (select bang from tb10 where uria >= 200);
・=をつかう場合
ー１件以上あるとエラーになる
ー１件以上あるとlimitを使ってもどの１件かわからない
ーorder byとlimitを掛け合わせれば=も利用可能
・存在するレコードだけを対象とする(EXISTS)
select * from tb10 where tb10.bang=tb11.bang;
select * from tb11 where exists
(select * from tb10 where tb10.bang=tb11.bang);
select * from tb11 where not exists
(select * from tb10 where tb10.bang=tb11.bang);
・順位づけ2
いったん除外
■参考
本：基礎からのMySQL
