SQLite3を用いたSQL入門

はじめに_

本ページはSQLite3を用いて、SQLの使い方を演習形式で学ぶことを目的としている。

このため、端末(ターミナル)上でSQLite3を実行できるようにしておく必要がある。最低限、以下の内容まで終わらせておくこと。

Windows Subsystem for LinuxでLinux環境を整えた場合_

VcxSrv(XLaunch)の起動_

gnome-terminalを利用するので、まず、VcxSrv(XLaunch)を起動する。

デスクトップにあるアイコン「XLaunch」をクリックする。すると以下のウィンドウが開く。「Multiple windows」を選択し、「次へ」をクリックする。

「Start no client」を選択し、「次へ」をクリックする。

「次へ」をクリックする。

「完了」をクリックする。

デスクトップ右下のアイコンにXLaunchがあればちゃんと起動している。

gnome-terminalの起動_

gnome-terminalの利用で設定したgnome-terminalを起動する。

Ubuntuターミナルを起動し、ターミナル上で以下を起動する。

% gterm &

SQLite3_

RDBMS_

リレーショナルデータベース管理システム(RDBMS)の代表的なものはいくつかある。

  • MySQL:Oracle社が提供しているオープンソースのRDBMS。さまざまな場所で利用されている。
  • MariaDB:MySQLがOracle社の占有ソフトウェアになった際に、分岐したプロジェクト。機能的にはMySQLと同じ。
  • PostreSQL:オープンソースのRDBMS。MySQLと並んで利用されている。
  • SQLite:オープンソースのRDBMS。データベース本体を1ファイルに格納し、簡単に使えることからRDBMSを利用するソフトウェアの開発環境として広く使われている。本講義でも利用する。
  • Oracle Database:Oracle社が提供する商用データベースとして最も使われているRDBMS。
  • IBM DB2:IBM社が提供するRDBMS。無料で利用できる評価版と商用版の2種類が提供されている。

本講義では、データベース本体が1ファイルに格納され、バックアップや移行もファイルのコピーだけで利用できるSQLiteのバージョン3(SQLite3)を利用して、RDBMSの使い方およびSQLの使い方を学ぶ。

インストール状況の確認_

Linux上であるソフトウェアがインストール済みかどうかは以下のコマンドで調べることができる。

% which コマンド名
# インストール済みの場合はインストールされているディレクトリが表示される。
# インストールされていない場合は何も表示されない。

SQLite3がインストール済みかどうかを調べる。

% which sqlite3
/usr/bin/sqlite3  #このように表示されていればインストールされている。

表示されない場合はインストールする。以下の例はUbuntuのインストール例。

% sudo apt install sqlite3
% which sqlite3

Linuxコマンド入門_

Linuxコマンドがわからない場合は以下をひととおりやってみること。

作業ディレクトリの作成_

今日の演習用の作業ディレクトリを作成する。

% mkdir IntroSQL
% cd IntroSQL
% pwd
/home/gotoh/IntroSQL (真ん中の"gotoh"はそれぞれのユーザ名になる)

SQLite3を用いたSQL入門_

SQL_

SQLはリレーショナルデータベースを扱うときに使用する言語であり、国際標準が存在する。現在はISO (International Organization for Standardization: 国際標準化機構) で国際標準が定められている。

SQLは国際標準が定めれているが、各RDMSごとに各製品ごとの独自機能や準拠しているSQLのバージョンに違いがあるため、必ずしもすべてのRDMSで同じSQLを用いることができるわけではない。

RDBMSとリレーショナルデータモデルとの違い_

RDBMSではリレーショナルデータモデルで用いられる用語と一部異なる点がある。

  • テーブル:リレーションのこと。
  • 行(row):タプルのこと。レコード(record)ともいう。
  • 列(column):属性のこと。フィールド(field)ともいう。

リレーショナルデータモデルではリレーションに同じ値を持つタプルは複数存在しないが、RDBMSでは同じ値を持つレコード(タプル)を保存することができる。

SQLite チュートリアル_

SQLの構文などについては以下を参考にしている。

本チュートリアルで使うテーブル_

テーブル:goods

gidgnameprice
A01Office Paper A42000
A02Office Paper A34000
A03Office Paper B51500
B01Toner Cartridge Black25000
C01White Board14000

テーブル: customers

cidcnameaddress
0101A corp.Chuoh-ku, Sapporo city, Hokkaido
0201C corp.Hirosaki city, Aomori
1301A corp.Chiyoda-ku, Tokyo
1302B corp.Mitaka city, Tokyo
2301D corp.Chigusa-ku, Nagoya city, Aichi
4001E corp.Iizuka city, Fukuoka

テーブル: orders

oidcidgidnumber
10101A0110
11301A015
21301A022
14001B011

データベースの作成、読み込み、読み込み終了_

SQLite3では、1つのデータベースは1つのファイルで管理される。このため、データベースの読み込みとデータベースの作成が同じコマンドになっている。

% sqlite3 test.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> 

一度、読み込みを終了する。

sqlite> .quit

何も保存されてないためファイルが作成されていない。

% ls

もう一度、読み込む。

% sqlite3 test.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> 

テーブルの作成(create table)_

SQLiteではテーブルのことをスキーマ(schema)と呼んでいる。構文は以下のとおり。

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (
 column_1 data_type PRIMARY KEY,
 column_2 data_type NOT NULL,
 column_3 data_type DEFAULT 0,
 table_constraint
) [WITHOUT ROWID];

SQLの注意点

  • 属性値を除き、半角文字を用いる。
  • 大文字と小文字は区別されない。
  • 改行は無視される。
  • 文の終わりはセミコロン(;)で示す。

以下を入力しテーブルを作成する。コピー&ペーストで入力してよい(Windows Subsystem for Linux環境でのコピー&ペーストの方法

sqlite> CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int);

sqlite> CREATE TABLE customers ( cid int PRIMARY KEY, cname char(20), address char(40) );

# 注意:「...>」は改行したときの入力待ち表示
sqlite> CREATE TABLE orders( oid int NOT NULL, cid int NOT NULL, gid char(3) NOT NULL, --改行する
   ...> number int, PRIMARY KEY (oid, cid), FOREIGN KEY (cid) REFERENCES customers (cid),  --改行する
   ...> FOREIGN KEY (gid) REFERENCES goods (gid), CHECK (number > 0));

RDBMSではこのようにCREATE TABLEで作成したテーブルを実テーブルという。

テーブルの定義の確認(非SQL)_

SQLite3では「.SQLiteコマンド」でデータベースの情報を調べることができる。ただし、これらはSQL中のコマンドではない。すべてのテーブルの定義を確認する場合は「.schema」、個々のテーブルの定義を確認する場合は「.schema テーブル名」とする。

sqlite> .schema goods
CREATE TABLE goods (
gid char(3) PRIMARY KEY,
gname char(30),
price int
);

sqlite> .schema customers
CREATE TABLE customers (
cid int PRIMARY KEY,
cname char(20),
address char(40)
);

sqlite> .schema orders
CREATE TABLE orders(
cid int not null,
oid int not null,
gid char(3) not null,
number int,
PRIMARY KEY (oid, cid),
FOREIGN KEY (cid) REFERENCES customers (cid),
FOREIGN KEY (gid) REFERENCES goods (gid),
CHECK (number > 0)
);

データの挿入(INSERT)_

構文は以下のとおり。

INSERT INTO table1 (
 column1,
 column2 ,..)
VALUES
 (
 value1,
 value2 ,...);

補足説明

  • 1つ目のタプルで列(フィールド名)を列挙する。次のタプルは対応する値を記載する。
  • 1つ目のタプルは省略可能である。ただし、その場合は次のタプルはテーブルの定義通りのフィールド名に対応した順番で並んでいるとみなされる。
  • 文字列の値はシングルクォート(')かダブルクォート(")で囲う。
  • 「--」以降は行末までコメント扱いされる。

以下を入力しテーブルを作成する。コピー&ペーストで入力してよい。

sqlite> INSERT INTO goods values('A01', 'Office Paper A4', 2000); --(1)
sqlite> INSERT INTO goods (gid, gname) values('A03', 'Office Paper B5');  --(2)
sqlite> INSERT INTO customers values(0101,'A corp.', 'Chuoh-ku, Sapporo city, Hokkaido'); --(3)
sqlite> INSERT INTO orders (oid, cid, gid, number) values(1, 0101, 'A01', 10), --改行する
  ...> (1, 1301, 'A01', 5),(1, 4001, 'B01', 1); -- (4)

(1)と(3)は1つ目のタプル(フィールド名の列挙)を省略している。

(2)は1つ目のタプルでpriceを除いて定義している。つまり、この行はpriceの値が空値である。

(4)は複数行を一度に挿入している。

データの参照(SELECT)_

構文は以下のとおり。

SELECT DISTINCT column_list
FROM table_list
  JOIN table ON join_condition
WHERE row_filter
ORDER BY column
LIMIT count OFFSET offset
GROUP BY column
HAVING group_filter;

もっとも単純な構文は以下の通り。

SELECT column_list FROM table_list [WHERE row_filter];

補足

  • colum_listで出力したいフィールドを指定する。すべて出力する場合は*を使う。
  • table_listで参照先のテーブル名を列挙する。多くの場合は1つだけ指定する。
  • WHERE句で参照する行の条件を記載する。リレーショナル代数の選択演算の条件式に対応する。

以下の例を入力し、データがどのように出力されるか確認してみる。

sqlite> SELECT * FROM goods; -- (1)
sqlite> SELECT gid, gname FROM goods; -- (2)
sqlite> SELECT * FROM orders WHERE cid = 0101; --(3)
sqlite> SELECT oid, gid FROM orders WHERE cid = 0101;  --(4)
sqlite> SELECT * FROM orders WHERE cid = 9999;  --(5)

(1)はテーブルgoodsに登録されているデータをすべて表示する。(2)はフィールド名をgidとgnameに限定して登録されているデータをすべて表示している。

(3)と(4)はwhere句に合致する行だけを表示している。

(5)は合致する行がないので何も表示されない。

現時点でのデータベースの内容_

sqlite> select * from goods;
A01|Office Paper A4|2000
A03|Office Paper B5|

テーブル:goods

gidgnameprice
A01Office Paper A42000
A03Office Paper B5(空)
sqlite> select * from customers;
101|A corp.|Chuoh-ku, Sapporo city, Hokkaido

テーブル: customers

cidcnameaddress
0101A corp.Chuoh-ku, Sapporo city, Hokkaido
sqlite> select * from orders;
1|101|A01|10
1|1301|A01|5
1|4001|B01|1

テーブル: orders

oidcidgidnumber
10101A0110
11301A015
14001B011

上記リンクのファイルを使用する方法の例。

% cd ~/IntroSQL
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/test_sample1.db
% sqlite3 test_sample1.db

練習:INSERTとSELECTの復習_

以下のテーブルのうち、未入力の行についてINSERT命令を用いて入力せよ。また、SELECT命令を用いて適宜入力状況を確かめよ。

テーブル:goods

gidgnameprice
A01Office Paper A42000
A02Office Paper A34000
A03Office Paper B5(空)
B01Toner Cartridge Black25000
C01White Board14000

テーブル: customers

cidcnameaddress
0101A corp.Chuoh-ku, Sapporo city, Hokkaido
0201C corp.Hirosaki city, Aomori
1301A corp.Chiyoda-ku, Tokyo
1302B corp.Mitaka city, Tokyo
2301D corp.Chigusa-ku, Nagoya city, Aichi
4001E corp.Iizuka city, Fukuoka

テーブル: orders

oidcidgidnumber
10101A0110
11301A015
21301A022
14001B011
  • quiz1.db:上記の操作を終えた後のDB

データの更新(UPDATE)_

構文は以下の通り。

UPDATE table
SET column_1 = new_value_1,
    column_2 = new_value_2
WHERE
    search_condition 
ORDER column_or_expression
LIMIT row_count OFFSET offset;

補足

  • WHERE句で更新する行を限定しないと、テーブル内のすべての行でSET句で指定された更新が実行されてしまうので注意すること。
  • WHERE句の書き方はSELECT文と全く同じなので、まず、SELECT文で自分の更新したい行だけWHERE句で抜き出すことができるのを確認した上でUPDATE文を実行する方がよい。

以下の入力例を試してみる。

sqlite> SELECT * FROM goods WHERE gid = 'A01';
sqlite> UPDATE goods SET price = 1000 WHERE gid = 'A01';
sqlite> SELECT * FROM goods WHERE gid = 'A01';

値の入力に数式を使うこともできる。税込み表示にしてみる。

sqlite> SELECT * FROM goods WHERE gid = 'A01';
sqlite> UPDATE goods SET price = price * 1.08 WHERE gid = 'A01';
sqlite> SELECT * FROM goods WHERE gid = 'A01';

WHERE句をつけないとどうなるか確かめてみる。

sqlite> SELECT * FROM goods;
sqlite> UPDATE goods SET price = 1000;
sqlite> SELECT * FROM goods;

練習:UPDATE 復習_

テーブルgoodsのpriceの値を以下の値に戻せ。

テーブル:goods

gidgnameprice
A01Office Paper A42000
A02Office Paper A34000
A03Office Paper B51500
B01Toner Cartridge Black25000
C01White Board14000
  • quiz2.db:上記の操作を終えた後のDB

2019年10月17日講義用操作_

上記の練習問題「UPDATE 復習」終了後のデータベースをダウンロードし、以後の演習に使用する操作。

必要に応じて wgetをインストールする。

% which wget
% sudo apt install wget
% which wget 

データベースquiz2.dbをダウンロードし、以後の演習で使用するデータベース名test.dbとしてコピーする。

% mkdir -p ~/IntroSQL
% cd IntroSQL
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/quiz2.db quiz2.db
% cp quiz2.db test.db

データベースのバックアップ(その1:ファイルコピー、非SQL)_

SQLite3を一度終了する。

sqlite> .quit

するとtest.dbというファイルが生成されているのがわかる。SQLite3ではデータベースを1つのファイルに格納している。

% ls

SQLite3の場合、このデータベースが格納されているファイルをコピーしておけばバックアップを簡単にとることができる。Linuxコマンドのcpコマンドを使う。

% cp -p test.db test.db.YYYYMMDD (YYYYは西暦4桁表記、MMは月の2桁表記、DDは日の2桁表記)

データベースのバックアップ(その1:SQLファイルの書き出し、非SQL)_

SQLite3でデータベースを読み込む。

% sqlite3 test.db

データベースを再現するためのSQL文をファイルに書き出す。このようなデータベース内のデータを外部ファイルに書き出す機能をダンプ(dump)という。多くのRDBMSではダンプ機能(コマンド)を用意している。

sqlite> .output test_db_YYYYMMDD.sql
sqlite> .dump
sqlite> .quit

ファイルを確認してみる。catコマンドはファイルの中身をターミナル上に表示するコマンドである。

% ls
% cat test_db_YYYYMMDD.sql (YYYYMMDDは自分が入力した値に置き換えること)

データの削除(DELETE)_

構文は以下の通り。

DELETE
FROM
 table
WHERE
 search_condition;

補足

  • WHERE句で更新する行を限定しないと、テーブル内のすべての行が削除される。
  • WHERE句の書き方はSELECT文と全く同じなので、まず、SELECT文で自分の更新したい行だけWHERE句で抜き出すことができるのを確認した上でDELETE文を実行する方がよい。

以下の入力例を試してみる。

sqlite> SELECT * FROM goods where gid = 'C01';
sqlite> DELETE FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods where gid = 'C01';

手がすべってみる。

sqlite> SELECT * FROM goods;
sqlite> DELETE FROM goods;
sqlite> SELECT * FROM goods;

練習:DELETEの復習_

WHERE句において、あるフィールドの値に特定の文字列を含む行だけを抜き出す場合には「フィールド名 like "%特定の文字列%"」という条件式を用いる。

たとえば、以下のテーブルcustomersにおいてaddressにcityを含む行を抜き出す場合は以下のように入力する。

テーブル: customers

cidcnameaddress
0101A corp.Chuoh-ku, Sapporo city, Hokkaido
0201C corp.Hirosaki city, Aomori
1301A corp.Chiyoda-ku, Tokyo
1302B corp.Mitaka city, Tokyo
2301D corp.Chigusa-ku, Nagoya city, Aichi
4001E corp.Iizuka city, Fukuoka
sqlite> SELECT * FROM customers WHERE address like "%city%";

customersにおいて「~区」を含む行を削除せよ。

  • quiz3.db:上記の操作を終えた後のDB

データベースのリストア(その1:ファイルのコピー、非SQL)_

バックアップしておいたデータを使用できる状態に戻すことをリストア(復元、restore)という。ファイルのコピーでバックアップしておいたデータは以下のようにリストアする。

先ほどのファイルのコピーでバックしたデータを使って復元する。コピーしたファイル名がtest.db.YYYYMMDDのとき、以下のように入力する。

% cp test.db.YYYYMMDD test.db

データベースのリストア(その2:SQL文の挿入、非SQL)_

SQL文でダンプしたデータを使ってリストアする。ダンプしたSQL文がtest_db_YYYYMMDD.sqlであるとき、以下のように入力する。

% sqlite3 new.db < test_db_YYYYMMDD.sql

ビュー (CREATE VIEW)_

SELECT文によりある条件をみたしたデータを参照できる。しかし、複雑なSELECT文を頻繁に使用する場合、毎回、SQL文を作成するのは大変である。

そこで、SELECT文により生成されるテーブルを仮想的なテーブルとして保存する機能がビューである。データベースの構成(テーブルやフィールド名など)を変えたとしても、ある用途やアプリケーションから参照されるテーブルをビューを用いて容易に再現することができる。

構文は以下の通り。

CREATE [TEMP] VIEW [IF NOT EXISTS] view_name(column-name-list)
AS 
   select-statement;

以下の入力例を試す前にバックアップをしていたデータベースをリストアすること(先のDELETEの復習でデータベースの内容が変わっているため)。

sqlite3 new.db

以下の例ではgidとgnameからなるビュー goods_listsを作成している。作成したビューは実テーブル(CREATE TABLEで作ったテーブル)と同様にSELECT文で参照できる。

sqlite> CREATE VIEW goods_lists (gid, gname) AS SELECT gid,gname FROM goods;
sqlite> SELECT * from goods_lists;

別のビューを作成してみる。

sqlite> CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000;
sqlite> SELECT * FROM low_prices;

練習:CREATE VIEWの復習_

テーブルcutomersから東京にある会社だけを抜き出したビュー tokyo_corpを作成せよ。

テーブル: customers

cidcnameaddress
0101A corp.Chuoh-ku, Sapporo city, Hokkaido
0201C corp.Hirosaki city, Aomori
1301A corp.Chiyoda-ku, Tokyo
1302B corp.Mitaka city, Tokyo
2301D corp.Chigusa-ku, Nagoya city, Aichi
4001E corp.Iizuka city, Fukuoka

ビューの削除(DROP VIEW)_

構文は以下の通り。

DROP VIEW [IF EXISTS] view_name;

定義済みのVIEWを確認する(非SQL)。

sqlite> .tables  --テーブルおよびビュー名だけ表示する。
sqlite> .schema  --定義済みのテーブルやビューについてCREATE TABLEおよびCREATE VIEWの文を表示する。

ビュー goods_listsを削除する。

sqlite> DROP VIEW goods_lists;
sqlite> .tables

テーブルの削除(DROP TABLE)_

構文は以下の通り。

DROP TABLE [IF EXISTS] [schema_name.]table_name;

たとえば、goodsテーブルを削除してみる。

sqlite> DROP TABLE goods;
sqlite> .tables

テーブルの定義の変更(ALTER TABLE)_

テーブルの定義を変更する場合にはALTER TABLE文を用いる。テーブル名、フィールド名、各フィールドの値など変更する場所ごとに記述法が異なるので、以下のページを参照のこと。

リンク_

戻る_