Агрегатные функции
PostgreSQL, как и многие другие реляционные СУБД, поддерживает агрегатные функции. Агрегатная функция производит вычисление над единичным результатом от множества записей. Например, есть агрегаты для вычисления count (количества), sum (суммы), avg (среднего арифметического), max (максимального значения) и min (минимального значения) списка записей.
В качестве примера, мы можем найти наиболее высокую низкую температуру, создав запрос:
SELECT max(temp_lo) FROM weather;
max ----- 46 (1 row)
Если мы хотим знать, в каком городе (или городах) это происходило, мы можем попытаться создать такой запрос:
SELECT city FROM weather WHERE temp_lo = max(temp_lo); НЕПРАВИЛЬНО
но он не будет работать, потому что агрегат max
нельзя использовать в предложении WHERE. (Это ограничение существует, потому что предложение WHERE
определяет записи, которые будут использованы на стадии обработки агрегатами; и таким образом оно должно уже отработать перед тем, как будут запущены агрегатные функции). Однако, эту ситуацию можно разрешить, если использовать позапрос:
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city --------------- San Francisco (1 row)
Теперь всЈ в порядке, потому что подзапрос является независимым вычислением, которое вычисляет свой собственный агрегат отдельно от того, который работает во внешнем запросе.
Агрегаты являются очень полезными в комбинациях с литералами GROUP BY. Например, мы можем получить максимально низкую температуру, отмеченную в каждом городе с помощью запроса:
SELECT city, max(temp_lo) FROM weather GROUP BY city;
city | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows)
который предоставит нам по одной записи на город. Каждый результат агрегата подсчитывается исходя из записей таблицы, которые соответствуют определенному городу. Мы можем фильтровать сгруппированные записи, используя литерал HAVING:
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
city | max ---------+----- Hayward | 37 (1 row)
Данный запрос возвращает такой же результат, но только для тех городов, в которых низкая температура temp_lo меньше. Наконец, если мы хотим получить только те города, имена которых начинаются на букву "S", мы можем выполнить запрос:
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1)
GROUP BY city HAVING max(temp_lo) < 40;
(1) Оператор LIKE выполняет сравнение по шаблону и описывается подробно в PostgreSQL User's Guide.
Очень важно понимать различия между агрегатами и такими конструкциями SQL как WHERE и HAVING. Фундаментальное отличие между WHERE и HAVING состоит в следующем: WHERE выбирает входящие записи перед группированием и вычислениями агрегатами (а значит управляет тем, какие записи попадут в агрегатные функции), в то время как HAVING
выбирает группу записей после группирования и вычисления агрегатов. Таким образом, предложение WHERE не должно содержать агрегатных функций; оно не оказывает влияния на попытку использовать агрегаты для того, чтобы определить какие записи будут переданы агрегатам. С другой стороны предложение HAVING
всегда содержит агрегатные функции. (Вкратце, вам разрешается писать предложение HAVING, которое не использует агрегаты, но это непроизводительно: такое же условие можно использовать в предложении WHERE с большей эффективностью).
Обратите внимание, что мы можем применять ограничения по имени города в предложении WHERE, так как оно не требует использования агрегата. Это будет более эффективно, чем добавлять это ограничение в предложение HAVING, потому что мы пропускаем операции группирования и вычисления агрегатов для всех записей, которые не соответствуют условию в предложении WHERE.
Что такое PostgreSQL?
PostgreSQL - это объектно-реляционная система управления базами данных (ORDBMS) (по-русски ОРСУБД или просто СУБД) основанная на POSTGRES, Версии 4.2, которая была разработана в Научном Компьютерном Департаменте Беркли Калифорнийского Университета. Проект POSTGRES, под руководством профессора Майкла Стоунбрейкера (Michael Stonebraker), был поддержан Агентством Расширенных Оборонных Исследовательских Проектов (Defense Advanced Research Projects Agency (DARPA)), Офисом Армейских Исследований (Army Research Office (ARO)), Национальным Научным Фондом (National Science Foundation (NSF)), а также ESL, Inc.
PostgreSQL является прямым потомком с открытым исходным кодом от оригинального кода, сделанного в Беркли. СУБД предоставляет поддержку SQL92/SQL99 и другие современные возможности.
POSTGRES является пионером во многих объектно-реляционных аспектах, появившихся теперь в некоторых коммерческих СУБД. Традиционные реляционные СУБД (RDBMS) поддерживают модель данных, которая составляет коллекцию поименованных кортежей, содержащих атрибуты заданного типа. В современных коммерческих системах, к возможным типам относятся числа с плавающей точкой, целые числа, символьные строки, денежные типы и даты. Это обычно приводит к тому, что данная модель является неадекватной для будущих приложений обработки данных. Реляционная модель успешно заменяет предыдущие модели отчасти в силу "Спартанской простоты". Однако, такая простота далает реализацию определЈнных приложений очень трудной. PostgreSQL предлагает существенное увеличение мощи СУБД, через внедрение следующих дополнительных аспектов, которые позволяют пользователям легко расширять систему:
наследованиетипы данныхфункции
Другие возможности, предоставляющие дополнительные удобства и мощь:
ограничения целостноститриггерыправилатранзакционная целостность
Все эти особенности помещают PostgreSQL в категорию СУБД, известную как объектно-реляционные (object-relation). Заметим, что здесь есть отличие от тех объектно-ориентированных (object-oriented)
СУБД, которые в основном поддерживают традиционные языки реляционных СУБД. Однако, PostgreSQL имеет некоторые объектно-ориентированные возможности, это важно в мире реляционных СУБД. Фактически, некоторые коммерческие СУБД только недавно заимели встроенные возможности, которые были открыты в PostgreSQL.
Доступ к базе данных
Если вы создали базу данных, то вы можете получить доступ к ней через:
Запуск интерактивной терминальной программы PostgreSQL, называемой psql, которая позволит вам интерактивно вводить, редактировать и выполнять команды SQL.
Использование графического инструмента типа PgAccess или офисного пакета с поддержкой ODBC, который позволит создавать и манипулировать базой данных. Эти возможности не описываются в данном учебнике.
Написание специального приложения, используя один из нескольких доступных языков программирования, для которых существуют привязки к PostgreSQL. Эти возможности описываются далее в PostgreSQL Programmer's Guide.
Наверное вы все-таки захотите запустить psql, чтобы выполнять примеры из этого учебника. Вы можете подключится к базе данных с именем mydb, введя следующую команду:
$ psql mydb
Если вы опустите имя базы данных, то по умолчанию будет выбрано имя базы данных совпадающее с вашим именем пользователя. Вы уже читали об этом в предыдущей секции.
В psql, вы увидите следующее сообщение:
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit
mydb=>
Последняя строка может также иметь вид
mydb=#
Это будет означать, что вы имеете права суперпользоватя в базе данных, что наиболее вероятно, если вы устанавливали PostgreSQL сами. Быть суперпользователем означает, что вы не попадаете под ограничения доступа. Для целей, которые преследует данный учебник это не важно.
Если у вас возникли проблемы при запуске psql, то вернитесь назад к предыдущей секции. Диагностические сообщения psql и createdb похожи и если одна из этих программ работает, то и вторая тоже должна работать.
Последняя строка, которую выдает psql - это приглашение, которое показывает, что psql
ожидает ввода запросов SQL в рабочую область, которой управляет psql. Попытайтесь ввести эти команды:
mydb=> SELECT version();
version ---------------------------------------------------------------- PostgreSQL 7.3devel on i586-pc-linux-gnu, compiled by GCC 2.96 (1 row)
mydb=> SELECT current_date;
date ------------ 2002-08-31 (1 row)
mydb=> SELECT 2 + 2;
?column? ---------- 4 (1 row)
Программа psql имеет несколько внутренних команд, которые не являются командами SQL. Они начинаются с обратной косой черты, "\". Некоторые из этих команд были перечислены в приветственном сообщении при запуске программы. Например, вы можете получить помощь в синтаксисе разных команд PostgreSQL SQL если введЈте:
mydb=> \h
Чтобы выйти из psql введите
mydb=> \q
и psql завершит свою работу и вы вернетесь в командный интерпретатор (shell). (Внутренние команды можно увидеть, набрав \? на приглашение psql.) Полные возможности psql описываются в PostgreSQL Reference Manual. Если PostgreSQL установлен корректно, вы можете также ввести man psql
в приглашении командного интерпретатора, чтобы посмотреть документацию на эту программу. В данном учебнике, мы не используем явно все возможности этой программы, но вы можете использовать их сами, когда прочтЈте документацию и увидите их.
Фундаментальные основы архитектуры
Перед тем как двигаться дальше, вы должны понимать основы системной архитектуры PostgreSQL. Понимание того, как взаимодействуют части PostgreSQL сделает этот раздел понятней.
На жаргоне баз данных, PostgreSQL использует модель клиент/сервер. Сессия PostgreSQL состоит из следующих скооперированных процессов (программ):
Серверный процесс (backend), который управляет файлами баз данных, разрешает подключения к базам данным от клиентских приложений и выполняет действия над базой данных, затребованные клиентами. Программа-сервер баз данных называется postmaster.
Клиентское приложения пользователя (frontend), которое хочет выполнить операции с базой данных. Клиетнсткие приложения могут быть очень разными: ориентированными на работу с текстом, с графикой, с web-сервером или специальные инструменты обслуживания базы данных. Некоторые клиентские приложения поставляются в составе дистрибутива PostgreSQL, но подавляющее большинство пишется пользователями.
Для клиент-серверных приложений обычно клиент и сервер находятся на разных компьютерах. В этом случае, они соединяются по сети через TCP/IP. Вы должны взять это на заметку, потому что файлы, которые доступны на клиентской машине могут быть недоступны (или доступны, используя другое имя файла) на машине-сервере.
Сервер PostgreSQL может управлять несколькими конкурентыми (говоря проще одновременными -- прим. пер.) соединениями от клиентов. Для этой цели, сервер запускает (через системный вызов "fork") новый процесс для каждого соединения. Начиная с момента запуска клиент и новый серверный процесс общаются друг с другом без помощи первоначального postmaster процесса. Таким образом, процесс postmaster запущен всегда и ожидает соединений от клиентов, после чего начинают работать клиент и соответствующий ему серверный процесс. (Все это, разумеется, происходит незримо для пользователя. Мы объясняем это здесь только для понимания того, как все работает).
Концепции
PostgreSQL - это система управления реляционными базами данных (СУБД). Это означает, что это система для управления данными, которые хранятся в виде отношений. (В учебниках по СУБД существуют и другие термины, например, кортежи -- прим. пер.). Отношение - это математический термин для таблицы. Понятие хранения данных в таблицах является сегодня таким банальным, что оно может показаться самоочевидным, однако есть несколько других способов организации баз данных. Файлы и каталоги в Unix-подобных операционных системах являются примером иерархической базы данных. Одно из наиболее современных направлений разработки СУБД - это объектно-ориентированные базы данных.
Каждая таблица является поименованной коллекцией строк (rows) (в русской литературе по SQL преимущественно говорят "записей" -- прим. пер.). Каждая запись в таблице имеет некоторый набор поименованных колонок (columns) (опять-таки в русской литературе преимущественно говорят "полей" -- прим. пер.) и каждое поле является определенным типом данных. Поля в записи расположены в фиксированный порядке, важно помнить, что SQL никогда не гарантирует упорядоченного следования записей в таблице (за исключением случая, когда они могут быть явно отсортированы для выдачи пользователю).
Таблицы группируются в базы данных, а коллекция баз данных, управляемая одной копией сервера PostgreSQL называется кластером баз данных.
Наследование
Наследование - это концепт из объектно-ориентированных СУБД. Оно открывает новые интересные возможности разработки баз данных.
Создайте две таблицы: Таблицу cities (города)
и таблицу capitals (столицы). Фактически, столицы - это тоже города, так что вы можете захотеть получить какой-либо способ просматривать неявно и столицы, когда вы смотрите список всех городов. Если вы действительно сообразительны вы можете реализовать например такую схему:
CREATE TABLE capitals ( name text, population real, altitude int, -- (in ft) state char(2) );
CREATE TABLE non_capitals ( name text, population real, altitude int -- (in ft) );
CREATE VIEW cities AS SELECT name, population, altitude FROM capitals UNION SELECT name, population, altitude FROM non_capitals;
Это хорошо работает, когда вы создаете запросы для просмотра, но это безобразно, когда вам нужно обновить в нескольких записях, например, поле name.
Лучшим решением является:
CREATE TABLE cities ( name text, population real, altitude int -- (in ft) );
CREATE TABLE capitals ( state char(2) ) INHERITS (cities);
В данном случае, строки в таблице capitals
наследуют все колонки (name, population и altitude) от родительской таблицы cities. Тип колонки name это text - один из родных типов PostgreSQL для символьных строк переменной длины. Столицы штатов имеют дополнительную колонку state, которая показывает штат. В PostgreSQL таблица может наследовать и от нескольких других таблиц.
Например, следующий запрос находит имена всех городов, включая столицы штатов, которые находятся на высоте свыше 500 футов:
SELECT name, altitude FROM cities WHERE altitude > 500;
запрос возвращает:
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows)
С другой стороны, следующий запрос находит все города которые не являются столицами штатов и находятся на высоте выше 500 футов:
SELECT name, altitude FROM ONLY cities WHERE altitude > 500;
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 (2 rows)
Здесь ONLY перед cities
означает, что запрос должен быть запущен только для таблицы cities, а не для таблиц ниже cities в иерархии наследования. Многие из тех команд, которые мы рассмотрели -- SELECT, UPDATE и DELETE -- поддерживают нотацию ONLY.
О чем эта книга
Добро пожаловать в PostgreSQL и Учебник PostgreSQL. Следующие несколько глав предназначаются для того, чтобы дать простое введение в PostgreSQL, концепции реляционных баз данных и языка SQL для тех, кто является новичком в любом из этих вопросов. Мы просто сгруппировали некоторые общие знания в использовании компьютеров. Опыт в программировании или в Unix не требуется. Данная книга в основном предназначается для того, чтобы дать элементарные основы в важных аспектах системы PostgreSQL. В ней не делается попыток полностью или подробно раскрыть все затрагиваемые темы.
После того как вы поработаете с этим учебником, вы возможно захотите прочесть PostgreSQL User's Guide чтобы получить более подробные знания языка SQL, или PostgreSQL Programmer's Guide чтобы ознакомится с информацией о разработке приложений для PostgreSQL. Тот, кто устанавливает свой собственный сервер и будет управлять им, должны также прочитать PostgreSQL Administrator's Guide.
Объединения таблиц (Join)
Мы видим, что наши запросы используют в только одну таблицу. Но запросы могут одновременно обращаться к нескольким таблицам или к в одно и то же время к той же таблице но с другим способом обработки произвольных записей. Запрос, который обращается к нескольким записям одной таблицы или к нескольким таблицам одновременно, называется join запросом (объединением). В качестве примера, скажем вы хотите посмотреть все записи о погоде вместе с теми городами, к которым они относятся. Чтобы это сделать, нам нужно сравнить поле city каждой записи о погоде в таблице погоды weather с именем поля всех записей в таблице городов cities, и выбрать пары записей, где эти значения совпадают.
Note: Это только концептуальная модель. Фактически, объединение может быть выполнено более эффективно, но это будет не видно пользователю.
Таким образом, нужное нам действие будет выполнено следующим запросом:
SELECT * FROM weather, cities WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name | location ---------------+---------+---------+------+------------+---------------+----------- San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) (2 rows)
После выполнения которого мы получим две строки:
Вы не увидите записи для города Hayward, потому что для этого города нет соответствующей записи в таблице cities, и таким образом, объединение игнорирует несовпадающие записи в таблице weather. Далее мы увидим как это можно исправить.
В получившемся результате есть два поля, содержащие имя города. Это правильно, потому что происходит слияние списка полей таблиц weather и cities. На практике это нежелательно, и вы наверное захотите явно указать те поля, которые нужно, вместо использования *:
SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
Упражнение: Попытайтесь найти смысл этого запроса, если будет опущен элемент WHERE.
Поскольку все поля имеют разные имена, анализатор автоматически находит какое имя соответствует какой таблице, но хорошим стилем в запросах объединения является указание полного имени поля:
SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities. location FROM weather, cities WHERE cities.name = weather.city;
Запросы объединения такого вида, могут быть также записаны в альтернативной форме:
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
Этот синтаксис не является общеиспользуемым, но мы показываем этот пример, чтобы помочь вам понять последующий материал.
Теперь мы рассмотрим как можно получить записи для города Hayward, о которых мы говорили ранее. Нам нужен такой запрос, который бы выполнил сканирование таблицы weather и для каждой записи в ней, нашел соответствующую запись в таблице cities. Если соответствующая запись не найдена, мы хотим подставить некоторые "пустые значения" на место полей таблицы cities. Такой вид запросов называется outer join (внешнее объединение). (Которое как мы видим отличается от inner join (внутреннего объединения)). Вот как будет выглядеть запрос:
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location ---------------+---------+---------+------+------------+---------------+----------- Hayward | 37 | 54 | | 1994-11-29 | | San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) (3 rows)
Данный запрос называется left outer join, потому что для таблицы упоминаемой в левой части оператора объединения join в полученном результате будут выданы все записи по крайней мере один раз, в то время как для таблицы справа в результат попадут только те записи, которые соответствуют записям в таблице слева. Когда будет производится вывод записи из таблицы слева, для которой нет соответствующей записи в таблице справа, в поля, соответствующие таблице справа будут подставлены пустые (null) значения.
Упражнение: Существует также right outer join и full outer join запросы. Попытайтесь понять, что они делают.
Мы также можем объединять таблицу с собой же. Такой вид запросов называется self join. Предположим, что мы хотим найти все записи о погоде, которые находятся в определенном диапазоне температур. Для этого нам необходимо сравнить значения полей temp_lo и temp_hi для каждой записи о погоде weather с значениями полей temp_lo и temp_hi
для всех остальных записей о погоде weather. Мы можем сделать это с помощью следующего запроса:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high ---------------+-----+------+---------------+-----+------ San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)
В этом запросе мы переназвали таблицу weather именами W1
и W2 чтобы различать левую и правую части оператора объединения join. Мы можем также использовать такие виды псевдонимов в других запросах, чтобы сделать запись запроса более короткой, т.е.:
SELECT * FROM weather w, cities c WHERE w.city = c.name;
Вы увидите, что такой стиль аббревиатур встречается часто.
Обновление данных в таблицах
Вы можете обновить существующие записи, используя команду UPDATE. Предположим вы обнаружили, что 28 ноября температура в обоих случаях была на два градуса ниже. Вы можете обновить данные так:
UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';
Посмотрите на новое состояние данных:
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 41 | 55 | 0 | 1994-11-29 Hayward | 35 | 52 | | 1994-11-29 (3 rows)
Представления (Views)
Вернитесь обратно к запросам в Section 2.6. Допустим, что комбинированный список записей погоды и местоположения городов представляет определенный интерес для вашего приложения, но вы не хотите выполнять соответствующий запрос каждый раз, когда он вам понадобится. Вы можете создать view(представление)
для этого запроса, которое получит имя, которое потом можно использовать в запросе как будто обращение происходит к обычной таблице.
CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
SELECT * FROM myview;
Создание необходимых представлений является одним из ключевых аспектов хорошего дизайна базы данных SQL. Представления позволяют вам скрывать подробности структуры ваших таблиц, которые могут изменяться по мере модернизации вашего приложения, сохраняя старые интерфейсы.
Представления могут быть использованы в любом месте, где используется реальная таблица. Нередко встречается построения представлений на основе других представлений.
Создание базы данных
h2>Notes
| [1] |
В качестве объяснения как это работает: Имена пользователей в PostgreSQL существуют отдельно от имен пользователей в операционной системе. Если вы подключаетесь к какой-либо базе данных, вы можете выбрать для подключения какое-нибудь имя пользователя PostgreSQL; если вы это не сделаете, то по умолчанию будет установлено такое-же имя, какое у вас в операционной системе. Однако, всегда существует пользователь PostgreSQL, который имеет такое же имя как и в операционной системе и от имени которого осуществляется запуск сервера, а кроме того этот пользователь всегда имеет права на создание баз данных. Вместо того, чтобы регистрироваться под этим пользователем, вы можете также задать опцию -U и в ней указать имя того пользователя PostgreSQL, которое вы хотите использовать при подключении.
Создание новой таблицы
Вы можете создать новую таблицу, указав имя таблицы, а затем все имена полей в этой таблице и их типы (Таблица описывает погоду -- прим. пер.):
CREATE TABLE weather ( city varchar(80), temp_lo int, -- низкая температура temp_hi int, -- высокая температура prcp real, -- количество осадков date date );
Вы можете ввести эти строки в psql с разделителями строк. psql понимает, что команда не завершена, пока не встретится точка с запятой.
Пустые символы (т.е. пробелы, табуляция и символы перевода строки) свободно могут использоваться в командах SQL. Это означает, что вы можете вводить команду с произвольным выравниванием или даже вообще вводить все в одной строке. После двух дефисов ("--") вводят комментарии. ВсЈ что идет за ними игнорируется до конца текущей строки. SQL не обращает внимание на регистр вводимых слов и идентификаторов, за исключением случаев, когда идентификаторы находятся в двойных кавычках, которые сохраняют регистр (чего нет в данном выше примере).
varchar(80) задаЈт тип данных, который может хранить символьные строки длиной до 80 символов. int - это обычный целочисленный тип. real - это тип данных, хранящий числа с плавающей точкой одинарной точности. Тип date говорит сам за себя. (Совершенно верно, поле с типом date так и называется дата. Удобно это или нет -- решать вам.)
PostgreSQL поддерживает таки полезные типы SQL как int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp и interval, а также и другие общеиспользуемые типы и богатый набор геометрических типов. PostgreSQL можно настроить так, чтобы он работал с произвольным числом типов данных, определенных пользователем. Следовательно, имена типов не являются синтаксическими ключевыми словами, за исключаем тех случаев, где требуются поддержка специально согласно стандарту SQL.
Второй пример сохранит города и соответствующие им географические координаты:
CREATE TABLE cities ( name varchar(80), location point );
Тип point - это пример специфического для PostgreSQL типа данных.
Наконец, если вам не нужна далее созданная вами таблица или если вы планируете пересоздать еЈ с другим набором полей, вы можете удалить еЈ используя команду:
DROP TABLE tablename;
Транзакции
Транзакции являются одним из фундаментальных концептов всех СУБД. Сущность транзакции состоит в связывании нескольких шагов в одну операцию по принципу все-или-ничего. Внутренние промежуточные состояния между шагами не видны для других конкурирующих транзакций и если во время выполнения транзакции случится ошибка, которая помешает транзакции завершится, то в базе данных никаких изменений сделано не будет.
Например, допустим, что есть база данных, которая содержит балансы для нескольких клиентов и общие депозитные балансы для филиалов. Предположим, что мы хотим внести поступление $100.00 от клиента Alice для клиента Bob. Простейшая команда, которая выполняет данную операцию может выглядеть так
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice'); UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob'; UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
Делали этих команд сейчас не важны; важно что здесь мы имеем дело с несколькими отдельными обновлениями (операторы update), которые реализуют нужную нам операцию. Наши банковские работники захотят сделать так, чтобы все эти обновления происходили сразу или чтобы не происходило ни одно из них. Это обусловлено тем, что в результате какой-либо системной ошибки может получиться так, что Bob получит $100.00, которые не будут вычтены у Alice. Или может случиться так, что у Alice будет вычтена эта сумма, но Bob еЈ не получит. Нам нужна гарантия, что если что-либо пойдет не так во время операций обновления, счетов, то никаких изменений фактически внесено не будет. Такую гарантию можно получить, если сгруппировать операторы update в транзакцию. Транзакция является атомарным
действием с точки зрения других транзакций и либо она завершится полностью успешно, либо никакие действия, составляющие транзакцию выполнены не будет.
Мы также хотим гарантировать, что одна полностью завершившаяся и подтверждЈнная СУБД транзакция является действительно сохранЈнной и не может быть потеряна, даже если после еЈ выполнения произойдет крах системы. Например, если мы сохраняем кэш перевода клиента Bob, мы не хотим, чтобы эти деньги клиента Bob потерялись в результате краха системы, который, например, может произойти в тот момент, когда Bob вышел за двери банка. Традиционные СУБД гарантируют что все обновления, осуществляемые в одной транзакции, протоколируются в надежное хранилище (т.е. на диск) перед тем как СУБД сообщит о завершении транзакции.
Другое важное свойство транзакционных СУБД состоит в строгой изоляции транзакций: когда несколько транзакций запускаются конкурентно, каждая из них не видит тех неполных изменений, которые производят другие транзакции. Например, если одна транзакция занята сложением всех балансов филиалов, она не должна учитывать как денег снятых со счета Alice так и денег пришедших на счет Bob. Таким образом транзакции должны выполнять принцип все-или-ничего не только в плане нерушимости тех изменений, которые они производят в базе данных, но и также в плане того, что они видят в момент работы. Обновления, которые вносит открытая транзакция являются невидимыми для других транзакций пока данная транзакция не завершиться, после чего все внесенные ей изменения станут видимыми.
В PostgreSQL транзакция - это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT. Таким образом наша банковская транзакция будет выглядеть так
BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; -- и т.д. .... COMMIT;
Если во время выполнения транзакции мы решаем, что не хотим завершать еЈ (например мы получили извещение о том, что счет Alice отрицательный), то мы вместо команды COMMIT выдаем команду ROLLBACK и все наши изменения от начала транзакции, будут отменены.
PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT
иногда называется транзакционным блоком.
Note: Некоторые клиентские библиотеки выполняют команды BEGIN и COMMIT автоматически, так что вы можете без вопросов организовывать транзакционные блоки. Проверьте документацию по тому интерфейсу, который вы используете.
Удаление данных из таблиц
Предположим, что вас больше не интересует погода в городе Hayward. Тогда вы можете удалить из таблицы соответствующие записи. Удаление выполняется с использованием команды DELETE:
DELETE FROM weather WHERE city = 'Hayward';
Все записи в таблице weather соответствующие городу Hayward будут удалены.
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 41 | 55 | 0 | 1994-11-29 (2 rows)
Будьте осторожны используя операторы в виде:
DELETE FROM tablename;
Без наличия ограничивающих условий, DELETE
удалит все записи из указанной таблицы, оставив еЈ пустой. Перед выполнением удаления, система не будет требовать подтверждения!
Установка
Перед тем как вы сможете использовать PostgreSQL, вам, конечно же, необходимо его установить. Возможно, что PostgreSQL уже установлен на вашей машине, или потому, что он включЈн в состав дистрибутива операционной системы или потому что системный администратор уже установил его. В этом случае, вы должны получить информацию из документации по операционной системе или от вашего системного администратора, о том как получить доступ к PostgreSQL.
Если вы не уверены в том, что PostgreSQL уже доступен или, что вы можете использовать его в ваших экспериментах, то вы можете установить его сами. Это нетрудно сделать и может быть полезно для вашего опыта. PostgreSQL может установить любой непривилегированный пользователь, но вам потребуются права суперпользователя (root).
Если вы устанавливаете PostgreSQL сами, то обратитесь к PostgreSQL Administrator's Guide за инструкциями по установке и вернитесь к данному руководству, когда завершите установку. Убедитесь, что вы прошли секцию, где рассказывается об установке соответствующих переменных окружения.
Если администратор вашей машины не произвел некоторых установок по умолчанию, то вы можете сделать это сами. Например, если машина с сервером баз данных является удаленной машиной, вам может понадобится прописать имя этой машины в перменную окружения PGHOST. Также может быть установлена и переменная PGPORT. В заключение можно сказать так: если вы пытаетесь запустить приложение и оно говорит, что не может подключиться к базе данных, то вы должны проконсультироваться с системным администратором или, если вы им и являетесь, прочитать документацию, чтобы убедиться, что вы правильно установили ваше окружение. Если вы не поняли предыдущий параграф, то прочтите следующий.
Внешние ключи (Foreign Keys)
Посмотрите на таблицы weather и cities из Chapter 2. На лицо следующая проблема: Вы хотите быть уверенными, что каждая из строк в таблице weather имеет соответствующую запись в таблице cities. Это называется обслуживанием ссылочной целостности ваших данных. В простейших СУБД вам потребовалось бы реализовать сперва просмотр таблицы cities, чтобы проверить существование нужной записи и только затем решить вставлять или отвергнуть вставку новых записей данных в таблицу weather. Такое решение создает некоторые проблемы и очень неудобно, так что PostgreSQL может сделать это для вас.
Новое описание этих таблиц будет выглядеть так:
CREATE TABLE cities ( city varchar(80) primary key, location point );
CREATE TABLE weather ( city varchar(80) references cities, temp_lo int, temp_hi int, prcp real, date date );
Теперь попытайтесь вставить неправильную запись:
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR: <unnamed> referential integrity violation - key referenced from weather not found in cities
Поведение внешних ключей может быть прекрасно использовано вашим приложением. Мы не ограничиваемся только этим простым примером в данном учебнике, но отсылаем вас за подробностями к PostgreSQL User's Guide
Правильное использование внешних ключей существенно увеличивает качество ваших приложений по работе с базами данных, так что мы очень рекомендуем вам научиться пользоваться ими.
В предыдущей главе мы рассмотрели
В предыдущей главе мы рассмотрели основы использования SQL для хранения и доступа к вашим данным в PostgreSQL. Теперь мы рассмотрим некоторые более расширенные возможности SQL которые упрощают управление и предохраняют от потери или повреждения данных. В заключении мы рассмотрим некоторые расширения PostgreSQL.
В данной главе мы будем ссылаться на примеры, которые можно найти на Chapter 2 для того, чтобы попробовать изменить или улучшить их так, чтобы они помогли вам, когда вы прочтЈте эту главу. Некоторые примеры из данной главы можно также найти в advanced.sql в каталоге tutorial. Этот файл также содержит некоторые данные для загрузки, которые не будут повторяться здесь. (Смотрите Section 2.1
чтобы понять как использовать этот файл.)
Данная глава представляет обзор использования SQL для выполнения простых операций. Данный учебник задумывался только для введения в SQL, а не как исчерпывающее руководство. По работе с SQL было написано несколько книг, включая Understanding the New SQL и A Guide to the SQL Standard. Вы должны получить представление о некоторых особенностях языка в PostgreSQL, которые являются расширениями стандарта.
В примерах, которые даны ниже, мы считаем, что вы создали базу данных с именем mydb, как описывалось в предыдущей главе, а также запустили psql.
Примеры, данные в этом руководстве, вы также можете найти в дистрибутиве исходных текстов PostgreSQL в каталоге src/tutorial/. Прочтите файл README в этом каталоге, чтобы увидеть как их использовать. Для запуска учебника, сделайте следующее:
$ cd ..../src/tutorial
$ psql -s mydb
...
mydb=> \i basics.sql
Команда \i читает другие команды из заданного файла. Опция -s переключает вас в режим пошагового выполнения, когда перед выполнением каждого оператора на сервере происходит пауза. Команды, используемые в данном разделе находятся в файле basics.sql.
PostgreSQL имеет множество возможностей не
PostgreSQL имеет множество возможностей не затронутых в данном учебнике, который ориентирован прежде всего на новичков в SQL. Эти возможности более детально описываются как в PostgreSQL User's Guide так и в PostgreSQL Programmer's Guide.
Если вам необходимо больше вводного материала, пожалуйста посетите Web сайт PostgreSQL
и посмотрите там ссылки на другие ресурсы.
Заполнение таблицы записями
Для помещения записей в таблицу используется оператор INSERT:
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
Обратите внимание, что все типы данных используемые в команде имеют соответствующие форматы. Константы, которые не являются простыми числовыми значениями обычно должны быть заключены в одинарные кавычки ('), как показано в примере. Тип date фактически может быть записан по-разному, но в данном учебнике мы будем придерживаться понятного формата, который показан в примере.
Тип point требует пару координат, как показано здесь:
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
Синтаксис, используемый здесь требует, чтобы вы помнили порядок полей. Альтернативная форма записи позволяет вам перечислять поля явно:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
Вы можете указать поля в другом порядке, если захотите это или даже опустить некоторые поля, например, если проценты неизвестны:
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
Многие разработчики считают, что явное перечисление полей является более лучшим стилем, чем использование неявного порядка следования полей.
Пожалуйста, вводите все команды данные выше так, чтобы у вас были какие-либо данные для работы с ними в следующих секциях.
Для загрузки большого количества данных из простого текстового файла, вы также можете использовать команду COPY. Обычно это работает быстрее, потому что команда COPY
оптимизирована для операции, которую она выполняет, но в то же время она менее гибкая чем команда INSERT. Вот пример еЈ использования:
COPY weather FROM '/home/user/weather.txt';
где файл, указанный как источник данных должен быть доступен на машине с backend сервером, а не на клиентской машине, потому что backend сервер читает этот файл напрямую. Вы можете прочитать подробности о команде COPY в PostgreSQL Reference Manual.
Запросы к таблицам
Для получения данных из какой-либо таблицы, к этой таблице осуществляется запрос. Для этого используется оператор SQL SELECT. Этот оператор подразделяется на список выбора (часть, где перечисляются возвращаемые запросом поля), список таблиц (часть, где перечисляются таблицы, из которых выбираются данные) и необязательную часть отбора (часть, где указываются разные ограничения). Например, чтобы получить все записи таблицы weather введите:
SELECT * FROM weather;
(здесь * означает "все поля") и вывод должен выглядеть так:
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows)
Вы можете задать какие-либо произвольные выражения в списке выбора. Например, вы можете сделать так:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
Что приведет к выводу:
city | temp_avg | date ---------------+----------+------------ San Francisco | 48 | 1994-11-27 San Francisco | 50 | 1994-11-29 Hayward | 45 | 1994-11-29 (3 rows)
Обратите внимание, как для слово AS используется для изменения заголовка выводимого поля. (Это необязательно).
В запросе, в части отбора, разрешаются произвольные Логические операторы (AND, OR и NOT). Например, следующий запрос получает погоду в Сан-Франциско в дождливые дни:
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
Результат:
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 (1 row)
В качестве последнего замечания, отметим что результаты запроса могут быть получены в отсортированном виде, а также с удалением записей, которые дублируют друг друга:
SELECT DISTINCT city FROM weather ORDER BY city;
city --------------- Hayward San Francisco (2 rows)
DISTINCT и ORDER BY, разумеется, могут использоваться и по отдельности.
