What's New Qiita

20190127のSQLに関する記事は2件です。

  • 投稿日：2019-01-27T22:12:33+09:00

【初心者向け】データベースの基本とSQL文「INSERT / UPDATE / SELECT / AS / DELETE / ORDER BY」

やること

データベースの基本とSQL文を学ぶ

  • ①レコードの追加（INSERT文）
  • ②レコードの更新（UPDATE文）
  • ③テーブルからフィールドを抜き出す（SELECT文）
  • ④練習問題
  • ⑤レコードの抽出（SELECT文）
  • ⑥複数のテーブルから仮想テーブルを作成する
  • ⑦演算フィールドについて(AS句）
  • ⑧レコードの削除（DELETE文）
  • ⑨レコードを並び替えよう（ORDER BY句）
  • ⑩まとめ

参考書籍
PHPとMySQLのツボとコツがゼッタイにわかる本

DB情報

1. 商品マスタテーブル(goods)
GoodsID GoodsName Price
2. 顧客マスタテーブル(customer)
CustermerID CustomerName TEL Email
3. 売上情報テーブル(salesinfo)
id SalesDate CustomerID GoodsID Quantity

学習内容

①INSERT文　～レコードの追加～

INSERT INTO テーブル名 VALUES(値、値、値・・・)

①-①テーブルgoodsにレコードを追加するSQL文

GoodsID GoodsName Price
- - -
INSERT文
INSERT INTO goods VALUES('1000','シンプルな消しゴム',100)

実行結果

GoodsID GoodsName Price
1000 シンプルな消しゴム 100

①-②テーブルcustomerにレコードを追加するSQL文

CustermerID CustomerName TEL Email
INSERT文
INSERT INTO customer
VALUES('0001','青空商事','0268-26-9999','ao@aaaa.jp')

実行結果

CustermerID CustomerName TEL Email
0001 青空商事 0268-26-9999 ao@aaaa.jp

②UPDATE文　～レコードの更新～

レコードを更新するときの構文
UPDATE テーブル名 SET フィールド名 = 値 WHERE 条件式

複数のフィールドの値を一度に更新する場合
SET フィールド名 = 値, フィールド名 = 値・・・WHERE 条件式

②-①goodsテーブルのレコードのPriceの値を200に変更する

GoodsID GoodsName Price
1000 シンプルな消しゴム 100
UPDATE文
UPDATE goods SET Price=200 WHERE GoodsID = '1000'

実行結果

GoodsID GoodsName Price
1000 シンプルな消しゴム 200

③SELECT文　～テーブルからフィールドを抜き出す～

1つのテーブルから任意の数のフィールドを抜き出して仮想テーブルを作成
SELECT フィールド名,フィールド名 FROM テーブル名

テーブル内のすべてのフィールドを抜き出すとき
SELECT * FROM テーブル名

③-①仮想テーブルを作成するSQL文

SELECT文
SELECT CustomerID, CustomerName FROM customer

実行結果

CustomerID CustomerName
0001 青空商事

④練習問題

④-①goodsテーブルに7件のレコードを追加する

GoodsID GoodsName Price
1000 シンプルな消しゴム 100
SELECT文(複数のレコードを追加)
INSERT INTO goods VALUES
      ('1001','カラフルノート','150'),
      ('1002','きらきらボールペン','120'),
      ('1003','天使のメモ帳','200'),
      ('1004','チョコの味するスケール','1080'),
      ('1005','センサー付クリップ','650'),
      ('1006','おっせかいなノート','1500'),
      ('1007','ミスト付ボールペン','230')

実行結果

GoodsID GoodsName Price
1000 シンプルな消しゴム 100
1001 カラフルノート 150
1002 きらきらボールペン 120
1003 天使のメモ帳 200
1004 チョコの味するスケール 1080
1005 センサー付クリップ 650
1006 おせっかいなノート 1500
1007 ミスト付ボールペン 230

④-②customerテーブルに3件のレコードを追加する

CustermerID CustomerName TEL Email
0001 青空商事 0268-26-9999 ao@aaaa.jp
INSERT文(複数のテーブルを追加)
INSERT INTO customer VALUES
       ('0002','ひまわり商事','06-6547-8963','info@himawari.com'),
       ('0003','まこに酒房','0268-36-3214','makoni@plala.pr'),
       ('0004','丸子文具','0268-26-9999','tatsudai2000@gmail.com')

実行結果

CustomerID CustomerName TEL Email
0001 青空商事 0268-26-9999 ao@aaaa.jp
0002 ひまわり商事 06-6547-8963 info@himawari.como
0003 まこに酒房 0268-36-3214 makoni@plala.pr
0004 丸子文具 0268-26-9999 tatsudai2000@gmail.com

⑤SELECT レコードの抽出

レコードを抽出する構文
SELECT フィールド名 FROM テーブル名 WHERE 抽出条件

フィールドは最低1つを指定する必要があります。もちろん複数指定も可能です。

⑤-①WHERE句を使ったSELECT文を記述する

SELECT文(WHERE句を使った)
SELECT * FROM goods WHERE GoodsID = '1001'

実行結果

GoodsID GoodsName Price
1001 カラフルノート 150

⑥複数のテーブルから仮想テーブルを作成

⑥-① salesinfoテーブルに売上情報を登録する

売上情報
2014年7月24日に丸子文具にシンプルな消しゴムを40個販売した

各フィールドに設定する値

フィールド名 値 理由
id 設定しない 自動連番になっているから
SalesDate 2014-07-24
CustomerID 0004 丸子文具のID
GoodsID 1000 シンプルな消しゴムのID
Queantity 40

INSERT文を記述

INSERT文(売上情報を登録する)
INSERT INTO salesinfo VALUES(null,'2014-07-24','0004','1000',40)

実行結果

id SalesDate CustomerID GoodsID Quantity
1 2014-07-24 0004 10000 40

⑥-② INNER JOIN句を使ったSELECT文を記述する

INNER JOIN句

SELECT フィールド名,フィールド名・・・
FROM テーブルA
INNER JOIN テーブルB ON テーブルA.X = テーブルB.X

INNETJOIN句(データベースsalesmanagement上でクエリを実行する
SELECT id,SalesDate,CustomerName,GoodsName,Price,Quantity
FROM salesinfo
INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
INNER JOIN goods ON salesinfo.GoodsID=goods.GoodsID

実行結果

id SalesDate CustomerName GoodsName Price Quantity
1 2014-07-24 丸子文具 シンプルな消しゴム 100 40

⑦演算フィールドについて（AS句＝フィールドを指定するときに使う）

price * Quantity = Amount

AS句
SELECT id,SalesDate,CustomerName,GoodsName,Price,Quantity
(Price*'Quantity')AS Amount
FROM salesinfo
INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
INNER JOIN goods ON salesinfo.GoodsID=goods.GoodsID

実行結果

id SalesDate CustomerName GoodsName Price Quantity Amount
1 2014-07-24 丸子文具 シンプルな消しゴム 100 40 4000

参考書籍によれば、上記の実行結果が表示されるだが、実際には下記のエラーが表示され先に進まなかった（原因調査中）

1305 -FUNCTION salesmanagement.Quantity does not exist

⑧レコードの削除(DELETE文を使う）

レコードの削除はDELETE文を使います。
DELETE FROM テーブル名 WHERE 条件式

削除はレコード単位で行われます。WHERE句を書き忘れると全レコードが削除されてしまうので注意しましょう。

⑧-①goodsテーブルからレコードを削除

GoodsID GoodsName Price
1000 シンプルな消しゴム 100
1001 カラフルノート 150
1002 きらきらボールペン 120
1003 天使のメモ帳 200
1004 チョコの味するスケール 1080
1005 センサー付クリップ 650
1006 おせっかいなノート 1500
1007 ミスト付ボールペン 230 
DELETE FROM goods WHERE GoodsID='1007'

実行結果

GoodsID GoodsName Price
1000 シンプルな消しゴム 100
1001 カラフルノート 150
1002 きらきらボールペン 120
1003 天使のメモ帳 200
1004 チョコの味するスケール 1080
1005 センサー付クリップ 650
1006 おせっかいなノート 1500

⑨レコードを並び替えよう（ORDER BY句）

SELECT文の結果を並び替える（ソートする）ときの構文
SELECT フィールド名・・・FROM テーブル名
ORDER BY ソート対象のフィールド名 ASC/DESC

⑨-①レコードをPriceの昇順に並び替える

ORDERBY句(ASC/DESCを省略すると昇順になる）
SELECT * FROM goods ORDER BY price

実行結果

GoodsID GoodsName Price
1000 シンプルな消しゴム 100
1002 きらきらボールペン 120
1001 カラフルノート 150
1003 天使のメモ帳 200
1005 センサー付クリップ 650
1004 チョコ味のするスケール 1080
1006 おせっかいなノート 1500

⑨-②レコードをPriceの降順に並び替える

SELECT * FROM goods WHERE GoodsID>='1003'
ORDER BY Price DESC

実行結果

GoodsID GoodsName Price
1006 おせっかいなノート 1500
1004 チョコの味するスケール 1080
1005 センサー付クリップ 1500
1003 天使のメモ帳 1500

⑩まとめ

  • SELECT文（抽出・検索）
  • INSERT文（追加）
  • UPDATE文（更新・変更）
  • DELETE文（削除）

おまけ：SQLコーディングの際の注意事項(著者から）

  • 全角スペースが紛れ込まないように
  • アルファベットに全角を使わないように
  • フィールド名は大文字小文字を区別する
  • 文字列や日付の両端はシングルコーテーションマークで囲む
  • 同列のフィールドが登場する場合はテーブル名、フィールド名と記述する
  • 1日やってもダメなときは誰かにアドバイスを求める
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-01-27T13:50:42+09:00

ListAgg関数とwmsys.wm_concat関数の違い

昔、US-OTNに投稿した
ListAgg関数とwmsys.wm_concat関数の違い
https://community.oracle.com/thread/1090158
を日本語に翻訳しつつ、全面リニューアルしてみました。

かつては、wmsys.wm_concat関数というマニュアルに載ってない関数があったのですが
11gR2XEや12cで消されてしまいました :scream:

ListAgg関数と似た関数だったのですが、
wmsys.wm_concat関数との違いをまとめてみます。

01 distinctオプションの有無

wmsys.wm_concat関数は、distinctオプションを指定できますが
ListAgg関数では不可です。

create table diffT(SortKey,Val) as
select 1,'aa' from dual union all
select 2,'bb' from dual union all
select 3,'aa' from dual union all
select 4,'dd' from dual;

col concatV for a20

select wmsys.wm_concat(distinct Val) as concatV from diffT;

concatV
--------
aa,bb,dd

02 文字の接続順序の指定

ListAgg関数は、文字の接続順序を指定できますが
wmsys.wm_concat関数では不可です。

select ListAgg(Val,',')
       within group(order by SortKey desc) as concatV
from diffT;

concatV
------------
dd,aa,bb,aa

03 デリミタの指定

ListAgg関数は、文字の接続のデリミタを指定できますが
wmsys.wm_concat関数では不可です。

select ListAgg(Val,'***')
       within group(order by SortKey desc) as concatV
from diffT;

concatV
-----------------
dd***aa***bb***aa

04 分析関数として使用した時のOrderBy指定

wmsys.wm_concat関数は、分析関数として使用した時にOrderBy指定できますが、
ListAgg関数では不可です。

select SortKey,wmsys.wm_concat(Val)
               over(order by SortKey) as concatV
  from diffT;

SortKey  concatV
-------  -----------
      1  aa
      2  aa,bb
      3  aa,bb,aa
      4  aa,bb,aa,dd

05 Keep指定

wmsys.wm_concat関数は、Keep指定が使えますが
ListAgg関数では不可です。

select wmsys.wm_concat(Val) 
       Keep(Dense_Rank First order by Val) as concatV
  from diffT;

concatV
-------
aa,aa
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧