Sql основное. Описание учебной базы данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Логическая обработка запросов

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

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

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

На следующем рисунке представлена схема логической обработки запроса в SQL Server 2012:

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

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

1. Вычисление выражений

   Удаление дубликатов

OFFSET-FETCH/TOP

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

Предложения, поддерживающие оконные функции

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

Я попытаюсь продемонстрировать эту неоднозначность на примере. Сначала выполните следующий код, чтобы создать таблицу T1 и наполнить ее данными:

SET NOCOUNT ON; USE TSQL2012; IF OBJECT_ID("dbo.T1", "U") IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 (col1 VARCHAR(10) NOT NULL CONSTRAINT PK_T1 PRIMARY KEY); INSERT INTO dbo.T1(col1) VALUES("A"),("B"),("C"),("D"),("E"),("F"); GO

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

Прежде чем говорить, что это очевидно, что это должны быть значения C, D и Е, вспомните о принципе «все сразу» в SQL. Этот принцип подразумевает, что с точки зрения концепции все выражения одного логического этапа выполняются одновременно. Это значит, что порядок следования выражений не должен влиять на результат. Если так, то следующий запрос должен быть семантически эквивалентен такому:

Сможете ли вы на этот раз определить, какое выражение правильное? Это C, D и Е или только C?

Это пример неоднозначности, о которой я говорил. Разрешение использовать оконные функции только в предложениях SELECT и ORDER BY позволяет избавиться от этой неоднозначности.

При анализе блок-схемы на рисунке выше вы могли заметить, что на этапе SELECT оконные функции поддерживает шаг 5-1 (Вычисление выражений) и он выполняет перед шагом 5-2 (Удаление дубликатов). Если вы спросите, почему так важно знать такие детали, я продемонстрирую, зачем это нужно.

Вот вопрос, возвращающий атрибуты empid и country всех сотрудников из таблицы сотрудников Employees:

SELECT empid, country FROM HR.Employees;

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

SELECT DISTINCT country, ROW_NUMBER() OVER(ORDER BY country) AS rownum FROM HR.Employees;

Некоторые будут ожидать такой результат:

Но на самом деле вы получите это:

А теперь вспомните, что в этом запросе функция ROW_NUMBER вычисляется на шаге 5-1, на котором вычисляются выражения списка SELECT - до удаления дубликатов на шаге 5-2. Функция ROW_NUMBER назначает девять уникальных номеров строк, содержащих информацию о сотрудниках, поэтому предложению DISTINCT нечего удалять.

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

WITH EmpCountries AS (SELECT DISTINCT country FROM HR.Employees) SELECT country, ROW_NUMBER() OVER(ORDER BY country) AS rownum FROM EmpCountries;

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

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

Сначала вычисляется предложение FROM, после чего выполняется соединение. Затем фильтр оставляет только строки, относящиеся к 2007 году. После этого оставшиеся строки группируются по идентификатору сотрудника. Только после этого вычисляются выражения в списке SELECT, в числе которых функция RANK, которая вычисляется с использование упорядочения по убыванию общего количества. Если бы в списке SELECT были другие оконные функции, в них в качестве исходной точки использовался этот же набор результатов.

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

В обход ограничений

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

Вот пример, демонстрирующий, как можно фильтровать на основе результатов оконной функции с использованием CTE:

В инструкциях, изменяющих данные, оконные функции полностью запрещены, потому что в этих инструкциях не поддерживаются предложения SELECT и ORDER BY. Но есть случаи, когда оконные функции нужны в изменяющих данные инструкциях. Табличные выражения позволяют решить и эту проблему, потому что T-SQL позволяет менять данные через табличные выражения. Продемонстрирую это поведение на примере UPDATE. Сначала выполните следующий код, чтобы создать таблицу T1 со столбцами col1 и col2 и наполнить ее данными:

SET NOCOUNT ON; USE TSQL2012; IF OBJECT_ID("dbo.T1", "U") IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 (col1 INT NULL, col2 VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(col2) VALUES("C"),("A"),("B"),("A"),("C"),("B"); GO

Значения столбца col2 определены явно, a col1 был заполнен значениями NULL.

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

UPDATE dbo.T1 SET col1 = ROW_NUMBER() OVER(ORDER BY col2);

Но, как вы помните, в такой инструкции это запрещено. Обходной способ заключается в создании запроса по отношению к T1, который возвращает col1, и выражения, основанного на функции ROW_NUMBER (назовем ее rownum); определите табличное выражение, основанное на этом запросе, и, наконец, примените инструкцию UPDATE к CTE для присвоения значения rownum столбцу col1:

WITH C AS (SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col2) AS rownum FROM dbo.T1) UPDATE C SET col1 = rownum; SELECT col1, col2 FROM dbo.T1;

Получите данные из T1 - вы увидите, что все строки получили уникальное значение в столбце col1:

Возможность создания дополнительных фильтров

Я показал, как в T-SQL можно прибегнуть к обходному решению и косвенным образом использовать оконные функции в элементах, которые не поддерживают их напрямую. Это обходное решение основано на применении табличного выражения в форме CTE или производной таблицы. Приятно иметь дополнительный вариант, но в табличном выражении используется дополнительный уровень запроса и все немного усложняется. Приведенные мной примеры просты, но как насчет длинных и сложных запросов. Возможно ли более простое решение без этого дополнительного уровня?

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

Не работает в SQL Server 2012 SELECT orderid, orderdate, val FROM Sales.OrderValues QUALIFY RANK() OVER(ORDER BY val DESC)

Более того, можно ссылаться на псевдонимы столбцов, определенных в списке SELECT, так:

Не работает в SQL Server 2012 SELECT orderid, orderdate, val, RANK() OVER(ORDER BY val DESC) AS rnk FROM Sales.OrderValues QUALIFY rnk

Предложения QUALIFY нет в стандартном SQL - оно поддерживается только в продуктах Teradata. Но оно кажется очень интересным решением, и было бы неплохо, если бы и стандарт, и в SQL Server удовлетворили такую потребность.

Повторное использование определений окон

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

SELECT empid, ordermonth, qty, SUM(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_sum_qty, AVG(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_avg_qty, MIN(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_min_qty, MAX(qty) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_max_qty FROM Sales.EmpOrders;

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

SQL Server пока не поддерживает предложение WINDOW . В стандартном SQL можно сократить предыдущий запрос с использованием предложения WINDOW так:

Не работает в SQL Server 2012 SELECT empid, ordermonth, qty, SUM(qty) OVER W1 AS run_sum_qty, AVG(qty) OVER W1 AS run_avg_qty, MIN(qty) OVER W1 AS run_min_qty, MAX(qty) OVER W1 AS run_max_qty FROM Sales.EmpOrders WINDOW W1 AS (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

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

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

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

Идентификаторы языка SQL предназначены для обозначения объектов в базе данных и являются именами таблиц , представлений, столбцов и других объектов базы данных . Символы, которые могут использоваться в создаваемых пользователем идентификаторах языка SQL , должны быть определены как набор символов. Стандарт SQL задает набор символов, который используется по умолчанию, – он включает строчные и прописные буквы латинского алфавита (A-Z , a-z ), цифры (0-9 ) и символ подчеркивания (_ ). На формат идентификатора накладываются следующие ограничения:

• идентификатор может иметь длину до 128 символов;
• идентификатор должен начинаться с буквы;
• идентификатор не может содержать пробелы.

<идентификатор>::=<буква> {<буква>|<цифра>}[,...n]

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

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

Таблица 1.1.

Символ	Обозначение
::=	Равно по определению
|	Необходимость выбора одного из нескольких приведенных значений
<…>	Описанная с помощью метаязыка структура языка
{…}	Обязательный выбор некоторой конструкции из списка
[…]	Необязательный выбор некоторой конструкции из списка
[,…n]	Необязательная возможность повторения конструкции от нуля до нескольких раз

Описание учебной базы данных

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

Исходя из анализа предметной области , можно выделить два типа сущностей – ТОВАР и КЛИЕНТ , которые связаны между собой отношением "многие–ко–многим", т.к. каждый покупатель может купить много наименований товара, а каждый товар может быть куплен многими покупателями. Однако реляционная модель данных требует заменить отношение "многие–ко-многим" на несколько отношений "один–ко-многим". Добавим еще один тип сущностей, отображающий процесс продажи товаров, – СДЕЛКА .

Установим связи между объектами. Один покупатель может неоднократно покупать товары, поэтому между объектами КЛИЕНТ и СДЕЛКА имеется связь "один–ко–многим". Каждое наименование товара может неоднократно участвовать в сделках, в результате между объектами ТОВАР и СДЕЛКА имеется связь " один-ко-многим ".

Определим атрибуты и свяжем их с сущностями и связями . К объекту ТОВАР относятся такие характеристики, как название, тип, цена, сорт. К объекту КЛИЕНТ – имя, отчество, фамилия, фирма , город, телефон. Тип сущности СДЕЛКА может быть охарактеризован такими признаками, как дата и количество проданного товара.

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

Для таблицы ТОВАР название не может служить первичным ключом , т.к. товары разных типов могут иметь одинаковые названия, поэтому введем первичный ключ КодТовара , под которым можно понимать, например, артикул товара . Точно так же ни Имя , ни Фирма , ни Город не могут служить первичным ключом в таблице КЛИЕНТ . Введем первичный ключ КодКлиента , под которым можно понимать номер паспорта, идентификационный номер налогоплательщика или любой другой атрибут , однозначно определяющий каждого клиента. Для таблицы СДЕЛКА первичным ключом является поле КодСделки , т.к. оно однозначно определяет дату, покупателя и другие элементы данных. В качестве первичного ключа можно было бы выбрать не одно поле , а некоторую совокупность полей, но для иллюстрации конструкций языка ограничимся простыми первичными ключами .