4.4 Команда выборки данных (select)

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

Новая таблица является виртуальной, т.е. она существует (обычно в оперативной памяти) до тех пор, пока в ней есть необходимость, она может участвовать в другой команде SQL (CREATE для различных объектов БД, INSERT), в другой команде SELECT (а также DELETE или UPDATE) как результат вложенного запроса или передается клиенту для последующей обработки.

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

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

SELECT [DISTINCT] список_выражений(* - все столбцы таблицы)

FROM список_таблиц, представлений, запросов

(возможно, с операцией соединения JOIN)

[WHERE условия_отбора_строк]

[GROUP BY список_выражений_для_группировки]

[HAVING условия_отбора_групп_с_агрегатными_функциями]

[ORDER BY список_выражений_для_сортировки]

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

4.4.1 Запросы на выборку по одной таблице

Выборка всех данных таблицы

SELECT * FROM имя_таблицы (представления)

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

Например: выбрать все данные из таблицы students

SELECT * FROM students

В такой форме запросы ставятся крайне редко, поскольку пересылку на компьютер клиента всех данных таблицы, хранимой на сервере, разумно только для совсем небольших таблиц. В подавляющем большинстве приложений требуется отобрать только часть столбцов и/или строк таблицы. Однако в процессе интерактивной работы, например, с утилитой SQL *PLUS в Oracle, символ * может оказать хорошую услугу, если имена столбцов неизвестны или хочется сократить время набора текста запроса.

Отбор столбцов (операция проекции)

SELECT список_имен_столбцов FROM имя_таблицы

Например:

выбрать столбцы с фамилией и датой рождения из таблицы студентов

SELECT name_st, born FROM students

выбрать только столбец с фамилией из таблицы студентов

SELECT name_st FROM students

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

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

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

Устранить перечисленные недостатки совсем несложно.

Удаление строк-дубликатов из результатов запроса

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

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

SELECT DISTINCT name_st FROM students

При большом ожидаемом количестве строк-дубликатов в результатах запроса использование DISTINCT позволяет существенно сократить размер выборки.

Например, команда

SELECT DISTINCT mark FROM marks

при существующей пятибальной системе возвратит максимум четыре строки, содержащих значения - 2,3,4,5. Порядок следования этих значений предсказать невозможно, поэтому при необходимости получить результаты запроса в отсортированном виде, необходимо явно указать в его тексте параметры, необходимые для сортировки.

Сортировка результатов запроса

Фраза ORDER BY…, необходимая для представления результатов выборки в отсортированном виде, обязательно должна быть последней в команде SELECT, поскольку сортировка результатов всегда завершает выполнение запроса на выборку.

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

Сортировку можно выполнять по столбцам различных типов, исключая типы больших объектов (BLOB, CLOB). Сортировка по текстовым столбцам выполняется в лексикографическом (алфавитном) порядке, сортировка по столбцам даты и времени – в хронологическом порядке.

По умолчанию значения сортируются по возрастанию (точнее, по неубыванию), но можно установить порядок сортировки «по убыванию» с помощью ключевого слова DESC[ENDING]. Можно и явно указать сортировку по возрастанию с помощью ключеого слова ASC[ENDING], но это никак не отразится на результате. Если сортировка выполняется сразу по нескольким столбцам, DESC необходимо указывать для каждого столбца в списке, для которого необходима сортировка по убыванию.

Например, запрос

SELECT name_st FROM students ORDER BY name_st

возвратит список студентов в алфавитном порядке, а запрос

SELECT name_st FROM students ORDER BY name_st, born

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

SELECT name_st FROM students ORDER BY name_st, born DESC

Если и сортировать фамилии требуется в порядке, обратном алфавитному, текст будет выглядеть так:

SELECT name_st FROM students ORDER BY name_st DESC, born DESC

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

SELECT name_st, born FROM students ORDER BY 1

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

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

Отбор строк (операция выборки)

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

В качестве критерия для отбора строк таблицы можно задать любое логическое выражение, используя фразу WHERE. В процессе выполнения запроса быдет выполнен отбор только тех строк, для которых данное выражение принимает значение «истина». Таким образом, результатом операции выборки может быть произвольное число строк таблицы (от нуля до всех строк).

Например, запрос:

select * from students where cod_st=10

возвратит все данные о студенте, у которого личный код=10. Такой запрос гарантированно вернет не более одной строки (если студента с кодом 10 в таблице нет, то запрос не вернет ни одной строки, но вернуть, например, две строки он не сможет, т.к. личный код студента обладает свойством уникальности).

Условие отбора строк может включать следующие операции:

• операции сравнения < , > , <= , >= , !=(<>) , =

• операция проверки на отсутствие данных IS NULL

или их наличие IS NOT NULL

• логические операции: and, or, not

• операция вхождения значения в заданный диапазон значений

between начальное_значение and конечное_значение

• операция принадлежности значения заданному множеству

in (множество)

• операция соответствия заданному шаблону (для текстовых столбцов)

LIKE ‘шаблон’

В шаблоне для операции LIKE разрешено использовать два символа-заменителя:

• % заменяет последовательность из любого количества любых символов (в том числе и пустая последовательность)

• _ заменяет один любой символ в заданной позиции.

Например, пусть требуется выбрать фамилии и телефоны всех студентов, фамилия которых начинается на букву «С». Список отсортировать по фамилии. Текст запроса:

select namt_st, phone

from students

where name_st LIKE ‘С%’

ORDER BY 1

Если в приведенном запросе заменить шаблон на ‘%C%’, то такой запрос выберет всех студентов, у которых в фамилии есть хотя бы одна буква «С». Если шаблон заменить на ‘C_____’ (после буквы С стоит пять знаков подчеркивания), то будут выбраны студенты, фамилия которых начинается на букву С и содержит 6 символов.

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

Поэтому, например, при поиске студентов, в фамилии которых есть хотя бы одна буква С, в качестве условия отбора следует использовать

WHERE name_st LIKE ‘%C%’ OR name_st LIKE ‘%c%’

Следует помнить, что запросы с операцией LIKE обычно выполняются довольно медленно, особенно если символ % стоит в начале шаблона (к такому запросу невозможно подключить древовидный индекс для ускорения поиска). Однако при поиске в текстовых столбцах данная операция используется довольно часто. В Oracle версии 10 появилось и еще более мощное средство – поиск по регулярным выражениям, которое пока в стандарт языка SQL не вошло.

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

Пусть требуется выбрать все строки из таблицы оценок, в которых присутствуют оценки 3 или 4. Запрос простой, но может быть выполнен несколькими разными способами:

SELECT * FROM marks WHERE mark=3 OR mark=4

SELECT * FROM marks WHERE mark>2 AND mark<5

SELECT * FROM marks WHERE mark IN (3, 4)

SELECT * FROM marks WHERE mark BETWEEN 3 AND 4

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

Требуется выбрать такие строки из таблицы студентов, в которых отсутствует телефон студента.

SELECT * FROM students WHERE phone IS NULL

Обратим внимание, что запрос

SELECT * FROM students WHERE phone = NULL

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

Создание вычисляемых столбцов (операция расширения)

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

SELECT … выражение_для_вычисления [AS] псевдоним …

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

Например, пусть требуется выводить возраст студента вместо его даты рождения. Для этого необходимо создать вычисляемый столбец на основе выражения, которое вычисляет возраст, используя дату рождения и текущую дату. Приводимый запрос будет работать только в СУБД Oracle, поскольку использует ее функцию sysdate для получения текущей даты. Разность двух дат исчисляется в днях, которые затем переводятся в возраст в годах:

select cod_st, name_st, trunc((sysdate-born)/365.25) age from students

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

• имена столбцов

• константы

• операции

• функции

• круглые скобки.

Числовые и текстовые константы записываются обычным способом, например, запрос:

SELECT ‘студент’ FROM students

столько раз выведет слово студент, сколько строк имеется в таблице students (разумеется, данный запрос не имеет никакого практического смысла). Можно заметить, кстати, что в Oracle фраза FROM в запросах является обязательной, поэтому в любой базе данных автоматически создается таблица с именем dual, состоящая из одной строки и одного столбца, которая может использоваться в запросах, выводящих константы или системные функции. Например, запрос:

SELECT user FROM dual

возвратит имя пользователя, который открыл тот сеанс связи с сервером, в котором выполняется данный запрос.

Набор арифметических операций является стандартным (в стандарте имеется еще операция возведения в степень **, которая в большинстве СУБД не реализована). Для текстовых данных часто используется операции конкатенации, которая в стандарте и большинстве СУБД обозначается двумя вертикальными линиями (||) вместо привычного знака «+», который также используется в некоторых СУБД (Access, Microsoft SQL Server).

Например, выведем фамилию и телефон студентов в виде одного текстового столбца для тех студентов, телефон которых известен:

SELECT name_st ||‘ тел. ‘|| phone AS name_phone FROM students

WHERE phone IS NOT NULL

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

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

• текстовые функции; используются для управления текстовыми строками, например, для удаления ведущих и завершающих пробелов (функции LTRIM RTRIM), получения фрагмента строки (функция SUBSTR) или преобразования букв в верхний или нижний регистр (функции UPPER и LOWER)

• числовые функции; используются для выполнения математических операций над числовыми данными, например, для вычисления абсолютных значений (функция ABS) и выполнения алгебраических вычислений (тригонометрические функции, логарифмы, остаток от деления – функция MOD, округление и усечение – функции ROUND и TRUNC)

• функции даты и времени; используются для управления значениями даты и времени и для выборки отдельных частей этих значений, например, для возвращения года, месяца, дня или дня недели для заданной даты

• функции преобразования типов данных, например, преобразовать дату или число в строку текста и наоборот (в стандарте CHAR(x), DATE(x) и т.д., в Oracle аналогичные функции TO_CHAR(x, строка_формата), TO_DATE(x, строка_формата) и т.д.)

• системные функции, например, уже упоминавшиеся имя пользователя (функция USER) или системная дата (функция SYSDATE).

Использование оператора CASE в вычисляемых столбцах

Данный оператор позволяет организовывать ветвления в вычисляемых столбцах, что позволяет внести в запрос элементы процедурной логики. Он аналогичен, например, оператору case из языка Pascal или оператору switch из языка С. Например, запрос:

SELECT cod_st, cod_sub, CASE mark

WHEN 5 THEN ‘ОТЛИЧНО’

WHEN 4 THEN ‘ХОРОШО’

WHEN 3 THEN ‘УДОВЛЕТВОРИТЕЛЬНО’

WHEN 2 THEN ‘ПЛОХО’

END mark FROM marks

вместо цифры выведет оценку в виде соответствующего слова.

Использование агрегатных функций

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

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

В стандарте определено 5 агрегатных функций:

sum(имя_столбца) – сумма значений заданного столбца,

avg(имя_столбца) – среднее значение

min(имя_столбца), max(имя_столбца)– минимальное и максимальное значение

count([DISTINCT] * или имя_столбца) – подсчет количества строк.

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

Например:

Подсчитать средний балл по всем студентам и предметам

select avg (mark) avg_mark from marks

Найти минимальную и максимальную даты рождения студентов

select MIN(born) min_date, MAX(born) max_date from students

Функция COUNT, казалось бы, вообще не должна содержать аргументов, поскольку ее назначение – подсчет количества строк, что она и делает, если в качестве аргумента используется символ-заменитель *. Однако, если в качестве аргумента использовать имя столбца, данная функция будет подсчитывать количество непустых значений в данном столбце. Второй вариант аргумента разумно использовать, только если столбец не имеет ограничения NOT NULL. Использование ключевого слова DISTINCT приводит к тому, что подсчитывается количество уникальных значений в заданном столбце.

Например:

Посчитать количество студентов (количество строк в таблице students)

select count (*) count_students from students

Подсчитать количество студентов, для которых известен номер их телефона.

select count (phone) count_phones from students

Подсчитать количество уникальных значений оценок в таблице marks:

select count (DISTINCT mark) count_marks from marks

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

Группировка и агрегатные функции

Агрегатные функции могут вычисляться как по всей таблице, так и по отдельным группам строк, в последнем случае над таблицей выполняется операция группировки.

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

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

Например, сгруппировав эту таблицу по столбцу cod_st, можно подсчитать различные итоговые данные по каждому студенту. Так, запрос:

Select cod_st, avg(mark) avg_mark, count(mark) count_mark

from marks

GROUP BY cod_st

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

Если выполнить группировку таблицы marks по столбцу cod_sub (код предмета), то можно подсчитать те же итоговые данные по каждому предмету. Но тогда в список вывода запроса, кроме агрегатных функций, можно включить только код предмета.

Select cod_sub, avg(mark) avg_mark, count(mark) count_mark

from marks

GROUP BY cod_sub

Обобщив примеры, выведем общее правило для запросов с группировкой:

в списке выражений, который следует за словом SELECT, могут быть только выражения из фразы group by и агрегатные функции (или выражения на их основе).

Условия отбора групп

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

Например, пусть требуется выбрать тех студентов, у которых средний бал >4. Текст запроса имеет вид:

select cod_st, avg(mark) avg_mark

from marks

Group by cod_st

having avg(mark)>4

Следует отметить, что фраза HAVING может использоваться только после фразы GROUP BY.