MySQL – グループごとの最大値のレコード

2021-02-23 / tau / コメントする

概要

インラインビューとグルーピングを使って、レコード中のある列の値が最大値／最小値となるレコードをグループごとに抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは各科目ごとの最高点をとった学生と教科を抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

手順

グループごとの最大値

科目によってグルーピングし、各科目の最高得点を得る。

SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

1	SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;
+---------+------------+
| subject | MAX(score) |
+---------+------------+
| 数学    |         85 |
| 物理    |         90 |
| 英語    |         85 |
| 文学    |         95 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

+---------+------------+

| subject | MAX(score) |

+---------+------------+

| 数学 | 85 |

| 物理 | 90 |

| 英語 | 85 |

| 文学 | 95 |

+---------+------------+

4 rows in set (0.00 sec)

インラインビューによる最高得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最高スコアの列が追加される。

SELECT name, subject, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
;

SELECT name, subject, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

;

mysql> SELECT name, subject, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> ;
+------+---------+---------+---------+
| name | subject | max_sub | max_scr |
+------+---------+---------+---------+
| 江藤 | 文学    | 数学    |      85 |
| 江藤 | 物理    | 数学    |      85 |
| 江藤 | 数学    | 数学    |      85 |
| 宇藤 | 文学    | 数学    |      85 |
| 宇藤 | 英語    | 数学    |      85 |
| 宇藤 | 物理    | 数学    |      85 |
| 伊藤 | 文学    | 数学    |      85 |
| 伊藤 | 数学    | 数学    |      85 |
| 安藤 | 英語    | 数学    |      85 |
| 安藤 | 物理    | 数学    |      85 |
| 安藤 | 数学    | 数学    |      85 |
| 江藤 | 文学    | 物理    |      90 |
| 江藤 | 物理    | 物理    |      90 |
| 江藤 | 数学    | 物理    |      90 |
| 宇藤 | 文学    | 物理    |      90 |
.....

mysql> SELECT name, subject, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> ;

+------+---------+---------+---------+

+------+---------+---------+---------+

| 江藤 | 文学 | 数学 | 85 |

| 江藤 | 物理 | 数学 | 85 |

| 江藤 | 数学 | 数学 | 85 |

| 宇藤 | 文学 | 数学 | 85 |

| 宇藤 | 英語 | 数学 | 85 |

| 宇藤 | 物理 | 数学 | 85 |

| 伊藤 | 文学 | 数学 | 85 |

| 伊藤 | 数学 | 数学 | 85 |

| 安藤 | 英語 | 数学 | 85 |

| 安藤 | 物理 | 数学 | 85 |

| 安藤 | 数学 | 数学 | 85 |

| 江藤 | 文学 | 物理 | 90 |

| 江藤 | 物理 | 物理 | 90 |

| 江藤 | 数学 | 物理 | 90 |

| 宇藤 | 文学 | 物理 | 90 |

.....

最高得点のレコードの追加

各学生・科目のレコードのうち、スコアが最高スコアに等しいレコードを抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

mysql> SELECT name, subject, score, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> WHERE subject = max_sub AND score = max_scr
    -> ;
+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

mysql> SELECT name, subject, score, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> WHERE subject = max_sub AND score = max_scr

-> ;

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

MySQL – シェアの計算

2021-02-23 / tau / コメントする

概要

インラインビューを使って、各レコードのある列の値のシェアを計算できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM components;
+-----------+----------+
| component | quantity |
+-----------+----------+
| 成分A     |       60 |
| 成分B     |       35 |
| 成分C     |       15 |
+-----------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM components;

+-----------+----------+

| component | quantity |

+-----------+----------+

| 成分A | 60 |

| 成分B | 35 |

| 成分C | 15 |

+-----------+----------+

3 rows in set (0.00 sec)

ここでは各成分の量quantityのシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

手順

合計値の計算

合計値はSUM()関数で計算。

SELECT SUM(quantity) FROM components;

1	SELECT SUM(quantity) FROM components;

mysql> SELECT SUM(quantity) FROM components;
+---------------+
| SUM(quantity) |
+---------------+
|           110 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(quantity) FROM components;

+---------------+

| SUM(quantity) |

+---------------+

| 110 |

+---------------+

1 row in set (0.00 sec)

インラインビューによる合計列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな合計値の列が追加される。

SELECT component, quantity, total
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT component, quantity, total

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT component, quantity, total
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+
| component | quantity | total |
+-----------+----------+-------+
| 成分A     |       60 |   110 |
| 成分B     |       35 |   110 |
| 成分C     |       15 |   110 |
+-----------+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT component, quantity, total

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+

| component | quantity | total |

+-----------+----------+-------+

| 成分A | 60 | 110 |

| 成分B | 35 | 110 |

| 成分C | 15 | 110 |

+-----------+----------+-------+

3 rows in set (0.00 sec)

各レコードのシェアの計算

各レコードのquantityを合計値で割ってシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

MySQL – 最小値・最大値のレコードの抽出

2021-02-23 / tau / コメントする

概要

インラインビューを使って、レコード中のある列が最大値／最小値となるレコードを抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは、全学生・全教科のうち、最低得点とその得点を取った学生と科目を表示させる。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
WHERE score = min_score
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

WHERE score = min_score

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> WHERE score = min_score
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 伊藤 | 数学    |    65 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> WHERE score = min_score

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 伊藤 | 数学 | 65 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

2 rows in set (0.00 sec)

手順

最低得点の取得

まず、全体の最低得点は以下で得られる。

SELECT MIN(score) AS min_score FROM student_scores;

1	SELECT MIN(score) AS min_score FROM student_scores;

mysql> SELECT MIN(score) AS min_score FROM student_scores;
+-----------+
| min_score |
+-----------+
|        65 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MIN(score) AS min_score FROM student_scores;

+-----------+

| min_score |

+-----------+

| 65 |

+-----------+

1 row in set (0.00 sec)

インラインビューによる最低得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最低スコアの列が追加される。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 安藤 | 数学    |    80 |        65 |
| 安藤 | 物理    |    90 |        65 |
| 安藤 | 英語    |    75 |        65 |
| 伊藤 | 数学    |    65 |        65 |
| 伊藤 | 文学    |    85 |        65 |
| 宇藤 | 物理    |    70 |        65 |
| 宇藤 | 英語    |    85 |        65 |
| 宇藤 | 文学    |    95 |        65 |
| 江藤 | 数学    |    85 |        65 |
| 江藤 | 物理    |    80 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
11 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 安藤 | 数学 | 80 | 65 |

| 安藤 | 物理 | 90 | 65 |

| 安藤 | 英語 | 75 | 65 |

| 伊藤 | 数学 | 65 | 65 |

| 伊藤 | 文学 | 85 | 65 |

| 宇藤 | 物理 | 70 | 65 |

| 宇藤 | 英語 | 85 | 65 |

| 宇藤 | 文学 | 95 | 65 |

| 江藤 | 数学 | 85 | 65 |

| 江藤 | 物理 | 80 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

11 rows in set (0.00 sec)

最低得点レコードの抽出

各学生のレコードのうち、スコアが最低スコアに等しいレコードを抽出する。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
WHERE score = min_score
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

WHERE score = min_score

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> WHERE score = min_score
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 伊藤 | 数学    |    65 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> WHERE score = min_score

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 伊藤 | 数学 | 65 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

2 rows in set (0.00 sec)

MySQL – インラインビュー

2021-02-23 / tau / コメントする

確認用データ

以下のデータを使う。

mysql> SELECT * FROM subject_scores;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM subject_scores;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

6 rows in set (0.00 sec)

基本形

FROM句の中にSELECTクエリーを書いてエイリアスをつけると、それがテーブルとして参照される。

SELECT *
FROM
  (SELECT subject, score FROM subject_scores) as view
;

SELECT *

FROM

(SELECT subject, score FROM subject_scores) as view

;

mysql> SELECT *
    -> FROM
    ->   (SELECT subject, score FROM subject_scores) as view
    -> ;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT *

-> FROM

-> (SELECT subject, score FROM subject_scores) as view

-> ;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

6 rows in set (0.00 sec)

インラインビューにはエイリアスを付けないとエラーになる。

mysql> SELECT *
    -> FROM
    ->   (SELECT subject, score FROM subject_scores)
    -> ;
ERROR 1248 (42000): Every derived table must have its own alias

mysql> SELECT *

-> FROM

-> (SELECT subject, score FROM subject_scores)

-> ;

ERROR 1248 (42000): Every derived table must have its own alias

テーブルとインラインビューの併記

エイリアスによる修飾

テーブルとインラインビューを並べて使える。不明確さをなくすために、テーブル名またはエイリアスで列名を修飾する。

SELECT org.subject, org.score, view.subject, view.score
FROM
  subject_scores as org,
  (SELECT subject, score FROM subject_scores) as view
;

SELECT org.subject, org.score, view.subject, view.score

FROM

subject_scores as org,

(SELECT subject, score FROM subject_scores) as view

;

mysql> SELECT org.subject, org.score, view.subject, view.score
    -> FROM
    ->   subject_scores as org,
    ->   (SELECT subject, score FROM subject_scores) as view
    -> ;
+-------------+-------+-------------+-------+
| subject     | score | subject     | score |
+-------------+-------+-------------+-------+
| philosophy  |    80 | mathematics |    85 |
| literature  |  NULL | mathematics |    85 |
| geology     |    70 | mathematics |    85 |
| chemistry   |    80 | mathematics |    85 |
| physics     |    95 | mathematics |    85 |
| mathematics |    85 | mathematics |    85 |
| philosophy  |    80 | physics     |    95 |
| literature  |  NULL | physics     |    95 |
| geology     |    70 | physics     |    95 |
| chemistry   |    80 | physics     |    95 |
| physics     |    95 | physics     |    95 |
| mathematics |    85 | physics     |    95 |
| philosophy  |    80 | chemistry   |    80 |
| literature  |  NULL | chemistry   |    80 |
| geology     |    70 | chemistry   |    80 |
| chemistry   |    80 | chemistry   |    80 |
| physics     |    95 | chemistry   |    80 |
| mathematics |    85 | chemistry   |    80 |
| philosophy  |    80 | geology     |    70 |
| literature  |  NULL | geology     |    70 |
| geology     |    70 | geology     |    70 |
| chemistry   |    80 | geology     |    70 |
| physics     |    95 | geology     |    70 |
| mathematics |    85 | geology     |    70 |
| philosophy  |    80 | literature  |  NULL |
| literature  |  NULL | literature  |  NULL |
| geology     |    70 | literature  |  NULL |
| chemistry   |    80 | literature  |  NULL |
| physics     |    95 | literature  |  NULL |
| mathematics |    85 | literature  |  NULL |
| philosophy  |    80 | philosophy  |    80 |
| literature  |  NULL | philosophy  |    80 |
| geology     |    70 | philosophy  |    80 |
| chemistry   |    80 | philosophy  |    80 |
| physics     |    95 | philosophy  |    80 |
| mathematics |    85 | philosophy  |    80 |
+-------------+-------+-------------+-------+
36 rows in set (0.00 sec)

mysql> SELECT org.subject, org.score, view.subject, view.score

-> FROM

-> subject_scores as org,

-> (SELECT subject, score FROM subject_scores) as view

-> ;

+-------------+-------+-------------+-------+

+-------------+-------+-------------+-------+

| philosophy | 80 | mathematics | 85 |

| geology | 70 | mathematics | 85 |

| chemistry | 80 | mathematics | 85 |

| physics | 95 | mathematics | 85 |

| mathematics | 85 | mathematics | 85 |

| philosophy | 80 | physics | 95 |

| geology | 70 | physics | 95 |

| chemistry | 80 | physics | 95 |

| physics | 95 | physics | 95 |

| mathematics | 85 | physics | 95 |

| philosophy | 80 | chemistry | 80 |

| geology | 70 | chemistry | 80 |

| chemistry | 80 | chemistry | 80 |

| physics | 95 | chemistry | 80 |

| mathematics | 85 | chemistry | 80 |

| philosophy | 80 | geology | 70 |

| geology | 70 | geology | 70 |

| chemistry | 80 | geology | 70 |

| physics | 95 | geology | 70 |

| mathematics | 85 | geology | 70 |

| philosophy | 80 | philosophy | 80 |

| geology | 70 | philosophy | 80 |

| chemistry | 80 | philosophy | 80 |

| physics | 95 | philosophy | 80 |

| mathematics | 85 | philosophy | 80 |

+-------------+-------+-------------+-------+

36 rows in set (0.00 sec)

列名のエイリアス

インラインビューの列名にエイリアスを定義してもよい。

SELECT subject, score, view_subject, view_score
FROM
  subject_scores,
  (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view
;

SELECT subject, score, view_subject, view_score

FROM

subject_scores,

(SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view

;

mysql> SELECT subject, score, view_subject, view_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view
    -> ;
+-------------+-------+--------------+------------+
| subject     | score | view_subject | view_score |
+-------------+-------+--------------+------------+
| philosophy  |    80 | mathematics  |         85 |
| literature  |  NULL | mathematics  |         85 |
| geology     |    70 | mathematics  |         85 |
| chemistry   |    80 | mathematics  |         85 |
| physics     |    95 | mathematics  |         85 |
| mathematics |    85 | mathematics  |         85 |
| philosophy  |    80 | physics      |         95 |
| literature  |  NULL | physics      |         95 |
.....

mysql> SELECT subject, score, view_subject, view_score

-> FROM

-> subject_scores,

-> (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view

-> ;

+-------------+-------+--------------+------------+

+-------------+-------+--------------+------------+

| philosophy | 80 | mathematics | 85 |

| geology | 70 | mathematics | 85 |

| chemistry | 80 | mathematics | 85 |

| physics | 95 | mathematics | 85 |

| mathematics | 85 | mathematics | 85 |

| philosophy | 80 | physics | 95 |

.....

集約関数の利用

インラインビューで集約関数を使うと威力を発揮。以下の例では、全レコード中の最高得点と最低得点を各レコードに付加している。

SELECT subject, score, max_score, min_score
FROM
  subject_scores,
  (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
  (SELECT MIN(score) as min_score FROM subject_scores) as min_table
;

SELECT subject, score, max_score, min_score

FROM

subject_scores,

(SELECT MAX(score) as max_score FROM subject_scores) as max_table,

(SELECT MIN(score) as min_score FROM subject_scores) as min_table

;

mysql> SELECT subject, score, max_score, min_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
    ->   (SELECT MIN(score) as min_score FROM subject_scores) as min_table
    -> ;
+-------------+-------+-----------+-----------+
| subject     | score | max_score | min_score |
+-------------+-------+-----------+-----------+
| mathematics |    85 |        95 |        70 |
| physics     |    95 |        95 |        70 |
| chemistry   |    80 |        95 |        70 |
| geology     |    70 |        95 |        70 |
| literature  |  NULL |        95 |        70 |
| philosophy  |    80 |        95 |        70 |
+-------------+-------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT subject, score, max_score, min_score

-> FROM

-> subject_scores,

-> (SELECT MAX(score) as max_score FROM subject_scores) as max_table,

-> (SELECT MIN(score) as min_score FROM subject_scores) as min_table

-> ;

+-------------+-------+-----------+-----------+

+-------------+-------+-----------+-----------+

| mathematics | 85 | 95 | 70 |

| physics | 95 | 95 | 70 |

| chemistry | 80 | 95 | 70 |

| geology | 70 | 95 | 70 |

| literature | NULL | 95 | 70 |

| philosophy | 80 | 95 | 70 |

+-------------+-------+-----------+-----------+

6 rows in set (0.00 sec)

このようにすると、最高得点や最低得点のレコードを抽出することができる。

SELECT subject, score, max_score, min_score
FROM
  subject_scores,
  (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
  (SELECT MIN(score) as min_score FROM subject_scores) as min_table
WHERE
  score = max_score
;

SELECT subject, score, max_score, min_score

FROM

subject_scores,

(SELECT MAX(score) as max_score FROM subject_scores) as max_table,

(SELECT MIN(score) as min_score FROM subject_scores) as min_table

WHERE

score = max_score

;

mysql> SELECT subject, score, max_score, min_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
    ->   (SELECT MIN(score) as min_score FROM subject_scores) as min_table
    -> WHERE
    ->   score = max_score
    -> ;
+---------+-------+-----------+-----------+
| subject | score | max_score | min_score |
+---------+-------+-----------+-----------+
| physics |    95 |        95 |        70 |
+---------+-------+-----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT subject, score, max_score, min_score

-> FROM

-> subject_scores,

-> (SELECT MAX(score) as max_score FROM subject_scores) as max_table,

-> (SELECT MIN(score) as min_score FROM subject_scores) as min_table

-> WHERE

-> score = max_score

-> ;

+---------+-------+-----------+-----------+

+---------+-------+-----------+-----------+

| physics | 95 | 95 | 70 |

+---------+-------+-----------+-----------+

1 row in set (0.00 sec)

MySQL – グループ化と集約～GROUP BY

2021-02-23 / tau / コメントする

概要

グループ化はGROUP BY文でグループ化する列を指定する。ほとんどの場合、以下のように集約関数を適用する。

SELECT FUNC(col) FROM table GROUP BY col;

上記の分では、table中の列colによってグループ化し、そのグループごとに集約関数FUNC()を適用した結果を返す。

実行例

以下のデータを使う。学生ごとの履修科目と得点のデータのイメージ。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

COUNTの例

以下の例では、学生でグルーピングして科目数をカウントしている。これにより、各学生の履修科目数が得られる。

mysql> SELECT name, COUNT(subject) FROM student_scores
    -> GROUP BY name;
+------+----------------+
| name | COUNT(subject) |
+------+----------------+
| 安藤 |              3 |
| 伊藤 |              2 |
| 宇藤 |              3 |
| 江藤 |              3 |
+------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT name, COUNT(subject) FROM student_scores

-> GROUP BY name;

+------+----------------+

| name | COUNT(subject) |

+------+----------------+

| 安藤 | 3 |

| 伊藤 | 2 |

| 宇藤 | 3 |

| 江藤 | 3 |

+------+----------------+

4 rows in set (0.00 sec)

以下の例では、科目でグルーピングして科目数をカウントしている。これにより、各科目の被履修数が得られる。

mysql> SELECT subject, COUNT(subject) FROM student_scores
    -> GROUP BY subject;
+---------+----------------+
| subject | COUNT(subject) |
+---------+----------------+
| 数学    |              3 |
| 物理    |              3 |
| 英語    |              2 |
| 文学    |              3 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, COUNT(subject) FROM student_scores

-> GROUP BY subject;

+---------+----------------+

| subject | COUNT(subject) |

+---------+----------------+

| 数学 | 3 |

| 物理 | 3 |

| 英語 | 2 |

| 文学 | 3 |

+---------+----------------+

4 rows in set (0.00 sec)

MIN/MAXの例

以下の例では、科目でグルーピングして点数の最小値と最大値を表示させている。

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores
    -> GROUP BY subject;
+---------+------------+------------+
| subject | MIN(score) | MAX(score) |
+---------+------------+------------+
| 数学    |         65 |         85 |
| 物理    |         70 |         90 |
| 英語    |         75 |         85 |
| 文学    |         65 |         95 |
+---------+------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores

-> GROUP BY subject;

+---------+------------+------------+

| subject | MIN(score) | MAX(score) |

+---------+------------+------------+

| 数学 | 65 | 85 |

| 物理 | 70 | 90 |

| 英語 | 75 | 85 |

| 文学 | 65 | 95 |

+---------+------------+------------+

4 rows in set (0.00 sec)

SELECTでsubjectも表示させている。上記の例ではsubjectでグルーピングしているので適切に表示されるが、subjectの値に使われるレコードは不定なので不適切に使うと以下のようになる。

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores;
+---------+------------+------------+
| subject | MIN(score) | MAX(score) |
+---------+------------+------------+
| 数学    |         65 |         95 |
+---------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT subject, MIN(score), MAX(score) FROM student_scores;

+---------+------------+------------+

| subject | MIN(score) | MAX(score) |

+---------+------------+------------+

| 数学 | 65 | 95 |

+---------+------------+------------+

1 row in set (0.00 sec)

SUM/AVGの例

以下の例では、学生ごとの合計点数と教科ごとの平均点数をグルーピングにより計算している。

mysql> SELECT name, SUM(score) FROM student_scores
    -> GROUP BY name;
+------+------------+
| name | SUM(score) |
+------+------------+
| 安藤 |        245 |
| 伊藤 |        150 |
| 宇藤 |        250 |
| 江藤 |        230 |
+------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, AVG(score) FROM student_scores
    -> GROUP BY subject;
+---------+------------+
| subject | AVG(score) |
+---------+------------+
| 数学    |    76.6667 |
| 物理    |    80.0000 |
| 英語    |    80.0000 |
| 文学    |    81.6667 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT name, SUM(score) FROM student_scores

-> GROUP BY name;

+------+------------+

| name | SUM(score) |

+------+------------+

| 安藤 | 245 |

| 伊藤 | 150 |

| 宇藤 | 250 |

| 江藤 | 230 |

+------+------------+

4 rows in set (0.00 sec)

mysql> SELECT subject, AVG(score) FROM student_scores

-> GROUP BY subject;

+---------+------------+

| subject | AVG(score) |

+---------+------------+

| 数学 | 76.6667 |

| 物理 | 80.0000 |

| 英語 | 80.0000 |

| 文学 | 81.6667 |

+---------+------------+

4 rows in set (0.00 sec)

HAVING

各科目が履修された数は以下のようだった。

mysql> SELECT subject, COUNT(subject) FROM student_scores
    -> GROUP BY subject;
+---------+----------------+
| subject | COUNT(subject) |
+---------+----------------+
| 数学    |              3 |
| 物理    |              3 |
| 英語    |              2 |
| 文学    |              3 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, COUNT(subject) FROM student_scores

-> GROUP BY subject;

+---------+----------------+

| subject | COUNT(subject) |

+---------+----------------+

| 数学 | 3 |

| 物理 | 3 |

| 英語 | 2 |

| 文学 | 3 |

+---------+----------------+

4 rows in set (0.00 sec)

ここで、履修数が2よりも多い科目だけを抽出したいとする。

WHERE句でできそうだがエラーになる。

mysql> SELECT subject, count(subject) FROM student_scores
    -> GROUP BY subject
    -> WHERE count(subject) > 2;
ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count(subject) > 2' at line 3

mysql> SELECT subject, count(subject) FROM student_scores

-> GROUP BY subject

-> WHERE count(subject) > 2;

ERROR 1064 (42000): You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count(subject) > 2' at line 3

集約結果によって抽出したい場合は、HAVING句を使う。

mysql> SELECT subject, count(subject) FROM student_scores
    -> GROUP BY subject
    -> HAVING count(subject) > 2;
+---------+----------------+
| subject | count(subject) |
+---------+----------------+
| 数学    |              3 |
| 物理    |              3 |
| 文学    |              3 |
+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT subject, count(subject) FROM student_scores

-> GROUP BY subject

-> HAVING count(subject) > 2;

+---------+----------------+

| subject | count(subject) |

+---------+----------------+

| 数学 | 3 |

| 物理 | 3 |

| 文学 | 3 |

+---------+----------------+

3 rows in set (0.00 sec)

WHERE句との組み合わせの例。scoreが80以上の履修数を科目ごとにカウント。

mysql> SELECT subject, count(subject) FROM student_scores
    -> WHERE score >= 80
    -> GROUP BY subject;
+---------+----------------+
| subject | count(subject) |
+---------+----------------+
| 数学    |              2 |
| 物理    |              2 |
| 文学    |              2 |
| 英語    |              1 |
+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, count(subject) FROM student_scores

-> WHERE score >= 80

-> GROUP BY subject;

+---------+----------------+

| subject | count(subject) |

+---------+----------------+

| 数学 | 2 |

| 物理 | 2 |

| 文学 | 2 |

| 英語 | 1 |

+---------+----------------+

4 rows in set (0.00 sec)

さらに、scoreが80以上の数が1より大きい科目のみHAVING句で取り出す。

mysql> SELECT subject, count(subject) FROM student_scores
    -> WHERE score >= 80
    -> GROUP BY subject
    -> HAVING count(subject) > 1;
+---------+----------------+
| subject | count(subject) |
+---------+----------------+
| 数学    |              2 |
| 物理    |              2 |
| 文学    |              2 |
+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT subject, count(subject) FROM student_scores

-> WHERE score >= 80

-> GROUP BY subject

-> HAVING count(subject) > 1;

+---------+----------------+

| subject | count(subject) |

+---------+----------------+

| 数学 | 2 |

| 物理 | 2 |

| 文学 | 2 |

+---------+----------------+

3 rows in set (0.00 sec)

MySQL – 集約関数

2021-02-22 / tau / コメントする

概要

集約関数(aggregate functions)は、テーブルの全レコードまたはグループ化されたグループごとの集約値を返す。

COUNT	指定したカラムがNULL以外の行数
MIN	指定したカラムの最小値(NULLの行は含めない)
MAX	指定したカラムの最大値(NULLの行は含めない)
SUM	指定したカラムの合計値(NULLの行は含めない)
AVG	指定したカラムの平均値(NULLの行は含めない)

実行例

以下のテーブルを使う。

mysql> SELECT * FROM subject_scores;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+

mysql> SELECT * FROM subject_scores;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

COUNT～個数

CONT()は引数に指定した列のうちNULL以外の個数を数える。

以下の例ではCOUNT(subject)はレコード数と同じ6となるが、COUNT(score)はNULLが対象とならないため5となっている。

mysql> SELECT COUNT(subject) FROM subject_scores;
+----------------+
| COUNT(subject) |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(score) FROM subject_scores;
+--------------+
| COUNT(score) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(subject) FROM subject_scores;

+----------------+

| COUNT(subject) |

+----------------+

| 6 |

+----------------+

1 row in set (0.00 sec)

mysql> SELECT COUNT(score) FROM subject_scores;

+--------------+

| COUNT(score) |

+--------------+

| 5 |

+--------------+

1 row in set (0.00 sec)

MIN/MAX～最小値・最大値

MIN()は引数にした列のうち最小の値を返し、MAX()は最大の値を返す。NULLは対象に含まれない。

以下の例では列としてscoreを指定し、最小値と最大値が得られている。

mysql> SELECT MIN(score) FROM subject_scores;
+------------+
| MIN(score) |
+------------+
|         70 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(score) FROM subject_scores;
+------------+
| MAX(score) |
+------------+
|         95 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(score) FROM subject_scores;

+------------+

| MIN(score) |

+------------+

| 70 |

+------------+

1 row in set (0.00 sec)

mysql> SELECT MAX(score) FROM subject_scores;

+------------+

| MAX(score) |

+------------+

| 95 |

+------------+

1 row in set (0.00 sec)

なおMIN()/MAX()にsucjectを渡すと、文字列の照合順序に従って大小関係が判定される。

mysql> SELECT MIN(subject) FROM subject_scores;
+--------------+
| MIN(subject) |
+--------------+
| chemistry    |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(subject) FROM subject_scores;
+--------------+
| MAX(subject) |
+--------------+
| physics      |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(subject) FROM subject_scores;

+--------------+

| MIN(subject) |

+--------------+

| chemistry |

+--------------+

1 row in set (0.00 sec)

mysql> SELECT MAX(subject) FROM subject_scores;

+--------------+

| MAX(subject) |

+--------------+

| physics |

+--------------+

1 row in set (0.00 sec)

SUM～合計

SUM()は引数の合計を返す。NULLは無視される。

以下の例では6つのレコードのscoreのうちNULLを除いた5つの合計が得られている。

mysql> SELECT SUM(score) FROM subject_scores;
+------------+
| SUM(score) |
+------------+
|        410 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(score) FROM subject_scores;

+------------+

| SUM(score) |

+------------+

| 410 |

+------------+

1 row in set (0.00 sec)

なお文字列データの列を引数に与えるとSUM()は0を返す。

mysql> SELECT SUM(score) FROM subject_scores;
+------------+
| SUM(score) |
+------------+
|        410 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(score) FROM subject_scores;

+------------+

| SUM(score) |

+------------+

| 410 |

+------------+

1 row in set (0.00 sec)

AVG～平均値

AVG()は引数の列データの平均値を返す。NULLは無視されるため、割る数からものぞかれる。

以下の例では6つのscoreのうち1つがNULLのため、平均値は410/5で計算される。

mysql> SELECT AVG(score) FROM subject_scores;
+------------+
| AVG(score) |
+------------+
|    82.0000 |
+------------+

mysql> SELECT AVG(score) FROM subject_scores;

+------------+

| AVG(score) |

+------------+

| 82.0000 |

+------------+

文字列型の列を引数に指定すると、結果は0となる。

MySQL – 初期値の設定

2021-02-20 / tau / コメントする

列の初期値を設定するには、対象の列でDEFAULT VALUEを定義する。

mysql> CREATE TABLE testtable (
    ->   product VARCHAR(20),
    ->   price INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC testtable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| product | varchar(20) | YES  |     | NULL    |       |
| price   | int         | YES  |     | 0       |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO testtable (product)
    -> VALUES ('battery');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM testtable;
+---------+-------+
| product | price |
+---------+-------+
| battery |     0 |
+---------+-------+

mysql> CREATE TABLE testtable (

-> product VARCHAR(20),

-> price INT DEFAULT 0

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> DESC testtable;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| price | int | YES | | 0 | |

+---------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> INSERT INTO testtable (product)

-> VALUES ('battery');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM testtable;

+---------+-------+

| product | price |

+---------+-------+

| battery | 0 |

+---------+-------+

MySQL – 主キーの設定

2021-02-20 / tau / コメントする

列の設定で宣言する方法

書式

CREATE TABLEの列の定義の中で以下を設定する。

NOT NULL
PRIMARY KEY
AUTO INCREMENT（必要な場合）

実行例

mysql> CREATE TABLE testtable (
    ->   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   product VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC testtable;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| product | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

mysql> CREATE TABLE testtable (

-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> product VARCHAR(20)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> DESC testtable;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

+---------+-------------+------+-----+---------+----------------+

PRIMARY KEYを別に宣言する方法

書式

列の宣言の最後で以下を設定する。複合キーの場合はキーを並べる。

PRIMARY KEY(key1 [, key2, ...]);

実行例

mysql> CREATE TABLE testtable (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   product VARCHAR(20),
    ->   PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC testtable;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| product | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

mysql> CREATE TABLE testtable (

-> id INT NOT NULL AUTO_INCREMENT,

-> product VARCHAR(20),

-> PRIMARY KEY(id)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> DESC testtable;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

+---------+-------------+------+-----+---------+----------------+

MySQL – 複数レコードの一括登録

2021-02-20 / tau / コメントする

記法

複数レコードを登録するには、VALUESの値をカンマで区切って並べる。

INSERT INTO table (columns) VALUES (values), (values), ... (values);

実行例

mysql> DESC test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO test (name, age) VALUES
    -> ('Alex', 15),
    -> ('Ben', 28),
    -> ('Cod', 32),
    -> ('Dave', 18);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESC test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> INSERT INTO test (name, age) VALUES

-> ('Alex', 15),

-> ('Ben', 28),

-> ('Cod', 32),

-> ('Dave', 18);

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

MySQL – COLLATE～照合順序

2021-02-20 / tau / 2件のコメント

概要

COLLATE文は文字の照合順序を指定する
照合順序は文字の大小比較をする際の基準
検索時のWHEREや並べ替えORDER BYなどで適用される

書き方

WHERE

WHERE colname COLLATE collateion = 'expression';

WHERE colname COLLATE collateion LIKE 'expression';

ORDER BY

ORDER BY colname COLLATE collateion;

Collation

utf8mb4_unicode_ci
utf8mb4_general_ci
utf8mb4_bin
utf8mb4_0900-ai_ci（デフォルト）

デフォルトの照合順序

UTF-8のデフォルトはutf8_general_ciだが、utf8mb4の場合utf8mb4_0900-ai_ciと照合順序が異なる。

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

mysql> SHOW CHARACTER SET;

+----------+---------------------------------+---------------------+--------+

+----------+---------------------------------+---------------------+--------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| hp8 | HP West European | hp8_english_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

+----------+---------------------------------+---------------------+--------+

確認

以下のテーブルを使う。

mysql> SELECT text FROM testtable;
+--------------+
| text         |
+--------------+
| a            |
| b            |
| c            |
| A            |
| B            |
| C            |
| まま         |
| ママ         |
| ﾏﾏ           |
| はは         |
| ハハ         |
| ﾊﾊ           |
| ぱぱ         |
| パパ         |
| ﾊﾟﾊﾟ         |
| ばば         |
| ババ         |
| ﾊﾞﾊﾞ         |
+--------------+

mysql> SELECT text FROM testtable;

+--------------+

| text |

+--------------+

| a |

| b |

| c |

| A |

| B |

| C |

| まま |

| ママ |

| ﾏﾏ |

| はは |

| ハハ |

| ﾊﾊ |

| ぱぱ |

| パパ |

| ﾊﾟﾊﾟ |

| ばば |

| ババ |

| ﾊﾞﾊﾞ |

+--------------+

区別

utf8mb4_0900_ai_ci

大文字と小文字は区別される
全角と半角は区別される
平仮名と片仮名は区別される
濁点の有無は区別される

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_0900_ai_ci IN('a', 'はは');
+--------------+
| text         |
+--------------+
| a            |
| A            |
| はは         |
| ハハ         |
| ﾊﾊ           |
| ぱぱ         |
| パパ         |
| ﾊﾟﾊﾟ         |
| ばば         |
| ババ         |
| ﾊﾞﾊﾞ         |
+--------------+

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_0900_ai_ci IN('a', 'はは');

+--------------+

| text |

+--------------+

| a |

| A |

| はは |

| ハハ |

| ﾊﾊ |

| ぱぱ |

| パパ |

| ﾊﾟﾊﾟ |

| ばば |

| ババ |

| ﾊﾞﾊﾞ |

+--------------+

utf8mb4_general_ci

大文字と小文字は区別されない
全角と半角は区別される
平仮名と片仮名は区別される
濁点の有無は区別される

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_general_ci IN('a', 'はは');
+--------+
| text   |
+--------+
| a      |
| A      |
| はは   |
+--------+

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_general_ci IN('a', 'はは');

+--------+

| text |

+--------+

| a |

| A |

| はは |

+--------+

utf8mb4_unicode_ci

大文字と小文字は区別されない
全角と半角は区別されない
平仮名と片仮名は区別されない
濁点の有無は区別されない

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_unicode_ci IN('a', 'はは');
+--------------+
| text         |
+--------------+
| a            |
| A            |
| はは         |
| ハハ         |
| ﾊﾊ           |
| ぱぱ         |
| パパ         |
| ﾊﾟﾊﾟ         |
| ばば         |
| ババ         |
| ﾊﾞﾊﾞ         |
+--------------+

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_unicode_ci IN('a', 'はは');

+--------------+

| text |

+--------------+

| a |

| A |

| はは |

| ハハ |

| ﾊﾊ |

| ぱぱ |

| パパ |

| ﾊﾟﾊﾟ |

| ばば |

| ババ |

| ﾊﾞﾊﾞ |

+--------------+

utf8mb4_bin

大文字と小文字は区別される
全角と半角は区別される
平仮名と片仮名は区別される
濁点の有無は区別される

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_bin IN('a', 'はは');
+--------+
| text   |
+--------+
| a      |
| はは   |
+--------+

mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_bin IN('a', 'はは');

+--------+

| text |

+--------+

| a |

| はは |

+--------+

順序

utf8mb4_0900_ai_ci

utf8mb4_unicode_ciと同じ。

アルファベットは小文字・大文字順→アルファベット順
仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_0900_ai_ci;
+--------------+
| text         |
+--------------+
| a            |
| A            |
| b            |
| B            |
| c            |
| C            |
| はは         |
| ハハ         |
| ﾊﾊ           |
| ぱぱ         |
| パパ         |
| ﾊﾟﾊﾟ         |
| ばば         |
| ババ         |
| ﾊﾞﾊﾞ         |
| まま         |
| ママ         |
| ﾏﾏ           |
+--------------+

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_0900_ai_ci;

+--------------+

| text |

+--------------+

| a |

| A |

| b |

| B |

| c |

| C |

| はは |

| ハハ |

| ﾊﾊ |

| ぱぱ |

| パパ |

| ﾊﾟﾊﾟ |

| ばば |

| ババ |

| ﾊﾞﾊﾞ |

| まま |

| ママ |

| ﾏﾏ |

+--------------+

utf8mb4_general_ci

アルファベットは小文字・大文字順→アルファベット順
仮名はユニコード文字順

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_general_ci;
+--------------+
| text         |
+--------------+
| a            |
| A            |
| b            |
| B            |
| c            |
| C            |
| はは         |
| ばば         |
| ぱぱ         |
| まま         |
| ハハ         |
| ババ         |
| パパ         |
| ママ         |
| ﾊﾊ           |
| ﾊﾞﾊﾞ         |
| ﾊﾟﾊﾟ         |
| ﾏﾏ           |
+--------------+

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_general_ci;

+--------------+

| text |

+--------------+

| a |

| A |

| b |

| B |

| c |

| C |

| はは |

| ばば |

| ぱぱ |

| まま |

| ハハ |

| ババ |

| パパ |

| ママ |

| ﾊﾊ |

| ﾊﾞﾊﾞ |

| ﾊﾟﾊﾟ |

| ﾏﾏ |

+--------------+

utf8mb4_unicode_ci

Unicodeの文字順と異なる。

アルファベットは小文字・大文字順→アルファベット順
仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_unicode_ci;
+--------------+
| text         |
+--------------+
| a            |
| A            |
| b            |
| B            |
| c            |
| C            |
| はは         |
| ハハ         |
| ﾊﾊ           |
| ぱぱ         |
| パパ         |
| ﾊﾟﾊﾟ         |
| ばば         |
| ババ         |
| ﾊﾞﾊﾞ         |
| まま         |
| ママ         |
| ﾏﾏ           |
+--------------+

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_unicode_ci;

+--------------+

| text |

+--------------+

| a |

| A |

| b |

| B |

| c |

| C |

| はは |

| ハハ |

| ﾊﾊ |

| ぱぱ |

| パパ |

| ﾊﾟﾊﾟ |

| ばば |

| ババ |

| ﾊﾞﾊﾞ |

| まま |

| ママ |

| ﾏﾏ |

+--------------+

utf8mb4_bin

アルファベット・仮名ともUnicode文字順

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_bin;
+--------------+
| text         |
+--------------+
| A            |
| B            |
| C            |
| a            |
| b            |
| c            |
| はは         |
| ばば         |
| ぱぱ         |
| まま         |
| ハハ         |
| ババ         |
| パパ         |
| ママ         |
| ﾊﾊ           |
| ﾊﾞﾊﾞ         |
| ﾊﾟﾊﾟ         |
| ﾏﾏ           |
+--------------+

mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_bin;

+--------------+

| text |

+--------------+

| A |

| B |

| C |

| a |

| b |

| c |

| はは |

| ばば |

| ぱぱ |

| まま |

| ハハ |

| ババ |

| パパ |

| ママ |

| ﾊﾊ |

| ﾊﾞﾊﾞ |

| ﾊﾟﾊﾟ |

| ﾏﾏ |

+--------------+