Обзор основных sql запросов

Временные таблицы

Временная таблица — это объект базы данных, который хранится и управляется системой базы данных на временной основе. Временные таблицы могут быть локальными или глобальными. Локальные временные таблицы представлены физически, т.е. они хранятся в системной базе данных tempdb. Имена временных таблиц начинаются с префикса #, например #table_name.


Временная таблица принадлежит создавшему ее сеансу, и видима только этому сеансу. Временная таблица удаляется по завершению создавшего ее сеанса. (Также локальная временная таблица, определенная в хранимой процедуре, удаляется по завершению выполнения этой процедуры.)

Глобальные временные таблицы видимы любому пользователю и любому соединению и удаляются после отключения от сервера базы данных всех обращающихся к ним пользователей. В отличие от локальных временных таблиц имена глобальных временных таблиц начинаются с префикса ##. В примере ниже показано создание временной таблицы, называющейся project_temp, используя две разные инструкции языка Transact-SQL:

Два этих подхода похожи в том, что в обоих создается локальная временная таблица #project_temp. При этом таблица, созданная инструкцией CREATE TABLE, остается пустой, а созданная инструкцией SELECT заполняется данными из таблицы Project.

Подзапросы в основных командах SQL

Последнее обновление: 20.07.2017

Подзапросы в SELECT

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

  1. Использовать в условии в выражении WHERE

  2. Использовать в условии в выражении HAVING

  3. Использовать в качестве таблицы для выборки в выражении FROM

  4. Использовать в качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:

SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)

Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: .

Или выберем всех покупателей из таблицы Customers, у которых нет заказов в таблице Orders:

SELECT * FROM CUSTOMERS
WHERE Id NOT IN (SELECT CustomerId FROM Orders)

Хотя в данном случае подзапросы прекрасно справляются со своей задачей, стоит отметить, что это не самый эффективный способ для извлечения данных из других таблиц, так как в рамках T-SQL для сведения данных из разных таблиц можно использовать оператор JOIN, который рассматривается в следующей теме.

Получение набора значений

При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOME или ANY.

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

SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')

Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.

Допустим, если подзапрос возвращает значения vl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:

WHERE Price < val1 AND Price < val2 AND Price < val3

В тоже время подобный запрос гораздо проще переписать другим образом:

SELECT * FROM Products
WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')

При применении ключевых слов ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом. По действию оба этих оператора аналогичны, поэтому можно применять любое из них. Например, в следующем случае получим товары, которые стоят меньше самого дорого товара компании Apple:

SELECT * FROM Products
WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')

И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:

SELECT * FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')

Подзапрос как спецификация столбца

Результат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:

SELECT *, 
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product 
FROM Orders

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:

INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES
( 
	(SELECT Id FROM Products WHERE ProductName='Galaxy S8'), 
	(SELECT Id FROM Customers WHERE FirstName='Tom'),
	'2017-07-11',  
	2, 
	(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)

Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

  1. В качестве устанавливаемого значения после оператора SET

  2. Как часть условия в выражении WHERE

Так, увеличим количество купленных товаров на 2 в тех заказах, где покупатель Тоm:

UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')

Или установим для заказа цену товара, полученную в результате подзапроса:

UPDATE Orders
SET Price = (SELECT Price FROM Products WHERE Id=Orders.ProductId) + 2000
WHERE Id=1

Подзапросы в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8, которые сделал Bob:

DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8')
AND CustomerId=(SELECT Id FROM Customers WHERE FirstName='Bob')

НазадВперед

Oracle ключи, индексы, уникальные ограничения

Primary Keys Первичные ключи
Foreign Keys Внешние ключи
Foreign Keys with cascade delete Внешние ключи с каскадным удалением
Foreign Keys with set null on delete Внешние ключи с «set null on delete»
Drop a foreign key Удаление внешнего ключа
Disable a foreign key Выключение внешнего ключа
Enable a foreign key Включение внешнего ключа
Indexes Индексы
unique constraints Уникальные ограничения
check constraints Проверка ограничений
IF-THEN-ELSE CASE GOTO
  • LOOP оператор
  • FOR LOOP
  • CURSOR FOR LOOP
  • WHILE LOOP
  • EXIT оператор
Comparison operators Операторы сравнения Oracle PL/SQL такие как =, !=, , >, >=,
AND логический оператор и
OR логический оператор или
AND и OR логический операторы и и или
NOT логический оператор не
LIKE сопоставляет данные с шаблоном
REGEXP_LIKE выполняет регулярные выражения
IN определяет, соответствует ли значение или список значений выражению в указанном наборе.
IS NULL проверка значения NULL
IS NOT NULL проверка на значения NOT NULL
BETWEEN используется для получения значений в пределах диапазона
Record Запись
%ROWTYPE Модификатор записи
%TYPE Модификатор столбца
Associative Arrays Ассоциативные массивы
Varrays Массивы переменной длины
Nested Tables Вложенные таблицы

Методы коллекций Oracle PL/SQL

Метод коллекции Тип Описание
DELETE Процедура Удаляет элементы из коллекции.
TRIM Процедура Удаляет элементы из конца varray или Nested Tables.
EXTEND Процедура Добавляет элементы в конец varray или Nested Tables.
EXISTS Функция Возвращает TRUE тогда и только тогда, когда существует определенный элемент varray или Nested Tables.
FIRST и LAST Функции FIRST возвращает первый, LAST последний индекс в коллекции.
COUNT Функция Возвращает количество элементов в коллекции.
LIMIT Функция Возвращает максимальное количество элементов, которые может иметь коллекция.
PRIOR и NEXT Функции PRIOR возвращает индекс, который предшествует указанному индексу, NEXT возвращает индекс, который следует за указанным индексом.

Oracle PL/SQL операции над коллекциями

MULTISET UNION Возвращает объединение двух коллекций
MULTISET UNION DISTINCT Возвращает объединение двух коллекций с дистинктом (убирает дубли)
MULTISET INTERSECT Возвращает пересечение двух коллекций
MULTISET INTERSECT DISTINCT Возвращает пересечение двух коллекций с дистинктом (убирает дубли)
SET Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)
MULTISET EXCEPT Возвращает разницу (усечение) двух коллекций
MULTISET EXCEPT DISTINCT Возвращает разницу (усечение) двух коллекций с дистинктом (убирает дубли)

Сравнение данных за две даты

Хотя данная статистика из рода задач довольно редко встречаемых, но все-таки необходимость в ее получении иногда существует. И получить такую статистику ничуть не сложнее других.

Работать мы будем с двумя таблицами, структура которых представлена ниже:

Структура таблицы products

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ShopID` int(11) NOT NULL,
  `Name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf-8 AUTO_INCREMENT=10 ;

Структура таблицы statistics

CREATE TABLE IF NOT EXISTS `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductID` bigint(20) NOT NULL,
  `Orders` int(11) NOT NULL,
  `Date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`),
  KEY `ProductID` (`ProductID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf-8 AUTO_INCREMENT=20 ;

Дело в том, что стандарт языка SQL допускает использование вложенных запросов везде, где разрешается использование ссылок на таблицы. Здесь вместо явно указанных таблиц, благодаря использованию псевдонимов, будут применяться результирующие таблицы вложенных запросов с имеющейся связью один – к – одному. Результатом каждой результирующей таблицы будут данные о количестве произведенных заказов некоего товара за определенную дату, полученные путем выполнения запроса на выборку данных из таблицы statistics по требуемым критериям. Иными словами мы свяжем таблицу statistics саму с собой. Пример запроса:

SELECT stat1.Name, stat1.Orders, stat1.Date, stat2.Orders, stat2.Date FROM 
(SELECT statistics.ProductID, products.Name, statistics.Orders, statistics.Date 
FROM products JOIN statistics ON products.id = statistics.ProductID WHERE 
DATE(statistics.date) = '2014-09-04') AS stat1 JOIN (SELECT statistics.ProductID, 
statistics.Orders, statistics.Date FROM statistics WHERE DATE(statistics.date) = 
'2014-09-12') AS stat2 ON stat1.ProductID = stat2.ProductID

В итоге имеем такой результат:

+------------------------+----------+------------+----------+------------+
| Name                   | Orders1  | Date1      | Orders2  | Date2      |
+------------------------+----------+------------+----------+------------+
| Процессоры Pentium II  |        1 | 2014-09-04 |        1 | 2014-09-12 |
| Процессоры Pentium III |        1 | 2014-09-04 |       10 | 2014-09-12 |
| Оптическая мышь Atech  |       10 | 2014-09-04 |        3 | 2014-09-12 |
| DVD-R                  |        2 | 2014-09-04 |        5 | 2014-09-12 |
| DVD-RW                 |       22 | 2014-09-04 |       18 | 2014-09-12 |
| Клавиатура MS 101      |        5 | 2014-09-04 |        1 | 2014-09-12 |
| SDRAM II               |       26 | 2014-09-04 |       12 | 2014-09-12 |
| Flash RAM 8Gb          |        8 | 2014-09-04 |        7 | 2014-09-12 |
| Flash RAM 4Gb          |       18 | 2014-09-04 |       30 | 2014-09-12 |
+------------------------+----------+------------+----------+------------+

Oracle PL/SQL программирование

Data Types Типы данных
Literals Такие как text, integer, number и datetime.
Declaring Variables Объявление переменных, констант
Comments within SQL Комментарии SQL используются для комментирования кода программ
Procedures Создание и удаление процедур
Functions Создание и удаление функций
Triggers Создание, удаление, включение и отключение триггеров
Dynamic SQL Динамический SQL
Packages Пакеты и тела пакетов — создание, удаление
Sequences Последовательности
Synonyms Синонимы
Aliases Псевдонимы (алиасы) для таблиц или столбцов
WITH Оператор WITH
Объявление курсора (Declare a Cursor)
Операторы
OPEN FETCH CLOSE
Атрибуты курсора (Cursor Attributes: %FOUND, %NOTFOUND, %ISOPEN, ROWCOUNT)
SELECT FOR UPDATE оператор
WHERE CURRENT OF оператор

Ключевые слова SQL SOME | ANY и ALL

Пример: Найти поставщиков компьютеров, у которых номера отсутствуют в продаже (т.е. отсутствуют в таблице )

Решение: 

Исходные данные таблиц:

Таблица product:
Таблица pc:

Решение:

1
2
3
4
5
6
7
SELECT DISTINCT Производитель
FROM product
WHERE Тип =  "Компьютер"
AND NOT Номер = ANY(
 SELECT Номер
 FROM pc
)

Результат:

В примере предикат вернет в том случае значение TRUE, когда Номер из основного запроса найдется в списке Номеров таблицы (возвращаемом подзапросом). Кроме того, используется . Результирующий набор будет состоять из одного столбца — Производитель. Чтобы один производитель не выводился несколько раз, введено служебное слово . Теперь рассмотрим использование ключевого слова ALL:

Пример: Найти номера и цены ноутбуков, стоимость которых превышает стоимость любого компьютера

Решение: 

1
2
3
4
5
6
7
SELECT DISTINCT Номер, Цена
FROM notebook
WHERE Цена > 
ALL (
  SELECT цена
  FROM pc
)

Результат:

Важно: Стоит заметить, что в общем случае запрос с возвращает множество значений. Поэтому использование подзапроса в предложении без операторов , , и , которые дают булево значение (логическое), может привести к ошибке времени выполнения запроса

Пример: Найти номера и цены компьютеров, стоимость которых превышает минимальную стоимость ноутбуков. Решение: 

Решение: 

1
2
3
4
5
SELECT DISTINCT  `Номер` ,  `Цена` 
FROM  `pc` 
WHERE  `Цена` > ( 
  SELECT MIN(`Цена`) 
  FROM notebook)

Этот запрос корректен по той причине, что скалярное выражение сравнивается с подзапросом, который возвращает единственное значение

Операции EXISTS и NOT EXISTS

Операция позволяет выполнять проверку на предмет существования строк во внутреннем запросе или подзапросе при использовании подзапросов, а операция — на предмет не существования строк во внутреннем запросе. В приведенном ниже операторе операция вернет в случае извлечения подзапросом хотя бы одной строки: 

SQL> SELECT department_id
FROM departments d
WHERE EXISTS
(SELECT * FROM employees e
WHERE d.department_id
= e.department_id);

Вас заинтересует / Intresting for you:

Назначение языка SQL и необход… 533 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:40:06


Операции SQL в базе данных Ora… 2737 просмотров Antoni Wed, 11 Apr 2018, 12:22:28

Встроенные методы коллекций PL… 4370 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Управление приложениями PL/SQL… 2157 просмотров Rasen Fasenger Thu, 16 Jul 2020, 06:20:48

Author: Александров Попков

Другие статьи автора:

Вставка (INSERT)

Синтаксис 1:

> INSERT INTO <table> (<fields>) VALUES (<values>)

Синтаксис 2:

> INSERT INTO <table> VALUES (<values>)

* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую. * первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.

1. Вставка нескольких строк одним запросом:

> INSERT INTO cities (`name`, `country`) VALUES (‘Москва’, ‘Россия’), (‘Париж’, ‘Франция’), (‘Фунафути’ ,’Тувалу’);

* в данном примере мы одним SQL-запросом добавим 3 записи.

2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):

Синтаксис при копировании строк из одной таблицы в другую выглядит так:

> INSERT INTO <table1> SELECT * FROM <table2> WHERE <условие для select>;

* где table1 — куда копируем; table2 — откуда копируем.

а) скопировать все без разбора:

> INSERT INTO cities-new SELECT * FROM cities;

* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.

б) скопировать определенные столбцы строк с условием:

> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;

* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.

в) копирование с обновлением повторяющихся ключей.

Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry ‘xxx’ for key ‘PRIMARY’». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:

> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);


* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.

Оператор FROM

Подзапрос также можно найти в операторе FROM. Они называются встроенными представлениями (view). Например:

PgSQL

SELECT products.product_name, subquery1.category_name FROM products, (SELECT categories.category_id, categories.category_name, COUNT(category_id) AS total FROM categories GROUP BY categories.category_id, categories.category_name) subquery1 WHERE subquery1.category_id = products.category_id;

1 2 3 4 5 6

SELECTproducts.product_name,subquery1.category_name

FROMproducts,

(SELECTcategories.category_id,categories.category_name,COUNT(category_id)AStotal

FROMcategories

GROUP BYcategories.category_id,categories.category_name)subquery1

WHEREsubquery1.category_id=products.category_id;

В этом примере мы создали подзапрос в операторе FROM следующим образом:

PgSQL

(SELECT categories.category_id, categories.category_name, COUNT(category_id) AS total FROM categories GROUP BY categories.category_id, categories.category_name) subquery1

1 2 3

(SELECTcategories.category_id,categories.category_name,COUNT(category_id)AStotal

FROMcategories

GROUP BYcategories.category_id,categories.category_name)subquery1

Этот подзапрос имеет псевдоним с именем subquery1. Это будет имя, используемое для ссылки на этот подзапрос или любое из его полей.

Подзапросы и функция EXISTS

Функция EXISTS принимает вложенный запрос в качестве аргумента и возвращает значение false, если вложенный запрос не возвращает строк и значение true в противном случае. Рассмотрим работу этой функции на нескольких примерах, начиная со следующего примера:

В этом примере происходит выборка фамилий всех сотрудников, работающих над проектом p1. Вложенный запрос функции EXISTS почти всегда зависит от переменной с внешнего запроса. Поэтому функция EXISTS обычно определяет связанный подзапрос.

Давайте проследим, как Database Engine может обрабатывать запрос в этом примере. Сначала внешний запрос рассматривает первую строку таблицы Employee (сотрудник Фролов). Далее функция EXISTS определяет, есть ли в таблице Works_on строки, чьи номера сотрудников совпадают с номером сотрудника в текущей строке во внешнем запросе и чей ProjectNumber равен p1. Поскольку сотрудник Фролов не работает над проектом p1, вложенный запрос возвращает пустой набор, вследствие чего функция EXISTS возвращает значение false. Таким образом, сотрудник Фролов не включается в конечный результирующий набор. Этому процессу подвергаются все строки таблицы Employee, после чего выводится конечный результирующий набор.

В примере ниже показано использование функции NOT EXISTS:

В этом примере происходит выборка фамилий сотрудников, чей отдел не расположен в Санкт-Петербурге.

Список выбора инструкции SELECT во внешнем запросе с функцией EXISTS не обязательно должен быть в форме SELECT *, как в предыдущем примере. Можно использовать альтернативную форму SELECT colum_list, где column_list представляет список из одного или нескольких столбцов таблицы. Обе формы равнозначны, потому что функция EXISTS только проверяет на наличие (или отсутствие) строк в результирующем наборе. По этой причине в данном случае правильнее использовать форму SELECT *.


С этим читают