プログラマのためのSQL 読書会(18)に参加
22章,23章あたりを読みました。
- IN述語の中には列名を書ける
- NOT INのサブクエリにNULLが含まれる場合、常に結果は無しになる。
- MySQLではcheck制約の代わりにenum,setなどが使われている
- VALUES句はINSERT句以外にも使える
- SQL ServerのINCLUDE
- 量化比較述語 ALL,ANY
- Aの上下逆さ記号とEの左右反対記号
- EXISTSをCHECK句 vs REFERENCE句
- IS NOT FALSEとIS TRUE
- 23.1にある1歳年上のSQL上での表現
- 量化子SOME
- この本の人物録を作るのは極めて困難
IN述語の中には列名を書ける
これは結構以外に思われる方が多いのではないでしょうか?
例えばテストの点数をscore1~score5でもつEcaminationテーブルについて、 score1~score5のどれか一つでも100点を取っている生徒、という条件抽出を試してみます。
PostgreSQLで例示します。 SELECT文発行対象のテーブル*1
DROP TABLE Examination; CREATE TABLE Examination( student_id char(4) NOT NULL PRIMARY KEY, score1 integer, score2 integer, score3 integer, score4 integer, score5 integer ); insert into Examination(student_id,score1,score2,score3,score4,score5) values('0001',90,90,60,53,0); insert into Examination(student_id,score1,score2,score3,score4,score5) values('0002',40,20,70,51,30); insert into Examination(student_id,score1,score2,score3,score4,score5) values('0003',60,90,10,43,100);
実行
SELECT * FROM Examination WHERE 100 in(score1,score2,score3,score4,score5);
結果
postgres=# SELECT * FROM Examination postgres-# WHERE 100 in(score1,score2,score3,score4,score5); student_id | score1 | score2 | score3 | score4 | score5 ------------+--------+--------+--------+--------+-------- 0003 | 60 | 90 | 10 | 43 | 100 (1 row)
NOT INのサブクエリにNULLが含まれる場合、常に結果は無しになる。
以前このテーマを題材にQiitaに記事を書きました(懐かしい)
- みなさんNOT INはあまり使わないようです。
MySQLではcheck制約の代わりにenum,setなどが使われている
- 後述しますが、MySQLにはcheck制約のようなものはないようです。
- 方言がある。使用実績は少ないのが実情。
- トリガーで値をチェックすることもある。
- ググるとMySQLでcheck制約を頑張って実現しようとする投稿が多く引っかかる。
VALUES句はINSERT句以外にも使える
これはちょっと衝撃的でした。 ちょっとした検証用の一時表を作るときは
- 1.スカラ式のみのSELECT句を作る
- 2.UNIONで繋げる
- 3.WITHで囲って一時表とする
このテクニックはこのQiitaの投稿で利用しています。
WITH students as( SELECT 1 AS sid, '高橋太郎' AS sname FROM DUAL UNION ALL SELECT 2 AS sid, '山田幸助' AS sname FROM DUAL UNION ALL SELECT 3 AS sid, '鈴木美恵子' AS sname FROM DUAL UNION ALL SELECT 4 AS sid, '織田信長' AS sname FROM DUAL ) SELECT * FROM students ORDER BY sid
このやり方を使わずともVALUES句でUNIONを使わずに一時表が作れたようです。
SELECT * FROM (VALUES(1,'高橋太郎'),(2,'山田幸助'),(3,'鈴木美恵子'),(4,'織田信長')) AS students(sid,sname);
PostgreSQLでの実行結果
postgres=# select * from postgres-# (values(1,'高橋太郎'),(2,'山田幸助'),(3,'鈴木美恵子'),(4,'織田信長')) as students(sid,sname); sid | sname -----+------------ 1 | 高橋太郎 2 | 山田幸助 3 | 鈴木美恵子 4 | 織田信長 (4 rows)
Mysqlではできない。
SQL ServerのINCLUDE
- インデックスのリーフにINCLUDEで値を入れられる。
- INCLUDEで指定した値のみの問い合わせで条件でインデックスが使われる場合、インデックスオンリースキャンになる。
- 同等の仕組みがDB2にもある。
MySQLのセカンダリーインデックス
MySQLのセカンダリーインデックス(PK以外のインデックス)は必ず主キーを保持しているので 主キーの一部のみの問い合わせを、セカンダリーインデックスで行なった場合、 インデックスオンリースキャンにできる。
量化比較述語 ALL,ANY
初めて見ました。 http://jutememo.blogspot.jp/2010/11/sql-3-all-any.html 初出現が22章ですが、23章で説明が入ります。(さすが難しい本。厳しい)
Aの上下逆さ記号とEの左右反対記号
離散数学とか論理学の話でよく出てくる記号ですが
- :ALLのA
- :EXISTSのE
が元らしいです。こういうのとりあえず暗記させられてたからルーツとかちょっと意外でした。
EXISTSをCHECK句 vs REFERENCE句
制約の対象となる親レコード
CREATE TABLE ZipCodes (state_code CHAR(2) NOT NULL PRIMARY KEY); --データ投入 insert into ZipCodes (state_code) values('JP'); insert into ZipCodes (state_code) values('US');
REFERENCE句
DROP TABLE Addresses; CREATE TABLE Addresses( addresses_name CHAR(25) NOT NULL PRIMARY KEY, street_addr CHAR(25) NOT NULL, city_name CHAR(20) NOT NULL, state_code CHAR(2) NOT NULL REFERENCES ZipCodes(state_code) ); INSERT INTO Addresses (addresses_name,street_addr,city_name,state_code) values('俺の家','1','1','JP'); INSERT INTO Addresses (addresses_name,street_addr,city_name,state_code) values('トランプの家','1','1','US'); INSERT INTO Addresses (addresses_name,street_addr,city_name,state_code) values('金正恩の家','1','1','KP'); --三行目でエラー(親側のテーブルにKPが登録されていないため) --ERROR: insert or update on table "addresses" violates foreign key constraint "addresses_state_code_fkey" --DETAIL: Key (state_code)=(KP) is not present in table "zipcodes".
CHECK制約
DROP TABLE Addresses; CREATE TABLE Addresses( addresses_name CHAR(25) NOT NULL PRIMARY KEY, street_addr CHAR(25) NOT NULL, city_name CHAR(20) NOT NULL, state_code CHAR(2) NOT NULL, CONSTRAINT valid_state_code CHECK( EXISTS( SELECT * FROM ZipCodes AS Z1 WHERE Z1.state_code = Addresses.state_code )) ); --上記のDDLはpostgresqlでは実行できない。 --ERROR: cannot use subquery in check constraint
- REFERENCE句の方がカスケードを使えるというメリットがある。
- このCHECK句が使える実装(RDBMS)がない?
オマケ:MySQLのReference句はINSERTに対して制約を書けない?
帰宅後、自宅で検証してちょっと不思議な現象が発生したのでメモです。
mysql> CREATE TABLE Addresses( -> addresses_name CHAR(25) NOT NULL PRIMARY KEY, -> street_addr CHAR(25) NOT NULL, -> city_name CHAR(20) NOT NULL, -> state_code CHAR(2) NOT NULL REFERENCES ZipCodes(state_code) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO Addresses (addresses_name,street_addr,city_name,state_code) values('俺の家','1','1','JP'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Addresses (addresses_name,street_addr,city_name,state_code) values('トランプの家','1','1','US'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Addresses (addresses_name,street_addr,city_name,state_code) values('金正恩の家','1','1','KP'); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Addresses; +--------------------+-------------+-----------+------------+ | addresses_name | street_addr | city_name | state_code | +--------------------+-------------+-----------+------------+ | トランプの家 | 1 | 1 | US | | 俺の家 | 1 | 1 | JP | | 金正恩の家 | 1 | 1 | KP | +--------------------+-------------+-----------+------------+ 3 rows in set (0.00 sec)
ZipCodeに登録していない北朝鮮の住所が登録できてしまいました。
少し調べると衝撃的なことが書いてありました。
2 つのテーブルを結合するだけの場合は、外部キー制約は必要ありません。InnoDB 以外のストレージエンジンの場合、カラムを定義するときに REFERENCES tbl_name(col_name) 句を使用できます。これは実際の効果はありませんが、現在定義しようとしているカラムが別のテーブルのカラムを参照する予定であるという自分のメモまたはコメントとして役立ちます。この構文を使用するときは、次の点を理解しておくことが非常に重要です。
MySQL は、col_name が実際に tbl_name に存在するか (また、その tbl_name 自体が存在するか) を確認するためのどのような CHECK も実行しません。
MySQL は、tbl_name に対してどのようなアクションも実行しません。たとえば、定義しようとしているテーブルの行に実行されたアクションに対応して行を削除することなどはありません。つまり、この構文にはどのような ON DELETE 動作や ON UPDATE 動作もありません。(REFERENCES 句の一部として ON DELETE 句や ON UPDATE 句を記述することはできますが、これらも無視されます。)
この構文はカラムを作成します。どのようなインデックスやキーも作成しません。
https://dev.mysql.com/doc/refman/5.6/ja/example-foreign-keys.html
😱
IS NOT FALSEとIS TRUE
postgres=# select true IS TRUE; ?column? ---------- t (1 row) postgres=# select null IS NOT FALSE; ?column? ---------- t (1 row)
- 初めて知りました。
- 普通は使わないらしい
23.1にある1歳年上のSQL上での表現
INTERVAL 365 DAY
は1歳年上としては不適切なのでは?
- 閏年
- 365日先に生まれたから1歳上と言えるのか?
ドメインルールによると思いますが、教育関係の場合は学年での差分?をとるといいと思うのですが、 例えば
- 1991/03/11 (1990/4/2~1991/4/1のグループ)
- 1991/04/11 (1991/4/2~1992/4/1のグループ)
この2人の場合は1歳(1学年)差があるという扱いをする気がしますね ちなみに学年グループが4月2日からはじまる理由を知らない人はこちらを参照*2
あんまりSQL関係ありませんね。
量化子SOME
ANYと同じ意味
この本の人物録を作るのは極めて困難
しかし、アレックス・ドーフマンは1つのサブクエリで解く方法を見つけた。
アレックス・ドーフマンって誰?検索してもこの本しか出てこないんだけど…*3
この本はニュースグループというのでしょうか?ネット上の草の根SQLコミュニティの投稿なども多数紹介されており、このドーフマンさんのように出典が一切書かれていないものも多くあります。主催の方は人名録的の作成を諦められたようです。
- 作者: ジョー・セルコ,Joe Celko,ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2013/05/24
- メディア: 大型本
- この商品を含むブログ (16件) を見る