close

Вход

Забыли?

вход по аккаунту

?

Rabota v srede SQL Server 2000 labor i kur rab

код для вставкиСкачать
ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
Государственное образовательное учреждение
высшего профессионального образования
САНКТ!ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ
РАБОТА В СРЕДЕ SQL Server 2000
Методические указания
к выполнению лабораторных и курсовых работ
Санкт!Петербург
2007
Составители: Н. В. Барклаевская, Г. С. Евсеев, Г. В. Преснякова,
А. В. Шахомиров
Рецензент кандидат технических наук Г. С. Бритов
Даны методические указания к выполнению лабораторных и кур!
совых работ с использованием среды SQL Server 2000 по дисципли!
нам «Базы данных», «Проектирование интеллектуальных компью!
терных систем».
Указания содержат основные сведения по созданию базы данных
и манипулированию данными с использованием языка запросов
TRANSACT!SQL. Приведено большое количество примеров, пред!
ложены задания для самостоятельного выполнения.
Предназначены для студентов специальности 23010200, а также
могут быть полезны студентам других специальностей.
Подготовлены кафедрой информационных систем и рекомендо!
ваны к изданию редакционно!издательским советом Санкт!Петер!
бургского государственного университета аэрокосмического прибо!
ростроения.
Редактор Г. Д. Бакастова
Верстальщик С. Б. Мацапура
Сдано в набор 29.01.07. Подписано к печати 27.03.07.
Формат 60х84 1/16. Бумага офсетная. Печать офсетная. Усл. печ. л.4,06.
Уч.!изд. л. 4,4. Тираж 150 экз. Заказ №
Редакционно!издательский центр ГУАП
190000, Санкт!Петербург, Б. Морская ул., 67
© ГУАП, 2007
2
Введение
Существуют два основных типа архитектуры приложений: архи!
тектура мэйнфреймов (или монолитная архитектура) и распределен!
ная архитектура. В монолитных системах все компоненты приложе!
ний (пользовательский интерфейс, логическая структура програм!
мы, проверка корректности вводимых данных и осуществление
доступа к данным) сосредоточены на мэйнфрейме.
Системы с распределенной архитектурой обычно распределяют
вычислительную нагрузку между разными компьютерами, объеди!
ненными в сеть. Наиболее распространенный вид распределенной
архитектуры – это клиент!серверная архитектура.
В архитектуре клиент!сервер все задачи, связанные с доступом к
данным, выполняются на центральном сервере. Особенностью архи!
тектуры клиент!сервер является то, что один процесс может запро!
сить информацию у другого процесса. В этой архитектуре вычисли!
тельная нагрузка распределена между клиентами и сервером. Под
клиентом понимается программное обеспечение, которое, с одной
стороны, взаимодействует с сервером баз данных, а с другой – с пользо!
вателем через графический интерфейс. При использовании этого типа
архитектуры приложение выполняет все задачи, связанные с интер!
фейсом пользователя, логикой работы программы и проверкой цело!
стности данных. Сервер отвечает за логику работы программы и про!
верку целостности данных.
На клиентской машине выполняются процессы, которые отвеча!
ют за составление запросов и представление полученных данных. На
сервере выполняются процессы, которые обрабатывают запросы и
отвечают на них. Одним из главных преимуществ архитектуры кли!
ент!сервер является то, что клиенту после его запроса к серверу баз
данных возвращается только результат выполнения этого запроса.
Другими словами, выборка данных происходит на сервере, а не на
клиентской машине. Вследствие этого значительно снижается сете!
вой трафик.
Клиент запрашивает данные или обращается к каким!нибудь дру!
гим службам сервера и предоставляет данные пользователю, часто с
использованием графического интерфейса пользователя (graphical
3
user interface – GUI). Отображение представляемых данных для
пользователя в нужном виде ! задача клиента. Поскольку на клиен!
тской машине выполняется программный код службы представле!
ния данных и частично других служб приложения, то в результате
освобождаются вычислительные ресурсы на сервере, которые смогут
быть использованы для работы с данными и выполнения запросов.
Архитектура SQL Server включает в себя клиентскую часть и сер!
веры баз данных, взаимодействующие друг с другом с помощью сете!
вых протоколов, даже если клиент и сервер расположены на одном и
том же компьютере. Представление данных и пользовательский ин!
терфейс контролируется клиентским программным обеспечением.
Сервер всегда контролирует доступ к данным. В зависимости от осо!
бенностей приложения логическая структура программы и органи!
зация проверки целостности вводимых данных могут быть реализо!
ваны как на клиентской машине, так и на сервере. Преимущества
такого подхода, использованного в SQL Server, следующие:
– упрощается поддержка приложений в связи с тем, что структуру
базы данных можно менять независимо от логики представления
данных;
– каждое рабочее место может быть оптимизировано отдельно
с точки зрения выполняемых на ней задач;
– организация распределенных вычислений позволяет добиться
наилучшей производительности и обеспечивает возможность одно!
временной работы большого числа пользователей.
К преимуществам Мicrosoft SQL Server относится также:
– использование «родных» потоков операционной системы для
выполнения части низкоуровневых вычислительных задач;
– организация симметричной мультипроцессорной обработки
(SМР); cимметричная архитектура сервера Мicrosoft основывается
на 32!разрядных службах операционной системы, обеспечивающих
защиту памяти, поддержку симметричной мультипроцессорной об!
работки и надежность работы сервера; пользовательским соединени!
ям динамически предоставляются потоки, которые затем распреде!
ляются планировщиком операционной системы для выполнения на
определенных процессорах.
SQL Server может организовывать одновременное выполнение не!
скольких задач, даже если в системе имеется только один процессор.
Он динамически распределяет соединения пользователей по потокам,
даже если в настоящий момент количество одновременных подклю!
чений пользователей превышает число потоков, которые одновре!
менно можно запустить.
4
SQL Serveг динамически оптимизирует выполнение запросов с по!
мощью использования встроенного оптимизатора, который произ!
водит оценку вариантов выполнения запросов с точки зрения сто!
имости затрачиваемых системных ресурсов. Оптимизатор автомати!
чески выбирает лучший метод доступа к данным, который будет
использован для выполнения запроса.
В Microsoft SQL Server 2000 представлены приложения с графи!
ческим интерфейсом для разработки и администрирования баз дан!
ных. Наиболее полезными компонентами являются SQL Server Query
Analyzer и SQL Server Enterprise Manager. Первая утилита позволя!
ет работать с серверами баз данных, вводить и выполнять запросы в
стандартном графическом интерфейсе. С помощью утилиты Enterprise
Manager осуществляется администрирование сервера и баз данных.
В SQL Server 2000 реализовано расширение языка ANSI SQL, ко!
торое называется TRANSACT!SQL (T!SQL). Язык SQL стандарта
ANSI позволяет только создавать, модифицировать базы данных и
читать из них информацию. Использование языка TRANSACT!SQL
обеспечивает возможность программирования, от управления пото!
ком команд до модульности.
При использовании SQL Server возникает задача сохранения ин!
формации о том, как хранятся данные в базе. Информация о хране!
нии данных называется метаданными.
Метаданные хранятся в словаре данных (набор системных таб!
лиц), заполняемом командами CREATE и ALTER. Помимо систем!
ных таблиц, многие из которых присутствуют во всех базах, суще!
ствует несколько системных баз данных, содержащих информацию
о конкретной системе.
В SQL Server существуют четыре системные базы данных:
– master, содержащая высокоуровневые данные о сервере (регист!
рация обращений к серверу, параметры конфигурации базы данных
и их отображение на физические устройства); базе данных master со!
ответствуют файлы master.mdf и masterlog.ldf.
– tempdb, содержащая временные таблицы, специфические для
отдельных процессов или запросов (временные таблицы, намеренно
созданные разработчиком, а также временные таблицы с промежу!
точными результатами, используемыми сервером при обработке зап!
росов); базе данных tempdb соответствуют файлы tempdb.mdf и
templog.ldf.
– model, являющаяся шаблоном для создания новых баз данных
(файлы model.mdf и modellog.ldf).
5
– msdb, содержащая данные для планирования оповещений и за!
даний, а также данные об операторах (файлы msdbdata.mdf и
msdblog.ldf).
Файл базы данных tempdb динамически увеличивается по мере
необходимости. При каждом запуске системы размер tempdb сбрасы!
вается до первоначального значения, которым является размер базы
данных model.
Кроме того, SQL Server поставляется с двумя демострационными
базами данных: Northwind (с файлами Northwnd.mdf и Northwnd.ldf)
и Pubs (с файлами Pubs.mdf и Pubs_log.ldf).
В данной работе в качестве примера рассматриваются две простые
реляционные базы данных:
1. Postavka (рис.1) состоит из трех связанных таблиц, в которых
хранятся данные из предметной области ПОСТАВКА_ДЕТАЛЕЙ:
– таблицы Post, содержащей данные о поставщиках (PN – номер,
ST – статус, PIM – имя, GOR – город, TEL – телефон поставщика,
COMMENT – комментарий о поставщике);
– таблицы Det, содержащей данные о поставляемых деталях (DN
– номер, DIM – имя, CENA – цена детали);
– таблицы PD, содержащей данные о поставках (PN – номер по!
ставщика, DN – номер поставляемой детали, KOL – количество по!
ставленных деталей, DATA – дата поставки).
2. Sotrudniki (рис. 2) состоит из двух таблиц:
– таблицы Otdeli, содержащей информацию об отделах предприя!
тия (OTDEL – номер, NAME_OTDEL – название отдела, в котором
работает сотрудник);
– таблицы Sotr, содержащей информацию о сотрудниках (NOMER
– номер, FAM – фамилия, NAME – имя, DOL – должность, OTDEL –
отдел, ZARPL – зарплата сотрудника).
Рис. 1. База данных postavka
6
Рис. 2. База данных sotrudniki
Из рис. 1 и 2 видно, что таблицы Post и Det связаны с таблицей PD
по атрибутам PN и DN соответственно, а таблицы Otdeli и Sotr – по
атрибуту OTDEL.
7
Лабораторная работа № 1
Создание базы данных на SQL Server
Для создания базы данных используются следующие способы:
– утилита Enterprise Manager;
– оператор T!SQL.
При создании новой базы данных на самом деле создается лишь
копия системной базы данных model. Объем созданной базы данных
автоматически увеличивается до необходимых размеров, занимая
дополнительные страницы памяти.
Создание базы данных оператором TSQL
Для создания базы данных используется оператор CREATE
DATABASE, синтаксис которого такой:
CREATE DATABASE имя_базы_данных
[ON {[PRIMARY]
спецификация_файлов_данных}[,...]]
[LOG ON
{спецификация_файла_транзакций}[,...]]
[COLLATE способ_сортировки]
[FOR LOAD]|[FOR ATTACH],
где спецификация файла задается так:
(NAME = логическое_имя_файла,
FILENAME = 'физическое_имя_файла'
[, SIZE = размер]
[, MAXSIZE = максимальный_размер|UNLIMITED]
[, FILEGROWTH = инкремент_увеличения_файлов])
и:
– ON PRIMARY задает основную группу файлов данных;
– FILENAME определяет полное имя файла на жестком диске;
– SIZE – исходный размер файла (задает по умолчанию размер
системной базы данных model). Для размера файла можно указать
единицы измерения, например 10 MB (мегабайт) или 700 KB (кило!
байт);
– MAXSIZE – максимальный размер. Если его не указать, то раз!
мер будет автоматически увеличиваться, пока не останется свобод!
ного места на жестком диске (при этом опция автоматического уве!
личения базы данных должна быть включена);
– FILEGROWTH – шаг увеличения (по умолчанию принимается 1 МВ);
– LOG ON задает спецификацию файла транзакций;
– UNLIMITED – размер файла не ограничивается;
8
– COLLATE задает тип сортировки (SQL Server по умолчанию ис!
пользует тип, принятый в Windows);
– FORLOAD используется только для совместимости с SQL Server 6.5;
– FORATTACH позволяет перекомпилировать набор файлов, со!
ставляющих единую базу данных.
Пример 1
Создать базу данных postavka с основным файлом данных по име!
ни postavkaData размером 20 МВ с шагом увеличения 10 МВ до мак!
симального размера 100 МВ и с соответствующим файлом журнала
транзакций.
Для решения поставленной задачи нужно:
– запустить анализатор запросов: ⇒ Пуск/Программы/MS SQL
Server 2000/Query Analyzer;
– набрать в окне анализатора оператор:
CREATE DATABASE postavka
ON PRIMARY
(NAME = postavkaData,
FILENAME = 'D:\………..\ postavkaData.mdf',
SIZE = 20 MB,
MAXSIZE = 100 MB,
FILEGROWTH = 10 MB)
LOG ON
(NAME = postavkaLog,
FILENAME = 'D:\………..\ postavkaLog.ldf',
SIZE = 5 MB,
MAXSIZE = 15 MB,
FILEGROWTH = 1 MB)
Здесь D:\………..\ – путь к файлу postavkaData.mdf;
– выполнить оператор следующей командой анализатора запро!
сов: ⇒ Query/Execute.
Вообще, для создания базы данных достаточно указать имя базы
и всего один параметр NAME (логическое имя файла), хотя так де!
лать не рекомендуется. Обычно используют следующий минималь!
ный набор параметров: NAME, FILENAME, SIZE, а также FILENAME
и SIZE для файла журнала транзакций.
Пример 2
Создать базу данных на основе разрозненных файлов:
CREATE DATABASE postavka
ON PRIMARY
(NAME = postavkaData1,
FILENAME = 'D:\………..\ postavkaData1.mdf',
9
SIZE = 5 MB,
MAXSIZE = 20 MB,
FILEGROWTH = 1 MB),
(NAME = postavkaData2,
FILENAME = 'D:\………..\ postavkaData2.ndf',
SIZE = 5 MB,
MAXSIZE = 20 MB,
FILEGROWTH = 1 MB)
LOG ON
(NAME = postavkaLog1,
FILENAME = 'D:\………..\ postavkaLog1.ldf',
SIZE = 5 MB,
MAXSIZE = 15 MB,
FILEGROWTH = 1 MB),
(NAME = postavkaLog2,
FILENAME = 'D:\………..\ postavkaLog2.ldf',
SIZE = 5 MB,
MAXSIZE = 15 MB,
FILEGROWTH = 1 MB)
Первый файл с данными имеет расширение .mdf, а все последую!
щие файлы – расширение .ndf
Создание базы данных с помощью утилиты
Enterprise Manager
Для этого нужно выполнить следующую последовательность
действий:
– запустить утилиту: ⇒ Пуск/Программы/MS SQL Server 2000/
Enterprise Manager;
– подключиться к необходимому экземпляру SQL Server;
– открыть папку Databases;
– из контекстно!зависимого меню, открытого на папке Databases,
выполнить команду ⇒ New Database;
– в окне свойств (Database Properties) указать свойства базы дан!
ных.
Типы данных и создание таблиц
Таблица – это объект базы данных (источник данных). База
данных может содержать до двух квадриллионов таблиц, а каж!
дая таблица до 1024 столбцов. Максимальная ширина столбца
составляет 8192 байта, исключение составляют текстовые и гра!
фические типы данных, размер которых может доходить до двух
GВ (гигабайт).
10
Идентификатор – это имя объекта в базе данных (объектом может
быть таблица, представление, хранимая процедура, переменная и пр.).
Длина идентификатора может составлять 1–128 символов в ко!
дировке Unicode (2 байта на символ). Идентификатор содержит бук!
вы, специальные символы и цифры. Первый символ – буква, симво!
лы @, # или символ подчеркивания ( _ ). Символы @, # имеют спе!
циальное назначение:
– с символа @ начинаются имена локальных переменных;
– с символа # – имена временных объектов, используемых одним
пользователем базы данных;
– с символов ## – имена глобальных временных объектов, ис!
пользуемых всеми пользователями базы данных.
Типы данных, поддерживаемые SQL Server 2000, указаны в табл. 1.
T!SQL допускает использование комментариев:
– однострочный (два дефиса — делает после них текст коммента!
рием);
– многострочный (/* – начало комментария и */ – конец коммен!
тария).
Создание таблицы
Создать таблицу можно:
– оператором CREATE TABLE;
– с помощью Enterprise Manager.
Создавать таблицы может любой пользователь с правами систем!
ного администратора или владельца базы данных.
Синтаксис оператора SQL Server:
CREATE TABLE имя_та6лицы (описание,...),
где описание задается так:
имя_столбца тип_данных
[NULL | NOT NULL] [DEFAULT значение]
[IDENTITY [(начало, приращение)]],
[CONSTRAINT имя_ограничения PRIMARY KEY
(имя_столбца,...)]
...............................
Здесь ключевое слово NULL разрешает, а NOT NULL запрещает
использование пустых (не введенных) значений в указанных столб!
цах таблицы.
Счетчик (IDENTITY) использует только целочисленные значения.
Предложение CONSTRAINT позволяет определить первичный
ключ (PRIMARY KEY) в таблице или связь этой таблицы с другой
таблицей.
11
12
SMALLDATETIME
DATETIME
MONEY
SMALLMONEY
FLOAT[(n)]
REAL
NUMERIC (p,s)
DECIMAL [(p[,s])]
TINYINT
SMALLINT
INT
BIGINT
DECIMAL [(p[,s])]
Тип данных
Описание
Целочисленные
Целое число в интервале от 0 до 255
2 байта. Целое число в интервале от –32 768 до 32 767
4 байта. Целое число в интервале от –2147 483 648 до 2147 483 647
8 байт. Диапазон от –263 до +263–1
2–17 байт. По умолчанию p = 18, s = 0
Вещественные
2–17 байт. Обычно s > 0. Аналог предыдущего типа данных
Числовые данные с количеством разрядов (р) и количеством цифр после запятой (s).
Интервал: от –1038–1 до 1038–1. Аналог Decimal [(p[,s])]
4 байта.Число с плавающей запятой, т. е. число представляется в виде мантиссы и поряд!
ка. Диапазон: от –3,40Е + 38 до 3,40Е + 38. Точность – 7 десятичных цифр после запятой
8 байт. Число с плавающей запятой. Интервал: от –1,79Е + 308 до 1,79Е + 308. Точность – 15
десятичных цифр после запятой (по умолчанию)
Денежные
4 байта. Диапазон от (–214 748, 3648) до (214 748, 3647) с точностью до четырех десятич!
ных цифр после запятой
8 байт. Интервал: от –922337203685477,5808 до 922337203685,5807 с точностью до четы!
рех десятичных цифр после запятой
Дата и Время
4 байта. От 01.01.1900 до 6.06.2079 н. э. Точность – 1 мин
8 байт. От 01.01.1753 до 31.12.9999 н. э. Точность – 3,33 мл/с
Таблица 1. Основные типы данных SQL Server 2000
13
Описание
8000 байт. Строка переменной длины. Хранение текста в формате Unicode (4000 символов)
Двоичные
1 бит. Целое значение 0 или 1. Аналог логического типа данных
8000 байт. Двоичная информация
8000 байт. Двоичная информация без дополнительных пробелов
2 Гбайта. Большие блоки двоичной информации
Строковые
8000 байт. Строка фиксированной длины (n). Позволяет хранить до 8000 знаков в форма!
те ASCII. В конец строки добавляются незначащие пробелы до длины n
8000 байт. Строка переменной длины. Хранение текста в формате Unicode (4000 символов)
8000 байт. Строка фиксированной длины (n). Позволяет хранить до 8000 знаков в форма!
те ASCII
Текстовые
TEXT
2 Гбайта. 2 147 483 647 символов
NTEXT
2 Гбайта. 1 073 741 823 символа в формате Unicode
8 байт. Уникальный идентификатор (в пределах базы) VARBINARY(8)
TIMESTAMP
UNIQUEIDENTIFIER 16 байт. Глобальный уникальный идентификатор – уникальность в пределах планеты
Специальные
8000 байт. Позволяет хранить данные нескольких типов в одном столбце за исключе!
SQL_VARIANT
нием text, ntext, image и timestamp
NVARCHAR(n)
VARCHAR (n)
NCHAR(n)
CHAR (n)
BIT
BINARY (n)
VARBINARY (n)
IMAGE
Тип данных
Продолжение табл. 1
Пример 3
/* Создание таблицы, содержащей сведения о поставщиках,
в базе данных postavka */
USE postavka
CREATE TABLE Post
(PN tinyint IDENTITY NOT NULL,
PIM char(15),
GOR char(15),
COMMENT varchar(50))
Для создания таблицы с помощью Enterprise Manager нужно:
– поключиться к SQL Server;
– открыть папку Databases. Найти в ней нужную базу данных и
открыть ее (2с – двойное нажатие левой кнопки мыши на папке с име!
нем базы данных);
– из контекстного меню, открытого правой кнопкой мыши на папке
Tables, выполнить команду ⇒ New Table;
– задать имена столбцов (Column Name), тип данных (Data Type);
длину (Length), значения Null (Allow Nulls), сведения о таблице;
– задать свойства столбцов;
– кнопкой Save сохранить таблицу и ввести ее имя.
Пример 4
Создать структуру таблиц и обеспечить связь между ними, как
показано на рис. 1 (см. введение):
/*Создание родительской таблицы Post*/
USE postavka
CREATE TABLE Post
(PN int IDENTITY (1,1) NOT NULL,
PIM char(15) NULL,
ST int NULL,
GOR varchar(20) NULL DEFAULT 'СПб',
TEL char(15) NULL,
COMMENT varchar(50),
CONSTRAINT Id_PN PRIMARY KEY(PN))
В этой таблице предложением CONSTRAINT объявлен столбец
PN первичным ключом. По первичному ключу автоматически созда!
ется кластерный индекс, Id_PN – имя индекса. Подробнее об индек!
сах будет сказано в лабораторной работе № 2.
/*Создание родительской таблицы Det*/
CREATE TABLE Det
(DN int IDENTITY(1,1) NOT NULL,
DIM char(15) NULL,
CENA money NULL,
14
CONSTRAINT Id_DN PRIMARY KEY(DN))
/*Создание дочерней таблицы PD и связей между таблицами*/
CREATE TABLE PD
(PN int NOT NULL,
DN int NOT NULL,
KOL bigint NULL,
DATA Datetime NULL,
CONSTRAINT Id_PN_DN PRIMARY KEY(PN,DN),
CONSTRAINT FK_Post FOREIGN KEY(PN)
REFERENCES Post(PN),
CONSTRAINT FK_Det FOREIGN KEY(DN)
REFERENCES Det(DN))
Здесь предложение FOREIGN KEY определяет внешние ключи
(PN, DN) дочерней таблицы PD, а предложение REFERENCES опре!
деляет ссылку на первичный ключ соответствующей родительской
таблицы (Post или Det).
Модификация таблиц
Выполняется оператором ALTER TABLE в соответствии с синтак!
сисом:
ALTER TABLE имя_таблицы спецификация [, …]
где спецификация:
ALTER COLUMN имя_столбца описание_столбца
| ADD имя_столбца описание_столбца
| ADD CONSTRAINT имя_ограничения описание_ограничения
| DROP имя_столбца описание_столбца
| DROP CONSTRAINT имя_ограничения описание_ограничения
Пример 5
Добавить в таблицу Det поле комментария, разрешив не вводить
данные в это поле:
ALTER TABLE Det
ADD Comment_Det varchar(20) NULL
Предположим, что создана таблица PR, в которой есть поле PR_DN,
и пусть эта таблица будет дочерней по отношению к таблице Det, пер!
вичным ключом в которой объявлено поле DN. Связать таблицы PR и
Det по полям PR_DN и DN соответственно можно оператором:
ALTER TABLE PR
ADD CONSTRAINT FK_PR_Det
FOREIGN KEY (PR_DN)
REFERENCES Det(DN)
Здесь FK_PR_Det – имя ограничения (может быть любым), а пос!
ледние две строки – описание ограничения.
15
Удаление таблиц
Осуществляется оператором:
DROP TABLE имя_таблицы
Создание клиентского приложения с использованием
Access 2002
Клиентское приложение в Access 2002 – это так называемый проект
Access или adp!файл. Проекты позволяют применить хорошо знако!
мый интерфейс Access для управления Microsoft SQL Server. Однако
проект содержит совершенно новый набор конструкторов для объектов
SQL Server, а некоторые из прежних конструкторов в нем изменены.
Создание adp(проекта
Проект Access позволяет связать пользовательский интерфейс
Access с источником данных SQL Server. Это можно сделать тремя
способами:
1) путем создания нового проекта Access и подключения его к су!
ществующей базе данных SQL Server;
2) создав с помощью мастера Microsoft SQL Server Database Wizard
новый проект Access на основе новой базы данных SQL Server;
3) путем конвертирования существующей базы данных Access
в новый проект Access и базу данных SQL Server, используя мастер
Upsizing Wizard.
Рассмотрим каждый из этих способов.
1. Создание adp!проекта и подключение его к существующей базе
данных:
– запустить Access;
– выбрать команду ⇒ Создание / Проект (новые данные);
– указать имя файла проекта и его местоположение в с в о е й пап!
ке на локальном диске на клиентской ЭВМ и кнопкой Создать запу!
стить мастер создания проекта;
– указать сервер, например локальный сервер (уточнить у препо!
давателя!);
– выбрать способ защиты, включив переключатель Use a specific
user name and password, в качестве кода входа указать свое имя (Login)
и пароль, например 123 (уточнить у преподавателя!);
– выбрать из списка базу данных, с которой нужно соединиться;
– проверить правильность соединения кнопкой Test Connection;
– связать проект с базой данных на сервере можно, исполнив ко!
манду ⇒ Файл / Подключение.
2. Создание нового проекта Access с помощью мастера Microsoft
SQL Server Database Wizard на основе новой базы данных SQL Server.
16
Для этого нужно выполнить действия, аналогичные п. 1 за исключе!
нием того, что нужно не выбирать из списка базу данных, а задавать
имя новой базы данных. При этом создается одновременно новый
проект и новая база данных, которая будет иметь стандартный раз!
мер, равный 1 Мбайту, и 1!мегабайтный журнал транзакций. И база
данных, и файлы журнала транзакций хранятся в той же папке, что
и системная база данных (master), а по мере заполнения таковых их
размеры увеличиваются на 10 % до тех пор, пока хватает дискового
пространства.
3. Использование мастера преобразования Upsizing Wizard. Для
преобразования существующей базы данных Access в новый проект
Access и базу данных SQL Server нужно выполнить следующую це!
почку действий:
– открыть существующую базу данных Access;
– исполнить команду ⇒ Сервис / Служебные программы / Мас(
тер преобразования в формат SQL Server;
– после запуска мастера на п е р в о м шаге его работы нужно ука!
зать, что создается новая база данных;
– на в т о р о м шаге нужно выбрать из списка или ввести с клави!
атуры имя сервера, (уточнить у преподавателя!), задать имя базы дан!
ных, а также свое имя и пароль;
– на т р е т ь е м шаге нужно выбрать таблицы, которые необхо!
димо переместить из существующей базы данных Access в новую базу
данных SQL Server. Чтобы избежать потенциальных проблем в даль!
нейшем, рекомендуется переносить все таблицы;
– на ч е т в е р т о м шаге нужно выбрать свойства таблиц, кото!
рые должны быть перенесены в новую базу данных. При этом перено!
сить можно следующее:
– индексы (мастер создаст индексы SQL Server, соответствую!
щие имеющимся у таблиц индексам Access. Однако при связывании
двух таблиц Access создаст скрытый индекс, поэтому в базе данных
SQL Server может оказаться больше индексов, чем предполагалось;
– правила (мастер попытается преобразовать существующие
в Access условия на значения (validation rules) в соответствующие
ограничения для таблиц SQL Server);
– значения по умолчанию (мастер попытается преобразовать
существующие значения по умолчанию в соответствующие ограни!
чения для таблиц SQL Server);
– межтабличные связи (мастер создаст в базе данных SQL
Server связи, соответствующие связям в базе данных Access). При
этом если выбрать опцию Use DRI, то связи между таблицами будут
реализованы в виде ограничений PRIMARY KEY и FOREIGN KEY.
17
При выборе опции Use Triggers связи будут поддерживаться тригге!
рами с помощью кода Transact!SQL. В дополнение к перечисленным
опциям можно выбрать еще две опции:
– Add Timestamp Fields To Tables? Если в таблице не опреде!
лен первичный ключ, то мастер добавит в нее поле типа timestamp.
Обычно целесообразнее выбрать ответ Yes, let wizard decide, посколь!
ку мастер, как правило, принимает верные решения;
– Only Create the Table Structure. Эта опция полезна в тех
случаях, когда с переносом базы данных с помощью мастера возник!
ли проблемы и вы хотите проверить, что он делает. Обычно этот
флажок не устанавливают;
– на п я т о м шаге определяют изменения, которые предстоит
внести мастеру в наше приложение:
– Create a new Access Client/Server Application. Мастер создаст
базу данных SQL Server и свяжет ее таблицы с новым проектом
Access. Существующие объекты приложения Access будут перенесе!
ны из базы данных в новый проект;
– ADP File Name. Мастер задает имя файла для нового проек!
та Access;
– Link SQL Server Tables To Existing Application. Мастер создаст
базу данных SQL Server и свяжет ее таблицы с текущей базой дан!
ных Access;
– No Application Changes. Мастер создаст базу данных SQL
Server, но не станет создавать проект Access;
– Save Password and User ID. Указанные на втором шаге имя
и пароль пользователя будут сохранены в строке подключения про!
екта. Это облегчает открытие проекта, но оставляет данные неза!
щищенными.
– после того как сделаны все установки, нажмите кнопку Finish
(Готово).
В результате преобразования базы данных Access в проект мастер
сделает следующее:
– создаст новую базу данных SQL Server;
– перенесет в эту базу таблицы с выбранными вами свойствами;
– создаст новый проект Access и свяжет его с только что сформиро!
ванной базой данных SQL Server;
– создаст в базе данных SQL Server представления и хранимые
процедуры, соответствующие запросам, которые имелись в базе дан!
ных Access (с теми же именами), и преобразует инструкции Jet!SQL
в Transact!SQL. В некоторых случаях для создания эквивалента зап!
роса Access в базе данных SQL Server может потребоваться и пред!
ставление, и хранимая процедура;
18
– скопирует формы и отчеты из базы данных в проект Access.
В некоторых случаях свойства RecordSource, ControlSource и Row!
Source форм, отчетов и элементов управления могут быть модифици!
рованы;
– создаст новые страницы доступа к данным, которые будут функ!
ционировать так же, как и уже существующие, но использовать дан!
ные из базы данных SQL Server;
– скопирует все макросы и модули из базы данных Access в проект,
ничего в них не меняя;
– закончив работу, мастер выведет отчет, в котором будут пере!
числены все выполненные им действия и указано, какие объекты ему
не удалось перенести в новое приложение. После закрытия отчета
вместо текущей базы данных Access откроет новый проект.
Если мастеру не удается преобразовать базу данных Access
в проект, обратитесь к преподавателю.
Перенос приложения на платформу SQL Server может вызвать не!
которые проблемы. Так, преобразование базы данных Access в про!
ект требует учета следующих особенностей защиты Access:
– для всех объектов, которые вы хотите перенести в новое прило!
жение, у вас должно быть разрешение Read Design, а для таблиц,
если вы хотите переносить в базу данных SQL Server их данные, еще
и разрешение Read Data;
– если ваш VBA!код защищен паролем, вы должны его знать и
быть готовым ввести;
– для создания новой базы данных SQL Server у вас должно быть
разрешение Create Database, а также разрешение Select для таблиц
системной базы данных. Кроме того, необходимы разрешения Create
Table и Create Default для преобразования таблиц;
– мастер преобразования в формат SQL Server не сможет конверти!
ровать базу данных MDE в проект.
Некоторые составляющие базы данных Access вообще не могут
быть перенесены в новый проект Access, а именно:
– маски ввода;
– некоторые условия на значения и значения по умолчанию;
– свойство Allow Zero Length (для его эмуляции можно создать
в базе данных SQL Server ограничение CHECK);
– поля гиперссылок будут преобразованы в текстовые, после чего
перестанут функционировать в качестве таковых;
– нужно внимательно проверить все перенесенные в проект модули.
Поскольку для проекта Access Jet не загружается, а следователь!
но, не загружается и библиотека DAO. В новом проекте придется вруч!
ную перевести весь имеющийся код на использование технологии ADO.
19
Мастер не особенно силен в преобразовании запросов. Вниматель!
но просмотрев его отчет, нужно выяснить, все ли запросы он перенес
в новую базу данных. Для тех запросов, перенос которых не удался,
мастер включает в отчет сгенерированные им инструкции SQL, так
что можно взять их за основу и довести до рабочего состояния, создав
хранимые процедуры и представления.
На SQL Server не будут перенесены:
– перекрестные запросы;
– запросы на выборку с параметрами;
– запросы на выборку на базе других запросов;
– запросы к серверу и управляющие запросы;
– все запросы, прямо или косвенно базирующиеся на запросе, ко!
торый не удалось перенести.
Поскольку многие формы, отчеты, списки и поля со списками ба!
зируются на запросах, с их переносом в новый проект могут быть
проблемы.
Несмотря на перечисленные выше проблемы, следует пользовать!
ся мастером преобразования в формат SQL Server, так как он пре!
красно выполняет перенос данных и переносит в новое приложение
как минимум часть объектов, так что значительный объем работы он
вместо вас все!таки сделает. Однако без тщательного тестирования
получившегося приложения не обойтись.
Задания для самостоятельного выполнения
Задание 1. Предъявить преподавателю спроектированную по мето!
ду синтеза реляционную базу данных, состоящую из набора декомпо!
зиционных подсхем (не менее трех таблиц). Зарегистрироваться на
сервере, если он установлен на другом компьютере.
Задание 2. Ознакомиться с операторами TRANSACT!SQL по созда!
нию БД. Создать на сервере базу данных с помощью Enterprise Manager.
Задание 3. Создать adp!проект в Access на клиентской стороне.
Обеспечить связь проекта с базой данных на сервере.
Задание 4. Создать таблицы в базе данных, используя Enterprise
Manager (одну таблицу), анализатор запросов Query Analyzer (одну
таблицу), а также из adp!проекта (остальные таблицы).
Задание 5. Создать в adp!проекте диаграмму связей для этих таб!
лиц, обеспечив ссылочную целостность данных. Заполнить таблицы
данными.
Задание 6. Используя анализатор запросов Query Analyzer, со!
здать для одной из таблиц справочник, который связать с этой таб!
лицей.
Предъявить результаты работы преподавателю.
20
Лабораторная работа № 2
Работа с анализатором запросов (Query Analyzer).
Основы TransactSQL
Предлагаемые в работе примеры используют учебные базы дан!
ных, которые были приведены на рис. 1 и 2 (см. введение).
Выборка (извлечение) данных из таблиц
Выборка осуществляется оператором SELECT, неполный синтак!
сис которого может быть определен следующим образом:
SELECT [предикат] {*| имя_таблицы.* | [имя_таблицы.]имя_поля1
[AS
псевдоним1] [, [имя_таблицы.]имя_поля2 [AS псевдоним2]
[, … ] ] }
FROM выражение [, …] [IN имя_внешней_БД]
[ WHERE условие_отбора]
[ GROUP BY группируемые_поля ]
[ HAVING условие_группирования]
[ ORDER BY имя_поля1 [ASC | DESC] [,имя_поля2[ASC |
DESC] [, … ]]
Здесь вертикальная черта означает ИЛИ. То, что заключено
в квадратные скобки, может отсутствовать. Из заключенного в фи!
гурные скобки обязательно должна присутствовать одна из указан!
ных альтернатив.
Предикат может принимать различные значения, в зависимости
от задач, выполняемых выборкой:
– ALL – подразумевается по умолчанию. Выбираются все записи,
удовлетворяющие условию отбора;
– DISTINCT – выводит только те записи, которые не совпадают с
другими по всем выбранным полям в запросе;
– DISTINCTROW – похож по действию на предыдущий предикат,
но отбрасывает записи не по повторяющимся значениям в отдель!
ных выбранных полях, а по целым записям. Этот предикат игнори!
руется, если выводятся записи из одной таблицы или все поля из
всех таблиц;
– TOP n – возвращает n первых или последних записей из итого!
вой выборки в зависимости от того, какой порядок сортировки уста!
новлен с помощью предложения ORDER BY;
– TOP n PERCENT – результатом выборки будет определенный
процент первых или последних записей.
В тексте запроса допускаются комментарии. Два дефиса () дела!
ют часть строки за ними комментариями. Многострочный коммента!
рий начинается с символов /* и заканчивается символами */.
21
Пример 6
Однотабличный запрос на выборку данных
/* Выдать все сведения о деталях: */
SELECT Det.* $$ ответ содержит все поля таблицы
FROM Det
Здесь символ * обозначает, что динамический набор данных вклю!
чает все поля таблицы Det. Ключевое слово DISTINCTROW (уста!
навливается по умолчанию, поэтому в дальнейшем будет опускать!
ся) означает исключение полностью повторяющихся записей из ди!
намического набора данных.
Этот же запрос можно было написать иначе :
SELECT DN, DIM, CENA
FROM Det
или так:
SELECT *
FROM Det
или так:
SELECT Det.DN, Det.DIM, Det.CENA
FROM Det
Для выполнения запроса с помощью анализатора запросов нужно:
– запустить анализатор запросов командой ⇒ Пуск/Программы/
MS SQL Server 2000/Query Analyzer;
– выбрать из списка нужную базу данных или базу данных указать
оператором
USE имя_базы_данных
– набрать в окне анализатора запросов текст запроса;
– исполнить запрос командой ⇒ Query/Execute.
Задание псевдонимов для полей
Псевдонимы заголовкам столбцов в SQL Server задаются операто!
ром присваивания или согласно стандарту ANSII ключевым словом AS.
Пример 7
Выдать все сведения о деталях, называя поле DN в ответе как
номер детали, DIM – как имя детали:
USE Det
SELECT DN AS [номер детали],
DIM AS [имя детали], CENA
FROM Det
Здесь псевдонимы взяты в квадратные скобки, так как они содер!
жат пробелы.
22
Создание в запросах вычисляемых полей и использование
стандартных функций
В результате выборки можно выводить не только данные из полей
таблицы, но и новые данные, получаемые в результате вычислений.
Пример 8
Пусть все детали подорожали на 10 %. Вывести сведения о дета!
лях, указав их прежнюю цену и новую цену:
SELECT DN AS [номер детали],
DIM AS [имя детали], CENA AS [прежняя цена],
CENA * 1.1 AS [новая цена]
FROM Det
Пример 9
Вывести следующие сведения о деталях:
– количество записей в таблице Det,
– среднюю и максимальную цену по всем деталям:
SELECT Count(*) AS [Число деталей],
Avg (CENA) AS [средняя цена],
Max (CENA) AS [максимальная цена]
FROM Det
Здесь функция Count используется для подсчета количества всех
записей в таблице Det (символ * используется в качестве аргумента).
Часто используемые стандартные функции приведены в табл. 2.
Таблица 2. Встроенные функции SQL Server
Функция
Значение функции
Математические
Абсолютное значение
Арккосинус, арксинус, арктангенс
(в радианах)
Kосинус, синус, тангенс, котангенс
(в радианах)
Наименьшее целое, большее или рав!
CEILING (выражение_numeric)
ное выражение_numeric
EXP (выражение_float)
Экспонента
PI ( )
Kонстанта p = 3,141592653589793
LOG (выражение_float)
Натуральный логарифм
Десятичный логарифм
LOG10 (выражение_float)
Возведение выражение_ numeric в сте!
POWER (выражение_numeric,y)
пень y
Случайное число типа float, располо!
RAND ([начальное число] )
женное между 0 и 1
ABS (выражение_numeric)
ACOS | ASIN | ATAN
(выражние_float)
COS | SIN | TAN | COT
(выражение_float)
23
Продолжение табл. 2
Функция
Значение функции
Округленное выражение_numeric с точ!
ROUND (выражение_numeric,
ностью после запятой, определенной
длина)
целым длина
SIGN (выражение_numeric)
Знак числа
SQUARE (выражение_float)
Kвадрат числа
Kвадратный корень числа
SQRT (выражение_float)
Строковые
Kонкатенация (слияние) двух
'выражение1' + 'выражение2'
символьных строк
Возвращает значение кода ASCII самого
ASCII (выражение_char)
левого символа
Возвращает символ, имеющий указан!
CHAR (выражение_integer)
ный код ASCII
Сравнивает две строки и анализирует их
DIFFERENCE
на совпадение; возвращает значение от 0
(выражение1_char,
до 4; при наилучшем совпадении возвра!
выражение2_char)
щается 4
Возвращает строку, начиная слева, со!
LEFT (выражение_char,
стоящую из символов выражение_integer
выражение_integer)
Преобразует символы к нижнему реги!
LOWER (выражение_char)
стру
LTRIM (выражение_char)
Возвращает строку без первых пробелов
Возвращает начальную позицию первой
встретившейся в выражении подстроки,
PATINDEX(выражение_char)
совпадающей с образцом
REPLACE ('строка1', 'строка2', Заменяет в 'строка1' все элементы
'строка3')
'строка2' на 'строка3'
REPLICATE (выражение_char, Повторяет выражение_char выраже(
выражение_integer)
ние_integer раз
Возвращает реверсивное значение вы(
REVERSE (выражение_char)
ражение_char
Возвращает символьную подстроку из
RIGHT(выражение_char, выра(
выражение_char, начиная с символа
жение_integer)
выражение_integer справа
Возвращает строку без последних про!
RTRIM (выражение_char)
белов
Возвращает строку из выражение_in(
SPACE (выражение_integer)
teger пробелов
Возвращает символьные данные, пре!
STR (выражение_float [, длина[, образованные из числовых данных;
десятичное_число]])
длина – общая длина строки; десятич(
ное_число – количество пробелов справа
24
Продолжение табл. 2
Функция
Значение функции
STUFF (выражение1_char,
начало, длина,
выражение2_char)
SUBSTRING (выражение,
начало, длина)
Удаляет длина символов, начиная
с символа начало, и вставляет в это
место строку выражение2_char
Возвращает часть символьной или би!
нарной строки
Преобразует символы в верхний ре!
UPPER (выражение_char)
гистр
Обработка дат
DATEADD (часть_даты, число, Добавляет к date величину часть_даты,
date)
умноженную на число раз
DATEDIFF (часть_даты, date1, Возвращает количество величин
часть_даты между двумя датами
date2)
DAY (date)
Возвращает целое число дней в date
Возвращает текущие системную дату и
GETDATE ( )
время во внутреннем формате
MONTH (date)
Возвращает целое число месяцев в date
YEAR (date)
Возвращает целое число лет в date
Преобразует значение, заданное выра(
CONVERT(тип_данных
жением, в указанный тип данных,
[(длина)], выражение [, стиль] )
представляемый указанным стилем
Аналогична функции CONVERT, но без
CAST(... )
параметра стиль
Системные функции
COALESCE (выражение1,
Возвращает первое не равное NULL
выражение2,…, выражениеN)
выражение
COL_NAME (id_таблицы,
Возвращает имя столбца
id_столбца)
COL_LENGTH ('имя_таблицы',
Возвращает ширину поля столбца
'имя_столбца')
Возвращает фактическую длину пере!
DATALENGTH ('выражение')
менной выражение любого типа дан!
ных
Возвращает идентификатор ID базы
DB_ID (['имя_базы_данных'])
данных
DB_NAME ([id_базы_данных]) Возвращает имя базы данных
Возвращает начальное значение для
IDENT_SEED ('имя_таблицы')
столбца типа identity
Возвращает имя индексированного
IDENT_COL ('имя_таблицы',
id_индекса, id_ключа)
столбца
25
Окончание табл. 2
Функция
Значение функции
Проверяет правильность формата даты;
ISDATE (переменная |
если формат правильный – возвращает
имя_столбца)
1, иначе – 0
ISNULL (выражение, значение) Заменяет все NULL на значение
Проверяет правильность числового
ISNUMERIC (переменная
формата; возвращает 1, если формат
имя_столбца)
правильный и 0 – в противном случае
Возвращает NULL, если выражение1 =
NULLIF (выражение1,
выражение2
выражение2)
OBJECT_NAME (id_объекта)
Возвращает имя объекта базы данных
SUSER_ID(['имя_пользователя( Возвращает идентификатор пользова!
_сервера])
теля сервера
SUSER_NAME
Возвращает имя пользователя сервера
([идентификтор_сервера])
USER_ID
Возвращает идентификатор пользова!
теля
(['имя_пользователя'])
USER_NAME
Возвращает имя пользователя базы
([id_пользователя'])
данных
Замечания по функциям обработки дат
Функции обработки дат выполняют операции над данными типа
datetime. Эти функции можно использовать в списке столбцов опера!
тора SELECT, в предложении WHERE и в любом другом месте, где
задается выражение. Синтаксис функции обработки дат имеет вид:
SELECT функция_обработки_дат (параметры)
Значения типа datetime, передаваемые в качестве параметров, не!
обходимо заключать в одинарные или двойные кавычки. В некото!
рые функции добавляется специальный параметр, называемый
часть_даты, который указывает функции, с какой частью перемен!
ной типа datetime следует выполнить требуемые операции. Ниже пе!
речислены допустимые значения параметра часть_даты.
Краткая запись
Полная запись
Диапазон значений
dd
day
1–31
dy
day of year
1–366
hh
hour
0–23
ms
millisecond
0–999
mi
minute
0–59
mm
month
1–12
qq
quarter
1–4
26
ss
second
0–59
wk
week
0–53
dw
weekday
1–7
yy
year
1753–9999
П р и м е р ы выполнения функций обработки дат:
SELECT DATEDIFF (mm, '1/1/00', '12/31/02')
Результат: 35
SELECT GETDATE ( )
Результат: 2006$10$15 21:31:01.170 (текущие дата и время)
SELECT DATEADD (mm, 6, '1/1/00')
Результат: 2000$07$01 00:00:00.000
SELECT DATEADD (mm, $5, '10/6/00')
Результат: 2000$05$06 00:00:00.000
Предложение WHERE задает условие отбора данных.
Пример 10
Вывести все сведения о поставщиках из Москвы.
SELECT *
FROM
Post
WHERE
GOR
= 'Москва'
Ниже указаны другие примеры предложения WHERE:
1) WHERE GOR In(‘Москва’; ‘СПб’; ‘Самара’) – вывод сведений
о поставщиках из перечисленного множества городов. Это предложе!
ние можно было написать иначе, например:
WHERE GOR = 'Москва' OR GOR = 'СПб' OR GOR = 'Самара'
2) WHERE PN Between 2 AND 5 – вывод сведений
о поставщиках, номера которых заключены в интервале [2; 5];
3) WHERE TELEFON Is Null – вывод сведений о поставщиках,
у которых не введены номера телефонов;
4) WHERE PIM Like ‘Ив%’ – вывод сведений о поставщиках, име!
на которых начинаются на Ив, например: Иванов, Иваненко, Иван!
ченков, Ивов и пр. (см. табл. 3).
В предложении WHERE нельзя использовать агрегатные (ста(
тистические) функции: Sum, Avg, Max, Min и др.
В SQL!операторах трафаретные символы и последовательности
символов необходимо заключать в одиночные кавычки. Приведем
несколько примеров:
– LIKE ‘_ob’ – поиск слова, состоящего из трех букв и заканчива!
ющего на ob, например Bob или Rob;
– LIKE ‘Иван%’ – поиск фамилий, начинающихся с Иван, напри!
мер Иванов или Иванова или Иванченко;
27
Таблица 3. Запросы с трафаретными символами и конструкцией LIKE
Трафаретные символы
–
%
[]
[^]
Значения
Любой одиночный символ
Любая последовательность из нуля и
более символов
Любой одиночный символ в границах
указанного диапазона ([a–c]) или на!
бора ([abc])
Любой одиночный символ не в грани!
цах указанного диапазона ([a–c]) или
набора ([abc])
– LIKE ‘%ов’ – поиск фамилий, заканчивающихся на ов, напри!
мер Иванов или Петров;
– LIKE ‘%bi%’ – поиск всех слов, содержащих bi, например binary
или cabinet;
– LIKE ‘[CS]ery1’ – поиск Chery1 или Shery1;
– LIKE ‘U[^n]’ – поиск, начинающихся с буквы U и не содержа!
щих букву n во второй позиции.
Предложение GROUP BY группирует данные по полям, указан!
ным за ключевыми словами GROUP BY.
Группирование данных позволяет получать статистическую, ито!
говую и другую информацию по отдельным группам данных. Группи!
ровать данные можно по одному или нескольким полям. При этом
в одну группу включаются данные с одинаковыми значениями в поле
группирования. Например, все записи, сгруппированные по номеру
поставщика в таблице Post, образуют столько групп, сколько имеет!
ся разных значений в поле номера поставщика PN. При группирова!
нии данных по нескольким полям порядок (последовательность) имен
полей в списке определяет уровень группирования для каждого поля
(вложенные группы). Группирование осуществляется сначала по пер!
вому полю списка, затем для одинаковых значений первого поля дан!
ные группируются по второму полю в списке и т. д.
При группировке данных можно использовать агрегатные функ!
ции:
– Sum – суммирование всех значений поля по группе данных;
– Avg – среднее значение поля по группе;
– Min (Max) – минимальное (максимальное) значение поля в группе;
– Count – количество записей, входящих в группу;
– StDev – среднеквадратическое отклонение;
– Var – дисперсия;
– First (Last) – первое (последнее) значение поля в группе,
28
а также сверхагрегатные операторы CUBE и ROLLUP, синтаксис ко!
торых такой:
SELECT список_столбцов с указанием необходимых агрегатных функций
FROM список_таблиц
WHERE условие_отбора
[GROUP BY [ALL] неагрегатные выражения
[WITH {ROLLUP | CUBE}]]
Оператор ROLLUP создает дополнительные строки, в которые за!
носит суммарную информацию (или средние значения) по несколь!
ким строкам для столбцов, заданных директивой GROUP BY.
Оператор CUBE создает сверхагрегатные строки для всех возмож!
ных сочетаний столбцов директивы GROUP BY. Как и ROLLUP, опе!
ратор CUBE вычисляет текущие суммы (или средние значения), од!
нако, в отличие от него, дополнительно создает сверхагрегаты для
сочетаний, не возвращаемых оператором ROLLUP.
Пример 11
На рис. 3 показано содержимое таблицы Det1.
Создадим запрос, в котором закажем группировку по двум полям:
сначала по полю DIM, а затем по полю CENA. По каждой группе
вычислим суммарное количество деталей на складе и количество за!
писей в группе (по имени детали). Такой запрос может иметь вид:
SELECT DIM AS [Имя детали], Count (DIM)
AS [Кол в группе], CENA AS [Цена детали],
Sum (KOL_SKLAD) AS [Суммарное кол на складе]
FROM Det1
GROUP BY DIM, CENA
DN
1
2
3
4
5
6
7
8
9
10
11
DIM
Болт
Гайка
Болт
Шайба
Болт
Гайка
Шуруп
Гайка
Болт
Шуруп
Шуруп
CENA, р
KOL_SKLAD
120,00
120,00
130,00
300,00
120,00
100,00
700,00
100,00
120,00
100,00
120,00
200
300
100
100
200
100
400
500
200
100
300
Рис. 3. Содержимое таблицы Det1
29
Имя детали
Kоличество в группе
Цена детали
Суммарное количество
на складе
3
1
2
1
1
1
1
1
120
1 30
1 00
120
300
1 00
1 20
700
600
100
600
300
100
100
300
400
Болт
Болт
Гайка
Гайка
Шайба
Шуруп
Шуруп
Шуруп
Рис. 4. Ответ на запрос
На рис. 4 приведен ответ на этот запрос.
Использование конструкции COMPUTE и COMPUTE BY
Простые функции агрегирования генерируют одно итоговое зна!
чение для всех строк в группе. Итоговые значения выполнения опе!
ратора COMPUTE появляются в ответе в виде новых строк.
Пример 12
SELECT DN, KOL
FROM PD
ORDER BY DN, DATA
COMPUTE AVG(KOL)
Предложение HAVING определяет условие группирования,
т. е. какие записи должны быть отобраны в группу.
Пример 13
Вычислить суммарные поставки по каждому типу детали для де!
талей с номерами, большими трех. Это можно сделать с помощью
запроса:
SELECT DN, Sum(KOL) AS [суммарные поставки]
FROM PD
GROUP BY
DN
HAVING DN > 3
Предложение ORDER BY позволяет отсортировать данные в отве!
те по одному или нескольким полям.
Ключевое слово ASC (Ascending, устанавливается по умолчанию)
выполняет сортировку по возрастанию значений поля (для символь!
ных полей – по алфавиту), а ключевое слово DESC (Descending) – по
убыванию значений.
30
Пример 14
Выдать все сведения о поставщиках, упорядоченные по убыванию
значений поля PIM. Это можно сделать запросом:
SELECT Post.*
FROM Post
ORDER BY PIM DESC
Пример 15
Выдать следующие сведения по каждому типу деталей:
– номер детали (в упорядоченном виде по возрастанию значений);
– суммарное количество поставок по каждому типу детали;
– для каждого типа детали выдать дату последней поставки.
Это можно сделать запросом:
SELECT DN, Sum(KOL) AS [суммарная поставка],
Max (DATA) AS [последняя поставка]
FROM PD
GROUP BY DN
ORDER BY DN
Используя сортировку данных и предикат TOP n или TOP n
PERCENT, можно выводить заданное числом n количество или n
процентов (n PERCENT) первых записей с минимальным или макси!
мальным значением в отсортированных полях.
Пример 16
Вывести сведения о двух максимальных поставках. Это можно
сделать запросом:
SELECT TOP 2 PD.*
FROM PD
ORDER BY KOL DESC
Запросы на создание таблицы
Синтаксис оператора:
SELECT имя_поля _1 [,имя_поля _2 [, ...]]
[IN имя_внешней_базы_данных ]
FROM имя_источника
INTO
имя_новой_таблицы
Пример 17
Отобрать в таблицу по имени New сведения о болтах можно, ис!
полнив запрос:
SELECT Det.* INTO New
FROM Det
WHERE (Det.DIM ='болт')
31
Создается новая таблица, в которую отбираются указанные в зап!
росе записи. При повторном исполнении этого запроса созданная ра!
нее таблица удаляется и создается заново.
Запросы на изменение данных
Добавление записей
Синтаксис оператора на добавление нескольких записей:
INSERT INTO имя_приемника
[IN имя_внешней_базы_данных]
[(имя_поля_1[, имя_поля_2[, ...]])]
SELECT [имя_источника]имя_поля_1[, имя_поля_2[, ...]
FROM выражение
Синтаксис оператора на добавление одной записи:
INSERT INTO имя_приемника [(имя_поля _1[,имя_поля _2
[, ...]])]
VALUES (значение_1[, значение_2[, ...])
Пример 18
Добавить в таблицу Det новую деталь с номером 20, имя которой
ролик по цене 300 можно, исполнив запрос:
INSERT INTO Det(DN, DIM, CENA )
VALUES (20, ‘ролик’,300)
Добавление данных всегда осуществляется в конец таблицы, при!
чем первое значение (20) добавляется в первое поле (DN), указанное
в списке полей, второе значение – во второе поле и т. д.
Пример 19
Добавить в таблицу New гайки, цена которых меньше 120, мож!
но, исполнив запрос:
INSERT INTO New
SELECT Det.*
FROM Det
WHERE (Det.DIM =’гайка’) AND (Det.CENA < 120)
В данном примере добавление данных осуществляется во все поля
таблицы. Если нужно добавлять данные только в поля DN и DIM, то
запрос будет сформулирован в виде:
INSERT INTO New (DN, DIM )
SELECT Det.DN, Det.DIM
FROM Det
WHERE (Det.DIM =’гайка’) AND (Det.CENA < 120)
32
Удаление записей
Синтаксис оператора:
DELETE [таблица]
FROM таблица
WHERE Условие_отбора
Пример 20
Удалить гайки из таблицы New можно запросом:
DELETE
FROM New
WHERE (New.DIM = ‘гайка’)
Удалить все записи из таблицы можно оператором
DELETE New
FROM New
или оператором
TRUNCATE TABLE имя_таблицы
При этом структура таблицы сохраняется. Последний оператор
может выполнять только пользователь, наделенный правами адми!
нистратора, или владелец таблицы.
Обновление (корректировка) данных Синтаксис оператора:
UPDATE таблица
SET Новое_значение
WHERE Условие_отбора
Пример 21
Заменить значение Москва в поле GOR в таблице Post на СПб мож!
но запросом:
UPDATE Post SET Post.GOR =’Москва’
WHERE (Post.GOR =’СПб’)
Многотабличные запросы
Многотабличный запрос – это запрос, ответ на который может
быть получен из более чем одной таблицы. Для реализации такого
запроса таблицы должны быть связаны между собой.
Возможны следующие типы объединения (связи) двух таблиц:
– симметричное объединение (INNER JOIN), при котором объе!
диняются только те записи, в которых значения связанных полей
обеих таблиц совпадают;
– внешнее объединение:
33
– левое (LEFT OUTER JOIN), при котором объединяются все запи!
си из первой таблицы и только сопоставимые записи из второй таб!
лицы;
– правое (RIGHT OUTER JOIN), при котором объединяются все
записи из второй таблицы и только сопоставимые записи из первой
таблицы;
– полное (FULL OUTER JOIN), которое включает все сопостави!
мые и несопоставимые записи обеих таблиц.
Пример 22
Вывести сведения о поставках из города Москва, указав при этом
основные сведения о поставщике. Это можно сделать следующим зап!
росом из двух таблиц PD и Post:
SELECT PD.PN, Post.PIM, Post.ST, Post.GOR, PD.DN,
PD.KOL, PD.DATA
FROM Post INNER JOIN PD ON Post.PN = PD.PN
WHERE (Post.GOR = 'Москва')
Пример 23
Вывести те же сведения, что и в примере 22, добавив имя постав!
ляемой детали. Это можно сделать следующим запросом из трех таб!
лиц Post, Det и PD:
SELECT PD.PN, Post.PIM, Post.ST, Post.GOR, PD.DN,
Det.DIM, PD.KOL, PD.DATA
FROM Det INNER JOIN (Post INNER JOIN PD ON
Post.PN = PD.PN) ON Det.DN = PD.DN
WHERE (Post.GOR = 'Москва')
Использование псевдонимов для таблиц
Можно сократить имя таблицы, заменив его псевдонимом. Псев!
доним для таблицы определяется в предложении FROM, а его наиме!
нование – общими правилами именования объектов. Определенный
в предложении FROM псевдоним действует только для текущего
SELECT!оператора, при этом имя таблицы должно быть заменено ее
псевдонимом во всех предложениях запроса, включая SELECT!спи!
сок.
Пример 24
Выдать информацию о поставщиках, для которых есть поставки,
можно, исполнив запрос:
SELECT DISTINCT D.PN, PIM, ST, GOR
FROM PD E, Post D
WHERE E.PN = D.PN
ORDER BY D.PN
34
При реализации запросов можно объединить значения отдельных
полей и отобразить в ответе объединенные значения в одном поле.
Пример 25
Составить запрос, добавив заголовок новому столбцу, объединя!
ющему имена и фамилии сотрудников:
SELECT 'полное имя' = FAM + ' ' + NAME,
'должность' = DOL
FROM Sotr
Подчиненные (вложенные) запросы
Вложенный запрос (подзапрос) – это SELECT!оператор, который
помещается внутрь другого оператора и возвращает результаты, ис!
пользуемые в предложениях WHERE или HAVING внешнего запро!
са. Подзапрос также может использоваться в предложении FROM.
Запрос, содержащий подзапрос, называется родительским, или глав(
ным.
Различают вложенные и взаимосвязанные подзапросы. Вложен!
ный подзапрос отличается от взаимосвязанного тем, что условие по!
иска в нем не зависит от данных внешнего запроса, в то время как во
взаимосвязанном подзапросе, определяющем критерий выбора во
внешнем запросе, условие поиска в свою очередь зависит от данных
анализируемой строки внешнего запроса.
Подзапросы всегда ограничиваются круглыми скобками и долж!
ны стоять в правой части оператора сравнения родительского запроса.
Предложение ORDER BY в подзапросах недопустимо.
В простом случае вложенный подзапрос выглядит следующим об!
разом:
SELECT
операнд1, операнд2, ...
FROM имя_таблицы
WHERE операнд = (SELECT операнд
FROM
имя_таблицы WHERE условие)
Различают подзапросы, которые могут возвращать одну или бо!
лее одной строки.
Подзапросы, возвращающие одну строку. Когда подзапрос воз!
вращает одну строку, в предложении WHERE родительского запроса
используют простые операции сравнения (=, < >, >, <, <= и т. д.).
Пример 26
Для поиска сотрудников, имеющих минимальную зарплату, не!
обходимо исполнить запрос:
35
SELECT NAME, OTDEL, ZARPL
FROM Sotr
WHERE ZARPL =(SELECT Min(ZARPL)
FROM Sotr)
Подзапросы, возвращающие более одной строки.
Пример 27
Для получения номеров и имен поставщиков, имеющих более трех
поставок, необходимо выполнить запрос:
SELECT PN, PIM
FROM Post
WHERE PN IN (SELECT PN FROM PD
GROUP BY PN HAVING count (*) > 3)
Поскольку подзапрос может вернуть более одного поставщика,
в родительском запросе используется функция IN, осуществляющая
сравнение с множеством значений.
Такой же результат можно получить и без подчиненного запроса.
Как? Попробуйте ответить на этот вопрос самостоятельно.
Типы подчиненных запросов:
– запросы, использующие сравнения, синтаксическая конструк!
ция которых может быть такой:
[ANYЅALL ЅSOME] (инструкция SQL),
где ANY – любой, ALL – все (по умолчанию), SOME – некоторые;
– запросы, содержащие предложения, синтаксическая конструк!
ция которых может быть такой:
выражение [NOT] IN (инструкция SQL);
– запросы, содержащие предложения, синтаксическая конструк!
ция которых может быть такой:
[NOT] EXISTS (инструкция SQL),
где EXISTS – существуют.
Пример 28
Отобрать из таблицы Det детали, которые стоят столько же, сколь!
ко подшипник. Если в таблице есть только один подшипник, то эту
задачу можно решить с помощью следующего запроса:
SELECT Det.*
FROM Det
WHERE (Det.CENA = SELECT CENA
FROM Det
WHERE DIM = 'подшипник'))
36
Операторы ALL, ANY и SOME совместно с операциями =, < >, >,
<, <=, >= могут быть использованы для подзапросов, возвращаю!
щих более одной строки.
Операторы ANY и SOME возвращают значения TRUE, если усло!
вие выполняется хотя бы для одного возвращаемого подзапросом
значения, в противном случае – FALSE.
Оператор ALL возвращает значения TRUE, если условие выпол!
няется для каждого возвращаемого подзапросом значения, в против!
ном случае – FALSE.
Пример 29
Чтобы получить список сотрудников, получающих зарплату боль!
ше, чем зарплата любого сотрудника 30!го отдела, необходимо вы!
полнить запрос:
SELECT *
FROM Sotr
WHERE ZARPL > ANY (SELECT
ZARPL
FROM
Sotr
WHERE OTDEL = 30)
Пример 30
Из таблицы Sotr (NOM, FAM, NAME, DOL, OTDEL, ZARPL) ото!
брать сведения о продавцах, зарплата которых превышает зарплату
всех начальников и директоров. Это можно сделать запросом:
SELECT Sotr.*
FROM Sotr
WHERE DOL LIKE 'продавец%' AND ZARPL > ALL
(SELECT ZARPL FROM Sotr
WHERE ((DOL LIKE '%начальник%')OR(DOL LIKE
'директор%')))
Операция NOT IN используется для исключения значений, воз!
вращаемых подзапросом. При использовании NOT IN необходимо
учитывать, что если среди возвращаемых подзапросом значений име!
ется неопределенное значение (NULL), то результат сравнения в опе!
рации NOT IN будет NULL.
Использование подзапросов в предложении HAVING
Подзапросы можно использовать не только в предложениях
WHERE, определяющих условия выбора строк, но и в условии выбо!
ра групп строк, задаваемых предложением HAVING.
Пример 31
Для получения списка отделов, в которых средняя зарплата боль!
ше, чем в пятом отделе, можно исполнить запрос:
37
SELECT OTDEL, Avg (ZARPL) AS [средняя зарплата]
FROM Sotr
GROUP BY OTDEL
HAVING Avg (ZARPL) > (SELECT Avg (ZARPL)
FROM Sotr WHERE OTDEL = 5)
С помощью подзапроса определяется средняя зарплата в пятом
отделе, значение которой далее используется для сравнения со сред!
ней зарплатой в каждом отделе.
Вложенность подзапросов
Подзапросы могут быть вложенными один в другой.
Пример 32
SELECT FAM, NAME, DOL, ZARPL
FROM Sotr
WHERE ZARPL > (SELECT Max (ZARPL)
FROM Sotr
WHERE OTDEL = (SELECT OTDEL
FROM Otdeli
WHERE NAME_OTDEL = 'специальный'))
Допускается до 255 уровней вложенности подзапросов.
Основные правила при формировании вложенных подзапросов:
– вложенный подзапрос должен быть заключен в скобки и располагать!
ся в правой части условия внешнего по отношению к нему запроса;
– подзапрос не может иметь предложения ORDER BY, которое
может появиться только в конце основного запроса;
– число столбцов в SELECT!списке вложенного подзапроса, их
порядок и типы данных должны быть такими же, как в левой части
условия родительского запроса;
– вложенные подзапросы всегда выполняются от внутренних под!
запросов к внешним.
Подзапросы могут:
– возвращать одну и более строк,
– использовать агрегатные функции и предложение GROUP BY,
соединять таблицы,
– выбирать данные из таблицы, которая присутствует в основном
запросе,
– использовать операции над множествами.
Таблицам, как и полям, можно давать другие имена!псевдонимы.
Операторы EXISTS и NOT EXISTS часто используются в условии
для взаимосвязанных запросов и определяют наличие или отсутствие
возвращаемого подзапросом значения. Если подзапрос обнаружива!
38
ет хотя бы одну строку, удовлетворяющую условию поиска, опера!
тор EXISTS (существуют) возвращает значение TRUE, в противном
случае – FALSE.
Оператор NOT EXISTS может оказаться более корректным, чем
оператор сравнения NOT IN, когда подзапрос находит пустые значе!
ния. Условие NOT IN возвращает значение NULL, когда в списке срав!
ниваемых значений появляется пустое значение, что в большинстве
случаев имеет смысл FALSE.
Пример 33
Найти поставщиков, которые ничего не поставляют.
Запрос вида
SELECT *
FROM Post
WHERE PN NOT IN (SELECT PN FROM PD)
может не вернуть ни одной строки, в то время как запрос
SELECT *
FROM Post P
WHERE NOT EXISTS (SELECT PN
WHERE PN = P.PN)
FROM PD
вернет корректный результат. Здесь P – псевдоним для таблицы Post.
Пример 34
Выдать сведения о деталях (номер, имя и цену), для которых есть
поставки. Эта задача может быть решена с помощью запроса
SELECT D.DN AS [номер детали],
D.DIM AS [имя детали], D.CENA AS [цена детали]
FROM Det AS D
WHERE EXISTS (SELECT *
FROM PD AS P
WHERE D.DN = P.DN)
Пример 35
Отобрать все поставки для деталей, не дороже 200 р. Эту задачу
также можно решить с помощью запроса
SELECT PD.*
FROM PD
WHERE PD.DN In (SELECT DN
FROM Det
WHERE CENA < 200)
Пример 36
Выдать сведения о сотрудниках, зарплата которых больше средней
зарплаты в их отделе. Это так называемый взаимосвязанный запрос
39
SELECT *
FROM SOTR s
WHERE ZARPL > (SELECT avg (ZARPL) FROM SOTR m
WHERE m.OTDEL = s.OTDEL)
ORDER BY OTDEL
Управляющие запросы
Создание таблицы
Синтаксис оператора SQL (см. лаб. работу № 1):
CREATE TABLE
таблица (поле_1 тип [(размер)]
[NOT NULL] [индекс_1] [, поле_2 тип [(размер)]
[NOT NULL] [индекс_2] [, ...]] [,
CONSTRAINT составнойИндекс [, ...]] )
Здесь предложение CONSTRAINT используется для индексирова!
ния поля.
Простой индекс:
CONSTRAINT имя индекса{PRIMARY KEY | UNIQUE | NOT
NULL
REFERENCES внешняяТаблица [(внешнееПоле_1,
внешнееПоле_2)]}
Составной индекс:
CONSTRAINT имя индекса
{PRIMARY KEY (ключевое_1[, ключевое_2 [,...]] ) |
UNIQUE (уникальное_1[, уникальное_2 [,...]] ) |
NOT NULL (непустое_1[, непустое_2 [,...]] ) |
FOREIGN KEY (ссылка_1[, ссылка_2 [,...]] )
REFERENCES внешняяТаблица [(внешнееПоле_1
[, внешнееПоле_2 [,...]] )]}
Предложение CONSTRAINT позволяет создать для поля индекс
одного из двух описанных ниже типов:
– UNIQUE – для создания уникального индекса. Это означает, что
в таблице не может быть двух записей, имеющих одно и то же значе!
ние в этом поле. Уникальный индекс создается для любого поля или
любой группы полей. Если в таблице определен составной уникаль!
ный индекс, то комбинация значений включенных в него полей дол!
жна быть уникальной для каждой записи таблицы, хотя отдельные
поля и могут иметь совпадающие значения;
– PRIMARY KEY – для создания первичного ключа таблицы, состо!
ящего из одного или нескольких полей. Все значения ключа табли!
цы должны быть уникальными и не значениями Null. Кроме того, в
таблице может быть только один ключ;
40
Не используйте зарезервированные слова PRIMARY KEY при
создании индекса в таблице, в которой уже определен ключ.
В противном случае возникнет ошибка.
– FOREIGN KEY – для создания внешнего ключа (ключа связи).
Если ключ внешней таблицы состоит из нескольких полей, необхо!
димо использовать предложение CONSTRAINT, предназначенное для
создания составного индекса. При этом следует перечислить все поля,
содержащие ссылки на поля во внешней таблице, а также указать
имя внешней таблицы и имена полей внешней таблицы, на которые
ссылаются поля, перечисленные выше, причем в том же порядке. Если
последние поля являются ключом внешней таблицы, то указывать
их необязательно, поскольку ядро базы данных считает, что в каче!
стве этих полей следует использовать поля, составляющие ключ
внешней таблицы;
– ссылка_1, ссылка_2 – имена одного или нескольких полей
дочерней таблицы, включенных во внешний ключ, которые содер!
жат ссылки на поля в другой таблице;
– ВнешняяТаблица – имя внешней (родительской) таблицы, ко!
торая содержит поля связи, указанные с помощью аргумента внеш$
нееПоле;
– внешнееПоле_1, внешнееПоле_2 – имена одного или несколь!
ких полей во внешнейТаблице, на которые ссылаются поля, ука!
занные с помощью аргумента ссылка_1, ссылка_2. Это предложе!
ние можно опустить, если данное поле является ключом внешнейТаб$
лицы.
Пример 37
Создать таблицу Klient с числовым полем KN (номер клиента),
текстовым полем FAM (фамилия клиента) размером 15 символов,
текстовым полем NAME (имя клиента) размером 10 символов, тек!
стовым полем DOL (должность) размером 20 символов и денежным
полем ZARPL (зарплата клиента). Объявить первичным ключом поле
KN.
Это можно сделать запросом:
CREATE TABLE Klient (KN int
CONSTRAINT KN PRIMARY KEY, FAM char(15),
NAME char(10), DOL char(20), ZARPL money)
Модификация структуры таблицы
Модификация структуры таблицы (добавление или удаление поля)
осуществляется инструкцией ALTER TABLE, синтаксис которой опи!
сывается так:
41
ALTER TABLE таблица {ADD { поле тип[(размер)] [NOT NULL]
[CONSTRAINT индекс] | CONSTRAINT составнойИндекс} |
DROP {COLUMN поле | CONSTRAINT имяИндекса} },
где ADD – добавить, DROP – удалить, COLUMN – поле.
Пример 38
Добавить в таблицу Sotr новое текстовое поле ADRES размером
30 символов можно оператором:
ALTER TABLE Sotr ADD ADRES char(30),
а удалить поле ADRES!оператором:
ALTER TABLE Sotr DROP COLUMN ADRES
Создание индекса
Индексы представляют собой наборы уникальных для данной таб!
лицы значений и соответствующий им список указателей на страни!
цы данных, где эти значения находятся в таблице физически. Ин!
дексы позволяют ускорить выборку информации из таблиц базы дан!
ных. В SQL Server существуют индексы двух типов: кластерные и
некластерные.
При определении для таблицы кластерного индекса данные физи!
чески сортируются в порядке индекса. Поскольку создание кластер!
ного индекса приводит к физической сортировке данных, у каждой
таблицы может быть лишь один кластерный индекс. Обычно клас!
терный индекс создается для первичного ключа.
Использование кластерного индекса может значительно сократить
время доступа к данным. Однако обновление данных заметно замед!
ляется за счет физического упорядочения данных. Кроме того, об!
новление кластерного индекса влечет за собой обновление всех не!
кластерных индексов (а их может быть до 249).
Упрощенный синтаксис оператора создания индекса имеет следу!
ющий вид:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX имя_индекса ON имя_таблицы (столбец1 [,...])
[WITH FILLFACTOR = x]
Оператор содержит следующие параметры:
– UNIQUE запрещает наличие повторяющихся значений в индек!
сируемом столбце;
– CLUSTERED | NONCLUSTERED создает кластерный (некластер!
ный) индекс;
– имя_индекса определяет имя индекса, уникальное в пределах
таблицы;
42
– список столбцов определяет столбцы, включаемые в индекс.
Опция FILLFACTOR позволяет оптимизировать работу с таблицей.
Дополнительную информацию по данной теме можно найти в [2].
Пример 39
Создать уникальный кластерный индекс с именем abc для столбца
KN таблицы Klient:
CREATE UNIQUE CLUSTERED INDEX abc ON Klient (KN)
Удаление таблицы или индекса
Синтаксис оператора удаления индекса:
DROP INDEX имя_таблицы.имя_индекса
Пример 40
Удалить индекс, созданный в примере 39.
DROP INDEX Klient.abc
Удалить таблицу из базы данных можно оператором:
DROP TABLE имя_таблицы
Удаленная таблица навсегда пропадает из базы данных. Это дей!
ствие отменить невозможно.
Объединение запросов
Запрос на объединение объединяет результаты нескольких неза!
висимых запросов.
Синтаксис:
запрос_1 UNION [ALL] запрос_2
[UNION [ALL] запрос_n [... ]],
По умолчанию повторяющиеся записи не возвращаются при ис!
пользовании операции UNION, однако можно добавить предикат
ALL, чтобы гарантировать возврат всех записей. Кроме того, такие
запросы выполняются быстрее.
Все запросы, включенные в операцию UNION, должны содержать
одинаковое число полей. Избыточные дубликаты из результата ис!
ключаются.
Используйте псевдонимы только в первом предложении SELECT,
потому что в остальных они пропускаются. В предложении ORDER
BY ссылайтесь на поля по их названиям в первом предложении
SELECT.
В каждом аргументе запроса допускается использование пред(
ложения GROUP BY или HAVING для группировки возвращае(
мых данных.
43
В конец последнего аргумента запроса можно включить пред(
ложение ORDER BY, чтобы отсортировать возвращенные дан(
ные.
Пример 41
Выдать номера деталей, цена которых больше 200, а также номе!
ра деталей, поставляемых в количестве более 100. Это можно сде!
лать запросом:
SELECT Det.DN
FROM Det
WHERE Det.CENA > 200
UNION
SELECT PD.DN
FROM PD
WHERE PD.KOL > 100
Пример 42
Создание структуры таблиц и схемы данных операторами SQL.
Пусть требуется создать базу данных, схема которой изображена
на рис. 5. Из рисунка видно, что таблицы клиенты и товар являют!
ся родительскими для таблицы покупка, которая выступает в роли
дочерней таблицы.
П е р в ы й способ. Сначала создаются таблицы, а затем связи.
1. Создать таблицу клиенты запросом:
CREATE TABLE клиенты(номерк INT PRIMARY KEY,
имяк CHAR(15), город CHAR(20))
2. Создать таблицу товар запросом:
CREATE TABLE товар(имят CHAR(15) PRIMARY KEY,
цена MONEY)
3. Создать таблицу покупка запросом:
CREATE TABLE покупка(номеркп INT, имятп CHAR(15),
кол BIGINT, CONSTRAINT номеркп_имятп
PRIMARY KEY(номеркп, имятп))
4. Создать связи запросом:
ALTER TABLE покупка ADD CONSTRAINT связи
FOREIGN KEY(номеркп)
REFERENCES клиенты(номерк),
FOREIGN KEY(имятп)
REFERENCES товар(имят)
В т о р о й способ. Сначала создаются родительские таблицы (кли!
енты, товары), как только что было описано, а затем создается до!
44
Рис. 5. Схема данных
черняя таблица (покупка) вместе со связями следующим операто!
ром:
CREATE TABLE покупка(номеркп INT, имятп CHAR(15),
кол BIGINT,
CONSTRAINT номеркп_имятп
PRIMARY KEY номеркп, имятп),
CONSTRAINT связи
FOREIGN KEY(номеркп)
REFERENCES клиенты(номерк),
FOREIGN KEY(имятп)
REFERENCES товар(имят))
Задания для самостоятельного выполнения
Задание 1. Вывести без повторения города, в которых размещают!
ся поставщики.
Задание 2. Вывести без повторения имена поставляемых деталей.
Задание 3. Вывести для всех поставщиков из городов, начинаю!
щихся на букву М, следующие сведения: номер поставщика, его имя,
статус и город, в котором поставщик размещается.
Задание 4. Вывести сведения о поставках в заданном диапазоне дат.
Задание 5. Вывести все сведения о деталях, цена которых больше
100.
Задание 6. Вывести сведения о поставках в количестве от 100 до
400 шт.
45
Задание 7. Все поставки увеличились на 100 шт. Создать запрос,
который смог бы отобразить эти изменения. В ответе вывести не толь!
ко новые количества поставляемых деталей, но и указать старые зна!
чения.
Задание 8. Вывести сведения о поставщиках из городов Москва,
Самара, СПб, номера которых больше трех.
Задание 9. Вывести сведения о поставщиках из Москвы и Петер(
бурга или статус которых больше, чем 40.
Задание 10. Вывести всех продавцов из таблицы Sotr.
Задание 11. Вывести всех поставщиков из таблицы Post, имею!
щих номера телефонов, начинающиеся на (812), со следующей циф!
рой 2 или 3.
Задание 12. Вывести всех поставщиков из таблицы Post, имею!
щих номера телефонов, начинающиеся на (812), со следующей циф!
рой от 5 до 8.
Задание 13. Вывести всех поставщиков из таблицы Post, имею!
щих номера телефонов, начинающиеся на (8), и не содержащие циф!
ру 1 в третьей позиции.
Задание 14. Сгруппировать данные по полю PN в таблице PD.
Вычислить количество записей в каждой группе и суммарное количе!
ство поставляемых деталей для каждой группы поставщиков.
Задание 15. Используя группировку данных по городу, вывести
без повторений города, в которых размещаются поставщики.
Задание 16. Вычислить суммарные поставки для каждого вида
детали для деталей, количество которых в группе превышает 1.
Задание 17. Вывести все сведения о поставках, упорядоченных по
возрастанию значений номеров деталей и по убыванию значений но!
меров поставщиков, их поставляющих.
Задание 18. Вывести все сведения о 20 % самых дешевых дета!
лях. Из ответа исключить детали, для которых цена отсутствует (име!
ет значение Null).
Задание 19. Вывести города, из которых поставляются болты.
Задание 20. Выдать сведения о поставщиках, не поставляющих
детали. Сохранить результат запроса в отдельной таблице.
Задание 21. Отобрать города, где размещаются поставщики, по!
ставляющие болты, а также имена этих поставщиков, количество и
дату поставки. Сохранить отобранные сведения в новой таблице.
Задание 22. Добавить в таблицу, созданную в задании 21, сведе!
ния о поставщиках, поставляющих шайбы.
Задание 23. Добавить в таблицу, созданную в задании 21 нового
поставщика по имени Петров из города Киева, который поставил
ролики 28 декабря 2000 г. в количестве 100 шт.
46
Задание 24. Используя базу данных о сотрудниках (см. рис. 2),
отобрать названия всех отделов (в том числе и тех, в которых нет
сотрудников), имена и фамилии сотрудников, упорядочив данные по
названию отдела.
Задание 25. Удалить все сведения о поставщике с номером 5 и все
его поставки одним запросом.
Задание 26. Найдите служащих, получающих зарплату, большую
чем у всех других служащих, работающих в должности продавца кос!
метики. Упорядочите данные по убыванию значения зарплаты.
Задание 27. Используя подзапрос, найдите все отделы, в которых
нет служащих.
Задание 28. Найдите отдел, в котором суммарный годовой доход
сотрудников максимален.
Задание 29. Получите список сотрудников, имеющих один из трех
самых высоких окладов в компании. Выведите их имена и оклады.
Задание 30. Найти всех поставщиков, поставляющих большее
количество деталей, чем поставщик с номером 2.
Задание 31. Найти поставщика, для которого суммарное количе!
ство поставляемых деталей максимально.
Задание 32. В каком месяце было поставлено больше всего дета!
лей.
Задание 33. Указать имена и города поставщиков, поставивших
детали в марте.
Задание 34. Создать таблицу Spravka с полями:
KOD_PRED (код предприятия) – числовое или счетчик,
TIP_PRED (тип предприятия) – текстовое поле длины 20,
COMMENT (примечания) – поле примечания.
Объявить поле KOD_PRED первичным ключом.
Задание 35. Добавить в таблицу Post поле KOD_PRED такого же
типа, как и в задании 34.
Задание 36. Связать таблицы Post и Spravka по полю KOD_PRED.
Задание 37. Индексировать поле GOR в таблице Post. Убедиться
в том, что индекс создан.
Задание 38. Удалить только что созданный индекс.
Задание 39. Напишите запрос, печатающий звездочку напротив
имени детали, поставленной последней по времени.
Задание 40. Напечатать символ * напротив имени поставщика,
поставляющего максимальное количество деталей.
47
Лабораторная работа № 3
Представления и хранимые процедуры
Представлением называется логический «вид» данных, храня!
щихся в базе данных. С практической точки зрения представление
является предварительно проанализированной командой SELECT,
которая интерпретируется как таблица. Представления в SQL Server
очень похожи на запросы в Microsoft Access. Команда создания пред!
ставления имеет следующий синтаксис:
CREATE VIEW имя_представления
AS
команда_SELECT
Представление может создаваться на основе одной или несколь!
ких таблиц.
Пример 43
Создадим представление, с помощью которого отбираются все све!
дения о поставщиках из Москвы.
CREATE VIEW Post_Gorod
AS
SELECT *
FROM Post
WHERE GOR = ‘Москва’
Хранимая процедура (stored procedure) – это набор операторов
управления потоком, которые компилируются и хранятся в базе дан!
ных на сервере.
Результаты, получаемые с помощью представлений, можно полу!
чить и с помощью хранимой процедуры. Однако хранимая процедура
обладает большими возможностями.
Преимущества хранимых процедур:
– хранимые процедуры содержатся на сервере в уже откомпилиро!
ванном виде, поэтому они выполняются быстрее SQL!кода, который
хранится на клиентской стороне;
– разумное использование хранимых процедур позволяет центра!
лизовать бизнес!правила и другую логику приложения, сократить
количество избыточного кода и тем самым упростить дальнейшее
сопровождение проекта;
– в хранимые процедуры можно включать сложные операторы
управления потоком и транзакциями, благодаря чему программный
код клиентской части проекта упрощается;
48
– возможность организации доступа к данным таблиц посредством
хранимых процедур избавляет разработчика от необходимости пре!
доставлять пользователям разрешение на доступ к таблицам и тем
самым упрощает защиту базы данных, уменьшая вероятность внесе!
ния в нее изменений, которые могут привести к катастрофе.
Хранимые процедуры бывают системные и пользовательские.
Имя системной процедуры всегда начинается с sp_. Например,
получить сведения о таблице можно с помощью системной процеду!
ры sp_help. Вызов этой процедуры осуществляется оператором:
EXEC sp_help имя_таблицы
Создание пользовательской хранимой процедуры может быть вы!
полнено несколькими способами:
– из adp!проекта ⇒ Запрос / Создать / Ввод хранимой процедуры;
– с помощью Enterprise Manager из контекстного меню на папке
Stored Procedure для рассматриваемой базы данных выполнить ко!
манду ⇒ New Stored Procedure;
– с помощью мастера ⇒ Tools / Wizards / опция Database/
Create Stored Procedure Wizard. Мастер позволяет создавать проце!
дуры для добавления, удаления и обновления данных в таблицах;
– из окна анализатора запросов оператором создания хранимой
процедуры, синтаксис которой такой:
CREATE PROC[EDURE] имя_процедуры
[@параметр тип_данных [ = значение_по_умолчанию] [OUTPUT]] [,...]
AS
команды SQL
Ниже описаны аргументы, используемые при создании хранимых
процедур:
– имя_процедуры – определяет имя хранимой процедуры,
– параметр – представляет формальный параметр, получаемый
процедурой,
– ключевое слово OUTPUT определяет выходной параметр.
Пример 44
Хранимая процедура, извлекающая из таблицы Post данные
о поставщиках из заданного города, может быть такой:
CREATE PROC Post_g
@gor char(15)
AS
SELECT *
FROM Post
WHERE GOR = @gor
49
Вызываются хранимые процедуры с помощью следующей команды:
EXEC[UTE] имя_процедуры [@список_фактических_параметров]
Выполнение хранимой процедуры, только что набранной в окне
анализатора запросов, осуществляется командой ⇒ Query/Execute.
Таким образом, приведенная выше процедура будет запускаться
на выполнение командой:
EXEC Post_g @gor = ‘Москва’
При запуске процедуры из adp!проекта значение параметра @gor
будет запрашиваться в диалоге (запрос с параметром).
Хранимая процедура может иметь и локальные переменные, опи!
сываемые оператором DECLARE, синтаксис которого такой:
DECLARE @имя_переменной тип [,…]
Пример 45
Хранимая процедура для добавления одной записи в таблицу Det
с полями DN, DIM, CENA может быть такой:
CREATE PROC Ins_Det
@DN int,
@DIM char(15),
@CENA money
AS
/* разрешение на возвращение значения переменной @newDN */
SET NOCOUNT ON
/*описание локальной переменной */
DECLARE @newDN int
/*добавление записи в таблицу Det */
INSERT INTO Det(DN, DIM, CENA)
VALUES (@DN, @DIM, @CENA)
/*присвоение переменной @newDN значения поля DN из только что введен$
ной записи*/
SET @newDN = @DN
/*вывод введенного значения поля DN*/
SELECT @newDN AS newDN
Вызов этой процедуры осуществляется оператором:
EXEC Ins_Det 10, ‘шайба’, 250
Исполнение процедуры из adp!проекта вызовет запрос на ввод па!
раметров для DN, DIM и CENA.
Использование операторов управления потоком
В хранимых процедурах можно использовать операторы управле!
ния потоком, приведенные в табл. 4.
50
Таблица 4. Операторы управления потоком
Оператор
Описание
Выполняет блок операторов в цикле, пока ис!
WHILE
тинно заданное условие
BREAK
Принудительный выход из цикла WHILE
BEGIN . . . END
Определяет блок операторов T!SQL
CONTINUE
Осуществляет переход к началу цикла WHILE
Описывает локальную переменную
DECLARE
EXEC[UTE]
Выполняет хранимую процедуру
GO TO
Выполняет переход к метке
IF . . . ELSE
Ветвление
Выводит строку в окно анализатора запросов
PRINT
SET NOCOUNT ON/OFF ON – разрешает, OFF – не разрешает вывод
Прекращает выполнение хранимой процедуры
RETURN
Приостанавливает выполнение хранимой
WAITFOR
процедуры на определенное время
Пример 46
Использование операторов: BEGIN . . . END , IF . . .ELSE , PRINT ,
RETURN:
CREATE PROC ttt
@DN int
AS
IF (SELECT Count(*) FROM Det
WHERE DN = @DN)=1
BEGIN
/* успех*/
PRINT 'успех'
RETURN
END
ELSE
BEGIN
/* неудача*/
PRINT 'неудача'
RETURN
END
Оператор SELECT после IF должен быть заключен в круг(
лые скобки.
При вызове процедуры ttt оператором EXEC ttt @DN = 1 в окне
анализатора запросов будет выведено слово успех, так как в таблице
51
Det присутствует запись для DN = 1, а при вызове процедуры опера!
тором
EXEC ttt @DN = 100 будет выведено слово неудача.
Пример 47
Использование оператора WAITFOR. Синтаксис:
WAITFOR {DELAY ‘задержка’ | TIME ‘время’}
Ключевое слово DELAY определяет длительность задержки в фор!
мате часы: мин: сек, а ключевое слово TIME определяет абсолютное
время возобновления выполнения программы в том же формате, на!
пример:
WAITFOR DELAY ’00: 00: 05'
WAITFOR TIME ’17: 00: 05'
Пример 48
Использование оператора WHILE. Будем увеличивать цену дета!
лей до тех пор, пока средняя цена детали не составит 300 денежных
единиц. Хранимая процедура, решающая эту задачу, может быть
такой:
CREATE PROC Increase_AvgCena
AS
SET NOCOUNT ON $$ разрешение вывода
/*описание локальной переменной */
DECLARE @oldAvg money,
@newAvg money
/*вычисляем и сохраняем старую среднюю цену */
SELECT @oldAvg = (SELECT Avg(CENA)
FROM Det)
WHILE (SELECT Avg(CENA) FROM Det )< 300
BEGIN
UPDATE Det
SET CENA = CENA + 10 $$ 10 $ шаг увеличения цены
END
/* вычисление новой средней цены */
SELECT @newAvg = (SELECT Avg(CENA)
FROM Det)
/*возвращаем старую и новую среднюю цену */
SELECT @oldAvg AS старая_ср_цена,
@newAvg AS новая_ср_цена
Вызов процедуры выполняется оператором
EXEC Increase_AvgCena.
В результате будет выведена старая и новая средняя цены, напри!
мер, следующим образом:
52
Модификация хранимой процедуры осуществляется оператором
ALTER PROC имя_процедуры
..............
или с помощью Enterprise Manager: из контекстного меню на имени
пользовательской процедуры открыть окно свойств командой ⇒
Properties.
Удаление хранимой процедуры осуществляется оператором
DROP PROC имя_процедуры
Задания для самостоятельного выполнения
Задание 1. Создать два!три представления из одной и нескольких
таблиц используемой базы данных.
Задание 2. Создать хранимые процедуры для добавления, удале!
ния и корректировки данных одной таблицы.
Задание 3. Создать хранимую процедуру, использующую цикл
WHILE с принудительным выходом из цикла, а также с приостанов!
кой выполнения программы.
Задание 4. Создать хранимую процедуру, выполняющую добавле!
ние записей в родительскую и дочернюю таблицы.
Задание 5. Создать хранимую процедуру, удаляющую запись из
родительской таблицы и все связанные с ней записи из дочерних таб!
лиц.
53
Лабораторная работа № 4
Транзакции
Транзакция позволяет сгруппировать и последовательно выпол!
нить несколько операторов TRANSACT!SQL. Транзакция – это про!
цесс, в котором или все операторы выполняются успешно, или не
выполняется ни один оператор, или не выполняются операторы пос!
ле точки сохранения. Классическим примером транзакции является
перевод определенной суммы денег с одного счета на другой. Эта опе!
рация состоит из двух частей:
– уменьшение суммы на счете, с которого снимается;
– увеличение суммы на счете, на который осуществляется пере!
вод.
Транзакция должна обладать следующими свойствами:
– атомарность, или элементарность (Atomic), т. е. неделимость
транзакции на отдельные части. Она или выполняется целиком, или
не выполняется;
– согласованность, или целостность (Consistent) – выполнение
транзакции не может нарушать целостность базы данных как в слу!
чае успешного, так и аварийного завершения;
– изолированность (Isolated) – транзакция не взаимодействует и
не конфликтует с другими транзакциями в базе данных. Другими
словами, транзакция должна быть автономной и не должна воздей!
ствовать на другие транзакции или зависеть от них;
– устойчивость, или надежность (Durable) – гарантирует выпол!
нение требуемых действий независимо от внешних событий. Если
неожиданно отключается питание, выйдет из строя линия связи или
даже разрушится сервер – все равно после повторной загрузки серве!
ра база данных будет в целостном состоянии.
При проектировании транзакции следует учитывать перечислен!
ные свойства и стараться писать согласованный с ними программ!
ный код.
Типы транзакций:
– явные;
– автоматически фиксируемые;
– неявные.
Явные транзакции – это транзакции, определяемые напрямую
SQL!опреторами. Для задания транзакции используются ключевые
слова:
– BEGIN TRAN[SACTION] – начать транзакцию;
– COMMIT TRAN[SACTION] – закрепить результаты работы тран!
закции (нормальное завершение транзакции);
54
– ROLLBACK TRAN[SACTION] – откат (отмена) транзакции;
– SAVE TRAN[SACTION] – определение точки сохранения (час!
тичный откат).
Схематический пример транзакции:
BEGIN TRAN
UPDATE.......
INSERT........
SELECT........
COMMIT TRAN
В операторе BEGIN TRAN можно задавать имя транзакции.
Пример 49
Удалить из таблицы Post запись с номером поставщика 125. Если
при удалении возникнет ошибка, то отменить транзакцию:
BEGIN TRAN
DELETE Post WHERE PN = 125
/* @@Error $ глобальная переменная, которая при возникновении ошибки
принимает значение больше нуля */
IF @@Error > 0
ROLLBACK TRAN $$ откат транзакции
ELSE
COMMIT TRAN $$ нормальное завершение транзакции
Внутри транзакции можно создать точку сохранения. В этом слу!
чае отменяются только те операции, которые выполняются после
точки сохранения.
Пример 50
BEGIN TRAN
UPDATE table1 SET col1 = 5 WHERE col2 = 14
SAVE TRAN savepoint1
INSERT table2 VALUES(3,16)
IF @@Error > 0
ROLLBACK TRAN savepoint1 $$ откат всего
$$ после точки сохранения
DELETE table3 WHERE col1 > 2
IF @@Error > 0
ROLLBACK TRAN $$ откат всей транзакции
ELSE
COMMIT TRAN $$нормальное завершение транзакции
При работе с транзакциями используется глобальная перемен!
ная @@trancount. Эту переменную можно использовать для диагно!
стики. Значение переменной @@trancount задается по определенным
55
правилам. Так, когда выполняется оператор BEGIN TRAN, значе!
ние этой переменной увеличивается на 1. При выполнении
ROLLBACK TRAN переменная очищается, если имя транзакции не
указано. Если имя транзакции указано, то переменная сохраняет свое
значение:
SELECT @@trancount
$$ возвращает 0
BEGIN TRAN t1 $$ t1 $ имя транзакции
SELECT @@trancount
$$ возвращает 1
SAVE TRAN savepoint1
SELECT @@trancount
$$ возвращает 1
BEGIN TRAN t2
SELECT @@trancount
$$ возвращает 2
ROLLBACK TRAN
SELECT @@trancount
$$ возвращает 0
Количество точек сохранения в транзакции не ограничено.
Внутри одной транзакции может присутствовать несколько точек
сохранения с одним и тем же именем, однако фактически при этом
используется только одна, а именно – последняя. Предыдущие точ!
ки сохранения игнорируются.
Пример 51
BEGIN TRAN
INSERT . . . . . .
UPDATE . . . . . .
SAVE TRAN tsave1
DELETE . . . . . .
INSERT . . . . . .
SELECT . . . . . .
SAVE TRAN tsave1
INSERT . . . . . .
DELETE . . . . . .
IF @@Error < > 0
ROLLBACK
TRAN tsave1
ELSE
COMMIT TRAN
В явной транзакции запрещено совместно использовать операторы:
ALTER DATABASE
DROP DATABASE
CREATE DATABASE
и некоторые другие.
Автоматически фиксируемые транзакции выполняются даже,
если операторы объявления транзакции не присутствуют в програм!
ме явно. Например, любое изменение данных сервером рассматрива!
56
ется как транзакция. Например, если последовательно выполняется
пакет из трех операторов (пакет фиксируется оператором GO):
INSERT
UPDATE
DELETE
GO
то каждый из этих операторов SQL является транзакцией. Таким
образом, в указанном пакете выполняется три транзакции. Если один
из этих операторов завершается аварийно, то это никак не влияет на
выполнение других операторов.
Явное использование транзакций имеет некоторые преимущества,
связанные с тем, что, пока транзакция не завершена, элементы жур!
нала транзакций не записываются на диск. Поэтому SQL Server мо!
жет при этом более эффективно использовать дисковое пространство.
Так, если операторы этого примера сгруппировать в одну транзак!
цию, то можно меньше элементов записать в журнал транзакций.
Неявные транзакции включены в язык TRANSACT!SQL для обес!
печения совместимости со стандартом ANSI (American National
Institute).
Когда включается режим неявных транзакций, то автоматически
выполняется оператор BEGIN TRANS. При этом транзакцию можно
завершить или отменить операторами COMMIT или ROLLBACK
TRANS. Включить режим неявных транзакций на уровне сеанса мож!
но оператором
SET IMPLICIT_TRANSACTIONS ON
а выключить
SET IMPLICIT_TRANSACTIONS OFF
Пример 52
CREATE TABLE table1(col1 int Not Null)
BEGIN TRAN
INSERT table1 VALUES(1)
COMMIT TRAN
Здесь использована явная транзакция. Тот же пример при неяв!
ном задании транзакции будет иметь вид:
CREATE TABLE table1(col1 int Not Null)
SET IMPLICIT_TRANSACTIONS ON
INSERT table1 VALUES (1)
COMMIT TRAN
Если включен режим неявных транзакций, то до конца сеанса опе!
раторы ALTER TABLE, CREATE, SELECT, TRUNCATE TABLE,
57
GRANT, DROP, INSERT, REVOKE, DELETE, UPDATE, OPEN дол!
жны быть явно зафиксированы или отменены.
Чтобы избежать возникающих трудностей, советуем не ис(
пользовать неявные транзакции.
При разработке транзакций желательно следовать следующим
рекомендациям:
– создавайте как можно более короткие транзакции, так как SQL
Server использует довольно много ресурсов для того, чтобы убедить!
ся в соответствии транзакции перечисленным выше свойствам (ато!
марности, согласованности и пр.), что может привести к потере про!
изводительности;
– разрабатывайте эффективные стратегии блокировки во избежа!
ние ситуаций, в которых пользователь может извлечь из базы дан!
ных информацию, внесенную в нее еще не завершившейся транзак!
цией;
– исключите возможность ввода информации в базу данных во
время выполнения транзакции;
– исключите возможность открытия транзакции во время просмот!
ра хранящейся в базе данных информации.
– старайтесь уменьшать объем хранящейся в базе данных инфор!
мации, которая каким!либо образом затрагивается при выполнении
транзакции.
Задание для самостоятельного выполнения получить у препо
давателя.
58
Лабораторная работа № 5
Триггеры. Функции. Правила.
Значения по умолчанию
Триггер (trigger) – это особый вид хранимой процедуры, которая
присоединяется к событию сервера и автоматически выполняется,
когда это событие происходит. Чаще всего такими событиями явля!
ются добавление (INSERT), удаление (DELETE) записей и обновле!
ние полей записи (UPDATE).
Типы триггеров:
– AFTER (используется по умолчанию) – выполняется после вне!
сения изменений в таблицу. При обнаружении ошибки изменения
могут быть отменены с помощью оператора отката транзакций
ROLLBACK;
– INSTEAD OF – выполняется перед внесением изменений в таб!
лицу. Триггер «на лету» анализирует корректность вносимых изме!
нений и затем, в зависимости от результата проверки, осуществляет
эти изменения удобным способом.
Триггеры обычно используются для следующих целей:
– если необходимо каскадное изменение через связанные таблицы
в базе данных;
– если база данных денормализована и требуется способ автома!
тизированного обновления избыточных данных в нескольких таб!
лицах;
– если необходимо сверить значение в одной таблице с неидентич!
ным значением в другой таблице;
– если требуется вывод пользовательских сообщений и сложная
обработка ошибок.
Триггер нельзя запустить вручную.
Триггер можно связать с одним событием или с несколькими со!
бытиями.
Синтаксис оператора для создания триггера:
CREATE TRIGGER имя_триггера
ON имя_таблицы | представления
{ FOR | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
[, …]
[WITH ENCRYPTION]
AS [команды SQL]
При создании триггеров необходимо придерживаться следующих
правил:
– нельзя создавать триггеры для временных таблиц. Однако триг!
геры могут обращаться к представлениям и временным таблицам;
59
– триггеры не могут возвращать динамические наборы. Следова!
тельно, использовать в триггере оператор SELECT нужно крайне ос!
торожно. Поэтому в примере 54 оператор SELECT использован в тран!
закции. Обычно в операторе SELECT, входящем в триггер, использу!
ется конструкция IF EXISTS;
– триггеры следует использовать для поддержания целостности
данных, ссылочной целостности и рабочих правил;
– с помощью опции WITH ENCRYPTION исходный код триггера,
хранящийся в таблице syscomments, можно зашифровать;
– в триггерах нельзя использовать операторы CREATE, DROP,
ALTER, SELECT INTO и некоторые другие.
При выполнении операций INSERT, UPDATE или DELETE сервер
создает в памяти две таблицы: inserted и deleted, которые хранят
копии строки только что введенной или удаленной из базы данных.
При выполнении операции UPDATE эти таблицы используются од!
новременно.
Триггер выполняется как неявно определенная транзакция, по!
этому внутри триггера допускается применение команд управления
транзакциями. В частности, при обнаружении нарушений ограниче!
ний целостности для прерывания выполнения триггера и отмены всех
изменений, которые пытался выполнить пользователь, необходимо
использовать команду ROLLBACK TRANSACTION. В случае успеш!
ного завершения триггера можно использовать команду COMMIT
TRANSACTION.
Выполнение команд ROLLBACK TRANSACTION и COMMIT
TRANSACTION не прерывает работу триггера.
При запуске триггера исходная таблица находится в состоя(
нии, в которое ее привело бы успешное завершение транзакции
(все изменения, добавления и удаления строк выполнены).
В зависимости от типа операции содержимое таблиц inserted и
deleted может быть различным.
Команда INSERT. В таблице inserted будут содержаться все стро!
ки, которые пользователь пытается вставить в таблицу. Таблица
deleted не будет содержать ни одной строки. Исходная таблица после
выполнения команды Insert будет содержать вставляемые строки.
После завершения триггера все строки из таблицы inserted будут
окончательно вставлены в таблицу.
Команда DELETE. В таблице deleted будут содержаться все стро!
ки, которые пользователь пытается удалить из таблицы. Таблица
inserted не будет содержать ни одной строки. Исходная таблица пос!
ле выполнения команды Delete не будет содержать удаляемые стро!
60
ки. После завершения триггера все строки, содержащиеся в таблице
deleted, будут окончательно удалены из исходной таблицы.
Команда UPDATE. В таблице deleted будут содержаться старые
значения строк, которые будут удалены из таблицы при успешном
завершении триггера. Таблица inserted будет содержать новые зна!
чения строк. Исходная таблица после выполнения команды Update
будет содержать измененные значения строк. Строки из таблицы
inserted будут окончательно добавлены в исходную таблицу после
успешного завершения работы триггера.
Пример 53
Триггер, информирующий о вставке записи в таблицу поставщи!
ков, имеет вид:
CREATE TRIGGER сообщение
ON Post
FOR INSERT
AS
PRINT 'запись вставлена'
Выполненный из окна анализатора запросов оператор:
INSERT INTO Post(PN, PIM,ST,GOR)
VALUES(15,'Петров',75,'Тула')
добавит в таблицу Post нового поставщика. При этом будет выведено
сообщение
Пример 54
Пусть дана таблица сотрудников, в которой есть поле FIO сотруд!
ника. При занесении в таблицу сведений о новом сотруднике прове!
рим, есть ли сведения об этом сотруднике в таблице. Эту задачу мож!
но решить с помощью следующего триггера:
CREATE TRIGGER Sotr_Ins_Upd
ON Sotr
FOR INSERT
AS
BEGIN TRANSACTION
/* описание временных переменных*/
DECLARE @Sotr_FAM Varchar (50)
DECLARE @Sotr_NOMER int
/* выборка вводимых имен и номеров сотрудников из
61
временно создаваемой таблицы inserted, которой
дается псевдоним i, и сохранение их в перемен$
ных @Sotr_FAM и @Sotr_NOMER */
SELECT @Sotr_FAM = i.FAM
FROM inserted i
SELECT @Sotr_NOMER = i.NOMER
FROM inserted i
/* если вводимое имя сотрудника уже есть в таблице
Sotr, и если номер имеющегося в таблице сотрудни$
ка не совпадает с номером вводимого сотрудника,
то такого сотрудника вводить не допускается по
условию задачи */
IF exists(SELECT FAM, NOMER
FROM Sotr
WHERE (Sotr.FAM = @Sotr_FAM)
AND (Sotr.NOMER <> @Sotr_NOMER))
BEGIN
/* откат транзакции */
ROLLBACK TRANSACTION
PRINT 'такое имя уже есть в таблице'
END
ELSE
BEGIN
/* завершение транзакции и сохранение в
таблице Sotr введенного сотрудника*/
COMMIT TRANSACTION
PRINT 'вставка завершена успешно'
END
При выполнении оператора:
INSERT INTO Sotr(NOMER, FAM, OTDEL)
VALUES (20, 'Бобиков',3)
триггер разрешит вставить указанную запись в таблицу Sotr, если
она не содержала записи с именем Бобиков.
Пример 55
Пусть таблицы Det и PD не связаны между собой и ссылочная це!
лостность данных поддерживается из программы. Создадим триггер,
который будет поддерживать ссылочную целостность данных при
выполнении операции удаления записей:
/* Создание триггера для контроля ссылочной целостности данных при
выполнения операции удаления записей из таблицы Det. Если
целостность данных нарушается, то есть в таблице PD
остаются записи, для которых родительская запись из таблицы
62
Det удалена, то триггер выдает сообщение о нарушении целостности
данных и отменяет удаление */
CREATE TRIGGER PD_Det
ON Det
FOR DELETE
AS
IF EXISTS (SELECT * FROM deleted, PD
WHERE
PD.DN = deleted.DN )
ROLLBACK
TRANSACTION
PRINT 'Нельзя удалять деталь'
Выполненный из окна анализатора запросов оператор:
DELETE
FROM Det
WHERE DN = 1
не приведет к удалению детали с номером 1 из таблицы Det, а выдаст
сообщение, указанное в триггере, если в таблице PD имеется запись
с таким же номером детали.
Триггер можно включить (ENABLE TRIGGER) и отключить
(DISABLE TRIGGER) в операторе ALTER TABLE. Например:
ALTER TABLE имя_таблицы DISABLE TRIGGER имя_триггера
ALTER TABLE Sotr ENABLE TRIGGER Sotr_Ins_Upd
Отключить все триггеры, связанные с таблицей Post, можно так:
ALTER TABLE Post DISABLE TRIGGER ALL
Функции
Функции могут быть встроенными и созданными пользователями.
Использование некоторых встроенных функций:
– GETDATE ( ) определяет сегодняшнюю дату;
– CONVERT ( ) выполняет преобразование используемого типа
данных в тип Varchar. Иногда сервер сам выполняет такое преобра!
зование.
Синтаксис функции:
– CONVERT (тип_данных_в_который_преобразуется, Преобразуемый_объект
(имя или литерал), стиль)
Стиль указывает на внешний вид отображаемых данных.
Пример 56
Выдать информацию о номере и цене детали. Преобразовать цену
детали в строку из 10 символов, в начале которой добавить символ
доллара. Это можно сделать так:
63
SELECT DN, 'S' + CONVERT (Varchar (10), CENA)
AS цена
FROM Det
Преобразование целого числа 22 в строку из двух символов можно
так:
CONVERT (Varchar (2), 22)
Вместо функции CONVERT можно использовать встроенную функ!
цию CAST, которая имеет меньшее число параметров (стиль не ука!
зывается).
STUFF ( ) позволяет форматировать строковые значения. Синтак!
сис функции:
STUFF(строка_в_которой_будет_произведена_замена, начало,
_удаляемых_символов, подставляемое_в_строку_значение)
число$
Пример 57
Добавить в начало каждой строки динамического набора данных
из таблицы Post слово номер можно так:
SELECT STUFF (PN, 1, 0,'номер')
FROM Post
В результате выполнения этого запроса получится динамический
набор данных в виде:
PN
номер 1
номер 2
номер 3
........
К встроенным функциям относится класс агрегатных функций,
перечисленных в лабораторной работе № 2.
Создание пользовательских функций. Пользовательские функ!
ции могут возвращать как скалярное значение (одно значение), так и
таблицу. Возвращаемое значение определяется ключевым словом
RETURNS.
Существуют следующие типы пользовательских функций:
– если таблица возвращается одним оператором SELECT, то функ!
ция называется подставляемой табличной функцией. Создание таб!
лицы, таким образом, предполагает, что все имена столбцов и их типы
определены в операторе SELECT;
– если при определении функции задаются новые имена столбцов
и типы, то функция называется многооператорной.
64
Ниже перечислены операторы, которые можно использовать
в многооператорной функции:
– операторы присваивания;
– операторы управления процессом;
– локальные операторы объявления переменных;
– операторы SET/SELECT, использующиеся для присвоения зна!
чений локальным переменным функции.
Недетерминированные функции SQL Server 2000 использовать
внутри функции нельзя, поскольку они могут возвращать различ!
ные значения даже при одинаковых входных данных, например
GETDATE, RAND и др.
Скалярные функции имеют следующий синтаксис:
CREATE FUNCTION имя_функции
([@Имя_параметра скалярный_тип_параметра
значение_по_умолчанию] [, . . .,n])
RETURNS возвращаемый_тип_данных
[AS]
[операторы_SQL]
RETURN [(] оператор_SELECT [)]
[ =
Пример 58
Функция, форматирующая дату в европейский формат: день/ме(
сяц/год, может быть такой:
CREATE FUNCTION DataForm
(@Date DateTime, @DateSeper Char(1))
RETURNS Varchar (20)
AS
BEGIN
DECLARE @ReturnDate Varchar (20)
SET @ReturnDate = CONVERT (Varchar (2),
DAY(@Date )) + @DateSeper + CONVERT(Varchar (2),
MONTH(@Date))+ @DateSeper + CONVERT(Varchar (4),
YEAR (@Date))
RETURN (@ReturnDate )
END
Вызов созданной пользовательской функции можно сделать так:
SELECT dbo.DataForm (GETDATE ( ), ‘/’) AS NateNow
В результате будет выдана сегодняшняя дата в европейском фор!
мате.
При вызове функции необходимо добавить префикс dbo. Префикс
dbo. (владелец, если не задан иной владелец) желательно добавлять
к таким именам объектов как таблицы, хранимые процедуры, функ!
ции, представления и пр.
65
Правила
Правила используются для контроля вводимых значений. Пра!
вило может задаваться несколькими способами:
– как свойство Условие на значение для столбца таблицы;
– с помощью Enterprise Manager;
– операторами TRANSACT!SQL.
Пример 59
Определим правило, которое проверяет значение для номера по!
ставщика (должно быть больше нуля):
CREATE
AS
@PN >0
RULE
Znach_PN_Post
Здесь Znach_PN_Post – имя правила.
Привязка правила к столбцу таблицы осуществляется с помощью
системной хранимой процедуры sp_bindrule следующим образом:
EXEC
sp_bindrule 'Znach_PN_Post','Post.PN'
Здесь задание имени таблицы префикса Post обязательно.
Значения по умолчанию (стандартные значения)
Задавать значения по умолчанию можно теми же способами, что и
правила.
Пример 60
Определим значение СПб для поля GOR таблицы Post как значе!
ние по умолчанию:
CREATE
DEFAULT GOROD AS 'СПб'
Привязка значения по умолчанию к столбцу таблицы осуществ!
ляется с помощью системной хранимой процедуры sp_bindefault:
EXEC sp_bindefault ‘GOROD’,’Post.GOR’
Задания для самостоятельного выполнения
Задание 1. Создайте триггер, предотвращающий добавление но!
вой детали, если для нее не указана цена.
Задание 2. Создайте пользовательскую функцию, вычисляющую
среднее арифметическое (или сумму) цен всех деталей, проданных до
указанной даты.
Задание 3. Создайте правила для трех столбцов для ваших таб!
лиц, используя три способа, описанные выше.
Задание 4. Определить значения по умолчанию для трех столбцов
ваших таблиц способами, описанными выше.
66
ПРИЛОЖЕНИЕ
Копирование и присоединение баз данных
В случае выполнении лабораторных работ дома может возникнуть
проблема копирования базы.
Простым копированием невозможно перенести базу данных с од!
ного носителя информации на другой. Сначала необходимо выпол!
нить операцию отсоединения базы данных (рис. П.1):
1. Откройте Enterprise Manager;
2. Выделите базу данных и из контекстного меню выберите ко!
манду ⇒ Все задачи / Detach Datebase
База данных исчезнет из окна Enterprise Manager. Далее базу мож!
но копировать на любой носитель.
Чтобы начать работать с базой на другом сервере, необходимо вы!
полнить операцию присоединения базы данных.
Для этого в окне Enterprise Manager нужно из контекстного меню,
открытого на папке Databases, выполнить команду ⇒ Все задачи /
Attach Database и указать путь к базе данных (рис. П.2).
На вашем компьютере возможен другой путь.
Рис. П.1. Отсоединение базы данных
67
Рис. П.2. Присоединение базы данных
Обзор каталогов, используемых SQL Server
В табл. П.1 приведен обзор тех каталогов для SQL 2000 – Program
Files / MSSQL, которые появляются после установки SQL Server. Их
расположение зависит от того, какие каталоги вы выбрали для раз!
мещения Data Files и Program Files. Если вы воспользовались вари!
антом, предлагаемым по умолчанию, то все каталоги будут помеще!
ны в каталог Mssql.
Таблица П.1. Описание каталогов SQL Server
Имя каталога
\Backup
\Binn
\Books
\Data
68
Содержимое каталога
Файлы, создаваемые при резервном копировании
DLL!файлы, используемые для выполнения расширенных
хранимых процедур. Здесь же содержатся разнообразные
утилиты и файлы для взаимодействия с операционной
системой
В этом каталоге находится документация Books Online.
Файл Sqlbol.chm представляет собой скомпилированный
HTML файл и может быть запущен как обычный
исполняемый файл
Здесь располагаются файлы баз данных (.mdf) и журналы
транзакций (.ldf). В журнал транзакций записываются
операции, выполняемые сервером, а также состояние базы
данных до и после их выполнения. Журнал транзакций
используется для восстановления базы данных в случае
возникновения ошибок
Продолжение табл. П.1
Имя каталога
\Ftdata
\DevTools
\Html
\Install
\Jobs
\Log
\Repldata
\Upgrade
Содержимое каталога
Kаталог используется Microsoft Search для размещения
полнотекстовых каталогов
В каталоге находятся файлы, применяемые при разра!
ботке программ, использующих ODBC, DB!Library, Open
Data Services, SQL!DMO, Embedded SQL для С, а также MS
DTC
HTML!файлы, используемые Enterprise Manager и SQL!
Server!Agent (заставки)
В каталоге содержатся файлы, используемые в процессе
установки SQL Server
Kаталог для размещения временных файлов в процессе
выполнения заданий
Файлы, содержащие информацию об ошибках
Kаталог используется при выполнении репликации
Файлы, используемые для обновления предыдущих вер!
сий SQL Server
Следует понимать, что в зависимости от типа установки на вашем
диске могут отсутствовать некоторые каталоги.
Библиографический список
1. Хомоненко А. Д. и др. Базы данных: учеб. пособие / под ред.
А. Д. Хомоненко. СПб.: Корона Принт, 2000. 416 с.
2. Пол Литвин и др. Access 2000: Руководство разработчика. Т. 2. Кор!
поративные приложения. Киев: BXV, 2001. 912 с.
3. Пол Литвин и др. Access 2002. Разработка корпоративных приложе!
ний. СПб.: Питер; Киев: BXV, 2003. 848 с.
4. Роб Хоторн. Разработка БД MS SQL Server на примерах. М., 2001.
5. Веймар, Сотел Р. Освой самостоятельно MS SQL Server 2000 за
21 день. М.: Вильямс, 2001. 704 с.
6. Мамаев Е., Шкарина Л. Microsoft SQL Server 2000 (для профессио!
налов). СПб.: Питер, 2001. 1088 с.
7. Артемов Д. В. SQL Server 2000 (установка, управление, оптимиза!
ция, новейшие технологии). М.: Русская редакция, 2001. 576 с.
8. Вескес Дж. Л. Access и SQL Server. М.: Лори, 1998. 362 с.
9. http://www.microsoft.com/sql/
69
СОДЕРЖАНИЕ
Введение ..........................................................................
Лабораторная работа № 1. Создание базы данных
на SQL Server ....................................................................
Лабораторная работа № 2. Работа с анализатором запросов
(Query Analyzer). Основы Transact!SQL .................................
Лабораторная работа № 3. Представления и хранимые
процедуры ........................................................................
Лабораторная работа № 4. Транзакции .................................
Лабораторная работа № 5. Триггеры. Функции. Правила.
Значения по умолчанию .....................................................
Приложение .....................................................................
Библиографический список .................................................
70
3
8
21
48
54
59
67
69
71
Документ
Категория
Без категории
Просмотров
0
Размер файла
254 Кб
Теги
kur, sql, server, labor, rabota, 2000, sreda, rab
1/--страниц
Пожаловаться на содержимое документа