BD_Sem5_Lektsia5_Neskolko_SELECT_39_ov_v_odnom_zaprose (1)

1. БАЗЫ ДАННЫХ

НЕСКОЛЬКО SELECT’ов
В ОДНОМ ПРЕДЛОЖЕНИИ
1

2. ПОДЗАПРОСЫ ИЛИ ВЛОЖЕННЫЙ SELECT

Geography
Store_Information
Date
region_name store_name
store_name
Sales
Los Angeles
San Diego
$1500 Jan-05-1999
$250 Jan-07-1999
East
East
Boston
New York
Los Angeles
Boston
$300 Jan-08-1999
$700 Jan-08-1999
West
West
Los Angeles
San Diego
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West');
SUM(Sales)
2050
2

3. ПОДЗАПРОСЫ ИЛИ ВЛОЖЕННЫЙ SELECT

SELECT "column_name1"
FROM "table_name"
WHERE "column_name2" [Оператор сравнения]
(SELECT "column_name1"
FROM "table_name"
WHERE [Условия]);
Результат
подзапроса
должен
содержать
ЕДИНСТВЕННОЕ
значение
3

4. КВАНТОР СУЩЕСТВОВАНИЯ ОПЕРАТОР EXISTS

SELECT columns
FROM tables
WHERE EXISTS (subquery);
Выражение считается истинным только тогда,
когда результат вычисления subquery является
непустым множеством, т.е. когда существует какая-либо
запись в таблице, указанной во фразе FROM подзапроса,
которая удовлетворяет условию WHERE подзапроса.
4

5. ОПЕРАТОР EXISTS

Определить список имеющихся на складе товаров
SELECT Название FROM Товар
WHERE EXISTS (SELECT КодТовара FROM Склад
WHERE Товар.КодТовара=Склад.КодТовара)
Ключевые слова EXISTS и NOT EXISTS предназначены для
использования только совместно с подзапросами. Результат их обработки
представляет собой логическое значение TRUE или FALSE. Для ключевого
слова EXISTS результат равен TRUE в том и только в том случае, если в
возвращаемой подзапросом результирующей таблице присутствует хотя бы
одна строка. Если результирующая таблица подзапроса пуста, результатом
обработки операции EXISTS будет значение FALSE. Для ключевого слова NOT
EXISTS используются правила обработки, обратные по отношению к
ключевому слову EXISTS. Поскольку по ключевым словам EXISTS и NOT
EXISTS проверяется лишь наличие строк в результирующей таблице
подзапроса, то эта таблица может содержать произвольное количество
столбцов.
5

6. ОПЕРАТОР EXISTS

Определить список отсутствующих на складе товаров
SELECT Название FROM Товар WHERE NOT EXISTS
(SELECT КодТовара FROM Склад
WHERE Товар.КодТовара=Склад.КодТовара);
Ключевые слова EXISTS и NOT EXISTS предназначены для
использования только совместно с подзапросами. Результат их обработки
представляет собой логическое значение TRUE или FALSE. Для ключевого
слова EXISTS результат равен TRUE в том и только в том случае, если в
возвращаемой подзапросом результирующей таблице присутствует хотя бы
одна строка. Если результирующая таблица подзапроса пуста, результатом
обработки операции EXISTS будет значение FALSE. Для ключевого слова NOT
EXISTS используются правила обработки, обратные по отношению к
ключевому слову EXISTS. Поскольку по ключевым словам EXISTS и NOT
EXISTS проверяется лишь наличие строк в результирующей таблице
подзапроса, то эта таблица может содержать произвольное количество
столбцов.
6

7. ОБЪЕДИНЕНИЕ ЗАПРОСОВ UNION

[SQL Statement 1]
UNION
[SQL Statement 2]
7

8. ОБЪЕДИНЕНИЕ ЗАПРОСОВ UNION

Store_Information
store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales;
Date
Internet Sales
Jan-05-1999
Date
Sales
Jan-07-1999
$250
Jan-10-1999
$535
Jan-11-1999
$320
Jan-10-1999
Jan-12-1999
$750
Jan-11-1999
Jan-07-1999
Jan-08-1999
Jan-12-1999
8

9. ОБЪЕДИНЕНИЕ ЗАПРОСОВ UNION

[SQL Statement 1]
UNION ALL
[SQL Statement 2]
9

10. ОБЪЕДИНЕНИЕ ЗАПРОСОВ UNION

Store_Information
store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
Internet Sales
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales ;
Date
Date
Jan-05-1999
Jan-05-1999
Jan-07-1999
Jan-07-1999
Date
Sales
Jan-08-1999
Jan-08-1999
Jan-07-1999
$250
Jan-08-1999
Jan-10-1999
Jan-10-1999
$535
Jan-07-1999
Jan-11-1999
Jan-11-1999
$320
Jan-10-1999
Jan-12-1999
Jan-12-1999
$750
Jan-11-1999
Jan-12-1999
10

11. ОБЪЕДИНЕНИЕ ЗАПРОСОВ INTERSECT

[SQL Statement 1]
INTERSECT
[SQL Statement 2]
11

12. ОБЪЕДИНЕНИЕ ЗАПРОСОВ UNION

Store_Information
store_name
Sales
Date
Los Angeles $1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles $300
Jan-08-1999
Boston
Jan-08-1999
$700
SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales ;
Date
Internet Sales
Date
Sales
Jan-07-1999
$250
Jan-10-1999
$535
Jan-11-1999
$320
Jan-12-1999
$750
Jan-07-1999
12

13. ОБЪЕДИНЕНИЕ ЗАПРОСОВ MINUS

[SQL Statement 1]
MINUS
[SQL Statement 2]
13

14. ОБЪЕДИНЕНИЕ ЗАПРОСОВ MINUS

Store_Information
store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
Internet Sales
Date
Sales
Jan-07-1999
$250
Jan-10-1999
$535
Jan-11-1999
$320
Jan-12-1999
$750
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales ;
Date
Jan-05-1999
Jan-08-1999
14

15. СОЕДИНЕНИЕ - JOIN

Employees
Employee_ID
Orders
Name
Prod_ID Product Employee_ID
01
Hansen, Ola
234
Printer
01
02
Svendson, Tove
657
Table
03
03
Svendson, Stephen
865
Chair
03
04
Pettersen, Kari
Name
Hansen, Ola
Product
Printer
Svendson, Stephen Table
Svendson, Stephen Chair
SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID;
15

16. СОЕДИНЕНИЕ - JOIN

Employees
Employee_ID
Orders
Name
Prod_ID Product Employee_ID
01
Hansen, Ola
234
Printer
01
02
Svendson, Tove
657
Table
03
03
Svendson, Stephen
865
Chair
03
04
Pettersen, Kari
Name
Hansen, Ola
SELECT Employees.Name FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID AND
Orders.Product='Printer' ;
КТО КУПИЛ ПРИНТЕР?
16

17. ЯВНОЕ СОЕДИНЕНИЕ – INNER JOIN

SELECT field1, field2, field3 FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
17

18. ЯВНОЕ СОЕДИНЕНИЕ – INNER JOIN

Employees
Employee_ID
Orders
Name
Prod_ID Product Employee_ID
01
Hansen, Ola
234
Printer
01
02
Svendson, Tove
657
Table
03
03
Svendson, Stephen
865
Chair
03
04
Pettersen, Kari
Name
Hansen, Ola
Product
Printer
Svendson, Stephen Table
Svendson, Stephen Chair
SELECT Employees.Name, Orders.Product FROM Employees
INNER JOIN Orders ON
Employees.Employee_ID=Orders.Employee_ID ;
18

19. СОЕДИНЕНИЕ СЛЕВА – LEFT JOIN

SELECT field1, field2, field3 FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
19

20. СОЕДИНЕНИЕ СЛЕВА – LEFT JOIN

Employees
Employee_ID
Orders
Name
Prod_ID Product Employee_ID
01
Hansen, Ola
234
Printer
01
02
Svendson, Tove
657
Table
03
03
Svendson, Stephen
865
Chair
03
04
Pettersen, Kari
Name
Hansen, Ola
Product
Printer
Svendson, Tove
Svendson, Stephen Table
Svendson, Stephen Chair
Pettersen, Kari
SELECT Employees.Name, Orders.Product FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID ;
20

21. СОЕДИНЕНИЕ СПРАВА – RIGHT JOIN

SELECT field1, field2, field3 FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
21

22. СОЕДИНЕНИЕ СПРАВА – RIGHT JOIN

Employees
Employee_ID
Orders
Name
Prod_ID Product Employee_ID
01
Hansen, Ola
234
Printer
01
02
Svendson, Tove
657
Table
03
03
Svendson, Stephen
865
Chair
03
04
Pettersen, Kari
Name
Hansen, Ola
Product
Printer
Svendson, Stephen Table
Svendson, Stephen Chair
SELECT Employees.Name, Orders.Product FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID ;
22

23. СОЕДИНЕНИЕ

Employees
Employee_ID
Orders
Name
Prod_ID Product Employee_ID
01
Hansen, Ola
234
Printer
01
02
Svendson, Tove
657
Table
03
03
Svendson, Stephen
865
Chair
03
04
Pettersen, Kari
Name
КТО КУПИЛ ПРИНТЕР?
Hansen, Ola
SELECT Employees.Name FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer' ;
23

24. ГРУППИРОВАНИЕ - GROUP BY

SELECT column, SUM(column)
FROM table
GROUP BY column
24

25. ГРУППИРОВАНИЕ - GROUP BY

Sales
Company
Amount
W3Schools 5500
IBM
4500
W3Schools 7100
SELECT Company, SUM(Amount) FROM Sales;
Company
SUM(Amount)
W3Schools 17100
IBM
17100
W3Schools 17100
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company;
Company
SUM(Amount)
W3Schools 12600
IBM
4500
25

26. ГРУППИРОВАНИЕ - GROUP BY… HAVING...

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
26

27. ГРУППИРОВАНИЕ - GROUP BY… HAVING...

Sales
Company
Amount
W3Schools 5500
IBM
4500
W3Schools 7100
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount)>10000;
Company
SUM(Amount)
W3Schools 12600
27

28. ПРЕДЛОЖЕНИЕ SELECT INTO

SELECT column_name(s)
INTO newtable [IN externaldatabase]
FROM source
28

29. ПРЕДЛОЖЕНИЕ SELECT INTO

SELECT * INTO Persons_backup FROM Persons
SELECT Persons.* INTO Persons IN 'Backup.mdb' FROM Persons
SELECT LastName,FirstName INTO Persons_backup FROM Persons
SELECT LastName,Firstname INTO Persons_backup FROM Persons
WHERE City='Sandnes'
SELECT Employees.Name,Orders.Product INTO Empl_Ord_backup
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
29

30. ПРЕДСТАВЛЕНИЯ ПРЕДЛОЖЕНИЕ CREATE VIEW

CREATE VIEW view_name AS
SELECT column_name(s) FROM table_name
WHERE condition
30

31. ПРЕДСТАВЛЕНИЯ ПРЕДЛОЖЕНИЕ CREATE VIEW

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName FROM Products
WHERE Discontinued=No;
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products);
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales)
AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages';
31

32. БЕЗОПАСНОСТЬ И САНКЦИОНИРОВАНИЕ ДОСТУПА ПРЕДЛОЖЕНИЯ GRANT и REVOKE

GRANT {привилегии}
ON {объекты}
TO {пользователи} [ WITH ADMIN OPTION]
Привилегии для таблиц и представлений:
SELECT
UPDATE (может относиться к конкретным столбцам)
DELETE
INSERT
ALL PRIVILEGES – все привилегии
Только для базовых таблиц:
ALTER
INDEX
32

33. БЕЗОПАСНОСТЬ И САНКЦИОНИРОВАНИЕ ДОСТУПА ПРЕДЛОЖЕНИЯ GRANT и REVOKE

GRANT SELECT ON TABLE S TO U1805;
GRANT SELECT, UPDATE (CITY, PERSON)
ON TABLE S TO U1805, Mary, Bob;
GRANT ALL PRIVELEGES ON TABLE S, P, SP TO Boss;
GRANT SELECT ON TABLE P TO PUBLIC;
Специальное ключевое
слово означающее
общедоступный
33

34. БЕЗОПАСНОСТЬ И САНКЦИОНИРОВАНИЕ ДОСТУПА ПРЕДЛОЖЕНИЯ GRANT и REVOKE

REVOKE {привилегии} [ON объекты] FROM {пользователи}
REVOKE SELECT ON TABLE S FROM U1805;
Отмена привилегии UPDATE не может
относиться к конкретным столбцам
34

35. ТРАНЗАКЦИИ COMMIT и ROLLBACK

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
IF «всё завершилось хорошо» THEN
COMMIT; -- «утвердить» изменения
ELSE
ROLLBACK; -- восстановить состояние до начала транзакции
END IF;
35

36. ТРАНЗАКЦИИ COMMIT и ROLLBACK

LOCK TABLES tbl_name [AS alias]
{READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
Команда LOCK TABLES блокирует указанные в ней таблицы для
данного потока. Команда UNLOCK TABLES снимает любые блокировки,
удерживаемые данным потоком. Все таблицы, заблокированные
текущим потоком, автоматически разблокируются при появлении в
потоке иной команды LOCK TABLES или при прекращении соединения
с сервером.
36

37. КУРСОРЫ ВЕРСИЯ ORACLE

Завершить
цикл если
курсор пуст
LOOP
DECLARE
CURSOR c_american_pie IS
SELECT Film_Title FROM Film_Table
WHERE Film_Title like "American Pie%";
OPEN c_american_pie;
FETCH c_american_pie INTO v_film_title;
EXIT WHEN c_american_pie%NOT_FOUND;
-- Обрабатываем полученные данные.
-- В этом примере просто наращивается счётчик,
-- Хотя что-то можно сделать с значением переменной v_film_title
v_american_pie_count := v_american_pie_count + 1;
END LOOP;
CLOSE c_american_pie;
37

38. КУРСОРЫ ВЕРСИЯ DB2

Declare MyCursor Cursor for
Select Film_Title From Director_Film_Table,
Where Director_Last_Name equals "Cameron" and
Director_First_Name equals "James" Order By Film_Title;
Open MyCursor;
LOOP
SQLCODE is set to +100
Fetch MyCursor Into : CameronMovieName;
END LOOP;
Close MyCursor;
38