Простая выборка данных из базы данных MS SQL Server

Основы оператора SELECT

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

Оператор SELECT языка Transact-SQL позволяет получать существующие данные из базы данных SQL Server. Большинство операторов SELECT описывают четыре главных свойства результирующего набора:

• столбцы, которые должны войти в результирующий набор;

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

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

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

Например, оператор SELECT в следующем сценарии получает из таблицы Persons (предполагается, что она уже создана в БД) идентификатор персоны, ее имя и возраст, причем только тех, кто старше 40 лет:

SELECT PersonId, Name, PersonAge

FROM Persons

WHERE PersonAge > 40

ORDER BY PersonAge ASC

GO

Конструкция SELECT в этом примере определяет столбцы, из которых следует извлечь значения, а конструкция FROM — таблицу, в которой находятся эти столбцы. Конструкция WHERE ограничивает результирующий набор теми персонами, значение PersonAge которых больше 40. Конструкция ORDER BY задает сортировку результирующего набора по возрастанию на основе значения столбца PersonAge. Полный синтаксис оператора SELECT достаточно сложен, однако в общем виде главные конструкции можно записать следующим образом:

SELECT список_выбора

[INTO имя_новой_таблицы]

FROM список_таблиц

[WHERE условия_поиска]

[GROUP BY группировка_по_списку]

[HAVING условия_поиска ]

[ORDER BY поле_для_сортировки [ASC DESC]]

Далее мы подробно расскажем о каждой конструкции и покажем на примерах, как определять конструкции, получающие определенные данные из базы данных SQL Server. Более подробная информация — в SQL Server Books Online.

Конструкция SELECT

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

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

Ключевое слово DISTINCT запрещает вывод в результирующем наборе повторяющихся строк. Например, в таблице Persons в нашей базе данных могут содержаться повторяющиеся значения в столбце BitrhPlace. Следующий код позволяет получить список значений BirthPlace без дубликатов:

SELECT DISTINCT BirthPlace, FirstName, LastName

FROM Persons

ORDER BY BirthPlace

GO

Ключевое слово ТОР n задает первые n строк результирующего набора, которые необходимо возвратить. Если задан параметр ORDER BY, выбор строк выполняется после упорядочения результирующего набора. Значение n указывает число возвращаемых строк (если не определено ключевое слово PERCENT). Если задано ключевое слово PERCENT, то n — это процент возвращаемых строк от общего числа строк в результирующем наборе.

Например, в следующем примере оператор SELECT возвращает первые 5 упорядоченных по алфавиту городов из таблицы Persons.

SELECT DISTINCT TOP 5 BirthPlace, FirstName, LastName

FROM Persons

ORDER BY BirthPlace

GO

Можно сделать оператор SELECT более читабельным, присвоив таблице псевдоним (его также называют связанным именем или переменной-диапазоном). Псевдоним можно присвоить таблице как с помощью ключевого слова AS, так и без него:

• имя_таблицы AS псевдоним_таблицы

• имя_таблицы псевдоним_таблицы

В следующем примере таблице Persons присваивается псевдоним р:

USE people

SELECT p.PersonId, p.Name

FFOM persons AS p

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

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

SELECT FirstName + ' ' + LastName AS "Person Name",

IDENTITYCOL AS "Person ID",

HomePhone,

BirthPlace

FROM dbo.Persons

ORDER BY LastName, FirstName ASC

Этот оператор помещает в один столбец имена и фамилии персон, разделенные пробелом. Имя столбца, в котором хранятся имена людей, — Person Name. В результирующий набор также войдет столбец с идентификатором, который будет назван Person ID; а также столбцы HomePhone и BirthPlace. Результирующий набор упорядочен сначала по фамилии, а затем по имени.

Конструкция INTO

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

В следующем сценарии оператор SELECT извлекает значения из столбцов FirstName и LastName таблицы Persons:

SELECT FirstName, LastName

INTO PersonNames

FROM Employees

Сгенерированный результирующий набор создает таблицу PersonNames. В новой таблице столбцы FirstName и LastName будут содержать значения из таблицы Persons.

Результирующий набор не будет выводиться в SQL Server Management Studio на вкладке Results, если не выполнить явный запрос к новой таблице.

Конструкция FROM

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

Конструкция FROM представляет собой список имен таблиц, представлений и конструкций JOIN, разделенных запятыми. В следующем примере в операторе SELECT конструкция FROM задает таблицу Persons:

SELECT * FROM Persons

GO

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

Конструкции WHERE, GROUP BY и HAVING

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

SELECT PersonId, LastName, FirstName

FROM Persons

WHERE BirthPlace = 'Krasnoyarsk'

GO

Конструкция HAVING, как правило (но не обязательно), используется вместе с конструкцией GROUP BY. Конструкция HAVING задает дополнительные фильтры, которые применяются после завершения фильтрации, определяемой конструкцией WHERE. В следующем сценарии в операторе SELECT использованы конструкции WHERE, GROUP BY и HAVING:

SELECT OrdD1OrderId AS OrderId,

SUM(OrdD1.Quantity) AS "Units Sold",

SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue

FROM [Order Details] AS OrdD1

WHERE OrdD1OrderId IN (SELECT DISTINCT OrdD2.OrderId

FROM [Order Details] AS OrdD2

WHERE OrdD2.UnitPrice > $1000)

GROUP BY OrdD1.OrderId

HAVING SUM(OrdD1.Quantity) > 50

GO

Здесь конструкция WHERE возвращает заказы, стоимость которых больше $1000, а далее конструкция HAVING ограничивает результат, отбирая заказы на более чем 50 единиц товара. Конструкция GROUP BY ограничивает строки для каждого конкретного значения поля OrderId.

Конструкция GROUP BY

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

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

Если в операторе SELECT имеется конструкция GROUP BY, SQL Server налагает ограничения на элементы списка выбора. В списке выбора могут быть лишь те группирующие столбцы и выражения, которые возвращают только одно значение для каждого значения группирующих столбцов, например агрегатные функции (векторные агрегаты), одним из параметров которых является имя столбца.

Как правило, конструкция HAVING используется с конструкцией GROUP BY, хотя ее разрешается применять и отдельно. Любое выражение может стать группирующим, если оно не содержит агрегатных функций.

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

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

Понимание верной последовательности, в которой применяются конструкции WHERE, GROUP BY и HAVING, помогает создавать достаточно эффективные запросы:

• конструкция WHERE фильтрует строки, которые являются результатом операций, заданных в конструкции FROM;

• выходная информация конструкции WHERE группируется с помощью конструкции GROUP BY;

• строки сгруппированного результата фильтруются средствами конструкции HAVING.

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

Конструкция ORDER BY

Конструкция ORDER BY сортирует результат запроса по одному или нескольким полям. Сортировка может быть как по возрастанию (ASC), так и по убыванию (DESC). Если не задан ни один из видов сортировки, по умолчанию предполагается ASC. Если в конструкции ORDER BY названо несколько столбцов, выполняется вложенная сортировка.

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

SELECT PersonId, LastName, FirstName, Age

FROM Persons

ORDER BY LastName DESC, FirstName, Age

GO

Пакеты, хранимые процедуры и триггеры

Пакет — это группа из одного или нескольких операторов Transact-SQL, которые приложение одновременно посылает на SQL Server для исполнения. SQL Server компилирует операторы пакета в единую исполнимую единицу (план исполнения – Execution Plan). После этого по очереди выполняются операторы этого плана.

Ошибка при компиляции, например синтаксическая, останавливает процесс компиляции плана исполнения. В этом случае ни один из операторов пакета исполнен не будет.

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

• большинство ошибок периода выполнения останавливают исполнение текущего и последующих операторов пакета;

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

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

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

При обработке пакетов действуют следующие правила:

• операторы CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER и CREATE VIEW не могут соседствовать в пакетах с другими операторами. Пакет должен начинаться с оператора CREATE. Все следующие за ним операторы будут интерпретированы как часть определения, созданного первым оператором CREATE;

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

• если оператор EXECUTE — первый оператор пакета, ключевое слово EXECUTE не требуется. Но оно необходимо, когда оператор EXECUTE не является первым оператором пакета.

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

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

• Все операторы SQL, которые приложение отправляет на сервер как единицу исполнения, составляют единый пакет и генерируют один план исполнения.

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

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

• Строка, исполняемая системной хранимой процедурой sp_executesql, — это пакет, при компиляции которого получается один план исполнения.

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

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

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

• оператор EXECUTE, исполняющий хранимую процедуру;

• вызов процедуры sp_executesql для обработки строки;

• оператор EXECUTE, обрабатывающий строку;

• оператор UPDATE, ссылающийся на таблицу, у которой есть триггер на обновление.

EXEC FirstProcedure

EXEC sp_executesql N'SELECT * FROM AdventureWorks.HumanResources.Employee

WHERE ManagerID = @level',

N'@level tinyint',

@level = 109;

EXEC ('sp_who')

UPDATE Persons

SET PersonName = 'kuku'

GO

Хранимая процедура — это группа операторов Transact-SQL, которая компилируется один раз и после этого может выполняться многократно. Такая функциональность повышает производительность, поскольку отпадает необходимость в перекомпиляции операторов Transact-SQL.

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

Операторы CREATE PROCEDURE и CREATE TRIGGER не могут располагаться в нескольких пакетах. Другими словами, хранимая процедура или триггер всегда создаются в одном пакете и компилируются в план исполнения.

SQL Server хранит только исходный текст хранимых процедур и триггеров. Когда хранимая процедура или триггер исполняется первый раз, исходный текст компилируется в план исполнения. Если до того, как план исполнения устареет и будет удален из памяти, хранимая процедура или триггер исполняется снова, реляционный механизм обнаруживает существующий план и использует его повторно. Если план устарел и удален из памяти, создается новый план исполнения. Этот процесс напоминает обработку SQL Server всех операторов SQL. Увеличение производительности при применении хранимых процедур и триггеров объясняется постоянством их SQL-операторов, что позволяет SQL Server использовать для них существующие планы исполнения.

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

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

Дата добавления: 2014-01-11