НОВЫЙ КУРС БД 2013

Рисунок 9. Контекстное меню таблиц.

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

41

В окне для создания таблиц мы вводим имя столбца в поле Column name, указываем тип данных, длину и возможность принимать Null-значения. Кроме того, внизу есть несколько настроек поля, это описание (description), возможность задать счетчик (identity), формулу и прочее. Как только все поля таблицы забиты, можно ее сохранять, это можно сделать двумя способами – нажать на кнопку закрытия окна и тогда SQL автоматически выдаст запрос на сохранение или нажать на кнопку с изображением дискеты, что вызовет диалог сохранения. Пример этого приведен на рисунке 11.

Рисунок 11. Диалог сохранения таблицы.

Таким образом создается вся структура базы данных. При этом мы сразу можем задать первичный ключ. Выделив нужный столбец и нажав на пиктограмму ключа. При этом рядом с именем столбца появится изображение ключа, а если нам необходимо создать составной ключ, то зажав клавишу Control (Ctrl) мы можем выделить несколько столбцов и нажать на клавишу с изображением ключа. Этот процесс показан на рисунке 12.

Рисунок 12. Создание ключей в таблице.

42

В данном случае мы создали составной ключ id, name, surname, patronymic. Здесь же в верхней части окна есть и другие возможности, - мы можем создавать и внешние ключи и прочие операции. Однако есть еще одна возможность создавать ключи, в том числе и внешние, на мой взгляд, более легкая. Для этого нужно выбрать диаграммы в объектах базы данных и через правую кнопку мыши вызвать диалог создания диаграмм, нажав соответствующую клавишу в меню. Это действие представлено на рисунке 13.

Рисунок 13. Создание диаграмм в базе данных.

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

Рисунок 14. Выбор таблиц для диаграммы

43

После этого нажать кнопку «далее» и затем кнопку «готово». У нас появятся наши созданные таблицы. Скриншот этой операции показан на рисунке 15.

Рисунок 15. Диаграмма.

Далее мы можем управлять ключами абсолютно также как в режиме таблиц. Давайте выделим с зажатым Control (Ctrl) все поля составного ключа таблицы Students и нажмем на пиктограмму ключа. Таким способом мы удалили первичный ключ в таблице. Теперь давайте выделим столбец Id в таблице Students и нажмем на пиктограмму ключа, задав тем самым новый ключ. Также поступим и с таблицей Marks. После проделанных действий таблицы должны выглядеть, так как показано на рисунке 16.

Рисунок 16. Диаграмма после изменений.

Таким образом, мы определили первичные ключи, а теперь определим внешние ключи, для этого выберем, необходимы столбец для связи (первичный ключ) и перетащим на столбец внешнего ключа. Итак, выбираем столбец ID, зажимаем левую кнопку мыши и тащим в сторону таблицы Marks на столбец student. При этом курсор изменится, как показано на рисунке 17.

Рисунок 17. роцесс создания внешнего ключа.

Как только мы оказались над требуемой колонкой, отпускаем клавишу мыши и тогда мы увидим окно, представлено на рисунке 18.

44

Рисунок 18. роцесс создания внешнего ключа.

Здесь показывается, какое соответствие будет проверяться,- первичный ключ из таблицы Students будет соответствовать внешнему ключу столбца student таблицы Marks. Если вы неверно выбрали столбец, его легко можно поменять, указав нужный из раскрывающегося списка. После нажатия кнопки OK, связь будет успешно создана, и вы увидите окно, представлено на рисунке 19.

Рисунок 19. Созданная связь.

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

3.4Соединения и объединения.

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

(JOIN).

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

45

Всего существуют 4 типа соединений:

INNER JOIN

OUTER JOIN

FULL JOIN

CROSS JOIN

Общий вид строки соединения таблиц приведен ниже:

SELECT <список столбцов>

FROM <первая таблица> [тип соединения] <вторая таблица> ON <столбец, по которому производится соединение>

По сути, все виды соединений представляют собой операции над множествами:

INNER JOIN	OUTER JOIN
(Пересечение множеств)	(Разность + Пересечение)

	U
FULL JOIN	CROSS JOIN
(Объединение множеств)	(Произведение множеств)
	(Декартово произведение)
	Пусть даны два множества X и Y. Прямое
	произведение множества X и множества
	Y есть такое множество , элементами
	которого являются упорядоченные пары (x,y)
	для всевозможных	и	.

3.4.1 INNER JOIN

Любое соединение является проверкой некого условия (чаще всего равенства). Как правило, на равенство проверяют первичный и внешний ключи связанных таблиц. Таким образом, даже не используя специальный синтаксис соединений (INNER JOIN, FULL JOIN и пр.) можно произвести операцию соединения. Предположим, что у нас есть 2 таблицы: Products и Categories, связанных по ключу CategoryID. Показанный ниже запрос, приводит к созданию внутреннего соединения (INNER JOIN) и отображению списка продуктов с указанием, к какой категории принадлежит данный продукт:

SELECT ProductName, CategoryName FROM Products, Categories

WHERE Products.CategoryID = Categories.CategoryID

В общем случае синтаксис для связывания таблиц имеет вид:

SELECT column-list

46

FROM table1, table2

WHERE table1.column1=table2.column2

Внутреннее соединение таблиц (inner join) — это соединение, результатом которого

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

Общий вид соединения INNER JOIN:

SELECT column-list FROM table1

INNER JOIN table2 ON table1.column1 = table2.column1 INNER JOIN table3 ON table2.column2 = table3.column1

...

Таким образом, для создания внутреннего соединения можно использовать оба варианта: как с применением INNER JOIN так и указанием условия соединения в предикате WHERE.

(Примечание: хотя есть две возможности создавать внутреннее соединение, однако на практике лучше использовать специальный синтаксис INNER JOIN … ON, поскольку по результатам тестов он работает быстрее, хотя и не всегда.)

3.4.2 OUTER JOIN (LEFT и RIGTH)

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

Примеры синтаксиса приведены ниже:

Левое соединение

SELECT ProductName, CategoryName FROM Products

LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

равое соединение

SELECT ProductName, CategoryName FROM Products

RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

3.4.3 FULL JOIN

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

SELECT ProductName, CategoryName FROM Products

FULL OUTER JOIN Categories

ON Products.CategoryID = Categories.CategoryID

Несвязанные поля также заполняются NULL-значениями.

3.4.4 CROSS JOIN

Для получения всех комбинаций строк из обеих таблиц (декартова произведения) можно использовать ключевое слово CROSS JOIN без указания связываемых полей:

SELECT ProductName, CategoryName FROM Products

CROSS JOIN Categories

47

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

3.4.5 UNION

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

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

Требования, предъявляемые к запросам, участвующие в UNION:

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

2.Заголовки для столбцов результирующего набора берутся у первого запроса в выборке

3.Типы данных в столбцах должны быть совместимы, т.е. не обязательно, чтобы они имели один тип данных (хотя очень желательно), необходимо чтобы они были приводимы в общих тип данных для всей выборки UNION. Т.е. если один запрос имеет столбец с типом char, а другой запрос имеет столбец на этом же месте varchar, то можно говорить о совместимости, однако если тип данных одного varchar, а другой float, будет ошибка объединения.

4.В каждом из запросов, объединенных операцией UNION, выборка происходит с ключевым словом DISTINCT, а не ALL как принято по умолчанию. Т.е. выбираются только уникальные строки из каждого запроса. Для того чтобы явно объединять все строки нужно писать UNION ALL.

Пример запроса с объединением приведен ниже:

Select name from Employees union

Select surname from Employees

В результате выполнения запроса мы получим имена и фамилии в одном «плоском списке».

3.5 Групповые операции и агрегирующие функции

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

GROUP BY {column1} [, …]

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

Всего существует 5 основных операций агрегирования:

1.	COUNT (подсчитывает количество строк в группе)
2.	SUM	(подсчитывает сумму значений столбца на котором задана функция)
3.	AVG	(подсчитывает среднее значение столбца на котором задана функция)
4.	MIN	(подсчитывает минимальное значение столбца в каждой группе)
5.	MAX	(подсчитывает максимальное значение столбца в каждой группе)

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

48

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

SELECT Customers.CustomerID, COUNT (Orders.OrderID)

FROM Customers INNER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID

Важно отметить тот факт, что при внутреннем соединении отсутствуют NULL-значения и подсчет количество значений по полю Orders.OrderID происходит без проблем, однако если использовать внешние соединения может так случиться, что один из внешних ключей примет значение NULL. Так вот при подсчете количества значений функцией COUNT (как собственно и другими агрегирующими функциями) NULL-значения игнорируются. На практике это означает, что строка может быть заполнена на 9 столбцов из 10, а столбец по которому происходит подсчет имеет NULL-значение и функция COUNT проигнорирует такую строку как будто ее нет в группе!

Выхода из этой ситуации есть 2:

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

2.Использовать конструкцию COUNT(*) она подсчитывает общее количество строк при этом NULL значения также учитываются

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

Предложение HAVING имеет назначение, сходное с предложением WHERE, но используется с агрегатными данными. Например:

SELECT Customers.CustomerID,COUNT (Orders.OrderID)

FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID

HAVING COUNT(Orders.OrderID) >= 10

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

3.6Дополнительные сведения о запросах

3.6.1Ключевые слова ALL, DISTINCT и TOP

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

SELECT DISTINCT Country FROM Customers

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

Ключевое слово TOP используется для возврата первых n строк или первых n процентов таблицы. Например, запрос:

SELECT TOP 10 * FROM PRODUCTS ORDER BY ProductName

возвращает первые 10 продуктов из таблицы, тогда как запрос:

SELECT TOP 25 PERCENT * FROM PRODUCTS ORDER BY ProductName

вернет первую четверть записей таблицы.

49

3.6.2 Спецификация вычисляемого столбца

До этого момента в качестве объекта выбора в операторах манипуляции данными, такими как SELECT, INSERT, UPDATE использовались имена столбцов. Например:

SELCT name from MyTable

Однако возможности TSQL весьма обширны, и он позволяет прямо в запросах использовать математические операции (и даже функции, процедуры и логические структуры, типа IF и CASE о чем будет сказано ниже). Таким образом, мы можем прямо в запросах выполнять различные математические вычисления, например, вычислим НДФЛ (13%) от зарплаты сотрудника и назначим ему премию, если зарплата < 15000 месяцев в размере 25% от оклада и 10% от оклада, если зарплата > 15000, пусть данные о денежных операциях сотрудника хранятся в таблице EmployeeOperation, а зарплата храниться в таблице EmployeeInfo:

EmployeeInfo

EmployeeOperation

PIT - personal income tax – НДФЛ – налог на доход физических лиц

INSERT INTO EmployeeOperation (PIT, BONUS)

SELECT salary * 0.13, CASE WHEN salary < 15000 THEN salary * 0.25 ELSE salary * 0.1 END FROM EmployeeInfo

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

Логический оператор CASE WHEN … END будет рассмотрен далее.

3.6.3 Вложенные запросы.

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

SELECT <столбцы> FROM <таблицы>

WHERE <условие, налагаемое на столбец таблицы> ( SELECT <столбцы> FROM <таблицы>

WHERE <условие, налагаемое на столбец таблицы> )

Вложенные подзапросы делятся на два вида:

Подзапросы, возвращающие одно значение

Подзапросы, возвращающие список значений

Подзапросы возвращающие одно значение используют конкретную математическую операцию >, <, = или <> (не равно). А также специальные операторы и функции SQL, такие как LIKE, BEETWEEN и т.д. т.е. все те операторы, которые работают с одним значением. Рассмотрим пример подзапроса, возвращающий одно значение. Пусть есть таблица Mans (люди), в которой охраняться следующие данные:

50