［sql］よく使う基本構文と知っておくと便利な構文

完全に自分用のメモです。
DBを作る構文から、データ抽出の際に使用頻度の高い構文までの代表的なものをまとめてみました。
基本的にMySQLにおける構文となっています。

［DDL］ DB作成からテーブルの作成・編集など

まずはDDL(Data Definiation Language)のよく使うコマンドを確認

DBの作成 ［CREATE］

CREATE DATABASE test_db;

DBの選択 ［USE］

USE test_db;

テーブル作成 ［CREATE］

userというテーブルを例に作成してみます。
［id・name・age・height］ という4つのカラム構成におけるcreate文を、せっかくなので［MySQL・PosgreSQL・Oracle］それぞれの形式で記述してみました。

MySQL

-- MySQL
CREATE TABLE user(
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  age INT,
  height INT
);

posgreSQL

-- posgreSQL
CREATE TABLE user(
  id BISERIAL PRIMARY KEY,
  name VARCHAR(255),
  age INTEGER,
  height INTEGER
);

Oracle

-- Oracle
CREATE TABLE user(
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  name VARCHAR2(255),
  age NUMBER,
  height NUMBER
);

テーブル編集 ［ALTER TABLE］

カラムの追加

ALTER TABLE user ADD COLUMN email VARCHAR(255);

カラムの削除

ALTER TABLE user DROP COLUMN email;

カラムのデータ型変更

ALTER TABLE user MODIFY COLUMN email VARCHAR(64);

テーブル名の変更

ALTER TABLE user RENAME TO customers;

［DML］ データ操作系

次にselectなどのデータ操作系(Data Manipulation Language)の構文をまとめていく。

データ抽出 ［SELECT］

テーブルからレコードの取得を行う構文。

SELECT * FROM テーブル名

*で全てのカラムを出力。
特定のカラムを出力する場合はコンマ区切りでカラム名を指定。

SELECT カラム名, カラム名, カラム名 FROM テーブル名

並び替え ［ORDER BY］

SELECT * FROM テーブル名 ORDER BY カラム名

ORDER BY句の利用で並び替えが可能。
カラム名の後に何も記述しないと昇順で結果を表示。
降順で表示する場合はDESCをつける。

-- DESCは降順
SELECT * FROM テーブル名 ORDER BY カラム名 DESC
-- ASCは昇順 （記述を省いても良い）
SELECT * FROM テーブル名 ORDER BY カラム名 ASC

昇順: 小さい値 → 大さい値 の順番
降順: 大きい値 → 小さい値 の順番

範囲指定での抽出 ［BETWEEN］

特定の値の範囲に当てはまるレコードを表示するにはWHERE句の後に BETWEEN AND を記述する。

SELECT * FROM テーブル名 WHERE BETWEEN カラム名 下限値 AND 上限値

-- 年齢が10歳から20歳までに該当するユーザーレコードを抽出する
SELECT * FROM user WHERE age BETWEEN 10 AND 20

以下のように比較演算子を利用して記述することも可能。
比較演算子の場合

SELECT * FROM user WHERE BETWEEN 10 <= age AND age <= 20

あいまい検索 ［LIKE］

部分一致の方法で検索する場合は［LIKE］ と ［%］ を使用する。

SELECT * FROM user WHERE name LIKE 'シブヤ%'

nameカラムの値が ［シブヤ テルヒサ］ というレコードがある場合、上のSQLを実行するとレコードが取得される。
なお、前後1文字の部分一致検索を行う場合は以下の記述でOK

SELECT * FROM user WHERE name LIKE 'シブヤ_'

nameカラムの値が ［シブヤテ］ というレコードがあれば上記のsqlで取得される。
［シブヤ テルヒサ］ というレコードは取得されない。

重複排除 ［DSTINCT］

DISTINCTという句の後で特定のカラムを指定する。指定したカラムで値が重複しているレコードがある場合は、重複するレコードは抽出しない。

SELECT DISTINCT name FROM user

nameの値が［シブヤテルヒサ］という値のレコードが2つあった場合、1レコードのみ抽出される。
なお、以下の［GROUP BY］ 句を使用しても同様の結果が得られる。

SELECT name FROM user GROUP BY name

最大値・最小値・平均 ［MAX ・ MIN ・ AVG］

-- 最大値の抽出
SELECT MAX(age) FROM user
-- 最小値の抽出
SELECT MIN(age) FROM user
-- 平均値の抽出
SELECT AVG(age) FROM user

レコード数や値のカウント ［COUNT］

-- userテーブルに登録されているレコード数を出力
SELECT COUNT(*) FROM user

括弧の中でカラム名を指定すると、指定したカラムに登録されている値が何種類あるかあ出力してくれる。

SELECT COUNT(age) FROM user

例えば 20歳〜25歳までのデータが1歳刻みでテーブルに登録されている場合、出力結果は ［6］ となる。

グループ化 ［GROUP BY］

GROUP BY句を利用してグループ化できる。
基本的には ［DISTINCT］ と同じような動きをするが、最大・最小・レコード件数(COUNT)などの集計関数と組み合わせて使用できるのがポイント。

各年齢ごとの最大身長を求める場合。

SELECT age, MAX(height) FROM user GROUP BY age;

HAVING句

GROUP BY句に対して抽出条件を設定できる。

・年齢でグループ化を行う
・170cm以上の人がいるグループ年齢グループを抽出

上記の抽出条件は以下の形式で記述できる

SELECT age FROM user GROUP BY age HAVING height >= 170

サブクエリ

先に実行されるsql。特定のsqlの結果を用いて別の結果を取得したい場合に使用する。

employees テーブルにおいて、部署IDが1の従業員の平均給与よりも高い給与を持つ従業員の名前と給与を取得する場合は以下の通り。

SELECT name, salary FROM employees WHERE
 salary > (SELECT AVG(salary) FROM employees WHERE department_id = 1);

SELECT 
  department_name
FROM 
  departments
WHERE 
  department_id 
IN
  (SELECT department_id FROM employees WHERE salary > 50000);

employees テーブルから給与が50000以上の従業員が所属する部署の部署名が抽出される。
サブクエリにより得られるレコード(行)が複数の場合は IN で囲むことで対応可能。