Учебник PostgreSQL 7.3.3
Агрегатные функции
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);
Этот синтаксис не является общеиспользуемым, но мы показываем этот пример, чтобы помочь вам понять последующий материал.