Добавлен: 25.10.2018
Просмотров: 852
Скачиваний: 7
1
Лабораторная работа № 2
по дисциплине
«Базы данных»
на тему:
«
Манипулирование данными с помощью языка
Transact-SQL
в СУБД Microsoft SQL Server 2008»
Составитель:
канд. техн. наук Исмоилов М.И.
2
Лабораторная работа № 2. Манипулирование данными с помощью языка Transact-SQL в СУБД
Microsoft SQL Server 2008.
Цель работы: Изучить синтаксические конструкции языка Transact-SQL, реализованные в
СУБД Microsoft SQL Server 2008, применяемые для вставки, обновления, удаления и выборки
данных.
Задание на лабораторную работу: Для каждого варианта индивидуального задания с
помощью языка Transact-SQL необходимо выполнить действия, описанные в приложении А.
Ход выполнения работы
Использование основных синтаксических конструкций будет продемонстрировано на
тестовой базе данных, физическая модель которой представлена на рис. 1.
Рис. 1. - Физическая модель тестовой базы данных
Для того, чтобы создать структуру БД и заполнить таблицы начальными данными, необходимо
открыть оснастку SQL Server Management Studio и подключиться к серверу СУБД. Затем
необходимо создать тестовую БД (назовём её ProductSales) и нажать кнопку Создать запрос. В
результате появится окно, внешний вид которого представлен на рис 2.
Рис. 2. – Окно ввода нового запроса в оснастке SQL Server Management Studio
3
В верхней части окна должна быть выбрана новая БД, т.к. именно в ней будут созданы
требуемые таблицы. В среднюю часть рассматриваемого окна необходимо ввести программный
код, представленный в листинге 1. Для запуска скрипта необходимо нажать кнопку Выполнить.
--
Вставка значений в таблицу Product
insert into Product --
Несколько значений зез указания
полей
select '
Авторучка', 10
union
select '
Карандаш', 5
union
select '
Линейка', 4
union
select '
Ластик', 8
union
select '
Треугольник', 30
union
select '
Маркер', 20
--
Вставка значений в таблицу Sale
insert Sale --
Без указания полей
values(1,1,'2010-02-01T08:30:00',100)
--
C указанием полей (в порядке объявления)
insert Sale(ClientNo,ProductNo,Dates,Counts)
values(2,1,'2010-02-07T09:15:00',30)
--
C указанием полей (в произвольном порядке)
insert Sale(Dates,Counts, ClientNo,ProductNo)
values('2010-02-02T17:13:00',7,1,2)
--
Несколько строк с указанием полей (в порядке
объявления)
insert into Sale(ClientNo,ProductNo,Dates,Counts)
select 3,2,'2010-03-03T15:10:00',10
union all
select 2,3,'2010-02-05T10:53:00',5
union all
select 1,3,'2010-03-07T14:07:00',3
union all
select 3,4,'2010-02-15T18:09:00',1
union all --
Использование выражение в качестве
значения
select 3,4,'2010-02-16T12:28:00',2*5
union all
select 2,5,'2010-04-07T10:00:00',5
union all
select 1,5,'2010-04-08T09:56:00',2
union all
select 2,5,'2010-04-09T13:15:00',10
Листинг 1. – Программный код создания таблиц в БД и занесения в них данных
Несмотря, на то, что программный код хорошо документирован, он требует
дополнительного рассмотрения. С помощью оператора create table происходит создание таблицы в
текущей базе данных. При этом указываются имена всех полей и присвоенные им типы данных.
Директива not null указывает на то, что для поля недопустимо пустое значение. Ключевое слово
identity
предписывает СУБД самостоятельно генерировать уникальное значение для поля.
Директива primary key определяет первичный ключ в отношении. Для создания ограничения
внешнего ключа используется директива alter table совместно с ключевыми словами foreign key.
---
Создание таблиц в БД
create table Product
(
ProductNo bigint identity(1,1) not null primary key,
ProductName varchar(50) not null,
Price money not null default 0
)
create table Client
(
ClientNo bigint identity(1,1) not null primary key,
ClientName varchar(50) not null
)
create table Sale
(
SaleNo bigint identity(1,1) not null primary key,
ClientNo bigint not null,
ProductNo bigint not null,
Dates smalldatetime not null,
Counts int not null default 0
)
--
Создание внешних ключей для таблицы Sale
alter table Sale
add constraint FK_Sale_Client
foreign key(ClientNo)
references Client(ClientNo)
on update cascade
on delete cascade
alter table Sale
add constraint FK_Sale_Product
foreign key(ProductNo)
references Product(ProductNo)
on update cascade
on delete cascade
--
Вставка значений в таблицу
--
Без указания имени полей Client
insert Client values('ИП Иванов И.И.')
--
С указанием имён полей
insert Client(ClientName)
values('
ООО Эдельвейс')
--
Вставка нескольких значений
insert Client(ClientName)
select '
ИП Петров П.П.'
union all --
Допускается дублирование
select 'ОАО Юг-Креатив'
4
Директива cascade требует каскадного изменения (удаления) значения внешнего ключа при
соответствующем изменении (удалении) этого значения, записанного в поле первичного ключа.
Вставка значений в таблицы (см. листинг 1) выполняется с помощью оператора insert. Для
добавления новой строки необходимо указать имя таблицы и значения для всех обязательных полей
таблицы. Обязательными считаются поля, для которых определена директива not null (для
первичных ключей указывается автоматически) и не прописано ни значение по умолчанию (default)
ни свойство identity. Вставляемые значения указываются после ключевого слова values. Если
порядок и количество соответствует порядку (и количеству) объявления полей в таблице (в
директиве create table), то после имени таблицы названия столбцом можно не указывать. В
противном случае указание столбцов, в которые заносятся значения – обязательно.
Отметим, что операторы вставки (insert), обновления (update) и удаления (delete) могут
выполнять соответствующие операции только для строк одной единственной таблицы.
Результаты занесения значений в таблицы (выполнения скрипта из листинга 1) представлены на
рис. 3.
Рис. 3. – Тестовые значения в БД
Оператор insert имеет следующий синтаксис [1]:
INSERT
<имя таблицы> [(<имя поля>.,..)] VALUES
(<список выражений>) | <запрос>;
Перейдём к рассмотрению операторов update и delete, используемых для обновления и
удаления значений соответственно. Для этого добавим одного клиента и два заказа для него, что
выполняется соответствующим кодом, представленным на рис. 4 (слева).
5
insert Client
values('
ООО Лотос')
insert Sale(ClientNo,ProductNo,Dates,Counts)
values(5,1,'2010-02-03T17:15:00',30)
Рис. 4. – Добавление нового клиента и двух заказов
Результатом выполнения запроса явилось добавление в таблицу Client организации с
название ООО Лотос и информации о продаже (вставка в таблицу Sale) ей авторучек (30 штук) и
карандашей (11 штук) (рис. 4 справа).
Для изменения названия организации ООО Лотос на ОАО Лотос+ необходимо написать
запрос, представленный на рис. 5.
update Client
set ClientName='
ОАО Лотос+'
where ClientNo=5
Рис. 5. – Обновление названия организации ООО Лотос на ОАО Лотос+
Обновление значений в строке выполняется с помощью оператора update, в котором
указывается название обновляемой таблицы (или её псевдоним). После ключевого слова set
указывается перечень полей, в которые заносятся новые значения. При этом обновляются значения
во всех строках, удовлетворяющих предикату, записанному в директиве where. В нашем случае
обновляется только одна строка, так как поле ClientNo первичный ключ и по определению
уникален. Если в приведённом выше запросе убрать фразу where ClientNo=5, то все имеющиеся в
БД организации будут названы ОАО Лотос+.
Рассмотрим более сложный запрос (рис. 6). В этом запросе в каждом заказе, сделанном
организацией ОАО Лотос+, увеличивается количество купленной продукции. При этом если
куплено более 20 единиц, то исходное значение увеличивается в 3 раза, если меньше или равно, то
увеличивается в 2 раза.
insert Sale(ClientNo,ProductNo,Dates,Counts)
values(5,2,'2010-02-03T17:18:00',11)