ねこきっくぱんちのメモ帳

ITに関することいろいろめも。たまにアニメ。

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,サブクエリなどで複数テーブルが複合されたものはできない。
純粋な1テーブルなら可能。ただし、必須カラムがあると多分エラーになる。

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 つづき

■副問い合わせ(サブクエリ)
ークエリで取り出されたデータをつかいクエリを発行する。
ー1つ目の処理を()で囲む。

<値の返却>
・最大値をもつものを表示
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);

・=をつかう場合
ー1件以上あるとエラーになる
ー1件以上あるとlimitを使ってもどの1件かわからない
ー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

MySQL011 データの編集(外部結合:OUTER JOIN, LEFT/RIGHT)

chap10 つづき

■複数のテーブルの"すべてのレコード"を表示する(外部結合)
ーキーが一致しているか否か?が内部・外部結合の違い
ー全データを表示させる際、左右どちらかを主にする(左外部/右外部結合)

・LEFT JOIN / left outer join
select tb10.bang,tb11.nama from tb10
left join tb11 using(bang);

select tb10.bang,tb11.nama from tb10
left outer join tb11 using(bang);

・RIGHT JOIN
select tb10.bang,tb11.nama from tb10
right join tb11 using(bang);

・FULL OUTER JOIN
ここでは扱わない。


■自己結合
select * from tb11 join tb11; //Error.
select * from tb11 as a join tb11 as b; //aliasを付与で回避

順位付け
select a.nama, a.tosi, count(*)
from tb11 as a join tb11 as b
where a.tosi<=b.tosi
group by a.bang;


■参考
本:基礎からのMySQL

MySQL010 データの編集(内部結合 INNER JOIN)

chap10 つづき

■複数のテーブルを結合して表示する(内部結合:INNER JOIN)
ー複数のテーブルを何らかのキーで結びつけて処理するのが「結合」
ーunionは'レコード'を結合
ーjoin テーブル名 on キーとなるカラム
ーレコードが一致しなくても全て出す場合は外部結合(OUTER JOIN)

select * from tb10 join tb11 on tb10.bang = tb11.bang;
select * from tb10 inner join tb11 on tb10.bang = tb11.bang;

・カラムを選んで表示
select tb10.bang,tb11.nama,tb10.uria from tb10
join tb11 on tb10.bang=tb11.bang;


・テーブル名にエイリアスを使う
select x.bang,y.nama,x.uria from tb10 as x
join tb11 as y on x.bang=y.bang;

・USINGをつかってON部分を見やすくする
select tb10.bang,tb11.nama,tb10.uria from tb10
join tb11 using(bang);

・結合テーブルからwhereで抽出
select tb10.bang,tb11.nama,tb10.uria from tb10
join tb11 using(bang)
where tb10.uria>=100;

・複数テーブルを内部結合
select tb10.bang,tb10.uria,tb13.ken from tb10
join tb11 using(bang)
join tb13 using(bang);

■参考
本:基礎からのMySQL

MySQL009 データの編集(UNION, UNION ALL)

chap10

■複数テーブルのレコードを合わせて表示する

<事前準備:サンプルテーブル 4つ>
create table tb10 select * from chap8;

create table tb11 (bang varchar(10),nama varchar(10),tosi int);
insert into tb11 values('A101','佐藤',40);
insert into tb11 values('A102','高橋',28);
insert into tb11 values('A103','中川',20);
insert into tb11 values('A104','渡辺',23);
insert into tb11 values('A105','西沢',35);

create table tb12 (bang varchar(10),nama varchar(10),tosi int);
insert into tb12 values('A106','中村',26);
insert into tb12 values('A107','田中',24);
insert into tb12 values('A108','鈴木',23);
insert into tb12 values('A109','村井',25);
insert into tb12 values('A110','吉田',27);

create table tb13 (bang varchar(10),ken varchar(10));
insert into tb13 values('A101','東京都');
insert into tb13 values('A102','埼玉県');
insert into tb13 values('A103','神奈川県');
insert into tb13 values('A104','北海道');
insert into tb13 values('A105','静岡県');

・複数の抽出結果を合わせて表示(UNION)

■UNION
・複数テーブルから抽出データを収集する。
※基本的にデータ型は一致する必要はあるが、集められることもある。
select * from tb11 union select * from tb12;
(select * from tb11) union (select * from tb12);

・3つ以上のテーブルから抽出データを収集する。
select * from tb11 where bang='A102'
union select * from tb11 where bang='A103'
union select * from tb11 where bang='A104'
union select * from tb11 where bang='A105';
→select * from tb11 where bang not in ('A101');
→select * from tb11 where bang in ('A102','A103','A104','A105');

・条件付きで複数の抽出結果を表示する(重複する)
select bang from tb10 where uria>=200
union
select bang from tb11 where tosi>=35;
※重複データは省かれる

・条件付きで複数の抽出結果を表示する(重複しない) union all ★
select bang from tb10 where uria>=200
union all
select bang from tb11 where tosi>=35;

■参考
本:基礎からのMySQL