SQL の基本きほん

1導入どうにゅう

この講義こうぎでは、関係かんけいデータベースに対たいする問とい合あわせと更新こうしんを宣言的せんげんてきに記述きじゅつする SQLStructured Query Language の基本きほんを説明せつめいする。前講義ぜんこうぎで導入どうにゅうした表ひょう、行ぎょう、列れつ、主しゅキー、外部がいぶキーを、SQL の構文こうぶんと対応たいおうさせる。

SQL は、データへ到達とうたつする手順てじゅんではなく、必要ひつような結果けっかの条件じょうけんを記述きじゅつする。索引さくいんの利用りようや結合順序けつごうじゅんじょなどの実行計画じっこうけいかくは、通常つうじょう、データベース管理かんりシステムが決定けっていする。この宣言性せんげんせいにより、論理的ろんりてきな問とい合あわせと物理的ぶつりてきな実行方法じっこうほうほうを分離ぶんりできる。

2SELECT、FROM、WHERE

基本的きほんてきな問とい合あわせは、SELECT、FROM、WHERE の三要素さんようそから構成こうせいされる。

• FROM は、問とい合あわせの対象たいしょうとなる表ひょうを指定していする。
• WHERE は、各行かくぎょうを条件式じょうけんしきによって選択せんたくする。この操作そうさを選択せんたくSelectionまたはフィルタリングという。
• SELECT は、結果けっかに含ふくめる列れつまたは式しきを指定していし、概おおむね射影しゃえいProjectionに対応たいおうする。ただし、SQLは既定きていで重複行ちょうふくぎょうを保持ほじし、SELECT DISTINCTを指定していした場合ばあいに重複ちょうふくを除去じょきょする。これは関係代数かんけいだいすうの集合しゅうごうとしての射影しゃえいとの相違そういである。

論理的ろんりてきには FROM で対象たいしょうを定さだめ、WHERE で行ぎょうを選択せんたくし、SELECT で列れつを射影しゃえいすると解釈かいしゃくできる。これは概念上がいねんじょうの役割やくわりであり、データベース管理かんりシステムがこの順序じゅんじょで物理的ぶつりてきに処理しょりするとは限かぎらない。

SELECT student_id, name
FROM students
WHERE department = 'Computer Science';

この問とい合あわせは、students 表ひょうから所属しょぞくが Computer Science である行ぎょうを選択せんたくし、その student_id と name を結果列けっかれつとして指定していする。重複行ちょうふくぎょうはDISTINCTを指定していしない限かぎり保持ほじされる。

3結果けっかの順序じゅんじょ

SQL の問とい合あわせ結果けっかには、ORDER BY を指定していしない限かぎり順序じゅんじょの保証ほしょうがない。同一どういつの問とい合あわせでも、実行計画じっこうけいかくやデータの配置はいちによって表示順ひょうじじゅんが変化へんかし得うる。

SELECT student_id, name
FROM students
ORDER BY student_id ASC;

再現可能さいげんかのうな順序じゅんじょが必要ひつような場合ばあいは、同順位どうじゅんいを解消かいしょうできる列れつまで ORDER BY に指定していする。

4NULL と三値論理さんちろんり

NULL は、値あたいが存在そんざいしない、または不明ふめいであることを表あらわす標識ひょうしきであり、空文字列からもじれつや数値すうちの 0 ではない。NULL を含ふくむ通常つうじょうの比較ひかくは、TRUE または FALSE ではなく UNKNOWN となる。SQL の条件式じょうけんしきは、この三値論理さんちろんりThree-valued logicに従したがう。

WHERE が結果けっかに残のこすのは、条件式じょうけんしきが TRUE となる行ぎょうだけである。FALSE と UNKNOWN の行ぎょうは除外じょがいされる。したがって、NULL の検査けんさには = NULL ではなく IS NULL または IS NOT NULL を使用しようする。

SELECT student_id, name
FROM students
WHERE email IS NULL;

5JOIN とキー

別々べつべつの表ひょうに格納かくのうされた関連かんれんデータは、JOIN によって結合けつごうできる。例れいとして、students.student_id を主しゅキー、enrollments.student_id をそれを参照さんしょうする外部がいぶキーとする。

SELECT s.student_id, s.name, e.course_id
FROM students AS s
JOIN enrollments AS e
  ON e.student_id = s.student_id
WHERE e.status = 'enrolled';

ON は二ふたつの表ひょうの行ぎょうを対応たいおうさせる結合条件けつごうじょうけんを指定していする。主しゅキーの一意性いちいせいと外部がいぶキーの参照整合性さんしょうせいごうせいにより、この対応関係たいおうかんけいをデータベース側がわで検証けんしょうできる。ただし、JOIN 自体じたいは外部がいぶキーが宣言せんげんされていなくても実行可能じっこうかのうである。

6更新こうしん、制約せいやく、トランザクション

SQL は、INSERT で行ぎょうを追加ついかし、UPDATE で既存行きそんぎょうを変更へんこうし、DELETE で行ぎょうを削除さくじょする。

INSERT INTO enrollments (student_id, course_id, status)
VALUES (1001, 'CS101', 'enrolled');

UPDATE enrollments
SET status = 'completed'
WHERE student_id = 1001 AND course_id = 'CS101';

DELETE FROM enrollments
WHERE student_id = 1001 AND course_id = 'CS101';

PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、CHECK などの制約せいやくConstraintは、許容きょようされるデータの条件じょうけんをスキーマに記録きろくし、不正ふせいな更新こうしんを拒否きょひする。ただし、制約せいやくの具体的ぐたいてきな機能きのうや構文こうぶんにはデータベース製品せいひんによる差異さいがある。

複数ふくすうの文ぶんを一体いったいの処理しょりとして扱あつかう場合ばあいは、トランザクションTransactionを使用しようする。COMMIT はトランザクションの変更へんこうを確定かくていし、ROLLBACK は未確定みかくていの変更へんこうを取消とりけす。これにより、処理しょりの途中とちゅうだけが反映はんえいされた状態じょうたいを回避かいひできる。ただし、トランザクションの分離ぶんりレベルや自動確定じどうかくていの既定値きていちは実装じっそうによって異ことなる。

7パラメータ化かと安全性あんぜんせい

利用者りようしゃの入力にゅうりょくを SQL 文字列もじれつへ直接連結ちょくせつれんけつすると、入力にゅうりょくが SQL の構文こうぶんとして解釈かいしゃくされる SQL インジェクションを招まねき得うる。値あたいは、使用しようするドライバが提供ていきょうするパラメータ化かされた問とい合あわせへ別途べっと渡わたす。

SELECT student_id, name
FROM students
WHERE email = ?;

? はパラメータの概念例がいねんれいであり、実際じっさいのプレースホルダ構文こうぶんはドライバやデータベース製品せいひんによって異ことなる。パラメータ化かは値あたいと SQL 構文こうぶんを分離ぶんりするが、表名ひょうめいや列名れつめいなどの識別子しきべつしを任意にんいに安全化あんぜんかするものではない。識別子しきべつしを動的どうてきに選択せんたくする場合ばあいは、許可きょかリストによる検証けんしょうなどが必要ひつようである。また、認可にんか、最小権限さいしょうけんげん、機密情報きみつじょうほうの保護ほごも別途必要べっとひつようとなる。

8要点ようてん

• SQL は、必要ひつような結果けっかを宣言せんげんし、その物理的ぶつりてきな実行方法じっこうほうほうをデータベース管理かんりシステムへ委ゆだねる。
• WHERE は行ぎょうを選択せんたくし、SELECT は結果けっかの列れつや式しきを指定していする。SQLは既定きていで重複行ちょうふくぎょうを保持ほじする。NULL を含ふくむ条件じょうけんには三値論理さんちろんりが適用てきようされる。
• JOIN は複数ふくすうの表ひょうを関連付かんれんづけ、キーと制約せいやくはデータの整合性せいごうせいを支ささえる。
• 更新こうしんはトランザクションによって一体いったいとして管理かんりでき、外部入力がいぶにゅうりょくはパラメータ化かして SQL 構文こうぶんから分離ぶんりする。
• ORDER BY のない結果けっかに順序じゅんじょの保証ほしょうはない。

9次つぎに進すすむ講義こうぎ