2.3. Развертывание учебной базы данных
Для получения краткой справки по всем сервисным командам нужно ввести
\?
Многие такие команды начинаются с символов «\d». Например, для того чтобы про- смотреть список всех таблиц и представлений (views), созданных в той базе данных,
к которой вы сейчас подключены, введите команду
\dt
Если же вас интересует определение (попросту говоря, структура) какой-либо кон- кретной таблицы базы данных, например, students, нужно ввести команду
\d students
Для получения списка всех SQL-команд нужно выполнить команду
\h
Для вывода описания конкретной SQL-команды, например, CREATE TABLE, нужно сделать так:
\h CREATE TABLE
Эта утилита позволяет сокращать объем ручного ввода за счет дополнения вводи- мой команды «силами» psql. Например, при вводе SQL-команды можно использовать клавишу для дополнения вводимого ключевого слова команды или имени таблицы базы данных. Например, при вводе команды CREATE TABLE можно, введя символы «cr», нажать клавишу — psql дополнит это слово до «create». Анало- гично можно поступить и со вторым словом: для его ввода достаточно ввести лишь буквы «ta» и нажать клавишу . Если вы ввели слишком мало букв для того, что- бы утилита psql могла однозначно идентифицировать ключевое слово, дополнения не произойдет. Но в таком случае вы можете нажать клавишу дважды и полу- чить список всех ключевых слов, начинающихся с введенной вами комбинации букв.
2.3. Развертывание учебной базы данных
Завершив установку сервера баз данных, мы можем перейти непосредственно к рас- смотрению вопроса о том, как развернуть в вашем кластере PostgreSQL учебную базу данных «Авиаперевозки», подготовленную компанией Postgres Professional.
27
Глава 2. Создание рабочей среды
На сайте компании есть раздел, посвященный этой базе данных, найти его можно по ссылке 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, обозначающей дескриптор стандартного устройства вывода сообщений об ошибках, и знаком «>»,
обозначающим переадресацию вывода, не должно быть пробела.
Если вам удобнее собрать все сообщения в один общий файл, тогда нужно сделать так:
psql -f demo_small.sql -U postgres > demo.log 2>&1
Обратите внимание, что все выражение 2>&1 в конце команды пишется без пробелов.
Оно указывает операционной системе, что сообщения об ошибках нужно направить туда же, куда выводятся и обычные сообщения.
28
Контрольные вопросы и задания
Если бы наш 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.
29
Глава 3
Основные операции с таблицами
Язык SQL — очень многообразный, он включает в себя целый ряд команд, которые, в свою очередь,
иной раз имеют множество параметров и ключевых слов. Но начнем мы с краткого обзора основ- ных возможностей языка SQL. В этой главе вы научитесь вводить данные в базу данных, освоите основные способы получения информации из базы данных, т. е. выборки, а также узнаете, как можно внести изменения в информацию, хранящуюся в базе данных, и удалить те данные, которые больше не нужны.
В практике изучения иностранных языков есть хорошая традиция. Уже на первом занятии ученик изучает некоторые базовые грамматические конструкции и слова,
позволяющие ему сказать несколько самых простых, но, тем не менее, практически полезных фраз. Мы последуем этой традиции. В данной главе нашего пособия вы ознакомитесь с основными командами языка SQL, которые позволят вам выполнять базовые операции. А более сложные (и интересные) команды вы изучите в следую- щих главах.
Скажем два слова о нашем подходе к работе. В принципе возможны два способа орга- низации работы студента (обучающегося). Первый способ таков: студент использует базу данных, в которой уже содержатся все необходимые таблицы и другие объекты базы данных, подготовленные заранее автором учебного пособия или другим ква- лифицированным специалистом. При этом некоторый набор необходимых данных также уже введен в таблицы, поэтому можно сразу же переходить к выполнению запросов к этим таблицам. Описанный способ кажется очень привлекательным, по- скольку он требует меньше усилий на начальном этапе освоения языка SQL.
Однако, на наш взгляд, более правильным является другой способ. Наверное, он бо- лее трудоемкий, но при его использовании вы лучше, как говорится, прочувствуете процесс создания таблиц и ввода записей в эти таблицы. А при выполнении раз- личных запросов к базе данных вам будет легче оценить правильность полученного результата выполнения запроса, поскольку вы ввели все данные самостоятельно и поэтому сможете обоснованно предположить, какие результаты ожидаете увидеть на экране.
31
Глава 3. Основные операции с таблицами
Конечно, первый способ может быть очень полезным при изучении более сложных,
продвинутых, возможностей языка SQL, которые трудно понять без использования больших массивов данных, а большие массивы данных вводить в базу данных вруч- ную — нерационально. Гораздо более рациональным будет их автоматическое фор- мирование программным путем.
В главе 1 мы описали предметную область, поэтому сейчас можем приступить к непо- средственному созданию таблиц в базе данных. Для выполнения всех последующих команд и операций мы будем использовать утилиту psql, входящую в стандартную поставку СУБД PostgreSQL.
На вашем компьютере уже должна быть развернута база данных demo. Процесс ее создания описан в главе 2. Теперь запустите утилиту psql и подключитесь к этой базе данных с учетной записью пользователя postgres:
psql -d demo -U postgres
Для создания таблиц в языке SQL служит команда CREATE TABLE. Ее полный синтак- сис представлен в документации на PostgreSQL, а упрощенный синтаксис таков:
CREATE TABLE имя-таблицы
(
имя-поля тип-данных [ограничения-целостности],
имя-поля тип-данных [ограничения-целостности],
...
имя-поля тип-данных [ограничения-целостности],
[ограничение-целостности],
[первичный-ключ],
[внешний-ключ]
);
В квадратных скобках показаны необязательные элементы команды. После команды нужно поставить символ «;».
Для получения в среде утилиты psql полной информации о команде CREATE TABLE
сделайте так:
\h CREATE TABLE
Обратите внимание на отсутствие символа «;» в конце строки.
Наименование SQL-команды можно вводить и в нижнем регистре, т. е. строчными буквами:
\h create table
32
Глава 3. Основные операции с таблицами
В качестве первой таблицы, которую мы создадим, выберем «Самолеты». Таблица имеет следующую структуру (т. е. набор атрибутов и их типы данных):
Описание атрибута
Имя атрибута
Тип данных
Тип 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. К тому же атрибут «Максимальная дальность полета» не должен принимать отрицательных значений и нулевого значения, поэтому приходится добавить еще одно ограничение:
range > 0.
В качестве первичного ключа выбран атрибут «Код самолета, IATA». Таким образом,
первичный ключ будет, как говорят, естественным. Это означает, что и в реальной предметной области существует такое понятие, как код самолета, и это понятие ис- пользуется на практике. В отличие от естественных ключей иногда используются и так называемые суррогатные ключи, но о них мы расскажем в последующих главах пособия.
33
Глава 3. Основные операции с таблицами
Итак, команда для создания нашей первой таблицы «Самолеты» такова:
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 можно вводить двумя способами. Первый способ заключается в том, что коман- да вводится полностью на одной строке, при этом строка сворачивается «змейкой».
Нажимать клавишу после ввода каждого фрагмента команды не нужно, но можно для повышения наглядности вводить пробел. На экране это выглядит так:
demo=# CREATE TABLE aircrafts ( aircraft_code char( 3 ) NOT NULL, model
text NOT NULL, range integer NOT NULL, CHECK ( range > 0 ), PRIMARY KEY
( aircraft_code ) );
Второй способ заключается в построчном вводе команды точно так же, как она напе- чатана в тексте главы. При этом после ввода каждой строки нужно нажимать клавишу
<
Enter>.
Обратите внимание, что до тех пор, пока команда не введена полностью, вид при- глашения к вводу команд, выводимого утилитой psql, будет отличаться от первона- чального. В конце команды необходимо поставить точку с запятой.
34
Глава 3. Основные операции с таблицами
demo=# CREATE TABLE aircrafts
demo-# ( aircraft_code char( 3 ) NOT NULL,
demo(# model text NOT NULL,
demo(# range integer NOT NULL,
demo(# CHECK ( range > 0 ),
demo(# PRIMARY KEY ( aircraft_code )
demo(# );
В среде утилиты psql предлагаются и другие способы завершения вводимых команд с целью их последующего выполнения. Например, вместо ввода символа «;» команду можно завершить символами «\g»:
demo=# CREATE TABLE aircrafts ... \g
Впоследствии можно с помощью клавиши <↑> вызвать на экран (из буфера истории введенных команд) всю команду полностью в компактном виде и при необходимо- сти отредактировать ее либо выполнить еще раз без редактирования. При этом для команды, введенной построчно, сохраняется ее построчная структура, а приглаше- ние выводится только для первой строки:
demo=# 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 вызвать внешний редактор для редак- тирования текущего буфера запроса, то нужно воспользоваться командой \e.
Если вы решили прервать ввод команды, еще не введя ее полностью, то просто на- жмите клавиши +, в результате ввод команды будет прерван, а приглаше- ние к вводу, выводимое утилитой psql, примет свой первоначальный вид:
demo=# CREATE TABLE aircrafts
( aircraft_code char( 3 ) NOT NULL,
demo(# ^C
demo=#
35
Глава 3. Основные операции с таблицами
Теперь выберите способ ввода команды для создания таблицы aircrafts и введите ее. Если вы не допустили ошибок, то в ответ psql выведет сообщение, означающее успешное создание таблицы:
CREATE TABLE
Вы можете проверить, какую таблицу создала СУБД. Для этого служит команда ути- литы psql
\d aircrafts
В ответ вы получите примерно такой вывод на экран:
Таблица "public.aircrafts"
Колонка
|
Тип
| Модификаторы
----------------+--------------+-------------- aircraft_code | character(3) | NOT NULL
model
| text
| NOT NULL
range
| integer
| NOT NULL
Индексы:
"aircrafts_pkey" PRIMARY KEY, btree (aircraft_code)
Ограничения-проверки:
"aircrafts_range_check" CHECK (range > 0)
В этом выводе новым для вас может быть выражение public.aircrafts. В нем сло- во public означает имя так называемой схемы. Это, упрощенно говоря, раздел базы данных, в котором и создаются таблицы и другие объекты. По умолчанию исполь- зуется схема public. О схемах мы будем говорить более подробно в последующих главах пособия.
В описание таблицы входит также информация о созданных индексах. Индекс —
это специальная структура данных, позволяющая решать задачу ускорения доступа к строкам в таблице, а также задачу предотвращения дублирования значений клю- чевых атрибутов в различных строках таблицы. Для реализации первичного ключа
(PRIMARY KEY) всегда автоматически создается индекс. Имя индекса в наше случае —
aircrafts_pkey. Оно было сгенерировано ядром PostgreSQL. Указан также и тип индекса — btree, т. е. B-дерево. Далее в круглых скобках приводится список ключе- вых атрибутов. В нашем случае он состоит из одного атрибута — aircraft_code.
Далее в описании таблицы приводятся сведения об ограничениях, наложенных на отдельные атрибуты таблицы и на таблицу в целом. В принципе, при создании таб- лицы можно задать свои собственные имена для всех ограничений, однако делать это не обязательно. Мы не задавали никакого имени для ограничения, наложенного на
36
Глава 3. Основные операции с таблицами
атрибут range, поэтому ядро PostgreSQL также сгенерировало это имя автоматиче- ски — aircrafts_range_check.
Следует различать команды языка SQL и команды утилиты psql. Команды, начина- ющиеся с символа «\», являются командами, которые утилита psql предлагает для удобства пользователя.
Поскольку таблицы, которые мы будем сейчас создавать, очень простые, то в случае выявления какого-либо упущения при их создании вы можете просто удалить табли- цу и создать ее заново, с учетом необходимых исправлений. А команду ALTER TABLE,
предназначенную для модифицирования структуры таблиц, мы рассмотрим немного позднее. Поэтому прежде чем вы приступите к вводу данных, ознакомьтесь с команд- ной для удаления таблицы.
DROP TABLE имя-таблицы;
Теперь вы можете приступить к вводу данных в таблицу «Самолеты». Для выполне- ния этой операции служит команда INSERT. Ее упрощенный формат таков:
INSERT INTO имя-таблицы [( имя-атрибута, имя-атрибута, ... )]
VALUES ( значение-атрибута, значение-атрибута, ... );
В начале команды перечисляются атрибуты таблицы. При этом можно указывать их не в том порядке, в котором они были указаны при ее создании. Вы вовсе не обязаны помнить порядок атрибутов в команде CREATE TABLE. Обратите внимание на нали- чие квадратных скобок. Они указывают, что список атрибутов в команде не является обязательным, но при вводе команды квадратные скобки вводить не нужно. Одна- ко если вы не привели список атрибутов, тогда вы обязаны в предложении VALUES
задавать значения атрибутов с учетом того порядка, в котором они следуют в опре- делении таблицы. Конечно, такая форма записи команды является более короткой,
но она менее универсальна, т. к. в случае реструктуризации таблицы и изменения порядка столбцов в ее определении или добавления нового столбца (даже без из- менения порядка существующих столбцов) вам придется корректировать и команду
INSERT в ваших прикладных программах.
Давайте добавим одну строку в таблицу aircrafts. Обратите внимание на одинар- ные кавычки, в которые заключены значения атрибутов aircraft_code и model.
Для атрибутов символьных типов данных одинарные кавычки обязательны, а для числовых типов кавычки использовать не нужно.
INSERT INTO aircrafts ( aircraft_code, model, range )
VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 );
37