SQL sample (for sqlite3)
#
# sqlite3 testdb
#
.echo ON
.header ON
.mode column
.width 14 14 14 14 14 14 14 14 14 14
#
# 準備作業(テーブル削除)
#
DROP TABLE t_syain;
DROP TABLE t_syozoku;
DROP TABLE t_tel;
DROP TABLE t_tmp;
#
# 準備作業(テーブル作成：社員テーブル)
#
CREATE TABLE t_syain (
id CHAR(6) PRIMARY KEY, # 社員コード
name CHAR(10), # 氏名
section CHAR(10), # 所属コード
start CHAR(7), # 採用日
end CHAR(7), # 退職日
age INT # 年齢
);
CREATE INDEX i_t_syain_name on t_syain(name);
CREATE INDEX i_t_syain_section on t_syain(section);
#
# 準備作業(テーブル作成：所属テーブル)
#
CREATE TABLE t_syozoku (
section CHAR(10) PRIMARY KEY, # 所属コード
name CHAR(14) # 所属名称
);
CREATE UNIQUE INDEX i_t_syozoku on t_syozoku(name);
#
# 準備作業(テーブル作成：電話番号テーブル)
#
CREATE TABLE t_tel (
tel CHAR(15), # 電話番号
section CHAR(10), # 所属コード
biko CHAR(10), # 備考
PRIMARY KEY (tel, section)
);
#
# 準備作業(データ追加：社員テーブル)
#
INSERT INTO t_syain VALUES(003303,'Yamada',123430000,3470401,9999999,58);
INSERT INTO t_syain VALUES(004197,'Suzuki',123430000,3490501,9999999,54);
INSERT INTO t_syain VALUES(004984,'Nakamura',123430000,3570401,9999999,43);
INSERT INTO t_syain VALUES(005222,'Yokoyama',123430000,3620401,9999999,42);
INSERT INTO t_syain VALUES(005390,'Ito',999999999,4040401,9999999,40);
#
# 準備作業(データ追加：所属テーブル)
#
INSERT INTO t_syozoku VALUES(123400000,'営業第1部');
INSERT INTO t_syozoku VALUES(123410000,'営業1課');
INSERT INTO t_syozoku VALUES(123420000,'営業2課');
INSERT INTO t_syozoku VALUES(123430000,'営業3課');
#
# 準備作業(データ追加：電話番号テーブル)
#
INSERT INTO t_tel VALUES('987-654-7322',123400000,'代表');
INSERT INTO t_tel VALUES('987-654-7322',123410000,'代表');
INSERT INTO t_tel VALUES('987-654-7170',123420000,'代表');
INSERT INTO t_tel VALUES('987-654-7343',123430000,'代表');
INSERT INTO t_tel VALUES('987-654-7354',123430000,'Aグループ');
INSERT INTO t_tel VALUES('987-654-7341',123430000,'Bグループ');
INSERT INTO t_tel VALUES('987-654-7346',123430000,'Cグループ');
INSERT INTO t_tel VALUES('987-654-7344',123430000,'課長');
INSERT INTO t_tel VALUES('987-654-9999',888888888,'');
#
# 検索１(項目指定で、全件検索)
#
# 社員テーブルの全項目を全件検索する場合。
#
SELECT id, name, section, start, end, age FROM t_syain;
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
003303 Yamada 123430000 3470401 9999999 58
004197 Suzuki 123430000 3490501 9999999 54
004984 Nakamura 123430000 3570401 9999999 43
005222 Yokoyama 123430000 3620401 9999999 42
005390 Ito 999999999 4040401 9999999 40
#
# 検索２(項目指定を楽して、全件検索)
#
# 社員テーブルの全項目を全件検索する場合２。
#
SELECT * FROM t_syain;
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
003303 Yamada 123430000 3470401 9999999 58
004197 Suzuki 123430000 3490501 9999999 54
004984 Nakamura 123430000 3570401 9999999 43
005222 Yokoyama 123430000 3620401 9999999 42
005390 Ito 999999999 4040401 9999999 40
#
# 検索３
#
SELECT section FROM t_syain;
section
----------
123430000
123430000
123430000
123430000
999999999
#
# 検索４(同一レコードを１つにまとめる)
#
# 社員テーブルの所属コードを重複データは１件にまとめて検索する場合。
#
SELECT DISTINCT section FROM t_syain;
section
----------
123430000
999999999
#
# 検索５(検索条件：完全一致で検索)
#
# 社員テーブルから 'Nakamura' さんの全項目を検索する場合。
#
SELECT * FROM t_syain WHERE name = 'Nakamura';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
004984 Nakamura 123430000 3570401 9999999 43
#
# 検索６(検索条件：部分一致で検索)
#
# 社員テーブルから氏名が 'Y' で始まる人の全項目を検索する場合。
#
SELECT * FROM t_syain WHERE name LIKE 'Y%';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
003303 Yamada 123430000 3470401 9999999 58
005222 Yokoyama 123430000 3620401 9999999 42
#
# 検索７(検索条件：かつ条件で検索)
#
# 社員テーブルから氏名が 'Y' で始まり、かつ、採用が昭和５０年より前の人の
# 全項目を検索する場合。
#
SELECT * FROM t_syain WHERE name LIKE 'Y%' AND start < '3500000';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
003303 Yamada 123430000 3470401 9999999 58
#
# 検索８(検索条件：または条件で検索)
#
# 社員テーブルから氏名が 'Y' で始まるか、または、採用が昭和５０年より前の人の
# 全項目を検索する場合。
#
SELECT * FROM t_syain WHERE name LIKE 'Y%' OR start < '3500000';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
003303 Yamada 123430000 3470401 9999999 58
004197 Suzuki 123430000 3490501 9999999 54
005222 Yokoyama 123430000 3620401 9999999 42
#
# 検索９(並び換え：昇順で検索)
#
# 社員テーブルの全項目を所属コードの昇順に全件検索する場合。
#
SELECT * FROM t_syain ORDER BY section ASC;
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
005222 Yokoyama 123430000 3620401 9999999 42
004984 Nakamura 123430000 3570401 9999999 43
004197 Suzuki 123430000 3490501 9999999 54
003303 Yamada 123430000 3470401 9999999 58
005390 Ito 999999999 4040401 9999999 40
#
# 検索１０(並び換え：降順、昇順とりまぜて検索)
#
# 社員テーブルの全項目を所属コードの降順、採用日の昇順に全件検索する場合。
#
SELECT * FROM t_syain ORDER BY section DESC, start ASC;
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
005390 Ito 999999999 4040401 9999999 40
003303 Yamada 123430000 3470401 9999999 58
004197 Suzuki 123430000 3490501 9999999 54
004984 Nakamura 123430000 3570401 9999999 43
005222 Yokoyama 123430000 3620401 9999999 42
#
# 検索１１(突き合わせ条件：一致のみで検索)
#
# 社員テーブルの全項目に所属テーブルから所属名称をくっつけて検索する場合。
#
SELECT t_syain.*, t_syozoku.name FROM t_syain, t_syozoku ON t_syain.section = t_syozoku.section;
t_syain.id t_syain.name t_syain.section t_syain.start t_syain.end t_syain.age t_syozoku.name
------------ -------------- ----------------- --------------- ------------- ------------- --------------
003303 Yamada 123430000 3470401 9999999 58 営業3課
004197 Suzuki 123430000 3490501 9999999 54 営業3課
004984 Nakamura 123430000 3570401 9999999 43 営業3課
005222 Yokoyama 123430000 3620401 9999999 42 営業3課
#
# 検索１２(突き合わせ条件：一致、不一致合わせて検索)
#
# 社員テーブルの全項目に所属テーブルから所属名称をくっつけて全件検索する場合。
#
SELECT t_syain.*, t_syozoku.name FROM t_syain LEFT JOIN t_syozoku ON t_syain.section = t_syozoku.section;
t_syain.id t_syain.name t_syain.section t_syain.start t_syain.end t_syain.age t_syozoku.name
------------ -------------- ----------------- --------------- ------------- ------------- --------------
003303 Yamada 123430000 3470401 9999999 58 営業3課
004197 Suzuki 123430000 3490501 9999999 54 営業3課
004984 Nakamura 123430000 3570401 9999999 43 営業3課
005222 Yokoyama 123430000 3620401 9999999 42 営業3課
005390 Ito 999999999 4040401 9999999 40
#
# 検索１３(突き合わせ＋検索条件＋並び換え)
#
# 社員テーブルの全項目に所属テーブルから所属名称をくっつけて、採用日が昭和５０年より前のデータを
# 社員コードの降順に検索する場合。
#
SELECT t_syain.*, t_syozoku.name FROM t_syain LEFT JOIN t_syozoku ON t_syain.section = t_syozoku.section WHERE t_syain.start < '3500000' OR
DER BY t_syain.id DESC;
t_syain.id t_syain.name t_syain.section t_syain.start t_syain.end t_syain.age t_syozoku.name
------------ -------------- ----------------- --------------- ------------- ------------- --------------
004197 Suzuki 123430000 3490501 9999999 54 営業3課
003303 Yamada 123430000 3470401 9999999 58 営業3課
#
# 検索１４(複数テーブルの突き合わせ：社員テーブル←所属テーブル、社員テーブル←電話番号テーブル)
#
# 社員テーブルの全項目に所属テーブルから所属名称を、電話番号テーブルから電話番号と備考をくっつけて、
# 採用日が昭和５０年より前のデータを社員コードの降順に検索する場合。
#
SELECT t_syain.*, t_syozoku.name, t_tel.tel, t_tel.biko FROM t_syain LEFT JOIN t_syozoku ON t_syain.section = t_syozoku.section left join t_tel
ON t_syain.section = t_tel.section WHERE t_syain.start < '3500000' ORDER BY t_syain.id DESC;
t_syain.id t_syain.name t_syain.section t_syain.start t_syain.end t_syain.age t_syozoku.name t_tel.tel t_tel.biko
------------ -------------- ----------------- --------------- ------------- ------------- -------------- ------------ ----------
004197 Suzuki 123430000 3490501 9999999 54 営業3課 987-654-7343 代表
004197 Suzuki 123430000 3490501 9999999 54 営業3課 987-654-7354 Aグループ
004197 Suzuki 123430000 3490501 9999999 54 営業3課 987-654-7341 Bグループ
004197 Suzuki 123430000 3490501 9999999 54 営業3課 987-654-7346 Cグループ
004197 Suzuki 123430000 3490501 9999999 54 営業3課 987-654-7344 課長
003303 Yamada 123430000 3470401 9999999 58 営業3課 987-654-7343 代表
003303 Yamada 123430000 3470401 9999999 58 営業3課 987-654-7354 Aグループ
003303 Yamada 123430000 3470401 9999999 58 営業3課 987-654-7341 Bグループ
003303 Yamada 123430000 3470401 9999999 58 営業3課 987-654-7346 Cグループ
003303 Yamada 123430000 3470401 9999999 58 営業3課 987-654-7344 課長
#
# 検索１５(サブセレクト：検索結果を別の検索の検索条件にする...)
#
# 氏名が 'Y' で始まる社員が所属する職場の電話番号、所属コード、備考を電話番号テーブルから検索する。
#
SELECT * FROM t_tel WHERE section IN (SELECT section FROM t_syain WHERE name LIKE 'Y%');
tel section biko
------------ ---------- ----------
987-654-7343 123430000 代表
987-654-7354 123430000 Aグループ
987-654-7341 123430000 Bグループ
987-654-7346 123430000 Cグループ
987-654-7344 123430000 課長
#
# 検索１６(集計)
#
# 社員テーブルを所属コードごとに集計し、所属コード、件数、最大年齢、最小年齢、平均年齢、年齢合計
# を表示する。またその際に最大年齢の見出しを 'saidai' に変更する。
#
SELECT section, COUNT(*), MAX(age) AS saidai, MIN(age), AVG(age), SUM(age) FROM t_syain GROUP BY section;
section COUNT(*) saidai MIN(age) AVG(age) SUM(age)
-------------- -------------- -------------- -------------- -------------- --------------
123430000 4 58 42 49.25 197
999999999 1 40 40 40 40
#
# 検索１７(集計：条件付)
#
# 社員テーブルを所属コードごとに集計し、件数が１件より多い集計行に関して、所属コード、件数、最大年齢、
# 最小年齢、平均年齢、年齢合計を表示する。またその際に最大年齢の見出しを 'saidai' に変更する。
#
SELECT section, COUNT(*), MAX(age) AS saidai, MIN(age), AVG(age), SUM(age) FROM t_syain GROUP BY section HAVING COUNT(*) > 1;
section COUNT(*) saidai MIN(age) AVG(age) SUM(age)
-------------- -------------- -------------- -------------- -------------- --------------
123430000 4 58 42 49.25 197
#
# 追加１(テーブルに定義された全ての項目の値を指定して追加)
#
# 社員テーブルに全項目を指定してデータを追加する場合。
#
INSERT INTO t_syain VALUES(111111,'Ichiro',123430000,4170401,9999999,12);
#
# 追加２(一部の項目を指定して追加)
#
# 社員テーブルに社員コード、氏名を指定してデータを追加する場合。
#
INSERT INTO t_syain (id, name) VALUES(222222,'Jiro');
INSERT INTO t_syain (id, name) VALUES(333333,'Saburo');
INSERT INTO t_syain (id, name) VALUES(444444,'shiro');
INSERT INTO t_syain (id, name) VALUES(555555,'Goro');
#
# 確認
#
SELECT * FROM t_syain WHERE id >= '111111';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
111111 Ichiro 123430000 4170401 9999999 12
222222 Jiro
333333 Saburo
444444 shiro
555555 Goro
#
# 追加３の準備
#
CREATE TABLE t_tmp (
id CHAR(6) PRIMARY KEY,
name CHAR(10),
section CHAR(10)
);
#
# 追加３(テーブルから検索したデータを別のテーブルに追加)
#
# 社員テーブルから社員コード、氏名、所属コードを社員コードが '111111' 未満で検索し、
# その結果を一時テーブル(t_tmp)に追加する場合。
#
INSERT INTO t_tmp (id, name, section) SELECT id, name, section FROM t_syain WHERE id < '111111';
#
# 確認
#
SELECT * FROM t_tmp;
id name section
---------- ---------- ----------
003303 Yamada 123430000
004197 Suzuki 123430000
004984 Nakamura 123430000
005222 Yokoyama 123430000
005390 Ito 999999999
#
# 後始末
#
DROP TABLE t_tmp;
#
# 更新１(検索条件、値を指定しての更新)
#
# 社員コードが '222222' の社員の所属コード、採用日、退職日、年齢を更新する場合。
#
UPDATE t_syain SET section='123430000', start='4170401', end='9999999', age=22 WHERE id='222222';
#
# 確認
#
SELECT * FROM t_syain WHERE id = '222222';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
222222 Jiro 123430000 4170401 9999999 22
#
# 更新２(検索条件、式を指定しての更新)
#
# 社員コードが '111111' の社員の年齢を１０才進める場合。
#
UPDATE t_syain SET age = age + 10 WHERE id='111111';
#
# 確認
#
SELECT * FROM t_syain WHERE id = '111111';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
111111 Ichiro 123430000 4170401 9999999 22
#
# 更新３(対象レコードが複数でもＯＫ)
#
# 社員コードが '333333' 以上の社員の所属コード、採用日、退職日を更新する場合。
#
UPDATE t_syain SET section='123430000', start='4170401', end='9999999' WHERE id >= '333333';
#
# 確認
#
SELECT * FROM t_syain WHERE id >= '333333';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
333333 Saburo 123430000 4170401 9999999
444444 shiro 123430000 4170401 9999999
555555 Goro 123430000 4170401 9999999
#
# 削除１(検索条件を指定して削除)
#
# 社員コードが '444444' 以上の社員データを削除する場合。
#
DELETE FROM t_syain WHERE id >= '444444';
#
# 確認
#
SELECT * FROM t_syain WHERE id >= '111111';
id name section start end age
---------- ---------- ---------- ---------- ---------- ----------
111111 Ichiro 123430000 4170401 9999999 22
222222 Jiro 123430000 4170401 9999999 22
333333 Saburo 123430000 4170401 9999999
.exit
[更新]
[戻る]
m-ito@myh.no-ip.org