SQL

【SELECT FROM WHEREだけじゃないよ】SQLの全体像と使い方

  /
目次
  1. SQLとは？
  2. データ定義言語（DDL）
  3. データ操作言語（DML）
  4. データ制御言語（DCL）

SQLとは？

関係データベース言語であるSQLは、関係データモデルに基づく標準のデータベース言語であり、分類として大きく以下の3つがあります。

  • データ定義言語（DDL）
  • データ操作言語（DML）
  • データ制御言語（DCL）

よく使われるSQLのSELECTは、DMLに分類されます。
他にもトランザクション制御言語（TCL）と言うものがありますが、ここでは、データ制御言語（DCL）と同じ分類として考えます。

SQLの特徴

関係データモデルへの準拠

関係データベースモデルに基づいて定義された言語である

公的機関による標準化

公的機関によって標準化されているために、どのRDBMS製品にも使用されている

管理機能の実装

テーブルの危機管理機能も標準化されている

セット処理への対応

データに１件ずつアクセスするだけでなく、条件を満たす複数のデータに対して一括して操作を行える

関係データモデルとSQLの用語の対応

関係データモデルSQL
関係（リレーション）表（テーブル）
タプル行
属性（アトリビュート）列
定義域（ドメイン）定義域（ドメイン）
ここでは、SQLの基本的な文法や使い方を紹介していきます。 SQL詳しい部分（SQLの高速化の仕方など）については、別な記事で紹介します。まずは、この記事でSQLの全体像を見ていただきたいです。

データ定義言語（DDL）

データベースを定義するための言語。テーブル、ビュー、インデックス、ストアドプロシージャを定義、削除、変更したりします。そのほかにも、ロールやシーケンスも定義できる。

  • テーブル
  • ビュー
  • インデックス
  • ストアドプロシージャ
  • ロール
  • シーケンス
テーブルとビューの違い

ビューとは、CREATE TABLE で定義された実テーブルから作成される仮想的なテーブルのことです。ビューはあくまでも仮想テーブルなので、その中にデータは存在しません。ビューは実テーブルをどのように見るのかを定義したものと言えます。 参照：https://www.techscore.com/tech/sql/SQL9/09_01.html

CREATE

テーブル、ビュー、インデックス、ストアドプロシージャ、ロール、シーケンスを定義する。

/* テーブル定義 */
CREATE TABLE テーブル名(
    列名1 データ型 [DEFAULT デフォルト値] [列の制約] 
    [, ...列名n データ型 [DEFAULT デフォルト値] [列の制約]]
    [テーブルの制約]
)

※ DEFAULTでは、INSERTで具体的な値を指定しなかった場合に、NULLではなく特定のデフォルト値（初期値）を格納する。

/* ビュー定義 */
CREATE VIEW ビュー名 [(列のリスト)] AS(
    SELECT文
    [WITH [CASCADED | LOCAL] CHECK OPTION]
)

※ビューとは、SELECT文に名前をつけたものです。ビューを使用することで、SQL文がシンプに記述できる。

/* インデックス定義 */
CREATE INDEX インデックス名 ON テーブル名(
    列名1
    [, ...列名n]
)

※インデックスとは、データベースで作成することのできる索引情報のことで、検索を高速化することができる。インデックスを使えば、必ず高速化できると言うわけではないが、ここでは深掘りしない。

データ型

分類 データ型 意味
文字列型 CHAR(n) n文字の半角固定長文字列
NCHAR(n) n文字の全角固定長文字列
VARCHAR(n) 最大n文字の半角可変長文字列
NCHAR VARYING(n) 最大n文字の全角可変長文字列
整数型 NUMBER(p.q) q桁の小数部を持つp桁の10進数
SMALLINT 2バイトの符号付き整数
INTGER 4バイトの符号付き整数
REAL 符号つき浮動小数
日付/時間型 DATE 日付（年月日）
TIME 時間（時分秒）
TIMESTANP タイムスタンプ
その他 BLOG

BLOG(Binary Large OBject)型は、
画像や音声、動画などの様々な種類の
バイナリデータをデータベース中に
直接格納することができるデータ型

テーブルの制約

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

制約 説明と構文
主キー制約

一意性制約＋非ナル制約を合わせた制約。
主キーの定義に違反したデータの挿入を防ぐことができる。

PRIMARY KEY
複数列にまたがる場合は、表の最後に
PRIMARY KEY(列名リスト)のように記述する
参照制約
（外部キー制約）

列（または列の組）のデータが、他のテーブルのデータを
参照して一致していることを保証する制約。
この制約では、参照されるテーブルと列（または列の組）に
同じ値があるかどうかを確認し、違反したデータの挿入を
防ぐことができる。

REFERENCES テーブル名(列名)[オプション]
複数行にまたがる場合は、表の最後に
FOREIGN KEY REFERENCES テーブル名(列名リスト)のように記述する

※ 参照制約のオプション

  • NO ACTION (デフォルト) 外部キーが参照制約に違反した場合には、
    エラーを生成して何もしない。

  • CASCADE 参照すると列と参照される列の値を連動させる。
    参照される列の値が更新、削除されると、
    参照している列の値も連動して更新、削除される。

  • SET NULL 対応行がなくなった参照列の値にNULLを設定する

  • SET DEFAULT 対応行がなくなった参照列の値に
    あらかじめ設定されていたデフォルトの値を設定する。
非ナル制約

列のデータがNULLでないことを保証する制約。
必ず値を設定する必要がある。

NOT NULL
一意性制約

列（または列の組）のデータが他の行と重複しないことを
保証する制約。
NULLは複数の行に存在しても良い。
主キーではない候補キーでの使用が一般的。

UNIQUE
範囲制約

列のデータ値が特定の条件を満たすかどうかを検査する制約。

CHECK(範囲の条件)

ALTER

CREAREで定義したテーブル、　　の定義を変更する。

/* 属性の追加 */
ALTER TABLE テーブル名 ADD 列名　型　制約

/* 属性の削除 */
ALTER TABLE テーブル名 DROP 列名　型　制約

DROP

CREATEで定義したテーブル、ビュー、インデックス、シーケンスを削除する。

DROP TABLE テーブル名
DROP VIEW ビュー名
DROP INDEX インデックス名
DROP SEQUENCE シーケンス名

TRUNCATE

テーブルの全行を削除する。
※ 厳密には、データの削除ではなく、初期化の命令。

TRUNCATE TABLE テーブル名

実行結果は、DELETE TABLE テーブル名と同じだが、次のような違いがある。

  • DELETEは、WHERE句で指定した行だけ削除できるが、TRUNCATEは必ず全行を削除する。
  • DMLだが、TRUNCATEはDDLに所属する命令
  • DELETEはロールバックに備えて記録を残しながら仮削除していくが、TRUNCATEは記録を残さずに行を削除する（ロールバックできない）
  • DELETEは記録を残すために低速だが、TRUNCATEは記録を残さないために高速

データ操作言語（DML）

データベースのデータを操作するための言語。表内のデータの参照・更新・追加・削除などを行う。

命令 動作
SELECT データの参照
INSERT データの追加
UPDATE データの変更
DELETE データの削除

SELECT

データを参照する

構文

SELECT * | [ALL, DISTINCT, TOP 数字, LIMIT 数字]  
    列名１[, 列名２, ...]
  FROM  テーブル名１[, テーブル名２, ...] 
  [JOIN  テーブル名２]
  [ON  結合条件]
  [WHERE 検索条件１ (AND 検索条件２ ...)]
  [GROUP BY グループ化する列の名前（または列の位置）]
  [HAVING グループ化した後の行を抽出する条件]
  [ORDER BY　整列の元になる列 [ASC | DESC]]

SELECTの評価順序

普通のプログラミング言語は上から下の順番で実行されていきますが、SQLはそうではありません。どのような優先順位で実行していくかは下の表のようになっています。

〜句内容
FROM参照するテーブルを指定
ON結合させるテーブルの条件を指定
JOIN参照しているテーブルに結合させるテーブルを指定
WHERE結合を終えたテーブルの列の条件を指定
GROUP BY結合を終えたテーブルの行をグループ化する列の条件を指定
HAVINGグループ化したテーブルの行を抽出する列の条件を指定
SELECT表示するテーブルの列を指定
DISTINCT重複を取り除く→重複がなくなる
ORDER BY行をどの列の情報で並べかえをするかを指定
TOP (LIMIT)取得件数を指定
【これを知らずにSELECTは語れない】SQLのSELECTの力を引き出すための詳細文法 SELECT 構文 SELECT [ALL | DISTINCT ] 表示する列のリスト FROM...

INSERT

データを追加する

INSERT [INTO] 表名 | ビュー名 [(列のリスト)]
    VALUES (値1 [, ...値n])
INSERT [INTO] 表名 | ビュー名 [(列のリスト)]
    SELECT文

※ テーブルの列の全てにデータを追加する場合は、列のリストは省略可能

UPDATE

データを更新する

UPDATE 表名 | ビュー名
    SET 列名1 = 値1 [, ...列名n = 値n]
    [WHERE 選択条件]

※WHERE句のないUPDATE命令は全件更新！ 　現在のテーブルの属性の値をもとに、値を更新することも可能

DELETE

データを削除する

DELETE [FROM] 表名 | ビュー名
    [WHERE 選択条件]

※ WHERE句のないDELETE命令は全件削除！

EXPLAIN

LOCK TABLE

データ制御言語（DCL）

データベースを制御するための言語です。トランザクションの開始や終了したり、アクセス権限などを制御する。

トランザクションの開始や終了に関する命令をトランザクション制御言語（TCL）といい、DCLと分けるときがあります。ここでは、DCLと同じ枠組みとして考えます。

命名 動作
GRANT 権限の付与
REVOKE 権限の取り消し

GRANT

権限を付与する

GRANT {ALL [PRIVILEGES]}
| SELECT 
| INSERT [(列のリスト)]
| DELETE 
| UPDATE [(列のリスト)]
| REFERENCES [(列のリスト)]　
| USAGE
ON {[TABLE] 表(ビュー)名 
| DOMAIN ドメイン名 
| COLLATION 照合順序名 
| CHARACTER SET 文字コード設定名 
| TRANSLATION 置換名}
TO {ユーザー名 | PUBLIC}
    [WITH GRANT OPTION]

REVOKE

権限の取り消し

START TRANSACTION

START TRANSACTION { {READ ONLY | READ WRITE} [,...]
| ISOLATION LEVEL
    { READ UNCOMMITTED | READ COMMITED 
    | REPEATABLE READ | SERIALIZABLE [,...] 
| DIAGNOSTIC SIZE int(整数)};

トランザクション開始時にISOLATION LEVELでトランザクションの分離レベルを設定する

  • ① READ UNCOMMITTED
    コミットしていないデータまで読み取る
  • ② READ COMMITTED
    コミットした最新のデータを読み取る
  • ③ REPEATABLE READ
    読みとり対象のデータが他のトランザクションで更新されないことを保証する
  • ④ SERIALIZABLE
    トランザクションを独立して順番に実行した場合と同じ結果になる

※ ①最も高速で危険（分離性低い）〜④最も低速で安全（分離性高い）

分離レベルダーティーリードインコンシステントリードノンリピータブルリードロストアップデートファントムリード
READ UNCOMMITTED恐れあり恐れあり恐れあり恐れあり恐れあり
READ COMMITTED発生しない恐れあり恐れあり恐れあり恐れあり
REPEATABLE READ発生しない発生しない発生しない恐れあり恐れあり
SERIALIZABLE発生しない発生しない発生しない 　発生しない発生しない

BEGIN

この命令以降のSQL文を1つのトランザクションとする。

COMMIT

この命令までのSQL文を1つのトランザクションとし、変更を確定する。

ROLLBACK

この命令までのSQL文を1つのトランザクションとし、変更の取り消しをする。

SAVEPOINT

関連記事