Язык SQL
2.1. Введение
Данная глава представляет обзор использования SQL для выполнения простых операций. Данный учебник задумывался только для введения в SQL, а не как исчерпывающее руководство. Вы должны получить представление о некоторых особенностях языка в PostgreSQL, которые являются расширениями стандарта.
В примерах, которые даны ниже, мы считаем, что вы создали базу данных с именем mydb, как описывалось в предыдущей главе, а также запустили psql.
Примеры, данные в этом руководстве, вы также можете найти в дистрибутиве исходных текстов PostgreSQL в каталоге src/tutorial/. Прочтите файл README в этом каталоге, чтобы увидеть как их использовать. Для запуска учебника, сделайте следующее:
$ cd ..../src/tutorial $ psql -s mydb ... mydb=> \i basics.sql
Команда \i читает другие команды из заданного файла. Опция -s переключает вас в режим пошагового выполнения, когда перед выполнением каждого оператора на сервере происходит пауза. Команды, используемые в данном разделе находятся в файле basics.sql.
2.2. Концепции
PostgreSQL - это система управления реляционными базами данных (СУБД). Это означает, что это система для управления данными, которые хранятся в виде отношений. (В учебниках по СУБД существуют и другие термины, например, кортежи -- прим. пер.). Отношение - это математический термин для таблицы. Понятие хранения данных в таблицах является сегодня таким банальным, что оно может показаться самоочевидным, однако есть несколько других способов организации баз данных. Файлы и каталоги в Unix-подобных операционных системах являются примером иерархической базы данных. Одно из наиболее современных направлений разработки СУБД - это объектно-ориентированные базы данных.
Каждая таблица является поименованной коллекцией строк (rows) (в русской литературе по SQL преимущественно говорят "записей" -- прим. пер.). Каждая запись в таблице имеет некоторый набор поименованных колонок (columns) (опять-таки в русской литературе преимущественно говорят "полей" -- прим. пер.) и каждое поле является определенным типом данных. Поля в записи расположены в фиксированный порядке, важно помнить, что SQL никогда не гарантирует упорядоченного следования записей в таблице (за исключением случая, когда они могут быть явно отсортированы для выдачи пользователю).
Таблицы группируются в базы данных, а коллекция баз данных, управляемая одной копией сервера PostgreSQL называется кластером баз данных.
2.3. Создание новой таблицы
Вы можете создать новую таблицу, указав имя таблицы, а затем все имена полей в этой таблице и их типы (Таблица описывает погоду -- прим. пер.):
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;
2.4. Заполнение таблицы записями
Для помещения записей в таблицу используется оператор 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.
2.5. Запросы к таблицам
Для получения данных из какой-либо таблицы, к этой таблице осуществляется запрос. Для этого используется оператор 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, разумеется, могут использоваться и по отдельности.
2.6. Объединения таблиц (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;
Вы увидите, что такой стиль аббревиатур встречается часто.
2.7. Агрегатные функции
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%'
GROUP BY city
HAVING max(temp_lo) < 40;
Оператор LIKE выполняет сравнение по шаблону и описывается подробно в PostgreSQL User's Guide.
Очень важно понимать различия между агрегатами и такими конструкциями SQL как WHERE и HAVING. Фундаментальное отличие между WHERE и HAVING состоит в следующем: WHERE выбирает входящие записи перед группированием и вычислениями агрегатами (а значит управляет тем, какие записи попадут в агрегатные функции), в то время как HAVING выбирает группу записей после группирования и вычисления агрегатов. Таким образом, предложение WHERE не должно содержать агрегатных функций; оно не оказывает влияния на попытку использовать агрегаты для того, чтобы определить какие записи будут переданы агрегатам. С другой стороны предложение HAVING всегда содержит агрегатные функции. (Вкратце, вам разрешается писать предложение HAVING, которое не использует агрегаты, но это непроизводительно: такое же условие можно использовать в предложении WHERE с большей эффективностью).
Обратите внимание, что мы можем применять ограничения по имени города в предложении WHERE, так как оно не требует использования агрегата. Это будет более эффективно, чем добавлять это ограничение в предложение HAVING, потому что мы пропускаем операции группирования и вычисления агрегатов для всех записей, которые не соответствуют условию в предложении WHERE.
2.8. Обновление данных в таблицах
Вы можете обновить существующие записи, используя команду 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)
2.9. Удаление данных из таблиц
Предположим, что вас больше не интересует погода в городе 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 удалит все записи из указанной таблицы, оставив еЈ пустой. Перед выполнением удаления, система не будет требовать подтверждения!
Комментарии