В последствии мы будем рассматривать различные возможности базы данных последовательно – создание, удаление и редактирование. Но иногда мы будем отходить от этой последовательности, как сейчас. Мы пока научились только создавать и удалять базу данных, но тут же переходим к рассмотрению создания таблиц, а редактирование баз увидим чуть позже (разд. 1.3).
Когда мы создали базу данных, можно переходить к созданию таблиц, которые будут содержать непосредственно данные. Если сравнить базу данных с файловой системой, то ее можно рассматривать как папку, а таблицы можно рассматривать как файлы в этой папке. Только базы данных не поддерживают вложенности, т.е. в одной базе не может быть другой базы данных.
База данных сама по себе не несет никакой пользы, как и папка без файлов, потому что и то и другое не могут хранить данные. Данные хранятся в таблицах (файлах файловой системы) и именно их мы научимся создавать в этом разделе.
Что такое таблица в базе данных MS SQL Server? Данный сервер работает с реляционными таблицами, которые представляют собой двухмерный массив данных, в котором колонка определяет значение, а строки – это данные. Например, таблица базы данных схожа с классическими таблицами (списками), которые мы строим в программе Excel или на бумаге в рефератах/курсовых.
Рассмотрим таблицу на примере. Допустим, что нам надо хранить в таблице список жителей дома. Не задумываясь о базах данных, а просто на бумаге для решения поставленной задачи мы нарисуем таблицу 1.1.
Таблица 1.1. Список жителей дома
| Фамилия | Имя | Отчество | Дата рождения | Адрес |
|---|---|---|---|---|
| Иванов | Иван | Иванович | 1.1.1950 | Шаболовка 37 |
| Петров | Петр | Петрович | 4.7.1967 | Королева 12 |
| ... | ... | ... | ... | ... |
В ведении мы уже кратко говорили об уникальности, а сейчас нам предстоит поговорить об этом более подробно. Исходя из реляционной модели, все строки в базе данных должны быть уникальными. Это значит, что не может быть в списке жителей дома две записи для одного и того же жителя. Некоторые могут сказать, что это не проблема, потому что вероятность того, что в одном доме будет жить два человека с одними и теми же данными в полях ФИО и даты рождения. Вероятность маленькая, но она же есть! А ведь есть еще вероятность ошибки пользователя программы, который может ввести дважды одного и того же человека. Вот это уже станет действительно проблемой.
Чтобы понять, какие проблемы могут возникнуть при появлении в таблице одинаковых записей, представим, что у нас в списке 10 человек с одинаковыми ФИО и дата рождения (адрес опустим и не учитываем, чтобы пример был более реальным). Такой пример вполне реален. Если взять всех жителей России, то количество людей с фамилией Иванов будет не просто большим, а огромным, а совпадение остальных параметров также возможно.
Теперь представим, что у одной из записей, фамилия введена неверно и запись случайно двоит с 10 другими записями. Какую из 10 одинаковых строк должен изменить сервер? Любую, потому что записи одинаковые? Это не очень корректный выход, поэтому в стандарте SQL-92 сказано, что в реляционной таблице не может быть двух абсолютно одинаковых записей, хотя многие базы данных позволяют нарушить это ограничение и MS SQL Server не является исключением.
Чтобы обеспечить уникальность, было введено понятие ключевое поле. Это одно или несколько полей, которые обеспечивают уникальность строк в таблице. В нашем случае, ключевыми можно сделать ФИО и дату рождения. В этом случае, при попытке добавить новую строку, в которой значения в этих полях будут одинаковы с любой из уже существующих строк таблицы, база данных возвратит ошибку и не сможет добавить такую строку. Получается, что с помощью ключевых полей базы данных обеспечивают уникальность и по ним гарантировано можно найти одну и только одну запись. То есть, запросив из базы данных строку с определенными ФИО и датой рождения, мы получим только одну запись.
Некоторые базы данных позволяют создавать таблицы без ключевых полей, но я не рекомендую этого делать без особой надобности. Всегда создавайте ключевое поле. В MS SQL Server для этого можно использовать автоматически увеличиваемое поле, которое не требует дополнительных расходов на сопровождение.
В настоящее время Microsoft не рекомендует использовать в качестве ключа автоматически увеличиваемые поля. Да, эти поля достаточно просты в сопровождении, но не всегда эффективны, потому что автоматически увеличиваемое поле нельзя редактировать. Сейчас рекомендуется для ключа создавать поле типа GUID (Global Unique Identifier, Глобально Уникальный Идентификатор). Значения полей этого типа можно редактировать и достаточно просто сгенерировать самостоятельно.
Пессимисты считают, что GUID не могут быть идеальным решением проблемы ключевого поля, потому что значение генерируется и может возникнуть ситуация, когда будут сгенерированы два одинаковых значения. Оптимисты утверждают, что даже если все компьютеры в мире будут одновременно генерировать GUID, вероятность получения двух одинаковых значений стремиться к нулю.
В оптимистическое утверждение мне вериться с трудом, ведь количество возможных GUID ограничено и после генерации хотя бы 1/3 из этих значений вероятность получения повтора увеличивается в два раза. Но к пессимистам меня тоже отнести нельзя, потому что возможных вариантов GUID настолько много, что хватит даже для очень большой базы данных, с которой вы скорей всего никогда не встретитесь в своей практике. Поэтому, я использую GUID поля (и пока не разу не встречался с конфликтами GUID), о которых мы еще поговорим в разделе 2.24. А в этом разделе мы будем в основном создавать таблицы с ключевым полем в виде автоматически увеличиваемого поля, что намного удобнее при изучении языка запросов SQL.
Теперь посмотрим, как сервер хранит данные в строке. Иногда возникает необходимость хотя бы приблизительно рассчитать размер строки, а лучше всегда знать это значение. Это позволит вам более эффективно управлять размером базы данных.
Строка данных состоит из строки заголовка и порции данных. Заголовок содержит 4 байта информации о поле строки данных, таких как указатель, на расположение конца фиксированной порции данных строки, и какой размер переменной используется в строке.
Поле может содержать следующие элементы:
По возможности, делайте размер строки (общий размер всех полей) компактным, что позволяет поместить больше строк в страницу. Это позволит уменьшить операции ввода вывода и улучшить работу буфера. Для того, чтобы размер строки был компактным, необходимо выделять каждому полю только необходимый размер. Например, для хранения данных о фамилии вполне достаточно 20 символов строки переменной длинны varchar, и не имеет смысла выделять поле типа char, фиксированной длинны в 50 символов.
Тип char имеет фиксированную длину, и в странице данных будет всегда съедать определенный размер данных (в данном случае 50 символов). Тип varchar будет отнимать только столько, сколько необходимо. Например, вы объявили поле типа varchar размером 50 символов, но записали в него только 10. В странице данных на диске будет выделено (помимо заголовка) только 10 символов и не каплей больше.
Типы данных переменной длины (text, ntext и image) могут храниться как одна коллекция страниц или данные в строке. Посмотрим, как они хранятся:
Так как text, ntext и image типы данных обычно большие, SQL Server хранит их вне строк. В строке на хранящиеся данные указывает только шестнадцати битный указатель. Если эти типы данных хранят небольшое количество данных, то вы можете включить опцию text in row. Вы можете также указать предел от 24 до 7000 байтов. Эту опцию для таблицы можно включить, используя системную процедуру sp_tableoption.
В следующем примере с помощью функции sp_tableoption устанавливается опция text in row и указывается, что до 1000 символов будет храниться в строке:
EXEC sp_tableoption N'TestDatabase', 'text in row', '1000'
Для вас пока может быть не очень понятна эта команда, потому что хранимые процедуры мы еще не изучали. Но в третьей главе мы рассмотрим эту тему более подробно.
Когда вы создаете таблицу, вы должны указать имя таблицы, имена колонок и типы данных колонок. Имена колонок должны быть уникальными для определенной таблицы, но вы можете использовать одно и то же имя в разных таблицах одной базы данных.
MS SQL Server позволяет создавать до 2 биллионов таблиц в базе данных, каждая таблица может содержать до 1024 колонки и общий размер полей в таблице может достигать 8060 байтов на строку (без учета text, ntext и image). Это действительно очень много. Превысить этот предел очень сложно, и я пока не слышал, чтобы кому-то его не хватило. Если вам все же не хватит количества полей, то можно создать две таблицы и связать их один к одному. Для этого используются внешние ключи, о которых мы будем говорить в разделе 1.2.6.
С теорией таблиц закончено, теперь попробуем эту таблицу рассмотреть с точки зрения базы данных. Имена колонок в списке баз данных называются именами полей. Нам необходимо только создать таблицу в базе данных с такими полями и все готово для хранения списка.
Общий вид команды CREATE TABLE показан в листинге 1.5.
Листинг 1.5. Общий вид команды CREATE TABLE
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ]}
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
Команда CREATE TABLE достаточно большая и сложная, потому что включает в себя не только создание самой таблицы, но и описание полей, ограничений, первичных и вторичных ключей. Поэтому, описание команды отнимет достаточно много времени, потому что без рассмотрения оператора CREATE TABLE по частям нам не обойтись, чем мы и будем заниматься ближайшие пару десятков страниц.
Прежде чем создавать какую-либо тестовую таблицу, давайте создадим базу данных, в которой будут проходить все следующие тестирования SQL запросов. Все предыдущие тестовые базы данных можно удалить и создать новую, чистую базу данных. Я для этого создал базу данных с именем TestDatabase с параметрами по умолчанию.
Теперь посмотрим, как создать таблицу и удалить ее. Подключитесь к своей тестовой базе данных (TestDatabase) и выполните следующий запрос:
CREATE TABLE TestTable ( id int )
Невозможно создавать пустую таблицу, без описания полей. Хотя бы одно поле должно быть создано. В данном примере создается таблица с именем TestTable. После имени в скобках указываются имена и типы полей в формате:
имя тип параметры
Обязательными являются только имя и тип, как в примере выше, а параметры могут отсутствовать. В этом случае, поле не будет обладать никакими дополнительными параметрами.
Если полей несколько, то все они перечисляются в скобках через запятую. В данном случае, описано только одно поле с именем id, тип int, и без параметров. Какие могут быть типы данных? Стандарт SQL описывает только основные типы: строка, число и т.д., но в MS SQL Server их намного больше. Все типы, которые поддерживает SQL Server 2000 можно увидеть в приложении 1.
Для удаления таблицы используется команда:
DROP TABLE имя
Где имя – это имя удаляемой таблицы. При этом вы должны быть подключены к серверу, таблицу которого вы удаляете, т.е. удаление происходит в текущей базе данных.
Следующий запрос показывает, как удалить таблицу TestTable, созданную нами ранее:
DROP TABLE TestTable
Если полей в таблице несколько, то их необходимо указывать в круглых скобках через запятую. Например:
CREATE TABLE TestTable ( id int, vcName varchar(30), vcLastName varchar(30), vcSurName varchar(30), cPol char(1), dBirthDay datetime )
В качестве дополнительных параметров можно указывать, например, явную кодировку (Collation) для поля. В следующем примере создается таблица с двумя текстовыми полями. В первом поле для хранения фамилии не указывается кодировка, поэтому для него будет взято значение из базы данных, а у второго поля явно указываем латинскую кодировку:
CREATE TABLE TestTable ( Famil varchar(10), Name varchar(10) collate latin1_general_cs_as )
При создании таблицы можно указать файловую группу, в которой должны храниться данные этой таблицы. Имя группы указывается после ключевого слова ON, за объявлением всех полей. В следующем примере создается таблица, данные которой будут помещены в группу с именем group1:
CREATE TABLE TestTable ( Famil varchar(10), Name varchar(10) collate latin1_general_cs_as ) ON group1
Если файловая группа не указана, то таблица создается в группе по умолчанию. Если вы создали пользовательскую группу, но она не является группой по умолчанию, то обязательно указывайте ее явно.
Очень часто необходим какое-либо поле-счетчик, значение которого будет автоматически увеличиваться без вмешательства пользователя. Например, таким образом часто создаются ключевые поля и об этом методе мы говорили в разделе 1.2. Опытные программисты, которые работают с базами данных уже не первый день, с помощью таких полей добиваются уникальности данных. Например, очень часто можно видеть таблицы, в которых первое поле называется "id" (я люблю именовать их как Key1 или префикс id+имя таблицы), которое автоматически увеличивается при добавлении новых строк. Таким образом, можно сказать, что это поле всегда уникально.
Вы можете использовать свойство Identity (тождество) для создания колонки, которая содержит сгенерированное системой следующее значение, определяющее каждую строку, вставленную в таблицу. Тождественная колонка может использоваться не только для хранения значения первичного ключа, но и для определения строки, которая была вставлена в таблицу последней, ведь каждая новая строка будет получать число, которое больше любого существующего.
Когда SQL Server автоматически предоставляет значение ключа, можно уменьшить затраты, потому что облегчается программирование, первичный ключ будет коротким и уменьшаются пользовательские транзакции.
Автоматически увеличиваемое поле имеет следующие ограничения:
При разработке запросов вы можете использовать ключевое слово IDENTITYCOL в месте с именем колонки в запросе. Это позволяет вам ссылаться на колонку в таблице со свойством Identity, не зная имени колонки. Так как в таблице может быть только одна колонка со свойством Identity, сервер сам сможет найти нужное поле.
Вы можете управлять свойством Identity несколькими путями:
В современных базах данных для обеспечения уникальности рекомендуют использовать глобально уникальные идентификаторы GUID (Globally Unique Identifier). Это действительно более мощный способ обеспечения целостности, но не очень удобный с точки зрения использования, поэтому программисты старой закалки продолжают использовать автоматическое увеличение.
Некоторые базы данных создают GUID идентификаторы для каждой строки, даже если пользователь не затребовал этого. При этом, идентификатор прячется и для его отображения необходимо включать специальную опцию или использовать специальную команду в SQL запросе. В MS SQL Server идентификаторы GUID создаются автоматически, если таблица участвует в репликации.
В таблице может быть только одна колонка с автоматически увеличиваемым значением. Чтобы задать данный тип поля, необходимо среди параметров указать ключевое слово IDENTITY в следующем виде:
IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ]
После слова IDENTITY необходимо в круглых скобках указать два параметра – начальное значение (будет присвоено первой строке в таблице) и приращение. По умолчанию обоим параметрам присваивается значение 1. Следующий пример, показывает создание таблицы, с автоматически увеличиваемым полем ID:
CREATE TABLE TestTable ( id int IDENTITY(100, 2) )
В качестве начального значения выбрано число 100. При добавлении каждой последующей строки в поле будет записываться число на 2 больше, т.е. 102, 104, 106, 108 и т.д.
Для чего нужен ключ NOT FOR REPLICATION (не для репликации)? Допустим, что у вас есть два сервера баз данных, которые расположены в разных офисах и не объединены каналом связи. Необходимо иметь какую-то возможность объединения баз данных в одно целое. В MS SQL Server есть удобный инструмент решения данной проблемы – репликация. Репликация - это процесс слияния двух баз данных.
Но тут есть одна проблема. Допустим, у вас есть две базы данных DB1 и DB2. В базе данных DB1 может быть добавлена новая строка с определенным идентификатором, который был назначен автоматически. Во время репликации эта строка должна попасть в базу данных DB2, но при вставке средствами репликации в поле id будет сгенерировано другое значение. Строки получаться не одинаковыми и нарушается целостность данных.
Чтобы решить проблему, для поля с автоматически увеличиваемым значением можно указать ключ NOT FOR REPLICATION. В этом случае, при вставке новой строки из DB1 в DB2 во время репликации новый id не будет генерироваться, а будет использоваться тот, который был указан в DB1.
Но это не решает проблемы полностью, потому что идентификатор строки из DB1 может конфликтовать с уже существующей строкой в DB2, а это недопустимо. Эта проблема решается намного проще. В DB1 для поля ID задаем начальное значение 1, а для DB2 задаем начальное значение 100 000 000. Теперь новые строки в DB1 будут иметь номера 1, 2, 3, а в DB2 на 100 000 000 больше и пересечение в идентификаторах произойдет не скоро, а точнее, когда в DB1 будет добавлено 100 000 000 строк.
В общей базе данных у вас будут строки с идентификаторами из обеих таблиц, и по номеру вы можете легко определить, из какой базы данных он попал к вам в руки. Если поле id более 100 000 000, то это явно DB2.
Необходимо сделать одно замечание (хотя оно и выходит за рамки книги) – когда будете настраивать репликацию, обе базы данных должны иметь одинаковую структуру, а в мастере необходимо указать, что структуру базы данных реплицировать не надо. Если вы этого не сделаете, то сразу после выполнения мастера структура базы данных DB1 будет скопирована в DB2, и все настройки идентификаторов и начальных значений уничтожаться.
Итак, посмотрим на пример создания таблицы с автоматически увеличиваемым идентификатором и не участвующем в репликации:
CREATE TABLE TestTable1 ( id int IDENTITY(100, 2) NOT FOR REPLICATION )
Если вы используете автоматически увеличиваемое поле только для того, чтобы создать первичный ключ, то это не совсем корректное решение для базы данных, с которой работает множество человек. Дело в том, что в языках программирования строка добавляется на стороне клиента. В этот момент в этой строке значение автоматически увеличиваемого поля будет нулевым, и заполниться только после завершения ввода данных и выполнения оператора INSERT (мы его рассмотрим во 2-й главе). Очень часто это неудобно и в этом случае лучшим вариантом будет использование глобально уникальные идентификаторы GUID.
Если после ключевого слова IDENTITY ничего не указано, то подразумевается, что начальное значение и увеличение равно 1. Посмотрим на следующий запрос:
CREATE TABLE TestTable2 ( id int IDENTITY )
Он идентичен следующему:
CREATE TABLE TestTable2 ( id int IDENTITY(1,1) )
Начальное значение и приращение в виде единицы встречаются наверно в 90% случаев, и именно поэтому эти значения используется по умолчанию для IDENTITY полей.
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.