LoginSignup
0
0

More than 1 year has passed since last update.

@YusukeYamazaki(Yusuke Yamazaki)

SQLゼロから始めるDB操作　ノート

Posted at

ミック著　SQLゼロから始めるDB操作を読んで、新しく学んだ箇所を自分用のノートとしてまとめます。

　0.SQL文の種類　(DDL, DML, DCL)

DDL (Data Definition Language)

DBやテーブルの作成削除
CREATE
DROP
ALTER

DML (Data Manipulation Language)

テーブル内のデータを検索したり、追加したり消したり
SELECT
INSERT
UPDATE
DELETE

DCL (Data Control Language)

DBに対する変更の確定、取り消し
ユーザーの権限を付与したり、奪ったり
COMMIT
ROLLBACK
GRANT　: ユーザーに権限を付与
REVOKE　: ユーザーから操作権限を剥奪

　1. DB作成　

format
CREATE DATABASE <DB名>;
example
CREATE DATABASE shop;


　2. テーブルの作成

format
CREATE TABLE <テーブル名>
(<列名1> <データ型> <この列の制約>,
 <列名2> <データ型> <この列の制約>,
 <列名3> <データ型> <この列の制約>,
 <列名4> <データ型> <この列の制約>,
            .
            .
<このテーブルの制約1>, <このテーブルの制約2>,....);
example
CREATE TABLE Shohin
(shohin_id     CHAR(4) NOT NULL,
 shohin_mei    VARCHAR(100) NOT NULL,
 shohin_bunrui VARCHAR(32) NOT NULL,
 hanbai_tanka  INTEGER ,
 shiire_tanka  INTEGER ,
 torokubi      DATE ,
     PRIMARY KEY (shohin_id));

　命名ルール

  • DB、テーブル、列の命名に使えるのは半角アルファベット、数字、アンダーバーのみ

  • 頭文字は必ずアルファベットにすること（数字を頭文字にすることは標準SQLでは禁止されている）

　データ型

  • INTEGER型
    整数型（数値型）。小数は不可。

  • CHAR型（キャラ型）
    固定文字列(文字列型)。CHAR(10) のように後ろのカッコで文字列の長さ（最大長）を指定できる。
    列にがいる文字列の長さが最大長より短い場合、不足分は半角スペースで補填される。
    イメージ　'abc_ _ _ _ _'

  • VARCHAR型（バーキャラ型）
    可変長文字列(文字列型)。CHAR型同様、最大長をカッコで指定する。
    列にがいる文字列の長さが最大長より短い場合でも、半角スペースで補填されない。
    イメージ　'abc'

  • DATE型
    日付型。（年月日）

　制約

  • NOT NULL制約
    NULLだとエラーになる。

  • PRIMARY KEY制約
    主キー制約。ユニークキー制約。

　3.NULは比較演算子には使えない

以下のようにNULLを比較演算子で条件指定しても、1行も選択されない。

SELECT shohin_mei, shiire_tanka
  FROM Shohin
 WHERE shiire_tanka = NULL;

NULLを条件で絞り込むには、IS NULL、 IS NOT NULLを使う必要がある。

SELECT shohin_mei, shiire_tanka
  FROM Shohin
 WHERE shiire_tanka IS NULL;


SELECT shohin_mei, shiire_tanka
  FROM Shohin
 WHERE shiire_tanka IS NOT NULL;

　4.OR演算子よりAND演算子のほうが優先される

例えば、以下のテーブルを、以下の検索条件を書く場合,
カッコでくくりOR演算子を優先にしれやる必要がある。
カッコで括らないと、AND演算子が優先され

「shohin_bunrui = '事務用品' and torokubi = '2009-09-11'」 OR 「torokubi = '2009-09-20'」

になってしまう。

検索条件　：　商品分類が事務　かつ　登録日が2009/9/11または2009/9/20 

SELECT shohin_mei, shohin_bunrui, torokubi
  FROM Shohin
 WHERE shohin_bunrui = '事務用品'
   AND ( torokubi = '2009-09-11'
      OR torokubi = '2009-09-20');
テーブル
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 |
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20
 0005      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-01-15
 0006      | フォーク       | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね     | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン     | 事務用品      |          100 |              | 2009-11-11

　5.３値論理

通常論理演算は２値論理だが、SQLだけ３値論理と呼ばれている。
値がNULLの場合は、真でも偽でもなく不明（UNKNOWN）をなる。

２値論理

真true　偽false

３値論理

真true　偽false　不明unknown（=NULL）

　6.GROUP BYを使用した際の、SELECT句に記述できるもの

  • 定数
  • 集約関数（COUNT, SUM, AVG, MAX, MIN）
  • GROUP BYで指定したカラム（つまり集約キー）
    　言い換えるとGROUP BY句を使う時、SELECT句には集約キー以外のカラム名を書けない

WHERE句では集約関数は使えない

いかのSQLはエラーになる。
集約関数をかけるのは、SELECT句、HAVING句、ORDER BY句のみ。

SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 WHERE COUNT(*) = 2
 GROUP BY shohin_bunrui;

　7.DISTINCT　重複を削除

SELECT COUNT(DISTINCT shohin_bunrui)
  FROM Shohin;

実行結果

 count
-------
     3

SELECT DISTINCT shohin_bunrui
  FROM Shohin;

実行結果

 shohin_bunrui
---------------
 事務用品
 衣服
 キッチン用品

　8.WHERE句とHAVING句

WHERE句　それぞれの行に対する条件指定
HAVING句　それぞれのグループに対する条件指定

集約キーに対する条件は、HAVING句ではないく、WHERE句に記述する

コラム　WHERE句とHAVING句の違い
WHERE句はHAVING句より実行速度が早い。
その理由は、WHERE句はマシン負荷の高いソート処理の前に行われるからである。
ソート処理より先に、WHERE句で行数を減らすことでパフォーマンスが上がる。
HAVING句の処理は、ソート処理の後に行われる。

　9.句の記述順　実行順

句の記述順
1.SELECT句 -> 2.FROM句 -> 3.WHERE句 -> 4.GROUP BY句 -> 5.HAVING句 -> 6.ORDER BY句

句の実行順序
1.FROM句 -> 2.WHERE句 -> 3.GROUP BY句 -> 4.HAVING句 -> 5.SELECT句 -> 6.ORDER BY句

この句の実行順序からわかるように、GROUP BY句はSELECT句より先に実行されている。
つまり、SELECT句でつける別名は、GROUP BY句では使用できない。

　10.ソートにおけるNULLの順番

ソートキー（ORDER BYで並べられるやつ）の値にNULLがあった場合、先頭もしくは末尾にまとめられて表示される。

　11.ORDER BYには、SELECT句に含まれていないカラムや集合関数も使える

sample
SELECT shohin_mei, hanbai_tanka, shiire_tanka
  FROM Shohin
 ORDER BY shohin_id;
sample2
SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui
 ORDER BY COUNT(*);

　12.INSERT文の基本構造

format
INSERT INTO <テーブル名> (列1, 列2, 列3, ...) VALUES (値1, 値2, 値3, ...);
example
INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) 
       VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');

原則として、INSERT文は1回の実行で1行を挿入すること。

　13.他のテーブルのデータをINSERTする

他のテーブルからデータをコピーする
事前にコピーするテーブルと同じカラムのコピー先になるテーブルを作成する。
ここではコピー元をShohin コピー先をShohinCopyとする。
その上で以下のSQLでコピーできる。

INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi)
SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi
  FROM Shohin;

他のテーブルのデータを加工してINSERTする
ShohinBunrui (shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka)のようなテーブルに、
shohin_bunruiの集約関数の結果を入れる場合、以下の様になる。

INSERT INTO ShohinBunrui (shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka)
SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka)
  FROM Shohin
 GROUP BY shohin_bunrui;

　14.DROP文とDELETE文

  • DROP文はテーブル自体を消す
  • DELETE文はテーブルの内のデータを消す
DROP文
DROP TABLE <テーブル名>;
DELETE文
DELETE FROM <テーブル名>;

DELETE FROM <テーブル名>
 WHERE <条件>;

　15.TRUNCATE文

テーブル内の全データを高速に削除する

DELETE文との違い
1. WHERE句で条件指定をできない。
2. TRUNCATE文はDELETE文より高速。DELETE処理はかなり重たい処理。
3. TRUNCATE文はOracle, SQL Server, PostgreSQL, MySQL, DB2に用意されているコマンド。
　DELETE文は標準SQLのコマンド。

　16.標準SQL

SQLの標準規格は、ANSI（米国規格協会）やISO（国際標準化機構）といった標準化団体により、数年に一度改訂されます。
改訂ごとに決められた規格は、制定された年ごとに「SQL:1999」「SQL:2003：「SQL:2003」「SQL:2008」「SQL:2011」「SQL:2016」などと呼ばれます。こうした標準規格に準拠したSQLが標準SQLです。

ただし、SQLの標準規格に「すべてのRDBMSは標準SQLを使わなければならない」という強制力はありません。標準SQLをサポートしたRDBMSは増えましたが、それでも標準SQLで書いたSQL文を実行できないことがあります。

標準SQL徹底入門より引用

　17.探索型UPDATE

UPDATE句は、DELETE句同様、WHERE句を使える。

format
UPDATE <テーブル名>
   SET <列名> = <式>
  WHERE <条件>;
sample
UPDATE Shohin
   SET hanbai_tanka = hanbai_tanka * 10
 WHERE shohin_bunrui = 'キッチン用品';

　18.トランザクションはいつ始まるのか

ユーザーがDBに接続した時点で暗黙の内にトランザクションが開始されている。
そのためユーザーが明示的にトランザクションの開始を宣言する必要はない。

では、トランザクションはどのタイミングで区切られているのか？
以下の２つのパターンが有る。

  1. 「１つのSQL文で１つのトランザクション」というルールが適用される（自動コミット）
  2. 　ユーザーがCOMMIT または ROLLBACkするまでが１つのトランザクションとみなされる

一般的なDBはどちらも選択可能になっている。
SQL Server, PostgreSQL, MySQLなどはデフォルトで、自動コミットになってる。

　20.VIEW

VIEWは、SELECT文に名前をつけて、テーブルのように扱えるようにしたもの。

VIEWの作成方法

format
CREATE VIEW ビュー名 (<ビュー列名１>,<ビュー列名２>,<ビュー列名３>,....)
AS
<SELECT文>
exampleCreateView
CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin)
AS
SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui;
exampleSelectView
SELECT shohin_bunrui, cnt_shohin
  FROM ShohinSum;

VIEWをFROM句に指定したSELECT文でも、WHERE, GROUP BY, HAVINGは使える。

VIEWをFROM句に指定したときの検索は、
1. VIEWで定義した、SELECT文が実行
2. その結果に対して、VIEWをFROM句に指定したSELECT文を実行

VIEWの制限
1. ORDER BY句が使えない。
2. VIEWに対して、データの更新はできるっちゃできるが、基本的にはしない方が良い。

VIEWの削除

format
DROP VIEW ビュー名

　21.SQLで使える代表的な関数

　算術関数（数値の計算用の関数）

  • 算術関数
    +, -, *, /　など
  • 絶対値
    ABS(数値)
  • 剰余
    MOD(被序数, 除数)
    例　MOD(7, 3) は 1
  • 四捨五入
    ROUND(対象数,　丸めの行数)
    丸めの行数を1にしたら少数第2を四捨五入
    丸めの行数を2にしたら少数第3を四捨五入

　文字列関数（文字列操作用の関数）

  • 連結
    文字列A || 文字列B
sample
SELECT last_name || first_name AS full_name
FROM User;

  • LENGTH 文字列の長さ
    LENGTH(文字列)

  • LOWER 小文字化
    LENGTH(文字列)

  • UPPER 大文字化
    LENGTH(文字列)

  • REPLACE 文字列の置換
    REPLACE(対象文字列, 検索文字列, 置き換え文字列)

  対象文字列   | 検索文字列 | 置き換え文字列
--------------+-----------+----------------
 あいう       | えお       | あいうえお
 abc          | def       | abcdef
 山田         | 太郎       | 山田太郎
 aaa          |           |
              | あああ    |
 @!#$%        |           |
 ABC          |           |
 aBC          |           |
 abc太郎      | abc       | abc太郎abc
 abcdefabc    | abc       | abcdefabcabc
 ミックマック  | ッ        | ミックマックッ
  • SUBSTRING 文字列切り出し
    SUBSTRING(対象文字列 FROM 切り出し開始位置 FOR 切り出す文字数)

    SUBSTRING('ABCDE' FROM 3 FOR 2) だと結果は　CD SUBSTRING('ABCDE' FROM 3 FOR 5) だと結果は　CDE

　日付関数（日付操作用の関数）

  • CURRENT_DATE 現在の日付
    CURRENT_DATE
    2020-10-08
    引数派が不要なため()カッコはいらない
  • CURRENT_TIME 現在の時間
    CURRENT_TIME
    17:26:50.995.+09
    引数派が不要なため()カッコはいらない
  • CURRENT_TIMESTAMP 現在の日時
    CURRENT_TIMESTAMP
    2020-10-08 17:26:50.995.+09
    引数派が不要なため()カッコはいらない
  • EXTRACT 日付要素の切り出し
    EXTRACT(日付要素 FROM 日付)
sample
SELECT CURRENT_TIMESTAMP,
       EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
       EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
       EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
       EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
       EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
       EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

       current_timestamp       | year | month | day | hour | minute |  second
-------------------------------+------+-------+-----+------+--------+-----------
 2021-10-08 15:59:41.083063+09 | 2021 |    10 |   8 |   15 |     59 | 41.083063

　変換関数（データ型の値を変換するための関数）

  • CAST　型変変換 CAST(変換前の値 AS 変換するデータ)
sample
--MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

--Oracle
SELECT CAST('0001' AS INTEGER) AS int_col
  FROM DUAL;

--SQL Server、PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;

　集約関数（データの集計を行うための関数）

COUNT, SUM, AVG, MAX, MINなど

　22.COALESCE(コアレス) NULLの場合置換

format
COALES(データ1, データ2, データ3, ...)

COALESCECOALESCE関数は、SQL独自の関数。
可変数個の引数を取り、左から見ていき最初に出会ったNULL出ない値を返す。

例えば、以下のようなテーブルでにおいて、割引価格を求める場合、
SELECT id, amount - discount as discounted_amount
FROM purchases;
と書いた場合、id1の割引金額は1000 - null　= nullになってしまう。
そのような時は、以下のように書けば良い。
SELECT id, amount - COALESCE(discount, 0) as discounted_amount
FROM purchases; 

> SELECT * FROM purchases;
+----+--------+----------+
| id | amount | discount |
+----+--------+----------+
|  1 |   1000 |     NULL |
|  2 |   2000 |      500 |
|  3 |   3000 |     1000 |
+----+--------+----------+

　23.述語（predicate）とは

述語とは平たく言うと関数の一部だが、「戻り地が真理値になること」という条件を満たすものを、
述語という。
- LIKE
- BETWEEN
- IS NULL, IS NOT NULL
- IN
- EXISTS

LIKE述語

文字列の一部検索

  • =　　 完全一位の場合TRUE
  • LIKE 　部分一致でTRUE

前方一致、中間一致、後方一致の三種類がある

前方一致
SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'ddd%';
中間一致
SELECT *
  FROM SampleLike
 WHERE strcol LIKE '%ddd%';
後方一致
SELECT *
  FROM SampleLike
 WHERE strcol LIKE '%ddd';

EXISTS述語

相関サブクエリにヒットすつレコードが存在するかでTRUE FALSE

example
SELECT shohin_mei, hanbai_tanka
  FROM Shohin AS S
 WHERE EXISTS (SELECT *
                 FROM TenpoShohin AS TS
                WHERE TS.tenpo_id = '000C'
                  AND TS.shohin_id = S.shohin_id);

サブクエリのSELET句には何を書いても良い。
SELECT 1でも良いが、慣習的にSELECT *と書きましょう。

　24. CASE式

条件分岐。

format
CASE WHEN <評価式> THEN <式>
     WHEN <評価式> THEN <式>
     WHEN <評価式> THEN <式>
      .
      .
     ELSE <式>
END
example
SELECT shohin_mei,
       CASE WHEN shohin_bunrui = '衣服'         THEN 'A：' || shohin_bunrui
            WHEN shohin_bunrui = '事務用品'     THEN 'B：' || shohin_bunrui
            WHEN shohin_bunrui = 'キッチン用品' THEN 'C：' || shohin_bunrui
            ELSE NULL
       END AS abc_shohin_bunrui
  FROM Shohin;


   shohin_mei   | abc_shohin_bunrui
----------------+-------------------
 Tシャツ        | A：衣服
 穴あけパンチ    | B：事務用品
 カッターシャツ  | A：衣服
 包丁           | C：キッチン用品
 圧力鍋         | C：キッチン用品
 フォーク       | C：キッチン用品
 おろしがね     | C：キッチン用品
 ボールペン     | B：事務用品

　25. 内部結合　INNER JOIN

結合キーが存在するもののみで結合する。
image.png

　26. 外部結合 OUTER JOIN

LEFT ,RIGHTのどちらか片方の情報はすべて表示され、それに合わせて結合する。
image.png

以下の2つの結果は同じくなるが、一般的にはLEFTを使う場合が多いが、どちらでも良い。

RIGHT_OUTER_JOIN
SELECT TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka
  FROM TenpoShohin TS RIGHT OUTER JOIN Shohin S
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
LEFT_OUTER_JOIN
SELECT TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka
  FROM Shohin AS S LEFT OUTER JOIN TenpoShohin AS TS
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;

　27. ウィンドウ関数　OLAP関数

format
<ウィンドウ関数> ORVER ([PARTITION BY <配列リスト>]
                           ORDER BY <ソート用列リスト>)

ウィンドウ関数には大きく分けて2種類存在する。
1. 集約関数（SUM, AVG, COUNT, MAX, MIN）をウィンドウ関数として使う。
2. RANK, DENSE_RANK, ROW_NUMBERなどのウィンドウ専用関数を使う。

RANK

分類別に販売単価の安い順で並べたランキング

SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
       RANK () OVER (PARTITION BY shohin_bunrui
                         ORDER BY hanbai_tanka) AS ranking
  FROM Shohin;


   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking
----------------+---------------+--------------+---------
 フォーク       | キッチン用品  |          500 |       1
 おろしがね     | キッチン用品  |          880 |       2
 包丁           | キッチン用品  |         3000 |       3
 圧力鍋         | キッチン用品  |         6800 |       4
 ボールペン     | 事務用品      |          100 |       1
 穴あけパンチ   | 事務用品      |          500 |       2
 Tシャツ        | 衣服          |         1000 |       1
 カッターシャツ | 衣服          |         4000 |       2

PARTITION BYは、順位をつける対象を設定している。
ORDER BY　どんな列に、どんな順位をつけるか指定している。

PARTITION BYは指定しなくても良い

PARTITION BYで分類小分けをしない場合、テーブル全体のランキングになる。

SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
       RANK () OVER (ORDER BY hanbai_tanka) AS ranking
  FROM Shohin;


   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking
----------------+---------------+--------------+---------
 ボールペン     | 事務用品      |          100 |       1
 フォーク       | キッチン用品  |          500 |       2
 穴あけパンチ   | 事務用品      |          500 |       2
 おろしがね     | キッチン用品  |          880 |       4
 Tシャツ        | 衣服          |         1000 |       5
 包丁           | キッチン用品  |         3000 |       6
 カッターシャツ | 衣服          |         4000 |       7
 圧力鍋         | キッチン用品  |         6800 |       8

RANK関数　DENSE_RANK関数　ROW_NUMBER関数

  • RANK関数
    同順位が複数存在する場合、後続の順位が飛ぶ。
    例）1位が3つ存在する場合：1位　1位　1位　4位...
  • DENSE_RANK関数
    同順位が複数存在する場合、後続の順位は飛ばない。
    例）1位が3つ存在する場合：1位　1位　1位　2位...
  • ROW_NUMBER関数
    同順位が複数存在する場合、一意な連番を付与する。
    例）1位が3つ存在する場合：1位　2位　3位　4位...
SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
       RANK () OVER (ORDER BY hanbai_tanka) AS ranking,
       DENSE_RANK () OVER (ORDER BY hanbai_tanka) AS dense_ranking,
       ROW_NUMBER () OVER (ORDER BY hanbai_tanka) AS row_num
  FROM Shohin;


   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking | dense_ranking | row_num
----------------+---------------+--------------+---------+---------------+---------
 ボールペン     | 事務用品      |          100 |       1 |             1 |       1
 フォーク       | キッチン用品  |          500 |       2 |             2 |       2
 穴あけパンチ   | 事務用品      |          500 |       2 |             2 |       3
 おろしがね     | キッチン用品  |          880 |       4 |             3 |       4
 Tシャツ        | 衣服          |         1000 |       5 |             4 |       5
 包丁           | キッチン用品  |         3000 |       6 |             5 |       6
 カッターシャツ | 衣服          |         4000 |       7 |             6 |       7
 圧力鍋         | キッチン用品  |         6800 |       8 |             7 |       8

　28. GROUPING演算子

以下の3種類がある
- ROLLUP

小計と合計を一緒に求めることができる。
- CUBE
- GROUPING SETS

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0