Поиск по сайту:

Узнайте, как использовать несколько функций MySQL и MariaDB. Часть 2.

Это вторая часть серии из двух статей об основах команд MariaDB/MySQL. Прежде чем продолжить, прочтите нашу предыдущую статью по этой теме.

  1. Изучите основы MySQL/MariaDB для начинающих – часть 1

Во второй части серии статей для начинающих MySQL/MariaDB мы объясним, как ограничить количество строк, возвращаемых запросом SELECT, и как упорядочить набор результатов на основе заданного условия.

Кроме того, мы научимся группировать записи и выполнять основные математические манипуляции с числовыми полями. Все это поможет нам создать сценарий SQL, который мы сможем использовать для создания полезных отчетов.

Предварительные условия

Для начала выполните следующие действия:

1. Загрузите образец базы данных employees, которая включает шесть таблиц, содержащих в общей сложности 4 миллиона записей.

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 минуты, пока образец базы данных загрузится (имейте в виду, что здесь речь идет о записях 4M!).

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.

Как мы упоминали ранее, таблица зарплаты содержит доходы каждого сотрудника с течением времени. Помимо 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) зарплату за определенный период времени, округленную до двух десятичных знаков (как указано в 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;

Строки, начинающиеся с двух тире, игнорируются, а отдельные запросы выполняются один за другим. Мы можем выполнить этот скрипт либо из командной строки 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

Краткое содержание

В этой статье мы объяснили, как использовать несколько функций MariaDB для уточнения наборов результатов, возвращаемых операторами SELECT. После их определения в сценарий можно вставить несколько отдельных запросов, чтобы упростить его выполнение и снизить риск человеческой ошибки.

Есть ли у вас какие-либо вопросы или предложения по поводу этой статьи? Не стесняйтесь оставить нам сообщение, используя форму комментариев ниже. Мы с нетерпением ждем вашего ответа!