MySQL データ更新・行追加・行削除(構文&サンプル)

新規行(レコード)の追加 insert

基本的なレコードの追加

構文

insert into
 テーブル名(列1,列2,列3...)
values
 (値1,値2,値3);

注意点:列リストとvalues句のリストは、列数が一致している必要があります。

商品テーブル(products)に新しい商品を追加します。

商品名(name):新商品A

価格(price):1000

とした場合以下のような記述になります。

insert into products (name,price) values ('新商品A',1000);

確認するにはSQLで以下のように入力します。

select * from products;

※新商品の名前が表示されない時は、limitを変更してからもう一度SQLを実行しましょう。

列リストを省略してデータを1件追加する

条件

  • テーブルの全列に対して、値を指定する
  • values句に定義順、カンマ区切りで値を設定

テーブルの全列が id  商品名 価格 3つの場合

例)

insert テーブル名 values(1002,'新商品B',2000);

のようになります。

それでは、テーブル名を(products)にして実行してみます。

insert products values(1002,'新商品B',2000);

下の方に以下のようになっていれば行は追加されていますが

sqlで確認したい時は先程と同じようにselect * from products;とSQLに記述して確認します。

すると新商品Bが追加されているのが確認できます。

行を複数追加する insert

3件をデータベースに追加

  • 商品名:新商品C  価格:3,000
  • 商品名:新商品D 価格:4,000
  • 商品名:新商品E 価格:5,000

構文

insert into
 テーブル名(列1,列2,列3,...)
values
 (値1,値2,値3...),
 (値1,値2,値3...),
 (値1,値2,値3...);

※MySQL,PostgreSQL,SQL Server,DB2では使用可能。
Oracleでは使用できない。

それでは3件をデータベースに追加します。

insert into products (name,price) 
values 
('新商品C',3000),
('新商品D',4000),
('新商品E',5000);

すると以下のように3件分追加されます。

行(レコード)の更新 update

構文

update テーブル名 set 値1, [列2 = 値2...]
[where 条件式];

特定の条件の合致するデータを更新

商品テーブル(products) 

id name price
1 商品0001 4770
2 商品0002 5310
3 商品0003 1440
4 商品0004 3420
5 商品0005 8550

上のテーブルがあった場合に

idが1の商品名を

SQL入門書に変更する場合は以下のようにupdate文とwhere句を使って記述します。

update products set name = 'SQL入門書'where id =1;

すると以下のように商品0001がSQL入門書に変更されます。

品名と価格を同時に更新するには、カンマ(,)区切りで以下のように入力します。

update products set name = 'SQL入門書',price = 1000 where id =3;

※今度は、分かりやすいようにidを3にしています。

実行結果は・・

商品名と価格が一度に更新できてます。

累計販売数が5を超えている商品の価格を5%UPする。

以下のようにテーブルがあった場合に

productテーブル

order_detailsテーブル(productsとordersの中間テーブル)に累計が入っているのでこのテーブルを使用します。

まず、以下のようにorder_detailsテーブルから販売個数を求めます。

select 
  product_id,
  sum(product_qty)
from 
  order_details
group by 
  product_id;

次に販売個数が5以上のものに絞り込みます。

select 
  product_id,
  sum(product_qty)
from 
  order_details
group by 
  product_id
having
  sum(produuct_qty) >=5;

結果を確認したいのでこの時点でのproductを確認しておきます。

次にアップデート文を書き

update
  products
set
  price =price * 1.05
where
  id in
 (
 );

( )内に先ほど販売個数を求めた文を切り取って貼り付けます。

※inには累計販売個数が5を超えるidのリストを渡したいのでsum(product_qty)は削除します。

update
  products
set
  price =price * 1.05
where
  id in
  (
 select 
   product_id
 from 
   order_details
 group by 
   product_id
 having
   sum(product_qty) >=5
  );

すると累計販売個数5以上のid 1,2,3,6が5%upされました。

全てのデータを更新

構文

update テーブル名 set 値1, [列2 = 値2...]
[where 条件式];

全商品を10%引きに設定します。商品テーブルは(products)です。

まず、全商品のリストをSQLで select * from products; と記述して確認すると以下のようなデータが抽出されました。

構文通りに記述すると以下のようになりますが、

update products set price = price * 0.9;

一度にアップデートできない設定になっている場合は先にセーフモードの解除します。

セーフモードの解除は以下の通りです。

set sql_safe_updates = 0;

価格が10%引きになっているのがわかりますね。

※このようなデータの更新は実務上では殆どありませんので更新時は注意が必要です。

実務上使用するのは、一部のデータ更新が多く使用されます。

行(レコード)の削除

構文

delete from テーブル名 [where 削除条件]

条件を指定して削除する

商品テーブル:products

このテーブルの

商品ID 1002を指定して削除します。

delete from products where id = 1002;

今回の実行順とポイント

・1002行目なので、Limitを2000に変更。

・select文で対象の行を読み込む

・対象行の削除

・select文で削除されているか確認(NULLが返されていれば削除されています)

where句を指定し忘れるとテーブル全体が削除対象となりますので注意しましょう!

条件にサブクエリを使う

構文

delete from テーブル名 [where 削除条件]

DB2では動作しません。

今回の条件は、一回も注文がなかった商品を削除します。

販売された実績のある商品が以下のように

注文詳細テーブル(order_detaile)にあったと仮定し

この注文詳細テーブル(order_detaile)は、製品テーブル(products)に紐づいている中間テーブルです。

注文詳細テーブル(order_detaile)

製品テーブル(products)

製品テーブル(products)のidに 4 がありますが、

注文詳細テーブル(order_detaile)には 注文分のみのデータなので 4 はありません。

まず、売れたことのある注文詳細テーブルのproduct_idを利用して注文があった分のみを取得します。

select 
  product_id
from
  order_details
group by
  product_id;

次に削除する文を not in (~以外・含んでない)を使って記述します。

削除するのは商品テーブルなのでproductsです。以下のように記述します。

delete
from
  products
where
  id not in(
  );

次に先ほど記述したselect文の最後の ; のみ削除して全てコピーして、( )の中に貼り付けます。

delete
from
  products
where
   id not in(
select 
   product_id
from
   order_details
group by
   product_id   
   );

分かりづらい時は以下の画像を参照してください。

Before
After

すると一度も売れていないproductsテーブルからid 4 の 行が削除されます。

※Error Code: 1451 が出るときは、

Error Code: 1451 外部キー制約を一時的に無効にしてデータを操作するには、SET FOREIGN_KEY_CHECKS = 0 で外部接続を一旦無効にし、実行したい処理をしたら、すぐに SET FOREIGN_KEY_CHECKS = 1 で外部キー制約を元に戻しましょう!

を参考にしてください。

行の全削除

構文

delete from テーブル名 [where 削除条件];

以下のように

products(製品テーブル)

products_categories(productsとcategoriesを紐づけている中間テーブル)

categories(カテゴリーテーブル)

があった時に

products_categoriesの紐づきを削除するには中間テーブルのproducts_categoriesの行を削除します。

まず、select文でproducts_categoriesを確認します。

select * from products_categories;

すると以下のようにテーブルが表示されますので、このテーブルの行を削除します。

delete from products_categories;

もう一度、先程のselect文で読みだしてみると行が削除されているのでNULLが返されています。

にほんブログ村 IT技術ブログへ

スポンサーリンク
スポンサーリンク
SNSフォローボタン

フォローする

シェアする

  • このエントリーをはてなブックマークに追加
スポンサーリンク
スポンサーリンク