Базы данных 2

Вид материала

Содержание

Создание базы данных
Работа с базой данных
Параметры базы данных
No chkpt on Recovery
Recursive Trigger
Select into/Bulk Copy
Single user
Trunc. Log on chkpt
Целостность данных
Типы целостности данных
Использование свойства Identity для обеспечения целостности данных
Типы данных
Системные типы данных
Определенный пользователем тип данных
Работа с таблицами
Типы ограничений
Ограничение проверки
Ограничение первичного ключа
Ограничения уникальности

Подобный материал:

1 2 3 4 5 6

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

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

Информация о каждой базе данных хранится в таблице sysdatabases базы данных MASTER.

При работе с базой данных следует учесть:

Все базы данных состоят из первичного файла данных (.mdf), одного или более вторичных файлов данных (.ndf) и одного или более операционных журналов (.ldf).
Копия базы данных MODEL копируется в новую базу данных во время создания базы данных. База данных MODEL состоит из системных таблиц. Размер новой базы данных должен быть равен или больше, чем база данных MODEL.
Данные сохранены в 8-килобайтных блоках непрерывного дискового пространства - страницах. Это означает, что база данных может сохранять 128 страниц в мегабайте.
Таблицы и индексы сохранены в степенях. Степень равна восьми непрерывным страницам, или 64 КБ.
Операционные журналы используются, чтобы сохранить необходимую информацию, которая используется для восстановления базы данных в случае системного отказа.

Создание базы данных осуществляется с помощью команды CREATE DATABASE.

Работа с базой данных

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

Увеличение базы данных осуществляется с помощью команды ALTER DATABASE.

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

Необходимо отметить, что база данных не может быть сокращена до размера, меньшего, чем размер базы данных MODEL.

Для уменьшения размера базы данных используется команда DBCC SHRINKDB.

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

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

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

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

Удаление базы данных осуществляется с помощью команды DROP DATABASE.

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

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

Параметры базы данных

SQL Server имеет набор параметров, настраивающих базу данных. Они могут быть установлены либо с использованием Enterprice Manager, либо при помощи системной хранимой процедуры sp_dboption.

Опция	Enterprise Manager	Описание
Autoclose	Autoclose	Если эта опция установлена, то сервер базы данных выключается автоматически при завершении последнего соединения
dbo use only	DBO USE Only	Опция определяет, что база данных может использоваться только владельцем базы данных. Никакой новый пользователи не может использовать базу данных, если эта опция установлена. Однако, пользователи базы данных с установленными соединениями могут продолжать обращаться с базой данных, если они не используют ключевое слово USE
No chkpt on Recovery	No Check-point on Recovery	Снятие этой опции приводит к добавлению контрольных записей в базе данных после того, как она восстановлена при запуске SQL Server. Опция полезна при ежедневном копировании базы данных.
Offline	None	Эта опция чаще всего используется в базе данных со сменными устройствами, но может также использоваться на любой другой базе данных. Когда база данных помещена как "online", все устройства указанной базы данных открыты, база данных восстановлена и становится доступной для использования. Когда база данных помещена "offline", все устройства, принадлежащие этой базе данных, закрыты
Read only	Read Only	Если эта опция выбрана, то база данных доступна только для чтения. Эта опция ограничивает пользователей от модифицирования базы данных
Recursive Trigger	Recursive Triggers	Если эта опция установлена, выполняющийся триггер может использовать данные связанных таблиц. По умолчанию опцию не активна
Select into/Bulk Copy	Select into/Bulk Copy	Опция делает возможными быстрые, не регистрируемые в базе данных действия, включая SELECT INTO и UPDATETEXT
Single user	Single User	Опция ограничивает доступ к базе данных одним пользователем
Subscribed	None	Опция делает базу данных публикуемой для реплицирования
torn page detection	Torn Page Detection	Опция удаляет все незавершенные задачи из-за сбоя питания
Trunc. Log on chkpt	Truncate Log on Checkpoint	Опция определяет: будет ли журнал регистрации изменений усечен после каждой сгенерированной системой контрольной точки.
Autoshrink		Опция определяет возможность автоматического уменьшения размера базы данных

Целостность данных

Целостность данных относится к последовательности данных, сохраненных в базе данных.

Целостность данных в объектах базы данных SQL Server проверяется автоматически всякий раз, когда объект модифицируется, удаляется или добавляется в базу данных

Типы целостности данных

Целостность данных гарантирует правильность данных, сохраненных в базе данных. Существуют следующие типы целостности:

Табличная целостность

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

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

Доменная целостность

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

Ссылочная целостность

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

Ссылочная целостность поддерживается использованием Reference и Foreign Key.

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

Значения во внешнем ключе (foreign key) должны соответствовать значениям в соответствующем столбце первичного ключа (primary key).
Добавление или изменение столбца внешнего ключа должны гарантировать проверку первичного ключа.
Удаление первичных ключевых значений должно гарантировать отсутствие удаляемых значение в столбцах с соответствующим внешним ключом.
Определенная пользователем целостность

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

Использование свойства Identity для обеспечения целостности данных

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

Когда строчка добавляется в таблицу, то значение ячейки в столбце, для которого установлено свойство Identity, формируется автоматически SQL Server.

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

Значения в колонке не может изменяться;
Колонка не может содержать пустые значения;
Свойство применимо только для колонок с типом данных: int, smallint, tinyint, numeric или decimal;
Только одна колонка в таблице может иметь это свойство.

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

Типы данных

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

Есть два вида типов данных:

Системный тип данных
Определенный пользователем тип данных.

Системные типы данных

Системный тип данных – тип данных, поддерживаемый SQL Server.

Сист. тип данных	Описание
CHAR	Используется для сохранения строки фиксированной длины
VARCHAR	Используется для сохранения строки переменной длины. Применяется для сохранения пустых значений и значений переменной длины.
BIT	Применяется для хранения 0 или 1. Если значение отлично от 0 или 1, то оно воспринимается как единица.
BINARY	Используется для сохранения 255 байтов бинарной информации постоянной длины
DATETIME	Используется для сохранения даты и время в восьми байтах двух 4-байтовых целых чисел. Первое 4-байтовое целое число определяет количество дней после 1 января 1900, второе - число миллисекунд после полночи
FLOAT	Используется для сохранения 4-байтового числа с плавающей точкой
IMAGE	Используется для сохранения данных переменной длины
INT	Используется для сохранения целого числа
MONEY	Используется для сохранения денежного типа данных
NUMERIC	Используется для сохранения числа, как 96 битного (12 байтного) целого без знака
REAL	Используется для сохранения числа с плавающей точкой с точностью до 7 знаков после запятой
SMALLMONEY	Используется для сохранения денежного типа в диапазоне от –214748,3648 до 214748.3647
TEXT	Используется для сохранения типов данных переменной длины
TINYING	Используется для сохранения целых чисел от 0 до 255. Размер поля 1 байт
VARBINARY	Используется для сохранения бинарной информации переменной длины

Определенный пользователем тип данных

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

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

Добавление нового типа данных осуществляется через системную хранимую процедуру sp_addtype

Работа с таблицами

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

Таблица состоит из строк и столбцов, которые определяют экземпляр и атрибуты объекта.

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

Названия таблиц должны быть уникальны в пределах базы данных.
Названия столбцов должны быть уникальны в пределах таблицы.
Два миллиарда таблиц может быть создано в одной базе данных.
Таблица может содержать до 1024 столбцов.
Значение по умолчанию для столбца установлено в «не пусто».
Отдельная строка не может содержать больше чем 8092 байта данных.
Имя столбца не может превышать 30 символов

Создание, изменение и удаление таблиц с помощью команд CREATE TABLE, ALTER TABLE, DROP TABLE.

Ограничения

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

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

Ограничения бывают двух видов:

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

Типы ограничений

Значение по умолчанию - используется для задания значения для столбца, если значение не задано пользователем. Следующие рекомендации должны быть учтены при задании значения по умолчанию:

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

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

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

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

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

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

Ограничение первичного ключа - обеспечивает целостность на уровне столбца и ссылочную целостность на уровне таблицы.

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

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

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

Ограничение внешнего ключа - обеспечивает ссылочную целостность.

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

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

Следующие рекомендации должны быть учтены при определении внешнего ключа:

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

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

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

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

Blog