新規行(レコード)の追加 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が返されていれば削除されています)
条件にサブクエリを使う
構文
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 );
分かりづらい時は以下の画像を参照してください。
すると一度も売れていないproductsテーブルからid 4 の 行が削除されます。
※Error Code: 1451 が出るときは、
を参考にしてください。
行の全削除
構文
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が返されています。
コメント