SQL基本

テーブル作成・削除

CREATE

---テーブルの作成
CREATE TABLE tableName (
column1 datatype,
column2 datatype,
column3 datatype,
...
);

---他テーブルを参照してテーブルを新たに作成
CREATE TABLE newTableName AS SELECT column1,... FROM tableName;

DROP

---テーブルの削除
DROP TABLE tableName;

レコード取得・挿入・更新・削除

SELECT

---データの取り出し
SELECT column1,... FROM tableName;

---データの取り出し(全カラム指定)
SELECT * FROM tableName;

---カラムに別名を付与
SELECT column1 AS alias1,...  FROM tableName;

---条件を絞り込み
SELECT column1,... FROM tableName WHERE column1 = value1;

---カラムの連結
SELECT column1 || column2 FROM tableName;

INSERT

---データの挿入(カラム指定あり)
INSERT INTO tableName (column1,...) VALUES (value1,...);

---データの挿入(カラム指定なし)
INSERT INTO tableName VALUES (value1,...);

---SELECTで取り出した結果をテーブルに格納
INSERT INTO tableName SELECT column1,... FROM tableName;

UPDATE

---データの更新
UPDATE tableName SET column1 = value1;

---データの更新(条件に満たすもの)
UPDATE tableName SET column1 = value1 WHERE column2 = value2;

DELETE

---データの削除
DELETE FROM tableName;

---データの削除(条件に満たすもの)
DELETE FROM tableName WHERE column1 = value1;

レコード絞り込む

WHERE

---条件の否定
SELECT * FROM tableName WHERE column1 NOT value1;

---含まれるいずれかのもの
SELECT * FROM tableName WHERE column1 IN (value1,...);

---含まれないのもの
SELECT * FROM tableName WHERE column1 NOT IN (value1,...);

---パターンに一致するもの
SELECT * FROM tableName WHERE column1 LIKE '正規表現';

---パターンに一致しないもの
SELECT * FROM tableName WHERE column1 NOT LIKE '正規表現';

---value1以上value2以下のもの
SELECT * FROM tableName WHERE column1 BETWEEN value1 AND value2;

---value1以上value2以下以外のもの
SELECT * FROM tableName WHERE column1 NOT BETWEEN value1 AND value2;

---NULL値のみ
SELECT * FROM tableName WHERE column1 IS NULL;

---NULL値以外
SELECT * FROM tableName WHERE column1 IS NOT NULL;

---条件Aかつ条件B
SELECT * FROM tableName WHERE '条件A' AND '条件B';

---条件A又は条件B
SELECT * FROM tableName WHERE '条件A' OR '条件B';

DISTINCT

---重複を除いたレコードの取り出し
SELECT DISTINCT column1,... FROM tableName;

LIMIT

---指定したレコード数を取り出す
SELECT * FROM tableName LIMIT rowCount;

OFFSET

---指定したレコード数を飛ばしてから指定したレコード数を取り出す
SELECT * FROM tableName LIMIT limitRowCount OFFSET offsetRowCount;

レコード並び変え

ORDER BY

---昇順に並び替える
SELECT * FROM tableName ORDER BY column1 (ASC);

---降順に並び替える
SELECT * FROM tableName ORDER BY column1 DESC;

##レコード集計

GROUP BY

---特定のカラムで集計する
SELECT column1, '集計関数'(column2) FROM tableName GROUP BY column1;

HAVING

---特定のカラムで集計した後特定の条件で絞り込む
SELECT column1, '集計関数'(column2) FROM tableName GROUP BY column1 HAVING '集計関数'(column2);

WHERE - 絞り込んで集計
HAVING - 集計したのち絞り込み

集計関数

---要素の数
SELECT COUNT(*) FROM tableName;

---合計値
SELECT SUM(column1) FROM tableName;

---平均値
SELECT AVG(column1) FROM tableName;

---最大値
SELECT MAX(column1) FROM tableName;

---最小値
SELECT MIN(column1) FROM tableName;

CASE文

---条件を判定して満たす値を返す
SELECT
CASE
WHEN '条件' THEN '結果'
WHEN '条件' THEN '結果'
...
ELSE '結果'
END
FROM tableName;

SQL応用

テーブル定義

CREATE TABLE tableName (
  column1 datatype PRIMARY KEY, ---主キー制約
  column2 datatype NOT NULL, ---非ナル制約
  column3 datatype CHECK('条件式') DEFAULT '値', ---チェック制約
  column4 datatype UNIQUE, ---一意性制約
  column5 datatype, ---参照制約
  FOREIGN KEY(column5)
  REFERENCES tableName(column0)
)

副問い合わせ(サブクエリ)

---FROM句で副問い合わせを指定する
SELECT
  AVG(alias1.alias2)
FROM(
  SELECT
    SUM(column2) AS alias2
  FROM
    tableName1
  GROUP BY
    column1
) AS alias1;

---IN句の中に副問い合わせを入れる
SELECT
  *
FROM
  tableName1
WHERE column3 IN (
  SELECT
    column1 
  FROM
    tableName2
  WHERE
    column2 = '値'
);

---SELECTの取得結果にSELECTを含める
SELECT
  column1,
  (
    SELECT AVG(column2)
    FROM tableName2
    WHERE tableName2.column1 = tableName1.column1
  ) AS alias1
FROM
  tableName1;

EXISTS

---IN句と同様の結果が得られる(実行計画が異なる)
SELECT
  *
FROM
  tableName1
WHERE EXISTS (
  SELECT
    1
  FROM
    tableName2
  WHERE
    column2 = '値' AND column1 = tableName1.column1 
);

---上記の否定
SELECT
  *
FROM
  tableName1
WHERE NOT EXISTS (
  SELECT
    1
  FROM
    tableName2
  WHERE
    column2 = '値' AND column1 = tableName1.column1 
);

テーブル結合

JOIN (INNER JOIN)

---一般的な方法
SELECT
*
FROM
tableName1 AS alias1, tableName2 AS alias2
WHERE
alias1.column1 = alias2.column1;

---内部結合(紐づくレコードのみ取得)
SELECT
*
FROM
tableName1 AS alias1
INNER JOIN
tableName2 AS alias2
ON alias1.column1 = alias2.column1;

LEFT (OUTER) JOIN

---外部結合(紐づかなかったレコードはNULLとして取得)
SELECT
*
FROM
tableName1 AS alias1
LEFT JOIN
tableName2 AS alias2
ON alias1.column1 = alias2.column1;

SELF JOIN

---同じテーブル同士で結合
SELECT
*
FROM
tableName AS alias1
INNER JOIN ---or LEFT JOIN
tableName AS alias2
ON alias1.column1 = alias2.column3;

UNION

---共通要素は1つにして結合(重複なし)
SELECT
column1
FROM
tableName1
UNION
SELECT
column1
FROM
tableName2;

UNION ALL

---共通要素は削除せず足し合わせて結合(重複あり)
SELECT
column1
FROM
tableName1
UNION ALL
SELECT
column1
FROM
tableName2;

EXCEPT

---tableName1の結果からtableName2に存在するものを除いて取得
SELECT
column1
FROM
tableName1
EXCEPT
SELECT
column1
FROM
tableName2;

INTERSECT

---共通要素のみ取得
SELECT 
column1
FROM
tableName1
INTERSECT
SELECT
column1
FROM
tableName2;

INDEX

---インデックスの作成
CREATE [UNIQUE] INDEX
indexName
ON tableName(column1);

---複合インデックスの作成
CREATE [UNIQUE] INDEX
indexName
ON tableName(column1, column2);

---インデックスの削除
DROP INDEX [IF EXISTS] indexName;

目安として絞り込む対象が全データ量に対して15%未満の場合はインデックスを利用した方が高速。

WITH

---テーブルから取得したデータを中間テーブルに格納し後のSQLに使用
WITH middleTableName AS(
  SELECT
    *
  FROM
    tableName1
  WHERE
    column1 = '値'
)
SELECT
  *
FROM
  tableName2
WHERE
  column1 IN(SELECT column1 FROM middleTableName);

Transaction

複数の処理をまとめたもの。
COMMIT、ROLLBACKをするまで処理を完了せず、
DELETE、UPDATEなどをするとテーブルをロックして他プロセスからDELETE、UPDATEできないようにする。

BEGIN TRANSACTION

トランザクションの開始。

COMMIT

トランザクション中に実行した処理を反映する。

ROLLBACK

トランザクション中の実行結果を破棄してトランザクション前の状態に戻す。

DEAD LOCK

複数のプロセスが互いのテーブルをロックしてその次の処理にすすめなくなること。

VIEW

---Viewの作成
CREATE VIEW viewName (columnName1,...) AS
SELECT * FROM tableName WHERE column1 IN (value1,...);

---Viewの結果の取得
SELECT * FROM viewName;

---Viewの削除
DROP VIEW viewName;