スッキリわかる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月;