Как сделать запрос в mysql workbench

Как сделать запрос в mysql workbench

Для упрощения работы с сервером MySQL в базовый комплект установки входит такой инструмент как MySQL Workbench . Он представляет графический клиент для работы с сервером, через который мы в удобном виде можем создавать, удалять, изменять базы данных и управлять ими.

С некоторыми версиями MySQL графический клиент MySQL Workbench может устанавливаться автоматически. Но также его можно отдельно установить. Загрузить дистрибутив для всех распространенных операционных систем можно с адреса https://dev.mysql.com/downloads/shell/

Так, на Windows после установки в меню Пуск мы можем найти значок программы и запустить ее:

Нам откроется следующее окно, где мы можем увидеть поле с названием запущенного локально экземпляра MySQL:

Нажмем на него, и нам отобразится окно для ввода пароля:

Здесь надо ввести пароль, который был установлен для пользователя root при установке MySQL.

После успешного логина нам откроется содержимое сервера:

В частности, в левой части в окне SCHEMAS можно увидеть доступные базы данных.

Теперь посмотрим, как мы можем выполнять в этой программе запросы к бд. Вначале создадим саму БД. Для этого нажмем над списком баз данных на значок «SQL» с плюсом:

После этого в центральной части программы откроется окно для ввода скрипта SQL. Введем в него следующую команду:

CREATE DATABASE usersdb;

Данная команда создает базу данных usersdb.

Для выполнения скрипта в панели инструментов нажмем на значок молнии:

После этого внизу программы в поле вывода в случае удачного выполнения мы увидим зеленый маркер и отчет о выполнении.

Таким образом, бд создана. Теперь добавим в нее таблицу и какие-нибудь данные. Для этого изменим код в поле ввода скрипта на следующий:

USE usersdb; CREATE TABLE users ( id INTEGER AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30), age INTEGER ); INSERT INTO users (firstname, age) VALUES ('Tom', 34);

Все команды отделяются друг от друга точкой с запятой. Первая комнда — USE устанавливает в качестве используемой базу данных usersdb, которая была создана выше. Вторая команда — CREATE TABLE создает в бд таблицу users, в которой будет три столбца: id, firstname и age. Третья команда — INSERT INTO добавляет в таблицу users одну строку. Для выполнения этих команд также нажмем на значок молнии.

И в конце получим все данные из таблицы users с помощью следующих команд:

USE usersdb; SELECT * FROM users;

Таким образом, мы можем осуществлять запросы к БД в программе MySQL Workbench CE.

как сделать запрос в mysql workbench

Для того чтобы сделать запрос в MySQL Workbench, следует следовать следующим шагам:

1. Откройте MySQL Workbench и подключитесь к базе данных, с которой вы хотите работать. Для этого выберите пункт «Новое подключение» из меню «База данных».
2. После успешного подключения к базе данных, перейдите во вкладку «Запросы» или «Query» в верхней панели инструментов.
3. В поле для написания запроса введите SQL-запрос, который вы хотите выполнить. Например, чтобы выбрать все записи из определенной таблицы, нужно выполнить запрос:

Таким образом, вы можете легко делать запросы в MySQL Workbench и работать с базами данных.»

Запуск MySQL Workbench

Откройте меню Пуск и наберите ‘Workbench’. В списке выберите MySQL Workbench.

Откроется окно, называемое домашним экраном.

В верхней части Вы можете видеть список соединений с СУБД. Эти элементы управления используются для подключения к СУБД и прямого управления.

В нижней части находится список моделей. Модели используются для визуального проектирования баз данных.

Подключения

При наведении курсора мыши на подключение, у “плитки” отгибается уголок. Если нажать на этот “уголок”, отобразится информация о подключении: версия СУБД, дата последнего использования, адрес сервера СУБД, логин, и т.п.

В правом нижнем углу есть кнопка ‘Connect’, которая откроет данное подключение.

Подключение так же можно открыть, нажав на саму “плитку”.

Откройте первое подключение в списке.

Редактор SQL-запросов

После открытия подключения, открывается окно редактора SQL-запросов.

В центре мы видим окно редактирования запроса.

Обратно к домашнему экрану можно вернуться, нажав на иконку в левом верхнем углу окна.

Слева находится навигатор, отображающий основные задачи и список объектов БД.

В левом нижнем углу находится окно информации о выбранном объекте.

Справа находится окно помощи.

Внизу – окно истории запросов.

В правом верхнем углу находятся элементы управления, позволяющие скрыть или отобразить боковые панели.

Выполнение запросов

Выполним запрос к базе world . Во-первых, выберем базу данных world как активную.

Для этого, найдите объект world в навигаторе, и выберете его двойным кликом, либо нажав правой кнопкой мыши и в контекстном меню выбрав Set as Default Schema .

Теперь можно выполнять запросы к базе world .

Вы так же можете выбрать активную базу, выполнив запрос

 USE schema_name;

 USE world;

Для начала, запросим список таблиц в выбранной базе. Для этого, в окне редактирования SQL-запроса, напишем

 SHOW TABLES;

При введении запроса, окно помощи автоматически отображает подсказки по синтаксису вводимого запроса, если эта возможность включена. Чтобы включить автоматическую подсказку, нажмите на третью слева кнопку панели в окне помощи.

Затем нажмем на кнопку выполнения запроса (желтая молния), или выберем в меню Query → Execute (All or Selection) . Клавиатурное сокращение – ctrl + shift + enter

Другие кнопки панели окна SQL-запроса

Слева направо, кнопки:

• Open an SQL Script File: Загружает содержимое ранее сохраненного запроса из файла.
• Save SQL Script to File: Сохраняет запрос в файл
• Execute SQL Script: Выполняет выделенную часть запроса, или весь запрос, если ничего не выделено.
• Execute Current SQL script: Выполняет выражение, на котором находится текстовый курсор.

- **Explain (All or Selection)**: Отображает для выбранной части запроса или всего запроса информацию о работе оптимизатора.

• Stop the query being executed: Прерывает выполнение текущего запроса.
• Toggle whether execution of SQL script should continue after failed statements: Переключает поведение при ошибках в выражениях. Если на кнопке отображен красный кружок, то выполнение запроса прерывается при ошибке выполнения одного из выражений. Иначе, если отображена зеленая стрелка, выражения с ошибками пропускаются и выполнение запроса продолжается.
• Commit: Подтверждает текущую транзакцию
• Rollback: Отменяет текущую транзакцию
• Toggle Auto-Commit Mode: Если включено, каждое выражение будет автоматически подтверждено.
• Set Limit for Executed Queries: Ограничение количества результатов запроса.
• Save Snippet: Сохранить текущее выражение или выбранный фрагмент для быстрого использования.
• Beautify SQL: Форматировать текст запроса.
• Find panel: Отобразить панель поиска в тексте запроса.
• Invisible characters: Отображать “невидимые” символы.
• Wrapping: Включить перенос по словам.

Откроется окно результата запроса.

В центре окна результата находится результат запроса – в нашем случае, список названий таблиц. Справа – кнопки выбора стиля отображения:

• Result Grid – в виде таблицы (по умолчанию)
• Form Editor – в виде формы. Каждая запись открывается на отдельной странице, в верхней части окна есть стрелки для навигации по записям.
• Field Types – отображает типы атрибутов результата.
• Query Stats – отображает различную статистику запроса
• Execution Path – показывает алгоритм работы оптимизатора

Посмотрим, как устроена таблица Country . Для этого, выполним команду

 SELECT * FROM Country;

и выберем стиль отображения результата Field Types.

Запуск запроса на выполнение запускает все выражения, которые написаны в окне запроса. Если в окне запроса написано несколько выражений, разделенных точкой с запятой, они выполнятся последовательно. Поэтому, удаляйте либо комментируйте уже выполненные запросы.

Комментарии в SQL начинаются с двойного дефиса, например:

 -- комментарий -- SHOW TABLES; SELECT * FROM Country;

В данный момент нас интересуют атрибуты (колонки) Name – названия страны и LifeExpectancy – средняя продолжительность жизни граждан.

Выведем список стран со средней продолжительностью жизни граждан более 80 лет. Для этого выполним запрос

 SELECT Name, LifeExpectancy FROM Country WHERE LifeExpectancy>80;

Другой запрос, который можно выполнить к таблице Country – количество стран по форме правления.

 SELECT GovernmentForm, COUNT(Name) FROM Country GROUP BY GovernmentForm;

Создание базы данных

Для создания базы данных можно выполнить запрос

 CREATE DATABASE db_name;

Создайте базу данных с названием myFirstDatabase :

 CREATE DATABASE myFirstDatabase;

Чтобы отобразить созданную БД в списке объектов, нажмите на пустое место в списке правой кнопкой мыши и выберете Refresh All .

Выберите myFirstDatabase как активную.

Создание таблицы

Создадим таблицу People , состоящую из колонок id , name , birthday :

 CREATE TABLE People ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name TINYTEXT NOT NULL, birthday DATE );

Замечание Существует графический инструмент создания таблиц. Разберем его, когда будем работать с инструментом графического проектирования БД.

Проверим, что таблица создалась:

 SHOW TABLES;

Выведем содержимое таблицы:

 SELECT * FROM People;

Добавление и редактирование данных

При выборе всех столбцов таблицы, Workbench позволяет редактировать записи непосредственно через интерфейс результата запроса. Выберите стиль отображения результата Form Editor , и введите значения атрибутов name и birthday . Последнее вводится в формате YYYY-MM-DD , например для 1 сентября 2015 года введите 2015-09-01 .

Поле id оставьте пустым.

В правом нижнем углу окна результата можно увидеть кнопки Apply и Revert . Первая сгенерирует и выполнит SQL-запрос INSERT , а вторая отменит изменения.

Нажмите на Apply , посмотрите сгенерированный запрос, и примените его. Если все сделано правильно, то в таблицу вставлена новая запись. Выполните запрос

 SELECT * FROM People;

еще раз, чтобы в этом убедиться.

Замечание Можно так же добавлять и редактировать записи в табличном отображении результата.

Удаление данных

Данные можно удалять из окна результатов, используя кнопку панели Delete selected rows .

Другой способ – выполнение SQL-запроса

 DELETE FROM tbl_name WHERE condition;

Например, удалим из таблицы People запись с каким-либо значением id :

 DELETE FROM People WHERE id = 1;

Подставьте вместо 1 какое-то из существующих значений.

Замечание По умолчанию, Workbench выполняет запросы с параметром SQL_SAFE_UPDATES . Этот параметр не позволяет производить запросы UPDATE и DELETE без указания условия WHERE с первичным ключом (в данном случае id ).

Импорт и экспорт

Экспорт

В навигаторе выберите Data Export . Выберите базы данных и таблицы, которые хотите экспортировать. При необходимости, отметте другие объекты, которые хотите экспортировать: Dump Stored Procedures and Functions , Dump Events , Dump Triggers .

Выберите Export to Self-Contained File и файл, в который будет сохранен экспорт.

Не отмечайте Include Create Schema : эта опция включит в экспорт выражение CREATE DATABASE .

Нажмите на кнопку Start Export .

Импорт

В навигаторе выберите Data Import/Restore . Выберите Import from Self-Contained File .

Выберите базу данных, в которую будет произведен импорт в выпадающем списке Default Target Schema . Можно так же создать новую БД, нажав на кнопку New…

Нажмите Start Import .

Удаление таблиц

Для удаления таблиц используется запрос

 DROP TABLE tbl_name;

Удалим таблицу People

 DROP TABLE People;

Удаление БД

Для удаления БД используется запрос

 DROP DATABASE tbl_name;

Удалим таблицу myFirstDatabase

 DROP DATABASE myFirstDatabase;

Работа с запросами в MySQL

Базы данных являются ключевым компонентом многих веб-сайтов и приложений и лежат в основе хранения и обмена данными в Интернете. Одним из наиболее важных аспектов управления базой данных является практика извлечения данных из базы данных, будь то на разовой основе или частью процесса, который был закодирован в приложении. Существует несколько способов получения информации из базы данных, но один из наиболее часто используемых методов выполняется путем отправки запросов через командную строку.

В системах управления реляционными базами данных запрос — это любая команда, используемая для извлечения данных из таблицы. В языке структурированных запросов (SQL) запросы почти всегда выполняются с помощью оператора SELECT.

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

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

Подготовка окружения

В целом, команды и концепции, представленные в этом руководстве, могут использоваться в любой операционной системе на базе Linux, на которой работает любое программное обеспечение базы данных SQL. Тем не менее, он был написан специально для сервера Ubuntu 18.04. Для настройки вам понадобится следующее:

• Сервер с Ubuntu 18.04
• Установленный на сервере MySQL

Если сервер создается в NetAngels, то при создании сервера мы рекомендуем выбрать Ubuntu 18.04 Bionic LAMP

Создание образца базы данных

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

Для примера базы данных, которую мы будем использовать в этом руководстве, представьте следующий сценарий:

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

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

Если вы создали сервер в NetAngels на основе образа Ubuntu 18.04 Bionic LAMP, то откройте приглашение MySQL выполнив от пользователя root команду:

Примечание: Если зайти в MySQL таким образом не удается, то для аутентификации с использованием пароля используйте команду:

Затем создайте базу данных, запустив:

CREATE DATABASE `birthdays`; 

Затем выберите эту базу данных, набрав:

USE birthdays; 

Затем создайте две таблицы в этой базе данных. Мы будем использовать первую таблицу, чтобы отслеживать записи ваших друзей в боулинге. Следующая команда создаст таблицу под названием «tourneys» со столбцами для «name» каждого из ваших друзей, количества турниров, которые они выиграли («wins»), их лучший результат за все время и каков размер обувь для боулинга, которую они носят ( размер ):

CREATE TABLE tourneys ( name varchar(30), wins real, best real, size real ); 

Как только вы запустите команду CREATE TABLE и заполните ее заголовками столбцов, вы получите следующий вывод:

Query OK, 0 rows affected (0.00 sec) 

Заполните таблицу ‘tourneys’ некоторыми примерами данных:

INSERT INTO tourneys (name, wins, best, size) VALUES ('Dolly', '7', '245', '8.5'), ('Etta', '4', '283', '9'), ('Irma', '9', '266', '7'), ('Barbara', '2', '197', '7.5'), ('Gladys', '13', '273', '8'); 

Вы получите такой вывод:

Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 

После этого создайте еще одну таблицу в той же базе данных, которую мы будем использовать для хранения информации о любимых блюдах ваших друзей на день рождения. Следующая команда создает таблицу с именем dinners и столбцами для«имя» каждого из ваших друзей, их «дата рождения», их любимое «блюдо», их любимое «гарнир» и их любимый «десерт»:

CREATE TABLE dinners ( name varchar(30), birthdate date, entree varchar(30), side varchar(30), dessert varchar(30) ); 

Аналогично для этой таблицы вы получите отзыв, подтверждающий успешное выполнение команды:

Query OK, 0 rows affected (0.01 sec) 

Заполните эту таблицу также некоторыми примерами данных:

INSERT INTO dinners (name, birthdate, entree, side, dessert) VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'), ('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'), ('Irma', '1941-02-18', 'tofu', 'fries', 'cake'), ('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'), ('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 

Как только эта команда завершится успешно, вы закончили настройку базы данных. Далее мы рассмотрим основную структуру команд запросов SELECT.

Понимание операторов SELECT

Как упоминалось во введении, SQL-запросы почти всегда начинаются с оператора SELECT . SELECT используется в запросах, чтобы указать, какие столбцы из таблицы должны быть возвращены в наборе результатов. Запросы также почти всегда включают FROM , который используется для указания таблицы, к которой будет обращаться оператор.

Как правило, SQL-запросы следуют этому синтаксису:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply; 

Например, следующий оператор вернет весь столбец name из таблицы dinners :

SELECT name FROM dinners; +---------+ | name | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | +---------+ 5 rows in set (0.00 sec) 

Вы можете выбрать несколько столбцов из одной таблицы, разделяя их имена запятыми, например:

SELECT name, birthdate FROM dinners; +---------+------------+ | name | birthdate | +---------+------------+ | Dolly | 1946-01-19 | | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Barbara | 1948-12-25 | | Gladys | 1944-05-28 | +---------+------------+ 5 rows in set (0.00 sec) 

Вместо того, чтобы называть конкретный столбец или набор столбцов, вы можете следовать за оператором SELECT со звездочкой ( * ), которая служит заполнителем, представляющим все столбцы в таблице. Следующая команда возвращает каждый столбец из таблицы tourneys :

SELECT * FROM tourneys; +---------+------+------+------+ | name | wins | best | size | +---------+------+------+------+ | Dolly | 7 | 245 | 8.5 | | Etta | 4 | 283 | 9 | | Irma | 9 | 266 | 7 | | Barbara | 2 | 197 | 7.5 | | Gladys | 13 | 273 | 8 | +---------+------+------+------+ 5 rows in set (0.00 sec) 

WHERE используется в запросах для фильтрации записей, которые удовлетворяют указанному условию, и любые строки, которые не удовлетворяют этому условию, исключаются из результата. Предложение WHERE обычно соответствует следующему синтаксису:

. . . WHERE column_name comparison_operator value 

Оператор сравнения в предложении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:

Оператор	Что он делает
=	тесты для равенства
!=	тесты для неравенства
тесты для больше
=	тесты для больше чем или равный к
BETWEEN	проверяет лежит ли в заданном диапазоне
IN	проверяет содержатся ли строки в наборе значений
EXISTS	тесты на соответствие строки существует при заданных условиях
LIKE	проверяет совпадает ли значение с указанной строкой
IS NULL	тесты для `NULL` значения
IS NOT NULL	тесты для всех других значений, чем `NULL`

Например, если вы хотите найти размер обуви Ирмы, вы можете использовать следующий запрос:

SELECT size FROM tourneys WHERE name = 'Irma'; +------+ | size | +------+ | 7 | +------+ 1 row in set (0.00 sec) 

SQL допускает использование подстановочных знаков, и это особенно удобно при использовании в предложениях WHERE. Знаки процента ( % ) представляют ноль или более неизвестных символов, а подчеркивания ( _ ) представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не уверены, что эта запись. Чтобы проиллюстрировать это, скажем, что вы забыли любимое блюдо нескольких своих друзей, но вы уверены, что это конкретное блюдо начинается с буквы “t”. Вы можете найти его имя, выполнив следующий запрос:

SELECT entree FROM dinners WHERE entree LIKE 't%'; +--------+ | entree | +--------+ | tofu | | tofu | +--------+ 2 rows in set (0.00 sec) 

Основываясь на вышеприведенном выводе, мы видим, что блюдо — это тофу.

Могут быть случаи, когда вы работаете с базами данных, в которых есть столбцы или таблицы с относительно длинными или трудно читаемыми именами. В этих случаях вы можете сделать эти имена более читабельными, создав псевдоним с ключевым словом AS . Псевдонимы, созданные с помощью AS , являются временными и существуют только на время запроса, для которого они созданы:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners; +---------+------------+-----------+ | n | b | d | +---------+------------+-----------+ | Dolly | 1946-01-19 | cake | | Etta | 1938-01-25 | ice cream | | Irma | 1941-02-18 | cake | | Barbara | 1948-12-25 | ice cream | | Gladys | 1944-05-28 | ice cream | +---------+------------+-----------+ 5 rows in set (0.00 sec) 

Здесь мы сказали SQL отображать столбец name как n , столбец birthdate как b , а столбец sert как d .

Примеры, которые мы рассмотрели до этого момента, включают в себя некоторые из наиболее часто используемых ключевых слов и предложений в запросах SQL. Они полезны для базовых запросов, но они бесполезны, если вы пытаетесь выполнить вычисление или получить скалярное значение (одно значение, а не набор из нескольких различных значений) на основе ваших данных. Это где агрегатные функции вступают в игру.

Агрегатные функции

Часто при работе с данными необязательно просматривать сами данные. Скорее, вам нужна информация о данных. Синтаксис SQL включает в себя ряд функций, которые позволяют интерпретировать или выполнять вычисления для ваших данных, просто выполнив запрос «SELECT». Они известны как aggregate functions.

Функция COUNT считает и возвращает количество строк, соответствующих определенным критериям. Например, если вы хотите узнать, сколько ваших друзей предпочитают тофу для своего дня рождения, вы можете выполнить этот запрос:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu'; +---------------+ | COUNT(entree) | +---------------+ | 2 | +---------------+ 1 row in set (0.00 sec) 

Функция AVG возвращает среднее (среднее) значение столбца. Используя наш пример таблицы, вы можете найти средний лучший результат среди ваших друзей с помощью этого запроса:

SELECT AVG(best) FROM tourneys; +-----------+ | AVG(best) | +-----------+ | 252.8 | +-----------+ 1 row in set (0.00 sec) 

SUM используется для поиска общей суммы данного столбца. Например, если вы хотите посмотреть, сколько игр вы и ваши друзья играли в боулинг за эти годы, вы можете выполнить этот запрос:

SELECT SUM(wins) FROM tourneys; +-----------+ | SUM(wins) | +-----------+ | 35 | +-----------+ 1 row in set (0.00 sec) 

Обратите внимание, что функции AVG и SUM будут работать правильно только при использовании с числовыми данными. Если вы попытаетесь использовать их для нечисловых данных, это приведет к ошибке или просто к «0», в зависимости от того, какую СУБД вы используете:

SELECT SUM(entree) FROM dinners; +-------------+ | SUM(entree) | +-------------+ | 0 | +-------------+ 1 row in set, 5 warnings (0.00 sec) 

MIN используется для поиска наименьшего значения в указанном столбце. Вы можете использовать этот запрос, чтобы увидеть, какой худший общий рекорд в боулинге (с точки зрения количества побед):

SELECT MIN(wins) FROM tourneys; +-----------+ | MIN(wins) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) 

Аналогично, MAX используется для поиска наибольшего числового значения в данном столбце. Следующий запрос покажет лучший общий результат в боулинге:

SELECT MAX(wins) FROM tourneys; +-----------+ | MAX(wins) | +-----------+ | 13 | +-----------+ 1 row in set (0.00 sec) 

В отличие от SUM и AVG , функции MIN и MAX могут использоваться как для числовых, так и для буквенных типов данных. При запуске в столбце, содержащем строковые значения, функция MIN будет отображать первое значение в алфавитном порядке:

SELECT MIN(name) FROM dinners; +-----------+ | MIN(name) | +-----------+ | Barbara | +-----------+ 1 row in set (0.00 sec) 

Аналогично, при запуске в столбце, содержащем строковые значения, функция MAX покажет последнее значение в алфавитном порядке:

SELECT MAX(name) FROM dinners; +-----------+ | MAX(name) | +-----------+ | Irma | +-----------+ 1 row in set (0.00 sec) 

Агрегатные функции умеют больше того, что было описано в этом разделе. Они особенно полезны при использовании с предложением GROUP BY , которое рассматривается в следующем разделе, а также с несколькими другими предложениями запроса, которые влияют на сортировку наборов результатов.

Управление запросами

В дополнение к предложениям FROM и WHERE , есть несколько других предложений, которые используются для манипулирования результатами запроса SELECT . В этом разделе мы объясним и предоставим примеры для некоторых из наиболее часто используемых предложений запросов.

Одним из наиболее часто используемых предложений запроса, помимо FROM и WHERE , является предложение GROUP BY . Обычно он используется, когда вы выполняете статистическую функцию для одного столбца, но в отношении сопоставления значений в другом.

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

SELECT COUNT(name), entree FROM dinners GROUP BY entree; +-------------+---------+ | COUNT(name) | entree | +-------------+---------+ | 1 | chicken | | 2 | steak | | 2 | tofu | +-------------+---------+ 3 rows in set (0.00 sec) 

Предложение ORDER BY используется для сортировки результатов запроса. По умолчанию числовые значения сортируются в порядке возрастания, а текстовые значения сортируются в алфавитном порядке. Чтобы проиллюстрировать это, следующий запрос перечисляет столбцы name и birthdate , но сортирует результаты по дате рождения:

SELECT name, birthdate FROM dinners ORDER BY birthdate; +---------+------------+ | name | birthdate | +---------+------------+ | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Gladys | 1944-05-28 | | Dolly | 1946-01-19 | | Barbara | 1948-12-25 | +---------+------------+ 5 rows in set (0.00 sec) 

Обратите внимание, что поведение по умолчанию ORDER BY состоит в сортировке набора результатов в порядке возрастания. Чтобы изменить это и отсортировать набор результатов в порядке убывания, закройте запрос с помощью DESC :

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC; +---------+------------+ | name | birthdate | +---------+------------+ | Barbara | 1948-12-25 | | Dolly | 1946-01-19 | | Gladys | 1944-05-28 | | Irma | 1941-02-18 | | Etta | 1938-01-25 | +---------+------------+ 5 rows in set (0.00 sec) 

Как уже упоминалось ранее, предложение WHERE используется для фильтрации результатов на основе определенных условий. Однако, если вы используете предложение WHERE с агрегатной функцией, оно вернет ошибку, как в случае со следующей попыткой выяснить, какие стороны являются фаворитами по крайней мере трех ваших друзей:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3; ERROR 1111 (HY000): Invalid use of group function 

Предложение HAVING было добавлено в SQL для обеспечения функциональности, аналогичной функциональности предложения WHERE, а также совместимости с агрегатными функциями. Полезно думать о разнице между этими двумя пунктами как о том, что WHERE применяется к отдельным записям, в то время как HAVING применяется к групповым записям. С этой целью каждый раз, когда вы вводите предложение HAVING , также должно присутствовать предложение GROUP BY .

Следующий пример — еще одна попытка найти, какие гарниры являются фаворитами как минимум трех ваших друзей, хотя этот вернет результат без ошибок:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3; +-------------+-------+ | COUNT(name) | side | +-------------+-------+ | 3 | fries | +-------------+-------+ 1 row in set (0.00 sec) 

Агрегатные функции полезны для суммирования результатов определенного столбца в данной таблице. Однако во многих случаях необходимо запросить содержимое более чем одной таблицы. Мы рассмотрим несколько способов сделать это в следующем разделе.

Запрос нескольких таблиц

Чаще всего база данных содержит несколько таблиц, каждая из которых содержит разные наборы данных. SQL предоставляет несколько разных способов выполнения одного запроса для нескольких таблиц.

Предложение JOIN может использоваться для объединения строк из двух или более таблиц в результате запроса. Это достигается путем нахождения связанного столбца между таблицами и соответствующей сортировки результатов в выходных данных.

Операторы SELECT , которые включают предложение JOIN , обычно следуют этому синтаксису:

SELECT table1.column1, table2.column2 FROM table1 JOIN table2 ON table1.related_column=table2.related_column; 

Обратите внимание, что поскольку предложения JOIN сравнивают содержимое нескольких таблиц, в предыдущем примере указывается, из какой таблицы выбрать каждый столбец, предшествуя имени столбца с именем таблицы и точкой. Вы можете указать, из какой таблицы должен быть выбран столбец, например, для любого запроса, хотя это не обязательно при выборе из одной таблицы, как мы делали в предыдущих разделах. Давайте рассмотрим пример, используя наш образец Dата.

Представьте, что вы хотите купить каждому из ваших друзей пару ботинок для боулинга в качестве подарка на день рождения. Поскольку информация о датах рождения и размерах обуви ваших друзей хранится в отдельных таблицах, вы можете запросить обе таблицы по отдельности, а затем сравнить результаты для каждой из них. Тем не менее, с помощью предложения JOIN вы можете найти всю необходимую информацию с помощью одного запроса:

SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys JOIN dinners ON tourneys.name=dinners.name; +---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+------+------------+ 5 rows in set (0.00 sec) 

Предложение JOIN , используемое в этом примере, без каких-либо других аргументов, является предложением inner JOIN . Это означает, что он выбирает все записи, которые имеют совпадающие значения в обеих таблицах, и печатает их в наборе результатов, в то время как все несоответствующие записи исключаются. Чтобы проиллюстрировать эту идею, давайте добавим новую строку в каждую таблицу, у которой нет соответствующей записи в другой:

INSERT INTO tourneys (name, wins, best, size) VALUES ('Bettye', '0', '193', '9'); INSERT INTO dinners (name, birthdate, entree, side, dessert) VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream'); 

Затем повторно запустите предыдущий оператор SELECT с предложением JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys JOIN dinners ON tourneys.name=dinners.name; +---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+------+------------+ 5 rows in set (0.00 sec) 

Обратите внимание, что, поскольку в таблице «tourneys» нет записи для Лесли, а в таблице «dinners» нет записи для Бетти, эти записи отсутствуют в этих выходных данных.

Тем не менее, можно вернуть все записи из одной из таблиц, используя предложение outer JOIN . В MySQL предложения JOIN записываются как LEFT JOIN или RIGHT JOIN .

Предложение LEFT JOIN возвращает все записи из« левой »таблицы и только совпадающие записи из правой таблицы. В контексте внешних объединений левая таблица — это таблица, на которую ссылается условие FROM , а правая таблица — любая другая таблица, на которую ссылается после оператора JOIN .

Выполните предыдущий запрос еще раз, но на этот раз используйте предложение LEFT JOIN :

SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys LEFT JOIN dinners ON tourneys.name=dinners.name; 

Эта команда будет возвращать каждую запись из левой таблицы (в данном случае, «турниры»), даже если в правой таблице нет соответствующей записи. Каждый раз, когда в правой таблице нет подходящей записи, она возвращается как NULL или просто пустое значение, в зависимости от вашей RDBMS:

+---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | Bettye | 9 | NULL | +---------+------+------------+ 6 rows in set (0.00 sec) 

Теперь выполните запрос еще раз, на этот раз с предложением RIGHT JOIN :

SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys RIGHT JOIN dinners ON tourneys.name=dinners.name; 

Это вернет все записи из правой таблицы ( dinners ). Поскольку дата рождения Лесли записана в правой таблице, но для нее нет соответствующей строки в левой таблице, столбцы name и size вернутся как значения NULL в этой строке:

+---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | NULL | NULL | 1946-05-02 | +---------+------+------------+ 6 rows in set (0.00 sec) 

Обратите внимание, что левые и правые объединения могут быть записаны как LEFT OUTER JOIN или RIGHT OUTER JOIN , хотя подразумевается часть «OUTER» в предложении. Аналогично, указание INNER JOIN даст тот же результат, что и простое написание JOIN .

В качестве альтернативы использованию JOIN для запроса записей из нескольких таблиц, вы можете использовать предложение UNION .

Оператор UNION работает немного иначе, чем предложение JOIN : вместо того, чтобы печатать результаты из нескольких таблиц в виде уникальных столбцов с использованием одного оператора SELECT , UNION объединяет результаты двух операторов SELECT в один столбец.

Чтобы проиллюстрировать, запустите следующий запрос:

SELECT name FROM tourneys UNION SELECT name FROM dinners; 

Этот запрос удалит все повторяющиеся записи, что является поведением по умолчанию оператора UNION :

+---------+ | name | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Lesley | +---------+ 7 rows in set (0.00 sec) 

Чтобы вернуть все записи (включая дубликаты), используйте оператор UNION ALL :

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners; +---------+ | name | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Lesley | +---------+ 12 rows in set (0.00 sec) 

Имена и количество столбцов в таблице результатов отражают имя и количество столбцов, запрошенных первым оператором SELECT. Обратите внимание, что при использовании UNION для запроса нескольких столбцов из более чем одной таблицы каждый оператор SELECT должен запрашивать одинаковое количество столбцов, соответствующие столбцы должны иметь одинаковые типы данных, а столбцы в каждом операторе SELECT должны быть в том же порядке. В следующем примере показано, что может произойти, если вы используете предложение UNION для двух операторов SELECT , которые запрашивают разное количество столбцов:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys; ERROR 1222 (21000): The used SELECT statements have a different number of columns 

Другой способ запроса нескольких таблиц — использование subqueries. Подзапросы (также известные как inner или nested query) — это запросы, заключенные в другой запрос. Это полезно в тех случаях, когда вы пытаетесь отфильтровать результаты запроса по сравнению с результатами отдельной агрегатной функции.

Чтобы проиллюстрировать эту идею, скажем, вы хотите знать, кто из ваших друзей выиграл больше матчей, чем Барбара. Вместо того, чтобы узнать, сколько матчей выиграла Барбара, а затем выполнить другой запрос, чтобы узнать, кто выиграл больше игр, вы можете рассчитать обе с помощью одного запроса:

SELECT name, wins FROM tourneys WHERE wins > ( SELECT wins FROM tourneys WHERE name = 'Barbara' ); +--------+------+ | name | wins | +--------+------+ | Dolly | 7 | | Etta | 4 | | Irma | 9 | | Gladys | 13 | +--------+------+ 4 rows in set (0.00 sec) 

Подзапрос в этом операторе был выполнен только один раз; нужно было только найти значение из столбца wins в той же строке, что и Barbara в столбце name , а данные, возвращаемые подзапросом и внешним запросом, не зависят друг от друга. Однако существуют случаи, когда внешний запрос должен сначала прочитать каждую строку в таблице и сравнить эти значения с данными, возвращенными подзапросом, чтобы получить требуемые данные. В этом случае подзапрос называется коррелированным подзапросом.

Следующее утверждение является примером коррелированного подзапроса. Этот запрос пытается выяснить, кто из ваших друзей выиграл больше игр, чем в среднем для тех, у кого одинаковый размер обуви:

SELECT name, size FROM tourneys AS t WHERE wins > ( SELECT AVG(wins) FROM tourneys WHERE size = t.size ); 

Чтобы запрос завершился, он должен сначала собрать столбцы name и size из внешнего запроса. Затем он сравнивает каждую строку из этого набора результатов с результатами внутреннего запроса, который определяет среднее количество побед для людей с одинаковыми размерами обуви. Поскольку у вас есть только два друга с одинаковым размером обуви, в наборе результатов может быть только одна строка:

+------+------+ | name | size | +------+------+ | Etta | 9 | +------+------+ 1 row in set (0.00 sec) 

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

SELECT name, entree, side, dessert FROM dinners WHERE name = (SELECT name FROM tourneys WHERE wins = (SELECT MAX(wins) FROM tourneys)); +--------+--------+-------+-----------+ | name | entree | side | dessert | +--------+--------+-------+-----------+ | Gladys | steak | fries | ice cream | +--------+--------+-------+-----------+ 1 row in set (0.00 sec) 

Обратите внимание, что этот оператор не только включает подзапрос, но также содержит подзапрос в этом подзапросе.

Заключение

Выдача запросов является одной из наиболее часто выполняемых задач в области управления базами данных. Существует ряд инструментов администрирования баз данных, таких как phpMyAdmin или pgAdmin, которые позволяют выполнять запросы и визуализировать результаты, но с помощью операторов SELECT это все еще широко распространенный рабочий процесс, который также может предоставить вам больший контроль.

Рекомендуемые статьи:

• Оптимальные настройки mysql для Битрикс
• PhpMyAdmin — как создать пользователя и базу данных
• Резервное копирование Mysql
• Перенос пользователей в MySQL между серверами
• Как сбросить пароль MariaDB 10 на сервере с Debian