MySQL の単一テーブルクエリ操作例の詳細な説明 [構文、制約、グループ化、集計、フィルタリング、並べ替えなど]

この記事では、MySQL の単一テーブルクエリ操作について説明します。ご参考までに、詳細は以下の通りです。

文法

1. 単一テーブルクエリ構文

テーブル名からフィールド1、フィールド2...を選択します
WHERE条件
GROUP BYフィールド
フィルターを持つ
ORDER BYフィールド
LIMIT エントリの数を制限する

2. キーワード実行の優先順位（ポイント）

最も重要なポイント：キーワード実行の優先順位

から
どこ
グループ化
持つ
選択
明確な
並び替え
制限

1. テーブルfrom探す:

2. whereで指定された制約に従って、ファイル/テーブルからレコードを1つずつ取得します。

3. 取得したレコードを group by でグループ化します。group by がない場合、レコード全体が 1 つにグループ化されます。

4.グループ化された結果をフィルタリングするには、

5. 選択を実行する

6. 重複排除

7. 結果を条件で並べ替える: order by

8. 表示される結果の数を制限する

（１）制約条件

where演算子

where句は、
1. 比較演算子: >、<、>=、<=、<>、!=
2.80から100の間: 値は80から100の間です
3.in(80,90,100)の値は10または20または30です
4. 'xiaomagepattern' のように、パターンは % または _ になります。 % は任意の数の文字を表し、_ は 1 文字を表します。
5. 論理演算子: 論理演算子andまたはnotを複数の条件で直接使用できます。

（２）クエリによるグループ化

#1. まず、グループ化は where の後に行われること、つまり、グループ化は where の後に取得されたレコードに基づいて行われることは明らかです。
#2. グループ化とは、従業員情報テーブルでの役職によるグループ化や、性別によるグループ化など、共通のフィールドに従ってすべてのレコードを分類することを意味します。
#3. なぜグループ化する必要があるのでしょうか?
各部門で最も高い給与を受け取る
各部門の従業員数を取得する
男性の数と女性の数をとって
ヒント: 「each」の後のフィールドがグループ化の基準となります
#4. 大前提:
任意のフィールドでグループ化できますが、グループ化後、たとえば投稿でグループ化すると、投稿フィールドのみを表示できます。グループ内の情報を表示するには、集計関数を使用する必要があります。

次のSQL文を実行するとエラーは報告されませんが、それ自体は意味がありません。

mysql> 従業員グループから投稿別に * を選択します。
+----+---------+--------+-----------+------------+------------------------------------------+--------------+------------+------------+
| ID | 名前 | 性別 | 年齢 | 入社日 | 職位 | 投稿コメント | 給与 | 勤務先 | 退職ID |
+----+---------+--------+-----------+------------+------------------------------------------+--------------+------------+------------+
| 14 | 張 イエ | 男性 | 28 | 2016-03-11 | 操作 | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪| 女性 | 48 | 2015-03-11 | セール | NULL | 3000.13 | 402 | 2 |
| 2 | alex | 男性 | 78 | 2015-03-02 | 教師 | | 1000000.31 | 401 | 1 |
| 1 | egon | 男性 | 18 | 2017-03-01 | 沙河の旧少年院の大使 | NULL | 7300.33 | 401 | 1 |
+----+---------+--------+-----------+------------+------------------------------------------+--------------+------------+------------+
セット内の 4 行 (0.00 秒)

sql_mode を ONLY_FULL_GROUP_BY に設定し、終了してから再度入力すると、設定が有効になります。

mysql> グローバル sql_mode を 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY' に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

再入力

mysql> @@sql_mode を選択します。
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------

mysql> select * from emp group by post; // この場合、ERROR 1054 (42S22): Unknown column 'post' in 'group statement' が報告されます。
mysql> 従業員グループから投稿別に * を選択します。
エラー 1055 (42000): 't1.employee.id' は GROUP BY にありません
mysql> post ごとに従業員グループから投稿を選択します。
+-----------------------------------------+
| 投稿 |
+-----------------------------------------+
| 操作 |
| セール |
| 先生 |
| 沙河のOld Boys Officeの外交大使 |
+-----------------------------------------+
セット内の 4 行 (0.00 秒)

または以下のように使用する

mysql> post,name によって employee グループから name,post を選択します。
+------------+------------------------------------------+
| 名前 | 役職 |
+------------+------------------------------------------+
| 張野| 作戦 |
| 程耀進 | オペレーション |
| チェン・ヤオティエ | オペレーション |
| チェン・ヤオトン | オペレーション |
| チェン・ヤオイン | オペレーション |
| ディンディン | セール |
| ヤヤ | セール |
| スター | セール |
| ゲゲ | セール |
| ワイワイ | セール |
| アレックス | 先生 |
| jinliyang | 先生 |
| ジンシン | 先生 |
| liwenzhou | 教師 |
| wupeiqi | 先生 |
| xiaomage | 先生 |
| yuanhao | 先生 |
| egon | 沙河のOld Boys Officeの外交大使 |
+------------+------------------------------------------+
セット内の行数は 18 です (0.00 秒)

mysql> post ごとに従業員グループから post、count(id) を選択します。
+-----------------------------------------+-----------+
| 投稿 | カウント(ID) |
+-----------------------------------------+-----------+
| 操作 | 5 |
| セール | 5 |
| 教師 | 7 |
| 沙河事務所のOld Boys外交大使 | 1 |
+-----------------------------------------+-----------+
セット内の 4 行 (0.00 秒)

（３）集計関数

max()は最大値を求める
min()は最小値を見つける
avg() は平均値を求める
和（）
count() で合計数を求める

#強調: 集計関数はグループの内容を集計します。グループがない場合は、デフォルトで 1 つのグループになります。# 各部門には何人の従業員がいますか? select post, count(id) from employee group by post;
# 各部門の最高給与 select post,max(salary) from employee group by post;
# 各部門の最低給与 select post,min(salary) from employee group by post;
# 各部門の平均給与 select post,avg(salary) from employee group by post;
# 各部門のすべての給与 select post,sum(age) from employee group by post;

（4）フィルタリング

HAVINGとWHEREの違いは

#! ！！実行優先度は高い順: where > group by > having
#1. Where は group by の前にあるため、任意のフィールドを Where に含めることができますが、集計関数は使用できません。
#2. グループ化の後にHavingが実行される場合、グループ化されたフィールドはHavingで使用でき、他のフィールドは直接取得できません。集計関数を使用できます。

mysql> 給与が1000000の場合、従業員から*を選択します。
+----+------+------+-----+------------+----------+--------------+-------------+-----------+------------+
| ID | 名前 | 性別 | 年齢 | 入社日 | 職位 | 投稿コメント | 給与 | 勤務先 | 退職ID |
+----+------+------+-----+------------+----------+--------------+-------------+-----------+------------+
| 2 | alex | 男性 | 78 | 2015-03-02 | 教師 | | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+----------+--------------+-------------+-----------+------------+
セット内の 1 行 (0.00 秒)

mysql> 給与が1000000を超える従業員から*を選択します。
エラー 1463 (42000): グループ化されていないフィールド 'salary' が HAVING 句で使用されています

# group_concat() 関数を使用してすべての名前の値を連結するには、group by を使用する必要があります。mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##エラー、グループ化後に給与フィールドを直接取得することはできません。ERROR 1054 (42S22): 'field list' に不明な列 'post' があります

練習する

1. 各職位の従業員数が2人未満の職位名と、各職位の従業員名と人数を照会する
2. 平均給与が10,000を超えるすべての職種の職名と平均給与を照会する
3. 平均給与が10,000以上20,000未満のすべての職種の職名と平均給与を照会する

答え

mysql> post、group_concat(name)、count(id) を employee group by post から選択します。
+-----------------------------------------+--------------------------------------------------------+-----------+
| 投稿 | group_concat(名前) | count(ID) |
+-----------------------------------------+--------------------------------------------------------+-----------+
| 操作 | Cheng Yaotie、Cheng Yaotong、Cheng Yaoyin、Cheng Yaojin、Zhang Ye | 5 |
| セール | Gege、Xingxing、Dingding、Yaya、Waiwai | 5 |
| 先生 | xiaomage、jinxin、jingliyang、liwenzhou、yuanhao、wupeiqi、alex | 7 |
| 沙河事務所の旧友外交大使 | egon | 1 |
+-----------------------------------------+--------------------------------------------------------+-----------+
セット内の 4 行 (0.00 秒)

mysql> 従業員グループから、count(id)<2 を持つ post で post、group_concat(name)、count(id) を選択します。
+-----------------------------------------+--------------------+-----------+
| 投稿 | group_concat(名前) | count(ID) |
+-----------------------------------------+--------------------+-----------+
| 沙河事務所の旧友外交大使 | egon | 1 |
+-----------------------------------------+--------------------+-----------+
セット内の 1 行 (0.00 秒)

#質問2:
mysql> 従業員グループから post、avg(salary) を選択します。post は avg(salary) > 10000 を持ちます。
+-----------+---------------+
| 役職 | 平均(給与) |
+-----------+---------------+
| 操作 | 16800.026000 |
| 教師 | 151842.901429 |
+-----------+---------------+
セット内の 2 行 (0.00 秒)

#質問3:
mysql> avg(salary) > 10000 かつ avg(salary) <20000 である post を持つ従業員グループから post、avg(salary) を選択します。
+-----------+--------------+
| 役職 | 平均(給与) |
+-----------+--------------+
| 操作 | 16800.026000 |
+-----------+--------------+
セット内の 1 行 (0.00 秒)

(5) クエリソートによる順序

単一列で並べ替え

  SELECT * FROM employee ORDER BY age;
  SELECT * FROM employee ORDER BY age ASC;
  SELECT * FROM employee ORDER BY age DESC;

複数の列で並べ替える: 最初に年齢の昇順で並べ替え、年齢が同じ場合は ID の降順で並べ替えます。

  従業員から*を選択
    年齢順（ASC）
    id DESC;

（５）制限は、照会されるレコードの数を制限します。

例：

  SELECT * FROM 従業員 ORDER BY 給与 DESC
   LIMIT 3; #デフォルトの初期位置は0です

  SELECT * FROM 従業員 ORDER BY 給与 DESC
    LIMIT 0,5; #0番から開始します。つまり、最初に最初の項目を照会し、次にこの項目を含む次の5つの項目を照会します。SELECT * FROM employee ORDER BY salary DESC
    LIMIT 5,5; #5番目の項目から開始します。つまり、最初に6番目の項目を見つけ、次にこれを含む次の5つの項目を見つけます。

練習: 一度に5つのアイテムを表示する

# ページ 1 datamysql> select * from employee limit 0,5;
+----+-----------+-----+-----+-------------+------------------------------------------+--------------+------------+------------+------------+
| ID | 名前 | 性別 | 年齢 | 入社日 | 職位 | 投稿コメント | 給与 | 勤務先 | 退職ID |
+----+-----------+-----+-----+-------------+------------------------------------------+--------------+------------+------------+------------+
| 1 | egon | 男性 | 18 | 2017-03-01 | 沙河の旧少年院の大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | 男性 | 78 | 2015-03-02 | 教師 | | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | 男性 | 81 | 2013-03-05 | 教師 | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | 男性 | 73 | 2014-07-01 | 教師 | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | 男性 | 28 | 2012-11-01 | 教師 | NULL | 2100.00 | 401 | 1 |
+----+-----------+-----+-----+-------------+------------------------------------------+--------------+------------+------------+------------+
セット内の行数は 5 です (0.00 秒)
# ページ 2 datamysql> select * from employee limit 5,5;
+----+------------+--------+-----------+-----------+-----------+--------------+-----------+-----------+------------+
| ID | 名前 | 性別 | 年齢 | 入社日 | 職位 | 投稿コメント | 給与 | 勤務先 | 退職ID |
+----+------------+--------+-----------+-----------+----------+--------------+-----------+-----------+------------+
| 6 | jingliyang | 女性 | 18 | 2011-02-11 | 教師 | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | 男性 | 18 | 1900-03-01 | 教師 | NULL | 30000.00 | 401 | 1 |
| 8 | xiaomage | 男性 | 48 | 2010-11-11 | 教師 | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪| 女性 | 48 | 2015-03-11 | セール | NULL | 3000.13 | 402 | 2 |
| 10 | ヤヤ | 女性 | 38 | 2010-11-01 | セール | NULL | 2000.35 | 402 | 2 |
+----+------------+--------+-----------+-----------+----------+--------------+-----------+-----------+------------+
セット内の行数は 5 です (0.00 秒)
# ページ 3 datamysql> select * from employee limit 10,5;
+----+-----------+---------+-----------+------------+------------+--------------+---------+-----------+------------+
| ID | 名前 | 性別 | 年齢 | 入社日 | 職位 | 投稿コメント | 給与 | 勤務先 | 退職ID |
+----+-----------+---------+-----------+------------+------------+--------------+---------+-----------+------------+
| 11 | Ding Ding | 女性 | 18 | 2011-03-12 | セール | NULL | 1000.37 | 402 | 2 |
| 12 | スター | 女性 | 18 | 2016-05-13 | セール | NULL | 3000.29 | 402 | 2 |
| 13 | 格| 女性 | 28 | 2017-01-27 | セール | NULL | 4000.33 | 402 | 2 |
| 14 | 張 イエ | 男性 | 28 | 2016-03-11 | 操作 | NULL | 10000.13 | 403 | 3 |
| 15 | チェン・ヤオジン | 男性 | 18 | 1997-03-12 | 操作 | NULL | 20000.00 | 403 | 3 |
+----+-----------+---------+-----------+------------+------------+--------------+---------+-----------+------------+
セット内の行数は 5 です (0.00 秒)

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: