Выборка и модификация данных в MS SQL Server

Создание таблиц и управление ими

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

Таблица — это набор данных о некоторой сущности, например покупателях, заказах или складских запасах. Таблица состоит из набора столбцов. Каждый столбец представляет атрибут табличных данных. Например, атрибутом сущности «заказ» может быть дата заказа. Каждый экземпляр данных представлен в таблице записью или строкой (иногда их также называют кортежами).

На этом этапе разработки базы данных вы должны собрать все сведения, необходимые для создания таблиц. В идеале все необходимые параметры таблицы определяется одновременно, в том числе PRIMARY KEY и другие ограничения. Сначала мы создадим простые таблицы (в определение которых входит имя, столбцы, типы данных, возможность пустых значений и, где нужно, значения столбцов).

Возможность хранения в столбце пустых значений определяет, могут ли соответствующие поля таблицы хранить пустые значения. Пустое значение (NULL), ноль, пробел и символьная строка нулевой длины (например, «») — это разные вещи. Пустое значение показывает, что в ячейку не были введены данные. Обычно пустое значение свидетельствует о том, что значение не известно или не определено. Например, пустое значение в столбце Price таблицы Titles из базы данных Publications означает не отсутствие цены на книгу, а то, что цена не известна или пока не установлена.

В целом, следует избегать возможности хранения пустых значений, поскольку они усложняют обработку запросов и обновлений, кроме того, их нельзя использовать в столбах с некоторыми параметрами, например с ограничением PRIMARY KEY. Операция сравнения двух пустых значений, а также пустого значения с любым другим значением возвращает неизвестное значение, поскольку значение любого NULL неизвестно. Пустые значения нельзя применять в качестве идентификаторов, позволяющих отличить одну строку таблицы от другой. Кроме того, стоит исключать пустые значения при вычислениях, поскольку некоторые вычисления (например, расчет среднего) могут быть неточны, если в них используется столбец, содержащий NULL. Когда требуется создать столбец, значения которого не известны, зачастую можно определить для него значение по умолчанию. Например, столбец Phone таблицы Authors из базы данных Publications не допускает пустых значений. В этом столбце определено значение по умолчанию UNKNOWN. Если в строке со сведениями об авторе номер телефона не указан, то значение в столбце Phone равно UNKNOWN. Ниже определение значений по умолчанию обсуждается более подробно.

Если при добавлении строки не указано значение поля, допускающего пустые значения, SQL Server присваивает ему пустое значение (если не определено значение или объект по умолчанию). В поле, определенном посредством ключевого слова NULL, пользователь может явно ввести пустое значение, при этом не играют роли ни тип данных поля, ни определенное для него значение по умолчанию. NULL нельзя заключать в кавычки, поскольку в этом случае оно интерпретируется как строка символов, а не как пустое значение.

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

Возможность ввода пустых значений определяется во время определения столбца, создания или модификации таблицы. Независимо от того, допускает ли столбец пустые значения, из-за сложностей обработки пустых значений в SQL Server в определении всех столбцов всегда приходится использовать ключевые слова NULL или NOT NULL. Если столбец допускает пустые значения, то применяется ключевое слово NULL, если нет — то NOT NULL.

Следующий SQL-сценарий создает посредством оператора CREATE TABLE таблицу Person. Столбцы Person_ID и LastName не допускают пустых значений, а столбец FirstName — допускает:

CREATE TABLE Persons

(

Person_ID char(4) NOT NULL,

FirstName varchar(30) NULL,

LastName varchar(30) NOT NULL

)

GO

Все поля записи должны быть заполнены (даже с использованием пустых значений). Иногда требуется загрузить в таблицу строку, но значение какого-либо поля не известно (или не существует). Если поле допускает пустые значения, можно загрузить строку с пустым значением. Поскольку поля, допускающие пустые значения, нежелательны, лучше определить для поля значение по умолчанию (определить ограничение DEFAULT). Например, в качестве значения по умолчанию, которое используется, если значение не указано, для числовых полей обычно задают ноль, а для символьных — «N/A».

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

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

Есть два способа определения значения по умолчанию:

• определить его во время создания самой таблицы (как часть определения таблицы);

• добавить это определение к существующей таблице (у любого столбца таблицы оно может быть только одно).

В следующем SQL-сценарии с помощью оператора CREATE TABLE создается таблица Persons. Ни один из трех ее столбцов не допускает пустых значений. Однако на самом деле столбец FirstName позволяет ввести неизвестное имя, поскольку в его определении содержится определение значения по умолчанию. Для его определения в операторе CREATE TABLE используется ключевое слово DEFAULT.

CREATE TABLE Persons

(

Person_ID char(4) NOT NULL,

FirstName varchar(30) NOT NULL DEFAULT 'UnKnown',

LastName varchar(30) NOT NULL

)

GO

Существующее определение значения по умолчанию можно модифицировать или удалить. При модификации определения значения по умолчанию с помощью Transact-SQL необходимо сначала удалить существующее определение DEFAULT а затем создать новое определение «с нуля».

Невозможно создать определения DEFAULT в следующих столбцах:

• с типом данных timestamp;

• со свойствами IDENTITY или ROWGUIDCOL;

• с существующим определением или объектом по умолчанию.

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

Когда определение DEFAULT применяется к существующему столбцу, SQL Server применяет (по умолчанию) новое определение только к новым строкам, добавленным к таблице. Существующие данные, вставленные во время действия старого определения значения по умолчанию, не затрагиваются. Однако при добавлении к существующей таблице нового столбца можно указать серверу на вставку в существующие строки таблицы не пустых значений, а значений по умолчанию (заданные определением умолчания для этого столбца).

Автоматическая нумерация и столбцы с идентификатором

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

Для любой таблицы, значения которой уникальны для всех компьютеров мира, объединенных в сеть, можно создать столбец с глобально уникальным идентификатором. Столбец с гарантированно уникальными значениями полезен, когда необходимо свести похожие данные из нескольких серверов (например, в системах выписки счетов, данные о которых находятся в различных частях транснациональной корпорации, расположенных в различных странах). Когда на центральном узле выполняется сведение данных для консолидации и составления отчетов, использование глобально уникальных значений исключает совпадение идентификаторов и номеров счета у покупателей из различных стран. SQL Server использует столбцы с глобально уникальным идентификатором при репликации сведением для обеспечения уникальности идентификатора строк среди нескольких копий одной таблицы.

В любой таблице может быть создан только один столбец с идентификатором или глобально уникальным идентификатором.

Свойство IDENTITY

Столбцы с идентификатором можно реализовать с помощью свойства IDENTITY, которое позволяет разработчику приложений задать номер идентификатора первой строки, добавленной в таблицу (свойство Identity Seed, исходное значение идентификатора) и инкремент (свойство Identity Increment), который будет прибавляться к исходному значению для определения последовательных значений идентификатора. SQL Server автоматически генерирует следующее значение идентификатора, прибавляя инкремент к исходному значению.

Определяя столбец с идентификатором при помощи свойства IDENTITY, необходимо учитывать следующие моменты:

• в таблице разрешен только один столбец со свойством IDENTITY. Возможные типы данных для этого столбца: decimal, int, numeric, smallint, bigint или tinyint;

• можно задать исходное значение и инкремент идентификатора. Для обоих значение по умолчанию равно 1;

• столбец с идентификатором не доложен допускать пустых значений, в нем запрещены определения или объект DEFAULT;

• когда установлено свойство IDENTITY, на этот столбец можно ссылаться в списке выбора с помощью ключевого слова IDENTITYCOL;

• чтобы узнать, есть ли в таблице столбец со свойством IDENTITY, предназначена функция OBJECTPROPERTY; функция COLUMN PROPERTY позволяет определить имя столбца IDENTITY.

Следующий сценарий создает таблицу Persons с помощью оператора языка Transact-SQL CREATE TABLE. Ни один столбец не допускает пустых значений. Кроме того, столбец Person_ID является столбцом с идентификатором. Исходное значение идентификатора равно 101, инкремент — 1.

CREATE TABLE Persons

(

Person_ID SMALLINT IDENTITY(101,1) NOT NULL,

PersonName VARCHAR(50) NOT NULL

)

GO

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

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

Хотя свойство IDENTITY автоматизирует нумерацию строк таблицы, значения идентификатора в различных таблицах, содержащих столбцы с идентификаторами, могут совпадать. Свойство IDENTITY гарантированно уникально в пределах той таблицы, в которой оно используется. Если разрабатываемое нами приложение должно генерировать столбец с идентификатором, уникальным в пределах всей базы данных (или всех баз данных на всех серверах сети), следует использовать свойство ROWGUIDCOL, тип данных uniqueidenlifier и функцию NEWID.

Когда мы определяем столбец с глобально уникальным идентификатором при помощи свойства ROWGUIDCOL, нужно помнить, что:

• в таблице может быть только один столбец со свойством ROWGUIDCOL, к тому же этот столбец должен быть определен с помощью типа данных uniqueidentifier;

• SQL Server не генерирует значения этого столбца автоматически; для добавления глобально уникального значения нужно создать в столбце определение DEFAULT, которое генерирует глобально уникальное значение с помощью функции NEWID;

• после того как свойство ROWGUIDCOL установлено, на этот столбец разрешается ссылаться с помощью ключевого слова ROWGUIDCOL, аналогично тому, как на столбец со свойством IDENTITY ссылаются с помощью ключевого слова IDENTITYCOL;

• функция OBJECTPROPERTY позволяет определить, есть ли в таблице столбец со свойством ROWGUIDCOL, а функция COLUMNPROPERTY - имя этого столбца;

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

В следующем сценарии создается таблица Persons с помощью оператора CREATE TABLE. Столбец Person_ID автоматически генерирует GUID для каждой новой строки, добавляемой к таблице:

CREATE TABLE Persons

(

Person_ID UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,

PersonName VARCHAR(60) NOT NULL

)

GO

Методы создания таблиц

SQL Server предоставляет несколько методов создания таблиц: оператор языка Transact-SQL CREATE TABLE, консоль SQL Server Management Studio и Database Designer, который доступен через SQL Server Management Studio.

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

CREATE TABLE Ships

(

ShipID char(4),

ShipName varchar(40)

)

GO

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

Таблицы можно создавать непосредственно в SQL Server Management Studio. Чтобы создать таблицу в существующей базе данных, нужно раскройте консоль до базы данных, далее раскрыть базу данных и щелкнуть вспомогательной кнопкой на узел Tables, а затем щелкните New Table. В появившемся окне New Table нужно ввести всю необходимую для определения таблицы информацию.

В SQL Server Management Studio средства Database Designer позволяют добавить таблицу к диаграмме базы данных, отредактировать структуру таблицы или связать ее с другими таблицами, показанными на диаграмме. К диаграмме можно добавлять как существующие, так и новые таблицы, еще не определенные в базе данных. В качестве альтернативы разрешается строить новые таблицы и модифицировать существующие с помощью Table Designer. Этот визуальный инструмент позволяет конструировать и визуализировать таблицу в базе данных, к которой мы подключились.

Управление таблицами в базе данных SQL Server

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

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

В SQL Server существует несколько методов просмотра характеристик и зависимостей таблицы:

• для просмотра определения таблицы используется системная хранимая процедура sp_help или SQL Server Management для просмотра свойств таблицы;

• чтобы просмотреть зависимости таблицы, можно воспользоваться системной хранимой процедурой sp_depends или параметром Display Dependencies в SQL Server Management Studio;

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

После создания таблицы многие параметры, определенные во время ее создания, разрешается изменять. Например, добавлять, модифицировать и удалять столбцы, в том числе изменять имя, длину, тип данных, точность (точность дробной части), возможность ввода пустых значений — последнее с некоторыми ограничениями. Более подробно об этом рассказано в SQL Server Books Online, здесь мы просто перечислим все возможные операции:

• добавление и удаление ограничения PRIMARY KEY и FOREIGN KEY;

• добавление и удаление ограничения UNIQUE и CHECK, а также определение ограничений (и объектов) DEFAULT;

• добавление и удаление столбцов с идентификатором с помощью свойств IDENTITY или ROWGUIDCOL. Разрешается добавлять и удалять свойство ROWGUIDCOL у существующих столбцов, но при этом в любой момент времени возможен только один столбец со свойством ROWGUIDCOL;

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

Кроме того, разрешается изменить имя или владельца таблицы. При этом также придется исправить имя таблицы во всех триггерах, хранимых процедурах, сценариях Transact-SQL и других программах, в которых используется старое имя или владелец таблицы.

Некоторые типы изменений свойств таблицы перечислены в следующей таблице. Здесь также указаны методы внесения этих изменений. Более подробную информацию об этом можно найти в SQL Server Books Online.

Тип изменения	Методика внесения
Переименование таблицы	Системная хранимая процедура sp_rename, параметр Rename в SQL Server Management Studio
Изменение владельца таблицы	Системная хранимая процедура sp_changeobjectowner
Модификация свойств столбца	Оператор ALTER TABLE, функция Table Designer в SQL Server Management Studio
Переименование столбца	Системная хранимая процедура sp_rename, функция Table Designer в SQL Server Management Studio

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

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

Удаляют таблицу из базы данных SQL Server с помощью оператора DROP TABLE или SQL Server Management Studio (который удаляет таблицу из узла Tables).

DROP TABLE Ships

GO

Дата добавления: 2014-01-11