SQLの勉強 1日目

1. 調査日時

20220710

2. 調査の趣旨・目的

3. 参考URL

4. 調査結果概要（要約）

5. 調査詳細（具体的な内容）

5.1. SQLとは

5.1.1. SQL

  • DBMSのデータ操作を行うための言語がSQL

    5.1.2. DBMS

  • DBを管理するためのシステム
  • データの一貫性を保ちつつ、データを操作する機能
  • DBMSに必要な機能5つ
    1. データ操作機能 データの構造や型の定義、データの更新、問い合わせなど
    2. 同時実行制御 複数のユーザーが同じデータベースを使用できるようにする機能 データ更新時には、更新が完了するまで参照させないようにする、排他制御を行う
    3. トランザクション管理 ユーザーから見た、ひとまとまりの処理をトランザクション トランザクション内の処理は「すべて成功」or 「全て失敗」 コミット：全て成功、結果をDBに反映 ロールバック：全て失敗、トランザクション開始前の状態に戻す
    4. 機密保護 ユーザーの認証、アクセス制御、暗号化
    5. 障害回復 データの回復など データの更新記録（ログ）から回復することも

      5.1.3. RDB (Relational Data-base)

  • データを常に表の形（テーブル）で表す
  • DBMSに対して、欲しいデータを要求することをクエリという。
  • テーブルのルールとして、制約を設定することができる
  • 一意性制約 あるテーブルのあるデータは重複を許さない（生徒IDなど）
  • 参照制約、参照生合成制約、外部キー制約 テーブルAがテーブルBを参照する場合、テーブルAのデータはテーブルBに登録されていなければならない FOREIGN KEY (student_id) REFERENCES students (student_id)で設定

5.1.4. 三層スキーマ

  • スキーマ
    • 何らかのシステム設計をするときに、考えるべき枠組み構造のこと
  • 3層スキーマ
    • データベースの設計の際に、考えるべき枠組みのこと
      1. 外部スキーマ エンドユーザーからの視点をとらえた枠組み RDBでは、エンドユーザが見たいデータをSELECTで柔軟に取り出せる
      2. 内部スキーマ データをファイルに保存するときの構造などを扱う部分
      3. 概念スキーマ 外部スキーマと内部スキーマの橋渡し データベースの設計の中心は、この「概念スキーマ」の設計にあたる

5.2. SQLのあれこれ

  • SQLの記述ルール
    • 文末にはセミコロン
    • テーブル名や列名には、原則半角数字と_
    • 文字列や日付は「`」で囲む
    • コメントは--の後ろか、/ /で
  • SELECT
    • データの問い合わせに使う
    • SELECT, FROM, WHEREで同時に使うことが多い
    • SELECT student_id student_name FROM students WHERE branch='新宿';
  • テーブルの作成
    • CHAR(5)
      • データの型を決める、今回は5桁の文字列
      • 主キーは商品コード
      • 商品名は255桁の、可変長の文字列
      • 在庫数のデフォルトは0
      • 登録日時のデフォルトは、今の時間（INSERTが実行された時間） CREATE TABLE　商品マスター（ 商品コード　CHAR(5)　PRIMARY KEY, 商品名 VARCHAR(255), 在庫数　INTEGER DEFAULT 0, 登録日時 TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

  • SQLで文字以外を格納する

    型 保存したいデータ
    BLOB 画像
    ARRAY 画像
    JSON json
    XML xml

  • ドメイン

    • 同じ性質を持つ値の集合のこと
    • 指名という列のドメインは人の名前の集合
  • NULL
    • 値のわからないデータであるときに保存するデータ
    • ただし、NULLがある時点で計算も比較もできなくなってしまう
    • NULLを許さない場合はテーブルの作成時に、NOT NULLで指定できる
  • 主キー
    • データを識別するのにメインで使用する列のこと
    • 候補キーのうちの一つ
    • IDや顧客番号などがあれば、大体それが主キー
    • 主キーの特性（デフォルト）
      • NULLが禁止
      • 重複不可
  • 複合キー
    • 複数の列を組み合わせてキーにすること
    • 例：校舎ごとに生徒番号が1~Nまでついている
    • 複合キー：「校舎コード＋生徒番号」
    • ちなみに、もちろん全校舎で共通の番号にすれば、複合キーにする必要がないよね
  • 参照制約　（外部キー）
    • coursesテーブルのstudent_idは、studentsテーブルのstudent_idにないとダメ
CREATE TABLE courses (
  student_id CHAR(5) NOT NULL,
  course VARCHAR(255) NOT NULL,
  PRIMARY KEY (student_id, course),
  FOREIGN KEY (student_id) REFERENCES students (student_id),
  FOREIGN KEY (course) REFERENCES course_master(course)
);
  • SQLのキーワード
キーワード 操作内容 備考
CREATE TABLE テーブルを作成 ---
DROP TABLE テーブルを削除 ---
PRIMARY KEY 主キーの設定 ---
FOREIGN KEY 外部キーの設定 ---
INSERT INTO データの追加 ---
REFERENCES 参照制約 ---
UPDATE データの更新 ---
WHERE 対象の指定
WHEREしないと全部指定 		---
DELETE データの削除 ---
ON DELETE RESTRICT テーブルの参照先に、データがあると削除できない ---
ON DELETE CASCADE テーブルの参照先に、データがあると、両方のテーブルで削除 ---
SELECT データの問い合わせ ---
FROM テーブルの指定 ---
ORDER BY データの並べ替え ASCで昇順、DESCで降順 ---
COUNT() データの件数の調査 ---
GROUP BY グループの単位を決める場合、例えば出身が同じ人ごとにとか ---
AS 違う列名を付けて保存する ---
JOIN テーブルを結合 ---
CREATE VIEW 実際のテーブルとは別の、一時的に使うためのテーブル ---
DROP VIEW データは一時的な変数のようなもののため、削除しても影響なし ---
CHECK 保存できる値を限定できる　例：テストなら0点以上100点以下 ---

6. 不明単語

単語 意味 備考
キーワード SELECTとかFROMとか、SQL文を構成するための単語 ---
予約語 DBMSではキーワードを予約語とも ---
主キー、プライマリキー テーブル内でデータを特定するのに用いるデータ（IDなど） ---
宣言 student_idは主キーである、と定義すること ---
外部キー テーブルAとテーブルBを関連づけるデータ ---
アトミック これ以上分割できないデータ ---
キー、識別子 データを識別するのに使える項目 ---
候補キー キーにできる列、候補キーの中でメインで使うものは主キー ---