AP データベース SQL基礎2
AP データベースへ戻る。
AP データベース SQL基礎へ戻る。
応用情報処理技術者試験の出題範囲のSQL技術についてまとめている2ページ目の記事です。いきなりここに来てしまった人は上部リンクから戻れます。
| 社員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):
- テーブル自身の名前を変更します。
- １．列の追加
ALTER TABLE 社員
ADD COLUMN 入社日 DATE;
| 社員ID | 氏名 | 所属部署 | 給与 | 入社日 |
|---|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 | NULL |
| 2 | 佐藤花子 | 開発部 | 400000 | NULL |
| 3 | 鈴木一郎 | 営業部 | 350000 | NULL |
| 4 | 田中美咲 | 総務部 | 280000 | NULL |
このように列が追加されます。次は列を削除してもとに戻します。
２．列の変更
ALTER TABLE 社員
MODIFY COLUMN 給与 BIGINT NOT NULL;
上記で、社員テーブルの給与列にBIGINT型を設定して、NOT NULL制約を指定できます。
- ３．列の削除
ALTER TABLE 社員
DROP COLUMN 入社日;
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
| 4 | 田中美咲 | 総務部 | 280000 |
４．制約の追加
ALTER TABLE 社員
ADD CONSTRAINT PK_社員ID PRIMARY KEY (社員ID);
上記のようにすると社員テーブルの社員ID列を主キー設定にし、一意な値を保持し、NOT NULL制約を適用できます。
外部キー制約の追加
外部キー制約を追加する場合は以下のようになります。
ALTER TABLE 社員
ADD CONSTRAINT FK_社員_部署
FOREIGN KEY (所属部署)
REFERENCES 部署テーブル (部署名);
外部キーを設定する列をFOREIGN KEY (所属部署)のように列指定し、外部キーとして参照するテーブルと列を、REFERENCES 部署テーブル (部署名)で指定するという記述方法になります。
５．制約の削除
ALTER TABLE 社員
DROP CONSTRAINT PK_社員ID;
上記のようにすると社員テーブルの社員IDの制約を削除することができます。
６．テーブル名の変更
ALTER TABLE 社員
RENAME TO 従業員;
上記のようにすると社員テーブルを従業員テーブルという名前に変更できます。
ON DELETE CASCADE・ON UPDATE CASCADE
外部キーの削除変更があれば連動して、更新されたり、削除されたりする仕組みを形成します。
ALTER TABLE 社員
ADD CONSTRAINT FK_社員_部署 FOREIGN KEY (所属部署)
REFERENCES 部署テーブル (部署名)
ON DELETE CASCADE
ON UPDATE CASCADE;
このように連動定義をしていると
DELETE FROM 部署テーブル
WHERE 部署名 = '総務部';
のようにして総務部を削除すると、部署テーブルは
| 部署名 | 拠点 |
|---|---|
| 営業部 | 東京 |
| 開発部 | 大阪 |
| 試作部 | 三重 |
となり
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
社員テーブルはこのように自動的に総務部の情報がなくなります。
また営業部の名称を営業部から営業戦略部のように変更する以下のSQLを実行すると
UPDATE 部署テーブル
SET 部署名 = '営業戦略部'
WHERE 部署名 = '営業部';
部署テーブルは以下のようになり
| 部署名 | 拠点 |
|---|---|
| 営業戦略部 | 東京 |
| 開発部 | 大阪 |
| 試作部 | 三重 |
となり、社員テーブルは
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業戦略部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業戦略部 | 350000 |
のように連動して値が変更されます。便利ですが、本当に置き換えてしまうことで解決する関係であるかどうかまで熟知して利用する必要があります。
CREATE USER
SQLシステムにログインして表を扱うユーザを追加する操作です。
CREATE USER report_user WITH PASSWORD 'password123';
CREATE USER app_user WITH PASSWORD 'password456';
これでレポートを出力するためのユーザreport_user(任意のユーザ名)とアプリを動かすときにアプリがSQL問合せをするときの権限のユーザapp_userをそれぞれパスワードを指定して作成したことになります。
REVOKE
権限をなくすための句です。
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を使います。
GRANT
ユーザの権限と表の権限の関係を設定するものです。
GRANT SELECT ON TABLE 社員 TO report_user;
GRANT SELECT ON TABLE 部署テーブル TO report_user;
部署テーブルと社員テーブルにselectのような射影操作だけをreport_userに許可する権限設定となりました。
これでreport_userは以下のようなSQLが実行できます。
SELECT * FROM 社員;
SELECT 部署名, 拠点 FROM 部署テーブル WHERE 部署名 = '営業部';
ただし、以下のようなSQLは実行できません。
INSERT INTO 社員 (社員ID, 氏名, 所属部署, 給与) VALUES (5, '新入社員', '開発部', 300000);
DELETE FROM 社員 WHERE 社員ID = 1;
UPDATE 社員 SET 給与 = 500000 WHERE 社員ID = 1;
app_userには、より様々なことができる権限を設定するとしたら、以下のようなSQLを実行します。
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 社員 TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 部署テーブル TO app_user;
上記によりapp_userはSELECT、INSERT、UPDATE、DELETEを実行できる。
社員テーブルの氏名、所属部署列のみ更新できる権限をapp_userに設定する場合は以下のとおりのSQLとなる。
GRANT UPDATE (氏名, 所属部署) ON TABLE 社員 TO app_user;
あらゆる権限を付与する場合には以下のようなALL PRIVILEGES句を使ったSQLを実行します。
GRANT ALL PRIVILEGES ON TABLE 社員 TO app_user;
GRANT ALL PRIVILEGES ON TABLE 部署テーブル TO app_user;
このようなGRANTによる権限設定のみをできるようにするには
GRANT ALL PRIVILEGES ON TABLE 社員 TO report_user WITH GRANT OPTION;
のようにWITH GRANT OPTIONという句をつけます。
CASE
表の中で表示する値をIF文を使って、条件に応じた値を表示するのに使います。
SELECT
氏名,
給与,
CASE
WHEN 給与 >= 350000 THEN '高給与'
WHEN 給与 >= 300000 THEN '中給与'
ELSE '低給与'
END AS 給与ランク
FROM
社員
ORDER BY
給与 DESC;
上記の例では、新たに給与ランクという列を表示用のために新たに作り(表自体に列を追加するわけではない)、既存の列、給与の列の値によって、表示する値を決めています。給与が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句を使って示すことで各行に全体やある列の何かの値ごとグループ化された値について関数で集計した結果を各行に示すことができます。
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を実行すると、以下のような表を得ることができます。
| 氏名 | 所属部署 | 給与 | 部署平均給与 | 部署給与総額 | 全体平均給与 | 全体給与総額 |
|---|---|---|---|---|---|---|
| 高橋健太 | 開発部 | 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行にまとまった結果を得ることができます。
SELECT
SUM(給与) AS 全体給与総額,
AVG(給与) AS 全体平均給与,
COUNT(社員ID) AS 総社員数
FROM
社員;
| 全体給与総額 | 全体平均給与 | 総社員数 |
|---|---|---|
| 2470000 | 352857.14 | 7 |
OVER句を使わない場合はこのような集計表示になります。1行に集約しようとします。したがって、ある列ではOVER句をつかっているのにある列ではOVER句の指定がない場合には1行に集約しようとしているのに、一行に集約しないものを使おうとしているということで矛盾が生じ、SQLの実行はエラーになります。
ROW_NUMBER, RANK, DENSE_RANK
- ROW_NUMBER：順序内の位置に応じて一意の連番を付与します。
- RANK：同じ値を持つ行には同じ順位を付与し、次の順位は同順位の数だけスキップされます。スポーツ競技の順位付けと同じ仕組みです。
- DENSE_RANK：同じ値を持つ行には同じ順位を付与しますが、次の順位はスキップされずに連続します。すべての順位を使います。
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;
| 氏名 | 所属部署 | 給与 | 部署内順位_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番目は対象が無い場合の値を指定します。
SELECT
氏名,
所属部署,
給与,
LAG(給与, 1, 0) OVER (PARTITION BY 所属部署 ORDER BY 入社年度) AS 前の社員の給与,
LEAD(給与, 1, 0) OVER (PARTITION BY 所属部署 ORDER BY 入社年度) AS 次の社員の給与
FROM
社員
ORDER BY
所属部署, 入社年度;
| 氏名 | 所属部署 | 給与 | 前の社員の給与 | 次の社員の給与 |
|---|---|---|---|---|
| 佐藤花子 | 開発部 | 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であったレコードを省いてカウントしてくれますので、違いはあります。
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;
| 氏名 | 所属部署 | 給与 | 評価点 | 全体給与上位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句をまとめて書くような手法のためにあるものです。
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;
| 氏名 | 所属部署 | 給与 | 部署内給与ランク | 部署内最低給与 | 部署内最高給与 | 部署社員数 |
|---|---|---|---|---|---|---|
| 高橋健太 | 開発部 | 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 |
SELECT
社員ID,
氏名,
電話番号,
メールアドレス,
住所,
COALESCE(電話番号, メールアドレス, 住所, '不明') AS 主な連絡先
FROM
連絡先;
| 社員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 | 不明 |
SUBSTRING, INSTR, LENGTH, UPPER, LOWER
- SUBSTRING：第一引数は抽出列名、第二引数は抽出開始文字位置、第三引数は抽出終了文字位置
- INSTR：第一引数は検索列名、第二引数は検索文字で列名の中の値で検索された最初の文字位置を前方からの文字位置を返す
- LENGTH：引数に列名を与えると列値の文字列の文字数を返す。
- UPPER：引数に列名を与えると列値の中に含まれるアルファベット文字をすべて大文字に変換する。
- LOWER：引数に列名を与えると列値の中に含まれるアルファベット文字をすべて大文字に変換する。
| 社員ID | 氏名 | メールアドレス |
|---|---|---|
| 1 | 山田太郎 | yamada@example.com |
| 2 | 佐藤花子 | sato@example.com |
| 3 | 鈴木一郎 | suzuki@example.com |
| 4 | 田中美咲 | tanaka.misaki@example.com |
| 5 | 高橋健太 | takahashi@example.com |
WITH 初期データ AS (
SELECT
氏名,
メールアドレス,
-- ここで最初の計算を行い、エイリアスを設定
SUBSTRING(メールアドレス, 1, INSTR(メールアドレス, '@') - 1) AS メールアドレス_ローカルパート,
LENGTH(氏名) AS 氏名_文字数
FROM
社員連絡先
)
SELECT
氏名,
メールアドレス,
メールアドレス_ローカルパート,
氏名_文字数,
-- CTEで作成した'メールアドレス_ローカルパート'をここで利用
UPPER(メールアドレス_ローカルパート) AS メールアドレス_ローカルパート大文字,
LOWER(メールアドレス) AS メールアドレス_小文字化
FROM
初期データ;
Common Table Expression(共通テーブル式)を作るWITH句を使って一度メールアドレス_ローカルパートを計算させると、その後のSELECT句でメールアドレス_ローカルパート列を使って大文字変換関数UPPERの引数にできます。
| 氏名 | メールアドレス | メールアドレス_ローカルパート | 氏名_文字数 | メールアドレス_ローカルパート大文字 | メールアドレス_小文字化 |
|---|---|---|---|---|---|
| 山田太郎 | yamada@example.com | yamada | 4 | YAMADA | yamada@example.com |
| 佐藤花子 | sato@example.com | sato | 4 | SATO | sato@example.com |
| 鈴木一郎 | suzuki@example.com | suzuki | 4 | SUZUKI | suzuki@example.com |
| 田中美咲 | tanaka.misaki@example.com | tanaka.misaki | 4 | TANAKA.MISAKI | tanaka.misaki@example.com |
| 高橋健太 | takahashi@example.com | takahashi | 4 | TAKAHASHI | takahashi@example.com |
文字列関数
CONCAT
- CONCAT(str1, str2, ...): 文字列を結合。
TRIM, LTRIM, RTRIM
- TRIM(str) / LTRIM(str) / RTRIM(str): 空白文字を削除（全体、左、右）。
REPLACE
- REPLACE(str, from_str, to_str): 文字列内の特定のサブ文字列を置換。
LEFT, RIGHT
- LEFT(str, len) / RIGHT(str, len): 文字列の左/右から指定文字数分抽出。
LPAD, RPAD
- LPAD(str, len, pad_str) / RPAD(str, len, pad_str): 文字列を指定した長さまで埋める（左/右）。
数値関数
ROUND
- ROUND(num, decimal_places): 数値を四捨五入。
FLOOR
- FLOOR(num): 数値の小数点以下を切り捨て。
CEIL, CEILING
- CEIL(num) / CEILING(num): 数値の小数点以下を切り上げ。
ABS
- ABS(num): 数値の絶対値。
POWER
- POWER(base, exponent): べき乗。
SQRT
- SQRT(num): 平方根。
RAND, RANDOM
- RAND() / RANDOM(): 乱数生成。
日付/時刻関数
NOW, CURRENT_TIMESTAMP
- NOW() / CURRENT_TIMESTAMP: 現在の日付と時刻。
CURDATE, CURRENT_DATE
- CURDATE() / CURRENT_DATE: 現在の日付。
YEAR, MONTH, DAY
- YEAR(date) / MONTH(date) / DAY(date): 日付から年/月/日を抽出。
DATEDIFF, DATE_PART
- DATEDIFF(unit, start_date, end_date) / DATE_PART('unit', date): 2つの日付間の差、または日付の一部を抽出。
DATE_ADD, DATE_SUB
- DATE_ADD(date, INTERVAL value unit) / DATE_SUB(date, INTERVAL value unit): 日付に期間を加算/減算。
FORMAT
- FORMAT(value, format): 値を指定した形式で整形（特に日付）。
変換関数
CAST, CONVERT
- CAST(expression AS type) / CONVERT(type, expression): データ型を変換。
TO_CHAR, FORMAT
- TO_CHAR(value, format) (Oracle/PostgreSQL) / FORMAT(value, format) (SQL Server): 値を特定の書式で文字列に変換。
TO_DATE
- TO_DATE(string, format) (Oracle/PostgreSQL): 文字列を日付型に変換。
NULL関連関数
IFNULL, ISNULL
- IFNULL(expr1, expr2) (MySQL) / ISNULL(expr1, expr2) (SQL Server): expr1がNULLならexpr2を返す。COALESCEの2引数版。
NULLIF
- NULLIF(expr1, expr2): expr1とexpr2が同じならNULLを返し、そうでなければexpr1を返す。
CREATE INDEX
社員数が10万人とかになってくるとインデックスというものを作るとインデックスを作った特定列の検索の速度が速くなります。例えば社員テーブルの所属部署列にインデックスを作るには以下のようにします。検索が速くなりますがインデックスを整備し続けるためデータの更新などがあった場合のインデックス再構成に時間がかかるため、更新が若干遅くなるのが普通です。
CREATE INDEX idx_社員_所属部署
ON 社員 (所属部署);
メールアドレスのような、一意に決まる列でインデックスを作る場合は以下のように一意インデックスを作ることができます。
CREATE UNIQUE INDEX uix_社員_メールアドレス
ON 社員 (メールアドレス);
所属部署と給与の組み合わせで検索やソートが頻繁に行われる場合、複合インデックスを作成することができ、以下のようにします。
CREATE INDEX idx_社員_所属部署_給与
ON 社員 (所属部署, 給与 DESC); -- 所属部署で昇順、給与で降順にインデックスを張る
具体例として以下のような効果が得られます。
| クエリ | インデックス作成前 (時間) | インデックス作成後 (時間) | 備考 |
|---|---|---|---|
SELECT * FROM 社員 WHERE 所属部署 = '開発部'; |
1000ms | 10ms | idx_社員_所属部署が利用 |
SELECT * FROM 社員 WHERE メールアドレス = 'foo@example.com'; |
800ms | 5ms | uix_社員_メールアドレスが利用 |
SELECT * FROM 社員 ORDER BY 所属部署, 給与 DESC; |
1200ms | 20ms | idx_社員_所属部署_給与が利用 |
INSERT INTO 社員 VALUES (...); |
10ms | 12ms | インデックス更新のオーバーヘッド |
AP データベース SQL基礎へ戻る。
AP データベースへ戻る。