Лекция № 3

Тема: «Работа с таблицами. Индексы»

 

        План

    1. Введение

    2. Работа с таблицами в SQL Server

        2.1. Выбор типа данных

        2.2. Ограничения целостности

        2.3. Использование автонумерации

        2.4. Создание таблицам средствами Transact-SQL

        2.5. Связывание таблиц

        2.7. Изменение таблицы

        2.8. Удаление таблицы

        2.9. Управление таблицами средствами Enterprise Manager

    3. Работа с таблицами в MySQL

    4. Работа с таблицами в SQLite

    5. Работа с индексами

 

1. Введение

В любой системе управления базами данных таблицы играют огромную роль. Таб­лицы используются для хранения всей информации, которую пользователи внесли в базу данных. С точки зрения пользователя таблица представляет собой двумер­ный массив, каждая строка которого является экземпляром описываемого в таб­лице типа объекта. Столбцы массива представляют собой атрибуты объекта. На пересечении конкретной строки и конкретного столбца находится атрибут кон­кретного объекта.

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

 

2. Работа с таблицами в SQL Server

 

2.1. Выбор типа данных

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

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

 

Целочисленные типы данных

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

В SQL Server поддерживаются следующие целочисленные типы данных;

bigint – это тип данных, использующий для хранения данных 8 байт. При работе с типом данных bigint пользователи могут хранить числа в диапазоне от -263 до 263-1;

int предназначен для хранения чисел в диапазоне от -231 до 231. Для хранения чисел в этом типе данных используется 4 байта;

smallint, как и предыдущий тип, обеспечивает хранение и положительных, и отрицательных значений. Однако для представления чисел используется 2 байта. Таким образом, в распоряжении пользователя имеется 65 536 комбинаций, что с учетом знака обеспечивает хранение чисел в диапа­зоне от -215 до 215-1;

tinyint – это самый «маленький» из целочисленных типов данных, использующих для хранения чисел всего 1 байт. В отличие от двух предыдущих ти­пов, не разрешает хранение информации о знаке числа. Таким образом, в распоряжении пользователя имеется 256 комбинаций, что соответствует интервалу от 0 до 255.

 

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

Типы данных этой группы применяются для хранения чисел с десятичной точ­кой. В SQL Server существует два вида нецелочисленных типов данных;

десятичные (Decimal). Для хранения чисел этого типа они представляются в виде отдельных цифр, каждая из которых хранится отдельно. Десятичные нецелочисленные типы данных не лучшим образом используют ресурсы памяти. Тем не менее, такой подход обеспечивает точное пред­ставление чисел с десятичной точкой. Общее количество цифр и количество цифр после запятой для десятичных типов строго фиксировано.

приблизительные (Approximately). Этот вид нецелочисленных типов данных обеспечивает хранение чисел в огромном диапазоне от бесконечно малых до предельно больших. Хотя такой подход обеспечивает оп­тимальное использование предоставляемых ресурсов (в отличие от десятич­ного вида), тем не менее, точность вычислений оставляет желать лучшего.

К десятичным типам данных относятся decimal(m,n) и numeric(m,n). Эти два типа данных эквивалентны. При использовании десятичных типов данных необходимо явно указать общее количество цифр (в сумме до и после запятой), а также отдельно ко­личество цифр после запятой. Общее количество цифр указывается с помо­щью аргумента m, тогда как количество цифр после запятой задается аргу­ментом n. Значение аргумента р может лежать в пределах от 1 до 38. От значения р зависит количество байт, отводимых для хранения чисел, мини­мум отводится 2 байта, максимум– 17 байт. Аргумент s может достигать значения аргумента р, но не должен превышать его. По умолчанию значение аргумента s устанавливается равным 0, т. е. не поддерживается хранение цифр после запятой. Значение параметра m, если оно не задано явно, может динамически изменяться в зависимости от величины хранимого значения.

К приблизительным типам данных относятся перечисленные ниже типы данных:

float (n). Аргумент n задает количество бит, необходимых для хранения мантиссы. Максимальное значение этого аргумента равно 53. Если значение n находится в диапазоне от 1 до 24, то для хранения числа используется 4 байта. Для диапазона от 25 до 53 отводится 8 байт. В первом случае дости­гается точность до 7 цифр, тогда как во втором – до 15 цифр. Как видно, особой экономии от применения минимального значения n нет. То есть для хранения, например, числа со значением n не более 9, будет отводиться столько же памяти, как и для хранения числа со значением n до 24. Если нет особых причин, то для обеспечения дополнительной точности лучше исполь­зовать крайние значения n – 24 или 53. С другой стороны, повышенная точ­ность требует дополнительных ресурсов процессора при вычислениях.

real. Этот тип данных является частным случаем типа float, а конкретнее это компактная запись типа данных float (24) со всеми вытекающими последст­виями. Для хранения числа используется 4 байта, обеспечивается точность до 7 цифр. Само число может лежать в диапазоне от -3,40Е+38 до 3,40Е+38.

 

Денежные типы данных

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

money. Для хранения данных этого типа отводится 8 байт, что обеспечивает представление значений в диапазоне от -263 до 263. Для хранения более точных или больших значений можно использовать тип данных decimal или numeric.

smallmoney. Этот тип данных является компактным вариантом типа данных money. Для данных используется 4 байта, что обеспечивает хранение чисел в диапазоне от -231 до 231.

 

Типы данных «дата и время»

Как следует из названия, типы данных этой группы служат для хранений ин­формации о дате и времени. Конечно, для хранения этих данных можно легко использовать символьные строки, однако применение специализированных ти­пов дает определенные преимущества. Например, можно легко сравнивать даты, выделять из них отдельные части (год, месяц, число, день недели, часы и т. д.), вычитать и складывать их и т. д. Поддержка этих операций встроена в Transact-SQL. При работе с символьными строками необходимо будет реализовывать дополнительные алгоритмы обработки данных.

В SQL Server имеются следующие типы данных для хранения информации о дате и времени:

datetime. Для хранения данных применяются два 4-байтовых блока.

smalldatetime. Тип данных smalldatetime может использоваться для хранения дат в диапа­зоне от 1 января 1900 года до 6 июня 2079 года.

date. Для хранения даты. Тип данных позволяет хранить информацию о датах в интервале от 1 января 0001 года до 31 декабря 9999 года.

time. Для хранения только времени.

Хотя данные о времени хранятся в специальном формате, тем не менее, для удобства была обеспечена работа с данными в символьном виде. Сервер автоматически выполняет преобразование символьной строки в специ­альный формат. В Transact-SQL допускается при­менение множества  форматов даты и времени

 

dd.mm.yy;

dd/mm/yy;

dd/mm/yy;

yyyy-mm-dd;

hh:mm:ss;

dd.mm.yy hh:mm:ss

 

Двоичные типы данных

Двоичные типы данных предназначены для хранения больших блоков двоичных данных. Некоторые данные в SQL Server хранятся не в виде целых чисел или строк, а непосредственно в виде блока двоичных данных. Одним из двоичных типов является image. Существует ряд задач, которые требуют наличия блоков данных большего размера. Например, при использовании SQL Server для хранения документов необходимо обеспечить возможность хранения данных объемом в несколько мегабайт. Для удовлетворения этих требований был раз­работан тип данных image, позволяющий хранить до 2 Гбайт данных (251–1). Максимальный размер не задается явно и зависит от реального размера данных. Тип данных image относится к так называемым «тяжелым» типам данных.

 

Строковые типы данных

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

К строковым типам SQL Server относятся типы данных;

char (n). Этот тип данных позволяет хранить до 8000 знаков в формате ASCII. Этот формат использует 1 байт для представления каждого символа, что обеспечивает поддержку 256 символов. Аргумент n опреде­ляет количество символов, которое разрешается хранить. Независимо от то­го, какая реальная длина строки, всегда будет резервироваться фиксирован­ное количество памяти. Если в столбце таблицы зарезервировано 4000 сим­волов, а реально хранится всего 50 символов, то неиспользуемое прост­ранство будет заполнено пробелами.

varchar (n). Как и предыдущий, тип данных varchar обеспечивает хране­ние блоков данных до 8000 символов в стандарте ASCII. Отличие этого типа данных от char в том, что аргумент n определяет не конкретный объем памя­ти, выделяемый для хранения значений, а максимально возможное количест­во символов. Реальный же размер памяти, занимаемый значением, соответ­ствует его длине.

nchar (n). В отличие от двух предыдущих типов, тип данных nchar обеспе­чивает хранение текста в формате Unicode. В стандарте Unicode для представления каждого символа используется 2 байта, что обеспечивает поддержку 65 536 символов. Это значительный прогресс по сравнению со стандартом ASCII. Теперь пользователи могут легко хранить тексты на любом националь­ном языке. Однако, как и для типов данных char и varchar, для типа nchar может быть выделен максимальный блок размером в 8000 байт. Так как один символ представлен 2 байтами, то максимальная длина текстового блока типа данных nchar составляет 4000 символов. Выделение памяти для объектов типа данных nchar выполняется аналогично типу char. To есть отводится фиксиро­ванный размер памяти независимо от реального размера данных.

nvarchar (n). Этот тип данных относится к типу nchar так же, как тип varchar относится к типу char. To есть тип данных nvarchar обеспечивает хранение текстовых строк в формате Unicode максимальной длиной до 4000 символов (8000 байт). При этом для объектов этого типа отводится ров­но столько памяти, сколько реально занимает значение.

 

Текстовые типы данных

Типы данных этой группы очень близки строковым типам данных. Обе группы типов данных предназначены для хранения текстовых блоков данных. Однако размер строковых типов данных ограничен 8000 байтами. Конечно, для боль­шинства задач этого достаточно, не все же иногда бывает необходимо хранить довольно большие блоки текстовой информации. Например, можно с уверенно­стью сказать, что сохранить несколько сотен страниц книги в любом строковом типе данных не получится. Решением этой задачи будет использование тексто­вых типов данных, обеспечивающих хранение очень больших объемов текста.

В SQL Server поддерживаются следующие текстовые типы данных:

text. Этот тип данных обеспечивает хранение блоков текстовых данных размером до 231-1 символов. При этом для представления каж­дого символа используется 1 байт.

ntext. В отличие от предыдущего типа данных, применение типа данных ntext позволяет работать с двухбайтовым представлением символов. Это позволяет хранить текст практически на лю­бом национальном языке. Хотя для типа данных ntext, как и для типа дан­ных text, доступно 230-1 символов.

Работа с текстовыми типами данных отличается от работы с остальными типами данных. Как и тип image, типы данных text и ntext относятся к так называемым «тяжелым» типам данных.

 

Специальные типы данных

К специальным относятся типы данных, которые нельзя отнести ни к одному из предыдущих типов данных. Специальные типы данных имеют специфическое назначение, часто узкоспециализированное.

В SQL Server имеются следующие специальные типы данных:

timestamp. Этот тип данных переводится как «временной штамп» и предназначен для отслеживания последовательности изменения строк таблиц базы дан­ных. Тип данных timestamp не имеет ничего общего с типами данных datetime и smalidatetime. Кроме того, пользователь не обладает возможно­стью изменять или явно задавать значения столбцов с типом данных times­tamp. В любой таблице может быть создан столбец, имеющий тип данных timestamp. Однако в каждой таблице может быть только один такой столбец. Каждый раз при вставке новой или изменении существующей строки в табли­це со столбцом timestamp сервер автоматически вставляет в столбец timestamp новое значение. Это значение уникально в пределах базы данных и монотонно возрастает на 1 каждый раз при вставке очередного значения. Уникальность значения timestamp в пределах базы данных гарантирует, что ни в каких лю­бых двух таблицах базы данных не будет двух строк с одинаковым значением в столбце timestamp. Для хранения типа данных timestamp требуется 8 байт.

bit. Как не трудно догадаться, этот тип данных занимает всего 1 бит и часто служит для хранения значений, которые могут принимать всего два значе­ния. Примером может служить пол (мужской/женский). Также тип данных bit активно используется для представления значений "Уеs/Nо", "On/off", "True/False" и т. д.

 

2.2. Ограничения целостности

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

Ограничения целостности (constraints) – это механизм контроля значений, кото­рые могут храниться в полях строки. В SQL Server поддерживаются сле­дующие ограничения целостности:

 

Ограничение целостности Null

В полях таблицы SQL Server можно хранить не только конкретные значе­ния, но и неопределенные значения. Неопределенное значение это не то же самое, что пустое значение для текстовых строк или 0 – для числовых значе­ний. Если в поле хранится неопределенное значение, можно считать, что в поле не хранится ничего. Неопределенное значение обозначается как Null.

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

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

 

Ограничение целостности Unique

Иногда бывает необходимо обеспечить уникальность значений, которые могут храниться в столбце таблицы. Часто это делается не только для того, чтобы обеспечить уникальность идентификационных номеров, но и для гарантии, что в таблицу не было введено несколько одинаковых значений. Например, в таб­лице стран необходимо гарантировать уникальность названия стран. Иначе для ссылки на одно и то же государство могут быть использованы два разных иден­тификационных номера.

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

 

Ограничение целостности Primary Key

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

В одной таблице может существовать множество ключей. Один из таких воз­можных ключей может быть выбран в качестве первичного ключа (Primary Key).

Для столбца или столбцов, выбранных в качестве первичного ключа, SQL Server автоматически обеспечивает уникальность значений. Однако одним из требований к первичному ключу является запрещение хране­ния неопределенных значений (null). В этом и есть основное отличие ограни­чений целостности Unique и Primary Key. Кроме того, в таблице можно опреде­лить лишь один первичный ключ, но можно задать множество ограничений целостности Unique. Последнее должно использоваться, если необходимо гаран­тировать уникальность значений, но первичный ключ уже определен.

Обычно в качестве первичного ключа выбираются столбцы небольшого размера, занимающие всего несколько байтов. Чаще всего используются столбцы с цело­численными типами данных – tiayint, smallint, int и bigint. Тем не менее, разрешается создание первичного ключа и на основе столбцов любых других типов данных.

 

Ограничение целостности Default

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

Ограничение записывается так: default(значение).

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

Например, пусть в таблице Address имеется поле CityID, где указывается город, в котором прожи­вает сотрудник. Во многих организациях большинство сотрудников живут в том же городе, в котором находится организация. Поставив в поле CityID значение по умолчанию (defaults), соответствующее названию нужного города, можно избавить пользователей от многократного указания одних и тех же значений. Только при добавлении сведений о сотруднике, проживающем в другом городе, нужно будет указать явно другое значение. Это лишь один из примеров использования значений по умолчанию.

 

Ограничение целостности Check

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

Например, если в столбце cost хранятся числовые значения и необходимо га­рантировать, что они не будут превышать 100 и будут не менее 15, то условие будет выглядеть следующим образом:

 

cost>=15 and cost<=l00

 

или

 

cost between 15 and 100

 

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

Предположим, что в поле mincost хранится себестоимость товара, в поле maxcost – макси­мально разрешенная цена товара, а в поле cost – цена товара, установленная менеджером для конкретного покупателя. Необходимо ввести ограничение, ко­торое бы автоматически контролировало значение в поле cost в пределах между mincost и maxcost. Логическое условие будет выглядеть следующим образом:

 

cost>=mincost  and cost<=maxcost

 

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

 

2.3. Использование автонумерации

Чтобы создать поле таблицы для автоматической нумерации, для него необхо­димо установить свойство Identity. При этом пользователь должен задать начальное значение и шаг прираще­ния. Первая вставляемая в таблицу строка будет иметь значение, указанное как начальное. При вставке каждой следующей строки значение в поле со свойством Identity станет монотонно увеличиваться на шаг приращения. Каждая вновь вставляемая строка будет иметь уникальное в пределах таблицы значение. Даже если вы удалите десять последних вставленных строк, новая строка будет иметь уникальное значение, не пересекающееся со значением ни одной удаленной стро­ки. Таким образом, обеспечивается уникальность значений в таблице.

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

 

2.4. Создание таблиц средствами Transact-SQL

Создание таблиц в SQL Server производится с помощью команды:

 

create table имя_таблицы

(описание поля 1,

описание поля 2, …,

описание поля n)

 

Имя_таблицы. Имя, которое будет присвоено таблице. Оно не должно превышать 128 символов.

 

Описание поля. Эта конструкция определяет свойства столбца., имеющей синтаксис:

 

имя_поля тип_поля [default(значение)][identity(seed,increment)][ограничения_целостности check]

 

Рассмотрим подробно назначение и использование каждого из аргументов.

Имя_поля. Имя, которое будет иметь столбец. Имя должно быть уникальным в пределах таблицы. Если в имени столбца применяются запрещенные символы, такие как пробел, %, * и т. п, то имя столбца при создании должно быть заключено в квадратные скобки.

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

default (значение). С помощью этого аргумента можно определить значение по умолчанию, ко­торое будет присваиваться соответствующему полю строки, если при ее вставке пользователь явно не указал конкретное значение. Значение по умолчанию не может быть применено к столбцам с типом данных timestamp или с установленным свойством identity. В качестве значений по умолча­нию могут применяться константы, системные переменные и функции, а также любые выражения, построенные на их основе. Использование ссылок на другие столбцы запрещено.

identity (seed, increment). Указание этого аргумента предписывает создать столбец с поддержкой авто­матической нумерации. При вставке новой строки в таблицу SQL Server 2000 автоматически обеспечивает вставку в поле identity уникального значения, монотонно увеличивающегося при вставке каждой новой строки. Свойство identity может быть установлено только для столбцов с типом данных int, smallint, tinyint, decimal (p, 0) и numeric (р, 0). В пределах одной таблицы можно создать только один столбец с установленным свойством identity. Естественно, определение значения по умолчанию для столбца с установлен­ным свойством identity невозможно. По умолчанию нумерация строк в таб­лице начинается с 1 и при вставке каждой новой строки это значение увели­чивается на 1. Однако можно указать иное первоначальное значение и шаг приращения. Первоначально значение указывается с помощью аргумента seed, а шаг приращения – с помощью аргумента increment.

ограничения_целостности check. Эта конструкция определяет ограничения целостности на уровне столбца. Для одного столбца может быть определено множество ограничений целостности или не определено ни одного. Конструкция  имеет следующий синтаксис:

 

([NULL/NOT NULL][PRIMARY KEY][UNIQUE][CHECK(логическое выражение)]

 

Рассмотрим назначение и использование вышеперечисленных аргументов.

NULL / NOT NULL. С помощью этих опций определяется, будет ли возможным хранение в столбце неопределенных значений, или нет. Для одного столбца допускается применение только одного из аргументов. При указании Null хранение не­определенных значений разрешено, тогда как при указании Nот Null запре­щается.

PRIMARY КЕУ. При создании столбца с этим параметром будет создан первичный ключ. В таблице разрешается создавать только один первичный ключ.

UNIQUE. Это ключевое слово обеспечивает создание для столбца ограничения целост­ности Unique, обеспечивающего уникальность значений. Ограничение цело­стности Unique может быть определено на основе более чем одного столбца. Для одного и того же столбца не могут быть одновременно установлены ограниче­ния целостности Primary Key и Unique.

CHECK (логическое_выражение). Ключевое слово check говорит о том, что далее в скобках будет указано про­верочное ограничение целостности, которое задается в виде логического вы­ражения. Логическое выражение задается с помощью аргумента логическое_выражение. Если логическое выражение возвращает значение true, то операция вставки или изменения значений в соответствующем столбце разрешается. Если же возвращается значение false, to выдается соответст­вующее сообщение об ошибке и операция отменяется,

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

 

Примеры создания таблиц

В базе данных Personal создать таблицу Sotrudniki с полями Табельный номер, Фамилия, Имя, Отчество, Дата рождения, Оклад, Премия.

Табельный номер является ключевым, поле Отчество можно оставлять незаполненным, Оклад должен находиться в диапазоне от 800 до 1500 грн, поле премия вычисляется как 20% от оклада.

 

use personal

create table sotrudniki

(tabnom int not null primary key,

fam varchar(20) not null,

imya varchar(15) not null,

otch varchar(20) null,

datar datetime not null,

oklad decimal(6,2) check (oklad>=800 and oklad <=1500))

 

2.5. Связывание таблиц

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

 

Ограничение References (Foreign Key)

Для создания внешнего ключа в таблице SQL Server используется ограничение целостности References (Foreign Key). Таким образом, обеспечивается ссылочная целостность данных.

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

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

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

Само ограничение записывается после списка всех полей таблицы в виде:

 

CONSTRAINT имя_ограничения FOREIGN KEY(имя_поля)REFERENCES главная_таблица (имя_поля) [ON DELETE CASCADE/NO ACTION] [ON UPDATE CASCADE/NO ACTION]

 

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

Foreign Key(имя поля) - имя подчиненной таблицы для связывания с главной таблицей.

ON DELETE CASCADE/NO ACTION. Предписывает использовать для соответствующего внешнего ключа ограничение целостности Cascade или No Action на удаление строк в главной таблице. Таким образом, удаление строк в главной таблице будет приводить к удалению соответствующих строк (при указании cascade) в создаваемой таблице или отмене операции удаления строки главной таблицы (при указании No action).

ON UPDATE CASCADE/NO ACTION. Предписывает использовать для соответствующего внешнего ключа ограни­чение целостности Cascade или No Action на изменение строк и главной таб­лице. Таким образом, изменение первичного ключа в главной таблице будет приводить к модификации соответствующих строк (при указании cascade) в создаваемой таблице или отмене операции изменения строки главной табли­цы (при указании no action).

 

Пример создания подчиненной таблицы

В базе данных Personal создать подчиненную таблицу Uchet с полями Номер операции, Табельный номер, Дата, Количество изделий. Таблица хранит информацию о выработке продукции каждым сотрудником по датам и является подчиненной для таблицы Sotrudniki.

Поле Номер операции является счетчиком, поле Табельный номер является внешним ключом с каскадным обновлением и удалением данных, поле дата по умолчанию заполняется текущей датой, поле Количество изделий должно позволять вводить только положительные значения и по умолчанию равно 0.

 

use personal

create table uchet

(id_uchet int identity (1,1) not null,

tabnom int not null,

data date not null default (getdate()),

kolvo int not null default (0) check (kolvo>=0),

constraint tabnom foreign key(tabnom) references sotrudniki(tabnom) on delete cascade on update cascade)

 

2.7. Изменение таблицы

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

Для изменения таблиц с помощью Transact-SQL используется команда Alter Table, позволяющая выполнить следующие операции:

 

Изменение поля

alter table имя_таблицы

alter column имя_поля новый_тип [null/not null]

 

Новый_тип. Определяет тип данных, который должен иметь столбец после изменения. Если используется нецелочисленный тип данных, то дополнительно можно указать, какое количество десятичных цифр будет храниться и сколько цифр их них будет после запятой.

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

NULL/NOT NULL. С помощью этих опций определяется, возможно ли будет хранение в изме­ненном столбце неопределенных значений (Null). При указании Null хране­ние неопределенных значений разрешено, тогда как при указании Not Null запрещается. Если при изменении столбца не было явно указано, будет ли он хранить значения Null или нет, то остается старое значение.

Добавление поля

alter table имя_таблицы

add

описание поля

 

Описание_поля. Эта конструкция определяет свойства столбца. Ее синтаксис и использование были рассмотрены в разделе «Определение столбцов».

 

Удаление поля

alter table имя_таблицы

drop column  имя_поля1, имя_поля2, …, имя_поляN

 

Эта конструкция применяется для удаления из таблицы столбцов. Нельзя удалить:

- реплицированные столбцы;

- индексированные столбцы;

- столбцы, для которых определены ограничения целостности Check, Unique, Primary Key или References;

- столбцы, для которых определено значение по умолчанию (Default)

 

Добавление ограничений

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

 

Задание ограничения на уникальность значений.

 

alter table имя_таблицы

add constraint имя_ограничения unique(имя_поля1, имя_поля2, ...) - поле или набор полей уникальные

 

Например. Сделаем уникальным поле imya в таблице sotrudniki

 

alter table sotrudniki

add constraint imyaUnique unique(imya)

 

Задание ограничения на допустимость значений

 

alter table имя_таблицы

add constraint имя_ограничения check(условие) - задает условия на допустимые значения

 

Например. Поле kolvo в таблице uchet не может превышать 1000.

 

alter table uchet

add constraint kolvo1000 check (kolvo<=1000)

 

Ограничение на значение по умолчанию для поля

 

alter table имя_таблицы

add constraint имя_ограничения default значение_по умолчанию for имя_поля

 

Например. Для поля fam таблицы sotrudniki зададим значение по умолчанию 'Иванов'

 

alter table sotrudniki

add constraint famDefault default 'Иванов' for fam

 

Задание для поля признака ключевого поля

 

alter table имя_таблицы

add constraint имя_ограничения primary key (поле1, поле2, ...) - делает поле или поля ключевыми

 

Например. Для поля otch таблицы sotrudniki задаим признак ключевого поля

 

alter table sotrudniki

add constraint otchPrimary primary key (otch)

 

Удаление ограничений

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

 

alter table имя_таблицы

drop constraint имя_ограничения

 

2.8. Удаление таблицы

Для удаления таблиц предназначена команда, имеющая синтаксис:

 

drop table имя_таблицы

 

Существуют определенные ограничения на удаление таблиц. Нельзя удалить таблицу, если на нее с помощью ограничения целостности Foreign Key ссылает­ся одна или более таблиц. Необходимо сначала удалить ограничения целостно­сти (или связанные таблицы), и только после этого удалять таблицу.

При удалении таблицы уничтожаются все ограничения целостности (constraints), которые были определены для нее. Рекомендуется создавать резервную копию базы данных перед удалением из нее объектов. Впоследствии при необходимости вы сможете вос­становить необходимые объекты из резервной копни

При удалении таблицы происходит также и удаление всех данных, хранимых в таблице. SQL Server не требует, чтобы сначала из таблицы были удалены все строки. Перед удалением таблицы следует убедиться, что в ней не содержит­ся нужных данных.

 

2.9. Управление таблицами средствами утилиты "SQL Server Manager Studio"

Работа с таблицами средствами Transact-SQL хотя и предлагает пользователям широкие возможности, требует знания синтаксиса команд и хранимых процедур, а также сравнительно больших затрат времени на выполнение тех или иных действий. Более удобным для выполнения большинства действий является ис­пользование утилиты, которая предлагает пользователям удобный графический интерфейс для выполнения различных действий по управлению таблицами,

 

Создание таблицы

Первая операция, которую выполняет пользователь в отношении таблицы (если конечно не считать этапа разработки), – это ее создание.

В окне утилиты в дереве объектов СУБД раскройте папку БД, в которой вы хотите создать таблицу, вызовите контекстное меню папки Таблицы и выберите команду "Создать таблицу". В результате в правой части откроется окно ввода структуры таблицы.

 

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

В нижней части окна для каждого поля задают дополнительные параметры:

- значение по умолчанию (ограничение default);

- параметры счетчика в группе "Спецификация идентификатора" (ограничение identity).

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

Для задания ограничений для поля в его контекстном меню выберите команду "Проверочные ограничения". В появившемся окне щелкните на кнопке Добавить и в поле Выражение введите текст ограничения (аналог ограничения Check).

Для задания признака уникальности поля в его контекстном меню выберите команду "Индексы и ключи". В появившемся окне щелкните на кнопке Добавить, укажите нужное поле и установите параметр Уникальное в значение "Да".

После того, как будут сконфигурированы параметры всех столбцов, необходимо сохранить сконфигурированную таб­лицу. Для этого достаточно нажать кнопку Сохранить, расположенную в панели инстру­ментов. При этом будет выведено окно, с помощью которого следу­ет ввести имя, которое будет присвоено сконфигурированной таблице. Затем мож­но закрыть окно создания таблицы.

В результате в дереве объектов появится значок созданной таблицы.

 

Изменение таблицы

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

Обратите внимание, что по умолчанию в SQL Server выключена возможность внесения изменений в таблицу. Для ее включения выполните команду Сервис - Параметры. В окне нстроек выберите в левой части узел ЕКонструкторы и выключите флажок "Запретить сохранение изменений, требующих повторного создания таблицы".

 

Переименование таблицы

В контекстном меню таблицы выберите команду Переименовать, введите новое имя и нажмите Enter.

 

Удаление таблицы

Для удалении таблицы достаточно выбрать нуж­ную таблицу и нажать либо клавишу Delete, либо в контекстном меню таблицы выбрать команду Удалить. Однако прежде чем производить эту операцию, не будет лишним посмотреть, не ссылаются ли на удаляемую таблицу другие объекты базы дан­ных, такие как представления, функции или процедуры. Для этого в контекстном меню таблицы выберите команду "Просмотреть зависимости".

Пока у таблицы есть зависящие от нее элементы вы не сможете ее удалить.

 

3. Работа с таблицами в MySQL

 

Типы данных

При создании таблицы для каждого поля нужно указывать тип данных. Данные в основном похожи на SQL Server, но есть небольшие ограничения:

Целые типы: tinyint, smallint, mediumint, integer,  bigint.

Вещественные типы: float(m,n) , double(m,n) , decimal(m,n)

Строковые: char(n), varchar(n)

Текстовые (тяжелые): tinytext, mediumtext, text, longblob, tinyblob, mediumblob, blob, longblob.

Дата/время: datetime, date, time, timestamp.

Двоичные данные: binary (n), varbinary (n).

 

Ограничения целостности

В MySQL имеются все ограничения кроме check. Запись ограничений такая же как и в SQL Server. Единственное отличие – ограничение default записывается без скобок:

 

default значение

 

Создание счетчика

Для создания поля-счетчика нужно указать ключевое слово auto_increment.

 

Создание таблицы

В целом команда похожа на команду SQL Server, за исключением одного отличия: в конце команды указывается тип "движка" таблицы. В MySQL имеется несколько движков, каждый из них имеет свои особенности. Однако "предпочтительным" движком является движок InnoDB. Следовательно команда имеет вид:

 

Создание таблиц в SQL Server производится с помощью команды:

 

create table имя_таблицы

(описание поля 1,

описание поля 2, …,

описание поля n)

engine=innodb;

 Примеры создания таблиц

В базе данных Personal создать таблицу Sotrudniki с полями Табельный номер, Фамилия, Имя, Отчество, Дата рождения, Оклад.

Табельный номер является ключевым, поле Отчество можно оставлять незаполненным.

 

use personal

create table sotrudniki

(tabnom integer not null primary key,

 fam varchar(20) not null,

 imya varchar(15) not null,

 otch varchar(20) null,

 datar date not null,

 oklad decimal (6,2))

engine=innodb;

 

Связывание таблиц

В базе данных Personal создать подчиненную таблицу Uchet с полями Номер операции, Табельный номер, Дата, Количество изделий. Таблица хранит информацию о выработке продукции каждым сотрудником по датам и является подчиненной для таблицы Sotrudniki.

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

Таблицы связаны по полю tabnom с каскадным удалением данных.

 

use personal

create table uchet

(id_uchet integer primary key auto_increment,

tabnom integer not null,

data date not null,

kolvo integer not null default 0,

CONSTRAINT tabnom FOREIGN KEY (tabnom) REFERENCES sotrudniki(tabnom) on delete cascade)

engine=innodb;

Изменении и удаление таблиц

Любые операции над таблицами выполняются с помощью таких же команд, как и для SQL Server. Есть несколько отличий.

 

Добавление нового поля

Для добавление поля необходимо использовать команду вида:

 

alter table имя_таблицы

add column имя_поля тип список_ограничений

 

Например. В таблицу sotrudniki добавим новое поле Пол (pol), обязательное для заполнения и со значением по умолчанию 'м'.

 

alter table sotrudniki

add column pol varchar(1) not null default 'м';

 

Добавление или удаление ограничений

Для добавление или удаление ограничений необходимо использовать команду вида:

 

alter table имя_таблицы

modify column имя_поля тип список_ограничений

 

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

 

Например. Для поля imya зададим значение по умолчанию 'Иван'.

 

alter table sotrudniki

modify column imya varchar(15) default 'Иван'

 

Например. Для поля imya удалим значение по умолчанию 'Иван'.

 

alter table sotrudniki

modify column imya varchar(15)

 

Например. Для поля otch добавим ограничение NULL.

 

alter table sotrudniki

modify column otch varchar(15) null

 

Например. Для поля otch удалим ограничение NULL.

 

alter table sotrudniki

modify column otch varchar(15)

 

Например. Для поля tabnom добавим ограничение unique.

 

alter table sotrudniki

modify column tabnom integer unique

 

Например. Для поля tabnom удалим ограничение unique.

 

alter table sotrudniki

modify column tabnom integer

 

Работа с таблицами с помощью утилиты EMS SQL Manager for MySQL

 

Создание таблицы

Для создания таблицы двойным щелчком откройте нужную базу данных в левой части окна. В итоге отроет список узла с объектами базы данных . В этом списке в контекстном меню папки "Таблицы" выберите команду "Новый объект: таблица". После этого в правой части откроется окно для создания новой таблицы. На закладке "Таблица" введите имя таблицы и обязательно укажите тип таблицы. СУБД MySQL может работать с несколькими типами таблиц, но тип "InnoDB" является "предпочтительным" типом, который обеспечивает максимальную скорость и безопасность. Этот тип является желательным при создании таблиц.

 

 

На вкладке «Поля» можно создать нужные поля таблицы.

 

 

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

 

 

Можно указать имя поля, его тип и размер. С помощью флажков можно задать дополнительные параметры:

• Не Null – поле обязательное для заполнения;
• Беззнаковый – можно вводить только положительные числа;
• Уникальный – в поле не допускаются повторяющиеся значения;
• Автоинкремент – поле будет счетчиком;
• Первичный ключ – поле буде ключевым.

После ввода всех полей щелкните на команде Компилировать.

В результате будет создана новая таблица. На закладке "Данные" таблицу можно будет заполнить. 

 

Связывание таблиц

Откройте подчиненную таблицу. Перейдите на закладку "Внешние ключи" и в контекстном меню выберите команду "Новый внешний ключ". В новом окне укажите поле подчиненной таблицы для связывания, ниже укажите имя главной таблицы и ее поле для связывания. Еще ниже укажите правило при удалении или обновлении данных в главной таблице (cascade / no action).

 Внесение изменений в таблицу

Для изменения параметров таблицы двойным щелчкомм в левой части окна откройте нужную базу данных, а потом в папке "Таблицы" откройте нужную таблицу. В правой части откроется окно с параметрами таблицы. На закладках окна внесите нужные изменения и щелкните на команде "Компилировать".

 

Удаление таблицы

В контекстном меню таблицы выберите команду команду «Удалить таблицу».

 

4. Работа с таблицами в SQLite

 

СУБД SQLite по принципам работы похожа на работу с MySQL. Основные отличия:

- набор типов значительно сокращен: integer, float(n,m), numeric (n,m), time, date, char(n), varchar(n), text, blob;

- при создании поля-счетчика указывают ключевое слово autoincrement;

- при создании таблицы не нужно указывать тип "движка" таблицы engine.

- СУБД поддерживает ограничение check.