Языки манипулирования данными

Общая характеристика Язык, в котором можно (по крайней мере) моделировать исчисление с переменными- кортежами, либо, что равносильно, реляционную алгебру или исчисление с переменными на доменах, называется полным 2

Обзор языков манипулирования данными (1) ISBL (Information System Base Language) – «чистый» язык реляционной алгебры. Разработан в исследовательском центре фирмы IBM в Питерли (Англия) для использования в экспериментальной системе PRTV (Peterlee Relational Test Vehicle). Нет агрегатных операций, а также средств для вставки, удаления и модификации кортежей 3

Обзор языков манипулирования данными (2) SEQUEL (Structured English Query Language) – разработан в 1974 г. в исследовательской лаборатории IBM в Сан-Хосе; использует реляционную алгебру, но имеет синтаксис, напоминающий реляционное исчисление с переменными-кортежами г. – СУБД System R; важнейший из результатов – разработка языка SQL (Structured Query Language) 4

Обзор языков манипулирования данными (3) QUEL – язык реляционного исчисления с переменными-кортежами, разработан в Калифорнийском университете в Беркли в конце 70-х г.г. для реляционной СУБД INGRES. Включает широкий спектр операторов реляционного исчисления с переменными-кортежами, агрегатные функции. Более структурирован, чем SQL 5

Обзор языков манипулирования данными (4) QBE (Query-By-Example) – язык исчисления с переменными на доменах; разработан в Исследовательском центре IBM в Йорктаун- Хейтсе. Предназначен для работы с терминала. Включены агрегатные функции 6

Обзор языков манипулирования данными (5) SQL (Structured Query Language) – язык, ориентированный на отображение; описывается отображение известного атрибута или множества атрибутов в искомый атрибут или множество атрибутов. Первая коммерческая СУБД – ORACLE (конец 70-х г.г.) 7

SQL 1982 г. – ANSI, 1983 г. – Международный комитет по стандартизации (ISO) 1987 г. – исходный вариант стандарта языка SQL (SQL-87, SQL 1-го поколения) 1992 г. – первая версия стандарта ISO, SQL-2 или SQL г. – стандарт SQL-1999 (SQL-3); реляционные и объектно-ориентированные свойства 8

Предложения SQL INSERT INTO имя таблицы (колонка 1, … ) VALUES (значение 1, … ) DELETE FROM имя таблицы WHERE условие отбора строк UPDATE имя таблицы SET колонка 1 = выражение, … WHERE условие отбора строк 9

Формирование запросов SELECT DISTINCT список вывода FROM источники WHERE условие отбора строк GROUP BY список для группирования HAVING условие отбора групп ORDER BY список для упорядочивания 10

Примеры запросов (1) Схема базы данных: S(Sid, SN, SC) – ПОСТАВЩИК ( Номер поставщика, Имя, Город) P(Pid, PN, PC) – ДЕТАЛЬ ( Номер детали, Название, Цена) SP(Sid(FK1), Pid (FK2), QTY) – ПОСТАВКА ( Номер поставщика, Номер детали, Количество) 11

Примеры запросов (2) 1. Получить имена поставщиков, поставляющих деталь с номером P1. 12 SSIdSNSCSPSIdPIdQty S1SmithLondonS1P1100 S2JonesParisS1P2150 S3ClarkParisS2P1200 S4AdamsLondonS3P1110 S5BlackAthensS3P220 S3P3180 S5P2140

Примеры запросов (3) SELECT SN Имя поставщика FROM S JOIN SP ON S.Sid = SP.Sid WHERE Pid = P1 13 Имя поставщика Smith Jones Clark

Примеры запросов (4) 2. Получить номера и имена поставщиков, не поставляющих деталь с номером P1 14 SSIdSNSCSPSIdPIdQty S1SmithLondonS1P1100 S2JonesParisS1P2150 S3ClarkParisS2P1200 S4AdamsLondonS3P1110 S5BlackAthensS3P220 S3P3180 S5P2140

Примеры запросов (5) SELECT Sid, SN FROM S WHERE Sid NOT IN ( SELECT Sid FROM SP WHERE Pid = P1 ) 15 SidSN S4Adams S5Black Sid S1 S2 S3

Примеры запросов (6) 3. Получить имена поставщиков, поставляющих только деталь с номером P1 16 SSIdSNSCSPSIdPIdQty S1SmithLondonS1P1100 S2JonesParisS1P2150 S3ClarkParisS2P1200 S4AdamsLondonS3P1110 S5BlackAthensS3P220 S3P3180 S5P2140

Примеры запросов (7) SELECT Sid, SN FROM S JOIN SP ON S.Sid = SP.Sid WHERE Pid = P1 AND S.Sid NOT IN ( SELECT Sid FROM SP WHERE Pid != P1 ) 17 Sid S1 S2 S3 Sid S1 S3 S5 SidSN S2Jones

Примеры запросов (8) 4. Получить имена поставщиков, поставляющих все детали 18 SSIdSNSCSPSIdPIdQtyPPIdPNPC S1SmithLondonS1P1100P1NutParis S2JonesParisS1P2150P2BoltRome S3ClarkParisS2P1200P3CamLondon S4AdamsLondonS3P1110 S5BlackAthensS3P220 S3P3180 S5P2140

Примеры запросов (9) SELECT SN FROM S WHERE NOT EXISTS ( SELECT Pid FROM P WHERE NOT EXISTS ( SELECT Sid FROM SP WHERE S.Sid = SP.Sid AND P.Pid = SP.Pid ) 19

Использование агрегатных функций (1) 1. Для всех поставщиков получить отчет в виде: 20 SSIdSNSCSPSIdPIdQty S1SmithLondonS1P1100 S2JonesParisS1P2150 S3ClarkParisS2P1200 S4AdamsLondonS3P1110 S5BlackAthensS3P220 S3P3180 S5P2140 SNTotal QTY

Использование агрегатных функций (2) SELECT SN, coalesce(sum(Qty),0) as Total QTY FROM S left outer join SP on S.Sid = SP.Sid GROUP BY SN 21 SNTotal QTY Smith250 Jones200 Clark310 Adams0 Black140

Использование агрегатных функций (3) 2. Для поставщиков, поставляющих более одной разновидности товара, получить отчет в виде: 22 SSIdSNSCSPSIdPIdQty S1SmithLondonS1P1100 S2JonesParisS1P2150 S3ClarkParisS2P1200 S4AdamsLondonS3P1110 S5BlackAthensS3P220 S3P3180 S5P2140 SNTotal QTYNumber of Products

Использование агрегатных функций (4) 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 23 SNTotal QTYNumber of Products Smith2502 Clark3103

Использование агрегатных функций (5) 3. Получить имена поставщиков, поставляющих все детали SELECT SN FROM S join SP on S.Sid = SP.Sid GROUP BY SN HAVING count(Pid) = ( SELECT count(Pid) FROM P) 24

Использование агрегатных функций (6) Для поставщиков, поставляющих максимальное суммарное количество товара, получить отчет в виде: 25 SNTotal QTY SSIdSNSCSPSIdPIdQtyV1SIdQty S1SmithLondonS1P1100S1250 S2JonesParisS1P2150S2200 S3ClarkParisS2P1200S3310 S4AdamsLondonS3P1110S5140 S5BlackAthensS3P220 S3P3180 S5P2140

Использование агрегатных функций (7) CREATE VIEW V1(Sid, Qty) as SELECT Sid, sum(Qty) from SP GROUP BY Sid go SELECT SN, Qty as Total QTY FROM S join V1 on S.Sid = V1. Sid WHERE Qty = (SELECT max(Qty) From V1) DROP VIEW V1 26

Использование агрегатных функций (8) WITH V1(Sid, Qty) as ( SELECT Sid, sum(Qty) from SP GROUP BY Sid ) SELECT SN, Qty as Total QTY FROM S join V1 on S.Sid = V1. Sid WHERE Qty = (SELECT max(Qty) From V1) 27