SQL(3) データの検索

Image from Gyazo

テーブルのレストア

前回までに作成したテーブルを .restore でレストアしましょう． また，.tableで【学生リスト】，【学部リスト】，【テニス部】，【サッカー部】があることを確認しましょう．

sqlite> .restore db2.sqlite
sqlite> .table
サッカー部  テニス部     学生リスト  学部リスト
学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【学生リスト】

学部 キャンパス 教員数
工学部 春日井 30
人文学部 春日井 20
国際関係学部 名古屋 25

【学部リスト】

学籍番号 氏名 学年 学部 よみがな
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【テニス部】

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
B003 杉江弘子 3 人文学部 すぎえひろこ
A005 内田弘 3 工学部 うちだひろし

【サッカー部】

レコードの取得

検索の理解を助けるために .header でヘッダーを表示する設定にしておきます．

sqlite> .header on
sqlite> select * from 学生リスト;
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか

テーブルからレコードを取得するには SELECT文 を用います． テーブル名 は対象のテーブルの名前です． 属性名 を指定して，特定の列だけを取得することができます． 属性名は複数指定することも可能です． また，属性名に「*（アスタリスク）」を指定すると，全てのレコードを取得します（これまでに何度も登場しています）．

/* SELECT文 レコードの取得 */
SELECT 属性名 FROM テーブル名;

/* SELECT文 全レコードの取得 */
SELECT * FROM テーブル名;

それでは，【学生リスト】からレコードを取得してみましょう．

sqlite> select 氏名 from 学生リスト;
氏名
岩城隼人
岩村優
杉江弘子
仙波あすか

sqlite> select 氏名,学部 from 学生リスト;
氏名|学部
岩城隼人|工学部
岩村優|工学部
杉江弘子|人文学部
仙波あすか|国際関係学部

レコードの検索

次は検索条件を満足するレコードのみを取得してみましょう． SELECT文 に WHERE句 を組み合わせます． ここで，条件式 とは，比較演算子を用いて表現した検索条件のことです． 比較演算子には，等号($=$)，不等号($<>$)，より小さい($<$)，より大きい($>$)，以下($<=$)，以上($>=$)などを用いることができます．

/* WHERE句 */
SELECT 属性名 FROM テーブル名 WHERE 条件式;

それでは，【学生リスト】から1年生のレコードを取得してみましょう．

sqlite> select * from 学生リスト where 学年=1;
学籍番号|氏名|学年|学部|よみがな
A002|岩村優|1|工学部|いわむらゆう
C004|仙波あすか|1|国際関係学部|せんばあすか

同様に，2年生以上のレコードを取得してみます．

sqlite> select * from 学生リスト where 学年>=2;
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
B003|杉江弘子|3|人文学部|すぎえひろこ

課題1

【学部リスト】から，キャンパスが春日井のレコードを取得し，その出力結果を提出しなさい．

課題2

下記の【商品リスト】を新規に作成しなさい． また，赤字のレコードを取得し，その出力結果を提出しなさい．

商品ID 商品名 仕入れ値 売値
C01 テレビ 50000 65000
C02 エアコン 60000 70000
C03 冷蔵庫 80000 55000
C04 電卓 3000 2000
C05 炊飯器 20000 25000
【商品リスト】

算術演算子を利用した検索

SELECT文で取得したレコードに対し， 和や差などの算術演算子を利用して加工することができます． 算術演算子には，和（$+$），差（$-$），乗算（$*$），除算（$/$）などがあります．

それでは，【商品リスト】から，商品名と 利益（売値と仕入れ値の差額） を取得してみましょう．

sqlite> select 商品名,売値-仕入れ値 from 商品リスト;
商品名|売値-仕入れ値
テレビ|15000
エアコン|10000
冷蔵庫|-25000
電卓|-1000
炊飯器|5000

次に，【商品リスト】から，商品名と 税込みの売値（10%） を取得してみましょう．

sqlite> select 商品名,売値*1.1 from 商品リスト;
商品名|売値*1.1
テレビ|71500.0
エアコン|77000.0
冷蔵庫|60500.0
電卓|2200.0
炊飯器|27500.0

課題3

【商品リスト】から，利益率が20%以上の商品を検索し，商品名と利益率を表示しなさい． また，その出力結果を提出しなさい．

検索結果のソート

SELECT文で取得したレコードを昇順・降順でソートすることができます． 昇順の場合は ORDER BY 属性名 ，また， 降順の場合は ORDER BY 属性名 DESC と記述します．

/* SELECT文 昇順でソート */
SELECT 属性名 FROM テーブル名 ORDER BY 属性名;

/* SELECT文 降順でソート */
SELECT 属性名 FROM テーブル名 ORDER BY 属性名DESC;

それでは，【学部リスト】を教員数の昇順で並べ替えます．

sqlite> select * from 学部リスト order by 教員数;
学部|キャンパス|教員数
人文学部|春日井|20
国際関係学部|名古屋|25
工学部|春日井|30

次に，【学部リスト」を教員数の降順で並べ替えます．

sqlite> select * from 学部リスト order by 教員数 DESC;
学部|キャンパス|教員数
工学部|春日井|30
国際関係学部|名古屋|25
人文学部|春日井|20

合計や平均の計算

SELECT文で取得したレコードに対し， 合計や平均などの関数を適用することができます． 合計は$SUM(属性名)$，平均は$AVG(属性名)$，件数は$COUNT(属性名)$と記述します．

それでは，【商品リスト】の売値の合計を計算してみましょう．

sqlite> select sum(売値) from 商品リスト;
sum(売値)
217000

次に，【商品リスト】の売値の平均を計算してみましょう．

sqlite> select avg(売値) from 商品リスト;
avg(売値)
43400.0

最後に，【商品リスト】のレコード件数を求めてみましょう．

sqlite> select count(*) from 商品リスト;
5

正規表現

正規表現を利用した検索条件を設定することが出来ます． 正規表現は GLOB句 を用いて表現します．

文字 説明
* 任意の0文字以上からなる文字列
? 任意の1文字
[abc] a，b，cのいずれかに一致
[a-d] aからdのいずれかに一致
[1-4] 1から4のいずれかに一致 
/* WHERE句 */
SELECT 属性名 FROM テーブル名 WHERE 属性名（条件） GLOB 正規表現;

それでは，学籍番号がAから始まるレコードを正規表現で検索してみましょう． 正規表現は A* と表します．

sqlite> select * from 学生リスト where 学籍番号 glob "A*";
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう

課題4

【学生リスト】から，正規表現を用いて，氏名が"岩"から始まるレコードを取得しなさい， また，同様に1年生〜2年生のレコードを取得しなさい． また，その出力結果を提出しなさい．

テーブルのバックアップ

作成したテーブルをファイルにバックアップするには，.backupを用います． バックアップするファイル名は db3.sqlite としましょう．

sqlite> .backup db3.sqlite

愛知県名古屋市にある椙山女学園大学 文化情報学部 向研究室の公式サイトです． 専門は情報科学であり，人工知能やデータベースなどの技術要素を指導しています． この公式サイトでは，授業で使用している教材を公開すると共に， ベールに包まれた女子大教員のミステリアスな日常を４コマ漫画でお伝えしていきます． サイトに関するご意見やご質問はFacebookまたはTwitterでお問い合わせください．