Устойчивое развитие бизнеса сложно представить без надлежащего управления данными. Создание четко определенной системы необходимо для управления внутренними и внешними операциями. Детальное понимание бизнес-данных помогает понять общий рабочий процесс и оценить эффективность. Более того, использование баз данных экономит время и позволяет без труда получать доступ к информации.
Что такое база данных?
База данных — это организованная система, используемая для безопасного хранения информации. Она предлагает категоризацию и позволяет создавать таблицы для сортировки схожих элементов в одну группу. Таблицы предоставляют возможность перечислять информацию в нескольких строках и столбцах. В результате повышается качество и согласованность данных. Помимо возможностей хранения и легкого доступа, базы данных играют важную роль в обеспечении конфиденциальности и безопасности систем. Будь то разовый запрос или часть процесса, закодированного в приложении, извлечение данных является одним из наиболее критически важных аспектов управления базами данных.
В реляционных СУБД запрос — это любая команда, используемая для извлечения данных из таблицы. SELECT операторы почти всегда используются в запросах на языке структурированных запросов (SQL). Существует несколько методов извлечения информации из базы данных. Как правило, разработчики предпочитают вариант с командной строкой, поскольку он быстрый и эффективный. Он позволяет беспрепятственно отправлять запросы.
В этом руководстве мы познакомим вас с MySQL и обсудим, как работать с запросами в MySQL. Давайте начнем!
Предварительные требования
Чтобы следовать этому руководству, вам понадобится следующее:
-
Последняя версия Ubuntu установлена в вашей системе.
-
Пользователи системы должны иметь привилегии sudo.
-
Если у вас возникли трудности с доступом к учетной записи sudo, подумайте об изменении пароля root в MySQL.
-
-
MySQL установлен на вашем сервере.
Шаг 1 — Создание и настройка базы данных
Прежде чем начать и написать наш первый SQL-запрос, нам нужно создать базу данных и добавить в нее таблицы. После этого мы заполним таблицы тестовыми данными. Работа с базами данных поможет вам понять базовую настройку и придаст уверенности при работе с запросами.
Практический опыт — самый эффективный способ сформировать концепции и понять важность баз данных. В этом руководстве мы расскажем вам об использовании баз данных и их значении для решения реальных задач. Давайте рассмотрим сценарий, чтобы понять, как базы данных могут помочь организовать записи структурированным образом.
Сценарий: Мы создадим базу данных, в которой группа студентов колледжа отправляется в отпуск, чтобы отпраздновать свои дни рождения. Перед поездкой они планируют принять участие в дружеском соревновании по плаванию. Также каждый друг в группе планирует список дел, чтобы сделать их поездку захватывающей и приятной.
Сначала откройте MySQL от имени пользователя root:
|
1 |
$ sudo mysql |
Затем создайте базу данных, выполнив следующую команду:
|
1 2 3 |
mysql> CREATE DATABASE `vacation`; Query OK, 1 row affected (0.52 sec) |
Затем давайте выберем нашу базу данных с помощью следующей команды:
|
1 |
mysql> USE vacation; |
После этого мы создадим таблицы в нашей базе данных. Назовите первую таблицу celebration. В нашей таблице будут столбцы для имен наших друзей (name), выигранных ими турниров (wins) а также столбец для их best лучшего времени (time):
|
1 2 3 4 5 6 7 |
mysql> CREATE TABLE celebration ( -> name varchar(50), -> wins real, -> time real -> ); Query OK, 0 rows affected (2.03 sec) |
Затем мы заполним нашу таблицу данными:
|
1 2 3 4 5 6 7 8 9 |
mysql> INSERT INTO celebration (name, wins, time) VALUES('Austin', '4', '15.5'), ('Ivan','7','9'), ('Aisha','10','5'), ('Zane','13','7.5'); Query OK, 4 rows affected (0.29 sec) Records: 4 Duplicates: 0 Warnings: 0 |
Затем создайте еще одну таблицу в нашей базе данных для хранения информации об их любимых занятиях в день рождения. Мы создадим таблицу с именем vacation и со следующими столбцами:
|
List |
Detail |
|
name: |
Хранит имя каждого друга. |
|
birthdate: |
Отслеживает дату рождения каждого человека. |
|
activity: |
Хранит запись об их любимом занятии. |
|
Destination: |
Хранит информацию о любимом месте назначения каждого человека. |
|
meal: |
Отслеживает любимое блюдо человека. |
|
1 2 3 4 5 6 7 8 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 rows affected (0.09 sec) |
Заполните таблицу данными:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 |
Настройка базы данных завершена.
Шаг 2 — Начало работы с операторами SELECT
Запросы в SQL обычно начинаются с SELECT. Он используется в запросах для указания того, какие столбцы таблицы должны быть возвращены в результатах. Запрос также всегда должен включать FROM, который используется для указания таблицы, к которой будет обращен запрос.
Запросы строятся по следующему синтаксису:
|
1 |
mysql> SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply; |
Мы будем использовать синтаксис запроса для возврата столбца meal из таблицы vacation:
|
1 |
mysql> SELECT meal FROM vacation; |
Наш вывод будет следующим:
|
1 2 3 4 5 6 7 8 9 10 |
+-------+ | meal | +-------+ | Стейк | | Суши | | Картошка фри | | Тофу | +-------+ 4 rows in set (0.00 sec) |
Вы также можете выбрать несколько столбцов, используя запятую для их разделения:
|
1 |
mysql> SELECT name, destination FROM vacation; |
Вывод:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+-------------+ | name | destination | +--------+-------------+ | Остин | Мальдивы | | Иван | Маврикий | | Аиша | Колорадо | | Зейн | Бора Бора | +--------+-------------+ 4 rows in set (0.00 sec) |
Вы также можете использовать asterisk (*), если хотите выбрать все столбцы в таблице:
|
1 |
mysql> SELECT * FROM celebration; |
Вывод:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------+ | name | wins | time | +--------+------+------+ | Остин | 4 | 15.5 | | Иван | 7 | 9 | | Аиша | 10 | 5 | | Зейн | 13 | 7.5 | +--------+------+------+ 4 rows in set (0.00 sec) |
Если вы хотите отфильтровать записи, соответствующие определенному условию, используйте WHERE. Строки, не соответствующие указанному условию, исключаются из результатов. Предложение WHERE использует следующий синтаксис:
|
1 |
mysql> . . . WHERE column_name comparison_operator value |
Это оператор сравнения, который определяет, как указанный столбец должен сравниваться со значением. К распространенным операторам сравнения SQL относятся:
|
Operator |
Применение |
|
= |
Равенство |
|
!= |
Неравенство |
|
< |
Меньше |
|
> |
Больше |
|
<= |
Меньше или равно |
|
>= |
Больше или равно |
|
BETWEEN |
Проверяет, находится ли значение в заданном диапазоне. |
|
IN |
Проверяет, содержится ли значение строки в наборе указанных значений. |
|
EXISTS |
Проверяет, существует ли строка |
|
LIKE |
Проверяет, соответствует ли значение указанной строке |
|
IS NULL |
Проверяет на наличие значений NULL |
|
IS NOT NULL |
Проверяет на любые значения, отличные от NULL |
Если бы вы хотели найти любимое место назначения Аиши, вы могли бы использовать следующий запрос:
|
1 |
mysql> SELECT destination FROM vacation WHERE name ='Aisha'; |
Запрос вернет следующее:
|
1 2 3 4 5 6 7 |
+-------------+ | destination | +-------------+ | Colorado | +-------------+ 1 row in set (0.03 sec) |
SQL поддерживает использование подстановочных знаков, которые особенно полезны в WHERE предложениях. Знаки процента ( %) обозначают ноль или более неизвестных символов, в то время как символы подчеркивания ( _) обозначают один неизвестный символ. Они полезны, если вы хотите найти определенную запись в таблице, но не уверены в ней.
Например, если вы забыли любимое направление друга и знали только букву, с которой оно начинается, например, “m”. Вы можете найти название направления с помощью следующего запроса:
|
1 |
mysql> SELECT destination FROM vacation WHERE destination LIKE 'm%'; |
Запрос вернет:
|
1 2 3 4 5 6 7 8 |
+-------------+ | destination | +-------------+ | Maldives | | Mauritius | +-------------+ 2 rows in set (0.04 sec) |
При работе с базами данных вы можете столкнуться со столбцами или таблицами с относительно длинными или трудночитаемыми именами. В таких случаях вы можете сделать имена более читаемыми, используя ключевое слово AS для создания псевдонима. Псевдонимы, созданные с помощью AS, действительны только во время выполнения запроса, для которого они были созданы:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT name AS E, destination as D, activity as A FROM vacation; +--------+-----------+-------------------+ | E | D | A | +--------+-----------+-------------------+ | Austin | Maldives | skiing | | Ivan | Mauritius | paragliding | | Aisha | Colorado | mountain climbing | | Zane | Bora Bora | fishing | +--------+-----------+-------------------+ 4 rows in set (0.00 sec) |
Шаг 3 — Введение в агрегатные функции
При работе с данными вам не всегда нужно видеть сами данные. Скорее, вам нужна информация о данных. Выполняя запрос SELECT, вы можете интерпретировать или выполнять вычисления над вашими данными, используя синтаксис SQL. Они называются агрегатными функциями.
Функция COUNT подсчитывает и возвращает количество строк, соответствующих определенному набору критериев. Например, если вы хотите узнать, сколько друзей предпочитают поехать на Maldives, вы можете использовать следующий запрос:
|
1 |
mysql> SELECT COUNT(destination) FROM vacation WHERE destination = 'Maldives'; |
Будут возвращены следующие результаты:
|
1 2 3 4 5 6 7 |
+--------------------+ | COUNT(destination) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.06 sec) |
MIN используется для поиска наименьшего значения в указанном столбце:
|
1 |
mysql> SELECT MIN(wins) FROM celebration; |
Запрос выведет:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(wins) | +-----------+ | 4 | +-----------+ 1 row in set (0.02 sec) |
MAX используется для поиска наибольшего числового значения в данном столбце:
|
1 |
mysql> SELECT MAX(wins) FROM celebration; |
Ожидаемый вывод:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(wins) | +-----------+ | 13 | +-----------+ 1 row in set (0.03 sec) |
Как MIN, так и MAX функции могут использоваться как для числовых, так и для алфавитных данных. При применении к столбцу строковых значений функция MIN возвращает первое значение по алфавиту.
Функция MIN возвращает первое значение по алфавиту:
|
1 |
mysql> SELECT MIN(name) FROM celebration; |
Вот каким будет вывод:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(name) | +-----------+ | Aisha | +-----------+ 1 row in set (0.00 sec) |
Функция MAX возвращает последнее значение по алфавиту:
|
1 |
mysql> SELECT MAX(name) FROM celebration; |
Результат будет следующим:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(name) | +-----------+ | Zane | +-----------+ 1 строка в наборе (0.00 сек) |
Шаг 4 — Управление результатами запросов
Другим популярным предложением является GROUP BY предложение. Оно используется при выполнении агрегатной функции для одного столбца, но в связи с соответствующими значениями в другом:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity; |
Результат будет следующим:
|
1 2 3 4 5 6 7 8 9 10 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | лыжный спорт | | 1 | парапланеризм | | 1 | горное восхождение | | 1 | рыбалка | +-------------+-------------------+ 4 строки в наборе (0.04 сек) |
Для сортировки результатов запроса используйте предложение ORDER BY предложение. Числовые значения по умолчанию сортируются по возрастанию, а текстовые — по алфавиту. Запрос ниже выводит столбцы name и birthdate, но сортирует результаты по birthdate:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate; |
Результат будет следующим:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Zane | 1996-01-01 | | Aisha | 1999-07-24 | | Austin | 2002-01-07 | | Ivan | 2010-12-18 | +--------+------------+ 4 строки в наборе (0.04 сек) |
Результат выводится по возрастанию; чтобы отсортировать его по убыванию, завершите запрос словом DESC:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate DESC; |
Посмотрите на результат:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Ivan | 2010-12-18 | | Austin | 2002-01-07 | | Aisha | 1999-07-24 | | Zane | 1996-01-01 | +--------+------------+ 4 строки в наборе (0.00 сек) |
Предложение HAVING было добавлено в SQL для обеспечения функциональности, аналогичной предложению WHERE , но при этом совместимой с агрегатными функциями. Разница между этими двумя предложениями заключается в том, что WHERE служит для обращения к отдельным записям, а HAVING относится к групповым записям. Для этого предложение GROUP BY должно присутствовать всегда, когда используется предложение HAVING:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity HAVING COUNT(name) >= 1; |
Запрос выведет следующее:
|
1 2 3 4 5 6 7 8 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | лыжный спорт | | 1 | парапланеризм | | 1 | горное восхождение | | 1 | рыбалка | +-------------+-------------------+ |
Значение COUNT равно 1 для всех, так как нет двух друзей, которым нравилось бы одно и то же занятие.
Step 5 — Query Multiple Tables
Предложение JOIN можно использовать в результате запроса для объединения строк из двух или более таблиц. Это достигается путем поиска связанного столбца между таблицами и соответствующей сортировки вывода.
SELECT операторы, содержащие предложение JOIN , используют следующий синтаксис:
|
1 2 3 |
mysql> SELECT table1.column1, table2.column2 mysql> FROM table1 mysql> JOIN table2 ON table1.related_column=table2.related_column; |
Если бы вы хотели купить каждому из своих друзей кубок за их победы во время плавания в их дни рождения, вы могли бы создать запрос, который объединит обе таблицы, чтобы помочь вам найти всю необходимую информацию с помощью одного запроса:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration JOIN vacation ON celebration.name=vacation.name; |
Результат будет следующим:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 строк в наборе (0.00 сек) |
Это внутреннее JOIN объединение. Это связано с тем, что оно выбирает все записи, имеющие совпадающие значения в обеих таблицах, и выводит их в результирующий набор. Записи, не соответствующие запросу, не включаются. Мы можем добавить в наши таблицы новую строку, которая не соответствует ни одной записи:
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 строка затронута (0.01 сек) |
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 строка затронута (0.01 сек) |
Затем снова запустите инструкцию SELECT с предложением JOIN:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate -> FROM celebration -> JOIN vacation ON celebration.name=vacation.name; +--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 строк в наборе (0.00 сек) |
Поскольку в таблице celebration нет записи для Peter, а в таблице vacation нет записи для Ella, эти записи отсутствуют.
Мы можем вернуть все записи из одной из таблиц, используя внешнее JOIN объединение. Это может быть либо LEFT JOIN либо RIGHT JOIN. LEFT JOIN возвращает все записи из левой таблицы и только совпадающие записи из правой таблицы. Левая таблица в контексте внешних объединений — это та, на которую ссылается предложение FROM, а правая таблица — это любая таблица, указанная после оператора JOIN.
Запустите запрос еще раз, но используйте предложение LEFT JOIN:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration LEFT JOIN vacation ON celebration.name=vacation.name; |
Команда вернет все записи из левой таблицы ( celebration) даже если в правой таблице нет соответствующей записи. Если в правой таблице нет совпадающей записи, она возвращается как NULL:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | Ella | 1 | NULL | +--------+------+------------+ 5 строк в наборе (0.00 сек) |
А теперь предложение RIGHT JOIN:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration RIGHT JOIN vacation ON celebration.name=vacation.name; |
Будут возвращены все значения из правой таблицы (vacation). Поскольку birthdate записана в правой таблице, а в левой таблице отсутствуют name и wins, эти столбцы вернут значения NULL в этих строках:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | NULL | NULL | 1991-05-03 | +--------+------+------------+ 5 строк в наборе (0.01 сек) |
Вы можете использовать UNION предложение вместо JOIN для выборки записей из нескольких таблиц. Оператор UNION отличается от предложения JOIN тем, что он объединяет результаты двух инструкций SELECT в один столбец, а не выводит результаты из нескольких таблиц в виде отдельных столбцов с помощью одного запроса SELECT.
Вы можете выполнить этот запрос для иллюстрации:
|
1 |
mysql> SELECT name FROM celebration UNION SELECT name FROM vacation; |
Запрос удаляет дубликаты. Это поведение по умолчанию для оператора UNION:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+--------+ | name | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Peter | +--------+ 6 строк в наборе (0.00 сек) |
Чтобы вернуть все записи (включая дубликаты), используйте оператор UNION ALL:
|
1 |
mysql> SELECT name FROM celebration UNION ALL SELECT name FROM vacation; |
Вывод:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+--------+ | name | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Austin | | Ivan | | Aisha | | Zane | | Peter | +--------+ 10 строк в наборе (0.00 сек) |
Подзапросы — это еще один метод создания запросов к нескольким таблицам. Подзапросы — это запросы, которые вложены в другой запрос (также известные как внутренние или вложенные запросы). Они полезны, когда вам нужно сравнить результаты запроса с результатами отдельной агрегатной функции.
Мы рассмотрим пример, в котором попытаемся выяснить, кто из друзей выиграл больше турниров по плаванию, чем Элла. Вместо того чтобы запрашивать, сколько матчей выиграла Элла, а затем выполнять другой запрос, чтобы узнать, кто выиграл больше игр, вы можете вычислить и то, и другое с помощью одного запроса:
|
1 2 3 4 |
mysql> SELECT name, wins FROM celebration -> WHERE wins > ( -> SELECT wins FROM celebration WHERE name = 'Ella' -> ); |
Запрос вернет:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+ | name | wins | +--------+------+ | Austin | 4 | | Ivan | 7 | | Aisha | 10 | | Zane | 13 | +--------+------+ 4 строки в наборе (0.06 сек) |
Если бы вы хотели устроить своим друзьям неожиданный отпуск, вы могли бы использовать запрос, чтобы узнать, у кого больше всего побед, и вернуть пункт назначения для этого человека:
|
1 2 3 4 |
mysql> SELECT name, destination, activity, meal -> FROM vacation -> WHERE name = ( SELECT name FROM celebration -> WHERE wins = (SELECT MAX(wins) FROM celebration)); |
Запрос вернет:
|
1 2 3 4 5 6 7 |
+------+-------------+----------+------+ | name | destination | activity | meal | +------+-------------+----------+------+ | Zane | Bora Bora | fishing | Tofu | +------+-------------+----------+------+ 1 строка в наборе (0.00 сек) |
Эта инструкция содержит подзапрос внутри подзапроса.
Заключение
Создание запросов — одна из самых распространенных задач в управлении базами данных. Существует несколько инструментов администрирования баз данных, таких как phpMyAdmin и pgAdmin, которые вы можете использовать для работы с запросами и визуализации их результатов. Тем не менее, инструкция SELECT в командной строке является наиболее предпочтительным выбором из-за простоты использования и отличного контроля.
Кроме того, вы можете изучить множество руководств по базам данных в нашем блоге:
- SQLite vs MySQL vs. PostgreSQL: сравнение реляционных систем управления базами данных
- Быстрые решения для восстановления поврежденных таблиц в MySQL: руководство
- Как установить MySQL на Centos 7
- Пользователь MySQL — Создание и предоставление прав доступа
- Настройка репликации в MySQL на Ubuntu
Приятной работы!
Комментарии
Комментариев пока нет. Будьте первым.