SQLite3を用いたSQL入門
- はじめに
- 本ページでのLinux/Unixコマンドの表記方法
- Windows Subsystem for LinuxでLinux環境を整えた場合
- SQLite3
- Linuxコマンド入門
- 作業ディレクトリの作成
- SQLite3を用いたSQL入門
- SQL
- RDBMSとリレーショナルデータモデルとの違い
- SQLite チュートリアル
- 本チュートリアルで使うテーブル
- データベースの作成、読み込み、読み込み終了
- テーブルの作成(create table)
- テーブルの定義の確認(非SQL)
- データの挿入(INSERT)
- データの参照(SELECT)
- 現時点でのデータベースの内容
- 練習:INSERTとSELECTの復習
- データの更新(UPDATE)
- 練習:UPDATE 復習(2021年10月28日はここまで)
- 2021年11月4日の講義を始める前に
- データベースのバックアップ(その1:ファイルコピー、非SQL)
- データベースのバックアップ(その1:SQLファイルの書き出し、非SQL)
- データの削除(DELETE)
- 練習:DELETEの復習
- データベースのリストア(その1:ファイルのコピー、非SQL)
- データベースのリストア(その2:SQL文の挿入、非SQL)
- ビュー (CREATE VIEW)
- 練習:CREATE VIEWの復習
- ビューの削除(DROP VIEW)
- テーブルの削除(DROP TABLE)
- テーブルの定義の変更(ALTER TABLE)
- リンク
- 戻る
はじめに_
本ページはSQLite3を用いて、SQLの使い方を演習形式で学ぶことを目的としている。
このため、端末(ターミナル)上でSQLite3を実行できるようにしておく必要がある。最低限、以下の内容まで終わらせておくこと。
本ページでのLinux/Unixコマンドの表記方法_
本ページではターミナル上で入力する命令(Linux/Unixコマンド、あるいはコマンドという)をパーセント記号(%)の後ろに表記する。たとえば以下のように表記する。
% Linux/Unixコマンド
Linux/Unixコマンドの一つであるlsを表記する場合は以下のようにあらわす。これはターミナル上でlsという文字列を入力し、そのごEnterキーを押すという意味である。
% ls
また、必要に応じてLinux/Unixコマンドを実行した際の出力結果も例として示す。出力結果は冒頭にパーセント記号がついていない文字列である。以下の表記例は ls -1 というコマンドを実行した結果としてgotohという文字列が表示されたことを表す。
% ls -1 gotoh
これとは別に、SQLite3中で入力するコマンドは「>」に並べて以下のように表現している。
> SELECT * FROM goods;
Windows Subsystem for LinuxでLinux環境を整えた場合_
VcxSrv(XLaunch)の起動_
gnome-terminalを利用するので、まず、VcxSrv(XLaunch)を起動する。VcxSrvのインストールで設定したVcxSrvのアイコンをクリックし、VcxSrvを起動する。
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 /usr/bin/sqlite3
- MacOSの例
% brew install sqlite3 % which sqlite3 /usr/bin/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
gid | gname | price |
A01 | Office Paper A4 | 2000 |
A02 | Office Paper A3 | 4000 |
A03 | Office Paper B5 | 1500 |
B01 | Toner Cartridge Black | 25000 |
C01 | White Board | 14000 |
テーブル: customers
cid | cname | address |
0101 | A corp. | Chuoh-ku, Sapporo city, Hokkaido |
0201 | C corp. | Hirosaki city, Aomori |
1301 | A corp. | Chiyoda-ku, Tokyo |
1302 | B corp. | Mitaka city, Tokyo |
2301 | D corp. | Chigusa-ku, Nagoya city, Aichi |
4001 | E corp. | Iizuka city, Fukuoka |
テーブル: orders
oid | cid | gid | number |
1 | 0101 | A01 | 10 |
1 | 1301 | A01 | 5 |
2 | 1301 | A02 | 2 |
1 | 4001 | B01 | 1 |
データベースの作成、読み込み、読み込み終了_
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アプリとUbuntuターミナル間でのコピー&ペースト)
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( 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)は合致する行がないので何も表示されない。
現時点でのデータベースの内容_
sqlite> select * from goods; A01|Office Paper A4|2000 A03|Office Paper B5|
テーブル:goods
gid | gname | price |
A01 | Office Paper A4 | 2000 |
A03 | Office Paper B5 | (空) |
sqlite> select * from customers; 101|A corp.|Chuoh-ku, Sapporo city, Hokkaido
テーブル: customers
cid | cname | address |
0101 | A corp. | Chuoh-ku, Sapporo city, Hokkaido |
sqlite> select * from orders; 1|101|A01|10 1|1301|A01|5 1|4001|B01|1
テーブル: orders
oid | cid | gid | number |
1 | 0101 | A01 | 10 |
1 | 1301 | A01 | 5 |
1 | 4001 | B01 | 1 |
- test_sample1.db:現時点でのデータベースファイル。
上記リンクのファイルを使用する方法の例。
% 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の復習_
以下のテーブルのうち、未入力の行についてINSERT命令を用いて入力せよ。また、SELECT命令を用いて適宜入力状況を確かめよ。
テーブル:goods
gid | gname | price |
A01 | Office Paper A4 | 2000 |
A02 | Office Paper A3 | 4000 |
A03 | Office Paper B5 | (空) |
B01 | Toner Cartridge Black | 25000 |
C01 | White Board | 14000 |
テーブル: customers
cid | cname | address |
0101 | A corp. | Chuoh-ku, Sapporo city, Hokkaido |
0201 | C corp. | Hirosaki city, Aomori |
1301 | A corp. | Chiyoda-ku, Tokyo |
1302 | B corp. | Mitaka city, Tokyo |
2301 | D corp. | Chigusa-ku, Nagoya city, Aichi |
4001 | E corp. | Iizuka city, Fukuoka |
テーブル: orders
oid | cid | gid | number |
1 | 0101 | A01 | 10 |
1 | 1301 | A01 | 5 |
2 | 1301 | A02 | 2 |
1 | 4001 | B01 | 1 |
データの更新(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 復習(2021年10月28日はここまで)_
テーブルgoodsのpriceの値を以下の値に戻せ。
テーブル:goods
gid | gname | price |
A01 | Office Paper A4 | 2000 |
A02 | Office Paper A3 | 4000 |
A03 | Office Paper B5 | 1500 |
B01 | Toner Cartridge Black | 25000 |
C01 | White Board | 14000 |
2021年11月4日の講義を始める前に_
10/28の「練習:UPDATE 復習」が終了したデータベースを以下の手順で用意する。
必要に応じてwgetをインストールする。
% which wget (何も表示されていなければインストールが必要)
- Ubuntuの場合
% sudo apt install wget % which wget
- Mac OSの場合
% brew 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ではデータベースを1つのファイルに格納している。test.dbというファイルがあるか確認する。
% 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は自分が入力した値に置き換えること) PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int); INSERT INTO goods VALUES('A01','Office Paper A4',2000); ~省略~ INSERT INTO orders VALUES(1,1301,'A01',5); INSERT INTO orders VALUES(2,1301,'A02',2); INSERT INTO orders VALUES(1,4001,'B01',1); COMMIT;
データの削除(DELETE)_
構文は以下の通り。
DELETE FROM table WHERE search_condition;
補足
- WHERE句で更新する行を限定しないと、テーブル内のすべての行が削除される。
- WHERE句の書き方はSELECT文と全く同じなので、まず、SELECT文で自分の更新したい行だけWHERE句で抜き出すことができるのを確認した上でDELETE文を実行する方がよい。
以下の入力例を試してみる。
% sqlite3 test.db sqlite> SELECT * FROM goods where gid = 'C01'; C01|White Board|14000 sqlite> DELETE FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods where gid = 'C01'; (削除されているので何も表示されない。)
手がすべってみる。
sqlite> SELECT * FROM goods; C01|White Board|14000 sqlite> DELETE FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods; A01|Office Paper A4|2000 A03|Office Paper B5|1500 A02|Office Paper A3|4000 B01|Toner Cartridge Black|25000 sqlite> DELETE FROM goods; sqlite> SELECT * FROM goods; (削除されているので何も表示されない。)
練習:DELETEの復習_
WHERE句において、あるフィールドの値に特定の文字列を含む行だけを抜き出す場合には「フィールド名 like "%特定の文字列%"」という条件式を用いる。
たとえば、以下のテーブルcustomersにおいてaddressにcityを含む行を抜き出す場合は以下のように入力する。
テーブル: customers
cid | cname | address |
0101 | A corp. | Chuoh-ku, Sapporo city, Hokkaido |
0201 | C corp. | Hirosaki city, Aomori |
1301 | A corp. | Chiyoda-ku, Tokyo |
1302 | B corp. | Mitaka city, Tokyo |
2301 | D corp. | Chigusa-ku, Nagoya city, Aichi |
4001 | E corp. | Iizuka city, Fukuoka |
sqlite> SELECT * FROM customers; 101|A corp.|Chuoh-ku, Sapporo city, Hokkaido 201|C corp.|Hirosaki city, Aomori 1301|A corp.|Chiyoda-ku, Tokyo 1302|B corp.|Mitaka city, Tokyo 2301|D corp.|Chigusa-ku, Nagoya city, Aichi 4001|E corp.|Iizuka city, Fukuoka sqlite> SELECT * FROM customers WHERE address like "%city%"; 101|A corp.|Chuoh-ku, Sapporo city, Hokkaido 201|C corp.|Hirosaki city, Aomori 1302|B corp.|Mitaka city, Tokyo 2301|D corp.|Chigusa-ku, Nagoya city, Aichi 4001|E corp.|Iizuka city, Fukuoka
customersにおいて「~区」を含む行を削除せよ。
データベースのリストア(その1:ファイルのコピー、非SQL)_
バックアップしておいたデータを使用できる状態に戻すことをリストア(復元、restore)という。ファイルのコピーでバックアップしておいたデータは以下のようにリストアする。
先ほどのファイルのコピーでバックしたデータを使って復元する。コピーしたファイル名がtest.db.YYYYMMDDのとき、以下のように入力する。(なお、SQLite3を一度終了させること。その後、実行する)
% cp test.db.YYYYMMDD test.db
復旧していることを確認する。先ほど内容を削除した goodsテーブルの中身が復旧していることを確認する。
% sqlite3 test.db sqlite> SELECT * FROM goods; C01|White Board|14000 sqlite> DELETE FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods; A01|Office Paper A4|2000 A03|Office Paper B5|1500 A02|Office Paper A3|4000 B01|Toner Cartridge Black|25000
次の作業のため、一旦終了する。
sqlite> .quit
データベースのリストア(その2:SQL文の挿入、非SQL)_
SQL文でダンプしたデータを使ってリストアする。ダンプしたSQL文がtest_db_YYYYMMDD.sqlであるとき、以下のように入力する。
% sqlite3 new.db < test_db_YYYYMMDD.sql
外部に出力したSQL文からデータベースが復旧されていることを確認する。
% sqlite3 new.db sqlite> .schema CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int); CREATE TABLE customers ( cid int PRIMARY KEY, cname char(20), address char(40) ); 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)); sqlite> SELECT * FROM goods; C01|White Board|14000 sqlite> DELETE FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods where gid = 'C01'; sqlite> SELECT * FROM goods; A01|Office Paper A4|2000 A03|Office Paper B5|1500 A02|Office Paper A3|4000 B01|Toner Cartridge Black|25000 sqlite> SELECT * FROM customers; ~出力省略~ sqlite> SELECT * FROM orders; ~出力省略~
データベースの中身をSQL文として出力し、バックアップをとっておくのは、他のRDBMS(たとえば、PostgreSQLなど)に移行するときにも役に立つ。
ビュー (CREATE VIEW)_
SELECT文によりある条件をみたしたデータを参照できる。しかし、複雑なSELECT文を頻繁に使用する場合、毎回、SQL文を作成するのは大変である。
そこで、SELECT文により生成されるテーブルを仮想的なテーブルとして保存する機能がビューである。データベースの構成(テーブルやフィールド名など)を変えたとしても、ある用途やアプリケーションから参照されるテーブルをビューを用いて容易に再現することができる。
構文は以下の通り。
CREATE [TEMP] VIEW [IF NOT EXISTS] view_name(column-name-list) AS select-statement;
SQL文からリストアしたデータベースを利用して、以下の演習を実行する。
sqlite3 new.db
まず、現在のテーブル構成を確認する。3つのテーブルが存在する。
sqlite> .tables customers goods orders
以下の例ではgidとgnameからなるビュー goods_listsを作成している。作成したビューは実テーブル(CREATE TABLEで作ったテーブル)と同様にSELECT文で参照できる。
sqlite> CREATE VIEW goods_lists (gid, gname) AS SELECT gid,gname FROM goods; sqlite> .tables customers goods goods_lists orders sqlite> SELECT * from goods_lists; A01|Office Paper A4 A03|Office Paper B5 A02|Office Paper A3 B01|Toner Cartridge Black C01|White Board
別のビューを作成してみる。2000円以下の品物だけを登録しているビューを作成する。
sqlite> CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000; sqlite> .tables customers goods goods_lists low_prices orders sqlite> SELECT * FROM low_prices; A01|Office Paper A4|2000 A03|Office Paper B5|1500
練習:CREATE VIEWの復習_
テーブルcutomersから東京にある会社だけを抜き出したビュー tokyo_corpを作成せよ。
テーブル: customers
cid | cname | address |
0101 | A corp. | Chuoh-ku, Sapporo city, Hokkaido |
0201 | C corp. | Hirosaki city, Aomori |
1301 | A corp. | Chiyoda-ku, Tokyo |
1302 | B corp. | Mitaka city, Tokyo |
2301 | D corp. | Chigusa-ku, Nagoya city, Aichi |
4001 | E corp. | Iizuka city, Fukuoka |
ビューの削除(DROP VIEW)_
構文は以下の通り。
DROP VIEW [IF EXISTS] view_name;
定義済みのVIEWを確認する(非SQL)。
sqlite> .tables customers goods goods_lists low_prices orders tokyo_corp sqlite> .schema CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int); CREATE TABLE customers ( cid int PRIMARY KEY, cname char(20), address char(40) ); 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)); CREATE VIEW goods_lists (gid, gname) AS SELECT gid,gname FROM goods /* goods_lists(gid,gname) */; CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000 /* low_prices(gid,gname,price) */; CREATE VIEW tokyo_corp as select * from customers where address like "%Tokyo%" /* tokyo_corp(cid,cname,address) */;
ビュー goods_listsを削除する。
sqlite> DROP VIEW goods_lists; sqlite> .tables customers goods low_prices orders tokyo_corp
テーブルの削除(DROP TABLE)_
構文は以下の通り。
DROP TABLE [IF EXISTS] [schema_name.]table_name;
たとえば、goodsテーブルを削除してみる。
sqlite> .tables customers goods low_prices orders tokyo_corp sqlite> DROP TABLE goods; sqlite> .tables customers low_prices orders tokyo_corp
ビューlow_pricesの本体はテーブルgoodsの内容を閲覧するSELECT文であるため、goodsを削除したあとにlow_pricesを閲覧するとエラーが表示される。
sqlite> .schema low_prices CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000; sqlite> .tables customers low_prices orders tokyo_corp sqlite> SELECT * from low_prices; Error: no such table: main.goods
テーブルの定義の変更(ALTER TABLE)_
テーブルの定義を変更する場合にはALTER TABLE文を用いる。テーブル名、フィールド名、各フィールドの値など変更する場所ごとに記述法が異なるので、以下のページを参照のこと。
リンク_
- SQLの高度な問合せ(11/4の講義の続きはこちら)