Что помимо имен столбцов может стоять в целевом списке

Условия отбора в операторе SELECT в запросах SQL на практике

Vovan_ST

ИТ специалист со стажем. Автор статьи. Профиль

В SQL используется множество условий отбора, позволяющих эффективно и естественно создавать различные типы запросов. Ниже рассматриваются пять основных условий отбора (в стандарте ANSI/ISO они называются предикатами):

Наиболее распространенным условием отбора в SQL является сравнение. При сравнении SQL вычисляет и сравнивает значения двух SQL-выражений для каж­дой строки данных. Выражения могут быть как очень простыми, например содер­жать одно имя столбца или константу, так и более сложными, например содер­жать арифметические операции. В SQL имеется шесть различных способов срав­нения двух выражений, показанных на рис. 6.

Рис. 6. Синтаксическая диаграмма сравнения

Ниже приведены типичные примеры сравнения.

Найти имена всех служащих, принятых на работу до 2006 года.

Вывести список офисов, фактические объемы продаж в которых составили менее 80 процентов от плановых.

Вывести список офисов, менеджером которых не является служащий с идентифика­тором 108.

Когда СУБД сравнивает значения двух выражений, могут быть получены три результата:

Выборка одной строки

Чаще всего используется сравнение, в котором определяется, равно ли значе­ние столбца некоторой константе. Если этот столбец представляет собой первич­ный ключ, то запрос возвращает всего одну строку, как в следующем примере.

Узнать имя и лимит кредита клиента с идентификатором 2107.

Значения NULL

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

Вывести список служащих, превысивших плановый объем продаж.

Вывести список служащих, не выполнивших план.

Проверка на принадлежность диапазону (BETWEEN)

Рис. 7. Синтаксическая диаграмма проверки на принадлежность диапазону (between)

Следующий пример иллюстрирует типичную процедуру проверки на принад­лежность диапазону.

Найти все заказы, сделанные в последнем квартале 2007 года.

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

Найти заказы, стоимости которых попадают в различные диапазоны.

Инвертированная версия проверки на принадлежность диапазону ( NOT between ) позволяет выбрать значения, которые лежат за пределами диапа­зона, как в следующем примере.

Вывести список служащих, фактические объемы продаж которых не попадают в диапазон от 80 до 720 процентов плана.

Однако прежде чем полагаться на эти правила, неплохо было бы поэкспери­ментировать со своей СУБД.

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

полностью эквивалентна сравнению

Тем не менее проверка BETWEEN является более простым способом выразить ус­ловие отбора в терминах диапазона значений.

Проверка наличия во множестве (IN)

Еще одним распространенным условием отбора является проверка на наличие во множестве (in), схематически изображенная на рис. 8. В этом случае выполня­ется проверка, соответствует ли значение какому-либо элементу заданного списка. Ниже приведен ряд запросов с использованием проверки наличия во множестве.

Рис. 8. Синтаксическая диаграмма проверки наличия во множестве (IN)

Вывести список служащих, которые работают в Нью-Йорке, Атланте или Денвере.

Найти все заказы, сделанные в пятницы в январе 2008 года.

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

полностью эквивалентно условию

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

В стандарте ANSI/ISO не определено максимальное количество элементов множе­ства, и в большинстве СУБД явный верхний предел не задан. По соображениям переносимости, лучше избегать множеств, содержащих один элемент.

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

Проверка на соответствие шаблону (LIKE)

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

Показать лимит кредита для Smithson Corp.

Однако очень легко можно забыть, какое именно название носит интересую­щая нас компания: «Smith», «Smithson» или «Smithsonian». Проверка на соответст­вие шаблону позволяет выбрать из базы данных строки на основе частичного соот­ветствия имени клиента.

Проверка на соответствие шаблону (оператор LIKE ), схематически изображен­ная на рис. 9, позволяет определить, соответствует ли значение данных в столбце некоторому шаблону. Шаблон представляет собой строку, в которую может вхо­дить один или несколько подстановочных символов. Эти символы интерпретируют­ся особым образом.

Рис. 9. Синтаксическая диаграмма проверки на соответствие шаблону (LIKE)

Подстановочные знаки

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

Оператор like указывает SQL, что необходимо сравнивать содержимое столб­ца NAME с шаблоном «Smith% Corp.». Этому шаблону соответствуют все перечис­ленные ниже имена.

А вот эти имена данному шаблону не соответствуют.

Подстановочный знак _ (символ подчеркивания) совпадает с любым отдельным символом. Например, если вы уверены, что название компании либо «Smithson», либо «Smithsen», то можете воспользоваться следующим запросом.

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

Smithson Corp.

Smithsen Corp.

Smithsun Corp.

А вот ни одно из следующих ему соответствовать не будет.

Подстановочные знаки можно помещать в любое место строки шаблона, и в одной строке может содержаться несколько подстановочных знаков. Следующий запрос допускает как написание «Smithson» и «Smithsen», так и любое другое окончание названия компании, включая «Corp.», «Inc.» или какое-то другое.

Управляющие символы *

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

В стандарте ANSI/ISO определен способ проверки наличия в строке литералов, использующихся в качестве подстановочных знаков. Для этого применяются управляющие символы. Когда в шаблоне встречается такой символ, то символ, сле­дующий непосредственно за ним, считается не подстановочным знаком, а литера­лом. Непосредственно за управляющим символом может следовать либо один из двух подстановочных символов, либо сам управляющий символ, поскольку он также приобретает в шаблоне особое значение.

Найти товары, коды которых начинаются с четырех букв «A%ВС».

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

Проверка на равенство NULL (IS NULL)

Рис. 10. Синтаксическая диаграмма проверки на равенство null (is null)

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

Найти служащего, который еще не закреплен за офисом.

Вывести список служащих, которые уже закреплены за офисами.

Может показаться странным, что нельзя проверить значение на равенство NULL с помощью операции сравнения, например:

Ключевое слово NULL здесь нельзя использовать, поскольку на самом деле это не настоящее значение; это просто свидетельство того, что значение неизвестно. Даже если бы сравнение

было возможно, правила обработки значений NULL в сравнениях привели бы к то­му, что оно вело бы себя не так, как ожидается. Если бы СУБД обнаружила строку, в которой столбец REP_OFFICE содержит значение null, выполнилась бы сле­дующая проверка.

Составные условия отбора (AND, OR и NOT)

Рис. 11. Синтаксическая диаграмма предложения WHERE

Оператор OR используется для объединения двух условий отбора, из которых или одно, или другое (или оба) должно быть истинным.

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

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

Таблица 1. Таблица истинности оператора and

Источник

Правила именование объектов SQL: таблицы и столбцы

У каждого объекта в базе данных есть уникальное имя. Имена используются в инструкциях SQL и указывают, над каким объектом базы данных должно быть выполнено действие. Фундаментальными именованными объектами в реляцион­ной базе данных являются имена таблиц, столбцов и пользователей; правила их именования были определены еще в стандарте SQL1. В последующих версиях стандарта этот список был значительно расширен и теперь включает схемы (коллекции таблиц), ограничения (ограничительные условия, накладываемые на содержимое таблиц и их отношения), домены (допустимые наборы значений, ко­торые могут быть занесены в столбец) и ряд других типов объектов. Во многих СУБД существуют дополнительные виды именованных объектов, например хра­нимые процедуры, отношения «первичный ключ-внешний ключ», формы для ввода данных и схемы репликации данных.

В соответствии с первоначальным стандартом ANSI/ISO, имена в SQL должны содержать от 1 до 18 символов, начинаться с буквы и не могут содержать пробельные символы или специальные символы пунктуации. В стандарте SQL2 максимальное число символов в имени увеличено до 127 (дословно в стандарте сказано «менее 128»), и это количество остается неизменным до последнего стандарта SQL:2006. На практике поддержка имен в различных СУБД реализована по-разному. Чаще всего приходится сталкиваться с ограничениями на имена, связанные с другим программ­ным обеспечением вне базы данных (например, имена пользователей, которые могут совпадать с именами, используемыми операционной системой). Различные продук­ты отличаются и по отношению к применению в именах специальных символов. С точки зрения переносимости, лучше воздержаться от длинных имен и не приме­нять в них никаких специальных символов, за исключением символа подчеркива­ния, который используется для разделения слов в именах SQL.

Имена таблиц

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

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

Стандарт ANSI/ISO SQL еще больше обобщает понятие квалифицированного имени таблицы. Он разрешает создавать именованное множество таблиц, назы­ваемое схемой. Вы можете обращаться к таблице определенной схемы с использо­ванием квалифицированного имени. Например, обращение к таблице BIRTHDAYS в схеме employee_info имеет следующий вид.

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

Имена столбцов

Если в SQL-инструкции указано имя столбца, обычно SQL сам в состоянии оп­ределить, в какой из указанных в этой же инструкции таблиц содержится данный столбец. Однако если в инструкцию требуется включить два столбца из различных таблиц, но с одинаковыми именами, необходимо указать квалифицированные имена столбцов, которые однозначно определяют их местонахождение. Такое квалифи­цированное имя столбца состоит из имени таблицы, содержащей столбец, и имени столбца, разделенных точкой. Например, полное имя столбца SALES из таблицы SALESREPS имеет такой вид:

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

Источник

Использование в запросе нескольких источников записей стр. 2

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

model maker 1232 A 1260 E

Иногда в предложении FROM требуется указать одну и ту же таблицу несколько раз. В этом случае обязательным является переименование.

Вывести пары моделей, имеющих одинаковые цены:

Здесь условие a.model DISTINCT применяется для того, чтобы исключить одинаковые строки, поскольку в таблице PC имеются модели с одинаковыми номерами по одной и той же цене. В результате получим следующую таблицу:

model_1 model_2 1232 1233 1232 1260

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

Обратите внимание, что в этом случае в других предложениях оператора SELECT уже нельзя использовать квалификатор Product, поскольку таблица Product уже не используется. Вместо него используется псевдоним Prod. Кроме того, ссылаться извне теперь можно только на те столбцы таблицы Product, которые перечислены в подзапросе.

Источник

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

Vovan_ST

ИТ специалист со стажем. Автор статьи. Профиль

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

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

Выдать для каждого офиса список городов, регионов и сумм, на которые был пере­выполнен/недовыполнен план по продажам.

Рис. 3. Выполнение запроса, содержащего вычисляемый столбец

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

Показать общую стоимость по каждому товару (показаны только 8 строк резуль­тирующего набора).

Что получится, если увеличить плановый объем продаж для каждого служащего на 3% от его фактического объема продаж?

Как было сказано в блоге «Основы SQL», во многих СУБД реализованы до­полнительные арифметические операции, операции над строками символов и встроенные функции, которые можно применять в выражениях SQL. Их также можно использовать в выражениях в списке возвращаемых столбцов, как в сле­дующем примере для DB2, в котором из даты извлекается значение месяца и года.

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

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

Список объемов продаж для каждого города.

Источник

Тема 3. Язык SQL. Формирование запросов к базе данных

Изучить методы написания сложных SQL-запросов и способы их реализации на сервере MS SQL Server 2000.

Оглавление

Задания

1. Агрегатные функции в языке SQL

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

ФункцияРезультатCOUNTКоличество строк или непустых значений полей, которые выбрал запрос в каждой группеSUMСумма всех выбранных значений данного поля в группеAVGСреднее арифметическое всех выбранных значений данного поля в группеMINНаименьшее из всех выбранных значений данного поля в группеMAXНаибольшее из всех выбранных значений данного поля в группе

Запросы с группировкой имеют следующий синтаксис:

Select
FROM
Where
GROUP BY
Having

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

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

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

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

Например, при вычислении среднего балла за сессию мы получим следующий результат (рис. 2).

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

Select count(*) from groops

Мы получили результат: всего 5 групп.

Функция count(distinct ) действует здесь аналогично.

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

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

Задания для самостоятельной работы

Выполнить следующие запросы к учебной базе данных «Сессия»:

2. Принципы создания составных запросов

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

ПредикатСмыслИнтерпретацияINВходит в множествоИстинен, когда значение, стоящее слева в основном запросе, входит в множество значений, выводимое подчиненным запросомNOT INНе входит в множествоИстинен, когда значение, стоящее слева в основном запросе, не входит в множество значений, выводимое подчиненным запросомExistСуществуетИстинен, когда результат подчиненного запроса содержит хотя бы одну строкуNot ExisitНе существуетИстинен, когда результат подчиненного запроса не содержит ни одной строки

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

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

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

select Last_Name
from students
where N_zach not in (select N_zach
from sessia
Where mark = 2)

Встроенные запросы могут быть зависимыми и независимыми.

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

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

В этом случае общий текс запроса выглядит следующим образом:

select Last_Name
from students, Groops, uch_plan
where students.N_groop = Groops.N_groop
and groops.kod_spec = uch_plan.kod_spec
group by Last_Name, N_zach
Having count(distinct Discipline) = (select count(distinct Discipline)
from sessia
where mark > 2
and sessia.N_zach = students.N_zach)

Задания для самостоятельной работы (продолжение)

3. Зачетный SQL-тест в системе ВУОКСа

В разделе статистики постоянно ведется учет набранных вами баллов. Следите за этим показателем.

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

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

Источник