SQL関連メモ

SQLについて調べて、残しておきたいものを書き連ねていきます。

気をつけること

FK作成時

• 対象カラムにINDEXが張っているか（対象がPRIMARY KEYでない場合）
• 参照先、参照元のどちらもInnoDBか
• 参照元のカラムと参照先のカラムの型は同じか
• 参照先カラムにデフォルト値をセットしていないか
• ON DELETE SET NULLを指定しているが参照先がNOT NULLではないか
• その他何らかの構文エラーがある
  （なぜかSyntax Errorではなく、Can’t create tableが出る）

これらを守らない場合、Can’t create table error 150がでる。

いろいろなSQL

重複データのあぶり出し

WHERE (user_id in (SELECT user_id FROM user_roles GROUP BY user_id HAVING COUNT(user_id) > 1))

IN句

countryテーブルから「Australia」「Japan」「Spain」を抽出したい場合

where country in('Australia','Japan','Spain');

countryテーブルから「Australia」「Japan」「Spain」を除いて抽出したい場合

where country not in('Australia','Japan','Spain');

FKをデータを入れた後につけるとき

対応していないデータを探すには

SELECT ct.id, pt.id
FROM child_table ct LEFT JOIN parent_table pt ON ct.parent_id = pt.id
WHERE pt.id is NULL

とすると、FK制約に違反しているデータを探すことができます。
INNER JOINでは結合相手がいない行は結果に出ないので、OUTER JOINで検索する必要があります。

最大値や最新日付

最大値や最新日付を出したい場合は、いくつかのパターンがあります。

SELECT * FROM tableName WHERE columnName=(SELECT MAX(columnName) FROM tableName);

SELECT * FROM tableName ORDER BY columnName DESC LIMIT 1;

SELECT max(columnName) FROM tableName GROUP BY columnName;

テーブル間のデータ移行

古いテーブルから新しいテーブルへデータを移行したいとき

UPDATE new_table nt
INNER JOIN old_table ot ON nt.old_id = ot.id
SET nt.column1 = ot.column1,
	nt.column2 = ot.column2,
	nt.column3 = ot.column3,