🗊Презентация Язык SQL. (Лекция 2)

СУБД Лекция 2 Основы языка SQL

II. Отбор данных из нескольких таблиц II. Отбор данных из нескольких таблиц 1) Естественное соединение таблиц (способ 1 - явное указание условий соединения): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P, PD WHERE P.PNUM = PD.PNUM; 2) Естественное соединение таблиц (способ 2 - ключевые слова JOIN… USING…): SELECT P.PNUM, P.PNAME, PD.DNUM,PD.VOLUME FROM P JOIN PD USING PNUM; Замечание. Ключевое слово USING позволяет явно указать, по каким из общих колонок таблиц будет производиться соединение.

3) Естественное соединение таблиц (способ 3 - ключевое слово NATURAL JOIN): 3) Естественное соединение таблиц (способ 3 - ключевое слово NATURAL JOIN): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P NATURAL JOIN PD; Замечание. В разделе FROM не указано, по каким полям производится соединение. NATURAL JOIN автоматически соединяет по всем одинаковым полям в таблицах. 4) Естественное соединение трех таблиц: SELECT P.PNAME, D.DNAME, PD.VOLUME FROM P NATURAL JOIN PD NATURAL JOIN D;

5) Прямое произведение таблиц: 5) Прямое произведение таблиц: SELECT P.PNUM, P.PNAME, D.DNUM, D.DNAME FROM P, D; 6) Соединение таблиц по произвольному условию. Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает запрос: SELECT P.PNUM,P.PNAME,P.PSTATUS, D.DNUM,D.DNAME,D.DSTATUS FROM P, D WHERE P.PSTATUS >= D.DSTATUS;

III. Использование имен корреляции (алиасов, псевдонимов) III. Использование имен корреляции (алиасов, псевдонимов) Существуют запросы, в которых таблица соединяется сама с собой, или одна таблица соединяется дважды с другой таблицей. В этих случаях используются имена корреляции (алиасы, псевдонимы), которые позволяют различать соединяемые копии таблиц. Имена корреляции вводятся в разделе FROM и идут через пробел после имени таблицы. Имена корреляции должны использоваться в качестве префикса перед именем столбца и отделяются от имени столбца точкой. Если в запросе указываются одни и те же поля из разных экземпляров одной таблицы, они должны быть переименованы для устранения неоднозначности в именованиях колонок результирующей таблицы. Определение имени корреляции действует только во время выполнения запроса.

1) Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика: 1) Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика: 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;

2) Пусть некоторые поставщики (назовем их контрагенты) могут выступать как в качестве поставщиков деталей, так и в качестве получателей. 2) Пусть некоторые поставщики (назовем их контрагенты) могут выступать как в качестве поставщиков деталей, так и в качестве получателей.

Запрос "кто кому какие детали в каком количестве поставляет". Запрос "кто кому какие детали в каком количестве поставляет". SELECT P.NAME AS PNAME, C.NAME AS CNAME, DETAILS.DNAME, CD.VOLUME FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS, CD WHERE P.NUM = CD.PNUM AND C.NUM = CD.CNUM AND D.DNUM = CD.DNUM; Замечание. Этот запрос может быть выражен большим количеством способов.

IV. Использование в запросах агрегатных функций IV. Использование в запросах агрегатных функций 1) Получить общее количество поставщиков (ключевое слово COUNT): SELECT COUNT(*) AS N FROM P; 2) Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG): SELECT SUM(PD.VOLUME) AS SM, MAX(PD.VOLUME) AS MX, MIN(PD.VOLUME) AS MN, AVG(PD.VOLUME) AS AV FROM PD;

V. Использование агрегатных функций с группировками V. Использование агрегатных функций с группировками 1) Для каждой детали получить суммарное поставляемое количество (ключевые слова GROUP BY…): SELECT PD.DNUM, SUM(PD.VOLUME) AS SM FROM PD GROUP BY PD.DNUM; Замечание. Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результирующую таблицу будет включена одна строка.

Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку: Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку: SELECT PD.PNUM, PD.DNUM, SUM(PD.VOLUME) AS SM FROM PD GROUP BY PD.DNUM; Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результирующей таблице, невозможно (в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM).

2) Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING…- условие отбора групп). 2) Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING…- условие отбора групп). SELECT PD.DNUM, SUM(PD.VOLUME) AS SM FROM PD GROUP BY PD.DNUM HAVING SUM(PD.VOLUME) > 400; Замечание. Условие, что суммарное поставляемое количество должно быть больше 400, не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции, должны быть размещены в специальном разделе HAVING.

Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY. Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY. Замечание. В MySQL допускается в HAVING использовать вместо агрегатной функции ее алиас. SELECT PD.DNUM, SUM(PD.VOLUME) AS SM FROM PD GROUP BY PD.DNUM HAVING SM > 400;

VI. Использование подзапросов VI. Использование подзапросов Удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос. 1) Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом). SELECT * FROM P WHERE P.STATUS < (SELECT MAX(P.STATUS) FROM P);

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

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

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

3) Использование подзапросов в качестве вычисляемых полей. Для каждого поставщика подсчитать количество поставляемых им видов деталей. 3) Использование подзапросов в качестве вычисляемых полей. Для каждого поставщика подсчитать количество поставляемых им видов деталей. SELECT P.PNUM, P.PNAME, (SELECT COUNT(*) FROM PD WHERE PD.PNUM=P.PNUM) AS NUMBER0FDET FROM P ORDER BY P.PNUM; Замечание. Вложенный запрос выполняется многократно для каждого значения P.PNUM из таблицы P. Для каждого значения P.PNUM из таблицы P в таблице PD выбираются строки с текущим значением PNUM из внешнего запроса и подсчитывается их количество.