AP データベース SQL基礎2

提供：yonewiki
2025年5月22日 (木) 16:10時点におけるYo-net (トーク | 投稿記録)による版

AP データベースへ戻る。

AP データベース SQL基礎へ戻る。


社員
社員ID 氏名 所属部署 給与
1 山田太郎 営業部 300000
2 佐藤花子 開発部 400000
3 鈴木一郎 営業部 350000
4 田中美咲 総務部 280000

　


ALTER TABLE

　表に対する様々な変更をする句です。ALTER TABLEに続けて使うキーワードで、

  • １．列の追加 (ADD COLUMN):
既存のテーブルに新しい列を追加します。
  • ２．列の削除 (DROP COLUMN):
既存のテーブルから列を削除します。
  • ３．列の変更 (ALTER COLUMN / MODIFY COLUMN):
既存の列のデータ型、NULL許容性、デフォルト値などを変更します。構文はデータベースシステムによって異なる場合があります(例: SQL Server, PostgreSQLはALTER COLUMN、MySQLはMODIFY COLUMN)。
  • ４．列名の変更 (RENAME COLUMN):
既存の列の名前を変更します。これもデータベースシステムによって構文が異なります (例: RENAME COLUMN old_name TO new_nameやCHANGE old_name new_name data_type)。
  • ５．制約の追加・削除 (ADD CONSTRAINT / DROP CONSTRAINT):
主キー、外部キー、UNIQUE、CHECKなどの制約を追加または削除します。
  • ６．テーブル名の変更 (RENAME TO):
テーブル自身の名前を変更します。


  • １．列の追加


sql 
ALTER TABLE 社員
ADD COLUMN 入社日 DATE;
sql


社員（入社日列追加後）
社員ID 氏名 所属部署 給与 入社日
1 山田太郎 営業部 300000 NULL
2 佐藤花子 開発部 400000 NULL
3 鈴木一郎 営業部 350000 NULL
4 田中美咲 総務部 280000 NULL


　このように列が追加されます。次は列を削除してもとに戻します。


２．列の変更

sql 
ALTER TABLE 社員
MODIFY COLUMN 給与 BIGINT NOT NULL;
sql


上記で、社員テーブルの給与列にBIGINT型を設定して、NOT NULL制約を指定できます。


  • ３．列の削除


sql 
ALTER TABLE 社員
DROP COLUMN 入社日;
sql


社員
社員ID 氏名 所属部署 給与
1 山田太郎 営業部 300000
2 佐藤花子 開発部 400000
3 鈴木一郎 営業部 350000
4 田中美咲 総務部 280000


４．制約の追加


sql 
ALTER TABLE 社員
ADD CONSTRAINT PK_社員ID PRIMARY KEY (社員ID);
sql


　上記のようにすると社員テーブルの社員ID列を主キー設定にし、一意な値を保持し、NOT NULL制約を適用できます。


外部キー制約の追加

外部キー制約を追加する場合は以下のようになります。


sql 
ALTER TABLE 社員
ADD CONSTRAINT FK_社員_部署 
FOREIGN KEY (所属部署)
REFERENCES 部署テーブル (部署名);
sql


外部キーを設定する列をFOREIGN KEY (所属部署)のように列指定し、外部キーとして参照するテーブルと列を、REFERENCES 部署テーブル (部署名)で指定するという記述方法になります。


５．制約の削除


sql 
ALTER TABLE 社員
DROP CONSTRAINT PK_社員ID;
sql


　上記のようにすると社員テーブルの社員IDの制約を削除することができます。


６．テーブル名の変更


sql 
ALTER TABLE 社員
RENAME TO 従業員;
sql


　上記のようにすると社員テーブルを従業員テーブルという名前に変更できます。


ON DELETE CASCADE・ON UPDATE CASCADE

　外部キーの削除変更があれば連動して、更新されたり、削除されたりする仕組みを形成します。


sql 
ALTER TABLE 社員
ADD CONSTRAINT FK_社員_部署 FOREIGN KEY (所属部署)
REFERENCES 部署テーブル (部署名)
ON DELETE CASCADE
ON UPDATE CASCADE;
sql


このように連動定義をしていると


sql 
DELETE FROM 部署テーブル
WHERE 部署名 = '総務部';
sql


のようにして総務部を削除すると、部署テーブルは


部署テーブル（総務部削除後）
部署名 拠点
営業部 東京
開発部 大阪
試作部 三重


となり


社員テーブル（総務部削除後の連動削除）
社員ID 氏名 所属部署 給与
1 山田太郎 営業部 300000
2 佐藤花子 開発部 400000
3 鈴木一郎 営業部 350000


　社員テーブルはこのように自動的に総務部の情報がなくなります。


　また営業部の名称を営業部から営業戦略部のように変更する以下のSQLを実行すると


sql 
UPDATE 部署テーブル
SET 部署名 = '営業戦略部'
WHERE 部署名 = '営業部';
sql


部署テーブルは以下のようになり


部署テーブル（営業部更新後）
部署名 拠点
営業戦略部 東京
開発部 大阪
試作部 三重


　となり、社員テーブルは


社員テーブル（営業部更新後の連動更新）
社員ID 氏名 所属部署 給与
1 山田太郎 営業戦略部 300000
2 佐藤花子 開発部 400000
3 鈴木一郎 営業戦略部 350000


　のように連動して値が変更されます。便利ですが、本当に置き換えてしまうことで解決する関係であるかどうかまで熟知して利用する必要があります。

　

CREATE USER

　SQLシステムにログインして表を扱うユーザを追加する操作です。


sql 
CREATE USER report_user WITH PASSWORD 'password123';
CREATE USER app_user WITH PASSWORD 'password456';
sql


　これでレポートを出力するためのユーザreport_user(任意のユーザ名)とアプリを動かすときにアプリがSQL問合せをするときの権限のユーザapp_userをそれぞれパスワードを指定して作成したことになります。

　

REVOKE

　権限をなくすための句です。

sql 
REVOKE ALL PRIVILEGES ON TABLE 社員 FROM report_user;
REVOKE ALL PRIVILEGES ON TABLE 部署テーブル FROM report_user;
REVOKE ALL PRIVILEGES ON TABLE 社員 FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE 部署テーブル FROM app_user;
sql


　すべての権限を各テーブルからはく奪するには上記のようなSQLを使います。

　

GRANT

　ユーザの権限と表の権限の関係を設定するものです。


sql 
GRANT SELECT ON TABLE 社員 TO report_user;
GRANT SELECT ON TABLE 部署テーブル TO report_user;
sql


　部署テーブルと社員テーブルにselectのような射影操作だけをreport_userに許可する権限設定となりました。


　これでreport_userは以下のようなSQLが実行できます。


sql 
SELECT * FROM 社員;
SELECT 部署名, 拠点 FROM 部署テーブル WHERE 部署名 = '営業部';
sql


　ただし、以下のようなSQLは実行できません。


sql 
INSERT INTO 社員 (社員ID, 氏名, 所属部署, 給与) VALUES (5, '新入社員', '開発部', 300000);
DELETE FROM 社員 WHERE 社員ID = 1;
UPDATE 社員 SET 給与 = 500000 WHERE 社員ID = 1;
sql


　app_userには、より様々なことができる権限を設定するとしたら、以下のようなSQLを実行します。


sql 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 社員 TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 部署テーブル TO app_user;
sql


　上記によりapp_userはSELECT、INSERT、UPDATE、DELETEを実行できる。


　社員テーブルの氏名、所属部署列のみ更新できる権限をapp_userに設定する場合は以下のとおりのSQLとなる。


sql 
GRANT UPDATE (氏名, 所属部署) ON TABLE 社員 TO app_user;
sql


　あらゆる権限を付与する場合には以下のようなALL PRIVILEGES句を使ったSQLを実行します。


sql 
GRANT ALL PRIVILEGES ON TABLE 社員 TO app_user;
GRANT ALL PRIVILEGES ON TABLE 部署テーブル TO app_user;
sql


　このようなGRANTによる権限設定のみをできるようにするには


sql 
GRANT ALL PRIVILEGES ON TABLE 社員 TO report_user WITH GRANT OPTION;
sql


　のようにWITH GRANT OPTIONという句をつけます。

　

CASE

　表の中で表示する値をIF文を使って、条件に応じた値を表示するのに使います。


sql 
SELECT
    氏名,
    給与,
    CASE
        WHEN 給与 >= 350000 THEN '高給与'
        WHEN 給与 >= 300000 THEN '中給与'
        ELSE '低給与'
    END AS 給与ランク
FROM
    社員
ORDER BY
    給与 DESC;
sql


　上記の例では、新たに給与ランクという列を表示用のために新たに作り(表自体に列を追加するわけではない)、既存の列、給与の列の値によって、表示する値を決めています。給与が35万円以上なら高給与、それ以外の値の中で30万以上なら中給与、それ以外なら低給与と表示します。


　結果として以下のような表の出力を得ます。


社員の給与ランク
氏名 給与 給与ランク
佐藤花子 400000 高給与
鈴木一郎 350000 高給与
山田太郎 300000 中給与
田中美咲 280000 低給与


　SELECT句の列の指定のところで、CASE WHEN 条件 THEN 表示値 ELSE 表示値 END AS 列名のような指定をします。列指定に複雑なキーワードを設定していると思えば良いです。

　

ウィンドウ関数

　列の表示に関数を指定することで、GROUP BY で設定した範囲で集計したり OVER (PARTITION BY)句で設定したグループごとで集計したりすることができます。この時、列の指定値として記述する関数をウィンドウ関数と呼んでいます。さまざまなウィンドウ関数サンプルを示すために、これまでの社員テーブルを以下のように拡張したものとして説明したいと思います。


拡張された社員テーブル
社員ID 氏名 所属部署 給与 入社年度 評価点
1 山田太郎 営業部 300000 2020 85
2 佐藤花子 開発部 400000 2018 92
3 鈴木一郎 営業部 350000 2019 88
4 田中美咲 総務部 280000 2021 78
5 高橋健太 開発部 420000 2019 95
6 伊藤美穂 営業部 300000 2020 80
7 小林大輔 開発部 380000 2020 90

　

AVG、SUM、OVER句を使った例

　平均や合計を算出するウィンドウ関数です。集計グループはOVER句の中にPARTITION BY句を使って示すことで各行に全体やある列の何かの値ごとグループ化された値について関数で集計した結果を各行に示すことができます。


sql 
SELECT
    氏名,
    所属部署,
    給与,
    AVG(給与) OVER (PARTITION BY 所属部署) AS 部署平均給与,
    SUM(給与) OVER (PARTITION BY 所属部署) AS 部署給与総額,
    AVG(給与) OVER () AS 全体平均給与, -- PARTITION BY なしは全体に対して
    SUM(給与) OVER () AS 全体給与総額   -- PARTITION BY なしは全体に対して
FROM
    社員
ORDER BY
    所属部署, 給与 DESC;
sql


　上記のようなSQLを実行すると、以下のような表を得ることができます。


社員と集計ウィンドウ関数の結果
氏名 所属部署 給与 部署平均給与 部署給与総額 全体平均給与 全体給与総額
高橋健太 開発部 420000 400000.00 1200000 352857.14 2470000
佐藤花子 開発部 400000 400000.00 1200000 352857.14 2470000
小林大輔 開発部 380000 400000.00 1200000 352857.14 2470000
鈴木一郎 営業部 350000 316666.67 950000 352857.14 2470000
山田太郎 営業部 300000 316666.67 950000 352857.14 2470000
伊藤美穂 営業部 300000 316666.67 950000 352857.14 2470000
田中美咲 総務部 280000 280000.00 280000 352857.14 2470000

　

AVG、SUM、OVER句を使わない例

　OVER句を使わない場合は、1行にまとまった結果を得ることができます。


sql 
SELECT
    SUM(給与) AS 全体給与総額,
    AVG(給与) AS 全体平均給与,
    COUNT(社員ID) AS 総社員数
FROM
    社員;
sql


会社全体の集計結果
全体給与総額 全体平均給与 総社員数
2470000 352857.14 7


　OVER句を使わない場合はこのような集計表示になります。1行に集約しようとします。したがって、ある列ではOVER句をつかっているのにある列ではOVER句の指定がない場合には1行に集約しようとしているのに、一行に集約しないものを使おうとしているということで矛盾が生じ、SQLの実行はエラーになります。

　

ROW_NUMBER, RANK, DENSE_RANK

  • ROW_NUMBER：順序内の位置に応じて一意の連番を付与します。
  • RANK：同じ値を持つ行には同じ順位を付与し、次の順位は同順位の数だけスキップされます。スポーツ競技の順位付けと同じ仕組みです。
  • DENSE_RANK：同じ値を持つ行には同じ順位を付与しますが、次の順位はスキップされずに連続します。すべての順位を使います。


sql 
SELECT
    氏名,
    所属部署,
    給与,
    ROW_NUMBER() OVER (PARTITION BY 所属部署 ORDER BY 給与 DESC) AS 部署内順位_ROW_NUMBER,
    RANK() OVER (PARTITION BY 所属部署 ORDER BY 給与 DESC) AS 部署内順位_RANK,
    DENSE_RANK() OVER (PARTITION BY 所属部署 ORDER BY 給与 DESC) AS 部署内順位_DENSE_RANK
FROM
    社員
ORDER BY
    所属部署, 給与 DESC;
sql


社員の部署内順位
氏名 所属部署 給与 部署内順位_ROW_NUMBER 部署内順位_RANK 部署内順位_DENSE_RANK
高橋健太 開発部 420000 1 1 1
佐藤花子 開発部 400000 2 2 2
小林大輔 開発部 380000 3 3 3
鈴木一郎 営業部 350000 1 1 1
山田太郎 営業部 300000 2 2 2
伊藤美穂 営業部 300000 3 2 2
田中美咲 総務部 280000 1 1 1

　

LAG, LEAD

LAG：引数の1番目はn前の対象列名、引数の2番目はn前のn、引数の3番目は対象が無い場合の値を指定します。 LEAD：引数の1番目はn後の対象列名、引数の2番目はn後のn、引数の3番目は対象が無い場合の値を指定します。


sql 
SELECT
    氏名,
    所属部署,
    給与,
    LAG(給与, 1, 0) OVER (PARTITION BY 所属部署 ORDER BY 入社年度) AS 前の社員の給与,
    LEAD(給与, 1, 0) OVER (PARTITION BY 所属部署 ORDER BY 入社年度) AS 次の社員の給与
FROM
    社員
ORDER BY
    所属部署, 入社年度;
sql


社員と前後給与
氏名 所属部署 給与 前の社員の給与 次の社員の給与
佐藤花子 開発部 400000 0 420000
高橋健太 開発部 420000 400000 380000
小林大輔 開発部 380000 420000 0
鈴木一郎 営業部 350000 0 300000
山田太郎 営業部 300000 350000 300000
伊藤美穂 営業部 300000 300000 0
田中美咲 総務部 280000 0 0

　

NTILE, MIN, MAX, COUNT

  • NTILE：ランキングを指定した数字のグループに分ける関数です。
  • MIN：最小値を返す関数です。
  • MAX：最大値を返す関数です。
  • COUNT：指定した列名の行数を返す関数です。単にレコード総数を得たい場合は列名の指定を省略した * でもよいです。ただし、列名を指定した場合はNULLであったレコードを省いてカウントしてくれますので、違いはあります。


sql 
SELECT
    氏名,
    所属部署,
    給与,
    評価点,
    NTILE(3) OVER (ORDER BY 給与 DESC) AS 全体給与上位3グループ, -- 全体を3グループに分割
    MIN(給与) OVER (PARTITION BY 所属部署) AS 部署内最低給与,
    MAX(給与) OVER (PARTITION BY 所属部署) AS 部署内最高給与,
    COUNT(社員ID) OVER (PARTITION BY 所属部署) AS 部署社員数,
    COUNT(社員ID) OVER () AS 全体社員数
FROM
    社員
ORDER BY
    全体給与上位3グループ, 所属部署, 給与 DESC;
sql


社員と各種ウィンドウ関数の結果
氏名 所属部署 給与 評価点 全体給与上位3グループ 部署内最低給与 部署内最高給与 部署社員数 全体社員数
高橋健太 開発部 420000 95 1 380000 420000 3 7
佐藤花子 開発部 400000 92 1 380000 420000 3 7
鈴木一郎 営業部 350000 88 1 300000 350000 3 7
小林大輔 開発部 380000 90 2 380000 420000 3 7
山田太郎 営業部 300000 85 2 300000 350000 3 7
伊藤美穂 営業部 300000 80 3 300000 350000 3 7
田中美咲 総務部 280000 78 3 280000 280000 1 7

　

WINDOW

　ウィンドウ関数を使っていた時にグループ化していたOVER句の中のPARTITION句をまとめて書くような手法のためにあるものです。


sql 
SELECT
    氏名,
    所属部署,
    給与,
    RANK() OVER w_dept_salary_desc AS 部署内給与ランク,
    MIN(給与) OVER w_dept AS 部署内最低給与,
    MAX(給与) OVER w_dept AS 部署内最高給与,
    COUNT(社員ID) OVER w_dept AS 部署社員数
FROM
    社員
WINDOW
    w_dept AS (PARTITION BY 所属部署), -- 部署ごとのウィンドウ定義
    w_dept_salary_desc AS (PARTITION BY 所属部署 ORDER BY 給与 DESC) -- 部署ごと、給与降順のウィンドウ定義
ORDER BY
    所属部署, 給与 DESC;
sql


WINDOW句を使った社員の分析
氏名 所属部署 給与 部署内給与ランク 部署内最低給与 部署内最高給与 部署社員数
高橋健太 開発部 420000 1 380000 420000 3
佐藤花子 開発部 400000 2 380000 420000 3
小林大輔 開発部 380000 3 380000 420000 3
鈴木一郎 営業部 350000 1 300000 350000 3
山田太郎 営業部 300000 2 300000 350000 3
伊藤美穂 営業部 300000 2 300000 350000 3
田中美咲 総務部 280000 1 280000 280000 1

　

COALESCE 関数

　表示結果がNULL値のときの代わりの値を指定できる関数です。


　以下のようなテーブルがあったとして


拡張された連絡先テーブル
社員ID 氏名 電話番号 メールアドレス 住所
1 山田太郎 090-1111-2222 yamada@example.com 東京都
2 佐藤花子 080-3333-4444 sato@example.com NULL
3 鈴木一郎 NULL suzuki@example.com 神奈川県
4 田中美咲 070-5555-6666 NULL NULL
5 高橋健太 NULL NULL 大阪府
6 伊藤美穂 NULL NULL NULL


sql 
SELECT
    社員ID,
    氏名,
    電話番号,
    メールアドレス,
    住所,
    COALESCE(電話番号, メールアドレス, 住所, '不明') AS 主な連絡先
FROM
    連絡先;
sql


連絡先とCOALESCE関数の結果
社員ID 氏名 電話番号 メールアドレス 住所 主な連絡先
1 山田太郎 090-1111-2222 yamada@example.com 東京都 090-1111-2222
2 佐藤花子 080-3333-4444 sato@example.com NULL 080-3333-4444
3 鈴木一郎 NULL suzuki@example.com 神奈川県 suzuki@example.com
4 田中美咲 070-5555-6666 NULL NULL 070-5555-6666
5 高橋健太 NULL NULL 大阪府 大阪府
6 伊藤美穂 NULL NULL NULL 不明

　

AP データベース SQL基礎へ戻る。

AP データベースへ戻る。

「https://wiki.yo-net.jp/index.php?title=AP_データベース_SQL基礎2&oldid=11559」から取得