🗊Презентация Создание таблиц БД

Создание таблиц БД Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу, не содержащую записей. Очевидно, что данные в нее можно внести, например, с помощью команды INSERT. В команде CREATE TABLE определяется имя таблицы, и набор имен полей. Кроме того, этой же командой оговариваются типы данных и длины полей.

Синтаксис команды CREATE TABLE следующий: Синтаксис команды CREATE TABLE следующий: CREATE TABLE <имя таблицы> (<имя поля1> <тип данных> [(<длина>)], (<имя поля2> <тип данных> [(<длина>)]), …).

Пример: Пример: CREATE TABLE STUDENTS (NOM_ZACH INTEGER, SFAM CHAR (20), SNAME CHAR (10), STIP DECIMAL)

Числовые типы Числовые типы Tочные числовые типы К категории точных числовых типов в SQL относятся те типы, значения которых точно представляют числа. Типы данных этой категории распадаются на две части: целые типы ( INTEGER и SMALLINT ) и типы, допускающие наличие дробной части ( NUMERIC и DECIMAL ).   целочисленные: tinyint 0-255, smallint (от -32 768 до 32 767), int (от -2,147,483,648 до 2,147,483,647) и bigint (от -2^63 до 2^63 ); десятичные: decimal и numeric (это - два названия одного и того же типа); денежные: money (от -2^63 до 2^63 - с точностью 4 знака после запятой) и smallmoney (от -214748.3648 до +214748.3647).; с плавающей запятой: float (от -1.79E + 308 до 1.79E + 308) и real (от -3.40E + 38 до 3.40E + 38).

DECIMAL [(точность[,масштаб])] Параметр точность указывает максимальное количество цифр вводимых данных этого типа (до и после десятичной точки в сумме), а параметр масштаб – максимальное количество цифр, расположенных после десятичной точки. DECIMAL [(точность[,масштаб])] Параметр точность указывает максимальное количество цифр вводимых данных этого типа (до и после десятичной точки в сумме), а параметр масштаб – максимальное количество цифр, расположенных после десятичной точки.

Строковые типы В SQL Server предусмотрены две дублирующих разновидности полей для представления текстовых данных: поля Unicode и не-Unicode. Unicode - типы данных начинаются символом n (от слова national, то есть с поддержкой национальных символов).

Всего в SQL Server предусмотрены следующие типы для текстовых данных: Всего в SQL Server предусмотрены следующие типы для текстовых данных: ·        char/nchar - строковые данные фиксированной длины; ·        varchar/nvarchar - строковые данные переменной длины.

При использовании типа Char значения длиной короче заданной дополняются пробелами до указанной длины. Максимальное значение длины – 8000 символов. При использовании типа Char значения длиной короче заданной дополняются пробелами до указанной длины. Максимальное значение длины – 8000 символов. При использовании типа VarChar значения длиной короче заданной не дополняются пробелами.

datetime (8 байт, точность до 3,33 миллисекунд); datetime (8 байт, точность до 3,33 миллисекунд); smalldatetime (4 байта, точность до минуты). В большинстве приложений вполне хватает smalldatetime;

Логический тип данных - хранит значения вида true/false (единица/ноль). Логический тип данных - хранит значения вида true/false (единица/ноль). В SQL Server он представлен типом данных boolean.

Преобразование типов Для выполнения преобразований SQL Server содержит функции CONVERT и CAST, с помощью которых значения одного типа преобразовываются в значения другого типа, если такие изменения вообще возможны. CONVERT и CAST могут быть взаимозаменяемыми. CAST(выражение AS тип_данных) CONVERT(тип_данных[(длина)], выражение)

Пример: SELECT ‘сегодня ‘ + CONVERT(VARCHAR(11),GETDATE()) CAST('1977.01.07‘ AS Datetime)

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

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

Создание ограничений

Декларативные ограничения при создании таблиц При создании таблиц могут быть заданы декларативные ограничения целостности атрибутов: значения по умолчанию (DEFAULT), задание обязательности или необязательности значений (NULL или NOT NULL), условия проверки значения (CHECK), задание уникальность столбца (UNIQUE) .

Например, на значение стипендии может быть наложено ограничение (стипендия должна находиться в пределах от 500 до 750 тысяч рублей) по умолчанию значение стипендии равно 500 тыс. руб. Например, на значение стипендии может быть наложено ограничение (стипендия должна находиться в пределах от 500 до 750 тысяч рублей) по умолчанию значение стипендии равно 500 тыс. руб. STIP MONEY DEFAULT 700 CHECK(STIP >=700 AND <=750)

Возраст сотрудника должен быть Возраст сотрудника должен быть не менее 18 лет: BIRTH_DAY DATE CHECK(DATEDIFF(YEAR,GETDATE(),BIRTH_DAY)>=18)

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

Пример ограничения на уровне таблицы Пример ограничения на уровне таблицы CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, vcName varchar(50) NOT NULL, dBirthDate datetime, dDocDate datetime, CONSTRAINT CK_birthdate CHECK (dDocDate>dBirthDate AND <=getdate())

Часто для поля или группы полей требуется реализовать ограничение, связанное c уникальностью значений. Часто для поля или группы полей требуется реализовать ограничение, связанное c уникальностью значений. В этом случае в ограничение поля (группы) при создании таблицы помещают ключевое слово UNIQUE. Можно определить группу полей как уникальную, например, в таблице USP уникальными должны быть комбинации полей NOM_ZACH и PKOD: UNIQUE (NOM_ZACH,PKOD)

Ограничение PRIMARY KEY действует аналогично UNIQUE, но для таблицы должен быть определен только один первичный ключ, а уникальных полей может быть несколько. Ограничение PRIMARY KEY действует аналогично UNIQUE, но для таблицы должен быть определен только один первичный ключ, а уникальных полей может быть несколько. Первичный ключ может быть составным (как в таблице USP, где ключ состоит из атрибутов NOM_ZACH и PKOD). Для объявления составного первичного ключа требуется объявление на уровне таблицы.

PRIMARY KEY(NOM_ZACH, PKOD) PRIMARY KEY(NOM_ZACH, PKOD)

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

Таблица USP подчинена двум другим таблицам: SUBJECTS и STUDENTS. При этом таблица USP связана с таблицей STUDENTS обязательной связью. Таблица USP подчинена двум другим таблицам: SUBJECTS и STUDENTS. При этом таблица USP связана с таблицей STUDENTS обязательной связью. Каждому значению атрибута NOM_ZACH в таблице USP обязательно должно соответствовать ровно одно значение этого же атрибута в таблице STUDENTS. В таблице USP не может быть значений атрибута NOM_ZACH, которых нет в таблице STUDENTS. Связь с таблицей SUBJECTS также будет обязательной.

Ключ FOREIGN KEY ограничивает значения, которые можно ввести в БД так, чтобы заставить внешний и родительский ключи соответствовать принципу ссылочной целостности. Ключ FOREIGN KEY ограничивает значения, которые можно ввести в БД так, чтобы заставить внешний и родительский ключи соответствовать принципу ссылочной целостности. Синтаксис ограничения FORIGN KEY: FOREIGN KEY <список полей> REFERENCES <имя таблицы, содержащей родительский ключ>[список полей родительского ключа].

Создадим таблицу USP с полем NOM_ZACH, и PKOD определенными в качестве внешних ключей: Создадим таблицу USP с полем NOM_ZACH, и PKOD определенными в качестве внешних ключей: CREATE TABLE USP (NOM_ZACH INTEGER NOT NULL, PKOD INTEGER NOT NULL, TNUM INTEGER, UDATE DATE , MARK INTEGER, PRIMARY KEY(NOM_ZACH, PKOD) , FOREIGN KEY (NOM_ZACH) REFERENCES STUDENTS (NOM_ZACH), FOREIGN KEY (PKOD) REFERENCES SUBJECTS (PKOD))

Используя ограничения FOREIGN KEY, можно не указывать список полей родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. Используя ограничения FOREIGN KEY, можно не указывать список полей родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. CREATE TABLE USP (NOM_ZACH INTEGER NOT NULL FOREIGN KEY REFERENCES STUDENTS, PKOD INTEGER NOT NULL FOREIGN KEY REFERENCES SUBJECT, TNUM INTEGER, UDATE DATE , MARK INTEGER, PRIMARY KEY (NOM_ZACH,PKOD)); В случае употребления ключей со многими полями, обязательно выполнение условия, чтобы порядок полей во внешних и первичных ключах совпадал.

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

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

Итак, изменения в родительском ключе можно разделить на Итак, изменения в родительском ключе можно разделить на ограниченные (NO ACTION), каскадируемые (CASCADE), пустые (SET NULL) , устанавливающие значения по умолчанию (SET DEFAULT).

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

CREATE TABLE USP CREATE TABLE USP (NOM_ZACH INTEGER NOT NULL, PKOD INTEGER, TNUM INTEGER, UDATE DATE , MARK INTEGER, PRIMARY KEY(NOM_ZACH, PKOD) FOREIGN KEY (PKOD) REFERENCES SUBJECTS FOREIGN KEY (NOM_ZACH) REFERENCES STUDENTS ON UPDATE CASCADE)

Если данные о студенте удаляются, удаление их должно быть выполнено сначала в подчинённой (USP), а затем в главной таблице (STUDENTS). Если данные о студенте удаляются, удаление их должно быть выполнено сначала в подчинённой (USP), а затем в главной таблице (STUDENTS). В этом случае используется ограничение ON DELETE NO ACTION После этого при удалении данных о студенте из таблицы STUDENT команда не будет выполнена до тех пор, пока не будут удалены его данные из таблицы USP.

Изменение таблиц При необходимости в уже созданную таблицу можно внести изменения, например, добавить столбец. ALTER TABLE STUDENTS ADD S_TEL VARCHAR (20) NOT NULL

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

В таблицу могут быть добавлены и новые ограничения с помощью команды В таблицу могут быть добавлены и новые ограничения с помощью команды ADD CONSTRAINT <имя ограничения>. Имя ограничения состоит из краткого названия типа ограничения (например, PK для первичного ключа, ID для индекса), символа подчёркивания, имени поля или таблицы и порядкового номера ограничения данного типа, если к одному объекту задаётся несколько ограничений одного типа.

Примеры: 1. Для добавления ограничения, задающего значение по умолчанию: ALTER TABLE USP ADD CONSTRAINT Def_Mark DEFAULT 7 FOR MARK 2. Для добавления ограничения проверки значения: ALTER TABLE USP ADD CONSTRAINT Сh_Mark CHECK MARK IN (3,4,5) 3. Для добавления внешнего ключа (NOM_ZACH) в таблицу USP для связи с таблицей STUDENTS ALTER TABLE USP ADD CONSTRAINT FK_USP_STUDENTS FOREIGN KEY (NOM_ZACH) REFERENCES STUDENTS ON UPDATE CASCADE

Для получения информации об ограничениях используется системная Для получения информации об ограничениях используется системная процедура sp_helpconstraint имя_таблицы или sp_help имя ограничения.

Удаление столбцов и ограничений Из созданной таблицы можно удалить столбцы или ограничения. При удалении ограничений следует помнить, что выполнению команды могут помешать некоторые зависимости. Например, если столбец является первичным ключом, сервер не позволит удалить его до тех пор, пока не будет снято соответствующее ограничение. Если в другой таблице существует ссылка на столбец, сервер также не позволит удалить его до снятия ограничения. Примеры: ALTER TABLE USP DROP CONSTRAINT Ch_Mark ALTER TABLE USP DROP COLUMN Udate

Разрешение и запрет ограничений С помощью команды ALTER TABLE с предложениями ENABLE и DISABLE можно разрешать и запрещать действия ограничений, не удаляя их. ALTER TABLE таблица ENABLE | DISABLE CONSTRAINT имя ограничения

Модификация столбцов Иногда при создании таблиц делают неверные предположения относительно типа данных, которые собираются хранить в таблице. Неверный выбор приводит к неэффективному хранению данных или же данные могут оказаться слишком большими и не помещаться в столбцах. В таких ситуациях следует изменить исходное определение столбцов командой ALTER COLUMN. Пример: ALTER TABLE PREP ALTER COLUMN FIO varchar(50)

Удаление таблиц Удаление таблиц выполняется с помощью команды DROP TABLE. Для того чтобы иметь возможность удалить таблицу, пользователь должен быть ее владельцем. Кроме того, перед удалением SQL требует очистки таблицы от данных. Таким образом, таблица с находящимися в ней данными не может быть удалена. Перед удалением следует убедиться, что на таблицу не ссылается никакая другая таблица, и что она не используется в каком-либо представлении. Синтаксис команды удаления: DROP TABLE <имя таблицы>

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

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

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

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

Для создания уникальных (не содержащих повторяющихся значений) индексов используется ключевое слово UNIQUE в операторе CREATE INDEX (CREATE UNIQUE INDEX …). Для создания уникальных (не содержащих повторяющихся значений) индексов используется ключевое слово UNIQUE в операторе CREATE INDEX (CREATE UNIQUE INDEX …). Например, создать индекс c именем Ind_Tnum для поля TNUM таблицы SUBJECT: CREATE UNIQUE INDEX Ind_Tnum ON SUBJECT(TNUM)

Для удаления индекса используется команда Для удаления индекса используется команда DROP INDEX имя индекса Чтобы изменить индекс таблицы, необходимо удалить его и затем создать заново в соответствии с новым определением. Для получения информации о текущих индексах таблицы используется процедура sp_helpindex.

Кластеризованный индекс Использование опции Clustered index позволяет произвести так называемое кластерное индексирование, в результате чего будут отсортированы данные в самой таблице согласно порядку этого индекса, и вся добавляемая информация будет приводить к изменению физического порядка данных. При этом нужно учитывать, что в таблице может быть определён только один кластерный индекс. Синтаксис: CREATE CLUSTERED INDEX

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