Создание, модификация и удаление объектов базы данных с использованием SQL

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

 

Порядок выполнения работы

 

Изучение языка баз данных

 

1. Изучить язык определения данных SQL, включая операторы создания таблиц (CREATE TABLE), создания представлений (CREATE VIEW), модификации таблиц (ALTER TABLE), удаления таблиц (DROP TABLE), удаления представлений (DROP VIEW), а также операторы создания индексов (CREATE INDEX) и удаления индексов (DROP INDEX). Изучить типы ограничений и способы их представления на языке SQL.

2. Изучить операторы манипулирования данными, связанные с курсором, включая оператор объявления курсора (DECLARE CURSOR), оператор открытия курсора (OPEN), оператор чтения очередной строки курсора (FETCH), оператор позиционного удаления (DELETE), оператор позиционной модификации (UPDATE), оператор закрытия курсора (CLOSE).

3. Изучить одиночные операторы манипулирования данными, включая) операторы поискового удаления (DELETE) и поисковой модификации (UPDATE), а также оператор включения (INSERT).

4. Изучить интегрированную утилиту SQL Enterprise Manager, а также системные хранимые процедуры с точки зрения их использования для просмотра объектов базы данных, создания и удаления таблиц. В частности, изучить работу с системной хранимой процедурой sp_help, используемой для получения информации о базе данных и объектах базы данных.

 

Работа непосредственно с таблицей

 

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

2. Составить оператор создания таблицы с учетом приведенных в задании ограничений, выполнить данный оператор на SQL-сервере с использованием клиентской утилиты. Просмотреть результат выполнения данного оператора с помощью системной хранимой процедуры sp_help или клиентской утилиты SQL-EM.

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

4. Изменить одну или несколько строк таблицы с использованием оператора поисковой модификации. Просмотреть измененную таблицу.

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

6. Добавить столбец в таблицу с использованием оператора модификации таблицы. Просмотреть измененную таблицу.

7. Удалить ограничение из таблицы с использованием оператора модификации таблицы. Просмотреть результат выполнения оператора.

 

Работа с курсором

1. Объявить скроллируемый курсор в соответствии со спецификацией курсора из лабораторного задания.

2. Открыть курсор.

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

4. Удалить n-ю строку результирующей таблицы с использованием оператора позиционного удаления (число n задается преподавателем). Просмотреть базовую таблицу и сравнить ее c предыдущим вариантом.

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

6. Закрыть курсор.

Работа с представлением

1. В соответствии с заданием составить оператор создания представления, выполнить данный оператор на SQL-сервере с использованием клиентской утилиты. Просмотреть результат выполнения данного оператора с помощью системной хранимой процедуры sp_help или клиентской утилиты SQL-EM. Просмотреть представляемую таблицу.

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

3. Изменить несколько строк представляемой таблицы с использованием оператора поисковой модификации. Просмотреть представляемую и базовую таблицы и сравнить их с предыдущими вариантами этих таблиц.

4. Удалить несколько строк из представляемой таблицы с использованием оператора поискового удаления. Просмотреть представляемую и базовую таблицы и сравнить их с предыдущими вариантами этих таблиц.

5. Удалить представление с использованием оператора удаления представления.

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

 

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

2. Составить и выполнить какой-нибудь запрос к индексированной таблице.

3. Удалить индекс.

4. Удалить базовую таблицу с использованием оператора удаления таблицы.

Содержание отчета

1. Задание;

2. Операторы создания и удаления таблицы;

3. Операторы создания и удаления представления;

4. Операторы объявления и закрытия курсора;

5. Операторы создания и удаления индекса;

6. Операторы манипулирования данными, относящиеся к базовой, представляемой и результирующей таблицам;

7. Исходная базовая таблица;

8. Исходная представляемая таблица;

9. Исходная результирующая таблица курсора;

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

 

Типы данных Transact-SQL

Символьные типы данных

· CHAR[(n)] - cтроки фиксированной длины, где n - число символов в строке;

· VARCHAR[(n)] - строки переменной длины, где n - максимальное число символов в строке;

· TEXT - строки потенциально неограниченного размера (до 2 Гб текста в строке).

В данном случае 1£n£255. Символьные столбцы, допускающие пустые значения (NULL), хранятся как столбцы переменной длины.

Примеры определений столбцов и типов данных:

 

name VARCHAR(40)

state CHAR(2)

description CHAR(50) NULL

 

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

· BINARY(n) - двоичные строки фиксированной длины, где n - число двоичных символов в строке;

· VARBINARY(n) - двоичные строки переменной длины, где n - максимальное число двоичных символов в строке;

· IMAGE - большие двоичные строки (изображения до 2 Гб в строке).

В данном случае 1£n£255.

Пример задания двоичного столбца:

 

bin_column BINARY(4) NOT NULL

 

Типы данных даты

SQL Server поддерживает два типа обозначения даты и времени при хранении: DATETIME и SMALLDATETIME. Последний менее точный и охватывает меньший диапазон дат, но зато позволяет экономить место на диске.

SQL Server поддерживает различные форматы ввода даты. По умолчанию он работает с датами в формате xx/yy/zz как с последовательностями месяц/день/год. Точность представления времени при использовании DATETIME - 3 миллисекунды, а при использовании SMALLDATETIME - 1 минута. Пример:

 

Формат ввода: 4/15/99

Значение DATETIME: Apr 15 1999 12:00:00:000 AM

Значение SMALLDATETIME: Apr 15 1999 12:00 AM

Логический тип данных

SQL Server поддерживает логический тип данных BIT для столбцов флагов, имеющих значение 1 или 0.

 

Числовые типы данных

Числовые типы данных разбиваются на четыре основные категории:

*  целые, включающие INT, SMALLINT и TINYINT.

*  данные с плавающей точкой, включающие FLOAT и REAL.

*  данные с фиксированной точкой - NUMERIC и DECIMAL

*  денежные типы данных - MONEY и SMALLMONEY.

 

Целые типы данных

Характеристика	INT	SMALLINT	TINYINT
Минимальное значение	-2		0
Максимальное значение			255
Объем памяти	4 байта	2 байта	1 байт

Типы данных с плавающей точкой

Характеристика	FLOAT	REAL
Минимальное значение	±2.23E-308	±1.18E-38
Максимальное значение	±1.79E308	±3.40E38
Точность	до 15 значащих цифр	до 7 значащих цифр
Объем памяти	8 байтов	4 байта

Спецификатор типа FLOAT имеет вид FLOAT[(p)], где p - точность.

 

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

Эти типы данных вводится описателями DECIMAL[(p,s)] и NUMERIC[(p,s)], где p - точность, s - масштаб. Они являются синонимами и взаимозаменяемы, но только NUMERIC может использоваться в комбинации со столбцами IDENTITY. Точность - это число значащих цифр, масштаб - число цифр после десятичной точки.

Пример: NUMERIC(7,2).

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

 

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

Характеристика	MONEY	SMALLMONEY
Диапазон	922337203685477.5808	214748.3647
Размер памяти	8 байтов	4 байта

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

Оператор создания таблицы имеет следующий синтаксис:

<оператор создания таблицы>::= CREATE TABLE <имя таблицы> (<элемент таблицы>[{,<элемент таблицы>}...])

<элемент таблицы>::=<определение столбца> | <определение ограничения целостности>

Каждая таблица БД имеет простое и квалифицированное (уточненное) имена. В качестве квалификатора имени выступает “идентификатор полномочий”.

 Квалифицированное имя таблицы имеет вид: <идентификатор полномочий>.<простое имя>

 

Определение столбца

 

<определение столбца>::= <имя столбца><тип данных>[<раздел умолчания>] [{<ограничение целостности столбца>}...]

<раздел умолчания>::= DEFAULT {<литерал> | USER | NULL}

<ограничение целостности столбца>::= NOT NULL[<спецификация уникальности>] | <спецификация ссылок> | CHECK (<условие поиска>)

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

Если значение столбца по умолчанию не специфицировано, и в разделе ограничений целостности столбца указано NOT NULL (т.е. наличие неопределенных значений запрещено), то попытка занести в таблицу строку с неспецифицированным значением данного столбца приведет к ошибке.

Если ограничение NOT NULL не указано, и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL.

Ограничения целостности столбца в принципе сходны с ограничениями целостности таблицы и рассмотрены ниже.