Где выполняются программные модули, написанные на процедурных расширениях SQL, входящих в состав СУБД?
Где хранятся программные модули, написанные на процедурных расширениях SQL, входящих в состав СУБД?
Модули процедурного расширения SQL могут быть следующих типов:
Процедурное расширение T-SQL используется в СУБД:
Процедурное расширение PL/SQL используется в СУБД:
Какое процедурное расширение используется в СУБД Oracle?
Какое процедурное расширение не используется в СУБД Oracle?
Какое процедурное расширение не используется в СУБД SQL Server?
Какое процедурное расширение используется в СУБД SQL Server?
Базу данных, декомпозированную и фрагментированную на несколько узлов вычислительной сети, с возможным управлением различными СУБД называют:
Какое процедурное расширение используется в СУБД Oracle?
Какое процедурное расширение используется в СУБД SQL Server?
Какое процедурное расширение не используется в СУБД SQL Server?
Процедурное расширение PL/SQL используется в СУБД:
Процедурное расширение T-SQL используется в СУБД:
База данных Oracle — это:
Сервер ORACLE состоит из следующих частей:
Существуют следующие виды областей памяти Oracle:
Где выполняются программные модули, написанные на процедурных расширениях SQL, входящих в состав СУБД?
Где хранятся программные модули, написанные на процедурных расширениях SQL, входящих в состав СУБД?
Организация баз данных
Только сегодня: скидка до 20% в подарок на первый заказ.
Какую работу нужно написать?
Другую работу
Помощник Анна
| 4.3 CASE-средства | 81 |
CASE-средство Silverrun — разработка фирмы Сomputer Systems Advisers , Inc . (США). Silverrun может быть использовано для анализа и проектирования информационных систем бизнес-класса. Оно ориентировано в большей степени на спиральную модель ЖЦ, а также применимо для поддержки любой методологии, основанной на раздельном построении функциональной и информационной моделей (диаграмм потоков данных и диаграмм «сущность-связь»). Модуль концептуального моделирования данных ERX ( Entity-Relationship eXpert ) обеспечивает построение моделей данных «сущность-связь», не привязанных к конкретной реализации. Этот модуль имеет встроенную экспертную систему, позволяющую создать корректную нормализованную модель данных посредством ответов на содержательные вопросы о взаимосвязи данных. Возможно автоматическое построение модели данных из описаний структур данных. Анализ функциональных зависимостей атрибутов дает возможность проверить соответствие модели требованиям третьей нормальной формы и обеспечить их выполнение. Проверенная модель передается в модуль RDM. Модуль реляционного моделирования RDM ( Relational Data Modeler ) позволяет создавать детализированные модели «сущность-связь», предназначенные для реализации в реляционной базе данных. В этом модуле документируются все конструкции, связанные с построением базы данных: индексы, триггеры, хранимые процедуры и т. д. Гибкая изменяемая нотация и расширяемость репозитория позволяют работать по любой методологии. Возможность создавать подсхемы соответствует подходу ANSI SPARC к представлению схемы базы данных. На языке подсхем моделируются как узлы распределенной обработки, так и пользовательские представления. Этот модуль обеспечивает проектирование и полное документирование реляционных БД. Менеджер репозитория рабочей группы WRM ( Workgroup Repository Manager ) применяется как словарь данных для хранения общей для всех моделей информации, а также обеспечивает интеграцию модулей Silverrun в единую среду проектирования. Для автоматической генерации схем баз данных у Silverrun существуют средства интеграции с наиболее распространенными СУБД: Oracle, Informix, DB2, Ingres, Progress, SQL Server, SQL-Base, Sybase. Это позволяет документировать, перепроектировать или переносить на новые платформы уже находящиеся в эксплуатации базы данных и прикладные системы. Таким образом, можно полностью определить ядро базы данных с использованием всех возможностей конкретной СУБД: триггеров, хранимых процедур, ограничений ссылочной целостности. Для обмена данными с другими средствами автоматизации проектирования, создания специализированных процедур анализа и проверки проектных спецификаций, составления специализированных отчетов в соответствии с различными стандартами в системе Silverrun имеются различные способы выдачи. Vantage Team Builder представляет собой интегрированный программный продукт, ориентированный на реализацию каскадной модели жизненного цикла (ЖЦ) программного обеспечения и поддержку полного ЖЦ ПО. Vantage Team Builder обеспечивает выполнение следующих функций: • проектирования диаграмм потоков данных, ER-диаграмм, структур данных, структурных схем программ и последовательностей экранных форм;
82 Глава 4. Технология проектирования реляционных баз данных • проектирования диаграмм архитектуры системы (SAD-проектирования состава и связи вычислительных средств, распределения задач системы между вычислительными средствами, моделирования отношений типа «клиентсервер», анализа использования менеджеров транзакций и особенностей функционирования систем в реальном времени); • генерации кода программ на языке 4GL целевой СУБД с полным обеспечением программной среды и генерации SQL-кода для создания таблиц БД, индексов, ограничений целостности и хранимых процедур; • программирования на языке C со встроенным SQL; • управления версиями и конфигурацией проекта; • многопользовательского доступа к репозиторию проекта; • генерации проектной документации по стандартным и индивидуальным шаблонам; • экспорта и импорта данных проекта в формате CDIF ( CASE Data Interchange Format ). При построении модели данных в виде ER-диаграммы в среде Vantage Team Builder выполняется ее нормализация и вводится определение физических имен элементов данных и таблиц, которые будут использоваться в процессе генерации физической схемы данных конкретной СУБД. Обеспечивается возможность определения альтернативных ключей сущностей и полей, составляющих дополнительные точки входа в таблицу (поля индексов), и мощности отношений между сущностями. AllFusion ERwin Data Modeler от разработчика Computer Associates предназначено для моделирования и создания баз данных произвольной сложности на основе диаграмм «сущность-связь». В настоящее время AllFusion ERwin Data Modeler является наиболее популярным пакетом моделирования данных благодаря поддержке широкого спектра СУБД самых различных классов: SQL-серверов (Oracle, Informix, Sybase SQL Server, MS SQL Server, Progress, DB2, SQLBase, Ingress, Rdb и др.) и «настольных» СУБД типа XBase (Clipper, dBase, FoxPro, MS Access, Paradox и др.). Информационная модель представляется в виде диаграмм «сущ- ность-связь», отражающих основные объекты предметной области и связи между ними. Дополнительно определяются атрибуты сущностей, характеристики связей, индексы и бизнес-правила, описывающие ограничения и закономерности предметной области. После создания ER-диаграммы пакет автоматически генерирует SQL-код для создания таблиц, индексов и других объектов базы данных. По заданным бизнес-правилам формируются стандартные триггеры БД для поддержки целостности данных. Для сложных бизнес-правил можно создавать собственные триггеры, используя библиотеку шаблонов. Пакет позволяет осуществлять реинжиниринг существующих БД: по SQL-текс- там автоматически генерируются ER-диаграммы. Таким образом, в пакете поддерживается технология FRE ( Forward and Reverse Engineering ), последовательность этапов которой приведена ниже: • импорт с сервера существующей БД; • автоматическая генерация модели БД;
| 4.3 CASE-средства | 83 |
• модификация модели; • автоматическая генерация новой схемы и построение физической БД на том же самом или любом другом сервере. Для разработки клиентской части приложения имеются специальные версии пакета, обеспечивающие интеграцию с такими инструментами, как SQLWindows, PowerBuilder, Visual Basic, Delphi. Предлагаются и усеченные версии продукта: • ERWin/SQL, обеспечивающая лишь прямое проектирование для любых СУБД; • ERWin/Desktop, поддерживающая технологию FRE только для «настольных» СУБД. S-Designor представляет собой CASE-средство для проектирования реляционных баз данных. По своим функциональным возможностям и стоимости он близок к CASE-средству ERwin, отличаясь внешне используемой на диаграммах нотацией. S-Designor реализует стандартную методологию моделирования данных и генерирует описание БД для таких СУБД, как Oracle, Informix, Ingres, Sybase, DB/2, Microsoft SQL Server и др. Для существующих систем выполняется реинжиниринг БД. S-Designor совместим с рядом средств разработки приложений (PowerBuilder, Uniface, TeamWindows и др.) и позволяет экспортировать описание БД в репозитории данных средств. Для PowerBuilder выполняется также прямая генерация шаблонов приложений. Visible Analyst Workbench фирмы Visible Systems представляет собой сетевое многопользовательское средство проектирования информационных систем, базирующееся на репозитарии, хранимом на сервере SQLBase, Oracle или Informix. Пакет основан на методологии Мартина и поддерживает следующие диаграммные техники: • диаграммы функциональной декомпозиции; • диаграммы потоков данных в нотациях Йодана и Гейна-Сарсона; • диаграммы «сущность-связь»; • структурные карты в нотации Константайна. Пакет обеспечивает генерацию схем БД для вышеперечисленных СУБД и поддерживает технологию FRE. Имеется возможность экспорта проектов в системы SQLWindows, PowerBuilder и Uniface. К достоинствам пакета может быть отнесено наличие развитых средств верификации проекта и, прежде всего, возможностей вертикального и горизонтального балансирования диаграмм. Так, функциональная и информационная модели сильно коррелированны, что позволяет избавиться от лишних объектов моделей. PowerDesigner компании Sybase — средство моделирования масштаба предприятия, объединяющего технологический и бизнес-уровни моделирования для обеспечения максимально эффективного взаимодействия между бизнес- и IT-пользова- телями. В состав PowerDesigner входят следующие модули: • Process Analyst — средство для функционального моделирования, поддерживает нотацию Йордона-ДеМарко, Гейна-Сарсона и несколько других. Имеется возможность описать элементы данных (имена, типы, форматы),
| 84 | Глава 4. Технология проектирования реляционных баз данных |
связанные с потоками данных и хранилищами данных. Эти элементы передаются на следующий этап проектирования, причем хранилища данных могут быть автоматически преобразованы в сущности; • Data Analyst (Architect) — инструмент для построения модели «сущностьсвязь» и автоматической генерации на ее основе реляционной структуры. Исходные данные для модели «сущность-связь» могут быть получены из DFD-моделей, созданных в модуле Process Analyst. В ER-диаграммах допускаются только бинарные связи, задание атрибутов у связей не поддерживается. Поддерживаются диалекты языка SQL примерно для 30-ти реляционных СУБД, при этом могут быть сгенерированы таблицы, представления, индексы, триггеры и т. д. В результате порождается SQL-сценарий (последовательность команд CREATE), выполнение которого создает спроектированную схему базы данных. Имеется также возможность установить соединение с СУБД через интерфейс ODBC. Другие возможности: автоматическая проверка правильности модели, расчет размера базы данных, реинжиниринг (построение модельных диаграмм для уже существующих баз данных) и т. д.; • Application Modeler — инструмент для автоматической генерации прототипов программ обработки данных на основе реляционных моделей, построенных в Data Analyst. Может быть получен код для Visual Basic, Delphi, а также для таких систем разработки в архитектуре «клиент-сервер», как PowerBuilder, Uniface, Progress и др. Генерация кода осуществляется на основе шаблонов, соответственно управлять генерацией можно за счет изменения соответствующего шаблона. Начиная с версии 9.5, PowerDesigner позволяет согласовывать объектно-ориен- тированную и концептуальную модели данных, ориентированную на реляционные СУБД. PowerDesigner позволяет генерировать физическую структуру БД на основе спроектированной концептуальной модели для большинства современных СУБД (Oracle, Informix, Ingres, Sybase, MS SQL Server и др.). Design/IDEF фирмы Meta Software Corp . — продукт, предназначенный для автоматизиции всех этапов проектирования сложных систем различного назначения: формулировки требований и целей проектирования, разработки спецификаций, определения компонентов и взаимодействий между ними, документирования проекта, проверки его полноты и непротиворечивости. Наиболее успешно пакет применяется для описания и анализа деятельности предприятия. Он позволяет оценить такую структуру, как единый организм, сочетающий управленческие, производственные и информационные процессы. В основе пакета лежит методология структурного проектирования и анализа сложных систем IDEFO/SADT. Design/IDEF строит иерархические модели сложных систем посредством декомпозиции ее компонентов, поддерживает коллективную разработку IDEF-модели, позволяя в любой момент объединять различные подмодели в единую модель системы, создает словарь данных для хранения всей информации о функциях и структурах данных проекта; формирует пять типов отчетов, поддерживающих процесс разработки и анализа моделей.
| Контрольные вопросы по главе 4 | 85 |
Disign/IDEF также интегрирован с пакетом динамического анализа сложных систем WorkFlow Analyzer и пакетом функционально-стоимостного анализа EasyABC. В последнее время CASE-средства становятся все более унифицированными, появляются дополнительные возможности по реализации сложных многоуровневых моделей, механизмы реинжиниринга и анализа моделей. Аналитики и разработчики баз данных имеют возможность выбора — купить мощное средство моделирования (PowerDesigner, ERwin и др.) или воспользоваться бесплатными, свободно распространяемыми продуктами, но с меньшим функционалом (MySQL Workbench, Dia, SQLyog). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Контрольные вопросы по главе 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1. Дайте определения основных понятий реляционной модели данных. 2. Приведите требования удовлетворения отношений нормальным формам (1NF, 2NF, 3NF). 3. Опишите технологию семантического моделирования предметной области в терминах ER-модели. 4. Дайте определение понятий сущности и связи в ER-модели. 5. Перечислите и кратко охарактеризуйте наиболее известные CASE-средства. 6. Опишите основные функции приведенных в обзоре CASE-средств.
Глава 5 ЯЗЫКИ УПРАВЛЕНИЯ И МАНИПУЛИРОВАНИЯ ДАННЫМИ 5.1 Язык SQL 5.1.1 История развития языка Как отмечалось выше, при работе с БД необходимо осуществлять доступ к данным и управлять ими. Таким образом, важным требованием к реляционным СУБД является наличие языка, позволяющего выполнять все необходимые пользователям операции. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . В процессе эволюции СУБД стало очевидно, что любой язык, ис- пользуемый для управления данными в реляционной базе данных , должен предоставлять разработчику и пользователю следующие основные функциональные возможности: • создавать в базе данных объекты с возможностью полноценного описания их структурной составляющей; • формировать запросы различных уровней сложности на выборку необходимых данных из таблиц БД; • формировать запросы к базе данных, с помощью которых обеспечивается выполнение основных операций по манипулированию данными (вставка, модификация и удаление данных из таблиц). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Кроме выделенных функциональных возможностей, язык, предназначенный для работы с реляционными базами данных, должен позволять обеспечивать их выполнение с минимальными усилиями со стороны разработчика, при этом син-
| 5.1 Язык SQL | 87 |
таксис команд этого языка и процесс формирования запросов должны быть достаточно просты и доступны для изучения разработчикам разных уровней подготовки. Также, беря во внимание большое количество СУБД, существующих на рынке, такой язык должен быть в меру универсальным, соответствовать некоторому стандарту, что позволит использовать один и тот же синтаксис и структуру команд (с минимальными изменениями) при написании запросов в разных СУБД. В процессе эволюции СУБД наибольшее распространение получили два языка управления и манипулирования данными: • язык структурированных запросов SQL ( Structured Query Language ), созданный фирмой IBM в начале 70-х годов XX века. В первоначальном варианте язык получил название SEQUEL ( Structured English Query Language ) и был разработан для созданной фирмой IBM СУБД System/R. Позднее язык получил свое современное название — SQL; • запрос по образцу QBE ( Query-by-Example ), созданный фирмой IBM в Йорк- таун-Хейтсе. Наибольшее распространение в процессе эволюции СУБД получил язык SQL, хотя на начальном этапе более перспективным считался язык QBE, как наиболее близкий к пользовательскому интерфейсу. В настоящее время язык SQL, расширенный возможностью визуального проектирования запросов с использованием технологии QBE, используется в большинстве современных СУБД. Первыми СУБД, поддерживающими SQL, стали СУБД Oracle V2 в 1979 году от компании Relational Software Inc . (впоследствии ставшей компанией Oracle ) и System/38 от IBM, основанная на базе СУБД System/R. Помимо операторов формулирования запросов к базе данных и возможностей манипулирования данными, язык SQL содержит большое количество функциональных средств, например: • средства определения схемы БД и манипулирования схемой; • операторы для определения ограничений целостности и триггеров; • средства определения представлений БД; • средства авторизации доступа к отношениям и их полям; • средства управления транзакциями. 5.1.2 Стандарты языка SQL С течением времени выявилась основная проблема использования языка SQL — у разных производителей СУБД используются разные диалекты SQL, часто между собой несовместимые. Так, запрос на выборку, созданный и прекрасно выполняющийся в СУБД MS Access, может выдать ошибки и не запускаться в СУБД Oracle, и наоборот. Фактически, различные СУБД предоставляют возможность выполнения программных конструкций, записанных в разных диалектах и процедурных расширениях (дополнительных возможностях, позволяющих, например, реализовать процедуры и функции) языка SQL. Существует достаточное количество публикаций по различным диалектам и процедурным расширениям языка SQL. Отметим, что
| 88 | Глава 5. Языки управления и манипулирования данными |
первый официальный стандарт языка SQL был принят ANSI ( American National Standards Institute ) в 1986 году и ISO (Международной организацией по стандартизации) в 1987 году (названный SQL-86). До 1996 года вопросами соответствия диалектов SQL в конкретных СУБД официальному стандарту занимался в основном Национальный институт стандартов и технологий (NIST). Начиная со стандарта SQL-92, ANSI и NIST определили четыре уровня соответствия конкретной реализации SQL этому стандарту: • Entry (базовый); • Transitional (переходный) — проверку на соответствие этому уровню проводил только институт NIST; • Intermediate (промежуточный); • Full (полный). Любая компания-разработчик СУБД, в случае, если в этой СУБД поддерживался синтаксис команд уровня Entry, могла заявлять себя как «SQL-92 compliant» — соответствующей стандарту SQL-92, хотя на самом деле переносимость и соответствие стандарту ограничивалось набором возможностей выполнения запросов, входящих в этот базовый уровень. С появлением и утверждением стандарта SQL:1999 стандарт языка SQL (ANSI SQL) приобрел модульную структуру, в которой основная часть стандарта была вынесена в раздел «SQL/Foundation», а все остальные были выведены в отдельные модули. В таблице 5.1 представлены основные версии стандартов языка SQL [16]. Таблица 5.1 – Основные версии стандартов SQL
| Год | Название | Альтернативное | Описание изменений | ||
| утверждения | название | стандарта | |||
| 1986 | SQL-86 | SQL-87 | Первый вариант стандарта, при- | ||
| нятый институтом ANSI и одоб- | |||||
| ренный ISO в 1987 году | |||||
| 1989 | SQL-89 | FIPS 127-1 | Небольшое изменение, в котором | ||
| важным дополнением были огра- | |||||
| ничения целостности | |||||
| 1992 | SQL-92 | SQL2, FIPS 127-2 | Значительные | изменения (ISO | |
| 9075); уровень Entry Level стан- | |||||
| дарта SQL-92 принят как стан- | |||||
| дарт FIPS 127-2 | |||||
| 1999 | SQL:1999 | SQL3 | Добавлена поддержка | регуляр- | |
| ных выражений, рекурсивных за- | |||||
| просов (например, транзитивное | |||||
| замыкание), | поддержка | тригге- | |||
| ров, базовые процедурные рас- | |||||
| ширения, нескалярные типы дан- | |||||
| ных и некоторые объектно-ори- | |||||
| продолжение на следующей странице | |||||
| 5.1 Язык SQL | 89 |
Таблица 5.1 — Продолжение
| Год | Название | Альтернативное | Описание изменений | |||||
| утверждения | название | стандарта | ||||||
| ентированные возможности | (на- | |||||||
| пример, структурные типы). Под- | ||||||||
| держка | для | встраивания | SQL | |||||
| в Java, и наоборот | ||||||||
| 2003 | SQL:2003 | SQL2003 | Введены расширения для работы | |||||
| с XML-данными, оконные функ- | ||||||||
| ции (применяемые | для | работы | ||||||
| с OLAP-базами данных), генера- | ||||||||
| торы последовательностей и ос- | ||||||||
| нованные на них типы данных ат- | ||||||||
| рибутов таблиц | ||||||||
| 2006 | SQL:2006 | SQL2006 | Функциональность работы с XML- | |||||
| данными значительно расширена. | ||||||||
| Определены | способы импорта | |||||||
| и хранения XML-данных в базе | ||||||||
| данных SQL. Появилась возмож- | ||||||||
| ность | совместно | использовать | ||||||
| в запросах SQL и XQuery (язык | ||||||||
| запросов XML) | ||||||||
| 2008 | SQL:2008 | SQL2008 | Улучшены | возможности | окон- | |||
| ных функций, устранены неко- | ||||||||
| торые неоднозначности стандар- | ||||||||
| та SQL:2003 | ||||||||
| 2011 | SQL:2011 | Улучшены функции по | работе | |||||
| с временными базами данных | ||||||||
Разнообразие использования различных диалектов процедурного расширения стандарта языка ANSI SQL (SQL/PSM — SQL/Persistent Stored Modules) в наиболее известных СУБД представлено в таблице 5.2. Таблица 5.2 – Основные версии стандартов SQL
| СУБД | Сокращенное | Полное наименование и описание |
| наименование | ||
| Interbase/Firebird | PSQL | Procedural SQL |
| IBM DB2 | SQL PL | SQL Procedural Language (реализова- |
| ны базовые возможности SQL/PSM) | ||
| IBM Informix | SPL | Stored Procedural Language |
| IBM Netezza | NZPLSQL | Основан на Postgres PL/pgSQL |
| Microsoft/Sybase | T-SQL | Transact-SQL |
| Mimer SQL | SQL/PSM | SQL/Persistent Stored Module (реализо- |
| ваны базовые возможности SQL/PSM) | ||
| продолжение на следующей странице |
90 Глава 5. Языки управления и манипулирования данными Таблица 5.2 — Продолжение
| СУБД | Сокращенное | Полное наименование и описание |
| наименование | ||
| MySQL | SQL/PSM | SQL/Persistent Stored Module (реализо- |
| ваны базовые возможности SQL/PSM) | ||
| NuoDB | SSP | Starkey Stored Procedures |
| Oracle | PL/SQL | Procedural Language/SQL (основан на |
| синтаксисе языка Ada) | ||
| PostgreSQL | PL/pgSQL | Procedural Language/PostgreSQL (осно- |
| ван на языке Oracle PL/SQL) | ||
| PostgreSQL | PL/PSM | Procedural Language/Persistent Stored |
| Modules (реализованы базовые воз- | ||
| можности SQL/PSM) | ||
| Sybase | Watcom-SQL | SQL Anywhere Watcom-SQL Dialect |
| Teradata | SPL | Stored Procedural Language |
| SAP | SAP HANA | SQL Script |
5.1.3 Описание основных команд SQL Что касается синтаксиса запросов и предложений, написанных на языке SQL, то в большинстве работ по описанию команд SQL авторы придерживаются общего синтаксиса написания SQL-предложений: 1) в описании команд слова, написанные прописными латинскими буквами, являются зарезервированными словами SQL; 2) фрагменты SQL-предложений, заключенные в фигурные скобки и разделенные символом « | », являются альтернативными. При формировании соответствующей команды для конкретного случая необходимо выбрать одну из них; 3) фрагмент описываемого SQL-предложения, заключенный в квадратные скобки [ ] , имеет необязательный характер и может не использоваться; 4) многоточие перед закрывающейся скобкой говорит о том, что фрагмент, указанный в этих скобках, может быть повторен. На рисунке 5.1 схематично представлен SQL-запрос на выборку данных из таблицы «Студент». Мы остановимся на описании основных команд языка SQL в диалекте языка, используемого в СУБД MS Access (Microsoft Jet SQL), который имеет некоторые отличия от стандарта ANSI SQL, о которых будет сказано ниже. Однако освоив создание SQL запросов в одной СУБД, вы без особых усилий сможете применить свои знания при реализации запросов в других СУБД. Создание новой таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Инструкция CREATE TABLE создает новую таблицу и используется для описания ее полей и индексов. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
PostgreSQL. Основы языка SQL
Учебно-практическое пособие охватывает первую, базовую, часть учебного курса по языку SQL, созданного при участии российской компании Postgres Professional. Учебный материал излагается в расчете на использование системы управления базами данных PostgreSQL. Рассмотрено создание рабочей среды, описаны языки определения данных и основные операции выборки и изменения данных. Показаны примеры использования транзакций, уделено внимание методам оптимизации запросов. Материал сопровождается многочисленными практическими примерами. Пособие может использоваться как для самостоятельного обучения, так и при проведении занятий под руководством преподавателя.
Издательство:
БХВ-Петербург
5977540221
9785977540223
PDF, 42.51 MB
QmTrvpkeVmpRQMxGvBSoQMfgdfwCKEoRTAtnrgJ5YAbJAW
IPFS CID blake2b:
bafykbzacecqqvrzslahtbucs6lllxczigc4rqdxvppanb7y6muomkchwxlvfa
russian, 2018
Начните свое путешествие в мир знаний! Ознакомьтесь с Предпросмотром и откройте другие возможности
Вас может заинтересовать
Ключевые слова
Связанные Подборки
Основы технологий баз данных
PDF, 28.37 MB
russian, 2019
Windows Help Advice — Christmas 2016
PDF, 15.34 MB
english, 2016
Компания Postgres Professional Е. П. Моргунов PostgreSQL. Основы языка SQL Учебное пособие Санкт-Петербург «БХВ-Петербург» 2018 УДК 004.655 ББК 32.973.26-018.2 М79 Моргунов, Е. П. М79 PostgreSQL. Основы языка SQL: учеб. пособие / Е. П. Моргунов; под ред. Е. В. Рогова, П. В. Лузанова. — СПб.: БХВ-Петербург, 2018. — 336 с.: ил. ISBN 978-5-9775-4022-3 Учебно-практическое пособие охватывает первую, базовую, часть учебного курса по языку SQL, созданного при участии российской компании Postgres Professional. Учебный материал излагается в расчете на использование системы управления базами данных PostgreSQL. Рассмотрено создание рабочей среды, описаны языки определения данных и основные операции выборки и изменения данных. Показаны примеры использования транзакций, уделено внимание методам оптимизации запросов. Материал сопровождается многочисленными практическими примерами. Пособие может использоваться как для самостоятельного обучения, так и при проведении занятий под руководством преподавателя. Для программистов и студентов УДК 004.655 ББК 32.973.26-018.2 Рецензент: Б. А. Новиков, д-р. физ.-мат. наук, профессор СПбГУ ISBN 978-5-9775-4022-3 ISBN 978-5-6041193-2-7 © ООО «ППГ», 2018 Оглавление Предисловие автора 5 Введение 7 Глава 1. Введение в базы данных и SQL 1.1. Что такое базы данных и зачем они нужны . . . . . . . . 1.2. Основные понятия реляционной модели . . . . . . . . . 1.3. Что такое язык SQL . . . . . . . . . . . . . . . . . . . . . . 1.4. Описание предметной области и учебной базы данных Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 13 15 18 19 23 Глава 2. Создание рабочей среды 2.1. Установка СУБД . . . . . . . . . . . . . . . . . . . . . . . . 2.2. Программа psql — интерактивный терминал PostgreSQL 2.3. Развертывание учебной базы данных . . . . . . . . . . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .; . . . . . . . . . . . . . . . . . . . . . . . . . 25 25 26 27 29 Глава 3. Основные операции с таблицами 31 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Глава 4. Типы данных СУБД PostgreSQL 4.1. Числовые типы . . . . . . . . . 4.2. Символьные (строковые) типы 4.3. Типы «дата/время» . . . . . . . 4.4. Логический тип . . . . . . . . . 4.5. Массивы . . . . . . . . . . . . . 4.6. Типы JSON . . . . . . . . . . . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 51 54 56 63 64 68 73 Глава 5. Основы языка определения данных 5.1. Значения по умолчанию и ограничения целостности 5.2. Создание и удаление таблиц . . . . . . . . . . . . . . 5.3. Модификация таблиц . . . . . . . . . . . . . . . . . . 5.4. Представления . . . . . . . . . . . . . . . . . . . . . . 5.5. Схемы базы данных . . . . . . . . . . . . . . . . . . . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 95 105 117 123 131 133 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Глава 6. Запросы 145 6.1. Дополнительные возможности команды SELECT . . . . . . . . . . . . . . 145 6.2. Соединения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 3 Оглавление 6.3. Агрегирование и группировка . . . . . . . . . . . . . . . . . . . . . . . . . 168 6.4. Подзапросы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 Глава 7. Изменение данных 7.1. Вставка строк в таблицы . . . 7.2. Обновление строк в таблицах 7.3. Удаление строк из таблиц . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 211 219 224 226 Глава 8. Индексы 8.1. Общая информация . . . . . . . . 8.2. Индексы по нескольким столбцам 8.3. Уникальные индексы . . . . . . . . 8.4. Индексы на основе выражений . . 8.5. Частичные индексы . . . . . . . . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241 241 246 247 248 248 250 Глава 9. Транзакции 9.1. Общая информация . . . . . . . . . . 9.2. Уровень изоляции Read Uncommitted 9.3. Уровень изоляции Read Committed . . 9.4. Уровень изоляции Repeatable Read . . 9.5. Уровень изоляции Serializable . . . . . 9.6. Пример использования транзакций . 9.7. Блокировки . . . . . . . . . . . . . . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 255 259 261 265 269 275 278 280 Глава 10. Повышение производительности 10.1. Основные понятия . . . . . . . . . . . . . . . . . . . 10.2. Методы просмотра таблиц . . . . . . . . . . . . . . . 10.3. Методы формирования соединений наборов строк 10.4. Управление планировщиком . . . . . . . . . . . . . 10.5. Оптимизация запросов . . . . . . . . . . . . . . . . Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 293 296 302 305 311 317 . . . . . . . . Рекомендуемые источники 328 Предметный указатель 329 4 Предисловие автора С системой управления базами данных PostgreSQL мне довелось познакомиться в 1998 году, это была еще версия 6.2. Важную роль в моей встрече с PostgreSQL сыграл Вадим Михеев, который в те годы был одним из ключевых разработчиков этой СУБД. После перехода в 2000 году на преподавательскую работу в вуз мне потребовалась свободно распространяемая СУБД для использования в учебном процессе. На эту роль идеально подходила СУБД PostgreSQL. Книга, которую вы держите в руках, появилась по инициативе и при поддержке компании Postgres Professional — российского поставщика СУБД PostgreSQL. Летом 2016 года я встретился с директором компании Олегом Бартуновым. Он высказал идею о том, что для распространения СУБД PostgreSQL необходим учебник по языку SQL. PostgreSQL является самой продвинутой свободно распространяемой СУБД с очень хорошей поддержкой стандарта SQL. Тем не менее существуют и специфические черты, присущие именно PostgreSQL, поэтому такой учебник должен был отражать эту специфику. Поскольку я уже много лет использовал PostgreSQL в преподавании в вузе, то Олег Бартунов предложил мне заняться написанием такого учебника. Это предложение показалось мне очень интересным. Мы обсудили план будущего учебника с Павлом Лузановым и Егором Роговым, которые занимаются образовательными программами компании. Они согласились выступить в трудной и ответственной роли редакторов книги. Координировали работу над учебником и помогали на разных стадиях этого трудного процесса Олег Бартунов и заместитель директора компании Иван Панченко. Оригинальную обложку книги сделал Андрей Климковский, а Егор Рогов подготовил предметный указатель, благодаря которому книгой будет удобно пользоваться. Вообще, без поддержки компании Postgres Professional учебник в таком виде не смог бы состояться. Данный учебник рекомендуется использовать в комплексе с учебником по основам технологий баз данных, который написан профессором Санкт-Петербургского государственного университета Борисом Асеновичем Новиковым также при поддержке компании Postgres Professional. Важным является то, что в обоих учебниках для иллюстрации приемов применения языка SQL используется одна и та же учебная база данных «Авиаперевозки», разработанная Павлом Лузановым и Егором Роговым. Она содержит данные, по своим свойствам близкие к реальным. Это позволяет в качестве упражнений моделировать ситуации, которые могли бы иметь место в реальной работе специалиста по базам данных. 5 Предисловие автора Знанием только языка SQL квалификация специалиста по базам данных не исчерпывается, поэтому на сайте компании Postgres Professional представлен целый ряд «фирменных» учебных курсов по PostgreSQL. Ссылка на них приведена в конце книги в разделе «Рекомендуемые источники». В планах автора и компании Postgres Professional — выпуск второй части учебника. Она будет посвящена более сложным вопросам использования языка SQL в среде СУБД PostgreSQL. Предполагается рассмотреть такие темы, как хранимые функции и процедуры, язык PL/pgSQL, триггеры, полнотекстовый поиск, расширяемость PostgreSQL. Е. П. Моргунов Введение В настоящее время термин «база данных» известен многим людям, даже далеким от профессиональной разработки компьютерных программ. Базы данных стали очень широко распространенной технологией, что потребовало, в свою очередь, большего числа специалистов, способных проектировать их и обслуживать. В ходе эволюции теории и практики баз данных стандартом де-факто стала реляционная модель данных, а в рамках этой модели сформировался и специализированный язык программирования, позволяющий выполнять все необходимые операции с данными — Structured Query Language (SQL). Таким образом, важным компонентом квалификации специалиста в области баз данных является владение языком SQL. В настоящем учебном пособии излагаются основы языка SQL — это базовый курс. Причем язык рассматривается применительно к конкретной системе управления базами данных (СУБД) — PostgreSQL. Реализация языка SQL в каждой СУБД соответствует стандарту в той или иной степени, но кроме стандартизированных функций и возможностей, каждая СУБД предлагает и свои дополнительные расширения языка. PostgreSQL обеспечивает очень хорошую поддержку стандарта языка SQL и также предоставляет интересные и практически полезные дополнительные возможности. Одним из главных достоинств PostgreSQL является расширяемость. Это означает, например, что пользователь (конечно, являющийся специалистом в области баз данных) может разработать свои собственные типы данных. Эти типы данных будут обладать всеми свойствами встроенных типов данных и могут быть введены в работу без останова сервера. Кроме того, PostgreSQL является свободно распространяемым продуктом с открытым исходным кодом, который доступен на большом числе платформ. В пособии рассматриваются не только все основные команды языка SQL, но также и другие вопросы, такие как индексы и транзакции. Книга написана таким образом, чтобы ее можно было использовать как под руководством преподавателя, так и самостоятельно. Предполагается, что студенты имеют доступ к уже установленной СУБД, поэтому процедура установки PostgreSQL детально не рассматривается, а лишь даются указания о том, где найти инструкции по установке. Это пособие предназначено для получения практических навыков использования языка SQL. Учебный материал подается таким образом, что многие важные знания 7 Введение читатель должен получить в результате выполнения заданий, находящихся в конце каждой главы. В основном тексте глав эти знания могут быть не представлены. Предполагается, что значительная часть заданий будет выполняться читателем самостоятельно с помощью документации на СУБД PostgreSQL, но зачастую даются и указания к их выполнению. Задания, приведенные в пособии, различаются по уровню сложности. Самые сложные из них, а также те, которые требуют много времени для выполнения, отмечены звездочкой. Задания можно выполнять по мере изучения учебного материала конкретной главы. Однако некоторые из них имеют комплексный характер, поэтому для их выполнения необходимо изучить всю главу или, как минимум, несколько ее разделов. Хотя пособие имеет практическую направленность и не является теоретическим курсом, все же в первой главе кратко, на элементарном уровне излагаются основные понятия теории баз данных и реляционной модели. Это сделано для того, чтобы студенты могли приступить к практическому освоению языка SQL без задержки, с первых дней учебного семестра, еще до того момента, когда эти понятия будут основательно рассмотрены в лекционном курсе. На факультетах информационных технологий в российских вузах базы данных традиционно изучаются на втором или третьем курсе. Причем этой дисциплине, как правило, отводится один семестр. Однако количество академических учебных часов может различаться. Если на практические занятия по этой дисциплине учебный план отводит 36 часов, тогда мы рекомендуем следующее распределение времени на изучение материала пособия. Глава 1. Введение в базы данных и SQL Глава 2. Создание рабочей среды Глава 3. Основные операции с таблицами Глава 4. Типы данных СУБД PostgreSQL Глава 5. Основы языка определения данных Глава 6. Запросы Глава 7. Изменение данных Глава 8. Индексы Глава 9. Транзакции Глава 10. Повышение производительности 1 час 1 час 4 часа 4 часа 4 часа 8 часов 4 часа 2 часа 4 часа 4 часа Главы 1 и 2 могут быть изучены за одно двухчасовое занятие, поскольку система PostgreSQL уже должна быть установлена в учебной аудитории заранее. Глава 3 представляет собой краткий обзор основных возможностей языка SQL, после ее изучения студенты должны представлять себе простые способы использования 8 Введение всех основных команд языка. Эта глава не очень сложная, но объемная, поэтому на ее изучение отводится четыре часа. Глава 4 посвящена рассмотрению основных типов данных, используемых в СУБД PostgreSQL. Это большая глава, однако в ней значительную часть составляют задания и упражнения. Предполагается, что студенты за четыре часа должны усвоить только основные приемы использования типов данных. А для того, чтобы знания закрепились, рекомендуется обращаться к материалу этой главы (в том числе и к упражнениям) в процессе изучения остальных глав пособия при необходимости уточнения тех или иных особенностей применения конкретных типов. Распределить время, выделенное на изучение этой главы, мы рекомендуем следующим образом: два часа на первые четыре раздела — числовые и строковые типы, типы «дата/время» и логический тип, еще два часа — на массивы и тип json/jsonb. Чтобы выполнять запросы к базе данных, необходимо хорошо понимать ее структуру, взаимосвязи таблиц. Поэтому глава 5, в которой рассматриваются основы языка определения данных, очень важна с точки зрения детального изучения таблиц базы данных «Авиаперевозки» и подготовки к освоению главы 6. Поскольку материал главы основан на том, что база данных уже развернута на компьютере студента, то вводить команды для создания таблиц не требуется. Это позволяет сократить время, затрачиваемое на изучение главы. В пособии принят подход, при котором сначала рассматриваются команды определения данных, а затем — команды манипулирования данными. Поэтому глава 5 «Основы языка определения данных» предшествует главе 6 «Запросы». Однако избранный подход реализуется не слишком жестко: в обзорной главе 3 рассматриваются основные команды, в том числе и несложные запросы. А запросы — это уже язык манипулирования данными. На изучение главы 5 отведено четыре часа. В течение первого двухчасового занятия нужно изучить два первых раздела, в которых освещаются такие вопросы, как ограничения целостности и создание и удаление таблиц. Второе двухчасовое занятие нужно посвятить изучению трех оставшихся разделов. В них говорится о способах модификации таблиц, а также о представлениях (views) и схемах базы данных. Глава 6 является центральной главой пособия, поэтому на ее изучение отводится восемь часов, т. е. больше, чем на изучение других глав. Она состоит из четырех разделов. Первый из них посвящен разнообразным дополнительным возможностям команды SELECT. Речь идет, в частности, о таких вещах, как предложения LIMIT и OFFSET, оператор LIKE и регулярные выражения в условиях предложения WHERE и о других возможностях. Тем не менее материал этого раздела несложный, для его изучения достаточно выделить один час. Во втором разделе рассказывается о способах соединения таблиц. Это более сложная тема, поэтому для ее изучения необходимо выделить два часа. Третий раздел посвящен агрегированию и группировке. В нем 9 Введение рассматривается и такая важная и интересная тема, как оконные функции. Данный раздел также требует двухчасового занятия. Самый сложный раздел этой главы — четвертый. Он посвящен подзапросам. В нем, в частности, освещается такая важная и интересная тема, как общие табличные выражения (Common Table Expressions — CTE). Для изучения материала данного раздела необходимо выделить три часа. В главе 7 собраны все команды, предназначенные для изменения данных: вставка строк, их обновление и удаление. Поскольку в предшествующих главах эти команды уже применялись для решения простых задач, то в данной главе рассматриваются более сложные способы их использования. В ней много упражнений, они составляют половину ее объема. Рекомендуется уделить два часа изучению способов вставки строк в таблицы, а еще два часа — рассмотрению операций обновления и удаления строк. Глава 8 посвящена индексам, она небольшая, поэтому с ней можно ознакомиться за одно двухчасовое занятие. Поскольку индексы тесно связаны с вопросами производительности, т. е. скорости выполнения запросов, то было бы целесообразно после изучения заключительной главы вернуться к главе 8 и посмотреть на представленные в ней команды и запросы, уже зная о команде EXPLAIN. Транзакциям посвящена глава 9. Механизмы их выполнения имеют много тонкостей, поэтому при изучении этой главы необходимо экспериментировать и стараться объяснить полученные результаты. В заключительной главе 10 рассматриваются вопросы повышения производительности. Эта глава может показаться слишком абстрактной и сложной для начального курса языка SQL, тем не менее она очень важна. Студенты должны научиться читать планы выполнения запросов и понимать назначение каждой операции, представленной в плане. А овладение искусством оптимизации запросов потребует много времени и опыта, оно придет не сразу. В том случае, когда на практические занятия по дисциплине «Базы данных» в учебном плане отводится 54 часа, можно изменить предлагаемое распределение учебных часов. В частности, в главе 4 можно больше времени посвятить типам данных json/jsonb и массивам. В главе 6 можно более детально рассмотреть оконные функции и общие табличные выражения. При изучении главы 9, посвященной транзакциям, было бы целесообразно разработать несложное приложение, в котором использовались бы транзакции, и провести эксперименты с этим приложением, выполняя параллельно несколько сеансов и изменяя при этом уровни изоляции транзакций. В рамках главы 10 имеет смысл вернуться к командам и запросам главы 8 и изучить 10 Введение планы их выполнения с помощью команды EXPLAIN. За счет дополнительного времени можно рассмотреть все задания и упражнения повышенной сложности (помеченные звездочкой). Таким образом, распределение времени может быть таким: Глава 1. Введение в базы данных и SQL Глава 2. Создание рабочей среды Глава 3. Основные операции с таблицами Глава 4. Типы данных СУБД PostgreSQL Глава 5. Основы языка определения данных Глава 6. Запросы Глава 7. Изменение данных Глава 8. Индексы Глава 9. Транзакции Глава 10. Повышение производительности 1 час 1 час 4 часа 6 часов 6 часов 12 часов 6 часов 4 часа 8 часов 6 часов В пособии используются различные виды шрифтов для выделения фрагментов текста в зависимости от их назначения. Команды, вводимые пользователем как в среде операционной системы, так и в среде утилиты psql, выделяются полужирным моноширинным шрифтом. Например: psql -d demo -U postgres или SELECT avg( total_amount ) FROM bookings; Результаты работы команд операционной системы и SQL-команд, выполняемых в среде утилиты psql, напечатаны моноширинным шрифтом. Например, в ответ на команду EXPLAIN SELECT * FROM aircrafts; на экран будет выведено следующее: QUERY PLAN ---------------------------------------------------------Seq Scan on aircrafts (cost=0.00..1.09 rows=9 width=52) (1 строка) Мы надеемся, что изучение материала, изложенного в учебном пособии, будет способствовать повышению уровня вашей квалификации и расширению профессионального кругозора. 11 Глава 1 Введение в базы данных и SQL Эта глава — вводная. В ней мы расскажем об основах баз данных, о том, что такое реляционная модель и зачем нужен язык SQL. Очень важной темой этой главы станет описание предметной области, на основе которой будет спроектирована учебная база данных, которая и будет служить в качестве площадки для изучения языка SQL. Это пособие предназначено в первую очередь для практического освоения языка SQL, а не для изучения теории баз данных, поэтому для изучения теории необходимо обращаться к авторитетным источникам, список которых приведен в конце учебного пособия. 1.1. Что такое базы данных и зачем они нужны Технологии баз данных существовали не всегда. Однако и до их внедрения в практику люди также собирали и обрабатывали данные. Одним из способов хранения данных были так называемые плоские файлы (flat files), которые имели очень простую структуру: данные хранились в виде записей, разделенных на поля фиксированной длины. В реальной жизни между элементами данных зачастую возникают сложные связи, которые необходимо перенести и в электронную базу данных. При использовании плоских файлов эти связи организовать сложно, а еще сложнее поддерживать их при изменениях и удалениях отдельных элементов данных. Одним из основных понятий в теории баз данных является модель данных. Можно сказать, что она характеризует способ организации данных и основные методы доступа к ним. Сначала были предложены иерархическая и сетевая модели данных. Однако в ходе эволюции теорий и идей была разработана реляционная модель данных, которая сейчас и является доминирующей. Поэтому в настоящее время преобладают базы данных реляционного типа. Их характерной чертой является тот факт, что данные воспринимаются пользователем как таблицы. В распоряжении пользователя имеются операторы для выборки данных из таблиц, а также для вставки новых данных, обновления и удаления имеющихся данных. Одним из достоинств реляционной базы данных является ее способность поддерживать связи между элементами данных, избавляя программиста от необходимости заниматься этой рутинной и очень трудоемкой работой. В те времена, когда технологии 13 Глава 1. Введение в базы данных и SQL реляционных баз данных еще не получили широкого распространения, программистам приходилось на процедурных языках вручную реализовывать такие операции, которые сейчас называются каскадным обновлением внешних ключей или каскадным удалением записей из подчиненных таблиц (файлов). Здесь слово «вручную» означает, что для выполнения этих операций приходилось писать код, состоящий из элементарных команд, позволяющий добраться до каждой обновляемой или удаляемой записи. Тот подход к работе с базами данных назывался навигационным — программист указывал программе конкретный алгоритм поиска записей. Приведем в качестве примера простую ситуацию: в базе данных, построенной на основе файлов, хранится информация о студентах и их экзаменационных оценках, причем личные данные студентов хранятся в одном файле, назовем его условно «Студенты», а экзаменационные оценки — в другом файле, который условно назовем «Успеваемость». Если требуется удалить информацию о конкретном студенте и его экзаменационных оценках, то придется не только выполнить операцию удаления конкретной записи из файла «Студенты», но дополнительно организовать цикл для поиска и удаления тех записей из файла «Успеваемость», у которых ключевое поле имеет то же значение, что и поле в удаляемой записи из файла «Студенты». Работая с реляционными базами данных, программист избавлен от программирования на «атомарном» уровне, потому что современные языки для «общения» с этими базами данных являются декларативными. Это означает, что для получения результата достаточно лишь указать, что нужно получить, но не требуется предписывать способ получения результата, т. е. как его получить. Система баз данных — это компьютеризированная система, предназначенная для хранения, переработки и выдачи информации по запросу пользователей. Такая система включает в себя программное и аппаратное обеспечение, сами данные, а также пользователей. Современные системы баз данных являются, как правило, многопользовательскими. В таких системах одновременный доступ к базе данных могут получить сразу несколько пользователей. Основным программным обеспечением является система управления базами данных. По-английски она называется database management system (DBMS). Кроме СУБД в систему баз данных могут входить утилиты, средства для разработки приложений (программ), средства проектирования базы данных, генераторы отчетов и др. Пользователи систем с базами данных подразделяются на ряд категорий. Первая категория — это прикладные программисты. Вторая категория — это конечные пользователи, ради которых и выполняется вся работа. Они могут получить доступ к базе 14 1.2. Основные понятия реляционной модели данных, используя прикладные программы или универсальные приложения, которые входят в программное обеспечение самой СУБД. В большинстве СУБД есть так называемый процессор языка запросов, который позволяет пользователю вводить команды языка высокого уровня (например, языка SQL). Третья категория пользователей — это администраторы базы данных. В их обязанности входят: создание базы данных, выбор оптимальных режимов доступа к ней, разграничение полномочий различных пользователей на доступ к той или иной информации в базе данных, выполнение резервного копирования базы данных и т. д. Систему баз данных можно разделить на два главных компонента: сервер и набор клиентов (или внешних интерфейсов). Сервер — это и есть СУБД. Клиентами являются различные приложения, написанные прикладными программистами, или встроенные приложения, поставляемые вместе с СУБД. Один сервер может обслуживать много клиентов. Современные СУБД включают в себя словарь данных. Это часть базы данных, которая описывает сами данные, хранящиеся в ней. Словарь данных помогает СУБД выполнять свои функции. 1.2. Основные понятия реляционной модели В каждой технологической сфере есть своя терминология. Существуют базовые термины, на которых основываются все дальнейшие рассуждения. Такие термины присутствуют и в сфере баз данных. Сейчас мы кратко о них поговорим. В эпоху, предшествующую рождению реляционной теории, базы данных традиционно рассматривались как набор файлов, состоящих из записей, а записи, в свою очередь, подразделялись на отдельные поля. Поле являлось элементарной единицей данных. В реляционных базах данных пользователь воспринимает данные в виде таблиц. Поэтому термину «файл» соответствует термин «таблица», вместо термина «запись» используется термин «строка», а вместо термина «поле» — термин «столбец» (или «колонка»). Таким образом, таблицы состоят из строк и столбцов, на пересечении которых должны находиться «атомарные» значения, которые нельзя разбить на более мелкие элементы без потери смысла. В формальной теории реляционных баз данных эти таблицы называют отношениями (relations) — поэтому и базы данных называются реляционными. Отношение — 15 Глава 1. Введение в базы данных и SQL это математический термин. При определении свойств таких отношений используется теория множеств. В терминах данной теории строки таблицы будут называться кортежами (tuples), а колонки — атрибутами. Отношение имеет заголовок, который состоит из атрибутов, и тело, состоящее из кортежей. Количество атрибутов называется степенью отношения, а количество кортежей — кардинальным числом. Кроме теории множеств, одним из оснований реляционной теории является такой раздел математической логики, как исчисление предикатов. Таким образом, в теории и практике баз данных существует три группы терминов. Иногда термины из разных групп используют в качестве синонимов, например, запись и строка. Как мы уже сказали выше, в реляционных базах данных пользователь воспринимает данные в виде таблиц. Рассмотрим простую систему, в которой всего две таблицы. Первая — «Студенты»: № зачетной книжки Ф. И. О. Серия документа Номер документа 55500 Иванов Иван Петрович 0402 645327 55800 Климов Андрей Иванович 0402 673211 55865 Новиков Николай Юрьевич 0202 554390 И вторая — «Успеваемость»: Зачетная книжка Предмет Учебный год Семестр Оценка 55500 Физика 2016/2017 1 5 55500 Математика 2016/2017 1 4 55800 Физика 2016/2017 1 4 55800 Физика 2016/2017 2 3 При работе с базами данных часто приходится следовать различным ограничениям, которые могут быть обусловлены спецификой конкретной предметной области. Упрощая реальную ситуацию, примем следующие ограничения: – номер зачетной книжки состоит из пяти цифр и не может быть отрицательным (в разных вузах используются различные схемы присваивания номеров зачетным книжкам, эти схемы могут быть гораздо сложнее принятой нами и могут учитывать, например, год поступления студента в вуз); – серия документа, удостоверяющего личность, является четырехзначным числом, а номер документа, удостоверяющего личность, — шестизначным числом; 16 1.2. Основные понятия реляционной модели – номер семестра может принимать только два значения — 1 (осенний семестр) и 2 (весенний семестр); – оценка может принимать только три значения — 3 (удовлетворительно), 4 (хорошо) и 5 (отлично): другие оценки выставлять в зачетные книжки не принято. Для идентификации строк в таблицах и для связи таблиц между собой используются так называемые ключи. Потенциальный ключ — это комбинация атрибутов таблицы, позволяющая уникальным образом идентифицировать строки в ней. Ключ может состоять только лишь из одного атрибута таблицы. Например, в таблице «Студенты» таким идентификатором может быть атрибут «Номер зачетной книжки». В качестве потенциального ключа данной таблицы могут также служить два ее атрибута, взятые вместе: «Серия документа, удостоверяющего личность» и «Номер документа, удостоверяющего личность». Ни один из них в отдельности не может использоваться в качестве уникального идентификатора. В таком случае ключ будет составным. При этом важным является то, что потенциальный ключ должен быть неизбыточным, т. е. никакое подмножество атрибутов, входящих в него, не должно обладать свойством уникальности. Потенциальный ключ, включающий два упомянутых атрибута, является неизбыточным. Ключи нужны для адресации на уровне строк (записей). При наличии в таблице более одного потенциального ключа один из них выбирается в качестве так называемого первичного ключа, а остальные будут являться альтернативными ключами. Рассмотрим таблицы «Студенты» и «Успеваемость». Предположим, что в таблице «Студенты» нет строки с номером зачетной книжки 55900, тогда включать строку с таким номером зачетной книжки в таблицу «Успеваемость» не имеет смысла. Таким образом, значения столбца «Номер зачетной книжки» в таблице «Успеваемость» должны быть согласованы со значениями такого же столбца в таблице «Студенты». Атрибут «Номер зачетной книжки» в таблице «Успеваемость» является примером того, что называется внешним ключом. Таблица, содержащая внешний ключ, называется ссылающейся таблицей (referencing table). Таблица, содержащая соответствующий потенциальный ключ, называется ссылочной (целевой) таблицей (referenced table). В таких случаях говорят, что внешний ключ ссылается на потенциальный ключ в ссылочной таблице. Внешний ключ может быть составным, т. е. может включать более одного атрибута. Внешний ключ не обязан быть уникальным. Проблема обеспечения того, чтобы база данных не содержала неверных значений внешних ключей, известна как проблема ссылочной целостности. Ограничение, согласно которому значения внешних ключей должны соответствовать значениям потенциальных ключей, называется ограничением ссылочной целостности (ссылочным ограничением). 17 Глава 1. Введение в базы данных и SQL Обеспечением выполнения ограничений ссылочной целостности занимается СУБД, а от разработчика требуется лишь указать атрибуты, служащие в качестве внешних ключей. При проектировании баз данных часто предусматривается, что при удалении строки из ссылочной таблицы соответствующие строки из ссылающейся таблицы должны быть также удалены, а при изменении значения столбца, на который ссылается внешний ключ, должны быть изменены значения внешнего ключа в ссылающейся таблице. Этот подход называется каскадным удалением (обновлением). Иногда применяются и другие подходы. Например, вместо удаления строк из ссылающейся таблицы в этих строках просто заменяют значения атрибутов, входящих во внешний ключ, так называемыми NULL-значениями. Это специальные значения, означающие «ничто» или отсутствие значения, они не совпадают со значением «нуль» или «пустая строка». NULL-значение применяется в базах данных и в качестве значения по умолчанию, когда пользователь не ввел никакого конкретного значения. Первичные ключи не могут содержать NULL-значений. Транзакция — одно из важнейших понятий теории баз данных. Она означает набор операций над базой данных, рассматриваемых как единая и неделимая единица работы, выполняемая полностью или не выполняемая вовсе, если произошел какойто сбой в процессе выполнения транзакции. Таким образом, транзакции являются средством обеспечения согласованности данных. В нашей базе данных транзакцией могут быть, например, две операции: удаление строки из таблицы «Студенты» и удаление связанных по внешнему ключу строк из таблицы «Успеваемость». 1.3. Что такое язык SQL Язык SQL — это непроцедурный язык, который является стандартным средством работы с данными во всех реляционных СУБД. Операторы (команды), написанные на этом языке, лишь указывают СУБД, какой результат должен быть получен, но не описывают процедуру получения этого результата. СУБД сама определяет способ выполнения команды пользователя. В языке SQL традиционно выделяются группа операторов определения данных (Data Definition Language — DDL), группа операторов манипулирования данными (Data Manipulation Language — DML) и группа операторов, управляющих привилегиями доступа к объектам базы данных (Data Control Language — DCL). К операторам языка определения данных (DDL) относятся команды для создания, изменения и удаления таблиц, представлений и других объектов базы данных. Детальному рассмотрению этих команд посвящены главы 5 и 8. 18 1.4. Описание предметной области и учебной базы данных К операторам языка манипулирования данными (DML) относятся команды для выборки строк из таблиц, вставки строк в таблицы, обновления и удаления строк. Эти команды подробно рассматриваются в главах 6 и 7. Операторы DCL в пособии не рассматриваются, т. к. PostgreSQL позволяет на начальном этапе изучения языка SQL обойтись без их использования. 1.4. Описание предметной области и учебной базы данных Чтобы показать все основные возможности языка SQL, нам потребуется база данных. Эта база данных не должна быть слишком сложной, чтобы ее изучение не потребовало слишком много времени. Но вместе с тем она должна быть достаточно разнообразной, чтобы запросы к ней выглядели бы правдоподобными, почти такими же, как и в реальной работе. В качестве предметной области выберем пассажирские авиаперевозки. Ее оригинальное описание и описание базы данных «Авиаперевозки» можно найти по адресам https://postgrespro.ru/education/demodb и https://postgrespro.ru/docs/postgrespro/ current/demodb-bookings.html. Надеемся, что эта область знакома многим читателям нашего учебного пособия. Конечно, в учебных целях реальная ситуация намеренно упрощена, но все принципиальные вещи сохранены. Итак, некая российская авиакомпания выполняет пассажирские авиаперевозки. Она обладает своим парком самолетов различных моделей. Каждая модель самолета имеет определенный код, который присваивает Международная ассоциация авиаперевозчиков (IATA). При этом будем считать, что самолеты одной модели имеют одинаковые компоновки салонов, т. е. порядок размещения кресел и нумерацию мест в салонах бизнес-класса и экономического класса. Например, если это модель Sukhoi SuperJet-100, то место 2A относится к бизнес-классу, а место 20D — к экономическому классу. Бизнес-класс и экономический класс — это разновидности так называемого класса обслуживания. Наша авиакомпания выполняет полеты между аэропортами России. Каждому аэропорту присвоен уникальный трехбуквенный код, при этом используются только заглавные буквы латинского алфавита. Эти коды присваивает не сама авиакомпания, а специальные организации, управляющие пассажирскими авиаперевозками. Зачастую название аэропорта не совпадает с названием того города, которому этот аэропорт принадлежит. Например, в городе Новосибирске аэропорт называется Толмачево, в городе Екатеринбурге — Кольцово, а в Санкт-Петербурге — Пулково. К тому же 19 Глава 1. Введение в базы данных и SQL некоторые города имеют более одного аэропорта. Сразу в качестве примера вспоминается Москва с ее аэропортами Домодедово, Шереметьево и Внуково. Добавим еще одну важную деталь: каждый аэропорт характеризуется географическими координатами — долготой и широтой, а также часовым поясом. Формируются маршруты перелетов между городами. Конечно, каждый такой маршрут требует указания не только города, но и аэропорта, поскольку, как мы уже сказали, в городе может быть и более одного аэропорта. В качестве упрощения реальности мы решим, что маршруты не будут иметь промежуточных посадок, т. е. у них будет только аэропорт отправления и аэропорт назначения. Каждый маршрут имеет шестизначный номер, включающий цифры и буквы латинского алфавита. На основе перечня маршрутов формируется расписание полетов (или рейсов). В расписании указывается плановое время отправления и плановое время прибытия, а также тип самолета, выполняющего этот рейс. При фактическом выполнении рейса возникает необходимость в учете дополнительных сведений, а именно: фактического времени отправления и фактического времени прибытия, а также статуса рейса. Статус рейса может принимать ряд значений: – Scheduled (за месяц открывается возможность бронирования); – On Time (за сутки открывается регистрация); – Delayed (рейс задержан); – Departed (вылетел); – Arrived (прибыл); – Cancelled (отменен). Теперь обратимся к пассажирам. Полет начинается с бронирования авиабилета. В настоящее время общепринятой практикой является оформление электронных билетов. Каждый такой билет имеет уникальный номер, состоящий из 13 цифр. В рамках одной процедуры бронирования может быть оформлено несколько билетов, но каждая такая процедура имеет уникальный шестизначный номер (шифр) бронирования, состоящий из заглавных букв латинского алфавита и цифр. Кроме того, для каждой процедуры бронирования записывается дата бронирования и рассчитывается общая стоимость оформленных билетов. В каждый билет, кроме его тринадцатизначного номера, записывается идентификатор пассажира, а также его имя и фамилия (в латинской транскрипции) и контактные данные. В качестве идентификатора пассажира используется номер документа, удостоверяющего личность. Конечно, пассажир может сменить свой документ, а иной 20 1.4. Описание предметной области и учебной базы данных раз даже фамилию и имя, за время, прошедшее между бронированием билетов в разные дни, поэтому невозможно наверняка сказать, что какие-то конкретные билеты были оформлены на одного и того же пассажира. В каждый электронный билет может быть вписано более одного перелета. Специалисты называют эти записи о перелетах сегментами. В качестве примера наличия нескольких сегментов можно привести такой: Красноярск — Москва, Москва — Анапа, Анапа — Москва, Москва — Красноярск. При этом возможно в рамках одного бронирования оформить несколько билетов на различных пассажиров. Для каждого перелета указывается номер рейса, аэропорты отправления и назначения, время вылета и время прибытия, а также стоимость перелета. Кроме того, указывается и так называемый класс обслуживания: экономический, бизнес и др. Когда пассажир прибывает в аэропорт отправления и проходит регистрацию билета, оформляется так называемый посадочный талон. Этот талон связан с авиабилетом: в талоне указывается такой же номер, который имеет электронный авиабилет данного пассажира. Кроме того, в талоне указывается номер рейса и номер места в самолете. Указывается также и номер посадочного талона — последовательный номер, присваиваемый в процессе регистрации билетов на данный рейс. Напомним, что каждому креслу в салоне самолета соответствует конкретный класс обслуживания. Данная информация учитывается при регистрации билетов и оформлении посадочных талонов. Если, например, пассажир приобрел билет с экономическим классом обслуживания, то в его посадочном талоне будет указан номер места в салоне экономического класса, но не в салоне бизнес-класса. Следуя приведенному описанию предметной области, можно спроектировать модельную базу данных, предназначенную для изучения языка SQL. Поскольку наше учебное пособие в первую очередь предназначено для изучения языка SQL, а не для овладения искусством проектирования баз данных, то мы приведем лишь диаграмму схемы данных, на которой показаны сущности, выделенные в предметной области, а также их связи и атрибуты. Конкретные же типы данных, первичные и внешние ключи, а также ограничения, наложенные на атрибуты и таблицы, мы покажем уже в последующих главах в процессе рассмотрения команд SQL, предназначенных для физического создания таблиц в базе данных. Приведенную ниже схему можно найти на сайте компании Postgres Professional по адресам, указанным в начале этого раздела. 21 Глава 1. Введение в базы данных и SQL Airports Аэропорты Bookings Бронирования # ∗ ∗ ∗ ∗ ∗ # book_ref ∗ book_date ∗ total_amount airport_code airport_name city longitude latitude timezone Flights Рейсы Tickets Билеты # ∗ ∗ ∗ ◦ ticket_no book_ref passenger_id passenger_name contact_data Ticket_flights Перелеты # # ∗ ∗ ticket_no flight_id fare_conditions amount Boarding_passes Посадочные талоны # # ∗ ∗ 22 ticket_no flight_id boarding_no seat_no # ∗ ∗ ∗ ∗ ∗ ∗ ∗ ◦ ◦ flight_id flight_no scheduled_departure scheduled_arrival departure_airport arrival_airport status aircraft_code actual_departure actual_arrival Aircrafts Самолеты # aircraft_code ∗ model ∗ range Seats Места # aircraft_code # seat_no ∗ fare_conditions Контрольные вопросы и задания Контрольные вопросы и задания 1. Какие группы операторов выделяются в составе языка SQL? 2. Дайте неформальное определение основных понятий реляционной модели данных: отношение, кортеж, атрибут. 3. Для чего нужны внешние ключи в реляционных таблицах? 4. Что такое потенциальный ключ? 5.* Предложите пример избыточного потенциального ключа для одной из таблиц базы данных «Авиаперевозки» и объясните, почему он будет избыточным. 6.* В реализации базы данных «Авиаперевозки» предполагается, что самолеты одной модели могут иметь только одну компоновку салона. Представим, что руководством принято решение о том, что нужно учитывать возможность наличия различных компоновок для каждой модели. Какие таблицы придется модифицировать в таком случае и каким образом? Потребуется ли создавать дополнительные таблицы? 23 Глава 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-команды можно использовать клавишу для дополнения вводимого ключевого слова команды или имени таблицы базы данных. Например, при вводе команды 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 ) ); Второй способ заключается в построчном вводе команды точно так же, как она напечатана в тексте главы. При этом после ввода каждой строки нужно нажимать клавишу . Обратите внимание, что до тех пор, пока команда не введена полностью, вид приглашения к вводу команд, выводимого утилитой psql, будет отличаться от первоначального. В конце команды необходимо поставить точку с запятой. 34 Глава 3. Основные операции с таблицами demo=# demo-# demo(# demo(# demo(# demo(# 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 предлагаются и другие способы завершения вводимых команд с целью их последующего выполнения. Например, вместо ввода символа «;» команду можно завершить символами «\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 Глава 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 Boeing 767-300 Boeing 777-300 Bombardier CRJ-200 Cessna 208 Caravan Sukhoi SuperJet-100 (9 строк) | | | | | | 733 763 773 CR2 CN1 SU9 | 4200 | 7900 | 11100 | 2700 | 1200 | 3000 Далеко не всегда требуется выбирать все строки из таблицы. Множество выбираемых строк можно ограничить с помощью предложения WHERE команды SELECT. Давайте выберем модели самолетов, у которых максимальная дальность полета находится в пределах от 4 до 6 тыс. км включительно. SELECT model, aircraft_code, range FROM aircrafts WHERE range >= 4000 AND 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 ( 'SU9', ( 'SU9', ( 'SU9', ( 'SU9', ( 'SU9', ( 'SU9', seats VALUES '1A', 'Business' '1B', 'Business' '10A', 'Economy' '10B', 'Economy' '10F', 'Economy' '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 Глава 4 Типы данных СУБД PostgreSQL После первоначального знакомства с языком SQL имеет смысл немного упорядочить полученные вами знания. Речь идет о типах данных, применяемых в СУБД PostgreSQL. Вообще, типы данных — это одно из базовых понятий любого языка программирования, и язык SQL в этом плане не является исключением. PostgreSQL имеет очень разнообразный набор встроенных типов данных, т. е. тех типов, которые СУБД предоставляет в распоряжение пользователя, как говорят, по умолчанию. Мы намеренно употребили здесь термин «встроенные», поскольку пользователь имеет возможность создавать и свои собственные типы данных, которые затем можно включить в систему и использовать их так же, как и встроенные. Такая возможность адаптации системы типов данных к конкретным ситуациям является одной из отличительных черт PostgreSQL. В этой главе мы расскажем лишь о самых основных типах данных, поскольку в вашем распоряжении всегда имеется полная документация. Настоящее учебное пособие не является ее заменой, оно призвано лишь помочь вам сделать первые шаги в освоении богатого мира типов данных PostgreSQL. Типы данных объединены в группы, в рамках этих групп они имеют некоторые общие свойства, но также они имеют и различия. 4.1. Числовые типы Группа числовых типов данных включает в себя целый ряд разновидностей: целочисленные типы, числа фиксированной точности, типы данных с плавающей точкой, последовательные типы (serial). В составе целочисленных типов находятся следующие представители: smallint, integer, bigint. Если атрибут таблицы имеет один из этих типов, то он позволяет хранить только целочисленные данные. При этом перечисленные типы различаются по количеству байтов, выделяемых для хранения данных. В PostgreSQL существуют псевдонимы для этих стандартизированных имен типов, а именно: int2, int4 и int8. Число байтов отражается в имени типа. 51 Глава 4. Типы данных СУБД PostgreSQL При выборе конкретного целочисленного типа принимают во внимание диапазон допустимых значений и затраты памяти. Зачастую тип integer считается оптимальным выбором с точки зрения достижения компромисса между этими показателями. Числа фиксированной точности представлены двумя типами — numeric и decimal. Однако они являются идентичными по своим возможностям. Поэтому мы будем проводить изложение на примере типа numeric. Для задания значения этого типа используются два базовых понятия: масштаб (scale) и точность (precision). Масштаб показывает число значащих цифр, стоящих справа от десятичной точки (запятой). Точность указывает общее число цифр как до десятичной точки, так и после нее. Например, у числа 12.3456 точность составляет 6 цифр, а масштаб — 4 цифры. Параметры этого типа данных указываются в круглых скобках после имени типа: numeric(точность, масштаб). Например, numeric(6, 2). Его главное достоинство — это обеспечение точных результатов при выполнении вычислений, когда это, конечно, возможно в принципе. Это оказывается возможным при выполнении сложения, вычитания и умножения. Числа типа numeric могут хранить очень большое количество цифр: 131 072 цифры — до десятичной точки (запятой), 16 383 — после точки. Однако нужно учитывать, что такая точность достигается за счет замедления вычислений по сравнению с целочисленными типами и типами с плавающей точкой. При этом для хранения числа затрачивается больше памяти, чем в случае целых чисел. Данный тип следует выбирать для хранения денежных сумм, а также в других случаях, когда требуется гарантировать точность вычислений. Представителями типов данных с плавающей точкой являются типы real и double precision. Они представляют собой реализацию стандарта IEEE «Standard 754 for Binary Floating-Point Arithmetic». Тип данных real может представить числа в диапазоне, как минимум, от 1E−37 до 1E+37 с точностью не меньше 6 десятичных цифр. Тип double precision имеет диапазон значений примерно от 1E−307 до 1E+308 с точностью не меньше 15 десятичных цифр. При попытке записать в такой столбец слишком большое или слишком маленькое значение будет генерироваться ошибка. Если точность вводимого числа выше допустимой, то будет иметь место округление значения. А вот при вводе очень маленьких чисел, которые невозможно представить значениями, отличными от нуля, будет генерироваться ошибка потери значимости, или исчезновения значащих разрядов (an underflow error). 52 4.1. Числовые типы При работе с числами таких типов нужно помнить, что сравнение двух чисел с плавающей точкой на предмет равенства их значений может привести к неожиданным результатам. Например: SELECT 0.1::real * 10 = 1.0::real; ?column? ---------f (1 строка) В дополнение к обычным числам эти типы данных поддерживают и специальные значения Infinity (бесконечность), −Infinity (отрицательная бесконечность) и NaN (не число). PostgreSQL поддерживает также тип данных float, определенный в стандарте SQL. В объявлении типа может использоваться параметр: float(p). Если его значение лежит в диапазоне от 1 до 24, то это будет равносильно использованию типа real, а если же значение лежит в диапазоне от 25 до 53, то это будет равносильно использованию типа double precision. Если же при объявлении типа параметр не используется, то это также будет равносильно использованию типа double precision. Последним из числовых типов является тип serial. Однако он фактически реализован не как настоящий тип, а просто как удобная замена целой группы SQL-команд. Тип serial удобен в тех случаях, когда требуется в какой-либо столбец вставлять уникальные целые значения, например, значения суррогатного первичного ключа. Синтаксис для создания столбца типа serial таков: CREATE TABLE имя-таблицы ( имя-столбца serial ); Эта команда эквивалентна следующей группе команд: CREATE SEQUENCE имя-таблицы_имя-столбца_seq; CREATE TABLE имя-таблицы ( имя-столбца integer NOT NULL DEFAULT nextval( 'имя-таблицы_имя-столбца_seq' ) ); ALTER SEQUENCE имя-таблицы_имя-столбца_seq OWNED BY имя-таблицы.имя-столбца; 53 Глава 4. Типы данных СУБД PostgreSQL Для пояснения вышеприведенных команд нам придется немного забежать вперед. Одним из видов объектов в базе данных являются так называемые последовательности. Это, по сути, генераторы уникальных целых чисел. Для работы с этими последовательностями-генераторами используются специальные функции. Одна из них — это функция nextval, которая как раз и получает очередное число из последовательности, имя которой указано в качестве параметра функции. В команде CREATE TABLE ключевое слово DEFAULT предписывает, чтобы СУБД использовала в качестве значения по умолчанию то значение, которое формирует функция nextval. Поэтому если в команде вставки строки в таблицу INSERT INTO не будет передано значение для поля типа serial, то СУБД обратится к услугам этой функции. В том случае, когда в таблице поле типа serial является суррогатным первичным ключом, тогда нет необходимости указывать явное значение для вставки в это поле. В заключение скажем, что кроме типа serial существуют еще два аналогичных типа: bigserial и smallserial. Им фактически, за кадром, соответствуют типы bigint и smallint. Поэтому при выборе конкретного последовательного типа нужно учитывать предполагаемое число строк в таблице и частоту удаления и вставки строк, поскольку даже для небольшой таблицы может потребоваться большой диапазон, если операции удаления и вставки строк выполняются часто. 4.2. Символьные (строковые) типы Стандартные представители строковых типов — это типы character varying(n) и character(n), где параметр указывает максимальное число символов в строке, которую можно сохранить в столбце такого типа. При работе с многобайтовыми кодировками символов, например UTF-8, нужно учитывать, что речь идет о символах, а не о байтах. Если сохраняемая строка символов будет короче, чем указано в определении типа, то значение типа character будет дополнено пробелами до требуемой длины, а значение типа character varying будет сохранено так, как есть. Типы character varying(n) и character(n) имеют псевдонимы varchar(n) и char(n) соответственно. На практике, как правило, используют именно эти краткие псевдонимы. PostgreSQL дополнительно предлагает еще один символьный тип — text. В столбец этого типа можно ввести сколь угодно большое значение, конечно, в пределах, установленных при компиляции исходных текстов СУБД. 54 4.2. Символьные (строковые) типы Документация рекомендует использовать типы text и varchar, поскольку такое отличительное свойство типа character, как дополнение значений пробелами, на практике почти не востребовано. В PostgreSQL обычно используется тип text. Константы символьных типов в SQL-командах заключаются в одинарные кавычки. SELECT 'PostgreSQL'; ?column? -----------PostgreSQL (1 строка) В том случае, когда в константе содержится символ одинарной кавычки или обратной косой черты, их необходимо удваивать. Например: SELECT 'PGDAY''17'; ?column? ---------PGDAY'17 (1 строка) В том случае, когда таких символов в константе много, все выражение становится трудно воспринимать. На помощь может прийти использование удвоенного символа «$». Эти символы выполняют ту же роль, что и одинарные кавычки, когда в них заключается строковая константа. При использовании символов «$» в качестве ограничителей уже не нужно удваивать никакие символы, содержащиеся в самой константе: ни одинарные кавычки, ни символы обратной косой черты. Например: SELECT $$PGDAY'17$$; ?column? ---------PGDAY'17 (1 строка) Возможность использования символов доллара в роли ограничителей символьной константы не является частью стандарта SQL. Это расширение, предлагаемое PostgreSQL. Подробно об этом написано в разделе документации 4.1.2.4 «Строковые константы, заключенные в доллары». 55 Глава 4. Типы данных СУБД PostgreSQL PostgreSQL предлагает еще одно расширение стандарта SQL — строковые константы в стиле языка C. Чтобы иметь возможность их использовать, нужно перед начальной одинарной кавычкой написать символ «E». Например, для включения в константу символа новой строки нужно сделать так: SELECT E'PGDAY\n17'; ?column? ---------PGDAY + 17 (1 строка) При использовании C-стиля необходимо удваивать обратную косую черту, если требуется поместить ее в константу буквально. А для включения в содержимое константы символа обратной кавычки можно либо удвоить ее, либо сделать так: SELECT E'PGDAY\'17'; ?column? ---------PGDAY'17 (1 строка) Подробно об использовании C-стиля написано в разделе документации 4.1.2.2 «Строковые константы со спецпоследовательностями в стиле C». 4.3. Типы «дата/время» PostgreSQL поддерживает все типы данных, предусмотренные стандартом SQL для даты и времени. Даты обрабатываются в соответствии с григорианским календарем, причем это делается даже в тех случаях, когда дата относится к тому моменту времени, когда этот календарь в данной стране еще не был принят. Для этих типов данных предусмотрены определенные форматы для ввода значений и для вывода. Причем эти форматы могут не совпадать. Важно помнить, что при вводе значений их нужно заключать в одинарные кавычки, как и текстовые строки. Начнем рассмотрение с типа date. Рекомендуемый стандартом ISO 8601 формат ввода дат таков: «yyyy-mm-dd», где символы «y», «m» и «d» обозначают цифру года, месяца и дня соответственно. PostgreSQL позволяет использовать и другие форматы 56 4.3. Типы «дата/время» для ввода, например: «Sep 12, 2016», что означает 12 сентября 2016 года. При выводе значений PostgreSQL использует формат по умолчанию, если не предписан другой формат. По умолчанию используется формат, рекомендуемый стандартом ISO 8601: «yyyy-mm-dd». SELECT '2016-09-12'::date; date -----------2016-09-12 (1 строка) А в следующем примере используется другой формат ввода, но формат вывода остается тот же самый, поскольку мы его не изменяли: SELECT 'Sep 12, 2016'::date; date -----------2016-09-12 (1 строка) Чтобы «сказать» СУБД, что введенное значение является датой, а не простой символьной строкой, мы использовали операцию приведения типа. В PostgreSQL она оформляется с использованием двойного символа «двоеточие» и имени того типа, к которому мы приводим данное значение. Важно учесть, что при выполнении приведения типа производится проверка значения на соответствие формату целевого типа и множеству его допустимых значений. В PostgreSQL предусмотрен целый ряд функций для работы с датами и временем. Например, для получения значения текущей даты служит функция current_date. Ее особенностью является то, что при ее вызове круглые скобки не используются. SELECT current_date; date -----------2016-09-21 (1 строка) Если нам требуется вывести дату в другом формате, то для разового преобразования формата можно использовать функцию to_char, например: SELECT to_char( current_date, 'dd-mm-yyyy' ); 57 Глава 4. Типы данных СУБД PostgreSQL СУБД выведет: to_char -----------21-09-2016 (1 строка) Обратите внимание, что для демонстрации возможностей СУБД по работе с датами нам даже не потребовалось создавать таблицу, а оказалось достаточно лишь команды SELECT. Для хранения времени суток служат два типа данных: time и time with time zone. Первый из них хранит только время суток, а второй — дополнительно — еще и часовой пояс. Однако документация на PostgreSQL не рекомендует использовать тип time with time zone, поскольку смещение (offset), соответствующее конкретному часовому поясу, может зависеть от даты перехода на летнее время и обратно, но в этом типе дата отсутствует. При вводе значений времени допустимы различные форматы, например: SELECT '21:15'::time; При выводе СУБД дополнит введенное значение, в котором присутствуют только часы и минуты, секундами. time ---------21:15:00 (1 строка) Чтобы «сказать» СУБД, что введенное значение является значением времени, а не простой символьной строкой, мы опять использовали операцию приведения типа. Предложим СУБД заведомо недопустимое значение времени, например: SELECT '25:15'::time; Получим такое сообщение об ошибке: ОШИБКА: значение поля типа date/time вне диапазона: "25:15" СТРОКА 1: select '25:15'::time; ^ А теперь возьмем значение, которое включает еще и секунды. SELECT '21:15:26'::time; 58 4.3. Типы «дата/время» time ---------21:15:26 (1 строка) А если число секунд недопустимое, то опять получим сообщение об ошибке. SELECT '21:15:69'::time; ОШИБКА: значение поля типа date/time вне диапазона: "21:15:69" СТРОКА 1: select '21:15:69'::time; ^ Время можно вводить не только в 24-часовом формате, но и в 12-часовом, при этом нужно использовать дополнительные суффиксы am и pm. Например: SELECT '10:15:16 am'::time; time ---------10:15:16 (1 строка) SELECT '10:15:16 pm'::time; time ---------22:15:16 (1 строка) Для получения значения текущего времени служит функция current_time. При ее вызове круглые скобки не используются. SELECT current_time; timetz -------------------23:51:57.293522+03 (1 строка) Текущее время выводится с высокой точностью и дополняется числовым значением, соответствующим локальному часовому поясу, который установлен в конфигурационном файле сервера PostgreSQL. В приведенном примере значение часового пояса равно +03, но если ваш компьютер находится в другом часовом поясе, то это значение будет другим, например, для регионов Сибири оно может быть +08. 59 Глава 4. Типы данных СУБД PostgreSQL В результате объединения типов даты и времени получается интегральный тип — временная отметка. Этот тип существует в двух вариантах: с учетом часового пояса — timestamp with time zone, либо без учета часового пояс
1 Перейдите по этой ссылке или найдите бота "@BotFather" в Telegram
2 Отправьте команду /newbot
3 Укажите имя для вашего бота
4 Укажите имя пользователя для бота
5 Скопируйте последнее сообщение от BotFather и вставьте его сюда