Yabu.log

ITなどの雑記

プログラマのためのSQL 読書会(18)に参加

22章,23章あたりを読みました。

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に記事を書きました(懐かしい)

qiita.com

  • みなさんNOT INはあまり使わないようです。

MySQLではcheck制約の代わりにenum,setなどが使われている

  • 後述しますが、MySQLにはcheck制約のようなものはないようです。
  • 方言がある。使用実績は少ないのが実情。
  • トリガーで値をチェックすることもある。
  • ググるMySQLでcheck制約を頑張って実現しようとする投稿が多く引っかかる。

VALUES句はINSERT句以外にも使える

これはちょっと衝撃的でした。 ちょっとした検証用の一時表を作るときは

  • 1.スカラ式のみのSELECT句を作る
  • 2.UNIONで繋げる
  • 3.WITHで囲って一時表とする

このテクニックはこのQiitaの投稿で利用しています。

qiita.com

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の左右反対記号

離散数学とか論理学の話でよく出てくる記号ですが

  •  \forall:ALLのA
  •  \exists: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ではcheck制約verのDDLを実行できたが、意図した制約動作をしていない。

オマケ: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コミュニティの投稿なども多数紹介されており、このドーフマンさんのように出典が一切書かれていないものも多くあります。主催の方は人名録的の作成を諦められたようです。

プログラマのためのSQL 第4版

プログラマのためのSQL 第4版

*1:ちなみにこのDDLSQLアンチパターン7章のマルチカラムアトリビュートです。本番でテーブル設計する人は従属テーブルを作りましょう

*2:確か祖母がこれを食らってた気がします

*3:あなたがこの文章を読む頃にはこのブログも検索結果に含まれていることでしょう。