SQL(3) データの検索

Image from Gyazo

データベースの復元

前回までに作成したデータベースを読み込むには,[File]→[Open DB]をクリックし, 保存した sqlite2.db を選択します. 復元したデータベースに【学生リスト】,【学部リスト】,【テニス部】,【サッカー部】があることを確認しましょう.

sqlite2.db

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

【学生リスト】

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

【学部リスト】

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

【テニス部】

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

【サッカー部】

レコードの取得

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

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

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

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

/* 【学生リスト】から氏名を取得 */
SELECT 氏名 FROM 学生リスト;

Image from Gyazo

/* 【学生リスト】から氏名と学部を取得 */
SELECT 氏名,学部 FROM 学生リスト;

Image from Gyazo

レコードの検索

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

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

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

/* 【学生リスト】から1年生のレコードを取得 */
SELECT * FROM 学生リスト WHERE 学年=1;

Image from Gyazo

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

/* 【学生リスト】から2年生以上のレコードを取得 */
SELECT * FROM 学生リスト WHERE 学年>=2;

Image from Gyazo

例題1

【学部リスト】から,キャンパスが春日井のレコードを取得しなさい. Image from Gyazo

例題2

下記の【商品リスト】を新規に作成しなさい. また,赤字(売値が仕入れ値より安い)のレコードを取得しなさい.

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

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

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

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

/* 【商品リスト】から商品名と利益(売値-仕入れ値)を取得 */
SELECT 商品名,売値-仕入れ値 FROM 商品リスト;

Image from Gyazo

このとき,列名には「売値-仕入れ値」が表示されてしまいます. 列名に別名を付ける場合は AS句 を利用します.

/* 【商品リスト】から商品名と利益(売値-仕入れ値)を取得 */
SELECT 商品名,売値-仕入れ値 AS 利益 FROM 商品リスト;

Image from Gyazo

次に,【商品リスト】から,商品名,売値,税込(売値の10%を加算) を取得してみましょう. このとき,cast()を利用して,計算結果のデータ型を INTEGER に変換することで,小数以下を切り捨てていることに注意してください.

/* 【商品リスト】から商品名と税込みの売値を取得 */
SELECT 商品名, 売値, CAST(売値 * 1.1  AS INTEGER) AS 税込 FROM 商品リスト;

Image from Gyazo

例題3

【商品リスト】から,利益率が20%以上の商品を検索し,商品名と利益率を表示しなさい. Image from Gyazo

合計や平均の計算

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

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

/* 【商品リスト】の売値の合計 */
SELECT SUM(売値) from 商品リスト;

Image from Gyazo

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

/* 【商品リスト】の売値の平均 */
SELECT AVG(売値) from 商品リスト;

Image from Gyazo

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

/* 【商品リスト】のレコード数 */
SELECT COUNT(売値) from 商品リスト;

Image from Gyazo

グループ集計

特定の列でグループ化して,グループごとの合計や平均などの関数を適用することが出来ます. グループ化したい列は GROUP BY句 で指定します.

それでは,【学部リスト】のキャンパスでグループ化して,教員数の合計を計算してみましょう.

/* 【学部リスト】のキャンパスでグループ化 */ 
SELECT キャンパス,SUM(教員数) FROM 学部リスト GROUP BY キャンパス;

Image from Gyazo

次に,【学生リスト】の学部でグループ化して,レコード件数を求めてみましょう.

/* 【学生リスト】の学部でグループ化 */ 
SELECT 学部, COUNT(*) FROM 学生リスト GROUP BY 学部; 

Image from Gyazo

例題4

【学生リスト】の学年でグループ化し,レコード件数を求めなさい.このとき,レコード件数の列名は「学生数」と表示しなさい. Image from Gyazo

検索結果のソート

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

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

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

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

/* 【学部リスト】を教員数の昇順で並べ替え */
SELECT * FROM 学部リスト ORDER BY 教員数;

Image from Gyazo

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

/* 【学部リスト】を教員数の降順で並べ替え */
SELECT * FROM 学部リスト ORDER BY 教員数 DESC;

Image from Gyazo

正規表現

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

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

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

/* 学生リストから学籍番号がAから始まるレコードを取得 */
SELECT * FROM 学生リスト where 学籍番号 glob "A*";

Image from Gyazo

例題5

【学生リスト】から,正規表現を用いて,氏名が"岩"から始まるレコードを取得しなさい, また,同様に1年生〜2年生のレコードを取得しなさい. Image from Gyazo Image from Gyazo

データベースの保存

作成したデータベースを保存するには,[File]→[Save DB]をクリックします. 保存したファイルは sqlite3.db とファイル名を変更してから,提出してください.

参考書籍

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