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

На уроке будет рассмотрена тема использования операций объединения, пересечения и разности запросов. Разобраны примеры того, как используется SQL запрос Union, Exists, а также использование ключевых слов SOME, ANY и All. Рассмотрены строковые функции

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

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

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

<запрос 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  `Цена`

Результат:
sql запрос union

Рассмотрим более сложный пример с объединением 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 запрос union пример

SQL Union 1. Найти производителя, номер и цену всех ноутбуков и принтеров
SQL Union 2. Найти номера и цены всех продуктов, выпущенных производителем Россия

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

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

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

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

[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 exists

SQL Exists 1. Найти тех производителей компьютеров, которые не производят принтеров

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

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

Синтаксис:

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

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

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

✍ Решение:
 

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

Таблица product: sql some таблица product
Таблица pc: sql some таблица pc

Решение:

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

Результат:
sql some пример

В примере предикат Номер = 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
)

Результат:
sql all пример

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

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

✍ Решение:
 

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

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

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

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

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

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

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

Пример: Вывести первые буквы из названий всех производителей

✍ Решение:
 

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

Результат:
sql left пример строковых функций

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

✍ Решение:
 

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

Результат:
sql left right пример

SQL Left 1. БД Institute. Вывести первые три буквы фамилий учителей

Функция SQL Replace

Синтаксис:

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

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

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

✍ Решение:
 

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

Результат:
sql replace пример

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример: необходимо вывести фамилии учителей, затем 6 пробелов, а потом заработную плату (всё одним полем)

✍ Решение:
 

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

Результат:
строковые функции sql пример

Поделитесь уроком с коллегами и друзьями:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*
*


Вставить формулу как
Блок
Строка
Дополнительные настройки
Цвет формулы
Цвет текста
#333333
Используйте LaTeX для набора формулы
Предпросмотр
\({}\)
Формула не набрана
Вставить