Как установить Postgresql на Linux и Windows с. 16

Название	Как установить Postgresql на Linux и Windows с. 16
Дата	10.07.2022
Размер	1.38 Mb.
Формат файла
Имя файла	potgresql.pdf
Тип	Документы #628132
страница	3 из 8

1 2 3 4 5 6 7 8

SELECT * FROM courses WHERE hours > 45;
c_no | title
| hours
-------+----------+-------
CS305 | Сети ЭВМ |
60
(1 row)
Условие должно иметь логический тип. Например, оно может содержать отношения =, <> (или !=), >, >=, <, <=;
может объединять более простые условия с помощью логических операций AND, OR, NOT и круглых скобок —
как в обычных языках программирования.
Тонкий момент представляет собой неопределенное значение NULL. В результирующую таблицу попада- ют только те строки, для которых условие фильтрации истинно; если же значение ложно или не определено,
строка отбрасывается.
Учтите:
• результат сравнения чего-либо с неопределенным значением не определен;
• результат логических операций с неопределенным значением, как правило, не определен (исключения:
true OR NULL = true, false AND NULL = false);
• для проверки определенности значения исполь- зуются специальные отношения IS NULL (IS NOT
NULL) и IS DISTINCT FROM (IS NOT DISTINCT FROM),
39

а также бывает удобно воспользоваться функцией coalesce.
Подробнее смотрите в документации: postgrespro.ru/
doc/functions-comparison.html
Соединения
Грамотно спроектированная реляционная база данных не содержит избыточных данных. Например, таблица экзаменов не должна содержать имя студента, потому что его можно найти в другой таблице по номеру сту- денческого билета.
Поэтому для получения всех необходимых значений в запросе часто приходится соединять данные из несколь- ких таблиц, перечисляя их имена во фразе FROM:
test=# SELECT * FROM courses, exams;
c_no |
title
| hours | s_id | c_no | score
-------+-------------+-------+------+-------+-------
CS301 | Базы данных |
30 | 1451 | CS301 |
5
CS305 | Сети ЭВМ
|
60 | 1451 | CS301 |
5
CS301 | Базы данных |
30 | 1556 | CS301 |
5
CS305 | Сети ЭВМ
|
60 | 1556 | CS301 |
5
CS301 | Базы данных |
30 | 1451 | CS305 |
5
CS305 | Сети ЭВМ
|
60 | 1451 | CS305 |
5
CS301 | Базы данных |
30 | 1432 | CS305 |
4
CS305 | Сети ЭВМ
|
60 | 1432 | CS305 |
4
(8 rows)
То, что у нас получилось, называется прямым или декар- товым произведением таблиц — к каждой строке одной таблицы добавляется каждая строка другой.
40

Как правило, более полезный и содержательный резуль- тат можно получить, указав во фразе WHERE условие со- единения. Получим оценки по всем дисциплинам, сопо- ставляя курсы с теми экзаменами, которые проводились именно по данному курсу:
test=# SELECT courses.title, exams.s_id, exams.score
FROM courses, exams
WHERE courses.c_no = exams.c_no;
title
| s_id | score
-------------+------+-------
Базы данных | 1451 |
5
Базы данных | 1556 |
5
Сети ЭВМ
| 1451 |
5
Сети ЭВМ
| 1432 |
4
(4 rows)
Запросы можно формулировать и в другом виде, ука- зывая соединения с помощью ключевого слова JOIN.
Выведем студентов и их оценки по курсу «Сети ЭВМ»:
test=# SELECT students.name, exams.score
FROM students
JOIN exams
ON students.s_id = exams.s_id
AND exams.c_no = 'CS305';
name | score
--------+-------
Анна
|
5
Виктор |
4
(2 rows)
С точки зрения СУБД обе формы эквивалентны, так что можно использовать тот способ, который представляет- ся более наглядным.
41

Этот пример показывает, что в результат не включают- ся строки исходной таблицы, для которых не нашлось пары в другой таблице: хотя условие наложено на дис- циплины, но при этом исключаются и студенты, которые не сдавали экзамен по данной дисциплине. Чтобы в вы- борку попали все студенты, независимо от того, сдава- ли они экзамен или нет, надо использовать операцию внешнего соединения:
test=# SELECT students.name, exams.score
FROM students
LEFT JOIN exams
ON students.s_id = exams.s_id
AND exams.c_no = 'CS305';
name | score
--------+-------
Анна
|
5
Виктор |
4
Нина
|
(3 rows)
В этом примере в результат добавляются строки из ле- вой таблицы (поэтому операция называется LEFT JOIN),
для которых не нашлось пары в правой. При этом для столбцов правой таблицы возвращаются неопределен- ные значения.
Условия во фразе WHERE применяются к уже готовому результату соединений, поэтому, если вынести ограни- чение на дисциплины из условия соединения, Нина не попадет в выборку — ведь для нее exams.c_no не опре- делен:
42

test=# SELECT students.name, exams.score
FROM students
LEFT JOIN exams ON students.s_id = exams.s_id
WHERE exams.c_no = 'CS305';
name | score
--------+-------
Анна
|
5
Виктор |
4
(2 rows)
Не стоит опасаться соединений. Это обычная и есте- ственная для реляционных СУБД операция, и у Post- greSQL имеется целый арсенал эффективных механиз- мов для ее выполнения. Не соединяйте данные в при- ложении, доверьте эту работу серверу баз данных — он прекрасно с ней справляется.
Подробнее смотрите в документации: postgrespro.ru/
doc/sql-select.html#SQL-FROM
Подзапросы
Оператор SELECT формирует таблицу, которая (как мы уже видели) может быть выведена в качестве резуль- тата, а может быть использована в другой конструкции языка SQL в любом месте, где по смыслу может нахо- диться таблица. Такая вложенная команда SELECT, за- ключенная в круглые скобки, называется подзапросом.
Если подзапрос возвращает одну строку и один столбец,
его можно использовать как обычное скалярное выра- жение:
43

test=# SELECT name,
(SELECT score
FROM exams
WHERE exams.s_id = students.s_id
AND exams.c_no = 'CS305')
FROM students;
name | score
--------+-------
Анна
|
5
Виктор |
4
Нина
|
(3 rows)
Если подзапрос, использованный в списке выражений
SELECT, не содержит ни одной строки, возвращается неопределенное значение (как в последней строке ре- зультата примера).
Такие скалярные подзапросы можно использовать и в условиях фильтрации. Получим все экзамены, которые сдавали студенты, поступившие после 2014 года:
test=# SELECT *
FROM exams
WHERE (SELECT start_year
FROM students
WHERE students.s_id = exams.s_id) > 2014;
s_id | c_no | score
------+-------+-------
1556 | CS301 |
5
(1 row)
В SQL можно формулировать условия и на подзапро- сы, возвращающие произвольное количество строк. Для
44

этого существует несколько конструкций, одна из кото- рых — отношение IN — проверяет, содержится ли зна- чение в таблице, возвращаемой подзапросом.
Выведем студентов, получивших оценки по указанному курсу:
test=# SELECT name, start_year
FROM students
WHERE s_id IN (SELECT s_id
FROM exams
WHERE c_no = 'CS305');
name | start_year
--------+------------
Анна
|
2014
Виктор |
2014
(2 rows)
Вариантом является отношение NOT IN, возвращающее противоположный результат. Например, список студен- тов, получивших только отличные оценки (то есть не получивших более низкие оценки):
test=# SELECT name, start_year
FROM students
WHERE s_id NOT IN (SELECT s_id
FROM exams
WHERE score < 5);
name | start_year
------+------------
Анна |
2014
Нина |
2015
(2 rows)
45

Другая возможность — предикат EXISTS, проверяющий,
что подзапрос возвратил хотя бы одну строку. С его по- мощью можно записать предыдущий запрос в другом виде:
test=# SELECT name, start_year
FROM students
WHERE NOT EXISTS (SELECT s_id
FROM exams
WHERE exams.s_id = students.s_id
AND score < 5);
name | start_year
------+------------
Анна |
2014
Нина |
2015
(2 rows)
Подробнее смотрите в документации: postgrespro.ru/
doc/functions-subquery.html
В примерах выше мы уточняли имена столбцов назва- ниями таблиц, чтобы избежать неоднозначности. Иногда этого недостаточно. Например, в запросе одна и та же таблица может участвовать два раза, или вместо табли- цы в предложении FROM мы можем использовать безы- мянный подзапрос. В этих случаях после подзапроса можно указать произвольное имя, которое называется псевдонимом (alias). Псевдонимы можно использовать и для обычных таблиц.
Имена студентов и их оценки по предмету «Базы дан- ных»:
46

test=# SELECT s.name, ce.score
FROM students s
JOIN (SELECT exams.*
FROM courses, exams
WHERE courses.c_no = exams.c_no
AND courses.title = 'Базы данных') ce
ON s.s_id = ce.s_id;
name | score
------+-------
Анна |
5
Нина |
5
(2 rows)
Здесь s — псевдоним таблицы, а ce — псевдоним под- запроса. Псевдонимы обычно выбирают так, чтобы они были короткими, но оставались понятными.
Тот же запрос можно записать и без подзапросов, на- пример, так:
test=# SELECT s.name, e.score
FROM students s, courses c, exams e
WHERE c.c_no = e.c_no
AND c.title = 'Базы данных'
AND s.s_id = e.s_id;
Сортировка
Как уже говорилось, данные в таблицах не упорядоче- ны, но часто бывает важно получить строки результата в строго определенном порядке. Для этого используется предложение ORDER BY со списком выражений, по кото- рым надо выполнить сортировку. После каждого выра- жения (ключа сортировки) можно указать направление:
47

ASC — по возрастанию (этот порядок используется по умолчанию) или DESC — по убыванию.
test=# SELECT * FROM exams
ORDER BY score, s_id, c_no DESC;
s_id | c_no | score
------+-------+-------
1432 | CS305 |
4 1451 | CS305 |
5 1451 | CS301 |
5 1556 | CS301 |
5
(4 rows)
Здесь строки упорядочены сначала по возрастанию оценки, для совпадающих оценок — по возрастанию номера студенческого билета, а при совпадении первых двух ключей — по убыванию номера курса.
Операцию сортировки имеет смысл выполнять в конце запроса непосредственно перед получением результата;
в подзапросах она обычно бессмысленна.
Подробнее смотрите в документации: postgrespro.ru/
doc/sql-select.html#SQL-ORDERBY.
Группировка
При группировке в одной строке результата размещает- ся значение, вычисленное на основании данных несколь- ких строк исходных таблиц. Вместе с группировкой ис- пользуют агрегатные функции. Например, выведем об- щее количество проведенных экзаменов, количество сдававших их студентов и средний балл:
48

test=# SELECT count(*), count(DISTINCT s_id),
avg(score)
FROM exams;
count | count |
avg
-------+-------+--------------------
4 |
3 | 4.7500000000000000
(1 row)
Аналогичную информацию можно получить в разбивке по номерам курсов с помощью предложения GROUP BY,
в котором указываются ключи группировки:
test=# SELECT c_no, count(*),
count(DISTINCT s_id), avg(score)
FROM exams
GROUP BY c_no;
c_no | count | count |
avg
-------+-------+-------+--------------------
CS301 |
2 |
2 | 5.0000000000000000
CS305 |
2 |
2 | 4.5000000000000000
(2 rows)
Полный список агрегатных функций: postgrespro.ru/
doc/functions-aggregate.html.
В запросах, использующих группировку, может возник- нуть необходимость отфильтровать строки на основании результатов агрегирования. Такие условия можно задать в предложении HAVING. Отличие от WHERE состоит в том,
что условия WHERE применяются до группировки (в них можно использовать столбцы исходных таблиц), а усло- вия HAVING — после группировки (и в них можно также использовать столбцы таблицы-результата).
Выберем имена студентов, получивших более одной пятерки по любому предмету:
49

test=# SELECT students.name
FROM students, exams
WHERE students.s_id = exams.s_id AND exams.score = 5
GROUP BY students.name
HAVING count(*) > 1;
name
------
Анна
(1 row)
Подробнее смотрите в документации: postgrespro.ru/
doc/sql-select.html#SQL-GROUPBY.
Изменение и удаление данных
Изменение данных в таблице выполняет оператор UPDATE,
в котором указываются новые значения полей для строк,
определяемых предложением WHERE (таким же, как в опе- раторе SELECT).
Например, увеличим число лекционных часов для курса
«Базы данных» в два раза:
test=# UPDATE courses
SET hours = hours * 2
WHERE c_no = 'CS301';
UPDATE 1
Подробнее смотрите в документации: postgrespro.ru/
doc/sql-update.html.
Оператор DELETE удаляет из указанной таблицы строки,
определяемые все тем же предложением WHERE:
50

test=# DELETE FROM exams WHERE score < 5;
DELETE 1
Подробнее смотрите в документации: postgrespro.ru/
doc/sql-delete.html.
Транзакции
Давайте немного расширим нашу схему данных и рас- пределим студентов по группам. При этом потребуем,
чтобы у каждой группы в обязательном порядке был староста. Для этого создадим таблицу групп:
test=# CREATE TABLE groups(
g_no text PRIMARY KEY,
monitor integer NOT NULL REFERENCES students(s_id)
);
CREATE TABLE
Здесь мы использовали ограничение целостности NOT
NULL, которое запрещает неопределенные значения.
Теперь в таблице студентов нам необходимо еще одно поле — номер группы, — о которым мы не подумали при создании. К счастью, в уже существующую таблицу можно добавить новый столбец:
test=# ALTER TABLE students
ADD g_no text REFERENCES groups(g_no);
ALTER TABLE
51

Мы столкнулись с тем, что две операции надо совер- шить одновременно, потому что ни одна из них не име- ет смысла без другой. Такие операции, составляющие логически неделимую единицу работы, называются тран-
закцией.
Начнем транзакцию:
test=# BEGIN;
BEGIN
Затем добавим группу вместе со старостой. Поскольку мы не помним наизусть номер студенческого билета
Анны, выполним запрос прямо в команде добавления строк:
test=# INSERT INTO groups(g_no, monitor)
SELECT 'A-101', s_id
FROM students
WHERE name = 'Анна';
INSERT 0 1
Откройте теперь новое окно терминала и запустите еще один процесс psql: это будет сеанс, работающий парал- лельно с первым.
Чтобы не запутаться, команды второго сеанса мы будем показывать с отступом. Увидит ли он сделанные измене- ния?
postgres=# \c test
You are now connected to database "test" as user
"postgres".
53

test=# SELECT * FROM groups;
g_no | monitor
------+---------
(0 rows)
Нет, не увидит, ведь транзакция еще не завершена.
Теперь переведем всех студентов в созданную группу:
test=# UPDATE students SET g_no = 'A-101';
UPDATE 3
И снова второй сеанс видит согласованные данные, ак- туальные на начало еще не оконченной транзакции:
test=# SELECT * FROM students;
s_id | name | start_year | g_no
------+--------+------------+------
1451 | Анна
|
2014 |
1432 | Виктор |
2014 |
1556 | Нина
|
2015 |
(3 rows)
А теперь завершим транзакцию, зафиксировав все из- менения:
test=# COMMIT;
COMMIT
И только в этот момент второму сеансу становятся до- ступны все изменения, сделанные в транзакции, как будто они появились одномоментно:
54

test=# SELECT * FROM groups;
g_no | monitor
-------+---------
A-101 |
1451
(1 row)
test=# SELECT * FROM students;
s_id | name | start_year | g_no
------+--------+------------+-------
1451 | Анна
|
2014 | A-101 1432 | Виктор |
2014 | A-101 1556 | Нина
|
2015 | A-101
(3 rows)
СУБД гарантирует выполнение нескольких важных свойств.
Во-первых, транзакция либо выполняется целиком (как в нашем примере), либо не выполняется совсем. Если бы в одной из команд произошла ошибка, или мы сами прервали бы транзакцию командой ROLLBACK, то ба- за данных осталась бы в том состоянии, в котором она была до команды BEGIN. Это свойство называется ато-
марностью.
Во-вторых, когда фиксируются изменения транзакции,
все ограничения целостности должны быть выполнены,
иначе транзакция прерывается. Таким образом, в нача- ле транзакции данные находятся в согласованном со- стоянии, и в конце своей работы транзакция оставляет их согласованными; это свойство так и называется —
согласованность.
В-третьих, как мы убедились на примере, другие поль- зователи никогда не увидят несогласованные данные,
55

которые транзакция еще не зафиксировала. Это свой- ство называется изоляцией; за счет его соблюдения
СУБД способна параллельно обслуживать много сеансов,
не жертвуя корректностью данных. Особенностью Post- greSQL является очень эффективная реализация изоля- ции: несколько сеансов могут одновременно читать и изменять данные, не блокируя друг друга. Блокировка возникает только при одновременном изменении одной и той же строки двумя разными процессами.
И в-четвертых, гарантируется

1 2 3 4 5 6 7 8