Zenn
🥰

【Jupyter上で完結】SQL(SQLite)を20分でざっくり理解

2021/08/13に公開
SQL
SQLite
DB
tech

1. SQL概要（５分）

そもそもSQLでデータを扱うと何が嬉しいの？

pandasとの違い

具体的にどんなSQLがあるの？

今までSQLiteを取り上げましたが、実はもっといろいろなデータベースエンジンがあります。例えば以下です。
・Oracle （Oracle社）
・SQLServer（Microsoft社）
・MySQL（Oracle社）
・PostgreSQL（OSS）

SQLiteの他のSQLとの違い（長所と短所）

長所

  • Pythonでは標準ライブラリに入っているのでインストールする必要がなく、sqlite3をインポートするだけですぐに使える。
  • 一つのファイルとして扱えるので保存や復元が簡単
  • 軽量かつ高性能
    ファイルベースのRDMS
  • オープンソースで無料
    （SQLite はサーバとして動作させるのではなく単独のアプリケーションとして動作させることが可能）

短所

  • MySQLなど本格的にサーバーに設置して大規模処理するデータベースとは異なり、


2. SQLの基本的な操作(１5分)

【最初のおまじない】

Jupyter Notebook環境でSQLiteを利用できるようにする設定を記述（おまじないのようなもの）

%config SqlMagic.autopandas = True
%config SqlMagic.feedback = True
%load_ext sql

【データベースの操作】

(1)データベース（１つのファイル）を作成

空のデータベースファイルを作成する （同じディレクトリに「data.db」というファイルができていることが確認できる。）

import os

if(os.path.exists('data.db')):
  os.system('rm data.db')
os.system('touch data.db')

(2)データベースに接続

次に、作成したデータベースdata.dbに接続します。

%sql sqlite:///data.db

【テーブルの操作】

基本的な操作として，CREATE，ALTER，DROPの３つを紹介します。

(1)CREATEでテーブルを作成

書式は次のとおりです。

CREATE TABLE テーブル名  (
    カラム名 型 [制約], 
    カラム名 型 [制約], 
    ... 
);

SQLiteで定義できるデータ型は次の通りです。

SQLiteのデータ型

NULL......NULL値
INTEGER...符号付整数。1, 2, 3, 4, 6, or 8 バイトで格納
REAL......浮動小数点数。8バイトで格納
TEXT......テキスト。UTF-8, UTF-16BE or UTF-16-LEのいずれかで格納
DATE......
BLOB......Binary Large OBject。入力データをそのまま格納

試しに１つ人間関係を整理するとして、以下のテーブルを作成してみます。
IF NOT EXISTSは既に同一名のテーブル名がある場合に作成を避けるためのコードです。

%%sql
CREATE TABLE IF NOT EXISTS table_test (id INTEGER PRIMARY KEY, name TEXT ,birthday DATE, age INTEGER);
SELECT * FROM table_test;

最後の行のSELECT * FROM table_test;は、いわゆるSELECT分で、作ったテーブルを表示させるコマンドだと思ってください。
詳しくは【データの中身をいじる】のSELECTの節で後述します。

(2)ALTERでテーブルに変更を加える

テーブルの「変更」には、テーブル名の変更、列の追加、制約の追加や削除が含まれますが、
ここでは、テーブル名の変更と列の追加についてのみ説明します。
テーブル名の変更の書き方

ALTER TABLE 現在のテーブル名 RENAME TO 新しいテーブル名;

%%sql
ALTER TABLE table_test RENAME TO table_new;

列の追加の書き方

ALTER TABLE テーブル名
ADD COLUMN 列名 データ型; 

%%sql
ALTER TABLE table_new ADD COLUMN hight INTEGER NULL;
SELECT * FROM table_new;

(3)DROPでテーブルを削除

DROP TABLE テーブル名

%%sql
DROP TABLE table_new;

【テーブル内のデータの操作】

先ほどのままでは、まだテーブルの骨格だけで中身はありませんでした。
ここからは、テーブル内のデータ（中身）を操作のしかたを紹介します。
主要なコマンドとして、INSERT，SELECT，UPDATE，DELETEなどがあります。

(1)INSERTでテーブルにデータを挿入

上記の時点ではまだ、カラム名とデータ型しか設定されていないので、中身として実際のデータを挿入していきましょう。

%%sql
INSERT INTO table_test (id,name, birthday, age) VALUES (0, date('1994-10-04'), 'Yamada', 10, 181);
INSERT INTO table_test (id,name, birthday, age) VALUES (1, date('1968-11-15'), 'Ueda', 12, 150);
INSERT INTO table_test (id,name, birthday, age) VALUES (2, date('2001-02-19'), 'Jackson', 8, 202);
INSERT INTO table_test (id,name, birthday, age) VALUES (3, date('1998-04-01'), 'Kataoka', 13, 164);

%%sql
SELECT * FROM example;

Discussion