Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
Если вам потребуется восстановить все объекты базы данных, то вы всегда смо- жете воспользоваться файлом demo_small.sql и просто повторить процедуру развертывания учебной базы данных, которая описана в главе 2. Поэтому смело экспериментируйте с таблицами и представлениями. 14. Представления (views) могут быть обновляемыми. Это значит, что можно с по- мощью команд INSERT, UPDATE и DELETE, применяемых к представлению, вне- сти изменения в таблицу, лежащую в основе этого представления. Самостоятельно ознакомьтесь с этим вопросом с помощью документации (см. описание команды CREATE VIEW) и, создав простое представление над одной из таблиц базы данных «Авиаперевозки», выполните несколько команд с целью внесения изменений в эту таблицу. 15. Определение таблицы можно изменить с помощью команды ALTER TABLE. Ана- логичные команды существуют и для изменения представлений и материали- зованных представлений: ALTER VIEW и ALTER MATERIALIZED VIEW. Самосто- ятельно ознакомьтесь с их возможностями с помощью документации. 16. Как вы думаете, при изменении данных в таблицах, на основе которых скон- струировано материализованное представление, содержимое этого представ- ления тоже синхронно изменяется или нет? Если содержимое материализованного представления изменяется синхронно с базовыми таблицами, то продемонстрируйте это. Если же оно остается неиз- менным, то покажите, как его синхронизировать с базовыми таблицами. 17. Представления могут быть, условно говоря, вертикальными и горизонтальными. При создании вертикального представления в список его столбцов включается лишь часть столбцов базовой таблицы (таблиц). Например: CREATE VIEW airports_names AS SELECT airport_code, airport_name, city FROM airports; SELECT * FROM airports_names; В горизонтальное представление включаются не все строки базовой таблицы (таблиц), а производится их отбор с помощью фраз WHERE или HAVING. На- пример: 107 CREATE VIEW siberian_airports AS SELECT * FROM airports WHERE city = 'Новосибирск' OR city = 'Кемерово'; SELECT * FROM siberian_airports; Конечно, вполне возможен и смешанный вариант, когда ограничивается как список столбцов, так и множество строк при создании представления. Подумайте, какие представления было бы целесообразно создать для нашей базы данных «Авиаперевозки». Необходимо учесть наличие различных групп пользователей, например: пилоты, диспетчеры, пассажиры, кассиры. Создайте представления и проверьте их в работе. 18.* Предположим, что нам понадобилось иметь в базе данных сведения о техниче- ских характеристиках самолетов, эксплуатируемых в авиакомпании. Пусть это будут такие сведения, как число членов экипажа (пилоты), тип двигателей и их количество. Следовательно, необходимо добавить столбец в таблицу «Самоле- ты» (aircrafts). Дадим ему имя specifications, а в качестве типа данных выберем jsonb. Если впоследствии потребуется добавить и другие характеристики, то мы сможем это сделать, не модифицируя определение таблицы. ALTER TABLE aircrafts ADD COLUMN specifications jsonb; ALTER TABLE Добавим сведения для модели самолета Airbus A320-200: UPDATE aircrafts SET specifications = '{ "crew": 2, "engines": { "type": "IAE V2500", "num": 2 } }'::jsonb WHERE aircraft_code = '320'; UPDATE 1 Посмотрим, что получилось: SELECT model, specifications FROM aircrafts WHERE aircraft_code = '320'; model | specifications -----------------+------------------------------------------------ Airbus A320-200 | {"crew": 2, "engines": {"num": 2, "type": "IAE V2500"}} (1 строка) Можно посмотреть только сведения о двигателях: SELECT model, specifications->'engines' AS engines FROM aircrafts WHERE aircraft_code = '320'; 108 model | engines -----------------+--------------------------------- Airbus A320-200 | {"num": 2, "type": "IAE V2500"} (1 строка) Чтобы получить еще более детальные сведения, например, о типе двигателей, нужно учитывать, что созданный JSON-объект имеет сложную структуру: он со- держит вложенный JSON-объект. Поэтому нужно использовать оператор «#>» для указания пути доступа к ключу второго уровня. SELECT model, specifications #> '{ engines, type }' FROM aircrafts WHERE aircraft_code = '320'; model | ?column? -----------------+------------- Airbus A320-200 | "IAE V2500" (1 строка) Задание. Подумайте, какие еще таблицы было бы целесообразно дополнить столбцами типа json/jsonb. Вспомните, что, например, в таблице «Билеты» (tickets) уже есть столбец такого типа — contact_data. Выполните модификации таблиц и измените в них одну-две строки для проверки правильности ваших решений. 109 6 Запросы Эта глава будет самой насыщенной и интересной, поскольку умение писать SQL-запросы — это не только ремесло, но, пожалуй, и искусство тоже. В предыдущих главах мы уже не раз использовали команду SELECT и формировали с ее помощью различные запросы. Эти запросы строились как на основе одной табли- цы, так и на основе двух и более таблиц. Мы рассмотрели простые способы сортиров- ки и группировки строк в полученных выборках из таблиц, использовали функцию count для подсчета числа выбранных строк. Таким образом, вы уже получили эле- ментарное представление о том, как формировать выборки из базы данных. В этой главе мы покажем более сложные способы их получения. С целью приведения в систему тех знаний о формировании выборок, что были полу- чены в предыдущих главах, в этой главе мы повторим некоторые сведения, но сдела- ем это уже на новых примерах. 6.1 Дополнительные возможности команды SELECT Основой для экспериментов в этом разделе будут самые маленькие (по числу строк) таблицы базы данных «Авиаперевозки»: «Самолеты» (aircrafts) и «Аэропорты» (airports). Прежде чем перейти к конкретным запросам, просто просмотрите содержимое этих двух таблиц. Таблица «Самолеты» совсем маленькая, а таблица «Аэропорты» содер- жит чуть больше ста строк. Для ее просмотра можно включить расширенный режим вывода данных \x. SELECT * FROM aircrafts; SELECT * FROM airports; Начнем с различных условий отбора строк в предложении WHERE. Эти условия могут конструироваться с использованием следующих операторов сравнения: =, <>, >, >= , <, <=. В предыдущих главах мы уже использовали ряд таких операторов, поэто- му сейчас рассмотрим некоторые другие способы осуществления отбора строк. Для начала поставим перед собой такую задачу: выбрать все самолеты компании Airbus. В этом нам поможет оператор поиска шаблонов LIKE: SELECT * FROM aircrafts WHERE model LIKE 'Airbus%'; Обратите внимание на символ «%», имеющий специальное значение. Он соответ- ствует любой последовательности символов, т. е. вместо него могут быть подстав- лены любые символы в любом количестве, а может и не быть подставлено ни одного символа. В результате будут выбраны строки, в которых значения атрибута model на- чинаются с символов «Airbus»: 110 aircraft_code | model | range ---------------+-----------------+------- 320 | Airbus A320-200 | 5700 321 | Airbus A321-200 | 5600 319 | Airbus A319-100 | 6700 (3 строки) Шаблон в операторе LIKE всегда покрывает всю анализируемую строку. Поэтому если требуется отыскать некоторую последовательность символов где-то внутри строки, то шаблон должен начинаться и завершаться символом «%». Однако в этом случае нужно учитывать следующие соображения. Если по тому столбцу, к которому приме- няется оператор LIKE, создан индекс для ускорения доступа к данным, то при нали- чии символа «%» в начале шаблона этот индекс использоваться не будет. В результате может ухудшиться производительность, т. е. запрос будет выполняться медленнее. Индексы подробно рассматриваются в главе 8, а вопросы производительности — в главе 10. Конечно, существует и оператор NOT LIKE. Например, если мы захотим узнать, каки- ми самолетами, кроме машин компаний Airbus и Boeing, располагает наша авиаком- пания, то придется усложнить условие: SELECT * FROM aircrafts WHERE model NOT LIKE 'Airbus%' AND model NOT LIKE 'Boeing%'; aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3000 CN1 | Cessna 208 Caravan | 1200 CR2 | Bombardier CRJ-200 | 2700 (3 строки) Кроме символа «%» в шаблоне может использоваться и символ «_», который соответ- ствует в точности одному любому символу. В качестве примера найдем в таблице «Аэропорты» (airports) те из них, которые имеют названия длиной три символа (бук- вы). С этой целью зададим в качестве шаблона строку, состоящую из трех символов «_». SELECT * FROM airports WHERE airport_name LIKE '___'; --[ RECORD 1 ]+------------------- airport_code | UFA airport_name | Уфа city | Уфа longitude | 55.874417 latitude | 54.557511 timezone | Asia/Yekaterinburg Существует ряд операторов для работы с регулярными выражениями POSIX. Эти операторы имеют больше возможностей, чем оператор LIKE. Для того чтобы выбрать, например, самолеты компаний Airbus и Boeing, можно сделать так: SELECT * FROM aircrafts WHERE model '^(A|Boe)'; 111 aircraft_code | model | range ---------------+-----------------+------- 773 | Boeing 777-300 | 11100 763 | Boeing 767-300 | 7900 320 | Airbus A320-200 | 5700 321 | Airbus A321-200 | 5600 319 | Airbus A319-100 | 6700 733 | Boeing 737-300 | 4200 (6 строк) Оператор ищет совпадение с шаблоном с учетом регистра символов. Символ «^» в начале регулярного выражения означает, что поиск совпадения будет привязан к началу строки. Если же требуется проверить наличие такого символа в составе стро- ки, то перед ним нужно поставить символ обратной косой черты «\». Выражение в круглых скобках означает альтернативный выбор между значениями, разделяемы- ми символом «|». Поэтому в выборку попадут значения, начинающиеся либо на «A», либо на «Boe». Для инвертирования смысла оператора нужно перед ним добавить знак «!». В каче- стве примера отыщем модели самолетов, которые не завершаются числом 300. SELECT * FROM aircrafts WHERE model ! '300$'; В этом регулярном выражении символ «$» означает привязку поискового шаблона к концу строки. Если же требуется проверить наличие такого символа в составе строки, то перед ним нужно поставить символ обратной косой черты «\». aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3000 320 | Airbus A320-200 | 5700 321 | Airbus A321-200 | 5600 319 | Airbus A319-100 | 6700 CN1 | Cessna 208 Caravan | 1200 CR2 | Bombardier CRJ-200 | 2700 (6 строк) Использование регулярных выражений подробно рассматривается в разделе доку- ментации 9.7.3 «Регулярные выражения POSIX». В качестве замены традиционных операторов сравнения могут использоваться пре- дикаты сравнения , которые ведут себя так же, как и операторы, но имеют другой синтаксис. Давайте ответим на вопрос: какие самолеты имеют дальность полета в диапазоне от 3000 км до 6000 км? Ответ получим с помощью предиката BETWEEN. SELECT * FROM aircrafts WHERE range BETWEEN 3000 AND 6000; aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3000 320 | Airbus A320-200 | 5700 321 | Airbus A321-200 | 5600 733 | Boeing 737-300 | 4200 (4 строки) 112 Обратите внимание, что граничное значение 3000 было включено в полученную вы- борку. При выборке данных можно проводить вычисления и получать в результирующей таблице вычисляемые столбцы. Если мы захотим представить дальность полета лайнеров не только в километрах, но и в милях, то нужно вычислить это выражение и для удобства присвоить новому столбцу псевдоним с помощью ключевого слова AS. SELECT model, range, range / 1.609 AS miles FROM aircrafts; model | range | miles ---------------------+-------+----------------------- Boeing 777-300 | 11100 | 6898.6948415164698571 Boeing 767-300 | 7900 | 4909.8819142324425109 (9 строк) По всей вероятности, такая высокая точность представления значений в милях не требуется, поэтому мы можем уменьшить ее до разумного предела в два десятичных знака: SELECT model, range, round( range / 1.609, 2 ) AS miles FROM aircrafts; model | range | miles ---------------------+-------+--------- Boeing 777-300 | 11100 | 6898.69 Boeing 767-300 | 7900 | 4909.88 Теперь обратимся к такому вопросу, как упорядочение строк при выводе. Если не принять специальных мер, то СУБД не гарантирует никакого конкретного поряд- ка строк в результирующей выборке. Для упорядочения строк служит предложение ORDER BY , которое мы уже использовали ранее. Однако мы не говорили, что можно задать не только возрастающий, но также и убывающий порядок сортировки. Напри- мер, если мы захотим разместить самолеты в порядке убывания дальности их полета, то нужно сделать так: SELECT * FROM aircrafts ORDER BY range DESC; aircraft_code | model | range ---------------+---------------------+------- 773 | Boeing 777-300 | 11100 763 | Boeing 767-300 | 7900 CR2 | Bombardier CRJ-200 | 2700 CN1 | Cessna 208 Caravan | 1200 (9 строк) Мы детально разобрались с таблицей «Самолеты» (aircrafts) и теперь обратим на- ше внимание на таблицу «Аэропорты» (airports). В ней есть столбец «Часовой пояс» (timezone). Давайте посмотрим, в каких различных часовых поясах располагаются аэропорты. Если сделать традиционную выборку SELECT timezone FROM airports; 113 то мы получим список значений, среди которых будет много повторяющихся. Конеч- но, это неудобно. Для того чтобы оставить в выборке только неповторяющиеся зна- чения, служит ключевое слово DISTINCT: SELECT DISTINCT timezone FROM airports ORDER BY 1; Обратите внимание, что столбец, по значениям которого будут упорядочены строки, указан не с помощью его имени, а с помощью его порядкового номера в предложении SELECT. Получим такой результат: timezone -------------------- Asia/Anadyr Asia/Chita Asia/Irkutsk Asia/Kamchatka Asia/Krasnoyarsk Asia/Magadan Asia/Novokuznetsk Asia/Novosibirsk Asia/Omsk Asia/Sakhalin Asia/Vladivostok Asia/Yakutsk Asia/Yekaterinburg Europe/Kaliningrad Europe/Moscow Europe/Samara Europe/Volgograd (17 строк) Таким образом, аэропорты располагаются в семнадцати различных часовых поясах. Они описаны в базе данных часовых поясов, поддерживаемой международной орга- низацией IANA (Internet Assigned Numbers Authority), и отличаются от традиционных географических и административных часовых поясов, число которых в России равно одиннадцати. В таблице «Аэропорты» (airports) более ста записей. Если мы поставим задачу найти три самых восточных аэропорта, то для ее решения подошел бы такой алгоритм: от- сортировать строки в таблице по убыванию значений столбца «Долгота» (longitude) и включить в выборку только первые три строки. Как отсортировать строки по убыва- нию значений какого-либо столбца, вы уже знаете, а для того чтобы ограничить число строк, включаемых в результирующую выборку, служит предложение LIMIT. SELECT airport_name, city, longitude FROM airports ORDER BY longitude DESC LIMIT 3; airport_name | city | longitude --------------+--------------------------+------------ Анадырь | Анадырь | 177.741483 Елизово | Петропавловск-Камчатский | 158.453669 114 Магадан | Магадан | 150.720439 (3 строки) А как найти еще три аэропорта, которые находятся немного западнее первой трой- ки, т. е. занимают места с четвертого по шестое? Алгоритм будет почти таким же, как в первой задаче, но он будет дополнен еще одним шагом: нужно пропустить три первые строки, прежде чем начать вывод. Для пропуска строк служит предложение OFFSET SELECT airport_name, city, longitude FROM airports ORDER BY longitude DESC LIMIT 3 OFFSET 3; airport_name | city | longitude -----------------+----------------------+------------ Хомутово | Южно-Сахалинск | 142.717531 Хурба | Комсомольск-на-Амуре | 136.934 Хабаровск-Новый | Хабаровск | 135.188361 (3 строки) В дополнение к вычисляемым столбцам, когда выводимые значения получают путем вычислений, при выборке данных из таблиц можно использовать условные выраже- ния , позволяющие вывести то или иное значение в зависимости от условий. В табли- це «Самолеты» (aircrafts) есть столбец «Максимальная дальность полета» (range). Мы можем дополнить вывод данных из этой таблицы столбцом «Класс самолета», имея в виду принадлежность каждого самолета к классу дальнемагистральных, среднема- гистральных или ближнемагистральных судов. Для этого подойдет конструкция CASE WHEN condition THEN result [WHEN ...] [ELSE result] END Воспользовавшись этой конструкцией в предложении SELECT и назначив новому столбцу имя с помощью ключевого слова AS, получим следующий запрос: SELECT model, range, CASE WHEN range < 2000 THEN 'Ближнемагистральный' WHEN range < 5000 THEN 'Среднемагистральный' ELSE 'Дальнемагистральный' END AS type FROM aircrafts ORDER BY model; model | range | type ---------------------+-------+--------------------- Airbus A319-100 | 6700 | Дальнемагистральный Airbus A320-200 | 5700 | Дальнемагистральный Airbus A321-200 | 5600 | Дальнемагистральный Boeing 737-300 | 4200 | Среднемагистральный Boeing 767-300 | 7900 | Дальнемагистральный Boeing 777-300 | 11100 | Дальнемагистральный Bombardier CRJ-200 | 2700 | Среднемагистральный Cessna 208 Caravan | 1200 | Ближнемагистральный 115 |