Язык SQL. Общие сведения

Введение

В этой главе рассматривается применение SQL для выполнения простых операций. Это руководство предназначено только для ознакомления и никоим образом не является полным справочным руководством по SQL. Следует также учитывать, что некоторые возможности языка QHB являются расширениями стандарта.

В следующих примерах мы предполагаем, что вы создали базу данных mydb, как описано в главе Начало работы, и смогли запустить psql, как описано в разделе psql.

Основные положения

QHB — это реляционная система управления базами данных (РСУБД). Это означает, что это система управления данными, представленными в виде отношений. По сути, отношение — это математическое обозначение таблицы. Хранение данных в таблицах на сегодняшний день распространено настолько, что это может показаться заведомо очевидным, но все же существует ряд других способов организации баз данных. К примеру, файлы и каталоги в Unix-подобных операционных системах образуют иерархическую базу данных. К более современным разработкам относятся объектно- ориентированные базы данных.

Любая таблица представляет собой именованный набор строк. Каждая строка данной таблицы имеет одинаковый набор именованных столбцов, а каждому столбцу присваивается определенный тип данных. Несмотря на то, что порядок столбцов во всех строках фиксирован, важно помнить, что SQL никоим образом не гарантирует порядок строк в таблице (хотя их можно явно отсортировать при выводе).

Таблицы объединены в базы данных, а набор баз данных, управляемых одним экземпляром сервера QHB, составляет кластер баз данных.

Создание таблицы

Вы можете создать таблицу, указав ее имя вместе с именами всех столбцов и их типами:

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 тоже называется date. Это может быть удобно или сбивать с толку — как вам больше нравится.)

QHB поддерживает стандартные типы SQL int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp и interval, а также другие типы общего назначения и обширный набор геометрических типов. Кроме того, QHB можно расширить произвольным числом пользовательских типов данных. Поэтому в записях имена типов не являются ключевыми словами, кроме ситуаций, когда это требуется для поддержки особых случаев в стандарте SQL.

Во второй таблице будут храниться города и их географическое местоположение:

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

Тип point — пример специфического типа данных QHB.

Наконец, следует упомянуть, что если вам больше не нужна таблица или вы хотите пересоздать ее по-другому, ее можно удалить с помощью следующей команды:

DROP TABLE имя_таблицы;

Наполнение таблицы строками

Для наполнения таблицы строками используется команда 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';

где имя исходного файла должно быть доступно на компьютере, где запущен серверный процесс, а не на клиенте, поскольку серверный процесс читает этот файл напрямую. Подробнее о команде COPY можно узнать на соответствующей справочной странице.

Запросы к таблице

Для получения данных из таблицы нужно выполнить запрос. Для этого применяется оператор SQL SELECT. Этот оператор делится на список выборки (часть, в которой перечисляются столбцы, которые должны быть возвращены), список таблиц (часть, в которой перечислены таблицы, из которых необходимо получить данные), и необязательное уточнение (часть, в которой указываются ограничения). Например, чтобы получить все строки из таблицы weather, напишите:

SELECT * FROM weather;

Здесь * — это краткое обозначение для «всех столбцов».1 Этот же результат можно получить с помощью запроса:

SELECT city, temp_lo, temp_hi, prcp, date 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.

Запрос можно уточнить, добавив предложение WHERE, указывающее, какие строки необходимо вернуть. Предложение WHERE содержит логическое выражение (проверку истинности), и возвращаются только те строки, для которых это логическое выражение имеет значение true. В этом уточнении можно использовать обычные логические операторы (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 * FROM weather
    ORDER BY city;

city           | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
Hayward        |      37 |      54 |      | 1994-11-29
San Francisco  |      43 |      57 |    0 | 1994-11-29
San Francisco  |      46 |      50 | 0.25 | 1994-11-27

В этом примере порядок сортировки задан не полностью, поэтому вы можете получить строки Сан-Франциско в любом порядке. Но вы всегда получите показанные выше результаты, если введете:

SELECT * FROM weather
    ORDER BY city, temp_lo;

Можно потребовать, чтобы из результата запроса были удалены повторяющиеся строки:

SELECT DISTINCT city
    FROM weather;

city
---------------
Hayward
San Francisco
(2 rows)

И в этом случае порядок результирующих строк может варьироваться. Чтобы гарантировать согласующиеся результаты, следует использовать одновременно DISTINCT и ORDER BY:2

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

Соединения таблиц

До сих пор наши запросы обращались только к одной таблице за раз. Однако запросы могут обращаться сразу к нескольким таблицам или к одной таблице, но так, что одновременно будут обрабатываться несколько наборов ее строк. Запросы, обращающиеся к нескольким таблицам (или нескольким экземплярам одной таблицы) одновременно, называются соединениями (JOIN). Они объединяют строки из одной таблицы со строками из второй таблицы с помощью выражения, указывающего, какие пары строк должны образоваться. Например, чтобы вернуть все погодные записи вместе с координатами соответствующего города, базе данных нужно сравнить столбец city каждой строки таблицы weather со столбцом name всех строк таблицы cities и выбрать пары строк, где эти значения совпадают.3 Это можно сделать с помощью следующего запроса:

SELECT * FROM weather JOIN cities ON 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 JOIN cities ON city = name;

Поскольку у всех столбцов разные имена, анализатор автоматически обнаружил, какой таблице они принадлежат. Если же в двух таблицах имена столбцов повторяются, то необходимо дополнить имена столбцов, чтобы показать, какой конкретно имеется в виду:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;

В целом, считается хорошим тоном дополнять все имена столбцов в запросе с соединениями, чтобы запрос не завершился ошибкой, если позднее в одну из таблиц будет добавлен столбец с повторяющимся именем.

Запросы соединения, которые были показаны ранее, также можно записать в таком виде:

SELECT *
    FROM weather, cities
    WHERE city = name;

Этот синтаксис предшествует синтаксису JOIN/ON введенному в SQL-92. Таблицы просто перечисляются в предложении FROM, а выражение сравнения добавляется в предложение WHERE. Результаты, получаемые из этого старого неявного синтаксиса и нового явного синтаксиса JOIN/ON, одинаковы. Но читающему запрос проще понять смысл явного синтаксиса: условие соединения вводится с помощью собственного ключевого слова, тогда как раньше это условие включалось в предложение WHERE вместе с другими условиями.

Теперь мы выясним, как вернуть записи по Хейварду. Мы хотим, чтобы запрос просканировал таблицу weather и для каждой строки в ней нашел соответствующие строки cities. Если подходящей строки не найдено, мы хотим, чтобы вместо столбцов таблицы cities были выведены некоторые «пустые значения». Этот вид запроса называется внешним соединением. (Соединения, которые мы видели до сих пор, являются внутренними.) Команда выглядит так:

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)

Этот запрос называется левым внешним соединением, поскольку в вывод хотя бы по разу попадут все строки таблицы, указанной слева от оператора соединения, тогда как из таблицы справа туда попадут только строки, соответствующие какой-либо строке в левой таблице. При выводе строки левой таблицы, для которой нет совпадения в правой, вместо столбцов правой таблицы подставляются пустые значения (NULL).

Существуют также правые и полные внешние соединения.

Кроме того, можно соединить таблицу саму с собой. Это называется самосоединением. К примеру, предположим, что мы хотим найти все записи о погоде, находящиеся в диапазоне температур других записей о погоде. Поэтому нам нужно сравнить столбцы 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 JOIN weather w2
        ON 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)

Здесь мы переименовали таблицу погоды на w1 и w2 чтобы можно было различить левую и правую стороны соединения. Подобные псевдонимы можно использовать и в других запросах, чтобы сэкономить время на ввод, например:

SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

Такой тип сокращения встречается довольно часто.

Агрегатные функции

Как и большинство других СУБД для реляционных баз данных, QHB поддерживает агрегатные функции. Агрегатная функция вычисляет единственный результат из множества входных строк. Например, есть агрегаты для вычисления количества (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 ниже 40. Наконец, если нам нужны только города, названия которых начинаются с «S», можно написать:

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

(1): Синтаксис оператора LIKE, выполняющего сравнение по шаблону, рассматривается в разделе Функции и операторы.

Важно понимать взаимодействие между агрегатными функциями и предложениями SQL WHERE и HAVING. Принципиальное различие между WHERE и HAVING заключается в том, что WHERE сначала выбирает строки, а потом группирует их и вычисляет агрегатные функции (таким образом, оно определяет, какие строки пойдут в вычисление агрегатов), тогда как HAVING выбирает группы строк после группировки и вычисления агрегатных функций. Таким образом, WHERE не должно содержать агрегатных функций; нет смысла использовать агрегатную функцию для определения входных строк для агрегатных функций. С другой стороны, предложение HAVING всегда содержит агрегатные функции. (Строго говоря, вы можете написать HAVING, в котором нет агрегатных функций, но едва ли это будет полезно. То же самое условие с большей эффективностью можно использовать на стадии WHERE).

В предыдущем примере мы смогли применить в WHERE ограничение по названию города, так как это не агрегатная функция. Это более эффективно, чем добавлять ограничение к HAVING, поскольку так мы избегаем группировок и вычисления агрегатов для всех строк, не удовлетворяющих условию WHERE.

Изменение строк

Вы можете изменять существующие строки с помощью команды UPDATE. Предположим, вы обнаружили, что показания температуры после 28 ноября завышены на 2 градуса. Вы можете исправить данные следующим образом:

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)

Удаление строк

Строки можно удалить из таблицы с помощью команды DELETE. Предположим, вас больше не интересует погода в Хейварде. Вы можете удалить эти строки из таблицы следующим образом:

DELETE FROM weather WHERE city = '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 имя_таблицы;

Без уточнения DELETE удалит все строки из заданной таблицы, опустошив ее. При этом система не запросит предварительное подтверждение операции!

1

Хотя SELECT * полезен для спонтанных запросов, в рабочем коде это повсеместно считается дурным тоном, поскольку добавление столбца в таблицу приведет к изменению результатов таких запросов.

2

В некоторых базах данных реализация DISTINCT автоматически упорядочивает строки, поэтому в ORDER BY нет необходимости. Но это не требуется стандартом SQL, и QHB не гарантирует, что DISTINCT отсортирует выводимые строки.

3

Это просто концептуальная модель. Обычно соединение выполняется более эффективным способом, чем фактическое сравнение всех возможных пар строк, но это скрыто от пользователя.

Copyright © KVANTOM LLC, 2019-2024