Выражение может содержать в себе имена других столбцов, арифметические операции и встроенные функции T-SQL.
Описание ограничений табличного уровня также может начинаться с ключевого слова CONSTRAINT и имени ограничения (которые не являются обязательными) после чего следует непосредственно само ограничение.
В связи с тем, что типы столбцов и ограничения были подробно описаны в предыдущей лабораторной работе, мы не будем повторно описывать их. Заметим только, что кроме встроенных типов можно использовать пользовательские. Для этого используют следующую команду:
CREATE DOMAIN имя_пользовательского_типа имя_встроенного_типа ограничения
Таким образом, пользовательский тип строится на основе некоторого встроенного типа, на который наложены ограничения уровня атрибутов. Рассмотрим пример:
CREATE DOMAIN item_id char(5) NOT NULL CHECK (item_id like ‘[A-Z][A-Z][0-9][0-9][0-9]’)
В данном примере, мы создали пользовательский тип под именем item_id, который базируется на встроенном типе char(5), т.е. представляет собой текстовую строку из 5 символов, при этом первые 2 символа являются буквами латинского алфавита (заглавными), а последние три – цифры от 0 до 9. Кроме того, столбцы типа item_id не могут содержать пустых значений, что задается ограничением NOT NULL.
Завершим изучение команды Create table примеров sql-кода создания таблиц, которые мы проектировали на первых 2-х лабораторных работах:
CREATE TABLE worker
(
wrkr_id item_id PRIMARY KEY,
wrkr_name varchar(30) NOT NULL UNIQUE,
h_rate smallmoney,
skill_type varchar(10),
supv_id item_id
)
CREATE TABLE building
(
bldg_id item_id PRIMARY KEY,
address varchar(40),
bldg_type varchar(9) DEFAULT ‘Офис’ CHECK (bldg_type in ‘Офис’, ‘Склад’, ‘Жилой дом’, ‘Магазин’),
status decimal(1) DEFAULT 1 CHECK (status between 0 and 4)
)
CREATE TABLE assignment
(
wrkr_id item_id,
bldg_id item_id,
start_day datetime,
num_days smallint CHECK (num_days > 0),
CONSTRAINT assignment_pk PRIMARY KEY (wrkr_id, bldg_id),
CONSTRAINT assignment_fk_wrkr FOREIGN KEY wrkr_id REFERENCES worker ON DELETE SET NULL,
CONSTRAINT assignment_fk_bldg FOREIGN KEY bldg_id REFERENCES building ON DELETE SET NULL
)
После того, как таблицы созданы можно отредактировать их структуру или удалить их. Удаление производиться с помощью команды: DROP TABLE имя_таблицы. Команда изменения структуры таблицы имеет более сложный синтаксис:
ALTER TABLE имя_таблицы
{
alter column описание_столбца,
add описание_столбца,
drop column имя_столбца,
add описание_ограничения,
drop constraint имя_ограничения,
enable | disable trigger имя_триггера
}
Рассмотрим более подробно те команды, которые используются внутри alter table:
· Alter column – используется для изменения существующих в таблице столбцов. После нее следует новое описание некоторого столбца, при этом имя столбца сохраняется;
· Add – добавляет новый столбец в таблицу. Может быть добавлен как обычный, так и вычисляемый столбец. Кроме того, с помощью данной команды можно добавить новое ограничение уровня таблицы;
· Drop column – удаляет столбец с указанным именем;
· Drop constraint – удаляет ограничение с указанным именем;
· Enable trigger или Disable trigger – позволяют включить или временно отключить выполнение указанного триггера. Распространяется только на те триггеры, которые связаны с данной таблицей.
Для работы с данными T-SQL использует команды SELECT, INSERT, UPDATE и DELETE, которые подробно рассмотрены в [7].
Вопросы к допуску
1. Что называется таблицей базы данных? Для чего используются таблицы?
2. Какие таблицы называют нормализованными?
3. Что называется первичным ключом таблицы?
4. Что называется альтернативным ключом?
5. Что называется внешним ключом таблицы?
6. Сформулируйте правило ссылочной целостности для внешних ключей;
7. Перечислите ограничения, накладываемые на таблицу в реляционной модели данных;
8. Какие виды ограничений используются в MS SQL Server?
Контрольные вопросы
1. Какие средства для создания таблиц используются в MS SQL Server 2000?
2. Опишите процесс создания таблицы с помощью мастера;
3. Опишите окно конструктора таблиц. На какие части оно делится? Для чего предназначены эти части?
4. Опишите процесс создания таблицы с помощью конструктора;
5. Какая команда T-SQL используется для создания таблиц? Какой синтаксис она имеет?
6. Как создавать вычисляемые столбцы в таблицах?
7. Каким образом можно создать пользовательский тип средствами T-SQL?
8. Как средствами T-SQL можно связать таблицы между собой?
9. Какой командой T-SQL можно удалять таблицы?
10. Как отредактировать структуру таблицы с помощью T-SQL?
11. Как добавить и удалить столбцы в уже созданной таблице?
12. Как добавить и удалить в уже созданную таблицу ограничения уровня таблицы?
Лабораторная работа №4:
«Создание представлений в MS SQL Server 2000»
Задание:
- Создать представления с помощью T-SQL, используя Query Analyzer;
- Продемонстрировать умение работать с представлениями в Query Analyzer – просмотр, редактирование и удаление таблиц с помощью T-SQL;
- Продемонстрировать работу с представлениями в Enterprise Manager – создание, редактирование, просмотр.
Теоретический материал:
Представления (view) – можно определить как виртуальные таблицы, содержимое которых генерируется динамически в результате выполнения некоторого запроса. По сути, представления это и есть хранимый именованный запрос. В базе данных сохраняется только структура представления, информация генерируется автоматически, при вызове представления. Следует отметить, что, изменяя данные в представлениях, мы изменяем их и в таблицах, служащих источником для представлений. Т.е. можно говорить о том, что в представлениях хранятся «живые» данные.
Представления можно использовать для извлечения каких-либо данных из таблиц базы данных, однако наиболее распространенным сценарием использования представлений является организация доступа пользователей к базе данных. Зададим себе вопрос: «Зачем использовать представления, если можно работать непосредственно с таблицами?» Дело в том, что таблицы служат для хранения информации о некотором объекте. Но пользователь, решая свои задачи, может использовать далеко не всю информацию, хранящуюся в таблице (например, отбирая ее по некоторому критерию) или использовать информацию, хранящуюся в нескольких таблицах. Пользователю могут потребоваться некоторые сводные данные за какой-либо период. Таблицы не способны предоставить требуемую функциональность, необходимо создавать запросы. Если какие-либо запросы приходится создавать и исполнять достаточно часто – оптимальным вариантом будет создать их один раз и сохранить в базе данных, т.е. создать представления.
Заметим также, что представления являются удобным инструментом разграничения доступа. Предположим, что необходимо разрешить пользователю работать только с некоторыми строками таблицы (набора таблиц), а доступ к остальным строкам для него должен быть запрещен. Механизмы разрешений в MS SQL Server 2000 не распространяются на строки таблиц. Таким образом, данную задачу можно решить, создав представление и дав права пользователю на работу с представлением, а не непосредственно с таблицей. Следует отметить, что подобная практика является весьма распространенной.
Рассмотрим задачу создания представлений. Существует три способа:
· Использование мастера Create View wizard;
· Использование конструктора, встроенного в Enterprise Manager;
· С помощью T-SQL;
Первый способ предлагает автоматическое создание представлений. Мастер проведет Вас через ряд диалоговых окон, в которых представление будет создаваться по шагам. Диалоговые окна имеют интуитивно-понятный интерфейс, поэтому создать представление может даже малоопытный человек. Отрицательным моментов является то, что мастер способен создавать только относительно простые представления.
Использование конструктора позволяет создавать более гибкие представления. Окно конструктора можно условно разделить на 4 части:
· Источники данных – в данной части располагаются таблицы и представления на основе которых создается данное представление. Можно добавлять и удалять таблицы с помощью команд контекстного меню или кнопок на панели инструментов. Здесь же можно указать столбцы, которые должны войти в создаваемое представление;
· Вторая сверху область предназначена для установки характеристик представления. Здесь можно дать псевдонимы (другие имена) столбцам, задать сортировку и ее порядок для конкретных столбцов, наложить условия на строки данных, и т.п.;
· Третья область – область кода SQL. В ней динамически отображаются в SQL-код команды SELECT все сделанные в первых двух частях изменения;
· Последняя часть – грид, динамически показывает результат выполнения представления.
Фактически, конструктором являются только первые две части. Область SQL и область данных можно отключать и снова включать, используя кнопки на панели инструментов.
Использование конструктора требует от создателя представлений более серьезных знаний о работе с базами данных и создании запросов, в частности, нежели использования мастера. Но и в результате можно создавать такие представления, которые не способен сгенерировать мастер. Важным положительным фактором является и то, что конструктор запрос в MS SQL Server 2000 практически аналогичен конструктору запросов в настольной СУБД той же фирмы – MS Access. Это позволяет в некоторой степени облегчить процесс перехода от одной СУБД к другой.
Последний способ создания представлений – с помощью T-SQL применяется либо IT-профессионалами, привыкшими работать с базой данных на языке SQL, либо в том случае, когда необходимое представление невозможно создать первыми двумя способами в силу их меньшей гибкости.
В языке T-SQL для создания представлений применяется команда create view, имеющая следующий синтаксис:
CREATE VIEW имя_представления
AS запрос_sql
[WITH CHECK OPTION]
Как несложно заметить, основную часть команды занимает sql-запрос, формирующий информацию, отображаемую представлением. В представлениях разрешено применять практически все конструкции команды SELECT, за исключением ключевых слов INTO, COMPUTE, COMPUTE BY. Более подробно, процесс создания sql-запросов и конструкции команды select были рассмотрены в [7].
Необязательная конструкция with check option позволяет гарантировать, что для строк представления не будет разрешено выполнение изменений, которые могут привести к исчезновению строки из представления. Например, если в команде where запроса было задано условие age>18, то сервер будет запрещать для представления выполнения изменений, которые устанавливают для столбца age значения большие, чем 18.
Для модификации представлений в T-SQL применяют команду ALTER VIEW, синтаксис которой полностью аналогичен синтаксису команды CREATE VIEW. Т.е. модификация представления фактически представляет собой переписывание его заново. В Enterprise Manager для этого вызывается окно редактирования представления, в котором записан его код на языке T-SQL, в который можно внести изменения.
Для удаления представлений используют команду drop, имеющую следующий синтаксис:
DROP VIEW имя_представления
Рассмотрим пример создания представления на языке T-SQL:
CREATE VIEW w_skills_345
AS
Select wrkr_name, skill_type, h_rate
From worker inner join assignment on (worker.wrkr_id = assignment.wrkr_id)
Where bldg_id = 345
В данном примере мы создаем представление, содержащее информацию о рабочих, назначенных на работы в здание с кодовым номером 345.
Вопросы к допуску
1. Что называется представлением?
2. Для решения каких задач применяют представления?
3. В каком виде хранится представление в базе данных MS SQL Server 2000?
4. Какие ограничения накладываются на представление в MS SQL Server 2000?
5. Перечислите набор команд SQL-92, используемый для создания запросов на выборку;
Контрольные вопросы
1. Какие способы для создания представлений предлагаются в MS SQL Server 2000?
2. Опишите процесс создания представления с помощью мастера Create View Wizard;
3. На какие части делится окно конструктора представлений? Опишите эти части, укажите их назначение;
4. Опишите процесс создания представления с помощью конструктора;
5. Какая команда T-SQL используется для создания представлений?
6. Для чего используется инструкция with check option?
7. Как создавать вычислимые столбцы в представлениях?
8. Как можно задать собственные имена для столбцов представления?
9. Какие способы применяются для редактирования представления?
10. Какими способами можно удалить представление?
Лабораторная работа №5:
«Разработка SQL-сценариев»
Задание:
- Создать несколько сценариев на языке T-SQL, используя Query Analyzer;
- Продемонстрировать работу сценариев в среде Query Analyzer.
Теоретический материал:
Для решения различных задач обработки информации в базе данных, разработчики ПО создают внешние приложения для работы пользователя с базой данных. При этом используются сторонние среды разработки и языки программирования. Следует отметить, что разработчикам при этом создавать алгоритмы обработки данных, использовать API для соединения и работы с сервером БД и решать проблему синхронизации данных.
MS SQL Server 2000 предлагает другой путь – разработку сценариев обработки данных, которые будут выполняться непосредственно на сервере и могут быть вызваны из внешней программы или командной строки. Такие сценарии получили название sql-сценариев. Они имеют собственное имя и хранятся в отдельных файлах с расширением «.sql».
Для создания sql-сценариев в язык T-SQL были введены операторы, с помощью которых можно создавать различные программные конструкции. Рассмотрим эти операторы более подробно:
Прежде всего, следует выделить операторы exec и go. Первый из них, позволяет запустить некоторый sql-сценарий. Данный оператор может быть вызван как отдельно, так и внутри некоторого другого сценария. Синтаксис оператора:
Exec имя_сценария.sql
Следующий оператор – оператор go, позволяет разделять сценарий на пакеты. Под пакетом в T-SQL понимается набор операторов, одновременно посылаемых на сервер для исполнения. Деление на пакеты помимо простого удобства разделения сценария на составные части, позволяет в одном и том же сценарии решать такие задачи, как создание объекта, его обработка, вывод результатов обработки и уничтожение объекта. Если бы механизм пакетов отсутствовал, невозможно было бы приступить к обработке объекта, т.к. он был бы создан на сервере только после завершения работы сценария.
Работая с пакетами, необходимо соблюдать следующие правила:
- В пределах одного пакета нельзя модифицировать таблицу (командой alter table) и обращаться к новым столбцам;
- Команды create view, create proc, create trigger и create default не могут соседствовать с другими операторами в пределах одного пакета. Практически, данное правило означает, что данные команды должны завершаться оператором go;
Для хранения промежуточной информации в языке T-SQL используют временные объекты. Использование временных объектов удобно тем, что не нужно заботиться об их удалении из памяти, MS SQL Server 2000 сделает это автоматически. К подобным объектам относятся переменные и временные таблицы. Переменные используют для хранения атомарных значений, а временные таблицы – для хранения наборов строк.
Для создания переменной используют команду declare. Команды имеет следующий синтаксис: