Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
Глава 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/. 25 Глава 2. Создание рабочей среды Если же вы остановили свой выбор на дистрибутиве Postgres Pro, тогда следует обра- титься сюда: https://postgrespro.ru/products/postgrespro/download/latest. После установки как PostgreSQL, так и Postgres Pro, в среде Windows придется пред- принять дополнительные меры, чтобы использование русского алфавита в интерак- тивном терминале psql не вызывало проблем. Утилита psql рассматривается в следу- ющем разделе. В процессе установки будет создана отдельная учетная запись пользователя СУБД с именем postgres. Для изучения настоящего пособия создавать дополнительные учетные записи не требуется. Установив тот или иной дистрибутив 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, но и различные сервисные команды, поддерживаемые самой утилитой. 26 2.3. Развертывание учебной базы данных Для получения краткой справки по всем сервисным командам нужно ввести \? Многие такие команды начинаются с символов «\d». Например, для того чтобы про- смотреть список всех таблиц и представлений (views), созданных в той базе данных, к которой вы сейчас подключены, введите команду \dt Если же вас интересует определение (попросту говоря, структура) какой-либо кон- кретной таблицы базы данных, например, students, нужно ввести команду \d students Для получения списка всех SQL-команд нужно выполнить команду \h Для вывода описания конкретной SQL-команды, например, CREATE TABLE, нужно сделать так: \h CREATE TABLE Эта утилита позволяет сокращать объем ручного ввода за счет дополнения вводи- мой команды «силами» psql. Например, при вводе SQL-команды можно использовать клавишу 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. Если вы решили прервать ввод команды, еще не введя ее полностью, то просто на- жмите клавиши 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 Глава 3. Основные операции с таблицами В ответ мы получим сообщение об успешном добавлении этой строки: INSERT 0 1 В этом сообщении числа 0 и 1 имеют конкретный смысл. Второе из них, т. е. 1, озна- чает количество добавленных строк — в данном случае была добавлена всего одна строка. А первое число 0 имеет отношение к внутреннему устройству PostgreSQL, ко- торое в нашем учебном пособии не рассматривается. Теперь уже можно выполнить выборку данных из таблицы aircrafts. Для выборки информации из таблиц базы данных служит команда SELECT. Ее синтаксис, упро- щенный до предела, таков: SELECT имя-атрибута, имя-атрибута, ... FROM имя-таблицы; Часто бывает так, что требуется вывести значения из всех столбцов таблицы. В таком случае можно не перечислять имена атрибутов, а просто ввести символ «∗». Давайте выберем всю информацию из таблицы aircrafts: SELECT * FROM aircrafts; aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3000 (1 строка) Давайте добавим еще несколько строк в таблицу aircrafts. Команда INSERT поз- воляет сделать это за один раз. Вспомните о том, что можно редактировать ранее введенную команду, вызвав ее на экран при помощи клавиши <↑>. Как и при вво- де предыдущих команд, вы можете выбрать один из двух способов ввода: ввести всю команду на одной строке, когда ее текст сворачивается «змейкой», либо вводить команду построчно, нажимая клавишу занимающего одну строку текста в пособии. INSERT INTO aircrafts ( aircraft_code, model, range ) VALUES ( '773', 'Boeing 777-300', 11100 ), ( '763', 'Boeing 767-300', 7900 ), ( '733', 'Boeing 737-300', 4200 ), ( '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 ); 38 Глава 3. Основные операции с таблицами СУБД сообщит об успешном вводе 8 строк в таблицу aircrafts. INSERT 0 8 Давайте снова посмотрим, что содержится в таблице «Самолеты». SELECT * FROM aircrafts; Теперь в ней уже 9 строк. aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3000 773 | Boeing 777-300 | 11100 763 | Boeing 767-300 | 7900 733 | Boeing 737-300 | 4200 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 (9 строк) Если сравнить порядок, в котором вы вводили строки в таблицу, с тем порядком, в котором строки выведены из нее по команде SELECT, то можно увидеть совпаде- ние этих порядков. Однако полагаться на такое совпадение нельзя, т. к. порядок мо- жет измениться. При выполнении простой выборки из таблицы СУБД не гарантирует никакого конкретного порядка вывода строк. Если же вы хотите каким-то образом упорядочить расположение выводимых строк, то необходимо предпринять допол- нительные меры, а именно: использовать предложение ORDER BY команды SELECT. Давайте упорядочим строки по значению атрибута model, а заодно изменим порядок расположения столбцов в выводе информации. Обратите внимание, что символьные значения при выводе выравниваются по левому краю столбца, а числовые значе- ния — по правому краю. SELECT model, aircraft_code, range FROM aircrafts ORDER BY model; model | aircraft_code | range ---------------------+---------------+------- Airbus A319-100 | 319 | 6700 Airbus A320-200 | 320 | 5700 Airbus A321-200 | 321 | 5600 39 Глава 3. Основные операции с таблицами Boeing 737-300 | 733 | 4200 Boeing 767-300 | 763 | 7900 Boeing 777-300 | 773 | 11100 Bombardier CRJ-200 | CR2 | 2700 Cessna 208 Caravan | CN1 | 1200 Sukhoi SuperJet-100 | SU9 | 3000 (9 строк) Далеко не всегда требуется выбирать все строки из таблицы. Множество выбираемых строк можно ограничить с помощью предложения WHERE команды SELECT. Давай- те выберем модели самолетов, у которых максимальная дальность полета находится в пределах от 4 до 6 тыс. км включительно. SELECT model, aircraft_code, range FROM aircrafts WHERE range >= 4000 AND range <= 6000; Условие выбора строк может быть составным. В данном случае мы скомбинировали два ограничения с помощью логической операции AND (т. е. «И»). model | aircraft_code | range -----------------+---------------+------- Boeing 737-300 | 733 | 4200 Airbus A320-200 | 320 | 5700 Airbus A321-200 | 321 | 5600 (3 строки) Теперь мы ознакомимся с командой UPDATE, предназначенной для обновления дан- ных в таблицах. Ее упрощенный синтаксис таков: UPDATE имя-таблицы SET имя-атрибута1 = значение-атрибута1, имя-атрибута2 = значение-атрибута2, ... WHERE условие; Условие, указываемое в команде, должно ограничить диапазон обновляемых строк. Если это условие не задать, то будут обновлены все строки в таблице. Если же вам требуется обновить лишь часть из них, то не забывайте указывать условие отбора строк для обновления. Давайте предположим, что российские инженеры немного улучшили летные харак- теристики самолета Sukhoi SuperJet, и теперь дальность его полета стала на 500 км больше. 40 Глава 3. Основные операции с таблицами UPDATE aircrafts SET range = 3500 WHERE aircraft_code = 'SU9'; СУБД выведет сообщение, подтверждающее успешное обновление одной строки: UPDATE 1 Давайте проверим, что получилось в результате обновления данных. SELECT * FROM aircrafts WHERE aircraft_code = 'SU9'; aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3500 (1 строка) Итак, мы добрались до операции удаления строк из таблиц. Для этого используется команда DELETE, которая похожа на команду SELECT: DELETE FROM имя-таблицы WHERE условие; Удалите какую-нибудь одну строку из таблицы «Самолеты»: DELETE FROM aircrafts WHERE aircraft_code = 'CN1'; СУБД сообщит об успешном удалении одной строки: DELETE 1 Вы можете указать и какое-нибудь более сложное условие. Давайте, например, уда- лим информацию о самолетах с дальностью полета более 10 000 км, а также с даль- ностью полета менее 3 000 км: DELETE FROM aircrafts WHERE range > 10000 OR range < 3000; При необходимости удаления всех строк из таблицы команда будет совсем простой: DELETE FROM aircrafts; Теперь в таблице «Самолеты» нет ни одной строки. Для продолжения работы необхо- димо эти данные восстановить. Можно использовать несколько способов. 1. Ввести заново команды INSERT из текста пособия, которые вы уже вводили. 2. Используя клавиши <↑> и <↓>, найти в списке истории команд введенные ра- нее команды INSERT и повторно их выполнить. 41 Глава 3. Основные операции с таблицами 3. С помощью специальной команды, предусмотренной в утилите psql, сохранить всю историю выполненных вами команд в текстовом файле: \s имя-файла-для-сохранения-истории-команд Затем нужно открыть его в текстовом редакторе, найти в файле нужные вам команды INSERT и, копируя команды в буфер обмена, вставить их в командную строку утили- ты psql и выполнить. В нашей учебной базе данных содержится несколько таблиц, связанных между собой. Для таблицы «Самолеты» (aircrafts) ближайшей «родственницей» является табли- ца «Места» (seats). Она имеет следующую структуру: Описание атрибута Имя атрибута Тип данных Тип PostgreSQL Ограничения Код самолета, IATA aircraft_code Символьный char( 3 ) NOT NULL Номер места seat_no Символьный varchar( 4 ) NOT NULL Класс обслуживания fare_conditions Символьный varchar( 10 ) NOT NULL Значения из списка: Economy, Comfort, Business Для атрибута «Код самолета, IATA» был выбран тип char(3), поскольку этот атрибут присутствует и в таблице «Самолеты». Значения атрибута «Номер места» (seat_no) состоят из числовой части, обозначаю- щей номер ряда кресел в салоне самолета, и латинской буквы, обозначающей пози- цию в ряду, начиная с буквы A. Например: 10A, 21D, 17F и т. д. В качестве типа данных для этого атрибута выберем varchar(4). Этот тип позволяет хранить любые симво- лы. В скобках указана предельная длина символьной строки, которую можно ввести в поле такого типа. Значения атрибута «Класс обслуживания» (fare_conditions) могут выбираться из ограниченного списка значений. Проверка на соответствие вводимых значений это- му списку будет обеспечиваться с помощью ограничения CHECK. Также выбираем тип данных varchar. Все допустимые значения имеют различные длины, но мы ориен- тируемся на самое длинное значение. Значения всех атрибутов каждой строки данной таблицы не должны быть неопреде- ленными, поэтому на них накладывается ограничение NOT NULL. В качестве первичного ключа выбрана комбинация атрибутов «Код самолета, IATA» и «Номер места» — это составной ключ. Таким образом, первичный ключ будет есте- ственным . Как уже было сказано выше, это означает, что и в реальной предметной 42 Глава 3. Основные операции с таблицами области существуют такие понятия, как код самолета и номер места, и эти понятия используются на практике. В этой таблице используется внешний ключ. Предложение FOREIGN KEY создает ограничение ссылочной целостности. В качестве внешнего ключа служит атрибут «Код самолета» (aircraft_code). Он ссылается на одноименный атрибут в табли- це «Самолеты» (aircrafts). Таблица «Места» называется ссылающейся (referencing), а таблица «Самолеты» — ссылочной (referenced). Поскольку номера мест привязаны к модели самолета, то в случае удаления из таб- лицы «Самолеты» какой-либо строки с конкретным кодом самолета необходимо уда- лить также и из таблицы «Места» все строки, в которых значение атрибута «Код са- молета» такое же. Коротко говоря, если в базе данных нет информации о какой-либо модели самолета, то не может быть и информации о компоновке салона, т. е. о рас- пределении мест по классам обслуживания для этой модели. Поэтому в предложении для определения внешнего ключа появляется важное допол- нение: ON DELETE CASCADE. Это означает, что при удалении какой-либо строки из таблицы «Самолеты» удаление строк из таблицы «Места», связанных с этой строкой по внешнему ключу, берет на себя СУБД, избавляя программиста от этой заботы. По- добные действия, которые выполняет сама СУБД, называются каскадным удалением. Таким образом, внешний ключ служит для связи таблиц между собой. Итак, команда для создания нашей второй таблицы «Места» такова: CREATE TABLE seats ( aircraft_code char( 3 ) NOT NULL, seat_no varchar( 4 ) NOT NULL, fare_conditions varchar( 10 ) NOT NULL, CHECK ( fare_conditions IN ( 'Economy', 'Comfort', 'Business' ) ), PRIMARY KEY ( aircraft_code, seat_no ), FOREIGN KEY ( aircraft_code ) REFERENCES aircrafts (aircraft_code ) ON DELETE CASCADE ); Для того чтобы посмотреть, какая получилась таблица, введите команду \d seats 43 Глава 3. Основные операции с таблицами Таблица "public.seats" Колонка | Тип | Модификаторы -----------------+-----------------------+-------------- aircraft_code | character(3) | NOT NULL seat_no | character varying(4) | NOT NULL fare_conditions | character varying(10) | NOT NULL Индексы: "seats_pkey" PRIMARY KEY, btree (aircraft_code, seat_no) Ограничения-проверки: "seats_fare_conditions_check" CHECK (fare_conditions::text = ANY (ARRAY['Economy'::character varying, 'Comfort'::character varying, 'Business'::character varying]::text[])) Ограничения внешнего ключа: "seats_aircraft_code_fkey" FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE Вы видите, что тип данных char имеет также и полное название — character, а тип данных varchar — character varying. Первичный ключ здесь составной — (aircraft_code, seat_no). Ограничение CHECK, накладываемое на значения атри- бута fare_conditions, представлено в более сложной форме, чем это было сделано при создании таблицы. Двойные символы «::» означают операцию приведения типа. Это аналогично такой же операции в других языках программирования. Ключевое слово ARRAY говорит о том, что список допустимых значений представлен в виде массива. Массивы присутствуют в PostgreSQL, и их использование в ряде ситуаций позволяет, например, упростить схему базы данных. Более подробно о них мы будем говорить в главе 4. Принципиально новым по сравнению с рассмотренной выше таблицей «Самоле- ты» является наличие ограничения внешнего ключа. Это ограничение имеет имя seats_aircraft_code_fkey, сгенерированное самой СУБД, поскольку мы не пред- ложили в команде CREATE TABLE никакого своего имени для этого ограничения, хотя, в принципе, имели право это сделать, если бы захотели. Для просмотра всех таблиц, имеющихся в вашей базе данных, выполните команду \d Список отношений Схема | Имя | Тип | Владелец --------+-----------+---------+---------- public | aircrafts | таблица | postgres public | seats | таблица | postgres (2 строки) 44 Глава 3. Основные операции с таблицами В первой колонке выведенной таблицы указана так называемая схема базы данных — public. Мы уже говорили, что схема — это обособленный до некоторой степени раз- дел базы данных. По умолчанию все объекты создаются в схеме public. В третьей колонке указан тип — «таблица». Кроме таблиц могут быть еще и представления. В последней колонке указано имя пользователя, являющегося владельцем таблицы. Как правило, это пользователь, создавший таблицу. Давайте сразу же проделаем эксперимент, позволяющий показать работу внешнего ключа. Выполните следующую команду для ввода данных в таблицу «Места»: INSERT INTO seats VALUES ( '123', '1A', 'Business' ); СУБД ответит так: ОШИБКА: INSERT или UPDATE в таблице "seats" нарушает ограничение внешнего ключа "seats_aircraft_code_fkey" ПОДРОБНОСТИ: Ключ (aircraft_code)=(123) отсутствует в таблице "aircrafts" Это совершенно логично: если в таблице «Самолеты», на которую ссылается таблица «Места», нет описания самолета с кодом самолета 123, то добавлять информацию о номерах кресел для такого — несуществующего — самолета не имеет смысла. Так действует поддержка правил ссылочной целостности со стороны СУБД. Программист избавлен от необходимости отслеживать и обеспечивать «вручную» соблюдение этих правил. Теперь нужно заполнить данными таблицу «Места». Для каждой модели самолетов введите только несколько строк, при этом предусмотрите записи для классов обслу- живания Business и Economy. С помощью одной команды INSERT можно ввести сразу несколько строк: INSERT INTO seats VALUES ( 'SU9', '1A', 'Business' ), ( 'SU9', '1B', 'Business' ), ( 'SU9', '10A', 'Economy' ), ( 'SU9', '10B', 'Economy' ), ( 'SU9', '10F', 'Economy' ), ( 'SU9', '20F', 'Economy' ); Затем измените значение атрибута aircraft_code на другое, например, 773, и по- вторите команду INSERT. Так придется поступить со всеми моделями самолетов. Таблица «Места» заполнена необходимыми данными. Теперь решим еще одну зада- чу. Предположим, что нам нужно получить информацию о количестве мест в салонах 45 Глава 3. Основные операции с таблицами для всех типов самолетов. Имея некоторый опыт в программировании на других язы- ках, нетрудно предположить, что в языке SQL должна присутствовать функция для подсчета количества строк в таблицах. Да, такая функция есть — это count. Конечно, для решения задачи, поставленной выше, в принципе можно воспользоваться таки- ми командами: SELECT count( * ) FROM seats WHERE aircraft_code = 'SU9'; SELECT count( * ) FROM seats WHERE aircraft_code = 'CN1'; ... Очевидно, что это нерациональный подход, поскольку придется выполнять отдель- ные однотипные команды для всех моделей самолетов. Язык SQL позволяет упро- стить решение такой задачи за счет применения операции группирования строк на основе некоторого критерия. Этим критерием будет являться совпадение значе- ний атрибута «Код самолета» (aircraft_code) у различных строк таблицы «Места» (seats). В модифицированной команде вместо предложения WHERE будет добавлено предло- жение GROUP BY, которое отвечает за группировку строк с одинаковыми значениями атрибута aircraft_code. Обратите внимание, что при наличии предложения GROUP BY агрегатная функция count выполняет подсчеты строк для каждой группы строк. SELECT aircraft_code, count( * ) FROM seats GROUP BY aircraft_code; Конечно, в вашей выборке значения в столбце count будут гораздо меньше. aircraft_code | count ---------------+------- 773 | 402 733 | 130 CN1 | 12 CR2 | 50 319 | 116 SU9 | 97 321 | 170 763 | 222 320 | 140 (9 строк) Если мы захотим отсортировать выборку по числу мест в самолетах, то нужно будет дополнить команду предложением ORDER BY, которое обеспечит сортировку резуль- тирующих строк по значениям второго столбца. 46 Глава 3. Основные операции с таблицами SELECT aircraft_code, count( * ) FROM seats GROUP BY aircraft_code ORDER BY count; aircraft_code | count ---------------+------- CN1 | 12 CR2 | 50 SU9 | 97 319 | 116 733 | 130 320 | 140 321 | 170 763 | 222 773 | 402 (9 строк) Теперь поставим более сложную задачу: подсчитать количество мест в салонах для всех моделей самолетов, но теперь уже с учетом класса обслуживания (бизнес-класс и экономический класс). В этом случае группировка выполняется уже по двум атри- бутам: aircraft_code и fare_conditions. Отсортируем выборку по тем же столб- цам, по которым выполняли группировку. SELECT aircraft_code, fare_conditions, count( * ) FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; aircraft_code | fare_conditions | count ---------------+-----------------+------- 319 | Business | 20 319 | Economy | 96 320 | Business | 20 320 | Economy | 120 (17 строк) 47 Глава 3. Основные операции с таблицами Контрольные вопросы и задания 1. Попробуйте ввести в таблицу aircrafts строку с таким значением атрибута «Код самолета» (aircraft_code), которое вы уже вводили, например: INSERT INTO aircrafts VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 ); Обратите внимание, что в этой команде мы не привели список атрибутов, что вполне допустимо при задании значений атрибутов в том же порядке, в котором атрибуты следуют в определении таблицы. Но в ваших прикладных программах так поступать все же не следует, поскольку в случае возможной реструктуриза- ции таблицы и изменения порядка следования атрибутов в ней ваши команды INSERT могут перестать работать корректно. Вы получите сообщение об ошибке. ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "aircrafts_pkey" ПОДРОБНОСТИ: Ключ "(aircraft_code)=(SU9)" уже существует. Подумайте, почему появилось сообщение. Если вы забыли структуру таблицы aircrafts, то можно вывести ее определение на экран с помощью команды \d aircrafts 2. Предложение ORDER BY команды SELECT позволяет отсортировать данные при выводе. По умолчанию сортировка выполняется по возрастанию значений ат- рибута, указанного в этом предложении. Но можно упорядочить строки и по убыванию значения атрибута. Для этого нужно после имени атрибута в пред- ложении ORDER BY добавить ключевое слово DESC (это сокращение от слова descendant — убывающий порядок). Самостоятельно напишите команду для вы- борки всех строк из таблицы aircrafts, чтобы строки были упорядочены по убы- ванию значения атрибута «Максимальная дальность полета, км» (range). 3. Команда UPDATE позволяет в процессе обновления выполнять арифметические действия над значениями, находящимися в строках таблицы. Представим себе, что двигатели самолета Sukhoi SuperJet стали в два раза экономичнее, вслед- ствие чего дальность полета этого лайнера возросла ровно в два раза. Команда UPDATE позволяет увеличить значение атрибута range в строке, хранящей ин- формацию об этом самолете, даже не выполняя предварительно выборку с це- лью выяснения текущего значения этого атрибута. При присваивании нового 48 Контрольные вопросы и задания значения атрибуту range можно справа от знака «=» написать не только чис- ловую константу, но и целое выражение. В нашем случае оно будет простым: range = range * 2. Самостоятельно напишите команду UPDATE полностью, при этом не забудьте, что увеличить дальность полета нужно только у одной моде- ли — Sukhoi SuperJet, поэтому необходимо использовать условие WHERE. Затем с помощью команды SELECT проверьте полученный результат. 4. Если в предложении WHERE команды DELETE вы укажете логически и синтак- сически корректное условие, но строк, удовлетворяющих этому условию, в таб- лице не окажется, то в ответ СУБД выведет сообщение DELETE 0 Такая ситуация не является ошибкой или сбоем в работе СУБД. Например, ес- ли после удаления какой-то строки вы повторно попытаетесь удалить ее же, то получите именно такое сообщение. Самостоятельно смоделируйте описанную ситуацию, подобрав условие, кото- рому гарантированно не соответствует ни одна строка в таблице «Самолеты» (aircrafts). 49 |