Skip to content

SQL

Jump to bottom
Sᴛѧʀʟɪɴɢ edited this page Dec 19, 2018 · 1 revision

Виды соединений

Эквисоединением (equi-join) называется соединение, при котором две или более таблиц соединяются на основании условия равенства между столбцами. То есть один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах.

SELECT EMP.NAME, JOB.JOBNAME
FROM EMP, DEPT
WHERE EMP.EMPNO = JOB.EMPNO;
SELECT EMP.NAME, JOB.JOBNAME
FROM EMP JOIN DEPT 
USING (EMPNO);

Eстественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, не указываются. Они определяются системой автоматически.

SELECT EMP.NAME, JOB.JOBNAME
FROM EMP NATURAL JOIN DEPT;

Рефлексивным соединением (self join) называется соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.

Соединение таблицы с самой собой с удалением дублированных строк:

DELETE FROM EMP X 
WHERE ROWID > (
  SELECT MIN(ROWID)
  FROM EMP Y
  WHERE X.VALUES = Y.VALUES
);

Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения.

Примеры те же, что и у эквисоединения. Также можно использовать конструкцию ON:

SELECT DISTINCT NVL(DNAME, 'No Dept') DEPTNAME, COUNT(EMPNO) NBR_EMPS
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME;

Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения.
Три вида внешнего соединения: левое (left outer join), правое (right outer join) и полное (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.

SELECT DISTINCT NVL(DNAME, 'No Dept') DEPTNAME, COUNT(EMPNO) NBR_EMPS
FROM EMP FULL JOIN DEPT
ON DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME;

Операции

Операции сравнения позволяют сравнивать одно определенное значение столбца с несколькими другими значениями.

  • BETWEEN позволяет проверять, находится ли значение между двумя другими значениями.
  • IN позволяет проверять, присутствует ли значение в списке значений.
  • LIKE позволяет проверять, соответствует ли значение определенному образцу, как показано ниже.

Логические операции (булевские) позволяют логическим образом сравнивать два или более значений.

  • AND - и
  • OR - или
  • NOT - не
  • GE - больше или равно
  • LE - меньше или равно
SELECT * 
FROM EMP
WHERE YEARS BETWEEN 18 AND 25
OR EMPNAME IN ('FIN', 'JAKE')
AND YEARS LIKE 16;

Порядок выполнения операций:

  1. =, !=, <, >, <=, >=
  2. IS NULL, LIKE, BETWEEN, IN, EXISTS
  3. NOT
  4. AND
  5. OR

Операции над множествами.
Запрос называется составным, если он включает в себя результаты из нескольких подзапросов. Операции над множествами облегчают написание и выполнение составных запросов.

  • UNION объединяет результаты нескольких операторов SELECT, но сначала удаляет из них любые дублированные строки. В случае ее применения результирующий набор данных сортируется.
  • UNION ALL похожа на UNION, но не удаляет дублированные строки.
  • INTERSECTION получает общие (distinct) значения из двух или более результирующих наборов, происходящих от разных операторов SELECT. Эти значения имеют в конечном результирующем наборе уникальный и отсортированный вид.
  • EXCEPT возвращает те строки из результатов первого запроса, которых нет в результатах второго запроса. Эти строки имеют в конечном результирующем наборе уникальный и отсортированный вид.
SELECT EMPNO 
FROM EMP
UNION
SELECT EMPNO 
FROM NEW_EMP;

Типы подзапросов

Однострочные подзапросы возвращают в качестве результата только одну строку. В однострочных подзапросах обычно используют однострочные операторы сравнения (=, >, < и т.п.), сравнивающие выражение с одиночным значением, или логические операторы. Подзапрос, возвращающий более одной строки, называется многострочным и не может быть обработан с помощью однострочного оператора сравнения, необходимо использовать многострочный оператор сравнения, такой как IN, ANY или ALL.

Существует также подразделение подзапросов на коррелированные и некоррелированные. Данные, выбираемые некоррелированным подзапросом, никак не зависят от внешнего запроса. Некоррелированный подзапрос выполняется один раз для всего внешнего запроса. Коррелированный подзапрос (correlated subquery) содержит ссылку на данные внешнего запроса и выполняется не один раз, а для каждой строки внешнего запроса, поскольку данные внешнего запроса могут меняться.
Обычно коррелированный подзапрос применяется, чтобы ответить на многокомпонентный вопрос, ответ на который зависит от значения в каждой строке, обрабатываемой родительской инструкцией.
В коррелированном подзапросе можно использовать операторы ANY и ALL.
Любой некоррелированный подзапрос может быть переписан как коррелированный, но есть задачи, которые можно решить только с помощью коррелированных подзапросов.

Нужно определить максимальную дату полета для каждого пассажира и найти все его рейсы за эту дату

SELECT PASSNO, TRIPNO, [DATE] 
FROM PASS_IT_TRIP PT1
WHERE [date] = (
 SELECT MAX([DATE]) 
 FROM PASS_IT_TRIP PT2 
 WHERE PT1.PASSNO = PT2.PASSNO
);

Представления

Кроме базовых таблиц, которые содержат данные, существуют также и представления (views) - таблицы, чье содержание выбирается или получается из других таблиц. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных. Представления - подобны окнам, через которые вы просматриваете информацию, которая фактически хранится в базовой таблице. Представление - это фактически запрос, который выполняется всякий раз, когда представление становится темой команды. Вывод запроса при этом в каждый момент становится содержанием представления.

Создание представлений командой CREATE VIEW:

CREATE VIEW EMP_VIEW AS SELECT EMPNO, EMPNAME FROM EMP;

DML (Data Manipulation Language) - язык манипулирования данными (insert/update/delete).
Представление может изменяться командами модификации DML, но модификация не будет воздействовать на само представление: команды будут перенаправлены к базовой таблице.

Изменение командой UPDATE:

UPDATE EMP_VIEW SET EMPNAME = "Starling" WHERE EMPNO = 17;

Не все представления могут быть модифицированы. Например, этот пример выдаст ошибку, так как в представлении нет поля YEARS:

UPDATE EMP_VIEW SET YEARS = 20 WHERE EMPNO = 17;

Групповые представления - это представления, которые содержат предложение GROUP BY, или которые основываются на других групповых представлениях. Групповые представления могут стать превосходным способом обрабатывать полученную информацию непрерывно.

Представления могут облегчить работу со сложными запросами, как бы вынося его части или весь запрос целиком в отдельные переменные, с которыми намного проще работать.

CREATE VIEW TEMP_VIEW AS SELECT ... GROUP BY TEMPNO;
SELECT * FROM TEMP_VIEW;

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

Имеются большое количество типов представлений, которые являются доступными только для чтения.
Имеются некоторые виды запросов, которые не допустимы в определениях представлений. Одиночное представление должно основываться на одиночном запросе; ОБЪЕДИНЕНИЕ (UNION) и ОБЪЕДИНЕНИЕ ВСЕГО (UNIOM ALL) не разрешаются. упорядочивание (ORDER BY) никогда не используется в определении представлений. Вывод запроса формирует содержание представления, которое напоминает базовую таблицу и является - по определению - неупорядоченным.

Удаление представлений с помощью команды DROP VIEW:

DROP VIEW TEMP_VIEW;

Понятие модифицируемое представление (updating a view) означает возможность выполнения в представлении любой из трех команд модификации DML. Как определить, является ли представление модифицируемым? Основной принцип такой: модифицируемое представление - это представление в котором команда модификации может выполниться, чтобы изменить одну и только одну строку основной таблицы в каждый момент времени, не воздействуя на любые другие строки любой таблицы. Использование этого принципа на практике, однако, затруднено.

Критерии модифицируемости представления:

  • Оно должно выводиться в одну и только в одну базовую таблицу.
  • Оно должно содержать первичный ключ этой таблицы ( это технически не предписывается стандартом ANSI, но было бы неплохо придерживаться этого).
  • Оно не должно иметь никаких полей, которые бы являлись агрегатными функциями.
  • Оно не должно содержать DISTINCT в своем определении.
  • Оно не должно использовать GROUP BY или HAVING в своем определении.
  • Оно не должно использовать подзапросы ( это - ANSI_ограничение которое не предписано для некоторых реализаций )
  • Оно может быть использовано в другом представлении, но это представле- ние должно также быть модифицируемыми.
  • Оно не должно использовать константы, строки, или выражения значений ( например: comm * 100 ) среди выбранных полей вывода.
  • Для INSERT, оно должно содержать любые пол основной таблицы которые имеют ограничение NOT NULL, если другое ограничение по умолчанию, не определено.

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

У модифицируемых и немодифицируемых представлений обычно разные цели. Модифицируемые представления, в основном, используются точно так же как и базовые таблицы. Фактически, пользователи не могут даже осознать, является ли объект который они запрашивают, базовой таблицей или представлением. Это превосходный механизм защиты для сокрытия частей таблицы, которые являются конфиденциальными или не относятся к потребностям данного пользователя. Представления только_чтение позволяют вам получать и переформатировать данные более рационально. Они дают вам библиотеку сложных запросов, которые вы можете выполнить и повторить снова, сохраняя полученную информацию. Кроме того, результаты этих запросов в таблицах, которые могут затем использоваться в запросах самостоятельно (например, в объединениях) имеют преимущество над просто выполнением запросов.

Clone this wiki locally