SQL в ДОМИНО

Данная глава не является учебником по реляционным БД и SQL. Глава предназначена для новичков в области баз данных, в ней на множестве примеров показаны основы SQL и применение SQL в ДОМИНО. Желающие ознакомится с SQL в более полном объеме могут прочитать книгу Мартина Грабера ‘Введение в SQL’, откуда и были взяты примеры и объяснения.

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

Затем следует описание на языке скриптов в Домино. Описание включает картинку и комментарий к ней.

Введение

SQL (Structured Query Language) – структурный язык запросов – предназначен для работы с реляционными базами данных.

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

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

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

Реляционная база данных редко состоит из одной таблицы. При создании нескольких таблиц со связанной информацией можно выполнять более сложные и мощные операции над данными. Таблицы связываются по столбцам. Рассмотрим пример связанных таблиц.

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

Таблица Продавцы

продавец	имя_продавца	город	вознаграждение
1001	Пил	Лондон	0,12
1002	Серес	Сан Хосе	0,13
1004	Мотика	Лондон	0,11
1007	Рифкин	Барселона	0,15
1003	Аксельрод	Нью Йорк	0,10

• Продавец  - уникальный номер, назначенный каждому продавцу.
• Имя_продавца – Фамилия продавца.
• Город – Место расположения продавца.
• Вознаграждение – Комиссионные продавца.

Таблица Покупатели

покупатель	имя_покупателя	город	рейтинг	продавец
2001	Хофман	Лондон	100	1001
2002	Джованни	Рим	200	1003
2003	Лу	Сан Хосе	200	1002
2004	Грасс	Берлин	300	1002
2006	Клеменс	Лондон	100	1001
2008	Гиснерос	Сан Хосе	300	1007
2007	Перейра	Рим	100	1004

• Покупатель  - уникальный номер, назначенный каждому покупателю.
• Имя_покупателя – Фамилия покупателя.
• Город – Место расположения покупателя.
• Рейтинг – Цифровой код, определяющий уровень предпочтения данного покупателя. Чем больше число, тем больше предпочтение.
• Продавец – Номер продавца, назначенного данному покупателю.

Таблица Заказы

заказ	количество	дата	покупатель	продавец
3001	18,69	10.03.1990	2008	1007
3003	767,19	10.03.1990	2001	1001
3002	1900,10	10.03.1990	2007	1004
3005	5160,45	10.03.1990	2003	1002
3006	1098,16	10.03.1990	2008	1007
3009	1713,23	10.04.1990	2002	1003
3007	75,75	10.04.1990	2004	1002
3008	4723,00	10.05.1990	2006	1001
3010	1309,95	10.06.1990	2004	1002
3011	9891,88	10.06.1990	2006	1001

• Заказ – Уникальный номер, присвоенной данной покупке.
• Количество – Закупленное количество.
• Дата – Дата покупки.
• Покупатель – Номер покупателя, сделавшего покупку.
• Продавец – Номер продавца, обслужившего покупателя.

Три эти таблицы образуют реляционную базу данных. Покажем на её основе основные понятия, связанные с применением SQL.

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

Вот как эти таблицы могут быть описаны в Домино.

Использование SQL для выборки данных из таблиц. Предложение SELECT

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

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

SELECT * | { [DISTINCT | ALL] <список полей> .,…}

            FROM {<имя таблицы> [<алиас>]}…     

                        [WHERE <условие>]

                        [GROUP BY {<имя столбца> <целое>} .,…]

                        [HAVING < условие >]

                        [ORDER BY {<имя столбца> | <целое>} .,…]

[{UNION [ALL]

SELECT * | { [DISTINCT | ALL] <список полей> .,…}

                        FROM {<имя таблицы> [<алиас>]}…     

                                   [WHERE < условие >]

                                   [GROUP BY {<имя столбца> <целое>} .,…]

                                   [HAVING < условие >]

                                   [ORDER BY {<имя столбца> | <целое>} .,…]

}]…;

• Cписок полей – Выражение, включающее имена столбцов или состоящее из них.
• Имя таблицы – Имя или синоним таблицы или представления.
• Алиас – Временный синоним имени таблицы, используемый только в этой команде.
• Условие – Условие, которое может быть истинным или ложным для каждого столбца или комбинации столбцов из таблицы, определенных в предложении FROM.
• Имя столбца – Имя столбца таблицы.
• Целое – Целое число. В этом случае оно определяет значение из списка выбираемых полей в предложении SELECT, указывая его расположение в этом предложении.

Символы:

| - Все то, что предшествует данному символу, можно заменить тем, что следует за ним. Аналогично ИЛИ.

{} – Все то, что заключено в фигурные скобки, рассматривается как единое целое для применения других символов (в том числе для символа |).

[] – Все то, что заключено в квадратные скобки, является необязательным.

.,… - Все то, что предшествует этим символам, может повторяться произвольное число раз; каждое отдельное вхождение отделяется запятой.

Выбор чего-либо простейшим способом

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

SELECT продавец, имя_продавца, город, вознаграждение

FROM Продавцы

Команда просто выводит все данные из таблицы. Получаем:

Продавец	Имя-Продавца	Город	Вознаграждение
1001	Пил	Лондон	0,12
1002	Серес	Сан Хосе	0,13
1004	Мотика	Лондон	0,11
1007	Рифкин	Барселона	0,15
1003	Аксельрод	Нью Йорк	0,10

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

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

SELECT *

FROM Продавцы

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

Вот как этот запрос выглядит в Домино.

В Домино аналога символа звездочка нет.

Просмотр только определенных столбцов таблицы

Существует возможность указать только требуемые столбцы. Например, по запросу:

SELECT имя_продавца, вознаграждение

FROM Продавцы

Получаются следующие выходные данные:

Имя_продавца	Вознаграждение
Пил	0,12
Серес	0,13
Мотика	0,11
Рифкин	0,15
Аксельрод	0,10

В Домино запрос описывается вот так. В списке параметров описываются только необходимые поля.

Перестановка столбцов

Столбцы таблицы упорядочены, но это не означает, что их можно извлекать только в этом порядке. Звездочка в команде SELECT извлечет столбцы в соответствии с их порядком, но если указать столбцы раздельно, то они выстраиваются в любом желаемом порядке.

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

SELECT город, имя_продавца, вознаграждение, продавец 

FROM Продавцы

Город	Имя_Продавца	Вознаграждение	Продавец
Лондон	Пил	0,12	1001
Сан Хосе	Серес	0,13	1002
Лондон	Мотика	0,11	1004
Барселона	Рифкин	0,15	1007
Нью Йорк	Аксельрод	0,10	1003

В Домино тоже можно поменять столбцы местами, хотя это и не имеет смысла.

Устранение избыточных данных. Аргумент DISTINCT

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

SELECT Продавец

FROM Заказы

Получаем:

Продавец
1007
1001
1004
1002
1007
1003
1002
1001
1002
1001

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

SELECT DISTINCT Продавец

FROM Заказы

Продавец

1001

1002

1003

1004

1007

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

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

Альтернативой DISTINCT является ALL. Этот аргумент имеет противоположное действие. Если в предложении SELECT ни задан ни один из этих аргументов, то считается, что указан ALL.

В Домино имеется соответствующий атрибут у запроса.

Ограничение выборки. Предложение WHERE

С помощью предложения WHERE задается условие (критерий) для определения тех строк, которые следует включить в состав выходных данных. Условие может быть либо истинным, либо ложным для каждой строки таблицы. Команда извлекает только те строки таблицы, для которых условие имеет истинное значение.

Требуется узнать имена всех продавцов из Лондона.

SELECT имя_продавца, город

FROM Продавцы

WHERE город=Лондон

Имя_продавца	Город
Пил	Лондон
Мотика	Лондон

В Домино у запроса тоже имеется конструкция WHERE.

Использование булевых операторов внутри WHERE

Булевы операторы связывают одно или несколько значений ‘истина/ложь’ и в результате получают единственное значение ‘истина/ложь’. Стандартные булевы операторы – это AND, OR, NOT.

• AND – возвращает истинное значение, если оба операнда имеют истинное значение.
• OR – возвращает истинное значение, если хотя бы один операнд имеет истинное значение.
• NOT – изменяет истинное значение на ложное или ложное значение на истинное.

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

Требуется выбрать всех покупателей из Сан Хосе, чей рейтинг превышает 200.

SELECT *

FROM Покупатели

WHERE город=Сан Хосе AND рейтинг>200

Получаем:

покупатель	имя_покупателя	город	рейтинг	продавец
2008	Гиснерос	Сан Хосе	300	1007

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

Если требуется выбрать покупателей, которые либо проживают в Сан Хосе, либо имеют рейтинг превышающий 200, то команда выглядит следующим образом:

SELECT *

FROM Покупатели

WHERE город=Сан Хосе OR рейтинг>200

Результат:

покупатель	имя_покупателя	город	рейтинг	продавец
2003	Лу	Сан Хосе	200	1002
2004	Грасс	Берлин	300	1002
2008	Гиснерос	Сан Хосе	300	1007

В Домино изменим операцию:

Тот же запрос с использованием NOT. Оператор NOT должен предшествовать булеву выражению, значение которого он должен изменить.

SELECT *

FROM Покупатели

WHERE город=Сан Хосе OR NOT рейтинг>200

Результат:

покупатель	имя_покупателя	город	рейтинг	продавец
2001	Хофман	Лондон	100	1001
2002	Джованни	Рим	200	1003
2003	Лу	Сан Хосе	200	1002
2006	Клеменс	Лондон	100	1001
2008	Гиснерос	Сан Хосе	300	1007
2007	Перейра	Рим	100	1004

Все записи, за исключением Грасс, были выбраны. Грасс не находится в Сан Хосе и его рейтинг превышает 200, таким образом он не удовлетворяет ни одному из условий. Каждая из других строк удовлетворяет либо первому, либо другому условию.

В Домино для каждой операции надо использовать аналог скобок.

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

SELECT * FROM Покупатели WHERE NOT (город=Сан Хосе OR рейтинг>200)

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

покупатель	имя_покупателя	город	рейтинг	продавец
2001	Хофман	Лондон	100	1001
2002	Джованни	Рим	200	1003
2006	Клеменс	Лондон	100	1001
2007	Перейра	Рим	100	1004

В Домино тоже добавим дополнительные скобки:

Оператор IN внутри WHERE

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

Если нужно найти всех продавцов из Барселоны или из Лондона, то следует составить следующий запрос:

SELECT *

FROM Продавцы

WHERE город=Барселона OR город=Лондон

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

SELECT *

FROM Продавцы

WHERE город IN (‘Барселона’, ‘Лондон’)

продавец	имя_продавца	город	вознаграждение
1001	Пил	Лондон	0,12
1004	Мотика	Лондон	0,11
1007	Рифкин	Барселона	0,15

В Домино тоже имеется конструкция IN внутри WHERE, но она применяется в тех случаях, когда множество допустимых значений определяется вложенным запросом. В данном же случае достаточно применить выражение.

Оператор BETWEEN внутри WHERE

Оператор BETWEEN сходен с IN. Вместо перечисления элементов множества, как это делается в IN, BETWEEN задает границы, в которые должно попасть значение, чтобы условие было истинным. Используется ключевое слово BETWEEN, за которым следует начальное значение, ключевое слово AND и конечное значение.

Следующий запрос позволит извлечь всех продавцов, комиссионные которых имеют величину в диапазоне от 0,10 до 0,12.

SELECT *

FROM Продавцы

WHERE вознаграждение

BETWEEN 0,10 AND 0,12

продавец	имя_продавца	город	вознаграждение
1001	Пил	Лондон	0,12
1004	Мотика	Лондон	0,11
1003	Аксельрод	Нью Йорк	0,10

В Домино вместо BETWEEN используются выражения.

Оператор LIKE внутри WHERE

LIKE применим только к строчным полям, поскольку используется для поиска подстрок. Другими словами, оператор LIKE осуществляет просмотр строки для выяснения: входит ли заданная подстрока в указанное поле.

Для задания подстроки применяются специальные символы:

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

Можно найти покупателей, фамилии которых начинаются на Г.

SELECT *

FROM Покупатели

WHERE имя_покупателя

LIKE ‘Г%’

покупатель	имя_покупателя	город	рейтинг	продавец
2004	Грасс	Берлин	300	1002
2006	Клеменс	Лондон	100	1001
2008	Гиснерос	Сан Хосе	300	1007

В Домино вместо LIKE используются выражения и функция SQL сервера.

В данном примере функция возвращает номер позиции, в которой находится буква Г.

Оператор IS NULL внутри WHERE

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

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

Для нахождения всех записей со значениями NULL используется специальный оператор.

Чаще требуется не искать NULL-значения, а, напротив, необходимо исключить их из результата. В комбинации с NOT это выглядит следующим образом:

SELECT *

FROM Покупатели

WHERE город IS NOT NULL

Или

SELECT *

FROM Покупатели

WHERE NOT город IS NULL

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

В Домино такой запрос легко описывается с помощью выражения.

Функции агрегирования

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

• COUNT – определяет количество строк или значений поля, выбранных посредством запроса и не являющихся NULL-значениями
• COUNT(*) – определяет количество строк, отобранных по запросу

• SUM – вычисляет арифметическую сумму всех выбранных значений данного поля
• AVG – вычисляет среднее значение для всех выбранных значений данного поля
• MAX – вычисляет наибольшее из всех выбранных значений данного поля
• MIN – вычисляет наименьшее из всех выбранных значений данного поля

В Домино описание функций агрегирования находится в библиотеке ‘!Примитивы скриптов’, раздел ‘Структура базы данных’, раздел ‘Функции’.

Предложение GROUP BY

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

Например, требуется найти наибольший заказ из тех, что получил каждый из продавцов. Можно сделать отдельный запрос на каждого продавца, выбрав MAX(количество) для таблицы ‘Заказы’ для каждого значения поля ‘Продавец’. GROUP BY позволяет объединить все это в одной команде:

SELECT продавец, MAX(количество)

FROM Заказы

GROUP BY продавец

Результат:

Продавец	MAX(количество)
1001	9891,88
1002	5160,45
1003	1713,23
1004	1900,10
1007	1098,16

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

В Домино для описания запроса имеется конструкция GROUP BY.

Функция MAX применена в выражении, которое находится в списке выходных параметров запроса.

Можно также применять GROUP BY с набором полей. Если необходимо увидеть наибольший заказ, сделанный каждому продавцу на каждую дату, то следует сгруппировать данные таблицы ‘Заказы’ по дате внутри одного и того же поля ‘Продавец’ и применить функцию MAX к каждой группе.

SELECT продавец, дата, MAX(количество)

FROM Заказы

GROUP BY продавец, дата

Результат:

Продавец	Дата	MAX(количество)
1001	10.03.1990	767,19
1001	10.05.1990	4723,00
1001	10.06.1990	9891,88
1002	10.03.1990	5160,45
1002	10.04.1990	75,75
1002	10.06.1990	1309,95
1003	10.04.1990	1713,23
1004	10.03.1990	1900,10
1007	10.03.1990	1098,16

Даты, когда продавец не получал заказов, в результате не представлены.

Вот как выглядит этот запрос в Домино.

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

Обращаясь к предыдущему примеру, можно предположить, что интересуют только покупки, превышающие 3000. Однако для данного случая использовать агрегатные функции в предложении WHERE нельзя, поскольку условие WHERE применяется к одной строке, а агрегатные функции рассчитываются по нескольким строкам. Т.е. следующий запрос записан неверно:

SELECT продавец, дата, MAX(количество)

FROM Заказы

WHERE MAX(количество) > 3000

GROUP BY продавец, дата

Для таких случаев имеется предложение HAVING. Оно определяет критерий, согласно которому определенные группы исключаются из числа выходных данных, так же, как предложение WHERE делает это для отдельных строк.

Правильная команда выглядит так:

SELECT продавец, дата, MAX(количество)

FROM Заказы

GROUP BY продавец, дата

HAVING MAX(количество) > 3000

Результат:

Продавец	Дата	MAX(количество)
1001	10.05.1990	4723,00
1001	10.06.1990	9891,88
1002	10.03.1990	5160,45

В Домино применение конструкции HAVING выглядит следующим образом:

Аргументы HAVING подчиняются тем же правилам, что и аргументы SELECT в команде, использующей GROUP BY, и должны иметь единственное значение для каждой выходной группы.

Следующая команда неверна:

SELECT продавец, MAX(количество)

FROM Заказы

GROUP BY продавец

HAVING дата=10.03.1900

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

Вот правильный вариант этого запроса:

SELECT продавец, MAX(количество)

FROM Заказы

WHERE дата=10.03.1900

GROUP BY продавец

Смысл этого запроса – наибольшие заказы на 10.03.1990.

Посмотрим как этот запрос выглядит в Домино.

Строки и выражения в списке столбцов предложения SELECT

SQL позволяет вносить простые выражения и константы в список выбранных полей.

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

SELECT продавец, имя_продавца, город, вознаграждение * 100

FROM Продавцы

Получим:

Продавец	Имя_продавца	Город	Вознаграждение*100
1001	Пил	Лондон	12
1002	Серес	Сан Хосе	13
1004	Мотика	Лондон	11
1007	Рифкин	Барселона	15
1003	Аксельрод	Нью Йорк	10

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

Упорядочение записей

Таблицы являются неупорядоченными множествами, и отобранные из них данные необязательно представляются в какой-либо определенной последовательности. Для упорядочивания результата запроса применяется предложение ORDER BY. При наличии этого предложения записи упорядочиваются в соответствии со значениями одного или нескольких выбранных столбцов. Множество столбцов упорядочиваются один внутри другого, и можно задать возрастающую (ASC) или убывающую (DESC) последовательность сортировки для каждого из столбцов. По умолчанию принята сортировка по возрастанию значений.

Таблица заказов, упорядоченная по номеру заказа, выглядит так:

SELECT *

FROM Заказы

ORDER BY заказ DESC

заказ	количество	дата	покупатель	продавец
3001	18,69	10.03.1990	2008	1007
3006	1098,16	10.03.1990	2008	1007
3002	1900,10	10.03.1990	2007	1004
3008	4723,00	10.05.1990	2006	1001
3011	9891,88	10.06.1990	2006	1001
3007	75,75	10.04.1990	2004	1002
3010	1309,95	10.06.1990	2004	1002
3005	5160,45	10.03.1990	2003	1002
3009	1713,23	10.04.1990	2002	1003
3003	767,19	10.03.1990	2001	1001

В Домино для упорядочивания тоже применяется конструкция ORDER BY.

Внутри уже произведенного упорядочения по полю ‘Заказ’ можно упорядочить таблицу и по другому столбцу, например ‘Количество’.

SELECT *

FROM Заказы

ORDER BY заказ DESC, количество DESC

заказ	количество	дата	покупатель	продавец
3006	1098,16	10.03.1990	2008	1007
3001	18,69	10.03.1990	2008	1007
3002	1900,10	10.03.1990	2007	1004
3011	9891,88	10.06.1990	2006	1001
3008	4723,00	10.05.1990	2006	1001
3010	1309,95	10.06.1990	2004	1002
3007	75,75	10.04.1990	2004	1002
3005	5160,45	10.03.1990	2003	1002
3009	1713,23	10.04.1990	2002	1003
3003	767,19	10.03.1990	2001	1001

В Домино добавляем еще одну строку в конструкцию ORDER BY.

Предложение ORDER BY можно использовать совместно с предложение GROUP BY для упорядочения групп. Предложение ORDER BY всегда выполняется последним.

SELECT продавец, дата, MAX(количество)

FROM Заказы

GROUP BY продавец, дата

ORDER BY продавец

Результат:

Продавец	Дата	MAX(количество)
1001	10.03.1990	767,19
1001	10.05.1990	4723,00
1001	10.06.1990	9891,88
1002	10.03.1990	5160,45
1002	10.04.1990	75,75
1002	10.06.1990	1309,95
1003	10.04.1990	1713,23
1004	10.03.1990	1900,10
1007	10.03.1990	1098,16

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

В Домино этот запрос выглядит так:

Соединение таблиц

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

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

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

SELECT Покупатели.имя_покупателя, Продавцы.имя_продавца, Продавцы.город

FROM Продавцы, Покупатели

WHERE Продавцы.город = Покупатели.город

Результат:

Имя_покупателя	Имя_продавца	Город
Хофман	Пил	Лондон
Клеменс	Пил	Лондон
Лу	Серес	Сан Хосе
Гиснерос	Серес	Сан Хосе
Хофман	Мотика	Лондон
Клеменс	Мотика	Лондон

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

В Домино соединение двух таблиц выглядит вот таким образом.

Можно посмотреть какие покупатели обслуживаются какими продавцами.

SELECT Покупатели.имя_покупателя, Продавцы.имя_продавца

FROM Покупатели, Продавцы

WHERE Продавцы.продавец = Покупатели.продавец

Имя_Покупателя	Имя_продавца
Хофман	Пил
Джованни	Аксельрод
Лу	Серес
Грасс	Серес
Клеменс	Пил
Гиснерос	Рифкин
Перейра	Мотика

 В Домино такой запрос похож на предыдущий.

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

SELECT заказ, имя_покупателя, покупатель, продавец

FROM Продавцы, Покупатели, Заказы

WHERE Продавцы.город <> Покупатели.город

AND Заказы.покупатель = Покупатели.покупатель

AND Заказы.продавец = Продавцы.продавец

Заказ	Имя_покупателя	Покупатель	Продавец
3001	Гиснерос	2008	1007
3002	Перейра	2007	1004
3006	Гиснерос	2008	1007
3009	Джованни	2002	1003
3007	Грасс	2004	1002
3010	Грасс	2004	1002

Вот этот запрос в Домино.

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

В ДОМИНО алиасы реализуются автоматически, т.к. адресация к полям таблиц (подзапросов) осуществляется с помощью уточняющего параметра к ссылке на таблицу из  раздела 'FROM (Таблицы)'.

Рассмотрим пример поиска всех пар покупателей, имеющих одинаковый рейтинг.

SELECT Первая.покупатель, Вторая.покупатель, Первая.рейтинг

FROM Покупатели Первая, Покупатели Вторая

WHERE Первая.рейтинг = Вторая.рейтинг 

Покупатель	Покупатель	Рейтинг
Джованни	Джованни	200
Джованни	Лу	200
Лу	Джованни	200
Лу	Лу	200
Грасс	Грасс	300
Грасс	Гиснерос	300
Клеменс	Хофман	100
Клеменс	Клеменс	100
Клеменс	Перейра	100
Гиснерос	Грасс	300
Гиснерос	Гиснерос	300
Перейра	Хофман	100
Перейра	Клеменс	100
Перейра	Перейра	100
Хофман	Клеменс	100
Хофман	Хофман	100
Хофман	Перейра	100

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

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

SELECT Первая.покупатель, Вторая.покупатель, Первая.рейтинг

FROM Покупатели Первая, Покупатели Вторая

WHERE Первая.рейтинг = Вторая.рейтинг

            AND Первая. покупатель < Вторая. покупатель

Покупатель	Покупатель	Рейтинг
Гиснерос	Грасс	300
Джованни	Лу	200
Клеменс	Хофман	100
Клеменс	Перейра	100
Перейра	Хофман	100

Вот описание этого запроса в Домино.

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

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

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

Например

• WHERE Продавцы.город (+) = Покупатели.город означает, что строки из таблицы Продавцы с незаполненным полем город также удовлетворяют условию. Этот случай называется левое внешнее соединение (left outer join).
• WHERE Продавцы.город  = Покупатели.город (+) – пример правого внешнего соединения (right outer join). В этом случае условию удовлетворяют строки с незаполненным полем город из таблицы Покупатели.
• WHERE Продавцы.город (+)  = Покупатели.город (+) Комбинация левого и правого внешних соединений называется полным внешним соединением (full outer join).

В ДОМИНО для указания внешнего соединения используется специальный атрибут 'Результат (Внешнее соединение таблиц - допустимо отсутствие значения'. Различать правое и левое внешние соединения в ДОМИНО не имеет смысла, а полное внешнее соединение не поддерживается.

Вложенные запросы

SQL позволяет вкладывать запросы друг в друга.

В Домино возможно применение вложенных запросов в выражениях (в этом случае запрос должен возвращать только одно значение), в предложении FROM (в качестве источника данных) или в конструкциях IN и EXISTS.

Предположим, известно имя, но не известно значение поля ‘Продавец’ для продавца Мотика. Необходимо извлечь все ее заказы.

SELECT *

FROM Заказы

WHERE продавец =

            ( SELECT продавец

               FROM Продавцы

               WHERE имя_продавца = ‘Мотика’ )

заказ	количество	дата	покупатель	продавец
3002	1900,10	10.03.1990	2007	1004

Чтобы выполнить внешний (основной) запрос, SQL прежде всего должен выполнить внутренний запрос в предложении WHERE. В результате выбранной оказывается единственная строка с ‘Продавец’ = 1004. SQL подставляет это значение в условие вместо подзапроса: WHERE Продавец = 1004

Затем выполняется основной запрос.

В Домино для описания подзапроса имеется соответствующая конструкция внутри WHERE.

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

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

В СУБД ORACLE для гарантированного получения единственной записи в предложение WHERE нужно добавить условие (ROWNUM < 2). В ДОМИНО такое условие реализуется с помощью специальной функции ‘Это первая найденная запись запроса’.

Операторы BETWEEN, LIKE, IS NULL в подзапросах применять нельзя.

При использовании операторов IN, EXISTS, ANY, ALL, SOME (ДОМИНО поддерживает только IN и EXISTS) можно использовать подзапросы, возвращающие несколько значений. IN определяет множество значений, на вхождение в которое проверяется результат выражения.

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

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

SELECT заказ, количество, дата, покупатель, Заказы.продавец

FROM Заказы, Продавцы

WHERE Заказы.продавец = Продавцы.продавец

            AND Продавцы.город = ‘Лондон’

Если для решения этой задачи применить подзапрос с оператором IN, то получим следующий запрос:

SELECT заказ, количество, дата, покупатель, продавец

FROM Заказы

WHERE продавец IN

            ( SELECT продавец 

               FROM Продавцы 

               WHERE город = ‘Лондон’)

Заказ	Количество	Дата	Покупатель	Продавец
3003	767,19	10.03.1990	2001	1001
3002	1900,10	10.03.1990	2007	1004
3008	4723,00	10.05.1990	2006	1001
3011	9891,88	10.06.1990	2006	1001

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

В Домино можно использовать конструкцию IN для вложенного запроса.

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

Подзапросы можно применять также внутри предложения HAVING.

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

SELECT рейтинг, COUNT (DISTINCT покупатель)

FROM Покупатели

GROUP BY рейтинг

HAVING рейтинг >

            ( SELECT AVG(рейтинг)

              FROM Покупатели

              WHERE город = ‘Сан Хосе’)

Рейтинг	COUNT (DISTINCT покупатель)
300	2

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

В Домино такой запрос выглядит тоже не просто.

Оператор EXISTS

Оператор EXISTS применяется для проверки условия генерации подзапросом выходных данных.

Оператор EXISTS возвращает значение либо ‘истина’, либо ‘ложь’. Этот оператор можно использовать отдельно в условии или комбинировать его с другими булевыми выражениями с помощью операторов AND, OR и NOT. Используя подзапрос в качестве аргумента, EXISTS оценивает его как истинный, если он генерирует выходные данные. В противном случае запрос оценивается как ложный.

Например, требуется извлечь данные о покупателях в том случае, если один (или более) покупатель живет в Лондоне.

SELECT покупатель, имя_покупателя, город

FROM Покупатели

WHERE EXISTS

            ( SELECT *

               FROM Покупатели

               WHERE город = ‘Лондон’ )

Покупатель	Имя_покупателя	Город
2001	Хофман	Лондон
2002	Джованни	Рим
2003	Лу	Сан Хосе
2004	Грасс	Берлин
2006	Клеменс	Лондон
2008	Гиснерос	Сан Хосе
2007	Перейра	Рим

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

Применение конструкции EXISTS в Домино выглядит вот так.

В ДОМИНО часто применяются подзапрос внутри конструкции EXISTS, который в конструкции WHERE содержит ссылки на параметры основного запроса.

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

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

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

SELECT продавец, имя_продавца

FROM Продавцы

WHERE город = ‘Лондон’

UNION

SELECT покупатель, имя_покупателя

FROM Покупатели

WHERE город = ‘Лондон’

Продавец / Покупатель	Имя
1001	Пил
1004	Мотика
2001	Хофман
2006	Клеменс

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

Этот запрос в Домино разбивается на три запроса:

Использование SQL для ввода, изменения и удаления значений полей. Операторы INSERT, UPDATE, DELETE

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

Команды имеют следующий синтаксис:

INSERT INTO <имя таблицы> [(<имя столбца> .,…)]

{VALUES (<список полей>) .,…)} | <запрос>

UPDATE <имя таблицы>

            SET { | } .,… <имя столбца> = <список полей>

            [ WHERE <условие> ]

DELETE  FROM <имя таблицы>

            [ WHERE <условие> ]

• Cписок полей – Выражение, включающее имена столбцов или состоящее из них.
• Имя таблицы – Имя или синоним таблицы или представления.
• Условие – Условие, которое может быть истинным или ложным для каждого столбца или комбинации столбцов из таблицы, определенных в предложении FROM.
• Имя столбца – Имя столбца таблицы.

Символы:

| - Все то, что предшествует данному символу, можно заменить тем, что следует за ним. Аналогично ИЛИ.

{} – Все то, что заключено в фигурные скобки, рассматривается как единое целое для применения других символов (в том числе для символа |).

[] – Все то, что заключено в квадратные скобки, является необязательным.

.,… - Все то, что предшествует этим символам, может повторяться произвольное число раз; каждое отдельное вхождение отделяется запятой.

Для того чтобы добавить одну строку в таблицу ‘Продавцы’ можно использовать простую форму команды INSERT:

INSERT INTO Продавцы VALUES (1001, ‘Пил’, ‘Лондон’, 0,12)

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

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

INSERT INTO Продавцы (город, продавец, вознаграждение, имя_продавца)

VALUES (‘Лондон’, 1001, 0,12, ‘Пил’)

В Домино требуется точное указание столбца (поля).

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

INSERT INTO Продавцы_из_Лондона

            SELECT продавец, имя_продавца

                        FROM Продавцы

                        WHERE город = ‘Лондон’

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

 В Домино для такого запроса применяется другой оператор:

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

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

DELETE FROM Продавцы

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

Обычно из таблицы требуется удалить только некоторые указанные строки. Чтобы их определить используется условие. Например, чтобы исключить продавца Аксельрод из таблицы, следует выполнить следующую команду:

DELETE FROM Продавцы

WHERE продавец = 1003

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

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

Например, требуется изменить рейтинг всех покупателей на 200.

UPDATE  Покупатели SET рейтинг = 200

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

UPDATE  Покупатели

SET рейтинг = 200

WHERE покупатель = 1001

Рейтинг изменится только у покупателя с номером 1001.

В Домино для изменения записей применяется оператор ‘Изменить таблицу’.

Создание таблиц. Оператор CREATE

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

Синтаксис команды:

CREATE TABLE <имя таблицы>

            (<имя столбца> <тип данных> [(<размер>)] .,…)

Пример создания таблицы Продавцы:

CREATE TABLE Продавцы

            ( продавец integer,

               имя_продавца char(40),

               город char(40),

               вознаграждение decimal)

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

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

Когда создается индекс по значениям какого-либо поля таблицы, формируется упорядоченный список значений этого поля. Предположим, таблица ‘Покупатели’ имеет тысячи строк и нужно найти покупателя с номером 14762. Поскольку строки не упорядочены, то программа должна просмотреть всю таблицу, строку за строкой, и выбрать ту строку, в которой значение поля ‘покупатель’ равно 14762. Если бы по полю ‘покупатель’ был организован индекс, то программа могла бы сразу найти в нем значение 14762 и получить информацию о том, как обнаружить нужную строку таблицы. Создание индексов может значительно улучшить выполнение запросов, но управление индексами замедляет время выполнения операций обновления (INSERT, UPDATE и DELETE). Кроме того, сам индекс занимает место в памяти. Следовательно перед созданием индексов следует тщательно проанализировать ситуацию.

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

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

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

Соответствие SQL предложений и конструкций языка скриптов

SQL предложение	Конструкция языка скриптов
SELECT в целом	Запрос в целом
DISTINCT	Признак ‘Distinct (Исключать дублирующие записи)’
ALL	По умолчанию
FROM {<имя таблицы> [<алиас>]}	Раздел ‘FROM (Таблицы)’
SELECT <список полей>	Разделы ‘Параметры’ и ‘Выражения’
[WHERE <условие>]	Раздел ‘WHERE (Ограничения на отбор данных)’
[GROUP BY {<имя столбца> <целое>} .,…]	Раздел ‘GROUP BY (Группировка)’
[HAVING < условие >]	Раздел ‘HAVING (Ограничения на группы строк заданных разделом GROUP BY)’
[ORDER BY {<имя столбца> | <целое>} .,…]	Раздел ‘ORDER BY (Сортировка)’
UNION [ALL]	Условия объединения запросов в разделе ‘FROM (Таблицы)’
Операторы сравнения и булевы операторы	Атрибуты разделов ‘WHERE’ и ‘HAVING’
IN	Для произвольного набора значений реализуется с помощью выражения.
	Для результатов подзапроса - атрибут ‘IN (равно какому-либо из …) в разделах ‘WHERE’ и ‘HAVING’
BETWEEN	Реализуется с помощью выражения.
LIKE	Реализуется с помощью выражения и описанных в проекте функций SQL сервера.
IS NULL, NOT NULL	= NULL и  <> NULL
Функции агрегирования: COUNT, SUM, AVG, MAX, MIN	Соответствующие функции в проекте
Соединение таблиц (join)	Внутреннее соединение реализуется через обычное равенство, а для внешнего соединения используется специальный атрибут ‘Результат (Внешнее соединение таблиц – допустимо отсутствие значения’
Алиасы	Реализуются автоматически, т.к. адресация к полям таблиц осуществляется с помощью уточняющего параметра к ссылке на таблицу.
Вложенный запрос (подзапрос)	Атрибут ‘Запрос’ в разделах ‘WHERE’,  ‘HAVING’, ‘FROM (Таблицы)’
EXISTS	Атрибут ‘EXISTS (существует …) в разделах ‘WHERE’ и ‘HAVING’
ANY, ALL, SOME	Реализуются с помощью выражения.
INSERT	Операторы ‘Добавить в таблицу’ и ‘Добавить в таблицу результаты запроса’
DELETE	Оператор ‘Удалить из таблицы’
UPDATE	Оператор ‘Изменить таблицу’
CREATE	Достаточно описания таблицы в проекте. Если таблицы не существует, то будет создана. Если существует, то описание на сервере (поля, индексы) будет приведено в соответствие с описанием в проекте.