Лабораторная работа №4.
Цель работы: Изучить базовые операции по работе с массивами. Изучить синтаксис команд. Приобрести навыки работы с подзапросами в PostgreSQL.
Задание. Общая часть: Ознакомиться с теоретическими сведениями о создании массивов. Создать таблицу с полем-массивом, таблицу с полем, содержащим многомерный массив. Выполнить вставку значений в созданные таблицы (минимум по 6 записей в каждой). Выполнить выборку из созданных таблиц (в том числе продемонстрировать предотвращение выборки NULL в массивах). Осуществить выборку с использованием среза. Продемонстрировать работу функции array_dims(). Выполнить обновление данных в созданных таблицах. Осуществить модификацию среза массива, отдельного элемента массива.
Вариант 1: Найти всех нападающих с правым хватом клюшки и в возрасте больше, чем средний возраст нападающих с левым хватом клюшки. Найти всех защитников, набравших более 5 очков и с ростом больше, чем средний рост защитников в возрасте до 25 лет. Найти всех полузащитников в возрасте от 20 до 30 лет и с окладом больше, чем средний оклад нападающих из России.
Вариант 2: Найти все бакалейные товары и с ценой больше, чем средняя цена бакалейных товаров, поставляемых из Москвы. Найти все молочные продукты, поставляемых из Омска и Томска и с ценой больше, чем средняя цена товаров, поставленных за последние три месяца. Найти все товары, поставленные за последние полгода, и с ценой больше, чем средняя цена товаров, поставляемых из России и Индии.
Вариант 3: Найти всех водителей в возрасте до 30 лет и имеющих заработок больше, чем средний заработок водителей, получивших права от 18 до 20 лет. Найти всех водителей, выполнившие заказы за последние три месяца на сумму более 20000 рублей и в возрасте больше, чем средний возраст водителей, не состоящих в браке. Найти всех водителей в возрасте от 30 до 50 лет, родившихся в Новосибирске, и имеющих заработок больше, чем средний заработок водителей, приехавших из Омска и Томска.
Вариант 4: Найти все повести и романы, в которых от 250 до 500 страниц, и с ценой больше, чем средняя цена книг заданного издательства. Найти всю литературу автора Иванова, выпущенную в Москве и Новосибирске, и с ценой выше, чем средняя цена произведений жанра «детектив», выпущенных за последние полгода, автором Ивановым. Найти все поэтические произведения заданного издательства и с ценой больше, чем средняя цена книг, выпущенных в Чехии и Словакии.
Вариант 5: Найти все автомобили с правым рулем и автоматической коробкой передач, чья стоимость больше, чем средняя стоимость машин малолитражек из Японии. Найти все седаны и минивены, выпущенные в Германии и США, и с ценой выше, чем средняя стоимость машин Японского производства, выпущенных за последние три года. Найти все машины с левым рулем, выпущенные в Японии и Франции, и с ценой больше, чем средняя цена машин с механической коробкой передач, выпущенных в России.
Вариант 6: Найти все фрукты с датой поставки за последние полгода и с ценой больше, чем средняя цена фруктов поставщика Иванова. Найти все овощи, вес которых на складе от 150 до 350 кг, и чья стоимость больше, чем средняя стоимость овощей, поступивших за последние две недели. Найти все арбузы из Узбекистана и Таджикистана и сценой больше, чем средняя цена овощей из Омска и Томска.
Вариант 7: Найти все журналы и еженедельники, заказ которых составляет в пределах от 200 до 500 экземпляров, и с ценой больше, чем средняя цена продукции, проданной за наличный расчет. Найти всю продукцию, произведенную для клиентов из Омска и Томска, оплаченную безналичным путем и стоимость которой больше, чем средняя стоимость продукции, произведенной за последние полгода. Найти все газеты и бюллетени заданного издательства и имеющих цену больше, чем средняя цена журналов, выпущенных в Омске и Томске.
Вариант 8: Найти всех студентов, проживающих во 2 и 6 общежитии, обучающихся в 4 и 7 корпусах и получающих стипендию больше, чем средняя стипендия студентов факультетов ФЭН и АВТФ. Найти всех студентов, обучающихся на 2 и 3 курсах факультетов ФЭН и ФБ, и получающих стипендию больше, чем средняя стипендия студентов из Омска и Томска. Найти всех студентов, обучающихся в аудиториях с 301 по 320 в 7 корпусе и получающих стипендию больше, чем средняя стипендия студентов 2 и 4 курсов факультетов РЭФ и ФТФ.
Вариант 9: Найти всех мастеров в возрасте от 22 до 30 лет, которые занимаются ремонтом кофейников и микроволновок и выполнивших заказы на сумму больше, чем средняя стоимость заказов мастеров в возрасте старше 45 лет. Найти мастеров, родившихся в Омске и Томске, со стажем работы от 10 до 20 лет, в возрасте старше 50 лет, и выполнивших заказы на сумму больше, чем средняя стоимость заказов, выполненных мастерами по ремонту холодильников за последние три месяца. Найти всех мастеров со стажем работы до 2 лет, которые занимаются ремонтом телевизоров и выполнили заказов на сумму больше, чем средняя стоимость заказов, выполненных мастерами по ремонту соковыжималок со стажем работы от 2 до 5 лет.
Теоретические сведения. Массивы.
Поля данных PostgreSQL вместо отдельных величин могут содержать конструкции, называемые массивами.
Чтобы создать простейшее поле-массив, включите в команду CREATE TABLE или ALTER TABLE пару квадратных скобок после имени поля. Квадратные скобки показывают, что вместо одного значения в поле может храниться массив указанного типа.
Например, команда для создания поля single_array типа type выглядит так:
single_array type[] -- Одномерный массив
Дополнительные квадратные скобки определяют многомерные массивы, то есть «массивы массивов». Пример:
multi_array type[][] -- Многомерный массив
Теоретически в квадратных скобках можно указать целое число, чтобы созданный массив имел фиксированный размер (то есть всегда состоял из п элементов по указанному измерению и не более).
Пример 1. Создание таблицы с полем-массивом
CREATE TABLE book1 (employee_id integer, books text[])
Таблица позволяет хранить в одном поле неограниченное количество названий книг. Многомерные массивы создаются аналогичным образом, просто за первой парой квадратных скобок добавляются дополнительные пары.
Пример 2. Создание таблицы с полем, содержащим многомерный массив:
CREATE TABLE f_book2 (employee_id integer, authors_and_titles text[][]);
В примере создается таблица f_book2 с целочисленным полем employee_id и многомерным массивом author_and_titles. Фактически создается массив текстовых массивов.
Вставка значений в поля-массивы.
В PostgreSQL предусмотрен специальный синтаксис вставки нескольких значений в одно поле. Этот синтаксис основан на определении массивов-констант. Массив-константа состоит из фигурных скобок, апострофов и запятых, заключенных в апострофы. Кавычки нужны только при работе с массивами строк. Таким образом, обобщенная форма массива-константы выглядит так:
'{ "текст" [,...] }' -- массив строк
'{ число [,...]}' -- числовой массив
Поле может определяться также как массив произвольного типа (включая типы boolean, date и time). Как правило, если для описания величины в скалярном контексте должны использоваться апострофы, в контексте массива эта величина заключается в кавычки.
Пример 3. Вставка с использованием массивов-констант
INSERT INTO book1 VALUES (1, '{"The Student\'s Guide"}');
INSERT INTO book1 VALUES (2, '{"The Student1", "Student1, Student2"}');
В примере в таблицу book1 вставляются две записи. Первая команда создает массив с одним элементом для работника с кодом 1, а вторая запись создает массив с двумя элементами для работника с кодом 2. В обеих командах INSERT используются массивы-константы.
Даже при вставке одного элемента массив заключается в фигурные скобки. Кроме того, апостроф в названии книги (первая команда INSERT) экранируется символом \, хотя и находится внутри кавычек. Это связано с тем, что массив-константа сначала обрабатывается как одна длинная строка, а затем интерпретируется как массив по контексту целевого поля.
При вставке значений в многомерный массив все подмассивы заключаются в отдельные фигурные скобки и разделяются запятыми.
Пример 4. Вставка данных в многомерный массив
INSERT INTO f_book2 VALUES (1, '{{"Avtor1", "Kniga1"}, {"Avtor2", "Kniga2"}, {"Avtor3", "Kniga3"}}');
Многомерный массив в примере содержит три текстовых массива, каждый из которых состоит из двух элементов. Первые элементы каждого массива обозначают авторов, а вторые элементы обозначают названия книг, написанных этими авторами.
Выборка из полей-массивов
При выборке из поля-массива весь массив возвращается в формате константы. На практике чаще требуется получить некоторое подмножество элементов. Для решения этой задачи необходимо познакомиться с такими понятиями, как индексы элементов и срезы массивов.
Индексы элементов
К отдельным элементам можно обращаться при помощи индексов — целых чисел, заключенных в скобки и описывающих позицию искомого элемента в массиве. В PostgreSQL индексация в массивах начинается с 1, а не с 0.
Пример 5. Выборка отдельного элемента массива
SELECT books[1] FROM book1;
При указании индекса несуществующего элемента массива выборка возвращает NULL. Обычно для обработки таких ситуаций используется конструкция IS NOT NULL.
Пример 6. Предотвращение выборки NULL в массивах
SELECT books[2] FROM book1 WHERE books[2] IS NOT NULL;
Запрос возвращает только название, а запись с NULL исключается из выборки в результате использования секции WHERE с проверкой условия NOT NULL.
При выборке из многомерного массива за исходным индексом перечисляются дополнительные индексы.
Пример 7. Выборка из многомерного массива
SELECT authors_and_titles[1][1] AS author, authors_and_titles[1][2] AS title FROM f_book2;
В примере 7 из таблицы f_book2, созданной в примере 4, выбираются два элемента — имя автора и название книги.
Срезы
В PostgreSQL также поддерживается возможность создания срезов при выборке из массива. Срез аналогичен обычному обращению к элементам по индексу, но он описывает интервал значений. Срез задается парой целочисленных индексов, разделенных двоеточием и заключенных в квадратные скобки. Например, конструкция [2:5] описывает второй, третий, четвертый и пятый элемент заданного массива. Результат среза возвращается в виде константы-массива, которая фактически описывает подмножество элементов исходного массива.
Пример 8. Выборка с использованием среза
SELECT books[l:2] FROM book1;
В примере 8 выбираются первые два элемента массива books в записях таблицы book1.
Определение количества элементов
Чтобы узнать количество значений, хранящихся в массиве, следует воспользоваться функцией array_dims(). В качестве параметра функции передается идентификатор — имя поля-массива, для которого вызывается функция. Результат возвращается в виде строки, содержащей описание массива в синтаксисе среза.
Пример 9. Функция array_dims()
SELECT array_dims(books) FROM book1;
В примере 9 приведен вызов функции array_dims() для поля books таблицы book1.
Обновление данных в полях-массивах
Существует три варианта модификации данных в полях-массивах.
- Полная модификация. Все содержимое массива заменяется новыми данными, заданными в виде массива-константы.
- Модификация среза. Срез (то есть интервальное подмножество элементов) заменяется новыми данными, заданными в виде массива-константы. Количество элементов в константе должно соответствовать количеству элементов в обновляемом срезе.
- Модификация элемента. Отдельный элемент массива заменяется новой константой, относящейся к базовому типу данных массива. Элемент задается индексом.
Пример 10. Полная модификация массива
UPDATE book1 SET books='{"The Student\'s Guide", "The Student4"}' WHERE employee_id=1;
Команда UPDATE, приведенная в примере 10, заменяет все текущее содержимое массива. Этот способ подходит и для модификации среза массива. Для этого в конец идентификатора поля добавляется определение среза, например, books[l:3] означает первый, второй и третий элементы массива. На практике чаще возникает задача замены не всего массива и не среза, а отдельных элементов. При обновлении отдельного элемента к идентификатору поля присоединяется индекс, определяющий конкретный обновляемый элемент.