ざっくりSQL入門 #7:クエリを書く<INSERT&UPDATE編>
※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。
今回は、INSERTやUPDATEの構文を用いて、レコードの追加や更新を行う方法について。
INSERT・UPDATEとは?
新しくレコードを追加するときは「INSERT」、
既存レコードを更新するときは「UPDATE」を用います。
新しくレコードを追加したいが既存の場合は上書きしたい(もしくは何もしない)、という場合は
INSERTのオプションで対応します。
新しくレコードを追加する(INSERT)
追加するレコードの指定方法は大きく分けて2つあり、
以下のいずれでも追加することができます。
INSERT ... VALUES ...
「nameが○○、birthdayが△△、genderが…」と、追加するレコードが固定値で分かっている場合。
INSERT INTO 【テーブル名】 (【カラム1】, 【カラム2】, ...) VALUES (【値1】 , 【値2】 , ...) -- 複数レコードの場合、VALUES以下のカッコをカンマ区切りで記載すればOK INSERT ... VALUES (...), (...), ...
もちろん、カラムと値の記載順は合わせる必要があります。
また、以下のカラムと値については記載しなくてOKです。
- NULLが許可されているカラムで、NULLにしておきたい場合
- NULL不許可で無指定の場合、数値なら0、文字列なら空文字で記録されます
- INSERTは実行されますが、敢えて指定しないのは推奨しません(warningも出ます)
- NULL不許可で無指定の場合、数値なら0、文字列なら空文字で記録されます
- デフォルト値やauto_incrementが設定されているカラムで、その値を設定させたい場合
- 尚、auto_incrementのカラムに固定値を指定した場合、次のレコードは「指定値+1」になる
INSERT ... SELECT ...
「SELECTクエリの結果セットをそのまま追加したい」という場合。
INSERT INTO 【テーブル名】 (【カラム1】, 【カラム2】, ...) SELECT 【カラム1】, 【カラム2】, ... FROM ... WHERE ...
こちらも、INSERTのカラムとSELECTのカラムの記載順は合わせる必要があります。
既存のレコードを更新する(UPDATE)
以下のUPDATE構文を用いることで、既存レコードの更新が行えます。
UPDATE 【テーブル名】 SET 【更新対象のカラム】 = 【更新する値】 WHERE 【条件】 -- 複数カラムを更新する場合、SET句をカンマ区切りで記載すればOK UPDATE ... SET `hoge` = 'a', `fuga` = 'b', ... WHERE ...
条件に合致するレコードが複数ある場合はそのすべてが更新対象になります。
また、WHERE句を省略すると指定テーブルの全レコードが更新対象になります。
うっかり意図しないレコードを上書きしないよう注意しましょう。
基本はこれだけですが、応用的な使い方を以下に記載します。(クリックで展開/折りたたみ)
INSERTするが、重複する場合は上書きする(もしくは無視する)
※方言差の大きい項目です。繰り返しますが、ここではMySQLについて記載します。
todo::これだけで長文記事が一本出来上がるレベルなので
後程書いてリンクを張っておくこととして、ここでは簡単に。
例えば、プライマリキーでもある「id」と、単なる文字列の「name」を持つテーブル「user」があるとします。
現在以下のデータが入っています。
id | name |
1 | Alex |
ここに以下のINSERTをすると、idが重複しているためにエラーになりますね。
INSERT INTO `user` (`id`, `name`) VALUES (1, 'Anna')
ここで、重複しなければ「id=1, name=Anna」をINSERTしたくて、
idが重複するなら、そのidを持つレコードのnameをAnnaに上書きしたい…という場合。
一旦SELECTするとか、サブクエリを駆使する方法もありますが、
INSERT構文のみで「なければINSERT、あればUPDATE」を実現することもできます。
INSERT INTO `user` (`id`, `name`) VALUES (1, 'Anna') ON DUPLICATE KEY UPDATE `name` = 'Anna'
通常のINSERTに「ON DUPLICATE KEY UPDATE 【IDが重複していた場合の変更内容】」を付加した形となります。
上記の例は固定値で上書きという単純な形となっていますが、
以下のような書き方も可能です。
ON DUPLICATE KEY UPDATE `name` = '名前' -- 固定値で上書き(つまり、VALUES句に書いた値でなくともよい) ,`tel` = VALUES(`tel`) -- 本来INSERTするはずだった値で上書き ,`mail_address` = `mail_address` -- 元々の値で上書き(=何もしない) ,`count` = `count` + 1 -- インクリメント ,`status_before` = `status_now` -- 他のカラムの値で上書き