AP データベース SQL基礎

AP データベースに戻る

概要

　応用情報処理技術者試験の問題に取り上げられるようなSQLを紹介していきます。未完成です。2割くらいしかまだまとめれてないと思います。令和7年春の試験に間に合わず。力になれず申し訳なし。

　

CREATE TABLE

sql number wrapCopy

CREATE TABLE 社員 (
  社員ID INT PRIMARY KEY,
  氏名 VARCHAR(20),
  所属部署 VARCHAR(20),
  給与 INT CHECK (給与 >= 0)
);

sql

Showing null of 6 linesscrollable

　表に以下の列が定義された状態のデータベースが作成されます。

• 社員ID 整数型 主キー

• 氏名 文字型20桁

• 所属部署 文字型20桁

• 給与 整数型 0以上の整数である検査付き

社員

社員ID	氏名	所属部署	給与

　

INSERT

　INSERT句でデータを挿入できます。以下の例では4レコード挿入しています。これで表にデータがある状態になりました。ここで作った表をこの記事のベースにしたいと思います。

sql number wrapCopy

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

sql

社員

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

　

SELECT

　SELECT句でデータの抽出ができます。抜き出したい列を指定すると、以下のようになります。列に*を指定すると、すべての列が表示されます。

sql number wrapCopy

SELECT 氏名, 給与 FROM 社員;

sql

社員

氏名	給与
山田太郎	300000
佐藤花子	400000
鈴木一郎	350000
田中美咲	280000

　

WHERE

WHERE句でグループ化される前のフィルタリング条件指定ができます。

sql number wrapCopy

SELECT * FROM 社員 WHERE 所属部署='営業部';

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
3	鈴木一郎	営業部	350000

　

ORDER BY

　昇順や降順に並べる列名を指定するときに使います。DESCで降順です。ASCで昇順です。ORDER BY句のASCは規定値です。なのでASCの指定は省略できます。

sql number wrapCopy

SELECT * FROM 社員 ORDER BY 給与 DESC;

sql

社員

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

　

GROUP BY

sql number wrapCopy

SELECT 所属部署, AVG(給与) AS 平均給与 FROM 社員 GROUP BY 所属部署;

sql

社員

所属部署	平均給与
営業部	325,000
開発部	400,000
総務部	280,000

　

HAVING

　HAVING句はWHERE句に似ていますが、以下のような違いがあります。

• WHERE句はグループ化前に個々の行にフィルタリングを行います。
• HAVING句はグループ化後に集約結果に対する条件を指定します。

　したがってGROUP BY 句のところでよく使われたりします。

sql number wrapCopy

SELECT 所属部署, AVG(給与) AS 平均給与 FROM 社員 GROUP BY 所属部署 HAVING AVG(給与) > 300000;

sql

社員

所属部署	平均給与
営業部	325,000
開発部	400,000

　

DISTINCT

　抽出した結果に重複があれば、省略して結果表示してくれます。

sql number wrapCopy

SELECT DISTINCT 所属部署 FROM 社員;

sql

社員

所属部署
営業部
開発部
総務部

　

LIKE

　%によるワイルドカード指定ができます。IPAのSQLのDB変数みたいなものバインド変数またはプレースホルダですが、これを含めるときは '%' || :変数名 || '%'のように指定します。実際に使うときは先に記述した指定したとおりになりますが、プレースホルダの指定方法について、IPAでは特にきまりは無いので、'%:変数名%'として正解にしてくれていると思います。たぶん。実際はアプリケーションで組み込むときには違う表現になるような気もします。

PHPだとこんな感じ。

php number wrapCopy

$stmt = $pdo->prepare("SELECT * FROM 社員 WHERE 氏名 LIKE :name");
$stmt->execute([':name' => '%' . $searchWord . '%']);

php

　SQL自体での動作はシンプルは以下のようなものです。

sql number wrapCopy

SELECT * FROM 社員 WHERE 氏名 LIKE '田%';

sql

社員

社員ID	氏名	所属部署	給与
4	田中美咲	総務部	280000

　

UPDATE ～ SET ～

sql number wrapCopy

UPDATE 社員 SET 給与=310000 WHERE 氏名='山田太郎';

sql

社員

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

　

DELETE

sql number wrapCopy

DELETE FROM 社員 WHERE 所属部署='総務部';

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
3	鈴木一郎	営業部	350000

　

INNER JOIN

　以下のテーブルを追加します。ON句のキーワードでくっつけ方をよく指定します。テーブル名.列名=テーブル名.列名という対応での指定をします。2個以上の列条件の一致が必要な場合はANDとかも使います。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪
総務部	名古屋

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	名古屋

　

LEFT JOIN、LEFT OUTER JOIN

　以下のテーブルを追加します。LEFT JOIN と LEFT OUTER JOIN は同じです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 LEFT JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL

この状態でINNER JOINで結合すると

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京

　となり、総務部の情報の表示はなくなります。

　

IN(副問合せ)

　以降、以下のテーブルを追加します。

部署テーブル

部署名	拠点
営業部	東京
開発部	東京
開発部	大阪
総務部	名古屋

複数の値が返ってくる可能性のあるデータと一致するかをそれぞれ確認してくれます。以下だと、東京にある部署の名前が返ってきて、社員テーブルの所属部署名と比較してくれます。一致するレコードはすべて抽出されます。

sql number wrapCopy

SELECT 氏名 FROM 社員
WHERE 所属部署 IN (SELECT 部署名 FROM 部署 WHERE 拠点='東京');

sql

社員

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

　部署テーブルの拠点が東京のものには、営業部と、開発部がありますので、社員テーブルの営業部と開発部に一致するレコードが抽出されます。

sql number wrapCopy

SELECT 氏名 FROM 社員
WHERE 所属部署 IN ('営業部', '開発部');

sql

と実行したのと同じことです。INは副問合せ専用のキーワードではないです。INに指定されたデータに一致するものすべてという意味です。副問合せによく使うというだけです。

EXIST(副問合せ)

　レコードごとに副問合せの結果が存在したかどうかを判定します。

sql number wrapCopy

SELECT 氏名 FROM 社員 S
WHERE EXISTS (
  SELECT * FROM 部署 D
  WHERE D.部署名 = S.所属部署
    AND D.拠点='東京'
);

sql

社員

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

　何かしらの抽出レコードが副問合せの結果に存在したか、どうかを判定するだけです。真偽です。あったか、なかったか。

　

AP データベースに戻る