 |
Задание для индивидуальной работы 3
|
|
|
|
Напишите и отладьте SQL-сценарий создания вашей базы данных и таблиц для нее.
DML. Изменение данных
DML (Data Manipulation Language) – язык манипулирования данными, составная часть SQL.
Рассмотрим его основные команды – команды добавления, изменения и удаления данных INSERT, UPDATE и DELETE.
Для добавления новых строк в таблицу служит команда INSERT:
INSERT [INTO] имя_таблицы [(список_полей)]
VALUES (список_значений);
Например,
INSERT k_firm (firm_name, firm_addr)
VALUES('Альфа', 'Москва');
Список полей можно явно не указывать, тогда в списке значений нужно задавать значения для каждого поля в том порядке, в котором они были созданы.
Для поля с ограничением IDENTITY обычно явное значение указывать нельзя, т.к. оно формируется автоматически. Если требуется явно указывать значения для таких полей, следует предварительно выполнить команду:
SET IDENTITY_INSERT ON
Если мы вставляем значения из одной таблицы в другую, формат команды INSERT следующий:
INSERT [INTO] имя_таблицы [(список_полей)]
(SELECT параметры);
Для обновления данных используется команда UPDATE:
UPDATE имя_таблицы
SET поле1=выражение1 [,…, полеN=ВыражениеN]
[WHERE условие];
Например,
UPDATE k_dept SET staff_num=1
WHERE dept_short_name='Sales';
Если опция WHERE пропущена, изменяться будут все строки таблицы.
Для удаления данных используется команда DELETE:
DELETE [FROM] имя_таблицы [WHERE условие];
Например,
DELETE FROM k_dept WHERE dept_short_name='Sales';
Если опция WHERE пропущена, удалены будут все строки таблицы.
Заполним тестовыми данными нашу базу «Рога и копыта». Кроме команд добавления, для примера рассмотрены несколько команд изменения данных. Обратите внимание, что для полей, имеющих свойство IDENTITY, значения не задаются – они будут генерироваться автоматически.
Строки-константы следует задавать в одинарных кавычках.
|
|
|
Могут возникать некоторые проблемы с заданием констант типа дата. Формат таких констант зависит от региональных настроек операционной системы.
Если вы хотите задать определенный формат даты, например, день:месяц:год, выполните команду:
SET DATEFORMAT dmy
Мы будем использовать формат год:месяц:день
SET DATEFORMAT ymd
при котором константа-дата выглядит так: ‘2012-01-31’
В примерах также используется функция GETDATE(), которая возвращает текущие дату/время.
Таблица "Предприятия"
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Альфа', 'Москва');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Бета', 'Казань');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Гамма', 'Париж');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Дельта', 'Лондон');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Омега', 'Токио');
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_firm;
Результат будет выглядеть следующим образом (поле firm_phone мы не заполняли, поэтому в нем будут значения NULL):
firm_num firm_name firm_addr firm_phone
-------------------------------------------
1 Альфа Москва NULL
2 Бета Казань NULL
3 Гамма Париж NULL
4 Дельта Лондон NULL
5 Омега Токио NULL
(5 row(s) affected)
Таблица "Отделы"
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Sales', 'Отдел продаж');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Mart', 'Отдел маркетинга');
INSERT INTO k_dept (dept_short_name, dept_full_name) VALUES('Cust', 'Отдел гарантийного обслуживания');
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_dept;
dept_num dept_short_name dept_full_name staff_num
-----------------------------------------------------
1 Sales Отдел продаж NULL
2 Mart Отдел маркетинга NULL
3 Cust Отдел гарантийного обслуживания NULL
(3 row(s) affected)
Таблица "Сотрудники"
INSERT INTO k_staff
(staff_name, dept_num, staff_hiredate, staff_post)
VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
INSERT INTO k_staff
(staff_name, dept_num, staff_hiredate, staff_post) VALUES('Петров', 2, '2010-10-13','Менеджер');
INSERT INTO k_staff
(staff_name, dept_num, staff_hiredate, staff_post) VALUES('Сидоров', 3, '2005-12-01','Менеджер');
|
|
|
INSERT INTO k_staff
(staff_name, staff_hiredate, staff_post)
VALUES('Семенов', '1990-01-01','Директор');
INSERT INTO k_staff
(staff_name, dept_num, staff_hiredate, staff_post) VALUES('Григорьев', 3, '2008-12-19','Программист');
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_staff;
staff staff staff dept staff staff
_num _name _post _num _hiredate _termdate
--------------------------------------- -------------------
1 Иванов Менеджер 1 1999-01-01 00:00:00.000 NULL
2 Петров Менеджер 2 2010-10-13 00:00:00.000 NULL
3 Сидоров Менеджер 3 2005-12-01 00:00:00.000 NULL
4 Семенов Директор NULL 1990-01-01 00:00:00.000 NULL
5 Григорьев Программист 3 2008-12-19 00:00:00.000 NULL
(3 row(s) affected)
После того как мы заполнили таблицу "Сотрудники", мы можем в таблице "Отделы" заполнить столбец staff_num, содержащий код руководителя отдела.
UPDATE k_dept SET staff_num=2
WHERE dept_short_name='Mart';
UPDATE k_dept SET staff_num=3
WHERE dept_short_name='Cust';
UPDATE k_dept SET staff_num=1
WHERE dept_short_name='Sales';
Посмотрим результат изменения, для этого выполним следующую команду:
SELECT * FROM k_dept;
dept_num dept_short_name dept_full_name staff_num
--------------------------------------------------------
1 Sales Отдел продаж 1
2 Mart Отдел маркетинга 2
3 Cust Отдел гарантийного обслуживания 3
(3 row(s) affected)
Таблица "Договоры"
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('A', 1, 1,'2011-11-01');
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('B', 1, 2,'2011-10-01');
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('C', 1, 1,'2011-09-01');
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('A', 2, 2,'2011-11-15');
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('B', 2, 2,'2011-08-01');
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('C', 3, 1,'2011-07-15');
INSERT INTO k_contract
(contract_type, firm_num, staff_num, contract_date)
VALUES('A', 4, 1,'2011-11-12');
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_contract;
contract_num contract_date contract_type firm_num staff_num
-----------------------------------------------------------
1 2011-11-01 00:00:00.000 A 1 1
2 2011-10-01 00:00:00.000 B 1 2
3 2011-09-01 00:00:00.000 C 1 1
4 2011-11-15 00:00:00.000 A 2 2
5 2011-08-01 00:00:00.000 B 2 2
6 2011-07-15 00:00:00.000 C 3 1
7 2011-11-12 00:00:00.000 A 4 1
(7 row(s) affected)
Обратите внимание, что даты договоров заполнились автоматически текущими датой и временем – это сработало определение DEFAULT для данного поля.
Таблица "Счета"
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-11-12', '2011-12-12', 1000);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-12-12', '2012-01-12', 2000);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
|
|
|
VALUES(1, '2012-01-12', '2012-02-12',2000);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2011-12-12', '2012-01-12', 6000);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2012-01-12', '2012-02-12', 2000);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(3, '2012-01-12', '2012-02-12', 2500);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(4, '2011-12-12', '2012-01-12', 1500);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2011-12-12', '2012-01-12', 1200);
INSERT INTO k_bill
(contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2012-01-12', '2012-02-12', 10000);
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_bill;
bill bill bill bill contract bill
_num _date _term _peni _num _sum
-----------------------------------------------------------
1 2011-11-12 00:00:00.000 2011-12-12 00:00:00.000 0 1 1000
2 2011-12-12 00:00:00.000 2012-01-12 00:00:00.000 0 1 2000
3 2012-01-12 00:00:00.000 2012-02-12 00:00:00.000 0 1 2000
4 2011-12-12 00:00:00.000 2012-01-12 00:00:00.000 0 2 3000
5 2012-01-12 00:00:00.000 2012-02-12 00:00:00.000 0 2 2000
6 2012-01-12 00:00:00.000 2012-02-12 00:00:00.000 0 3 2500
7 2011-12-12 00:00:00.000 2012-01-12 00:00:00.000 0 4 1000
8 2011-12-12 00:00:00.000 2012-01-12 00:00:00.000 0 5 1200
9 2012-01-12 00:00:00.000 2012-02-12 00:00:00.000 0 5 2000
(9 row(s) affected)
Таблица "Платежи"
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(1, 1, '2011-12-01', 1000);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(1, 2, '2011-12-15', 1000);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(1, 3, '2012-01-13', 1500);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(2, 3, '2012-01-15', 500);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(1, 4, '2012-01-12', 1000);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(1, 7, '2012-01-05', 100);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(2, 7, '2012-01-12', 900);
INSERT INTO k_payment
(payment_num, bill_num, payment_date, payment_sum)
VALUES(1, 8, '2011-12-25', 1000);
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_payment;
payment_num bill_num payment_date payment_sum
-----------------------------------------------------------
1 1 2011-12-01 00:00:00.000 1000.00
1 2 2011-12-15 00:00:00.000 1000.00
1 3 2012-01-13 00:00:00.000 1500.00
1 4 2012-01-12 00:00:00.000 1000.00
1 7 2012-01-05 00:00:00.000 100.00
1 8 2011-12-25 00:00:00.000 1000.00
2 3 2012-01-15 00:00:00.000 500.00
2 7 2012-01-12 00:00:00.000 900.00
(8 row(s) affected)
Таблица "Товары/услуги" (или "Прайс-лист")
INSERT INTO k_price (price_name, price_sum, type_num)
VALUES('Материализация духов',1000, 2);
INSERT INTO k_price (price_name, price_sum, type_num)
VALUES('Раздача слонов',100, 2);
INSERT INTO k_price (price_name, price_sum, type_num)
VALUES('Слоновий бивень',3000, 1);
|
|
|
INSERT INTO k_price (price_name, price_sum, type_num)
VALUES('Моржовый клык',1500, 1);
INSERT INTO k_price (price_name, price_sum, type_num)
VALUES('Копыто Пегаса',5000, 1);
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_price;
price_num price_name price_sum type_num
-----------------------------------------------------------
1 Материализация духов 1000.00 2
2 Раздача слонов 100.00 2
3 Слоновий бивень 3000.00 1
4 Моржовый клык 1500.00 1
5 Копыто Пегаса 5000.00 1
(5 row(s) affected)
Таблица "Протоколы счетов"
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(1, 1, 1, 1000);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(1, 2, 2, 1000);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(2, 3, 20, 100);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(3, 4, 2, 3000);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(1, 5, 1, 1000);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(2, 5, 10, 100);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(1, 6, 2, 1000);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(2, 6, 5, 100);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(4, 7, 1, 1500);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(1, 8, 1, 1000);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(2, 8, 2, 100);
INSERT INTO k_protokol
(price_num, bill_num, kolvo, price_sum)
VALUES(5, 9, 2, 5000);
Посмотрим результат заполнения, для этого выполним следующую команду:
SELECT * FROM k_ protokol;
price_num bill_num kolvo price_sum
-----------------------------------------------------------
1 1 1 1000.00
1 2 2 1000.00
1 5 1 1000.00
1 6 2 1000.00
1 8 1 1000.00
2 3 20 100.00
2 5 10 100.00
2 6 5 100.00
2 8 2 100.00
3 4 2 3000.00
4 7 1 1500.00
5 9 2 5000.00
(12 row(s) affected)
Вопрос
Можно ли для таблицы, в которой имеется поле IDENTITY, выполнить команду INSERT, не указывая явно список полей? Проверьте.
|
|
|
