Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
\d Список отношений Схема | Имя | Тип | Владелец --------+-----------+---------+---------- public | aircrafts | таблица | postgres public | seats | таблица | postgres (2 строки) 32 В первой колонке выведенной таблицы указана так называемая схема базы данных — public. Мы уже говорили, что схема — это обособленный до некоторой степени раздел базы данных. По умолчанию все объекты создаются в схеме public. В третьей колонке указан тип — «таблица». Кроме таблиц могут быть еще и представления. В последней колонке указано имя пользователя, являющегося владельцем таблицы. Как правило, это пользователь, создавший таблицу. Давайте сразу же проделаем эксперимент, позволяющий показать работу внешнего ключа. Выполните следующую команду для ввода данных в таблицу «Места»: INSERT INTO seats VALUES ( '123', '1A', 'Business' ); СУБД ответит так: ОШИБКА: INSERT или UPDATE в таблице "seats" нарушает ограничение внешнего ключа "seats_aircraft_code_fkey" ,→ ПОДРОБНОСТИ: Ключ (aircraft_code)=(123) отсутствует в таблице "aircrafts". ,→ Это совершенно логично: если в таблице «Самолеты», на которую ссылается таблица «Места», нет описания самолета с кодом самолета, равным «123», то добавлять ин- формацию о номерах кресел для такого — несуществующего — самолета не имеет смысла. Так действует поддержка правил ссылочной целостности со стороны СУБД. Программист избавлен от необходимости отслеживать и обеспечивать «вручную» со- блюдение этих правил. Теперь нужно заполнить данными таблицу «Места». Для каждой модели самолетов введите только несколько строк, при этом предусмотрите записи для классов обслу- живания «Business» и «Economy». С помощью одной команды INSERT можно ввести сразу несколько строк: INSERT INTO seats VALUES ( 'SU9', '1A', 'Business' ), ( 'SU9', '1B', 'Business' ), ( 'SU9', '10A', 'Economy' ), ( 'SU9', '10B', 'Economy' ), ( 'SU9', '10F', 'Economy' ), ( 'SU9', '20F', 'Economy' ); Затем измените значение атрибута aircraft_code на другое, например, «773», и повто- рите команду INSERT. Так придется поступить со всеми моделями самолетов. Таблица «Места» заполнена необходимыми данными. Теперь решим еще одну зада- чу. Предположим, что нам нужно получить информацию о количестве мест в салонах для всех типов самолетов. Имея некоторый опыт в программировании на других язы- ках, нетрудно предположить, что в языке SQL должна присутствовать функция для подсчета количества строк в таблицах. Да, такая функция есть — это count. Конечно, для решения задачи, поставленной выше, в принципе можно воспользоваться таки- ми командами: SELECT count( * ) FROM seats WHERE aircraft_code = 'SU9'; SELECT count( * ) FROM seats WHERE aircraft_code = 'CN1'; ... ... ... ... 33 Очевидно, что это нерациональный подход, поскольку придется выполнять отдель- ные однотипные команды для всех моделей самолетов. Язык 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, которое обеспечит сортировку резуль- тирующих строк по значениям второго столбца. 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. Отсортируем выборку по тем же столбцам, по которым выполняли группировку. 34 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 строк) Контрольные вопросы и задания 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). 35 3. Команда UPDATE позволяет в процессе обновления выполнять арифметические действия над значениями, находящимися в строках таблицы. Представим себе, что двигатели самолета Sukhoi SuperJet стали в два раза экономичнее, вслед- ствие чего дальность полета этого лайнера возросла ровно в два раза. Команда UPDATE позволяет увеличить значение атрибута range в строке, хранящей ин- формацию об этом самолете, даже не выполняя предварительно выборку с це- лью выяснения текущего значения этого атрибута. При присваивании нового значения атрибуту range можно справа от знака «=» написать не только чис- ловую константу, но и целое выражение. В нашем случае оно будет простым: range = range * 2. Самостоятельно напишите команду UPDATE полностью, при этом не забудьте, что увеличить дальность полета нужно только у одной моде- ли — Sukhoi SuperJet, поэтому необходимо использовать условие WHERE. Затем с помощью команды SELECT проверьте полученный результат. 4. Если в предложении WHERE команды DELETE вы укажете логически и синтакси- чески корректное условие, но строк, удовлетворяющих этому условия, в таблице не окажется, то в ответ СУБД выведет сообщение DELETE 0 Такая ситуация не является ошибкой или сбоем в работе СУБД. Например, ес- ли после удаления какой-то строки вы повторно попытаетесь удалить ее же, то получите именно такое сообщение. Самостоятельно смоделируйте описанную ситуацию, подобрав условие, кото- рому гарантированно не соответствует ни одна строка в таблице «Самолеты» (aircrafts). 36 4 Типы данных СУБД PostgreSQL После первоначального знакомства с языком SQL имеет смысл немного упорядочить получен- ные вами знания. Речь идет о типах данных, применяемых в СУБД PostgreSQL. Вообще, типы данных — это одно из базовых понятий любого языка программирования, и язык SQL в этом плане не является исключением. PostgreSQL имеет очень разнообразный набор встроенных типов данных, т. е. тех типов, которые СУБД предоставляет в распоряжение пользователя, как говорят, по умолчанию. Мы намеренно употребили здесь термин «встроенные», поскольку поль- зователь имеет возможность создавать и свои собственные типы данных, которые затем можно включить в систему и использовать их так же, как и встроенные. Такая возможность адаптации системы типов данных к конкретным ситуациям является одной из отличительных черт PostgreSQL. В этой главе мы расскажем лишь о самых основных типах данных, поскольку в вашем распоряжении всегда имеется полная документация. Настоящее учебное пособие не является ее заменой, оно призвано лишь помочь вам сделать первые шаги в освоении богатого мира типов данных PostgreSQL. Типы данных объединены в группы, в рамках этих групп они имеют некоторые общие свойства, но также они имеют и различия. 4.1 Числовые типы Группа числовых типов данных включает в себя целый ряд разновидностей: цело- численные типы, числа фиксированной точности, типы данных с плавающей точкой, последовательные типы (serial). В составе целочисленных типов находятся следующие представители: smallint, integer, bigint. Если атрибут таблицы имеет один из этих типов, то он позволяет хра- нить только целочисленные данные. При этом перечисленные типы различаются по количеству байтов, выделяемых для хранения данных. В PostgreSQL существуют псевдонимы для этих стандартизированных имен типов, а именно: int2, int4 и int8. Число байтов отражается в имени типа. При выборе конкретного целочисленного типа принимают во внимание диапазон допустимых значений и затраты памяти. Зачастую тип integer считается оптималь- ным выбором с точки зрения достижения компромисса между этими показателя- ми. Числа фиксированной точности представлены двумя типами — numeric и decimal. Однако они являются идентичными по своим возможностям. Поэтому мы будем про- водить изложение на примере типа numeric. Для задания значения этого типа ис- пользуются два базовых понятия: масштаб (scale) и точность (precision). Масштаб по- казывает число значащих цифр, стоящих справа от десятичной точки (запятой). Точ- ность указывает общее число цифр как до десятичной точки, так и после нее. Напри- мер, у числа 12.3456 точность составляет 6 цифр, а масштаб — 4 цифры. 37 Параметры этого типа данных указываются в скобках: numeric(точность, масштаб). Например, numeric(6, 2). Его главное достоинство — это обеспечение точных результатов при выполнении вы- числений, когда это, конечно, возможно в принципе. Это оказывается возможным при выполнении сложения, вычитания и умножения. Числа типа numeric могут хра- нить очень большое количество цифр: 131072 цифры — до десятичной точки (запя- той), 16383 — после точки. Однако нужно учитывать, что такая точность достигается за счет замедления вычислений по сравнению с целочисленными типами и типами с плавающей запятой. При этом для хранения числа затрачивается больше памяти, чем в случае целых чисел. Данный тип следует выбирать для хранения денежных сумм, а также в других случа- ях, когда требуется гарантировать точность вычислений. Представителями типов данных с плавающей точкой являются 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). При работе с числами таких типов нужно помнить, что сравнение двух чисел с пла- вающей точкой на предмет равенства их значений может привести к неожиданным результатам. Например: 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. Если же при объявлении типа float параметр не используется, то это также будет равносильно использованию типа double precision. Последним из числовых типов является тип serial. Однако он фактически реализован не как настоящий тип, а просто как удобная замена целой группы SQL-команд. Тип serial удобен в тех случаях, когда требуется в какой-либо столбец вставлять уникаль- ные целые значения, например, значения суррогатного первичного ключа. Синтаксис для создания столбца типа serial таков: 38 CREATE TABLE tablename ( colname SERIAL ); Эта команда эквивалентна следующей группе команд: CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval( 'tablename_colname_seq' ) ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; Для пояснения вышеприведенных команд нам придется немного забежать впе- ред. Одним из видов объектов в базе данных являются так называемые последова- тельности. Это, по сути, генераторы уникальных целых чисел. Для работы с этими последовательностями-генераторами используются специальные функции. Одна из них — это функция 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. В столбец этого типа можно ввести сколь угодно большое значение, конечно, в пределах, уста- новленных при компиляции исходных текстов СУБД. 39 Документация рекомендует использовать типы 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 «Строковые константы, заключенные в доллары». PostgreSQL предлагает еще одно расширение стандарта SQL — строковые константы в стиле языка C. Чтобы иметь возможность их использовать, нужно перед начальной одинарной кавычкой написать символ E. Например, для включения в константу сим- вола новой строки «\n» нужно сделать так: |