【SQL】SQLの基本を覚える

#概要
「この記事を見ればSQLで困ったことが（だいたい）全て解決する！」っていう記事をまとめます。
備忘録の意味も込めてその内容をまとめます。

#SQLとは
データベースに格納されている情報は外部からのアクセスによって検索したり、書き換えたりすることが可能となっております。
そのために使用するのが専用の操作言語であるSQLを利用します。

#データベースとは
たくさんのデータが入ったPCやシステムを思い浮かべるかもしれません。
しかし、狭い意味で、検索や置き換え、分析などのデータ管理を目的にした様々な情報を蓄積したものを指します。

その代表となるものが、RDB(リレーショナルデータベース)です。

#RDBとは
複数の表の形式でデータを管理するものです。
その個々の表をテーブルと言います。

テーブルは列と行で構成されます。
このRDBは以下の図のような構造のデータを含んだただのファイルです。

しかし、実際はファイルに対してSQL文を実行しても検索や置き換えはできません。

そこでDBMSが仲介役をおこないます。

#DBMS(データベースマネジメントシステム)とは
DBMSはコンピュータ内で常に稼働してSQLを待ち受けています。
届いたSQLの内容にしたがって、データベースのファイルの内容を検索したり、書き換えたりする処理を実行します。

#SQL命令の種類
全てのSQL文は以下の4種類の命令に分類することができます

命令の種類	説明
DML	データ操作言語(データの格納や取り出し、更新、削除などの命令を実行)
TCL	トランザクション制御言語(トランザクションの開始や終了の命令を実行)
DDL	データ定義言語(テーブルなどの作成や削除、各種設定などの命令を実行)
DCL	データ制御言語(DMLやDDLの利用に関する許可や禁止を設定する命令を実行)

#DMLとは
データ操作言語といい代表的なものが以下の4つになります。

##SELECT文

特定のテーブルから条件を指定して目的のデータを取得する命令です。

###全ての列を検索
以下の例では家計簿テーブルの全ての列を取得した結果を表します。

//パターン①
SELECT * FROM 家計簿;

//パターン②
SELECT 日付,費目,メモ,入金額,出金額 FROM 家計簿;

##INSERT文

テーブルにデータを追加する際の命令です。

以下のような命令でデータを追加することができます。

INSERT INTO 家計簿 VALUES('2018-02-04','移住費','3月の家賃',0,70000);

##UPDATE文

データを更新する際の命令です。

以下のような命令でデータを更新することができます。
移住費が90000に更新されます。

UPDATE 家計簿 SET 出金額=90000 WHERE 費目='移住費';

##DELETE文

データを削除する際の命令です。

以下のような命令でデータを削除することができます。
先ほど追加した移住費の行が削除されます。

DELETE FROM 家計簿 WHERE 費目='移住費';

#SQLでのコメント
SQL文を複数実行しているとどの構文が何を実行するのか分からなくなる時があるかと思います。
この場合、コメントをつける方法としては/*　*/を使います。

/*家計簿テーブルを検索*/

SELECT * FROM 家計簿;

##ASによる別名定義

SELECT文において列名やテーブル名の指定で、それぞれの記述の後ろに「AS+任意のキーワード」をつけることで別名を定義することができます。

以下のような命令で任意のキーワードで検索表示させることができます。

SELECT 費目 AS item ,入金額 as 振り込み額 FROM 家計簿 as データ WHERE 費目='給料';

##INSERT文で列名を指定して値を追加

以下のような命令で指定した列にだけ値を入れてデータを追加することができます。
メモには値が入らない状態になります。

INSERT INTO 家計簿 (日付,費目,入金額,出金額) VALUES('2019-02-01','電気代',0,2500)

##4大命令の整理

命令の種類	説明
検索系	SELECT
更新系	UPDATE,DELETE,INSERT
既存系	SELECT,UPDATE,DELETE
新規系	UPDATE

上記全てはテーブル名を指定する必要があります。

#NULLの判定
テーブルの中のデータはNULLという何も示さないことを意味するものになることがあります。
それを表すのがNULLとなります。

####NULLとは
・そこに何も格納されていない、未定義であることを表す。
・数字の０や空白文字とも異なります。

以下のような命令文でNULLであることまたはNULLでないことの判定をしてくれます。

/* NULLであることの判定 */
SELECT * FROM 家計簿 WHERE 出金額 IS NULL;
/* NULLでないことの判定 */
SELECT * FROM 家計簿 WHERE 出金額 IS NOT NULL;

#LIKE演算子
文字列があるパターンに合致しているかをチェックする際に使用します。
以下のような命令文で「1月」という文字列を含む行を取り出します。

SELECT * FROM 家計簿 WHERE メモ LIKE '%1月%'

%1月は1月という文字列で終わる文字列、1月%は1月という文字列で始まることを意味します。

#BETWEEN演算子
ある範囲内に値が収まっているかを判定します。
以下のような命令文で出金額が0円以上~3000円以下の範囲にある行を検索します。

SELECT * FROM 家計簿 WHERE 出金額 BETWEEN 0 AND 3000;

#IN / NOT IN演算子
INの中に指定した列に合致するものが１つでもあるかどうかを判定します。

SELECT * FROM 家計簿 WHERE 費目 IN('食費','交際費')

以下の文の場合は、食費でも交際費でもない行を取得します。

SELECT * FROM 家計簿 WHERE 費目 NOT IN('食費','交際費')

#AND/OR/NOT演算子
優先順位としては

1. NOT
2. AND
3. OR

以下の命令文を実行した場合、費目が食費か給料でメモがコーヒーを購入か1月の給料だった場合の結果を表示します。

SELECT * FROM 家計簿 WHERE (費目='食費' OR 費目='給料') AND (メモ='コーヒーを購入' OR メモ='1月の給料');

#DISTINCT
重複行を削除します。
以下の命令文で入金額が重複している行を除外します。

SELECT DISTINCT 入金額 FROM 家計簿;

#ORDER BY
並び替えを行います。

昇順に並び替える場合

/* ASCは省略可能　*/
SELECT * FROM 家計簿 ORDER BY 出金額;

降順に並び替える場合

SELECT * FROM 家計簿 ORDER BY 出金額 DESC;

##複数の列で並び替えの場合

SELECT * FROM 家計簿 ORDER BY 入金額 DESC,出金額;

SELECT * FROM 家計簿 ORDER BY 4 DESC,5;

#OFSET -FETCH
検索結果の全部ではなく、並び替えた結果の一部の行だけを取得したい場合に使います。
以下の命令文では出金額の高い順に3件を取得します。

SELECT 費目,出金額 FROM 家計簿 ORDER BY 出金額 DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY

以下の命令文では、3番目に高い出金額だけを取得します。

SELECT 費目,出金額 FROM 家計簿 ORDER BY 出金額 DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

#LIMIT
取得するデータの行数の上限を設定することができます。

以下の命令文では、3件取得します。

SELECT 費目,出金額 FROM 家計簿 ORDER BY 出金額 DESC LIMIT 3

#UNION
2つの検索結果を足し合わせたものを取得します。

以下の命令分で家計簿テーブルと家計簿アーカイブテーブルの費目、出金額の結果を合わせて表示し、それを出金額の昇順,費目の昇順の順に並び替えています。

SELECT 費目,出金額 FROM 家計簿 UNION 
SELECT 費目,出金額 FROM 家計簿アーカイブ ORDER BY 2,1

#EXCEPT
ある集合と別の集合の差を求める。
以下のような命令文で差を求めることができます。

SELECT 費目 FROM 家計簿 EXCEPT
SELECT 費目 FROM 家計簿アーカイブ;

#INTERSECT
和集合を求めます。
２つのSELECT文に共通する行を集めた集合です。
以下のような命令文で家計簿テーブルと家計簿アーカイブテーブルに共通している費目を出力します。

SELECT 費目 FROM 家計簿 INTERSECT SELECT 費目 FROM 家計簿アーカイブ

#CASE演算子
SQLには+以外にも値の計算に用いる算術演算子として-や||(文字列連結)があります。

case 評価する列や式 WHEN 値1 THEN 値1の時に返す値 
     (WHEN 値2 THEN 値2の時に返す値)
     (ELSE デフォルト値)
END

SELECT 費目,出金額,
       CASE 費目 WHEN '移住費' THEN '固定費'
                WHEN '水道光熱費' THEN '固定費'
                ELSE '変動費'
       END AS 出費の分類
FROM 家計簿 WHERE 出金額>0

上記のような結果になります。
これは移住費があれば、固定費。水道光熱費があれば固定費、最後に変動費を返すという意味になります。

また、以下のように列名や式を記述せずにSELECTすることもできます。

SELECT 費目,入金額,
       CASE WHEN 入金額 < 5000 THEN 'お小遣い'
　　　　　　　WHEN 入金額 < 10000 THEN '一時収入'
            WHEN 入金額 < 30000 THEN '給料でたー'
            ELSE '想定外の収入です！'
       END AS 出費の分類
FROM 家計簿 WHERE 入金額>0

#LENGTH関数

以下の命令文を実行することでメモの長さを取得することができます。

SELECT メモ,LENGTH(メモ) AS メモの長さ FROM 家計簿;

#TRIM関数

以下の命令文を実行することで空白を取り除いた結果を返します。

SELECT メモ,TRIM(メモ) AS 空白除去したメモ FROM 家計簿

#SUBSTRING関数
文字列の一部を抽出する関数です。
SUBSTRING(文字列を表す列,抽出を開始する位置,抽出する文字の数)
=>抽出された文字列

こちらも文字列の一部を抽出する関数です。
SUBSTR(文字列を表す列,抽出を開始する位置,抽出する文字の数)
=>抽出された文字列

#REPLACE関数
文字列を置き換えします。

以下のような条件に当てはめて出力します。

REPLACE(置換対象の文字列,置換前の部分文字列,置換後の部分文字列)

以下の命令文ならば、メモの列に入っている購入を買ったに置換えます。

UPDATE 家計簿 SET メモ=REPLACE(メモ,'購入','買った')

#CONCAT関数
文字列を連結します。

CONCAT(文字列,文字列[,文字列])

以下の命令文で費目とメモを連結します。

SELECT CONCAT(費目,':' || メモ) FROM 家計簿;

#ROUND関数

指定桁で四捨五入します。
ROUND(数値を表す列,有効とする桁数)
=>四捨五入した値

以下のような命令文で出金額の下二桁目、つまり10の位で四捨五入されることになります。

SELECT 出金額,ROUND(出金額,-2) AS 百円単位の出金額 FROM 家計簿

#TRUNC
指定桁で切り捨てる

TRUNCT(数値を表す列,有効となる桁数)
=>切り捨てた値

#POWER
べき乗を計算する

POWER(数値を表す列,何乗するかを指定する数値)

POWER(出金額,3)としたら出金額を3乗した値を得ることができます。

#CURRENT_DATE
現在の日付を得る関数です。
以下の命令文で日付は現在の日付を追加できます。

INSERT INTO 家計簿 VALUES(CURRENT_DATE,'食費','ドーナッツを買った',0,260)

#CAST
データ型を変換します。

CAST(変換する値 AS 変換する型) =>変換後の値 となります。

#COALESCE
NULLではない値を返す関数になります。

COALESCE(列や式1,列や式2,列や式3)
=>引数のうち、最初に現れたNULLではない引数。

SELECT COALESCE('A','B','C'); /* 結果は'A'*/
SELECT COALESCE(NULL,'B','C'); /* 結果は'B'*/
SELECT COALESCE(NULL,'B',NULL); /* 結果は'B'*/
SELECT COALESCE(数値型の列,0); /*数値型の列が出力されます。*/

SELECT 日付,費目, COALESCE(メモ,'(メモはNULLです)') AS メモ、入金額,出金額 FROM 家計簿

上記の構文でメモがもしNULLの場合は、「メモはNULLです」と出力されます。

#集計関数
今月いくらお金を使ったか等金額の合計を調べたい時などに使う関数です。

以下の命令文を実行することで、出金額の合計を出力できます。
他にもAVG、MAXなどあります。

SELECT SUM(出金額) AS 出金額の合計 FROM 家計簿

#COUNT関数
検索結果の行数を数えてくれます。

SELECT COUNT(*) AS 食費の行数 FROM 家計簿 WHERE 費目='食費'

COUNT(*)はNULLも含めますが、COUNT(列)はNULLである行は無視して計算します。

#データをグループに分ける
SQLには検索結果をいくつかのグループにわけるグループ化という機能が備わっています。

以下の命令文で費目でグループ化してそれぞれの合計を求めます。

SELECT 費目,SUM(出金額) AS 出金額の合計 FROM 家計簿 GROUP BY 費目

グループ化の構文は以下のようになっています。

SELECT グループ化の基準列名...,集計関数 FROM テーブル名 (WHERE 絞り込み条件) GROUP BY グループ化の基準列名;

#HAVING
集計処理を行ったあとの結果表に対して絞り込みを行いたい場合は、WHERE句ではなく、HAVINGを使います。

SELECT グループ化の基準列名...,集計関数 FROM テーブル名 (WHERE 絞り込み条件) GROUP BY グループ化の基準列名 HAVING 集計結果に対する絞り込み条件;

SELECT 費目,SUM(出金額) AS 出金額の合計 FROM 家計簿 GROUP BY 費目 HAVING SUM(出金額)>0

上記のようなSQL文を実行することで以下のような結果を得ることができます。

#副問い合わせ
SELECT文を2回使って検索結果を出力する方法もありますが、以下の例の方が効率的です。
以下の命令文で出金額が最大の費目を得ることができます。最初に()内のSELECT文が実行されます。

SELECT 費目,出金額 FROM 家計簿 WHERE 出金額=(SELECT MAX(出金額) FROM 家計簿)

仮に2回で分けて実行すると、書き留めた額のところで手が止まるかと思います。

SELECT MAX(出金額) FROM 家計簿;
SELECT 費目,出金額 FROM 家計簿 WHERE 出金額=【書き留めた額】

副問い合わせには３つのパターンがあります。

単一の値に化ける問い合わせ、列挙された複数値に化ける副問い合わせ、表形式の複数値に化ける副問い合わせ

###単一問い合わせ
検索結果が一行一列の１つの値となる副問い合わせを指します。以下のような実行文です。

SELECT 費目,出金額 FROM 家計簿 WHERE 出金額=(SELECT MAX(出金額) FROM 家計簿)

SELECT 日付,メモ,出金額,(SELECT 合計 FROM 家計簿集計 WHERE 費目='食費') AS 過去の合計額 FROM 家計簿アーカイブ WHERE 費目='食費'

###複数行副問い合わせ
検索結果がn行1列の複数の値となる副問い合わせです。

SELECT * FROM 家計簿集計 WHERE 費目 IN(SELECT DISTINCT 費目 FROM 家計簿)

ANY/ALL演算子
ANYはどれかALLは全てと覚えれば良いと思います。

SELECT 費目,出金額 FROM 家計簿 WHERE 費目='食費' AND 出金額 < ANY(SELECT 出金額 FROM 家計簿アーカイブ WHERE 費目='食費')

NOT INと<>ALLは全ての値と一致しないことを判定する演算子で同じ意味になります。

#表形式の副問い合わせ
検索結果がn行m列の表となる副問い合わせです。
()内は全て副問い合わせです。

SELECT SUM(SUB.出金額) AS 出金額の合計 FROM (SELECT 日付,費目,出金額 FROM 家計簿 UNION SELECT 日付,費目,出金額 FROM 家計簿アーカイブ WHERE 日付>='2018-01-01' AND 日付 <='2019-01-31') AS SUB

#JOIN
外部キーとのリレーションシップを利用する際に使われます。

そこでテーブルの結合を利用したJOIN句を利用します。

SELECT 選択列リスト FROM テーブルA JOIN テーブルB ON 両テーブルの結合条件

例えば以下の命令文を実行すると家計簿の費目IDと費目テーブルのIDを結びつけた結果を表示します。

SELECT 日付,名前 AS 費目,メモ FROM 家計簿 JOIN 費目 ON 家計簿.費目ID = 費目.ID

#LEFT JOIN
左外部結合です。これは左表については結合相手が見つからなくても、NULLであっても必ず出力せよという意味になります。

SELECT ~ FROM 左表の名前 LEFT JOIN 右表の名前 ON　結合条件

#RIGHT JOIN
右外部結合です。これは右表については結合相手が見つからなくても、NULLであっても必ず出力せよという意味になります。

SELECT ~ FROM 左表の名前 RIGHT JOIN 右表の名前 ON　結合条件

#FULL JOIN
完全外部結合です。左、右表の全行を必ず出力します。

SELECT ~ FROM 左表の名前 FULL JOIN 右表の名前 ON　結合条件

#FULL JOINの代わりに
MySQLやMariaDBではFULL JOINを使えません。
その代わりとして、UNIONを使います。

SELECT 選択列リスト FROM 左表の名前 LEFT JOIN 右表の名前 ON 左表の結合条件列=右表の結合条件列 UNION SELECT 選択列リスト FROM 左表の名前 RIGHT JOIN 右表の名前 ON 左表の結合条件列 = 右側の結合条件列

#テーブル名を指定してJOINする場合
２つのテーブルを結合すると同じ列名の場合、どっちのテーブルの列名だっけ?となることが出てきてしまいます。
その際にテーブル名を指定してJOINします。

SELECT 日付,家計簿.メモ,費目.メモ FROM 家計簿 JOIN 費目 ON 家計簿.費目ID=費目.ID;

#別名を使ったJOIN
テーブル名を別名を使って表すこともできます。

SELECT 日付,K.メモ,H.メモ FROM 家計簿 AS K JOIN 費目 AS H ON K.費目ID=H.ID;

#副問い合わせの結果と結合するJOIN

SELECT 日付,費目.名前,費目.経費区分ID FROM 家計簿 JOIN (SELECT * FROM 費目 WHERE 経費区分ID=1) AS 費目 ON 家計簿.費目ID=費目.ID;

#トランザクション
DBMSに対して複数のSQL文を送る際に、１つ以上のSQL文をひとかたまりとして扱うよう指示することができます。
このかたまりをトランザクションといいます。

トランザクションを指示するために使うものは以下のようなものになります。

・BIGINは開始の指示。
・COMMITは終了の指示で変更を確定します。
・ROLLBACKは終了の指示で変更の取り消しをします。

BEGIN;
INSERT INTO 家計簿アーカイブ;
SELECT * FROM 家計簿 WHERE 日付<'2018-01-31';
DELETE FROM 家計簿 WHERE 日付<='2018-01-31';
COMMIT;

#DCLについて
DCLは誰に、どのようなデータ操作やテーブルを操作を許すかと言った権限を設定するためのSQL命令の総称です。権限を付与するのがGRANT、権限を剥奪するのがREVOKEです。

GRANT 権限名 TO ユーザ名
REVOKE 権限名 FROM ユーザ名

上記を実行できるのはデータベース管理者の立場の人だけが使う命令です。

#テーブルの作成

基本的な構文は以下になります。

CREATE TABLE テーブル名(
列名1 列1の型名,
列名2 列2の型名
)

以下の場合、家計簿テーブルを作成します。

CREATE TABLE 家計簿(
日付 DATE,
費目ID INTEGER,
メモ VARCHAR(100),
入金額 INTEGER,
出金額 INTEGER
)

#テーブル作成時にデフォルト値を設定
以下のような実行文でメモ、入金額、出金額にデフォルト値を設定できます。
また、NOT NULL制約でNULLを許可しない、UNIQUE制約で重複を防ぐ、CHECK制約である列に格納される値が妥当であるかを細かく判定できます。

CREATE TABLES 家計簿(
日付 DATE NOT NULL,
費目ID INTEGER ,
メモ VARCHAR(100) DEFAULT '不明',
入金額 INTEGER DEFAULT 0 CHECK(入金額>=0),
出金額 INTEGER DEFAULT 0 CHECK(出金額>=0)
)

#テーブルの削除

DROP TABLE テーブル名

#ALTER TABLE文
テーブル定義の内容を変更するにはALTER TABLE文を使います。

//列の追加
ALTER TABLE テーブル名 ADD 型名 型 制約
//カラムの変更
ALTER TABLE [table_name] MODIFY COLUMN [column_name] [column_definition];
//列の削除
ALTER TABLE テーブル名 DROP 型名 型 制約

#主キーの制約
以下のように指定することで主キー制約をつけれます。

CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE
)

また、以下のように記載して複合主キーを構成することもできます。

CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE,
PRIMARY KEY(ID,名前)
)

#外部キー制約
参照整合性(外部キーが指し示す先にきちんと行が存在してリレーションシップが成立していること)が崩壊しないようにする役割があります。

その際は以下のようにREFERENCESを使います。

CREATE TABLE テーブル名(
列名 型 REFERENCES 参照先テーブル名(参照先列名)
)

CREATE TABLE テーブル名(
FOREIGN KEY(参照元列名) REFERENCES 参照先テーブル名(参照先列名)
)

CREATE TABLES 家計簿(
日付 DATE NOT NULL,
費目ID INTEGER REFERENCES 費目(ID),
メモ VARCHAR(100) DEFAULT '不明' NOT NULL,
入金額 INTEGER DEFAULT 0 CHECK(入金額>=0),
出金額 INTEGER DEFAULT 0 CHECK(出金額>=0)
)

#AUTO_INCREMENT
データが追加される際に自動的に連番が振られます。
MySQLとMariaDBのみ。