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

26章の初めから26.7まで読みました。

ビューがメインテーマです。

• ビューの更新を知る人は少ない。

  • 情報技術者試験でよく出る
  • https://www.ap-siken.com/kakomon/23_aki/q30.html
    • このサイトに更新可能ビューの条件があるが一部違っている。本書の更新可能ビューの条件を引用します。
      • 1.１つだけのテーブルから作られていること
      • 2.GROUP BY句を使っていないこと
      • 3.HAVING句を使っていないこと
      • 4.集約関数を使っていないこと
      • 5.計算列を使っていないこと
      • 6.UNION,INTERSECT,EXCEPTを使っていないこと
      • 7.SELECT DISTINCT句を使っていないこと
      • 8.ビューに含まれていない規定テーブルの全ての列はNULLを許可するかデフォルト値が指定されていること。*1

• IBMではViewではなく「視点」というらしい。

• 確かにIBMの汎用機周りは謎翻訳が多い

  • そういえばindicator*2のことを標識とよく言っている

• クエリーのリライトプラグインというものがある

  • 特定の効率の悪いクエリを書き換える機構が存在するらしい

• マルチテナントシステムだとテナントごとにビューを切ったりする。

• ビューの権限

• アクセス権限の設定はビューだけではない？

  • ビューだけではなく規定テーブルへのアクセス権限の設定も必要。
    • そういう作業を最近した人がいる
    • 詳しい人曰く
      • sql securityの値
        • definer
        • invocar
    • を適切に設定することで迂回できるようです。

• oracleにはscottというデモ用のアカウントがいる

  • なんだこの裏技みたいなアカウント!?(知りませんでした)
  • https://ja.wikipedia.org/wiki/Oracle_Database#.E3.80.8CSCOTT.2FTIGER.E3.80.8D.E3.81.AE.E7.94.B1.E6.9D.A5

  Oracle Databaseに付属するdemobld.sql（Oracle Database 10g以降ではutlsampl.sql）を実行すると「EMP」「DEPT」というふたつのテーブルと「SCOTT/TIGER」というスキーマよりなる伝統的なデモ環境が構築される。「SCOTT」とはオラクルの前身であるSDLに在籍していたBruce Scottを指し、「Tiger」は彼の愛猫の名前に由来する。Scottは優秀な開発者であり最初期のSQL*Plusも彼の手によるものとされている。Scottはすでにオラクルを後にしているが、この伝統は変わる様子がない。

  • 権限は最強っぽいが詳細は不明。

  • mysqlではデフォルトで権限が最強の無名ユーザーがいる

  • ユーザー名：なし,パスワードなし
    • http://lovee7.blog.fc2.com/blog-entry-25.html
    • https://cloudpack.media/1316
      • それっぽいやつがいる
      • 自分の環境にはいませんでした(消したかも？)

• １対他の結合を行うクエリやスキーマをスター型のクエリ,スタースキーマというらしい。

  • ER図がスター型になるので

• MyISAM

  • ストレージエンジン。以前はISAMと呼ばれるものだった。
    • ISAM -> C,COBOLなどの汎用機の人が詳しいかも。
  • MySQLが使い始めてMyISAMというものを改良して使っている。

• MyISAMは古い。最近はinnoDBを使っている

  • 一応ストレートエンジンは選択可能。
    • ブラックホールストレージエンジンというものもある
      • https://dev.mysql.com/doc/refman/5.6/ja/blackhole-storage-engine.html
    • CSV形式でファイル保存できるエンジンもあるとか？(未検証)
  • MyISAMはトランザクションが使えない。

• mysqlのmerge table

  • https://dev.mysql.com/doc/refman/5.6/ja/merge-table-advantages.html
  • viewが導入される前に代替案で使われていた。
  • もう古いので使わないこと。

• ビューの循環参照（実験してみる）

  • ALTER VIEWで循環参照になるビューを作ればどうなるか、という話題になった。
  • tbl1←v1←v2という参照関係のテーブルビューを作る

--定義
create table tbl1(id int);
insert into tbl1 values(1);
create view v1 as select id from tbl1;
create view v2 as select * from v1;

--確認
select * from tbl1;
select * from v1;
select * from v2;

/*もちろん結果は全て

+------+
| id   |
+------+
|    1 |
+------+

になる。
*/

• ここでv1をalter viewで変更して v1 <-> v2という循環参照を作る
  • が、この循環参照を作るalter文は実行できなかった。

alter view v1 as select * from v2;
--ERROR 1146 (42S02): Table 'test.v1' doesn't exist

--もちろん構文は間違っていない。v2をtbl1にすれば実行できる。
--alter view v1 as select * from tbl1;
--結果:Query OK, 0 rows affected (0.00 sec)

• mysql "循環参照"でググっても関連しそうな情報はなかった。

• SQL Serverにはindex viewがある！

  • MySQLにはない。

• 誤植

  • P.466のSQL,'Sales'のSが小文字sになっている

▼P.466

SELECT *
  FROM Departments
 WHERE (dept_name = 'sales') --ココ！'sales'ではなく'Sales'!!
   AND (city_name = 'New York');

• 多分原著から間違っているのでは?(未確認ですが)とのこと。

次は26.7から！

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

*1:これは規定テーブルに行を挿入する際、値を一位に決めるためである

*2:システムグローバルなフラグ集,CPUのレジスタで言う所のEFLAGSのようなもの