🐥

はじめてのsqlite ~テーブルを新規作成~

2024/08/31に公開

Database

SQLite

tech

最初に

概要

データベースをいじれるようになりたいと思い、sqliteなら手軽に始められそうだったので、はじめてデータベースを作ってみました。
最低限のテーブルは作れたので、基本的な操作を記録として残しておきます。
テーブルの新規作成、レコードの新規登録、フィールドの追加、日時データの扱い方、レコードの更新などを行います。
今回作りたいのは、下記のような社員リストのデータベース。

ID	登録日時	氏名	生年月日	血液型
1	2024-08-31 17:30:46	山口	2001/4/4	A
2	2024-08-31 17:30:46	花橋	2002/2/4	B
3	2024-08-31 17:30:46	勝村	2002/4/8	O
4	2024-08-31 17:30:46	大江	2002/10/26	AB
5	2024-08-31 17:30:46	新井	2003/6/20	A
6	2024-08-31 17:30:46	小合	2003/11/13	A
7	2024-08-31 17:30:46	中林	2004/3/26	O
8	2024-08-31 17:30:46	鈴野	2004/8/12	O
9	2024-08-31 17:30:46	ヒルマン	2005/2/27	O

今回の記事の主要参考記事

その他の参考記事は適宜記載

コマンドが終了できない場合

結構やりがち。コマンドの最後に“;”を入れて抜けられる。

sqlite> quit
   ...> .quit
   ...> ;

sqliteを開始

データベースファイルを開くだけじゃなく、新規で作成するときもこれでOK。

sqlite> .open <YOUR_DB_FILENAME.db>

.open employees.db

テーブルの作成

CREATE TABLE テーブル名(カラム名1 データ型, カラム名2 データ型, ...)

CREATE TABLE employees (id integer primary key autoincrement, store_at text, name text, birthday text);

テーブルの存在確認をする場合

create table if not exists テーブル名 (
 フィールド名 データ型,
 フィールド名 データ型,
 フィールド名 データ型
);

データ型

データ型	説明
integer	整数
real	浮動点小数
text	文字列
blob	データの保存
null	カラ

Primary keyとautoincrementを設定

tableの確認

.tables

.tables
employees

構造の確認

コマンド

.schema // or .schema TABLE_NAME

結果

sqlite> .schema
CREATE TABLE employees (id integer primary key autoincrement, store_at text, name text, birthday text);

フィールド(column)の確認

PRAGMA table_info('<YOUR_TABLE_NAME>');

sqlite> PRAGMA table_info("employees");

0|id|INTEGER|0||1
1|store_at|TEXT|0||0
2|name|TEXT|0||0
3|birthday|TEXT|0||0

CRUD

SQL文	構文	注意
INSERT	INSERT INTO <TABLE> (column1, column2) VALUES(data1, data2);
SELECT	SELECT * FROM <YOUR_TABLE_NAME>;
UPDATE	UPDATE <TABLE> SET <COLUMN>=”value” WHERE <主キー>=1;	where を付けないと全レコード更新されてしまうので注意
DELETE	DELETE FROM <TABLE> WHERE <主キー>=1;

INSERT INTO employees(name, store_at) VALUES("John", datetime('now', '+9 hours'));
SELECT * FROM employees;
UPDATE employees SET blood_type=”A” WHERE id=1;
DELETE FROM employees WHERE id=1;

sqliteで時刻を扱う

日付時刻に関する関数(基本的にUTCで扱われる)

sqlite> SELECT date('2024-08-31');
2024-08-31 ---UTC
        
sqlite> SELECT datetime('2024-08-31');
2024-08-31 00:00:00 ---UTC

sqlite> SELECT strftime('%Y年%m月%d日', '2024-08-31');
2024年08月31日 ---UTC

日本時間にする

sqlite> SELECT datetime('2024-08-31', '+9 hours');
2024-08-31 09:00:00   ---UTCをJSTに

現在日時を取得する

sqlite> SELECT datetime('now', '+9 hours'); ---UTCをJSTに
2024-08-31 11:35:57

外部ファイルを読み込む

処理内容を外部のsqlファイルに記述し、実行することができる

.read ファイル名.sql

エクセルの一覧表から作ったsqlファイルで一括登録

--insert_all.sql
insert into employees(name, birthday, store_at) values("山口","2001-04-04", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("花橋","2002-02-04", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("勝村","2002-04-08", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("大江","2002-10-26", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("新井","2003-06-20", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("小合","2003-11-13", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("中林","2004-03-26", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("鈴野","2004-08-12", datetime("now", "+9 hours"));
insert into employees(name, birthday, store_at) values("ヒルマン","2005-02-27", datetime("now", "+9 hours"));

実行してみる

sqlite> .read insert_all.sql
sqlite> select * from employees;
id  store_at             name  birthday  
--  -------------------  ----  ----------
1   2024-08-31 17:30:46  山口    2001-04-04
2   2024-08-31 17:30:46  花橋    2002-02-04
3   2024-08-31 17:30:46  勝村    2002-04-08
4   2024-08-31 17:30:46  大江    2002-10-26
5   2024-08-31 17:30:46  新井    2003-06-20
6   2024-08-31 17:30:46  小合    2003-11-13
7   2024-08-31 17:30:46  中林    2004-03-26
8   2024-08-31 17:30:46  鈴野    2004-08-12
9   2024-08-31 17:30:46  ヒルマン  2005-02-27

フィールド(column)の追加

ALTERコマンド: テーブル名の変更や、フィールドの追加など。フィールド名は変更できない

// alter table <TABLE_NAME> add column <FILED_NAME> <TYPE>
alter table employees add column blood_type text

※ フィールド(Column)の修正や削除はできない！

実際にやってみる

sqlite> alter table employees add column blood_type text
sqlite> pragma table_info("employees");

cid  name        type     notnull  dflt_value  pk
---  ----------  -------  -------  ----------  --
0    id          INTEGER  0                    1 
1    store_at    TEXT     0                    0 
2    name        TEXT     0                    0 
3    birthday    TEXT     0                    0 
4    blood_type  TEXT     0                    0

血液型を一括登録してみる

--update.sql
update employees set blood_type="A" where id=1;
update employees set blood_type="B" where id=2;
update employees set blood_type="O" where id=3;
update employees set blood_type="AB" where id=4;
update employees set blood_type="A" where id=5;
update employees set blood_type="A" where id=6;
update employees set blood_type="O" where id=7;
update employees set blood_type="O" where id=8;
update employees set blood_type="O" where id=9;

sqlite> .read update.sql
sqlite> select * from employees;
id  store_at             name  birthday    blood_type
--  -------------------  ----  ----------  ----------
1   2024-08-31 17:30:46  山口    2001-04-04  A         
2   2024-08-31 17:30:46  花橋    2002-02-04  B         
3   2024-08-31 17:30:46  勝村    2002-04-08  O         
4   2024-08-31 17:30:46  大江    2002-10-26  AB        
5   2024-08-31 17:30:46  新井    2003-06-20  A         
6   2024-08-31 17:30:46  小合    2003-11-13  A         
7   2024-08-31 17:30:46  中林    2004-03-26  O         
8   2024-08-31 17:30:46  鈴野    2004-08-12  O         
9   2024-08-31 17:30:46  ヒルマン  2005-02-27  O

終了

.exit