Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
Bookings Бронирования # book_ref * book_date * total_amount Ticket_flights Перелеты # ticket_no # flight_id * fare_conditions * amount Flights Рейсы # flight_id * flight_no * scheduled_departure * scheduled_arrival * departure_airport * arrival_airport * status * aircraft_code ° actual_departure ° actual_arrival Boarding_passes Посадочные талоны # ticket_no # flight_id * boarding_no * seat_no Airports Аэропорты # airport_code * airport_name * city * longitude * latitude * timezone Aircrafts Самолеты # aircraft_code * model * range Seats Места # aircraft_code # seat_no * fare_conditions Tickets Билеты # ticket_no * book_ref * passenger_id * passenger_name ° contact_data схемы данных, на которой показаны сущности, выделенные в предметной области, а также их связи и атрибуты. Конкретные же типы данных, первичные и внешние ключи, а также ограничения, наложенные на атрибуты и таблицы, мы покажем уже в последующих главах в процессе рассмотрения команд SQL, предназначенных для физического создания таблиц в базе данных. Приведенную схему можно найти на сайте компании Postgres Professional по адресам, указанным в начале этого раздела. Контрольные вопросы и задания 1. Какие группы операторов выделяются в составе языка SQL? 2. Дайте неформальное определение основных понятий реляционной модели данных: отношение, кортеж, атрибут. 3. Для чего нужны внешние ключи в реляционных таблицах? 4. Что такое потенциальный ключ? 5.* Предложите пример избыточного потенциального ключа для одной из таблиц базы данных «Авиаперевозки» и объясните, почему он будет избыточным. 16 6.* В текущей реализации базы данных «Авиаперевозки» предполагается, что са- молеты одной модели могут иметь только одну компоновку салона. Предста- вим, что руководством принято решение о том, что нужно учитывать возмож- ность наличия различных компоновок для каждой модели. Какие таблицы при- дется модифицировать в таком случае и каким образом? Потребуется ли созда- вать дополнительные таблицы? 17 2 Создание рабочей среды Прежде чем приступать к непосредственному изучению языка SQL, нужно получить доступ к серверу PostgreSQL. Это можно сделать, например, в компьютерном классе или путем обра- щения к удаленному серверу через терминал. Однако можно создать рабочую среду для себя и на своем локальном компьютере, установив полную версию СУБД PostgreSQL, т. е. сервер и клиентские программы. В этом случае у вас будет гораздо больше полномочий по настройке и использованию PostgreSQL. В заключительной части главы мы покажем, как развернуть учебную базу данных «Авиапере- возки», наполненную специально подготовленными правдоподобными данными. 2.1 Установка СУБД Поскольку настоящее учебное пособие предназначено для изучения языка SQL, а не основ администрирования СУБД PostgreSQL, то мы ограничимся лишь краткими ука- заниями о том, где найти инструкции по установке. Начать нужно с выбора того дистрибутива СУБД, который вы хотели бы установить. Вы можете выбрать оригинальный вариант PostgreSQL или тот, который предлагает- ся компанией Postgres Professional. Он называется Postgres Pro и содержит не только все функции и модули, входящие в состав стандартного дистрибутива, но и дополни- тельные разработки, выполненные в компании Postgres Professional. Для изучения основ языка SQL эти дистрибутивы подходят в равной степени. Однако документа- ция на русском языке включена только в состав Postgres Pro. После того как вы определитесь с конкретным дистрибутивом СУБД, необходимо выбрать операционную систему. PostgreSQL поддерживает множество систем, в том числе различные версии Linux, а также Windows. Устанавливать рекомендуется последнюю стабильную версию СУБД. Если вы решили воспользоваться оригинальным дистрибутивом PostgreSQL, то най- ти инструкции по его установке в различных операционных системах можно по ад- ресу https://www.postgresql.org/download/. Если же вы остановили свой выбор на дистрибутиве Postgres Pro, тогда следует об- ратиться сюда: https://postgrespro.ru/products/postgrespro/download/ latest. После установки как PostgreSQL, так и Postgres Pro в среде Windows придется пред- принять дополнительные меры, чтобы использование русского алфавита в интерак- тивном терминале psql не вызывало проблем. Утилита psql рассматривается в следу- ющем разделе. В процессе установки будет создана учетная запись пользователя СУБД с именем postgres. Для изучения настоящего пособия создавать дополнительные учетные за- писи не требуется. 18 Установив тот или иной дистрибутив PostgreSQL, нужно научиться запускать сервер баз данных, потому что иначе невозможно работать с данными. Как это сделать, по- дробно описано в документации в разделе 18.3 «Запуск сервера баз данных». Най- ти этот раздел можно по адресу https://postgrespro.ru/docs/postgresql/ current/server-start.html. При установке СУБД в среде Windows создается служба (service) для автоматического запуска сервера PostgreSQL при загрузке опе- рационной системы. Завершив работу с сервером, нужно корректно остановить (выключить) его. Поря- док действий в такой ситуации описан в документации в разделе 18.5 «Выключе- ние сервера». Найти этот раздел можно по адресу https://postgrespro.ru/docs/ postgresql/9.6/server-shutdown.html. 2.2 Программа psql — интерактивный терминал PostgreSQL Для доступа к серверу баз данных в комплект PostgreSQL входит интерактивный тер- минал psql. Для его запуска нужно ввести команду psql При запуске утилиты psql в среде Windows возможно некорректное отображение букв русского алфавита. Для устранения этого потребуется в свойствах окна, в котором вы- полняется psql, изменить шрифт на Lucida Console и с помощью команды chcp сме- нить текущую кодовую страницу на CP1251: chcp 1251 В среде утилиты psql можно вводить не только команды языка SQL, но и различ- ные сервисные команды, поддерживаемые самой утилитой. Для получения краткой справки по всем сервисным командам нужно ввести \? Многие такие команды начинаются с символов \d. Например, для того чтобы про- смотреть список всех таблиц и представлений (views), созданных в той базе данных, к которой вы сейчас подключены, введите команду \dt Если же вас интересует определение (попросту говоря, структура) какой-либо кон- кретной таблицы базы данных, например, students, нужно ввести команду \d students Для получения списка всех SQL-команд нужно выполнить команду \h Для вывода описания конкретной SQL-команды, например, CREATE TABLE, нужно сделать так: \h CREATE TABLE 19 Эта утилита позволяет сокращать объем ручного ввода за счет дополнения вводимой команды «силами» psql. Например, при вводе SQL-команды можно использовать кла- вишу Tab для дополнения вводимого ключевого слова команды или имени таблицы базы данных. Например, при вводе команды CREATE TABLE ...можно, введя символы «cr», нажать клавишу Tab — psql дополнит это слово до «create». Аналогично можно поступить и со словом TABLE. Для его ввода достаточно ввести лишь буквы «ta» и на- жать клавишу Tab. Если вы ввели слишком мало букв для того, чтобы утилита psql могла однозначно идентифицировать ключевое слово, дополнения не произойдет. Но в таком случае вы можете нажать клавишу Tab дважды и получить список всех ключевых слов, начинающихся с введенной вами комбинации букв. 2.3 Развертывание учебной базы данных Завершив установку сервера баз данных, мы можем перейти непосредственно к рас- смотрению вопроса о том, как развернуть в вашем кластере PostgreSQL учебную базу данных «Авиаперевозки», подготовленную компанией Postgres Professional. На сайте компании есть раздел, посвященный этой базе данных, найти его можно по ссылке https://postgrespro.ru/education/demodb. Она предоставляется в трех версиях, отличающихся только объемом данных: самая компактная версия со- держит данные за один месяц, версия среднего размера охватывает временной пе- риод в три месяца, а самая полная версия включает данные за целый год. Все дан- ные были сгенерированы с помощью специальных алгоритмов, обеспечивающих их «правдоподобность». Мы рекомендуем вам начать с компактной версии базы данных «Авиаперевозки», а после получения некоторого опыта написания SQL-запросов вы установите полную версию и уже на ней сможете лучше «прочувствовать» различные тонкости работы с данными больших объемов, например, оцените влияние индексов на скорость доступа к данным. В качестве первого шага к развертыванию базы данных нужно скачать ее заархивиро- ванную резервную копию по ссылке https://edu.postgrespro.ru/demo_small. zip. Затем необходимо извлечь файл из архива: unzip demo_small.zip Извлеченный файл называется demo_small.sql. Теперь мы создадим базу данных с именем demo в вашем кластере PostgreSQL. Самый краткий вариант команды будет таким: psql -f demo_small.sql -U postgres Если вы хотите перенаправить вывод сообщений, которые генерирует СУБД в про- цессе работы, с экрана в файлы, то можно поступить так: psql -f demo_small.sql -U postgres > demo.log 2>demo.err Можно разделить стандартное устройство вывода и стандартное устройство вывода ошибок. Обычные сообщения будут перенаправлены в файл demo.log, а сообщения об ошибках — в файл demo.err. Обратите внимание, что между цифрой 2, обозначающей дескриптор стандартного устройства вывода сообщений об ошибках, и знаком «>», обозначающим переадресацию вывода, не должно быть пробела. 20 Если вам удобнее собрать все сообщения в один общий файл, тогда нужно сделать так: psql -f demo_small.sql -U postgres > demo.log 2>&1 Обратите внимание, что все выражение 2>&1 в конце команды пишется без пробе- лов. Оно указывает операционной системе, что сообщения об ошибках нужно напра- вить туда же, куда выводятся и обычные сообщения. Если бы наш SQL-файл был очень большим, тогда можно было бы выполнить коман- ду в фоновом режиме, поставив в конце командной строки символ «&», а за ходом процесса в реальном времени наблюдать с помощью команды tail. psql -f demo_small.sql -U postgres > demo.log 2>&1 & tail -f demo.log Выберите один из предложенных вариантов команды для развертывания базы дан- ных и выполните эту команду. Все готово! Можно подключаться к новой базе данных: psql -d demo -U postgres Контрольные вопросы и задания 1. Выполните процедуру установки СУБД PostgreSQL в среде выбранной вами опе- рационной системы. 2. Ознакомьтесь с утилитой psql с помощью встроенной справки, а также с помо- щью справки, вызываемой по команде psql --help 3. Кроме утилиты psql существуют и другие универсальные программы для рабо- ты с сервером баз данных PostgreSQL, например, pgAdmin. Это мощная утилита с графическим интерфейсом. Самостоятельно установите программу pgAdmin и изучите основные приемы работы с ней. 4. Выполните развертывание учебной базы данных. Попробуйте подключиться к ней с помощью утилиты psql. Для выхода из утилиты используйте команду \q. 21 3 Основные операции с таблицами Язык SQL — очень многообразный, он включает в себя целый ряд команд, которые, в свою очередь, иной раз имеют множество параметров и ключевых слов. Но начнем мы с краткого обзора основных возможностей языка SQL. В этой главе вы научитесь вводить данные в базу данных, освоите основные способы получения информации из базы данных, т. е. выборки, а также узнаете, как можно внести изменения в информацию, хранящуюся в базе данных, и удалить те данные, которые больше не нужны. В практике изучения иностранных языков есть хорошая традиция. Уже на первом занятии ученик изучает некоторые базовые грамматические конструкции и слова, позволяющие ему сказать несколько самых простых, но, тем не менее, практически полезных фраз. Мы последуем этой традиции. В данном разделе нашего пособия вы ознакомитесь с основными командами языка SQL, которые позволят вам выполнять базовые операции. А более сложные (и интересные) команды вы изучите в следую- щих главах. Скажем два слова о нашем подходе к работе. В принципе возможны два способа орга- низации работы студента (обучающегося). Первый способ таков: студент использует базу данных, в которой уже содержатся все необходимые таблицы и другие объекты базы данных, подготовленные заранее автором учебного пособия или другим квали- фицированным специалистом. При этом некоторый набор необходимых данных так- же уже введен в таблицы, поэтому можно сразу же переходить к выполнению запро- сов к этим таблицам. Описанный способ кажется очень привлекательным, поскольку он требует меньше усилий на начальном этапе освоения языка SQL. Однако, на наш взгляд, более правильным является другой способ. Наверное, он более трудоемкий, но при его использовании вы лучше, как говорится, прочувствуете процесс создания таблиц и ввода записей в эти таблицы. А выполняя различные запросы к базе данных, вам будет легче оценить правильность полученного результата выполнения запро- са, поскольку вы ввели все данные самостоятельно и поэтому сможете обоснованно предположить, какие результаты ожидаете увидеть на экране. Конечно, первый спо- соб может быть очень полезным при изучении более сложных, продвинутых, возмож- ностей языка SQL, которые трудно понять без использования больших массивов дан- ных, а большие массивы данных вводить в базу данных вручную — нерационально. Гораздо более рациональным будет их автоматическое формирование программным путем. В главе 1 мы описали предметную область, поэтому сейчас можем приступить к непо- средственному созданию таблиц в базе данных. Для выполнения всех последующих команд и операций мы будем использовать утилиту psql, входящую в стандартную поставку СУБД PostgreSQL. На вашем компьютере уже должна быть развернута база данных demo. Процесс ее создания описан в главе 2. Теперь запустите утилиту psql и подключитесь к этой базе данных с учетной записью пользователя postgres: psql -d demo --U postgres Для создания таблиц в языке SQL служит команда CREATE TABLE. Ее полный синтак- сис представлен в документации на PostgreSQL, а упрощенный синтаксис таков: 22 CREATE TABLE "имя_таблицы" ( имя_поля тип_данных [ограничения_целостности], имя_поля тип_данных [ограничения_целостности], ... имя_поля тип_данных [ограничения_целостности], [ограничение_целостности], [первичный_ключ], [внешний_ключ] ); В квадратных скобках показаны необязательные элементы команды. После команды нужно поставить символ «;». Для получения в среде утилиты psql полной информации о команде CREATE TABLE сделайте так: \h CREATE TABLE Обратите внимание на отсутствие символа «;» в конце строки. Наименование SQL-команды можно вводить и в нижнем регистре, т. е. строчными буквами: \h create table В качестве первой таблицы, которую мы создадим, выберем «Самолеты». Таблица имеет следующую структуру (т. е. набор атрибутов и их типы данных): Описание атрибута Имя атрибута Тип данных Тип PostgreSQL Ограничения Код самолета, IATA aircraft_code Символьный char(3) NOT NULL Модель самолета model Символьный text NOT NULL Максимальная даль- ность полета, км range Числовой integer NOT NULL range > 0 Типы char и text являются символьными типами данных и позволяют вводить любые символы, в том числе буквы и цифры. Для атрибута «Код самолета, IATA» мы выбрали тип char(3), поскольку эти коды состоят из трех символов: букв и цифр. Число 3 в описании типа данных char означает максимальное количество символов, которые можно ввести в это поле. Наименования конкретных моделей самолетов могут содержать различные количе- ства разных символов, поэтому для атрибута «Модель самолета» мы выбрали тип дан- ных text, который не требует указания максимальной длины сохраняемого значения. Вообще, число символов, которые можно сохранить в поле типа text, практически не ограничено. Для атрибута «Максимальная дальность полета» мы выбрали целый числовой тип. Значения всех атрибутов каждой строки данной таблицы должны быть определенны- ми, поэтому на них накладывается ограничение NOT NULL. В принципе в таблицах базы данных могут содержаться неопределенные значения некоторых атрибутов. Го- воря другими словами, их значения могут отсутствовать. В таких случаях в этих полях содержится специальное значение NULL. Но в таблице «Самолеты» не допускается отсутствие значений атрибутов, отсюда и возникает ограничение NOT NULL. К тому же атрибут «Максимальная дальность полета» не должен принимать отрицательных 23 значений и нулевого значения, поэтому приходится добавить еще одно ограничение: range > 0. В качестве первичного ключа выбран атрибут «Код самолета, IATA». Таким образом, первичный ключ будет, как говорят, естественным. Это означает, что и в реальной предметной области существует такое понятие, как код самолета, и это понятие ис- пользуется на практике. В отличие от естественных ключей иногда используются и так называемые суррогатные ключи, но о них мы расскажем в последующих главах пособия. Итак, команда для создания нашей первой таблицы «Самолеты» такова: CREATE TABLE aircrafts ( aircraft_code char( 3 ) NOT NULL, model text NOT NULL, range integer NOT NULL, CHECK ( range > 0 ), PRIMARY KEY ( aircraft_code ) ); Прежде чем вы сможете приступить к непосредственному вводу этой команды в ко- мандной строке утилиты psql, мы дадим ряд рекомендаций. Для СУБД регистр символов (прописные или строчные буквы), используемых для ввода ключевых (зарезервированных) слов, значения не имеет. Однако традицион- но ключевые слова языка SQL вводят в верхнем регистре, что повышает наглядность SQL-операторов. Тем не менее, наименования типов данных (integer, char, text и т. д.) мы будем писать не заглавными буквами, а строчными, поскольку именно так «по- ступает» утилита pg_dump (входящая в комплект поставки PostgreSQL), которая пред- назначена для создания резервной копии базы данных. Конечно, при выполнении заданий, приводимых в нашем учебном пособии, допустимо для ускорения набора вводить в нижнем регистре и ключевые слова. А в реальной работе нужно следовать тем правилам оформления исходных кодов, которые приняты в рамках выполняемо- го проекта. Эту команду для создания таблицы aircrafts (как и все SQL-команды) в утилите psql можно вводить двумя способами. Первый способ заключается в том, что команда вводится полностью на одной строке, при этом строка сворачивается «змейкой». На- жимать клавишу Enter после ввода каждого фрагмента команды не нужно, но можно для повышения наглядности вводить пробел. На экране это выглядит так: |