データベースsqlite3 その１２

sqlite3 でのコマンドプロンプトにおけるデータベース構築、操作を、ここでは、実際に「データベース sqlite その１」で示されたものを題材にして、行ってみました。

pc 環境

Windows7 Professional Service Pack 1
Cygwin
sqlite3_0 3.34.0

売り上げ管理システムデータテーブル定義

注文 orders: 注文番号(主キー) 購入者コード(外部キー) 発行日購入金額店コード(外部キー)
購入者 buyer: 購入者コード(主キー) 購入者名
店 branch: 店コード(主キー) 店名
商品 goods: 商品コード(主キー) 商品名単価
注文明細 details: 注文番号(外部キー) 商品コード(外部キー) 数量

データベースを作成する [.databases] [.shell]

takai@takai-pc ~/db
$ mkdir sales_manage

takai@takai-pc ~/db
$ cd sales_manage/

takai@takai-pc ~/db/sales_manage
$ sqlite3 sales_manage.sq3       ①
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .databases  　　②
main: /home/takai/db/sales_manage/sales_manage.sq3 r/w　　③

sales_manage.sq3 というデータベースを作成します。
接続されているデータベースを表示するコマンド。
main は一時データオブジェクトを保持するために予約されている sqliteドットコマンドプロンプト上のデータベース名であります。複数のデータベースに接続する attach 操作をする場合を除き、この場合は、とくに気にする必要が無いです。その後に続くファイルパス名が保存予定のファイル名になります。r/w は読み書きモードで接続されていることを示しております。

ファイルが生成されているか確認してみる。

.shell CMD ARGS...       Run CMD ARGS... in a system shell
                           システムが提供しているシェルコマンドを実行します。

sqlite> .shell ls -la
合計 0
drwxr-xr-x 1 takai None 0  7月  2 15:42 .
drwxr-xr-x 1 takai None 0  7月  2 12:43 ..
-rwxr-xr-x 1 takai None 0  7月  2 15:42 sales_manage.sq3　④

サイズ 0 の読み書き実行ファイルが生成されている。

テーブルを作成（削除）する [CREATE TABLE] [DROP TABLE] [.schema]

CREATE TABLE [IF NOT EXISTS] [database_name.] table_name(
   column1 [datatype] [column definition] 
   column2 [datatype] [column definition],
   column3 [datatype] [column definition],
   .....
   columnN [datatype] [column definition];
* column definition  NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY constraints etc.
DROP TABLE database_name.table_name;

.schema ?PATTERN?        Show the CREATE statements matching PATTERN
   Options:
      --indent             Try to pretty-print the schema
      --nosys              Omit objects whose names start with "sqlite_"  
作成されたsqlステートメントを表示します。
引数にテーブル名を指定すると、そのテーブル名と一致するテーブルのみを表示します

購入者テーブル buyer 作成

sqlite> create table buyer (id integer primary key not null, name text not null);
sqlite> .schema    ⑤
CREATE TABLE buyer (id integer primary key not null, name text not null);
sqlite> .schema buyer
CREATE TABLE buyer (id integer primary key not null, name text not null);

.作成された SQL 文を表示します。テーブル名指定で、テーブル名で作成されたことを確認できます。

店テーブル branch 作成

sqlite> create table branch (id integer primary key not null, name text not null);

商品テーブル goods 作成

sqlite> create table goods (code integer primary key not null, name text not null, price integer not null);

テーブルにデータを入れる１ [INSERT INTO]

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

購入者テーブル buyer にデータを挿入する

sqlite> insert into buyer values (null, 'takai'); ⑥
sqlite> insert into buyer values (null, 'arai');
sqlite> insert into buyer values (null, 'sato');
sqlite> insert into buyer values (null, 'kato');

id 属性に integer primary key 制約がついている場合、null を挿入すると、そのテーブルの rowid の値が反映される。

店テーブル branch にデータを挿入する

sqlite> insert into branch values (101, 'ニコニコ');
sqlite> insert into branch values (null, 'ホッコリ');
sqlite> insert into branch values (null, 'アッタカ');

商品テーブル goods にデータを挿入する

sqlite> insert into goods values (101, 'アッタカカイロ', 150);
sqlite> insert into goods values (201, 'コーンスープ', 200);
sqlite> insert into goods values (203, 'トン汁', 300);
sqlite> insert into goods values (303, 'ホカホカ弁当', 450);
sqlite> insert into goods values (304, '梅干おにぎり', 90);
sqlite> insert into goods values (305, '塩さけおにぎり', 110);
sqlite> insert into goods values (402, '座椅子', 2000);

テーブルに格納されているデータを表示[SELECT .. FROM] [.show] [.mode] [.tables]

SELECT column1, column2....columnN
FROM table_name;

.show  Show the current values for various settings
　　　　画面の設定情報などを表示する

.mode mode_name 画面出力モードを設定する
    csv -カンマ区切り値
    column -左揃えの列。
    html − HTML <table> コード
    insert - TABLE の SQL 挿入ステートメント
    column - 行ごとに1つの値
    list - .separator文字列で区切られた値
    tabs −タブ区切りの値
    tcl − TCL リスト要素

sqlite> select * from buyer;
1|takai  ⑦
2|arai
3|sato
4|kato
sqlite> .show   ⑧
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: sales_manage.sq3
sqlite> .explain on  ⑨
sqlite> select * from buyer;
id    name
----  -------------
1     takai
2     arai
3     sato
4     kato
sqlite> .show
        echo: off
         eqp: off
     explain: on
     headers: off
        mode: explain
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: sales_manage.sq3
sqlite> .mode column  ⑩
sqlite> select * from buyer;
id  name
--  -----
1   takai
2   arai
3   sato
4   kato

デフォルトでは、’｜’区切りのリストモードの表示となる。
.show コマンドは現在の表示設定値を表示するコマンドです。mode:list となっている。表示を替えたいときには、個々に表示されているコマンドの設定値をたとえば「.mode column」のようにかえればよい。
explain を on にすると見やすい表示になるようです。
mode を column にしても見た目は同じですが、表示設定値の違う箇所があるので、ファイル出力に切り替えるときには注意が必要です。

作成されているテーブルデータを確認する

作成されたテーブルを確認する[.tables]

.tables  ?TABLE?          List names of tables matching LIKE pattern TABLE
 　　　　　　　　　　　　　接続されたデータベース内のすべてのテーブルを一覧表示する

sqlite> .tables  ⑪
branch  buyer   goods
sqlite> .schema
CREATE TABLE buyer (id integer primary key not null, name text not null);
CREATE TABLE branch (id integer primary key not null, name text not null);
CREATE TABLE goods (code integer primary key not null, name text not null, price integer not null);

tables コマンドで、作成されているテーブル名を確認できます。

管理用に暗黙のうちに生成される sqlite_master テーブル

マスターテーブルは、データベーステーブルに関する重要な情報を保持し、sqlite_masterと呼ばれます。

sqlite> .schema sqlite_master  ⑫
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
sqlite> .mode line
sqlite> select * from sqlite_master;  ⑬
    type = table
    name = buyer
tbl_name = buyer
rootpage = 2
     sql = CREATE TABLE buyer (id integer primary key not null, name text not null)

    type = table
    name = branch
tbl_name = branch
rootpage = 3
     sql = CREATE TABLE branch (id integer primary key not null, name text not null)

    type = table
    name = goods
tbl_name = goods
rootpage = 4
     sql = CREATE TABLE goods (code integer primary key not null, name text not null, price integer not null)

テーブルを生成されると、マスターテーブルが暗黙のうちに生成される。
生成されたテーブル情報を表示する。

テーブルを作成する２[FOREIGN KEY] [pragma] [DEFAULT]

続いて、注文 orders テーブルと注文明細 detailsを作っていきます。

但し、これらのテーブルの要素には「テーブルを作成する１」で作った要素を参照する外部キーが存在します。FOREIGN KEY構文でキー参照の構文を作ることになります。sqlite3 では、バージョン 3.6.19 (2009-10-14) の時点以降でサポートされている。

FOREIGN KEY(key name) REFERENCES table name(key name)

外部キーは以下の特徴があります。

外部キー制約の適用を有効にするには、 PRAGMA foreign_keys=ONを実行するか、 -DSQLITE_DEFAULT_FOREIGN_KEYS=1でコンパイルします。
外部キー制約のリファレンスキーは、ROWID を使用できません。リファレンスキーは名前付き列のみを使用する必要があります。
リファレンスキーはリファレンスするテーブルの主キーである場合が多いですが、主キーでない場合は、uniqueである必要があります。また、主キーの場合は「(key name)」は省略できます。
後述のとおり、インライン表記で記述できます。
外部キー宣言の最後にカンマを記入しないでください。
列名が間違っている場合、外部キーは作成されますが機能しません

注文テーブル orders 作成

sqlite> .mode column
sqlite> pragma foreign_keys;  ⑭
foreign_keys
------------
0
sqlite> pragma foreign_keys = on;  ⑮
sqlite> pragma foreign_keys;
foreign_keys
------------
1
sqlite> create table orders (
   ....   id integer primary key not null,
   ...>   buyer_id integer not null,
   ...>   publish text,
   ...>   branch_id integer not null,
   ...>   amount integer default 0,  ⑯
   ...>   foreign key(buyer_id) references buyer(id)  ⑰
   ...>   foreign key(branch_id) references branch(id)  ⑰
   ...> );

SQLite version 3.6.19 (2009-10-14) 以降において、foreign keyの使用は pragma で設定する必要があります。pragma は SQLite 環境内のさまざまな環境変数と状態フラグを制御するために使用される特別なコマンドです。
設定を on にすると、foreign key の値が 1 にセットされ、off にすると 0 がセットされます。
DEFAULT 句は、 INSERT の実行時にユーザーが値を明示的に指定しない場合に列に使用するデフォルト値を指定します。列定義に明示的な DEFAULT 句が付加されていない場合、列のデフォルト値は NULL です。デフォルト値が NULL、文字列定数、blob 定数、signed-number、または括弧で囲まれた任意の定数式であることを指定できます。
ここでは、orders テーブルの buyer_id キーが buyer テーブルの id キーを、branch_id キーが branch テーブルの id キーを参照しています。buyer テーブルの主キーである id と branch テーブルの主キーである id は省略できます。それぞれ「references buyer references branch」と記述しても良い。

注文明細テーブル details 作成

sqlite> create table details (
   ...>  orders_id integer primary key not null,
   ...>  goods_id integer primary key not null,
   ...>  quantity integer not null,
   ...> foreign key(orders_id) references orders(id), foreign key(goods_id) references goods(code);
Error: table "details" has more than one primary key ⑱

sqlite> create table details (
 -- foreign key のインライン式書き方、親キー名省略
   ...>   orders_id integer not null references orders,
   ...>   goods_id integer not null references goods,
   ...>   quantity integer not null
-- primary key(orders_id, goods_id)  主キーが複数ある場合の書き方、ここでは主キーは設定しないことにする。
   ...> );

データをモデル化した段階では、注文詳細テーブル details は注文番号と商品コードを合わせたものがテーブルデータを一意にするキーの組み合わせになっております。sql 文では、– コメント記述のように複合プライマリーキーを定義できますが、後述、details データの追加仕様により、この組み合わせは、必ずしも一意ではないので「SELECT column FROM table name WHERE rowid=taget rowid;」のよう名方法で、レコードを特定することになります。

テーブルにデータを入れる２ [INSERT INTO]

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

注文テーブル orders にデータを挿入する

sqlite> insert into orders values (
   ...>   2101001, 1, date('2021-01-01'), 101, 3350); ⑲
sqlite> insert into orders values(
   ...>   2101002, 2, date('2021-01-01'), 102, 2000);

sqlite> insert into orders values(
   ...>   2102001, 3, date('2021-02-02'), 103, 850);
sqlite> insert into orders values(
   ...>   2102002, 4, date('2021-02-02'), 101, 310);

日付文字列は定義されている正しいフォーマットで記入する必要があります。(ex. YYYY-MM-DD)

注文詳細テーブル details にデータを挿入する

sqlite> insert into details values(
   ...> 111, 101, 3);  -- orders_id 111 unsetted data!
Error: foreign key mismatch - "details" referencing "orders"

sqlite> insert into details values(
   ...> 2101001, 999, 3);  -- goods_id 999 unsetted data!
Error: foreign key mismatch - "details" referencing "orders"  -- orders ??

sqlite> insert into details values(
   ...> 2101001, 101, 3);
sqlite> insert into details values(
   ...> 2101001, 201, 10);
sqlite> insert into details values(
   ...> 2101001, 303, 2);
sqlite> insert into details values(
   ...> 2101002, 402, 1);
sqlite> insert into details values(
   ...> 2102001, 201, 2);
sqlite> insert into details values(
   ...> 2102001, 203, 3);
sqlite> insert into details values(
   ...> 2102002, 304, 1);
sqlite> insert into details values(
   ...> 2102002, 305, 2);
sqlite> insert into details values(
   ...> 2102002, 303, 2);

FOREIGN KEY が正しくセットされているかチェックする[SELECT FROM][DELETE FROM]

SELECT column1, column2, columnN FROM table_name [WHERE {CONDITION}];
DELETE FROM table_name WHERE {CONDITION};

sqlite> .schema orders
CREATE TABLE orders (id integer primary key not null, buyer_id integer not null, publish text, branch_id integer not null, amount integer defaul
foreign key(buyer_id) references buyer(id),
foreign key(branch_id) references branch(id));
sqlite> select * from buyer;
id  name
--  -----
1   takai
2   arai
3   sato
4   kato

sqlite> select * from branch;
id   name
---  ----
101  ニコニコ
102  ホッコリ
103  アッタカ

---- 外部キー制約の設定を外してみる(デフォルトの状態)
sqlite> pragma foreign_keys = off;

sqlite> insert into orders values(
   ...> 2102003, 5, date('2021-02-02'), 103, 1000); -- 存在しない親キーを指定
sqlite> pragma foreign_keys;
foreign_keys
------------
0

sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount
-------  --------  ----------  ---------  ------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310
2102003  5         2021-02-02  103        1000  -- buyer_id の親キーは存在しない

---- データ挿入前に戻す                                                                                                                                                                                                                                                                                       
sqlite> delete from orders where id = 2102003;
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount
-------  --------  ----------  ---------  ------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310

---- 外部キーが使用できるようにして、参照できないデータ buyer_id=5 の挿入してみる　
sqlite> pragma foreign_keys = on;  
sqlite> insert into orders values(
   ...> 2102003, 5, date('2021-02-02'), 103, 1000);
Error: FOREIGN KEY constraint failed

---- 参照できないデータ branch_id=104 の挿入
sqlite> insert into orders values(
   ...> 2102003, 4, date('2021-02-02'), 104, 1000);
Error: FOREIGN KEY constraint failed

データベースsqlite3 その１２

pc 環境

売り上げ管理システムデータテーブル定義

データベースを作成する [.databases] [.shell]

テーブルを作成（削除）する [CREATE TABLE] [DROP TABLE] [.schema]

購入者テーブル buyer 作成

店テーブル branch 作成

商品テーブル goods 作成

テーブルにデータを入れる１ [INSERT INTO]

購入者テーブル buyer にデータを挿入する

店テーブル branch にデータを挿入する

商品テーブル goods にデータを挿入する

テーブルに格納されているデータを表示[SELECT .. FROM] [.show] [.mode] [.tables]

作成されているテーブルデータを確認する

作成されたテーブルを確認する[.tables]

管理用に暗黙のうちに生成される sqlite_master テーブル

テーブルを作成する２[FOREIGN KEY] [pragma] [DEFAULT]

注文テーブル orders 作成

注文明細テーブル details 作成

テーブルにデータを入れる２ [INSERT INTO]

注文テーブル orders にデータを挿入する

注文詳細テーブル details にデータを挿入する

FOREIGN KEY が正しくセットされているかチェックする[SELECT FROM][DELETE FROM]

いいね:

関連

コメントを残すコメントをキャンセル

pc 環境

売り上げ管理システム データテーブル定義

データベースを作成する [.databases] [.shell]

テーブルを作成（削除）する [CREATE TABLE] [DROP TABLE] [.schema]

購入者テーブル buyer 作成

店テーブル branch 作成

商品テーブル goods 作成

テーブルにデータを入れる１ [INSERT INTO]

購入者テーブル buyer にデータを挿入する

店テーブル branch にデータを挿入する

商品テーブル goods にデータを挿入する

テーブルに格納されているデータを表示[SELECT .. FROM] [.show] [.mode] [.tables]

作成されているテーブルデータを確認する

作成されたテーブルを確認する[.tables]

管理用に暗黙のうちに生成される sqlite_master テーブル

テーブルを作成する２[FOREIGN KEY] [pragma] [DEFAULT]

注文テーブル orders 作成

注文明細テーブル details 作成

テーブルにデータを入れる２ [INSERT INTO]

注文テーブル orders にデータを挿入する

注文詳細テーブル details にデータを挿入する

FOREIGN KEY が正しくセットされているかチェックする[SELECT FROM][DELETE FROM]

いいね:

関連

関連タグ

コメントを残す コメントをキャンセル

売り上げ管理システムデータテーブル定義

コメントを残すコメントをキャンセル