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, не указывая явно список полей? Проверьте.