SQLとは?
関係データベース言語であるSQLは、関係データモデルに基づく標準のデータベース言語であり、分類として大きく以下の3つがあります。
- データ定義言語(DDL)
- データ操作言語(DML)
- データ制御言語(DCL)
よく使われるSQLのSELECTは、DMLに分類されます。
他にもトランザクション制御言語(TCL)と言うものがありますが、ここでは、データ制御言語(DCL)と同じ分類として考えます。
SQLの特徴
関係データモデルへの準拠
関係データベースモデルに基づいて定義された言語である
公的機関による標準化
公的機関によって標準化されているために、どのRDBMS製品にも使用されている
管理機能の実装
テーブルの危機管理機能も標準化されている
セット処理への対応
データに1件ずつアクセスするだけでなく、条件を満たす複数のデータに対して一括して操作を行える
関係データモデルと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 |
参照制約 (外部キー制約) |
列(または列の組)のデータが、他のテーブルのデータを REFERENCES テーブル名(列名)[オプション] ※ 参照制約のオプション
|
非ナル制約 |
列のデータがNULLでないことを保証する制約。 NOT 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 数字]
列名1[, 列名2, ...]
FROM テーブル名1[, テーブル名2, ...]
[JOIN テーブル名2]
[ON 結合条件]
[WHERE 検索条件1 (AND 検索条件2 ...)]
[GROUP BY グループ化する列の名前(または列の位置)]
[HAVING グループ化した後の行を抽出する条件]
[ORDER BY 整列の元になる列 [ASC | DESC]]
SELECTの評価順序
普通のプログラミング言語は上から下の順番で実行されていきますが、SQLはそうではありません。どのような優先順位で実行していくかは下の表のようになっています。
〜句 | 内容 |
---|---|
FROM | 参照するテーブルを指定 |
ON | 結合させるテーブルの条件を指定 |
JOIN | 参照しているテーブルに結合させるテーブルを指定 |
WHERE | 結合を終えたテーブルの列の条件を指定 |
GROUP BY | 結合を終えたテーブルの行をグループ化する列の条件を指定 |
HAVING | グループ化したテーブルの行を抽出する列の条件を指定 |
SELECT | 表示するテーブルの列を指定 |
DISTINCT | 重複を取り除く→重複がなくなる |
ORDER BY | 行をどの列の情報で並べかえをするかを指定 |
TOP (LIMIT) | 取得件数を指定 |
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つのトランザクションとし、変更の取り消しをする。