Введение в MS SQL Server и T-SQL. Необоснованное использование хинтов в запросах

10.04.2019 Звуковые устройства

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

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

Это статья из цикла статей о языке SQL для Access. В ней описаны основы использования SQL для выборки данных и приведены примеры синтаксиса SQL.

В этой статье

Что такое SQL?

SQL - это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования, SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO и ANSI .

На языке SQL описываются наборы данных, помогающие получать ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис - это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и имеет много общих элементов с синтаксисом языка Visual Basic для приложений (VBA).

Например, простая инструкция SQL, извлекающая список фамилий контактов с именем Mary, может выглядеть следующим образом:

SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";

Примечание: Язык SQL используется не только для выполнения операций над данными, но еще и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этой статье. Дополнительные сведения см. в статье Создание и изменение таблиц или индексов с помощью запроса определения данных .

Инструкции SELECT

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

    таблицы, в которых содержатся данные;

    связи между данными из разных источников;

    поля или вычисления, на основе которых отбираются данные;

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

    необходимость и способ сортировки.

Предложения SQL

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

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

Описание

Обязательное

Определяет поля, которые содержат нужные данные.

Определяет таблицы, которые содержат поля, указанные в предложении SELECT.

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

Определяет порядок сортировки результатов.

В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение.

Только при наличии таких полей

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

Термины SQL

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

Термин SQL

Сопоставимая часть речи

Определение

Пример

идентификатор

существительное

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

Клиенты.[НомерТелефона]

оператор

глагол или наречие

Ключевое слово, которое представляет действие или изменяет его.

константа

существительное

Значение, которое не изменяется, например число или NULL.

выражение

прилагательное

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

>= Товары.[Цена]

Основные предложения SQL: SELECT, FROM и WHERE

Общий формат инструкций SQL:

SELECT field_1
FROM table_1
WHERE criterion_1
;

Примечания:

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

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

Пример в Access

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

1. Предложение SELECT

2. Предложение FROM

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

Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL.

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

SELECT , Company

Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора ("[Адрес электронной почты]" и "Компания").

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

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

В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM.

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

FROM Contacts

Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты).

В предложении FROM не указываются поля для выборки.

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

WHERE City="Seattle"

Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город="Ростов").

С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий. Дополнительные сведения об использовании этих предложений см. в следующих статьях:

Сортировка результатов: ORDER BY

Как и в Microsoft Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, вы также можете указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL.

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

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

ORDER BY Company DESC,

Примечание: По умолчанию Access сортирует значения по возрастанию (от А до Я, от наименьшего к наибольшему). Чтобы вместо этого выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC.

Дополнительные сведения о предложении ORDER BY см. в статье Предложение ORDER BY .

Работа со сводными данными: предложения GROUP BY и HAVING

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

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

Задание полей, которые не используются в агрегатной функции: предложение GROUP BY

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

Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT.

Продолжим предыдущий пример. Пусть в предложении SELECT агрегатная функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом:

GROUP BY Company

Дополнительные сведения о предложении GROUP BY см. в статье Предложение GROUP BY .

Ограничение агрегированных значений с помощью условий группировки: предложение HAVING

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

Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение):

SELECT COUNT(), Company

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

HAVING COUNT()>1

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

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

Объединение результатов запроса: оператор UNION

Оператор UNION используется для одновременного просмотра всех данных, возвращаемых несколькими сходными запросами на выборку, в виде объединенного набора.

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

Примечание: В запросах на объединение числовой и текстовый типы данных являются совместимыми.

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

Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис:

SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;

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

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;

Дополнительные сведения об объединении инструкций SELECT с помощью оператора UNION см. в статье

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

ASCII NCHAR SOUNDEX
CHAR PATINDEX SPACE
CHARINDEX REPLACE STR
DIFFERENCE QUOTENAME STUFF
LEFT REPLICATE SUBSTRING
LEN REVERSE UNICODE
LOWER RIGHT UPPER
LTRIM RTRIM

Начнем с двух взаимно обратных функций - ASCII и CHAR .

Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.

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


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

LEFT (<строковое выражение >, <целочисленное выражение >)

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

SELECT DISTINCT LEFT(name, 1) FROM Ships ORDER BY 1

А вот как, например, можно получить таблицу кодов всех алфавитных символов:

SELECT CHAR(ASCII("a")+ num-1) letter, ASCII("a")+ num - 1
FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x
CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y
CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z
) x
WHERE ASCII("a")+ num -1 BETWEEN ASCII("a") AND ASCII("z")

Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .

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


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

Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX . Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:

CHARINDEX (искомое_выражение , строковое_выражение [, стартовая_позиция ])

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

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

Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"

SELECT CHARINDEX("a",name) first_a,
CHARINDEX("a", name, CHARINDEX("a", name)+1) second_a
FROM Ships WHERE name="California"

Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой "a" символа - CHARINDEX("a", name)+1. Правильность результата - 2 и 10 - легко проверить:-).

Функция PATINDEX имеет синтаксис:

PATINDEX ("%образец %" , строковое_выражение )

Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки - % и _. При этом концевые знаки "%" являются обязательными. Например, использование этой функции в первом примере будет иметь вид


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


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

Соединение двух строковых значений в одно называется конкатенацией , и в SQL Server для этой операции используется знак "+" (в стандарте "||"). Итак,

А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав

Синтакс SQL

Этот раздел описывает основные различия в синтаксе языка SQL, используемого СУБД Firebird и MS SQL.

СУБД Firebird и MS SQL могут ссылаться на объекты базы данных (таблицы, поля и т.д.) по их именам напрямую, если имена объектов не содержат пробелы и другие недопустимые в прямой ссылке символы (например, нелатинские буквы). Для использования пробелов и других символов СУБД MS SQL использует квадратные скобки, [ и ] , а СУБД Firebird использует двойные кавычки, " . Еще одно различие - возможность использования в СУБД MS SQL схемы для ссылки на объект: база_данных.владелец_объекта.объект . СУБД Firebird не допускает такой нотации.

Внимание

СУБД MS SQL использует регистро-зависимые имена объектов, если при установке Вы выбрали использование различения регистра символов; в противном случае, имена объектов регистро-независимы. Весело? Не очень...

Подсказка

СУБД MS SQL способна работать с идентификаторами, имена которых заключены в двойные кавычки, но по умолчанию эта возможность доступна только при доступе через OLE DB и ODBC, но не при доступе через DB-Library. По этой причине такую практику работы следует избегать.

СУБД MS SQL 7 и выше поддерживает обновляемые соединения (joins) (обновление, удаление, вставка). СУБД Firebird не распознает такой синтакс.

Типы даных, конечно, различаются. Хотя обе СУБД имеют общее подмножество наиболее часто используемых типов. Этот вопрос редко вызывает проблемы при переносе базы данных.

Различаются встроенные функции. Большинство из встроенных функций СУБД MS SQL можно заменить в СУБД Firebird использованием функций, определяемых пользователем (UDFs).

Различаются форматы указания строковых констант для дат. СУБД Firebird принимает строки различных форматов, вне зависимости от используемой платформы. СУБД MS SQL, в свою очередь, использует совмещение серверо-независимых, серверо-платформенных форматов и формата настройки клиентского соединения. Дополнительно, методы доступа СУБД MS SQL обычно вводят один или два уровня, в которых строковая константа может быть преобразована в дату тем или иным образом.

В СУБД MS SQL можно определять бОльшее количество переменных окружения, чем в СУБД Firebird , но наиболее общие можно найти и в СУБД Firebird (извлечение идентификатора и имени пользователя). Единственная важная переменная, которая отсутствует в СУБД Firebird , - это переменная, возвращающая количество строк последней операции (с версии 1.5 СУБД Firebird такая переменная введена - прим. перев.).

Важное различие было в том, что СУБД Firebird 1.0 не поддерживала оператор CASE СУБД MS SQL. Иногда можно было заменить его функциональность использованием хранимой процедуры. Начиная с версии 1.5, СУБД Firebird поддерживает использование оператора CASE .

Небольшое различие между СУБД еще и в том, что СУБД MS SQL не использует разделителей для операторов, что может служить источником трудно обнаруживаемых ошибок при переходе, особенно при использовании множества скобок. СУБД Firebird в скриптах требует завершать каждый оператор точкой с запятой (если не определен другой разделитель - прим. перев.), поэтому ошибки легче обнаружить.

Обе СУБД MS SQL и Firebird поддерживают комментарии, заключенные между разделителями /* и */ . СУБД MS SQL также поддерживает синтакс «два дефиса » -- для однострочного комментария. Некоторые утилиты для СУБД Firebird также поддерживают такой синтакс.

WHILE

Оператор WHILE существует в обоих СУБД Firebird и MS SQL, но с некоторыми различиями. Не существует операторов BREAK или CONTINUE , но их можно эмулировать при помощи дополнительных конструкций. Так же есть небольшое различие в используемом синтаксисе: СУБД Firebird требует наличия ключевого слова DO после условия цикла. Сравните следующие эквивалентные части кода.

/* Синтакс Firebird. */ WHILE (i < 3) DO BEGIN i = i + 1; j = j * 2; END /* Синтакс MS SQL. */ WHILE (i < 3) BEGIN SET @i = @i + 1 SET @j = @j * 2 END

RETURN

Оператор RETURN в СУБД MS SQL возвращает значение целочисленной переменной и прекращает выполнение. В СУБД Firebird существует оператор EXIT , который передает управление на заключительный END хранимой процедуры. Однако здесь нет скрытой возвращаемой переменной, поэтому, если Вам необходимо вернуть некоторое значение (что опционально в СУБД MS SQL), Вы должны явно объявить возвращаемую переменную в процедуре.

WAITFOR

Оператор WAITFOR в СУБД MS SQL приостанавливает выполнение на некоторое время или до наступления указанного времени. Что-то подобное можно осуществить при помощи функций, определяемых пользователем (UDFs), в СУБД Firebird . Но в обоих СУБД использование такого оператора следует исключить, поскольку взаимодействие с клиентом полностью приостанавливается (в СУБД Firebird это может привести и к приостановке обслуживания всех соединений, а не только соединения, вызвавшего аналог указанного оператора - прим. перев.).

Стандартные операторы

Стандартными операторами, имеющимися в обоих СУБД, являются SELECT , INSERT , UPDATE и DELETE . СУБД Firebird и MS SQL поддерживают их, но в СУБД MS SQL есть несколько нестандартных расширений этих операторов, о которых необходимо рассказать на случай их использования.

В СУБД Firebird оператор SELECT не позволяет использовать ключевое слово INTO для создания новой таблицы «на лету ». Вместо этого, ключевое слово INTO используется для связи результата запроса с переменной.

/* Синтаксис MS SQL для присваивания значения поля переменной. */ SELECT @my_state = state FROM authors WHERE auth_name = "John" /* Синтаксис Firebird. */ SELECT state INTO:state /* --> обратите внимание на ":" перед именем переменной */ FROM authors WHERE auth_name = "John"

В СУБД MS SQL 7 и выше в операторе SELECT можно указвать спецификатор TOP для органичения возвращаемого набора данных. Эта функция в настоящий момент находится в стадии разработки для СУБД Firebird . (Спецификаторы FIRST и SKIP в СУБД Firebird введены, начиная с версии 1.5. - прим. перев.)

Обе СУБД MS SQL и Firebird поддерживают обычный синтаксис оператора INSERT и оператора INSERT..SELECT .

Обе СУБД MS SQL и Firebird поддерживают обычный синтаксис оператора UPDATE . СУБД MS SQL также поддерживает синтаксис оператора UPDATE , в котором выполняется соединение (join) и производится обновление одной из таблиц соединения. Можно думать об этом как об условии WHERE на стероидах. Если такая функция очень нужна, то ее можно реализовать в СУБД Firebird с использованием представлений (views).

Обе СУБД MS SQL и Firebird поддерживают обычный синтаксис оператора DELETE . СУБД MS SQL также поддерживает оператор TRUNCATE TABLE , который более эффективен (но и опаснее), чем оператор DELETE . (СУБД MS SQL также поддерживает синтаксис оператора DELETE , в котором выполняется соединение. - прим. перев.)

/* Синтаксис MS SQL для удаления всех записей my_table. */ TRUNCATE TABLE my_table /* ...или... */ DELETE FROM my_table /* Синтаксис Firebird. */ DELETE FROM my_table

Использование транзакций

В СУБД Firebird в DSQL (dynamic SQL) транзакции «напрямую » не используются. Именованные транзакции в этом случае недоступны совсем. (Операторы DSQL выполняются в контексте транзакций, запущенных и контролируемых клиентским приложением.- прим. перев.) Синтаксис обоих СУБД поддерживает ключевое слово WORK для совместимости.

В большинстве случаев проблем с транзакциями не должно возникать: явное управление транзакциями «на месте » в СУБД MS SQL используется обычно из-за отсутствия поддержки управлением через исключения (exceptions).

Подсказка

В СУБД MS SQL есть глобальная переменная XACT_ABORT , которая управляет откатом транзакции при возникновении ошибки времени выполнения (run-time error). В противном случае Вам необходимо проверять значение переменной @@ERROR после выполнения каждого оператора.

В общем, большинство проблем, связанных с уровнями изоляции транзакций в СУБД MS SQL, пропадают при переходе на СУБД Firebird . Соперничество между «читателями » и «писателями » минимально за счет использования многоверсионной архитектуры (multigeneration architecture, MGA).

В СУБД MS SQL курсоры используются, в основном, для перемещения по результатам запросов, чтобы выполнить с этими результатами некоторые действия. Кроме синтаксиса, нет большой разницы для выполнения одной и той же задачи. Хотя существуют опции для перемещения вперед и назад, на практике используются, по большей части, однонаправленные курсоры.

/* Синтакс MS SQL. */ DECLARE my_cursor CURSOR FOR SELECT au_lname FROM authors ORDER BY au_lname DECLARE @au_lname varchar(40) OPEN my_cursor FETCH NEXT FROM my_cursor INTO @au_lname WHILE @@FETCH_STATUS = 0 BEGIN /* Сделать что-то интересное с @au_lname. */ FETCH NEXT FROM my_cursor END CLOSE my_cursor DEALLOCATE my_cursor /* Синтакс Firebird. */ DECLARE VARIABLE au_lname VARCHAR(40); ... FOR SELECT au_lname FROM authors ORDER BY au_lname INTO:au_lname DO BEGIN /* Сделать что-то интересное с au_lname. */ END

Заметьте, что СУБД MS SQL может размещать курсоры в переменных, и передавать эти переменные как параметры; это невозможно в СУБД Firebird .

Программирование на T - SQL

Синтаксис и соглашения T-SQL

Правила формирования идентификаторов

Все объекты в SQL Server имеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры.

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

Завершение инструкции

Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке T-SQL точка с запятой не обязательна.

Комментарии

Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:

Это однострочный комментарий стиля ANSI

Также комментарии стиля ANSI могут вставляться в конце строки инструкции:

SELECT CityName – извлекаемые столбцы

FROM City – исходная таблица

WHERE IdCity = 1; -- ограничение на строки

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

Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы.

многострочного

комментария

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

Пакеты T-SQL

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

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

Файл сценария SQL и окно анализатора запросов (Query Analyzer) может содержать несколько пакетов. В данном случае все пакеты разделяют ключевые слова терминаторов. По умолчанию этим ключевым словом является GO, и оно должно быть единственным в строке. Все другие символы (даже комментарии) нейтрализуют разделитель пакета.

Отладка T-SQL

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

В утилиту Management Studio версии SQL Server 2005 не включен отладчик языка T-SQL, - он присутствует в пакете Visual Studio.

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

SELECT CityName

FROM City

WHERE IdCity = 1;

PRINT "Контрольная точка" ;

Результирующий набор данных отобразится в сетке и будет состоять из одной строки. В то же время во вкладке «Сообщения» отобразится следующий результат:

(строк обработано: 1)

Контрольная точка

Переменные

Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:

DECLARE @Имя_Переменной Тип_Данных [,

@Имя_Переменной Тип_Данных, …]

Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символов Unicode, можно использовать следующую инструкцию:

DECLARE @UStr varchar (16)

Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLARE через запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменные a и b:

DECLARE

@a int ,

@b int

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

Задание значений переменных

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

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

SET @a = 1;

SET @b = @a * 1.5

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

DECLARE @c int

SET @c = COUNT (*) FROM City

SELECT @c

а следующий оператор выполняется вполне успешно:

DECLARE @c int

SET @c = (SELECT COUNT (*) FROM City)

SELECT @c

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

DECLARE @c int

SELECT @c = COUNT (*) FROM City

SELECT @c

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

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

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

  • Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.

Использование переменных в запросах SQL

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

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

DECLARE @IdProd int ;

SET @IdProd = 1;

SELECT

FROM Product

WHERE IdProd = @IdProd;

Глобальные системные переменные

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

SELECT @@CONNECTIONS

Здесь используется глобальная переменная @@CONNECTIONS для извлечения количества подключений к SQL Server со времени запуска программы.

Среди наиболее часто применяемых системных переменных можно отметить следующие:

  • @@ERROR - Содержит номер ошибки, возникшей при выполнении последнего оператора T-SQL в текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.
  • @@IDENTITY - Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператора INSERT. Если в последнем операторе INSERT не произошла выработка идентификационного значения, системная переменная @@IDENTITY содержит NULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.
  • @@ROWCOUNT - Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператора DELETE с конструкцией WHERE количество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.

! Следует отметить, что с версии SQL Server 2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.

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

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

Оператор IF. . . ELSE

Операторы IF. . .ELSE действуют в языке T-SQL в основном так же, как и в любых других языках программирования. Общий синтаксис этого оператора имеет следующий вид:

IF Логическое выражение

SQL инструкция I BEGIN Блок SQL инструкций END

SQL инструкция | BEGIN Блок SQL инструкций END]

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

Следует учитывать, что выполняемым по условию считается только тот оператор, который непосредственно следует за оператором IF (ближайшим к нему). Вместо одного оператора можно предусмотреть выполнение по условию нескольких операторов, объединив их в блок кода с помощью конструкции BEGIN…END.

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

IF 1 = 0

PRINT "Первая строка"

PRINT "Вторая строка"

Необязательная команда ELSE позволяет задать инструкцию, которая будет выполнена в случае, если условие IF не будет выполнено. Подобно IF, оператор ELSE управляет только непосредственно следующей за ним командой или блоком кода заключенным между BEGIN…END.

Несмотря на то, что оператор IF выглядит ограниченным, его предложение условия может включать в себя мощные функции, подобно предложению WHERE. В частности это выражения IF EXISTS().

Выражение IF EXISTS() использует в качестве условия наличие какой-либо строки, возвращенной инструкцией SELECT. Так как ищутся любые строки, список столбцов в инструкции SELECT можно заменить звездочкой. Этот метод работает быстрее, чем проверка условия @@ROWCOUNT>0, потому что не требуется подсчет общего количества строк. Как только хотя бы одна строка удовлетворяет условию IF EXISTS(), запрос может продолжать выполнение.

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

IF EXISTS (SELECT * FROM WHERE IdCust = 1)

PRINT "Невозможно удалить клиента поскольку в базе имеются связанные с ним записи"

ELSE

WHERE IdCust = 1

PRINT "Удаление произведено успешно"

Операторы WHILE, BREAK и CONTINUE

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

Оператор WHILE имеет следующий синтаксис:

WHILE Логическое выражение

SQL инструкция I

Блок SQL инструкций

Безусловно, с помощью оператора WHILE можно обеспечить выполнение в цикле только одного оператора (по аналогии с тем, как обычно используется оператор IF), но на практике конструкции WHILE, за которыми не следует блок BEGIN. . .END, соответствующий полному формату оператора, встречаются редко.

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

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

Следующий короткий сценарий демонстрирует использование оператора WHILE для создания цикла:

DECLARE @Temp int ;

SET @Temp = 0;

WHILE @Temp < 3

BEGIN

PRINT @Temp;

SET @Temp = @Temp + 1;

Здесь в цикле целочисленная переменная @Temp увеличивается с 0 до 3 и на каждой итерации ее значение выводится на экран.

Оператор RETURN

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

Последнее обновление: 24.06.2017

SQL Server является одной из наиболее популярных систем управления базами данных (СУБД) в мире. Данная СУБД подходит для самых различных проектов: от небольших приложений до больших высоконагруженных проектов.

SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. А текущей версией является версия 16, которая вышла в 2016 году и которая будет использоваться в текущем руководстве.

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

SQL Server характеризуется такими особенностями как:

    Производительность. SQL Server работает очень быстро.

    Надежность и безопасность. SQL Server предоставляет шифрование данных.

    Простота. С данной СУБД относительно легко работать и вести администрирование.

Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Нередко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.

Для организации баз данных MS SQL Server использует реляционную модель. Эта модель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных.

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

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

Через ключи одна таблица может быть связана с другой, то есть между двумя таблицами могут быть организованы связи. А сама таблица может быть представлена в виде отношения ("relation").

Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.

Изначально язык SQL был разработан в компании IBM для системы баз данных, которая называлась System/R. При этом сам язык назывался SEQUEL (Structured English Query Language). Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как "сиквел".

В 1979 году компания Relational Software Inc. разработала первую систему управления баз данных, которая называлась Oracle и которая использовала язык SQL. В связи с успехом данного продукта компания была переименована в Oracle.

Впоследствии стали появляться другие системы баз данных, которые использовали SQL. В итоге в 1989 году Американский Национальный Институт Стандартов (ANSI) кодифицировал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2011 году. Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга.

Выделяются две разновидности языка SQL: PL-SQL и T-SQL. PL-SQL используется в таких СУБД как Oracle и MySQL. T-SQL (Transact-SQL) применяется в SQL Server. Собственно поэтому в рамках текущего руководства будет рассматриваться именно T-SQL.

В зависимости от задачи, которую выполняет команда T-SQL, он может принадлежать к одному из следующих типов:

    DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д. В общем определяют данные.

    В частности, к этому типу мы можем отнести следующие команды:

    • CREATE : создает объекты базы данных (саму базу даных, таблицы, индексы и т.д.)

      ALTER : изменяет объекты базы данных

      DROP : удаляет объекты базы данных

      TRUNCATE : удаляет все данные из таблиц

    DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбору данных, их обновление, добавление, удаление - в общем все те команды, с помощью которыми мы можем управлять данными.

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

    • SELECT : извлекает данные из БД

      UPDATE : обновляет данные

      INSERT : добавляет новые данные

      DELETE : удаляет данные

    DCL (Data Control Language / Язык управления доступа к данным). К этому типу относят команды, которые управляют правами по доступу к данным. В частности, это следующие команды:

    • GRANT : предоставляет права для доступа к данным

      REVOKE : отзывает права на доступ к данным