Примеры использования операторов

Операторы защиты и управления данными

Операторы DML (Data Manipulation Language) - операторы манипулирования данными

- SELECT - отобрать строки из таблиц

- INSERT - добавить строки в таблицу

- UPDATE - изменить строки в таблице

- DELETE - удалить строки в таблице

- COMMIT - зафиксировать внесенные изменения

- ROLLBACK - откатить внесенные изменения

- CREATE ASSERTION - создать ограничение

- DROP ASSERTION - удалить ограничение

- GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами

- REVOKE - отменить привилегии пользователя или приложения

INSERT - вставка строк в таблицу

Пример 1. Вставка одной строки в таблицу:

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин

INSERT INTO P (PNUM, PNAME) VALUES (4, "Иванов"); Рез. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов

Пример 2. Вставка в таблицу нескольких строк, выбранных из другой таблицы (в таблицу TMP_TABLE вставляются данные о поставщиках из таблицы P, имеющие номера, большие 2):

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов

INSERT INTO TMP_TABLE (PNUM, PNAME) SELECT PNUM, PNAME FROM P WHERE P.PNUM>2; Рез. Таблица TMP_TABLE

PNUM	PNAME
3	Ванин
4	Иванов

UPDATE - обновление строк в таблице

Пример 3. Обновление нескольких строк в таблице:

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов

UPDATE P SET PNAME = "Пушников" WHERE P.PNUM = 1; Рез. Таблица P

PNUM	PNAME
1	Пушников
2	Сидоров
3	Ванин
4	Иванов

DELETE - удаление строк в таблице

Пример 4. Удаление нескольких строк в таблице:

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов

DELETE FROM P WHERE P.PNUM = 1; Рез. Таблица P

PNUM	PNAME
2	Сидоров
3	Ванин
4	Иванов

Пример 5. Удаление всех строк в таблице:

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов

DELETE FROM P; Рез. Таблица P

PNUM

PNAME

6-7.3.3. ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ ОПЕРАТОРА SELECT

6-7.3.3.1 ОТБОР ДАННЫХ ИЗ ОДНОЙ ТАБЛИЦЫ

Пример 6. Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию (ключевое слово WHERE …):

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов

SELECT * FROM P WHERE P.PNUM > 2;Рез. Таблица

PNUM	PNAME
3	Ванин
4	Иванов

Замечание. В качестве условия в разделе WHERE можно использовать сложные логические выражения, использующие поля таблиц, константы, сравнения (>, <, = и т.д.), скобки, союзы AND и OR, отрицание NOT.

Пример 7. Выбрать некоторые колонки из исходной таблицы, удалив из результата повторяющиеся строки (ключевое слово DISTINCT):

Исх. Таблица P

PNUM	PNAME
1	Петров
2	Сидоров
3	Ванин
4	Иванов
5	Сидоров

SELECT DISTINCT P.NAME FROM P;Рез. Таблица

PNAME

Петров

Сидоров

Ванин

Иванов

Замечание. Использование ключевого слова DISTINCT приводит к тому, что в результирующей таблице будут удалены все повторяющиеся строки.

Пример 8. Использование скалярных выражений и переименований колонок в запросах (ключевое слово AS …):

Исх. Таблица TOVAR

TNAME	KOL	PRICE
Болт
Гайка
Винт

SELECT TOVAR.TNAME, TOVAR.KOL, TOVAR.PRICE, "=" AS EQU, TOVAR.KOL*TOVAR.PRICE AS SUMMA FROM TOVAR;

В результате получим таблицу с колонками, которых не было в исходной таблице TOVAR:

Рез. Таблица TOVAR

TNAME	KOL	PRICE	EQU	SUMMA
Болт			=
Гайка			=
Винт			=

Пример 9. Упорядочение результатов запроса (ключевое слово ORDER BY …) по нескольким полям с возрастанием или убыванием (ключевые слова ASC, DESC):

Исх. Таблица PD

PNUM	DNUM	VOLUME

SELECT PD.PNUM, PD.DNUM, PD.VOLUME FROM PD ORDER BY DNUM ASC, VOLUME DESC;

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

Рез. Таблица

PNUM	DNUM	VOLUME

6-7.3.3.2 ОТБОР ДАННЫХ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ

Пример 10. Естественное соединение таблиц:

а) - явное указание условий соединения,

б) - ключевые слова JOIN… USING… позволяют явно указать, по каким из общих колонок таблиц будет производиться соединение

в) - ключевое слово NATURAL JOIN - автоматически соединяет по всем одинаковым полям в таблицах

Таблица PD Таблица P

PNUM	DNUM	VOLUME

PNUM	PNAME
	Иванов
	Петров
	Сидоров

SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUMEа)FROM P, PD WHERE P.PNUM = PD.PNUM;б)FROM P JOIN PD USING PNUM;в)FROM P NATURAL JOIN PD;В результате получим новую таблицу, в которой строки с данными о поставщиках соединены со строками с данными о поставках деталей: Рез. Таблица

PNUM	PNAME	DNUM	VOLUME
	Иванов
	Иванов
	Иванов
	Петров
	Петров
	Сидоров

Замечания. а) Соединяемые таблицы перечислены в разделе FROM оператора, условие соединения приведено в разделе WHERE. Раздел WHERE, помимо условия соединения таблиц, может также содержать и условия отбора строк.

б) Ключевое слово USING позволяет явно указать, по каким из общих колонок таблиц будет производиться соединение.

в) В разделе FROM не указано, по каким полям производится соединение. NATURAL JOIN автоматически соединяет по всем одинаковым полям в таблицах.

Пример 11. Прямое произведение таблиц:

Исх. Таблица P Таблица D

PNUM	PNAME		DNUM	DNAME
	Иванов		Болт
	Петров		Гайка
	Сидоров		Винт

SELECT P.PNUM, P.PNAME, D.DNUM, D.DNAME FROM P, D;

В результате получим следующую таблицу:

Рез. Таблица

PNUM	PNAME	DNUM	DNAME
	Иванов		Болт
	Иванов		Гайка
	Иванов		Винт
	Петров		Болт
	Петров		Гайка
	Петров		Винт
	Сидоров		Болт
	Сидоров		Гайка
	Сидоров		Винт

Замечание. Т.к. не указано условие соединения таблиц, то каждая строка первой таблицы соединится с каждой строкой второй таблицы.

Пример 12. Соединение таблиц по произвольному условию. Рассмотрим таблицы поставщиков и деталей, которыми присвоен некоторый статус:

Исх. Таблица P Таблица D

PNUM	PNAME	PSTATUS		DNUM	DNAME	DSTATUS
	Иванов				Болт
	Петров				Гайка
	Сидоров				Винт

Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает следующий запрос:

SELECT P.PNUM, P.PNAME, P.PSTATUS, D.DNUM, D.DNAME, D.DSTATUS FROM P, D WHERE P.PSTATUS >= D.DSTATUS;

В результате получим следующую таблицу:

Рез. Таблица

PNUM	PNAME	PSTATUS	DNUM	DNAME	DSTATUS
	Иванов			Болт
	Иванов			Гайка
	Иванов			Винт
	Петров			Винт
	Сидоров			Гайка
	Сидоров			Винт

6-7.3.3.3 ИСПОЛЬЗОВАНИЕ ИМЕН КОРРЕЛЯЦИИ (АЛИАСОВ, ПСЕВДОНИМОВ)

Пример 13. Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика:

Исх. Таблица P

PNUM	PNAME	PSTATUS
	Иванов
	Петров
	Сидоров

SELECT P1.PNAME AS PNAME1, P1.PSTATUS AS PSTATUS1, P2.PNAME AS PNAME2, P2.PSTATUS AS PSTATUS2 FROM P P1, P P2 WHERE P1.PSTATUS1 > P2.PSTATUS2;

В результате получим следующую таблицу:

Рез. Таблица

PNAME1	PSTATUS1	PNAME2	PSTATUS2
Иванов		Петров
Иванов		Сидоров
Сидоров		Петров

6-7.3.3.4 ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ЗАПРОСАХ

Пример 14. Получить общее количество поставщиков (ключевое слово COUNT):

Исх. Таблица P

PNUM	PNAME	PSTATUS
	Иванов
	Петров
	Сидоров

SELECT COUNT(*) AS N FROM P;

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

Рез. Таблица

N

Пример 15. Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG):

Исх. Таблица PD

PNUM	DNUM	VOLUME

SELECT SUM(PD.VOLUME) AS SM, MAX(PD.VOLUME) AS MX, MIN(PD.VOLUME) AS MN, AVG(PD.VOLUME) AS AV FROM PD;

В результате получим следующую таблицу с одной строкой:

Рез. Таблица

SM	MX	MN	AV
			333.33333333

6-7.3.3.5 ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ С ГРУППИРОВКАМИ

Пример 16. Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …):

Исх. Таблица PD

PNUM	DNUM	VOLUME

SELECT PD.DNUM, SUM(PD.VOLUME) AS SM GROUP BY PD.DNUM;

Этот запрос будет выполняться так. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результирующую таблицу будет включена одна строка:

Рез. Таблица

DNUM	SM

Пример 17. Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …):

Исх. Таблица PD

PNUM	DNUM	VOLUME

SELECT PD.DNUM, SUM(PD.VOLUME) AS SM GROUP BY PD.DNUM HAVING SUM(PD.VOLUME) > 400;

В результате получим следующую таблицу:

Рез. Таблица

DNUM	SM

6-7.3.3.6 ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ

Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос.

Пример 18. Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом):

Исх. Таблица P

PNUM	PNAME	PSTATUS
	Иванов
	Петров
	Сидоров

SELECT * FROM P WHERE P.STATYS < (SELECT MAX(P.STATUS) FROM P);

Замечание. Т.к. поле P.STATUS сравнивается с результатом подзапроса, то подзапрос должен быть сформулирован так, чтобы возвращать таблицу, состоящую ровно из одной строки и одной колонки.

Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

1. Выполнить один раз вложенный подзапрос и получить максимальное значение статуса.

2. Просканировать таблицу поставщиков P, каждый раз сравнивая значение статуса поставщика с результатом подзапроса, и отобрать только те строки, в которых статус меньше максимального.

Рез. Таблица

PNUM	PNAME	PSTATUS
	Петров
	Сидоров

Пример 19. Использование предиката IN. Получить список поставщиков, поставляющих деталь номер 2:

Исх. Таблица PD Таблица P

PNUM	DNUM	VOLUME		PNUM	PNAME	PSTATUS
					Иванов
					Петров
					Сидоров

SELECT * FROM P WHERE P.PNUM IN (SELECT DISTINCT PD.PNUM FROM PD WHERE PD.DNUM = 2); Замечание. В данном случае вложенный подзапрос может возвращать таблицу, содержащую несколько строк. Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий: 1. Выполнить один раз вложенный подзапрос и получить список номеров поставщиков, поставляющих деталь номер 2. 2. Просканировать таблицу поставщиков P, каждый раз проверяя, содержится ли номер поставщика в результате подзапроса.Рез. Таблица P

PNUM	PNAME	PSTATUS
	Иванов
	Сидоров

Пример 20. Использование предиката (NOT) и EXIST. Получить список поставщиков, (не) поставляющих деталь номер 2:

Исх. Таблица PD Таблица P

PNUM	DNUM	VOLUME		PNUM	PNAME	PSTATUS
					Иванов
					Петров
					Сидоров

SELECT * FROM P WHERE (NOT) EXIST (SELECT * FROM PD WHERE PD.PNUM = P.PNUM AND PD.DNUM = 2);

Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

1. Просканировать таблицу поставщиков P, каждый раз выполняя подзапрос с новым значением номера поставщика, взятым из таблицы P.

2. В результат запроса включить только те строки из таблицы поставщиков, для которых вложенный подзапрос вернул непустое множество строк.

Замечание. В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXIST, но могут быть использованы и в других подзапросах.

Замечание. Может показаться, что запросы, содержащие коррелируемые подзапросы будут выполняться медленнее, чем запросы с некоррелируемыми подзапросами. На самом деле это не так, т.к. то, как пользователь, сформулировал запрос, не определяет, как этот запрос будет выполняться. Язык SQL является непроцедурным, а декларативным. Это значит, что пользователь, формулирующий запрос, просто описывает, каким должен быть результат запроса, а как этот результат будет получен - за это отвечает сама СУБД.

(Рез. Таблица)

PNUM	PNAME	PSTATUS
	Петров

Рез. Таблица

PNUM	PNAME	PSTATUS
	Иванов
	Сидоров

6-7.3.3.7 ИСПОЛЬЗОВАНИЕ ОБЪЕДИНЕНИЯ, ПЕРЕСЕЧЕНИЯ И РАЗНОСТИ

Пример 21. Получить имена поставщиков, имеющих статус, больший 3 или поставляющих хотя бы одну деталь номер 2 (объединение двух подзапросов - ключевое слово UNION):

Исх. Таблица P Таблица PD

PNUM	PNAME	PSTATUS		DNUM	DNAME	DSTATUS
	Иванов				Болт
	Петров				Гайка
	Сидоров				Винт

SELECT P.PNAME FROM P WHERE P.STATUS > 3 UNION SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

Замечание. Результирующие таблицы объединяемых запросов должны быть совместимы, т.е. иметь одинаковое количество столбцов и одинаковые типы столбцов в порядке их перечисления. Не требуется, чтобы объединяемые таблицы имели бы одинаковые имена колонок. Это отличает операцию объединения запросов в SQL от операции объединения в реляционной алгебре. Наименования колонок в результирующем запросе будут автоматически взяты из результата первого запроса в объединении.

Исходные таблицы:Исх. Таблица PD Таблица P

PNUM	DNUM	VOLUME		PNUM	PNAME	PSTATUS
					Иванов
					Петров
					Сидоров

Пример 22. Получить имена поставщиков, имеющих статус, больший 3 и одновременно поставляющих хотя бы одну деталь номер 2 (пересечение двух подзапросов - ключевое слово INTERSECT):

SELECT P.PNAME FROM P WHERE P.STATUS > 3 INTERSECT SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

Пример 23. Получить имена поставщиков, имеющих статус, больший 3, за исключением тех, кто поставляет хотя бы одну деталь номер 2 (разность двух подзапросов - ключевое слово EXCEPT):

SELECT P.PNAME FROM P WHERE P.STATUS > 3EXCEPT SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

6-7.3.4 СИНТАКСИС ОПЕРАТОРА ВЫБОРКИ ДАННЫХ (SELECT)

Дата добавления: 2014-01-05