case文（mysql）

mysqlのcase文について

・case：条件分岐。caseの条件は短絡評価される。
⇒
select shainname,
CASE sex
WHEN 'otoko' THEN '男'
WHEN 'onna' THEN '女'
ELSE 'その他'
END as conversex
,age
from
shainmeibo;
⇒これもオッケー
select shainname,
CASE
WHEN age = '21' and sex = 'otoko' THEN '21男'
WHEN sex = 'otoko' THEN '男'
WHEN sex = 'onna' THEN '女'
ELSE 'その他' END as conversex
,age
from shainmeibo;

■参考
・case文のすすめ

mysqlキーワード（関数とか）

mysqlの関数のキーワードとか。

・distinct：重複データを削除する
l・imit：表示するデータを制限する
・exists
⇒パフォーマンスの観点から、select *よりselec 1の方がいい。
⇒Where does the practice “exists (select 1 from …)” come from?
⇒Using a SELECT 1 vs a SELECT * in the EXISTS/NOT EXISTS query
・case：条件分岐。caseの条件は短絡評価される。
⇒case文（mysql）
・concat：文字列を連結する
・substr：部分文字列を取得する
⇒mysql> select shainname, substr(shainname,2), substr(shainname,2,5) from shainmeibo;
・between MIN and MAX, not between MIN and MAX：項目の条件を範囲で指定。日付とかint型の値とかで使う。
-- priceが100~200までのレコードを抽出
select *
from foodslist
where price between 100 and 200;

--priceが100より小さい、または、200よりおおきいレコードを抽出する。
select *
from foodslist
where price not between 100 and 200;

・union,union all：二つのテーブルの抽出結果を一つにまとめる。二つのテーブルは違っていても構わない。unionは重複データを纏める。union allの方が高速。



■
--二つのテーブルのデータ全体が等しいかどうかを確認するには？
select * from table1 A
where not exists (
select * from table2 B
where A.column1 = B.column1
and A.column2 = B.column2
and A.column3 = B.column3
and A.column4 = B.column4
and A.column5 = B.column5
and A.column6 = B.column6
);
--AとBのテーブルを入れ替える。
--これで、どちらも抽出レコードが０件だと二つのテーブルのデータ全体は等しいと言える。


■参考

mysqlのストアドプロシージャ、トリガ

mysqlのstoredProcedure（ストアドプロシージャ）についてです。

proctest01というストアドプロージャを定義します。その時delimiterを(;)から(//)に変更している。そして、最後にまた戻している。

delimiter //
create procedure proctest01 (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM kokuseki;
END;
//
delimiter ;

proctes01の呼び出し。プロシージャの出力引数には接頭辞@が必要です。

mysql> call proctest01(@outhoge);
Query OK, 1 row affected (0.00 sec)

mysql> select @outhoge;
+----------+
| @outhoge |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

登録したプロシージャは、mysqlサーバを起動しなおしても使える。

ちなみにshowでプロシージャのコードを表示しようとしたらエラーが出た。原因はビルドの仕方ぽい。

mysql> show procedure code proctest01;
ERROR 1289 (HY000): The 'SHOW PROCEDURE|FUNCTION CODE' feature is disabled; you need MySQL built with '--with-debug' to have it working

show create procedureだと見れた。

mysql> show create procedure proctest01\G
*************************** 1. row ***************************
Procedure: proctest01
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proctest01`(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM kokuseki;
END
character_set_client: cp932
collation_connection: cp932_japanese_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)


■参考
mysqlのストアードプロシージャの便利さをアピールしてみる

mysqlのauto_incrementとsequence table

mysqlのauto_incrementについてのメモ書きです。

とりあえずクエリのログだけ・・・

-- table
create table autotest(
id INT auto_increment,
name varchar(10),
index(id),
primary key(id)
);

--
insert into autotest(name) values('ほげほげ');
insert into autotest values(10, 'ほげほげ２');


--autoincrementの最後に自動生成された値を取得
select last_insert_id();

--これはダメくさい。一度できた気がしたんだけど。
insert into autotest values(last_insert_id()+1, 'ほげほげ２');

実際に一度はできていた。ただのinsert文を実行すると「select last_insert_id();」が返す値が更新される。
だから一度は「insert into autotest values(last_insert_id()+1, 'ほげほげ3');」ができる。
けどこれを連続して実行してもlast_insert_id()の値が更新されてないのでdupliate Key errorが発生する。

というかlast_insert_id()の使いかたを間違えていた。下のように引数にidを指定すればおっけ。参考URLを参照。
insert into autotest values(last_insert_id(id), 'ほげほげ3');

あと、last_insert_id()を使って、oracleとかにあるsequenceテーブルみたいなものが作れる。

CREATE TABLE sequence (testid2 INT NOT NULL);
--初期化
INSERT INTO sequence VALUES (0);

--NG selectだけだとエラー
SELECT LAST_INSERT_ID(testid2);
SELECT LAST_INSERT_ID(testid2+1);
-- OK
SELECT LAST_INSERT_ID();

--値を１つづインクリメントする。
①UPDATE sequence SET testid2=LAST_INSERT_ID(testid2); -- これだとincrementされない
②UPDATE sequence SET testid2=LAST_INSERT_ID(testid2+1); -- これはincrementされる

--上の②のupdate sqlを実行するたびに「SELECT LAST_INSERT_ID();」と「select * from sequence;」のtestid2の値が１ずつインクリメントされる。
--①のlast_insert_id()の引数がtestid2だとincrementされない。つまり、last_insert_id()の引数には、取得したい値を指定する。testid+10なら１０ずつインクリメントされる。

・mysqlのシーケンステーブル
・last_inser_id()とcreate table sequence（公式）



■参考
・auto_incrementの設定

InnnoDB,MyISAM,lock,directory,ファイル構成~mysql

■innnoDB,MyISam

mysqlプロンプトで「show engines」ですべてのエンジンが見れる

ｍｙｓｑｌのテーブルのストレージエンジンの代表格はInnnoDBとMyISAMの二つ。使い方によってパフォーマンスの違いがでてくるようなので色々考える必要がある。。また、パフォーマンスだけじゃなくてMyISAMだとトランザクションが組めないとかできる事できない事があるので要注意。

パフォーマンスに関してだけど、他サイトを見たら一概にはいえなそう。パフォテストは必要。

refs
・MySQLのMyISAMとInnoDBパフォーマンス比較 テーブルロック vs 行レベルロック
・MyISAMとInnoDBのどちらを使うべきか

・テーブルエンジンの確認は以下のコマンドでできる。
mysql> show table status;
・テーブルを指定
mysql> show table status like 'hoge_table'\G
・create文を見れる
mysql> show create table hoge_table\G

・foreignKey(外部キー)を使用する場合、両方innoDBである必要がある。

■ロック
refs
・データベースの行ロックとテーブルロック
・Mysql:　lockの確認とプロセスの削除
⇒コマンドで確認
・6.3.1. MySQL のテーブルロック方法(mysql5.1マニュアル)

■
refs
・MySQLのディレクトリ構造、MyISAMのファイル名、innoDBの特徴

■参考
・MySQLでトランザクションができない時の対処法（InnoDB）
・徹底比較!! MySQLエンジン
・更新があるシステムにはInnoDBを選ぼう。MyISAMを選択するならそれなりの理由が必要。それにInnoDBのパフォーマンスはそんなに悪くないよ。