Искусство запроса SELECT в&nb p;MySQL

Проектирование, создание и наполнение базы данных — это подготовительные шаги для последующего извлечения информации из нее: выборки записей, предоставления их пользователю и последующей их обработки как средствами сервера баз данных, так и скриптовыми языками. Для выполнения этой задачи предназначен наиболее часто используемый и, вероятно, самый сложный оператор языка SQL — оператор SELECT. Используя его возможности, можно легко извлечь практически любую необходимую информацию, хранящуюся в базе данных и, используя различные функции реализованные в данном диалекте SQL, даже произвести достаточно сложную предварительную их обработку. Так как это самый сложный оператор, то и его варианты в разных версиях SQL будут отличаться, и иногда даже значительно. Мы рассмотрим использование оператора SELECT в популярном сервере баз данных для веб-проектов MySQL.

 

 

Основной синтаксис оператора SELECT можно описать следующим образом:

SELECT список_полей
FROM список_таблиц
WHERE условия_запроса
GROUP BY условия_группировки
HAVING вторичные_условия_запроса
ORDER BY условия_сортировки
LIMIT диапазон_строк

Обязательными элементами являются имя оператора и список полей Из всех этих элементов синтаксиса обязательными являются только имя оператора — SELECT — и список_полей, который определяет перечень выбираемых столбцов. Даже список_таблиц в версии SQL, реализованной в MySQL, не является обязательным, позволяя производить вычисления, не обращаясь к таблицам, а используя только встроенные функции:

SELECT POW(3,2) + SQRT(2);

Понятно, что использование такой возможности практически никогда не встречается, но, тем не менее, существует.

Рассмотрим пример использования оператора SELECT для выборки информации из небольшой базы с товаром, в котором хотя и не используются все ключевые слова оператора, но осуществляется выборка из нескольких таблиц:

SELECT i.item_name AS Name, cat.category_name AS Category, i.item_price AS Price,
i.item_quantity AS Quantity, i.item_price * i.item_quantity AS Summ, c.country_name AS Country
FROM items AS i, categories AS cat, countries AS c
WHERE cat.category_id = i.category_id AND c.country_id = i.country_id,
ORDER BY Category DESC, Price ASC, Summ ASC;

Использование алиасов позволяет использовать более короткие записи, а иногда является необходимым В данном примере в списках полей и таблиц можно увидеть еще одно ключевое слово AS, которое используется для присвоения именам полей и таблиц дополнительных имен — алиасов, которые могут использоваться для ссылок в этом же запросе на поля и таблицы. Их объявление не является обязательным, но позволяет использовать более короткие варианты записи имен полей и таблиц и делать их обозначения более понятными. Объявление алиасов таблиц после ключевого поля FROM: items AS i, categories AS cat, countries AS c — позволяет использовать боле компактные записи в списке полей: i.item_name вместо items.item_name и так далее. Подобная запись означает принадлежность поля к данной таблице. Если выборка осуществляется из одной таблицы, то указывать в качестве префикса имя таблицы не обязательно, но в нашей более сложной выборке, осуществляющей объединение трех таблиц, такой префикс становится обязательным, и использование алиасов действительно облегчает использование ссылок на разные таблицы. Алиасы имен полей у нас также используются при задании условий сортировок. Но там они используются исключительно ради облегчения чтения запроса при его проверке: слова Category и Price легче воспринять, чем cat.category_name и i.item_price, а в более сложных выборках имена полей без алиасов вообще становится трудно воспринимать. В некоторых случаях использование алиаса становится необходимым — например, когда поле в запросе — вычисляемое значение на основе других полей, в действительности не существует в таблицах, а сослаться на него необходимо. В нашем примере это поле Summ, которое вычисляется на основе полей i.item_price и i.item_quantity, но участвует при задании сортировки записей сортировки.

Правильное задание условий запроса самая сложная задача Не считая возможности задания алиасов, синтаксис списка полей и таблиц достаточно прост — это перечисление полей и таблиц через запятую, и их задание не представляет трудности. К сожалению, этого нельзя сказать про условия_запроса, задание которых часто становится самой трудоемкой процедурой, особенно в случае выборки из нескольких таблиц. Анализ уже написанных запросов нередко при попытке их оптимизации позволяет свести два запроса (основной и вспомогательный) в один, особенно если первоначальный вариант составлялся копированием SQL-команды из других диалектов SQL, где разрешено использование вложенных подзапросов. При задании условий выборки широко используются операторы сравнения, логические операторы, операторы сравнения с образцом в различных комбинациях. В нашем примере мы использовали оператор равенства "=" и логический оператор «AND». Подобное сочетание операторов часто встречается при объединении нескольких таблиц. В данном случае мы осуществляем выборку всех записей из таблицы с товарами, присоединяя к ним по условиям выборки поля из таблиц категорий и стран. Существует несколько способов объединения таблиц, которые можно реализовать, в том числе, и с помощью условий выборки, но это отдельная большая тема, и мы не будем подробно останавливаться на этом в данной статье.

После того как мы указали, из каких полей и таблиц извлекать информацию, и наложили ограничения на выборку записей с помощью условий_запроса, мы можем указать условия_сортировки записей. Они задаются достаточно просто: список полей, по которым надо осуществлять сортировку, и тип сортировки — по возрастанию или убыванию. По умолчанию используется сортировка по возрастанию — ASC, поэтому явно можно указывать тип сортировки только для случаев убывания DESC. Если сортировка не указана, то по умолчанию записи сортируются по полям, заданным с ключевыми словами PRIMARY KEY и UNIQUE.

Иногда требуется ограничить количество выводимых строк — например, если вы отображаете на вашей веб-странице не весь каталог, а по частям, указывая ссылки на другие части каталога. Ограничить количество строк можно ключевым словом LIMIT. Например, LIMIT 10 ограничит вывод первыми 10 строками, а LIMIT 10,20 выведет 10 строк, начиная с 20 строки.

Группировки строк используются с суммирующими функциями В приведенном выше примере мы выводили строки таблицы на основе заданных условий. Но очень часто возникает необходимость произвести группировку по какому-то из полей, то есть отобразить все строки, в которых значение данного поля совпадает, как одну строку. Эта операция часто осуществляется вместе с суммирующими (агрегатными) функциями, например, COUNT — подсчет строк, AVG — подсчет среднего значения сгруппированных строк, SUM — сумма всех непустых значений и так далее. Запрос

SELECT COUNT(*) FROM items; вернет нам количество строк в таблице. Вместо символа "*" можно было использовать имя поля; разница в результирующем наборе будет заключаться в том, что в случае "*" будут возвращены и строки со значением NULL, а при использовании имени поля — общее значение непустых значений. Эту функцию вместе с группировкой можно применить, к примеру, для того чтобы узнать количество наименований товаров в каждой из категорий:

SELECT COUNT(*)
FROM items
GROUP BY category_id; Это самый простой запрос с группировкой, а в реальных задачах требуется вернуть больше информации, чтобы сделать запрос более понятным для пользователя. В данном запросе может понадобиться вернуть названия категорий, отсортировать их по убыванию количества товара и, возможно, отсеять товары, количество которых меньше определенного значения. В этом случае наш запрос немного усложнится:

SELECT cat.category_name, COUNT(i.category_id) AS total_number
FROM categories AS cat, items AS i
WHERE cat.category_id=i.category_id
GROUP BY i.category_id
HAVING total_number > 3
ORDER BY total_number DESC;

Для проверки результата агрегатных функций используется условие HAVING В этом запросе мы группируем строки по номерам категорий, которые хранятся в таблице товара, не забывая извлечь также название этой категории из таблицы категорий. Для каждой группировки по номеру категории мы подсчитываем количество наименований товаров с помощью функции COUNT и сортируем по убыванию. Общее количество выводимых строк мы уменьшаем с помощью условия HAVING > 3, отсеивая категории с числом наименований товара меньше трех. С помощью HAVING в запросе задается второе условие, которое действует уже после проверки условий заданных в WHERE. Его используют именно с агрегатными функциями, результат которых нельзя проверить с помощью условий, заданных в WHERE. Однако злоупотреблять применением условия HAVING не следует — если какое-то условие можно задать в WHERE, то лучше именно так и поступить, чтобы при выполнении запроса средства оптимизации сервера баз данных могли его обработать.

Добавляя в этот запрос разные суммирующие функции, можно получить разнообразную информацию по каждой из категорий товара: SUM(i.item_quantity) подсчитает общее количество товаров в каждой из категорий, AVG(i.item_quantity) вернет среднее значение товаров в категории и так далее.

Оператор SELECT, благодаря своей сложности, может вернуть практически любую информацию, которая вам может потребоваться, но написать правильный запрос часто становится очень трудоемкой задачей. Помимо возможностей, которые предоставляет сам оператор, еще большую гибкость ему предают многочисленные операторы и функции, которые можно использовать в условиях и списках полей. Однако не следует злоупотреблять слишком сложными конструкциями — подумайте, есть ли возможность их упростить. Другая распространенная проблема — это использование двух запросов, в то время как их можно переписать в один. Использование оператора SELECT, как никакого другого оператора SQL-языка, требует тщательного анализа.

Ссылки по теме

• MySQL в веб-проектах
• Создание базы данных в MySQL
• Редактирование информации в MySQL

Статья получена: hostinfo.ru

загрузка...