MYSQL виды выборок 


MYSQL виды выборок

Опубликовано

 

В большинстве случаев к БД обращаются для получения данных, находящихся в таблицах. Чтобы собрать нужную информацию, как правило, необходимо охватить в запросе более одной таблицы. СУБД MYSQ предоставляет ряд методов выборки, о которых пойдет речь в статье. Рассматриваемые выборки:

  • Тривиальный или элементарный запрос;
  • Полное объединение;
  • Левое объединение;
  • Вложенные выборки;
  • Выборки при участии оператора UNION;

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

Таблица `students`, содержит персональную информацию студентов

 

Таблица `subjects`, названия предметов

 Таблица `students_mark`, оценки студентов

 

Элементарный или тривиальный запрос

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

Запрос всех имеющихся столбцов в таблице `students`

SELECT * FROM `students`

Тот же запрос, явно указывающий столбцы для вывода

SELECT `id`,`firstname`,`lastname` FROM `students`

 

Полное объединение

Полным считается объединение, в котором задействовано более одной таблицы. При таком объединении строки из одной таблицы сопоставляются со строками из другой, создавая подмножество строк со всевозможными комбинациями.

При построении запроса в разделе FROM необходимо перечислить таблицы участвующие в объединении. Например, в нижеследующем примере, строки таблиц `students` и `students_mark` объединяются, образуя всевозможные комбинации.

SELECT * FROM `students`,`students_mark`

Как видно из рисунка такое объединение создает большое количество строк. Формула объединения такова:

“Количество строк 1 таблицы” * “Количество строк 2 таблицы” * … * “Количество строк N таблицы”

Из чего следует, что в текущем запросе скомбинировано 7*4=28 строк

 

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

SELECT * 
FROM `students`,`students_mark` 
WHERE `students`.`id`=`students_mark`.`idstudent`

В результате запроса получены все студенты, у которых есть оценки, студент Александр Александров исключается из списка, т.к. не имеет оценок.

Команда SELECT * выводит все имеющиеся столбцы в объединенных таблицах. Это не всегда удобно, к примеру, в текущем запросе уже есть совпадения имен (столбец id), некоторые столбцы вообще не нужны при выводе (idstudents, т.к. idstudents=id). Поэтому для вывода необходимых столбцов их нужно указать в операторе SELECT, к тому же есть возможность задать псевдоним для каждого столбца, после команды AS:

Перепишем запрос, учитывая полученную информацию:

SELECT 
`students`.`id` AS idstudent,
`students`.`firstname` AS name,
`students`.`lastname` AS surname,
`students_mark`.`id` AS idmark,
`students_mark`.`idsubject` AS idsubject,
`students_mark`.`mark` AS mark
FROM `students`,`students_mark` 
WHERE `students`.`id`=`students_mark`.`idstudent`

 

Левое объединение

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

Левое объединение действует, так же как и полное, однако оно еще включает данные одной из таблиц, которые не совпадают с критерием выборки.

Для создания левого объединения используется оператор LEFT JOIN, он располагается вместо запятой разделяющей таблицы в операторе FROM, для задания условия используется оператор ON, аналог оператора WHERE.

Итак, создадим запрос для вывода всех студентов, независимо есть ли у них оценка или нет:

SELECT 
`students`.`id` AS idstudent,
`students`.`firstname` AS name,
`students`.`lastname` AS surname,
`students_mark`.`id` AS idmark,
`students_mark`.`idsubject` AS idsubject,
`students_mark`.`mark` AS mark
FROM `students`
LEFT JOIN `students_mark` ON `students`.`id`=`students_mark`.`idstudent`

В данном объединении выбираются сначала все строки левой таблицы `students`, затем на основании условия ON им сопоставляются строки из таблицы `students_mark` создавая множество объединений. Для тех строк из левой таблицы`students`, где соответствия не были найдены, в столбцах устанавливаются значения NULL.

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

SELECT 
`students`.`id` AS idstudent,
`students`.`firstname` AS name,
`students`.`lastname` AS surname
FROM `students`
LEFT JOIN `students_mark` ON `students`.`id`=`students_mark`.`idstudent`
WHERE `students_mark`.`idstudent` IS NULL

 

Вложенные выборки

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

Вложенные выборки для получения идентификатора

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

SELECT *
FROM `table1`
WHERE `id` = (SELECT `id` FROM `table2` WHERE `name`="T" LIMIT 1)

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

Рассмотрим отдельно вложенный запрос для получения максимального среднего балла:

SELECT `idstudent`
FROM `students_mark` 
GROUP BY `idstudent`
ORDER BY AVG(`mark`)DESC
LIMIT 1

Запрос содержит оператор GROUP BY который объединяет строки с одинаковыми значениями столбца `idstudent`. Агрегирующая функция AVG находит из объединенных значений столбца `mark` среднее значение, а оператор ORDER BY сортирует полученные данные по убыванию. Оператор LIMIT 1 оставляет единственный необходимый результат, остальные отбрасываются.

Соединим вложенную выборку с основной:

SELECT * 
FROM `students`
WHERE 
`id`=
(SELECT
`idstudent`
FROM `students_mark` 
GROUP BY `idstudent`
ORDER BY AVG(`mark`)DESC
LIMIT 1)

Схема действия вложенного запроса

 

Вложенные выборки IN, NOT IN

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

SELECT *
FROM `table1`
WHERE `column1` IN (SELECT `column2` FROM `table2`)

Выборка студентов, у которых есть оценки.

SELECT *
FROM `students`
WHERE `id` IN (SELECT `idstudent` FROM `students_mark` GROUP BY `idstudent`)

 

Схема действия вложенного запроса

 

Аналогично, чтобы найти студентов, у которых отсутствуют оценки необходимо за место оператора IN применить оператор NOT IN

SELECT * FROM `students` 
WHERE `id` NOT IN (SELECT `idstudent` FROM `students_mark` GROUP BY `idstudent`)

 

Выборки из нескольких таблиц используя UNION

Оператор UNION применяется для объединения нескольких запросов SELECT в единый запрос.

SELECT `column1_1`,`column1_2` FROM `table1` 
UNION SELECT `column2_1`,`column2_2` FROM `table2`
UNION SELECT `column3_1`,`column3_2` FROM `table3`

Как видно из примера в одном запросе объединены три SELECT, каждый запрашивает данные из столбцов указанной таблицы. В результате будут получены данные последовательно соединенные из каждого запроса, т.е. сначала из таблицы `table1`, затем `table2` и `table3`.

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

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

Так как тип результирующего столбца определяется по типу столбца первого SELECT, то данные полученные из столбцов в остальных объединениях преобразуются к этому типу. На этом этапе данные могут быть утеряны, поэтому необходимо следить, чтобы типы столбцов были такие же или приблизительно схожи.

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

 

SELECT `id` FROM `students`
UNION SELECT `id`, FROM `subjects`

Объединение значений столбцов с именем `id` из двух таблиц, повторяющиеся значения отбрасываются.

 

SELECT `id` FROM `students`
UNION ALL SELECT `id` FROM `subjects`

Объединение всех значений столбцов из двух таблиц, включая повторяющиеся значения, благодаря использованию в запросе ключевого слова ALL.

 

Для каждой выборки можно добавить условие с помощью оператора WHERE.

SELECT `id`,`firstname` FROM `students` WHERE `id`="1" 
UNION SELECT `id`,`name` FROM `subjects` WHERE `id`="1"

В каждом запросе указано условие, по которому выводятся записи имеющие `id`="1"

 

Результат запроса можно отсортировать, для этого в конце последнего SELECT необходимо добавить оператор ORDER BY, имя столбца подвергающегося сортировке необходимо взять из первого SELECT. Данный способ сортировки относится к результату в целом.

 

SELECT `id`,`firstname` FROM `students` 
UNION SELECT `id`,`name` FROM `subjects`
ORDER BY `firstname`

 

Кроме сортировки, общий результат объединения можно ограничить оператором LIMIT, так же как и ORDER BY его необходимо поместить после последнего запроса.

SELECT `id`,`firstname` FROM `students` 
UNION SELECT `id`,`name` FROM `subjects`
LIMIT 3

 

Рассмотренные выше сортировки и ограничение применялись к результату в целом. Далее рассмотрим их применение в отдельно взятом запросе.

В старых версиях СУБД MYSQL была возможность сортировать отдельно взятый SELECT, однако в современных версия данная возможность была прекращена.

(SELECT `id`,`firstname` FROM `students` ORDER BY `id` DESC )
UNION (SELECT `id`,`name` FROM `subjects` ORDER BY `name` ASC)

Приведенный выше запрос выполнится успешно, однако сортировка будет проигнорирована, результаты будут такие же, как и при запросе:

SELECT `id`,`firstname` FROM `students`
UNION SELECT `id`,`name` FROM `subjects`

Подробнее об исключении ORDER BY читайте тут

 

В отличие от ORDER BY оператор LIMIT для ограничения вывода результатов в отдельном запросе SELECT поддерживается и по сей день.

(SELECT `id`,`firstname` FROM `students` LIMIT 3)
UNION (SELECT `id`,`name` FROM `subjects` LIMIT 2)