Формирование запросов средствами языка SQL

Оператор SQL состоит из зарезервированных слов и из слов, определяемых пользователем. Зарезервированные слова являются постоянной частью языка SQL, имеют фиксированное значение, их нельзя разбивать на части. Слова, определяемые пользователем, представляют собой имена различных объектов базы данных и записываются в соответствии с синтаксическими правилами.

Слова в операторе располагаются строго в определённой последовательности. Имена формируются из символов алфавита, заданного стандартом языка. Разрешено использовать строчные и прописные буквы латинского алфавита (AZ, a-z), цифры (0-9) и символ подчёркивания (_). Имя может иметь длину до 128 символов, должно начинаться с буквы и не может содержать пробелы.

Большинство элементов языка нечувствительны к регистру. Язык SQL имеет свободный формат, то есть там, где допустим один пробел между элементами, разрешено ставить любое количество пробелов и пустых строк. Это позволяет SQL-операторы и их фрагменты записывать с использованием отступов и выравнивания, что облегчает чтение и понимание запросов.

Точка с запятой является стандартным разделителем, но в некоторых реализациях (в частности, в компоненте Query) разделитель в конце команды необязателен.

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

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

Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект. Таблицы – на диске, представления – в оперативной памяти.

Хранимые процедуры представляют собой группу команд SQL, объединённых в один модуль. Такая группа команд компилируется и выполняется как единое целое.

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

 

Оператор SELECT

Общая форма команды SELECT

 

Общая форма оператора SELECT приводится в стандартах. В более простых случаях достаточно воспользоваться только некоторыми возможностями оператора SELECT:

SELECT [DISTINCT] список_выбираемых_полей

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

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

[GROUP BY спецификация_группировки

[HAVING условие_отбора_групп]]

[UNION другое_выражение_Select]

[ORDER BY спецификация_сортировки];

 

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

Простейшие конструкции языка SQL позволяют:

· назначать поля, которые должны быть выбраны;

· назначать к выборке все поля;

· управлять вертикальным и горизонтальным порядком выбираемых

· данных;

· подставлять собственные заголовки полей в результирующей таблице;

· производить вычисления в списке выбираемых элементов;

· использовать литералы в списке выбираемых элементов;

· ограничивать число возвращаемых строк;

· формировать сложные условия поиска;

· устранять одинаковые строки из результата.

 

 

Поля и предложение FROM

 

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

В предложении FROM перечисляются все объекты (один или несколько), из которых производится выборка данных. Каждая таблица или представление, которые упоминаются в запросе, должны быть перечислены в предложении FROM. В простейшем случае после слова FROM записывается имя таблицы, из которой извлекаются данные. Если требуется извлечение значений всех полей, то вместо списка полей можно указать символ *. Например, чтобы получить сведения из всех полей таблицы country, надо записать:

SELECT * FROM country

 

Для получения данных из определённых полей используется команда, в которой после слова SELECT перечислены только нужные поля:

SELECT Last_Name,First_Name,City,Country,Phone FROM custoly

 

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

SELECT First_Name,Last_Name,Phone,City,Country FROM custoly

 

Для уточнения объекта, которому принадлежит поле, перед именем поля указывается имя объекта. Задание составного имени имя_таблицы.имя_поля является обязательным при использовании нескольких таблиц или представлений, а также при использовании имён полей с пробелами. При задании полей, имена которых содержат пробел, надо использовать кавычки или апострофы.

Например, при выводе данных из таблицы biolife для полей Species Name и Length (cm) используются составные имена:

SELECT Category,Common_Name,biolife."Species Name", biolife."Length (cm)"

FROM biolife

 

SELECT Category,Common_Name,biolife.'Species Name', biolife.'Length (cm)'

FROM biolife

 

SELECT biolife.Category,biolife.Common_Name,

biolife.'Species Name',biolife.'Length (cm)'

FROM biolife

 

 

Литералы

 

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

апострофах.

SELECT LastName,"получает",Salary," в год" FROM employee

или

SELECT LastName,‘получает’,Salary,‘ в год’ FROM employee

 

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

 

 

Конкатенация

 

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

Для этого используется операция конкатенации, которая задаётся двумя вертикальными чёрточками (||).

SELECT FirstName||' '||LastName,HireDate FROM employee

 

Этот запрос выводит список сотрудников с указанием даты поступления на работу.

 

 

Использование квалификатора AS

 

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

 

SELECT VenueNo,Event_Name AS Name, Event_Description AS Description

FROM events

 

SELECT VenueNo,Event_Name AS Name,

Event_Date AS events.'Date',Event_Time AS events.'Time'

FROM events

 

В последнем запросе вместо имён полей Event_Name, Event_Date, Event_Time для столбцов используются названия Name, Date, Time. Поскольку идентификаторы Date и Time в таблицах формата Paradox используются для задания типов данных, то при формировании названий столбцов пришлось использовать составные имена.

 

 

Предложение WHERE

Ограничения на число выводимых строк

Число возвращаемых в результате запроса строк может быть ограничено путем использования предложения WHERE, содержащего условия отбора. Так как в языке SQL применяется трёхзначная логика, то условие отбора для отдельных строк может принимать значения true, false или unknown.

Значение unknown получается при сравнении значения null c любым другим значением, включая null. Запрос возвращает в качестве результата только те строки, для которых предикат имеет значение true. При формировании условия используются следующие операции: сравнения (=, <>, >, <, >=,<=), BETWEEN, IN, LIKE, IS NULL, EXIST, ANY, ALL, SOME.

 

Операции сравнения

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

 

Пример 2. Получить список сотрудников с именем Brown (Lee):

SELECT LastName, FirstName, Salary FROM employee

WHERE LastName='Brown'

 

SELECT LastName, FirstName, Salary FROM employee

WHERE LastName='Lee '

 

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

 

Пример 3. Получить список сотрудников с зарплатой меньше 27 000:

SELECT LastName, FirstName, Salary FROM employee

WHERE Salary<27000

 

Пример 4. Получить список фирм-заказчиков с указанием города и страны за исключением заказчиков из Канады:

SELECT Company,City,Country FROM customer

WHERE Country <>'Canada'

 

Пример 5. Получить список заказчиков из US с указанием названия фирмы, города и штата:

SELECT Company,City,State FROM customer

WHERE Country ='US'

 

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

 

 

Операция BETWEEN

 

Предикат BETWEEN задает диапазон значений, для которого выражение принимает значение true. Разрешено также использовать конструкцию NOT BETWEEN.

 

Пример 6. Получить список сотрудников, у которых зарплата лежит в диапазоне от 25 000

до 30 000:

SELECT LastName,FirstName,Salary FROM employee

WHERE Salary BETWEEN 25000 and 30000

 

Тот же запрос с использованием операторов сравнения будет выглядеть следующим образом:

SELECT LastName,FirstName,Salary FROM employee

WHERE Salary>=25000 and Salary<=30000

 

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

 

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

SELECT LastName,FirstName,Salary FROM employee

WHERE LastName BETWEEN "Nelson" AND "Osborne"

 

Рисунок 3 - Результат реализации операции BETWEEN в примере 7

 

Пример 8. Вывести список сотрудников, фамилии которых находятся между Nel и Osb:

SELECT LastName,FirstName,Salary FROM employee

WHERE LastName BETWEEN "Nel" AND "Osb"

 

В таблице базы данных employee значений Nel и Osb нет.

 

Рисунок 4 - Результат реализации операции BETWEEN в примере 8

 

Однако сотрудники с фамилиями, начинающимися с символов, для которых выполняется условие «больше или равно Nel» и «не более Osb», попадут в выборку. Фамилии, начинающиеся с символов O, Os, Osb, попадают в заданный диапазон, а Osborne – нет.

Предикат BETWEEN с отрицанием NOT (NOT BETWEEN) позволяет получить записи, у которых значение заданного поля лежит вне введённого диапазона (меньше нижней границы и больше верхней границы).

 

Пример 9. Найти рыб с длиной меньше 10 и больше 80 дюймов (маленьких и очень больших):

SELECT Category,Common_Name,Length_In FROM biolife

WHERE Length_In NOT BETWEEN 10 AND 80

 

Операция IN

 

Предикат IN проверяет, совпадает ли заданное значение (например, значение столбца или функция от него) с одним из перечисленных в списке. Элементы списка записываются через запятую в круглых скобках. Если проверяемое значение равно какому-либо элементу в списке, то предикат принимает значение true. Разрешено использовать конструкцию NOT IN.

 

Пример 10. Вывести список компаний из городов Santa Maria, San Jose, Downey:

SELECT Company,City FROM customer

WHERE City in ('Santa Maria','San Jose','Downey')

 

Пример 11. Вывести заказы и даты их оплаты, у которых средством оплаты была не Visa и не AmEx:

SELECT OrderNo,SaleDate,PaymentMethod FROM orders

WHERE PaymentMethod not in ('Visa','AmEx')

 

Предикат LIKE

 

Предикат LIKE используется только с символьными данными. Он проверяет, соответствует ли данное символьное значение указанной подстроке с указанной маской. Предусмотрена также конструкция NOT LIKE.

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

% – замещает любое количество символов (в том числе и 0),

_ – замещает только один символ.

 

Пример 12. Получить список сотрудников, фамилии которых начинаются с буквы F:

SELECT FirstName,LastName FROM employee

WHERE LastName LIKE "F%"

 

Пример 13. Получить список сотрудников, у которых имя заканчивается на «er»:

SELECT FirstName,LastName FROM employee

WHERE FirstName LIKE "%er"

 

Предикат IS NULL

 

В SQL-запросах NULL означает, что значение столбца неизвестно. Условия поиска, в которых значение столбца сравнивается с NULL, всегда принимают значение unknown и, соответственно, приводят к ошибке. Таким образом, в запросах нет смысла применять выражения вида

WHERE имя_поля=NULL

 

Если требуется определить, имеет ли поле значение, используется предикат IS NULL. Условие, содержащее IS NULL, принимает значение true только тогда, когда значение поля или выражения имеет значение NULL (пусто, не определено). Разрешено также использовать конструкцию IS NOT NULL, которая означает не пусто, имеет какое-либо значение. Предикат IS NULL возвращает только значения true или false.

Логические операции

 

Логические операции AND, OR, NOT позволяют сформировать сложные условия отбора записей. Если в одном выражении используется несколько логических операций, то они выполняются с учётом приоритета: сначала выполняется отрицание NOT, затем AND (логическое И), только после этого OR (логическое ИЛИ). Для изменения порядка выполнения операций разрешается использовать скобки.

 

Пример 14. По таблице country (рисунок 5) было выполнено два запроса. Проанализировать результаты.

 

Рисунок 5 - Исходная таблица

 

 

Запрос 1.

SELECT * FROM country

WHERE Area<100000 or Population<3000000 and Continent='South America'

 

Рисунок 6 - Результат запроса 1

 

Запрос 2.

SELECT * FROM country

WHERE (Area<100000 or Population<3000000) and Continent='South America'

 

Рисунок 7 - Результат запроса 2

 

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

 

 

Работа с датами

 

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

”October 27,2008”

”27-OCT-2008”

”10-27-08”

”10/27/08”

”27.10.08”

 

Пример 15. Вывести список сотрудников, принятых на работу после заданной даты. Использовать разные варианты форматов даты:

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'1.01.94'

 

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'1/01/90'

 

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'27-Oct-1992'

 

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

 

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

 

Рисунок 8 - Дата задана строкой '12/30/93'

 

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

 

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'12/30/93'

 

Дата 12/30/93 означает: 12 – месяц, 30 –день, 93 – год.

 

Рисунок 9 - Дата задана строкой '31.12.1992'

 

Привычная форма даты 31.12.1992 означает: 31–день, 12–месяц, 1992–год. Значения дат можно сравнивать друг с другом, вычитать одну из другой.

 

Пример 17. Получить список служащих, проработавших на предприятии к 1/01/2000 более 8 лет:

SELECT FirstName,LastName,HireDate FROM employee

WHERE '1/01/2000'-HireDate > 8*365+2

 

Пример 18. Получить список сотрудников, поступивших на работу до 1.01.89 и после 31.12.93, то есть раньше 1989 г. и позже 1993 г.:

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate NOT BETWEEN "1-JAN-1989" AND "31-DEC-1993"

 

Кроме абсолютных дат некоторые реализации языка SQL (например, InterBase) позволяют оперировать относительными значениями: yesterday (вчера), today (сегодня), now (сейчас, включая время), tomorrow (завтра).

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