スッキリわかるSQL入門を読んで、SQLの知見を高めたので備忘録として残す
SQLを考えるときの順序として、FROM句→WHERE句→GROUP BY句→SELECT句の順番で考える。まず、必要なテーブルを考える。
SELECT カラム名
FROM テーブル名
WHERE 制限
☆CAST（文字変換）
CAST（値 AS 変換後のデータ型,nullの場合文字）
CAST(残高 AS VARCHAR(10),’XXXX')
☆SUBSTRING（文字の切り取り）
SUBSTRING([文字列], [切り取り開始地点], [切り取る文字数]);
☆COALESCE（nullの置き換え）
SELECT COALESCE(カラム名, NULLだった場合の文字列)
☆出力
||で文字列を連結
SELECT 口座番号, 'カ' || 名義
FROM 口座
WHERE 種別=‘3'
文字列の一部を取得する
SELECT 名義
FROM 口座
WHERE SUBSTRING(名義, 1, 5) LIKE '%カワ%'
☆データ追加
INSERT INTO テーブル名
VALUES ('2014-02-15', ‘居住費', '3月の家賃', 0, 85000)
INSERT INTO 家計簿（出金額）
VALUES （1000 + 105）
※SELECT文から入れるときはVALUEはいらない
INSERT INTO 家計簿集計（費目, 合計, 平均, 回数）F
SELECT 費目, SUM（出金額）, AVG（出金額）, 0
FROM 家計簿
WHERE 出金額 > 0 (WHERE 費目 LIKE ‘%月%’)
GROUP BY 費目
☆LIKE句
% :0文字以上の任意の文字列
_ :任意の１文字
例
SELECT * FROM WHERE name LIKE '%A';
例:名義の姓が「エ」から始まる3文字で、名が「コ」で終わる
SELECT * FROM 口座 WHERE 名義 LIKE 'エ__%コ'
☆アップデート
UPDATE テーブル名前
SET 出金額=90000, 残金=80000
WHERE 日付='2013-02-25'
UPDATE 家計簿
SET 出勤簿 = 出金額 + 100
☆削除
DELETE FROM 家計簿
WHERE 日付='2013-02-25'
☆比較演算子
<> 左右の値が等しくない
select * from 気象情報 where 月<>6 （6月以外のデータが欲しい）
☆NULLの判定方法（比較演算子はNULLに使えない）
SELECT *
FROM 家計簿
WHERE 出金簿 IS NULL
☆BETWEEN演算子
SELECT *
FROM 家計簿
WHERE 出金簿 BETWEEN 100 AND 3000
☆IN/NOT IN演算子（一度にたくさんの値との比較が可能）
SELECT *
FROM 家計簿
WHERE 費目 IN (‘食費’, ‘交際費’)
☆複数の論理演算子を使ったSELECT文
SELECT * FROM 湊くんの買い物リスト
WHERE ( 販売店 = ‘A’
OR 販売店 = ‘B’
AND (カテゴリ = ‘ゲーム’
OR カテゴリ = ‘DVD’)
```
☆DISTINCT(内容が重複してある行があれば、重複を取り除く、データの種類を取得したい場合に役立つ)
SELECT DISTINCT 入金額 FROM 家計簿
```
☆ORDER BY（並びかえ、複数ある場合は最初に指定された列を並び替え、同じ値が複数行あれば、次に指定された列で並び替え）
SELECT * FROM 家計簿
ORDER BY 入金額 DESC, 出金額 DESC
☆LIMIT（先頭から数行だけ取得する, OFFSET先頭から除外する行数）
SELECT 費目, 出金額 FROM 家計簿
ORDER BY 出金額 DESC LIMIT 3 OFFSET
☆UNIONN（検索結果を足し合わせた結果を返す）
SELECT 費目, 入金額, 出金額 FROM 家計簿
UNION
SELECT 費目, 入金額, 出金額 FROM 家計簿アーカイブ
ORDER BY 2,3,1
※集合演算子でORDER BY 句を使うときの注意点
・ORDER BY 句は最後のSELECT文に記述する
・列番号以外による指定（列名やASによる別名）の場合、１つめのSELECT文のものを指定する。
・列番号は結果出力後の前からのカラムの順
☆EXCEPT（ある集合と別の集合の差をとる）
SELECT 費目 FROM 家計簿
EXCEPT
SELECT 費目 FROM 家計簿アーカイブ
☆INTERSECT（2つのSELECT文に共通する行を集めた集合）
SELECT 費目 FROM 家計簿
INTERSECT
SELECT 費目 FROM 家計簿アーカイブ
☆選択列リストへの様々な指定
SELECT 出金額 -- 列名での指定
出金額 + 100 AS 百円増しの出金額 -- 計算式での指定
‘SQL’ -- 固定値での指定
FROM 家計簿
☆CASE演算子（列の値や条件式を評価し、その結果に応じて好きな値に変換）
SELECT 費目, 出金額,
CASE 費目 WHEN ‘居住費’ THEN ‘固定費’
WHEN ‘水道光熱費’ THEN ‘固定費’
ELSE ‘変動費’
END AS 出費の分類
FROM 家計簿 WHERE 出金額 > 0
☆さまざまな集計
SELECT
SUM（出金額） AS 合計出金額,
AVG（出金額） AS 平均出金額,
MAX（出金額） AS 最も大きな散財,
SUM（出金額） AS 最も少額の支払い
FROM 家計簿
＊NULLは基本無視
NULLを０として平均を求める場合
SELECT AVG （COALESCE（出金額, 0）） AS 出金額の平均
FROM 家計簿
☆食費の行数を数える SELECT COUNT
SELECT COUNT（*） AS 食費の行数
FROM 家計簿
WHERE 費目 = '食費'
＊COUNT（*）とCOUNT（列）の違い
・COUNT（*）は、単純に行数をカウントする（NULLの行も含める）。
・COUNT（列）は、指定列の値がNULLである行を無視してカウントする。
☆費目でグループ化してそれぞれの合計を求める
SELECT 費目, SUM（出金額） AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目
HAVING SUM（出金額） > 0
＊HAVING COUNT(*) > 10
＊WHERE句は使えない。
☆SELECT文の全貌
SELECT 選択列リスト
FROM テーブル名
（WHERE 条件式）
（GROUP BY グループ化列名）
（HAVING 集計結果に対する条件式）
（ORDER BY 並び変え列名）
＊ORDER BYは最後
副問い合わせ(サブクエリ)
☆単一副問い合わせ
SELECT 費目, 出金額 FROM 家計簿
WHERE 出金額 = (SELECT MAX(出金額) FROM 家計簿)
☆SELECT文で作ったテーブルから取り出す
SELECT MAX(商品数)
FROM (
SELECT 支社コード, 支店コード, COUNT(*) AS 商品数
FROM 支店 INNER JOIN 支店商品
ON 支店.支店
)
☆複数行問い合わせ
SELECT * FROM 家計簿集計
WHERE 費目 IN (SELECT DISTINCT 費目 FROM 家計簿)
☆副問い合わせからNULLを除外する
※NOT IN または <>ALLで判定する副問い合わせの結果にNULLが含まれると、全体の結果もNULLとなる
SELECT * FROM 家計簿アーカイブ
WHERE 費目 IN (SELECT 費目 FROM 家計簿
WHERE 費目 IS NOT NULL)
SELECT * FROM 家計簿アーカイブ
WHERE 費目 IN (SELECT COALESCE(費目, '不明’) FROM 家計簿)
☆テーブルAとテーブルBの結合
SELECT 選択別リスト
FROM テーブルA
JOIN テーブルB
ON 両テーブルの結合条件
SELECT 日付, 名前 AS 費目, メモ
FROM 家計簿
JOIN 費目
ON 家計簿.費目ID = 費目.ID
☆その他の外部結合（通常はNULLがある場合、出力されない）
SELECT ~ FROM 左表の名前
LEFT JOIN 右表の名前
ON 結合条件
※左の表を右の情報がなくても出力
SELECT ~ FROM 左表の名前
RIGHT JOIN 右表の名前
ON 結合条件
※右の表を左の情報がなくても出力
SELECT ~ FROM 左表の名前
FULL JOIN 右表の名前
ON 結合条件
※結合相手の行がない場合や左表の結合条件列がNULLの場合、選択列リストに抽出される右表の列は全てNULLとなる。
☆2種類のメモを両方出力するSQL文
SELECT 日付, 家計簿.メモ, 費目.メモ
FROM 家計簿
JOIN 費目
ON 家計簿.費目ID = 費目.ID
※列名指定の前に『テーブル名.』という表記を加え、どのテーブルに属する列であるか明示的に指定できる。
SELECT 日付, K.メモ, H.メモ
FROM 家計簿 AS K
JOIN 費目 AS H
ON 家計簿.費目ID = 費目.ID
☆3つのテーブルを結合するSQL文
SELECT 日付, 費目.名前, 経費区分.名称
FROM 家計簿
JOIN 費目
ON 家計簿.費目ID = 費目.ID
JOIN 経費区分
ON 費目.経費区分ID = 経費区分.ID
※前から順に1つずつ結合処理が行われる。
☆自分自身と結合するSQL文
SELECT A.日付, A.メモ, A.関連日付, B.メモ
FROM 家計簿 AS A
LEFT JOIN 家計簿 AS B
ON A.関連日付 = B.日付
☆テーブル作成
CREATE TABLE 家計簿(
日付 DATE,
費目ID INTEGER,
メモ VARCHAR(100) DEFAULT ‘不明’ NOT NULL,
入金額 INTEGER DEFAULT 0 CHECK(入金額 >= 0),
出金額 INTEGER DEFAULT 0 CHECK(出金額 >= 0)
);
CREATE TABLE 費目 (
ID INTEGER,
名前 VARCHAR(40) UNIQUE
);
主キー制約の指定1
CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE
)
主キー制約の指定2
CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE,
PRIMARY KEY(ID, 名前)
)
主キー制約の指定3
CREATE TABLE 家計簿(
日付 DATE NOT NULL,
費目 ID INTEGER PREFERENCES 費目(ID),
メモ VARCHAR(100) DEFAULT '不明' NOT NULL,
入金額 INTEGER DEFAULT '不明' CHECK(入金額 >= 0),
出金額 INTEGER DEFAULT '不明' CHECK(出金額 >= 0),
☆テーブル削除
DROP TABLE テーブル名
☆テーブル定義の変更
追加するとき
ALTER TABLE 家計簿 ADD 関連日 DATE
削除するとき
ALTER TABLE 家計簿 DROP 関連日
☆インデックスの作成
CREATE INDEX インデックス名前 ON テーブル名(列名);
CREATE INDEX メモインデックス ON 家計簿(メモ);
CREATE INDEX 費目IDインデックス ON 家計簿(費目ID);
☆インデックスの削除
DROP INDEX インデックス名
DROP INDEX インデックス名 ON テーブル名 —MySQLの場合
☆ビュー（共通しているSELECT文を省略できる）
CREATE VIEW ビュー名 AS SELECT文
DROP VIEW ビュー名
CREATE VIEW 家計簿4月 AS
SELECT * FROM 家計簿
WHERE 日付 >= '2013-04-01'
AND 日付 <= ‘2013-04-30’
SELECT * FROM 家計簿;
SELECT DISTINCT 費目 ID FROM 家計簿4月;