Навигация

Основы Oracle 18c - 19c часть 4 - SQL запросы чуть сложнее

Основы Oracle 18c - 19c часть 4 - SQL запросы чуть сложнее

В данной заметке речь пойдет о более сложных запросах

Применим соединение таблиц, функции работы с датами и временем и т.п.

Выборка данных - SELECT

Возможные члены:

SELECT, FROM, WHERE, GROUP BY, HAVING, PIVOT, UNPIVOT, CONNECT BY, ORDER BY

Члены SELECT и FROM в конструкции - обязательны

Проризводительность во многом завист от правильно спланированного запроса

Отсечение ненужных данных на шаге WHERE даст большую производительность, чем на HAVING

Логика обработки этапов SELECT:

FROM

PIVOT, UNPIVOT

CONNECT BY

WHERE

GROUP BY

HAVING

ORDER BY

SELECT

Из предыдущего урока мы имеем три взаимосвязанные таблицы с данными колонками:

payments ( payment_num, payment_date, customer, customer_address, total_cost )

payments_goods ( payment_num, good_num, good_name, quantity, price, cost )

payments_services ( payment_num, service_name, cost )

Из предыдущей статьи у нас есть несколько записей:

SELECT * FROM payments ;

Добавим записей в другие таблицы:

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 1, 1, 'Beer Baltika 8', 100, 3000, 300000 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 1, 2, 'Beer Baltika 9', 100, 456.89, 45689 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 2, 1, 'Sneki Tomat', 2, 500, 1000 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 2, 2, 'Fish', 1, 256, 256 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 3, 1, 'Bolt M6', 6, 2000, 12000 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 3, 2, 'Key', 1, 221, 221 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 4, 1, 'Oil 10W40', 1, 500, 500 );

INSERT INTO payments_goods 
  ( payment_num, good_num, good_name, quantity, price, cost )
VALUES 
  ( 4, 2, 'Oil Tube', 1, 56, 56 );

INSERT INTO payments_services 
  ( payment_num, service_name, cost )
VALUES 
  ( 1, 'Express', 100 );

INSERT INTO payments_services 
  ( payment_num, service_name, cost )
VALUES 
  ( 3, 'Slow', 50 );

Попробуем сделать подзапрос

Выберем все из payments_goods, что совпадет по payment_num с таблицей payments

Теперь попробуем соеденить таблицы с помощью JOIN

--Традиционный синтаксис Oracle (аналог JOIN)
SELECT 
  p.payment_num, p.customer, 
  pd.good_name, pd.cost, 
  ps.service_name, ps.cost 
FROM 
  payments p, 
  payments_goods pd, 
  payments_services ps
WHERE 
  p.payment_num = pd.payment_num AND 
  p.payment_num = ps.payment_num;

--Синтаксис ANSI/ISO (JOIN, аналог предыдущего примера)
SELECT 
  p.payment_num, p.customer, 
  pd.good_name, pd.cost, 
  ps.service_name, ps.cost 
FROM 
  payments p
JOIN  
  payments_goods pd
ON 
  p.payment_num = pd.payment_num 
JOIN  
  payments_services ps
ON 
  p.payment_num = ps.payment_num;

--LEFT OUTER левое внешнее(все значения из левой)
SELECT 
  p.payment_num, p.customer, 
  pd.good_name, pd.cost, 
  ps.service_name, ps.cost 
FROM 
  payments p
LEFT OUTER JOIN  
  payments_goods pd
ON 
  p.payment_num = pd.payment_num 
LEFT OUTER JOIN  
  payments_services ps
ON 
  p.payment_num = ps.payment_num;

--RIGHT OUTER правое внешнее(все значения из правой)
SELECT 
  p.payment_num, p.customer, 
  pd.good_name, pd.cost, 
  ps.service_name, ps.cost 
FROM 
  payments p
RIGHT OUTER JOIN  
  payments_goods pd
ON 
  p.payment_num = pd.payment_num 
RIGHT OUTER JOIN  
  payments_services ps
ON 
  p.payment_num = ps.payment_num;

--FULL полное(все значения из всех таблиц в т.ч. не имеющие связь)
SELECT 
  p.payment_num, p.customer, 
  pd.good_name, pd.cost, 
  ps.service_name, ps.cost 
FROM 
  payments p
FULL JOIN  
  payments_goods pd
ON 
  p.payment_num = pd.payment_num 
FULL JOIN  
  payments_services ps
ON 
  p.payment_num = ps.payment_num;

--INNER внутреннее(все значения из всех таблиц имеющие связь)
SELECT 
  p.payment_num, p.customer, 
  pd.good_name, pd.cost, 
  ps.service_name, ps.cost 
FROM 
  payments p
INNER JOIN  
  payments_goods pd
ON 
  p.payment_num = pd.payment_num 
INNER JOIN  
  payments_services ps
ON 
  p.payment_num = ps.payment_num;

В примере с LEFT OUTER JOIN мы увидели значения NULL

Это говорит о том, что в данной ячейке нет никакого значения (пустота)

Работа с Null

Функция NVL (expr1, expr2)

Возвращает значение expr1, если expr1 is null - возвращает значение expr2

Функция NVL2 (expr1, expr2, expr3)

expr1 – выражение

expr2 – если expr1 "is not null" будет возвращено expr2

expr3 – если expr1 "is null" будет возвращено expr3

Теперь посмотрим несколько конструкций, которые могут работать со значениями

dual - псевдотаблица в Oracle для получения отвлеченных значений SELECT

Выражение CASE может содержать несколько блоков WHEN-THEN и один или ниодного ELSE

Синтаксис:

CASE

WHEN условное_выражение=true THEN выражение_результат

ELSE выражение_результат

END

Преобразование типов данных может функция CAST

Строковые функции для работы с текстом типа VARCHAR2 и CHAR

LENGTH — вычисление длины строки

LOWER, UPPER — нижний и верхний регистр

INITCAP — повышение регистра первых букв в словах и понижение остальных

RTRIM, LTRIM — обрезание одинаковых символов в конце или в начале (по умолчанию пробелов)

RPAD, LPAD — "добивание" строки текста одинаковыми символами справа или слева (по умолчанию пробелами)

INSTR, SUBSTR — поиск вхождения подстроки и замена

О работе с датами и временем

--Выборка месяц\день\год\часов\минут\секунд из даты
SELECT 
  TO_NUMBER(EXTRACT (MONTH FROM a.dt)) MM, 
  TO_NUMBER(EXTRACT (DAY FROM a.dt)) DD, 
  TO_NUMBER(EXTRACT (YEAR FROM a.dt)) YY, 
  TO_NUMBER(EXTRACT (HOUR FROM a.dt)) hh, 
  TO_NUMBER(EXTRACT (MINUTE FROM a.dt)) mm, 
  TO_NUMBER(EXTRACT (SECOND FROM a.dt)) ss 
FROM (SELECT CAST(SYSDATE AS TIMESTAMP) dt FROM dual) a;

--DBTIMEZONE - зона, установленная в базе данных
--SESSIONTIMEZONE - зона в сессии пользователя
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;

--CURRENT_DATE - текущая дата
--CURRENT_TIMESTAMP - текущая дата + время с зоной
--LOCALTIMESTAMP - текущая дата + время без зоны
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

--ADD_MONTHS(d, x)
--Возвращает дату, полученную в результате прибавления одного или нескольких месяцев. 
--Количество месяцев задается параметром x, который может быть отрицательным.
SELECT ADD_MONTHS (SYSDATE, -5) d FROM dual;

--LAST_DAY(d) - Возвращает последнее число месяца
SELECT LAST_DAY (SYSDATE) d FROM dual;

--Вычислить разницу в сутках
SELECT ROUND(to_date('13.01.2020', 'DD/MM/YYYY' )-SYSDATE,0) AS diff FROM dual;

--ADD_MONTHS добавляет\отнимает месяцы
--Вычислить дату двухмесячной давности
SELECT ADD_MONTHS(TRUNC(SYSDATE),-2) FROM dual;

Комментарии пользователей

Эту новость ещё не комментировалиНаписать комментарий

Анонимам нельзя оставоять комментарии, зарегистрируйтесь!

Главная	⇒ Статьи по тематикам ⇐	Карта сайта	Контакты
Учебники по IT	IP-Калькулятор	NSLOOKUP	Видео IT
Баллистический калькулятор: тип 1 и тип 2 Калькулятор поправок в MOA Баллистический коэффициент пневматики		Файлы NSLOOKUP IP-Калькулятор

Контакты Группа ВК	Код обмена баннерами		Видео к IT статьям на YoutubeВидео на другие темы Смотреть
Мои друзья:			© Snakeproject.ru создан в 2013 году. При копировании материала с сайта - оставьте ссылку. Весь материал на сайте носит ознакомительный характер, за его использование другими людьми, автор ответственности не несет.
		Поддержать автора и проект