SQL урок 4. операторы sql Union, Exists; строковые функции

SQL запрос Union (объединение)

Над множеством можно выполнять операции объединения, разности и декартова произведения. Те же операции можно использовать и в sql запросах (выполнять операции с запросами).

Для объединения нескольких запросов используется служебное слово UNION.
Синтаксис:

<запрос 1> UNION [ALL] <запрос 2>

<запрос 1> UNION [ALL] <запрос 2>

SQL запрос Union служит для объединения выходных строк каждого запроса в один результирующий набор.

Если используется параметр ALL, то сохраняются все дубликаты выходных строк. Если параметр отсутствует, то в результирующем наборе остаются только уникальные строки.

Объединять вместе можно любое число запросов.

Использование оператора UNION требует выполнения нескольких условий:

1. количество выходных столбцов каждого из запросов должно быть одинаковым;
2. выходные столбцы каждого из запросов должны быть сравнимы между собой по типам данных (в порядке их очередности);
3. в итоговом наборе используются имена столбцов, заданные в первом запросе;
4. ORDER BY может быть использовано только в конце составного запроса, так как оно применяетя к результату объединения.

✍ Решение:
 

1
2
3
4
5
6

SELECT  `Номер` ,  `Цена` 
  FROM  pc 
UNION 
SELECT  `Номер` ,  `Цена` 
  FROM notebook
ORDER BY  `Цена`

Рассмотрим более сложный пример с объединением inner join:

✍ Решение:
 

1
2
3
4
5
6
7
8

SELECT product.`Тип` , pc.`Номер` ,  `Цена` 
FROM pc
INNER JOIN product ON pc.`Номер` = product.`Номер` 
UNION 
SELECT product.`Тип` , notebook.`Номер` ,  `Цена` 
FROM notebook
INNER JOIN product ON notebook.`Номер` = product.`Номер` 
ORDER BY  `Цена`

SQL Предикат существования EXISTS

В языке SQL есть средства для выполнения операций пересечения и разности запросов — предложение INTERSECT (пересечение) и предложение EXCEPT (разность). Эти предложения работают подобно тому, как работает UNION: в результирующий набор попадают только те строки, которые присутствуют в обоих запросах — INTERSECT, или только те строки первого запроса, которые отсутствуют во втором — EXCEPT. Но беда в том, что многие СУБД не поддерживают эти предложения. Но выход есть — использование предиката EXISTS.

Предикат EXISTS принимает значение TRUE (истина), если подзапрос возвращает хоть какое-нибудь количество строк, иначе EXISTS принимает значение FALSE. Существует также предикат NOT EXISTS, который действует противоположным образом.

Обычно EXISTS используется в зависимых подзапросах (например, IN).

[NOT]EXISTS(табличный подзапрос)

[NOT]EXISTS(табличный подзапрос)

✍ Решение:
 

1
2
3
4
5
6
7
8
9

SELECT DISTINCT Производитель
FROM product AS pc_product
WHERE Тип =  "Компьютер"
AND EXISTS (
  SELECT Производитель
  FROM product
  WHERE Тип =  "Ноутбук"
  AND Производитель = pc_product.Производитель
)

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

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

Синтаксис:

<выражение><оператор сравнения>SOME | ANY (<подзапрос>)

<выражение><оператор сравнения>SOME | ANY (<подзапрос>)

Если для какого-нибудь значения X, получаемого из подзапроса, результат операции «<выражение><оператор сравнения>» возвращает TRUE, то предикат ANY также равняется TRUE.

✍ Решение:
 

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

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

Решение:

1 2 3 4 5 6 7

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

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

Результат:

В примере предикат Номер = ANY(SELECT Номер FROM pc) вернет в том случае значение TRUE, когда Номер из основного запроса найдется в списке Номеров таблицы pc (возвращаемом подзапросом). Кроме того, используется NOT. Результирующий набор будет состоять из одного столбца — Производитель. Чтобы один производитель не выводился несколько раз, введено служебное слово DISTINCT.
Теперь рассмотрим использование ключевого слова 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)

Функции работы со строками в SQL

Функция LEFT вырезает слева из строки заданное вторым аргументом число символов:

LEFT (<строка>,<число>)

LEFT (<строка>,<число>)

Функция RIGHT возвращает заданное число символов справа из строкового выражения:

RIGHT(<строка>,<число>)

RIGHT(<строка>,<число>)

✍ Решение:
 

1
2

SELECT DISTINCT LEFT(`Производитель` , 1) 
FROM  `product`

✍ Решение:
 

1
2
3

SELECT  `Производитель` 
FROM  `product` 
WHERE LEFT(  `Производитель` , 1 ) = RIGHT(  `Производитель` , 1 )

Функция SQL Replace

Синтаксис:

REPLACE(<строка1>,<строка2>,<строка3>)

Функция заменяет в строке1 все вхождения строки2 на строку3. В первую очередь данная функция полезна в операторах обновления таблиц (Update).

✍ Решение:
 

1
2

SELECT  `name` , REPLACE(  `name` ,  "а",  "аа" ) 
FROM  `teachers`

Другие строковые функции SQL

LTRIM (<строковое выражение>)

LTRIM (<строковое выражение>)

отсекает пробелы в начале строки

RTRIM (<строковое выражение>)

RTRIM (<строковое выражение>)

отсекает пробелы в конце строки

LOWER (<строковое выражение>)

LOWER (<строковое выражение>)

преобразует все символы строки к нижнему регистру

UPPER (<строковое выражение>)

UPPER (<строковое выражение>)

преобразует все символы строки к верхнему регистру

CAST(<числовое поле> AS DECIMAL)

CAST(<числовое поле> as decimal)

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

SPACE (<число пробелов>)

SPACE (<число пробелов>)

для добавления указанного количества пробелов

CONCAT (<строка1>, <строка2>, <строка3>...)

CONCAT (<строка1>, <строка2>, <строка3>...)

слияние строк

✍ Решение:
 

1
2

SELECT CONCAT(`name`, SPACE( 6 ), CAST(`zarplata` AS DECIMAL ) ) AS  "Сведения"
FROM  `teachers`