6.8. Изменение данных

Операторы изменения данных не возвращают результат, как это делает оператор запроса на выборку SELECT, но вместо этого они изменяют содержимое таблиц базы данных.

Имеется 3 вида изменений:

1. вставка стороки или нескольких строк

2. удаление стороки или нескольких строк

3. изменение значений полей стороки или нескольких строк

Общий вид оператора вставки:

INSERT INTO имя таблицы [ (<список столбцов>) ] VALUES (<список значений>)

(<список столбцов>) необязателен, если VALUES задает все столбцы в порядке их следования в таблице.

INSERT INTO имя таблицы [ (<список столбцов>) ]

SELECT (<список столбцов>) FROM имя таблицы …

Общий вид оператора удаления:

DELETE FROM имя таблицы [WHERE условия отбора]

Общий вид оператора изменения значений полей:

UPDATE имя таблицы SET имя столбца = новое значение [, имя столбца = новое значение] [, имя столбца = новое значение] … [WHERE условие отбора]

1. Записать факт получения оценки 5 студентом Ивановым на экзамене по курсу ‘Базы данных’:

Insert into r1 (o, fio, d) values (5, ‘Иванов’, ’бд’)

Для иллюстрации использования операторов модификации введем в рассмотренную ранее базу данных о студентах еще 2 отношения:

R4 = (G, K), где G – группа, K – курс.

R5 = (FIO, G, S, STIPENDIA), где FIO – имя студента, G – группа, S - % повышения стипендии по результатам сессии, STIPENDIA – величина стипендии в рублях.

2. Перевести все группы в ВУЗе на следующий курс:

UPDATE R4 SET R4.K = R4.K +1

3. Если количество пятерок равно 3 (примем, что всего надо сдать 3 экзамена - т. е. рассматриваем случай круглого отличника), то назначить повышенную стипендию (S = 50%):

UPDATE R5 SET R5.S = 50 WHERE R5.FIO IN

(SELECT R1.FIO FROM R1 WHERE R1.O = 5

GROUP BY R1.FIO HAVING COUNT(*)=3)

Здесь и далее использован оператор IN, который принимает значение TRUE только если кортеж совпадает хотя бы с одним кортежем из подзапроса.

4. Если количество пятерок равно 2 и нет двоек и троек (2 пятерки и 1 четверка), то S = 25%:

UPDATE R5 SET R5.S = 25 WHERE R5.FIO IN

(SELECT R1.FIO FROM R1 WHERE R1.O = 5 AND

R1.FIO NOT IN

(SELECT A.FIO FROM R1 A WHERE A.O <=3 OR A.O IS NULL)

GROUP BY R1.FIO HAVING COUNT(*)=2)

5. Если нет 3 и количество оценок 4 больше 1, то обычная стипендия:

UPDATE R5 SET R5.S = 0 WHERE R5.FIO IN

(SELECT R1.FIO FROM R1 WHERE R1.O=4 AND R1.FIO NOT IN

(SELECT A.FIO FROM R1 A

WHERE A.O <=3 OR A.Ocenka IS NULL)

GROUP BY R1.FIO HAVING COUNT(*)=2)

6. Если есть оценка 2, то нет стипендии:

UPDATE R5 SET R5.S = -100 WHERE R5.FIO IN

(SELECT R1.FIO FROM R1 WHERE R1.O <= 2 OR R1.O IS NULL)

7. Представления.

Представление (VIEW) – это не физическая, а “виртуальная” таблица, определенная путем ссылки на таблицы и другие представления. Синтакси определения представления:

<представление> ::= CREATE VIEW <имя представления> [( список столбцов)]

AS <SQL-запрос>

Пример представления (так называемое, горизонтальное представление):

CREATE VIEW PMI99 AS

SELECT * FROM R5 WHERE G = ‘ПМИ99’

Другой пример (так называемое, вертикальное представление):

CREATE VIEW PURCHASERS AS

SELECT DISTINCT NAME FROM PURCHASERS

Представление может быть образовано путем применения операции группировки (так называемое, сгруппированное):

CREATE VIEW STIP (G, CNT, SM, MX, MN, AG)

AS

SELECT G, COUNT(*), SUM(STIPENDIA), MAX(STIPENDIA), MIN(STIPENDIA), AVG(STIPENDIA)

FROM R5 GROUP BY G

Можно задать представление на основе соединения таблиц (так называемое, соединенное):

CREATE VIEW POSTAVKI AS

SELECT S.WEIGHT, S.PRICE, S.DAT, P.NAME, G.NAME, P.CODE, G.CODE

FROM SUPPLIES AS S

LEFT JOIN PROVIDERS AS P ON S.CODEC = P.CODE

LEFT JOIN GOODS AS G ON S.CODEG = G.CODE

Доступ к данным представления можно получить тем же способом, что и к данным обычной таблицы:

SELECT * FROM PMI99 ORDER BY S, FIO

Иногда можно изменять данные в представлении (т. е. изменять данные в соответствующих таблицах, лежащих в основе представления). Согласно стандарту SQL89, например, это возможно при следующих условиях:

1. в SQL-запросе не используется ключевое слово DISTINCT

2. раздел FROM запроса содержит только 1 таблицу

3. столбцы не являются выражениями и не основаны на применении агрегатных функций

4. WHERE не содержит вложенный подзапрос

5. не используются ключевые слова GROUP BY или HAVING