Команда \i считывает и выполняет команды из заданного файла. Переданный psql параметр -s переводит его в пошаговый режим, когда он делает паузу перед отправкой каждого оператора серверу.
PostgreSQL — это реляционная система управления базами данных (РСУБД). Это означает, что это система управления данными, представленными в виде отношений (relation). Отношение — это математически точное обозначение таблицы. Хранение данных в таблицах так распространено сегодня, что это кажется самым очевидным вариантом, хотя есть множество других способов организации баз данных. Например, файлы и каталоги в Unix-подобных операционных системах образуют иерархическую базу данных, а сегодня активно развиваются объектно-ориентированные базы данных
Любая таблица представляет собой именованный набор строк. Все строки таблицы имеют одинаковый набор именованных столбцов, при этом каждому столбцу назначается определённый тип данных. Хотя порядок столбцов во всех строках фиксирован, важно помнить, что SQL не гарантирует какой-либо порядок строк в таблице (хотя их можно явно отсортировать при выводе).
Таблицы объединяются в базы данных, а набор баз данных, управляемый одним экземпляром сервера PostgreSQL, образует кластер баз данных.
Создание таблицы
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- минимальная температура дня
temp_hi int, -- максимальная температура дня
prcp real, -- уровень осадков
date date
);
Связать таблицы по одному из полей
Как в одной таблице указать, что данное поле — то же самое, что аналогичное поле в другой таблице?
create table tb1 (
login varchar(20) unique,
...
);
create table tb2 (
login varchar(20) references tb1,
..
);
Добавление строк в таблицу
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
Константы, за исключением простых числовых значений, обычно заключаются в апострофы ('), как в данном примере. Тип date на самом деле очень гибок и принимает разные форматы, но в данном введении мы будем придерживаться простого и однозначного. Показанный здесь синтаксис требует, чтобы вы запомнили порядок столбцов. Можно также применить альтернативную запись, перечислив столбцы явно:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
Вы также можете загрузить большой объём данных из обычных текстовых файлов, применив команду COPY. Обычно это будет быстрее, так как команда COPY оптимизирована для такого применения, хотя и менее гибка, чем INSERT. Например, её можно использовать так:
COPY weather FROM '/home/user/weather.txt';
Выполнение запросов
Для этого предназначен SQL-оператор SELECT. Он состоит из нескольких частей: выборки (в которой перечисляются столбцы, которые должны быть получены), списка таблиц (в нём перечисляются таблицы, из которых будут получены данные) и необязательного условия (определяющего ограничения).
В списке выборки вы можете писать не только ссылки на столбцы, но и выражения. Например, вы можете написать:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
Запрос можно дополнить "условием", добавив предложение WHERE, ограничивающее множество возвращаемых строк. В предложении WHERE указывается логическое выражение (проверка истинности), которое служит фильтром строк: в результате оказываются только те строки, для которых это выражение истинно. В этом выражении могут присутствовать обычные логические операторы (AND, OR и NOT).
Следующий запрос покажет, какая погода была в Сан-Франциско в дождливые дни:
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
Соединения таблиц
Запрос, обращающийся к разным наборам строк одной или нескольких таблиц, называется соединением (JOIN). Например, мы захотели перечислить все погодные события вместе с координатами соответствующих городов. Для этого мы должны сравнить столбец city каждой строки таблицы weather со столбцом name всех строк таблицы cities и выбрать пары строк, для которых эти значения совпадают.
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
В результате нет строки с городом Хейуорд (Hayward). Так получилось потому, что в таблице cities нет строки для данного города, а при соединении все строки таблицы weather, для которых не нашлось соответствие, опускаются. Вскоре мы увидим, как это можно исправить. Название города оказалось в двух столбцах. Это правильно и объясняется тем, что столбцы таблиц weather и cities были объединены. Хотя на практике это нежелательно, поэтому лучше перечислить нужные столбцы явно, а не использовать *
Запросы соединения, которые вы видели до этого, можно также записать в другом виде:
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
Агрегатные функции
Агрегатная функция вычисляет единственное значение, обрабатывая множество строк. Например, есть агрегатные функции, вычисляющие: count (количество), sum (сумму), avg (среднее), max (максимум) и min (минимум) для набора строк. мы можем найти самую высокую из всех минимальных дневных температур:
SELECT max(temp_lo) FROM weather;
Можно получить желаемый результат, применив подзапрос:
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
Теперь всё в порядке — подзапрос выполняется отдельно и результат агрегатной функции вычисляется вне зависимости от того, что происходит во внешнем запросе.
Агрегатные функции также очень полезны в сочетании с предложением GROUP BY. Например, мы можем получить максимум минимальной дневной температуры в разрезе городов:
SELECT city, max(temp_lo) FROM weather GROUP BY city;
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
Важно понимать, как соотносятся агрегатные функции и SQL-предложения WHERE и HAVING. Основное отличие WHERE от HAVING заключается в том, что WHERE сначала выбирает строки, а затем группирует их и вычисляет агрегатные функции (таким образом, она отбирает строки для вычисления агрегатов), тогда как HAVING отбирает строки групп после группировки и вычисления агрегатных функций. Как следствие, предложение WHERE не должно содержать агрегатных функций; не имеет смысла использовать агрегатные функции для определения строк для вычисления агрегатных функций. Предложение HAVING, напротив, всегда содержит агрегатные функции. (Строго говоря, вы можете написать предложение HAVING, не используя агрегаты, но это редко бывает полезно. То же самое условие может работать более эффективно на стадии WHERE.)
В предыдущем примере мы смогли применить фильтр по названию города в предложении WHERE, так как названия не нужно агрегировать. Такой фильтр эффективнее, чем дополнительное ограничение HAVING, потому что с ним не приходится группировать и вычислять агрегаты для всех строк, не удовлетворяющих условию WHERE.
Изменение данных
используется команда UPDATE
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
Удаление данных
используется команда DELETE
DELETE FROM weather WHERE city = 'Hayward';
Без указания условия DELETE удалит все строки данной таблицы, полностью очистит её. При этом система не попросит вас подтвердить операцию!