Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
Компания Postgres Professional Е. П. Моргунов ЯЗЫК SQL. БАЗОВЫЙ КУРС УЧЕБНО-ПРАКТИЧЕСКОЕ ПОСОБИЕ Москва 2017 УДК 004.655 ББК 32.973.26-018.2 М79 Моргунов, Е. П. М79 Язык SQL. Базовый курс: учеб.-практ. пособие / Е. П. Моргунов; под ред. Е. В. Рогова, П. В. Лузанова; Postgres Professional. — М., 2017. — 256 с. Настоящее учебно-практическое пособие представляет собой первую, базо- вую, часть учебного курса по языку SQL, предлагаемого российской компанией Postgres Professional. Учебный материал излагается в расчете на использование системы управления базами данных PostgreSQL. Пособие может использовать- ся как под руководством преподавателя, так и для самостоятельного изучения языка SQL. Пособие предназначено для студентов, обучающихся по направлениям 09.03.01 – «Информатика и вычислительная техника», 09.03.02 – «Инфор- мационные системы и технологии», 09.03.03 – «Прикладная информатика», 09.03.04 – «Программная инженерия» и 02.03.03 – «Математическое обес- печение и администрирование информационных систем». Оно может быть полезно широкому кругу студентов и специалистов, желающих ознакомиться с основами языка SQL в среде системы управления базами данных PostgreSQL. УДК 004.655 ББК 32.973.26-018.2 © Postgres Professional, 2017 © Е. П. Моргунов, 2017 Оглавление Введение 5 1 Введение в базы данных и SQL 9 1.1 Что такое базы данных и зачем они нужны . . . . . . . . . . . . . . . . . . 9 1.2 Основные понятия реляционной модели . . . . . . . . . . . . . . . . . . . 10 1.3 Что такое язык SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 1.4 Описание предметной области и учебной базы данных . . . . . . . . . . 14 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2 Создание рабочей среды 18 2.1 Установка СУБД . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.2 Программа psql — интерактивный терминал PostgreSQL . . . . . . . . . . 19 2.3 Развертывание учебной базы данных . . . . . . . . . . . . . . . . . . . . . 20 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 3 Основные операции с таблицами 22 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 4 Типы данных СУБД PostgreSQL 37 4.1 Числовые типы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 4.2 Символьные (строковые) типы . . . . . . . . . . . . . . . . . . . . . . . . . 39 4.3 Типы «дата/время» . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 4.4 Логический тип . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 4.5 Массивы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 4.6 Типы JSON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 5 Основы языка определения данных 71 5.1 Значения по умолчанию и ограничения целостности . . . . . . . . . . . . 71 5.2 Создание и удаление таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . 79 5.3 Модификация таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 5.4 Представления . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 5.5 Схемы базы данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 6 Запросы 110 6.1 Дополнительные возможности команды SELECT . . . . . . . . . . . . . . 110 6.2 Соединения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 6.3 Агрегирование и группировка . . . . . . . . . . . . . . . . . . . . . . . . . 128 6.4 Подзапросы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 7 Изменение данных 165 7.1 Вставка строк в таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 7.2 Обновление строк в таблицах . . . . . . . . . . . . . . . . . . . . . . . . . . 171 7.3 Удаление строк из таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 3 8 Индексы 188 8.1 Общая информация . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 8.2 Индексы по нескольким столбцам . . . . . . . . . . . . . . . . . . . . . . . 191 8.3 Уникальные индексы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 8.4 Индексы на основе выражений . . . . . . . . . . . . . . . . . . . . . . . . . 193 8.5 Частичные индексы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 9 Транзакции 199 9.1 Уровень изоляции READ UNCOMMITTED . . . . . . . . . . . . . . . . . . . 202 9.2 Уровень изоляции READ COMMITTED . . . . . . . . . . . . . . . . . . . . . 204 9.3 Уровень изоляции REPEATABLE READ . . . . . . . . . . . . . . . . . . . . . 207 9.4 Уровень изоляции SERIALIZABLE . . . . . . . . . . . . . . . . . . . . . . . . 210 9.5 Пример использования транзакций . . . . . . . . . . . . . . . . . . . . . . 215 9.6 Блокировки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 10 Повышение производительности 228 10.1 Основные понятия . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228 10.2 Методы просмотра таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 10.3 Методы формирования соединений наборов строк . . . . . . . . . . . . . 235 10.4 Управление планировщиком . . . . . . . . . . . . . . . . . . . . . . . . . . 237 10.5 Оптимизация запросов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 Контрольные вопросы и задания . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 11 Рекомендуемые источники 255 4 Введение В настоящее время термин «база данных» известен многим людям, даже далеким от профессиональной разработки компьютерных программ. Базы данных стали очень широко распространенной технологией, что потребовало, в свою очередь, больше- го числа специалистов, способных проектировать их и обслуживать. В ходе эволю- ции теории и практики баз данных стандартом де-факто стала реляционная модель данных, а в рамках этой модели сформировался и специализированный язык про- граммирования, позволяющий выполнять все необходимые операции с данными — Structured Query Language (SQL). Таким образом, важным компонентом квалифика- ции специалиста в области баз данных является владение языком SQL. В настоящем учебном пособии излагаются основы языка SQL — это базовый курс. Причем, язык рассматривается применительно к конкретной системе управления ба- зами данных (СУБД) — PostgreSQL. Реализация языка SQL в каждой СУБД соответ- ствует стандарту в той или иной степени, но кроме стандартизированных функций и возможностей, каждая СУБД предлагает и свои дополнительные расширения язы- ка. PostgreSQL обеспечивает очень хорошую поддержку стандарта языка SQL и так- же предоставляет интересные и практически полезные дополнительные возможно- сти. Одним из главных достоинств PostgreSQL является расширяемость. Это означа- ет, например, что пользователь (конечно, являющийся специалистом в области баз данных) может разработать свои собственные типы данных. Эти типы данных будут обладать всеми свойствами встроенных типов данных и могут быть введены в работу без останова сервера. Кроме того, PostgreSQL является свободно-распространяемым продуктом с открытым исходным кодом, который доступен на большом числе плат- форм. В пособии рассматриваются не только все основные команды языка SQL, но также и другие вопросы, такие, как индексы и транзакции. Пособие написано таким образом, чтобы его можно было использовать как под руко- водством преподавателя, так и самостоятельно. Предполагается, что студенты имеют доступ к уже установленной СУБД, поэтому процедура установки PostgreSQL деталь- но не рассматривается, а лишь даются указания о том, где найти инструкции по уста- новке. Это пособие предназначено для получения практических навыков использования языка SQL. Учебный материал подается таким образом, что многие важные знания читатель должен получить в результате выполнения заданий, находящихся в конце каждой главы. В основном тексте глав эти знания могут быть не представлены. Пред- полагается, что значительная часть заданий будет выполняться читателем самосто- ятельно с помощью документации на СУБД PostgreSQL, но зачастую даются и ука- зания к их выполнению. Задания, приведенные в пособии, различаются по уровню сложности. Самые сложные из них, а также те, которые требуют много времени для выполнения, отмечены звездочкой. Задания можно выполнять по мере изучения учебного материала конкретной главы. Однако некоторые из них имеют комплексный характер, поэтому для их выполнения необходимо изучить всю главу или, как минимум, несколько ее разделов. 5 Хотя пособие имеет практическую направленность и не является теоретическим кур- сом, все же в первой главе кратко, на элементарном уровне излагаются основные по- нятия теории баз данных и реляционной модели. Это сделано для того, чтобы студен- ты могли приступить к практическому освоению языка 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, по- сле ее изучения студенты должны представлять себе простые способы использования всех основных команд языка. Эта глава не очень сложная, но объемная, поэтому на ее изучение отводится четыре часа. Глава 4 посвящена рассмотрению основных типов данных, используемых в PostgreSQL. Это большая глава, однако в ней значительную часть составляют задания и упражнения. Предполагается, что студенты за четыре часа должны усво- ить только основные приемы использования типов данных. А для того, чтобы знания закрепились, рекомендуется обращаться к материалу этой главы (в том числе и к упражнениям) в процессе изучения остальных глав пособия, при необходимости уточнения тех или иных особенностей применения конкретных типов. Распре- делить время, выделенное на изучение этой главы, мы рекомендуем следующим образом: два часа на первые четыре параграфа — числовые и строковые типы, типы «дата/время» и логический тип, еще два часа — на массивы и тип json/jsonb. Чтобы выполнять запросы к базе данных, необходимо хорошо понимать ее структу- ру, взаимосвязи таблиц. Поэтому глава 5, в которой рассматриваются основы языка определения данных, очень важна с точки зрения детального изучения таблиц ба- зы данных «Авиаперевозки» и подготовки к освоению главы 6. Поскольку материал главы основан на том, что база данных уже развернута на компьютере студента, то вводить команды для создания таблиц не требуется. Это позволяет сократить время, затрачиваемое на изучение главы. В пособии принят подход, при котором сначала рассматриваются команды определения данных, а затем — команды манипулирова- ния данными. Поэтому глава 5 «Основы языка определения данных» предшествует 6 главе 6 «Запросы». Однако избранный подход реализуется не слишком жестко: в об- зорной главе 3 рассматриваются основные команды, в том числе, и несложные запро- сы. А запросы — это уже язык манипулирования данными. На изучение главы 5 от- ведено четыре часа. В течение первого двухчасового занятия нужно изучить два пер- вых параграфа, в которых освещаются такие вопросы, как ограничения целостности и создание и удаление таблиц. Второе двухчасовое занятие нужно посвятить изуче- нию трех оставшихся параграфов. В них говорится о способах модификации таблиц, а также о представлениях (views) и схемах базы данных. Глава 6 является центральной главой пособия, поэтому на ее изучение отводится во- семь часов, т. е. больше, чем на изучение других глав. Она состоит из четырех па- раграфов. Первый из них посвящен разнообразным дополнительным возможностям команды SELECT. Речь идет, в частности, о таких вещах, как предложения LIMIT и OFFSET, оператор LIKE и регулярные выражения в условиях предложения WHERE и о других возможностях. Тем не менее, материал этого параграфа несложный, для его изучения достаточно выделить один час. Во втором параграфе рассказывается о спо- собах соединения таблиц. Это более сложная тема, для ее изучения необходимо вы- делить два часа. Третий параграф посвящен агрегированию и группировке. В нем рассматривается и такая важная и интересная тема, как оконные функции. Данный параграф также требует двухчасового занятия. Самый сложный раздел этой главы — четвертый. Он посвящен подзапросам. В нем, в частности, освещается такая важная и интересная тема, как общие табличные выражения (Common Table Expressions — CTE). Для изучения материала данного параграфа необходимо выделить три часа. В главе 7 собраны все команды, предназначенные для изменения данных: вставка строк, их обновление и удаление. Поскольку в предшествующих главах эти команды уже использовались для решения простых задач, то в данной главе рассматриваются более сложные способы их использования. В ней много упражнений, они составля- ют половину ее объема. Рекомендуется уделить два часа изучению способов вставки строк в таблицы, а еще два часа — рассмотрению операций обновления и удаления строк. Глава 8 посвящена индексам, она небольшая, поэтому с ней можно ознакомиться за одно двухчасовое занятие. Поскольку индексы тесно связаны с вопросами произво- дительности, т. е. скорости выполнения запросов, то было бы целесообразно после изучения заключительной главы вернуться к главе 8 и посмотреть на представлен- ные в ней команды и запросы, уже зная о команде EXPLAIN. Транзакциям посвящена глава 9. Механизмы их выполнения имеют много тонкостей, поэтому при изучении этой главы необходимо экспериментировать и стараться объ- яснить полученные результаты. В заключительной главе 10 рассматриваются вопросы повышения производительно- сти. Эта глава может показаться слишком абстрактной и сложной для начального кур- са языка SQL, тем не менее, она очень важна. Студенты должны научиться читать пла- ны выполнения запросов и понимать назначение каждой операции, представленной в плане. А овладение искусством оптимизации запросов потребует много времени и опыта, оно придет не сразу. В том случае, когда на практические занятия по дисциплине «Базы данных» в учеб- ном плане отводится 54 часа, можно изменить предлагаемое распределение учеб- ных часов. В частности, в главе 4 можно больше времени посвятить типам данных 7 json/jsonb и массивам. В главе 6 можно более детально рассмотреть оконные функ- ции и общие табличные выражения. При изучении главы 9, посвященной транзакци- ям, было бы целесообразно разработать несложное приложение, в котором исполь- зовались бы транзакции, и провести эксперименты с этим приложением, выполняя параллельно несколько сеансов и изменяя при этом уровни изоляции транзакций. В рамках главы 10 имеет смысл вернуться к командам и запросам главы 8 и изучить планы их выполнения с помощью команды 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 строка) Мы надеемся, что изучение материала, изложенного в учебном пособии, будет спо- собствовать повышению уровня вашей квалификации и расширению профессио- нального кругозора. 8 |