SQL入門

SQLとは

SQL構文（CRUD操作）

SQL構文（SELECT文）

PythonでSQLiteを使ってみる

SQLとは

SQLはリレーショナルデータベースを操作するためのプログラミング言語。MySQLやPostgreSQLなど、種類によって細かな違いはあるものの、基本的なSQL構文は共通している。そのため、いずれかのSQLを習得できれば、汎用的に利用できる。

SQL構文（CRUD操作）

SQL構文のCRUD操作一覧を以下にまとめる。

処理内容	構文
テーブル作成	CREATE TABLE テーブル名( 列名1 列1の型名, 列名2 列2の型名, … );
テーブル削除	DROP TABLE テーブル名;
データの取得	SELECT カラム名1, カラム名2, … FROM テーブル名 ;
データの登録	INSERT INTO テーブル名(カラム名1, カラム名2, …) VALUES (値1, 値2, …) ;
データの更新	UPDATE テーブル名 SET カラム名 = 値, カラム名 = 値 WHERE 条件式 ;
データの削除	DELETE FROM テーブル名 WHERE 条件式 ;

SQL構文（SELECT文）

SQL構文のSELECT文の操作一覧を以下にまとめる。データベースエンジニアでない限り、直接データベースの中身を書き換えることは少なく、実務で利用するのはほとんどがSELECT文である。

処理内容	構文
取得するデータ数の指定	SELECT * FROM テーブル名 LIMIT 行数 ;
取得するデータの開始位置の指定	SELECT * FROM テーブル名 LIMIT 行数 OFFSET 開始位置 ;
条件指定	SELECT * FROM テーブル名 WHERE 条件式 ;
条件指定（Null判定）	SELECT * FROM テーブル名 WHERE カラム名 IS NOT NULL ;
条件指定（部分一致）	SELECT * FROM テーブル名 WHERE カラム名 LIKE ‘%文字列%’ ;
条件指定（範囲指定）	SELECT * FROM テーブル名 WHERE カラム名 BETWEEN 1000 AND 2000 ;
条件指定（列挙）	SELECT * FROM テーブル名 WHERE カラム名 IN (値1, 値2, …) ;
条件指定（いずれかが真）	SELECT * FROM テーブル名 WHERE カラム名 =< ANY (値1,値2, ...) ;
条件指定（すべてが真）	SELECT * FROM テーブル名 WHERE カラム名 =< ALL (値1,値2, ...) ;
重複行の削除	SELECT DISTINCT カラム名 FROM テーブル名 ;
並べ替え	SELECT * FROM テーブル名 ORDER BY カラム名 DESC ;
和集合	SELECT * FROM テーブル名 UNION SELECT * FROM テーブル名
差集合	SELECT * FROM テーブル名 EXCEPT SELECT * FROM テーブル名
積集合	SELECT * FROM テーブル名 INTERSECT SELECT * FROM テーブル名
条件分岐	SELECT *, CASE WHEN 条件式1 THEN 値1 WHEN 条件式2 THEN 値2 ELSE 値3 END AS 新しいカラム名 FROM テーブル名 ;
関数（文字列型）	SELECT *, LENGTH(カラム名) AS len, TRIM(カラム名) AS trim, REPLACE(カラム名, '変換前の文字列', '変換後の文字列') AS rep, SUBSTRING(カラム名, 1, 3) AS sub FROM テーブル名 ;
関数（数値型）	SELECT *, ROUND(カラム名, -2) AS round, TRUNC(カラム名, 1) AS trunc, POWER(カラム名, 2) AS power FROM テーブル名 ;
関数（型変換）	SELECT *, CAST(カラム名 AS VARCHAR(20) + ‘円’ AS cast) FROM テーブル名 ;
集計関数	SELECT COUNT(カラム名) AS count, SUM(カラム名) AS sum, MAX(カラム名) AS max, MIN(カラム名) AS min, AVG(カラム名) AS avg FROM テーブル名 ;
グループ別の集計	SELECT SUM(カラム名2) AS sum FROM テーブル名 GROUP BY カラム名1 ;
副問合せ（サブクエリ）	SELECT * FROM テーブル名1 WHERE id IN( SELECT カラム名 FROM テーブル名2 WHERE 条件式 );
内部結合	SELECT * FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル1.カラム名 = テーブル2.カラム名 ;
外部結合	SELECT * FROM テーブル名1 LEFT JOIN テーブル名2 ON テーブル名1.カラム名 = テーブル名2.カラム名 ;

PythonでSQLiteを使ってみる

CRUD操作

VSCodeの拡張機能「SQLite Viewer」をインストールする

任意の場所にsample.pyファイルを作成し、以降ここにコードを記載していく。

以下のコードを実行し、商品テーブルを作成する。

import sqlite3

conn = sqlite3.connect("sample.db")
cur = conn.cursor()

query = """
    CREATE TABLE items(
        item_id INTEGER PRIMARY KEY AUTOINCREMENT,
        item_name STRING,
        price INTEGER
    );
"""

cur.executescript(query)
conn.commit()
conn.close()

※ item_idはAUTOINCREMENTに設定されていて、DB側で自動でIDを重複しないように降ってくれる。

生成されたsample.dbファイルを開いて、商品テーブルが作成されていることを確認する

※ 以降、コードを実行した後に更新ボタンを押し、テーブルの状態を確認していく。

以下のコードを実行し、商品テーブルに商品を追加する

import sqlite3

conn = sqlite3.connect("sample.db")
cur = conn.cursor()

query = """
    INSERT INTO items(
        item_name, price
    ) VALUES(
        'お茶', 120
    ), (
        'おにぎり', 140
    ), (
        '卵', 220
    );
"""

cur.executescript(query)
conn.commit()
conn.close()

▼プレビュー

以下のコードを実行し、商品テーブルのデータを更新する

import sqlite3

conn = sqlite3.connect("sample.db")
cur = conn.cursor()

query = """
    UPDATE items SET price=160 WHERE item_id=2;
    UPDATE items SET item_name='うずらの卵', price=150 WHERE item_name='卵';
"""

cur.executescript(query)
conn.commit()
conn.close()

▼プレビュー

以下のコードを実行し、商品テーブルから商品を削除する

import sqlite3

conn = sqlite3.connect("sample.db")
cur = conn.cursor()

query = """
    DELETE FROM items WHERE item_id=2;
"""

cur.executescript(query)
conn.commit()
conn.close()

▼プレビュー

以下のコードを実行し、商品テーブルを削除する

import sqlite3

conn = sqlite3.connect("sample.db")
cur = conn.cursor()

query = """
    DROP TABLE items;
"""

cur.executescript(query)
conn.commit()
conn.close()

▼プレビュー

SELECT文

以下のコードを実行し、注文テーブル、注文詳細テーブルを作成する

import sqlite3

conn = sqlite3.connect("sample.db")
cur = conn.cursor()

query = """
    CREATE TABLE orders(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        customer_id INTEGER,
        price INTEGER,
        payment_date
    );
    INSERT INTO orders(
        customer_id, price, payment_date
    ) VALUES(
        1, 1300, '2022-03-23'
    );
    INSERT INTO orders(
        customer_id, price, payment_date
    ) VALUES(
        2, 840, '2022-03-27'
    );
    INSERT INTO orders(
        customer_id, price, payment_date
    ) VALUES(
        3, 2500, '2022-03-27'
    );
    INSERT INTO orders(
        customer_id, price, payment_date
    ) VALUES(
        2, 720, '2022-04-02'
    );
    INSERT INTO orders(
        customer_id
    ) VALUES(
        4
    );
    CREATE TABLE order_details(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER,
        item STRING,
        quantity INTEGER
    );
    INSERT INTO order_details(
        order_id, item, quantity
    ) VALUES(
        3, 'お茶', 1
    );
    INSERT INTO order_details(
        order_id, item, quantity
    ) VALUES(
        2, 'おにぎり', 2
    );
    INSERT INTO order_details(
        order_id, item, quantity
    ) VALUES(
        4, 'アイス', 1
    );
    INSERT INTO order_details(
        order_id, item, quantity
    ) VALUES(
        1, 'コーヒー', 1
    );
"""

cur.executescript(query)
conn.commit()
conn.close()

以下のコードを実行し、データを抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT *
    FROM orders
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、カラムを絞ってデータを抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT customer_id, price
    FROM orders
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、データ数を絞ってデータを抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT *
    FROM orders
    LIMIT 2
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、値段で降順に並べ替えてデータを抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT *
    FROM orders
    ORDER BY price DESC
    LIMIT 5
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、条件を指定してデータを抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT *
    FROM orders
    WHERE price < 2000 AND payment_date > '2022-03-25'
    LIMIT 5
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、値がNULL以外のデータを抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT *
    FROM orders
    WHERE price IS NOT NULL
    LIMIT 5
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、新しいカラムに条件分岐した値を入れる。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT 
      customer_id,
      price,
      CASE
        WHEN price > 1000 THEN 'High'
        WHEN price < 1000 THEN 'Low'
        ELSE '???'
      END AS price_label
    FROM orders
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、算術演算子を用いて新しいカラムを作成する

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT 
      customer_id,
      price,
      price * (1+0.08) AS taxed_price
    FROM orders
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、集計関数を用いて集計を実施する

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT 
      COUNT(price) AS count,
      SUM(price) AS sum,
      MAX(price) AS max,
      MIN(price) AS min,
      AVG(price) AS avg
    FROM orders
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、重複を削除する

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT DISTINCT customer_id
    FROM orders
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、グループごとに集計する

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT customer_id, sum(price) AS sum
    FROM orders
    GROUP BY customer_id
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、条件抽出した後にグループ別に集計する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT customer_id, sum(price)
    FROM orders
    WHERE payment_date > '2022-03-25'
    GROUP BY customer_id
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、グループ別に集計した後に条件抽出する。

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT customer_id, sum(price)
    FROM orders
    GROUP BY customer_id
    HAVING sum(price) > 2000
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、内部結合を実施する

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT
      orders.customer_id,
      orders.price,
      order_details.item,
      order_details.quantity
    FROM orders
    INNER JOIN order_details
    ON orders.id = order_details.order_id
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

以下のコードを実行し、外部結合を実施する

import pandas as pd
import sqlite3

conn = sqlite3.connect("sample.db")

query = """
    SELECT
      orders.customer_id,
      orders.price,
      order_details.item,
      order_details.quantity
    FROM orders
    LEFT JOIN order_details
    ON orders.id = order_details.order_id
    ;
"""

df = pd.read_sql(query, conn)
print(df)
conn.commit()
conn.close()

▼出力結果

付録A - SELECT文の実行順序

SELECT文の実行順序は基本的に以下のようになっている。この順序を頭に入れておくことでSQLの挙動について理解しやすくなるので覚えておくと良い。

1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. UNION 7. ORDER BY 8. DISTINCT

付録B - 内部結合と外部結合

内部結合と外部結合については以下の記事が視覚的にわかりやすく説明されている。

Visual Representation of SQL Joins

参考資料

Pythonでデータベース入門

【SQLiteで学ぶ】ゼロから始めるデータベースとSQL超入門

【SQL】SQLを読み解く前に知っておきたいこと

【これだけ覚えてたらOK！】SQL構文まとめ

ゆうき

2018/04からITエンジニアとして活動、2021/11から独立。主な使用言語はPython, TypeScript, SAS, etc.