SQL(1)・データベースの作成

Image from Gyazo

SQL

SQLは,リレーショナルデータベースに対して, データの操作を行うための 問い合わせ言語 です. System Rの問い合わせ言語であった SEQUEL がルーツであり, 現在は,ISOによって国際的に標準化され,様々なRDBMSで利用可能です. 集合演算関係代数演算 などのリレーションに対する基本的な演算に加え, SUMAVG などの関数が利用できるなど, データベースの運用において,必要な機能が組み込まれています.

SQLで利用可能な問い合わせは,下記の3種類に分類されます.

DMLは,データの追加(INSERT),データの検索(SELECT), DDLは,テーブルの作成(CREATE),テーブルの削除(DROP), DCLは,データの変更の確定(COMMIT),データの変更の取り消し(ROLLBACK)などが該当します.

SQL Online IDE

SQLは様々なDBMSで利用が可能ですが, ここではオンラインで気軽にSQLによる問い合わせが可能なSQL Online IDEを利用します. このSQL Online IDEでは,SQLiteMariaDBPostgreSQLなど代表的なDBMSのSQLをウェブブラウザで利用可能になっています. どのDBMSを選んでも大きな差はありませんが,ここではSQLiteを採用することにします. SQLiteはパブリックドメイン(知的財産権が発生しない)のリレーショナルデータベース管理システム(RDBMS)です. 一般的なRDBMSはクライアント・サーバ型で動作しますが, SQLiteはアプリに組み込まれるなどの形で,ソフトウェア(ライブラリ)単体とファイルで動作することが特徴です. それでは下記のリンクからSQL Online IDEにアクセスしましょう.

SQL Online IDE

Image from Gyazo

SQLの記述ルール

SQLには様々な命令文がありますが,共通して下記のルールを守る必要があります. このルールを満たしていなければ,エラーとなり命令は実行されません.

例えば,テーブルからレコード(タプル)を取り出す SELECT文や, レコードを追加する INSERT文 は下記のように記述します. このとき,「SELECT」を「select」や「Select」と記述しても問題ありません.

/* 【学生リスト】から全てのレコード取得 */
SELECT * FROM 学生リスト;

/* 【学生リスト】にレコードを追加 */
INSERT INTO 学生リスト VALUES("A001","岩城隼人",2,"工学部");

テーブルの作成

それではテーブル(リレーション)を作成してみましょう. テーブルの作成には CREATE文 を用います. テーブル名は新規に作成するテーブルの名前です. テーブルに含まれる属性データ型制約 と共に複数記述することが可能です. ここで,データ型とはデータの種類を表し, INTEGER(整数),REAL(小数),TEXT(文字列),BLOB(音声や画像)のいずれかを指定します. また,制約は主キーを指定するときに用いられ,省略することも可能です.

/* CREATE文 */
CREATE TABLE テーブル名
(
	属性名1 データ型 制約,
	属性名2 データ型 制約,
	属性名3 データ型 制約,
	...
);
データ型 意味
INTEGER 整数
REAL 小数
TEXT 文字列
BLOB 音声や画像など

それでは,下記の【学生リスト】を作成してみましょう. 学籍番号が主キーであることに注意してください. CREATE文で主キーを指定するには, 制約として PRIMARY KEY と記述します.

学籍番号 氏名 学年 学部
A001 岩城隼人 2 工学部
A002 岩村優 1 工学部
B003 杉江弘子 3 人文学部
C004 仙波あすか 1 国際関係学部

【学生リスト】

/* 【学生リスト】の作成 */
CREATE TABLE 学生リスト
(
	学籍番号 TEXT PRIMARY KEY,
	氏名 TEXT,
	学年 INTEGER,
	学部 TEXT
);

上記のSQLを入力したら,Run ボタンをクリックします.

Image from Gyazo

この結果,新たに【学生リスト】が作成されていることが確認できます. また,学籍番号に主キーであることを表す鍵マークが表示されています.

Image from Gyazo

レコードの追加

次に作成した【学生リスト】にレコードを追加します. レコードの追加にはINSERT文を用います. テーブル名はレコードを追加する対象のテーブルの名前です. また,VALUESの後に,属性の順番に値を並べることで,追加するレコードを表現します.

/* INSERT文 */
INSERT INTO テーブル名 VALUES(1, 2, 3,...);

それでは,【学生リスト】に$(A001, 岩城隼人, 2, 工学部)$を追加してみましょう. 文字列には「"(ダブルクオーテーション)」を付けることを忘れないでください.

/* 【学生リスト】にレコード追加 */
INSERT INTO 学生リスト VALUES("A001", "岩城隼人", 2, "工学部");

Image from Gyazo

テーブルに追加したレコードを表示するには SELECT文 を用います. SELECT文の詳しい使い方は次々回に解説します.

/* 学生リストのレコードを表示 */
SELECT * FROM 学生リスト;

Image from Gyazo

例題1

残りのレコードを追加して【学生リスト】を完成させなさい.

Image from Gyazo

例題2

【学生リスト】に$(A001, 岩城隼人, 2, 工学部)$を.もう一度,追加するとどうなるか確認しなさい.

例題3

下記の【学部リスト】を作成しなさい.主キーは学部です.

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

Image from Gyazo

外部キーの設定

CREATE文で外部キーを設定することができます. 外部キーを設定するには,FOREIGN KEY を用いて,「参照する側(外部キー)となる属性」と, 「参照される側の属性」を指定します.

/* CREATE文 */
CREATE TABLE テーブル名
(
	属性名1 データ型 制約,
	属性名2 データ型 制約,
	属性名3 データ型 制約,
	FOREIGN KEY(外部キー) REFERENCES 参照先のテーブル(参照先の属性)
);

【学生リスト】を削除(DROP)して,新しく外部キーを考慮した【学生リスト】を作成しましょう. ここでは,【学生リスト】の「学部」が外部キーであり, 【学部リスト]の「学部」が参照される側の属性になります.

/* 【学生リスト】の作成(外部キー) */
CREATE TABLE 学生リスト
(
	学籍番号 TEXT PRIMARY KEY,
	氏名 TEXT,
	学年 INTEGER,
	学部 TEXT,
	FOREIGN KEY(学部) REFERENCES 学部リスト(学部)
);

SQLiteでは,外部キー制約は標準では有効になっていません. このため,次のようにPRAGMAを利用して,有効化する必要があります.

PRAGMA foreign_keys=ON;

例題4

上述の4人の学生のレコードを【学生リスト】に登録した後に, $(D006, 山下隆, 4, 経営情報学部)$を追加するとどうなるか確認しなさい.

データベースの保存

作成したデータベースを保存するには,[File]→[Save DB]をクリックします. 保存したファイルは sqlite1.db とファイル名を変更してから,提出してください. 今後は,教員からの指示がなくとも,必ずデータベースを保存してから授業を終了してください.

Image from Gyazo

参考書籍

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