Как выбрать все записи из таблицы cities где значение поля city начинается с m

SQL запросы быстро. Часть 1

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

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join’ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика

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

Кликнуть здесь

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

Структура sql-запросов

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

SELECT ('столбцы или * для выбора всех столбцов; обязательно') FROM ('таблица; обязательно') WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно') GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно') HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно') ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS’ом.

SELECT, FROM

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

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers

WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

select * from Customers where City IN ('London', 'Berlin')

select * from Customers where City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

select * from Customers where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15

select * from Customers where City in ('London', 'Berlin') OR CustomerID > 4

GROUP BY

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

При использовании GROUP BY обязательно:

перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

select City, count(CustomerID) from Customers GROUP BY City

Группировка количества клиентов по стране и городу:

select Country, City, count(CustomerID) from Customers GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:

 select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

 select City, count(CustomerID) from Customers WHERE Country = 'Germany' GROUP BY City

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

select City, count(CustomerID) AS Number_of_clients from Customers group by City

HAVING

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

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

 select City, count(CustomerID) from Customers group by City HAVING count(CustomerID) >= 5

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:

 select City, count(CustomerID) as number_of_clients from Customers group by City HAVING number_of_clients >= 5

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

 select City, count(CustomerID) as number_of_clients from Customers WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend') group by City HAVING number_of_clients >= 5

ORDER BY

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

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

 select * from Customers ORDER BY City

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

 select * from Customers ORDER BY Country, City

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

 select * from Customers order by CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

select * from Customers order by Country DESC, City

JOIN

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

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

select * from Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,

 select * from Orders join Customers on Orders.CustomerID = Customers.CustomerID where Customers.CustomerID >10

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN’ов можно увидеть на замечательной картинке ниже:

В следующей части подробнее поговорим о типах JOIN’ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!

Оператор SELECT в MySQL 8: полный обзор с примерами кода

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

Оператор WHERE
Предложение ORDER BY
Предложение LIMIT
Операции соединения SQL
UNION
Оптимизация запросов SELECT
Вас заинтересует / Intresting for you:

SELECT поле1, поле2, поле3 from имя_таблицы [WHERE предложение] [GROUP BY ] [HAVING условие_where] [ORDER BY , . ] [LIMIT]

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

поля поле1 и поле2 являются именами столбцов таблицы. Для извлечения всех столбцов из таблицы можно использовать выражение * ;
имя_таблицы задает имя таблицы, из которой необходимо извлечь данные;
оператор WHERE может использоваться для задания любого условия в одном и нескольких столбцах;
функция GROUP BY используется с агрегатными функциями для группирования результирующих наборов;
предложение HAVING необходимо после GROUP BY для фильтрации на основе условий для группы строк или агрегатов. Если мы используем предложение HAVING без GROUP BY , оно будет действовать аналогично оператору WHERE ;
предложение ORDER BY используется для сортировки результирующих наборов таблицы по возрастанию или убыванию;
LIMIT используется для ограничения количества строк, возвращаемых оператором SELECT .

Давайте рассмотрим каждый элемент оператора.

Оператор WHERE

Следующий ниже фрагмент кода содержит универсальную синтаксическую конструкцию для запроса SELECT с оператором WHERE :

SELECT поле1, поле2, поле3, . поле_N from имя_таблицы1, имя_таблицы2 [WHERE условие1 [AND [OR]] условие2.

Оператор WHERE является необязательной частью запроса SELECT . Для указания условий можно использовать операторы AND или OR . Оператор WHERE также может использоваться с запросом DELETE и UPDATE , который мы обсудим в ближайшее время в последующей статье.

Ниже приведен список операторов, используемых с оператором WHERE для задания условий. Чтобы понять эти операции, давайте рассмотрим пример схемы таблицы. Мы создадим таблицу users с упомянутой здесь схемой, имеющей поля id, first_name, last_name, address, city, state, zip, login_attempts, contact_number, email, username и password:

CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(255), `last_name` varchar(255), `address` varchar(255), `city` varchar(50), `state` varchar(2), `zip` varchar(10), `login_attempts` int(10), `contact_number` varchar(20), `email` varchar(191), `username` varchar(191), `password` varchar(255), PRIMARY KEY (`id`) ) ENGINE=InnoDB PRIMARY KEY (`id`) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Равно и не равно

Оператор равенства ( = ) проверяет, равны ли значения двух полей. Если они совпадают, то условие становится истинным, и оператор извлекает значение для дальнейшей обработки. Если они не совпадают, то условие должно содержать оператор неравенства ( <> ). Оно будет извлекать данные на основе условия, которое не совпадает.

Например, следующий ниже запрос используется для получения всех записей с городом, соответствующим значению New York:

SELECT * FROM 'users' WHERE 'city' = 'New York';

Больше и меньше

Оператор больше, чем ( > ) проверяет, больше ли значение левого поля, чем значение правого поля. Если да, то условие становится истинным. Оператор меньше, чем ( < ) проверяет, меньше ли значение левого поля, чем значение правого поля. Мы также можем использовать операторы >/ < и оператор равенства вместе.

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

SELECT * FROM 'users' WHERE 'login_attempts' > 2;

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

select * from users where username like 'a%';

В случае если мы хотим найти пользователей, чье имя начинается с «a» и заканчивается на «s», то запрос с подстановочным символом % будет таким:

select * from user where username like 'a%s';

select * from users where username like '_dmin';

IN/NOT IN

Оператор IN используется для сравнения нескольких значений в операторе WHERE . Например, следующий ниже запрос используется для поиска всех пользователей, имеющих город new york или chicago:

select * from users where city IN ('new york','chicago')

Оператор NOT IN работает наоборот, например чтобы найти всех пользователей, у которых нет ни города new york, ни города chicago, используется:

select * from users where city NOT IN ('new york','chicago');

BETWEEN

Оператор BETWEEN может использоваться в том случае, когда мы хотим получить записи, которые входят в определенный диапазон. Этот диапазон может быть любым, таким как текст, даты или цифры. Предположим, мы хотим отыскать пользователей, дата создания записи о которых находится между 1 июля и 16 июля 2017 года. Тогда приведенный ниже запрос с предложением BETWEEN может нам помочь.

select * from users where created_at BETWEEN '2017-07-01 00:00:00' AND '2017-07-16 00:00:00';

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

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

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

SELECT * FROM users ORDER BY city ASC;

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

SELECT * FROM users ORDER BY city, username;

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

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

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

SELECT * FROM users limit 5, 10;

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

SELECT * FROM users limit 10;

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

Операции соединения SQL

Операция соединения используется для извлечения данных из нескольких таблиц. Например, если есть две таблицы order и customer и мы хотим получить данные, то это можно сделать с помощью предложения JOIN .

Различные типы предложения JOIN следующие:

INNER JOIN : внутреннее соединение возвращает только те записи, значения которых совпадают в обеих таблицах;
CROSS JOIN : перекрестное соединение возвращает только те записи, которые имеют совпадающие значения в левой или правой таблице;
LEFT JOIN : левое соединение возвращает все записи из левой таблицы и только совпадающие записи из правой таблицы;
RIGHT JOIN : правое соединение возвращает все записи из правой таблицы и только совпадающие записи из левой таблицы.

На следующем ниже рисунке показан краткий пример для рассмотрения:

INNER JOIN

Внутреннее соединение возвращает записи, совпадающие в обеих таблицах. Например, ниже приведена таблица заказов order, используемая в приложениях электронной коммерции:

order_id

customer_id

order_amount

order_date

ship_id

Инструкция INSERT (Transact-SQL)

Добавляет одну или несколько строк в таблицу или представление в SQL Server. Примеры см. в разделе Примеры.

Синтаксис

-- Syntax for SQL Server and Azure SQL Database [ WITH [ . n ] ] INSERT < [ TOP ( expression ) [ PERCENT ] ] [ INTO ] < | rowset_function_limited [ WITH ( [ . n ] ) ] > < [ ( column_list ) ] [ ] < VALUES ( < DEFAULT | NULL | expression >[ . n ] ) [ . n ] | derived_table | execute_statement | | DEFAULT VALUES > > > [;] ::= < [ server_name . database_name . schema_name . | database_name .[ schema_name ] . | schema_name . ] table_or_view_name > ::= SELECT FROM ( ) [AS] table_alias [ ( column_alias [ . n ] ) ] [ WHERE ] [ OPTION ( [ . n ] ) ]

-- External tool only syntax INSERT < [BULK] < database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name >( ) [ WITH ( [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] FIRE_TRIGGERS ] [ [ , ] KEEP_NULLS ] [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] [ [ , ] ROWS_PER_BATCH = rows_per_batch ] [ [ , ] ORDER ( < column [ ASC | DESC ] >[ . n ] ) ] [ [ , ] TABLOCK ] ) ] > [; ] ::= column_name [ COLLATE collation_name ] [ NULL | NOT NULL ] ::= [ type_schema_name . ] type_name [ ( precision [ , scale ] | max ]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric INSERT [INTO] < database_name.schema_name.table_name | schema_name.table_name | table_name >[ ( column_name [ . n ] ) ] < VALUES ( < NULL | expression >) | SELECT > [ OPTION ( [ . n ] ) ] [;]

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

WITH
Определяет временный именованный результирующий набор, также называемый обобщенным табличным выражением, определенным в области инструкции INSERT. Результирующий набор получается из инструкции SELECT. Дополнительные сведения см. в статье WITH common_table_expression (Transact-SQL).

TOP (expression) [ PERCENT ]
Задает число или процент вставляемых случайных строк. expression может быть либо числом, либо процентом от числа строк. Дополнительные сведения см. в разделе TOP (Transact-SQL).

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

server_name
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

Когда server_name указывается как имя связанного сервера, необходимо указать database_name и schema_name. Если server_name указано с помощью OPENDATASOURCE, то аргументы database_name и schema_name могут применяться не ко всем источникам данных, в зависимости от возможностей поставщика OLE DB, который обращается к удаленному объекту.

database_name
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Имя базы данных.

schema_name
Имя схемы, которой принадлежит таблица или представление.

table_or view_name
Имя таблицы или представления, которые принимают данные.

В качестве источника таблицы в инструкции INSERT можно использовать табличную переменную внутри своей области.

Представление, на которое ссылается аргумент table_or_view_name, должно быть обновляемым и ссылаться только на одну базовую таблицу в предложении FROM в представлении. Например, инструкция INSERT в многотабличном представлении должна использовать аргумент column_list, который ссылается только на столбцы из одной базовой таблицы. Дополнительные сведения об обновляемых представлениях см. в разделе CREATE VIEW (Transact-SQL).

rowset_function_limited
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Либо функция OPENQUERY, либо функция OPENROWSET. Использование этих функций зависит от возможностей поставщика OLE DB, который обращается к удаленному объекту.

WITH ( [. n ] )
Задает одно или несколько табличных указаний, разрешенных для целевой таблицы. Ключевое слово WITH и круглые скобки обязательны.

Нельзя использовать подсказки READPAST, NOLOCK, и READUNCOMMITTED. Дополнительные сведения об указаниях по таблицам см. в разделе Указания по таблицам (Transact-SQL).

Возможность указывать указания HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD или UPDLOCK для таблиц, которые являются целевыми объектами инструкций INSERT, будут удалены в будущей версии SQL Server. Эти указания не влияют на производительность инструкций INSERT. Избегайте применять их в новых разработках и запланируйте внесение изменений в приложения, использующие их в настоящее время.

Указание подсказки TABLOCK для целевой таблицы инструкции INSERT приведет к тем же последствиям, что и указание подсказки TABLOCKX. К таблице будет применена монопольная блокировка.

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

Если столбец не находится в column_list, ядро СУБД должен иметь возможность предоставить значение на основе определения столбца; в противном случае строка не может быть загружена. Ядро СУБД автоматически предоставляет значение столбца, если столбец:

Имеется свойство IDENTITY. Используется следующее значение приращения для идентификатора.
Имеется стандартное значение. Используется стандартное значение для столбца.
Имеет тип данных timestamp. В этом случае используется текущее значение отметки времени.
Допускает значение NULL. Используется значение NULL.
Вычисляемый столбец. Используется вычисленное значение.

Аргумент column_list необходимо использовать, когда в столбец идентификаторов вставляются явно заданные значения, а параметру SET IDENTITY_INSERT необходимо присвоить значение ON для таблицы.

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

Предложение OUTPUT не поддерживается инструкциями DML, которые ссылаются на локальные секционированные представления, распределенные секционированные представления или удаленные таблицы, или инструкциями INSERT, содержащими аргумент execute_statement. Предложение OUTPUT INTO не поддерживается в инструкциях INSERT, содержащих предложение . Дополнительные сведения об аргументах и логике работы этого предложения см. в разделе Предложение OUTPUT (Transact-SQL).

ЗНАЧЕНИЯ
Позволяет использовать один или несколько списков вставляемых значений данных. Для каждого столбца в column_list, если этот параметр указан, или в таблице должно быть одно значение. Список значений должен быть заключен в скобки.

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

Можно использовать конструктор строк Transact-SQL (также называемый конструктором табличных значений), для указания нескольких строк в одной инструкции INSERT. Этот конструктор строк состоит из одного предложения VALUES со списками из нескольких значений, заключенными в круглые скобки и разделенными запятыми. Дополнительные сведения см. в разделе Конструктор табличных значений (Transact-SQL).

Конструктор значений таблиц в Azure Synapse Analytics не поддерживается. Вместо этого можно выполнить приведенные ниже инструкции INSERT для вставки нескольких строк. В Azure Synapse Analytics вставляемые значения могут быть только константными литералами или ссылками на переменные. Чтобы вставить нелитеральное значение, задайте переменной неконстантное значение и вставьте переменную.

ПО УМОЛЧАНИЮ
Принудительно ядро СУБД загрузить значение по умолчанию, определенное для столбца. Если для столбца не задано значение по умолчанию и он может содержать значение NULL, вставляется значение NULL. В столбцы с типом данных timestamp вставляется следующее значение метки времени. Значение DEFAULT недопустимо для столбца идентификаторов.

выражение
Константа, переменная или выражение. Выражение не может содержать инструкцию EXECUTE.

При ссылке на типы данных символов Юникода nchar, nvarchar и ntext выражение ‘expression‘ должно начинаться с заглавной буквы ‘N’. Если значение «N» не указано, SQL Server преобразует строку в кодовую страницу, соответствующую параметрам сортировки по умолчанию базы данных или столбца. Любые символы, не входящие в эту кодовую страницу, будут утрачены.

derived_table
Любая допустимая инструкция SELECT, возвращающая строки данных, которые загружаются в таблицу. Инструкция SELECT не может содержать обобщенное табличное выражение (CTE).

execute_statement
Любая допустимая инструкция EXECUTE, возвращающая данные с помощью инструкций SELECT или READTEXT. Дополнительные сведения см. в статье EXECUTE (Transact-SQL).

Параметры RESULT SETS инструкции EXECUTE нельзя указывать в инструкции INSERT…EXEC.

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

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

Если execute_statement возвращает данные с помощью инструкции READTEXT, каждая инструкция READTEXT может возвращать не более 1 МБ (1024 КБ) данных. execute_statement также может использоваться при работе с расширенными процедурами. execute_statement вставляет данные, возвращенные главным потоком расширенной процедуры; однако выходные данные из других потоков (кроме главного) не вставляются.

Возвращающий табличное значение параметр нельзя указывать в качестве объекта инструкции INSERT EXEC, но его можно указать в виде источника в строке INSERT EXEC или в хранимой процедуре. Дополнительные сведения см. в разделе Использование возвращающих табличные значения параметров (ядро СУБД).

Указывает, что вставленные в целевую таблицу строки были возвращены предложением OUTPUT инструкции INSERT, UPDATE, DELETE или MERGE с возможной фильтрацией предложением WHERE. Если используется аргумент , целевая таблица внешней инструкции INSERT должна удовлетворять следующим ограничениям:

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

Уровень совместимости базы данных должен быть не ниже 100. Дополнительные сведения см. в статье Предложение OUTPUT (Transact-SQL).

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

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

Допустимая инструкция INSERT, UPDATE, DELETE или MERGE, возвращающая изменяемые строки в предложении OUTPUT. Инструкция не может содержать предложение WITH и использовать удаленные таблицы или секционированные представления в качестве целевых. Если указаны UPDATE или DELETE, это не могут быть использующие курсор инструкции UPDATE или DELETE. На исходные строки нельзя ссылаться как на вложенные инструкции DML.

WHERE
Любое предложение WHERE, содержащее допустимый критерий поиска , фильтрующее строки, которые возвращаются аргументом . Дополнительные сведения см. в разделе Условие поиска (Transact-SQL). При использовании в этом контексте критерий не должен содержать вложенных запросов, определяемых пользователем скалярных функций, выполняющих доступ к данным, агрегатных функций, TEXTPTR или предикатов полнотекстового поиска.

DEFAULT VALUES
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

BULK
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Используется внешними средствами для передачи потока двоичных данных. Этот параметр не предназначен для использования с такими инструментами, как SQL Server Management Studio, SQLCMD, OSQL или интерфейсы программирования приложений доступа к данным, такие как собственный клиент SQL Server.

FIRE_TRIGGERS
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает, что при передаче потока двоичных данных будут выполняться триггеры INSERT, определенные для целевой таблицы. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).

CHECK_CONSTRAINTS
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает, что при передаче потока двоичных данных будет выполняться проверка всех ограничений целевой таблицы или представления. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).

KEEPNULLS
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

KILOBYTES_PER_BATCH = kilobytes_per_batch
Определяет приблизительное число килобайт данных в пакете как kilobytes_per_batch. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).

ROWS_PER_BATCH =rows_per_batch
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает приблизительное число строк в потоке двоичных данных. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).

Если список столбцов отсутствует, то возникает синтаксическая ошибка.

Замечания

Дополнительную информацию о вставке данных в графовые таблицы SQL см. в разделе INSERT (Граф SQL).

Тип данных	Стандартная операция
char	Заполнение значения пробелами до заданной ширины столбца.
varchar	Удаление конечных пробелов до последнего непробельного символа или до одного пробела, если строка состоит только из пробелов.
varbinary	Удаление конечных нулей.

Ограничения

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

При использовании выражения TOP в инструкции INSERT строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этих инструкциях. Если для вставки строк в значимом хронологическом порядке необходимо использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY. См. подраздел «Примеры» далее в этом разделе.

Запросы INSERT, которые используют SELECT с ORDER BY для заполнения строк, гарантируют способ вычисления значений идентификатора, но не порядок вставки строк.

В Parallel Data Warehouse предложение ORDER BY недопустимо в инструкциях VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, встраиваемых функциях, производных таблицах, подзапросах и обобщенных табличных выражениях, если также не указать TOP.

Режим ведения журнала

Инструкция INSERT всегда полностью регистрируется в журнале, кроме случаев использования функции OPENROWSET с ключевым словом BULK или выполнения инструкции INSERT INTO SELECT FROM . Для этих операций возможно минимальное протоколирование. Дополнительные сведения см. в подразделе «Рекомендации по массовой загрузке данных» этого раздела.

Безопасность

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

При использовании OPENROWSET(BULK. ) важно понимать, как SQL Server обрабатывает олицетворение. Дополнительные сведения см. в главе «Вопросы безопасности» в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK. ) (SQL Server).

Разрешения

Требуется разрешение INSERT на целевую таблицу.

Разрешения INSERT предоставлены по умолчанию членам предопределенной роли сервера sysadmin , членам предопределенных ролей баз данных db_owner и db_datawriter , а также владельцу таблицы. Члены ролей sysadmin , db_owner и db_securityadmin , а также владелец таблицы могут передавать разрешения другим пользователям.

Чтобы выполнить инструкцию INSERT с параметром BULK функции OPENROWSET, необходимо быть членом предопределенной роли сервера sysadmin или bulkadmin .

Примеры

Категория	Используемые элементы синтаксиса
Основной синтаксис	Конструктор значений INSERT * табличного значения
Обработка значений столбца	IDENTITY * NEWID * значения по умолчанию * определяемые пользователем типы
Вставка данных из других таблиц	ВСТАВИТЬ. SELECT * INSERT. EXECUTE * WITH common table expression * TOP * OFFSET FETCH
Указание целевых объектов, отличных от стандартных таблиц	Представления * табличные переменные
Вставка строк в удаленную таблицу	Связанный сервер * функция набора строк OPENQUERY * функция набора строк OPENDATASOURCE
Массовая загрузка данных из таблиц или файлов данных	ВСТАВИТЬ. SELECT * ФУНКЦИЯ OPENROWSET
Переопределение поведения по умолчанию для оптимизатора запросов с помощью указаний	Табличные указания
Сбор результатов инструкции INSERT	OUTPUT, предложение

Базовый синтаксис

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

А. Вставка одной строки данных

В следующем примере вставляется одна строка в таблицу Production.UnitMeasure в базе данных AdventureWorks2022. В этой таблице содержатся столбцы UnitMeasureCode , Name и ModifiedDate . Так как значения для всех столбцов предоставлены и перечислены в том же порядке, что и столбцы в таблице, не требуется указывать имена столбцов в списке столбцов*.*

INSERT INTO Production.UnitMeasure VALUES (N'FT', N'Feet', '20080414');

B. Вставка нескольких строк данных

В следующем примере конструктор значений таблицы используется для вставки трех строк Production.UnitMeasure в таблицу в базе данных AdventureWorks2022 в одной инструкции INSERT. Так как значения для всех столбцов предоставлены и перечислены в том же порядке, что и столбцы в таблице, то не нужно в параметре указывать имена столбцов.

Конструктор значений таблицы не поддерживается в Azure Synapse Analytics.

INSERT INTO Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923') , (N'Y3', N'Cubic Yards', '20080923');

C. Вставка данных в порядке, отличном от порядка столбцов таблицы

В следующем примере используется список столбцов для явного указания значений, которые будут вставляться в каждый столбец. Порядок столбцов в таблице базы Production.UnitMeasure данных AdventureWorks2022 имеет UnitMeasureCode значение , ModifiedDate Name но столбцы не перечислены в этом порядке в column_list.

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) VALUES (N'Square Yards', N'Y2', GETDATE());

Обработка значений столбцов

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

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

В следующем примере показана вставка строк в таблицу со столбцами, для которых автоматически создается значение или которые имеют значение по умолчанию. Column_1 — это вычисляемый столбец, который автоматически создает значение, объединяя строку со значением, вставленным в столбец column_2 . Столбец Column_2 определен с ограничением по умолчанию. Если для этого столбца не указано значение, используется значение по умолчанию. Столбец Column_3 имеет тип данных rowversion, который автоматически создает уникальное, последовательно увеличиваемое двоичное число. Столбец Column_4 не формирует значения автоматически. Если значение для этого столбца отсутствует, то вставляется значение NULL. Инструкция INSERT вставляет строки, которые содержат значения для некоторых столбцов, но не для всех. В последней инструкции INSERT столбцы не указаны, и поэтому вставляются только значения по умолчанию с помощью предложения DEFAULT VALUES.

CREATE TABLE dbo.T1 ( column_1 AS 'Computed column ' + column_2, column_2 varchar(30) CONSTRAINT default_name DEFAULT ('my column default'), column_3 rowversion, column_4 varchar(40) NULL ); GO INSERT INTO dbo.T1 (column_4) VALUES ('Explicit value'); INSERT INTO dbo.T1 (column_2, column_4) VALUES ('Explicit value', 'Explicit value'); INSERT INTO dbo.T1 (column_2) VALUES ('Explicit value'); INSERT INTO T1 DEFAULT VALUES; GO SELECT column_1, column_2, column_3, column_4 FROM dbo.T1; GO

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

В следующем примере показаны различные методы вставки данных в столбец идентификаторов. Первые две инструкции INSERT позволяют формировать значения идентификаторов для новых строк. Третья инструкция INSERT переопределяет свойство IDENTITY столбца с помощью инструкции SET IDENTITY_INSERT и вставляет явно заданное значение в столбец идентификаторов.

CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30)); GO INSERT T1 VALUES ('Row #1'); INSERT T1 (column_2) VALUES ('Row #2'); GO SET IDENTITY_INSERT T1 ON; GO INSERT INTO T1 (column_1,column_2) VALUES (-99, 'Explicit identity value'); GO SELECT column_1, column_2 FROM T1; GO

Е. Вставка данных в столбец типа uniqueidentifier с помощью функции NEWID()

В следующем примере функция NEWID() вызывается для вставки идентификатора GUID в столбец column_2 . В отличие от столбцов удостоверений, ядро СУБД не создает значения для столбцов с типом данных uniqueidentifier, как показано во втором INSERT операторе.

CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 uniqueidentifier, ); GO INSERT INTO dbo.T1 (column_2) VALUES (NEWID()); INSERT INTO T1 DEFAULT VALUES; GO SELECT column_1, column_2 FROM dbo.T1;

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

Следующие инструкции Transact-SQL вставляют три строки в столбец PointValue таблицы Points . Этот столбец имеет определяемый пользователем тип данных CLR. Тип данных Point состоит из целочисленных значений X и Y, которые представлены как свойства определяемого пользователем типа. Необходимо привести разделяемые запятой значения X и Y к типу Point с помощью функции CAST или CONVERT. Первые две инструкции используют функцию CONVERT для преобразования строкового значения в тип Point , а третья инструкция использует функцию CAST. Дополнительные сведения см. в разделе Работа с данными определяемого пользователем типа.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4')); INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5')); INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

Вставка данных из других таблиц

В примерах этого раздела показаны методы вставки строк из одной таблицы в другую.

H. Вставка данных из других таблиц с помощью параметров SELECT и EXECUTE

В следующем примере описана вставка данных из одной таблицы в другую с помощью инструкций INSERT…SELECT и INSERT…EXECUTE. Каждый метод основан на многотабличной инструкции SELECT, содержащей выражение и литеральное значение в списке столбцов.

Первая инструкция INSERT использует инструкцию SELECT для получения данных из исходных таблиц ( Employee , SalesPerson и Person ) в базе данных AdventureWorks2022 и хранения результирующий набор в EmployeeSales таблице. Вторая инструкция INSERT с помощью предложения EXECUTE вызывает хранимую процедуру, содержащую инструкцию SELECT, а третья инструкция INSERT с помощью предложения EXECUTE ссылается на инструкцию SELECT как на символьную строку.

CREATE TABLE dbo.EmployeeSales ( DataSource varchar(20) NOT NULL, BusinessEntityID varchar(11) NOT NULL, LastName varchar(40) NOT NULL, SalesDollars money NOT NULL ); GO CREATE PROCEDURE dbo.uspGetEmployeeSales AS SET NOCOUNT ON; SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.BusinessEntityID LIKE '2%' ORDER BY sp.BusinessEntityID, c.LastName; GO --INSERT. SELECT example INSERT INTO dbo.EmployeeSales SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.BusinessEntityID LIKE '2%' ORDER BY sp.BusinessEntityID, c.LastName; GO --INSERT. EXECUTE procedure example INSERT INTO dbo.EmployeeSales EXECUTE dbo.uspGetEmployeeSales; GO --INSERT. EXECUTE('string') example INSERT INTO dbo.EmployeeSales EXECUTE (' SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.BusinessEntityID LIKE ''2%'' ORDER BY sp.BusinessEntityID, c.LastName '); GO --Show results. SELECT DataSource,BusinessEntityID,LastName,SalesDollars FROM dbo.EmployeeSales;

I. Использование обобщенного табличного выражения WITH для определения вставляемых данных

В следующем примере создается NewEmployee таблица в базе данных AdventureWorks2022. Обобщенное табличное выражение ( EmployeeTemp ) определяет строки из одной или нескольких таблиц, которые вставляются в таблицу NewEmployee . Инструкция INSERT ссылается на столбцы в обобщенном табличном выражении.

CREATE TABLE HumanResources.NewEmployee ( EmployeeID int NOT NULL, LastName nvarchar(50) NOT NULL, FirstName nvarchar(50) NOT NULL, PhoneNumber Phone NULL, AddressLine1 nvarchar(60) NOT NULL, City nvarchar(30) NOT NULL, State nchar(3) NOT NULL, PostalCode nvarchar(15) NOT NULL, CurrentFlag Flag ); GO WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, Address, City, StateProvince, PostalCode, CurrentFlag) AS (SELECT e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber, a.AddressLine1, a.City, sp.StateProvinceCode, a.PostalCode, e.CurrentFlag FROM HumanResources.Employee e INNER JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID INNER JOIN Person.Address AS a ON bea.AddressID = a.AddressID INNER JOIN Person.PersonPhone AS pp ON e.BusinessEntityID = pp.BusinessEntityID INNER JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID INNER JOIN Person.Person as c ON e.BusinessEntityID = c.BusinessEntityID ) INSERT INTO HumanResources.NewEmployee SELECT EmpID, LastName, FirstName, Phone, Address, City, StateProvince, PostalCode, CurrentFlag FROM EmployeeTemp; GO

J. Использование TOP для ограничения данных, вставляемых из исходной таблицы

В следующем примере создается таблица EmployeeSales и вставляется данные о продажах по имени и годам для первых 5 случайных сотрудников из таблицы HumanResources.Employee в базе данных AdventureWorks2022. Инструкция INSERT выбирает любые пять строк из строк, возвращенных инструкцией SELECT . Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales . Обратите внимание, что предложение ORDER BY в инструкции SELECT не используется для определения 5 наиболее успешных сотрудников.

CREATE TABLE dbo.EmployeeSales ( EmployeeID nvarchar(11) NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, YearlySales money NOT NULL ); GO INSERT TOP(5)INTO dbo.EmployeeSales OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.SalesYTD > 250000.00 ORDER BY sp.SalesYTD DESC;

Если для вставки строк в значимом хронологическом порядке решено использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY, как показано в следующем примере. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales . Обратите внимание, что вставка данных 5 наиболее успешных сотрудников выполняется на основе результатов предложения ORDER BY, а не случайных строк.

INSERT INTO dbo.EmployeeSales OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.SalesYTD > 250000.00 ORDER BY sp.SalesYTD DESC;

Указание целевых объектов, отличных от стандартных таблиц

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

K. Вставка данных с указанием представления

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

CREATE TABLE T1 ( column_1 int, column_2 varchar(30)); GO CREATE VIEW V1 AS SELECT column_2, column_1 FROM T1; GO INSERT INTO V1 VALUES ('Row 1',1); GO SELECT column_1, column_2 FROM T1; GO SELECT column_1, column_2 FROM V1; GO

L. Вставка данных в табличную переменную

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

-- Create the table variable. DECLARE @MyTableVar table( LocationID int NOT NULL, CostRate smallmoney NOT NULL, NewCostRate AS CostRate * 1.5, ModifiedDate datetime); -- Insert values into the table variable. INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate) SELECT LocationID, CostRate, GETDATE() FROM Production.Location WHERE CostRate > 0; -- View the table variable result set. SELECT * FROM @MyTableVar; GO

Вставка строк в удаленную таблицу

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

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

В следующем примере в удаленную таблицу вставляются строки. Этот пример начинается с создания ссылки на удаленный источник данных с помощью хранимой процедуры sp_addlinkedserver. Имя связанного сервера, MyLinkServer , затем определяется как часть четырехчастного имени объекта в форме server.catalog.schema.object.

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

USE master; GO -- Create a link to the remote data source. -- Specify a valid server name for @datasrc as 'server_name' -- or 'server_nameinstance_name'. EXEC sp_addlinkedserver @server = N'MyLinkServer', @srvproduct = N' ', @provider = N'SQLNCLI', @datasrc = N'server_name', @catalog = N'AdventureWorks2022'; GO

-- Specify the remote data source in the FROM clause using a four-part name -- in the form linked_server.catalog.schema.object. INSERT INTO MyLinkServer.AdventureWorks2022.HumanResources.Department (Name, GroupName) VALUES (N'Public Relations', N'Executive General and Administration'); GO

О. Вставка данных в удаленную таблицу с помощью функции OPENQUERY

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

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2022.HumanResources.Department') VALUES ('Environmental Impact', 'Engineering'); GO

O. Вставка данных в удаленную таблицу с помощью функции OPENDATASOURCE

В следующем примере выполняется вставка строки в удаленную таблицу с помощью вызова функции OPENDATASOURCE, возвращающей набор строк. Определите допустимое имя сервера для источника данных, используя формат server_name или server_name\instance_name.

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

-- Use the OPENDATASOURCE function to specify the remote data source. -- Specify a valid server name for Data Source using the format -- server_name or server_nameinstance_name. INSERT INTO OPENDATASOURCE('SQLNCLI', 'Data Source= ; Integrated Security=SSPI') .AdventureWorks2022.HumanResources.Department (Name, GroupName) VALUES (N'Standards and Methods', 'Quality Assurance'); GO

P. Вставка во внешнюю таблицу, созданную с помощью PolyBase

Вы можете экспортировать данные из SQL Server в службу хранилища Azure или Hadoop. Для этого сначала необходимо создать внешнюю таблицу, которая указывает на целевой файл или каталог. Затем используйте инструкцию INSERT INTO, чтобы экспортировать данные из локальной таблицы SQL Server во внешний источник данных. При выполнении инструкции INSERT INTO создается целевой файл или каталог (если его не существует), а результаты выполнения инструкции SELECT экспортируются в указанное расположение в заданном формате. Дополнительные сведения см. в разделе Приступая к работе с PolyBase.

Область применения: SQL Server.

-- Create an external table. CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] ( [FirstName] char(25) NOT NULL, [LastName] char(25) NOT NULL, [YearlyIncome] float NULL, [MaritalStatus] char(1) NOT NULL ) WITH ( LOCATION='/old_data/2009/customerdata.tbl', DATA_SOURCE = HadoopHDP2, FILE_FORMAT = TextFileFormat, REJECT_TYPE = VALUE, REJECT_VALUE = 0 ); -- Export data: Move old data to Hadoop while keeping -- it query-able via external table. INSERT INTO dbo.FastCustomer2009 SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2 ON (T1.CustomerKey = T2.CustomerKey) WHERE T2.YearMeasured = 2009 and T2.Speed > 40;

Массовая загрузка данных из таблиц или файлов данных

В примерах этого раздела показано два метода массовой загрузки данных в таблицу с помощью инструкции INSERT.

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

В следующем примере создается таблица (куча), в которую вставляются данные из другой таблицы с минимальным протоколированием. В примере предполагается, что для базы данных AdventureWorks2022 выбрана модель восстановления FULL. Чтобы убедиться, что применяется минимальное протоколирование, модель восстановления базы данных AdventureWorks2022 перед вставкой строк устанавливается в значение BULK_LOGGED, а после выполнения инструкции INSERT INTO… SELECT возвращается в значение FULL. Кроме того, для целевой таблицы Sales.SalesHistory указывается подсказка TABLOCK. Это обеспечивает минимальное использование журнала транзакций инструкцией и ее эффективное выполнение.

-- Create the target heap. CREATE TABLE Sales.SalesHistory( SalesOrderID int NOT NULL, SalesOrderDetailID int NOT NULL, CarrierTrackingNumber nvarchar(25) NULL, OrderQty smallint NOT NULL, ProductID int NOT NULL, SpecialOfferID int NOT NULL, UnitPrice money NOT NULL, UnitPriceDiscount money NOT NULL, LineTotal money NOT NULL, rowguid uniqueidentifier ROWGUIDCOL NOT NULL, ModifiedDate datetime NOT NULL ); GO -- Temporarily set the recovery model to BULK_LOGGED. ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO -- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory INSERT INTO Sales.SalesHistory WITH (TABLOCK) (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate) SELECT * FROM Sales.SalesOrderDetail; GO -- Reset the recovery model. ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO

R. Использование функции OPENROWSET с параметром BULK для массовой загрузки данных а таблицу

В следующем примере выполняется вставка строки в таблицу из файла данных вызовом функции OPENQUERY. Для оптимизации производительности указывается табличная подсказка IGNORE_TRIGGERS. Дополнительные примеры см. в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK. ) (SQL Server).

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName) SELECT b.Name, b.GroupName FROM OPENROWSET ( BULK 'C:SQLFilesDepartmentData.txt', FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml', ROWS_PER_BATCH = 15000)AS b ;

Переопределение поведения по умолчанию для оптимизатора запросов с помощью указаний

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

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

S. Использование подсказки TABLOCK для указания метода блокировки

В следующем примере показано, как монопольная блокировка (Х) применяется к таблице Production.Location и сохраняется до завершения инструкции UPDATE.

Применимо к: SQL Server, База данных SQL.

INSERT INTO Production.Location WITH (XLOCK) (Name, CostRate, Availability) VALUES ( N'Final Inventory', 15.00, 80.00);

Получение результатов инструкции INSERT

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

T. Использование предложения OUTPUT с инструкцией INSERT

В следующем примере производится вставка строки в таблицу ScrapReason , а затем при помощи предложения OUTPUT результаты выполнения инструкции возвращаются в табличную переменную @MyTableVar . Так как столбец ScrapReasonID определен с помощью свойства IDENTITY , то значение для этого столбца не указано в инструкции INSERT . Однако обратите внимание, что значение, созданное ядро СУБД для этого столбца, возвращается в OUTPUT предложении в столбце INSERTED.ScrapReasonID .

DECLARE @MyTableVar table( NewScrapReasonID smallint, Name varchar(50), ModifiedDate datetime); INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE()); --Display the result set of the table variable. SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar; --Display the result set of the table. SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason;

Ф. Применение предложения OUTPUT со столбцами идентификаторов и вычисляемыми столбцами

В следующем примере создается таблица EmployeeSales , а затем в нее с помощью инструкции INSERT вставляется несколько строк, получаемых инструкцией SELECT из исходных таблиц. Таблица EmployeeSales содержит столбец идентификаторов ( EmployeeID ) и вычисляемый столбец ( ProjectedSales ). Так как эти значения создаются ядро СУБД во время операции вставки, ни в этих столбцах не могут быть определены @MyTableVar .

CREATE TABLE dbo.EmployeeSales ( EmployeeID int IDENTITY (1,5)NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, CurrentSales money NOT NULL, ProjectedSales AS CurrentSales * 1.10 ); GO DECLARE @MyTableVar table( LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, CurrentSales money NOT NULL ); INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales) OUTPUT INSERTED.LastName, INSERTED.FirstName, INSERTED.CurrentSales INTO @MyTableVar SELECT c.LastName, c.FirstName, sp.SalesYTD FROM Sales.SalesPerson AS sp INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID WHERE sp.BusinessEntityID LIKE '2%' ORDER BY c.LastName, c.FirstName; SELECT LastName, FirstName, CurrentSales FROM @MyTableVar; GO SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales FROM dbo.EmployeeSales;

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

В следующем примере производится отслеживание данных, возвращаемых предложением OUTPUT инструкции MERGE, а затем производится вставка этих данных в другую таблицу. Инструкция MERGE обновляет Quantity столбец ProductInventory таблицы ежедневно на основе заказов, обрабатываемых в SalesOrderDetail таблице в базе данных AdventureWorks2022. Инструкция также удаляет строки с продуктами, запас которых сократился до 0. В примере собираются удаленные строки и вставляются в другую таблицу, ZeroInventory , в которой ведется учет закончившихся продуктов.

--Create ZeroInventory table. CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime); GO INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate) SELECT ProductID, GETDATE() FROM ( MERGE Production.ProductInventory AS pi USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID AND soh.OrderDate = '20070401' GROUP BY ProductID) AS src (ProductID, OrderQty) ON (pi.ProductID = src.ProductID) WHEN MATCHED AND pi.Quantity - src.OrderQty

 Ц. Вставка данных с помощью параметра SELECT
 В следующем примере показано, как вставить несколько строк данных с помощью инструкции INSERT с параметром SELECT. Первая инструкция INSERT напрямую использует инструкцию SELECT для получения данных из исходной таблицы и сохранения результирующего набора в таблице EmployeeTitles .
 CREATE TABLE EmployeeTitles ( EmployeeKey INT NOT NULL, LastName varchar(40) NOT NULL, Title varchar(50) NOT NULL ); INSERT INTO EmployeeTitles SELECT EmployeeKey, LastName, Title FROM ssawPDW.dbo.DimEmployee WHERE EndDate IS NULL; 
 .X Указание метки с инструкцией INSERT
 В следующем примере показано использование метки с инструкцией INSERT.
 -- Uses AdventureWorks INSERT INTO DimCurrency VALUES (500, N'C1', N'Currency1') OPTION ( LABEL = N'label1' ); 
 Y. Использование метки и указания запроса с инструкцией INSERT
 Этот запрос показывает базовый синтаксис для использования метки и указания на соединение с запросом с инструкцией INSERT. После отправки запроса на узел Control SQL Server, работающий на вычислительных узлах, будет применять стратегию хэш-соединения при создании плана запросов SQL Server. Дополнительные сведения об указаниях по соединению и использованию предложения OPTION см. в разделе OPTION (SQL Server PDW).
 -- Uses AdventureWorks INSERT INTO DimCustomer (CustomerKey, CustomerAlternateKey, FirstName, MiddleName, LastName ) SELECT ProspectiveBuyerKey, ProspectAlternateKey, FirstName, MiddleName, LastName FROM ProspectiveBuyer p JOIN DimGeography g ON p.PostalCode = g.PostalCode WHERE g.CountryRegionCode = 'FR' OPTION ( LABEL = 'Add French Prospects', HASH JOIN); 
 Использование SQL для выборки данных из таблиц
 Глава 3. Использование SQL для выборки данных из таблиц Эта глава учит осуществлять поиск информации в таблицах, пропускать или пе реставлять столбцы, автоматически исключать избыточные данные при выводе ре зультата. Наконец, задавать условие - критерий, который можно применять для определения строк таблицы, используемых для вывода результирующих данных. С этой особенностью SQL более подробно мы ознакомимся в последующих главах. Формирование запроса SQL символизирует структурированный язык запросов (Structured Query Language). Запросы являются наиболее часто используемым аспектом SQL. Есть кате гория пользователей SQL, которые используют язык только для формулировки запро сов. Поэтому изучение SQL начинается с обсуждения запроса и того, как он выполняется в этом языке. Что такое запрос? Это команда, которая формулируется для СУБД и требует пре доставить определенную указанную информацию. Эта информация обычно выводит ся непосредственно на экран дисплея компьютера или используемый терминал, хотя в ряде случаев ее можно направить на принтер, сохранить в фай,1е или использовать в качестве исходных данных для другой команды или процесса. Как осуществляется связь за11росов? Запросы являются частью DML. Но так как они совершенно не изменяют инфор мации в таблицах, а лишь показывают ее пользователю, предположим, что запросы яв ляются самостоятельной категорией и определяют команды DML, воздействующие на содержимое базы данных, а не просто показывающие его. Все запросы в SQL конструируются на базе одной команды. Структура этой коман ды проста, потому что ее можно расширять для того, чтобы выполнить очень сложные вычисления и обработку данных. Эта команда называется SELECT. Команда SELECT В простейшей форме команда SELECT дает инструкцию базе данных для поиска информации в таблице. Например, можно получить таблицу Salespeop\e, введя с кла виатуры следующее: SELECT snum, sname, city, comm FROM Salespeople; Выходные данные для этого запроса представлены на рис. 3.1. Команда просто выводит все данные из таблицы. Большинство программ, как по казано выше, также выводит заголовки столбцов. Некоторые программы допускают
 Формирование запроса 
   р-- -  · -  --  -SOl Exccut1on loq----··-----  . - -  !  
  SllECT  snum  snamo. c1ty.  comm  
  I IROM Salespeople  .  
  ." .. 1001  . ",,  э.1.q,,1  
  Peel  London  0. 12  
  ".  Serres  San  Jose  0. 13  
  1002  
  1004  Motika  London  0. 11  
  1007  Rifkin  Barcelona  0. 15  
  101!3  Axelrod  New York  "'. 10  
 
  Рис. 3.1. Команда SELECT тщательное форматирование выходных данных, но это лежит за пределами специфи каций стандарта. Далее приводится объяснение каждой части этой команды:
 SELECT snum, sname ".
 Ключевое слово, которое сообщает базе данных, что команда является запросом. Все запросы начинаются с этого ключевого слова, за которым следует пробел. Список столбцов таблицы, которые должны быть представлены в результате выполнения запроса. Столбцы, имена которых не
   представлены в списке, не включаются в состав выходных дан  
  ных команды. Это, однако, не приводит к удалению из таблиц  
  таких столбцов или содержащейся в них информации, потому  
  что запрос не воздействует на информацию, представленную в  
  таблицах: он только извлекает данные.  
  FROM  FROM, так же как и SELECT, является ключевым словом, ко  
  Salespeople  торое должно быть представлено в каждом запросе. За ним  
  следует пробел, а затем - имя таблицы, которая используется  
  как источник информации для запроса. В приведенном приме  
  ре это таблица Salespeople.  
  Символ "точка с запятой"(;) используется во всех интерактив  
  ных командах SQL для сообщения базе данных, что команда  
  сформулирована и готова к выполнению. В некоторых системах  
 
 Глава 3. Использование SQL для выборки данных из таблиц
 этот символ заменен на символ "слэш обратный" ("\") в строке, которая непосредственно следует за концом команды. Стоит заметить, что запрос по своей природе не обязательно упорядочивает выход ные данные каким-либо определенным образом. Одна и та же команда, выполненная над одними и теми же данными в различные моменты времени, в результате выдает данные, упорядоченные по-разному. Обычно строки выдаются в том порядке, в кото ром они представлены в таблице, но этот порядок может быть совершенно произволь ным. Необязательно, что данные в результате выполнения запроса будут представлены в том порядке, в котором они вводятся или хранятся. Можно упорядо чить выходные данные непосредственно с помощью SQL-команд, указав специальное предложение. Позже будет объяснено, как это сделать. Сейчас же просто констатиру ем факт отсутствия какого-либо порядка в представлении выходных данных. Использование клавиши возврата каретки (клавиши Eпter) является произволь ным. Можно ввести запрос в одной строке следующим образом: SELECT snum, sname, city, comm FROM Salespeople; Поскольку в SQL точка с запятой применяется для того, чтобы пометить конец ко манды, большинство SQL-пporpaмм использует клавишу "Возврат каретки" (выпол няется нажатием клавиши Return или Enter) как пробел. Выбор чего-либо простейшим способом Если необходимо увидеть каждую колонку таблицы, существует упрощенный ва риант сделать это. Можно использовать символ "*" ("звездочка"), который заменяет полный список столбцов. SELECT • FROM Salespeople; Результат выполнения этой команды тот же, что и для рассмотренной ранее. SELECT в общем виде Обобщая предыдущие рассуждения, следует отметить, что команда SELECT на чинается с ключевого слова SELECT, за которым следует пробел. После него сле дует список разделенных запятыми имен столбцов, которые необходимо увидеть. Если нужно увидеть все столбцы таблицы, то можно заменить список имен столб цов символом (*) (звездочка). За звездочкой следует ключевое слово FROM, за ним - пробел и имя таблицы, к которой направляется запрос. Символ точка с запя той(;) нужно использовать для того, чтобы закончить запрос и показать, что коман да готова для выполнения.
 Формирование запроса Просмотр только определенных столбцов таблицы Мощность команды SELECT заключается в ее свойстве извлекать из таблицы лишь определенную информацию. Надо отметить возможность просмотра только указан ных столбцов таблицы. Для этого достаточно пропустить столбцы, которые нет необ ходимости просматривать, в части команды SELECT. Например, по запросу SELECT sname, comm FROM Salespeople; получаются выходные данные, представленные на рис. 3.2. Существуют таблицы, включающие большое количество столбцов, содержащих данные, не все из которых требуются в определенный момент. Следовательно, воз можность выбора и указания интересующих колонок весьма полезна. Рис. 3.2. Выбор определенных столбцов Перестановка столбцов Колонки таблицы упорядочены по определению, но это не значит, что их нужно из влекать в том же порядке. Звездочка (*) извлечет столбцы в соответствии с их поряд ком, но если указать столбцы раздельно, они выстраиваются их в любом желаемом порядке. В таблице Orders зададим такой порядок столбцов: сначала разместим стол бец "дата заказа" (odate), за ним - столбец "номер продавца" (snum), затем - "номер заказа" (onum) и "количество" (amt): SELECT odate, snum, onum, amt FROM Orders:
 Глава 3. Использоваиие SQL для выборки даниых из таблиц Выходные данные, полученные по этому запросу, представлены на рис. 3.3. Очевидно, что структура информации в таблицах является просто основой для ее реструктуризации средствами SQL. Устранение избыточных данных DISТINCT - аргумент, дающий возможность исключить дублирующиеся значе- Рис. 3.3. Переупорядоченные столбцы ния из результата выполнения предложения SELECT. Предположим, необходимо уз нать, какие продавцы имеют в настоящее время заказы в таблице Orders. Не имеет значения количество заказов каждого из продавцов, нужен лишь список номеров про давцов (snum). Необходимо ввести: SELECT snum FROM Orders; чтобы получить результат, представленный на рис. 3.4. Для того чтобы получить список без повторений, который легче прочесть, нужно ввести следующую команду: SELECT DISTINCT snum FROM Orders; Выходные данные для этого запроса представлены на рис. 3.5. DISTINCT отслеживает, какие значения появились в списке выходных данных, и исключает из него дублирующиеся значения. Это полезный способ исключить избы точные данные. Если таковых нет, не следует использовать D!SТINCT, поскольку он может скрыть проблемы. Предположим, все имена покупателей различны. Если кто-то
 Формирование запроса Р11с. 3.4. SELECT с повторениями Рис. 3.5. SELECT без повторений введет второго покупателя с фамилией Clemens в таблицу Customers при использова нии SELECT DISТINCT cname, можно не заметить, что имеются дублирующиеся дан ные. Будут получены ошибочные сведения о Clemens, поскольку в этом случае нет информации об избыточности данных.
 Глава 3. Использование SQL для выборки данных из таблиц Параметры DISТINCT. DISТINCT можно задать только один раз для данного предложения SELECT. Если SELECT извлекает множество полей, то он исключает строки, в которых все выбранные поля идентичны. Строки, в которых некоторые зна чения одинаковы, а другие - различны, включаются в результат. DISТINCT, факти чески, действует на всю выходную строку, а не на отдельное поле (исключение составляет его применение внутри агрегатных функций, см. главу 6), исключая воз можность их повторения. DISТINCT в сравнении с ALL. Альтернативой DISTINCT является ALL. Это ключевое слово имеет противоположное действие: повторяющиеся строки включа ются в состав выходных данных. Поскольку часто бывает так, что не заданы ни DISТINCT, ни ALL, предполагается ALL; это ключевое слово имеет преимущество перед функциональным аргументом. Определение выборки - предложение WHERE Таблицы бывают достаточно большими с тенденцией к увеличению по мере добав ления строк. В данный момент времени интересны только некоторые строки таблицы. SQL дает возможность задать критерий определения строк, которые следует включить в состав выходных данных. Предложение WHERE команды SELECT позволяет опре делить предикат, условие, которое может быть либо истинным, либо ложным для ка ждой строки таблицы. Команда извлекает только те строки из таблицы, для которых предикат имеет значение "истина". Предположим, необходимо узнать имена всех про давцов в Лондоне (London). В этом случае можно ввести следующую команду: SELECT sname, city FROM Salespeople WHERE city = 'London'; Лри наличии предложения WHERE программа обработки базы данных просматри вает таблицу строка за строкой и для каждой строки проверяет, истинен ли на ней пре дикат. Следовательно, для записи о продавце Рее/ программа просмотрит текущее значение в столбце city (город), определит, что оно равно 'London', и включит эту стро ку в состав выходных данных. Запись о продавце Serres не включается и т.д. Выход ные данные для приведенноm выше запроса представлены на рис. 3.6. Столбец city включен в результат не потому, что он указан в предложении WHERE, а потому, что имя этого столбца указано в предложении SELECT. Совершенно необя зательно, чтобы столбец, используемый в предложении WHERE, был представлен в числе тех столбцов, которые необходимо видеть среди выходных данных. Можно рассмотреть пример с использованием числового поля в предложении WHERE. Поле rating таблицы Custoшers предназначено для того, чтобы разделить по купателей на группы по некоторому критерию в соответствии с этим номером. Это
 О1tределение выборки - предло:ж:ение WHERE Рис. 3.6. SELECT с предложением WHERE своего рода оценка кредита или оценка, основанная на значении предыдущих поку пок. Такие цифровые коды могут быть полезны в реляционных базах данных как спо соб обобщения сложной информации. Можно выбрать всех покупателей (Customers) с рейтингом (rating) 100 следующим образом: SELECT * FROM Customers WHERE rating = 100; Здесь не используются одиночные кавычки, поскольку поле rating является число вым. Результат запроса представлен на рисунке 3.7. К предложению WHERE относятся все комментарии, сделанные в этой главе ра нее. Т.е. можно использовать номера столбцов, иск~1ючать повторяющиеся строки или переставлять столбцы в командах SELECT, использующих WHERE.
 Глава 3. Использование SQL для выборки данных из таблиц Рис. 3. 7. SELECT с числовым полем в предикате Ип~оги Мы выяснили, что существует несколько способов получения представленной в таблице информации в том виде, который вас интересует. Например, можно перестав лять или исключать столбцы таблицы, а также сохранять или исключать повторяю щиеся строки. И, наконец, наиболее важно то, что можно задать предикат, который определяет, вклю чается ли некоторая строка из множества строк в состав результирующих данных. Преди каты являются очень полезным инструментом, открывающим широкие возможности управления строками, которые должны войти в результат запроса. Именно это свойство предикатов и делает запросы SQL столь мощными. В следующих нескольких главах мы рассмотрим характерные черты и возможности предикатов. Глава 4 посвящена операто рам сравнения, отличным от равенства, которые можно использовать в условиях предика та, и способам комбинирования множества условий в единственный предикат.
 Работаем на SQL l. Запишите команду SELECT, которая выводит порядковый номер (ordernumber), количество (amount) и дату (date) для всех строк таблицы Order. 2. Запишите запрос, который выдает все строки таблицы Customers, где продавец имеет номер l 001. 3. Запишите запрос, который выдает строки таблицы salesperson в таком порядке: city, sname, snum, comm. 4. Запишите команду SELECT, которая выдает rating и следом за ним name каждого покупателя (customer), проживающего в San Jose. 5. Запишите запрос, позволяющий получить значения столбца snum для всех про давцов (salespeople), номера (orders) которых находятся в настоящее время в таб лице Orders, причем повторения требуется исключить. (Ответы представлены в прило:жении А.)
 Использование реляционных и булевых операторов для создания более слоJ1Сных предикатов
 Глава 4. Использованиереляционных и булевых операторов Из главы 3 выяснилось, что предикаты могут приписывать предложениям с ра венством значения "истина" или "ложь", а также оценивать операторы сравнения от личные от равенства. В этой главе рассмотрены и другие операторы сравнения, применяемые в SQL, и показано, как можно использовать булевы операторы для из менения и комбинирования значений предиката. В булевом выражении единственный предикат может содержать любое количество условий, что позволяет получить очень мощные предикаты. Здесь также объясняется применение круглых скобок для струк турирования сложных предикатов. Реляционные операторы Реляционный оператор - это математический символ, который задает определен ный тип сравнения между двумя значениями. Уже известно как применяются равенст ва, такие как 2 + 3 = 5 или city = 'London'. Однако существуют и другие операторы сравнения. Предположим, необходимо вычислить продавцов (Salespeople), комисси онные (commissioпs) которых превышают заданное значение. В этом случае следует воспользоваться сравнением типа "больше или равно". SQL распознает следующие операторы сравнения: Равно > Больше, чем < Меньше, чем >= Больше и.1и равно <= Меньше или равно <>Неравно Эти операторы имеют стандартное значение для числовых величин. Их определе ние для символьных значений зависит от используемого формата представления (ASCII или EBCDIC). SQL сравнивает символьные значения в терминах соответст вующих чисел, определенных в формате преобразования. Символьные значения, представляющие числа, например, 'l', необязательно равны тому числу, которое они представляют. Операторы сравнения можно применять для того, чтобы представить алфавитный порядок; например, 'а' < 'п' означает, что 'а' предшествует 'п' в алфавитном порядке, но эта процедура ограничена параметрами формата преобразования. Как в ASCII, так и в EBCDIC, сохранен алфавитный порядок предшествования символов, представленных в одном и том же регистре. В ASCII все заглавные символы меньше, чем все строчные, значит 'Z' 
 Реляционные операторы Значения, которые здесь сравниваются, называются скалярными зиачеиия.11и. Ска лярные значения получаются из скалярных выражений: 1 + 2 является скалярным вы ражением, которое дает скалярное значение 3. Скалярные значения могут быть символами или числами, хотя только числа используются с арифметическими опера торами, такими как + или *. Предикаты обычно сравнивают скалярные значения, ис пользуя операторы сравнения или специальные SQL-операторы, для того, чтобы проверить является ли результат сравнения истинным. Некоторые SQL-операторы рассмотрены в главе 5. Предположим, необходимо увидеть всех покупателей (Customeгs) с рейтингом (rating) более 200. Поскольку 200 - это скалярное значение, как и все значения столб ца rating, для их сравнения можно использовать оператор отношения: SELECT • FROM Customers WHERE rating > 200; Выходные данные для этого запроса представлены на рис. 4.1. При необходимости увидеть всех покупателей, рейтинг (гating) которых больше или равен 200, следовало бы использовать предикат: rating >= 200 Рис. 4.1. Использование "больше, чем" (>>
 Глава 4. Использованиереляционных и булевых операторов Булевы операторы SQL распознает основные булевы операторы. Булевы выражения - это те выраже ния, относительно которых, подобно предикатам, можно сказать, истинны они или ложны. Булевы операторы связывают одно или несколько значений "истина/ложь" и в результате получают единственное значение "истина/ложь". Стандартные булевы опе раторы, распознаваемые SQL, - это AND, OR, NОТ. Существуют и другие, более сложные булевы операторы (как, например, "исключающее ИЛИ"), но их можно по строить с помощью трех простых. Булева логика "истина/ложь" представляет собой полный базис для работы цифрового компьютера. Поэтому фактически весь SQL (или какой-либо другой язык программирования) можно свести к булевой логике. Далее пе речислены булевы операторы и основные принципы их действия: • AND берет два булевых выражения (в виде А AND В) в качестве аргументов и дает в результате истину, если они оба истинны. • OR два булевых выражения (в виде А OR В) в качестве аргументов и оценивает результат как истину, если хотя бы один из них истинен. • NOT берет единственное булево выражение (в виде NOT А) в качестве аргу мента и изменяет его значение с истинного на ложное или с ложного на истин ное. Используя предикаты с булевыми операторами, можно значительно увеличить их избирательную мощность. Предположим, необходимо увидеть всех покупателей (customers) из San Jose, чей рейтинг (rating) превышает 200: SELECT • FROM Customers WHERE city = 'San Jose' AND rating > 200; Выходные данные для этого запроса представлены на рис. 4.2. Существует только один покупатель, удовлетворяющий этому условию. При испоj1ьзовании OR , будут получены сведения обо всех тех покупателях (custoшers>, которые либо проживают в San Jose, либо имеют рейтинг (rating>, превы шающий 200. SELECT • FROM Customers WHERE city = ·san Jose· OR rating > 200; Результат выполнения этого запроса представлен на рис. 4.3.
 Булевы операторы Рис. 4.1. SELECT с использованием AND Рис. 4.3. SELECT с использованием OR NOT дает возможность получить отрицание (противоположное значение) булева выражения. Вот пример запроса с использованием NOT: SELECT * FROM Customers WHERE city = ·san Jose· OR NOT rating > 200;
 Глава 4. Использованиереляц1101111ых и булевых операторов
 Рис. 4.4. SELECT с использованием NOT Результат выполнения этого запроса представлен на рис. 4.4. Все записи, за исключением Grass, были выбраны. Grass не находится в San Jose и его рейтинг превышает 200, таким образом он не удовлетворяет обоим условиям. Каж дая из других строк удовлетворяет либо первому, либо второму условию (либо каждому из них). Заметим, что оператор NOT должен предшествовать булеву выражению, значе ние которого он должен изменить, но не может располагаться непосредственно перед оператором сравнения, как это можно сделать во фразе на английском языке. Таким об разом некорректно вводить rating NOT > 200 в качестве предиката, несмотря на то, что эту фразу можно легко сформулировать по анrлийски. Отсюда следует ряд проблем. Например, как SQL оценит следующее? SELECT * FROM Customers WHERE NOT city = ·san Jose· OR rating > 200; Применяется ли NOT к выражению city = 'San Jose' или к двум выражениям: тому, что указано, и выражению rating > 200? В соответствии с приведенной записью пра вильным является первый вариант. SQL применяет NOT только к тому булеву выраже нию, которое непосредственно следует за ним. Можно получить другой результат по следующей команде: 
   SELECT *  
  FROM  Customers  
  WHERE  NOT (city  ·san Jose'  
 
 Булевы 011ераторы OR rating > 200); SQL понимает круглые скобки следующим образом: все то, что расположено внут ри крутлых скобок, вычисляется прежде всего и рассматривается как единственное выражение по отношению к тому, что расположено за пределами крутлых скобок (это соответствует стандартной интерпретации в математике). Другими словами, SQL из влекает каждую строку и определяет, выполняется ли для нее условие city = 'San Jose' или rating > 200. Если одно из этих выражений истинно, то булево выражение, распо ложенное в круглых скобках, тоже истинно. Однако, если булево выражение в круглых скобках истинно, пре;:щкат в целом ложен, поскольку NOT превращает истину в ложь и наоборот. Результат выполнения этого запроса представлен на рис. 4.5. Вот преднамеренно усложненный пример. Проследим его логику (результат вы полнения запроса представлен на рис. 4.6): Рис. 4.5. SELECT с использованием NOT и круглых скобок SELECT • FROM Orders WHERE NOT((odate = 10/03/1990 AND snum > 1002) OR amt > 2000.00); Комбинации булевых операторов в сложных выражениях не столь просты, как каж дый из в отдельности. Способ оценки сложного булева выражения следующий: оце нить булево(ы) выражение(ия), имеющее(ие) наибольшую глубину вхождения в круглые скобки, скомбинировать результаты в одно булево выражение, а затем связать его значение со значениями выражений, имеющих меньшую глубину вхождения в круглые скобки.
 Глава 4. Использованиереляционных и булевых операторов Дадим детальное объяснение оценки рассмотренного выше примера. Наибольшую глубину вхождения в булево выражение имеет предикат: odate = 10/03/1990 and snum > 1002, со связкой AND, образующий булево выражение, которое оценивается как ис тинное для всех тех строк, которые удовлетворяют каждому из этих условий. Это со ставное булево выражение (которое мы назовем булево выражение номер 1 или, для краткости, Bl) соединено с amt > 2000.00 (выражение В2) с помощью OR и образует третье выражение (В3), которое является истинным для данной строки в том случае, если либо Bl либо В2 истинны для этой строки. В3 полностью содержится в круглых скобках, которым предшествует NOT, и образует заключительное булево выражение (В4), которое является условием предиката. Следовательно, В4 - предикат запроса - истинен, если В3 ложен и наоборот. ВЗ ложен, если ложен каждый из Bl и В2. Bl ло жен для строк, в которых либо order date не совпадает с заданным значением 10/03/1990, либо значение snum не превышает 1002. В2 ложен для всех строк, в кото рых значение поля amount не превосходит 2000.00. Любая строка с суммой, превы шающей 2000.00, делает В2 истинным, отсюда В3 тоже истинно, а 84 - ложно. Следовательно, все такие строки исключаются из числа выходных данных. Остаю щиеся строки от 3 октября 1990 года с snum, превышающим 1002 (такой, например, является строка с onum 3001 за октябрь, 3, 1990 с snum 1007), делают В! истинным, следовательно, и В3 истинно, а значит предикат ложен. Эти записи также исключают ся из рассмотрения. Оставшиеся строки входят в состав выходных данных (см. рис. 4.6). Рис. 4.6. Сложный запрос
 Итоги Итоги В этой главе более полно представлены сведения из области предикатов. Показано, как можно найти значения, которые связаны с данным значением любым количеством способов, заданных с помощью различных реляционных операторов, как применяют ся булевы операторы AND и OR для комбинации сложных условий, каждое из кото рых может рассматриваться как единственный предикат. Булев оператор NOT изменяет на противоположное значение условия или группы условий. Все булевы вы ражения и операторы отношения управляются с помощью круглых скобок, которые определяют порядок выполнения операции. Эти операции могут иметь любой уровень сложности. Было рассмотрено, как можно разложить записанное сложное выражение на составные части, каждая из которых является простой. В главе 5 будут представлены особые операторы языка SQL.
 Работаем на SQL 1. Запишите запрос, который покажет все заявки, превышающие $1,000. 2. Запишите запрос, который покажет имена (names) и названия городов (cities) для всех продавцов в London с комиссионными (commission), превышающими .10. 3. Запишите запрос для таблицы Customers, включающий в выходные данные всех покупателей, для которых rating <= 100, в том случае, если они расположены не в Rome. 4. Каков будет результат выполнения следующего запроса? SELECT * FAOM Orders WHEAE (amt < 1000 ОА NOT (odate = 10/03/1990 AND cnum >2003)); 5. Каков будет результат выполнения следующего запроса? SELECT * FAOM Orders WHEAE NOT((odate = 10/03/1990 ОА snum > 1006) AND amt > = 1500); 6. Как упростить запись следующего запроса? SELECT snum,sname,city,comm FAOM Salespeople WHERE (comm > +.12 OR comm < .14); (Ответы см. в 11риложении А.)
 Использование специальных операторов в "условиях,,
 Глава 5. Ис11ользова11ие специальных 011ераторов в "условиях" Кроме булевых операторов и операторов сравнения, рассмотренных в главе 4, SQL использует специальные операторы IN, BETWEEN, LIKE и IS NULL. Вы научи тесь применять их, подобно операторам сравнения, для получения более выразитель ных и мощных предикатов. Обсуждение IS NULL касается значений пропускаемых данных и NULL-значений, фиксирующих отсутствие данных. OnepamoplN IN полностью определяет множество, которому данное значение может принадле жать или не принадлежать. Если нужно найти всех продавцов, расположенных либо в 'Barcelona', либо в 'London', основываясь только на том, что известно к настоящему моменту, необходимо написать следующий запрос (выходные данные для него пред ставлены на рис. 5.1 ): SELECT • FROM Salespeople WHERE city = ·вагсеlоnа· 
   OR city  · London · ;  
 
  Однако существует более простой способ получить ту же самую информацию: SELECT • FROM Salespeople WHERE city IN ('Barcelona·, 'London'); 
   11 Sl-ll ~----------  SUL lxeCL1tJon Log- ~-~~ ___ l-:  
  1 1 f НОМ  S,11 ""Р"ОР] '-'  1  
  'IW11lH[  c1ty  - ·uarcelona'  
  1 011 с 1 t у  'l oпdon' .  London • ••  
  jjFIШ  э.1"1,,1  
  1001  Peel  0. 12  
  101114  Motika  London  0. 11  
  .""" 1007 . Rifkin  Barcelona  0. 15  
 
  Рис. 5.1. Поиск продавцов (Salespeople) в городах Barselona или London
 Оператор BETWEEN 
   1 -  -  - ---------oOL fxcc11t1or1 Lоц0  ~ ___ 1,  
  , . 1L1 С f  *  1  
  1' 1 f/ОГ~  . J lf,spr 0 op] е  
  WHll!l  c1ty  IN (  lJar·celoпd', Lоnсюп')  1  
  м1111"9  Peel  51ыш  э.1 11 ;,м  
  1001  0. 12  
  1004  Motika  0.11  
  1007  Rifkin  0. 15  
  ••~~~-вrowse :  ti~+  
 
  Рис. 5.2. SELECT с использованием IN Выходные данные этого запроса представлены на рис. 5.2. Как видно из примера, IN определяет множество, элементы которого точно пере числяются в круглых скобках и разделяются запятыми. Если в поле, имя которого ука зано слева от IN, есть одно из перечисленных в списке значений (требуется точное совпадение), то предикат считается истинным. Если элементы множества имеют чи словой, а не символьный тип, то одиночные кавычки непосредственно слева и справа от значения необходимо опустить. Можно найти всех покупателей, обслуживаемых продавцами 1001, 1007, 1004. Выходные данные для следующего запроса представле ны на рис. 5.3: SELECT • FROM Customers WHERE snum IN (1001, 1007, 1004); Оператор BETWEEN Оператор BETWEEN сходен с IN. Вместо перечисления элементов множества, как это делается в IN, BETWEEN задает границы, в которые должно попадать значение, чтобы предикат был истинным. Используется ключевое слово BETWEEN, за которым следуют начальное значение, ключевое слово AND и конечное значение. Также как и IN, BETWEEN чувствителен к порядку: первое значение в предложении должно быть первым в соответствии с алфавитным или числовым порядком. (В отличие от англий ского языка в SQL не говорят: значение расположено между ("is BETWEEN") значени-
 Глава 5. Использование специальных операторов в "условиях" rrsu-EC ;---• ~~-~~-SQL l:.xecutron Log~-~~--~ __ i
   'FROM Customers  . .. "_ MiifЩM  
  WfHRfsrшmIN(HHJ1  HHJI  1004)  
  MЭll!l,,M ми;;;;,1  
  2001  Hoffman  London  100  1001  
  2006  Clemens  London  100  1001  
  2008  Cisneros  San  Jose  300  1007  
  -··  
  2007  Pereira  Rome  100  1004  
 
 Рис. 5.3. SELECT с использованием IN с числовыми значениями 
   rs;-L~CT * ~--------SOL Exccutron 109°~-~~- ~  ~ ~ ~ 1 ]  
  llFROM  Salespeople  11  
  WH[RE  comm  BElWElN 10 AND 12.  ,  
  MilJIШM  е1р1ш  э.1 11 1.11  
  1001  0. 12  
  1004  0. 11  
  1003  0.10  
 
  Рис. 5.4. SELECT с использованием BETWEEN ем и значение11, но просто значение л1ежду ("BETWEEN") значением и значение~~. Это замечание справедливо и для оператора LIKE.) Следующий запрос позволит из влечь из таблицы Sa\espeople всех продавцов (salespeople), комиссионные которых имеют величину в диапазоне .10 и .12 (выходные данные представлены на рис. 5.4): SELECT • FROM Salespeople
 Оператор BETWEEN WHERE comm BEТWEEN .10 AND .12; Оператор BETWEEN является включающим, т.е. граничные значения (в данном примере это .1 О и .12) делают предикат истинным. SQL непосредственно не подцер живает исключающий BETWEEN. Необходимо сформулировать граничные значения так, чтобы включающая интерпретация была справедлива, либо сделать примерно следующую запись: SELECT * FROM Salespeople WHERE (comm BEТWEEN . 10, AND . 12) AND NOT comm IN ( .10, .12); Выходные данные для этого запроса представлены на рис. 5.5. Пусть эта запись и неуклюжа, но она показывает, как новые операторы можно ком бинировать с булевыми операторами для получения более сложных предикатов. Зна чит, IN и BETWEEN используются, как и операторы сравнения, для сопоставления значений, одно из которых является множеством (для IN) или диапазоном (для BETWEEN). Аналогично всем операторам сравнения, BETWEEN действует на символьных по лях, представленных в двоичном (ASCII) эквиваленте, т.е. для выборки можно вос пользоваться алфавитным порядком. Следующий запрос выбирает всех покупателей имена которых попадают в заданный алфавитный диапазон: SELECT • FROM Customers WHERE cname BEТWEEN ·д· AND 'G'; Выходные данные для этого запроса представлены на рис. 5.6. 
   , 1  ----  ~-  -~SOl lxecLJt1on log~--~- -  ,  
  ,  Sf ll С f •  '  
  1  
  1 liO"l  0,,1! •~ре "Р 1 ,,  
  Wlll  
 (имn 11' TWI  ГN  1С дNI, 1,)  1  
  д NlJ NO 1 r ornni 1 r,  (  10  1 , )  
  11/Jl/J4 Мotika  
  ---Browse : t.i.  
  ." i*i!il"*  """  
 
  Рис. 5.5. Выполнение исключающего BETWEEN
 Глава 5. Использование спеt(иальных операторов в "условиях" Рис. 5.6. Использование BETWEEN с выборкой в алфавитном порядке Grass и Giovanni опущены несмотря на то, что BETWEEN является включающим, так как он сравнивает строки неравной длины. Строка 'G' короче строки 'Giovanni', по этому BETWEEN дополняет 'G' пробелами. Пробелы предшествуют символам латин ского алфавита (в большинстве реализаций), поэтому Giovanni оказался невыбранным. Аналогично и для Grass. Помните об этом при использовании BETWEEN с алфавитными диапазонами. Для включения в результат выполнения за проса сведений о покупателях, фамилии которых начинаются на 'G', нужно указать следующую букву алфавита ('Н') или приписать символ 'z' (несколько символов 'z', если это необходимо) после второго граничного значения. Оператор LIКE LIKE применим только к полям типа CHAR или VARCHAR, поскольку он исполь зуется для поиска подстрок. Другими словами, он осуществляет просмотр строки для выяснения: входит ли заданная подстрока в указанное поле. С этой же целью исполь зуются шаблоны - специальные символы, которые могут обозначать все, что угодно. Существует два типа шаблонов, используемых с LIКE: • Символ "подчеркивание" (_) заменяет один любой символ. Например, образцу 'Ь_t' соответствуют 'Ьаt' или 'Ьit', но не соответствует 'Ьrat'.
 О11ератор LIKE • Символ "процент" (%) заменяет последовательность символов произвольной длины, в том числе и нулевой. Например, образцу '%p%t' соответствуют 'put', 'posit', 'opt', но не 'spite'. Можно найти покупателей, фамилии которых начинаются на 'G' (выходные дан ные, соответствующие этому запросу, представлены на рис. 5.7): Рис. 5. 7. SELECT с использованием LIКE с символом % SELECT * FROM Customers WHERE cname LIKE • GX.; LIКE может оказаться полезным при осуществлении поиска имени или другого значения, полное написание которого неизвестно. Предположим, не совсем понятно, как правильно записывается фамилия одного из продавцов (sa\espeop\e): Реа\ или Рее\. Можно использовать ту часть, которая известна, и символы шаблона для нахождения всех возможных вариантов (выходные данные для этого запроса представлены на рис. 5.8): SELECT * FROM Salespeople WHERE sname LIKE • р - _в. ; Каждый символ подчеркивания в шаблоне представляет единственный символ, по этому, например, имя Prettel не вошло бы в состав выходных данных. Символ шаблона (%) в конце строки необходим в тех реализациях SQL, в которых длина поля sname пре восходит количество букв в имени Рее\ (здесь это очевидно, потому что другие значения превышают четыре символа). В таком случае значение поля sпame реально хранится как
 Глава 5. Использование специальных операторов в "условиях" 
   и----  ~~-~- -SQL lxeCIJclOП Log-~---  ~  
  llSEL[CT  •  
  FROM Salespeople  1  
  1 \IHERE  sname LIK[ 'Р 1%'.  
 
  1•i81ff 11M g;ыш Рис. 5.8. SELECT с использованием LIКE с_ (символом подчеркивания) Рее!, за ним следует ряд пробелов. Следовательно, символ '1' не является последним в строке. Символ(%) в шаблоне заменяет все пробелы. Все вышеперечисленное не отно сится к полю sname типа VARCHAR. Чтобы найти в строке символ подчеркивания или процента, в предикате LIKE лю бой символ можно определить как Еsсаре-символ. Он используется в предикате непо средственно перед символом процента или подчеркивания и означает, что следующий за ним символ интерпретируется именно как обычный символ, а не как символ шабло на. Например, поиск символа подчеркивания в столбце sname можно задать следую щим образом: SELECT • FROM Salespeople WHERE sname LIKE 'Х/_Х' ESCAPE'/.; Для тех данных, которые хранятся в таблице в текущий момент времени, выходных данных нет, поскольку в именах продавцов нет подчеркиваний. Предложение ESCAPE определяет '/' как Еsсаре-символ, который используется в LIКЕ-строке, за ним следуют символ процента, символ подчеркивания или сам символ'/', т.е. тот символ, поиск которо го будет осуществляться в столбце и который уже не интерпретируется как символ шаб лона. Еsсаре-символ может быть единственным символом и применяться только к единственному символу, который следует за ним. В приведенном примере начальный и конечный символы процента являются символами шаблона, только символ подчеркива ния представляет собой символ как таковой. Еsсаре-символ может использоваться и в своем собственном значении. Другими словами, если нужно найти в столбце Еsсаре-символ, то его необходимо ввести дваж ды. Первый раз он действует как обычный Еsсаре-символ и означает: "следующий
 Оператор IS NULL символ надо понимать буквально так, как он указан", а второй раз указывает на то, что речь идет непосредственно об Еsсаре-символе. Далее представлен пример поиска строки '_>' в столбце sname: SELECT • FROM Salespeople WHERE sname LIKE 'Х/_//Х' ESCAPE. /'; В этом случае выходных данных нет. Просматриваемая строка состоит из любой последовательности символов(%), за которыми следуют символ подчеркивания(/_), Еsсаре-символ (//)и любая последовательность заключительных символов(%). Работа с NULL-значениями Часто в таблице встречаются записи с незаданными значениями какого-либо из по лей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL по зволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-либо значения для данной строки (записи). Дело обстоит не так в случае простого приписывания полю значения "нуль" или "пробел", которые база данных трактует как любое другое значе ние. Поскольку NULL не является значением как таковым, он не имеет типа данных. NULL может размещаться в поле любого типа. Тем не менее, NULL, как NULL- значение, часто используется в SQL. Предположим, появился покупатель, которому еще не назначен продавец. Чтобы констатировать этот факт, нужно ввести значение NULL в поле snum, а реальное зна чение включить туда позже, когда данному покупателю будет назначен продавец. Оператор IS NULL Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с лю бым значением, даже с NULL-значением, результат просто неизвестен. Булево значе ние "неизвестно" ведет себя также, как "ложь" - строка, на которой предикат принимает значение "неизвестно", не включается в результат запроса - при одном важном исключении: NOT от лжи есть истина (NOT (false)=true), тогда как NOT от не известного значения есть также неизвестное значение. Следовательно, такое выраже ние как "city = NULL" или "city IN (NULL)" является неизвестным независимо от значения city. Часто необходимо различать false и unknown - строки, содержащие значения столбца, не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения.
 Глава 5. Использование с11ециаль11ых операторов в "условиях"
 Для нахождения всех записей со значениями NULL в таблице Customers в столбце city следует ввести: SELECT • FROM Customers WHERE city IS NULL; В данном случае выходных данных не будет, поскольку в конкретных простых таб лицах нет NULL-значений. NULL-значения чрезвычайно важны, поэтому имеет смысл вернуться к ним позже. Использование NOT со специшzьными 01~ераторами Специальные операторы, которые были рассмотрены в этой главе, могут непосред ственно предшествовать булеву оператору NOT. Этим они отличаются от операторов сравнения, которые должны содержать NOT перед всем выражением. Например, если не осуществляется поиск NULL-значений, а, напротив, необходимо исключить их из выходных данных, то нужно использовать NOT для того, чтобы придать предикату противоположное значение: SELECT • FROM Customers WHERE city IS NOT NULL; Если NULL-значения отсутствуют (в данном случае это именно так), то в результа те выполнения этого запроса будет получена вся таблица Customers, что эквивалентно вводу: SELECT • FROM Customers WHERE NOT city IS NULL; что тоже приемлемо. Можно также использовать NOT и IN: SELECT • FROM Salespeople WHERE city NOT IN ('London', ·san Jose'); Другой способ выразить то же самое: SELECT • FROM Salespeople WHERE NOT city IN ( 'London' , 'San Jose'); Выходные данные для этого запроса представлены на рис. 5.9.
 Использование NOT с IN
 Аналогичным образом можно использовать NOT BETWEEN
 Итоги 
   SQl  lcxecut1on Log-- --  - -- -~  
  ст  *  11  
  Salespeople  
  WHERE  c1t  NOl IN ('London','San Josc'):  
  WilifШM  i1#iШ-  .  э.1,,r, 8 1  
  HIHIJ7  Ri fi  
 Barcelona  ". 15  
  1011!3  Axelrod  0. 111!  
 
  Рис. 5.9. и NOT LIKE. Итоги Теперь вы научились конструировать предикаты в терминах отношений, специаль но определенных для SQL, искать значения в определенном диапазоне (BETWEEN) или значения, принадлежащие определенному множеству (IN), искать символьные значения, удовлетворяющие заданному символьному шаблону (LIКE). Вы поняли, как SQL реагирует на пропуски данных ( вполне реальная ситуация для мира баз данных) с помощью NULL-значений. Вы умеете извлекать NULL-значения или исключать их из выходных данных, применяя оператор IS NULL (или IS NOT NULL). Полный набор стандартных математических функций и специальных опера торов позволяет переходить к специальным функциям SQL, которые оперируют целы ми группами, а не на единичными значениями. Этому посвящена глава 6.
 Работаем на SQL 1. Запишите два запроса, которые выдают сведения о всех заявках, принятых 3 или 4 октября 1990 года. 2. Запишите запрос, который выбирает всех покупателей, обслуживаемых Рее! или Motika. (Подсказка: поле snum связывает две таблицы друг с другом.) 3. Запишите запрос, который выбирает всех покупателей, имена которых начина ются на любую из букв от 'А' до 'G'. 4. Запишите запрос, который выбирает всех покупателей, имена которых начина ются на 'С'. 5. Запишите запрос, который выбирает все заявки, у которых в поле amt (amount) указано значение О или NULL. (Ответы см. в 11риложении А.)
 6 8J П []] ~ D D u Суммирование данных с помощью функций агрегирования
 Глава 6. Суммирование да11ных с помощью функций агрегирования В этой главе осуществляется переход к более сложным запросам на извлечение значений из базы данных и получение информации о базе данных, основываясь на этих значениях. Это делается с помощью функций агрегирования и суммирования, которые группируют значения поля и сводят их к единственному значению. Вы узнае те, как применять эти функции, как определять группы значений, для которых они применимы и какие группы выбираются в качестве выходных данных, при каких ус ловиях можно комбинировать значения полей с этой производной информацией в единственном запросе. Что такое функции агрегирования? Запросы могут обобщать не только группы значений, но и значения одного поля. Для этого применяются агрегатные функции. Они дают единственное значение для целой группы строк таблицы. Ниже приводится список этих функций: • COUNT определяет количество строк или значений поля, выбранных посредст вом запроса и не являющихся NULL-значениями; • SUM вычисляет арифметическую сумму всех выбранных значений данного поля; • А VG вычисляет среднее значение для всех выбранных значений данного поля; • МАХ вычисляет наибольшее из всех выбранных значений данного поля; • MIN вычисляет наименьшее из всех выбранных значений данного поля. Как используются функции агрегирования? Функции агрегирования используются как имена полей в предложении запроса SELECT с одним исключением: имена полей применяются как аргументы. Для SUM и AVG могут использоваться только цифровые поля. Для COUNT, МАХ и MIN - циф ровые и символьные поля. При употреблении с символьными полями МАХ и MIN применяются к АSСII-эквивалентам: MIN предполагает минимальное (первое), а МАХ - максимальное (последнее) значения в соответствии с алфавитным порядком (более детально алфавитное упорядочение рассмотрено в главе 4). Чтобы найти сумму (SUM) всех заявок из таблицы Orders, можно ввести следую щий запрос, выходные данные для которого представлены на рис. 6.1 : SELECT SUM(amt) FROM Orders;
 Что такое функции агрегирования? Рис. 6.1. Выбор суммы Такая операция существенно отличается от выбора поля тем, что выходные данные содержат единственное значение независимо от количества строк в таблице. По этой причине агрегатные функции и поля не могут выбираться одновременно, если только не используется предложение GROUP ВУ. Похожей операцией является поиск среднего значения (выходные данные для сле дующего запроса представлены на рис. 6.2): SELECT AVG(amt) FROM Orders; Рис. 6.2. Выбор среднего значения
 Глава 6. Суммирование данных с помощью функций агрегирования Специальные атрибуты в COUNT Функция COUNT отличается от предыдущих тем, что подсчитывает количество значений в данном столбце или количество строк в таблице. Когда подсчитываются значения по столбцу, в команде используется DISTINCT для подсчета числа различ ных значений данного поля. Можно использовать его, например, для подсчета количе ства продавцов, имеющих в настоящее время заказы в таблице Orders (выходные данные представлены на рис. 6.3): SELECT COUNT (DISTINCT snum) FROM Orders; Использование DISТINCT. В данном примере DISТINCT вместе со следующим за ним именем поля, к которому он применяется, заключен в круглые скобки и не следует непосредственно за SELECT, как это было в примере главы 3. Такая форма применения DISТINCT с COUNT к отдельным столбцам предписывается стандартом ANSI, но многие программы не придерживаются этого требования. Можно использо вать множество COUNT для DISTINCT полей в одном запросе; этот случай, рассмот ренный в главе 3, отличается от случая применения DISTINCT к строкам. Указанным способом DISТINCT можно применять с любой функцией агрегирова ния, но чаще всего он используется с COUNT. Применение его с МАХ и MIN беспо лезно; а используя SUM и AVG, необходимо включение в выходные данные повторяющихся значений, так как они влияют на сумму и среднее для значений всех столбцов. Использование COUNT со строками, а не со значениями. Для подсчета об щего количества строк в таблице следует использовать функцию COUNT со звездоч- Рис. 6.3. Подсчет количества значений поля
 Что такое функции агрегирования? Рис. 6.4. Подсчет количества строк, а не значений поля кой вместо имени поля так, как показано в следующем примере, выходные данные для которого представлены на рис. 6.4: SELECT COUNT (•) FROM Customers; COUNT со звездочкой включает как NULL-значения, так и повторяющиеся зна чения, значит DISTINCT в этом случае не применим. По этой причине в результате получается число, превышающее COUNT для отдельного поля, который исключает из этого поля все избыточные строки или NULL-значения. DISTINCT исключен для COUNT(*), поскольку он не имеет смысла для хорошо спроектированной и управляемой базы данных. В такой базе данных не должно быть ни строк, содержа щих в каждом поле только NULL-значения, ни полностью повторяющихся строк (поскольку первые не содержат никаких данных, а последние полностью избьпоч ны). С другой стороны, если имеются избыточные или содержащие одни NULL- значения строки, то нет необходимости применять COUNT для избавления от этой информации. Использование дубликатов в агрегатных функциях. Агрегатные функции могут также (во многих реализациях) иметь аргумент ALL, который размещается перед именем поля, как и DISTINCT, но обозначает противоположное: включить дубликаты. Требования ANSI не допускают подобного для COUNT, но многие реа лизации игнорируют это ограничение. Различие между ALL и * при использовании COUNT заключается в следующем: • ALL использует имя поля в качестве аргумента; • ALL не подсчитывает NULL-значения.
 Глава 6. Суммирование данных с помощью функций агрегирования
 Поскольку * является единственным аргументом, который включает NULL- значения и используется только с COUNT, функции, отличные от COUNT, игнориру ют NULL-значения в любом случае. Следующая команда осуществляет подсчет коли чества значений поля rating, отличных от NULL-значений, в таблице Customers (включая повторения): SELECT COUNT (ALL rating) FROM Customers; Агрегаты, построенные на скалярных выражениях До сих пор были использованы агрегатные функции с одним полем в качестве ар гумента. Можно использовать агрегатные функции с аргументами, которые состоят из скалярных выражений, включающих одно поле или большее количество полей. (При этом не разрешается применять DISТINCT.) Предположим, таблица Orders содержит дополнительный столбец с величиной предыдущего баланса (Ыnс) для каждого поку пателя. Можно найти текущий баланс, добавив значение поля amount (amt) к значению поля Ь!nс. Можно найти наибольшее значение текущего баланса: SELECT МАХ (binc + amt) FROM Orders; В процессе выполнения этого запроса для каждой строки таблицы выполняется сложение значений двух указанных полей записи и выбирается наибольшее из полу ченных значений. Конечно, поскольку покупатели могут иметь несколько заказов, их окончательный баланс в данном случае оценивается отдельно для каждого заказа. Предполагается, что последняя заявка имеет наибольшее значение баланса данного покупателя. В противном с.1учае в предыдущем примере мог быть выбран старый ба ланс. В SQL можно часто использовать скалярное выражение вместе с полями или вместо них. Предложение GROUP ВУ Предложение GROUP ВУ позволяет определять подмножество значений отдельно го поля в терминах другого поля и применять функции агрегирования к полученному подмножеству. Это дает возможность комбинировать поля и агрегатные функции в од ном предложении SELECТ. Например, предположим, что нужно найти наибольший заказ из тех, что получил каждый из продавцов. Можно сделать отдельный запрос на каждого продавца, выбрав МАХ (amt) для таблицы Orders для каждого значения поля snum и используя GROUP ВУ, однако, возможно объединить все в одной команде: SELECT snum, MAX(amt) FROM Orders GROUP ВУ snum; Выходные данные для этого запроса представлены на рис. 6.5.
 Что такое фу11к14ии агрегирова11ия? Рис. 6.5. Подсчет максимального количества (amounts) для каждого продавца (salesperson) GROUP ВУ применяет агрегатные функции отдельно к каждой серии групп, кото рые определяются общим значением поля. В данном случае каждая группа состоит из всех тех строк, которые имеют одно и то же значение snum, а функция МАХ применя ется отдельно к каждой такой группе. Это означает, что поле, к которому применяется GROUP ВУ по определению имеет на выходе только одно значение на каждую группу, что соответствует применению агрегатных функций. Такая совместимость результа тов и позволяет комбинировать агрегаты с полями указанным способом. Можно также применять GROUP ВУ с многозначными полями. Обращаясь к пре дыдущему примеру, можно предположить, что необходимо увидеть наибольший заказ, сделанный каждому продавцу на каждую дату. Для этого нужно сгруппировать данные таблицы Orders по дате (date) внутри одного и того же по,1я salesperson и применить функцию МАХ к каждой группе. В результате будет получено: SELECT snum, odate, MAX(amt) FROM Orders GROUP ВУ snum, odate; Выходные данные для этого запроса представлены на рис. 6.6. Пустые группы, т.е. даты, когда данный продавец не получал заказов, в результате не представлены.
 Глава 6. Суммирование данных с nомощью фу11кций агрегирования WiiifШM +.1:1, 1001 10/03 1990 767.19 1'!101 10/05/1990 4723.01!1 1001 10/06/1990 9891.88 1002 11!1/03/1990 5160.45 1002 10/04/1990 75.75 1002 10/06/1990 1309.95 HlliJ3 10/'14/1991iJ 1713.23 1004 10/03/1990 1900.10 11!107 11!1/03/1990 1098. 16 Рис. 6.6. Поиск максимальных заявок (orders) для каждого продавца (salesperson) на каждый день Предложение НА VING Обращаясь к предыдущему примеру, можно предположить, что интересны только покупки, превышающие $3000.00. Однако использовать агрегатные функции в пред ложении WHERE нельзя (если только не применяется подзапрос, который будет объ яснен позднее), поскольку предикаты оцениваются в терминах единственной строки, тогда как агрегатные функции оцениваются в терминах групп строк. Это значит, что нельзя формулировать запрос следующим образом: SELECT snum, odate, MAX(amt) FROM Orders WHERE MAX(amt) > 3000.00 GROUP ВУ snum, odate; Это неприемлемо с точки зрения точной интерпретации ANSI. Чтобы увидеть мак симальную покупку, превышающую $3000.00, следует использовать предложение HAVING. Оно определяет критерий, согласно которому определенные группы исклю чаются из числа выходных данных, так же, как предложение WHERE делает это для отдельных строк. Правильная команда выглядит так: SELECT snum, odate, MAX(amt) FROM Orders GROUP ВУ snum, odate HAVING MAX(amt) > 3000.00; Выходные данные для этого запроса представлены на рис. 6.7.
 Что такое функции агрегирования? Рис. 6. 7. Поглощение групп агрегатными значениями Аргументы HAVING подчиняются тем же правилам, что и аргументы SELECT в команде, использующей GROUP ВУ, и должны иметь единственное значение для каж дой выходной группы. Следующая команда некорректна: SELECT snum, MAX(amt) FROM Orders GROUP 8У snum HAVING odate = 10/03/1988; В предложении HAVING нельзя указывать поле odate, посколь~--у оно может иметь (и действительно имеет) более одного значения для каждой выходной группы. HAVING должно относиться только к агрегатам и полям, выбранным по GROUP ВУ. Вот корректный способ формулировки приведенного запроса (выходные данные пред ставлены на рис. 6.8): SELECT snum, MAX(amt) FROM Orders WHERE odate = 10/03/1990 GROUP 8У snum; Поскольку odate не является и не может быть выбранным полем, значимость полу ченных здесь данных, конечно, менее очевидна, чем в некоторых других примерах. Выходные данные должны были бы содержать нечто вроде следующего предложения: "Вот наибольшие заявки на 3 октября". В главе 7 будет объяснено, как вставить текст в выходные данные. HAVING может иметь только такие аргументы, у которых единственное значение для группы выходных данных. На практике чаще всего применяются агрегатные
 Глава 6. Суммирование данных с помощью функций агрегироватtя
 Рис. 6.8. Максимум для каждого продавца за 3 октября 1990 г. функции, но можно осуществлять выбор полей и с помощью GROUP ВУ. Например, можно взглянуть на самые большие заказы для Serres и Rifkin: SELECT snum, MAX(amt) FROM Orders GROUP ВУ snum HAVING snum IN (1002, 1007) Выходные данные для этого запроса представлены на рис. 6.9. Не используйте вложенные агрегаты В версии языка SQL, определяемой ANSI, нельзя применять агрегатную функцию с агрегатом в качестве аргумента. Предположим, нужно определить в какой день было сделано наибольшее число заявок. Ес.1и ввести команду: SELECT odate, МАХ ( SUM (amt) ) FROM Orders GROUP ВУ odate; то она, вероятно, будет отвергнута. (Существуют реализации, которые не учитывают такие ограничения, что дает определенные преимущества, поскольку вложенность аг регатов может быть полезной, даже если она и вызывает сомнения.) Например, в дан ной команде SUM должна быть применена к каждой оdаtе-группе, а МАХ - ко всем группам, причем она выдает единственное значение для всех этих групп. В то же вре мя предложение GROUP ВУ предполагает, что должна быть одна строка выходных данных на каждую группу odate.
 Итоги Рис. 6.9. Использование НА VING с GROUP ВУ Итоги Теперь вы научились использовать запросы иначе. Возможность выводить, а не просто локализовать значения очень важна и означает, что не надо отслеживать путь получения информации, если можно сформулировать запрос на ее вывод. Запрос дает результаты на текущий момент, тогда как таблица итогов и средних величин полезна на тот момент, когда она в последний раз обновлялась. Это не значит, что агрегатные функции во всех случаях могут полностью снять потребность проследить путь инфор мации. Агрегатные функции применимы к группам значений, определяемым предложени ем GROUP ВУ. Эти группы имеют общее значение поля и могут использоваться внут ри других групп, имеющих общее значение поля. Предикаты нужны для определения строк, к которым применяется функция агрегирования. Такое комбинирование позво ляет получить агрегаты на основе полностью определенных подмножеств значений поля. Затем вы можете задать условие исключения определенных результирующих групп с помощью предложения HAVING. Вы уже знаете, как запросы генерируют значения. В главе 7 будет показано, эти значения можно применять.
 Работаем на SQL l. Запишите запрос, который подсчитывает все заявки за 3 октября 1990 года. 2. Запишите запрос, который подсчитывает количество различных городов (не NULL) в таблице Customers. 3. Запишите запрос, который выбирает наименьшую заявку для каждого покупате ля. 4. Запишите запрос, который выбирает первого в алфавитном порядке покупателя, имя которого начинается с 'G'. 5. Запишите запрос, который выбирает максимальный рейтинг (rating) для каждого города. 6. Запишите запрос, который подсчитывает количество продавцов, получающих заказы каждый день. (Продавца, имеющего более одного заказа в день, следует включить в пересчет только один раз.) (Ответы см. в приложении А.)
 Форматирование результатов запросов
 Глава 7. Форматированиерезультатов запросов Назначение этой главы - расширить возможности обработки результатов за просов. Вы узнаете, как вставить текст и константы в выбранные поля, как использо вать последние в математических выражениях, результаты вычисления которых станут выходными данными и, наконец, как представить выходные данные в задан ной последовательности. Последнее предполагает возможность упорядочить выход ные данные по любому столбцу или по любым значениям, полученным на основе данных, содержащихся в столбце. Строки и выражения Во многих базах данных, использующих SQL, имеются специальные средства, по зволяющие оформлять результаты запросов. Естественно, в разных программных про дуктах они кардинально отличаются, но эти различия здесь не обсуждаются. Однако и стандартная версия SQL имеет ряд характерных свойств, позволяющих сделать нечто большее, чем просто вывести значения полей и функций агрегирования. О них и пой дет речь в данной главе. Скалярные выражения с выбранными полями. Предположим, необходимо выполнить простые числовые операции с данными для представления их в более удобном виде. SQL позволяет вносить скалярные выражения и константы в выбран ные поля. Эти выражения могут дополнять или заменять поля в предложениях SELECT и могут содержать множество выбранных полей. Например, если вам удоб нее представить комиссионные продавцов в виде процентов, а не десятичных чисел, достаточно указать: Рис. 7.1. Использование выражения в запросе
 Строки и выражения SELECT snum, sname, city, comm • 100 FROM Salespeople; Выходные данные для этого запроса представлены на рис. 7.1. Выходные столбцы. Последний столбец в предыдущем примере не имеет имени, поскольку является выходным столбцом. Выходные столбцы - это столбцы, кото рые создаются с помощью запроса (в тех случаях, когда в предложении запроса SELECT используются агрегатные функции, константы или выражения), а не извле каются непосредственно из таблицы. Поскольку имена столбцов являются атрибута ми таблицы, столбцы, не переходящие из таблицы в выходные данные, не имеют имен. Почти во всех ситуациях выходные столбцы отличаются от столбцов, извле каемых из таблицы тем, что они не поименованы. Внесение текста в выходные данные запроса. Буква 'А', не обозначающая ничего кроме самой себя, является константой, как и число 1. Константы, а также текст, можно включать в предложение запроса SELECT. Однако, буквенные констан ты, в отличие от числовых, нельзя использовать в выражениях. В SЕLЕСТ предложение можно включить 1+2, но не 'А'+ 'В', поскольку 'А' и 'В' здесь просто буквы, а не переменные или символы, используемые для обозначения чего-либо от личного от них самих. Тем не менее, возможность вставить текст в выходные данные запроса вполне реальна. Можно изменить предыдущий пример, пометив комиссионные, выраженные в про центах, символом "процент"(%), что позволяет представить их в выходных данных в виде символов и комментариев, например: SELECT snum, sname, city, ·х·, comm • 100 FROM Salespeople; 
   -~SOL Execut1on Log-- --  --- --- ;  
  SELfCT snum, sname. c1ty,  '%' comm .  * 1"1'1  
  1 FROM Salespeople,  
  ЧIF!Ш-  London •••  
  . , .."". 1001 Peel  12.00000  
  1002  Serres  San Jose  %  13.00000  
  1004  Mot.ika  london  %  11. 00000  
  1007  Rifkin  Barcelona  %  1 . 00001/J  
  1l/Jl/J3  New  Vork  '5 %  
  Д)(elrod  10.00000  
 
  Рис. 7.1. Включение символов в выходные данные
 Глава 7. Форматированиерезультатов запросов Выходные данные для этого запроса представлены на рис. 7.2. Аналогичный прием можно применить для того, чтобы пометить выходные дан ные, включив в них некоторый комментарий. Однако нужно помнить, что один и тот же комментарий будет печататься не один раз для всей таблицы, а в каждой строке вы ходных данных. Предположим, генерируются выходные данные для отчета, в котором фиксируется количество заказов на каждый день. Выходные данные можно пометить (см. рис. 7.3), оформив запрос следующим образом: SELECT "Fог·, odate, ·, there аге·, COUNT (DISTINCT onum), 'orders.. FROM Orders GROUP ВУ odate; Грамматическую ошибку в выходных данных на 10/05/1990 можно исправить, но запрос при этом сильно усложнится. (Для этого пришлось бы использовать два запро са и операцию объединения UNION, которая будет рассмотрена в главе 14.) Вам мо жет быть полезен единственный неизменный комментарий для каждой строки таблицы, но он ограничен. Иногда более элегантное и полезное решение состоит в том, чтобы выдать один и тот же комментарий для всех выходных данных в целом или разные комментарии для различных строк. Многие программные продукты, использующие SQL, часто предоставляют пользо вателям генераторы отчетов, которые применяются для форматирования и улучшения формы выходных данных. Встроенный SQL тоже может употреблять средства формати рования того языка, в который он встроен. SQL предназначен прежде всего для обработ ки данных. Его выходными данными является информация, а программа, Рис. 7.3. Комбинирование текста, значений полей и агрегатов
 Упорядочение выходных полей использующая SQL, может принимать эту информацию и выводить ее в более нагляд ной форме. Однако, это уже лежит за пределами самого SQL. Упорядочение выходных полей Таблицы являются неупорядоченными множествами, и исходящие из них данные необязательно представляются в какой-либо определенной последовательности. В SQL применяется команда ORDER ВУ, позволяющая внести некоторый порядок в выходные данные запроса. Она их упорядочивает в соответствии со значениями од ного или нескольких выбранных столбцов. Множество столбцов упорядочиваются один внутри другого, как в случае применения GROUP ВУ, и можно задать возрас тающую (ASC) или убывающую (DESC) последовательность сортировки для каждо го из столбцов. По умолчанию приията возрастающая последовательность сортировки. Таблица заявок (Orders), упорядоченная по номеру заявки, (обратить внимание на значения в столбце cnum) выглядит так: SELECT * FROM Orders ORDER ВУ cnum DESC; Выходные данные представлены на рис. 7.4. Рис. 7.4. Упорядочение выходных данных по убыванию поля cnum
 Глава 7. Форматированиерезулыпатов запросов Упорядочение по мноJ1Сеству столбцов Внутри уже произведенного упорядочения по полю cnum можно упорядочить таблицу и по другому столбцу, например, amt (выходные данные представлены на рис. 7.5): SELECT • FROM Orders ORDER ВУ cnum DESC, amt DESC; Так можно использовать ORDER ВУ одновременно для любого количества столб цов. Во всех случаях столбцы, по которым выполняется сортировка, входят в число выбранных. Этому требованию стандарта ANSI удовлетворяет большинство систем. Например, следующая команда неверна: SELECT cname, city FAOM Customers ORDER ВУ cnum; Поскольку поле cnum отсутствует в списке выбранных полей, предложение ORDER ВУ не может его найти для упорядочения выходных данных. Даже если сис тема позволяет это сделать, значимость такого упорядочения неочевидна, поскольку само поле, по которому выполняется сортировка, не представлено в выходных дан ных. Поэтому включение в них всех столбцов, используемых в предложении ORDER ВУ, весьма желательно. 
   н.1.111"м мш1м м.1.nи  
  3006  1098.16  10/03/1990  
  3001  18.69  10/03/1990  
  3002  1900.10  10/03/1990  
  3011  9891.88  10/06/1990  
  3008  4723.00  10/05/1990  
  3010  1309.95  10/06/1990  
  3007  75.75  10/04/1990  
  3005  5160.45  10/03/1990  
  3009  1713.23  10/04/1990  
  3003  767.19  10/03/1990  
  ; .  .. '  ". .  
 
  Рис. 7.5. Упорядочение выходных данных по множеству полей
 Упорядочение выходных полей Упорядочение составных групп ORDER ВУ может использоваться с GROUP ВУ для упорядочения групп. ORDER ВУ всегда выполняется последней. Вот пример из предыдущей главы с добавлением предложения ORDER ВУ. До этого выходные данные были сгруппированы, но поря док групп был произвольным; теперь группы выстроены в определенной последова тельности: SELECT snum, odate, MAX(amt) FROM Orders GROUP ВУ snum, odate ORDER ВУ snum; Выходные данные представлены на рис. 7.6. Поскольку в команде не указан способ упорядочения, по умолчанию применяется возрастающий. Рис. 7.6. Упорядочение групп Упорядочение результата по номеру столбца Вместо имен столбцов для указания полей, по которым упорядочиваются выход ные данные, можно использовать номера. Но ссылаясь на них, следует иметь в виду, что это номера в определении выходных данных, а не столбцов в таблице. Т.е. первое поле, имя которого указано в SELECT, является для предложения ORDER ВУ полем с номером 1, независимо от его расположения в таблице. Например, можно применить следующую команду, чтобы увидеть определенные поля таблицы Salespeople, упоря доченные по убыванию поля commission (comm) (выходные данные представлены на рис. 7.7):
 Глава 7. Форматироваииерезультатов за11росов Рис. 7. 7. Упорядочение с использованием номеров столбцов SELECT sname, comm FROM Salespeople ORDER ВУ 2 DESC; Мы рассматриваем это свойство ORDER ВУ для того, чтобы продемонстрировать воз можность его использования со столбцами выходных данных; эта процедура аналогична применению ORDER ВУ со столбцами таблицы. Столбцы, полученные с помощью функ ций агрегирования, константы или выражения в предложении запроса SELECT, можно применить и с ORDER ВУ, если на них ссьшаются по номеру. Например, чтобы подсчи тать заявки (orders) для каждого продавца (salespeople) и вывести результаты в убываю щем порядке, как показано на рис. 7.8: SELECT snum, COUNT (DISTINCT onum) FROM Orders
 GROUP ORDER
 snum 2 DESC;
 В этом случае был использован номер столбца, но так как выходной столбец не имеет имени, саму функцию агрегирования применять не понадобилось. В соответст вии со стандартом ANSI SQL, следующий запрос не работает, хотя в некоторых систе мах он воспринимается без проблем: SELECT snum, COUNT (DISTINCT onum) FROM Orders
 Итоги Рис. 7.8. Упорядочение выходных столбцов GROUP ВУ snum OROER ВУ COUNT (OISTINCT onum) OESC; Многими системами такая команда воспринимается как ошибочная. ORDER ВУ с NULL-значениями Если в поле, которое используется для упорядочения выходных данных, существу ют NULL-значения, то все они следуют в конце или предшествуют всем остальным значениям этого поля. Конкретный вариант не оговаривается стандартом ANSI, во прос решается индивидуально для каждого программного продукта, и один из этих ва риантов принимается. Итоги Теперь с помощью запросов вы можете получить нечто большее, чем простые зна чения полей и функций агрегирования для данных, представленных в таблице. Значе ния полей используются в выражениях: например, можно умножить числовое поле на 1 О или умножить его на другое числовое поле. Кроме того, константы, в том числе и символьные, можно включать в состав выходных данных. Все это дает возможность выводить текст непосредственно в результат запроса наравне с данными, содержащи-
 Глава 7. Форматированиерезультатов запросов
 мися в таблице, что, в свою очередь, позволяет помечать или комментировать выход ные данные различными способами. Вы научились управлять порядком вывода результатов запроса. Несмотря на то, что сама таблица базы данных остается неупорядоченной, предложение ORDER ВУ позволяет управлять порядком вывода строк выходных данных конкретного запроса. Порядок представления выходных данных запроса может быть возрастающим или убывающим, и столбцы можно упорядочить один внутри другого. В этой главе введено понятие выходных столбцов, которые можно применять для упорядочения результатов запроса, но эти столбцы не поименованы, поэтому для ссылки на них в предложении ORDER ВУ используется порядковый номер выходного столбца из предложения SELECТ. В главе 8 будут рассмотрены более сложные запросы. Вы узнаете, как объединить в одной единственной команде запросы к множеству таблиц базы данных, установив между ними связи.
 ·· -- · - ·· ------ Работаем на SQL l. Предположим, каждый продавец имеет 12% комиссионных. Запишите запрос к таблиuе Orders, который выдает номер заявки (order number), номер продавца (salesperson number) и общее значение комиссионных продавца (amount of salesperson's commission). Выходные данные упорядочите по значениям послед него столбца. 2. Запишите запрос к таблице Customers, который находит максимальный рейтинг для каждого города. Представьте выходные данные в таком виде: For the city (city), the highest rating is: (rating). (Для города (city) максимальный рейтинг составляет: (rating).) 3. Запишите запрос, который выдает список покупателей (customers) в порядке убывания рейтинга (rating). Поле rating в выходных данных должно быть пер вым, за ним следуют имя покупателя (customer's name) и номер покупателя (customer's number). 4. Запишите запрос, который подводит итоги по заказам на каждый день и пред ставляет результаты в убывающем порядке. (Ответы на вопросы см. в прило:жении А.)
 Использование мноJ1Сества таблиц в одном запросе
 Глава 8. Использование множества таблиц в одном запросе До сих пор каждый рассматриваемый запрос базировался на единственной таб лице. После изучения этой главы вы сможете формулировать запросы с помощью од ной команды для любого (произвольного) количества таблиц. Это исключительно мощная процедура, поскольку осуществляется не только комбинирование выходных данных из множества таблиц, но и устанавливаются связи между ними. Вы познако митесь с различными видами таких связей, узнаете, как они определяются и исполь зуются. Соединение таблиц Одна из наиболее важных черт запросов SQL состоит в их способности определять связи между множеством таблиц и отображать содержащуюся в них информацию в терминах этих связей в рамках единственной команды. Операция такого рода называ ется соединение« (join) и является одной из самых мощных операций для реляцион ных баз данных. Как уже говорилось в главе 1, преимущество реляционного подхода заключается в связях (relationships), которые можно установить между элементами данных в таблице. С помощью соединений непосредственно связывается информа ция, содержащаяся в таблицах, независимо от их чис.1а, а также между отдельными частями любой таблицы. При операции соединения таблицы перечисляются в пред~южении запроса FROM; имена таблиц разделяются запятыми. Предикат запроса может ссылаться на любой столбец любой из соединяемых таблиц и, следовательно, может использоваться для установления связей между ними. Обычно предикат сравнивает значения в столбцах различных таблиц для того, чтобы определить, удовлетворяется ли условие WHERE. Имена таблиц и столбцов Полное имя столбца состоит из имени таблицы, непосредственно за которым стоит точка, а за ней - имя столбца. Приведем несколько примеров: Salespeople.snum Customers.city Orders.odate В приводимых ранее примерах имена таблиц можно было опускать, поскольку за просы адресовались только к одной таблице, и SQL выполнял подстановку имени соот ветствующей таблицы в качестве префикса. Даже при формулировке запроса к множеству таблиц их имена можно опустить, если все столбцы этих таблиц различны. Однако так бывает далеко не всегда.Например, есть две простые таблицы с одинаковы ми именами столбцов-сitу. Если для них необходимо выполнить операцию соединения,
 Соедииение таблиц то следует указать Salespeople.city или Customers.city, что дает возможность SQL одно значно определить, о каком столбце идет речь. Выполнение операции соединения (joiп) Предположим, нужно установить связь между продавцами (Salespeople) и поку пателями (Customers) в соответствии с местом их проживания, чтобы получить все возможные комбинации продавцов и покупателей из одного города. Для этого не обходимо взять продавца из таблицы Salespeople и выполнить по таблице Customes поиск всех покупателей, имеющих то же значение в столбце city. Это можно сде лать, введя следующую команду (выходные данные представлены на рис. 8.1 ): SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city; Поскольку поле city присутствует в каждой из таблиц Salespeople и Customers, име на таблиц используются перед именем city в качестве префиксов. Это необходимо в том случае, когда два или более поля имеют одинаковые имена, но для ясности и пол ноты картины полезно включать в соединения имя таблицы. В дальнейшем имена таб лиц будут использоваться там, где это необходимо, чтобы было понятно, rде они нужны, а где нет. Выполняя операцию соединения, необходимо генерировать все возможные сочета ния строк для двух или более таблиц и проверять истинность предиката на каждом та ком сочетании. В предыдущем примере SQL берет строку, соответствующую 
   rг  SQL  Execution Log~~~~~~~~~~~~  
  fSElfCT Customers cname. Salespeople.sname,  
  !1Salespeople.c1ty  
  'FROM  Salespeople. Customcrs  
  WHERE  Salespeople c1ty  Customers c1ty:  
  1  
 
  srыme Peel Peel Serres Рис. 8.1. Соединение двух таблиц
 Глава 8. Использование множества таблиц в одном за11росе продавцу Рее! из таблицы Salespeople, и комбинирует ее с каждой строкой таблицы Customers, выбирая по одной строке из этой таблицы. Если на данной комбинации строк предикат имеет значение "истинно", т.е. поле city строки таблицы Customers со держит значение London такое же, как и у Рее!, то указанные в предложении SELECT поля из комбинации этих строк являются выходными данными. Те же действия пред принимаются относительно каждого продавца из таблицы Salespeople (некоторые из них не имеют покупателей, находящихся в том же городе). Операция соединения таблиц посредством ссылочной целостности Эrа операция применяется для использования связей, встроенных в базу данных. В предыдущем примере связь между таблицами была установлена с помощью операции со единения. Но эти таблиць1 уже связаны по значениям полем snum. Такая связь называется состоянием ссьuючной целостности, о которой упоминалось в главе 1. Стандартное при менение операции соединения состоит в извлечении данных в терминах этой связи. Что бы показать соответствие имен покупателей именам продавцов, обслуживающих этих покупателей, используется следующий запрос: SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum; Выходные данные для этого запроса представлены на рис. 8.2. МЭ1@IШ Hof f man Giovanni Liu Grass Clemens Рис. 8.2. Соединение двух продавцов с их покупателями
 Соединение таблиц Это также пример соединения, в котором столбцы, используемые в формулировке предиката запроса, - в данном случае это столбцы snum в обеих таблицах - опуще ны из выходных данных. Выходные данные показывают, какие покупатели обслужи ваются какими продавцами. Значения snum, на основании которых устанавливается связь, в данном случае не представлены, поскольку здесь они не являются существен ными. Однако, действуя таким образом, нужно .1ибо иметь уверенность в том, что вы ходные данные сами по себе ясны, либо дать им какие-то объяснения. Эквисоединение и другие виды соединений Соединение, испопьзующее предикаты, основанные на равенствах, называется эк висоединением. Рассмотренные в данной главе примеры относятся именно к этой кате гории, поскольку все условия в предложении WHERE базируются на математических выражениях, использующих символ равенства. "City=='London'" и "Sa\espeop\e.snum==Orders.snum" - примеры применения символа равенства в преди катах. Эквисоединение является, по-видимому, наиболее распространенным типом соединения, но существуют и другие. Фактически в соединении можно использовать любой оператор сравнения. Вот пример соединения другого рода (выходные данные для него представлены на рис. 8.3 ): SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200; Эта команда полезна далеко не всегда. Она генерирует все комбинации имен про давцов и покупателей так, что первые предшествуют последним в алфавитном поряд- Рис. 8.3. Соединение, основанное на неравенстве
 Глава 8. Использование множества таблиц в одном запросе ке, а последние имеют рейтинг меньше чем 200. Обычно такие сложные связи нет необходимости конструировать, и поэтому вам полезно знать также и о других воз можностях. Соединение более чем двух таблиц Можно конструировать запросы путем соединения более чем двух таблиц. Предпо ложим, нужно найти все заявки покупателей, не находящихся в том же городе, что и их продавец. Для этого потребуется связать все три рассматриваемые таблицы (выход ные данные представлены на рис. 8.4): SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city <> Salespeople.city AND Orders.cnum Customers.cnum AND Orders.snum Salespeople.snum; Хотя команда выглядит достаточно сложно, следуя ее логике, легко убедиться, что в выходных данных перечислены покупатели и продавцы, расположенные в разных городах (они сравниваются по полю snum), и что указанные заказы сделаны именно этими покупателями (подбор заказов устанавливается в соответствие с полями cnum и snum таблицы Orders). Рис. 8.4. Соединение трех таблиц
 Итоги Итоги Теперь вы можете не ограничиваться рассмотрением лишь одной таблицы в неко торый момент времени, а умеете сравнивать любые поля произвольного числа таблиц и применять полученные результаты для поиска нужной информации. Эта техника на столько полезна для установления связей, что используется и для их конструирования внутри единственной таблицы. В следующей главе будет рассмотрена эффективная процедура соединения двух копий одной таблицы.
 Работаем па SQL Запишите запрос, который выдает каждый номер заказа, и следующее за ним имя покупателя, сделавшего этот заказ. 2. Запишите запрос, который для каждого заказа выдает после его номера имена продавцов и покупателей. 3. Запишите запрос, который выдает имена всех покупателей, обслуживаемых про давцами, имеющими комиссионные более 12%. Выходными данными должны быть имя покупателя, имя продавца и комиссионные продавца. 4. Запишите запрос, который вычисляет размер комиссионных продавца для каж дого заказа покупателя с рейтингом, превышающим 100. (Ответы см. в приложении А.)
 9 ~~~ Операция соединения, операнды которой представлены одной таблицей
 Глава 9. Операция соединения, операнды которой представлены од11ой таблицей В главе 8 было показано, как соединить две или более таблицы. Аналогичную технику можно применить для соединения двух копий одной таблицы. В настоящей главе эта процедура рассматривается подробно. Она не является простым "самонало жением", а весьма полезно для выявления определенных видов связей между элемен тами данных в конкретной таблице. Как выполняется операция соединения двух копий одной таблицы Соединение таблицы с ее же копией означает следующее: любую строку таблицы (одну в каждый момент времени) можно комбинировать с ее копией и с любой другой строкой этой же таблицы. Каждая такая комбинация оценивается в терминах предика та, как и в случае соединения нескольких различных таблиц. Это позволяет легко кон струировать определенные виды связей между различными записями внутри единственной таблицы - например, осуществлять поиск пар строк с общим значени ем поля. Соединение таблицы со своей копией можно представить себе следующим обра зом: реально таблица не копируется, но SQL выполняет команду так, как будто бы де лалось именно это. Другими словами, подобный тип соединения не отличается от обычного соединения двух таблиц, за исключением того, что в данном случае они идентичны. Алиасы Синтаксис команды соединения таблицы с ее же копией тот же, что и для различ ных таблиц, с единственным исключением. В рассматриваемом случае все имена столбцов повторяются независимо от использования имени таблицы в качестве пре фикса. Чтобы сослаться на столбцы запроса, нужно иметь два различных имени для одной и той же таблицы. Для этого надо определить временные имена, называемые перелtенными области определения, перелtенны.ми корреляции или просто алиасами. Они определяются в предложении запроса FROM. Для этого указывается имя табли цы, ставится пробел, а затем указывается имя алиаса для данной таблицы. Приведем пример поиска всех пар продавцов, имеющих одинаковый рейтинг (вы ходные данные представлены на рис. 9 .l ): SELECT first.cname, second.cname, first. rating FROM Customers first, Customers second WHERE first.rating = second. rating;
 Как выполняется операция соединения двух ко1111й одной таблицы 
   Giovann1  Giovanni  гее  
  Giovanni  Liu  200  
  Liu  Giovanni  200  
  Liu  Liu  200  
  Grass  Grass  300  
  Grass  Cisneros  300  
  Clemens  Hoffman  100  
  Clemens  Clemens  100  
  Clemens  Pereira  100  
  Cisneros  Grass  300  
  Cisncros  Cisneros  300  
  Pereira  Hoffman  100  
  Pereira  Clemens  100  
  Pereira  Pereira  100  
  Browse :  1'.1.  
 PgUp -~  
 
  Рис. 9.1. Соединение таблицы по принципу "сама с собой" (заметим, что на рис. 9.1 , как и в последующих примерах, видна только часть вы ходных данных запроса, поскольку реально все они в пределах одного окна не уме щаются). В приведенном примере команды SQL ведет себя так, как будто в операции со единения участвуют две таблицы, называемые "first" (первая) и "secoпd" (вторая). Обе они в действительности являются таблицей Customers, но алиасы позволяют рассматривать ее как две независимые таблицы. Алиасы first и second были опреде лены в предложении запроса FROM непосредственно за именем таблицы. Алиасы применяются также в предложении SELECT, несмотря на то, что они не определены вплоть до предложения FROM. Это совершенно оправдано. SQL сначала примет какой-либо из таких алиасов на веру, но затем отвергнет команду, если в предложе нии FROM запроса алиасы не определены. Время жизни алиаса зависит от времени выполнения команды. После выполнения запроса используемые в нем алиасы теря ют свои значения. Получив две копии таблицы Customers для работы, SQL выпо.1няет операцию JOIN, как для двух разных таблиц: выбирает очередную строку из одного алиаса и со единяет ее с каждой строкой другого алиаса. Исключение избыточности Выходные данные включают каждую комбинацию значений дважды, причем во второй раз - в обратном порядке. Это объясняется тем, что значение появляется один раз для каждого алиаса, а предикат является симметричным. Следовательно, значение А в алиасе first выбирается в комбинации со значением В в алиасе second, и значение
 Глава 9. Операция соединения, операнды которой представлены одной таблицей А в алиасе second - в комбинации со значением В в алиасе first. В данном примере Hoffman был выбран с Clemens, а затем Clemens был выбран с Hoffman. Тоже самое произошло с Cisneros и Grass, Lie и Giovanni и т.д. Кроме того, каждая запись присое диняется сама к себе в выходных данных, например, Lie и Lie. Простой способ исключить повторения - задать порядок для двух значений так, чтобы одно значение было меньше, чем другое, или предшествовало в алфавитном по рядке. Это делает предикат ассиметричным, и одни и те же значения не извлекаются снова в обратном порядке, например: SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second WHERE first.rating second.rating AND first.cname < second.cname; Выходные данные для запроса представлены на рис. 9.2. Hoffman предшествует Periera в алфавитном порядке, эта комбинация удовлетворя ет обоим условиям предиката и появляется в составе выходных данных. Когда та же самая комбинация появляется в обратном порядке (т.е. когда Periera из таблицы с алиа сом first приписывается Hoffman из таблицы с алиасом second), второе условие не вы полняется. С другой стороны, Hoffman не выбирается сам по себе, как имеющий тот же рейтинг, потому •по его имя не предшествует ему же самому в алфавитном поряд- Рис. 9.2. Исключение избыточных выходных данных при операции соединения с собственной копией
 Как выполняется операция соедине11ия двух копий од11ой таблицы ке. Если нужно включить в запрос соединение строки с ее копией, достаточно исполь зовать <= вместо <. Выявление ошибок Рассмотренное свойство SQL можно использовать для выявления ошибок опре деленного рода. Если посмотреть на таблицу Orders, станет ясно, что поля cnum и snum используются для определения связи. Поскольку каждому покупателю (customer) может быть назначен один и только один продавец (salesperson), в любой момент времени определенному номеру покупателя для строки из таблицы Orders соответствует строка с таким же номером продавца. Следующая команда позволяет выявить любые несоответствия такого плана: SELECT first.onum, first.cnum, first.snum, second.onum, second.cnum, second.snum FROM Orders first, Orders second WHERE first.cnum = second.cnum AND first.snum <>second.snum; Команда выглядит сложной, но ее логика весьма прозрачна. Она берет первую стро ку таблицы Orders и запоминает ее под именем алиаса first, затем проверяет ее в комби нации с каждой строкой таблицы Orders под именем алиаса second. Если комбинация строк удовлетворяет предикату, она включается в состав выходных данных. В нашем случае просматривается строка, в которой поле cnum равно 2008, а поле snum равно 1007; затем осуществляется выбор каждой строки, в поле cnum которой содержится та кое же значение. Если обнаруживается, что в поле snum любой из этих строк содержит ся другое (отличное от 1007) значение, то предикат принимает значение "истина", и в состав выходных данных включаются те поля из текущей комбинации строк, имена ко торых указаны в предложении SELECT. Если все значения поля snum для данного зна чения cnum в этой .таблице одинаковы, приведенная выше команда не генерирует выходных данных. Еще про ш~иасы Хотя соединение таблиц со своими копиями - зто первый встретившийся случай, когда потребовалось понятие алиаса, его употребление не лимитировано использова нием для разграничения различных копий одной и той же таблицы. Алиасы можно применять при создании альтернативных имен таблиц в команде SELECТ. Например, если таблицы имеют очень длинные и сложные имена, то можно определить простые, состоящие из одной буквы алиасы, например, А или В, и использовать их вместо имен таблиц в предложении SELECT и в предикате. Их можно также применять со связан ными подзапросами (которые обсуждаются в главе 11).
 Глава 9. Операция соедииения, операнды которой представлены одной таблицей
 Некоторые более сложные операции соединения В запросе можно использовать любое количество алиасов для единственной табли цы, хотя применение более двух в одном предложении SELECT нетипично. Предполо жим, продавцам (salespeople) еще не назначили покупателей (customers). Политика кампании состоит в том, чтобы назначить всем продавцам по три покупателя, каждо му из которых приписывается одно из трех возможных значений рейтинга. Небходимо решить, как осуществить такое распределение, и использовать следующие запросы для просмотра всех возможных комбинаций назначаемых покупателей (выходные данные представлены на рис. 9.3): SELECT a.cnum, b.cnum, c.cnum FROM Customers а, Customers Ь, Customres с WHERE a.rating = 100 AND b.rating = 200 AND с. rating = 300; Этот запрос находит все возможные комбинации покупателей (customers) с тремя значениями рейтинга таким образом, что в первом столбце расположены покупатели с рейтингом 100, во втором столбце - покупатели с рейтингом 200, в третьем столбце - покупатели с рейтингом 300. Они повторены во всех возможных комбинациях. Это сво его рода группирование данных, которое нельзя выполнить средствами GROUP ВУ или ORDER ВУ, поскольку они сравнивают значения только из одного столбца. Каждый алиас или таблицы, имена которых упомянуты в предложении FROM за проса SELECT, использовать необязательно. Иногда алиас или таблица запрашивают ся таким образом, что на них ссылаются предикаты запроса. Например, следующий запрос находит всех покупателей (customers), расположенных в городах, где действует Рис. 9.3. Комбинирование покупателей с различными значениями рейтинга
 Как выполняется операция соединения двух копий одиой таблицы продавец (salesperson) Serres (snum 1002) (выходные данные представлены на рис. 9.4): SELECT b.cnum, b.cnam FROM Customers а, Customers Ь WHERE a.snum = 1002 AND b.city = a.city; Алиас а сделает предикат ложным, за исключением случаев, когда значение столб ца snum равно 1002. Так алиас исключает всех покупателей, кроме покупателей про давца Serres. Алиас Ь принимает значение "истина" для всех строк с тем же значением города (city), что и текущее значение города (city) в а; в процессе выполнения запроса строка с алиасом Ь делает предикат истинным всякий раз, когда в поле city этой строки представлено то же значение, что и в поле city строки с алиасом а. Поиск строк алиаса Ь выполняется исключительно для сравнения значений с алиасом а, из строк с алиасом Ь никакого реального выбора данных не выполняется. Покупатели (customers) продав ца Serres расположены в одном и том же городе, значит выбор их из алиаса а не явля ется необходимым. Итак, алиас а локализует строки покупателей (customers) Serres, Liu и Grass. Алиас Ь находит всех покупателей (customers), расположенных в одном из городов (San Jose и Berlin соответственно), включая, конечно, самих Liu и Grass. Можно конструировать соединения (joins), которые содержат различные таблицы и алиасы единственной таблицы. Следующий запрос соединяет таблицу Customers с ее копией для нахождения всех пар покупателей, обслуживаемых одним и тем же продав цом. В любой момент времени он соединяет покупателя (customer) с таблицей Salespeople для того, чтобы определить имя продавца (salesperson) (выходные данные для запроса представлены на рис. 9.5): Рис. 9.4. Поиск покупателей, расположенных в тех городах, где действует продавец Serres
 Глава 9. Операция соединения, операнды которой представлены одной таблицей Рис. 9.5. Соединение таблицы с ее копией и с другой таблицей SELECT sname, Salespeople.snum, first.cname, second.cname FROM Customers first, Customers second, Salespeople WHERE first.snum = second.snum AND Salespeople.snum = first.snum AND first.cnum < second.cnum; Итоги Вы узнали, что такое операции соединения Uoins), как их применять для конструи рования связей внутри одной таблицы, различных таблиц или двух одинаковых таб лиц, где эти возможности могут оказаться полезными. Теперь вам знакомы термины - диапазон переменных, переменные связи, алиасы (в различных программ ных продуктах и у разных авторов изложения материала по SQL терминология варьи руется, поэтому здесь объяснены все три термина). Вы больше знаете о том, как в действительности работают запросы. Следующий шаг после комбинации в запросе множества таблиц или множества ко пий единственной таблицы состоит в таком объединении множества запросов, при ко тором один запрос генерирует выходные данные, управляющие работой другого запроса. Подробнее об этой эффективной операции SQL мы поговорим в 1 О и после дующих главах.
 Работаем на SQL 1. Запишите запрос, который позволяет получить все пары продавцов (Salespeople), проживающих в одном городе. Из результата необходимо исключить комбина ции Salespeople с их копиями и повторяющиеся строки, отличающиеся порядком следования. 2. Запишите запрос, который генерирует все пары номеров для данного покупате ля, имена покупателей и исключает повторяющиеся значения, как определено в пункте!. 3. Запишите запрос, который позволяет получить имена и города для всех покупа телей (customers), с тем же рейтингом, что и у Hoffman. Запишите запрос, исполь зующий значение поля cnum вместо rating так, чтобы можно было использовать этот вариант запроса даже в случае изменения значения поля rating. (Ответы см. в 11риложении А.)
 10 Вло31еение запросов
 Глава 10. Вло;щ:ение запросов В конце прошлой главы мы отметили, что одни запросы могут управлять други ми. В большинстве случаев это можно сделать, размещая один запрос внутри преди ката, помещенного в другом, и используя выходные данные вложенного запроса для определения истинности или ложности предиката. Из этой главы вы узнаете, какого рода операторы могут использовать подзапросы, как их применять с DISTINCT, агре гатными функциями и выражениями вывода; как использовать подзапросы с предло жением HAVING и получать указатели правильного способа применения подзапросов. Как выполняются подзапросы? SQL позволяет вкладывать запросы друг в друга. Обычно внутренний запрос гене рирует зна
 Затем основной запрос выполняется как обычный, и его результат точно такой же, как на рис. 10.1.
 Подзапрос должен выбирать один и только один столбец, а тип данных этого
 столбца должен соответствовать типу значения, указанному в предикате. Часто вы бранное поле и это значение имеют одно и то же имя (в данном случае, snum).
 Если бы было известно значение персонального номера продавца (salesperson number) Motika, то можно было бы указать:
 Как вьтолняются подзапросы? Чтобы оценить внешний (основной) запрос, SQL прежде всего должен оценить внутренний запрос (или подзапрос) в предложении WHERE. Эта оценка осуществля ется так, как если бы запрос был единственным: просматриваются все строки таблицы Salespeople и выбираются все строки, для которых значение поля sname равно Motika, для таких строк выбираются значения поля snum. В результате выбранной оказывается единственная строка с snum = 1004. Одна ко вместо простого вывода этого значения SQL подставляет его в предикат основ ного запроса вместо самого подзапроса, теперь предикат читается следующим образом: WHERE snum = 1004 WHERE snum = 1004 и тем самым освободиться от подзапроса, однако использование подзапроса делает процедуру более гибкой. Вариант с подзапросом сработает и в случае изменения пер сонального номера продавца Motika. Простая замена имени продавца в подзапросе по зволяет использовать его во множестве вариантов. Значения, получаемые в процессе выполнения подзапросов Весьма полезно то, что подзапрос в данном случае возвращает одно и только одно значение. Если вместо WHERE sname = 'Motika' подставить WHERE city = 'London', то в результате выполнения подзапроса получается несколько значений. Это делает не возможной оценку предиката основного запроса на предмет истинности или ложно сти, что приводит к оценке запроса как ошибочного. При использовании подзапросов, основанных на операторах отношения (равен ства или неравенства, рассмотренных в главе 4), нужно быть уверенным, что вы ходными данными подзапроса является только одна строка. Если применяется подзапрос, не генерирующий никаких значений, то это не является ошибкой, одна ко в настоящем случае и основной запрос не даст никаких выходных данных. Под запросы, не генерирующие никаких выходных данных (или NULL-выxoд), приводят к тому, что предикат оценивается не как истинный или ложный, а как имеющий значение "неизвестно" (unknown). Предикат со значением "неизвестно" работает как и предикат со значением "ложь": основной запрос не выбирает ни од-
 Глава 10. Вложение заt~росов
 ной строки (информацию по поводу unknoun-пpeдикaтa см. в главе 5). Попытку ис пользовать нечто вроде SELECT • FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE city = 'Barcelona'); нельзя признать удачной. Если в городе Barcelona есть только один продавец (salesperson) Mr.Rifkin, то подза прос выберет единственное значение snum, и, следовательно, будет воспринят. Однако это справед.1иво только для текущих данных. В результате изменения состава данных табли цы Salespeople вполне реальной может стать ситуация, кшда в городе (city) Barcelona поя вятся два продавца, тогда в результате выполнения подзапроса получим два значения, и, следовательно, запрос будет признан ошибочным. DISTINCT с подзапросами В некоторых случаях можно использовать DISTINCT для гарантии получения единственного значения в результате выполнения подзапроса. Предположим, нужно найти все заказы (orders), с которыми работает продавец, обслуживающий покупателя Hoffman ( cnнm = 2001 ). Вот один из вариантов решения этой задачи (выходные дан ные представлены на рис. 10.2): SELECT • FROM Orders WHERE snum = (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001); Подзапрос выясняет, что значение поля snum для продавца, обслуживающего Hoffman, равно l 00 l; следовательно, основной запрос извлекает из таблицы Orders всех покупателей с тем же значением поля snum. Поскольку каждый покупатель об служивается только одним продавцом, каждая строка таблицы Orders с данным значе нием cnum имеет то же значение поля snum. Однако, поскольку может быть любое количество таких строк, подзапрос может дать в результате множество значений snum (возможно, одинаковых) для данного cnum. Если бы подзапрос возвращал более одно го значения, получилась бы ошибка в данных. Аргумент DISTINCT предотвращает та кую ситуацию.
 Как вьтолняются подзапросы? Рис. 10.2. Использование DISTINCT с целью получить единственное значение в результате выплнения подзапроса Альтернативный способ действия - сослаться в подзапросе на таблицу Customers, а не на таблицу Orders. Поскольку cnuш - первичный ключ таблицы Customer, есть гарантия, что в результате выполнения запроса будет получено единственное значе ние. Однако, если пользователь имеет доступ к таблице Orders, а не к таблице Customers, остается вариант, рассмотренный ранее. (SQL располагает механизмами определения того, кто и какие привилегии имеет при работе с таблицами. Эти вопросы рассмотрены в главе 22.) Надо помнить, что приведенный в предыдущем примере прием приемлем только тогда, когда есть уверенность, что в двух различных полях содержатся одни и те же значения. Такая ситуация является скорее исключением, а не правилом для реляцион ных баз данных. Предикаты с подзапросами являются неперемещаемыми Предикаты, включающие подзапросы, используют форму <скалярное выражение> , а не   <скалярное выражение>или  . Предыдущий пример нельзя записать в таком виде: SELECT • FROM Orders WHERE (SELECT DISTINCT snum FROM Orders
 Глава 10. Вложение запросов WHERE cnum 2001) snum; В соответствии с соглашениями ANSI, эта запись является ошибочной, хотя неко торые программы ее понимают. Согласно ограничению ANSI запрещен также вариант команды, в котором оба значения, участвующие в сравнении, получаются в результате выполнения подзапросов. Использование агрегатных функций в подзапросах Одним из видов функций, которые автоматически выдают в результате единствен ное значение для любого количества строк, конечно, являются агрегатные функции. Любой запрос, использующий единственную агрегатную функцию без предложения GROUP ВУ, дает в результате единственное значение для использования его в основ ном предикате. Например, нужно узнать все заказы, стоимость которых превышает среднюю стоимость заказов за 4 октября 1990 г. (выходные данные представлены на рис. 10.3): SELECT * FROM Orders WHERE amt > (SELECT AVG (amt) FROM Orders Рис. 10.3. Выбор заявок, в которых указано количество, превышающее среднее значение для заявок, поступивших 4 октября 1990 г.
 Как вьтолняются подзапросы? WHERE odate = 10/04/1990); Средняя стоимость заказов за 4 октября 1990 г. составляет 1788.98 (1713.23 + 75.75), деленное на 2, что равно 894.49. Строки, имеющие в поле amt (amount) значение, превышающее 894.49, выбираются в качестве результата запроса с вложенным подзапросом. Сгруппированные, то есть примененные с предложением GROUP ВУ, агрегатные функции могут дать в результате множество значений. Поэтому их нельзя применять в подзапросах. Такие команды отвергаются в принципе, несмотря на то, что примене ние GROUP ВУ и HAVING в некоторых случаях дает единственную группу в качестве выходных данных подзапроса. Для исключения ненужных групп следует применить единственную агрегатную функцию с предложением WHERE. Например, следующий запрос, составленный с целью найти средние комиссионные (comm) для продавцов (salespeople>, находящихся в Лондоне, SELECT AVG (comm) FROM Salespeople GROUP ВУ city HAVING city = 'London'; нельзя использовать в качестве подзапроса! Это вообще не лучший способ формули ровки запроса. Вот вариант, который нужен в данном случае: SELECT AVG (comm) FROM Salespeople WHERE city = 'London'; Применение подзапросов, которые формируют множественные строки с помощью IN Можно формулировать подзапросы, в результате выполнения которых получается лю бое количество строк, применяя специальный оператор IN (операторы BETWEEN, LIКE, IS NULL в подзапросах применять нельзя). IN определяет множество значений, которые тестируются на совпадение с другими значениями д'lя определения истинности предика та. Когда IN применяется в подзапросе, SQL просто строит это множество из выходных данных подзапроса. Следовательно, можно использовать IN для выполнения подзапроса, который не работал бы с реляционным оператором, и найти все заявки (Orders) для про давцов (salespeople) из London (выходные данные представлены на рис. 10.4): SELECT " FROM Orders WHERE snum IN (SELECT snum
 Глава 10. Вложение запросов
 Рис. 10.4. Использование подзапроса с IN FROM Salespeople WHERE city = 'London·); В подобной ситуации пользователю легче понять, а компьютеру проще (в конеч ном счете и быстрее) выполнить подзапрос, чем решать эту же задачу, применяя joiп: SELECT onum, amt, odate, cnum, Orders.snum FROM Orders, Salespeople WHERE Orders.snum = Salespeople.snum AND Salespeople.city = 'London·; Выходные данные совпадают с результатом выполнения подзапроса, но SQL про сматривает все возможные комбинации строк из двух таблиц и проверяет, удовлетво ряет ли каждая из них составному предикату. Проще и эффективнее извлечь из таблицы Salespeople значение поля snum для тех строк, где city = 'Loпdon', а затем вы полнить поиск этих значений в таблице Orders; именно по такой схеме выполняется вариант с вложенным подзапросом. Вложенный запрос дает номера 1001 и 1004. Внешний запрос дает строки таблицы Orders, в которых значение поля snum совпадает с одним из полученных (1001 или 1004). Эффективность варианта с использованием подзапроса зависит от вьтолнения - от особенностей реализации той программы, с которой идет работа. В любом коммер ческом программном продукте есть часть программы, называемая оптимизатором, которая пытается найти самые эффективные способы выполнения запросов. Хороший оптимизатор преобразует версию с joiп в версию с подзапросом, но простого способа проверки, сделано это или нет, не существует. Поэтому при написании запросов луч-
 Как выполняются подзапросы? ше использовать заведомо более эффективный вариант, чем полностью полагаться на возможности оптимизатора. Можно применять IN и в тех ситуациях, когда есть абсолютная уверенность в полу чении единственного значения в результате выполнения подзапроса. IN можно также использовать там, где применим реляционный оператор сравнения. В отличие от реля ционных операторов IN не приводит к ошибке выполнения команды, когда в результате выполнения подзапроса получается не одно, а несколько значений (выходных данных). В этом есть и плюсы, и минусы. Результаты выполнения подзапроса непосредственно не видны. При абсолютной уверенности в том, что в результате выполнения подзапроса будет получено только одно значение, а в действительности их получается несколько, невозможно объяснить разницу в выходных данных, полученных в результате выполне ния основного запроса. Рассмотрим следующую команду, сходную с командой преды дущего примера: SELECT onum, amt, odate FROM Orders WHERE snum = (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001); Можно отказаться от DISТINCT, воспользовавшись IN вместо равенства. В резуль тате получается: SELECT onum, amt, odate FROM Orders WHERE snum IN (SELECT snum FROM Orders WHERE cnum = 2001); Если допушен~,. ошибка и один из заказов (orders) бьm адресован нескольким продав цам (salesperson), версия с IN выдаст все заказы для обоих продавцов. Ошибку обнару жить невозможно и отчет или решения, принятые на основе полученных данных, бьmи бы неверными. С другой стороны, вариант с равенством, будет просто признан ошибоч ным, в результате чего возникнет проблема. Затем можно выполнить подзапрос сам по себе и внимательно рассмотреть его выходные данные. Если есть уверенность в получении единственного значения в результате выполне ния подзапроса, следует использовать равенство. IN подходит для тех случаев, когда за прос может генерировать одно или несколько значений, независимо от того, что именно ожидается. Предположим, нужно знать комиссионные всех продавцов (salespeople), об служивающих покупателей (customers) в Лондоне (London): SELECT comm FROM Salespeople WHERE snum IN
 Глава 10. Вложение запросов
 (SELECT snum FROM Customers WHERE city = "London"); Выходные данные для запроса, представленные на рис. 10.5, показывают комисси онные продавца PEEL (snum = 1001), обслуживающего обоих лондонских покупате лей. Однако, такой результат получается только на основе текущих данных. Нет (очевидной) причины, в силу которой невозможно, чтобы кого-то из лондонских поку- Рис. 1О.5. Использование IN в подзапросе, результатом которого является единственное значение пателей обслуживал другой продавец. Следовательно, для данного запроса наиболее логичным вариантом является использование IN. Отказ от испош.зовання префиксов таблиц в подзапросах. Префикс имени таблицы для поля city в предыдущем примере не является необходимым, несмотря на то, что поле city есть в таблице Salespeople и в таб,1ице Customers. SQL всегда снача ла пытается найти поля в таблице (таблицах), заданной (заданных) в предложении FROM текущего запроса (подзапроса). Если поле с указанным именем здесь не най дено, то анализируется внешний запрос. В рассмотренном примере предполагается, что "city" в предложении WHERE относится к столбцу city таблицы Customers (Custoшers.city). Поскольку имя таблицы Custoшers указано в предложении FROM текущего запроса, предположение оказывается верным. От такого предположения можно избавиться, указывая имя таблицы или алиас в качестве префикса; речь об этом пойдет позднее, при обсуждении связанных подзапросов. Если есть хоть какой то шанс допустить ошибку, то лучше всего использовать префиксы.
 Как вьтолпяются подзапросы? Подзапросы используют единственный столбец. Общая черта всех подзапросов, рассмотренных в этой главе, состоит в том, что они выбирают единственный столбец. Это существенно, так как выходные данные вложенного SЕLЕСТ-предложения сравни ваются с единственным значением. Из этого следует, что вариант SELECT * нельзя ис пользовать в подзапросе. Исключением из этого правила являются подзапросы с оператором EXISTS, который рассматривается в главе 12. Использование выражений в подзаnросах. В предложении подзапроса SELECT можно использовать выражения, основанные на столбцах, а не сами столбцы. Это можно сделать, применяя операторы отношения или IN. Например, следующий за прос использует оператор отношения = (выходные данные для этого запроса пред ставлены на рис. 10.6: SELECT * FROM Customers WHERE cnum (SELECT snum + 1000 FROM Salespeople WHERE sname = ·serres'); Запрос находит всех покупателей, для которых cnuш на 1 ООО превосходит значение поля snuш для Serres. В данном случае предполагается, что в столбце snuш нет повто ряющихся значений (этого можно добиться, применяя либо UNIQUE INDEX, обсуж даемый в главе 17, либо ограничение UNIQUE, обсуждаемое в главе 18); в противном случае результатом выполнения подзапроса может оказаться множество значений. За- Рис. 10.6. Использование подзапроса с выражением
 Глава 1 О. ВлоJкение заllросов прос, рассмотренный в данном примере, вероятно, не очень полезен, если только не предполагается, что поля snum и cnum имеют значение, отличное от того, что просто служат первичными ключами. Но все это не проясняет сути дела. Подзапросы с HAVING Подзапросы можно применять также внутри предложения HAVING. В самих таких подзапросах можно использовать их собственные агрегатные функции, если они не дают множества значений, а также GROUP ВУ или HAVING. Например (выходные данные представлены на рис. 10.7): SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP ВУ rating HAVING rating > (SELECT AVG (rating) FROM Customers WHERE city = 'San Jose'); Эта команда подсчитывает количество покупателей с рейтингом, превышающим Рис. 10. 7. Поиск покупателей (customers) с рейтингом (raiting), превышающим среднее значение для San Jose среднее значение для покупателей города San Jose. Если бы были другие рейтинги, от-
 Итоги личные от указанного значения 300, то каждое отдельное значение рейтинга выводи лось бы вместе с указанием числа покупателей, имеющих такой рейтинг. Итоги Теперь вы можете применять запросы в иерархическом порядке и знаете, насколь ко использование выходных данных одного запроса для у.правления другим облегчает выполнение многих действий, как можно использовать подзапросы с операторами от ношения, а также со специальным оператором IN в предложении WHERE или HAVING внешнего запроса. Далее будет продолжен анализ подзапросов. В следующей главе рассматриваются подзапросы, выполняемые отдельно для каждой строки, определенной во внешнем за просе. В главах 12 и 13 вводится несколько специальных операторов, действующих, как и IN, на весь подзапрос, за исключением тех случаев, когда они применяются ис ключительно к подзапросам.
    
Работаем на SQL 1. Запишите запрос, который использует подзапрос для получения всех заказов по купателя с именем Cisneros. Предположим, что его персональный номер неизвес тен. 2. Запишите запрос, который выдает имена и рейтинги всех тех покупателей, кото рые сделали больше среднего числа заказов. 3. Запишите запрос, выбирающий сумму заказов каждого продавца, у которого она превышает наибольшее значение поля amount в таблице Orders. (Ответы см. в приложении А.)

Как выбрать все записи из таблицы cities где значение поля city начинается с m

SQL запросы быстро. Часть 1

Практика

Структура sql-запросов

SELECT, FROM

WHERE

GROUP BY

HAVING

ORDER BY

JOIN

Оператор SELECT в MySQL 8: полный обзор с примерами кода

Оператор WHERE

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

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

Операции соединения SQL

Инструкция INSERT (Transact-SQL)

Синтаксис

Аргументы

Замечания

Рекомендации

Рекомендации по массовому импорту данных

Использование инструкции INSERT INTO…SELECT для массового импорта данных с минимальным ведением журнала и параллелизмом

Использование предложений OPENROWSET и BULK для массового импорта данных

Типы данных

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

Обработка ошибок

Совместимость

Ограничения

Режим ведения журнала

Безопасность

Разрешения

Примеры

Базовый синтаксис

А. Вставка одной строки данных

B. Вставка нескольких строк данных

C. Вставка данных в порядке, отличном от порядка столбцов таблицы

Обработка значений столбцов

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

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

Е. Вставка данных в столбец типа uniqueidentifier с помощью функции NEWID()

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

Вставка данных из других таблиц

H. Вставка данных из других таблиц с помощью параметров SELECT и EXECUTE

I. Использование обобщенного табличного выражения WITH для определения вставляемых данных

J. Использование TOP для ограничения данных, вставляемых из исходной таблицы

Указание целевых объектов, отличных от стандартных таблиц

K. Вставка данных с указанием представления

L. Вставка данных в табличную переменную

Вставка строк в удаленную таблицу

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

О. Вставка данных в удаленную таблицу с помощью функции OPENQUERY

O. Вставка данных в удаленную таблицу с помощью функции OPENDATASOURCE

P. Вставка во внешнюю таблицу, созданную с помощью PolyBase

Массовая загрузка данных из таблиц или файлов данных

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

R. Использование функции OPENROWSET с параметром BULK для массовой загрузки данных а таблицу

Переопределение поведения по умолчанию для оптимизатора запросов с помощью указаний

S. Использование подсказки TABLOCK для указания метода блокировки

Получение результатов инструкции INSERT

T. Использование предложения OUTPUT с инструкцией INSERT

Ф. Применение предложения OUTPUT со столбцами идентификаторов и вычисляемыми столбцами

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

Ц. Вставка данных с помощью параметра SELECT

.X Указание метки с инструкцией INSERT

Y. Использование метки и указания запроса с инструкцией INSERT

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

Добавить комментарий Отменить ответ