標準 SQL 集

SQL Server にて扱える標準 SQL を中心にまとめた自分用メモ。
他の RDBMS 向けの内容も一部混在しているため注意。

kenkenpa198/mssql-with-docker にて管理していたファイルを当サイト上へ引っ越したもの。

リンク

標準 SQL 集 ← イマココ！
自作 SQL 集

1. 環境関連
- 1.1. SQL Server 環境のバージョン・エディションを確認する
- 1.2. 環境で使用されている文字コードを調べる
2. データベース操作
3. テーブル操作
4. ビュー
- 4.1. ビューを作成する
- 4.2. ビューを削除する
5. トランザクション
- 5.1. トランザクションの作成
6. レコード操作
7. 検索
8. 参考文献

1. 環境関連

1.1. SQL Server 環境のバージョン・エディションを確認する

参考: SQL Server - sqlcmd でバージョンとエディションを確認する方法

SELECT @@VERSION;

/***************************
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
    Jan 12 2022 22:30:08
    Copyright (C) 2019 Microsoft Corporation
    Express Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>
***************************/

1.2. 環境で使用されている文字コードを調べる

参考: SQLServer日本語コードの確認方法 - Qiita

-- 先に確認したい列の照合順序を確認しておく（参考ページを参照）

SELECT COLLATIONPROPERTY('Japanese_CI_AS', 'CodePage');

-- 932   : Shift-JIS
-- 1200  : UTF-16
-- 65001 : UTF-8
-- 20932 : EUC-JP

-- 確認できるのは非 UNICODE 型の文字列型（char, varchar, text）に使用される文字コードのみ。
-- UNICODE 型の文字列型（nchar, nvarchar, ntext）はそもそも UTF-16 固定となる。

2. データベース操作

2.1. データベース一覧を取得する

SELECT name FROM sys.databases;

2.2. データベースを作成する

CREATE DATABASE <database-name>;

2.3. 使用するデータベースを設定する

USE <database-name>;

2.4. 使用しているデータベースを確認する

SELECT DB_NAME();

3. テーブル操作

3.1. テーブル一覧を表示する

SELECT name FROM sysobjects WHERE xtype = 'U';

3.2. テーブルの定義を表示する

すべての情報を出力する。
自作 SQL 集 > 2. カラムの情報を見やすい形式で表示するも参考に。

SELECT * FROM sys.columns WHERE object_id = object_id('Members');

3.3. テーブルを作成する

CREATE TABLE Members (
--  列名          データ型     この列の制約
    member_id     CHAR(4)      NOT NULL,
    member_name   VARCHAR(100) NOT NULL,
    sex           VARCHAR(32)  NOT NULL,
    age           INTEGER      ,
    foo_num       INTEGER      ,
    date_of_birth DATE         ,

--  このテーブルの制約
    PRIMARY KEY (member_id) -- 列の制約に「PRIMARY KEY」と付けてもよい
);

3.4. テーブルを削除する

DROP TABLE Members;

3.5. テーブルの定義を変更する

3.5.1. 列を追加する場合

ALTER TABLE <テーブル名> ADD COLUMN <列の定義>;

-- [DB2][PostgreSQL][MySQL]
ALTER TABLE Members ADD COLUMN hobby VARCHAR(128);

-- [SQL Server][Oracle] COLUMN をつけない
ALTER TABLE Members ADD hobby VARCHAR(128);

-- [Oracle] () をつけて複数の定義を一度に追加できる
ALTER TABLE Members ADD (hobby VARCHAR(128), pet VARCHAR(128));

3.5.2. 列を削除する場合

ALTER TABLE <テーブル名> DROP <列名>

-- [SQL Server][DB2][PostgreSQL][MySQL]
ALTER TABLE Members DROP COLUMN hobby;

-- [Oracle] COLUMN を省略できる。() をつけて複数の列を一度に削除できる
ALTER TABLE Members DROP (hobby, pet);

3.5.3. 列の設定を変更する場合

ALTER TABLE <テーブル名> ALTER COLUMN <列の定義>;

-- 列の定義を変更
ALTER TABLE Members ALTER COLUMN member_id INTEGER NOT NULL;
ALTER TABLE Members ALTER COLUMN first_name NVARCHAR(10);

3.5.4. 列へ主キーを付与する場合

ALTER TABLE Members ADD PRIMARY KEY (member_id, first_name);

4. ビュー

4.1. ビューを作成する

CREATE VIEW
    MembersSexCnt (
        -- ビューの列名（並び順が SELECT 文の結果と対応）
        sex,
        sex_count
    )
AS
    -- ビュー定義の本体（SELECT 文）
    SELECT
        sex,
        COUNT(*)
    FROM
        Members
    GROUP BY
        sex
;

4.2. ビューを削除する

DROP VIEW MembersSexCnt;

5. トランザクション

5.1. トランザクションの作成

-- [SQL Server][PostgreSQL]
BEGIN TRANSACTION
    ... DML 文（INSERT / UPDATE / DELETE 文） ...
COMMIT -- または ROLLBACK

-- [MySQL]
START TRANSACTION
    ... DML 文 ...
COMMIT -- または ROLLBACK

-- [Oracle][DB2]
ない

-- 例（SQL Server）
BEGIN TRANSACTION

    -- カッターシャツの販売単価を 1000 円値引き
    UPDATE Shohin
       SET hanbai_tanka = hanbai_tanka - 1000
     WHERE shohin_mei = 'カッターシャツ';

    -- カッターシャツの販売単価を 1000 円値引き
    UPDATE Shohin
       SET hanbai_tanka = hanbai_tanka + 1000
     WHERE shohin_mei = 'Tシャツ';

COMMIT

6. レコード操作

6.1. レコードを追加する

6.1.1. 基本

-- [SQL Server][PostgreSQL]
BEGIN TRANSACTION; -- 行の追加を開始する
    INSERT INTO <テーブル名> (列1, 列2, 列3, ...) VALUES (値1, 値2, 値3, ...);
COMMIT;            -- 行の追加を確定する

※ MySQL の場合、BEGIN TRANSACTION; を下記に変更する。

START TRANSACTION;

※ Oracle, DB2 の場合は BEGIN TRANSACTION; が必要ないので削除する。

6.1.2. 列リストの省略

テーブルの前列に対して INSERT を行う場合は列リストを省略できる。

BEGIN TRANSACTION;

--                          member_id  member_name  sex       age  foo_num  date_of_birth
INSERT INTO Members VALUES ('0001',    'Modane',    'female', 18,  111,     '2019-09-08');
INSERT INTO Members VALUES ('0002',    'Ryunosuke', 'male',   21,  NULL,    '2017-09-29');
INSERT INTO Members VALUES ('0003',    'Tsukune'  , 'female', 19,  222,     NULL);

COMMIT;

6.1.3. SELECT 句での抽出結果から INSERT する

INSERT INTO Members (
    member_id,
    member_name,
    date_of_birth
)
SELECT
    member_id,
    member_name,
    date_of_birth
FROM
    Foo_Table
;

-- すべての列へ値を入れる際は Members のカラム指定 (a, b, c, ...) を省略できる
INSERT INTO Members
SELECT
    member_id,
    member_name,
    date_of_birth
FROM
    Foo_Table
;

-- WHERE 句、GROUP BY 句などを追加することで集計結果を挿入することもできる。

6.2. レコードを削除する

-- レコードを全行削除
DELETE FROM Members;

-- WHERE 句で削除対象の行を指定できる（探索型 DELETE）
DELETE
FROM
    Members
WHERE
    age <= 20
;

6.3. レコードを更新する

-- 指定したカラムを全行更新
UPDATE
    Members
SET
    foo_num = 15
;

-- WHERE 句で更新対象の行を指定できる（探索型 UPDATE）
UPDATE
    Members
SET
    foo_num = 15
WHERE
    member_name = 'Modane'
;

-- 計算した結果で更新することも可能（例 : 指定列の値を10倍する）
UPDATE
    Members
SET
    foo_num = foo_num * 10
;

-- NULL で更新する（NULL クリア）
UPDATE
    Members
SET
    foo_num = NULL
;

-- 複数の列を更新する場合
UPDATE
    Members
SET
    age = age / 2,
    foo_num = foo_num * 10
WHERE
    sex = 'female'
;

7. 検索

7.1. 基本

SELECT
    member_id,
    member_name
FROM
    Members
WHERE
    age <= 20
;

7.2. カッコを使った条件式

-- 「商品分類が事務用品」かつ「登録日が2009年9月11日または2009年9月20日」
WHERE
    shohin_bunrui = '事務用品'
    AND (
        torokubi = '2009-09-11'
        OR torokubi = '2029-09-20'
    )
;

7.3. NULL を除外して行数を数える

COUNT 関数は * を引数に取ることができる。
また、引数へ * を渡した場合とカラム名を渡した場合によって動作が異なる。

-- NULL を含める場合は COUNT 関数の引数へ * を渡す。
SELECT
    COUNT(*) AS count
FROM
    Members
;

-- count : 3

-- NULL を含めない場合は引数へ列名を渡す。
SELECT
    COUNT(date_of_birth) AS count
FROM
    Members
;

-- count : 2

7.4. 相関サブクエリ

外側のクエリとサブクエリの特定のレコード同士を比較する際の書き方。

外側のクエリとサブクエリのキーとするカラムを縛り（バインドし）することで、
比較する値をひとつにして（スカラ・サブクエリ）正常な比較ができるようにする。

-- [SQL Server][DB2][PostgreSQL][MySQL]
-- [Oracle] では AS を削除する
SELECT
    shohin_bunrui,
    shohin_mei,
    hanbai_tanka
FROM
    Shohin AS S1
WHERE
    hanbai_tanka > (
        SELECT
            AVG(hanbai_tanka)
        FROM
            Shohin AS S2
        WHERE
            -- 各商品の販売単価と平均単価の比較を同じ商品分類の中で行う！
            S1.shohin_bunrui = S2.shohin_bunrui
        GROUP BY
            shohin_bunrui

    )
;

-- 結合条件を外側のクエリに書いてしまうと動作しない。
-- 外側のクエリの実行時点でサブクエリのテーブル S2 は消滅しているため（スコープのルール）。

7.5. EXISTS

ある条件に合致するレコードの存在有無を調べる。
IN で代用できるケースは多いが、パフォーマンス性に優れる。

-- EXISTS
SELECT
    *
FROM
    Members m
WHERE
    EXISTS (
        -- WHERE 句の条件を満たすレコードが存在した場合に真を返す
        SELECT
            * -- なんでもよい。1 でもよい
        FROM
            MembersSexCnt sc
        WHERE
            m.sex = sc.sex -- キーとして結合するカラムを指定
            AND sex_count >= 2 -- 条件を指定する場合は AND で繋げる
    )
;

7.6. ウィンドウ関数

別名 OLAP 関数（OnLine Analytical Processing）。
データを集約せずにデータ分析を行える。

基本は下記のような感じ。

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

-- [] 内は省略できる。
-- <ウィンドウ関数> には下記2種の関数を指定できる。
--   1. 集約関数（SUM, AVG, COUNT など）
--   2. ウィンドウ専用関数（RANK, DENSE_RANK, ROW_NUMBER など）

例）

-- 商品分類別に、販売単価の安い順で並べたランキング表を作る
SELECT
    shohin_mei,
    shohin_bunrui,
    hanbai_tanka,
    RANK() OVER (PARTITION BY shohin_bunrui ORDER BY hanbai_tanka) AS ranking
FROM
    Shohin;
-- ORDER BY     -- 結果で並べたいときは最後に ORDER BY 句をつける
--     ranking

↓

shohin_mei | shohin_bunrui | hanbai_tanka | ranking
---------- + ------------- + ------------ + --------
フォーク   | キッチン用品  |          500 |        1
おろしがね | キッチン用品  |          880 |        2
Tシャツ    | 衣服          |         1000 |        3
ボールペン | 事務用品      |          500 |        1

7.7. GROUPING

coming soon…（いつまでコレやねん）

リンク

目次