Операторы модификации данных

1.

Лекция
ОПЕРАТОРЫ МОДИФИКАЦИИ ДАННЫХ

2.

Язык манипуляции данными в SQL
(DML — Data Manipulation Language):
- оператор SELECT (осуществляет извлечение информации из
базы данных) ;
- операторы модификации данных (изменяют состояние
данных).
оператор
функция
INSERT
Добавление записей (строк) в таблицу БД
UPDATE
Обновление данных в столбце таблицы БД
DELETE
Удаление записей из таблицы БД

3.

Данные каких таблиц будем модифицировать?
MS SQL Server
My SQL
Автоинкрементное
поле
Автоинкрементное
поле
CREATE TABLE Printer_Inc
(
code int IDENTITY(1,1) PRIMARY KEY ,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);
CREATE TABLE Printer_Inc
(
code int AUTO_INCREMENT PRIMARY KEY ,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);
PostgreSQL
SQLite
Автоинкрементное
поле
CREATE TABLE Printer_Inc
(
code SERIAL PRIMARY KEY ,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);
Автоинкрементное
поле
CREATE TABLE Printer_Inc
(
code integer PRIMARY KEY,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);

4.

Оператор INSERT
Вставляет новые записи в таблицу. При этом значения столбцов могут
представлять собой
- литеральные константы,
- либо являться результатом выполнения подзапроса.
Синтаксис оператора :
INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUVALUES}

5.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
Хотим вставить строки в таблицу Product (maker, model, type)
INSERT INTO Product
VALUES ('B', 1157, 'PC');
Требования:
• список вставляемых значений должен быть полный;
• порядок значений должен соответствовать порядку, заданному для
таблицы, в которую вставляются строки;
• значения должны относиться к тому же типу данных, что и столбцы, в
которые они вносятся.

6.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
Хотим вставить строки в таблицу Product (maker, model, type)
INSERT INTO Product (type, model, maker)
VALUES ('PC', 1157, 'B');
• можно изменить «естественный» порядок их следования
• становится выигрышной, если столбцы имеют значения по
умолчанию

7.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
• становится выигрышной, если столбцы имеют значения по умолчанию
CREATE TABLE product_D
( maker char (1) NULL,
model varchar (4) NULL,
type varchar (7) NOT NULL DEFAULT 'PC');
INSERT INTO Product_D (model, maker)
VALUES (1157, 'B');
Результат: 'B‘, 1157,'PC'
INSERT INTO Product_D (model)
VALUES (1157)
Результат: null, 1157, 'PC'

8.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
Можно ли не указывать список столбцов и, тем не менее, воспользоваться
значениями по умолчанию
CREATE TABLE product_D
( maker char (1) NULL,
model varchar (4) NULL,
type varchar (7) NOT NULL DEFAULT 'PC');
INSERT INTO Product_D
VALUES ('B', 1157, DEFAULT);
Результат: 'B', 1157, 'PC‘
INSERT INTO Product_D
VALUES (DEFAULT, DEFAULT, DEFAULT);
Результат: ??????

9.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
Можно ли не указывать список столбцов и, тем не менее, воспользоваться
значениями по умолчанию
CREATE TABLE product_D
( maker char (1) NULL,
model varchar (4) NULL,
type varchar (7) NOT NULL DEFAULT 'PC');
INSERT INTO Product_D
VALUES ('B', 1157, DEFAULT);
Результат: 'B', 1157, 'PC‘
INSERT INTO Product_D
VALUES (DEFAULT, DEFAULT, DEFAULT);
Результат: Null, Null, 'PC‘

10.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
INSERT INTO Product_D
VALUES (DEFAULT, DEFAULT, DEFAULT);
Результат: Null, Null, 'PC‘
INSERT INTO Product_D DEFAULT VALUES;
Результат: Null, Null, 'PC

11.

INSERT INTO <имя таблицы> [(<имя столбца>,...)]
{VALUES (<значение столбца>,…)}
| <выражение запроса>
| {DEFAULT VALUES}
Требуется вставить в таблицу Product_D все строки из таблицы Product,
относящиеся к моделям персональных компьютеров (type = ‘PC’).
INSERT INTO Product_D
SELECT *
FROM Product
WHERE type = 'PC';
Если порядок следования столбцов в обеих таблицах одинаковый!!!

12.

INSERT INTO Product_D (maker, model, type)
SELECT *
FROM Product
WHERE type = 'PC';
или
INSERT INTO Product_D
SELECT maker, model, type
FROM Product
WHERE type = 'PC';
или
INSERT INTO Product_D (maker, model, type)
SELECT maker, model, type
FROM Product
WHERE type = 'PC';
или
INSERT INTO Product_D (maker, model)
SELECT maker, model
FROM Product
WHERE type = 'PC';

13.

Вставка нескольких строк:
INSERT INTO Product_D
SELECT 'B' AS maker, 1158 AS model, 'PC' AS type
UNION ALL
SELECT 'C', 2190, 'Laptop'
UNION ALL
SELECT 'D', 3219, 'Printer';
Вставка нескольких строк с помощью конструктора строк уже
реализована в SQL Server 2008.
С учетом этой возможности, последний запрос можно переписать в
виде:
INSERT INTO Product_D
VALUES
('B', 1158, 'PC'),
('C', 2190, 'Laptop'),
('D', 3219, 'Printer');

14.

MySQL допускает еще одну нестандартную синтаксическую конструкцию
INSERT [INTO] <имя таблицы>
SET {<имя столбца>={<выражение> | DEFAULT}}, ...
INSERT INTO Product
SET maker = 'B',
model = 1157,
type = 'PC';

15.

Вставка строк в таблицу, содержащую автоинкрементируемое поле
MS SQL SERVER
CREATE TABLE Printer_Inc
(
code int IDENTITY(1,1) PRIMARY KEY ,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);
INSERT INTO Printer_Inc
VALUES (15, 3111, 'y', 'laser', 599);
ОШИБКА!!!
INSERT INTO Printer_Inc (model, color, type, price)
VALUES (3111, 'y', 'laser', 599);
В поле code окажется какое-то уникальное значение

16.

Как подставить вполне конкретное
значение в автоинкрементируемое поле?
Cтандарт языка SQL не предполагает наличия автоинкрементируемых
полей, поэтому не существует и единого подхода.
Как это реализуется в MS SQL Server?
Оператор SET IDENTITY_INSERT < имя таблицы > { ON | OFF };
отключает (значение ON) или включает (OFF) использование
автоинкремента.
SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model, color, type, price) !!!!!!!!!
VALUES (15, 3111, 'y', 'laser', 599);
Проверка на уникальность значения в столбце code остается
(первичный ключ)!!!

17.

Оператор UPDATE
UPDATE <имя таблицы>
SET {<имя столбца> = {<выражение для вычисления значения столбца>
| NULL
| DEFAULT},...}
[ {WHERE <предикат>}]
- могут быть заданы значения для любого количества столбцов;
- в одном и том же операторе UPDATE можно вносить изменения в каждый столбец
указанной таблицы только один раз;
- при отсутствии предложения WHERE будут обновлены все строки таблицы;
- если столбец допускает NULL-значение, то его можно указать в явном виде;
- можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для
данного столбца.

18.

UPDATE <имя таблицы>
SET {<имя столбца> = {<выражение для вычисления значения столбца>
| NULL
| DEFAULT},...}
[ {WHERE <предикат>}]
UPDATE Laptop
SET price = price*0.9; относится к текущему значению таблицы
UPDATE Laptop
SET hd = ram/2
!!!типы данных столбцов hd и ram должны
WHERE hd < 10;
быть совместимы или приведены к одному
типу (cast или convert)

19.

Использование условных операторов:
UPDATE Laptop
SET hd = CASE
WHEN ram < 128
THEN 20
ELSE 40
END;
Использование подзапросов:
UPDATE Laptop
SET speed = (SELECT MAX(speed) FROM Laptop);

20.

Изменение значений в
автоинкрементируемых столбцах
Пример
Столбец code в таблице Laptop определен как IDENTITY(1,1)
UPDATE Laptop
SET code = 5
WHERE code = 4;
??????? Ошибка

21.

В MS SQL SERVER Автоикрементируемое поле не допускает обновления
Решение:
• Отключить автоинкрементирование
• Вставить новую строку с code = 5
• Удалить строку с code = 4
SET IDENTITY_INSERT Laptop_ID ON;
INSERT INTO Laptop_ID (code, model, speed, ram, hd, price, screen)
SELECT 5, model, speed, ram, hd, price, screen
FROM Laptop_ID WHERE code = 4;
DELETE FROM Laptop_ID
WHERE code = 4;
Другой строки со значением code = 5 в таблице быть не должно

22.

Пример
Пусть требуется указать «No PC» (нет ПК) в столбце type для тех моделей ПК из таблицы
Product, для которых нет соответствующих строк в таблице PC.
Первый вариант:
UPDATE Product
SET type = 'No PC'
WHERE type = 'pc' AND
model NOT IN (SELECT model FROM PC);

23.

Пример
Пусть требуется указать «No PC» (нет ПК) в столбце type для тех моделей ПК из
таблицы Product, для которых нет соответствующих строк в таблице PC.
Второй вариант:
UPDATE Product
SET type = 'No PC'
FROM Product pr LEFT JOIN PC ON pr.model=PC.model
WHERE type = 'pc' AND PC.model IS NULL;

24.

Оператор DELETE
DELETE удаляет строки из временных
или постоянных базовых таблиц, (…. ряд объектов, которые
еще не изучали)
Синтаксис:
DELETE FROM <имя таблицы >
[WHERE <предикат>];
Если предложение WHERE отсутствует, удаляются все строки из
таблицы

25.

Пример
Требуется удалить из таблицы Laptop все портативные
компьютеры с размером экрана менее 12 дюймов.

26.

Пример
Требуется удалить из таблицы Laptop все портативные
компьютеры с размером экрана менее 12 дюймов.
DELETE FROM Laptop
WHERE screen < 12;

27.

Пример
Удалить все блокноты

28.

Пример
Удалить все блокноты
DELETE FROM Laptop;

29.

Пример
Удалить те модели ПК из таблицы Product, для которых нет
соответствующих строк в таблице PC.

30.

Пример
Удалить те модели ПК из таблицы Product, для которых нет
соответствующих строк в таблице PC.
DELETE FROM Product
WHERE type = 'pc' AND
model NOT IN (SELECT model FROM PC );

31.

Пример
Удалить те модели ПК из таблицы Product, для которых нет соответствующих
строк в таблице PC.
Второй вариант:
Синтаксис оператора DELETE может быть расширен за счет дополнительного
предложения FROM
FROM <источник табличного типа>
DELETE FROM Product
FROM Product pr LEFT JOIN PC ON pr.model = PC.model
WHERE type = 'pc' AND PC.model IS NULL;

32.

Что происходит с автоинкрементированными столбцами при удалении строк в MS SQL SERVER?
CREATE TABLE Truncate_test (id INT IDENTITY(5,5) PRIMARY KEY, val INT);
INSERT INTO Truncate_test(val)
VALUES (1),(2),(3);
SELECT * FROM Truncate_test;
Результат:
id
val
5
1
10
2
15
3

33.

MS SQL SERVER
Удалим таблицу и вставим строки снова
DELETE FROM Truncate_test;
INSERT INTO Truncate_test(val) VALUES (1), (2), (3);
SELECT * FROM Truncate_test;
Состояние счетчика не было сброшено,
и приращение продолжилось с последнего значения (15)
id
val
20
1
25
2
30
3

34.

MS SQL Server
Оператор TRUNCATE TABLE
сбрасывает значение счетчика:
TRUNCATE TABLE Truncate_test;
INSERT INTO Truncate_test(val)
VALUES (1),(2),(3);
SELECT * FROM Truncate_test;

35.

Стандартный синтаксис
TRUNCATE TABLE < имя таблицы > [{CONTINUE IDENTITY} | {RESTART
IDENTITY}]
- значение счетчика может быть сброшено (опция RESTART
IDENTITY) или продолжено (опция CONTINUE IDENTITY);
- по умолчанию опция CONTINUE IDENTITY.
Реализация в MY SQL Server отличается от стандарта

36.

Задачи на sql-ex.ru
Insert - 1, 2, 3, 4, 10, 11, 13, 18, 19
Update - 7, 9, 12, 15, 17, 20
Delete - 5, 6, 8, 14, 16
Выполнять требуется по порядку