Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
SELECT * FROM pilots; pilot_name | schedule | meal ------------+-------------+------------------------- Ivan | {1,3,5,6,7} | {сосиска,макароны,кофе} Petr | {1,2,5,7} | {котлета,каша,кофе} Pavel | {2,5} | {сосиска,каша,кофе} Boris | {3,5,6} | {котлета,каша,чай} (4 строки) Давайте получим список пилотов, предпочитающих на обед сосиски: SELECT * FROM pilots WHERE meal[ 1 ] = 'сосиска'; 68 pilot_name | schedule | meal ------------+-------------+------------------------- Ivan | {1,3,5,6,7} | {сосиска,макароны,кофе} Pavel | {2,5} | {сосиска,каша,кофе} (2 строки) Предположим, что руководство авиакомпании решило, что пища пилотов долж- на быть разнообразной. Оно позволило им выбрать свой рацион на каждый из четырех дней недели, в которые пилоты совершают полеты. Для нас это ре- шение руководства выливается в необходимость модифицировать таблицу, а именно: столбец meal теперь будет содержать двумерные массивы. Определе- ние этого столбца станет таким: meal text[][] Задание. Создайте новую версию таблицы и соответственно измените коман- ду INSERT, чтобы в ней содержались литералы двумерных массивов. Они будут выглядеть примерно так: '{ { "сосиска", "макароны", "кофе" }, { "котлета", "каша", "кофе" }, { "сосиска", "каша", "кофе" }, { "котлета", "каша", "чай" } }'::text[][] Сделайте ряд выборок и обновлений строк в этой таблице. Для обращения к эле- ментам двухмерного массива нужно использовать два индекса. Не забывайте, что по умолчанию номера индексов начинаются с единицы. 34. В тексте раздела 4.6 мы выполняли обновление JSON-объекта с помощью функ- ции jsonb_set: добавляли значение в массив. Для обновления скалярных значе- ний, например, по ключу trips, можно сделать так: UPDATE pilot_hobbies SET hobbies = jsonb_set( hobbies, '{ trips }', '10' ) WHERE pilot_name = 'Pavel'; UPDATE 1 Второй параметр функции — это путь в пределах JSON-объекта. Он теперь пред- ставляет собой лишь имя ключа. Однако его необходимо заключить в фигурные скобки. Третий параметр — это новое значение. Хотя оно числовое, но все равно требуется записать его в одинарных кавычках. SELECT pilot_name, hobbies->'trips' AS trips FROM pilot_hobbies; pilot_name | trips ------------+------- Ivan | 3 Petr | 2 Boris | 0 Pavel | 10 (4 строки) Задание. Самостоятельно выполните изменение значения по ключу home_lib в одной из строк таблицы. 69 35. Изучая приемы работы с типами JSON, можно, как и в случае с массивами, поль- зоваться способностью команды SELECT обходиться без создания таблиц. Пока- жем лишь один пример. Для добавления нового ключа и соответствующего ему значения в уже существующий объект, можно воспользоваться оператором «| |»: SELECT '{ "sports": "хоккей" }'::jsonb || '{ "trips": 5 }'::jsonb; ?column? ---------------------------------- {"trips": 5, "sports": "хоккей"} (1 строка) Для работы с типами JSON предусмотрено много различных функций и опе- раторов, представленных в разделе документации 9.15 «Функции и операторы JSON». Самостоятельно ознакомьтесь с ними, используя описанную технологию работы с командой SELECT. 36.* Объекты JSON в разных строках таблицы могут иметь различные наборы клю- чей. Добавьте дополнительный ключ и соответствующее ему значение в JSON- объект какой-нибудь строки таблицы pilots. Воспользуйтесь оператором «| |». 37. Объекты JSON позволяют не только добавлять в них новые ключи, но также и удалять из них ключи существующие. Удалите один из ключей из JSON-объекта какой-нибудь строки таблицы pilots. Соответствующее ему значение будет так- же удалено, т. к. без ключа оно не может существовать. Воспользуйтесь опера- тором «−». 70 5 Основы языка определения данных Как мы уже говорили ранее, язык SQL традиционно разделяется на две группы команд. Пер- вая из них предназначена для определения данных, т. е. для создания объектов базы данных, таких, например, как таблицы. Вторая группа команд служит для выполнения различных опе- раций с данными, таких, как вставка строк в таблицы, выполнение запросов к ним, обновление и удаление строк из таблиц. В этой главе мы сосредоточимся на командах первой группы, т. е. на определении данных. Рассмотрим все таблицы базы данных «Авиаперевозки». 5.1 Значения по умолчанию и ограничения целостности В последующих параграфах этой главы в качестве «опорной» базы данных мы будем использовать базу данных «Авиаперевозки», описанную в первой главе. Однако ос- новные сведения о значениях по умолчанию и ограничениях мы проиллюстрируем на той простой базе данных, состоящей из двух таблиц — «Студенты» и «Успевае- мость», о которой речь шла также в первой главе пособия. Сначала представим описание таблицы «Студенты» (students). Она имеет следующую структуру (т. е. набор атрибутов и их типы данных): Описание атрибута Имя атрибута Тип данных Тип PostgreSQL Ограничения № зачетной книжки record_book Числовой numeric(5) NOT NULL Ф. И. О. name Символьный text NOT NULL Серия документа doc_ser Числовой numeric(4) Номер документа doc_num Числовой numeric(6) Для атрибута «Серия документа, удостоверяющего личность» мы выбрали числовой тип, хотя, пожалуй, более дальновидным был бы выбор символьного типа (см. зада- ние 10 в конце главы). Теперь перейдем к таблице «Успеваемость» (progress). Структура ее такова: Описание атрибута Имя атрибута Тип данных Тип PostgreSQL Ограничения № зачетной книжки record_book Числовой numeric(5) NOT NULL Учебная дисциплина subject Символьный text NOT NULL Учебный год acad_year Символьный text NOT NULL Семестр term Числовой numeric(1) NOT NULL term = 1 OR term = 2 Оценка mark Числовой numeric(1) DEFAULT 5 mark >= 3 AND mark <= 5 В описаниях таблиц «Студенты» и «Успеваемость» есть колонки «Ограничения». Они уже содержат конкретные сведения, хотя ограничения мы еще детально не рассмат- ривали. Таким образом, мы немного забежали вперед, но по мере рассмотрения огра- ничений вам будет становиться понятно назначение каждого из них в обеих табли- цах. 71 Итак, начнем со значений по умолчанию. При работе с базами данных нередко воз- никают ситуации, когда то или иное значение является типичным для какого-то кон- кретного столбца. Например, если мы при проектировании таблицы «Успеваемость» (progress), предназначенной для учета успеваемости студентов, знаем, что их успехи, как правило, заслуживают оценки «отлично», то в команде CREATE TABLE мы можем отразить этот факт с помощью ключевого слова DEFAULT: CREATE TABLE progress ... mark numeric( 1 ) DEFAULT 5, ... Теперь перейдем к рассмотрению различных видов ограничений (constraints). Будем придерживаться того же порядка, в котором они представлены в документации. Первым будет ограничение CHECK. Для его создания используется ключевое сло- во CHECK, за которым следует выражение в круглых скобках, содержащее одно или несколько условий, налагаемых на атрибуты таблицы. Это ограничение бывает двух видов: ограничение уровня атрибута и уровня таблицы. Различие между ними только в синтаксическом оформлении: в обоих случаях в выражении могут содержаться об- ращения не только к одному, но также и к нескольким атрибутам таблицы. В первом случае ограничение CHECK является частью определения одного конкретного атри- бута, а во втором случае оно записывается как самостоятельный элемент определе- ния таблицы. Каждое ограничение имеет имя. Мы можем задать его сами с помощью ключевого слова CONSTRAINT. Если же мы этого не сделаем, тогда СУБД сформирует имя автоматически. Когда мы задаем имя сами, мы можем выбрать его с учетом сути налагаемых ограничений, с позиции предметной области. Если же это имя форми- рует СУБД, оно будет сформировано «механически», т. к. СУБД не знает ни сути этих ограничений, ни специфики предметной области. В качестве примера приведем ограничения, налагаемые на атрибуты term и mark из таблицы «Успеваемость» (progress). Семестр может иметь только два значения: 1 и 2. Отметка фактически может иметь только три значения: 3, 4 или 5. CREATE TABLE progress ( ... term numeric( 1 ) CHECK ( term = 1 OR term = 2 ), mark numeric( 1 ) CHECK ( mark >= 3 AND mark <= 5 ), ... ); В данном случае можно и не давать этим ограничениям какие-либо специфические имена, поскольку суть этих ограничений очевидна. Тем не менее, поскольку имена ограничений используются в тех сообщениях, которые выводит СУБД при попытке нарушения ограничений, все же можно придумать для них осмысленные имена, ко- торые облегчат понимание причин появления сообщений об ошибках. В качестве примера приведем ограничение на допустимые значения атрибута mark, а оформим его как ограничение уровня таблицы: CREATE TABLE progress ( ... mark numeric( 1 ), CONSTRAINT valid_mark CHECK ( mark >= 3 AND mark <= 5 ), 72 ... ); Следующим видом ограничений, который мы рассмотрим, будет NOT NULL. Оно означает, что в столбце таблицы, на который наложено это ограничение, должны обязательно присутствовать какие-либо определенные значения. При разработке баз данных, исходя из логики конкретной предметной области, зачастую требуется ис- пользовать это ограничение. Как сказано в документации, оно функционально экви- валентно ограничению CHECK ( column_name IS NOT NULL ), но в PostgreSQL создание явного ограничения NOT NULL является более эффективным подходом. Еще один вид ограничений — это ограничение уникальности UNIQUE. Такое огра- ничение, наложенное на конкретный столбец, означает, что все значения, содержа- щиеся в этом столбце в различных строках таблицы, должны быть уникальными, т. е. не должны повторяться. Ограничение уникальности может включать в себя и несколько столбцов. В этом случае уникальной должна быть уже комбинация их зна- чений. Когда в ограничение уникальности включается только один столбец, то можно задать ограничение непосредственно в определении столбца. Например, для таблицы «Сту- денты» (students) было бы логично потребовать, чтобы уникальными были значения столбца record_book: CREATE TABLE students ( record_book numeric( 5 ) UNIQUE, ... Это ограничение можно было бы записать и так, дав ему осмысленное имя: CREATE TABLE students ( record_book numeric( 5 ), name text NOT NULL, ... CONSTRAINT unique_record_book UNIQUE ( record_book ), ... ); Опять обратимся к таблице «Студенты» (students) и покажем, как можно создать огра- ничение уникальности, включающее более одного столбца. В этой таблице первич- ным ключом является столбец record_book, но очевидно, что комбинация значений серии и номера документа, удостоверяющего личность, является уникальной. Поэто- му можно модифицировать определение таблицы таким образом: CREATE TABLE students ( ... doc_ser numeric( 4 ), doc_num numeric( 6 ), ... CONSTRAINT unique_passport UNIQUE ( doc_ser, doc_num ), ... ); 73 При добавлении ограничения уникальности автоматически создается индекс на ос- нове B-дерева для поддержки этого ограничения. Переходим к первичным ключам. Как мы уже говорили ранее, этот ключ является уникальным идентификатором строк в таблице. Ключ может быть как простым, т. е. включать только один атрибут, так и составным, т. е. включать более одного атри- бута. При этом в отличие от уникального ключа, определяемого с помощью ограни- чения UNIQUE, атрибуты, входящие в состав первичного ключа, не могут иметь зна- чений NULL. Таким образом, определение первичного ключа эквивалентно опреде- лению уникального ключа, дополненного ограничением NOT NULL. Однако не стоит в реальной работе заменять первичный ключ комбинацией ограничений UNIQUE и NOT NULL, поскольку теория баз данных требует наличия в каждой таблице именно первичного ключа. Первичный ключ является частью метаданных, его наличие поз- воляет другим таблицам использовать его в качестве уникального идентификатора строк в данной таблице. Это удобно, например, при создании внешних ключей, речь о которых пойдет ниже. Перечисленными свойствами обладает также и уникальный ключ. Если первичный ключ состоит из одного атрибута, то можно указать его непосред- ственно в определении этого атрибута: CREATE TABLE students ( record_book numeric( 5 ) PRIMARY KEY, ... ); А можно сделать это и в виде отдельного ограничения: CREATE TABLE students ( record_book numeric( 5 ), ... PRIMARY KEY ( record_book ) ); В случае создания составного первичного ключа имена столбцов, входящих в его со- став, перечисляются в выражении PRIMARY KEY через запятую: PRIMARY KEY ( column1, column2, ...) При добавлении первичного ключа автоматически создается индекс на основе B- дерева для поддержки этого ограничения. В таблице может быть любое число ограничений UNIQUE, дополненных ограничени- ем NOT NULL, но первичный ключ может быть только один. PostgreSQL допускает и отсутствие первичного ключа, хотя строгая теория реляционных баз данных не реко- мендует так поступать. Завершаем наш обзор различных видов ограничений рассмотрением такого важно- го понятия, как внешний ключ (foreign key). Внешние ключи являются средством поддержания так называемой ссылочной целостности (referential integrity) между связанными таблицами. Напомним, что это означает, на примере таблиц «Студенты» (students) и «Успеваемость» (progress). В первой из них содержатся данные о студен- тах, а во второй — сведения об их успеваемости. Поскольку в процессе обучения сту- денты сдают целый ряд зачетов и экзаменов, то в таблице «Успеваемость» для каждо- го студента может присутствовать несколько строк. Для большинства из них это так 74 и будет, хотя, в принципе, возможна ситуация, когда для какого-то студента в таб- лице «Успеваемость» не окажется ни одной строки (если, он, например, находится в академическом отпуске). Конечно, должна быть возможность определить, какому студенту принадлежат те или иные оценки, т. е. какие строки в таблице «Успеваемость» с какими строками в таблице «Студенты» связаны. Для решения этой задачи не требуется в каждой строке таблицы «Успеваемость» повторять все сведения о студенте: номер зачетной книж- ки, фамилию, имя и отчество, данные документа, удостоверяющего личность. Доста- точно включить в состав каждой строки таблицы «Успеваемость» лишь уникальный идентификатор строки из таблицы «Студенты». В нашем случае это будет номер за- четной книжки — record_book. Данный атрибут и будет являться внешним ключом таблицы «Успеваемость». Таким образом, получив строку из таблицы «Студенты», можно будет найти все соответствующие ей строки в таблице «Успеваемость», сопо- ставив значения атрибутов record_book в строках обеих таблиц. В результате мы смо- жем получить все строки таблицы «Успеваемость», связанные с конкретной строкой из таблицы «Студенты» по внешнему ключу. Таблица «Успеваемость» будет ссылающейся (referencing), а таблица «Студенты» — ссылочной (referenced). Обратите внимание, что внешний ключ ссылающейся таб- лицы ссылается на первичный ключ ссылочной таблицы. Допускается ссылка и на уникальный ключ, не являющийся первичным. В данном контексте для описания от- ношений между таблицами можно сказать, что таблица students является главной, а таблица progress — подчиненной. Создать внешний ключ можно в формате ограничения уровня атрибута следующим образом: CREATE TABLE progress ( record_book numeric( 5 ) REFERENCES students ( record_book ), ... ); Предложение REFERENCES создает ограничение ссылочной целостности и указывает в качестве ссылочного ключа атрибут record_book. Это означает, что в таблицу «Успе- ваемость» (progress) нельзя ввести строку, значение атрибута record_book которой от- сутствует в таблице «Студенты» (students). Говоря простым языком, нельзя ввести за- пись об оценке того студента, информация о котором еще не введена в таблицу «Сту- денты». Поскольку внешний ключ в нашем примере ссылается на первичный ключ, можно использовать сокращенную форму записи этого ограничения, не указывая список ат- рибутов: CREATE TABLE progress ( record_book numeric( 5 ) REFERENCES students, ... ); Можно определить внешний ключ и в форме ограничения уровня таблицы: CREATE TABLE progress ( record_book numeric( 5 ), ... 75 FOREIGN KEY ( record_book ) REFERENCES students ( record_book ) ); Конечно, число атрибутов и их типы данных во внешнем ключе ссылающейся табли- цы и в первичном ключе ссылочной таблицы должны быть согласованы. Ограничению внешнего ключа можно присвоить наименование, как и любому дру- гому ограничению, с помощью ключевого слова CONSTRAINT. При наличии связей между таблицами, организованных с помощью внешних клю- чей, необходимо придерживаться определенной политики при выполнении опера- ций удаления и обновления строк в ссылочных таблицах, т. е. в тех, на которые ссы- лаются другие таблицы. В нашем примере ситуация принятия «политического» ре- шения возникает при удалении строк из таблицы «Студенты» (students). Конечно, ес- ли бы было принято решение хранить всю историю успеваемости студентов, в том числе и отчисленных, тогда строки из таблицы students вообще не удалялись бы. Но, упрощая реальную ситуацию, мы решили историю не хранить. Тогда возникает за- кономерный вопрос: что делать со строками в таблице «Успеваемость» (progress), ко- торые ссылаются на удаляемую строку в таблице «Студенты» (students)? Возможны несколько вариантов. 1. Удаление связанных строк из таблицы «Успеваемость» (progress), что означает, что при отчислении студента будет удаляться вся история его успехов в учебе. Эта операция называется каскадным удалением и для ее реализации в опреде- ление внешнего ключа добавляются ключевые слова ON DELETE CASCADE. На- пример: |