SQLite3を用いたSQL入門（その1）

はじめに_

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

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

ターミナルの起動_

  • Windows 10/11の場合：gnome-terminalを起動する。
  • macOSの場合：「ターミナル」を起動する。

本ページでのLinux/Unixコマンドの表記方法_

本ページではターミナル上で入力する命令（Linux/Unixコマンド、あるいはコマンドという）をパーセント記号（％）の後ろに表記する。たとえば以下のように表記する。

% Linux/Unixコマンド

Linux/Unixコマンドの一つであるlsを表記する場合は以下のようにあらわす。これはターミナル上でlsという文字列を入力し、その後Enterキーを押すという意味である。

% ls

また、必要に応じてLinux/Unixコマンドを実行した際の出力結果も例として示す。出力結果は冒頭にパーセント記号がついていない文字列である。以下の表記例は ls -1 というコマンドを実行した結果としてgotohという文字列が表示されたことを表す。

% ls -1
gotoh

これとは別に、SQLite3中で入力するコマンドは「>」に並べて以下のように表現している。

> SELECT * FROM goods;

Linuxコマンド入門_

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

SQLite3_

RDBMS_

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

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

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

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

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

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

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

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

表示されない場合はインストールする。

  • Ubuntuのインストール例。 
    % sudo apt install sqlite3
% which sqlite3
/usr/bin/sqlite3
  • MacOSの例 
    % brew install sqlite3
% which sqlite3
/usr/bin/sqlite3

SQLite3のバージョンを確認する。

  • Ubuntu 22.04の場合（2023/10/6時点、/usr/bin/sqlite3にインストールされている場合） 
    % sqlite3 --version
3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5dalt1
  • macOS venturaの場合（2023/10/6時点、/usr/bin/sqlite3にインストールされている場合） 
    % sqlite3 --version
3.39.5 2022-10-14 20:58:05 554764a6e721fab307c63a4f98cd958c8428a5d9d8edfde951858d6fd02daapl
  • macOS Montereyの場合（2023/10/6時点、/usr/bin/sqlite3にインストールされている場合） 
    % sqlite3 --version
3.37.0 2021-12-09 01:34:53 9ff244ce0739f8ee52a3e9671adb4ee54c83c640b02e3f9d185fd2f9a179aapl

注：標準インストール（/usr/bin/sqlite3）ではない場合は上記のバージョンと異なる場合がある。

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

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

% 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
101A corp.Chuoh-ku, Sapporo city, Hokkaido
201C 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
1101A0110
11301A015
21301A022
14001B011

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

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

% sqlite3 test.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>

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

sqlite> .quit

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

% ls

もう一度、読み込む。

% sqlite3 test.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>

SQLではセミコロン（;）までを一文とみなすため、入力の途中でEnterキーを押しても入力待ち状態が続く。

sqlite> SELECT * from cities （ここでEnterキーを押す）
   ...> （...>はSQL文の途中であることを示している。もう一度Enterキーを押す）
   ...> ; （セミコロンを入力し、Enterキーを押すとSQL文が入力されたと判定される）
Error: in prepare, no such table: cities (1)  （今回は存在しないテーブルにアクセスしようとしているのでエラーがでる）。

SQLite3を強制終了するときにはCtrl-D（Ctrlキーを押しながら、Dキーを押す）を押す。

sqlite> （Ctrl-Dを押す）

%  （sqlite3が終了している）

テーブルの作成（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の注意点

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

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

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中のコマンドではない。存在するテーブルを表示するとき以下のコマンドで一覧表示できる。

sqlite> .tables
customers  goods      orders

テーブルの定義の確認（非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( 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));

データの挿入（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)
A01|Office Paper A4|2000
A03|Office Paper B5|

sqlite> SELECT gid, gname FROM goods; -- (2)
A01|Office Paper A4
A03|Office Paper B5

sqlite> SELECT * FROM orders WHERE cid = 0101; --(3)
1|101|A01|10

sqlite> SELECT oid, gid FROM orders WHERE cid = 0101;  --(4)
1|A01

sqlite> SELECT * FROM orders WHERE cid = 9999;  --(5)

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

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

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

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

テーブル：goods

gidgnameprice
A01Office Paper A42000
A03Office Paper B5（空）
sqlite> select * from goods;
A01|Office Paper A4|2000
A03|Office Paper B5|

テーブル: customers

cidcnameaddress
101A corp.Chuoh-ku, Sapporo city, Hokkaido
sqlite> select * from customers;
101|A corp.|Chuoh-ku, Sapporo city, Hokkaido

テーブル: orders

oidcidgidnumber
1101A0110
11301A015
14001B011
sqlite> select * from orders;
1|101|A01|10
1|1301|A01|5
1|4001|B01|1

INSERT文の実行がうまくいかない場合は、上記リンクのファイルを使用してもよい。上記リンクのファイルを使用する場合は以下のコマンドを実行する。

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

wgetがインストールされていない場合はインストールする。

Ubuntuの場合
% sudo apt install wget

macOSの場合
% brew install wget

練習：INSERTとSELECTの復習_

現在のgoods, customers, ordersの各テーブルを以下の内容になるように、未入力の行についてINSERT命令を用いて入力せよ。また、SELECT命令を用いて適宜入力状況を確かめよ。なお、レコード（行）の順番はこのとおりになっていなくてよい（行の順番はINSERT文で入力した順番で定まるため）。

テーブル：goods

gidgnameprice
A01Office Paper A42000
A02Office Paper A34000
A03Office Paper B5（空）
B01Toner Cartridge Black25000
C01White Board14000

テーブル: customers

cidcnameaddress
101A corp.Chuoh-ku, Sapporo city, Hokkaido
201C 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
1101A0110
11301A015
21301A022
14001B011

データの更新（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.1 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

リンク_

戻る_