🙌

学習備忘録〜O'Reilly「初めてのSQL」〜

2023/12/27に公開

SQL

tech

はじめに

この備忘録は、新卒1年目の初学者バックエンドエンジニアの学習記録のためにつけているものです。
解釈違いや、誤情報がある可能性があります。見つけた際にはご指摘をお願いします。

入門：クエリ

クエリの仕組み

MySQLサーバがユーザ名とパスワードを確認し、接続を作成した時点で、クエリ（およびそのほかのSQL文）を実行する準備が整う。クエリがサーバに送信されるたびに、サーバが文の実行に先立って以下の点を確認する。

この文を実行するためのアクセス許可がそのユーザにあるか
目的のデータにアクセスするための許可がそのユーザにあるか
この文の構文は正しいか

上記3点のテストを通過すると、クエリオプティマイザに渡される。
クエリオプティマイザ
役割：クエリを実行するための最も効率の良い方法を判断すること

サーバがクエリの実行を終了すると、呼び出し元のアプリケーションに結果セットを返却する。
結果セット
行と列からなる新しいテーブルのこと

結果が何も得られなかった場合、以下の最後の行に示すようなメッセージが返却される。

mysql> SELECT first_name, last_name
    -> FROM customer
    -> WHERE last_name = 'ZIEGER';
Empty set (0.02 sec)

一方で、１行以上の返却があった場合には以下のような形で表示される。

mysql> SELECT *
    -> FROM category;
+-------------+----------+---------------------+
| category_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | Action   | 2023-12-27 15:00:00 |
|           2 | Music    | 2023-12-27 16:00:00 |
+-------------+----------+---------------------+
2 rows in set (0.00 sec)

クエリの句

select文はいくつかの要素で構成される。これらの要素を句または節と呼ぶ。
select文で利用可能な句は主に6つある。

句の名前	目的
select	クエリの結果をセットに含める列を決める
from	データを取得するテーブルと、テーブルを結合する方法を特定する
where	不要なデータを取り除く
group by	共通の列の値に基づいて行をグループ化する
having	不要なグループを取り除く
order by	1つ以上の列に基づいて最終的な結果セットの行を並び替える

select句

select句はselect文の最初の句だが、データベースサーバが最後に評価する句の1つ。

mysql> SELECT *
    -> FROM language;
+-------------+-------------+------------------------+
| language_id | name        | last_update            |
+-------------+-------------+------------------------+
|           1 | English     | 2023-12-27 15:00:00    |
|           2 | Japanese    | 2023-12-27 15:00:00    |
|           3 | Italian     | 2023-12-27 15:00:00    |
|           4 | German      | 2023-12-27 15:00:00    |
+-------------+-------------+------------------------+
4 rows in set (0.00 sec)

上記は、languageテーブルの行と列を全て表示せよ
という意味になる。

mysql> SELECT name
    -> FROM language;
+-------------+
| name        | 
+-------------+
| English     |
| Japanese    |
| Italian     |
| German      | 
+-------------+
4 rows in set (0.00 sec)

上記は、languageテーブルの行とname列を表示せよ
となる。

select句の役割は、利用可能なすべての列のうちどれをクエリの結果セットに追加するべきかを判断することである。

重複を取り除く

場合によっては、クエリから重複するデータが返却されることもある。そういった場合には、selectキーワードの直後にdistinctキーワードを追加する。

mysql> SELECT DISTINCT actor_id FROM film_actor ORDER BY actor_id;

from句

from句は、クエリに使うテーブルと、テーブル同士をリンクする方法を定義する

テーブル

関連する行の集合のみに着目することで、広い定義に該当するテーブルとして以下の4種類がある。

永続テーブル（create table文を使って作成したテーブル）
派生テーブル（サブクエリによって返され、メモリ内で保持される行セット）
一時テーブル（メモリ内の揮発性データ）
仮想テーブル（create view文を使って作成したテーブル）

永続テーブルを追加する方法はselect句で記載のため省略

サブクエリによって生成された派生テーブル

サブクエリとは

別のクエリの中に含まれるクエリのこと

サブクエリは丸かっこで囲まれ、select文の様々な部分で使うことができる。
ただし、from句で使用する場合には、サブクエリは派生テーブルを生成するという役割を果たす。派生テーブルは、クエリの他の句から参照できるテーブルであり、from句に指定された他のテーブルと併せて処理できる。

mysql> SELECT concat(cust.last_name, ', ', cust.first_name ) full_name
    -> FROM
    -> ( SELECT first_name, last_name, email
    -> FROM customer
    -> WHERE first_name ='JESSIE'
    -> ) cust;
    +---------------+
    | BANKS, JESSIE |
    | MILAM, JESSIE |
    +---------------+
    2 row in set (0.01 sec)

一時テーブル

RDBには、揮発性のテーブル、つまり、一時的なテーブルを作成する機能がある。これらのテーブルは、永続テーブルと同じように見えるが、一時テーブルに挿入したデータはあるタイミングで消える。消えるタイミングは通常、トランザクションの最後か、データセッションを閉じるとき。

ビュー

データディクショナリに格納されたクエリのこと。

データディクショナリとは

今後更新

外観や振る舞いはテーブルに似ているが、データは関連づけられない。これが仮想テーブルと名付けられる所以。
ビューに対してクエリをsじっこ数rと、そのクエリがビューの定義とマージされて、最終的に実行されるクエリが作成される。
ビュ0を作成するときに追加のデータが作成されたり格納されたりすることはない。サーバがこのselevt文んを後から利用できるようにしまいこむだけ。
ビューを作成する理由は様々で、ユーザから列を見えなくしたいこともあれば、複雑なデータベースの設計を単純にしたいこと等がある。

テーブルエイリアスを定義する

エイリアスとは

別名、通称

1つのクエリで複数のテーブルを結合する際に、列を参照するときには、参照先のテーブルを区別する手段が必要となる。from句の外側でデータブルを参照するには以下の2つの選択肢がある。

完全なテーブル名を使う
各テーブルにエイリアスを割り当て、クエリ全体でエイリアスを使う
エイリアスを使うと、混乱を生むことなく文がよりコンパクトになる

where句

小さいテーブル等で、テーブルからすべての行を取得できれば良い。しかし、ほとんどの場合は、テーブルから行を１行残らず取得するのではなく、必要のない行は取り除く。そこで使うのがwhere句。
→where句とフィルタリングについては次章

レーティングがGでレンタル期間が7日の場合のみ

mysql> SELECT title
    -> FROM film
    -> WHERE rating = 'G' AND rental_duration >=7;
 +---------------------+
 | title               |
 +---------------------+
 | BLANKET BECERLY     |
 | BORROWERS BEDAZZLED |
 | WAR NOTTING         |
 +---------------------+
3 row in set (0.01 sec)

レーティングがGまたは、レンタル期間が7日の場合

mysql> SELECT title
    -> FROM film
    -> WHERE rating = 'G' OR rental_duration >=7;
 +---------------------+
 | title               |
 +---------------------+
 | BLANKET BECERLY     |
 | BORROWERS BEDAZZLED |
 | CONTROL ANTHEM      |
 | HOOK CHARIOTS       |
 | GUN BONNIE          |
 | WAR NOTTING         |
 +---------------------+
6 row in set (0.01 sec)

のように、'AND'か'OR'で絞り身が変わる。

group by句とhaving句

ここまで見てきたクエリはすべて、データ操作をせずにそのまま取得するものであった。しかし、データにどのような傾向があるか調べたいので、データベースサーバにデータを少しいじらせてから結果セットを取得したいことも出てくる。そのようなメカニズムの1つがorder byであり、列の値に基づいてデータをグループ化するために使われる。
group by句を使って、行をグループ化する
having句を使って、グループ化したデータをフィルタリングできる

mysql> SELECT c.first_name, c.last_name, count(*)
    -> FROM customer c
    ->   INNER JOIN rental r
    ->   ON c.customer_id = r.customer_id
    -> GROUP BY c.first_name, c.last_name
    -> HAVING count(*) >= 40;
    +------------+-----------+----------+
    | first_name | last_name | count(*) |
    +------------+-----------+----------+
    | TAMMY      | SANDERS   |       41 |
    | CLARA      | SHAW      |       42 |
    | SUE        | PETERS    |       40 |
    | KARL       | SEAL      |       45 |
    +------------+-----------+-----------+
    4 rows in set (0.01 sec)

order by句

一般に、クエリから返される結果セットの行は決まった順序で並んでいるわけではない。結果セットの行を決まった順番で並べたい場合は、order by句を使って結果セットを並び替える必要がある。

last_nameをアルファベット順にしたい場合以下のように記載する

mysql> SELECT c.first_name, c.last_name,
    ->    time(r.rental_date) rental_time
    -> FROM customer c
    ->   INNER JOIN rental r
    ->   ON c.customer_id = r.cusstomer_id
    -> WHERE date(r.rental_date) = '2023-12-27'
    -> ORDER BY c.last_name;
+------------+-----------+-------------+
| first_name | last_name | rental_date |
+------------+-----------+-------------+
| DANIEL     | CABRAL    | 12:00:00    |
| CHAPMAN    | DEVORE    | 12:00:00    |
| SONTA      | GREGORY   | 12:00:00    |
| TERRANCE   | ROUSH     | 12:00:00    |
+------------+-----------+-------------+

4 rows in set (0.01 sec)

昇順と降順

並び替えの際には、ascとdescの2つのキーワードを使って、昇順または降順の並び替えを指定できる。

 mysql> SELECT c.first_name, c.last_name,
     ->    time(r.rental_date) rental_time
     -> FROM customer c
     ->   INNER JOIN rental r
     ->   ON c.customer_id = r.cusstomer_id
     -> WHERE date(r.rental_date) = '2023-12-27'
     -> ORDER BY time(r.rental_date) asc;
 +------------+-----------+-------------+
 | first_name | last_name | rental_date |
 +------------+-----------+-------------+
 | DANIEL     | CABRAL    | 12:00:00    |
 | CHAPMAN    | DEVORE    | 13:00:00    |
 | SONTA      | GREGORY   | 14:00:00    |
 | TERRANCE   | ROUSH     | 15:00:00    |
 +------------+-----------+-------------+
 
4 rows in set (0.01 sec)

数値のプレースホルダーによる並び替え

select句で列を作った並び替えを行う場合は、それらの列を名前ではなく位置で参照することができる。この方法は、前項の例のように指揮を使って並び替えを行う場合に特に便利。

 mysql> SELECT c.first_name, c.last_name,
     ->    time(r.rental_date) rental_time
     -> FROM customer c
     ->   INNER JOIN rental r
     ->   ON c.customer_id = r.cusstomer_id
     -> WHERE date(r.rental_date) = '2023-12-27'
     -> ORDER BY 3 asc;
 +------------+-----------+-------------+
 | first_name | last_name | rental_date |
 +------------+-----------+-------------+
 | DANIEL     | CABRAL    | 15:00:00    |
 | CHAPMAN    | DEVORE    | 14:00:00    |
 | SONTA      | GREGORY   | 13:00:00    |
 | TERRANCE   | ROUSH     | 12:00:00    |
 +------------+-----------+-------------+
 
4 rows in set (0.01 sec)