[5] ストアドプロシージャってやつでなんとかして！！！！

yoidea commented 5 years ago

あなたはA社に今年入社した新入社員です。

A社は人手不足でAさんの他に上司のBさんしか情報システム部門にいません。ある日突然Bさんが人事のCさんに、「簡単に指定のデータを取り出せるようにしてほしい」と言われました。ですが、Bさんは次の日からバカンスに行く予定があったため、あなたに任せて出国してしまいました。 Bさんの代わりにCさんを助けてあげてください。問1

性別名が"男"の従業員のみを取得してください。
カラムは(employee_id, name, birthday, sex_name)を表示してください。

問2

性別名が"女"で年齢が女性従業員の平均年齢以上の従業員のみを取得してください。
カラムは(employee_id, name, age, sex_name)を表示してください。
age(年齢)に関してはbirthdayから求めて答えてください。

問3

部署も権限もすでに決まっている(NULL以外である)従業員のデータのみを取得してください。
カラムは(employee_id,name,age,department_name,permit_name)
age(年齢)に関してはbirthdayから求めて答えてください。

条件

Employees以外のテーブル内にあるカラム"〇〇_id"は今後内容が変更されることがあるので注意すること。ゴール

問1, 2, 3で指定したデータを取得するクエリを、q01, q02, q03という名前でストアドプロシージャとして登録する。情報

IPアドレス: 192.168.0.1
ユーザー名: admin
パスワード: 6pfmqje365Ed
DBユーザー名: root

arsley commented 5 years ago

踏み台からSSH後、 sudo mysql -u root でログインができることを確認。

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.4.6-MariaDB-1:10.4.6+maria~bionic-log mariadb.org binary distribution

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

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

MariaDB [(none)]>

arsley commented 5 years ago

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Employees          |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.003 sec)

arsley commented 5 years ago

MariaDB [Employees]> show tables;
+---------------------+
| Tables_in_Employees |
+---------------------+
| Departments         |
| Employees           |
| Permit              |
| Sex                 |
+---------------------+
4 rows in set (0.001 sec)

arsley commented 5 years ago

MariaDB [Employees]> select * from Employees limit 1;
+-------------+---------------+------------+--------+---------------+-----------+
| employee_id | name          | birthday   | sex_id | department_id | permit_id |
+-------------+---------------+------------+--------+---------------+-----------+
|        1001 | 東京 太郎     | 1984-10-02 |      0 |             2 |         2 |
+-------------+---------------+------------+--------+---------------+-----------+
1 row in set (0.001 sec)

arsley commented 5 years ago

MariaDB [Employees]> select * from Sex;
+--------+----------+
| sex_id | sex_name |
+--------+----------+
|      0 | 男       |
|      1 | 女       |
+--------+----------+
2 rows in set (0.001 sec)

arsley commented 5 years ago

1のクエリ

create procedure q01()
select employee_id, name, birthday, sex_name
from Employees
  inner join Sex on Employees.sex_id = Sex.sex_id
where Employees.sex_id = 0;

arsley commented 5 years ago

年齢を求めてみる方法参考 : https://dev.mysql.com/doc/refman/5.6/ja/date-calculations.html

select name, birthday, CURDATE(), TIMESTAMPDIFF(YEAR, birthday, CURDATE()) as age from Employees;

arsley commented 5 years ago

年齢の平均を求めるクエリ

SELECT AVG(average.age) FROM (SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM Employees) AS average;

+------------------+
| AVG(average.age) |
+------------------+
|          26.8500 |
+------------------+
1 row in set (0.001 sec)

arsley commented 5 years ago

女だけ

MariaDB [Employees]> SELECT * FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id where Employees.sex_id = 1;
+-------------+---------------------+------------+--------+---------------+-----------+--------+----------+
| employee_id | name                | birthday   | sex_id | department_id | permit_id | sex_id | sex_name |
+-------------+---------------------+------------+--------+---------------+-----------+--------+----------+
|        1009 | 大和 和子           | 1996-10-01 |      1 |             1 |         3 |      1 | 女       |
|        1010 | 涼風 青葉           | 2001-02-02 |      1 |             2 |         2 |      1 | 女       |
|        1011 | 望月 紅葉           | 2000-01-17 |      1 |          NULL |         1 |      1 | 女       |
|        1012 | 阿波根 うみこ       | 1989-07-20 |      1 |             3 |         3 |      1 | 女       |
|        1013 | 鳴海 ツバメ         | 1999-05-16 |      1 |          NULL |         1 |      1 | 女       |
|        1014 | 飯島 ゆん           | 1997-12-06 |      1 |             2 |         2 |      1 | 女       |
|        1015 | 滝本 ひふみ         | 1995-01-23 |      1 |             2 |         2 |      1 | 女       |
|        1016 | 桜 ねね             | 2000-05-05 |      1 |          NULL |         1 |      1 | 女       |
|        1017 | 遠山 りん           | 1993-12-03 |      1 |             2 |         2 |      1 | 女       |
|        1018 | 葉月 しずく         | 1983-05-22 |      1 |             1 |         3 |      1 | 女       |
|        1019 | 八神 コウ           | 1993-08-02 |      1 |             2 |         3 |      1 | 女       |
|        1020 | 篠田 はじめ         | 1998-01-01 |      1 |             2 |         2 |      1 | 女       |
+-------------+---------------------+------------+--------+---------------+-----------+--------+----------+

女の平均年齢

MariaDB [Employees]> SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id where Employees.sex_id = 1;
+-----------------------------------------------+
| AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) |
+-----------------------------------------------+
|                                       23.4167 |
+-----------------------------------------------+

arsley commented 5 years ago

2 のクエリ

CREATE PROCEDURE q02()
SELECT *
FROM (
  SELECT employee_id, name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, sex_name
  FROM Employees
    INNER JOIN Sex ON Employees.sex_id = Sex.sex_id
  WHERE Employees.sex_id = 1
) AS E
WHERE E.age >= (
  SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS age
  FROM Employees
    INNER JOIN Sex ON Employees.sex_id = Sex.sex_id
    WHERE Employees.sex_id = 1
);

arsley commented 5 years ago

3のクエリ

CREATE PROCEDURE  q03()
SELECT employee_id, name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, department_name, permit_name\
FROM Employees
  INNER JOIN Departments ON Employees.department_id = Departments.department_id
  INNER JOIN Permit ON Employees.permit_id = Permit.permit_id
;

arsley commented 5 years ago

解答

お疲れ様です、yrfw (ゆるふわ) です。

問「ストアドプロシージャってやつでなんとかして！！！！」の解答を送らせていただきます。

問1

create procedure q01()
select employee_id, name, birthday, sex_name
from Employees
  inner join Sex on Employees.sex_id = Sex.sex_id
where Employees.sex_id = 0;

問2

CREATE PROCEDURE q02()
SELECT *
FROM (
  SELECT employee_id, name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, sex_name
  FROM Employees
    INNER JOIN Sex ON Employees.sex_id = Sex.sex_id
  WHERE Employees.sex_id = 1
) AS E
WHERE E.age >= (
  SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS age
  FROM Employees
    INNER JOIN Sex ON Employees.sex_id = Sex.sex_id
    WHERE Employees.sex_id = 1
);

問3

CREATE PROCEDURE  q03()
SELECT employee_id, name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, department_name, permit_name\
FROM Employees
  INNER JOIN Departments ON Employees.department_id = Departments.department_id
  INNER JOIN Permit ON Employees.permit_id = Permit.permit_id
;

arsley commented 5 years ago

参考

https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46 https://qiita.com/merrill/items/967884c02e10bd8f32f5 https://dev.mysql.com/doc/refman/5.6/ja/date-calculations.html https://www.dbonline.jp/mysql/function/index5.html https://teratail.com/questions/158120 https://www.dbonline.jp/mysql/select/index20.html

arsley commented 5 years ago

__人人人人__
 > 40/200 <
  Y^Y^Y^Y

kstm-su / ictsc_2019_yrfw_pre1

[5] ストアドプロシージャってやつでなんとかして！！！！ #3

解答

参考