суббота, 19 марта 2011 г.

Основы SQL – DML – SELECT – Часть I

Продолжаю серию статей “Основы SQL”. В этой статье я расскажу про сравнительно простые варианты применения самого сложного оператора DML – SELECT. Если в начале статьи дать полный синтаксис SELECT – это будет серьезным поводом загрустить и не читать статью дальше, поэтому я буду рассказывать про возможности этого оператора постепенно :)

Простейший случай

Позволю себе немного дополнить статью MSDN о SELECT тем, что данный оператор может использоваться не только для "выборки одной или нескольких строк или столбцов из одной или нескольких таблиц", но и для получения набора данных, не имеющего в своей основе ни одной таблицы:

SELECT 1 AS ID, 'Test 1' AS Name

Разумеется, в итоге результат выглядит как выборка из некоторой таблицы (если мы не будем использовать специальные конструкции для получения результата в виде XML).

Если одной строки недостаточно, можно использовать несколько запросов, объединенных с помощью UNION ALL. Если не использовать “ALL” в этой конструкции, в результате гарантированно не будет дублирующихся строк. Это тоже бывает полезно, но, если вы точно знаете, что дубликатов и так не будет, лучше использовать “UNION ALL”, чтобы уменьшить накладные расходы.

SELECT 1 AS ID, 'Test 1' AS Name
UNION ALL
SELECT 2, 'Test 2'

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

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

Лирическое отступление

Вы наверное заметили, что, когда речь идет об оптимальности запросов, я часто употребляю слова “обычно” и “как правило”? :) Это суровая правда жизни – когда имеешь дело с СУБД, редко встречается подход, который работает гарантированно быстрее во всех случаях и на любых данных.

Эту мысль мне очень хотелось бы донести до читателей моего блога. Особенно для тех, кто только начинает работать с СУБД и SQL. Насчет NOSQL не буду ничего утверждать за неимением практического опыта (однако что-то мне подсказывает, что там тоже хватает недетерминизма).

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

Кстати, в MS SQL Server есть очень приличный Index Tuning Advisor (в разных версиях может называться по-разному) – он, на основе типичного набора запросов, дает рекомендации по тюнингу БД, и, частенько, очень даже разумные.

Простые запросы к одной таблице

Что ж, я обуздал свою страсть к философским рассуждениям и нам пора двигаться дальше. Теперь немного усложним задачу и используем выборку из таблицы “Persons” (скрипт для создания можно взять здесь, а для заполнения – здесь):

SELECT * FROM Persons

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

Для начала, давайте поговорим о выборке всех столбцов таблицы с помощью звездочки. На первых порах это кажется удобным. К примеру, мы заполняем данными таблицу и показываем ее пользователю. Если в будущем мы добавим еще один столбец, то он автоматически появится в нашей таблице без каких-либо изменений в коде приложения и запросе к БД. Титры, играет приятная музыка… :)

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

  1. В нашу таблицу могут попасть избыточные данные, как с точки зрения безопасности (текущий пользователь не должен видеть некоторые столбцы), так и с точки зрения лишней нагрузки (добавили большое текстовое поле или картинку). Помимо этого, пользователь может загрустить, увидев таблицу из 20 столбцов, 15 из которых ему не нужны. Плавно переходим ко второму пункту.
  2. Довольно часто просто отобразить таблицу “как есть” недостаточно. Во-первых, названия столбцов могут не устроить. Во вторых, значения в столбцах нужно будет отформатировать по-разному (выравнивание, цветовое оформление и т.п.). Это приводит к тому, что у нас все равно явно указан некоторый набор столбцов, а раз он есть, то почему бы его явно не указать в запросе.
  3. Если вы в коде приложения используете некоторый столбец по названию, а его потом удалили/переименовали, то вы столкнетесь с ошибкой, которую можно будет отследить только в процессе выполнения куска кода, затронутого изменениями. А этот код может вызваться по щелчку на какую-нибудь кнопку и при развертывании новой версии ее не протестируют (если у вас 100% покрытие кода тестами – не обращайте внимания на мои слова :). В случае же построения запроса с указаниями названий столбцов мы получим ошибку сразу при получении данных.
  4. Более редкий случай, когда часть столбцов, которая нам нужна находится в одном индексе (или индексе и первичном ключе). В этом случае MS SQL Server может взять данные напрямую из индекса, минуя таблицу. Логично предположить, что считывание при этом произойдет значительно быстрее (только не нужно покрывать всю таблицу индексами – запись в нее будет медленнее с каждым новым индексом).

По приведенным выше причинам, я обычно использую “*” для тестовых запросов, но не использую в реальной разработке.

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

SELECT PersonID, FirstName, LastName FROM Persons

Как вы можете видеть, мы задали свой порядок столбцов и взяли только три столбца из таблицы. Разумеется, это не все, на что способен T-SQL :)

Для начала, давайте получим ФИО в одном столбце:

SELECT PersonID, FirstName + 
COALESCE(' ' + MiddleName + ' ', ' ') + LastName AS ФИО
FROM Persons

Несложно догадаться, что, с помощью ключевого “AS”, мы назвали результирующий столбец “ФИО”, иными словами, задали для него “псевдоним” (alias). Есть и другой вариант того же действия – написать “ФИО = FirstName +…”.

Обратите внимание на функцию COALESCE – она используется для того, чтобы правильно обработать значения NULL в столбце MiddleName (в отличие от него, два оставшихся столбца в ФИО не допускают значения NULL). Функция COALESCE принимает переменное число аргументов и возвращает первый аргумент, не равный NULL. Стоит отметить, что сложение двух строк, одна из которых NULL, а другая не NULL, дает в результате NULL.

Разумеется над столбцами можно совершать множество других операций, рассмотреть которые в рамках блога невозможно, поэтому просто дам ссылку на функции Transact-SQL.

Сортировка (ORDER BY)

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

Сортировка результата запроса осуществляется с помощью фразы “ORDER BY”, при этом, сортировать можно по псевдонимам:

SELECT PersonID, FirstName +
COALESCE(' ' + MiddleName + ' ', ' ') + LastName AS ФИО
FROM Persons ORDER BY ФИО

Также можно сортировать по номеру столбца в результате (что приводит к не очень хорошо сопровождаемому коду, но иногда удобно). Естественно, можно сортировать по нескольким столбцам:

SELECT PersonID, FirstName, LastName FROM Persons
ORDER BY 3, FirstName

При сортировке по нескольким столбцам получаем результат упорядоченный по первому столбцу в “ORDER BY” (в примере выше это LastName), а для одинаковых значений первого столбца результат упорядочивается по второму столбцу и так далее.

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

SELECT FirstName, LastName FROM Persons ORDER BY SIN(PersonID)

По умолчанию результат сортируется по убыванию, но можно задать и сортировку по возрастанию (соответствующие ключевые слова – ASC и DESC):

SELECT FirstName, LastName FROM Persons
ORDER BY FirstName ASC, LastName DESC

Ограничение на количество строк

Наверное, мало для кого будет сюрпризом то, что некоторые таблицы могут содержать несколько сотен тысяч и более строк. Для того, чтобы можно было получить первые N строк можно (и нужно!) использовать ключевое слово “TOP”. Вообще говоря, TOP можно использовать и для других DML инструкций, однако за более чем 10 лет работы с MS SQL Server я использовал TOP только для SELECT (может задач подходящих не встречалось).

“TOP” можно использовать как в процентном отношении, например, выбрать только 10% записей:

SELECT TOP 10 PERCENT * FROM Phones

Или же указать конкретное количество строк:

SELECT TOP 10 * FROM Phones

Начиная с версии MS SQL Server 2005, в “TOP” можно использовать выражения (заключенные в круглые скобки). Естественно, в них можно использовать переменные и многие функции, но нельзя ссылаться на столбцы таблицы.

Разумеется, в использовании “TOP” без сортировки смысла не очень много – разве что, посмотреть на что похожи данные в таблице. А с сортировкой, кстати, связана еще одна редко используемая, но иногда полезная возможность “TOP” – включить дополнительно результаты с последним значением полей сортировки:

SELECT TOP 10 WITH TIES * FROM Phones ORDER BY PersonID

В приведенном выше примере, мы получим все телефоны для всех попавших в выборку лиц, даже если их суммарно строк будет больше 10.

Рассмотрим пример, более приближенный к жизни. Мы хотим увидеть 100 клиентов, лидирующих по количеству покупок в магазине (мы уже знаем, что здесь можно использовать “DESC” для сортировки). Но если последний в списке сделал 5 покупок, то мы хотим увидеть всех, кто тоже сделал 5 покупок. Теперь вы знаете, как это сделать, не выкачивая лишние данные ;)

К слову о выкачивании лишних данных, хочу в очередной раз предостеречь от этого. В свое время, делал с коллегами ревью кода некоего программного продукта, в котором первые 100 записей из таблицы, содержащей несколько сотен тысяч строк получались в лоб – сначала несколько сотен тысяч записей считывались в DataSet, а потом на экран в цикле выводились первые 100. Отчасти по этой причине я так подробно и остановился на возможностях “TOP”.

К слову сказать, встречался с квалифицированными разработчиками на C++/C#, которые совершали похожие (разве что менее вопиющие) ошибки, просто в силу некоей инерции мышления – они думали о таблицах так же, как о массивах или списках. Но все-таки со временем они понимали, что работа с базой данных имеет свою специфику, о которой нужно помнить.

Фильтры (WHERE)

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

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

SELECT * FROM Persons WHERE PersonID = 1

Если говорить о поиске среди данных о физических лицах, то еще один очень распространенный вопрос, который пользователь задает базе данных – у кого ФИО “такое-то” или у кого фамилия начинается заданным образом (в Transact-SQL для этого есть оператор LIKE):

SELECT * FROM Persons WHERE LastName LIKE 'Ив%'

Как и в случае с “ORDER BY”, в фильтрах можно использовать любые операторы и функции (если это не противоречит их определению), в частности, вышеупомянутый запрос по ФИО можно реализовать так:

SELECT * FROM Persons WHERE FirstName + 
COALESCE(' ' + MiddleName + ' ', ' ') + LastName =
'Петр Петрович Иванов'

Третий по популярности вариант – разнообразные сравнения. На всякий случай уточню – широкомасштабных исследований не проводил, основываюсь на личном опыте разработки типичных приложений :) Часто встречаются задачи вида “отобрать всех клиентов, закупающих более чем на 1000 у.е.” и т.п. – все это производится за счет операторов сравнения:

SELECT * FROM Persons WHERE Number > 2

И, как многие из вас уже знают, область поиска можно сужать последовательно, добавляя условия к “WHERE” с помощью операторов “AND” и “OR”. Так, например, можно выбрать телефонные номера заданного абонента по определенному шаблону:

SELECT * FROM Phones WHERE PersonID = 1 AND Phone LIKE '_08%'

Для начала, информации о фильтрах достаточно, более интересные ситуации (с многотабличными запросами) разберем в следующей статье. Хочу только еще раз предупредить о сравнениях с NULL (неопределенным значением). Сравнения с NULL всегда ложны, в том числе, когда мы сравниваем два значения NULL. То есть, если в “WHERE” мы напишем NULL = NULL или NULL != NULL – результатом в обоих случаях будет пустая выборка.

Анонс

В следующей части рассказа об операторе “SELECT” я расскажу о многотабличных запросах – одном из самых сложных и интересных аспектов SQL, но на достаточно простых примерах.

Если у вас есть пожелания или новые темы – пишите в комментариях или на olegaxenow.reformal.ru. Постараюсь учесть.

2 комментария:

  1. Очень интересно было бы увидеть возможности изменения параметров сортировки результата запроса в зависимости от условий.

    Например:
    ...
    ...
    ORDER BY
    x.BranchId DESC ,
    x.Risk DESC ,
    CASE x.CurrencyTag
    WHEN '*' THEN 2
    WHEN '$' THEN 1
    ELSE 0
    END ASC

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

    Спасибо.

    ОтветитьУдалить
  2. Спасибо за пожелание.

    Будет в третьей части, относительно GROUP BY уже в черновике написал, про ORDER BY напишу.

    Только не совсем понял насчет "выбрить" :)

    ОтветитьУдалить