Zenn
🐬

SQL基礎知識　まとめ

2023/07/05に公開
MySQL
SQL
初心者向け
DB
プログラミング学習
tech

概要

SQL基礎を学習したので見返せるように、スクラップの内容も含めてまとめておく。

取り扱わないもの

  • テーブル作成
  • 正規化

対象

SQL初心者

SQLの4大命令(DML)

  • SELECT
  • UPDATE
  • DELETE
  • INSERT

SELECT や WHEREなどの命令に使う単語→SQLとして特別な意味を持つ「予約語」

予約語→大文字と小文字のどちらで記述してもよい。

  • テーブル名や列名に予約語を利用することはできない。
  • ※列命やテーブル名について、大文字と小文字を区別するかどうかは、DBMS製品や
    動作するOS、設定などにより異なる。

コメント

/~/まではコメントとして扱う。
SELECT→目的とする列名
FROM 検索したいテーブル名

4大命令は2通りの分類ができる。

1,検索系と更新系

  • 検索系:SELECT→データベースのデータを書き換えることはない。実行結果は表の形になる
  • 更新系:UPDATE, DELETE, ⅠNSERT→データを書き換える、実行結果「成功」「失敗」の2つに1つ
    → 表は返されない。

2既存系と新規系

  • 既存系: SELECT, UPDATE, DELETE →　WHERE使える
  • 新規系:INSERT　→　WHERE使えない

WHERE句による絞り込み

SQLの言語としての特徴

  • 命令自体は単純で、数も少ない(主に使うものは4つ)
    さまざまな修飾語を付けることで、複雑な処理が可能に

WHERE句の基本

  • 処理対象行の絞り込みに用いる

    WHEREを指定しないと「すべての行」が処理対象になる。
  • SELECT, UPDATE ,DELETE　で使用可能

    新しい行を追加するINSERT文では使用できない
  • WHEREの後ろには条件式を記述する

    絞り込み条件に沿った「正しい条件式」を記述する

データ型とリテラル

  • リテラル(literal) SQL文の中に書き込まれた具体的なデータそのものの値
  • 「'」でくくられていないリテラル 数値
  • 「'」でくくられたリテラル→基本的に文字情報
  • 「'」でくくられ、「2022-02-25'のような一定の形式で記録されたリテラルは
    日付情報として扱われる
  • データベースの中には複数のテーブルがあり、テーブルは行と列から成り立っている。
    それぞれの列には名前が付いており、列ごとに格納できるデータの種類を表すデータ型
    を定めることになっている

データ型のまとめ

  • テーブルの各列には、データ型が指定されている
  • 列には、データ型で指定された種類の情報しか格納することはできない。
  • 利用可能なデータ型は、DBMS製品によって異なる。

条件式

その結果が必ず真か偽になる式のこと。
結果が数値や文字列、日付などの式は、WHERA句に記述することはできない

WHERE句に書けるもの

結果が必ず真(TRUE)または偽(FALSE)となる条件式

比較演算子 意味
= 左右の値が等しい
< 左辺は右辺より小さい
> 左辺は右辺より大きい
<= 左辺は右辺の値以下
>= 左辺は右辺の値以上
<> 左右の値が等しくない。

  • =>のようにイコールを先に書かない。

  • ※この6つが基本演算子と呼ばれる。

NULL

  • そこに何も値が格納されていない状態を意味する、特別なもの。
  • 数値のゼロや空白文字、長さゼロの文字列とも異なる存在である。
  • 格納データが「不明」や「無意味」である状況を示す意図で用いられる。

NULL の判定

NULLであることを判定する

  • 式　IS NULL

NULLでないことを判定

  • 式　IS NOT NULL

比較演算子

LIKE演菓子

  • パターンマッチング文字列があるパターンに合致しているかをチェックすること。　
    SQLではこのパターンマッチングにLIKE演算子を使う。

  • 式 LIKE パターン文字列

LIKE演算子に使えるパターン文字

パターン文字 意味
% 任意の0文字以上の文字列
_ (アンダースコア) 任意の1文字

BETWEEN演算子

データが「値が1以上かつ値2以下」の場合に真になる。

  • ある範囲内に値が収まっているかを判定する。

  • 式 BETWEEN 値1 AND 値2

IN/NOT IN演算子

IN演算子 カッコ内に列挙した複数の値(値リスト)のいずれかにデータが合致 するかを判定する演算子

  • 式 IN(値1.値2.値3・・・
  • (NOT IN)→カッコ内に列挙した値のどれとも合致しないことを判定

ANY/ALL演算子

値リストのそれぞれと比較して、いずれかが真なら真

  • 式 基本演算子 ANY(値)、値2,値3...)

値リストのそれぞれと比較して、すべて真なら真

  • 式 基本比較演算子 ALL(値1,値2,値3...)

検索結果の加工

  • SELECT 文で取得したデータは、様々な形に加工可能。
加工内容 キーワード
重複行を除外する DISTINCT
結果を並び替える ORDER BY
行を限定して取得する OFFSET-FETCH
結果を集合演算する UNION, EXCEPT/MINUS, INTERSECT
  • DBMS によって、使える機能やキーワードが異なる。

集合演算子

  • 集合演算子は、複数の SELECT 文の結果を使って集合演算を行う。
  • UNIONは和集合、EXCEPTとMINUSは差集合、INTERSECTは積集合を求める。
  • 集合演算子を用いるには、列数とデータ型を一致させる必要がある。
  • 集合演算子と ORDER BY 句を併用する際の制限に注意。

集合演算子と ORDER BY 句を併用する際の注意点

  • ORDER BY 句は最後の SELECT 文に記述する。
  • 列番号以外による指定(列名や AS による別名)の場合、1 つ目の SELECT 文に記述したものを指定する。

列数の一致しない SELECT 文を繋げるテクニック

列数が足りない方の選択列リストに NULL やその他のリテラルを追加して、列数を一致させられる。

基本的な算術演算子

演算子 使い方 説明
+ 数値+数値 数値同士で足し算する
+ 日付+数値 日付を指定日数だけ進める
- 数値-数値 数値同士で引き算する
- 日付-数値 日付を指定日数だけ戻す
- 日付-日付 日付の差の日数を得る
* 数値*数値 数値同士で掛け算をする
/ 数値/数値 数値同士で割り算をする
|| 文字列 || 文字列 文字列を連結する

特殊な演算子

CASE 演算子

  • 列の値や条件式を評価、その結果に応じて値を自由に変換してくれる。

関数

  • 関数は、引数に対し、決められた処理を行い、戻り値に変換する。
  • 関数は、処理の内容や戻り値に応じて、文字列関数、算術関数、日付関数、変換関数などに分類される
  • 関数は DBMS 製品による違いが大きいので、各製品のマニュアルを確認するべき。

関数について定められていること

名前　:その関数の名前
引数　:その関数を呼び出す際に引き渡す情報(関数によっては 2 つ以上の場合もあり)
戻り値 　:その関数の呼び出し結果として得られる情報

データの集計

集計関数

集計関数の特徴

  • データを集計できる
  • 検索対象の全行をひとまとめに扱い、一回だけ集計処理行う。
  • 集計関数の結果は必ず一行になる。
  • SELECT 文でのみ使用可能

代表的な集計関数

関数名 説明
SUM 各行の値の合計を求める
MAX 各業の値の最大値を求める
MIN 各業の値の最小値を求める
AVG 各業の値の平均値を求める
COUNT 行数をカウント

集計に関する注意点

集計関数の記述できる場所

  • SELECT 文の選択肢リストか ORDER BY 句、HVING 句のみ記述可能。

SQL の結果表

  • 結果表が凸凹になるような SQL 文は実行不可。

グループ化

グループ化とは

SQL に備わっている、集計前に、指定した基準で検索結果をいくつかのまとまりに分ける機能

概要

  • GROUP BY 句にグループ分けの基準となる列を指定して、グループ別に集計を行う。
  • GROUP BY 句を用いない集計 → 検索結果の全件を１つのグループとして扱う。
  • 集計値をもとに特定のグループのみを抽出 → HAVING 句を使用

集計テーブル

  • 大量のデータを取り扱う場合に用いられる。

副問合せ

概要

SQL 文のネスト

  • SQL 文の中に別の SELECT 文を記述可能。→ これを副問合せ、副照会、サブクエリ などと呼ぶ。
  • 副問合せは、実行すると何らかの値に置き換わる。
  • 副問合せは、内側から外側に向かって評価される。

副問合せの３つのパターン

単一行副問合せ

  • 副問合せの結果が一行一列になるもの。
  • 単一の値のかわりに副問合せの検索結果を用いる。
  • SELECT 文の選択肢リストや FROM 句、UPDETE 文の SET 句、１つの値との判定を行う WHERE 句の条件式などに記述できる。

複数行副問合せ

  • 副問合せの結果がｎ行 1 列になるもの。
  • 複数の値のかわりに,副問合せ結果を用いる
  • 複数の値との判定を行う WHERE 句の条件式や、SELECT 文の FROM 句に記述できる。
  • よく IN、ANY、ALL 演算子などと合わせて使用される。
  • 複数行副問合せの結果に NULL が含まれている場合 → NOT IN、<> ALL 演算子の評価結果も NULL になる。

  • 複数行との比較

    • 複数行副問合せは複数の値に化けるので、単なる符号や不等号では比較できない。
    • 等号や不等号に ANY/ALL 演算子を組み合わせたり、IN/NOT IN 演算子を用いたりすることで、複数の値と比較できる。

表副問合わせ

  • 副問合せの結果がｎ行ｍ列の表形式になる副問合せ。
  • 表のかわりに副問合せを用いる。
  • SELECT 文の FROM 句や INSERT 文などに記述できる。

副問合せの結果から確実に NULL を除外する方法

  • 副問合せの絞り込み条件に、IS NOT NULL 条件を含める。
  • COALESCE 関数を使って NULL を別の値に置き換える。

副問合せのコツ、注意点

  • 個々の SQL 文を１つずつ作り、あとから組み立てる。

テーブルの作成、削除

DDL

  • データ定義言語

テーブル作成には代表的な DDL である CREATE TABLE 文を使う。

  • この文では、作成したいテーブルの名前、テーブルを構成する列と型の一覧を指定。

デフォルト値の指定

CREATE TABLE 文で DEFAULT キーワードを使う。

  • INSERT 文で具体的な値を指定しない列に、NULL ではなく特定のデフォルト値(初期値)を格納できるようにする。

例：家計簿テーブル作成、デフォルト値の指定

CREATE TABLE 家計簿 (
 日付 DATE,
 ID INTEGER,
 メモ VARCHAR(100) DEFAULT '不明',
 入金 INTEGER, DEFAULT 0,
 出金 INTEGER, DEFAULT 0
)

テーブルの削除

DROP TABLE 文を使用。

DROP TABLE テーブル名

テーブル定義の内容の変更には、ALTER TABLE 文を使う。

  • 列の追加

ALTER TABLE テーブル名 ADD 列名 型

  • テーブルの削除

ALTER TABLE テーブル名 DROP 列名

例：家計簿の列の追加と削除

ALTER TABLE 家計簿 ADD 関連日 DATE;　-- 追加する
ALTER TABLE 家計簿 DROP 関連日; -- 削除する

既存のテーブルに列を追加する場合、新しい列が挿入される位置は、原則として一番うしろになる。

制約

あえて制限することによって安全性を高める仕組み。

  • 予期しない値を格納できないように制限をかけることで、人為的ミスによるデータ破壊の可能性を減らすことが可能。

基本的な 3 つの制約

NOT NULL 制約

  • NULL の格納を許可しないための制約。
  • NOT NULL 制約は、DEFAULT 指定と組み合わせて利用される場合がほとんど。

UNIQUE 制約

  • ある列の内容が決して重複してはならない場合に使用。
  • NULL が格納された行が複数存在することは許される。
  • 「NULL は NULL とも等しくない」ことがその理由。

CHECK 制約

  • ある列に格納される値が妥当であるかを細かく判定したい場合に用いる。
  • 「CHECK」の後ろのカッコ内に記述した条件式が真になる値だけが格納を許される。

主キー制約 (PRIMARY KEY 制約)

この制約の付いている列は、単なる「NULL も重複も許されない列」ではなく、
そのテーブルで管理しているデータを一意に識別する、主キーの役割が期待されているという、意味(セマンティクス)を持つ。

主キー制約の指定 (単独列)

CREATE TABLE 費目 (
  ID INTERGER PRIMARY KEY, -- 主キー制約
  名前 VARCHAR(40) UNIQUE
)

主キー制約 (複合主キー)

CREATE TABLE 費目 (
  ID INTERGER,
  名前 VARCHAR(40) UNIQUE
  PRIMARY KEY(ID, 名前) -- ID列と名前列で複合主キーを構成する
)

参照複合性の崩壊

  • 外部キーで別のテーブルの行を参照しているのに、その行が存在しない状態をいう。
  • この状態になることは、絶対に避けなければならない。

参照整合性の崩壊を引き起こすデータ操作

  • 「他の行から参照されている」行を削除してしまう。
  • 「他の行から参照されている」行の主キーを削除してしまう。
  • 「存在しない行を参照する」行を追加してしまう。
  • 「存在しない行を参照する」行に更新してしまう。

参照整合性制約を崩す 4 つの操作の例

-- ①家計簿テーブルで利用中の費目について、費目テーブルから削除
DELETE FROM 費目 WHERE ID = 2;
-- ②家計簿テーブルで利用中の費目について、費目テーブルのIDを変更
UPDATE 費目 SET ID = 5 WHERE ID = 1;
-- ③家計簿テーブルに行を追加する際、費目テーブルに存在しない費目を指定
INSERT INTO 家計簿 (日付, 費目ID, 入金額, 出金額)
     VALUES ('2022-04-06', 99, 0, 800);
-- ④家計簿テーブルの行を更新する際、費目テーブルに存在しない費目を指定
UPDATE 家計簿 SET 費目ID = 99
 WHERE 日付= '2022-04-10';

外部キー制約 (FOREIGN KEY 制約)

  • 参照整合性が崩れるようなデータ操作をしようとした場合にエラーを発生させ、強制的に処理を中断させる制約。
  • この制約は、参照元のテーブルの外部キー列に設定する。

外部制約キーの指定

CREATE TABLE テーブル名 (
 列名 型 REFERENCES 参照先テーブル名 (参照先名)
 ：
)

例文

CREATE TABLE 家計簿 (
  日付        DATE           NOT NULL,
  費目ID      INTEGER        REFERENCES 費目 (ID), -- 外部キー制約
  メモ        VARCHAR(100)   DEFAULT '不明' NOT NULL,
  入金額      INTEGER        DEFAULT 0 CHECK(入金額 >= 0),
  出金額      INTEGER        DEFAULT 0 CHECK(入金額 >= 0)
)

ACID 特性

  • 原子性
  • 一貫性
  • 分離性
  • 永続性

ロールバックとロールフォワード

  • ロールバック(実行した処理を取り消す)
  • データベースの利用中に実行失敗やデッドロックなどを要因として、度々発生する。
  • ロールフォワード(まだ実行されてない処理を実行する)
  • 障害復旧時に行われる処理であるため、滅多に発生しない。

参考資料

スッキリわかるSQL入門 第3版

Discussion