1

О разделе
Данный раздел посвящен реляционным системам управления баз данных и работе с
ними. Редкое приложение сегодня обходится без баз данных. И наиболее
распространенным типом баз данных являются реляционные. К реляционным СУБД
относят такие системы, как MS SQL Server, Oracle, MySQL, PostgreSQL, SQLite и ряд
других. Для работы с реляционными базами данных и выполнения запросов
применяется язык SQL. Его наиболее популярные разновидности: T-SQL и PL-SQL.

В данном разделе имеются материалы по работе с СУБД MS SQL

Server, PostgreSQL и MySQL.

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

данных.

Что нового
Начато добавление материалов по MySQL

11.05.2018

Начато добавление материалов по PostgreSQL

17.03.2018

Добавлена глава про Триггеры в MS SQL Server

09.11.2017

Добавлена глава про Хранимые процедуры в MS SQL Server

14.08.2017

Добавлена глава про Представления и табличные объекты в T-SQL

14.08.2017

Добавлена глава про Переменные и управляющие конструкции в T-SQL

14.08.2017
 2

Руководство по MS SQL Server 2017

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

1. Глава 1. Введение в MS SQL Server и T-SQL

1. Что такое SQL Server и T-SQL

2. Установка MS SQL Server 2016

3. Установка SQL Server Management Studio

2. Глава 2. Начало работы с MS SQL Server

1. Создание базы данных

2. Создание таблиц

3. Первый запрос на T-SQL

3. Глава 3. Основы T-SQL. DDL

1. Создание и удаление базы данных

2. Создание и удаление таблиц

3. Типы данных T-SQL

4. Атрибуты и ограничения столбцов и таблиц

5. Внешние ключи

6. Изменение таблицы

7. Пакеты. Команда GO

4. Глава 4. Основы T-SQL. DML

1. Добавление данных. Команда INSERT

2. Выборка данных. Команда SELECT

3. Сортировка. ORDER BY

4. Извлечение диапазона строк

5. Фильтрация. WHERE

6. Операторы фильтрации
 3

7. Обновление данных. Команда UPDATE

8. Удаление данных. Команда DELETE

5. Глава 5. Группировка

1. Агрегатные функции

2. Операторы GROUP BY и HAVING

3. Расширения SQL Server для группировки

6. Глава 6. Подзапросы

1. Выполнение подзапросов

2. Подзапросы в основных командах SQL

3. Оператор EXISTS

7. Глава 7. Соединение таблиц

1. Неявное соединение таблиц

2. Inner Join

3. Outer Join

4. Группировка в соединениях

5. UNION

6. EXCEPT

7. INTERSECT

8. Глава 8. Встроенные функции

1. Функции для работы со строками

2. Функции для работы с числами

3. Функции по работе с датами и временем

4. Преобразование данных

5. Функции CASE и IIF

 4

6. Функции NEWID, ISNULL и COALESCE

9. Глава 9. Переменные и управляющие конструкции

1. Переменные в T-SQL

2. Переменные в запросах

3. Условные выражения

4. Циклы

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

10. Глава 10. Представления и табличные объекты

1. Представления

2. Обновляемое представление

3. Табличные переменные

4. Временные и производные таблицы

11. Глава 11. Хранимые процедуры

1. Создание и выполнение процедур

2. Параметры в процедурах

3. Выходные параметры и возвращение результата

12. Глава 12. Триггеры

1. Определение триггеров

2. Триггеры для операций INSERT, UPDATE, DELETE

3. Триггер INSTEAD OF

Введение в MS SQL Server и T-SQL

Что такое SQL Server и T-SQL
 5
Последнее обновление: 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 году Эдгаром Коддом. А на
сегодняшний день она фактически является стандартом для организации баз данных.

Реляционная модель предполагает хранение данных в виде таблиц, каждая из

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

Для идентификации каждой строки в рамках таблицы применяется первичный ключ

(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 / Язык определения данных). К этому типу

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

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

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

индексы и т.д.)
 7

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

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

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

 DML (Data Manipulation Language / Язык манипуляции данными). К этому типу

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

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

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

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

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

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

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

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

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

Установка MS SQL Server 2017

Последнее обновление: 10.10.2017


MS SQL Server доступен в различных вариациях. Прежде всего, это MS SQL Server
Enterprise - полный выпуск, нацеленный на использование в реальных проектах.
Именно он используется на различных хостингах и серверах баз данных. Однако он
доступен только в платной версии (не считая триального периода) и стоит довольно
приличных денег.

Для простых приложений также может хватить и выпуска Express: он бесплатный. К

тому же у него есть преимущество - его можно ставить в качестве реального сервера
 8

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

сравнению с полной версией.

И также есть MS SQL Server Developer Edition. Это полнофункциональный выпуск,

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

Итак, установим MS SQL Server 2017 Developer Edition. Для этого перейдем по
адресу [Link]
При доступе может потребоваться учетная запись Microsoft. В этом случае надо
осуществить вход с помощью учетной записи Microsoft.

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

Здесь выберем первый пункт "New SQL Server stand-alone installation or add features to
an existing installation". Далее с помощью последовательности шагов нам надо будет
установить опции установки.

Прощелкаем до пункта "Product Key". На этом этапе надо ввести ключ, либо указать
один из бесплатных выпусков. Здесь мы указываем выпуск "Developer" и переходим к
новому шагу по кнопке Next.

Далее надо будет принять лицензионное соглашение. И затем прощелкаем до шага

"Feature Selection". На этом этапе предлагается выбрать компоненты для установки.
Здесь отметим все компоненты, учитывая при этом объем свободной памяти:

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

установки, где надо выполнить какие-либо настройки. В моем случае выбраны все
компоненты. Поэтому в дальнейшем рассмотрим тот случай, если выбраны все
компоненты.
 9

Далее на шаге "Instance Configuration" нам надо будет указать название и ID

запускаемой сущности SQL Server.

Для имени указываем опцию Default instance, а для ID

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

Затем прощелкаем последующие два шага с опциями по умолчанию до "Database

Engine Configuration". С помощью кнопки Add Current User здесь добавим текущего
пользователя в качестве администратора для сервера.

На следующем шаге "Analysis Services Configuration" также добавим текущего

пользователя в качестве администратора для функции Analysis Services:

На следующих двух шагах оставим настройки по умолчанию. И далее на шаге

"Distributed Replay Controller" аналогично добавим текущего пользователя

На всех последующих шагах оставим настройки по умолчанию и на самом последнем

экране для установки нажмем на кнопку Install:

Спустя некоторое время MS SQL Server будет установлен.

Итак, мы установили SQL Server 2017, при этому назначили для него идентификатор
"MSSQLSERVER". Следует отметить, что перед подключением к нему, надо убедиться,
что он запущен. Для этого можно открыть окно служб:

Если он не запущен, там же в панели служб мы его может запустить, и после этого мы
сможем с ним работать.

Установка SQL Server Management Studio

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

Для удобного управления базами данных и различными опциями и настройками в MS

SQL Server установим специальное средство администрирования, которое
называется SQL Server Management Studio (SSMS). Данную программу можно
использовать для создания баз данных и их таблиц, написания и выполнения
запросов к бд, а также для много другого.

Чтобы установить SSMS, перейдем на

страницу [Link]
studio-ssms. Ближе к низу станицы найдем ссылки на версии для различных локалей.
Загрузим версию для английского языка (либо при желании можно выбрать
локализованную версию на любом другом языке):

Несмотря на то, что эта версия SSMS имеет номер 17, она подходит и к MS SQL Server
2016 и даже к более ранним версиям сервера.

После загрузки запустим программу установки SSMS:

Для установки нажмем на кнопку Install.

После установки найдем SQL Server Management Studio в меню Пуск среди
установленных программ в подпункте Microsoft SQL Server Tools 2017:

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

серверу.

В поле "Server name" выберем в выпадающем списке "Browse for more...". И нам
откроется окно, где необходимо будет выбрать нужный сервер:
 11

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

выпуск Developer. Но по имени я могу понять, что первый элемент представляет
Express, и соответственно мне надо выбрать второй элемент. Если на локальном
компьютере установлен только один выпуск, то соответственно выбирать не
придется.

После выбора сервера его название отобразится в поле "Server name". И далее для
подключения к нему необходимо будет нажать на кнопку Connect:

И после успешного подключения программа откроет содержимое сервера - все его

базы данных и другие компоненты:

Начало работы с MS SQL Server

Создание базы данных
Последнее обновление: 26.06.2017

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

 Таблицы: хранят собственно данные

 Представления (Views): выражения языка SQL, которые возвращают набор

данных в виде таблицы

 Хранимые процедуры: выполняют код на языке SQL по отношению к данным

к БД (например, получает данные или изменяет их)

 Функции: также код SQL, который выполняет определенную задачу

В SQL Server используется два типа баз данных: системные и пользовательские.

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

Собственно это те базы данных, которые мы будем создавать и с которыми мы будем

работать.

Системные базы данных

В MS SQL Server по умолчанию создается четыре системных баз данных:

 master: эта главная база данных сервера, в случае ее отсутствия или

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

 model: эта база данных представляет шаблон, на основе которого создаются

другие базы данных. То есть когда мы создаем через SSMS свою бд, она
создается как копия базы model.

 msdb: хранит информацию о работе, выполняемой таким компонентом как

планировщик SQL. Также она хранит информацию о бекапах баз данных.

 tempdb: эта база данных используется как хранилище для временных

объектов. Она заново пересоздается при каждом запуске сервера.

Все эти базы можно увидеть через SQL Server Management Studio в узле Databases ->
System Databases:

Эти базы данных не следует изменять, за исключением бд model.

Если на этапе установки сервера был выбран и установлен компонент PolyBase, то

также на сервере по умолчанию будут расположены еще три базы данных, которые
используется этим компонентом: DWConfiguration, DWDiagnostics, DWQueue.

Создание базы данных в SQL Management Studio

Теперь создадим свою базу данных. Для этого мы можем использовать скрипт на
языке SQL, либо все сделать с помощью графических средств в SQL Management
Studio. В данном случае мы выберем второй способ. Для этого откроем SQL Server
Management Studio и нажмем правой кнопкой мыши на узел Databases. Затем в
появившемся контекстном меню выберем пункт New Database:

После этого нам открывается окно для создания базы данных:

 13

В поле Database необходимо ввести название новой бд. Пусть у нас база данных
называется university.

Следующее поле Owner задает владельца базы данных. По умолчанию оно имеет
значение <defult>, то есть владельцем будет тот, кто создает эту базу данных.
Оставим это поле без изменений.

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

 Logical Name: логическое имя, которое присваивается файлу базы данных.

 File Type: есть несколько типов файлов, но, как правило, основная работа
ведется с файлами данных (ROWS Data) и файлом лога (LOG)

 Filegroup: обозначет группу файлов. Группа файлов может хранить множество

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

 Initial Size (MB): устанавливает начальный размер файлов при создании

(фактический размер может отличаться от этого значения).

 Autogrowth/Maxsize: при достижении базой данных начального размера SQL

Server использует это значение для увеличения файла.

 Path: каталог, где будут храниться базы данных.

 File Name: непосредственное имя физического файла. Если оно не указано, то

применяется логическое имя.

После ввода названия базы данных нажмем на кнопку ОК, и бд будет создана.

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

Создание таблиц
Последнее обновление: 26.06.2017



 14

Ключевым объектом в базе данных являются таблицы. Таблицы состоят из строк и

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

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

Для этого раскроем узел базы данных university в SQL Server Management Studio,
нажмем на его подузел Tables правой кнопкой мыши и далее в контексто меню
выберем New -> Table...:

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

необходимо ввести данные о столбцах таблицы. Дизайнер содержит три поля:

 Column Name: имя столбца

 Data Type: тип данных столбца. Тип данных определяет, какие данные могут
храниться в этом столбце. Например, если столбец представляет числовой тип,
то он может хранить только числа.
 Allow Nulls: может ли отсутствовать значение у столбца, то есть может ли он
быть пустым

Допустим, нам надо создать таблицу с данными учащихся в учебном заведении. Для
этого в дизайнере таблицы четыре столбца: Id, FirstName, LastName и Year, которые
будут представлять соответственно уникальный идентификатор пользователя, его
имя, фамилию и год рождения. У первого и четвертого столбца надо указать тип int
(то есть целочисленный), а у столбцов FirstName и LastName -
тип nvarchar(50) (строковый).

Затем в окне Properties, которая содержит свойства таблицы, в поле Name надо
ввести имя таблицы - Students, а в поле Identity ввести Id, то есть тем самым
указывая, что столбец Id будет идентификатором.

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

того, стоит использовать название сущности в единственном или множественном

числе (Student или Students). В данном случае вопрос наименования таблицы всецело
ложится на разработчика базы данных.

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

Для установки первичного ключа нажмем на столбец Id правой кнопкой мыши и в

появившемся меню выберем пункт Set Primary Key.

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

И после сохранения в базе данных university появится таблица Students:

Мы можем заметить, что название таблицы на самом деле начинается с

префикса dbo. Этот префикс представляет схему. Схема определяет контейнер,
который хранит объекты. То есть схема логически разграничивает базы данных. Если
схема явным образом не указывается при создании объекта, то объект принадлежит
схеме по умолчанию - схеме dbo.

Нажмем правой кнопкой мыши на название таблицы, и нам отобразится контекстное

меню с опциями:

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

Для добавления начальных данных можно выбрать опцию Edit Top 200 Rows. Она
открывает в виде таблицы 200 первых строк и позволяет их изменить. Но так как у
нас таблица только создана, то естественно в ней будет никаких данных. Введем
пару строк - пару студентов, указав необходимые данные для столбцов:

В данном случае я добавил две строки.

Затем опять же по клику на таблицу правой кнопкой мыши мы можем выбрать в

контекстном меню пункт Select To 1000 Rows, и будет запущен скрипт, который
отобразит первые 1000 строк из таблицы:
 16

Первый запрос на T-SQL

Последнее обновление: 05.07.2017


В прошлой теме в SQL Management Studio была создана простенькая база данных с
одной таблицей. Теперь определим и выполним первый SQL-запрос. Для этого
откроем SQL Management Studio, нажмем правой кнопкой мыши на элемент самого
верхнего уровня в Object Explorer (название сервера) и в появившемся контекстном
меню выберем пункт New Query:

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

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

получим все данные из нее. База данных у нас называется university, а таблица
- [Link], поэтому для получения данных из таблицы введем следующий
запрос:

1 SELECT * FROM [Link]

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

брать данные. Фактически этим запросом мы говорим "ВЫБРАТЬ все ИЗ таблицы
[Link]". Стоит отметить, что для названия таблицы используется
полный ее путь с указанием базы данных и схемы.

После ввода запроса нажмем на панели инструментов на кнопку Execute, либо

можно нажать на клавишу F5.

В результате выполнения запроса в нижней части программы появится небольшая

таблица, которая отобразит результаты запроса - то есть все данные из таблицы
Students.
 17

Если необходимо совершить несколько запросов к одной и той же базе данных, то мы

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

1 USE university
2 SELECT * FROM Students

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

любой базе данных на сервере. Но также мы можем выполнять запросы только в
рамках конкретной базы данных. Для этого необходимо нажать правой кнопкой мыши
на нужную бд и в контекстном меню выбрать пункт New Query:

Если в этом случае мы захотим выполнить запрос к выше использованной таблице

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

1 SELECT * FROM Students

SQL Server\[Link]\MSSQL\DATA. Например, пусть в моем случае файл

с данными называется [Link]. И я хочу этот файл добавить на сервер как
базу данных. Вначале его надо скопировать в выше указанный каталог. Затем для
прикрепления базы к серверу надо использовать следующую команду:

CREATE DATABASE contactsdb

1
ON PRIMARY(FILENAME='C:\Program Files\Microsoft SQL Server\[Link]\MSS
2 [Link]')
3 FOR ATTACH;

После выполнения команды на сервере появится база данных contactsdb.

Удаление базы данных

Для удаления базы данных применяется команда DROP DATABASE, которая имеет
следующий синтаксис:

1 DROP DATABASE database_name1 [, database_name2]...

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

1 DROP DATABASE contactsdb

 18

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

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

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

Для создания таблиц применяется команда CREATE TABLE. С этой командой можно
использовать ряд операторов, которые определяют столбцы таблицы и их атрибуты.
И кроме того, можно использовать ряд операторов, которые определяют свойства
таблицы в целом. Одна база данных может содержать до 2 миллиардов таблиц.

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

1 CREATE TABLE название_таблицы

2 (название_столбца1 тип_данных атрибуты_столбца1,

3 название_столбца2 тип_данных атрибуты_столбца2,

4 ................................................

5 название_столбцаN тип_данных атрибуты_столбцаN,

6 атрибуты_таблицы

)
7

После команды CREATE TABLE идет название создаваемой таблицы. Имя таблицы
выполняет роль ее идентификатора в базе данных, поэтому оно должно быть
уникальным. Имя должно иметь длину не больше 128 символов. Имя может состоять
из алфавитно-цифровых символов, а также символов $ и знака подчеркивания.
Причем первым символом должна быть буква или знак подчеркивания.

Имя объекта не может включать пробелы и не может представлять одно из ключевых

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

Примеры корректных идентификаторов:

 19

1 Users

2 tags$345

3 users_accounts

4 "users accounts"

5 [Table]

После имени таблицы в скобках указываются параметры всех столбцов и в самом

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

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

Таблица может содержать от 1 до 1024 столбцов. Каждый столбец должен иметь

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

Например, определение простейшей таблицы Customers:

1 CREATE TABLE Customers

2 (

3 Id INT,

4 Age INT,

5 FirstName NVARCHAR(20),

6 LastName NVARCHAR(20),

7 Email VARCHAR(30),

Phone VARCHAR(20)
8
)
9

В данном случае в таблице Customers определяются шесть столбцов: Id, FirstName,

LastName, Age, Email, Phone. Первые два столбца представляют идентификатор
клиента и его возраст и имеют тип INT, то есть будут хранить числовые значения.
Следующие два столбца представляют имя и фамилию клиента и имеют
тип NVARCHAR(20), то есть представляют строку UNICODE длиной не более 20
символов. Последние два столбца Email и Phone представляют адрес электронной
почты и телефон клиента и имеют тип VARCHAR(30/20) - они также хранят строку,
но не в кодировке UNICODE.
 20

Создание таблицы в SQL Management Studio

Создадим простую таблицу на сервере. Для этого откроем SQL Server Management
Studio и нажмем правой кнопкой мыши на название сервера. В появившемся
контекстном меню выберем пункт New Query.

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

1 USE usersdb;
2
3 CREATE TABLE Customers
4 (

5 Id INT,

6 Age INT,

7 FirstName NVARCHAR(20),

8 LastName NVARCHAR(20),

9 Email VARCHAR(30),

Phone VARCHAR(20)
10
);
11

То есть в базу данных добавляется таблица Customers, которая была рассмотрена

ранее.

Также можно открыть редактор из под базы данных, также нажав на нее правой
кнопкой мыши и выбрав New Query:

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

которой был открыт редактор, и дополнительно ее устанавливать с помощью
команды USE не потребуется.
 21

Удаление таблиц

Для удаления таблиц используется команда DROP TABLE, которая имеет следующий
синтаксис:

1 DROP TABLE table1 [, table2, ...]

Например, удаление таблицы Customers:

1 DROP TABLE Customers

Переименование таблицы

Для переименования таблиц применяется системная хранимая процедура

"sp_rename". Например, переименование таблицы Users в UserAccounts в базе данных
usersdb:

1 USE usersdb;

2 EXEC sp_rename 'Users', 'UserAccounts';

Типы данных T-SQL

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

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

Язык T-SQL предоставляет множество различных типов. В зависимости от характера

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

Числовые типы данных

 BIT: хранит значение 0 или 1. Фактически является аналогом булевого типа в

языках программирования. Занимает 1 байт.

 TINYINT: хранит числа от 0 до 255. Занимает 1 байт. Хорошо подходит для

хранения небольших чисел.
 22

 SMALLINT: хранит числа от –32 768 до 32 767. Занимает 2 байта

 INT: хранит числа от –2 147 483 648 до 2 147 483 647. Занимает 4 байта.
Наиболее используемый тип для хранения чисел.

 BIGINT: хранит очень большие числа от -9 223 372 036 854 775 808 до 9 223 372
036 854 775 807, которые занимают в памяти 8 байт.

 DECIMAL: хранит числа c фиксированной точностью. Занимает от 5 до 17 байт

в зависимости от количества чисел после запятой.

Данный тип может принимать два параметра precision и

scale: DECIMAL(precision, scale).

Параметр precision представляет максимальное количество цифр, которые

может хранить число. Это значение должно находиться в диапазоне от 1 до 38.
По умолчанию оно равно 18.

Параметр scale представляет максимальное количество цифр, которые может

содержать число после запятой. Это значение должно находиться в диапазоне
от 0 до значения параметра precision. По умолчанию оно равно 0.

 NUMERIC: данный тип аналогичен типу DECIMAL.

 SMALLMONEY: хранит дробные значения от -214 748.3648 до 214 748.3647.

Предназначено для хранения денежных величин. Занимает 4 байта.
Эквивалентен типу DECIMAL(10,4).

 MONEY: хранит дробные значения от -922 337 203 685 477.5808 до 922 337 203
685 477.5807. Представляет денежные величины и занимает 8 байт.
Эквивалентен типу DECIMAL(19,4).

 FLOAT: хранит числа от –1.79E+308 до 1.79E+308. Занимает от 4 до 8 байт в

зависимости от дробной части.

Может иметь форму опредеения в виде FLOAT(n), где n представляет число

бит, которые используются для хранения десятичной части числа (мантиссы).
По умолчанию n = 53.

 REAL: хранит числа от –340E+38 to 3.40E+38. Занимает 4 байта. Эквивалентен

типу FLOAT(24).

Примеры числовых столбцов:

1 Salary MONEY,

2 TotalWeight DECIMAL(9,2),
 23

3 Age INT,

4 Surplus FLOAT

Типы данных, представляющие дату и время

 DATE: хранит даты от 0001-01-01 (1 января 0001 года) до 9999-12-31 (31

декабря 9999 года). Занимает 3 байта.

 TIME: хранит время в диапазоне от 00:00:00.0000000 до 23:59:59.9999999.

Занимает от 3 до 5 байт.

Может иметь форму TIME(n), где n представляет количество цифр от 0 до 7 в

дробной части секунд.

 DATETIME: хранит даты и время от 01/01/1753 до 31/12/9999. Занимает 8 байт.

 DATETIME2: хранит даты и время в диапазоне от 01/01/0001 00:00:00.0000000

до 31/12/9999 23:59:59.9999999. Занимает от 6 до 8 байт в зависимости от
точности времени.

Может иметь форму DATETIME2(n), где n представляет количество цифр от 0 до

7 в дробной части секунд.

 SMALLDATETIME: хранит даты и время в диапазоне от 01/01/1900 до

06/06/2079, то есть ближайшие даты. Занимает от 4 байта.

 DATETIMEOFFSET: хранит даты и время в диапазоне от 0001-01-01 до 9999-12-

31. Сохраняет детальную информацию о времени с точностью до 100
наносекунд. Занимает 10 байт.

Распространенные форматы дат:

 yyyy-mm-dd - 2017-07-12

 dd/mm/yyyy - 12/07/2017

 mm-dd-yy - 07-12-17

В таком формате двузначные числа от 00 до 49 воспринимаются как даты в

диапазоне 2000-2049. А числа от 50 до 99 как диапазон чисел 1950 - 1999.

 Month dd, yyyy - July 12, 2017

Распространенные форматы времени:

 hh:mi - 13:21
 24

 hh:mi am/pm - 1:21 pm

 hh:mi:ss - 1:21:34

 hh:mi:ss:mmm - 1:21:34:12

 hh:mi:ss:nnnnnnn - 1:21:34:1234567

Строковые типы данных

 CHAR: хранит строку длиной от 1 до 8 000 символов. На каждый символ

выделяет по 1 байту. Не подходит для многих языков, так как хранит символы
не в кодировке Unicode.

Количество символов, которое может хранить столбец, передается в скобках.

Например, для столбца с типом CHAR(10) будет выделено 10 байт. И если мы
сохраним в столбце строку менее 10 символов, то она будет дополнена
пробелами.

 VARCHAR: хранит строку. На каждый символ выделяется 1 байт. Можно

указать конкретную длину для столбца - от 1 до 8 000 символов,
например, VARCHAR(10). Если строка должна иметь больше 8000 символов, то
задается размер MAX, а на хранение строки может выделяться до 2
Гб: VARCHAR(MAX).

Не подходит для многих языков, так как хранит символы не в кодировке

Unicode.

В отличие от типа CHAR если в столбец с типом VARCHAR(10) будет сохранена

строка в 5 символов, то в столце будет сохранено именно пять символов.

 NCHAR: хранит строку в кодировке Unicode длиной от 1 до 4 000 символов. На

каждый символ выделяется 2 байта. Например, NCHAR(15)

 NVARCHAR: хранит строку в кодировке Unicode. На каждый символ выделяется

2 байта.Можно задать конкретный размер от 1 до 4 000 символов: . Если строка
должна иметь больше 4000 символов, то задается размер MAX, а на хранение
строки может выделяться до 2 Гб.

Еще два типа TEXT и NTEXT являются устаревшими и поэтому их не рекомендуется

использовать. Вместо них применяются VARCHAR и NVARCHAR соответственно.

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

1 Email VARCHAR(30),

2 Comment NVARCHAR(MAX)
 25

Бинарные типы данных

 BINARY: хранит бинарные данные в виде последовательности от 1 до 8 000

байт.

 VARBINARY: хранит бинарные данные в виде последовательности от 1 до 8

000 байт, либо до 2^31–1 байт при использовании значения MAX
(VARBINARY(MAX)).

Еще один бинарный тип - тип IMAGE является устаревшим, и вместо него
рекомендуется применять тип VARBINARY.

Остальные типы данных

 UNIQUEIDENTIFIER: уникальный идентификатор GUID (по сути строка с

уникальным значением), который занимает 16 байт.

 TIMESTAMP: некоторое число, которое хранит номер версии строки в таблице.

Занимает 8 байт.

 CURSOR: представляет набор строк.

 HIERARCHYID: представляет позицию в иерархии.

 SQL_VARIANT: может хранить данные любого другого типа данных T-SQL.

 XML: хранит документы XML или фрагменты документов XML. Занимает в

памяти до 2 Гб.

 TABLE: представляет определение таблицы.

 GEOGRAPHY: хранит географические данные, такие как широта и долгота.

 GEOMETRY: хранит координаты местонахождения на плоскости.

Атрибуты и ограничения столбцов и таблиц

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


 26

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

PRIMARY KEY

С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY,

4 Age INT,

5 FirstName NVARCHAR(20),

6 LastName NVARCHAR(20),

7 Email VARCHAR(30),

Phone VARCHAR(20)
8
)
9

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

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

Установка первичного ключа на уровне таблицы:

1 CREATE TABLE Customers

2 (
3 Id INT,

4 Age INT,

5 FirstName NVARCHAR(20),

6 LastName NVARCHAR(20),

7 Email VARCHAR(30),

8 Phone VARCHAR(20),

PRIMARY KEY(Id)
9
)
10
 27

Первичный ключ может быть составным (compound key). Такой ключ может
потребоваться, если у нас сразу два столбца должны уникально идентифицировать
строку в таблице. Например:

1 CREATE TABLE OrderLines

2 (

3 OrderId INT,

4 ProductId INT,

5 Quantity INT,

6 Price MONEY,

7 PRIMARY KEY(OrderId, ProductId)

)
8

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

IDENTITY

Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может

назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и
NUMERIC. При добавлении новых данных в таблицу SQL Server будет
инкрементировать на единицу значение этого столбца у последней записи. Как
правило, в роли идентификатора выступает тот же столбец, который является
первичным ключом, хотя в принципе это необязательно.

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 Age INT,

5 FirstName NVARCHAR(20),

6 LastName NVARCHAR(20),

7 Email VARCHAR(30),

Phone VARCHAR(20)
8
)
9

Также можно использовать полную форму атрибута:

 28

1 IDENTITY(seed, increment)

Здесь параметр seed указывает на начальное значение, с которого будет начинаться

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

1 IDENTITY(1, 1)

То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу.

Но мы можем это поведение переопределить. Например:

1 Id INT IDENTITY (2, 3)

В данном случае отсчет начнется с 2, а значение каждой последующей записи будет

увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая - 5, третья -
8 и т.д.

Также следует учитывать, что в таблице только один столбец должен иметь такой
атрибут.

UNIQUE

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

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 Age INT,

5 FirstName NVARCHAR(20),

6 LastName NVARCHAR(20),

7 Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE

8
)
9

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

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

Также мы можем определить этот атрибут на уровне таблицы:

 29

1 CREATE TABLE Customers

2 (
3 Id INT PRIMARY KEY IDENTITY,

4 Age INT,

5 FirstName NVARCHAR(20),

6 LastName NVARCHAR(20),

7 Email VARCHAR(30),

8 Phone VARCHAR(20),

UNIQUE(Email, Phone)
9
)
10
NULL и NOT NULL

Чтобы указать, может ли столбец принимать значение NULL, при определении

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

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 Age INT,

5 FirstName NVARCHAR(20) NOT NULL,

6 LastName NVARCHAR(20) NOT NULL,

7 Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE

8
)
9
DEFAULT

Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при

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

1 CREATE TABLE Customers

 30

2 (
3 Id INT PRIMARY KEY IDENTITY,

4 Age INT DEFAULT 18,

5 FirstName NVARCHAR(20) NOT NULL,

6 LastName NVARCHAR(20) NOT NULL,

7 Email VARCHAR(30) UNIQUE,

8 Phone VARCHAR(20) UNIQUE

);
9

Здесь для столбца Age предусмотрено значение по умолчанию 18.

CHECK

Ключевое слово CHECK задает ограничение для диапазона значений, которые могут
храниться в столбце. Для этого после слова CHECK указывается в скобках условие,
которому должен соответствовать столбец или несколько столбцов. Например,
возраст клиентов не может быть меньше 0 или больше 100:

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100),

5 FirstName NVARCHAR(20) NOT NULL,

6 LastName NVARCHAR(20) NOT NULL,

7 Email VARCHAR(30) UNIQUE CHECK(Email !=''),

Phone VARCHAR(20) UNIQUE CHECK(Phone !='')

8
);
9

Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в
качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND. Условия можно задать в
виде операций сравнения больше (>), меньше (<), не равно (!=).

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

1 CREATE TABLE Customers

 31

2 (
3 Id INT PRIMARY KEY IDENTITY,

4 Age INT DEFAULT 18,

5 FirstName NVARCHAR(20) NOT NULL,

6 LastName NVARCHAR(20) NOT NULL,

7 Email VARCHAR(30) UNIQUE,

8 Phone VARCHAR(20) UNIQUE,

CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !=''))
9
)
10
Оператор CONSTRAINT. Установка имени ограничений.

С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В

качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, DEFAULT, CHECK.

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

CONSTRAINT перед атрибутами:

1 CREATE TABLE Customers

2 (
3 Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY,

4 Age INT

5 CONSTRAINT DF_Customer_Age DEFAULT 18

6 CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100),

7 FirstName NVARCHAR(20) NOT NULL,

8 LastName NVARCHAR(20) NOT NULL,

Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE,

9
Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE
10
)
11

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

 "PK_" - для PRIMARY KEY

 "FK_" - для FOREIGN KEY

 32

 "CK_" - для CHECK

 "UQ_" - для UNIQUE

 "DF_" - для DEFAULT

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

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

И также можно задать все имена ограничений через атрибуты таблицы:

1
CREATE TABLE Customers
2 (
3 Id INT IDENTITY,
4 Age INT CONSTRAINT DF_Customer_Age DEFAULT 18,

5 FirstName NVARCHAR(20) NOT NULL,

6 LastName NVARCHAR(20) NOT NULL,

7 Email VARCHAR(30),

8 Phone VARCHAR(20),

9 CONSTRAINT PK_Customer_Id PRIMARY KEY (Id),

CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100),

10
CONSTRAINT UQ_Customer_Email UNIQUE (Email),
11
CONSTRAINT UQ_Customer_Phone UNIQUE (Phone)
12
)
13

Внешние ключи
Последнее обновление: 09.07.2017

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

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

Общий синтаксис установки внешнего ключа на уровне столбца:

1 [FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы)

2 [ON DELETE {CASCADE|NO ACTION}]

3 [ON UPDATE {CASCADE|NO ACTION}]

Для создания ограничения внешнего ключа на уровне столбца после ключевого

слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя
связанного столбца, на который будет указывать внешний ключ. Также обычно
добавляются ключевые слова FOREIGN KEY, но в принципе их необязательно
указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE.

Общий синтаксис установки внешнего ключа на уровне таблицы:

FOREIGN KEY (стобец1, столбец2, ... столбецN)

1
REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2
2
столбец_главной_таблицыN)
3 [ON DELETE {CASCADE|NO ACTION}]
4 [ON UPDATE {CASCADE|NO ACTION}]

Например, определим две таблицы и свяжем их посредством внешнего ключа:

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY IDENTITY,

Age INT DEFAULT 18,

4
FirstName NVARCHAR(20) NOT NULL,
5
LastName NVARCHAR(20) NOT NULL,
6
Email VARCHAR(30) UNIQUE,
7
Phone VARCHAR(20) UNIQUE
8
);
9
10
CREATE TABLE Orders
 34

11
(
12
Id INT PRIMARY KEY IDENTITY,
13
CustomerId INT REFERENCES Customers (Id),
14
CreatedAt Date
15
);
16

Здесь определены таблицы Customers и Orders. Customers является главной и

представляет клиента. Orders является зависимой и представляет заказ, сделанный
клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее
столбцом Id. То есть столбец CustomerId является внешним ключом, который
указывает на столбец Id из таблицы Customers.

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

1 CREATE TABLE Orders

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 CustomerId INT,

5 CreatedAt Date,

6 FOREIGN KEY (CustomerId) REFERENCES Customers (Id)

);
7

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего

ключа. Обычно это имя начинается с префикса "FK_":

1 CREATE TABLE Orders

(
2
Id INT PRIMARY KEY IDENTITY,
3
CustomerId INT,
4
CreatedAt Date,
5
CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES
6 Customers (Id)

7 );
 35

В данном случае ограничение внешнего ключа CustomerId называется

"FK_Orders_To_Customers".

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия,

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

 CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы

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

 NO ACTION: предотвращает какие-либо действия в зависимой таблице при

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

 SET NULL: при удалении связанной строки из главной таблицы устанавливает

для столбца внешнего ключа значение NULL.

 SET DEFAULT: при удалении связанной строки из главной таблицы

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

Каскадное удаление

По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается

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

1 CREATE TABLE Orders

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 CustomerId INT,

5 CreatedAt Date,

6 FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE

)
7
 36

Аналогично работает выражение ON UPDATE CASCADE. При изменении значения

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

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец
внешнего ключа допускал значение NULL:

1 CREATE TABLE Orders

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 CustomerId INT,

5 CreatedAt Date,

6 FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL

);
7
Установка значения по умолчанию
1 CREATE TABLE Orders

2 (

3 Id INT PRIMARY KEY IDENTITY,

4 CustomerId INT,

5 CreatedAt Date,

6 FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT

)
7

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


 37

Возможно, в какой-то момент мы захотим изменить уже имеющуюся таблицу.

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

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

1 ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]

2 { ADD название_столбца тип_данных_столбца [атрибуты_столбца] |

3 DROP COLUMN название_столбца |

4 ALTER COLUMN название_столбца тип_данных_столбца [NULL|NOT NULL] |

5 ADD [CONSTRAINT] определение_ограничения |

6 DROP [CONSTRAINT] имя_ограничения}

Таким образом, с помощью ALTER TABLE мы можем провернуть самые различные

сценарии изменения таблицы. Рассмотрим некоторые из них.

Добавление нового столбца

Добавим в таблицу Customers новый столбец Address:

1 ALTER TABLE Customers

2 ADD Address NVARCHAR(50) NULL;

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

1 ALTER TABLE Customers

2 ADD Address NVARCHAR(50) NOT NULL;

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

атрибут DEFAULT:

1 ALTER TABLE Customers

2 ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';

В этом случае, если в таблице уже есть данные, то для них для столбца Address будет
добавлено значение "Неизвестно".
 38

Удаление столбца

Удалим столбец Address из таблицы Customers:

1 ALTER TABLE Customers

2 DROP COLUMN Address;

Изменение типа столбца

Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200):

1 ALTER TABLE Customers

2 ALTER COLUMN FirstName NVARCHAR(200);

Добавление ограничения CHECK

При добавлении ограничений SQL Server автоматически проверяет имеющиеся

данные на соответствие добавляемым ограничениям. Если данные не соответствуют
ограничениям, то такие ограничения не будут добавлены. Например, установим для
столбца Age в таблице Customers ограничение Age > 21.

1 ALTER TABLE Customers

2 ADD CHECK (Age > 21);

Если в таблице есть строки, в которых в столбце Age есть значения,

несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы
избежать подобной проверки на соответствие и все таки добавить ограничение,
несмотря на наличие несоответствующих ему данных, используется выражение WITH
NOCHECK:

1 ALTER TABLE Customers WITH NOCHECK

2 ADD CHECK (Age > 21);

По умолчанию используется значение WITH CHECK, которое проверяет на

соответствие ограничениям.

Добавление внешнего ключа

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

1 CREATE TABLE Customers

2 (

3 Id INT PRIMARY KEY IDENTITY,

 39

4
Age INT DEFAULT 18,
5 FirstName NVARCHAR(20) NOT NULL,
6 LastName NVARCHAR(20) NOT NULL,
7 Email VARCHAR(30) UNIQUE,

8 Phone VARCHAR(20) UNIQUE

9 );

10 CREATE TABLE Orders

11 (

12 Id INT IDENTITY,

CustomerId INT,
13
CreatedAt Date
14
);
15

Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:

1 ALTER TABLE Orders

2 ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

Добавление первичного ключа

Используя выше определенную таблицу Orders, добавим к ней первичный ключ для
столбца Id:

1 ALTER TABLE Orders

2 ADD PRIMARY KEY (Id);

Добавление ограничений с именами

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

оператор CONSTRAINT, после которого указывается имя ограничения:

1 ALTER TABLE Orders

2 ADD CONSTRAINT PK_Orders_Id PRIMARY KEY (Id),

3 CONSTRAINT FK_Orders_To_Customers FOREIGN KEY(CustomerId) REFERENCES

Customers(Id);
4
5
ALTER TABLE Customers
 40

6 ADD CONSTRAINT CK_Age_Greater_Than_Zero CHECK (Age > 0);

Удаление ограничений

Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя
ограничения, то его можно узнать через SQL Server Management Studio:

Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного
и внешних ключей. Названия ограничений внешних ключей начинаются с "FK". А в
подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия
ограничений CHECK начинаются с "CK", а ограничений DEFAULT - с "DF".

Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в
таблице Orders называется "FK_Orders_To_Customers". Поэтому для удаления внешнего
ключа я могу использовать следующее выражение:

1 ALTER TABLE Orders

2 DROP FK_Orders_To_Customers;

Пакеты. Команда GO
Последнее обновление: 09.07.2017


В предыдущих случаях сначала создавалась база данных, а затем в эту БД

добавлялась таблица с помощью отдельных команд SQL. Но можно сразу совместить в
одном скрипте несколько команд. В этом случае отдельные наборы команд
называются пакетами (batch).

Каждый пакет состоит из одного или нескольких SQL-выражений, которые

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

Смысл разделения SQL-выражений на пакеты состоит в том, что одни выражения

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

Например, определим следующий скрипт:

 41

1 CREATE DATABASE internetstore;

2 GO
3
4 USE internetstore;
5
6 CREATE TABLE Customers
7 (
8 Id INT PRIMARY KEY IDENTITY,
9 Age INT DEFAULT 18,
10 FirstName NVARCHAR(20) NOT NULL,
11 LastName NVARCHAR(20) NOT NULL,
12 Email VARCHAR(30) UNIQUE,
13 Phone VARCHAR(20) UNIQUE
14 );
15
16 CREATE TABLE Orders
17 (
18 Id INT PRIMARY KEY IDENTITY,
19 CustomerId INT,
20 CreatedAt DATE,
21 FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
22 );

Вначале создается бд internetstore. Затем идет команда GO, которая сигнализирует,

что можно выполнять следующий пакет выражений. И далее выполняется второй
пакет, который добавляет в нее две таблицы - Customers и Orders.

Основы T-SQL. DML

Добавление данных. Команда Insert
Последнее обновление: 13.07.2017

Для добавления данных применяется команда INSERT, которая имеет следующий

формальный синтаксис:

INSERT [INTO] имя_таблицы [(список_столбцов)] VALUES (значение1, значение2, ...

1 значениеN)
 42

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

Например, пусть ранее была создана следующая база данных:

1 CREATE DATABASE productsdb;

2 GO
3 USE productsdb;

4 CREATE TABLE Products

5 (

6 Id INT IDENTITY PRIMARY KEY,

7 ProductName NVARCHAR(30) NOT NULL,

8 Manufacturer NVARCHAR(20) NOT NULL,

ProductCount INT DEFAULT 0,

9
Price MONEY NOT NULL
10
)
11

Добавим в нее одну строку с помощью команды INSERT:

1 INSERT Products VALUES ('iPhone 7', 'Apple', 5, 52000)

После удачного выполнения в SQL Server Management Studio в поле сообщений должно
появиться сообщение "1 row(s) affected":

Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES
передаются по порядку их объявления. Например, в выражении CREATE TABLE выше
можно увидеть, что первым столбцом идет Id. Но так как для него задан атрибут
IDENTITY, то значение этого столбца автоматически генерируется, и его можно не
указывать. Второй столбец представляет ProductName, поэтому первое значение -
строка "iPhone 7" будет передано именно этому столбцу. Второе значение - строка
"Apple" будет передана третьему столбцу Manufacturer и так далее. То есть значения
передаются столбцам следующим образом:

 ProductName: 'iPhone 7'

 Manufacturer: 'Apple'
 43

 ProductCount: 5

 Price: 52000

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

будут добавляться значения:

1 INSERT INTO Products (ProductName, Price, Manufacturer)

2 VALUES ('iPhone 6S', 41000, 'Apple')

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

 ProductName: 'iPhone 6S'

 Manufacturer: 'Apple'

 Price: 41000

Для неуказанных столбцов (в данном случае ProductCount) будет добавляться

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

Также мы можем добавить сразу несколько строк:

1 INSERT INTO Products

2 VALUES

3 ('iPhone 6', 'Apple', 3, 36000),

4 ('Galaxy S8', 'Samsung', 2, 46000),

5 ('Galaxy S8 Plus', 'Samsung', 1, 56000)

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

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

значение по умолчанию с помощью ключевого слова DEFAULT или значение NULL:

1 INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)

2 VALUES ('Mi6', 'Xiaomi', DEFAULT, 28000)

В данном случае для столбца ProductCount будет использовано значение по

умолчанию (если оно установлено, если его нет - то NULL).
 44

Если все столбцы имеют атрибут DEFAULT, определяющий значение по умолчанию,

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

1 INSERT INTO Products

2 DEFAULT VALUES

Но если брать таблицу Products, то подобная команда завершится с ошибкой, так как
несколько полей не имеют атрибута DEFAULT и при этом не допускают значение NULL.

Выборка данных. Команда SELECT

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

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

1 SELECT список_столбцов FROM имя_таблицы

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

1 CREATE TABLE Products

2 (

3 Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

4
Manufacturer NVARCHAR(20) NOT NULL,
5
ProductCount INT DEFAULT 0,
6
Price MONEY NOT NULL
7
);
8
9
INSERT INTO Products
10
 45

11 VALUES
12 ('iPhone 6', 'Apple', 3, 36000),

13 ('iPhone 6S', 'Apple', 2, 41000),

14 ('iPhone 7', 'Apple', 5, 52000),

15 ('Galaxy S8', 'Samsung', 2, 46000),

16 ('Galaxy S8 Plus', 'Samsung', 1, 56000),

17 ('Mi6', 'Xiaomi', 5, 28000),

('OnePlus 5', 'OnePlus', 6, 38000)

18

Получим все объекты из этой таблицы:

1 SELECT * FROM Products

Символ звездочка * указывает, что нам надо получить все столбцы.

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

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

Если нам надо получить данные не по всем, а по каким-то конкретным столбцам, то

тогда все эти спецификации столбцов перечисляются через запятую после SELECT:

1 SELECT ProductName, Price FROM Products

Спецификация столбца необязательно должна представлять его название. Это может

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

1 SELECT ProductName + ' (' + Manufacturer + ')', Price, Price * ProductCount

2 FROM Products

Здесь при выборке будут создаваться три столбца. Первый столбец представляет
результат объединения двух столбцов ProductName и Manufacturer. Второй столбец -
 46

стандартный столбец Price. А третий столбец представляет значение столбца Price,

умноженное на значение столбца ProductCount.

С помощью оператора AS можно изменить название выходного столбца или

определить его псевдоним:

1 SELECT

2 ProductName + ' (' + Manufacturer + ')' AS ModelName,

3 Price,

4 Price * ProductCount AS TotalSum

5 FROM Products

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

столбец ModelName объединяет столбцы ProductName и Manufacturere, второй
представляет стандартный столбец Price. Третий столбец TotalSum хранит
произведение столбцов ProductCount и Price. При этом, как в случае со столбцом Price,
необязательно определять название результирующего столбца с помощью AS.

DISTINCT

Оператор DISTINCT позволяет выбрать уникальные строки. Например, в нашем

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

1 SELECT DISTINCT Manufacturer

2 FROM Products

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

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

Выборка с добавлением
SELECT INTO

Выражение SELECT INTO позволяет выбрать из одной таблицы некоторые данные в

другую таблицу, при этом вторая таблица создается автоматически. Например:
 47

1 SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price

2 INTO ProductSummary

3 FROM Products

4
5 SELECT * FROM ProductSummary

После выполнения этой команды в базе данных будет создана еще одна таблица
ProductSummary, которая будет иметь два столбца ModelName и Price, а данные для
этих столбцов будут взяты из таблицы Products:

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

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

1 INSERT INTO ProductSummary

2 SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price

3 FROM Products

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

таблицы Products.

Сортировка. ORDER BY
Последнее обновление: 13.07.2017


Оператор ORDER BY позволяет отсортировать извлекаемые значения по

определенному столбцу:

1 SELECT *
2 FROM Products
3 ORDER BY ProductName
 48

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

Сортировку также можно проводить по псевдониму столбца, который определяется с

помощью оператора AS:

1 SELECT ProductName, ProductCount * Price AS TotalSum

2 FROM Products
3 ORDER BY TotalSum

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

оператора DESC можно задать сортировку по убыванию.

1 SELECT ProductName
2 FROM Products
3 ORDER BY ProductName DESC

По умолчанию вместо DESC используется оператор ASC:

1 SELECT ProductName
2 FROM Products
3 ORDER BY ProductName ASC

Если необходимо отсортировать сразу по нескольким столбцам, то все они

перечисляются после оператора ORDER BY:

1 SELECT ProductName, Price, Manufacturer

2 FROM Products
3 ORDER BY Manufacturer, ProductName

В этом случае сначала строки сортируются по столбцу Manufacturer по возрастанию.

Затем если есть две строки, в которых столбец Manufacturer имеет одинаковое
значение, то они сортируются по столбцу ProductName также по возрастанию. Но
опять же с помощью ASC и DESC можно отдельно для разных столбцов определить
сортировку по возрастанию и убыванию:

1 SELECT ProductName, Price, Manufacturer

2 FROM Products
3 ORDER BY Manufacturer ASC, ProductName DESC
 49

В качестве критерия сортировки также можно использовать сложно выражение на

основе столбцов:

1 SELECT ProductName, Price, ProductCount

2 FROM Products
3 ORDER BY ProductCount * Price

Извлечение диапазона строк

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


Оператор TOP

Оператор TOP позволяет выбрать определенное количество строк из таблицы:

1 SELECT TOP 4 ProductName

2 FROM Products

Дополнительный оператор PERCENT позволяет выбрать процентное количество строк

из таблицы. Например, выберем 75% строк:

1 SELECT TOP 75 PERCENT ProductName

2 FROM Products

OFFSET и FETCH

Оператор TOP позволяет извлечь определенное количество строк, начиная с начала

таблицы. Для извлечения набора строк из любого места, применяются
операторы OFFSET и FETCH. Важно, что эти операторы применяются только в
отсортированном наборе данных после выражения ORDER BY.

1 ORDER BY выражение

2 OFFSET смещение_относительно_начала {ROW|ROWS}

3 [FETCH {FIRST|NEXT} количество_извлекаемых_строк {ROW|ROWS} ONLY]

Например, выберем все строки, начиная с третьей:

 50

1 SELECT * FROM Products

2 ORDER BY Id

3 OFFSET 2 ROWS;

Число после ключевого слова OFFSET указывает, сколько строк необходимо

пропустить.

Теперь выберем только три строки, начиная с третьей:

1 SELECT * FROM Products

2 ORDER BY Id

3 OFFSET 2 ROWS

4 FETCH NEXT 3 ROWS ONLY;

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

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

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

Фильтрация. WHERE
Последнее обновление: 13.07.2017

Для фильтрации в команде SELECT применяется оператор WHERE. После этого

оператора ставится условие, которому должна соответствовать строка:

1 WHERE условие
 51

Если условие истинно, то строка попадает в результирующую выборку. В качестве

можно использовать операции сравнения. Эти операции сравнивают два выражения.
В T-SQL можно применять следующие операции сравнения:

 =: сравнение на равенство (в отличие от си-подобных языков в T-SQL для

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

 <>: сравнение на неравенство

 <: меньше чем

 >: больше чем

 !<: не меньше чем

 !>: не больше чем

 <=: меньше чем или равно

 >=: больше чем или равно

Например, найдем всех товары, производителем которых является компания

Samsung:

1 SELECT * FROM Products

2 WHERE Manufacturer = 'Samsung'

Стоит отметить, что в данном случае регистр не имеет значение, и мы могли бы

использовать для поиска и строку "Samsung", и "SAMSUNG", и "samsung". Все эти
варианты давали бы эквивалентный результат выборки.

Другой пример - найдем все товары, у которых цена больше 45000:

1 SELECT * FROM Products

2 WHERE Price > 45000

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

найдем все товары, у которых совокупная стоимость больше 200 000:

1 SELECT * FROM Products

2 WHERE Price * ProductCount > 200000

 52

Логические операторы

Для объединения нескольких условий в одно могут использоваться логические

операторы. В T-SQL имеются следующие логические операторы:

 AND: операция логического И. Она объединяет два выражения:

1 выражение1 AND выражение2

 Только если оба этих выражения одновременно истинны, то и общее условие

оператора AND также будет истинно. То есть если и первое условие истинно, и
второе.

 OR: операция логического ИЛИ. Она также объединяет два выражения:

1 выражение1 OR выражение2

 Если хотя бы одно из этих выражений истинно, то общее условие оператора OR

также будет истинно. То есть если или первое условие истинно, или второе.

 NOT: операция логического отрицания. Если выражение в этой операции

ложно, то общее условие истинно.

1 NOT выражение

Если эти операторы встречаются в одном выражении, то сначала выполняется NOT,

потом AND и в конце OR.

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

цена больше 50000:

1 SELECT * FROM Products

2 WHERE Manufacturer = 'Samsung' AND Price > 50000

Теперь изменим оператор на OR. То есть выберем все товары, у которых либо
производитель Samsung, либо цена больше 50000:

1 SELECT * FROM Products

2 WHERE Manufacturer = 'Samsung' OR Price > 50000

 53

Применение оператора NOT - выберем все товары, у которых производитель не

Samsung:

1 SELECT * FROM Products

2 WHERE NOT Manufacturer = 'Samsung'

Но в большинстве случае вполне можно обойтись без оператора NOT. Так, в

предыдущий пример мы можем переписать следующим образом:

1 SELECT * FROM Products

2 WHERE Manufacturer <> 'Samsung'

Также в одной команде SELECT можно использовать сразу несколько операторов:

1 SELECT * FROM Products

2 WHERE Manufacturer = 'Samsung' OR Price > 30000 AND ProductCount > 2

Так как оператор AND имеет более высокий приоритет, то сначала будет выполняться
подвыражение Price > 30000 AND ProductCount > 2, и только потом оператор OR. То
есть здесь выбираются товары, которыех на складе больше 2 и у которых
одновременно цена больше 30000, либо те товары, производителем которых является
Samsung.

С помощью скобок мы также можем переопределить порядок операций:

1 SELECT * FROM Products

2 WHERE (Manufacturer = 'Samsung' OR Price > 30000) AND ProductCount > 2

IS NULL

Ряд столбцов может допускать значение NULL. Это значение не эквивалентно пустой
строке ''. NULL представляет полное отсутствие какого-либо значения. И для проверки
на наличие подобного значения применяется оператор IS NULL.

Например, выберем все товары, у которых не установлено поле ProductCount:

1 SELECT * FROM Products

2 WHERE ProductCount IS NULL

 54

Если, наоборот, необходимо получить строки, у которых поле ProductCount не равно

NULL, то можно использовать оператор NOT:

1 SELECT * FROM Products

2 WHERE ProductCount IS NOT NULL

Операторы фильтрации
Последнее обновление: 13.07.2017


Оператор IN

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

1 WHERE выражение [NOT] IN (выражение)

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

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

Например, выберем товары, у которых производитель либо Samsung, либо Xiaomi,

либо Huawei:

1 SELECT * FROM Products

2 WHERE Manufacturer IN ('Samsung', 'Xiaomi', 'Huawei')

Мы могли бы все эти значения проверить и через оператор OR:

SELECT * FROM Products

1
WHERE Manufacturer = 'Samsung' OR Manufacturer = 'Xiaomi' OR Manufacturer =
2 'Huawei'

Но использование оператора IN гораздо удобнее, особенно если подобных значений

очень много.
 55

С помощью оператора NOT можно найти все строки, которые, наоборот, не

соответствуют набору значений:

1 SELECT * FROM Products

2 WHERE Manufacturer NOT IN ('Samsung', 'Xiaomi', 'Huawei')

Оператор BETWEEN

Оператор BETWEEN определяет диапазон значений с помощью начального и

конечного значения, которому должно соответствовать выражение:

1 WHERE выражение [NOT] BETWEEN начальное_значение AND конечное_значение

Например, получим все товары, у которых цена от 20 000 до 40 000 (начальное и

конечное значения также включаются в диапазон):

1 SELECT * FROM Products

2 WHERE Price BETWEEN 20000 AND 40000

Если надо, наоборот, выбрать те строки, которые не попадают в данный диапазон, то

применяется оператор NOT:

1 SELECT * FROM Products

2 WHERE Price NOT BETWEEN 20000 AND 40000

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

запасы которых на определенную сумму (цена * количество):

1 SELECT * FROM Products

2 WHERE Price * ProductCount BETWEEN 100000 AND 200000

Оператор LIKE

Оператор LIKE принимает шаблон строки, которому должно соответствовать

выражение.

1 WHERE выражение [NOT] LIKE шаблон_строки

Для определения шаблона могут применяться ряд специальных символов

подстановки:
 56

 %: соответствует любой подстроке, которая может иметь любое количество

символов, при этом подстрока может и не содержать ни одного символа

 _: соответствует любому одиночному символу

 [ ]: соответствует одному символу, который указан в квадратных скобках

 [ - ]: соответствует одному символу из определенного диапазона

 [ ^ ]: соответствует одному символу, который не указан после символа ^

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

 WHERE ProductName LIKE 'Galaxy%'

Соответствует таким значениям как "Galaxy Ace 2" или "Galaxy S7"

 WHERE ProductName LIKE 'Galaxy S_'

Соответствует таким значениям как "Galaxy S7" или "Galaxy S8"

 WHERE ProductName LIKE 'iPhone [78]'

Соответствует таким значениям как "iPhone 7" или "iPhone8"

 WHERE ProductName LIKE 'iPhone [6-8]'

Соответствует таким значениям как "iPhone 6", "iPhone 7" или "iPhone8"

 WHERE ProductName LIKE 'iPhone [^7]%'

Соответствует таким значениям как "iPhone 6", "iPhone 6S" или "iPhone8". Но не
соответствует значениям "iPhone 7" и "iPhone 7S"

 WHERE ProductName LIKE 'iPhone [^1-6]%'

Соответствует таким значениям как "iPhone 7", "iPhone 7S" и "iPhone 8". Но не
соответствует значениям "iPhone 5", "iPhone 6" и "iPhone 6S"

Применим оператор LIKE:

1 SELECT * FROM Products

2 WHERE ProductName LIKE 'iPhone [6-8]%'

Обновление данных. Команда UPDATE

Последнее обновление: 13.07.2017
 57

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

1 UPDATE имя_таблицы
2 SET столбец1 = значение1, столбец2 = значение2, ... столбецN = значениеN
3 [FROM выборка AS псевдоним_выборки]
4 [WHERE условие_обновления]

Например, увеличим у всех товаров цену на 5000:

1 UPDATE Products
2 SET Price = Price + 5000

Используем критерий, и изменим название производителя с "Samsung" на "Samsung

Inc.":

1 UPDATE Products
2 SET Manufacturer = 'Samsung Inc.'
3 WHERE Manufacturer = 'Samsung'

Более сложный запрос - заменим у поля Manufacturer значение "Apple" на "Apple Inc." в
первых 2 строках:

1 UPDATE Products
2 SET Manufacturer = 'Apple Inc.'
3 FROM
4 (SELECT TOP 2 FROM Products WHERE Manufacturer='Apple') AS Selected
5 WHERE [Link] = [Link]

С помощью подзапроса после ключевого слова FROM производится выборка первых

двух строк, в которых Manufacturer='Apple'. Для этой выборки будет определен
псевдоним Selected. Псевдоним указывается после оператора AS.

Далее идет условие обновления [Link] = [Link]. То есть фактически мы

имеем дело с двумя таблицами - Products и Selected (которая является производной от
Products). В Selected находится две первых строки, в которых Manufacturer='Apple'. В
Products - вообще все строки. И обновление производится только для тех строк,
которые есть в выборке Selected. То есть если в таблице Products десятки товаров с
производителем Apple, то обновление коснется только двух первых из них.
 58

Удаление данных. Команда DELETE

Последнее обновление: 13.07.2017


Для удаления применяется команда DELETE:

1 DELETE [FROM] имя_таблицы

2 WHERE условие_удаления

Например, удалим строки, у которых id равен 9:

1 DELETE Products
2 WHERE Id=9

Или удалим все товары, производителем которых является Xiaomi и которые имеют
цену меньше 15000:

1 DELETE Products
2 WHERE Manufacturer='Xiaomi' AND Price < 15000

Более сложный пример - удалим первые два товара, у которых производитель - Apple:

1 DELETE Products FROM

2 (SELECT TOP 2 * FROM Products
3 WHERE Manufacturer='Apple]') AS Selected
4 WHERE [Link] = [Link]

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

Если необходимо вовсе удалить все строки вне зависимости от условия, то условие
можно не указывать:

1DELETE Products

Manufacturer NVARCHAR(20) NOT NULL,

ProductCount INT DEFAULT 0,

 59

Price MONEY NOT NULL

);

INSERT INTO Products

VALUES

('iPhone 6', 'Apple', 3, 36000),

('iPhone 6S', 'Apple', 2, 41000),

('iPhone 7', 'Apple', 5, 52000),

('Galaxy S8', 'Samsung', 2, 46000),

('Galaxy S8 Plus', 'Samsung', 1, 56000),

('Mi6', 'Xiaomi', 5, 28000),

('OnePlus 5', 'OnePlus', 6, 38000)

Найдем среднюю цену товаров из базы данных:

1 SELECT AVG(Price) AS Average_Price FROM Products

Для поиска среднего значения в качестве выражения в функцию передается столбец

Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя
можно его и не устанавливать.

Также мы можем применить фильтрацию. Например, найти среднюю цену для

товаров какого-то определенного производителя:

1 SELECT AVG(Price) FROM Products

2 WHERE Manufacturer='Apple'

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

1 SELECT AVG(Price * ProductCount) FROM Products

Count

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции.
Первая форма COUNT(*) подсчитывает число строк в выборке:
 60

1 SELECT COUNT(*) FROM Products

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

этом строки со значениями NULL игнорируются:

1 SELECT COUNT(Manufacturer) FROM Products

Min и Max

Функции Min и Max возвращают соответственно минимальное и максимальное

значение по столбцу. Например, найдем минимальную цену среди товаров:

1 SELECT MIN(Price) FROM Products

Поиск максимальной цены:

1 SELECT MAX(Price) FROM Products

Данные функции также игнорируют значения NULL и не учитывают их при подсчете.

Sum

Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее

количество товаров:

1 SELECT SUM(ProductCount) FROM Products

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

Например, найдем общую стоимость всех имеющихся товаров:

1 SELECT SUM(ProductCount * Price) FROM Products

All и Distinct

По умолчанию все вышеперечисленных пять функций учитывают все строки выборки

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

1 SELECT AVG(DISTINCT ProductCount) AS Average_Price FROM Products

 61

По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все

строки:

1 SELECT AVG(ALL ProductCount) AS Average_Price FROM Products

Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно
не указывать.

Комбинирование функций

Объединим применение нескольких функций:

1 SELECT COUNT(*) AS ProdCount,

2 SUM(ProductCount) AS TotalCount,

3 MIN(Price) AS MinPrice,

4 MAX(Price) AS MaxPrice,

5 AVG(Price) AS AvgPrice

6 FROM Products

Операторы GROUP BY и HAVING

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

Для группировки данных в T-SQL применяются операторы GROUP BY и HAVING, для

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

1 SELECT столбцы

2 FROM таблица

3 [WHERE условие_фильтрации_строк]

4 [GROUP BY столбцы_для_группировки]

5 [HAVING условие_фильтрации_групп]

6 [ORDER BY столбцы_для_сортировки]
 62

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться.

Например, сгруппируем товары по производителю

1 SELECT Manufacturer, COUNT(*) AS ModelsCount

2 FROM Products

3 GROUP BY Manufacturer

Первый столбец в выражении SELECT - Manufacturer представляет название группы, а

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

Стоит учитывать, что любой столбец, который используется в выражении SELECT (не
считая столбцов, которые хранят результат агрегатных функций), должны быть
указаны после оператора GROUP BY. Так, например, в случае выше столбец
Manufacturer указан и в выражении SELECT, и в выражении GROUP BY.

И если в выражении SELECT производится выборка по одному или нескольким

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

1 SELECT Manufacturer, COUNT(*) AS ModelsCount

2 FROM Products

Другой пример, добавим группировку по количеству товаров:

1 SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount

2 FROM Products

3 GROUP BY Manufacturer, ProductCount

Оператор GROUP BY может выполнять группировку по множеству столбцов.

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

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

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE, но
до выражения ORDER BY:
 63

1 SELECT Manufacturer, COUNT(*) AS ModelsCount

2 FROM Products

3 WHERE Price > 30000

4 GROUP BY Manufacturer

5 ORDER BY ModelsCount DESC

Фильтрация групп. HAVING

Оператор HAVING определяет, какие группы будут включены в выходной

результат, то есть выполняет фильтрацию групп.

Применение HAVING во многом аналогично применению WHERE. Только есть WHERE

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

Например, найдем все группы товаров по производителям, для которых определено

более 1 модели:

1 SELECT Manufacturer, COUNT(*) AS ModelsCount

2 FROM Products

3 GROUP BY Manufacturer

4 HAVING COUNT(*) > 1

При этом в одной команде мы можем использовать выражения WHERE и HAVING:

1 SELECT Manufacturer, COUNT(*) AS ModelsCount

2 FROM Products

3 WHERE Price * ProductCount > 80000

4 GROUP BY Manufacturer

5 HAVING COUNT(*) > 1

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая

стоимость которых больше 80000. Затем выбранные товары группируются по
производителям. И далее фильтруются сами группы - выбираются те группы, которые
содержат больше 1 модели.
 64

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после
выражения HAVING:

1 SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

2 FROM Products

3 WHERE Price * ProductCount > 80000

4 GROUP BY Manufacturer

5 HAVING SUM(ProductCount) > 2

6 ORDER BY Units DESC

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

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

Расширения SQL Server для группировки

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

Дополнительно к стандартным операторам GROUP BY и HAVING SQL Server

поддерживает еще четыре специальных расширения для группировки
данных: ROLLUP, CUBE, GROUPING SETS и OVER.

ROLLUP

Оператор ROLLUP добавляет суммирующую строку в результирующий набор:

1 SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

2 FROM Products

3 GROUP BY Manufacturer WITH ROLLUP

 65

Как видно из скриншота, в конце таблицы была добавлена дополнительная строка,

которая суммирует значение столбцов.

Альтернативный синтаксис запроса, который можно использовать, начиная с версии

MS SQL Server 2008:

1 SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

2 FROM Products

3 GROUP BY ROLLUP(Manufacturer)

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

строку для каждой из подгрупп:

1 SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

2 FROM Products

3 GROUP BY Manufacturer, ProductCount WITH ROLLUP

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

CUBE

CUBE похож на ROLLUP за тем исключением, что CUBE добавляет суммирующие

строки для каждой комбинации групп.

1 SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

2 FROM Products

3 GROUP BY Manufacturer, ProductCount WITH CUBE

GROUPING SETS

Оператор GROUPING SETS аналогично ROLLUP и CUBE добавляет суммирующую

строку для групп. Но при этом он не включает сами группам:

1 SELECT Manufacturer, COUNT(*) AS Models, ProductCount

2 FROM Products

3 GROUP BY GROUPING SETS(Manufacturer, ProductCount)

 66

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

1 SELECT Manufacturer, COUNT(*) AS Models,

2 ProductCount, SUM(ProductCount) AS Units

3 FROM Products

4 GROUP BY GROUPING SETS(ROLLUP(Manufacturer), ProductCount)

С помощью скобок можно определить более сложные сценарии группировки:

1 SELECT Manufacturer, COUNT(*) AS Models,

2 ProductCount, SUM(ProductCount) AS Units

3 FROM Products

4 GROUP BY GROUPING SETS((Manufacturer, ProductCount), ProductCount)

OVER

Выражение OVER позволяет суммировать данные, при этому возвращая те строки,

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

1 SELECT ProductName, Manufacturer, ProductCount,

2 COUNT(*) OVER (PARTITION BY Manufacturer) AS Models,

3 SUM(ProductCount) OVER (PARTITION BY Manufacturer) AS Units

4 FROM Products

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

выражение PARTITION BY и столбец, по которому выполняется группировка.

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

единиц модели и добавляем к этому количество моделей для данного производителя
и общее количество единиц всех моделей производителя:
 67

Подзапросы
Выполнение подзапросов
Последнее обновление: 20.07.2017

T-SQL поддерживает функциональность подзапросов (subquery), то есть таких

запросов, которые могут встроены в другие запросы.

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

1 USE productsdb;

2
3 CREATE TABLE Products

4 (

Id INT IDENTITY PRIMARY KEY,

5
ProductName NVARCHAR(30) NOT NULL,
6
Manufacturer NVARCHAR(20) NOT NULL,
7
ProductCount INT DEFAULT 0,
8
Price MONEY NOT NULL
9
);
10
CREATE TABLE Customers
11
(
12 Id INT IDENTITY PRIMARY KEY,
13 FirstName NVARCHAR(30) NOT NULL
14 );

15 CREATE TABLE Orders

16 (

17 Id INT IDENTITY PRIMARY KEY,

 68

18
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
19
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
20
CreatedAt DATE NOT NULL,
21
ProductCount INT DEFAULT 1,
22
Price MONEY NOT NULL
23
);
24

Таблица Orders содержит ссылки на две другие таблицы через поля ProductId и
CustomerId.

Добавим в таблицы некоторые данные:

1 INSERT INTO Products

2 VALUES ('iPhone 6', 'Apple', 2, 36000),

3 ('iPhone 6S', 'Apple', 2, 41000),

('iPhone 7', 'Apple', 5, 52000),

4
('Galaxy S8', 'Samsung', 2, 46000),
5
('Galaxy S8 Plus', 'Samsung', 1, 56000),
6
('Mi 5X', 'Xiaomi', 2, 26000),
7
('OnePlus 5', 'OnePlus', 6, 38000)
8
9
INSERT INTO Customers VALUES ('Tom'), ('Bob'),('Sam')
10
11
INSERT INTO Orders
12
VALUES
13 (
14 (SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
15 (SELECT Id FROM Customers WHERE FirstName='Tom'),

16 '2017-07-11',

17 2,

18 (SELECT Price FROM Products WHERE ProductName='Galaxy S8')

19 ),
 69

20
21 (

22 (SELECT Id FROM Products WHERE ProductName='iPhone 6S'),

(SELECT Id FROM Customers WHERE FirstName='Tom'),

23
'2017-07-13',
24
1,
25
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
26
),
27
(
28
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'),
29
(SELECT Id FROM Customers WHERE FirstName='Bob'),
30 '2017-07-11',
31 1,
32 (SELECT Price FROM Products WHERE ProductName='iPhone 6S')

33 )

34

Здесь интерес представляет добавление элементов в таблицу Orders. Например,

первый заказ был сделан покупателем Tom на товар Galaxy S8. Соответственно в
таблицу Orders нам надо сохранить информацию о заказе, где поле ProductId
указывает на Id товара Galaxy S8, поле Price - на его цену, а поле CustomerId - на Id
покупателя Tom. Но на момент написания запроса нам может быть неизвестен ни Id
покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос.

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

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

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

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

1 SELECT *

2 FROM Products

3 WHERE Price = (SELECT MIN(Price) FROM Products)

Или найдем товары, цена которых выше средней:

 70

1 SELECT *

2 FROM Products

3 WHERE Price > (SELECT AVG(Price) FROM Products)

Коррелирующие подзапросы

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

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

Но также существуют коррелирующие подзапросы (correlated subquery),

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

Например, выберем все заказы из таблицы Orders, добавив к ним информацию о

товаре:

1 SELECT CreatedAt,

2 Price,

3 (SELECT ProductName FROM Products

4 WHERE [Link] = [Link]) AS Product

5 FROM Orders

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

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

выполняется основной запрос. Например, выберем из таблицы Products те товары,
стоимость которых выше средней цены товаров для данного производителя:

1 SELECT ProductName,

2 Manufacturer,

3 Price,

(SELECT AVG(Price) FROM Products AS SubProds

 71

4
WHERE [Link]=[Link]) AS AvgPrice
5
FROM Products AS Prods
6
WHERE Price >
7
(SELECT AVG(Price) FROM Products AS SubProds
8
WHERE [Link]=[Link])
9

В данном случае определено два коррелирующих подзапроса. Первый подзапрос

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

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

из таблицы Products. И также он будет выполняться для каждой строки.

Чтобы избежать двойственности при фильтрации в подзапросе при сравнении

производителей ([Link]=[Link]) для внешней выборки
установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним
SubProds.

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

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

Подзапросы в основных командах SQL

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


Подзапросы в SELECT

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

1. Использовать в условии в выражении WHERE

 72

2. Использовать в условии в выражении HAVING

3. Использовать в качестве таблицы для выборки в выражении FROM

4. Использовать в качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых

цена выше средней:

1 SELECT *

2 FROM Products

3 WHERE Price > (SELECT AVG(Price) FROM Products)

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

получение средней цены товара: SELECT AVG(Price) FROM Products.

Или выберем всех покупателей из таблицы Customers, у которых нет заказов в

таблице Orders:

1 SELECT * FROM CUSTOMERS

2 WHERE Id NOT IN (SELECT CustomerId FROM Orders)

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

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

Получение набора значений

При использовании в операторах сравнения подзапросы должны возвращать одно

скалярное значение. Но иногда возникает необходимость получить набор значений.
Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор
значений, перед ним необходимо использовать один из
операторов: ALL, SOME или ANY.

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

1 SELECT * FROM Products

2 WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')

 73

Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с

ошибкой.

Допустим, если подзапрос возвращает значения vl1, val2 и val3, то условие

фильтрации фактически было бы аналогично объединению этих значений через
оператор AND:

1 WHERE Price < val1 AND Price < val2 AND Price < val3

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

1 SELECT * FROM Products

2 WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')

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

1 SELECT * FROM Products

2 WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')

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

1 SELECT * FROM Products

2 WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')

Подзапрос как спецификация столбца

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

выберем все заказы и добавим к ним информацию о названии товара:

1 SELECT *,

2 (SELECT ProductName FROM Products WHERE Id=[Link]) AS Product

3 FROM Orders
 74

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое

вставляется в один из столбцов:

1 INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)

2 VALUES

3 (

4 (SELECT Id FROM Products WHERE ProductName='Galaxy S8'),

5 (SELECT Id FROM Customers WHERE FirstName='Tom'),

6 '2017-07-11',

7 2,

(SELECT Price FROM Products WHERE ProductName='Galaxy S8')

8
)
9
Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

1. В качестве устанавливаемого значения после оператора SET

2. Как часть условия в выражении WHERE

Так, увеличим количество купленных товаров на 2 в тех заказах, где покупатель Тоm:

1 UPDATE Orders

2 SET ProductCount = ProductCount + 2

3 WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')

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

1 UPDATE Orders

2 SET Price = (SELECT Price FROM Products WHERE Id=[Link]) + 2000

3 WHERE Id=1

Подзапросы в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим все
заказы на Galaxy S8, которые сделал Bob:
 75

1 DELETE FROM Orders

2 WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8')

3 AND CustomerId=(SELECT Id FROM Customers WHERE FirstName='Bob')

Оператор EXISTS
Последнее обновление: 20.07.2017


Оператор EXISTS позволяет проверить, возвращает ли подзапрос какое-либо

значение. Как правило, этот оператор используется для индикации того, что какая-
либо строка удовлетворяет условию. То есть фактически оператор EXISTS не
возвращает строки, а лишь указывает, что в базе данных есть как минимум одна
строка, которые соответствует данному запросу. Поскольку возвращения набора
строк не происходит, то подзапросы с подобным оператором выполняются довольно
быстро.

Применение оператора имеет следующий формальный синтаксис:

1 WHERE [NOT] EXISTS (подзапрос)

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

1 SELECT *
2 FROM Customers
3 WHERE EXISTS (SELECT * FROM Orders
4 WHERE [Link] = [Link])

Другой пример - найдем все товары из таблицы Products, на которые не было заказов
в таблице Orders:

1 SELECT *
2 FROM Products
3 WHERE NOT EXISTS (SELECT * FROM Orders WHERE [Link] = [Link])

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

опеатор IN:
 76

1 SELECT *
2 FROM Products
3 WHERE Id NOT IN (SELECT ProductId FROM Orders)

Но поскольку при применении EXISTS не происходит выборка строк, то его

использование более оптимально и эффективно, чем использование оператора IN.

Соединение таблиц
Неявное соединение таблиц
Последнее обновление: 20.07.2017

Для сведения данных из разных таблиц мы можем использовать стандартную

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

1 USE productsdb;

2
3 CREATE TABLE Products

4 (

Id INT IDENTITY PRIMARY KEY,

5
ProductName NVARCHAR(30) NOT NULL,
6
Manufacturer NVARCHAR(20) NOT NULL,
7
ProductCount INT DEFAULT 0,
8
Price MONEY NOT NULL
9
);
10
CREATE TABLE Customers
11
(
12 Id INT IDENTITY PRIMARY KEY,
13 FirstName NVARCHAR(30) NOT NULL
14
 77

15 );
16 CREATE TABLE Orders
17 (

18 Id INT IDENTITY PRIMARY KEY,

19 ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,

20 CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,

21 CreatedAt DATE NOT NULL,

22 ProductCount INT DEFAULT 1,

Price MONEY NOT NULL

23
);
24

Здесь таблицы Products и Customers связаны с таблицей Orders связью один ко

многим. Таблица Orders в виде внешних ключей ProductId и CustomerId содержит
ссылки на столбцы Id из соответственно таблиц Products и Customers. Также она
хранит количество купленного товара (ProductCount) и и по какой цене он был куплен
(Price). И кроме того, таблицы также хранит в виде столбца CreatedAt дату покупки.

Пусть эти таблицы будут содержать следующие данные:

1 INSERT INTO Products

2 VALUES ('iPhone 6', 'Apple', 2, 36000),

3 ('iPhone 6S', 'Apple', 2, 41000),

('iPhone 7', 'Apple', 5, 52000),

4
('Galaxy S8', 'Samsung', 2, 46000),
5
('Galaxy S8 Plus', 'Samsung', 1, 56000),
6
('Mi 5X', 'Xiaomi', 2, 26000),
7
('OnePlus 5', 'OnePlus', 6, 38000)
8
9
INSERT INTO Customers VALUES ('Tom'), ('Bob'),('Sam')
10
11
INSERT INTO Orders
12
VALUES
13 (
14
 78

15
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
16
(SELECT Id FROM Customers WHERE FirstName='Tom'),
17
'2017-07-11',
18
2,
19
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
20 ),
21 (
22 (SELECT Id FROM Products WHERE ProductName='iPhone 6S'),

23 (SELECT Id FROM Customers WHERE FirstName='Tom'),

24 '2017-07-13',

25 1,

26 (SELECT Price FROM Products WHERE ProductName='iPhone 6S')

27 ),

(
28
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'),
29
(SELECT Id FROM Customers WHERE FirstName='Bob'),
30
'2017-07-11',
31
1,
32
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
33
)
34

Теперь соединим две таблицы Orders и Customers:

1 SELECT * FROM Orders, Customers

При такой выборке для каждая строка из таблицы Orders будет совмещаться с каждой
строкой из таблицы Customers. То есть, получится перекрестное соединение.
Например, в Orders три строки, а в Customers то же три строки, значит мы получим 3 *
3 = 9 строк:

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

Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ
 79

из Orders связан с конкретным покупателем из Customers, а не со всеми возможными

покупателями.

Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать

строки при условии, что поле CustomerId из Orders соответствует полю Id из
Customers:

1 SELECT * FROM Orders, Customers

2 WHERE [Link] = [Link]

Теперь объединим данные по трем таблицам Orders, Customers и Proucts. То есть

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

1 SELECT [Link], [Link], [Link]

2 FROM Orders, Customers, Products

3 WHERE [Link] = [Link] AND [Link]=[Link]

Поскольку надо соединить три таблицы, то применяются как минимум два условия.
Ключевой таблицей остается Orders, из которой извлекаются все заказы, а затем к
ней подсоединяется данные по клиенту по условию [Link] =
[Link] и данные по товару по условию [Link]=[Link]

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

можем сократить за счет использования псевдонимов таблиц:

1 SELECT [Link], [Link], [Link]

2 FROM Orders AS O, Customers AS C, Products AS P

3 WHERE [Link] = [Link] AND [Link]=[Link]

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

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

1 SELECT [Link], [Link], O.*

2 FROM Orders AS O, Customers AS C, Products AS P

3 WHERE [Link] = [Link] AND [Link]=[Link]

 80

INNER JOIN
Последнее обновление: 20.07.2017


В прошлой теме было рассмотрено неявное соединение таблиц. Оно производилось на

основе простой выборки неявно путем сведения данных. Для явного соединения
данных из двух таблиц применяется оператор JOIN. Общий формальный синтаксис
применения оператора INNER JOIN:

1 SELECT столбцы
2 FROM таблица1
3 [INNER] JOIN таблица2
4 ON условие1
5 [[INNER] JOIN таблица3
6 ON условие2]

После оператора JOIN идет название второй таблицы, из которой надо добавить
данные в выборку. Перед JOIN может использоваться необязательное ключевое
словоINNER. Его наличие или отсутствие ни на что не влияет. Затем после
ключевого слова ON указывается условие соединения. Это условие устанавливает,
как две таблицы будут сравниваться. В большинстве случаев для соединения
применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.

Возьмем таблицы с данными из прошлой темы:

1 USE productsdb;
2
3 CREATE TABLE Products
4 (
5 Id INT IDENTITY PRIMARY KEY,
6 ProductName NVARCHAR(30) NOT NULL,
7 Manufacturer NVARCHAR(20) NOT NULL,
8 ProductCount INT DEFAULT 0,
9 Price MONEY NOT NULL
10 );
11 CREATE TABLE Customers
12 (
13 Id INT IDENTITY PRIMARY KEY,
14 FirstName NVARCHAR(30) NOT NULL
15 );
16 CREATE TABLE Orders
17 (
 81

18 Id INT IDENTITY PRIMARY KEY,

19 ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
20 CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
21 CreatedAt DATE NOT NULL,
22 ProductCount INT DEFAULT 1,
23 Price MONEY NOT NULL
24 );

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

1 SELECT [Link], [Link], [Link]

2 FROM Orders
3 JOIN Products ON [Link] = [Link]

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

указании столбцов для выборки указывается их полное имя вместе с именем таблицы,
например, "[Link]".

Также используя псевдонимы, мы можем сократить код:

1 SELECT [Link], [Link], [Link]

2 FROM Orders AS O
3 JOIN Products AS P
4 ON [Link] = [Link]

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

заказу информацию о покупателе из таблицы Customers:

1 SELECT [Link], [Link], [Link]

2 FROM Orders
3 JOIN Products ON [Link] = [Link]
4 JOIN Customers ON [Link]=[Link]

Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации

выборки или ее сортировки:

1 SELECT [Link], [Link], [Link]

2 FROM Orders
3 JOIN Products ON [Link] = [Link]
4 JOIN Customers ON [Link]=[Link]
5 WHERE [Link] < 45000
6 ORDER BY [Link]

Условия после ключевого слова ON могут быть более сложными по составу:

 82

SELECT [Link], [Link], [Link]

1
FROM Orders
2
JOIN Products ON [Link] = [Link] AND
3
[Link]='Apple'
4
JOIN Customers ON [Link]=[Link]
5
ORDER BY [Link]

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

Apple.

При использовании оператора JOIN следует учитывать, что процесс соединения

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

OUTER JOIN
Последнее обновление: 20.07.2017

В предыдущей теме было рассмотрено внутреннее соединение таблиц. Но MS SQL

Server также поддерживает внешнее соединение или outer join. В отличие от inner join
внешнее соединение возвращает все строки одной или двух таблиц, которые
участвуют в соединении.

Outer Join имеет следующий формальный синтаксис:

1 SELECT столбцы

2 FROM таблица1

3 {LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1

4 [{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...

Перед оператором JOIN указывается одно из ключевых слов LEFT, RIGHT или FULL,
которые определяют тип соединения:

 LEFT: выборка будет содержать все строки из первой или левой таблицы

 RIGHT: выборка будет содержать все строки из второй или правой таблицы
 83

 FULL: выборка будет содержать все строки из обоих таблиц

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

Например, соединим таблицы Orders и Customers:

1 SELECT FirstName, CreatedAt, ProductCount, Price, ProductId

2 FROM Orders LEFT JOIN Customers

3 ON [Link] = [Link]

Таблица Orders является первой или левой таблицей, а таблица Customers - правой
таблицей. Поэтому, так как здесь используется выборка по левой таблице, то вначале
будут выбираться все строки из Orders, а затем к ним по условию [Link]
= [Link] будут добавляться связанные строки из Customers.

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

аналогично INNER Join, но это не так. Inner Join объединяет строки из дух таблиц при
соответствии условию. Если одна из таблиц содержит строки, которые не
соответствуют этому условию, то данные строки не включаются в выходную выборку.
Left Join выбирает все строки первой таблицы и затем присоединяет к ним строки
правой таблицы. К примеру, возьмем таблицу Customers и добавим к покупателям
информацию об их заказах:

1 -- INNER JOIN
2 SELECT FirstName, CreatedAt, ProductCount, Price

3 FROM Customers JOIN Orders

4 ON [Link] = [Link]

5
6 --LEFT JOIN

7 SELECT FirstName, CreatedAt, ProductCount, Price

8 FROM Customers LEFT JOIN Orders

ON [Link] = [Link]
9

Изменим в примере выше тип соединения на правостороннее:

 84

1 SELECT FirstName, CreatedAt, ProductCount, Price, ProductId

2 FROM Orders RIGHT JOIN Customers

3 ON [Link] = [Link]

Теперь будут выбираться все строки из Customers, а к ним уже будет присоединяться
связанные по условию строки из таблицы Orders:

Поскольку один из покупателей из таблицы Customers не имеет связанных заказов из

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

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

пользователях и товарах:

1 SELECT [Link], [Link],

2 [Link], [Link]

3 FROM Orders

4 LEFT JOIN Customers ON [Link] = [Link]

5 LEFT JOIN Products ON [Link] = [Link]

И также можно применять более комплексные условия с фильтрацией и сортировкой.

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

1 SELECT [Link], [Link],

2 [Link], [Link]

3 FROM Orders

4 LEFT JOIN Customers ON [Link] = [Link]

5 LEFT JOIN Products ON [Link] = [Link]

6 WHERE [Link] < 45000

ORDER BY [Link]
7
 85

Или выберем всех пользователей из Customers, у которых нет заказов в таблице

Orders:

1 SELECT FirstName FROM Customers

2 LEFT JOIN Orders ON [Link] = [Link]

3 WHERE [Link] IS NULL

Также можно комбинировать Inner Join и Outer Join:

1 SELECT [Link], [Link],

2 [Link], [Link]

3 FROM Orders

4 JOIN Products ON [Link] = [Link] AND [Link] < 45000

5 LEFT JOIN Customers ON [Link] = [Link]

6 ORDER BY [Link]

Вначале по условию к таблице Orders через Inner Join присоединяется связанная

информация из Products, затем через Outer Join добавляется информация из таблицы
Customers.

Cross Join

Cross Join или перекрестное соединение создает набор строк, где каждая строка из
одной таблицы соединяется с каждой строкой из второй таблицы. Например,
соединим таблицу заказов Orders и таблицу покупателей Customers:

1 SELECT * FROM Orders CROSS JOIN Customers

Если в таблице Orders 3 строки, а в таблице Customers то же три строки, то в

результате перекрестного соединения создается 3 * 3 = 9 строк вне зависимости,
связаны ли данные строки или нет.

При неявном перекрестном соединении можно опустить оператор CROSS JOIN и просто
перечислить все получаемые таблицы:

1 SELECT * FROM Orders, Customers

Группировка в соединениях
 86
Последнее обновление: 20.07.2017


В выражениях INNER/OUTER JOIN также можно использовать группировку. Например,

выведем для каждого пользователя количество заказов, которые он сделал:

1 SELECT FirstName, COUNT([Link])

2 FROM Customers JOIN Orders
3 ON [Link] = [Link]
4 GROUP BY [Link], [Link];

Критерием группировки выступают Id и имя покупателя. Выражение SELECT выбирает

имя покупателя и количество заказов, используя столбец Id из таблицы Orders.

Так как это INNER JOIN, то в группах будут только те покупатели, у которых есть
заказы.

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

применяется OUTER JOIN:

1 SELECT FirstName, COUNT([Link])

2 FROM Customers LEFT JOIN Orders
3 ON [Link] = [Link]
4 GROUP BY [Link], [Link];

Или выведем товары с общей суммой сделанных заказов:

1 SELECT [Link], [Link],

2 SUM([Link] * [Link]) AS Units
3 FROM Products LEFT JOIN Orders
4 ON [Link] = [Link]
5 GROUP BY [Link], [Link], [Link]

UNION
Последнее обновление: 20.07.2017



 87

Оператор UNION подобно inner join или outer join позволяет соединить две таблицы.
Но в отличие от inner/outer join объединения соединяют не столбцы разных таблиц, а
два однотипных набора в один. Формальный синтаксис объединения:

1 SELECT_выражение1
2 UNION [ALL] SELECT_выражение2
3 [UNION [ALL] SELECT_выражениеN]

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

1 USE usersdb;
2
3 CREATE TABLE Customers
4 (
5 Id INT IDENTITY PRIMARY KEY,
6 FirstName NVARCHAR(20) NOT NULL,
7 LastName NVARCHAR(20) NOT NULL,
8 AccountSum MONEY
9 );
10 CREATE TABLE Employees
11 (
12 Id INT IDENTITY PRIMARY KEY,
13 FirstName NVARCHAR(20) NOT NULL,
14 LastName NVARCHAR(20) NOT NULL,
15 );
16
17 INSERT INTO Customers VALUES
18 ('Tom', 'Smith', 2000),
19 ('Sam', 'Brown', 3000),
20 ('Mark', 'Adams', 2500),
21 ('Paul', 'Ins', 4200),
22 ('John', 'Smith', 2800),
23 ('Tim', 'Cook', 2800)
24
25 INSERT INTO Employees VALUES
26 ('Homer', 'Simpson'),
27 ('Tom', 'Smith'),
28 ('Mark', 'Adams'),
29 ('Nick', 'Svensson')

Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных,
могут характеризоваться двумя общими атрибутами - именем (FirstName) и фамилией
(LastName). Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:

1 SELECT FirstName, LastName

2 FROM Customers
3 UNION SELECT FirstName, LastName FROM Employees
 88

В данном случае из первой таблицы выбираются два значения - имя и фамилия

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

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

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

1 SELECT FirstName + ' ' +LastName AS FullName

2 FROM Customers
3 UNION SELECT FirstName + ' ' + LastName AS EmployeeName
4 FROM Employees
5 ORDER BY FullName DESC

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

объединение имени и фамилии клиента или сотрудника. Но в случае с клиентами
столбец будет называться FullName, а в случае с сотрудниками - EmployeeName. Тем
не менее для сортировки применяется название столбца из первой выборки и он же
будет в результирующей выборке:

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

1 SELECT FirstName, LastName, AccountSum

2 FROM Customers
3 UNION SELECT FirstName, LastName
4 FROM Employees

Также соответствующие столбцы должны соответствовать по типу. Так, следующий

пример завершится с ошибкой из-за не соответствия по типу данных:

1 SELECT FirstName, LastName

2 FROM Customers
3 UNION SELECT Id, LastName
4 FROM Employees

В данном случае первый столбец первой выборки имеет тип NVARCHAR, то есть
хранит строку. Первый столбец второй выборки - Id имеет тип INT, то есть хранит
число.
 89

Если оба объединяемых набора содержат в строках идентичные значения, то при

объединении повторяющиеся строки удаляются. Например, в случае с таблицами
Customers и Employees сотрудники банка могут быть одновременно его клиентами и
содержаться в обеих таблицах. При объединении в примерах выше всех
дублирующиеся строки удалялись. Если же необходимо при объединении сохранить
все, в том числе повторяющиеся строки, то для этого необходимо использовать
оператор ALL:

1 SELECT FirstName, LastName

2 FROM Customers
3 UNION ALL SELECT FirstName, LastName
4 FROM Employees

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

суммы на счете клиента нам надо начислять ему определенные проценты:

1 SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum

2 FROM Customers WHERE AccountSum < 3000
3 UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum
4 FROM Customers WHERE AccountSum >= 3000

В данном случае если сумма меньше 3000, то начисляются проценты в размере 10%
от суммы на счете. Если на счете больше 3000, то проценты увеличиваются до 30%.

EXCEPT
Последнее обновление: 20.07.2017


Оператор EXCEPT позволяет найти разность двух выборок, то есть те строки которые
есть в первой выборке, но которых нет во второй. Для его использования применяется
следующий формальный синтаксис:

1 SELECT_выражение1
2 EXCEPT SELECT_выражение2

Для примера возьмем таблицы из прошлой темы:

1 USE usersdb;
2
3 CREATE TABLE Customers
 90

4 (
5 Id INT IDENTITY PRIMARY KEY,
6 FirstName NVARCHAR(20) NOT NULL,
7 LastName NVARCHAR(20) NOT NULL,
8 AccountSum MONEY
9 );
10 CREATE TABLE Employees
11 (
12 Id INT IDENTITY PRIMARY KEY,
13 FirstName NVARCHAR(20) NOT NULL,
14 LastName NVARCHAR(20) NOT NULL,
15 );
16
17 INSERT INTO Customers VALUES
18 ('Tom', 'Smith', 2000),
19 ('Sam', 'Brown', 3000),
20 ('Mark', 'Adams', 2500),
21 ('Paul', 'Ins', 4200),
22 ('John', 'Smith', 2800),
23 ('Tim', 'Cook', 2800)
24
25 INSERT INTO Employees VALUES
26 ('Homer', 'Simpson'),
27 ('Tom', 'Smith'),
28 ('Mark', 'Adams'),
29 ('Nick', 'Svensson')

Таблица Employees содержит данные обо всех сотрудниках банка, а таблица

Customers - обо всех клиентах. Но сотрудники банка могут также быть его клиентами.
И допустим, нам надо найти всех клиентов банка, которые не являются его
сотрудниками:

1 SELECT FirstName, LastName

2 FROM Customers
3 EXCEPT SELECT FirstName, LastName
4 FROM Employees

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

1 SELECT FirstName, LastName

2 FROM Employees
3 EXCEPT SELECT FirstName, LastName
4 FROM Customers

INTERSECT
 91
Последнее обновление: 20.07.2017


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

1 SELECT_выражение1
2 INTERSECT SELECT_выражение2

Для примера возьмем таблицы из прошлой темы:

1 USE usersdb;
2
3 CREATE TABLE Customers
4 (
5 Id INT IDENTITY PRIMARY KEY,
6 FirstName NVARCHAR(20) NOT NULL,
7 LastName NVARCHAR(20) NOT NULL,
8 AccountSum MONEY
9 );
10 CREATE TABLE Employees
11 (
12 Id INT IDENTITY PRIMARY KEY,
13 FirstName NVARCHAR(20) NOT NULL,
14 LastName NVARCHAR(20) NOT NULL,
15 );
16
17 INSERT INTO Customers VALUES
18 ('Tom', 'Smith', 2000),
19 ('Sam', 'Brown', 3000),
20 ('Mark', 'Adams', 2500),
21 ('Paul', 'Ins', 4200),
22 ('John', 'Smith', 2800),
23 ('Tim', 'Cook', 2800)
24
25 INSERT INTO Employees VALUES
26 ('Homer', 'Simpson'),
27 ('Tom', 'Smith'),
28 ('Mark', 'Adams'),
29 ('Nick', 'Svensson')

В таблице Customers хранятся все клиенты банка, а в таблице Employees - все его
сотрудники. Но сотрудники могут быть одновременно и клиентами банка, поэтому их
 92

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

1 SELECT FirstName, LastName

2 FROM Employees
3 INTERSECT SELECT FirstName, LastName
4 FROM Customers

Встроенные функции
Функции для работы со строками
Последнее обновление: 29.07.2017

Для работы со строками в T-SQL можно применять следующие функции:

 LEN: возвращает количество символов в строке. В качестве параметра в

функцию передается строка, для которой надо найти длину:

1 SELECT LEN('Apple') -- 5

 LTRIM: удаляет начальные пробелы из строки. В качестве параметра

принимает строку:

1 SELECT LTRIM(' Apple')

 RTRIM: удаляет конечные пробелы из строки. В качестве параметра принимает

строку:

1 SELECT RTRIM(' Apple ')

 CHARINDEX: возвращает индекс, по которому находится первое вхождение

подстроки в строке. В качестве первого параметра передается подстрока, а в
качестве второго - строка, в которой надо вести поиск:
 93

1 SELECT CHARINDEX('pl', 'Apple') -- 3

 PATINDEX: возвращает индекс, по которому находится первое вхождение

определенного шаблона в строке:

1 SELECT PATINDEX('%p_e%', 'Apple') -- 3

 LEFT: вырезает с начала строки определенное количество символов. Первый

параметр функции - строка, а второй - количество символов, которые надо
вырезать сначала строки:

1 SELECT LEFT('Apple', 3) -- App

 RIGHT: вырезает с конца строки определенное количество символов. Первый

параметр функции - строка, а второй - количество символов, которые надо
вырезать сначала строки:

1 SELECT RIGHT('Apple', 3) -- ple

 SUBSTRING: вырезает из строки подстроку определенной длиной, начиная с

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

1 SELECT SUBSTRING('Galaxy S8 Plus', 8, 2) -- S8

 REPLACE: заменяет одну подстроку другой в рамках строки. Первый параметр

функции - строка, второй - подстрока, которую надо заменить, а третий -
подстрока, на которую надо заменить:

SELECT REPLACE('Galaxy S8 Plus', 'S8 Plus', 'Note 8') -- Galaxy Note

1 8

 REVERSE: переворачивает строку наоборот:

1 SELECT REVERSE('123456789') -- 987654321

 CONCAT: объединяет две строки в одну. В качестве параметра принимает от 2-

х и более строк, которые надо соединить:

1 SELECT CONCAT('Tom', ' ', 'Smith') -- Tom Smith

 LOWER: переводит строку в нижний регистр:

 94

1 SELECT LOWER('Apple') -- apple

 UPPER: переводит строку в верхний регистр

1 SELECT UPPER('Apple') -- APPLE

 SPACE: возвращает строку, которая содержит определенное количество

пробелов

Например, возьмем таблицу:

1 CREATE TABLE Products

2 (

3 Id INT IDENTITY PRIMARY KEY,

4 ProductName NVARCHAR(30) NOT NULL,

5 Manufacturer NVARCHAR(20) NOT NULL,

6 ProductCount INT DEFAULT 0,

7 Price MONEY NOT NULL

);
8

И при извлечении данных применим строковые функции:

1 SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation,

2 CONCAT(ProductName, ' - ', Manufacturer) AS FullProdName

3 FROM Products

4 ORDER BY Abbreviation

Функции для работы с числами

Последнее обновление: 29.07.2017


Для работы с числовыми данными T-SQL предоставляет ряд функций:

 95

 ROUND: округляет число. В качестве первого параметра передается число.

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

1 SELECT ROUND(1342.345, 2) -- 1342.350

2 SELECT ROUND(1342.345, -2) -- 1300.000

 ISNUMERIC: определяет, является ли значение числом. В качестве

параметра функция принимает выражение. Если выражение является числом,
то функция возвращает 1. Если не является, то возвращается 0.

1 SELECT ISNUMERIC(1342.345) -- 1
2 SELECT ISNUMERIC('1342.345') -- 1
3 SELECT ISNUMERIC('SQL') -- 0
4 SELECT ISNUMERIC('13-04-2017') -- 0

 ABS: возвращает абсолютное значение числа.

1 SELECT ABS(-123) -- 123

 CEILING: возвращает наименьшее целое число, которое больше или равно

текущему значению.

1 SELECT CEILING(-123.45) -- -123

2 SELECT CEILING(123.45) -- 124

 FLOOR: возвращает наибольшее целое число, которое меньше или равно

текущему значению.

1 SELECT FLOOR(-123.45) -- -124

2 SELECT FLOOR(123.45) -- 123

 SQUARE: возводит число в квадрат.

1 SELECT SQUARE(5) -- 25

 SQRT: получает квадратный корень числа.

1 SELECT SQRT(225) -- 15

 RAND: генерирует случайное число с плавающей точкой в диапазоне от 0 до

1.

1 SELECT RAND() -- 0.707365088352935

2 SELECT RAND() -- 0.173808327956812
 96

 COS: возвращает косинус угла, выраженного в радианах

1 SELECT COS(1.0472) -- 0.5 - 60 градусов

 SIN: возвращает синус угла, выраженного в радианах

1 SELECT SIN(1.5708) -- 1 - 90 градусов

 TAN: возвращает тангенс угла, выраженного в радианах

1 SELECT TAN(0.7854) -- 1 - 45 градусов

Например, возьмем таблицу:

1 CREATE TABLE Products

2 (
3 Id INT IDENTITY PRIMARY KEY,
4 ProductName NVARCHAR(30) NOT NULL,
5 Manufacturer NVARCHAR(20) NOT NULL,
6 ProductCount INT DEFAULT 0,
7 Price MONEY NOT NULL
8 );

Округлим произведение цены товара на количество этого товара:

1 SELECT ProductName, ROUND(Price * ProductCount, 2)

2 FROM Products

Функции по работе с датами и временем

Последнее обновление: 29.07.2017


T-SQL предоставляет ряд функций для работы с датами и временем:

 GETDATE: возвращает текущую локальную дату и время на основе

системных часов в виде объекта datetime

1 SELECT GETDATE() -- 2017-07-28 21:34:55.830

 GETUTCDATE: возвращает текущую локальную дату и время по гринвичу

(UTC/GMT) в виде объекта datetime

1 SELECT GETUTCDATE() -- 2017-07-28 18:34:55.830

 97

 SYSDATETIME: возвращает текущую локальную дату и время на основе

системных часов, но отличие от GETDATE состоит в том, что дата и время
возвращаются в виде объекта datetime2

1 SELECT SYSDATETIME() -- 2017-07-28 21:02:22.7446744

 SYSUTCDATETIME: возвращает текущую локальную дату и время по

гринвичу (UTC/GMT) в виде объекта datetime2

1 SELECT SYSUTCDATETIME() -- 2017-07-28 18:20:27.5202777

 SYSDATETIMEOFFSET: возвращает объект datetimeoffset(7), который

содержит дату и время относительно GMT

1 SELECT SYSDATETIMEOFFSET() -- 2017-07-28 21:02:22.7446744 +03:00

 DAY: возвращает день даты, который передается в качестве параметра

1 SELECT DAY(GETDATE()) -- 28

 MONTH: возвращает месяц даты

1 SELECT MONTH(GETDATE()) -- 7

 YEAR: возвращает год из даты

1 SELECT YEAR(GETDATE()) -- 2017

 DATENAME: возвращает часть даты в виде строки. Параметр выбора части

даты передается в качестве первого параметра, а сама дата передается в
качестве второго параметра:

1 SELECT DATENAME(month, GETDATE()) -- July

 Для определения части даты можно использовать следующие параметры (в

скобках указаны их сокращенные версии):
o year (yy, yyyy): год
o quarter (qq, q): квартал
o month (mm, m): месяц
o dayofyear (dy, y): день года
o day (dd, d): день месяца
o week (wk, ww): неделя
o weekday (dw): день недели
o hour (hh): час
o minute (mi, n): минута
o second (ss, s): секунда
o millisecond (ms): миллисекунда
 98

o microsecond (mcs): микросекунда

o nanosecond (ns): наносекунда
o tzoffset (tz): смешение в минутах относительно гринвича (для
объекта datetimeoffset)
 DATEPART: возвращает часть даты в виде числа. Параметр выбора части
даты передается в качестве первого параметра (используются те же
параметры, что и для DATENAME), а сама дата передается в качестве второго
параметра:

1 SELECT DATEPART(month, GETDATE()) -- 7

 DATEADD: возвращает дату, которая является результатом сложения числа к

определенному компоненту даты. Первый параметр представляет компонент
даты, описанный выше для функции DATENAME. Второй параметр -
добавляемое количество. Третий параметр - сама дата, к которой надо сделать
прибавление:

1 SELECT DATEADD(month, 2, '2017-7-28') -- 2017-09-28 00:00:00.000

2 SELECT DATEADD(day, 5, '2017-7-28') -- 2017-08-02 00:00:00.000
3 SELECT DATEADD(day, -5, '2017-7-28') -- 2017-07-23 00:00:00.000

 Если добавляемое количество представляет отрицательное число, то

фактически происходит уменьшение даты.
 DATEDIFF: возвращает разницу между двумя датами. Первый параметр -
компонент даты, который указывает, в каких единицах стоит измерять
разницу. Второй и третий параметры - сравниваемые даты:

SELECT DATEDIFF(year, '2017-7-28', '2018-9-28') -- разница 1 год

1 SELECT DATEDIFF(month, '2017-7-28', '2018-9-28') -- разница 14
2 месяцев
3 SELECT DATEDIFF(day, '2017-7-28', '2018-9-28') -- разница 427
дней

 TODATETIMEOFFSET: возвращает значение datetimeoffset, которое

является результатом сложения временного смещения с другим объектом
datetimeoffset

1 SELECT TODATETIMEOFFSET('2017-7-28 01:10:22', '+03:00')

 SWITCHOFFSET: возвращает значение datetimeoffset, которое является

результатом сложения временного смещения с объектом datetime2

1 SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:30')

 EOMONTH: возвращает дату последнего дня для месяца, который

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

1 SELECT EOMONTH('2017-02-05') -- 2017-02-28

2 SELECT EOMONTH('2017-02-05', 3) -- 2017-05-31

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

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

1 SELECT DATEFROMPARTS(2017, 7, 28) -- 2017-07-28

 ISDATE: проверяет, является ли выражение датой. Если является, то

возвращает 1, иначе возвращает 0.

1 SELECT ISDATE('2017-07-28') -- 1
2 SELECT ISDATE('2017-28-07') -- 0
3 SELECT ISDATE('28-07-2017') -- 0
4 SELECT ISDATE('SQL') -- 0

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

заказов, которая содержит дату заказа:

1 CREATE TABLE Orders

2 (
3 Id INT IDENTITY PRIMARY KEY,
4 ProductId INT NOT NULL,
5 CustomerId INT NOT NULL,
6 CreatedAt DATE NOT NULL DEFAULT GETDATE(),
7 ProductCount INT DEFAULT 1,
8 Price MONEY NOT NULL
9 );

Выражение DEFAULT GETDATE() указывает, что если при добавлении данных не

передается дата, то она автоматически вычисляется с помощью функции GETDATE().

Другой пример - найдем заказы, которые были сделаны 16 дней назад:

1 SELECT * FROM Orders

2 WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 16

Преобразование данных
Последнее обновление: 29.07.2017


 100

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

datetime

smalldatetime

float

real

decimal

money

smallmoney

int

smallint

tinyint

bit

nvarchar

nchar

varchar

char

То есть SQL Server автоматически может преобразовать число 100.0 (float) в дату и
время (datetime).

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

приоритетом к типам с низшим приоритетом, то надо выполнять явное приведение
типов. Для этого в T-SQL определены две функции: CONVERT и CAST.

Функция CAST преобразует выражение одного типа к другому. Она имеет следующую
форму:

1 CAST(выражение AS тип_данных)

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

 101

1
CREATE TABLE Products
2
(
3
Id INT IDENTITY PRIMARY KEY,
4
ProductName NVARCHAR(30) NOT NULL,
5
Manufacturer NVARCHAR(20) NOT NULL,
6
ProductCount INT DEFAULT 0,
7 Price MONEY NOT NULL
8 );
9 CREATE TABLE Customers

10 (

11 Id INT IDENTITY PRIMARY KEY,

12 FirstName NVARCHAR(30) NOT NULL

13 );

14 CREATE TABLE Orders

(
15
Id INT IDENTITY PRIMARY KEY,
16
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
17
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
18
CreatedAt DATE NOT NULL,
19
ProductCount INT DEFAULT 1,
20
Price MONEY NOT NULL
21
);
22

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

строку:

SELECT Id, CAST(CreatedAt AS nvarchar) + '; total: ' + CAST(Price * ProductCount AS

1
nvarchar)
2 FROM Orders
 102

Convert

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

какое-то дополнительное форматирование, то можно использовать
функцию CONVERT. Она имеет следующую форму:

1 CONVERT(тип_данных, выражение [, стиль])

Третий необязательный параметр задает стиль форматирования данных. Этот

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

 0 или 100 - формат даты "Mon dd yyyy hh:miAM/PM" (значение по умолчанию)

 1 или 101 - формат даты "mm/dd/yyyy"

 3 или 103 - формат даты "dd/mm/yyyy"

 7 или 107 - формат даты "Mon dd, yyyy hh:miAM/PM"

 8 или 108 - формат даты "hh:mi:ss"

 10 или 110 - формат даты "mm-dd-yyyy"

 14 или 114 - формат даты "hh:mi:ss:mmmm" (24-часовой формат времени)

Некоторые значения для форматирования данных типа money в строку:

 0 - в дробной части числа остаются только две цифры (по умолчанию)

 1 - в дробной части числа остаются только две цифры, а для разделения

разрядов применяется запятая

 2 - в дробной части числа остаются только четыре цифры

Например, выведем дату и стоимость заказов с форматированием:

1 SELECT CONVERT(nvarchar, CreatedAt, 3),

2 CONVERT(nvarchar, Price * ProductCount, 1)

3 FROM Orders
 103

TRY_CONVERT

При использовании функций CAST и CONVERT SQL Server выбрасывает исключение,

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

1 SELECT CONVERT(int, 'sql')

Чтобы избежать генерации исключения можно использовать функцию TRY_CONVERT.

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

1 SELECT TRY_CONVERT(int, 'sql') -- NULL

2 SELECT TRY_CONVERT(int, '22') -- 22

Дополнительные функции

Кроме CAST, CONVERT, TRY_CONVERT есть еще ряд функций, которые могут
использоваться для преобразования в ряд типов:

 STR(float [, length [,decimal]]): преобразует число в строку. Второй параметр

указывает на длину строки, а третий - сколько знаков в дробной части числа
надо оставлять

 CHAR(int): преобразует числовой код ASCII в символ. Нередко используется

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

 ASCII(char): преобразует символ в числовой код ASCII

 NCHAR(int): преобразует числовой код UNICODE в символ

 UNICODE(char): преобразует символ в числовой код UNICODE

1 SELECT STR(123.4567, 6,2) -- 123.46

2 SELECT CHAR(219) -- Ы

3 SELECT ASCII('Ы') -- 219

4 SELECT NCHAR(1067) -- Ы

5 SELECT UNICODE('Ы') -- 1067

Функции CASE и IIF

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


 104


CASE

Функция CASE проверяет значение некоторого выражение, и в зависимости от

результата проверки может возвращать тот или иной результат.

CASE принимает следующую форму:

1 CASE выражение

2 WHEN значение_1 THEN результат_1

3 WHEN значение_2 THEN результат_2

4 .................................

5 WHEN значение_N THEN результат_N

6 [ELSE альтернативный_результат]

END
7

Возьмем для примера следующую таблицу Products:

1 CREATE TABLE Products

2 (

3 Id INT IDENTITY PRIMARY KEY,

4 ProductName NVARCHAR(30) NOT NULL,

5 Manufacturer NVARCHAR(20) NOT NULL,

6 ProductCount INT DEFAULT 0,

7 Price MONEY NOT NULL

);
8

Выполним запрос к этой таблице и используем функцию CASE:

1 SELECT ProductName, Manufacturer,

2 CASE ProductCount
 105

3 WHEN 1 THEN 'Товар заканчивается'

4 WHEN 2 THEN 'Мало товара'

5 WHEN 3 THEN 'Есть в наличии'

6 ELSE 'Много товара'

7 END AS EvaluateCount

8 FROM Products

Здесь значения столбца ProductCount последовательно сравнивается со значениями

после операторов WHEN. В зависимости от значения столбца ProductCount функция
CASE будет возвращать одну из строк, которая идет после соответствующего
оператора THEN. Для возвращаемого результата определен столбец EvaluateCount:

Также функция CASE может принимать еще одну форму:

1 CASE

2 WHEN выражение_1 THEN результат_1

3 WHEN выражение_2 THEN результат_2

4 .................................

5 WHEN выражение_N THEN результат_N

6 [ELSE альтернативный_результат]

END
7

Например, применительно к таблице Products:

1 SELECT ProductName, Manufacturer,

2 CASE

3 WHEN Price > 50000 THEN 'Категория A'

4 WHEN Price BETWEEN 40000 AND 50000 THEN 'Категория B'

5 WHEN Price BETWEEN 30000 AND 40000 THEN 'Категория C'

6 ELSE 'Категория D'

7 END AS Category

FROM Products
8
 106

Фактически все то же самое, что и в предыдущем примере, только после CASE не

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

IIF

Функция IIF в зависимости от результата условного выражения возвращает одно из

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

1 IIF(условие, значение_1, значение_2)

Если условие в функции IIF истинно то возвращается значение_1, если ложно, то

возвращается значение_2. Например:

1 SELECT ProductName, Manufacturer,

2 IIF(ProductCount>3, 'Много товара', 'Мало товара')

3 FROM Products

Функции NEWID, ISNULL и COALESCE

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


NEWID

Для генерации объекта UNIQUEIDENTIFIER, то есть некоторого уникального значения,

используется функция NEWID(). Например, мы можем определить для столбца
первичного ключа тип UNIQUEIDENTIFIER и по умолчанию присваивать ему значение
функции NEWID:

1 CREATE TABLE Clients

2 (

3 Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),

FirstName NVARCHAR(20) NOT NULL,

4
 107

5 LastName NVARCHAR(20) NOT NULL,

6 Phone NVARCHAR(20) NULL,

7 Email NVARCHAR(20) NULL

8 )

9
10 INSERT INTO Clients (FirstName, LastName, Phone, Email)

11 VALUES ('Tom', 'Smith', '+36436734', NULL),

('Bob', 'Simpson', NULL, NULL)

12
ISNULL

Функция ISNULL проверяет значение некоторого выражения. Если оно равно NULL, то
функция возвращает значение, которое передается в качестве второго параметра:

1 ISNULL(выражение, значение)

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

функцию ISNULL:

1 SELECT FirstName, LastName,

2 ISNULL(Phone, 'не определено') AS Phone,

3 ISNULL(Email, 'неизвестно') AS Email

4 FROM Clients

COALESCE

Функция COALESCE принимает список значений и возвращает первое из них, которое

не равно NULL:

1 COALESCE(выражение_1, выражение_2, выражение_N)

Например, выберем из таблицы Clients пользователей и в контактах у них определим

либо телефон, либо электронный адрес, если они не равны NULL:

1 SELECT FirstName, LastName,

2 COALESCE(Phone, Email, 'не определено') AS Contacts

3 FROM Clients
 108

То есть в данном случае возвращается телефон, если он определен. Если он не

определен, то возвращается электронный адрес. Если и электронный адрес не
определен, то возвращается строка "не определено".

Переменные и управляющие конструкции

Переменные в T-SQL
Последнее обновление: 14.08.2017

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

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

1 DECLARE @название_переменной тип_данных

Например, определим переменную name, которая будет иметь тип NVARCHAR:

1 DECLARE @name NVARCHAR(20)

Также можно определить через запятую сразу несколько переменных:

1 DECLARE @name NVARCHAR(20), @age INT

С помощью выражения SET можно присвоить переменной некоторое значение:

1 DECLARE @name NVARCHAR(20), @age INT;

2 SET @name='Tom';

3 SET @age = 18;

Так как @name предоставляет тип NVARCHAR, то есть строку, то этой переменной
соответственно и присваивается строка. А переменной @age присваивается число, так
как она представляет тип INT.
 109

Выражение PRINT возвращает сообщение клиенту. Например:

1 PRINT 'Hello World'

И с его помощью мы можем вывести значение переменной:

1 DECLARE @name NVARCHAR(20), @age INT;

2 SET @name='Tom';

3 SET @age = 18;

4 PRINT 'Name: ' + @name;

5 PRINT 'Age: ' + CONVERT(CHAR, @age);

При выполнении скрипта внизу SQL Server Management Studio отобразится значение
переменных:

Также можно использовать для получения значения команду SELECT:

1 DECLARE @name NVARCHAR(20), @age INT;

2 SET @name='Tom';

3 SET @age = 18;

4 SELECT @name, @age;

Переменные в запросах
Последнее обновление: 14.08.2017


Через переменные мы можем передавать данные в запросы. И также мы можем

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

1 SELECT @переменная_1 = спецификация_столбца_1,

2 @переменная_2 = спецификация_столбца_2,
3 ......................................
 110

4 @переменная_N = спецификация_столбца_N

Кроме того, в выражении SET значение, присваиваемое переменной, также может

быть результатом команды SELECT.

Например, пусть у нас будут следующие таблицы:

1 CREATE TABLE Products

2 (
3 Id INT IDENTITY PRIMARY KEY,
4 ProductName NVARCHAR(30) NOT NULL,
5 Manufacturer NVARCHAR(20) NOT NULL,
6 ProductCount INT DEFAULT 0,
7 Price MONEY NOT NULL
8 );
9 CREATE TABLE Customers
10 (
11 Id INT IDENTITY PRIMARY KEY,
12 FirstName NVARCHAR(30) NOT NULL
13 );
14 CREATE TABLE Orders
15 (
16 Id INT IDENTITY PRIMARY KEY,
17 ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
18 CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
19 CreatedAt DATE NOT NULL,
20 ProductCount INT DEFAULT 1,
21 Price MONEY NOT NULL
22 );

Используем переменные при извлечении данных:

1 DECLARE @maxPrice MONEY,

2 @minPrice MONEY,
3 @dif MONEY,
4 @count INT
5
6 SET @count = (SELECT SUM(ProductCount) FROM Orders);
7
8 SELECT @minPrice=MIN(Price), @maxPrice = MAX(Price) FROM Products
9
10 SET @dif = @maxPrice - @minPrice;
11
12 PRINT 'Всего продано: ' + STR(@count, 5) + ' товарa(ов)';
13 PRINT 'Разница между максимальной и минимальной ценой: ' + STR(@dif)

В данном случае переменная @count будет содержать сумму всех значений из

столбца ProductCount таблицы Orders, то есть общее количество проданных товаров.
 111

Переменные @min и @max хранят соответственно минимальное и максимальное

значения столбца Price из таблицы Products, а переменная @dif - разницу между этими
значениями. И подобно простым значениям, переменные также могут участвовать в
операциях.

Другой пример:

1 DECLARE @sum MONEY, @id INT, @prodid INT, @name NVARCHAR(20);

2 SET @id=2;
3
4 SELECT @sum = SUM([Link]*[Link]),
5 @name=[Link], @prodid = [Link]
6 FROM Orders
7 INNER JOIN Products ON ProductId = [Link]
8 GROUP BY [Link], [Link]
9 HAVING [Link]=@id
10
11 PRINT 'Товар ' + @name + ' продан на сумму ' + STR(@sum)

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

столбцам Id и ProductName из таблицы Products. Затем данные фильтруются по
столбцу Id из Products. А извлеченные данные попадают в переменные @sum, @name,
@prodid.

Условные выражения
Последнее обновление: 14.08.2017


Для выполнения действий по условию используется выражение IF ... ELSE. SQL

Server вычисляет выражение после ключевого слово IF. И если оно истинно, то
выполняются инструкции после ключевого слова IF. Если условие ложно, то
выполняются инструкции после ключевого слова ELSE.

Если после IF или ELSE располагает блок инструкций, то этот блок заключается между
ключевыми словами BEGIN и END:

1 IF условие
2 {инструкция|BEGIN...END}
3 [ELSE
 112

4 {инструкция|BEGIN...END}]

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

Например, пусть у нас есть следующие таблицы:

1 CREATE TABLE Products

2 (
3 Id INT IDENTITY PRIMARY KEY,
4 ProductName NVARCHAR(30) NOT NULL,
5 Manufacturer NVARCHAR(20) NOT NULL,
6 ProductCount INT DEFAULT 0,
7 Price MONEY NOT NULL
8 );
9 CREATE TABLE Customers
10 (
11 Id INT IDENTITY PRIMARY KEY,
12 FirstName NVARCHAR(30) NOT NULL
13 );
14 CREATE TABLE Orders
15 (
16 Id INT IDENTITY PRIMARY KEY,
17 ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
18 CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
19 CreatedAt DATE NOT NULL,
20 ProductCount INT DEFAULT 1,
21 Price MONEY NOT NULL
22 );

Таблица Orders представляет заказы, а столбец CreatedAt - дату заказов. Узнаем, были
ли заказы за последние 10 дней:

1 DECLARE @lastDate DATE

2
3 SELECT @lastDate = MAX(CreatedAt) FROM Orders
4
5 IF DATEDIFF(day, @lastDate, GETDATE()) > 10
6 PRINT 'За последние десять дней не было заказов'

Добавим выражение ELSE:

1 DECLARE @lastDate DATE

2
3 SELECT @lastDate = MAX(CreatedAt) FROM Orders
4
5 IF DATEDIFF(day, @lastDate, GETDATE()) > 10
6 PRINT 'За последние десять дней не было заказов'
7 ELSE
 113

8 PRINT 'За последние десять дней были заказы'

Если после IF или ELSE идут две и более инструкций, то они заключаются в блок
BEGIN...END:

1 DECLARE @lastDate DATE, @count INT, @sum MONEY

2
3 SELECT @lastDate = MAX(CreatedAt),
4 @count = SUM(ProductCount) ,
5 @sum = SUM(ProductCount * Price)
6 FROM Orders
7
8 IF @count > 0
9 BEGIN
10 PRINT 'Дата последнего заказа: ' + CONVERT(NVARCHAR, @lastDate)
11 PRINT 'Продано ' + CONVERT(NVARCHAR, @count) + ' единиц(ы)'
12 PRINT 'На общую сумму ' + CONVERT(NVARCHAR, @sum)
13 END;
14 ELSE
15 PRINT 'Заказы в базе данных отсутствуют'

Циклы
Последнее обновление: 14.08.2017

Для выполнения повторяющихся операций в T-SQL применяются циклы. В частности, в

T-SQL есть цикл WHILE. Этот цикл выполняет определенные действия, пока
некоторое условие истинно.

1 WHILE условие

2 {инструкция|BEGIN...END}

Если в блоке WHILE необходимо разместить несколько инструкций, то все они

помещаются в блок BEGIN...END.

Например, вычислим факториал числа:

1 DECLARE @number INT, @factorial INT

 114

2
SET @factorial = 1;
3
SET @number = 5;
4
5
WHILE @number > 0
6
BEGIN
7
SET @factorial = @factorial * @number
8
SET @number = @number - 1
9
END;
10
11 PRINT @factorial

То есть в данном случае пока переменная @number не будет равна 0, будет

продолжаться цикл WHILE. Так как @number равна 5, то цикл сделает пять проходов.
Каждый проход цикла называется итерацией. В каждой итерации будет
переустанавливаться значение переменных @factorial и @number.

Другой пример - рассчитаем баланс счета через несколько лет с учетом процентной
ставки:

1 USE productsdb;

2
3 CREATE TABLE #Accounts ( CreatedAt DATE, Balance MONEY)

4
5 DECLARE @rate FLOAT, @period INT, @sum MONEY, @date DATE

SET @date = GETDATE()

6
SET @rate = 0.065;
7
SET @period = 5;
8
SET @sum = 10000;
9
10
WHILE @period > 0
11
BEGIN
12
 115

13 INSERT INTO #Accounts VALUES(@date, @sum)

14 SET @period = @period - 1

15 SET @date = DATEADD(year, 1, @date)

16 SET @sum = @sum + @sum * @rate

17 END;

18
19 SELECT * FROM #Accounts

Здесь создается временная таблица #Accounts, в которую добавляется в цикле пять

строк с данными.

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

Оператор BREAK позволяет завершить цикл, а оператор CONTINUE - перейти к новой

итерации.

1
DECLARE @number INT
2 SET @number = 1
3
4 WHILE @number < 10
5 BEGIN

6 PRINT CONVERT(NVARCHAR, @number)

7 SET @number = @number + 1

8 IF @number = 7

9 BREAK;

10 IF @number = 4

CONTINUE;
11
PRINT 'Конец итерации'
12
END;
13

Когда переменная @number станет равна 4, то с помощью оператора CONTINUE

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

Когда переменная @number станет равна 7, то оператор BREAK произведет выход из

цикла, и он завершится.

Обработка ошибок
Последнее обновление: 14.08.2017


Для обработки ошибок в T-SQL применяется конструкция TRY...CATCH. Она имеет

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

1 BEGIN TRY
2 инструкции
3 END TRY
4 BEGIN CATCH
5 инструкции
6 END CATCH

Между выражениями BEGIN TRY и END TRY помещаются инструкции, которые

потенциально могут вызвать ошибку, например, какой-нибудь запрос. И если в этом
блоке TRY возникнет ошибка, то управление передается в блок CATCH, где можно
обработать ошибку.

В блоке CATCH для обаботки ошибки мы можем использовать ряд функций:

 ERROR_NUMBER(): возвращает номер ошибки

 ERROR_MESSAGE(): возвращает сообщение об ошибке
 ERROR_SEVERITY(): возвращает степень серьезности ошибки. Степень
серьезности представляет числовое значение. И если оно равно 10 и меньше,
то такая ошибка рассматривается как предупреждение и не обрабатывается
конструкцией TRY...CATCH. Если же это значение равно 20 и выше, то такая
ошибка приводит к закрытию подключения к базе данных, если она не
обрабатывается конструкцией TRY...CATCH.
 ERROR_STATE(): возвращает состояние ошибки

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

столбцов:

1 CREATE TABLE Accounts (FirstName NVARCHAR NOT NULL, Age INT NOT NULL)
2
3 BEGIN TRY
4 INSERT INTO Accounts VALUES(NULL, NULL)
 117

PRINT 'Данные успешно добавлены!'

5
END TRY
6
BEGIN CATCH
7
PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ':' +
8
ERROR_MESSAGE()
9
END CATCH

В данном случае для столбцов таблицы вставляются недопустимые данные - значения

NULL, поэтому обработка программы перейдет к блоку CATCH:

Представления и табличные объекты

Представления
Последнее обновление: 14.08.2017

Представления или Views представляют виртуальные таблицы. Но в отличии от

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

Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-

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

Для создания представления используется команда CREATE VIEW, которая имеет

следующую форму:

1 CREATE VIEW название_представления [(столбец_1, столбец_2, ....)]

2 AS выражение_SELECT

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

1 CREATE TABLE Products

2 (

3 Id INT IDENTITY PRIMARY KEY,

 118

4
ProductName NVARCHAR(30) NOT NULL,
5
Manufacturer NVARCHAR(20) NOT NULL,
6
ProductCount INT DEFAULT 0,
7
Price MONEY NOT NULL
8 );
9 CREATE TABLE Customers
10 (

11 Id INT IDENTITY PRIMARY KEY,

12 FirstName NVARCHAR(30) NOT NULL

13 );

14 CREATE TABLE Orders

15 (

Id INT IDENTITY PRIMARY KEY,

16
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
17
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
18
CreatedAt DATE NOT NULL,
19
ProductCount INT DEFAULT 1,
20
Price MONEY NOT NULL
21
);
22

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

представление:

1 CREATE VIEW OrdersProductsCustomers AS

2 SELECT [Link] AS OrderDate,

3 [Link] AS Customer,

4 [Link] As Product

5 FROM Orders INNER JOIN Products ON [Link] = [Link]

6 INNER JOIN Customers ON [Link] = [Link]

 119

То есть данное представление фактически будет возвращать сводные данные из трех

таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы
данных в SQL Server Management Studio:

Теперь используем созданное выше представление для получения данных:

1 SELECT * FROM OrdersProductsCustomers

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

должны иметь уникальные имена в рамках той же базы данных.

Представления могут иметь не более 1024 столбцов и могут обращать не более чем к
256 таблицам.

Также можно создавать представления на основе других представлений. Такие

представления еще называют вложенными (nested views). Однако уровень
вложенности не может быть больще 32-х.

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

выражения INTO или ORDER BY (за исключением тех случаев, когда также
применяется выражение TOP или OFFSET). Если же необходима сортировка данных в
представлении, то выражение ORDER BY применяется в команде SELECT, которая
извлекает данные из представления.

Также при создании представления можно определить набор его столбцов:

1 CREATE VIEW OrdersProductsCustomers2 (OrderDate, Customer,Product)

2 AS SELECT [Link],

3 [Link],

4 [Link]

5 FROM Orders INNER JOIN Products ON [Link] = [Link]

6 INNER JOIN Customers ON [Link] = [Link]

Изменение представления

Для изменения представления используется команда ALTER VIEW. Эта команда

имеет практически тот же самый синтаксис, то и CREATE VIEW:

1 ALTER VIEW название_представления [(столбец_1, столбец_2, ....)]

 120

2 AS выражение_SELECT

Например, изменим выше созданное представление OrdersProductsCustomers:

1 ALTER VIEW OrdersProductsCustomers

2 AS SELECT [Link] AS OrderDate,

3 [Link] AS Customer,

4 [Link] AS Product,

5 [Link] AS Manufacturer

6 FROM Orders INNER JOIN Products ON [Link] = [Link]

INNER JOIN Customers ON [Link] = [Link]

7
Удаление представления

Для удаления представления вызывается команда DROP VIEW:

1 DROP VIEW OrdersProductsCustomers

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

Обновляемое представление
Последнее обновление: 14.08.2017


Представления могут быть обновляемыми (updatable). В таких представлениях мы

можем изменить или удалить строки или добавить в них новые строки.

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

команда SELECT в представлении не может содержать:

 TOP
 DISTINCT
 UNION
 JOIN
 агрегатные функции типа COUNT или MAX
 GROUP BY и HAVING
 121

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

Допустим, у нас есть следующая таблица:

1 CREATE TABLE Products

2 (
3 Id INT IDENTITY PRIMARY KEY,
4 ProductName NVARCHAR(30) NOT NULL,
5 Manufacturer NVARCHAR(20) NOT NULL,
6 ProductCount INT DEFAULT 0,
7 Price MONEY NOT NULL
8 )

И создадим обновляемое представление:

1 CREATE VIEW ProductView

2 AS SELECT ProductName AS Product, Manufacturer, Price
3 FROM Products

Добавим в него данные:

1 INSERT INTO ProductView (Product, Manufacturer, Price)

2 VALUES('Nokia 8', 'HDC Global', 18000)
3
4 SELECT * FROM ProductView

Стоит отметить, что при добавлении фактически будет добавлен объект в таблицу
Products, которую использует представление ProductView. И поэтому надо учитывать,
что если в этой таблице есть какие-либо столбцы, в которые представление не
добавляет данные, но которые не допускают значение NULL или не поддерживают
значение по умолчанию, то добавление завершится с ошибкой.

Обновление строки представления:

1 UPDATE ProductView
2 SET Price= 15000 WHERE Product='Nokia 8'

Удаление строки в представлении:

1 DELETE FROM ProductView

2 WHERE Product='Nokia 8'
 122

Обновление и удаление также затрагивают ту таблицу, которую использует

представление.

Табличные переменные
Последнее обновление: 14.08.2017


Табличные переменные (table variable) позволяют сохранить содержимое целой

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

1 DECLARE @табличная_переменная TABLE

2 (столбец_1 тип_данных [атрибуты_столбца],
3 столбец_2 тип_данных [атрибуты_столбца] ....)
4 [атрибуты_таблицы]

Например:

1 DECLARE @ABrends TABLE (ProductId INT, ProductName NVARCHAR(20))

В данном случае переменная @ABrends будет содержать два столбца.

В дальнейшем мы сможем работать с этой переменной как с обычной таблицей, то

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

1 DECLARE @ABrends TABLE (ProductId INT, ProductName NVARCHAR(20))

2
3 INSERT INTO @ABrends
4 VALUES(1, 'iPhone 8'),
5 (2, 'Samsumg Galaxy S8')
6
7 SELECT * FROM @ABrends

Однако следует учитывать, что такие переменные не полностью эквивалентны

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

Временные и производные таблицы

Последнее обновление: 14.08.2017
 123


Временные таблицы

В дополнение к табличным переменным можно определять временные таблицы.

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

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

таблица создается в редакторе запросов (Query Editor) в SQL Server Management
Studio, то таблица будет существовать пока открыт редактор запросов. Таким
образом, к временной таблице можно обращаться из разных скриптов внутри
редактора запросов.

После создания все временные таблицы сохраняются в таблице tempdb, которая

имеется по умолчанию в MS SQL Server.

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

Название временной таблицы начинается со знака решетки #. Если используется

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

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

1 CREATE TABLE #ProductSummary

2 (ProdId INT IDENTITY,

3 ProdName NVARCHAR(20),

Price MONEY)
4
5
INSERT INTO #ProductSummary
6
VALUES ('Nokia 8', 18000),
7
('iPhone 8', 56000)
8
 124

9
SELECT * FROM #ProductSummary
10

И с этой таблицей можно работать в большей степени как и с обычной таблицей -

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

Подобные таблицы удобны для каких-то временных промежуточных данных.

Например, пусть у нас есть три таблицы:

1 CREATE TABLE Products

2 (

3 Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

4
Manufacturer NVARCHAR(20) NOT NULL,
5
ProductCount INT DEFAULT 0,
6
Price MONEY NOT NULL
7
);
8
CREATE TABLE Customers
9
(
10
Id INT IDENTITY PRIMARY KEY,
11 FirstName NVARCHAR(30) NOT NULL
12 );
13 CREATE TABLE Orders

14 (

15 Id INT IDENTITY PRIMARY KEY,

16 ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,

17 CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,

18 CreatedAt DATE NOT NULL,

19 ProductCount INT DEFAULT 1,

Price MONEY NOT NULL

20
);
21
 125

22

Выведем во временную таблицу промежуточные данные из таблицы Orders:

1 SELECT ProductId,

2 SUM(ProductCount) AS TotalCount,

SUM(ProductCount * Price) AS TotalSum

3
INTO #OrdersSummary
4
FROM Orders
5
GROUP BY ProductId
6
7
SELECT [Link], #[Link],
8 #[Link]

9 FROM Products

10 JOIN #OrdersSummary ON [Link] = #[Link]

Здесь вначале извлекаются данные во временную таблицу #OrdersSummary. Причем

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

Затем эта таблица может использоваться в выражениях INNER JOIN.

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

их имя начинается с двух знаков ##:

1 CREATE TABLE ##OrderDetails

2 (ProductId INT, TotalCount INT, TotalSum MONEY)

3
4 INSERT INTO ##OrderDetails

SELECT ProductId, SUM(ProductCount), SUM(ProductCount * Price)

5
FROM Orders
6
GROUP BY ProductId
7
8
 126

9 SELECT * FROM ##OrderDetails

Производные таблицы

Кроме временных таблиц MS SQL Server позволяет создавать производные таблицы,

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

1 WITH OrdersInfo AS
2 (
3 SELECT ProductId,

4 SUM(ProductCount) AS TotalCount,

5 SUM(ProductCount * Price) AS TotalSum

6 FROM Orders

7 GROUP BY ProductId

8 )

9
SELECT * FROM OrdersInfo -- здесь нормально
10
SELECT * FROM OrdersInfo -- здесь ошибка
11
SELECT * FROM OrdersInfo -- здесь ошибка
12

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

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

Хранимые процедуры
Создание и выполнение процедур
Последнее обновление: 14.08.2017


 127

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

выполнить в определенной последовательности. Например, при добавлении покупке
товара необходимо внести данные в таблицу заказов. Однако перед этим надо
проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится
проверить еще ряд дополнительных условий. То есть фактически процесс покупки
товара охватывает несколько действий, которые должны выполняться в
определенной последовательности. И в этом случае более оптимально будет
инкапсулировать все эти действия в один объект - хранимую процедуру (stored
procedure).

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

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

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

самым уменьшить вероятность преднамеренных или неосознанных нежелательных
действий в отношении этих данных.

И еще один важный аспект - производительность. Хранимые процедуры обычно

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

Для создания хранимой процедуры применяется команда CREATE

PROCEDURE или CREATE PROC.

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

кода, безопасность и производительность.

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

1 CREATE TABLE Products

2 (

3 Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

4
Manufacturer NVARCHAR(20) NOT NULL,
5
ProductCount INT DEFAULT 0,
6
Price MONEY NOT NULL
7
);
 128

Создадим хранимую процедуру для извлечения данных из этой таблицы:

1 USE productsdb;

2 GO

3 CREATE PROCEDURE ProductSummary AS

4 SELECT ProductName AS Product, Manufacturer, Price

5 FROM Products

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то

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

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко
помещается в блок BEGIN...END:

1 USE productsdb;

2 GO

3 CREATE PROCEDURE ProductSummary AS

4 BEGIN

5 SELECT ProductName AS Product, Manufacturer, Price

6 FROM Products

END;
7

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server
Management Studio в подузле Programmability -> Stored Procedures:

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

Выполнение процедуры

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE:

1 EXEC ProductSummary
 129

Удаление процедуры

Для удаления процедуры применяется команда DROP PROCEDURE:

1 DROP PROCEDURE ProductSummary

Параметры в процедурах
Последнее обновление: 14.08.2017

Процедуры могут принимать параметры. Параметры бывают входными - с их помощью

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

Например, пусть в базе данных будет следующая таблица Products:

1 USE productsdb;
2 CREATE TABLE Products

3 (

4 Id INT IDENTITY PRIMARY KEY,

5 ProductName NVARCHAR(30) NOT NULL,

6 Manufacturer NVARCHAR(20) NOT NULL,

7 ProductCount INT DEFAULT 0,

Price MONEY NOT NULL

8
);
9

Определим процедуру, которая будет добавлять данные в эту таблицу:

1 USE productsdb;

2 GO
 130

3 CREATE PROCEDURE AddProduct

4 @name NVARCHAR(20),

5 @manufacturer NVARCHAR(20),

6 @count INT,

7 @price MONEY

8 AS

9 INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)

VALUES(@name, @manufacturer, @count, @price)

10

После названия процедуры идет список входных параметров, которые определяются

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

Используем эту процедуру:

1 USE productsdb;
2
3 DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);

4 DECLARE @prodCount INT, @price MONEY

5 SET @prodName = 'Galaxy C7'

6 SET @company = 'Samsung'

7 SET @price = 22000

8 SET @prodCount = 5

9
EXEC AddProduct @prodName, @company, @prodCount, @price
10
11
SELECT * FROM Products
12

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

вызове процедуры ей через запятую передаются значения. При этом значения
передаются параметрам процедуры по позиции. Так как первым определен параметр
@name, то ему будет передаваться первое значение - значение переменной
@prodName. Второму параметру - @manufacturer передается второе значение -
 131

значение переменной @company и так далее. Главное, чтобы между передаваемыми

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

Также можно было бы передать непосредственно значения:

1 EXEC AddProduct 'Galaxy C7', 'Samsung', 5, 22000

Также значения параметрам процедуры можно передавать по имени:

1 USE productsdb;
2
3 DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);

4 SET @prodName = 'Honor 9'

5 SET @company = 'Huawei'

6
7 EXEC AddProduct @name = @prodName,

8 @manufacturer=@company,

9 @count = 3,

@price = 18000
10

При передаче параметров по имени параметру процедуры присваивается некоторое

значение.

Необязательные параметры

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

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

1 USE productsdb;

2 GO

3 CREATE PROCEDURE AddProductWithOptionalCount

@name NVARCHAR(20),
4
@manufacturer NVARCHAR(20),
5
 132

6 @price MONEY,

7 @count INT = 1

8 AS

9 INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)

10 VALUES(@name, @manufacturer, @count, @price)

При этом необязательные параметры лучше помещать в конце списка параметров

процедуры.

1 DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20), @price MONEY

2 SET @prodName = 'Redmi Note 5A'

3 SET @company = 'Xiaomi'

4 SET @price = 22000

5
6 EXEC AddProductWithOptionalCount @prodName, @company, @price

7
8 SELECT * FROM Products

И в этом случае для параметра @count в процедуру можно не передавать значение.

Выходные параметры и возвращение

результата
Последнее обновление: 14.08.2017

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

Выходные параметры определяются с помощью ключевого слова OUTPUT. Например,
определим еще одну процедуру:

1 USE productsdb;
 133

2 GO

3 CREATE PROCEDURE GetPriceStats

4 @minPrice MONEY OUTPUT,

5 @maxPrice MONEY OUTPUT

6 AS

7 SELECT @minPrice = MIN(Price), @maxPrice = MAX(Price)

FROM Products
8

При вызове процедуры для выходных параметров передаются переменные с

ключевым словом OUTPUT:

1 USE productsdb;

2 DECLARE @minPrice MONEY, @maxPrice MONEY

3
4 EXEC GetPriceStats @minPrice OUTPUT, @maxPrice OUTPUT

5
6 PRINT 'Минимальная цена ' + CONVERT(VARCHAR, @minPrice)

7 PRINT 'Максимальная цена ' + CONVERT(VARCHAR, @maxPrice)

Также можно сочетать входные и выходные параметры. Например, определим

процедуру, которая добавляет новую строку в таблицу и возвращает ее id:

1 USE productsdb;

2 GO

3
4 CREATE PROCEDURE CreateProduct

@name NVARCHAR(20),
5
@manufacturer NVARCHAR(20),
6
@count INT,
7
@price MONEY,
8
@id INT OUTPUT
9
AS
 134

10
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
11
VALUES(@name, @manufacturer, @count, @price)
12
SET @id = @@IDENTITY
13

С помощью глобальной переменной @@IDENTITY можно получить идентификатор

добавленной записи.

При вызове этой процедуры ей также по позиции передаются все входные и

выходные параметры:

1 USE productsdb;

2
3 DECLARE @id INT

4
5 EXEC CreateProduct 'LG V30', 'LG', 3, 28000, @id OUTPUT

6
7 PRINT @id

Возвращение значения

Кроме передачи результата выполнения через выходные параметры хранимая

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

Например, возвратим среднюю цену на товары:

1 USE productsdb;

2 GO

3 CREATE PROCEDURE GetAvgPrice AS

4 DECLARE @avgPrice MONEY

5 SELECT @avgPrice = AVG(Price)

6 FROM Products

RETURN @avgPrice;
7
 135

После оператора RETURN указывается возвращаемое значение. В данном случае это

значение переменной @avgPrice.

Вызовем данную процедуру:

1 USE productsdb;

2
3 DECLARE @result MONEY

4
5 EXEC @result = GetAvgPrice

6 PRINT @result

Для получения результата процедуры ее значение сохраняется в переменную (в

данном случае в переменную @result):

Триггеры
Определение триггеров
Последнее обновление: 09.11.2017

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

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

Формальное определение триггера:

1 CREATE TRIGGER имя_триггера

2 ON {имя_таблицы | имя_представления}

3 {AFTER | INSTEAD OF} [INSERT | UPDATE | DELETE]

4 AS выражения_sql
 136

Для создания триггера применяется выражение CREATE TRIGGER, после которого

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

Каждый триггер ассоциируется с определенной таблицей или представлением, имя

которых указывается после слова ON.

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

 AFTER: выполняется после выполнения действия. Определяется только для

таблиц.

 INSTEAD OF: выполняется вместо действия (то есть по сути действие -

добавление, изменение или удаление - вообще не выполняется). Определяется
для таблиц и представлений

После типа триггера идет указание операции, для которой определяется

триггер: INSERT, UPDATE или DELETE.

Для триггера AFTER можно применять сразу для нескольких действий, например,
UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера
INSTEAD OF можно определить только одно действие.

И затем после слова AS идет набор выражений SQL, которые собственно и составляют
тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим

определением:

1 CREATE DATABASE productdb;

2 GO

3
4 USE productdb;

CREATE TABLE Products

5
(
6
Id INT IDENTITY PRIMARY KEY,
7
ProductName NVARCHAR(30) NOT NULL,
8
Manufacturer NVARCHAR(20) NOT NULL,
9
ProductCount INT DEFAULT 0,
10
Price MONEY NOT NULL
11
);
 137

12

Определим триггер, который будет срабатывать при добавлении и обновлении

данных:

1 USE productdb;
2 GO

3 CREATE TRIGGER Products_INSERT_UPDATE

4 ON Products

5 AFTER INSERT, UPDATE

6 AS

7 UPDATE Products

SET Price = Price + Price * 0.38

8
WHERE Id = (SELECT Id FROM inserted)
9

Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко

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

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE
над таблицей Products. Сам триггер будет изменять цену товара, а для получения
того товара, который был добавлен или изменен, находим этот товар по Id. Но какое
значение должен иметь Id такой товар? Дело в том, что при добавлении или
изменении данные сохраняются в промежуточную таблицу inserted. Она создается
автоматически. И из нее мы можем получить данные о добавленных/измененных
товарах.

И после добавления товара в таблицу Products в реальности товар будет иметь

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

Удаление триггера

Для удаления триггера необходимо применить команду DROP TRIGGER:

1 DROP TRIGGER Products_INSERT_UPDATE

 138

Отключение триггера

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не

хотим. В этом случае его можно временно отключить с помощью команды DISABLE
TRIGGER:

1 DISABLE TRIGGER Products_INSERT_UPDATE ON Products

А когда триггер понадобится, его можно включить с помощью команды ENABLE

TRIGGER:

1 ENABLE TRIGGER Products_INSERT_UPDATE ON Products

Триггеры для операций INSERT, UPDATE,

DELETE
Последнее обновление: 09.11.2017

Для рассмотрения операций с триггерами определим следующую базу данных

productsdb:

1 CREATE DATABASE productsdb;

2 GO

3 USE productsdb;

CREATE TABLE Products

4
(
5
Id INT IDENTITY PRIMARY KEY,
6
ProductName NVARCHAR(30) NOT NULL,
7
Manufacturer NVARCHAR(20) NOT NULL,
8
ProductCount INT DEFAULT 0,
9
Price MONEY NOT NULL
10
);
 139

11
CREATE TABLE History
12
(
13
Id INT IDENTITY PRIMARY KEY,
14
ProductId INT NOT NULL,
15
Operation NVARCHAR(200) NOT NULL,
16
CreateAt DATETIME NOT NULL DEFAULT GETDATE(),
17 );
18

Здесь определены две таблиц: Products - для хранения товаров и History - для
хранения истории операций с товарами.

Добавление

При добавлении данных (при выполнении команды INSERT) в триггере мы можем

получить добавленные данные из виртуальной таблицы INSERTED.

Определим триггер, который будет срабатывать после добавления:

1 USE productsdb
2 GO

3 CREATE TRIGGER Products_INSERT

4 ON Products

5 AFTER INSERT

6 AS

7 INSERT INTO History (ProductId, Operation)

SELECT Id, 'Добавлен товар ' + ProductName + ' фирма ' + Manufacturer
8
FROM INSERTED
9

Этот триггер будет добавлять в таблицу History данные о добавлении товара, которые
берутся из виртуальной таблицы INSERTED.

Выполним добавление данных в Products и получим данные из таблицы History:

1 USE productsdb;

2 INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)

 140

3 VALUES('iPhone X', 'Apple', 2, 79900)

4
5 SELECT * FROM History

Удаление данных

При удалении все удаленные данные помещаются в виртуальную таблицу DELETED:

1 USE productsdb
2 GO

3 CREATE TRIGGER Products_DELETE

4 ON Products

5 AFTER DELETE

6 AS

7 INSERT INTO History (ProductId, Operation)

SELECT Id, 'Удален товар ' + ProductName + ' фирма ' + Manufacturer
8
FROM DELETED
9

Здесь, как и в случае с предыдущим триггером, помещаем информацию об удаленных

товарах в таблицу History.

Выполним команду на удаление:

1 USE productsdb;

2 DELETE FROM Products

3 WHERE Id=2

4
5 SELECT * FROM History

Изменение данных

Триггер обновления данных срабатывает при выполнении операции UPDATE. И в

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

Создадим триггер обновления:

1 USE productsdb
2 GO

3 CREATE TRIGGER Products_UPDATE

4 ON Products

5 AFTER UPDATE

6 AS

7 INSERT INTO History (ProductId, Operation)

SELECT Id, 'Обновлен товар ' + ProductName + ' фирма ' + Manufacturer
8
FROM INSERTED
9

И при обновлении данных сработает данный триггер:

Триггер INSTEAD OF
Последнее обновление: 09.11.2017


Триггер INSTEAD OF срабатывает вместо операции с данными. Он определяется в

принципе также, как триггер AFTER, за тем исключением, что он может определяться
только для одной операции - INSERT, DELETE или UPDATE. И также он может
применяться как для таблиц, так и для представлений (триггер AFTER применяется
только для таблиц).

Например, создадим следующие базу данных и таблицу:

1 CREATE DATABASE prods;

2 GO
3 USE prods;
4 CREATE TABLE Products
5 (
6 Id INT IDENTITY PRIMARY KEY,
7 ProductName NVARCHAR(30) NOT NULL,
8 Manufacturer NVARCHAR(20) NOT NULL,
 142

9 Price MONEY NOT NULL,

10 IsDeleted BIT NULL
11 );

Здесь таблица содержит столбец IsDeleted, который указывает, удалена ли запись. То

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

Определим триггер для удаления записи:

1 USE prods
2 GO
3 CREATE TRIGGER products_delete
4 ON Products
5 INSTEAD OF DELETE
6 AS
7 UPDATE Products
8 SET IsDeleted = 1
9 WHERE ID =(SELECT Id FROM deleted)

Добавим некоторые данные в таблицу и выполним удаление из нее:

1 USE prods;
2
3 INSERT INTO Products(ProductName, Manufacturer, Price)
4 VALUES ('iPhone X', 'Apple', 79000),
5 ('Pixel 2', 'Google', 60000);
6
7 DELETE FROM Products
8 WHERE ProductName='Pixel 2';
9
10 SELECT * FROM Products;

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