AP データベース SQL基礎
AP データベースに戻る
概要
応用情報処理技術者試験の問題に取り上げられるようなSQLを紹介していきます。未完成です。2割くらいしかまだまとめれてないと思います。令和7年春の試験に間に合わず。力になれず申し訳なし。
CREATE TABLE
CREATE TABLE 社員 (
社員ID INT PRIMARY KEY,
氏名 VARCHAR(20),
所属部署 VARCHAR(20),
給与 INT CHECK (給与 >= 0)
);
表に以下の列が定義された状態のデータベースが作成されます。
- 社員ID 整数型 主キー
- 氏名 文字型20桁
- 所属部署 文字型20桁
- 給与 整数型 0以上の整数である検査付き
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|
INSERT
INSERT句でデータを挿入できます。以下の例では4レコード挿入しています。これで表にデータがある状態になりました。ここで作った表をこの記事のベースにしたいと思います。
INSERT INTO 社員(社員ID, 氏名, 所属部署, 給与)
VALUES
(1, '山田太郎', '営業部', 300000),
(2, '佐藤花子', '開発部', 400000),
(3, '鈴木一郎', '営業部', 350000),
(4, '田中美咲', '総務部', 280000);
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
| 4 | 田中美咲 | 総務部 | 280000 |
SELECT
SELECT句でデータの抽出ができます。抜き出したい列を指定すると、以下のようになります。列に*を指定すると、すべての列が表示されます。
SELECT 氏名, 給与 FROM 社員;
| 氏名 | 給与 |
|---|---|
| 山田太郎 | 300000 |
| 佐藤花子 | 400000 |
| 鈴木一郎 | 350000 |
| 田中美咲 | 280000 |
WHERE
WHERE句でグループ化される前のフィルタリング条件指定ができます。
SELECT * FROM 社員 WHERE 所属部署='営業部';
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
ORDER BY
昇順や降順に並べる列名を指定するときに使います。DESCで降順です。ASCで昇順です。ORDER BY句のASCは規定値です。なのでASCの指定は省略できます。
SELECT * FROM 社員 ORDER BY 給与 DESC;
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
| 1 | 山田太郎 | 営業部 | 300000 |
| 4 | 田中美咲 | 総務部 | 280000 |
GROUP BY
SELECT 所属部署, AVG(給与) AS 平均給与 FROM 社員 GROUP BY 所属部署;
| 所属部署 | 平均給与 |
|---|---|
| 営業部 | 325,000 |
| 開発部 | 400,000 |
| 総務部 | 280,000 |
HAVING
HAVING句はWHERE句に似ていますが、以下のような違いがあります。
- WHERE句はグループ化前に個々の行にフィルタリングを行います。
- HAVING句はグループ化後に集約結果に対する条件を指定します。
したがってGROUP BY 句のところでよく使われたりします。
SELECT 所属部署, AVG(給与) AS 平均給与 FROM 社員 GROUP BY 所属部署 HAVING AVG(給与) > 300000;
| 所属部署 | 平均給与 |
|---|---|
| 営業部 | 325,000 |
| 開発部 | 400,000 |
DISTINCT
抽出した結果に重複があれば、省略して結果表示してくれます。
SELECT DISTINCT 所属部署 FROM 社員;
| 所属部署 |
|---|
| 営業部 |
| 開発部 |
| 総務部 |
LIKE
%によるワイルドカード指定ができます。IPAのSQLのDB変数みたいなものバインド変数またはプレースホルダですが、これを含めるときは '%' || :変数名 || '%'のように指定します。実際に使うときは先に記述した指定したとおりになりますが、プレースホルダの指定方法について、IPAでは特にきまりは無いので、'%:変数名%'として正解にしてくれていると思います。たぶん。実際はアプリケーションで組み込むときには違う表現になるような気もします。
PHPだとこんな感じ。
$stmt = $pdo->prepare("SELECT * FROM 社員 WHERE 氏名 LIKE :name");
$stmt->execute([':name' => '%' . $searchWord . '%']);
SQL自体での動作はシンプルは以下のようなものです。
SELECT * FROM 社員 WHERE 氏名 LIKE '田%';
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 4 | 田中美咲 | 総務部 | 280000 |
UPDATE ～ SET ～
UPDATE 社員 SET 給与=310000 WHERE 氏名='山田太郎';
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 310000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
| 4 | 田中美咲 | 総務部 | 280000 |
DELETE
DELETE FROM 社員 WHERE 所属部署='総務部';
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
INNER JOIN
以下のテーブルを追加します。ON句のキーワードでくっつけ方をよく指定します。テーブル名.列名=テーブル名.列名という対応での指定をします。2個以上の列条件の一致が必要な場合はANDとかも使います。
| 部署名 | 拠点 |
|---|---|
| 営業部 | 東京 |
| 開発部 | 大阪 |
| 総務部 | 名古屋 |
SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;
| 氏名 | 所属部署 | 拠点 |
|---|---|---|
| 山田太郎 | 営業部 | 東京 |
| 佐藤花子 | 開発部 | 大阪 |
| 鈴木一郎 | 営業部 | 東京 |
| 田中美咲 | 総務部 | 名古屋 |
LEFT JOIN、LEFT OUTER JOIN
以下のテーブルを追加します。LEFT JOIN と LEFT OUTER JOIN は同じです。
| 部署名 | 拠点 |
|---|---|
| 営業部 | 東京 |
| 開発部 | 大阪 |
SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 LEFT JOIN 部署
ON 社員.所属部署 = 部署.部署名;
| 氏名 | 所属部署 | 拠点 |
|---|---|---|
| 山田太郎 | 営業部 | 東京 |
| 佐藤花子 | 開発部 | 大阪 |
| 鈴木一郎 | 営業部 | 東京 |
| 田中美咲 | 総務部 | NULL |
この状態でINNER JOINで結合すると
SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;
| 氏名 | 所属部署 | 拠点 |
|---|---|---|
| 山田太郎 | 営業部 | 東京 |
| 佐藤花子 | 開発部 | 大阪 |
| 鈴木一郎 | 営業部 | 東京 |
となり、総務部の情報の表示はなくなります。
IN(副問合せ)
以降、以下のテーブルを追加します。
| 部署名 | 拠点 |
|---|---|
| 営業部 | 東京 |
| 開発部 | 東京 |
| 開発部 | 大阪 |
| 総務部 | 名古屋 |
複数の値が返ってくる可能性のあるデータと一致するかをそれぞれ確認してくれます。以下だと、東京にある部署の名前が返ってきて、社員テーブルの所属部署名と比較してくれます。一致するレコードはすべて抽出されます。
SELECT 氏名 FROM 社員
WHERE 所属部署 IN (SELECT 部署名 FROM 部署 WHERE 拠点='東京');
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
部署テーブルの拠点が東京のものには、営業部と、開発部がありますので、社員テーブルの営業部と開発部に一致するレコードが抽出されます。
SELECT 氏名 FROM 社員
WHERE 所属部署 IN ('営業部', '開発部');
と実行したのと同じことです。INは副問合せ専用のキーワードではないです。INに指定されたデータに一致するものすべてという意味です。副問合せによく使うというだけです。
EXIST(副問合せ)
レコードごとに副問合せの結果が存在したかどうかを判定します。
SELECT 氏名 FROM 社員 S
WHERE EXISTS (
SELECT * FROM 部署 D
WHERE D.部署名 = S.所属部署
AND D.拠点='東京'
);
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
何かしらの抽出レコードが副問合せの結果に存在したか、どうかを判定するだけです。真偽です。あったか、なかったか。
AP データベースに戻る