select、insert、delete、update と続いてきたので、今回は新しいテーブルの作成方法と、削除方法について解説したいと思います。
今回で DIYプログラミングでデータベースを扱う上で、必要最小限の説明が終了します。
実際はもっといろいろとあるのですが、それは必要に応じて少しづつ覚えていけば良いので、また別の機会に紹介できればと思います。
SQLを試す場合は、 こちら でツールを公開していますので、ダウンロードをお願いします。
Create文の書式
Create文の書式は次の通りです。
- カラム定義：カラム名とデータ型（後述）のペアをカンマ区切りで列挙します。
- プライマリキー（後述）：検索時にデータが一意に特定できるカラムの組み合わせを列挙します。
例えば、売上日,商品コード、商品名、数量という３つのカラムを持つ、売上明細というテーブルを作成したい場合は、次の様に記述します。
create table 売上明細
(
売上日 TEXT
,商品コード TEXT
,商品名 TEXT
,数量 INTEGER
,primary key (売上日,商品コード)
)
データ型は省略が可能ですが、その場合は全てのデータ型が登録できるカラムになるため、並べ替えや集計で正しい結果が得られなくなるリスクが発生します。
primary key も省略可能ですが、大量のデータを検索する際は検索速度が遅くなることと、誤って同じデータを複数登録した場合、正しい集計が得られなくなるリスクが発生します。
Createで使える型
SQLiteで使用できるデータ型は次の4種類です。残念ながら日付型はありませんので、日付や時刻を入れる場合、TEXT を指定しなければなりません。
| 型 | 解説 | 最大桁数 |
|---|---|---|
| REAL | 浮動小数点 | 約±2.2250738585072014e-308～約±1.7976931348623157e+308 |
| INTEGER | 整数 | -9223372036854775808～9223372036854775807 |
| TEXT | 任意の文字列 | 2^31-1 Byte≒約2GB |
| BLOB | 任意のバイナリデータ | 2^31-1 Byte≒約2GB |
BLOBはイメージデータやEXCEL、WORDなどのドキュメントファイルを格納するために使うものなので、使う機会は少ないかもしれません。
他のデータベースとの互換性を考慮し、Create文では varchar や double、decimal 、datetime などのデータ型が指定できますが、実際には上記のいずれかのデータ型として解釈されます。
Primary key（主キー）とは
キーは別名インデックスとか索引とも呼ばれていますが、次の役割を持っています。
- テーブルに対して高速な検索を可能にする
- データが重複しないことを保証する
Primary key は日本語で主キーといいますが、そのテーブルを検索するときに、主に使われるキーの事です。
特定のカラムをキーとして指定すると、そのカラムの値が抜き出され、検索しやすいような形に変えられてメモリ上に置かれます。
select、delete、update において、where 区で指定する検索条件の中に、キーに指定したカラムがあれば、データの検索が超高速に行われるので検索速度が向上します。
インデックスは後から好きなだけ追加することが可能ですが、create 文を実行する時に指定する主となるキーのことを primary key と呼んでいます。
Createでテーブルを作ってみる。
では、実際にテーブルを作ってみましょう。
先ほどの例に出した売上明細のSQLを実行してみます。
create table 売上明細
(
売上日 TEXT
,商品コード TEXT
,数量 INTEGER
,primary key (売上日,商品コード)
)
左側のテーブル一覧に、売上明細が表示されていると思いますので、クリックしてみて下さい。
以下の様な結果になります。
このツールでは、primary key については表示していないのでカラムだけの確認になりますが、カラム一覧に「売上日」「商品コード」「数量」が表示されています。
では、このテーブルにデータを追加したいと思います。
「【試して覚える】SQLite で Insert 文入門」では、insert 文１つにつき1レコードが追加されます。
好きなだけ insert文を作ればよいのですが、ここでは同じカラム持つ別テーブルの内容をコピーします。
select insert でデータをまとめて挿入
insert 文のところで説明していなかったのですが、insert にはもう１つ便利な書き方があります。
２つのテーブルAとBがあって、両方ともカラムの並び、データ型が一緒なら、一括してAからBにデータをコピーしてinsert するとができます。
先ほど作成した 売上明細と、ツールのサンプルテーブルである売上データは、全く同じカラムを持っていますので、 insert into select が使えます。
試しに、2020/09/01 以降で商品コードが 'A000001' のデータだけを売上明細にinsert してみましょう。
insert into 売上明細 select * from 売上データ where 売上日 >= '2020/09/01' and 商品コード='A000001'
実行したら売上明細の内容を確認しましょう。
次のSQLを実行するか、ツールのテーブル一覧で売上明細をダブルクリックして下さい。
select * from 売上明細
次の内容が表示されていると思います。
では、今度は全件insert してみましょう。
insert into 売上明細 select * from 売上データ
今度は次のようなエラーが表示されると思います。
これはユニークキー（一意キー）エラーと呼ばれるものです。
create table の時、primary key に「売上日」と「商品コード」を指定したので、「売上日と商品コードが全く同じデータは1件しか登録できない」という制約が付加されています。
今回は、テーブルが空の状態で「売上日 >= '2020/09/01' and 商品コード='A000001'」に一致するデータをインサートした後に、続けて全件インサートしたためにデータが重複してしまったのです。
このように、プライマリキーを設定することで、誤って二重登録になることを防いでくれます。
drop でテーブルを削除しよう二
作成したテーブルは drop 文で削除することが出来ます。
先ほど作成した、売上明細を削除するには次のように記述します。
drop table 売上明細
実行すると下記の様にてブル一覧から売上明細が消えます。
商品マスタ、商品仕様、売上データのテーブルも同様に drop table で削除できますので、お試しください。
まとめ
テーブルは create table で、テーブル削除は drop table で行うことが出来ます。
create table ではカラムとデータ型を列記していくのですが、使えるデータ型は TEXT,INTEGER,REAL,BLOB の4種類のみです。
primary key を指定すると、同じキーの値を持つレコードが登録できなくなります。
以上の点を踏まえておけば大丈夫です。
本サイトが提供するツールを使うことで、気に drop table を試せますので、是非ご活用ください。
コメント