一覧DB-02-1 · SELECTの基本

SELECTによるデータの抽出

六商品の表を用意し、在庫のある商品の抽出、価格順の表示、二件ずつの取得へ進みます。同じ表からカテゴリの一覧と在庫金額も求め、最後に元の六行が残っていることを確認します。

説明

SELECTは表に保存されたデータを取り出すSQLです。商品一覧を例に、表示する列、対象にする行、結果の並び順と件数を指定します。検索で表示されなかった商品も、元の表から削除されるわけではありません。

六商品の表を用意し、在庫のある商品の抽出、価格順の表示、二件ずつの取得へ進みます。同じ表からカテゴリの一覧と在庫金額も求め、最後に元の六行が残っていることを確認します。

1検索する六商品

商品ID・名前・カテゴリ・単価・在庫数を持つ表を用意します。価格は円単位の整数です。

CREATE TABLE products商品表を作る(id INTEGER PRIMARY KEY,name TEXT,category TEXT,price INTEGER,stock INTEGER) STRICT; INSERT INTO products VALUES六商品を追加(1,'ノート','文具',120,8),(2,'青ペン','文具',100,0),(3,'赤ペン','文具',100,5),(4,'マグ','食器',500,3),(5,'皿','食器',300,0),(6,'袋',NULL,50,12); SELECT * FROM products全列を取得 ORDER BY idID順に表示;

idは行を識別する主キーです。青ペンと皿の在庫は0、袋のカテゴリはNULLで未登録です。priceは単価、stockは個数なので、両者を掛けると在庫金額になります。

2表示する列を選ぶ

一覧には名前と価格だけを表示します。

SELECT name,price表示する二列 FROM products参照する表 ORDER BY id;

SELECTの後へ列を列挙すると、必要な列だけが結果に入ります。行数は六行のままです。FROMは参照する表、ORDER BYは表示順を指定します。

3在庫のある行に絞る

stockが0より大きい商品を取り出します。

SELECT id,name,stock FROM products WHERE stock>0在庫が一つ以上の行 ORDER BY id;

ID 1・3・4・6の四行です。WHEREの条件が真になる行だけが結果に残ります。青ペンと皿は表示対象から外れますが、保存された行は変わりません。

4価格と在庫の両方を条件にする

価格120円以下で、かつ在庫のある商品を取得します。

SELECT id,name,price,stock FROM products WHERE price<=120120円を含む上限 AND stock>0在庫条件も満たす ORDER BY id;

ノート・赤ペン・袋の三商品です。ANDは両方の条件が真の行を残します。価格が条件内でも在庫0の青ペンは入りません。

5安い順に並べる

在庫のある商品を価格の昇順に表示します。

SELECT id,name,price FROM products WHERE stock>0 ORDER BY price ASC,id ASC価格、同価格ならIDの順;

袋50円、赤ペン100円、ノート120円、マグ500円の順です。ASCは小さい順です。第2キーのidを指定しておくと、同じ価格の行があってもIDで順序を決められます。

6高い順と同価格の順序

全商品を高い順にし、同じ価格ではIDの小さい順にします。

SELECT id,name,price FROM products ORDER BY price DESC価格の降順,id ASC同じ価格ならIDの昇順;

マグ、皿、ノート、青ペン、赤ペン、袋の順です。DESCは大きい順です。100円の二行はid 2、3となります。ORDER BYの指定がない検索結果には、一定の並び順を期待できません。

7先頭の二件を取得する

安い順の先頭二件に件数を絞ります。

SELECT id,name,price FROM products ORDER BY price,id順序を一意に決める LIMIT 2先頭から最大二件;

袋と青ペンです。省略時の並び方はASCです。LIMITは結果件数の上限で、保存行を減らす操作ではありません。価格だけだと同価格の順序が未確定なので、ここでもidを添えます。

8次の二件を取得する

同じ順序で先頭二件を飛ばします。

SELECT id,name,price FROM products ORDER BY price,id LIMIT 2返す件数の上限 OFFSET 2先頭二件を飛ばす;

赤ペンとノートです。OFFSET 2で二件を飛ばし、その次から最大二件を返します。この例の表は途中で変わりませんが、検索の間に行が追加・削除されると位置がずれるため、実際のページ分割では更新の影響も考慮します。

9カテゴリの重複を取り除く

まずカテゴリ列をそのまま表示し、その後にDISTINCTを付けます。

SELECT category元の六行のカテゴリ FROM products ORDER BY id; SELECT DISTINCT categoryカテゴリの重複を除く FROM products ORDER BY category;

最初は六行、DISTINCT付きではNULL・文具・食器の三行です。DISTINCTは選んだ列全体の組が重複する結果行をまとめます。カテゴリがNULLの行も一覧へ残ります。SQLiteの昇順では、指定を変えない限りNULLが先です。

10計算した列へ名前を付ける

単価と在庫数から商品ごとの在庫金額を求めます。

SELECT id,name,price*stock単価と個数を掛ける AS stock_value計算列の名前 FROM products ORDER BY id;

ノート960円、青ペン0円、赤ペン500円、マグ1500円、皿0円、袋600円です。ASは結果列の名前を付けます。stock_valueという保存列を追加したのではなく、検索時に式を計算しています。

11整数同士の割り算

価格を100で割り、整数の100と実数の100.0で結果を比べます。

SELECT id,price,price/100整数同士の割り算 AS integer_div,price/100.0実数を含む割算 AS real_div FROM products WHERE id IN (1,6)二つのIDを対象にする ORDER BY id;

SQLiteでは整数同士の割算は小数部分を切り捨てます。120/100は1、50/100は0です。100.0を使うと1.2と0.5になります。表示単位を変える計算でも、数値の型が結果へ影響します。

12元の表は六行のまま

検索後の表をもう一度取得します。

SELECT *保存された全列 FROM products ORDER BY id最初と同じ順序;

価格・在庫・カテゴリは最初の値のままです。列の選択、行の抽出、並べ替え、件数制限、計算列はいずれもSELECTの結果を作る指定であり、元の行を更新していません。

まとめ

SELECTは表示する列、WHEREは対象行を指定します。

ORDER BYと同値時の第2キーで順序を定め、LIMIT/OFFSETで取得範囲を指定します。

DISTINCTと計算列は結果に作用し、保存された元の行は変わりません。

操作履歴と解説を振り返れます。

SQLite 3.54.0

1
CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT,category TEXT,price INTEGER,stock INTEGER) STRICT; INSERT INTO products VALUES(1,'ノート','文具',120,8),(2,'青ペン','文具',100,0),(3,'赤ペン','文具',100,5),(4,'マグ','食器',500,3),(5,'皿','食器',300,0),(6,'袋',NULL,50,12); SELECT * FROM products ORDER BY id;
╭────┬────────┬──────────┬───────┬───────╮
│ id │  name  │ category │ price │ stock │
╞════╪════════╪══════════╪═══════╪═══════╡
│  1 │ ノート │ 文具     │   120 │     8 │
│  2 │ 青ペン │ 文具     │   100 │     0 │
│  3 │ 赤ペン │ 文具     │   100 │     5 │
│  4 │ マグ   │ 食器     │   500 │     3 │
│  5 │ 皿     │ 食器     │   300 │     0 │
│  6 │ 袋     │ NULL     │    50 │    12 │
╰────┴────────┴──────────┴───────┴───────╯
2
SELECT name,price FROM products ORDER BY id;
╭────────┬───────╮
│  name  │ price │
╞════════╪═══════╡
│ ノート │   120 │
│ 青ペン │   100 │
│ 赤ペン │   100 │
│ マグ   │   500 │
│ 皿     │   300 │
│ 袋     │    50 │
╰────────┴───────╯
3
SELECT id,name,stock FROM products WHERE stock>0 ORDER BY id;
╭────┬────────┬───────╮
│ id │  name  │ stock │
╞════╪════════╪═══════╡
│  1 │ ノート │     8 │
│  3 │ 赤ペン │     5 │
│  4 │ マグ   │     3 │
│  6 │ 袋     │    12 │
╰────┴────────┴───────╯
4
SELECT id,name,price,stock FROM products WHERE price<=120 AND stock>0 ORDER BY id;
╭────┬────────┬───────┬───────╮
│ id │  name  │ price │ stock │
╞════╪════════╪═══════╪═══════╡
│  1 │ ノート │   120 │     8 │
│  3 │ 赤ペン │   100 │     5 │
│  6 │ 袋     │    50 │    12 │
╰────┴────────┴───────┴───────╯
5
SELECT id,name,price FROM products WHERE stock>0 ORDER BY price ASC,id ASC;
╭────┬────────┬───────╮
│ id │  name  │ price │
╞════╪════════╪═══════╡
│  6 │ 袋     │    50 │
│  3 │ 赤ペン │   100 │
│  1 │ ノート │   120 │
│  4 │ マグ   │   500 │
╰────┴────────┴───────╯
6
SELECT id,name,price FROM products ORDER BY price DESC,id ASC;
╭────┬────────┬───────╮
│ id │  name  │ price │
╞════╪════════╪═══════╡
│  4 │ マグ   │   500 │
│  5 │ 皿     │   300 │
│  1 │ ノート │   120 │
│  2 │ 青ペン │   100 │
│  3 │ 赤ペン │   100 │
│  6 │ 袋     │    50 │
╰────┴────────┴───────╯
7
SELECT id,name,price FROM products ORDER BY price,id LIMIT 2;
╭────┬────────┬───────╮
│ id │  name  │ price │
╞════╪════════╪═══════╡
│  6 │ 袋     │    50 │
│  2 │ 青ペン │   100 │
╰────┴────────┴───────╯
8
SELECT id,name,price FROM products ORDER BY price,id LIMIT 2 OFFSET 2;
╭────┬────────┬───────╮
│ id │  name  │ price │
╞════╪════════╪═══════╡
│  3 │ 赤ペン │   100 │
│  1 │ ノート │   120 │
╰────┴────────┴───────╯
9
SELECT category FROM products ORDER BY id; SELECT DISTINCT category FROM products ORDER BY category;
╭──────────╮
│ category │
╞══════════╡
│ 文具     │
│ 文具     │
│ 文具     │
│ 食器     │
│ 食器     │
│ NULL     │
╰──────────╯
╭──────────╮
│ category │
╞══════════╡
│ NULL     │
│ 文具     │
│ 食器     │
╰──────────╯
10
SELECT id,name,price*stock AS stock_value FROM products ORDER BY id;
╭────┬────────┬─────────────╮
│ id │  name  │ stock_value │
╞════╪════════╪═════════════╡
│  1 │ ノート │         960 │
│  2 │ 青ペン │           0 │
│  3 │ 赤ペン │         500 │
│  4 │ マグ   │        1500 │
│  5 │ 皿     │           0 │
│  6 │ 袋     │         600 │
╰────┴────────┴─────────────╯
11
SELECT id,price,price/100 AS integer_div,price/100.0 AS real_div FROM products WHERE id IN (1,6) ORDER BY id;
╭────┬───────┬─────────────┬──────────╮
│ id │ price │ integer_div │ real_div │
╞════╪═══════╪═════════════╪══════════╡
│  1 │   120 │           1 │      1.2 │
│  6 │    50 │           0 │      0.5 │
╰────┴───────┴─────────────┴──────────╯
12
SELECT * FROM products ORDER BY id;
╭────┬────────┬──────────┬───────┬───────╮
│ id │  name  │ category │ price │ stock │
╞════╪════════╪══════════╪═══════╪═══════╡
│  1 │ ノート │ 文具     │   120 │     8 │
│  2 │ 青ペン │ 文具     │   100 │     0 │
│  3 │ 赤ペン │ 文具     │   100 │     5 │
│  4 │ マグ   │ 食器     │   500 │     3 │
│  5 │ 皿     │ 食器     │   300 │     0 │
│  6 │ 袋     │ NULL     │    50 │    12 │
╰────┴────────┴──────────┴───────┴───────╯

演習完了