ウェブサイト検索

MySQL と MariaDB のいくつかの関数の使用方法を学ぶ - パート 2


これは、MariaDB/MySQL コマンドの要点に関する 2 つの記事シリーズの第 2 部です。続行する前に、このトピックに関する以前の記事を参照してください。

  1. 初心者向けに MySQL/MariaDB の基礎を学ぶ – パート 1

MySQL/MariaDB 初心者シリーズの第 2 部では、SELECT クエリによって返される行数を制限する方法と、特定の条件に基づいて結果セットを並べ替える方法について説明します。

さらに、レコードをグループ化し、数値フィールドに対して基本的な数学的操作を実行する方法を学びます。これらすべては、有用なレポートを作成するために使用できる SQL スクリプトを作成するのに役立ちます。

前提条件

まず、次の手順に従ってください。

1. employees サンプル データベースをダウンロードします。これには、合計 4 レコードで構成される 6 つのテーブルが含まれています。

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjf employees_db-full-1.0.6.tar.bz2
cd employees_db

2. MariaDB プロンプトを入力し、employees という名前のデータベースを作成します。

mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)

3. 次のように、MariaDB サーバーにインポートします。

MariaDB [(none)]> source employees.sql

サンプル データベースがロードされるまで 1 ~ 2 分待ちます (ここでは 400 万 レコードについて話していることに注意してください)。

4. テーブルを一覧表示して、データベースが正しくインポートされたことを確認します。

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. 従業員 データベースで使用する特別なアカウントを作成します (別のアカウント名とパスワードを自由に選択できます)。

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

次に、empadmin ユーザーとして Mariadb プロンプトにログインします。

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

続行する前に、上の図に示されているすべての手順が完了していることを確認してください。

結果セット内の行の順序付けと数の制限

給与テーブルには、各従業員のすべての収入と開始日と終了日が含まれています。 emp_no=10001 の給与を経時的に表示したい場合があります。これは、次の質問の答えに役立ちます。

  1. 彼/彼女は昇給しましたか?
  2. もしそうなら、いつですか?

次のクエリを実行して調べます。

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

最近の 5 件の昇給を表示する必要がある場合はどうすればよいでしょうか? ORDER BY from_date DESC を実行できます。 DESC キーワードは、結果セットを降順で並べ替えることを示します。

さらに、LIMIT 5 を使用すると、結果セットの上位 5 行のみを返すことができます。

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

複数のフィールドで ORDER BY を使用することもできます。たとえば、次のクエリは、従業員の生年月日に基づいて昇順形式 (デフォルト) に基づいて結果セットを並べ替え、次に姓をアルファベット降順形式で並べ替えます。

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)

LIMIT の詳細については、こちらをご覧ください。

レコードのグループ化/MAX、MIN、AVG、ROUND

前述したように、salaries テーブルには各従業員の長期にわたる収入が含まれています。 LIMIT の他に、MAX キーワードと MIN キーワードを使用して、従業員の最大数と最小数がいつ雇用されたかを決定できます。

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

上記の結果セットに基づいて、以下のクエリが何を返すか推測できますか?

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

一定期間の平均給与 (AVG で指定) を小数点第 2 位に四捨五入して返します (ROUND で指定) ことに同意するなら、その通りです。

従業員ごとにグループ化された給与の合計を表示し、上位 5 を返したい場合は、次のクエリを使用できます。

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

上記のクエリでは、給与が従業員ごとにグループ化されてから合計が実行されます。

すべてをひとつにまとめる

幸いなことに、レポートを作成するためにクエリを次々に実行する必要はありません。代わりに、一連の SQL コマンドを含むスクリプトを作成して、必要な結果セットをすべて返すことができます。

スクリプトを実行すると、私たちがそれ以上介入しなくても、必要な情報が返されます。たとえば、現在の作業ディレクトリに次の内容を持つ maxminavg.sql という名前のファイルを作成してみましょう。

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

2 つのダッシュで始まる行は無視され、個々のクエリが順番に実行されます。このスクリプトは Linux コマンド ラインから実行できます。

mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

または MariaDB プロンプトから:

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

まとめ

この記事では、SELECT ステートメントによって返される結果セットを調整するために、いくつかの MariaDB 関数を使用する方法を説明しました。定義した後は、複数の個別のクエリをスクリプトに挿入して、クエリをより簡単に実行し、人的エラーのリスクを軽減できます。

この記事に関してご質問やご提案はありますか?以下のコメントフォームを使用して、お気軽にお知らせください。お返事おまちしております!