Основы работы базы данных База данных
| Вид материала | Документы |
- Ms access Создание базы данных, 34.31kb.
- Курсовая работа по дисциплине «Базы данных» на тему: «Разработка базы данных для учета, 154.05kb.
- Базы данных 2, 398.32kb.
- Методика исследования: возможности и ограничения Порядок формирования базы данных федеральных, 1297.29kb.
- 6 Засоби структурування, аналізу та обробки даних в ms excel Табличні бази даних, 71.61kb.
- Практическая работа № «Создание базы данных», 21.96kb.
- Варианты предметных областей для курсовой работы по дисциплине «Базы данных и информационные, 245.82kb.
- Курсовая работа, 52.16kb.
- Проектирование базы данных, 642.58kb.
- Лекция на тему: Основы организации баз данных, 393.78kb.
Основы работы базы данных
База данных представляет собой группу файлов, хранящихся на жестком диске. Эти файлы являются лишь предварительно локализованным местом на жестком диске, где хранятся такие объекты SQL Server, как таблицы и представления. Эти файлы на жестком диске могут относиться к трем типам: файлы с первичными данными, файлы с вторичными данными и файлы журналатранзакций.
Первичный файл данных (с расширением .MDF) является первым файлом, созданным для базы данных. Этот файл можно использовать для хранения двух типов объектов: пользовательских и системных. К пользовательским объектам относятся таблицы, представления, хранимые процедуры, а также объекты, которые используются для модификации или хранения информации, введенной пользователем. Системные таблицы содержат информацию, требуемую SQL Server для поддержки такой функциональности базы данных, как имена таблиц, локализация индексов, учетные записи пользователей базы данных, а также информация о других объектах. Системные таблицы должны храниться в файле с первичными данными, а пользовательскую информацию и другие объекты можно перемещать и в файлы со вторичными данными. Если вы выходите за пределы емкости жесткого диска, содержащего файл с первичными данными, то можете создать на отдельном жестком диске вторичный файл
данных (с расширением .NDF). После создания вторичного файла вы можете использовать
его для хранения таких пользовательских данных, как таблицы, индексы и представления, но не системных объектов (которые можно хранить только в файле с первичными данными).
Третий тип файлов требует некоторых разъяснений. Файл журнала транзакций функционирует подобно постоянному резервному копированию путем хранения транзакций. Транзакцией называется группа команд модификации данных (например, INSERT, UPDATE или DELETE), содержащаяся в блоке BEGIN TRAN. . .COMMIT и выполняющаяся как единое целое. Это означает, что в базе данных будут выполнены либо все команды транзакции, либо ни одна. SQL Server понимает два типа транзакций: неявные и явные. Неявная транзакция выполняется при отправке команды модификации данных программе SQL Server без упаковки в блок BEGIN TRAN. . . COMMIT. В этом случае SQL Server добавит этот блок за вас. Явная транзакция выполняется при вводе инструкций BEGIN TRAN и COMMIT в начале и конце блока инструкций. Типичная явная транзакция выглядит следующим образом:
BEGIN TRAN
INSERT RECORD
DELETE RECORD
COMMIT TRAN
В данном примере SQL Server "видит" команды INSERT и DELETE как единый модуль модификации. Эти команды либо выполняются обе, либо не выполняются вообще. В терминологии SQL Server это означает, что произойдет либо выполнение, либо откат. Команда DELETE не может выполниться без команды INSERT и наоборот. В SQL Server каждая команда, модифицирующая данные, интерпретируется как транзакция и содержит команды BEGIN и COMMIT независимо от вашего желания (если вы не добавите эти команды сами, то это сделает за вас SQL Server). Если вы думаете, что все эти транзакции записываются прямо в файл базы данных, то заблуждаетесь. Когда пользователь пытается модифицировать запись в базе данных, SQL Server локализует страницу данных в базе данных, содержащую указанную запись. После локализации данная страница загружается в особую область памяти, называемую кешем данных, который использует SQL Server для хранения модифицируемых данных. Все изменения страницы находятся в оперативной памяти, поскольку она работает примерно в 100 раз быстрее жесткого диска.
Оставлять эти изменения записей в оперативной памяти — плохая идея, поскольку оперативная память является временной, т.е. ее содержимое очищается каждый раз при выключении компьютера. Если произойдет сбой питания, то в кеше данных вы потеряете все изменения. Поэтому SQL Server записывает изменения, сделанные в кеше данных, в журнал транзакций. Таким образом, вы получаете копию данных в оперативной памяти и в файле журнала транзакций на жестком диске. При выключении сервера все данные в оперативной памяти будут утеряны, но вы сможете восстановить их из журнала транзакций. В этом смысле журнал транзакций подобен оперативной резервной копии кеша данных.
Создание баз данных
Ранее мы уже говорили, что база данных содержит по крайней мере два файла: первичный файл данных (с расширением . MDF) и файл журнала транзакций (с расширением . LDF). Вам также могут потребоваться вторичные файлы данных, если жесткий диск с первичным файлом переполнен, так что мы обсудим эти вопросы далее в этой главе. Для создания базы данных вам нужно лишь создать первичный файл данных и файл журнала транзакций. Вы можете выполнить эту задачу двумя способами:
• графически с помощью SQL Server Management Studio,
• посредством кода Transact-SQL.
Создание баз данных в SQL Server Management Studio
Самый простой способ создания базы данных состоит в использовании утилитыSQL Server Management Studio. Мы создадим базу данных BookShopDb, которую позже заполним таблицами, представлениями и другими объектами, предназначенными для отдела продаж.
1. Откройте SQL Server Management Studio и выполните подключение с использованием аутентификации Windows.
2. В окне Object Explorer найдите и раскройте папку Databases.
3. Щелкните правой кнопкой мыши на папке Databases и выберите команду New Database.
4. В левой панели вы увидите список Select A Page. Перейдите во вкладку General и введите следующую информацию:
Database name: BookShopDb
Owner: sa
Collation:<по умолчания»
Recovery Model: Full
5. В папке Database Files вы должны увидеть две строки: одну для файла данных и одну для файла журнала. Измените исходный размер файла данных на 10.
6. В столбце Autogrowth для файла данных щелкните на кнопке с многоточием, установите переключатель в положение Restricted File Growth и измените максимальный размер на 20. Если вы оставите настройку Unrestricted File Growth, то файл данных заполнит весь жесткий диск, что приведет к неработоспособности компьютера, если другие программы находятся на том же диске (в частности,сама операционная система Windows).
7. В столбце Autogrowth для файла журнала щелкните на кнопке с многоточием, установите переключатель в положение Restricted File Growth, задайте максимальный размер 2 и измените значение File Growth на 10 процентов.
8. Для создания новой базы данных щелкните ОК.
9. Вы должны увидеть свою новую базу данных в окне Object Explorer приложения Management Studio. Щелкните на новой базе данных, чтобы просмотреть ее свойства
Создание баз данных с помощью Transact-SQL
Несмотря на эффективность и простоту использования приложения Management
Studio для создания баз данных, у вас может не оказаться его под рукой. Предположим, что вы разработчик, пишущий собственное приложение. Для этого приложения ваша программа установки должна создать необходимые базы данных. Приложение Management Studio не работает в таком сценарии, так что вам нужно знать, как создать базу данных с помощью языка T-SQL (сокращенная форма от Transact-SQL). Общий синтаксис инструкции CREATE DATABASE выглядит следующим образом:
CREATE DATABASE имя_базы_данных
[ ON
[ < f i l e s p e c > [ , . . . n ] ]
[ , < f i l e g r o u p > [ , . . . n ] ]
]
[
[ LOGON { < f i l e s p e c > [ , . . . n ] } ]
[ COLLATE имя_сортировки ]
• [ FOR { ATTACH [ WITH < s e r v i c e _ b r o k e r _ o p t i o n > ]
| ATTACH_REBUILD_LOG } ]
[ WITH < e x t e r n a l _ a c c e s s _ o p t i o n > ]
]
[ ; ]
[ PRIMARY ]
(
[ NAME = логическое_имя_файла , ]
FILEMANE = ' имя_файла_ОС
[ , SIZE = размер [ KB | MB | GB | ТВ ] ]
[ , MAXSIZE = {макс_размер [ KB | MB | GB | ТВ ] | UNLIMITED } ]
[ , FILEGROWTH = приращение_размера [ KB | MB | % ] ]
) [ ,...n ]
FILEGROUP имя_группы_файлов
DB_CHAINING { ON | OFF }
I TRUSTWORTHY { ON | OFF }
ENABLE_BROKER
| NEW_BROKER
I ERROR_BROKER_CONVERSATIONS
Далее следует описание каждого элемента синтаксиса.
• имя_базы_данных. Имя новой базы данных длиной до 128 символов.
• ON. Эта опция указывает на файловую группу, на основе которой следует создавать
файл данных. Файловая группа представляет собой логическую группу вторичных файлов данных, которую можно использовать для управления размещением пользовательских объектов (таких как таблицы и индексы). Опция PRIMARY после аргумента ON используется для указания группы файлов
PRIMARY, в которую по умолчанию входят все созданные файлы, и которая является единственной группой файлов, содержащей первичный файл данных.
• PRIMARY. Эта опция указывает на то, что связанный список
• LOG ON. Данная опция указывает место и размер создаваемых файлов журналов. Если опция LOG ON не задана, то SQL Server создаст файл журнала с размером 25% от размера всех файлов данных и сгенерированным системой именем, и при этом поместит его в тот же каталог, где лежат файлы данных. Опцию LOG ON лучше всего использовать для помещения файла журнала транзакций на отдельный физический диск, чтобы в случае аварийного отказа системы вы могли получить доступ ко всем транзакциям, выполненными до сбоя.
• COLLATE имя_сортировки. Это предложение указывает на сортировку для базы данных, используемую по умолчанию с именем сортировки Windows или SQL Server.
• FOR ATTACH. Используйте это предложение для создания базы данных путем присоединения существующего набора файлов базы данных. Чтобы присоединить базу данных, вам потребуется файл .mdf и все файлы . ndf. Если у вас множество файлов данных и журналов, убедитесь, что собраны все файлы, иначе эта команда не будет выполнена.
• FOR ATTACH_REBUILD_LOG. Вы можете использовать это предложение для создания базы данных путем присоединения существующего набора файлов базы данных, причем вам не понадобятся все файлы журналов. Данную команду удобно использовать, когда в целях отчетности требуется хранить копию базы данных "только для чтения" на другом сервере. При использовании этого предложения вам не потребуется копировать все файлы журналов транзакций (. ndf), однако потребуются все файлы данных (.mdf).
• NAME. Данная опция указывает логическое имя базы данных, которое будет применяться для ссылки на нее из кода Transact-SQL. При использовании опции FOR ATTACH этот параметр не требуется.
• FILENAME. Это имя и путь файла базы данных, хранящегося на жестком диске. Каталог должен быть локальным (не сетевым), и его нельзя сжимать.
• SIZE. Исходный размер файлов данных. Его можно задать в килобайтах или мегабайтах. Если вы не предоставите размер для исходного файла данных, то SQL Server сгенерирует файл с размером, который задан в системной базе данных Model. Если же не предоставить размер для вторичного файла, то SQL Server автоматически выберет размер 1 Мбайт.
• MAXSIZE. Максимальный размер, до которого может расти база данных. Его также можно задать в мегабайтах или килобайтах. В качестве альтернативы вы можете задать опцию UNLIMITED, разрешающую SQL Server расширять файл данных до размера всего жесткого диска.
• UNLIMITED. Эта опция указывает, что определяемый файл может расти до тех пор, пока не заполнит весь диск. Использовать ее не рекомендуется, разве что в том случае, если файл помещается на специально выделенный диск.
• FILEGROWTH. Это приращение расширения файла. Оно задается в мегабайтах, килобайтах или процентах (%). Если единица измерения не задана, то задается размер в мегабайтах.
• FILEGROUP. Данная опция указывает логическое имя для группы файлов, на основе которых создается файл данных.
• DB_CHAINING{ON | OFF}. Если пользователь извлекает из представления данные, то оно должно иметь доступ к соответствующим таблицам. Связь между представлением и таблицами называется цепочкой принадлежности. Если задать этой опции значение ON, то база данных сможет стать началом или концом цепочки принадлежности. В случае присваивания этой опции значения OFF база данных не сможет быть вовлечена в цепочку.
• TRUSTWORTHY{ON | OFF}. Если этой опции задать значение ON, то модули базы данных, использующие имперсонифицированный контекст, смогут получать доступ к ресурсам вне базы данных. Задавать эту опцию могут только члены фиксированной серверной роли sysadmin.
• ENABLE_BROKER. Данная опция указывает на то, что для базы данных включен брокер служб.
• NEWBROKER. Эта опция активизирует в базе данных брокер служб с идентификатором
service_broker_guid.
• ERROR_BROKER_CONVERSATION. Эта опция заканчивает все диалоги с ошибкой,
указывающей на создание копии брокера.
Настройка параметров базы данных
Если вы покупали новый автомобиль или хотя бы разговаривали с продавцами, то знаете что машины продаются с некоторыми функциональными элементами, напри мер радио и кондиционером, не входящими в минимальный набор. Эти элементы могут заставить автомобиль вести себя несколько иначе. Базы данных SQL Server также имеют свои опции — параметры, от значения которых зависит их поведение. Перед тем как использовать базу данных, мы рассмотрим некоторые из этих настроек. Большинство настроек баз данных можно задавать с помощью утилиты SQL Server Management Studio. Если вы щелкнете правой кнопкой мыши на одной из баз данных выберете в контекстном меню пункт Properties и откроете вкладку Options, то увидите окно.
• Параметр Auto Close. Когда пользователь подключается к базе данных, она должна быть открыта. Когда база данных открыта, она потребляет такие ресурсы, как оперативная память и процессор. Если данному параметру присвоить значение True, то после отключения последнего пользователя база данных будет закрыта. Поскольку, как правило, в любой системе избытка ресурсов не наблюдается, по умолчанию в редакции Express Edition этому параметру присвоено значение True. Таким образом, неиспользуемая база данных будет закрываться.
Во всех остальных версиях этому параметру присвоено значение False — пользователи смогут открывать и закрывать базу данных днем и ночью, однако это существенно затормозит работу системы.
• Параметр Auto Create Statistics. При отправлении запроса на сервер базы данных он перехватывается оптимизатором запросов, единственное назначение которого состоит в поиске самого быстрого способа возврата результирующего набора данных. Он выполняет эту задачу путем считывания статистики о каждом столбце, упомянутом в инструкции SELECT (эта статистика основана на количестве уникальных значений в столбце и количестве дубликатов). Если этому параметру присвоить значение True, то SQL Server автоматически создаст статистику для любого столбца, являющегося частью индекса. Если же присвоить значение False, то вам придется создавать собственную статистику. Опять-таки, лучше всего оставить этот параметр включенным до тех пор, пока вы не станете достаточно хорошо разбираться в SQL Server, чтобы мудрить с оптимизатором запросов.
• Параметр Auto Shrink. SQL Server периодически сканирует базы данных, чтобы определить не содержат ли они более 25% свободного пространства. Если это так, то SQL Server может автоматически уменьшить размер базы данных, чтобы в ней было лишь 25% свободного пространства. Если присвоить этому параметру значение True (как это делается по умолчанию в Desktop Edition), то может выполняться автоматическое сжатие базы данных. Лучше всего оставить этот параметр со значением по умолчанию, поскольку процесс сжатия может затребовать дополнительные системные ресурсы на сервере — о дисковом пространстве можно позаботиться и самому. Позже мы обсудим процесс сжатия баз данных вручную.
• Параметр Auto Update Statistics. Присваивание этой опции значения True укажет SQL Server время от времени автоматически обновлять статистику. Если же ей задать значение False, то вам придется обновлять статистику вручную. Если у вас дефицит системных ресурсов (таких как процессор и оперативная память), отключите эту опцию. Вы можете создать план поддержки базы данных, который будет выполнять эту задачу по указанной схеме.
• Параметр Cursor Close On Commit Enabled. Курсор можно представить себе как итоговый набор данных. Курсоры возвращают отдельные строки данных и поэтому ускоряют процесс их извлечения в случае большого итогового набора. Если присвоить этому параметру значение True, то курсоры будут закрываться сразу же после подтверждения транзакций. Будет благоразумным оставить данный параметр со значением False, чтобы курсоры оставались открытыми до завершения процесса модификации данных. После этого курсор можно закрыть
вручную.
• Параметр Default Cursor. Если присвоить этому параметру значение Local, то любой созданный курсор будет локальным для процедуры, его вызвавшей. Это означает, что если вы выполняете хранимую процедуру (предварительно записанный запрос, хранящийся в SQL Server), которая создает курсор, то этот курсор сможет использовать только данная хранимая процедура. Если же данному параметру присвоить значение Global (значение по умолчанию), то использовать созданный курсор смогут все процедуры, используемые тем же подключением. Поэтому, если некий Вася выполняет хранимую процедуру, создающую курсор, то со значением Global данного параметра созданный курсор сможет использовать любая иная процедура, выполняемая им. Если же этому параметру присвоить значение Local, то на курсор сможет ссылаться только та хранимая процедура, которая его создала.
• Параметр ANSI Null Default. При создании таблицы в SQL Server вы можете определить в ней возможность наличия пустых столбцов — так называемое условие пустого значения. Если при создании или модификации таблицы вы явно не задали возможность появления пустых значений, а этой опции задано значение False, то в ваших столбцах не сможет быть значений null. Если присвоить этому параметру опции значение True и не предусмотреть возможности появления пустых значений, то столбцы все равно смогут принимать значения null . Этот параметр используется по усмотрению. Если большинство ваших столбцов не должны содержать значения null , то эту опцию лучше оставить со значением по умолчанию False .
• Параметр ANSI Nulls Enabled. Если этому параметру присвоить значение True, то любое сравнение с пустым значением n u l l будет выдавать результат n u l l . Если же ей присвоить значение F a l s e , то сравнения данных не в кодировке Unicode со значениями n u l l будут выдавать F a l s e , а сравнения значений n u l l со значениями n u l l будут выдавать True. По умолчанию этой опции присваивается
значение F a l s e .
• Параметр ANSI Padding Enabled. Эта настройка управляет методом хранения в столбце значений короче определенного размера. Если ей присвоить значение True, то столбцы с типами данных c h a r (л) NOT NULL, c h a r (л)NULL, и b i n a r y (л) NULL будут дополняться до длины столбца, а столбцы с типами v a r c h a r (л) и v a r b i n a r y (л) не будут дополняться, и данные не будут обрезаться. Если же ей присвоить значение F a l s e , то столбцы с типами данных c h a r (л) NOT NULL и c h a r (л) NULL будут дополняться до длины столбца, а столбцы с типами c h a r (л) NULL, b i n a r y (л) NULL, v a r c h a r (л) и v a r b i n a r y (л) не будут дополняться, что приведет к сжатию данных.
• Параметр ANSI Warning Enabled. Мы знаем, что деление на нуль невозможно, но об этом следует сообщить и компьютеру. Если присвоить этому параметру значение F a l s e и попытаться выполнить деление на нуль или использовать значение n u l l в математическом уравнении, то в результате вы получите значение n u l l , а ошибки не будет. Если же присвоить параметру значение True, то вы получите предупреждение. По умолчанию этому параметру присваиваетсязначение F a l s e .
• Параметр Arithmetic Abort Enabled. Эту опция указывает SQL Server, что делать в случае переполнения или арифметической ошибки деления на нуль. Если ей присвоить значение True, то будет выполнен откат всего запроса или транзакции. Если же присвоить данной опции значение F a l s e , то запрос или транзакция продолжат выполняться, но будет выдано предупреждение.
• Параметр Concatenate Null Yields Null. Конкатенация строк комбинирует множество строк в одну с помощью оператора +. Например, конкатенация Привет, меня зовут + Вася вернет Привет, меня зовут Вася в виде одной строки. Если присвоить этому параметру значение True и попытаться выполнить
конкатенацию Привет, меня зовут + n u l l , то вы получите значение n u l l . Если же присвоить параметру значение F a l s e и попытаться выполнить конкатенациюПривет, меня зовут + n u l l , то вы получите строку Привет, менязовут. Значение F a l s e принято по умолчанию.
Встроенные типы данных
• bit. Этот тип данных может содержать только значения 0 или 1 (или "пустое" значение null). Его очень удобно использовать в качестве двоичного индикаторасостояния — on/off, yes/no, t r u e / f a l s e .
• 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 байт на жестком диске и удобен для хранения очень больших чисел, не помещающихсяв типе данных i n t .
• smallint. Этот тип данных содержит целочисленные данные от (-32 768) до (32 767). Он занимает 2 байт на жестком диске и удобен для хранения меньших чисел, чем те, которые хранятся в типе данных i n t .
• tiny int. Этот тип данных содержит целочисленные значения от 0 до 255. Он занимает один байт на жестком диске. Тип t i n y i n t удобно использовать для хранения чего-нибудь наподобие кода продукта, если у вас их не больше 255.
• decimal. Этот тип данных содержит числа с фиксированной точностью от -1038-1 до 1038-1 (единица с 38 нулями). Он использует два параметра: точность и степень. Точностью называется общее количество знаков, хранящееся в поле, а степень — это количество знаков справа от десятичной запятой. Таким образом,
если у вас имеется точность 5 и степень 2, то поле будет иметь формат 111,22. Этот тип данных следует использовать при хранении дробных чисел (чисел с десятичной запятой).
• numeric. Это синоним типа данных decimal — они идентичны.
• money. Этот тип данных содержит денежные значения от – 263(-922 337 203 685 477,5808) до 263 (922 337 203 685 477,5807) с десятитысячной точностью от денежной единицы. Он занимает 8 байт на жестком диске и удобен для хранения денежных сумм, превышающих 214 748,3647.
• smallmoney. Содержит значения от -214 748,3648 до 214 748,3647 с десятитысячной точностью. Он занимает 4 байт на жестком диске и удобен для хранения небольших денежных сумм.
• float. Содержит числа с плавающей запятой от -1.79Е + 38 до 1.79Е + 38. Некоторые числа вообще не заканчиваются после десятичной запятой, например число я. Такие числа нужно аппроксимировать, что и делается с помощью плавающей запятой. Если, к примеру, вы зададите тип данных f l o a t (2), то
число л будет выглядеть как 3,14с двумя цифрами после десятичной запятой.
• real. Содержит числа с плавающей запятой от -3,40Е + 38 до 3,40Е + 38. Например, r e a l (24) представляет тип с плавающей запятой и 24 знаками последесятичной запятой.
• datetime. Содержит дату и время в диапазоне от 1 января 1753 года до 31 декабря 9999 года с приращением значений ,000, ,003 и,007. Этот тип данных занимает 8 байт на жестком диске и должен использоваться при отслеживании специфических дат и времени.
• smaldatetype. Содержит дату и время, начиная с 1 января 1900 года и заканчивая 6 июнем 2079 года, с точностью до 1 минуты. Он занимает лишь 4 байт на жестком диске и должен использоваться для хранения дат и времени.
• timestamp. Используется для установки временнък меток записей при вставке, которые соответствующим образом обновляются. Удобен для отслеживания изменений в данных.
• uniqueidentifier. Функция NEWID () используется для создания глобально уникальных идентификаторов, которые выглядят примерно так: 6F9619FF-8B86-D011-B42D-00C04FC964FF. Эти уникальные числа могут храниться в поле с типом данных uniquidentif i e r и удобны для создания сопровождающих чисел
или серийных номеров, которые нельзя дублировать.
• char. Этот тип данных содержит символьные не Unicode-данные фиксированной длины до 8000 знаков. Он удобен для хранения символьных данных одинаковой длины. В качестве примера можно привести поле S t a t e , в каждойзаписи которого содержится только два символа. Этот тип данных постоянно использует один и тот же объем на диске независимо от количества символов, реально хранящихся в поле. Например, тип данных char (5) всегда использует 5 байт на жестком диске, даже если в поле содержится лишь два символа.
• varchar. Этот тип данных содержит не Unicode-данные переменной длины до 8000 символов. Он удобен для хранения данных переменной длины, например, для полей с именем и фамилией, где в каждой фамилии содержится разное число символов. Чем меньше символов хранится в этом типе, тем меньше места
он занимает на жестком диске. Например, если у вас имеется поле с типом данных varchar (20), в котором вы храните лишь 10 символов, то поле займет лишь 10 байт жесткого диска, а не 20. Максимальное количество символов, допустимое в типе varchar (20), равно 20.
• varchar(max). Этот тип подобен типу данных varchar с указанным максимально возможным размером (max) и может хранить 231-1 (2 147 483 67) байт данных.
• nchar. Содержит данные Unicode фиксированной длины до 4000 символов. Подобно всем типам данных Unicode его удобно использовать для хранения небольших фрагментов текста, которые будут считываться разноязычными клиентами (т.е. одни читают на немецком, а другие, например, на испанском).
• nvarchar. Этот тип содержит данные Unicode переменной длины до 4000 символов. Он не отличается от типа nchar, за исключением того, что тип nvarchar использует меньше дискового пространства при хранении меньшего количества символов.
• nvarchar(max). Этот тип подобен типу nvarchar с заданным максимально возможным размером (max) и может хранить 231-1 (2 147 483 67) байт данных.
• binary. Содержит двоичные данные фиксированной длины до 8000 байт. Интерпретируется как битовая строка (например, 11011001011) и удобен для хранения любых данных, которые лучше выглядят в двоичном или шестнадцатеричном виде, как, например, идентификатор защиты.
• varbinary. Содержит двоичные данные переменной длины до 8000 разрядов. Этот тип подобен типу данных binary за исключением того, что он использует меньше места на диске при хранении меньшего количества разрядов.
• varbinary(max). Этот тип имеет те же атрибуты, что и тип данных varbinary, но с декларированным размером (max). Может хранить 231-1 (2 147 483 67) разрядов данных. Удобен для хранения двоичных объектов наподобие файлов JPEG с изображениями или документов Word.
• xml. Этот тип данных используется для хранения целых документов или фрагментов XML (фрагмент — это документ без элемента высшего уровня).
• identity. На самом деле это вовсе не тип данных, но он выполняет важную роль. Это свойство, обычно используемое в конъюнкции с типом данных int, применяется для приращения значения столбца каждый раз при вставке новой записи. Например, первая запись таблицы имеет идентификационный номер
1, вторая строка — номер 2 и т.д.
• sql_variant. Подобно типу i d e n t i t y это вовсе не реальный тип данных; он используется для хранения значений с различными типами данных. В нем нельзя хранить лишь следующие значения: varchar (max), nvarchar (max), text, image, sql_variant, varbinary(max), xml, ntext, timestamp и типы данных, определяемые пользователем.
Создание таблиц с помощью мастера.
1. Откройте SQL Server Management Studio. В окне Object Explorer раскройте
папки Databases→BookShopDb.
2. Щелкните правой кнопкой на значке Tables и примените команду New Table, чтобы открыть конструктор таблиц.
3. В первую строку в столбце Column Name введите имя AuthorID.
4. В столбец Data Type введите тип smallint.
5. Убедитесь, что параметр Allow Nulls не включен. Со включенной этой опцией поле может быть вообще без данных.
6. В нижней половине экрана в разделе Table Designer блока Column Properties расширьте Identity Specification и измените значение параметра (Is Identity) в Yes.
7. Введите в столбец Column Name второй строки под AuthorID имя FirstName.
8. В столбец Data Type введите тип varchar(10).
9. В разделе General блока Column Properties определите для настройки Length значение 100.
10. Убедитесь, что параметр Allow Nulls отключен.
11. В столбец Column Name введите для третьей строки имя LastName.
12 В столбец Data Type введите тип varchar(30).
13. Аналогично заполняются остальные поля.
14. Сохраните созданную таблицу с именем Authors.
Использование ограничений проверки
Ограничение на проверку представляет собой инструкцию Transact-SQL связывания
с полем. Ограничения на проверку используются для ограничения данных, принимаемых
полем, даже если они имеют корректный тип.
1. В окне Object Explorer раскройте папки базы данных BookShopDb и найдите в папке Tables таблицу dbo. Authors.
2. Щелкните правой кнопкой на папке Constraints и выберите команду New Constraint.
3. Введите в текстовый блок (Name) диалогового окна New Constraint имя CK_ YearBorn.
4. В текстовый блок Description введите описание Check for YearBorn codes.
5. Чтобы создать ограничение, принимающее четыре числа, которыми могут быть
только цифры от 0 до 9,причем первая цифра может принимать значение 1 или 2 в текстовый блок Expression введите следующий код
(YearBorn like '1 [0-9] [0-9] [0-9]' or YearBorn like '2 [0-9] [0-9] [0-9]')
6. Щелкните на кнопке Close.
7. Щелкните на кнопке Save в левой верхней части панели инструментов.
Добавление записей в таблицу.
Введем в таблицу несколько новых записей, используя инструкцию INSERT.
1. В SQL Server Management Studio щелкните на кнопке New Query и выберите команду New SQL Server Query. В случае чего подключитесь к серверу с использованием аутентификации Windows.
2. Введите в окно запроса следующий код:
USE BookShopDb
INSERT Authors
VALUES (1, 'Михаил', 'Лермонтов',1814, 1841)
3. Щелкните на кнопке Execute, чтобы выполнить запрос
Чтобы просмотреть новую запись, выполните команду Query1 New Query With Current Connection (комбинация клавиш
SELECT * FROM Authors
Использование первичных ключей
1. Откройте SQL Server Management Studio и выполните подключение с использованием аутентификации Windows.
2. В Object Explorer раскройте папки Database →BookShopDb →Tables.
3. Щелкните правой кнопкой мыши на таблице Authors и выберите команду Modify Table.
4. В окне конструктора таблиц щелкните правой кнопкой на поле AuthorID в столбце
Column Name и выберите команду Set Primary Key.
Пример выполнения работы.
Создание базы данных с помощью кода TRANSACT
1. Откройте Management Studio и выполните регистрацию с помощью аутентификации
Windows.
2. Выполните команду File New→New →SQL Server Query.
3. Для создания базы данных размером 10 Мбайт с именем BookShopDB на диске С с файлом журнала размером 2 Мбайт выполните следующий код (отметим, что вы должны заменить путь С : \ путем к диску, на котором у вас установлена программа SQL Server):
CREATE DATABASE BookShopDB
ON PRIMARY
(name = BookShopDB,
filename = 'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\data\ BookShopDB.mdf' ,
s i z e = 10MB,
maxsize = 15MB,
filegrowth = 1MB)
LOG ON
(name = BookShopLog,
filename = ' c:\Program Files\Microsoft SQL
Server\MSSQL.l\MSSQL\data\ BookShopLog.ldf' ,
s i z e = 2MB,
maxsize = 3MB,
filegrowth = 10%)
4. На панели результатов окна запроса (внизу) вы должны увидеть сообщение об успешном выполнении команды. Чтобы проверить созданную базу данных, разверните сервер в Object Explorer, а затем раскройте папку Database. В списке доступных баз данных вы должны увидеть базу BookShopDB.
Создание таблиц с помощью TRANSACT-SQL. Использование ограничений PRIMARY KEY, FOREIGN KEY и ограничения проверки CHECK.
Создание таблицы Authors в базе BookShopDB
1. Откройте Management Studio и выполните команду File New→New →SQL Server Query.
2. Введите в окно запроса следующий текст
use[BookShopDB]
CREATE TABLE Authors
(AuthorID smallint
IDENTITY(1.1)
PRIMARY KEY CLUSTERED,
FirstName varchar(10) NOT NULL,
LastName varchar (30) NOT NULL,
YearBorn smallint NOT NULL
CONSTRAINT ck_YearBorn
CHECK (YearBorn LIKE '1[0-9][0-9][0-9]' or YearBorn LIKE '2[0-9] [0-9] [0-9]'),
YearDied char(4) NOT NULL DEFAULT '-')
3. Щелкните на кнопке Execute чтобы выполнить запрос и посмотрите на полученную таблицу в окне Object Explorer.
При создании таблицы Authors для колонки, содержащей идентификационный номер автора AuthorID имеющего тип данных smallint было использование ограничения первичного ключа. Также было использовано ключевое слово IDENTITY, которое предписывает системе осуществлять заполнение колонки автоматически. При его использовании необходимо указать начальное значение и приращение.
Использование ограничения проверки CHECK заключается в том, что в колонке года рождения можно ввести только четыре цифры от 0 до 9, причем при вводе в качестве первого символа разрешены только значения 1 и 2.
Использование ввода значения по умолчанию DEFAULT заключается в появление значка "-" при отсутствии введенных данных
4. Создание таблицы BookAuthors в базе BookShopDB
use[BookShopDB]
CREATE TABLE BookAuthors
(AuthorID smallint
IDENTITY(1.1)
PRIMARY KEY CLUSTERED,
TitleID сhar(8) NOT NULL
Title varchar(40) NOT NULL,
Genre char(10) NOT NULL,
BookDate smallint NOT NULL
CONTRAINT ck_ BookDate
CHECK (BookDate LIKE '1[0-9][0-9][0-9]' or BookDate LIKE '200[0-9]')
5. Щелкните на кнопке Execute чтобы выполнить запрос и посмотрите на полученную таблицу в окне Object Explorer.
Добавление ограничения PRIMARY KEY к таблице BookAuthors
При создании в базе данных BookAuthors таблицы BookShopDB мы создали первичный ключ. Но это можно сделать и после создания таблицы. Если вы забыли сразу определить ключи создайте следующий запрос на изменение таблицы:
1. Откройте Management Studio и выполните команду File New→New →SQL Server Query.
2. В раскрывающемся списке баз данных выберите BookShopDB.
3. Введите в окно запроса следующий текст
use[BookShopDB]
ALTER TABLE Authors
ADD CONSTRAINT authors_pk PRIMARY KEY (AuthorID)
Добавление к таблице BookAuthors ограничения FOREIGN KEY
1. Откройте Management Studio и выполните команду File New→New →SQL Server Query.
2. В раскрывающемся списке баз данных выберите BookShopDB.
3. Введите в окно запроса следующий текст
use[BookShopDB]
ALTER TABLE BookAuthors
ADD CONSTRAINT authorid_fk FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)
Задание по выполнению лабораторной работы №1
- Создать таблицу «Сотрудники» по следующей структуре:
Имя поля
Табельный номер
Дата рождения
Фамилия
Оклад
Код отдела
Отработано дней
Премия
Пол
Тип
Число
дата
текст
число
Число
Число
число
текст
(1 символ)
Значение по умолчанию
-
-
-
200
-
0
0
м
- Добавить ограничения на целостность данных.
- табельный номер является первичным ключом данной таблицы и он является уникальным;
- размер оклада должен быть в пределах 200-1000 р.
- премия – в пределах 0-600р.
- отработано дней- положительное число.
- пол может быть только «м» или «ж».
- Создайте таблицу «Отделы» по следующей структуре:
Имя поля
Код отдела
Название отдела
Количество сотрудников
Табельный номер начальника отдела
Тип
Число
текст
число
Число
Значение по умолчанию
-
“неизвестно”
0
-
- Добавить в первую таблицу «Сотрудники» ограничение ссылочной целостности FOREIGN KEY (внешний ключ) между столбцами [Код отдела] в таблицах «Сотрудники» и «Отделы».
- Описать подробно ход выполнения работы.