MySQLでのSQLコードのまとめ

概要

MySQLでのSQLによる操作を理解するため、まとめました。
認識間違いがあればご指摘ください。

MySQLへの接続

% mysql -u root
mysql> [以下 ここのコマンド]

共通

選択・指定

-- 使用データベースの選択
USE データベース名;
-- 対象テーブルの指定
FROM テーブル名

確認

-- データベース・テーブルの一覧表示
SHOW DATABASES;
-- テーブルの確認
SHOW TABLES;
-- テーブル構造確認
SHOW カラム名 FROM テーブル名;

主に使用する型

種類	型
整数	INT
実数	DECIMAL	＊少数点など
文字列	CHAR
文字列（長）	VARCHAR
真偽値	BOOL	ture/false
真偽値	TINYINT(1)	1/0
日付	DATE
時間	TIME
日時	DATETIME
特定文字列から1個	ENUM
特定文字列から複数	SET

DB提供側

テーブル作成、削除、権限付与など

DDL(データ定義）

CREATE(作成)

CREATE(作成)

-- データベースの作成
CREATE DATABASE データベース名;
-- テーブルの作成
CREATE TABLE テーブル名 (カラムA Aの型, カラムB Bの型, …);
-- 抽出結果を別テーブルにする,テーブルAの検索結果ををテーブルBとして作成(WHEREを外せば、全体複製）
CREATE TABLE テーブルB AS SELECT * FROM テーブルA WHERE 〜〜;
-- テーブルAの構造を複製してテーブルBとして作成（レコード含まず）
CREATE TABLE テーブルB LIKE テーブルA 
-- 元テーブルと連動した仮想テーブル(VIEW)：抽出した元データの変更に連動
-- テーブルAの変更に連動した仮想テーブルCを作成
CREATE VIEW テーブルC AS SELECT * FROM テーブルA WHERE 〜〜;

DROP/TRUNCATE(削除)

DROP/TRUNCATE(削除)

-- データベースの削除
DROP DATABASE データベース名;
-- テーブルの削除
DROP TABLE テーブル名;
-- エラーがでないよう仮にテーブルが存在していたら削除
DROP TABLE IF EXISTS テーブル名； 
-- エラーがでないよう仮に仮想テーブルが存在していたら削除
DROP VIEW IF EXISTS 仮想テーブル名； 
-- idの連番リセットしたい場合、TRUNCATEで連番リセット
TRUNCATE TABLE テーブル名; 

ALTER(追加・変更)

ALTER(追加・変更)

-- カラムの追加
ALTER TABLE テーブル名 操作
ALTER TABLE テーブル名 ADD カラムA　Aの型；  -- 1個
ALTER TABLE テーブル名 ADD カラムA　Aの型 AFTER カラムB；  -- カラムBの後にカラムAを追加
ALTER TABLE テーブル名 ADD カラムA　Aの型 FIRST； -- 先頭にカラムA追加
ALTER TABLE テーブル名 ADD (カラムA Aの型, カラムB Bの型, …); --複数

-- カラムの変更
ALTER TABLE テーブル名 CHANGE 旧カラムA 新カラムB 新カラムBの型;
-- カラムの削除
ALTER TABLE テーブル名 DROP カラム名;
-- テーブル名の変更
ALTER TABLE テーブル名 RENAME 新テーブルの名前;

テーブル作成構文例

構文

DROP TABLE IF EXISTS テーブル名； --仮にテーブルが存在していたら削除
-- テーブル作成 :設定 DDL
CREATE TABLE テーブル名 (　　　--テーブル名は複数にすることが多い
   message  VARCHAR(100),  -- カラム名　型（設定：今回は文字数）　オプション　
   likes INT UNSIGNED 　-- マイナス想定してなければ、UNSIGNEDで使用幅が増える
   likes INT DEFAULT 0 -- デフォルト値の設定
   created DATETIME DEFAULT NOW() -- デフォルト値で作成時間記録設定
   updated DATETIME DEFAULT NOW() ON UPDATE NOW() -- 更新時間自動記録    
   カラムB ENUM（'a','b','c'), --カラムBにはa,b,c,の内1つ挿入可（それ以外x）
   カラムCs SET（'a','b','c'), --カラムCs（複数形）にはa,b,c,の内複数挿入可（それ以外x）
   　　--a:2^0, B:2^1 ...と数値管理しているので、aは1、ac=1+4=5と表現可
   カラムD　Dの型 CHECK(条件）, --条件に当てはまらないものを制限
                           -- 例：0以上100以下など（カラムD >= 0 カラムD <= 100)
   カラムE　Eの型 UNIQUE, --重複を制限
   id INT NOT NULL AUTO_INCREMENT, --NULLではない整数連番、主キー設定されていれば自動設定 
   PRIMARY KEY (id)  -- idを主キーに設定し、入れ忘れや重複をエラーに。
   FOREIGN KEY (post_id) REFERENCES posts(id) -- 外部キー制約
     -- テーブル間のアソシエーションに影響するので、
     -- DROP TABLE IF EXISTS テーブル名;の順番を変更するなどで対応する。
     -- 投稿を消すためには、まず付随コメントを消させるなど
     ON DELETE CASCADE  -- 外部キー制約時、連動して削除される設定
     ON UPDATE CASCADE  -- 外部キー制約時、連動して更新される設定
   parent_id INT, -- コメントにコメントをつけるといったパターン時、カラムに親を示す設定を追加
);

-- レコードの挿入 :具体的なデータ　DML
INSERT INTO テーブル名(カラムA, カラムB) VALUES
　　　(値1, 値2),　　　--カンマ区切りで複数可、文字列は’”などで囲み
　　　(値1, 値2);
　　-- NOW():現在時間

DB使用側

既存のテーブルに対し、データの操作を行う

DML(データ操作）

SELECT(検索)

SELECT(検索)

-- テーブルのカラムを検索
SELECT カラム名,カラム名、.. FROM テーブル名  -- ,区切りで複数可
-- ワイルドカード ＊ で、すべてのカラムのデータ取得
SELECT * FROM テーブル名
-- テーブルの重複しないカラムのデータ取得
SELECT DISTINCT カラム名 FROM テーブル名
-- A名をB名に変更
A名 AS B名

INSERT(登録)

INSERT(登録)

-- すべてのカラムに値を入れる
INSERT INTO テーブル名 VALUES(値1, 値2, 値3)
-- 特定のカラムに値を入れる
INSERT INTO テーブル名(カラムA, カラムB) VALUES(値1, 値2)
-- 外部キー制約の関係にあるテーブル間で直前に挿入されたレコードの外部キーを調べて、挿入
INSERT INTO テーブルB(外部キー, カラムB) VALUES(LAST_INSERT_ID(), 値)
-- コメントにコメントをつけるパターン時などの親キーカラム設定
INSERT INTO comments(カラムA, カラムB, parent_id) VALUES (値1, 値2, 値3)

UPDATE(更新)

UPDATE(更新)

-- テーブルの条件に当てはまる部分を変更内容のように更新する(,区切りで複数可）
UPDATE テーブル名,テーブル名,... SET 変更内容 WHERE 条件

DELETE(削除)

DELETE(削除)

-- テーブルの条件に当てはまる部分を削除
DELETE FROM テーブル名 WHERE 条件

WHERE(条件)

WHERE(条件)演算子使用可

-- 条件によって取得するデータを制限
WHERE 条件( > >= < <= 他, =:等しい ,!= or <> ：等しくない )
-- A かつ B の指定
WHERE A AND B
-- A または B の指定
WHERE A OR B
-- A ではない（条件を反転させる）指定
WHERE NOT A
-- カラム名の上限〜下限に含まれているものの指定
WHERE カラム名 BETWEEN 下限 AND 上限
-- カラム名のリスト（値1..)に含まれているものの指定
WHERE カラム名 IN (値1, 値2, ……)
-- 特定箇所の文字を検索
WHERE カラム名 LIKE '検索文字'
-- ％：0文字以上の任意の文字
-- _:任意の1文字

-- NULLの取り扱い
WHERE A OR B IS NULL  -- NULLも取得
WHERE A OR B IS NOT NULL -- NULLを省く

-- サブクエリの利用
SELECT * FROM テーブル名
WHERE
  カラムA = (SELECT MAX(カラムA) FROM テーブル); -- カラムAの最大値のものを抽出

条件分岐

条件分岐

-- if文
IF（条件式,trueのときの値,falseのときの値）

-- case文
CASE　
　WHEN 条件A THEN Aのときの値
　WHEN 条件B THEN Bのときの値　　
  ELSE どれもあてはまらないときの値
END

連結

連結

-- 指定したテーブルのカラムに登録された値の一致するデータを元に結合(内部結合）
SELECT 
   *
   -- テーブルA.カラムa, テーブルB.カラムb, ...　-- 、区切りで特定カラムだけ抽出してから行うことも可
   -- id以外で重複していない場合は、テーブル名の省略可
FROM テーブルA
(INNER） JOIN テーブルB ON テーブルA.カラムa = テーブルB.カラムb -- ()省略可
-- => テーブルA の カラムa と テーブルB の カラムb が一致する行を結合する
-- 省略形
FROM users u
JOIN posts p  ON u.カラムa = p.カラムb
-- 一般的には各テーブルの頭文字を使って指定する

-- 左外部結合:テーブルAから全てのレコードを抽出し、関連するカラムbがあれば取得、ない部分はNULL
SELECT *
FROM 
  テーブルA LEFT (OUTER) JOIN テーブルB ON テーブルA.カラムa = テーブルB.カラムb; -- ()省略可

-- 右外部結合：テーブルBから全てのレコードを抽出し、関連するカラムaがあれば取得、ない部分はNULL
SELECT *
FROM 
  テーブルA RIGHT JOIN テーブルB ON テーブルA.カラムa = テーブルB.カラムb;

-- 検索結果をまとめる
(SELECT * FROM テーブルA ORDER BY カラムA DESC LIMIT 3)
UNION ALL -- 抽出結果が縦につながる
(SELECT * FROM テーブルA ORDER BY カラムA LIMIT 1);
-- 2つのクエリとカラム数とデータ型が一致している必要がある

計算

計算

-- + - * / %　などが使用可
-- ROUND()など（）がついた命令を「関数」
SELECT 
  likes * 300 / 3 AS bonus, -- 別名つけてわかり易くすることも可
  FLOOR(likes * 300 / 3) AS floor, -- 端数切り捨て
  CEIL(likes * 300 / 3) AS ceil, -- 端数切り上げ
  -- ROUND(likes * 300 / 3) AS round -- 四捨五入
  ROUND(likes * 300 / 3, 2) AS round -- ,区切りで桁数指定も可
FROM 
  posts;

文字列操作

文字列操作

SUBSTRING(カラム名, 3) FROM テーブル名; -- 文字列3文字以降を切り出し
SUBSTRING(カラム名, 3, 2) FROM テーブル名;　-- 3文字目から2文字分切り出し
SUBSTRING(カラム名, -2) FROM テーブル名; -- 末尾から2文字切り出し
-- SELECT カラム名, LENGTH(カラム名) AS len FROM テーブル名 ORDER BY len;
-- LENGTH(カラム名)文字列カウント
-- SELECT カラム名, CHAR_LENGTH(カラム名) AS len FROM テーブル名 ORDER BY len;
-- 日本語の場合は、CHAR_LENGTH(カラム名)

-- 複数の文字列を連結
CONCAT(文字列A, 文字列B, ……)

日時

日時

SELECT カラム名, YEAR(カラム名) FROM テーブル名;  -- 年　抽出
SELECT カラム名, MONTH(カラム名) FROM テーブル名;　-- 月　抽出
SELECT カラム名, DAY(カラム名) FROM テーブル名;　-- 日　抽出

-- 指定のフォーマット
SELECT カラム名, DATE_FORMAT(カラム名, '%M %D %Y , %W') AS date FROM テーブル名;
-- June 1st 2020,Friday など

-- 日付計算
SELECT カラム名, DATE_ADD(カラム名, INTERVAL 5 DAY) AS next FROM テーブル名;
-- データ日時に5日足して出力

-- 現在日時基準
SELECT カラム名, NOW(),DATEDIFF(カラム名, NOW()) AS diff FROM テーブル名;
-- 現在日時との差を出力

-- 〜秒待機
SELECT SLEEP(秒数）

集計

集計

-- カラムのデータを種類ごとにグループ化する
GROUP BY カラム名
-- GROUP BYの結果に条件を付けたい場合
HAVING 条件
-- 並び替え
ORDER BY カラム名
-- 逆順に並び替え
ORDER BY カラム名 DESC
-- 並び替えオプション
ORDER BY カラム名,message(アルファベット順） LIMIT 4 OFFSET 3; --最初の3件除外、して4件抽出
                                     LIMIT 3,4;--上と同じ

-- カラムのデータの総量をカウント（nullだとエラー）
SELECT COUNT(カラム名)
-- null含めて全ての行数をカウント
SELECT COUNT(*) 

SELECT SUM(カラム名)-- 合計
SELECT AVG(カラム名)-- 平均
SELECT MAX(カラム名)-- 最大
SELECT MIN(カラム名)-- 最小

-- サブクエリを用いた集計例
-- 大元のクエリと関連付けながら実行しているサブクエリ＝相関クエリ
-- 例：最初のレコードを処理しているときに、全体からそのレコードと等しいカラムBのレコードだけ抜き出して平均を出す
SELECT 
  *, 
  (SELECT AVG(カラムA) FROM テーブルA) AS avg,
  (SELECT AVG(カラムA) FROM テーブルA AS t2 WHERE t1.カラムB = t2.カラムB) AS area_avg
FROM 
  テーブルA AS t1;

-- 例：areaごとの投稿数：nの平均を出す場合、
SELECT 
  AVG(n)
FROM 
  (SELECT area, COUNT(*) AS n FROM posts GROUP BY area) AS t;
-- (エリアごとの投稿数=n:ereaごとにまとめ、数をカウントし、nとする)=tとして、nの平均値をだす

集計(ウィンドウ関数)

集計：ウィンドウ関数

-- テーブルをPARTITION単位で集計して、結果を各レーコードの横に追加
-- FRAME設定することで、特定範囲の累計なども可

-- PARTITION設定
SELECT 
  *, 
  AVG(カラムA) OVER () AS avg, -- 全体をPARTITIONにするなら（）のみで可
  AVG(カラムA) OVER (PARTITION BY area) AS area_avg, -- エリアごとにPARTITIONを区切る
  SUM(カラムA) OVER (PARTITION BY area) AS area_sum
-- OVER後の指定は↓の WINDOW w ~　で別名設定をすることで 
  AVG(カラムA) OVER w AS area_avg,
  SUM(カラムA) OVER w AS area_sum
-- ↑のように書き換えが可能
FROM 
  テーブル名
WINDOW w AS (PARTITION BY area); 

-- FRAME設定
SELECT 
  *, 
  SUM(カラムA) OVER (
    PARTITION BY area -- FRAMEはデフォルトで先頭からになっており
    ORDER BY カラムA  -- ここまでの設定で「エリアごとに、小さい順で累計」している
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- オプション可-- 例：前後1行を合わせる
  ) AS area_sum
FROM 
  テーブル名;

-- OVER時にのみ使用できる関数
SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY カラムA) as num, -- 小さい順に並び替えた後、連番取得
  -- 全体を対象にしているので、PARTITION省略
  RANK() OVER (ORDER BY カラムA) as rank, 
  -- 小さい順に並び替えた後、順位取得(同着が同じ値）:次の順位は飛ばす：123356..
  DENSE_RANK() OVER (ORDER BY カラムA) as dense 
  -- 小さい順に並び替えた後、順位取得(同着が同じ値）:次の順位を飛ばさない:123345..
FROM
  テーブル名;

n個前後のレコード取得

n個前後のレコード取得

SELECT
  *,
  LAG(カラムA, 1) OVER (ORDER BY カラムA) as lag, -- それぞれのレコードの1個前のレコード取得、
  LEAD(カラムA, 1) OVER (ORDER BY カラムA) as lead -- それぞれのレコードの1個後のレコード取得、
  -- 1の場合は、省略可↓
  LAG(カラムA) OVER (ORDER BY カラムA) as lag,
  LEAD(カラムA) OVER (ORDER BY カラムA) as lead
  カラムA - LAG(カラムA) OVER (ORDER BY カラムA) as diff -- 差分の表現も可 
FROM
  テーブル名;

外部キー関連

-- コメントにコメントをつける設定時、特定コメントについているすべてのコメントを抽出する
SELECT * FROM comments WHERE parent_id = 4 -- コメントid:4についたコメント
UNION ALL  -- 付随するコメントとまとめる
SELECT
  comments.*  -- コメントテーブルのコメント全てから
FROM
  comments JOIN (
    SELECT * FROM comments WHERE parent_id = 4
  ) AS t  -- (コメントid:4についたコメント)をtとして、
ON
  comments.parent_id = t.id; -- 親キーとt.idを紐付け

-- CTE (Common Table Expression)
-- クエリ内で使える一時的なテーブル：再帰的or再帰的でないもの
-- 再帰的でないもの↓:サブクエリをわかりやすくする：上の書き換え
WITH t AS (
  SELECT * FROM comments WHERE parent_id = 4
)
SELECT
  comments.*
FROM
  comments JOIN t
ON
  comments.parent_id = t.id;

-- 再帰的なもの↓:深い階層のデータを読み取りやすくする：上の書き換え
WITH RECURSIVE t AS (
  -- n = 1  :1階層目の検索を取得する＝tとする
  SELECT * FROM comments WHERE parent_id = 4
  UNION ALL  -- 上と連結
  -- n >= 2 :tを元に結果がなくなるまで処理を行う=すべての階層のコメントを処理
  SELECT   
    comments.*
  FROM
    comments JOIN t
  ON
    comments.parent_id = t.id
)
SELECT * FROM t;  -- 結果をまとめて表示

トリガー

-- 他テーブルの何らかの更新をトリガーに別テーブルに記録を残す
DROP TRIGGER IF EXISTS トリガー名; --　エラー防止
CREATE TABLE -- テーブルA

CREATE TABLE logs (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  created DATETIME DEFAULT NOW(),
  PRIMARY KEY (id)
);

CREATE TRIGGER
  トリガー名
AFTER UPDATE ON -- BEFOREにすると処理前にトリガー実行可
  テーブルA
FOR EACH ROW
  INSERT INTO
    logs (message)
  VALUES
    (値);
    (CONCAT(OLD.message, ' -> ', NEW.message)); 
     -- トリガー専用 OLD:更新前の値　NEW:更新後の値

SHOW TRIGGERS;  -- トリガーの確認 
SHOW TRIGGERS\G -- 見やすく変更

別ファイル読み込み

LOAD DATA LOCAL INFILE 'ファイル名.csv' INTO TABLE posts
  FIELDS TERMINATED BY ',' -- 項目の区切りを指定: ,　の場合
  LINES TERMINATED BY '\n' -- 行の区切りを指定： 改行 の場合
  IGNORE 1 LINES   -- スキップするレコード : 見出し行をスキップする場合
  (カラムA,カラムB、カラムC);

インデックス（索引）

検索されるカラムに対して、調べやすくするためにデータベースとは別につけるもの
id(PRIMARYキー）に関しては、自動的に生成されるため、高速に動作する

• メリット：効率的な検索
• デメリット：データ変更時に再構築が必要、データ量が増える

データ量が増えて、パーフォーマンスが低下
↓
EXPLAINで調査
↓
インデックスを設定

-- インデックスの確認
SHOW INDEX FROM テーブル名;
SHOW INDEX FROM テーブル名\G  -- 見やすい

-- クエリで使用されているインデックスの確認例
EXPLAIN SELECT * FROM テーブル名　WHERE id = 10\G
-- key:インデックスの名前  PRIMARYキーなど(NULLの場合存在していない）
-- rows:検索対象となるレコード数の見積もり(少ないほど良い) 

-- インデックスの追加
ALTER TABLE テーブル名 ADD INDEX インデックス名(対象カラム名）； -- つけ外しが多いためALTER

-- インデックスの削除
ALTER TABLE テーブル名 DROP INDEX インデックス名;

DML構文例

構文

SELECT 
   DISTINCT -- 重複除く...など特定オプション
   カラム名,
   IF,CASE,COUNT(),SUBSTRING(),LENGTH()...  -- 条件分岐,関数など
   (SELECT AVG(カラム名） FROM テーブル名) -- サブクエリ:レコードごとに平均をだしたい時など
   AS  -- リネーム
FROM テーブルA
 JOIN テーブルB　ON　〜〜〜　 
 WHERE 条件 -- AND OR IN LIKE ...
    GROUP BY カラム名（グループ分け）
    ORDER BY 並び替え　--LIMIT OFFSET　
      HAVING 追加条件
   AS  -- リネーム

TCL(トランザクション制御）

あるレコードから別のレコードに値を移すといったデータ修正時、障害や外部要因によって、エラーやデータの整合性がとれなくなることを防ぐため、一連の処理をしている間、他の処理を受け付けなくする仕組み

例

START TRANSACTION; -- 処理スタート
UPDATE テーブル名 SET カラムA = カラムA - 1 WHERE id = 1;
UPDATE テーブル名 SET カラムA = カラムA + 1 WHERE id = 2;
COMMIT; -- 処理終了：一連の流れの間、他処理受け付けず

ROLLBACK; -- 途中の処理がうまくいかなかった場合、処理を戻す
-- 障害内容は他言語との組み合わせ

SELECT * FROM テーブル名;