Функции и операторы для работы со строками и текстом. Объединение таблиц

Учебные вопросы
Используемая БД
Функции и операторы для работы со строками и текстом

Используемая база данных
ИСПОЛЬЗУЕТСЯ ДЛЯ ЛЕКЦИЙ 8-12

https://apex.oracle.com/pls/apex/

Структура БД
Первая таблица MAN содержит сведения о людях, которые приобрели машины.
Колонки таблицы MAN:
• PHONEnum – уникальный телефонный номер человека, первичный ключ для таблицы
MAN, содержит текстовые данные;
• CITYCode – код города, вторичный ключ для связи с таблицей CITY;
• FirstName – имя человека (текстовые данные);
• LAStName – фамилия человека (текстовые данные);
• YearOld – возраст человека (числовые данные).
Таблица CITY – справочник городов, состоит из трех колонок:
• CITYCODE – уникальный код города, ключевое поле для таблицы CITY (числовые данные);
• CITYNAME – наименование города (текстовые данные);
• PEOPLES – население города, количество человек, которые проживают в городе (числовые данные).
Таблица AUTO – сведения об автомобилях автосалона.
Колонки таблицы AUTO:
• REGnum – уникальный регистрационный номер автомобиля (содержит текстовые данные);
• PHONEnum – телефонный номер покупателя, вторичный ключ для связи с таблицей MAN;
• MARK – марка авто (текстовые данные);
• COLOR – цвет авто (текстовые данные);
• ReleASeDT – дата создания авто, дата/время (специальный тип данных).
Таблица AUTO1 является копией таблицы AUTO и имеет те же колонки, что и таблица AUTO, и достаточно похожие данные, эта таблица используется в нескольких учебных заданиях (так же, как CITY1, MAN1).

Функции и операторы для работы со строками и текстом

Теория и практика
Мы уже познакомились с оператором LIKE, позволяющим выбирать из базы данных
строки с текстом, соответствующие определенному шаблону.
В SQL ORACLE также есть множество полезных сервисных функций для модификации
строк, работы с подстроками, объединения строк.
Объединение строк – по-правильному конкатенация.
Для объединения строк в языке SQL диалекта ORACLE используется специальная синтаксическая конструкция ||.

INSTR– поиск позиции подстроки в строке.
INSTR (STR1, STR2, POSn, DIRECTION) – возвращает позицию STR2 в строке STR1,
где поиск осуществляется в позиции POSn
в направлении DIRECTION 1 – от начала строки, 0 – от окончания строки, то есть откуда
мы начинаем поиск – от начала строки или с конца строки.

Примеры
Найти первое вхождение буквы «а» в название городов в таблице CITY, вывести на экран
и вхождение символа «а».

Найти последнее вхождение буквы «а» в название городов в таблице CITY, вывести
на экран и номер последнего вхождения символа «а».

Length – длина строки в символах.
LENGTH (str1) возвращает длину строки
Str1 в символах.
Примеры
Длина строки «AAA»
– 10.

Вывести из таблицы MAN имя, фамилию, длину имени и фамилии в символах.

Выбор подстроки из строки SUBSTR
SUBSTR (STR1, POS, LEN) выбирает LEN символов в строке str1,
начиная с позиции POS.
STR1 – оригинальная строка.
POS – позиция, с которой начинается выделение.
NEWSUB – подстрока, на которую заменяем по умолчанию.
Примеры
– BCD.

Выбрать все имена из MAN, которые начинаются с «Ан».

Замена подстроки в строке REPLACE
REPLACE (SRCSTR, OLDSUB, NEWSUB) – функция, которая возвращает преобразованную строку SRCSTR, где подстрока OLDSUB из строки SRCSTR заменяется на подстроку
NEWSUB
SRCSTR – оригинальная строка.
OLDSUB – заменяемая подстрока.
NEWSUB – подстрока, на которую заменяем, по умолчанию NULL.

Заменить в имени в таблице MAN все буквы а на #.

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

1. Найти записи из таблицы МAN, начинающиеся на Ан, использовать SUBSTR.

2.Найти записи из таблицы MAN, где количество символов в фамилии человека
больше 10.

3.Вывести из таблицы MAN имя, фамилию, количество символов в фамилии, где количество символов в фамилии человека больше 5.

4. Заменить буквы «о» в слове «молоко» на @, вывести результат, использовать REPLACE и DUAL.

5. Подсчитать количество букв «о» в слове «молоко», вывести результат, использовать
REPLACE, DUAL и математику.

6. Найти первое вхождение буквы «о» в городе с кодом 1 из таблицы CITY.

7. Найти первое и последнее вхождения буквы «и» в городе с кодом 2 из таблицы CITY.

Математика и пустые значения в запросах. Случайность – RANDOM

Теория и практика
Для математических выражений используются следующие операции:
+ сложение,
– вычитание,
/ деление,
* умножение.
А также знакомые нам со школы функции:
sqrt – квадратный корень,
mod– остаток от деления,
trunc – округление до целого,
sim – синус
cos – косинус.
Все математические операции выполняются только для числовых значений, числовых колонок с типами NUMBER или производными от NUMBER.

Вывести из таблицы MAN имя, фамилию и возраст (FIRSTNAME, LASTNAME, YEAROLD) человека, разделенный на 10.

Вывести из таблицы MAN имя, фамилию и возраст человека (FIRSTNAME, LASTNAME, YEAROLD), умноженный на sin (1), округлить до целого.

Математика и пустые значения NULL
Если в математическом выражении используется пустое значение NULL, тогда значение любого математического выражения также будет NULL.
Например
– 10+NULL = NULL;
– 11*NULL+52+sIN (1) = NULL.
Эту особенность следует учитывать при построении запросов.

Генерация случайных чисел
SQL ORCALE диалекта также позволяет генерировать случайные значения, для этого
используется специальный встроенный пакет (набор функций и процедур) dbms_random.
Для генерации случайного числа используется специальная функция Value.
Функция VALUE возвращает случайное число, большее или равное 0 и меньшее 1,
с 38 цифрами справа от десятичной части (38 знаков после запятой). Кроме того, вы можете получить случайное число х, где х больше или равно LOW и менее HIGH.

Синтаксис
DBMS_RANDOM.VALUE RETURN NUMBER
Параметры:
LOW – наименьшее количество в диапазоне для генерации случайного числа. Номер,
который генерируется, может быть равен LOW;
HIGH – наибольшее число для генерации случайного числа. Номер, который генерируется, будет меньше, чем HIGH. Возвращаемое значение – NUMBER.

пример
-0,777585712081073.
– 11,3383710413575.
– 3,67901998206503.

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

1. Вывести из таблицы MAN имя, фамилию и квадратный корень из 133.

2. Вывести из таблицы MAN имя, фамилию и возраст человека, умноженный на cos (5).

3. Вывести из таблицы CITY записи (*), где популяция делится без остатка на 10 000.

4. Вывести из таблицы CITY название города, квадратный корень от популяции, умноженный на 10, где значение кода города делится нацело на 5.

Оператор IN

Теория и практика
Для удобной фильтрации выборки по списку значений в SQL существует специальный
оператор IN.
Он позволяет сравнить значение заданного поля со списком значений и выбирать данные
по результатам сравнения.
Синтаксис

Выбрать из таблицы MAN имена и фамилии людей (FIRSTNAME, LASTNAME), которым 22, 31, 34, 27 лет (YEAROLD).

Выбрать из таблицы CITY * города с кодами 3, 5, 7 (CITYCODE), где население (PEOPLES) больше 100 000 человек.

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

1. Выбрать из таблицы AUTO, где REGNUM – 111114, 111115, 111116.

2. Выбрать из таблицы MAN * людей с именами (FIRSTNAME) Андрей, Максим, Алиса.

3. Выбрать из таблицы CITY * города (CITYNAME) Москва, Владимир, Казань.

4. Выбрать из таблицы CITY * города с кодами (CITYCODE) 1, 3, 5, 7.

Объединение нескольких таблиц в запросе

Теория и практика
Давайте рассмотрим записи из таблицы города CITY, записи из таблицы MAN нашей
схемы.
Мы видим, что и в одной, и в другой таблице есть колонка «код города» (CITYCODE).
Если посмотреть значения этой колонки (CITYCODE) и в той, и в другой таблице, то мы
увидим, что числа, значения в этих колонках совпадают.
Например, в MAN есть записи с CITYCODE = 1 и в CITY есть записи CITYCODE = 1,
то есть эта колонка является колонкой связи для таблиц CITY и MAN. По этим значениям
мы можем выбрать данные из указанных таблиц, поэтому, используя эту колонку, мы можем извлечь данные из обоих таблиц.

Первый вариант синтаксиса
Запомните, как объединяются таблицы в нашей схеме:
AUTO – > MAN = PHONENUM
CITY – > MAN = CITYCODE

Правое и левое объединение таблиц

Теория и практика
Если внимательно присмотреться, то можно заметить, что в таблице CITY есть коды
городов, которых нет в таблице MAN.
Точно так же и в таблице MAN есть номера телефонов, которых нет в таблице AUTO.
А что если нам необходимо выбрать из таблицы CITY все записи, а из таблицы MAN
только те записи, которые совпадают с таблицей CITY по коду города (CITYCODE)?
Разумеется, в запросе, который объединяет обе эти таблицы.
Для этого в SQL ORACLE диалекта предусмотрен синтаксис правого и левого объединения таблиц, или RiGHT JOIN и LEFT JOIN.

Синтаксис LEFT JOIN
SELECT – перечень полей или * FROM – таблица, из которой мы извлекаем все записи;
LEFT JOIN – таблица, где мы извлекаем только совпадающие записи; on – условие объединения
ON (т1.код=т2.код).
Синтаксис RIGHT JOIN
SELECT перечень полей или * FROM – мы извлекаем только совпадающие записи;
RIGHT JOIN – таблица, из которой мы извлекаем все записи ON (т1.код=т2.код).
Итак, если мы используем правое объединение RIGHT JOIN, из правой таблицы от конструкции JOIN будут выбраны все записи, а из левой таблицы только совпадающие записи.
Если мы используем левое соединение LEFT JOIN, из левой таблицы от конструкции JOIN будут выбраны все записи, а из правой таблицы будут выбраны совпадающие записи.

Выбрать все записи из MAN и только совпадающие из AUTO.

Объединим MAN и CITY по колонке CITYCODE, выведем значения всех колонок
из этих таблиц

Объединим MAN и CITY по колонке CITYCODE, выведем наименование и население
(CITYNAME, PEOPLES) из таблицы CITY и имя и фамилию из таблицы MAN (FIRSTNAME,LASTNAME).

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