Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
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 ) ); Второй способ заключается в построчном вводе команды точно так же, как она напе- чатана в тексте главы. При этом после ввода каждой строки нужно нажимать клавишу Enter. Обратите внимание, что до тех пор, пока команда не введена полностью, вид приглашения к вводу команд, выводимого утилитой psql, будет отличаться от перво- начального. В конце команды необходимо поставить точку с запятой. demo=# CREATE TABLE aircrafts demo-# ( aircraft_code char( 3 ) NOT NULL, demo(# model text NOT NULL, 24 demo(# range integer NOT NULL, demo(# CHECK ( range > 0 ), demo(# PRIMARY KEY ( aircraft_code ) demo(# ); В среде утилиты 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. Если вы решили прервать ввод команды, еще не введя ее полностью, то просто на- жмите клавиши Ctrl-C, в результате ввод команды будет прерван, а приглашение к вводу, выводимое утилитой psql, примет свой первоначальный вид: demo=# CREATE TABLE aircrafts ( aircraft_code char( 3 ) NOT NULL, demo(# ^C demo=# Теперь выберите способ ввода команды для создания таблицы aircrafts и введите ее. Если вы не допустили ошибок, то в ответ psql выведет сообщение, означающее успешное создание таблицы: CREATE TABLE Вы можете проверить, какую таблицу создала СУБД. Для этого служит команда ути- литы psql \d aircrafts В ответ вы получите примерно такой вывод на экран: 25 Таблица "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. Далее в описании таблицы приводятся сведения об ограничениях, наложенных на от- дельные атрибуты таблицы и на таблицу в целом. В принципе, при создании таблицы можно задать свои собственные имена для всех ограничений, однако делать это не обязательно. Мы не задавали никакого имени для ограничения, наложенного на ат- рибут range, поэтому ядро PostgreSQL также сгенерировало это имя автоматически — aircrafts_range_check. Следует различать команды языка SQL и команды утилиты psql. Команды, начинаю- щиеся с символа «\» являются командами, которые утилита psql предлагает для удоб- ства пользователя. Поскольку таблицы, которые мы будем сейчас создавать, очень простые, то в случае выявления какого-либо упущения при их создании, вы можете просто удалить табли- цу и создать ее заново, с учетом необходимых исправлений. А команду ALTER TABLE, предназначенную для модифицирования структуры таблиц, мы рассмотрим немно- го позднее. Поэтому прежде чем вы приступите к вводу данных, ознакомьтесь с ко- мандной для удаления таблицы. DROP TABLE имя_таблицы; Теперь вы можете приступить к вводу данных в таблицу «Самолеты». Для выполне- ния этой операции служит команда INSERT. Ее упрощенный формат таков: INSERT INTO имя_таблицы [( имя_атрибута, имя_атрибута, ... )] VALUES ( значение_атрибута, значение_атрибута, ... ); 26 В начале команды перечисляются атрибуты таблицы. При этом можно указывать их не в том порядке, в котором они были указаны при ее создании. Вы вовсе не обязаны помнить порядок атрибутов в команде CREATE TABLE. Обратите внимание на нали- чие квадратных скобок. Они указывают, что список атрибутов в команде не является обязательным, но при вводе команды квадратные скобки вводить не нужно. Однако если вы не привели список атрибутов, тогда вы обязаны в предложении VALUES зада- вать значения атрибутов с учетом того порядка, в котором они следуют в определе- нии таблицы. Конечно, такая форма записи команды является более короткой, но она менее универсальна, т. к. в случае реструктуризации таблицы и изменения порядка столбцов в ее определении или добавления нового столбца (даже без изменения по- рядка существующих столбцов) вам придется корректировать и команду INSERT в ваших прикладных программах. Давайте добавим одну строку в таблицу aircrafts. Обратите внимание на одинарные кавычки, в которые заключены значения атрибутов aircraft_code и model. Для атри- бутов символьных типов данных одинарные кавычки обязательны, а для числовых типов кавычки использовать не нужно. INSERT INTO aircrafts ( aircraft_code, model, range ) VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 ); В ответ мы получим сообщение об успешном добавлении этой строки: 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 позволяет сделать это за один раз. Вспомните о том, что можно редактировать ранее введен- ную команду, вызвав ее на экран при помощи клавиши «стрелка вверх». Как и при вводе предыдущих команд, вы можете выбрать один из двух способов ввода: ввести всю команду на одной строке, когда ее текст сворачивается «змейкой», либо вводить 27 команду построчно, нажимая клавишу Enter после каждого фрагмента команды, за- нимающего одну строку текста в пособии. 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 ); СУБД сообщит об успешном вводе 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; 28 model | aircraft_code | range ---------------------+---------------+------- Airbus A319-100 | 319 | 6700 Airbus A320-200 | 320 | 5700 Airbus A321-200 | 321 | 5600 Boeing 737-300 | 733 | 4200 Boeing 767-300 | 763 | 7900 Boeing 777-300 | 773 | 11100 Bombardier CRJ-200 | CR2 | 2700 Cessna 208 Caravan | CN1 | 1200 Sukhoi SuperJet-100 | SU9 | 3000 (9 строк) Далеко не всегда требуется выбирать ВСЕ строки из таблицы. Множество выбираемых строк можно ограничить с помощью предложения WHERE команды SELECT. Давайте выберем модели самолетов, у которых максимальная дальность полета находится в пределах от 4 до 6 тысяч км включительно. SELECT model, aircraft_code, range FROM aircrafts WHERE range >= 4000 AND range <= 6000; Условие выбора строк может быть составным. В данном случае мы скомбинировали два ограничения с помощью логической операции AND (т. е. «И»). model | aircraft_code | range -----------------+---------------+------- Boeing 737-300 | 733 | 4200 Airbus A320-200 | 320 | 5700 Airbus A321-200 | 321 | 5600 (3 строки) Теперь мы ознакомимся с командой UPDATE, предназначенной для обновления дан- ных в таблицах. Ее упрощенный синтаксис таков: UPDATE имя_таблицы SET имя_атрибута1 = значение_атрибута1, имя_атрибута2 = значение_атрибута2, ... WHERE условие; Условие, указываемое в команде, должно ограничить диапазон обновляемых строк. Если это условие не задать, то будут обновлены ВСЕ строки в таблице. Если же вам требуется обновить лишь часть из них, то не забывайте указывать условие отбора строк для обновления. Давайте предположим, что российские инженеры немного улучшили летные харак- теристики самолета Sukhoi SuperJet, и теперь дальность его полета стала на 500 км больше. UPDATE aircrafts SET range = 3500 WHERE aircraft_code = 'SU9'; СУБД выведет сообщение, подтверждающее успешное обновление одной строки: UPDATE 1 29 Давайте проверим, что получилось в результате обновления данных. SELECT * FROM aircrafts WHERE aircraft_code = 'SU9'; aircraft_code | model | range ---------------+---------------------+------- SU9 | Sukhoi SuperJet-100 | 3500 (1 строка) Итак, мы добрались до операции удаления строк из таблиц. Для этого используется команда DELETE, которая похожа на команду SELECT: DELETE FROM имя_таблицы WHERE условие; Удалите какую-нибудь одну строку из таблицы «Самолеты» (aircrafts): DELETE FROM aircrafts WHERE aircraft_code = 'CN1'; СУБД сообщит об успешном удалении одной строки: DELETE 1 Вы можете указать и какое-нибудь более сложное условие. Давайте, например, уда- лим информацию о самолетах с дальностью полета более 10 000 км, а также с даль- ностью полета менее 3000 км: DELETE FROM aircrafts WHERE range > 10000 OR range < 3000; При необходимости удаления ВСЕХ строк из таблицы, команда будет совсем про- стой: DELETE FROM aircrafts; Теперь в таблице «Самолеты» (aircrafts) нет ни одной строки. Для продолжения ра- боты необходимо эти данные восстановить. Можно использовать несколько спосо- бов. 1. Ввести заново команды INSERT из текста пособия, которые вы ранее уже вводи- ли. 2. Используя клавиши «стрелка вверх» и «стрелка вниз», найти команды INSERT в списке истории команд и повторно их выполнить. 3. С помощью специальной команды, предусмотренной в утилите psql, сохранить всю историю выполненных вами команд в текстовом файле: \s имя_файла_для_сохранения_истории_команд Затем нужно открыть его в текстовом редакторе, найти в файле нужные вам команды INSERT и, копируя команды в буфер обмена, вставить их в командную строку утилиты psql и выполнить. В нашей учебной базе данных содержится несколько таблиц, связанных между собой. Для таблицы «Самолеты» (aircrafts) ближайшей «родственницей» является таблица «Места» (seats). Она имеет следующую структуру: 30 Описание атрибута Имя атрибута Тип данных Тип 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), поскольку этот атрибут присутствует и в таблице «Самолеты» (aircrafts). Значения атрибута «Номер места» (seat_no) состоят из числовой части, обозначающей номер ряда кресел в салоне са- молета, и латинской буквы, обозначающей позицию в ряду, начиная с буквы «A». На- пример: 10A, 21D, 17F и т. д. В качестве типа данных для этого атрибута выберем varchar(4). Этот тип позволяет хранить любые символы. В скобках указана предель- ная длина символьной строки, которую можно ввести в поле такого типа. Значения атрибута «Класс обслуживания» (fare_conditions) могут выбираться из ограниченно- го списка значений. Проверка на соответствие вводимых значений этому списку бу- дет обеспечиваться с помощью ограничения CHECK. Также выбираем тип данных varchar. Все допустимые значения имеют различные длины, но мы ориентируемся на самое длинное значение. Значения всех атрибутов каждой строки данной таблицы не должны быть не опреде- ленными, поэтому на них накладывается ограничение NOT NULL. В качестве первичного ключа выбрана комбинация атрибутов «Код самолета, IATA» и «Номер места» — это составной ключ. Таким образом, первичный ключ будет есте- ственным . Как уже было сказано выше, это означает, что и в реальной предметной области существуют такие понятия, как код самолета и номер места, и эти понятия используются на практике. В этой таблице используется внешний ключ. Предложение 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, 31 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 Таблица "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 никакого своего имени для этого ограничения, хотя, в принципе, имели право это сделать, если бы захотели. Для просмотра списка всех таблиц, имеющихся в вашей базе данных, выполните ко- манду |