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

Image from Gyazo

SQL

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

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

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

SQLite

SQLiteは, パブリックドメイン(知的財産権が発生しない)のリレーショナルデータベース管理システム(RDBMS)の一つです. 一般的なRDBMSはクライアント・サーバ型で動作しますが, SQLiteはアプリに組み込まれるなどの形で,ソフトウェア(ライブラリ)単体とファイルで動作することが特徴です. それでは,公式サイトからインストーラ(sqlite-tools-win32-x86-xxxx.zip)をダウンロードしてインストールしましょう. コマンドライン・ツール(sqlite3.exe)を含むインストーラを選択するようにしてください.

SQLiteのダウンロード

インストールが完了したら,インストールされたフォルダに含まれる コマンドライン・ツール(sqlite3.exe)をダブルクリックして起動してください.

Image from Gyazo

コマンドラインツールでは,プロンプト(sqlite>) に, SQL文やSQLite独自のコマンドを入力することでテーブルを操作します. SQLite独自のコマンドは先頭に「.(ドット)」が付いています. 例えば,SQLiteを終了するには,.quitと入力します.

Image from Gyazo

SQLの記述ルール

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

例えば,テーブルからレコード(タプル)を取り出す SELECT文や, レコードを追加する INSERT文 は下記のように記述します. このとき,「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
);

作成したテーブルの一覧を表示するには .table, また,テーブルのスキーマ(属性名やデータ型など)を表示するには .schema を用います.

sqlite> .table
学生リスト
sqlite> .schema 学生リスト
CREATE TABLE 学生リスト
(
学籍番号 TEXT PRIMARY KEY,
氏名 TEXT,
学年 INTEGER,
学部 TEXT
);

レコードの追加

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

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

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

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

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

sqlite> select * from 学生リスト;
A001|岩城隼人|2|工学部

課題1

残りのレコードを追加して【学生リスト】を完成させなさい. また,SELECT文の出力結果を提出しなさい.

課題2

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

課題3

下記の【学部リスト】を作成しなさい. また,SELECT文の出力結果を提出しなさい.

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

バックアップとレストア

作成したテーブルをファイルにバックアップするには,.backupを用います. バックアップするファイル名は db1.sqlite としましょう. バックアップをとらないと作業したテーブルは消失してしまいますので, 定期的にバックアップしておくと安心です.

sqlite> .backup db1.sqlite

また,ファイルからテーブルをレストアするには, .restore を用います. 一旦,SQLiteを終了し,再度,起動してからレストアしてみましょう. 元どおりにテーブルが復元されているでしょうか.

sqlite> .restore db1.sqlite
sqlite> .table
学生リスト

今後は,教員からの指示がなくとも, 必ずバックアップをとってから授業を終了してください.

参考書籍

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