Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
Компания 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 13 1.1. Что такое базы данных и зачем они нужны . . . . . . . . . . . . . . . . . . 13 1.2. Основные понятия реляционной модели . . . . . . . . . . . . . . . . . . . 15 1.3. Что такое язык SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 1.4. Описание предметной области и учебной базы данных . . . . . . . . . . 19 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Глава 2. Создание рабочей среды 25 2.1. Установка СУБД . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.2. Программа psql — интерактивный терминал PostgreSQL . . . . . . . . . . 26 2.3. Развертывание учебной базы данных . . . . . . . . . . . . . . . . . . . . . 27 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Глава 3. Основные операции с таблицами 31 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Глава 4. Типы данных СУБД PostgreSQL 51 4.1. Числовые типы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.2. Символьные (строковые) типы . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.3. Типы «дата/время» . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.4. Логический тип . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 4.5. Массивы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 4.6. Типы JSON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Глава 5. Основы языка определения данных 95 5.1. Значения по умолчанию и ограничения целостности . . . . . . . . . . . . 95 5.2. Создание и удаление таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.3. Модификация таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 5.4. Представления . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 5.5. Схемы базы данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Глава 6. Запросы 145 6.1. Дополнительные возможности команды SELECT . . . . . . . . . . . . . . 145 6.2. Соединения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 3 Оглавление 6.3. Агрегирование и группировка . . . . . . . . . . . . . . . . . . . . . . . . . 168 6.4. Подзапросы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 Глава 7. Изменение данных 211 7.1. Вставка строк в таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 7.2. Обновление строк в таблицах . . . . . . . . . . . . . . . . . . . . . . . . . . 219 7.3. Удаление строк из таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 226 Глава 8. Индексы 241 8.1. Общая информация . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241 8.2. Индексы по нескольким столбцам . . . . . . . . . . . . . . . . . . . . . . . 246 8.3. Уникальные индексы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 8.4. Индексы на основе выражений . . . . . . . . . . . . . . . . . . . . . . . . . 248 8.5. Частичные индексы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 250 Глава 9. Транзакции 255 9.1. Общая информация . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 9.2. Уровень изоляции Read Uncommitted . . . . . . . . . . . . . . . . . . . . . 259 9.3. Уровень изоляции Read Committed . . . . . . . . . . . . . . . . . . . . . . . 261 9.4. Уровень изоляции Repeatable Read . . . . . . . . . . . . . . . . . . . . . . . 265 9.5. Уровень изоляции Serializable . . . . . . . . . . . . . . . . . . . . . . . . . . 269 9.6. Пример использования транзакций . . . . . . . . . . . . . . . . . . . . . . 275 9.7. Блокировки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 Глава 10. Повышение производительности 293 10.1. Основные понятия . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 10.2. Методы просмотра таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 10.3. Методы формирования соединений наборов строк . . . . . . . . . . . . . 302 10.4. Управление планировщиком . . . . . . . . . . . . . . . . . . . . . . . . . . 305 10.5. Оптимизация запросов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 1 час Глава 2. Создание рабочей среды 1 час Глава 3. Основные операции с таблицами 4 часа Глава 4. Типы данных СУБД PostgreSQL 4 часа Глава 5. Основы языка определения данных 4 часа Глава 6. Запросы 8 часов Глава 7. Изменение данных 4 часа Глава 8. Индексы 2 часа Глава 9. Транзакции 4 часа Глава 10. Повышение производительности 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 1 час Глава 2. Создание рабочей среды 1 час Глава 3. Основные операции с таблицами 4 часа Глава 4. Типы данных СУБД PostgreSQL 6 часов Глава 5. Основы языка определения данных 6 часов Глава 6. Запросы 12 часов Глава 7. Изменение данных 6 часов Глава 8. Индексы 4 часа Глава 9. Транзакции 8 часов Глава 10. Повышение производительности 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 |