Скачать презентацию SQL Поисковые запросы Оператор SELECT DISTINCT функция Скачать презентацию SQL Поисковые запросы Оператор SELECT DISTINCT функция

SQL-select-2.ppt

  • Количество слайдов: 44

SQL. Поисковые запросы. SQL. Поисковые запросы.

Оператор SELECT [DISTINCT] {{функция агрегирования. . | выражение для вычисления значения [AS имя столбца]}. Оператор SELECT [DISTINCT] {{функция агрегирования. . | выражение для вычисления значения [AS имя столбца]}. , } | {спецификатор. *} |* FROM {{ имя таблицы [AS][имя корреляции]. [(имя столбца. , . . )]} | {подзапрос [AS][имя корреляции. [имя столбца. , . . ]} | соединенная таблица }. , . . [WHERE предикат ] [GROUP BY {{[ имя таблицы | имя корреляции]}. ] имя столбца}. , . . }] [HAVING предикат] [UNION | INTERSECT | EXCEPT}[ALL] [CORRESPONDING [BY (имя столбца. , . . )]] оператор select | TABLE имя таблицы | конструктор значений таблицы] [ORDER BY{{столбец-результат [ASC| DESC]}. , . . } | {{положительное число[ASC| DESC]}. , . . }] };

DISTINCT - Из ответа удаляются строкидубликаты функция агрегирования: : ={COUNT(*)}| {{AVG |Sum |MAX |MIN DISTINCT - Из ответа удаляются строкидубликаты функция агрегирования: : ={COUNT(*)}| {{AVG |Sum |MAX |MIN | COUNT} ([DISTINCT | ALL] выражение для вычисления значения)}

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Все строки считаются Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках. Второй тип функции COUNT подсчитывает все значения столбца, не являющиеся значениями NULL

типы соединений Тип соединения Выполняемое действие Cross (перекрестное) прямое декартово произведение Natural (естественное) соединение типы соединений Тип соединения Выполняемое действие Cross (перекрестное) прямое декартово произведение Natural (естественное) соединение внешнего ключа со связанным с ним ключом (одноименные столбцы) Inner (включающее или внутреннее) эквисоединение таблиц А и В (равные значения соответствующих столбцов) Left (левое (внешнее)) все строки таблицы А, а также значения из тех строк таблицы В, которые имеют совпадающие значения в поле связи Right (правое (внешнее)) все строки таблицы В, а также значения из тех строк таблицы А, которые имеют совпадающие значения в поле связи Full (полное) объединяет левое и правое соединение Union (соединение типа объединение) «противоположно» Inner

Таблица «А_СОТРУДНИКИ» таб_ном фио 01 Диго 02 Афанасьев 03 Сидоров Таблица «А_СОТРУДНИКИ» таб_ном фио 01 Диго 02 Афанасьев 03 Сидоров

Таблица «Б_РАЗРАБОТКИ» , фио продукт Диго п 1 Диго п 2 Афанасьев п 3 Таблица «Б_РАЗРАБОТКИ» , фио продукт Диго п 1 Диго п 2 Афанасьев п 3 Чистов п 4

Результат перекрестного соединения таб_ном а_сотрудники. фио б_разработки. фио продукт 01 Диго п 1 01 Результат перекрестного соединения таб_ном а_сотрудники. фио б_разработки. фио продукт 01 Диго п 1 01 Диго п 2 01 Диго Афанасьев п 3 01 Диго Чистов п 4 02 Афанасьев Диго п 1 02 Афанасьев Диго п 2 02 Афанасьев п 3 02 Афанасьев Чистов п 4 03 Сидоров Диго п 1 03 Сидоров Диго п 2 03 Сидоров Афанасьев п 3 03 Сидоров Чистов п 4

перекрестное соединение SELECT а_сотрудники. таб_ном, а_сотрудники. фио, б_разработки. продукт FROM а_сотрудники, б_разработки; перекрестное соединение SELECT а_сотрудники. таб_ном, а_сотрудники. фио, б_разработки. продукт FROM а_сотрудники, б_разработки;

INNER JOIN Таб_ном фио продукт 01 Диго п 2 01 Диго п 1 02 INNER JOIN Таб_ном фио продукт 01 Диго п 2 01 Диго п 1 02 Афанасьев п 3

INNER JOIN SELECT а_сотрудники. таб_ном, а_сотрудники. фио, б_разработки. продукт FROM а_сотрудники INNER JOIN б_разработки INNER JOIN SELECT а_сотрудники. таб_ном, а_сотрудники. фио, б_разработки. продукт FROM а_сотрудники INNER JOIN б_разработки ON а_сотрудники. фио = б_разработки. фио;

LEFT JOIN таб_ном фио продукт 01 Диго п 2 01 Диго п 1 02 LEFT JOIN таб_ном фио продукт 01 Диго п 2 01 Диго п 1 02 Афанасьев п 3 03 Сидоров

LEFT JOIN SELECT а_сотрудники. таб_ном, а_сотрудники. фио, б_разработки. продукт FROM а_сотрудники LEFT JOIN б_разработки LEFT JOIN SELECT а_сотрудники. таб_ном, а_сотрудники. фио, б_разработки. продукт FROM а_сотрудники LEFT JOIN б_разработки ON а_сотрудники. фио = б_разработки. фио;

RIGHT JOIN таб_ном фио продукт 01 Диго п 1 01 Диго п 2 02 RIGHT JOIN таб_ном фио продукт 01 Диго п 1 01 Диго п 2 02 Афанасьев п 3 Чистов п 4

RIGHT JOIN SELECT а_сотрудники. таб_ном, б_разработки. фио, б_разработки. продукт FROM а_сотрудники RIGHT JOIN б_разработки RIGHT JOIN SELECT а_сотрудники. таб_ном, б_разработки. фио, б_разработки. продукт FROM а_сотрудники RIGHT JOIN б_разработки ON а_сотрудники. фио = б_разработки. фио;

FULL JOIN таб_ном фио продукт 01 Диго П 1 01 Диго П 2 02 FULL JOIN таб_ном фио продукт 01 Диго П 1 01 Диго П 2 02 Афанасьев П 3 03 Сидоров NULL Чистов П 4

UNION JOIN таб_ном фио продукт 03 Сидоров NULL Чистов П 4 UNION JOIN таб_ном фио продукт 03 Сидоров NULL Чистов П 4

Предложение WHERE Предложение WHERE

предикаты • <интервальный предикат > • <предикат IN> • < предикат проверки на неопределенное предикаты • <интервальный предикат > • <предикат IN> • < предикат проверки на неопределенное значение > • <предикат подобия>

интервальный предикат WHERE [NOT]<выражение> BETWEEN <нижнее выражение> AND < верхнее выражение> Пример: SELECT * интервальный предикат WHERE [NOT]<выражение> BETWEEN <нижнее выражение> AND < верхнее выражение> Пример: SELECT * FROM post WHERE postdate BETWEEN #11/20/02# AND #11/30/02#; Другая форма: … WHERE postdate>=#11/20/02# AND postdate<= #11/30/02#;

предикат IN WHERE [NOT]< выражение > [NOT] IN (<список значений>/<подзапрос>) Пример: SELECT * FROM предикат IN WHERE [NOT]< выражение > [NOT] IN (<список значений>/<подзапрос>) Пример: SELECT * FROM post WHERE kod_post IN (“p 1”, “p 2”, “p 3”) Другая форма: SELECT * FROM post WHERE kod_post =“p 1” OR kod_post =“p 2” OR kod_post = “p 3”;

Предикат подобия WHERE [NOT] <выражение_для_вычисления_значения_строки_1> [NOT] LIKE <выражение_для_вычисления_значения_строки_2>. разрешается использовать заполнители (трафаретные символы): • Предикат подобия WHERE [NOT] <выражение_для_вычисления_значения_строки_1> [NOT] LIKE <выражение_для_вычисления_значения_строки_2>. разрешается использовать заполнители (трафаретные символы): • Символ подчеркивания (_) – используется вместо любого единичного символа в проверяемом значении. • Символ процента (%) – заменяет набор любых символов в проверяемом значении.

Пример предиката подобия SELECT * FROM post WHERE kod_mat LIKE ‘м%’; Пример предиката подобия SELECT * FROM post WHERE kod_mat LIKE ‘м%’;

Предикат проверки на неопределенное значение предикат NULL : : = конструктор значения строки IS Предикат проверки на неопределенное значение предикат NULL : : = конструктор значения строки IS [NOT] NULL Пример: SELECT fio FROM sotr WHERE ych_st IS NULL; - Список «неостепенных» сотрудников

квантор существования WHERE [NOT] EXISTS (<подзапрос>) Пример: SELECT naimmat FROM cennik WHERE EXISTS (SELECT квантор существования WHERE [NOT] EXISTS (<подзапрос>) Пример: SELECT naimmat FROM cennik WHERE EXISTS (SELECT * FROM post WHERE kodpost='P 01' AND cennik. kodmat=post. kodmat); Список материалов, поставленных поставщиком P 01

Предложение GROUP BY Предложение GROUP BY

GROUP BY SELECT uch, AVG(zpl) FROM zarpl GROUP BY uch; - средняя зарплата по GROUP BY SELECT uch, AVG(zpl) FROM zarpl GROUP BY uch; - средняя зарплата по участку - Группировка может производиться по нескольким полям

Предложение HAVING Предложение HAVING

• <having clause> : : = HAVING <search condition> • условие поиска раздела • : : = HAVING • условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки

SELECT codmat FROM post GROUP BY codmat HAVING COUNT(*)>1; - Список кодов материалов, по SELECT codmat FROM post GROUP BY codmat HAVING COUNT(*)>1; - Список кодов материалов, по которым было больше одной поставки

При использовании со звездочкой функция COUNT возвращает число всех строк в таблице, без исключения При использовании со звездочкой функция COUNT возвращает число всех строк в таблице, без исключения повторяющихся, не обращая внимания на возможно имеющиеся в столбце значения NULL

Предложение ORDER BY Предложение ORDER BY

РАЗДЕЛ ORDER BY • раздел ORDER BY позволяет установить желаемый порядок просмотра результата • РАЗДЕЛ ORDER BY • раздел ORDER BY позволяет установить желаемый порядок просмотра результата • Синтаксис ORDER BY : : : = ORDER BY [{, }. . . ] : : = { | } [ASC | DESC]

• задается список столбцов результата выражения • • запросов, и для каждого столбца • задается список столбцов результата выражения • • запросов, и для каждого столбца указывается порядок просмотра строк результата в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Столбцы можно задавать их именами тогда и только тогда, когда (1) выражение запросов не содержит операций UNION или UNION ALL и (2) в списке выборки спецификации запроса этому столбцу соответствует арифметическое выражение, состоящее только из имени столбца. Во всех остальных случаях в разделе ORDER BY должен указываться порядковый номер столбца в таблице-результате выражения запросов.

ORDER BY SELECT * FROM postavka ORDER BY kodpost, post_date; ORDER BY SELECT * FROM postavka ORDER BY kodpost, post_date;

Запросы, затрагивающие несколько таблиц Запросы, затрагивающие несколько таблиц

Варианты • условия соединения таблиц заданы в предложении WHERE • вложенные запросы • встроенные Варианты • условия соединения таблиц заданы в предложении WHERE • вложенные запросы • встроенные операторы JOIN

Описание примера Имеются две таблицы. Первая (POSTAVKA) содержит сведения о поставках продукции и включает Описание примера Имеются две таблицы. Первая (POSTAVKA) содержит сведения о поставках продукции и включает колонки: код поставщика (kod_post), код продукции (kod_prod), дата поставки (dat_post), количество поставленной продукции (kolv). Вторая таблица (SP_POST) содержит колонки код поставщика и наименование поставщика (naim_pst). Требуется выдать наименования поставщиков, которые поставляют товар с кодом Р 2.

Вариант 1 - простой вложенный запрос SELECT naim_post FROM sp_post WHERE kod_post IN (SELECT Вариант 1 - простой вложенный запрос SELECT naim_post FROM sp_post WHERE kod_post IN (SELECT kod_post FROM postavka WHERE kod_prod="P 2");

Вариант 2 – коррелированный подзапрос SELECT naim_post FROM sp_post WHERE Вариант 2 – коррелированный подзапрос SELECT naim_post FROM sp_post WHERE "P 2" IN (SELECT kod_prod FROM postavka WHERE sp_post. kod_post=postavka. kod_post);

Вариант 3 – условие связывания таблиц в WHERE SELECT naim_post FROM sp_post, postavka WHERE Вариант 3 – условие связывания таблиц в WHERE SELECT naim_post FROM sp_post, postavka WHERE postavka. kod_post=sp_post. kod_post AND postavka. kod_prod="P 2";

Вариант 4 – квантор существования SELECT naim_post FROM sp_post WHERE EXISTS (SELECT * FROM Вариант 4 – квантор существования SELECT naim_post FROM sp_post WHERE EXISTS (SELECT * FROM postavka WHERE kod_post=sp_post. kod_post AND kod_prod="P 2");

Вариант 5 - встроенный операторы JOIN SELECT naim_post FROM sp_post INNER JOIN postavka ON Вариант 5 - встроенный операторы JOIN SELECT naim_post FROM sp_post INNER JOIN postavka ON sp_post. kod_post= postavka. kod_post WHERE kod_prod="P 2";

• Стандарт SQL – это эталон, к которому следует • • стремиться при • Стандарт SQL – это эталон, к которому следует • • стремиться при создании СУБД. Ни одна из СУБД не реализует полностью все возможности стандарта. Необходимо детально изучить диалект SQL той СУБД, в среде которой Вы работаете. Особенно внимательно к особенностям реализации SQL надо относиться при работе в гетерогенной среде. SQL не является функционально полным языком. Как правило он используется совместно с каким-либо включающим языком.