Курс. NET
Орг. вопросы • Включить камеру • Отметить присутствующих • След. Занятие – 30 число (вторник)
Хорошая книга • Пол Нильсен • SQL Server 2005. Библия пользователя
Хорошая книга • Кайл Бэнкер • Mongo. DB в действии
Основы SQL
SQL • Абревиатура SQL обозначает Структурированный Язык Запросов (Structed Query Language). Стандартный язык позволит пользователям, знающим один набор команд, применять их, чтобы создавать, отыскивать, изменять и передавать информацию независимо от используемого сервера баз данных и его местоположения. • Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов), а также ISO (Международной организацией по стандартизации). • Для обращения к базе данных используются запросы, написанные на языке SQL. Запросом называется команда, которая передаётся серверу базы данных, и которая сообщает ему какую информацию из таблиц необходимо поместить в память.
Понятие таблицы SQL • Предполагается, что данные хранятся в так называемых плоских таблицах. Фактически, на физическом уровне, данные могут быть организованы и иначе, но пользователи и приложения, обращающиеся к данным, должны работать с данными так, как если бы они размещались в таких таблицах. В упрощенном виде плоская таблица − это таблица, каждая ячейка которой может быть однозначно идентифицирована указанием строки и столбца таблицы. Кроме того, в одном столбце все ячейки должны содержать данные одного простого типа.
Команды определения данных • Эти команды относятся к области SQL, называемой DML (Data Manipulation Language или Язык Манипулирования Данными). Тем не менее, существует специальная область SQL, называемая DDL (Data Defenition Language или Язык Определения Данных), специально работает над созданием объектов данных.
Оператор Create. Table create table Client. Info ( First. Name varchar(20), Last. Name varchar(20), Address varchar(20), Phone varchar(15) );
Нормальные формы таблиц в SQL • Окончательная цель нормализации сводится к получению такого проекта БД, в котором каждый факт появляется лишь в одном месте, т. е. исключена избыточность информации. Избыточность информации устраняется не только с целью экономии памяти, сколько для исключения возможной противоречивости хранимых данных и упрощения управления ими. Обычно различают следующие проблемы, возникающие при использовании ненормализованных таблиц: избыточность данных, аномалия обновления, аномалия удаления.
Избыточность данных • избыточность данных проявляется в том, что в нескольких записях таблицы БД повторяется одна и та же информация. Например, один человек может работать на двух и более должностях. Но если информация о личных данных сотрудника и его должности совмещены, то для сотрудника, занимающего более одной должности, его личные данные будут дублироваться;
Аномалия обновления • аномалия обновления тесно связана с избыточностью данных. предположим, что у сотрудника, работающего на нескольких должностях, изменился адрес. Чтобы информация, содержащаяся в таблице, была корректной, необходимо будет внести изменения в несколько записей. В противном случае возникает несоответствие информации, которое и называется аномалией обновления;
Аномалия удаления • аномалия удаления возникает при удалении записей из ненормализованной таблицы. Пусть в организации некоторые должности аннулируются. При этом следует удалить соответствующие записи в рассматриваемой таблице. Однако удаление приводит к потере информации о сотруднике, занимавшем эту должность. Такая потеря и называется аномалией удаления.
Первая нормальная форма • Первая нормальная форма подразумевает, что данные объединены в сущности и удовлетворяют следующим условиям – Каждая единица представлена в скалярном атрибуте – Все данные должны быть представлены уникальными атрибутами – Все данные содержатся в уникальных элементах
Вторая нормальная форма • Вторая нормальная форма гарантирует, что каждый атрибут на самом деле является атрибутом сущности. Это вопрос зависимости. Каждый атрибут должен иметь свой первичный ключ, в противном случае он не должен принадлежать БД
Третья нормальная форма • Третья нормальная форма проверяет наличие транзитивных зависимостей. Транзитивная зависимость аналогична частой в том, что обе относятся к атрибутам, которые не в полной мере зависят от первичного ключа. Зависимость называется транзитивной, когда один атрибут зависит от другого, который в свою очередь зависит от первичного ключа.
Нормальная форма Бойса-Кодда (BNCF) • Нормальная форма Бойса-Кодда занимает промежуточное положение между третьей и четвертой нормальными формами и решает проблемы сущностей, которые могут иметь два набора первичных ключей. Эта форма предполагает, что в данном случае сущность должна быть разбита на две – по одной для каждого первичного ключа
Четвертая нормальная форма • Четвертая нормальная форма решает проблемы, создаваемые сложными первичными ключами. Если два независимых атрибута сведены для формирования ключа с третьим и при этом оба атрибута однозначно не определяют элемент без третьего, то модель нарушает четвертую нормальную форму
Пятая нормальная форма • Пятая нормальная форма реализует метод проектирования сложных отношений, включающий в себя множество (три и более) сущностей. Если тройственное отношение правильно построено, оно не нарушает пятую нормальную форму. Строгость таких отношений может быть однозначной, так и многозначной. Пятая нормальная форма требует, что бы каждая сущность участвующая в разделённом тройственном отношении , оставалась целостной, без какой-либо потери данных.
А теперь более простыми словами
Первая нормальная форма • Первая нормальная форма (1 НФ) требует, чтобы каждое поле таблицы было неделимым и не содержало повторяющихся групп. Неделимость поля означает, что содержащиеся в нем значения не должны делиться на более мелкие части. • Повторяющимися являются поля, содержащие одинаковые по смыслу значения.
Действия по нормализации относительно 1 й НФ • Итак, чтобы привести таблицу к 1 НФ нужно выполнить следующие шаги: • Все значения полей необходимо привести к атомарному виду, выделив части сложных значений в отдельные поля. • Необходимо свести поля, одинаковые по смыслу (повторяющиеся группы) в одно поле.
Вторая нормальная форма • Следующий шаг в процессе нормализации состоит в удалении всех неключевых атрибутов, которые зависят только от части первичного ключа. Такие атрибуты называются частично зависимыми. Те поля, которые зависят только от части первичного ключа, должны быть выделены в отдельные таблицы.
Переход от 1 НФ к 2 НФ • Определить, на какие части можно разбить первичный ключ так, чтобы некоторые из неключевых полей зависели от одной из этих частей (причем эти части могут содержать несколько атрибутов). • Создать новую таблицу для каждой такой части ключа и группы зависящих от нее полей и переместить их в эту таблицу. Часть бывшего первичного ключа при этом станет первичным ключом новой таблицы. • Удалить из исходной таблицы поля, перемещенные в другие таблицы, кроме тех из них, которые станут вторичными ключами.
Пример 2 НФ
Третья НФ: устранение транзитивных зависимостей • Третья нормальная форма (3 НФ) требует, чтобы в таблице не имелось транзитивных зависимостей между неключевыми полями, т. е. чтобы значение любого поля, не входящего в первичный ключ, не зависело от другого поля, также не входящего в первичный ключ.
Пример неверной БД и того, какой она должна стать
Переход к 3 НФ от 2 НФ • Определить поля (или группы полей), от которых зависят другие поля. • Создать новую таблицу для каждого такого поля (или группы полей) и группы зависящих от него полей и переместить их в эту таблицу. Поле (или группа полей), от которого зависят все остальные перемещенные поля, станет при этом первичным ключом новой таблицы. • Удалить перемещенные поля из исходной таблицы, оставив лишь те из них, которые станут вторичными ключами.
А теперь совсем простыми словами • В реальности все БД не нормализованные, потому что практика • При попадании на высокие нагрузки БД обязана быть не нормированной, потому что порядок поступления данных не фиксирован • Суть – уйдите от аномалии обновления данных и избыточности данных. А удаление часто реализуется флагом удаленности. И программируете как вам удобно.
Ссылочная целостность • • Обычно реляционная БД состоит из набора взаимосвязанных таблиц. Организация связи (отношений) между таблицами называется связыванием таблиц. Для поддержки этих связей необходимо иметь возможность уникальной идентификации каждого отдельного кортежа отношения по значениям его атрибутов. При этом и основное, и подчиненное отношения должны содержать наборы атрибутов, по которым они связаны. В основном отношении это первичный ключ отношения (PRIMARY KEY), который однозначно определяет кортеж основного отношения. Поэтому первичный ключ любой таблицы обязан содержать уникальные непустые значения для каждой строки. В подчиненном отношении для моделирования связи должен присутствовать набор атрибутов, соответствующий первичному ключу основного отношения. Данный набор атрибутов в подчиненном отношении принято называть внешним или вторичным ключом (FOREIGN KEY). Иначе говоря, внешний ключ – это колонка или набор колонок, чьи значения совпадают с имеющимися значениями первичного ключа другой таблицы. Подобное взаимоотношение между таблицами называется связью (relationship). Связь между двумя таблицами устанавливается путем присвоения значений внешнего ключа одной таблицы значениям первичного ключа другой.
Операции выборки • Извлечение всех данных из таблицы • Извлечение нескольких столбцов из таблицы • Извлечение данных из таблицы по условию • select * from Туры where Цена > 27000; • Select * from Туры where Цена in (10000, 20000, 30000); • Select * from Туры where Цена between 10000 and 30000;
Агрегатные функции языка SQL • count извлекает количество записей данного поля. • sum извлекает арифметическую сумму всех выбранных значений данного поля. • avg извлекает арифметическое среднее (усреднение) всех выбранных значений данного поля. • max извлекает наибольшее из всех выбранных значений данного поля. • min извлекает наименьшее из всех выбранных значений данного поля. • select sum(Цена) from Туры
Операции вставки записей • При указании значений конкретных полей вместо использования каких-либо значений можно применить ключевое слово DEFAULT • Вставка пустой строки приводит к добавлению пробела ' ', а не значения NULL • Строки и даты задаются в апострофах. • Можно задать NULL явно, можно задать DEFAULT. • insert into Client. Info • (First. Name, Last. Name, Address, Phone) • values('Petr', 'Petrov', 'Chehova 13', '1234567');
Операция вставки записей insert into Client. Info (First. Name, Last. Name, Address, Phone) values('Petr', 'Petrov', 'Chehova 13', '1234567'); insert into Client. Info (First. Name, Last. Name, Address, Phone) values('Ivan', 'Ivanov', 'Naberejnaya 13', '1234568'); insert into Client. Info (First. Name, Last. Name, Address, Phone) values(null, 'Sidorov', 'Naberejnaya 25', '1234569'); select * from Client. Info;
Операции обновления записей • Команда update позволяет изменять заданные значения записей: • update Client. Info set First. Name = 'Andrey' where First. Name = 'Petr';
Операции удаления записей • Команда delete позволяет изменять заданные значения записей. • delete from Client. Info where Last. Name like 'Petrov'; • Запросы с командами insert, update и delete могут содержать в себе все прочие конструкции языка SQL.
Хранимые процедуры • Упорядоченная последовательность операторов SQL, оформленных в виде единого логического модуля. В хранимых процедурах допускается использование параметров и переменных, а так же операторов управления ходом выполнения и циклических конструкций.
Преимущества хранимых процедур • Предоставляется возможность вызывать на выполнение непосредственно хранимый процедуры, указывая их короткие имена, а не передавать длинные строки текста, поэтому для выполнения кода ХП требуется меньший сетевой трафик. • ХП заранее оптимизируются и компилируются, что позволяет экономить значительное время при каждом вызове ХП. • Пользователь не может проанализировать ход выполнения ХП, что повышает защищенность приложения • Хранимые процедуры могут вызываться из других ХП, что позволяет обеспечить повторное использование кода
Удаление хранимых процедур • Для удаления ХП необходимо выполнить Drop proc Название. Процедуры
Триггеры • Триггер – это хранимая процедура особого типа, вызываемая на выполнение в ответ на определенные события. • 3 основных типа триггеров – триггеры Insert, Delete, Update
Ситуации, когда могут использоваться триггеры • Обеспечение ссылочной целостности. • Создание контрольных журналов • Поддержка функциональных средств, подобных ограничению Check ( «проверка условий» ) • Подстановка других операторов вместо операторов модификации данных, применяемых пользователей. Обычно это направление использования триггеров предназначено для обеспечения вставки данных в сложные представления
Курсоры • Позволяют перемещаться по полученному набору данных
Структуры таблиц (сделано в SQL_Sample) • Main. Table с полями – Id – int, ключевое поле, установить автоинкрементность – Parent – int • Если parent = 0 или -1, то это корневая единица – Text – nvarchar(max) – number. Of. Childs – int – Archive. Link – int – temp. Value - int
Структура таблиц (2) • Создать таблицу проектов Main. Projects – Int id, автоинкрементное, первичный ключ – Nvarchar(max) name • Создать таблицу групп Main. Groups – – Id, автоинкрементное, первичный ключ Parent Name number. Of. Childs • Создать таблицу доступа групп к проекту Main. Project. Access – – Id, автоинкрементное, первичный ключ group. Id project. Id access. Type - int
Структура таблиц (3) • Создать таблицу служащих Main. Worker – Id – Name • Создать таблицу должностей служащих Main. Worker. Group, обеспечив ссылочную целостность – Id – worker. Id – group. Id
Создание ХП для вставки одной записи • Stored. Procedure 1 • Использование переменной @@Identity Slide 042. txt
Создание ХП для вставки нескольких записей • Stored. Procedure 1_1 • Просмотреть данные в таблице • Изменить поле Parent у записей, добавить еще несколько записей Slide 043. txt
Создание ХП для получения поддерева • Stored. Procedure 2 • Вместо « 5» подставить нужное значение Slide 044. txt
Создание ХП с параметром • Stored. Procedure 3 • Добавить параметр типа int в параметры ХП Slide 045. txt
Пример управляющих операторов на задаче получения цепочки родителей • Functions/Tablevalue…/Main. Table_Get. Parents • Обратить внимание на объявление переменных • Для вызова – выполнить select * from Main. Table_Get. Parents(9) Slide 046. txt
Триггеры • Добавить триггеры добавления, обновления и удаления для поддержания поля number. Of. Childs • Пример – таблица Main. Tables Slide 047_delete. txt Slide 047_update. txt Slide 047_insert. txt
Подзадача • Создать процедуру получения доступа к проектам по идентификатору пользователя • Project. Access. By. Worker. Id Slide 050. txt
Разбор строки • Задача: создать процедуру получения названий проектор по списку идентификаторов, разделенных пробелом. • Создать функцию iter_intlist_to_table • Создать процедуру Project. Get. By. Id Slide 051. txt
Агрегирующие функции SQL • • • AVG – среднее арифметическое COUNT – количество элементов (тип int) COUNT_BIG – количество элеметов (тип bigint) GROUPING – добавляет дополнительный стоблец к выводу оператора select MAX – возвращает максимальное среди значений MIN – возвращает минимальное среди значений STDEV – возвращает результат среднеквадратичного отклонения по всем значениям, представленных в параметре STDEVP – возвращает результат среднеквадратичного отклонения по всем значениям, представленным в выборке SUM – возвращает сумму всех значений VAR – возвращает дисперсию по всем значениям VARP – возвращает дисперсию выборки Во всех функциях все NULL значения игнорируются Пример использования: SELECT MAX(id) FROM STUDENTS WHERE id > 5
Системные переменные (1) • @@CURSOR_ROWS – какое количество строк находится в настоящее время в последнем наборе данных курсора, открытом в текущем соединении • @@ERROR – содержит код ошибки, относящийся к последнему оператору SQL, который был выполнен в текущем соединении. 0 – нет ошибки • @@FETCH_STATUS – содержит индикатор состояния последней операции FETCH с курсором. 0 – успех. Не 0 – неудачное завершение • @@IDENTITY – содержит последнее идентифицированное значение, созданное в текущем соединении.
Системные переменные (2) • @@MAX_PRECISION – позволяет получить уровень точности, заданный в настоящее время для десятичных и других числовых типов данных • @@NESTLEVEL – содержит информацию о текущем уровне вложенности для вложенных хранимых процедур • @@ROWCOUNT – содержит информацию о количестве строк, затронутых последним оператором. • @@TRANCOUNT – содержит информацию о количестве активных транзакций для текущего соединения • @@VERSION – позволяет получить информацию о текущей версии SQL Server Пример использования: select @@VERSION