Cacher is the code snippet organizer for pro developers

メモ

• SQLは大文字と小文字を区別しない
• 縦がカラムで横がレコード、それをまとめる表がテーブル
• SELECTとFROMで必要なデータの表を組んでWHEREで絞り込む感じ
• 実行の順番は
  １）テーブルの指定
  ２）結合（ON・JOIN
  ３）取得条件（WHERE
  ４）グループ化（GROUP BY
  ５）関数（SUM、MIN、COUNTなど）
  ６）HAVING
  ７）検索（SELECT・DISTINCT
  ８）順序（ORDER BY
  ９）LIMIT

SELECT name // nameカラムを
FROM purchases  // purchasesテーブルから
WHERE purchased_at = "2017-07-01" // purchased_atが2017-07-01であるレコードを絞り込み
ORDER BY purchased_at DESC  // purchased_at基準で降順に並べ替え
LIMIT 10  // 10件だけ取得
;

• 複数のカラムを選択するには,（カンマ）区切りで

SELECT name, id FROM purchases;

• すべてのカラムを選択するには*

SELECT * FROM purchases;

• 数値データはクォーテーションで囲まない
• 日付データはクォーテーションで囲む
• 日付データにも演算子が使える

// 2017-08-01以前のデータを
FROM purchases WHERE purchased_at <= "2017-08-01";

• XXを含むはLIKE、ワイルドカードには%を使う(LIKEは＝の位置

//  "プリン"を含むものすべて（前方後方一致
SELECT * FROM purchases WHERE name LIKE "%プリン%";

• 含まないデータはNOTEを使う

SELECT * FROM purchases WHERE NOT name LIKE "%プリン%";

• 特定のレコードが空のデータ

//  priceが空のデータ
SELECT * FROM purchases WHERE price IS NULL;

//  priceが空じゃないデータ
SELECT * FROM purchases WHERE price IS NOT NULL;

• 複数のレコードを条件に

SELECT * FROM purchases WHERE category = "食費" AND character_name = "ひつじ仙人";
SELECT * FROM purchases WHERE category = "食費" OR character_name = "にんじゃわんこ";

• 昇順、降順で並べ替え

//  ASCで昇順（小->大）、DESCで降順（大->小）
SELECT * FROM purchases ORDER BY price DESC;

• 取得件数制限（LIMITは末尾に

SELECT *
FROM purchases WHERE character_name = "にんじゃわんこ" LIMIT 10;

• カラム内で重複を除く

SELECT DISTINCT(name) FROM purchases;

• 演算・関数

//  四則演算
SELECT name, price, price * 1.08 FROM purchases;

//  合計
SELECT SUM(price) FROM purchases WHERE character_name = "にんじゃわんこ";

//  平均
SELECT AVG(price) FROM purchases WHERE character_name = "にんじゃわんこ";

//  カウント（NULLは含まない、指定カラムが*だとNULLを含んだ数
SELECT COUNT(name) FROM purchases WHERE character_name = "にんじゃわんこ";

//  最小、最大 MIN()で最小
SELECT MAX(price) FROM purchases WHERE character_name = "にんじゃわんこ";

// グループ化：カラムの要素ごとの集計
//  （必ずSUMやCOUNTなど集計したカラムとグループ化する基準のカラムの指定が必要
SELECT COUNT(*), purchased_at FROM purchases GROUP BY purchased_at;

//  複数のカラムを指定してグループ化
SELECT COUNT(*), purchased_at, character_name FROM purchases WHERE category = "食費" GROUP BY purchased_at, character_name;

// グループ化後の絞り込みはHAVING
SELECT SUM(price), purchased_at, character_name FROM purchases GROUP BY purchased_at, character_name HAVING SUM(price) > 3000;

• WHEREはグループ化される前テーブル全体が検索対象、HAVINGはグループ化されたデータが検索対象
• サブクエリ()の後には;はいらない

SELECT name,goals
FROM players
WHERE goals > (
  SELECT AVG(goals)
  FROM players
);

//   カラムの表示名を変更
SELECT name AS "身長180cm以上の選手"
FROM players WHERE height >= 180;

• テーブルとテーブルを紐付けるには主キーと外部キーを紐付ける（テーブル名.カラム名

SELECT players.name, countries.name FROM players
JOIN countries
ON players.country_id = countries.id;

• JOINは外部キーがNULLのレコードを取得しない
• NULLのレコードも含めて取得するにはLEFT JOIN
• JOINは複数可

SELECT players.name AS "選手名", teams.name AS "前年所属していたチーム"
FROM players
JOIN teams
ON players.previous_team_id = teams.id;
LEFT JOIN teams
ON players.previous_team_id = teams.id;

• ORDER BYは四則演算と併用可
• データを追加するときはINSERT INTO
• INSERT INTO 時のauto incrementのカラムは省略可

INSERT INTO students (name, course) VALUES("Kate", "Java");

• データの更新はUPDATE
• WHEREで指定しないと全部のデータが更新されてしまうので注意

UPDATE students SET name = "Jordan", course = "HTML" WHERE id ="6";

• データの削除はDELETE FROM
• WHEREで指定しないと全データ削除

DELETE FROM student WHERE id = 7;