7. Языки манипулирования данными
7. 1. Общая характеристика Язык, в котором можно (по крайней мере) моделировать исчисление с переменнымикортежами, либо, что равносильно, реляционную алгебру или исчисление с переменными на доменах, называется полным 2
7. 2. Обзор языков манипулирования данными ISBL (Information System Base Language) – «чистый» язык реляционной алгебры. Разработан в исследовательском центре фирмы IBM в Питерли (Англия) для использования в экспериментальной системе PRTV (Peterlee Relational Test Vehicle). Нет агрегатных операций, а также средств для вставки, удаления и модификации кортежей 3
7. 2. Обзор языков манипулирования данными SEQUEL (Structured English Query Language) – разработан в 1974 г. в исследовательской лаборатории IBM в Сан-Хосе; использует реляционную алгебру, но имеет синтаксис, напоминающий реляционное исчисление с переменными-кортежами. 1976 г. – СУБД System R; важнейший из результатов – разработка языка SQL (Structured Query Language) 4
7. 2. Обзор языков манипулирования данными QUEL – язык реляционного исчисления с переменными-кортежами, разработан в Калифорнийском университете в Беркли в конце 70 -х г. г. для реляционной СУБД INGRES. Включает широкий спектр операторов реляционного исчисления с переменными-кортежами, агрегатные функции. Более структурирован, чем SQL 5
7. 2. Обзор языков манипулирования данными QBE (Query-By-Example) – язык исчисления с переменными на доменах; разработан в Исследовательском центре IBM в Йорктаун. Хейтсе. Предназначен для работы с терминала. Включены агрегатные функции 6
7. 2. Обзор языков манипулирования данными SQL (Structured Query Language) – язык, ориентированный на отображение; описывается отображение известного атрибута или множества атрибутов в искомый атрибут или множество атрибутов. Первая коммерческая СУБД – ORACLE (конец 70 -х г. г. ) 7
7. 3. SQL 1982 г. – ANSI, 1983 г. – Международный комитет по стандартизации (ISO) 1987 г. – исходный вариант стандарта языка SQL (SQL-87, SQL 1 -го поколения) 1992 г. – первая версия стандарта ISO, SQL-2 или SQL-92 1999 г. – стандарт SQL-1999 (SQL-3); реляционные и объектно-ориентированные свойства 8
7. 4. Предложения SQL INSERT INTO имя таблицы (колонка 1, … ) VALUES (значение 1, … ) DELETE FROM имя таблицы WHERE условие отбора строк UPDATE имя таблицы SET колонка 1 = выражение, … WHERE условие отбора строк 9
7. 5. Формирование запросов SELECT DISTINCT список вывода FROM источники WHERE условие отбора строк GROUP BY список для группирования HAVING условие отбора групп ORDER BY список для упорядочивания 10
7. 6. Примеры запросов Схема базы данных: S(Sid, SN, SC) – ПОСТАВЩИК ( Номер поставщика, Имя, Город) P(Pid, PN, PC) – ДЕТАЛЬ ( Номер детали, Название, Цена) SP(Sid(FK 1), Pid (FK 2), QTY) – ПОСТАВКА ( Номер поставщика, Номер детали, Количество) 11
7. 6. Примеры запросов 1. Получить имена поставщиков, поставляющих деталь с номером P 1. S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 12
7. 6. Примеры запросов SELECT SN “Имя поставщика” FROM S JOIN SP ON S. Sid = SP. Sid WHERE Pid = ‘P 1’ Имя поставщика Smith Jones Clark 13
7. 6. Примеры запросов 2. Получить номера и имена поставщиков, не поставляющих деталь с номером P 1 S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 14
7. 6. Примеры запросов SELECT Sid, SN FROM S WHERE Sid NOT IN ( SELECT Sid FROM SP WHERE Pid = ‘P 1’ ) Sid S 1 S 2 S 3 Sid SN S 4 Adams S 5 Black 15
7. 6. Примеры запросов 3. Получить имена поставщиков, поставляющих только деталь с номером P 1 S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 16
7. 6. Примеры запросов SELECT Sid, SN FROM S JOIN SP ON S. Sid = SP. Sid WHERE Pid = ‘P 1’ Sid S 1 AND S. Sid NOT IN ( SELECT Sid FROM SP WHERE Pid != ‘P 1’ ) Sid S 1 S 2 S 3 S 5 Sid SN S 2 Jones 17
7. 6. Примеры запросов 4. Получить имена поставщиков, поставляющих все детали S SId SN SC S 1 Smith S 2 SP SId PId Qty P PId PN PC London S 1 P 1 100 P 1 Nut Paris Jones Paris S 1 P 2 150 P 2 Bolt Rome S 3 Clark Paris S 2 P 1 200 P 3 Cam London S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 18
7. 6. Примеры запросов SELECT SN FROM S WHERE NOT EXISTS ( SELECT Pid FROM P WHERE NOT EXISTS ( SELECT Sid FROM SP WHERE P. Pid = SP. Pid AND S. Sid = SP. Sid ) ) 19
7. 6. Примеры запросов 4. Получить имена поставщиков, поставляющих максимальное количество деталей S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140
7. 6. Примеры запросов SELECT DISTINCT T 1. Qty from SP T 1, SP T 2 where T 1. qty > T 2. qty T 1 = SP SId PId Qty S 1 P 1 S 1 T 2 = SP SId PId Qty 100 S 1 P 1 100 P 2 150 S 1 P 2 150 S 2 P 1 200 S 3 P 1 110 S 3 P 2 20 S 3 P 3 180 S 5 P 2 140
7. 6. Примеры запросов SELECT DISTINCT SN, T 1. QTY FROM S join SP T 1 on S. SID = T 1. SID, SP T 2 WHERE T 1. QTY > T 2. QTY AND T 1. QTY not in ( SN QTY SELECT DISTINCT T 1. QTY Jones 200 FROM SP T 1, SP T 2 WHERE T 1. QTY < T 2. QTY )
7. 7. Использование агрегатных функций 1. Для всех поставщиков получить отчет в SN Total QTY виде: S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 23
7. 7. Использование агрегатных функций SELECT SN, coalesce(sum(Qty), 0) as ‘Total QTY’ FROM S left outer join SP on S. Sid = SP. Sid GROUP BY SN SN Total QTY Smith 250 Jones 200 Clark 310 Adams 0 Black 140 24
7. 7. Использование агрегатных функций 2. Для поставщиков, поставляющих более одной разновидности товара, получить отчет в виде: SN Total QTY Number of Products S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 25
7. 7. Использование агрегатных функций SELECT SN, sum(Qty) as ‘Total QTY’, count(Pid) as ‘Number of Products’ FROM S join SP on S. Sid = SP. Sid GROUP BY SN HAVING count(Pid) > 1 SN Total QTY Number of Products Smith 250 2 Clark 310 3 26
7. 7. Использование агрегатных функций 3. Получить имена поставщиков, поставляющих все детали SELECT SN FROM S join SP on S. Sid = SP. Sid GROUP BY SN HAVING count(Pid) = ( SELECT count(Pid) FROM P) 27
7. 7. Использование агрегатных функций Для поставщиков, поставляющих максимальное количество товара, получить SN QTY отчет в виде: S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 100 Jones Paris S 1 P 2 150 S 3 Clark Paris S 2 P 1 200 S 4 Adams London S 3 P 1 110 S 5 Black Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 V 1 SN Qty Jones 200 140 28
7. 7. Использование агрегатных функций SELECT SN, max(Qty) as ‘QTY’ FROM S join SP on S. Sid = SP. Sid GROUP BY SN 29
7. 7. Использование агрегатных функций Для поставщиков, поставляющих максимальное суммарное количество SN товара, получить отчет в виде: S SId SN SC S 1 Smith S 2 SP SId PId Qty London S 1 P 1 Jones Paris S 1 S 3 Clark Paris S 4 Adams S 5 Black V 1 Total QTY SId Qty 100 S 1 250 P 2 150 S 2 200 S 2 P 1 200 S 3 310 London S 3 P 1 110 S 5 140 Athens S 3 P 2 20 S 3 P 3 180 S 5 P 2 140 30
7. 7. Использование агрегатных функций CREATE VIEW V 1(Sid, Qty) as SELECT Sid, sum(Qty) from SP GROUP BY Sid go SELECT SN, Qty as ‘Total QTY’ FROM S join V 1 on S. Sid = V 1. Sid WHERE Qty = (SELECT max(Qty) From V 1) DROP VIEW V 1 31
7. 7. Использование агрегатных функций WITH V 1(Sid, Qty) as ( SELECT Sid, sum(Qty) from SP GROUP BY Sid ) SELECT SN, Qty as ‘Total QTY’ FROM S join V 1 on S. Sid = V 1. Sid WHERE Qty = (SELECT max(Qty) From V 1) 32
Скачать презентацию 7 Языки манипулирования данными 7 1 Общая
7. Языки манипулирования данными.ppt