Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Выберите тип работыЧасть дипломаДипломная работаКурсовая работаКонтрольная работаРешение задачРефератНаучно - исследовательская работаОтчет по практикеОтветы на билетыТест/экзамен onlineМонографияЭссеДокладКомпьютерный набор текстаКомпьютерный чертежРецензияПереводРепетиторБизнес-планКонспектыПроверка качестваЭкзамен на сайтеАспирантский рефератМагистерская работаНаучная статьяНаучный трудТехническая редакция текстаЧертеж от рукиДиаграммы, таблицыПрезентация к защитеТезисный планРечь к дипломуДоработка заказа клиентаОтзыв на дипломПубликация статьи в ВАКПубликация статьи в ScopusДипломная работа MBAПовышение оригинальностиКопирайтингДругое

Нажимая кнопку "Продолжить", я принимаю политику конфиденциальности

Н.В. Мотина

 

 

Язык SQL

 

Методические указания к лабораторным работам
по курсам «Базы данных» и «Управление данными»

 

Псков

Издательство ПсковГУ

 2013

 

УДК 004.655

ББК 32.973.26-018.1

              М85

Рекомендовано к изданию кафедрой
«Вычислительная техника»

Псковского государственного университета

 

Рецензенты:

– Аристов М.В. – кандидат физ.-мат. наук, доцент кафедры «Информационные системы и технологии» Псковского государственного университета;

– Хватцев А.А. – канд. физ.-мат. наук, доцент кафедры «Высшая математика» Псковского государственного университета.

 

Мотина, Н.В.

М85 Язык SQL: Методические указания к лабораторным работам по курсам «Базы данных» и «Управление данными». ¾ Псковский государственный университет. Псков : Изд.-во ПсковГУ, 2013. ¾ 104 с.

 

В методических указаниях приведены описания лабораторных работ, появященных ознакомлению с SQL – языком структурированных запросов к базе данных. Представлен синтаксис команд выборки данных, начиная с простейших и заканчивая достаточно сложными запросами с агрегированием данных и использованием подзапросов и представлений, а также команд изменения данных и команд определения данных. Изложение материала сопровождается примерами соответствующих SQL-запросов. Каждый раздел оформлен в виде отдельной лабораторной работы, где после рассмотрения теоретического материала следуют задания для самостоятельного выполнения. В приложении приведена система команд и особенности SQL-команд СУБД PostgreSQL 7.1.

 

УДК 004.655

ББК 32.973.26-018.1

 

 

© Мотина Н. В., 2013

© Псковский государственный университет, 2013

ОГЛАВЛЕНИЕ

1. ВВЕДЕНИЕ В SQL. 6

1.1. Что такое SQL. 6

1.2. Ввод команд SQL. 8

1.3. Типы данных. 8

1.3.1. Строки. 9

1.3.2. Числа. 10

1.3.3. Логические типы данных. 12

1.3.4. Дата и время. 13

1.3.5. Интервалы.. 14

1.3.6. Неопределенные значения. 15

1.3.7. Преобразование типов. 16

Лабораторная работа 1. 17

2. ПРОСТАЯ ВЫБОРКА ДАННЫХ.. 18

2.1. Структура оператора SELECT. 18

2.2. Форма оператора SELECT для простой выборки данных. 20

2.3. Сортировка записей. 22

2.4. Выбор интервалов записей. 23

2.5. Условные выражения с оператором CASE. 24

2.5.1. Оператор CASE со значениями. 24

2.5.2. Оператор CASE с условием поиска. 25

2.6. Функция COALESCE. 26

Лабораторная работа 2. 27

3. УТОЧНЕНИЕ ЗАПРОСА.. 28

3.1. Секция WHERE. 28

3.2. Предикаты.. 29

3.2.1. Between. 30

3.2.2. IN и NOT IN.. 30

3.2.3. LIKE и NOT LIKE. 30

3.2.4. SIMILAR.. 31

3.2.5. IS NULL. 31

3.2.6. OVERLAPS. 32

Лабораторная работа 3. 33

4. МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ.. 34

4.1. Выбор источников в секции FROM.. 34

4.2. Операции соединения. 35

4.2.1. Перекрестное соединение. 35

4.2.2. Естественное соединение. 37

4.2.3. Соединение по именам столбцов. 38

4.2.4. Условное соединение. 39

4.2.5. Сложные соединения. 40

Лабораторная работа 4. 41

5. ИТОГОВЫЕ ЗАПРОСЫ.. 43

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

5.2. Группировка записей. 44

5.3. Отбор групп записей. 45

Лабораторная работа 5. 47

6. СЛОЖНЫЕ ЗАПРОСЫ.. 48

6.1. Теоретико-множественные операции. 48

6.1.1. Декартово произведение наборов записей. 48

6.1.2. Объединение наборов записей. 50

6.1.3. Пересечение наборов записей. 51

6.1.4. Вычитание наборов записей. 51

6.2. Внешние соединения. 52

6.2.1. Левое внешнее соединение. 52

6.2.2. Правое внешнее соединение. 53

6.2.3. Полное внешнее соединение. 53

6.3. Подзапросы.. 54

6.3.1. Простые подзапросы.. 54

6.3.2. Связанные подзапросы.. 56

6.4. Представления. 59

Лабораторная работа 6. 61

7. КОМАНДЫ ИЗМЕНЕНИЯ ДАННЫХ.. 62

7.1. Добавление новых записей. 62

7.2. Удаление записей. 63

7.3. Изменение данных. 63

Лабораторная работа 7. 66

8. ОПРЕДЕЛЕНИЕ ДАННЫХ.. 67

8.1. Создание таблиц. 67

8.1.1. Оператор CREATE TABLE. 67

8.1.2. Ограничения для столбцов. 67

8.1.3. Ограничения для таблиц. 68

8.1.4. Внешние ключи. 69

8.2. Удаление таблиц. 70

8.3. Модификация таблиц. 70

8.3.1. Создание полей. 71

8.3.2. Назначение и отмена значений по умолчанию.. 71

8.3.3. Переименование таблицы.. 72

8.3.4. Переименование полей. 72

8.3.5. Добавление ограничений. 72

Лабораторная работа 8. 74

ПРИЛОЖЕНИЯ.. 75

П.1. Возможности PostgreSQL. 75

П.2. Консоль psql 75

П.3. Команды SQL. 76

П.3.1. Перечень команд SQL СУБД PostgreSQL. 76

П.3.2. Форматирование команд SQL. 77

П.3.3. Выполнение запросов. 78

П.3.4. Ключевые слова и идентификаторы.. 79

П.3.5. Комментарии. 80

П. 4. Типы данных PostgreSQL 7.1. 81

П.4.1. Перечень типов данных. 81

П.4.2. Преобразование типов. 84

П.4.3. Константы.. 85

П.5. Операторы.. 87

П.5.1. Правила использования операторов. 87

П.5.2. Строковые операторы.. 88

П.5.3. Числовые операторы.. 89

П.5.4. Двоичные операторы.. 90

П.5.5. Логические операторы.. 91

П.5.6. Операторы и NULL. 91

П.5.7. Приоритет операторов. 92

П.6. Функции. 93

П.6.1. Использование функций. 93

П.6.2. Математические функции. 93

П.6.3. Строковые функции. 94

П.6.4. Функции для работы с датой и временем. 96

П.6.5. Функции преобразования типа. 98

СПИСОК ЛИТЕРАТУРЫ.. 102

 

ВВЕДЕНИЕ В SQL

Что такое SQL

 

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

Запросы выполняются системой управления базой данных (СУБД). Во многих случаях операции с базой данных выполняются с помощью специальных приложений, предоставляющих пользователю удобный интерфейс. Однако доступ к базе данных можно получить и без них – с помощью только SQL. Да и специализированные приложения обычно используют SQL-фрагменты кода при обращении к данным.

Таким образом, SQL – широко распространенный стандарт языка работы с реляционными базами данных. Различных СУБД существует много, а универсальное средство работы с базами данных одно – SQL.

Первые разработки систем управления реляционными базами данных (реляционных СУБД) были выполнены в компании IBM в начале 1970-х годов. Тогда же был создан язык данных, предназначенный для работы в этих системах. Экспериментальная версия этого языка называлась SEQUEL – от англ. Structured English QUEry Language (структурированный английский язык запросов). Однако официальная версия была названа короче – SQL (Structured Query Language).

В 1981 году IBM выпускает реляционную СУБД SQL/DS. К этому времени компания Relation Software Inc. (сегодня это Oracle corporation) уже выпустила свою реляционную СУБД. Эти продукты сразу же стали стандартом систем, предназначенных для управления базами данных. В состав этих продуктов вошел и SQL, который фактически стал стандартом для языков данных. Производители других СУБД выпустили свои версии SQL. В них имелись не только основные возможности продуктов IBM. Чтобы получить некоторое преимущество для «своей» СУБД, производители вводили некоторые расширения SQL. Вместе с тем, начались работы по созданию общепризнанного стандарта SQL.

В 1986 году Американский национальный институт стандартов (American National Standarts Institute, ANSI) выпустил официальный стандарт SQL-86, который в 1989 году был обновлен и получил новое название SQL-89. В 1992 году этот стандарт был назван SQL-92. В настоящее время известен стандарт SQL-2003.

Любая реализация SQL в конкретной СУБД несколько отличается от стандарта. Так, многие СУБД (например, Microsoft Access 2003, PostgreSQL 7.3) поддерживают SQL-92 не в полной мере, а лишь с некоторым уровнем соответствия. Кроме того, они поддерживают и элементы, которые не входят в стандарт. Однако разработчики СУБД стремятся к тому, чтобы новые версии их продуктов как можно в большей степени соответствовали стандарту SQL.

SQL задумывался как простой язык запросов к реляционной базе данных, близкий к естественному (точнее, к английскому). Предполагалось, что близость по форме к естественному языку сделает SQL средством, доступным для широкого применения обычными пользователями баз данных, а не только программистами. Первоначально SQL не содержал никаких управляющих структур, свойственных обычным языкам программирования. Запросы, синтаксис которых довольно прост, вводились прямо с консоли последовательно один за другим и в этой же последовательности выполнялись. Однако SQL так и не стал инструментом банковских служащих, продавцов авиа- и железнодорожных билетов, экономистов и других служащих различных фирм, использующих информацию, хранимую в базах данных. Для них простой SQL оказался слишком сложным и неудобным, несмотря на свою близость к естественному языку вопросов.

На практике с базой данных обычно работают посредством приложений, написанных программистами на процедурных языках, например, на С, Visual Basic, Pascal, Java и др. Часто приложения создаются в специальных средах визуальной разработки, таких как Delphi, Microsoft Access, Visual dBase и т.п. Приложения имеют удобный графический интерфейс, не вынуждающий пользователя непосредственно вводить запросы на языке SQL. Вместо него это делает приложение.

Реляционные базы данных могут и действительно существуют вне зависимости от приложений, обеспечивающих пользовательский интерфейс. Если по каким-либо причинам такого интерфейса нет, то доступ к базе данных можно осуществить с помощью SQL, используя консоль или какое-нибудь приложение, с помощью которого можно соединиться с базой данных, ввести и отправить SQL-запрос (например, Borland SQL Explorer).

Язык SQL считают декларативным (описательным) языком, в отличие от языков, на которых пишутся программы. Это означает, что выражения на языке SQL описывают, что требуется сделать, а не каким образом. Нет необходимости подробно описывать действия, которые должна выполнить СУБД, чтобы выбрать из таблицы указанные в запросе данные. Надо просто описать, что хочется получить. В результате выполнения запроса СУБД возвращает таблицу, содержащую запрошенные данные. Если в базе данных не оказалось данных, соответствующих запросу, то будет возвращена пустая таблица.

Однако последние версии SQL поддерживают операторы управления вычислениями, свойственные процедурным языкам (операторы условного перехода и цикла). Поэтому SQL сейчас это не чисто декларативный язык.

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

· DML (Data Manipulation Language – язык манипулирования данными) предназначен для поддержки базы данных: выбора (SELECT), добавления (INSERT), изменения (UPDATE) и удаления (DELETE) данных из таблиц;

· DDL (Data Definition Language – язык определения данных) предназначен для создания, модификации и удаления таблиц и всей базы данных. Примерами операторов, входящих в DDL, являются CREATE TABLE (создать таблицу), CREATE VIEW (создать представление), ALTER TABLE (изменить таблицу), DROP (удалить) и др.;

· DCL (Data Control Language – язык управления данными) предназначен для обеспечения защиты базы данных от различного рода повреждений.

 

Ввод команд SQL

 

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

Все ключевые слова SQL (команды, операторы и проч.) являются зарезервированными и не должны использоваться в качестве имен таблиц, столбцов, переменных и т.п.

Подробнее о правилах записи команд SQL, принятых в СУБД PostgreSQL, см. в приложении (П.3. Команды SQL).

 

Типы данных

 

Различные СУБД поддерживают несколько отличающиеся наборы типов данных для столбцов базы данных. Аналогичная ситуация и с типами данных, поддерживаемых различными версиями и реализациями SQL. Типы данных, используемые в PostgreSQL, приведены в приложении (П.4. Типы данных PostgreSQL 7.1.). Вместе с тем, всегда имеются типы данных, которые поддерживаются всеми реализациями SQL. В спецификации SQL:2003 признаны пять предопределенных общих типов, внутри которых могут быть подтипы:

строковый (символьный):

§ character (или char);

§ character varying (или varchar);

§ character large object (или clob);

числовой:

§ точные числовые типы:

1 integer;

2 SMALLINT;

3 BIGINT;

4 NUMERIC;

5 DECIMAL;

§ приблизительные числовые типы:

1 REAL;

2 DOUBLE PRECISION;

3 FLOAT;

логический (булевский) – BOOLEAN;

даты-времени:

§ DATE;

§ TIME WITHOUT TIME ZONE;

§ TIME WITH TIME ZONE;

§ TIMESTAMP WITHOUT TIME ZONE;

§ TIMESTAMP WITH TIME ZONE;

интервальный.

 

Строки

 

Строковые данные (последовательности символов) имеют три главных строковых типа. Для столбца таблицы можно указать тип CHARACTER (n) или CHAR (n) (строка фиксированной длины), где n – максимальное количество символов, содержащихся в строке. Если (n) не указано, то предполагается, что строка состоит из одного символа. Если в столбец типа CHARACTER (n) вводится m < n символов, то оставшиеся позиции заполняются пробелами.

Тип данных CHARACTER VARYING (n) или VARCHAR (n) (строка переменной длины) применяется тогда, когда вводимые данные имеют различную длину и нежелательно дополнять их пробелами. При этом сохраняется только то количество символов, которое ввел пользователь. В данном случае указание максимального количества символов обязательно (в отличие от CHARACTER).

Данные типов CHARACTER и CHARACTER VARYING могут участвовать в одних и тех же строковых операциях.

Тип данных CHARACTER LARGE OBJECT (CLOB – большой символьный объект) используется для представления очень больших символьных строк (например, статей, книг и т.п.). В некоторых СУБД данный тип называется MEMO, а в других – TEXT. С данными этого типа можно выполнять не все операции, предусмотренные для типов CHARACTER и CHARACTER VARYING. Так, их нельзя использовать в операциях сравнения, за исключением равенства и неравенства. Кроме того, столбцы этого типа не могут быть первичными и внешними ключами, а также быть объявлены как имеющие уникальные значения.

Значения строкового типа в SQL-выражениях заключаются в одинарные кавычки. Например, ‘Иванов Иван Иванович’, ‘12345 рублей’, ‘тел. (812) 123-4567’. Пустая строка не содержит ни одного символа и имеет вид: ''. Строка, содержащая один или более пробелов, не является пустой.

 

Числа

 

Числовой тип данных может быть двух видов – точный и приблизительный. Точные числовые типы позволяют точно выразить значение числа. Некоторые величины имеют очень большой диапазон значений, и в таких случаях достаточно ограничиться некоторым приближенным их представлением с учетом технических возможностей компьютера (размеров регистра).

К точным числовым относятся следующие пять типов:

· INTEGER – целое (без дробной части) число. Количество разрядов (точность) зависит от реализации SQL. В некоторых реализациях числа этого типа лежат в диапазоне от –2147483648 до 2147483647 (четырехбайтное целое число);

· SMALLINT – малое целое число. Количество разрядов зависит от реализации SQL, но не больше количества разрядов INTEGER в этой же реализации. В некоторых реализациях числа этого типа лежат в диапазоне от –32768 до 32767 (двухбайтное целое число);

· BIGINT – большое целое число. Количество разрядов зависит от реализации SQL и превышает количество разрядов числа типа INTEGER;

· NUMERIC (x, y) – число, в котором всего x разрядов (точность), из которых y разрядов (масштаб) отводится для дробной части. Если y не указано (NUMERIC (x)), то для дробной части отводится количество разрядов, установленное в системе по умолчанию. Если не указаны ни x, ни y (NUMERIC), то принимаются обе эти величины, установленные по умолчанию. Например, если указан тип NUMERIC (6, 2), то максимальное значение числа равно 9999.99;

· DECIMAL (x, y) – десятичное число, в котором всего x разрядов, из которых y разрядов отводятся для дробной части. Если x или/и y не указаны, то принимаются значения по умолчанию. Этот тип очень похож на NUMERIC. Отличие состоит в том, что если в DECIMAL (x, y) указанные x и y меньше, чем допустимые реализацией SQL, то будут использоваться последние. Если x и y не указаны, по применяется система умолчаний. Например, для столбца задан тип DECIMAL (6, 2). Если реализация SQL позволяет, то в этот столбец можно ввести числа, превышающие 9999.99. В отличие от DECIMAL (x, y), тип NUMERIC (x, y) жестко задает диапазон возможных значений числовой величины.

К приблизительным числовым типам относятся следующие три типа:

· REAL – вещественное число одинарной точности с плавающей разделительной точкой (эта точка «плавает», появляясь в различных местах числа). Например, 5.25, 5.257, 5.2573. Точность представления числа зависит от реализации SQL и оборудования. Например, 32-битовый компьютер дает большую точность, чем 16-битовый;

· DOUBLE PRECISION – вещественное число двойной точности с плавающей разделительной точкой. Точность представления зависит от реализации SQL и оборудования. Применяется для представления научных данных (например, результатов измерений) в широком диапазоне значений, т.е. как очень малых (близких к 0), так и очень больших;

· FLOAT (x) – вещественное число с плавающей разделительной точкой и минимальной точностью x, занимающее не более 8 байтов. Если компьютер может поддерживать указанную точность, используя аппаратную одинарную точность, то система будет использовать арифметику одинарной точности. Если указанная точность требует арифметики с двойной точностью, то система будет использовать ее. Данный тип следует применять, если предполагается возможность переноса базы данных на другую аппаратную платформу, отличающуюся размерами регистров. Пример значения FLOAT: 5.318E-24 (т.е. 5.318, умноженное на 10 в степени -24). Такую же форму представления имеют и числа типа REAL и DOUBLE PRECISION.

При создании таблиц целочисленные типы применяются для столбцов, содержащих разного рода идентификаторы, например, номера (коды) клиентов, товаров, заказов и т.п. Разумеется, если содержимое столбца должно быть целым числом (например, количество ящиков, бутылок, штук и т.п.), то тип этого столбца естественно определить как INTEGER, SMALLINT или BIGINT.

Пусть в таблице Клиенты имеется столбец ID_Клиента, содержащий уникальные идентификаторы клиентов. Если количество клиентов не превышает 32000, то тип столбца можно определить как SMALLINT. Если в таблице будут храниться сведения о сотнях тысяч клиентов, то тип столбца ID_Клиента следует определить как INTEGER.

Если столбец в проектируемой таблице должен содержать числа с дробной частью, то для него можно задать какой-нибудь нецелочисленный тип. При отсутствии уверенности, что применить: точные числовые типы или приблизительные, следует выбирать точные (NUMERIC, DECIMAL). Они требуют меньше ресурсов и дают точные результаты. Если в столбце предполагается хранить данные из очень широкого диапазона (и очень малые, и очень большие числа), то следует использовать приблизительные типы данных (FLOAT, REAL).

 

Логические типы данных

 

В этой части математической логики, основоположником которой был английский математик Джон Буль, данные имеют только два значения – ИСТИНА и ЛОЖЬ, обозначаемые как true и false соответственно. Данные логического типа получаются в результате операций сравнения. Например, результатом вычисления выражения 3 < 5 является ИСТИНА, а выражение 2 + 3 = 10 – ЛОЖЬ.

В SQL тип данных BOOLEAN (булевский) имеет три значения – true, false и unknown. Значение unknown (неизвестное) было введено для обозначения результата, получающегося при сравнении со значением NULL (неопределенное). Если пользователь еще не ввел в ячейку таблицы никакого значения, то эта «пустая» ячейка содержит значение NULL, интерпретируемое как неизвестное или неопределенное значение.

Результатом любой операции сравнения true или false с NULL или с unknown всегда является unknown.

В SQL-выражениях логические значения заключаются в кавычки, например, ‘TRUE’ или ‘true’.

 

Дата и время

 

Тип DATE (дата) предназначен для хранения значений даты, элементы которых расположены в следующем порядке: год (4 цифры), дефис (-), месяц (2 цифры), дефис, день (2 цифры). Таким образом, значения даты занимают 10 позиций, например, 2005-10-02.

Данные этого типа могут содержать любую дату с 0001 года по 9999 год.

Для представления времени предусмотрены два типа:

· TIME WITHOUT TIME ZONE (время без часового пояса) предназначен для хранения значений времени, элементы которых расположены в следующем порядке: часы, двоеточие, минуты, двоеточие, секунды. Часы и минуты представляются двумя цифрами, а секунды могут быть представлены двумя и более цифрами (если требуется дробная часть), например 18:35:19.547. Длина дробной части секунд зависит от реализации, но внутреннее представление времени должно иметь не менее 6 цифр. По умолчанию время данного типа представляют без дробной части секунд. Чтобы указать, что время должно быть представлено с n цифрами после разделительной точки, достаточно использовать такой синтаксис: TIME WITHOUT TIME ZONE (n). Например, чтобы кроме секунд указывались еще и миллисекунды, следует определить тип как TIME WITHOUT TIME ZONE (3). Длина данных рассматриваемого типа без дробной части равна 8 символам, а с дробной частью – 9 плюс количество цифр после разделительной точки. Для задания времени без указания часового пояса с использованием установок по умолчанию можно использовать короткий синтаксис – TIME;

· TIME WITH TIMEZONE (время с часовым поясом) – такой же тип данных, как и TIME WITHOUT TIME ZONE. Отличие заключается лишь в том, что к значению времени добавляется еще и информация о разности между местным и всемирным временем. Всемирное время (Universal Time Coordinated, UTC) – это время по Гринвичу, т.е. время нулевого меридиана, проходящего через г. Гринвич в Великобритании (Greenwich Mean Time, GMT). Значение разности между локальным и всемирным временем находится в диапазоне от –12:59 до 13:00. Длина данных рассматриваемого типа равна длине данных типа TIME WITHOUT TIME ZONE плюс 6, поскольку дополнительная информация о разности времен занимает 6 позиций (дефис, знак (+) или (-), 2 цифры для часов, двоеточие, 2 цифры для минут).

Для одновременного представления даты и времени служат следующие два типа:

· TIMESTAMP WITHOUT TIME ZONE (дата и время без часового пояса). Элементы данных этого типа имеют такие же характеристики, как и для данных типа DATE и TIME WITHOUT TIME ZONE, за исключением одного: данные типа TIMESTAMP WITHOUT TIME ZONE по умолчанию имеют 6 цифр в дробной части секунд, а не 0, как в типе TIME WITHOUT TIME ZONE. Для указания количества цифр в дробной части используется синтаксис TIMESTAMP WITHOUT TIME ZONE (n). Если дробной части нет, то данные занимают 19 позиций: 10 позиций для даты, один пробел и 8 позиций для времени. Если определена дробная часть, то длина данных равна 20 плюс количество цифр в дробной части секунд;

· TIMESTAMP WITH TIME ZONE (дата и время с часовым поясом) – такой же тип данных, как и TIMESTAMP WITHOUT TIME ZONE. Отличие состоит в том, что к значению времени добавляется еще и информация о разности между местным и всемирным временем. Дополнительная информация занимает 6 позиций. Данные типа TIMESTAMP WITH TIME ZONE без дробной части занимают 25 позиций, с дробной частью – 26 плюс количество цифр в дробной части секунд.

 

Интервалы

 

Интервал представляет собой разность между двумя значениями типа дата-время.

Интервал времени можно задать двумя способами: в виде начального и конечного моментов или в виде начального момента и длительности, например:

· (TIME '12:25:30', TIME '14:30:00') – интервал, заданный начальным и конечным моментами;

· (TIME '12:45:00', INTERVAL '5' HOUR) – интервал, заданный начальным моментом и длительностью в часах.

Чтобы задать значение типа интервал, используется такой синтаксис:

INTERVAL 'длина' YEAR | MONTH | DAY | HOUR | MINUTE | SECOND

Здесь длина – длина интервала, после которой указывается единица измерения (возможные значения указаны через вертикальную черту):

· YEAR – год;

· MONTH – месяц;

· DAY – день;

· HOUR – час;

· MINUTE – минута;

· SECOND – секунда.

Например, для задания интервала длиной 15 дней следует использовать выражение INTERVAL '15' DAY.

 

Неопределенные значения

 

Если в ячейку (поле) таблицы вводятся какие-то значения (буквы, цифры, пробелы или еще что-нибудь), то эти ячейки приобретают так называемые определенные значения. С определенными значениями, принадлежащими тому или иному типу можно выполнять какие-то операции. Однако возможно создать запись (строку) таблицы, ничего не вводя в ее поля (столбцы). Такая запись ничего не содержит. Запись в таблице, которая создана, но в которую не введены конкретные значения, называется пустой. В каждой ячейке пустой записи содержится так называемое неопределенное значение, обозначаемое через NULL. Значение NULL понимается как неопределенное или неизвестное.

Каждый раз при добавлении в таблицу новой записи без ввода в нее каких-либо конкретных значений, СУБД устанавливает в ее полях значение NULL.

Значение NULL можно интерпретировать так, как хочется, например, "еще не введено", "пока не известно".

Важно понимать, что число 0 или пустая строка '' являются вполне определенными значениями и отличаются от NULL, хотя визуально пустая строка и неопределенное значение могут отображаться одинаково – в виде пустой ячейки таблицы. Многие функции SQL возвращают в зависимости от обстоятельств какое-то определенное значение либо NULL. Это необходимо учитывать при составлении SQL-выражений, поскольку комбинация NULL и других вполне конкретных значений может дать тот или иной результат в зависимости от конкретной операции.

Таблица базы данных может иметь некоторые записи, имеющие неопределенные значения. Такие значения появляются по различным причинам, однако при этом могут возникать неоднозначные ситуации при извлечении данных. Чтобы их избежать, необходимо помнить, что при сравнении любого определенного значения с NULL результат равен false, т.е. любое определенное значение не равно NULL. То же самое происходит при сравнении двух величин, значениями которых являются NULL, т.е. сравнение двух неопределенных величин в результате дает false.

При проектировании баз данных можно указать, что некоторые столбцы не могут иметь значение NULL, т.е их значения обязательно должны быть определены (при определении столбца в операторе CREATE TABLE указывается NOT NULL). Однако во многих случаях значения NULL являются очень полезными. Так, например, возможно создать таблицу и ввести в нее сведения, которые имеются в данный момент. Затем, по мере поступления новой информации, можно изменить значения NULL на те, которые стали известны.

 

1.3.7. Преобразование типов

 

Чтобы выполнить какую-либо операцию над данными различных типов, необходимо сделать преобразование типов. Точнее, необходимо выполнить приведение данных одного типа к другому типу, чтобы участвующие в операции данные были либо однотипными, либо их типы были соответствующими.

Соответствующими типами являются:

· строковые CHARACTER и CHARACTER VARYING;

· все числовые типы;

· дата, время, дата-время и соответствующие интервалы.

Соответствующие типы не обязательно приводить друг к другу.

Приведение значения одного типа к другому осуществляется с помощью функции CAST():

CAST (выражение AS тип);

Например:

CAST ('1234.52' AS NUMERIC (9, 2));

CAST ('2005-10-03' AS DATE);

CAST (CURRENT_TIMESTAMP (2) AS CHAR (20));

 

 

Лабораторная работа 1

 

1. Соединиться с базой данных booktown на сервере баз данных server.

2. Просмотреть список имеющихся на сервере баз данных.

3. Просмотреть список зарегистрированных пользователей слэш-командой \du и командами языка SQL:

· SELECT *

FROM pg_user;

· select usename, usesysid

from pg_user;

4. Просмотреть список групп пользователей

select *

from pg_group;

5. Просмотреть список таблиц базы данных booktown.

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

SELECT *

FROM имя_таблицы;

Рабочие таблицы:

· authors,

· books,

· customers,

· editions,

· publishers,

· shipments,

· stock,

· subjects.

 

 

ПРОСТАЯ ВЫБОРКА ДАННЫХ

 

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

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

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

 

Структура оператора SELECT

 

Основное SQL-выражение для выборки данных имеет вид:

SELECT списокСтолбцов FROM таблица;

Такой запрос возвращает таблицу, полученную из указанной в операторе FROM путем выделения в ней только тех столбцов, которые определены в операторе SELECT. Для выделения требуемых записей (строк) исходной таблицы используется выражение, следующее за ключевым словом (оператором) WHERE. Оператор WHERE является наиболее часто используемым, хотя и необязательным в SQL-выражениях. Кроме WHERE, в SQL-выражениях используются и другие операторы, позволяющие уточнить запрос.

Для уточнения запроса на выборку данных служит ряд дополнительных операторов:

· WHERE (где) – указывает записи, которые должны войти в результатную таблицу (фильтр записей);

· GROUP BY (группировать по) – группирует записи по значениям определенных столбцов;

· HAVING (имеющие, при условии) – указывает группы записей, которые должны войти в результатную таблицу (фильтр групп);

· ORDER BY (сортировать по) – сортирует (упорядочивает) записи.

Эти операторы не являются обязательными. Их можно совсем не использовать, или использовать лишь некоторые из них, или все сразу. Если применяются несколько операторов, то в SQL-выражении они используются в указанном в списке порядке.

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

SELECT списокСтолбцов

FROM имяТаблиы

WHERE условиеПоиска

GROUP BY столбецГруппировки

HAVING условиеПоиска

ORDER BY условиеСортировки;

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

1. FROM – выбирает таблицу из базы данных; если указано несколько таблиц, то выполняется их декартово произведение, и результирующая таблица передается для обработки следующему оператору.

2. WHERE  – из таблицы выбираются записи, отвечающие условию поиска, и отбрасываются все остальные.

3. GROUP BY – создаются группы записей, отобранных с помощью оператора WHERE (если он присутствует в SQL-выражении); каждая группа соответствует какому-нибудь значению столбца группирования. Столбец группирования может быть любым столбцом таблицы, заданной в операторе FROM, а не только тем, который указан в SELECT.

4. HAVING – обрабатывает каждую из созданных групп записей, оставляя только те из них, которые удовлетворяют условию поиска; этот оператор используется только вместе с оператором GROUP BY.

5. SELECT – выбирает из таблицы, полученной в результате применения перечисленных операторов, только указанные столбцы.

6. ORDER BY – сортирует записи таблицы.

 

Сортировка записей

 

Записи хранятся в таблицах в произвольном порядке. Более того, даже повторное выполнение запроса никоим образом не гарантирует одинакового порядка следования возвращаемых записей. Однако упорядочение данных играет важную роль при выборке, поэтому в SQL поддерживается конструкция ORDER BY, являющаяся гибким средством сортировки итогового набора.

Секции ORDER BY передается список полей, разделенный запятыми (или выражений, в которых используются поля). Переданный список задает критерий сортировки. Для каждого критерия сортировки могут дополнительно указываться ключевые слова ASC и DESC, управляющие типом сортировки.

· ASC. Записи сортируются по возрастанию заданного критерия (то есть числа сортируются от меньших к большим, даты – от ранних к поздним, а текст – по алфавиту). По умолчанию выбирается именно этот способ сортировки, поэтому ASC используется лишь для наглядности.

· DESC. Записи сортируются по убыванию заданного критерия (то есть числа сортируются от больших к меньшим, даты – от поздних к ранним, а текст – в порядке, обратном алфавитному).

Например, получим фамилии и имена всех авторов с сортировкой по фамилии:

SELECT last_name, first_name

FROM authors

ORDER BY last_name;

При сортировке допускается использование полей, отсутствующих в целевом списке оператора SELECT. Более того, если запрос связан с агрегированием, секция ORDER BY может содержать вызовы агрегатных функций и выражения.

Например, получим фамилии и имена всех авторов с сортировкой по идентификационному номеру:

SELECT last_name, first_name

FROM authors

ORDER BY id;

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

Например, получим фамилии и имена всех авторов с сортировкой по фамилии, а если фамилии совпадают, то и по именам:

SELECT last_name, first_name

FROM authors

ORDER BY last_name, first_name;

При сортировке по нескольким критериям для каждого критерия можно задать свой тип упорядочивания.

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

SELECT edition, publication

FROM editions

ORDER BY edition ASC, publication DESC;

Секция ORDER BY обрабатывается перед удалением дубликатов ключевым словом DISTINCT. Этот факт позволяет с помощью сортировки составить запрос, аналогичный по результату использованию агрегатных функций max() или min().

Например, получим даты последних изданий для каждого номера издания:

SELECT DISTINCT ON (edition) edition, publication

FROM editions

ORDER BY edition ASC, publication DESC;

 

Выбор интервалов записей

 

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

В SQL предусмотрены ключевые слова LIMIT и OFFSET, упрощающие выборку заданной части итогового набора.

Секция LIMIT ограничивает максимальное количество записей в итоговом наборе (хотя размер итогового набора может быть меньше заданной величины).

Например, получим последние пять номеров isbn книг, которые были изданы:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

LIMIT 5;

При наличии секции OFFSET в итоговом наборе пропускается количество записей, заданное параметром секции.

Например, получим isbn и даты изданий всех книг, кроме последних пяти изданных:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

OFFSET 5;

Если заданы оба ключевых слова, и LIMIT, и OFFSET, то отсчет ограничения, указанного в секции LIMIT, начинается после пропуска записей в соответствии с секцией OFFSET.

Например, получим предпоследние (т.е. не учитывая самую последнюю книгу) пять номеров isbn книг, которые были изданы:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

LIMIT 5

OFFSET 1;

 

Оператор CASE со значениями

 

Оператор CASE со значениями имеет следующий синтаксис:

CASE проверяемое_значение

WHEN значение1 THEN результат1

WHEN значение2 THEN результат2

...

WHEN значениеN THEN результатN

ELSE результатX

END

В случае, когда проверяемое_значение равно значение1, оператор CASE возвращает значение результат1, указанное после ключевого слова THEN. В противном случае проверяемое_значение сравнивается с значение2, и если они равны, то возвращается значение результат2. В противном случае проверяемое_значение сравнивается со следующим значением, указанным после ключевого слова WHEN и т.д. Если проверяемое_значение не равно ни одному из таких значений, то возвращается значение результатХ, указанное после ключевого слова ELSE.

Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из значений, подлежащих сравнению, не равно проверяемому значению, то оператор CASE возвращает NULL.

Например, вместо номера издания в виде числа выведем номер издания в виде текстовой строки:

SELECT isbn,

CASE edition

    WHEN 1 THEN 'first'

    WHEN 2 THEN 'second'

    WHEN 3 THEN 'third'

    ELSE 'large then 3'

END AS edition_num

FROM editions;

 

Функция COALESCE

 

В ряде случаев, особенно в запросах на обновление данных (оператор UPDATE), удобно использовать вместо громоздкого оператора CASE более компактную функцию COALESCE.

Функция COALESCE(значение1, значение2, ..., значениеN) принимает список значений, которые могут быть как определенными, так и неопределенными (NULL). Функция возвращает первое определенное значение из списка. Если все значения окажутся неопределенными, функция возвращает NULL.

Например, заменим все NULL-значения поля location на значение 'не определено':

SELECT id, subject,

coalesce(location, 'не определено')

FROM subjects;

 

 

Лабораторная работа 2

 

Подключиться к базе данных booktown и выполнить следующие запросы:

1. Все сведения о книгах.

2. Все сведения об изданиях книг.

3. Код и название всех книг.

4. ISBN, номер издания, дата издания и тип обложки для всех изданий.

5. Неповторяющиеся коды издательств, выпускавших книги.

6. Неповторяющиеся коды тем, по которым написаны книги.

7. Для каждого кода ISBN получить прибыль магазина в случае продажи всех имеющихся в наличии экземпляров.

8. Названия всех книг, упорядоченные по алфавиту.

9. Фамилии и имена всех покупателей, отсортированные по алфавиту (если есть однофамильцы, то они должны быть отсортированы по именам).

10. Список всех ISBN с указанием розничной цены, упорядоченный от самых дорогих к самым дешевым.

11. Код ISBN и затраты магазина на закупку соответствующих имеющихся в наличии книг. Упорядочить по стоимости закупки (всех имеющихся экземпляров).

12. Код ISBN, номер издания и дата издания для всех книг в таком порядке, чтобы сначала шли книги, издававшиеся наибольшее количество раз. Книги с одним и тем же количеством изданий упорядочить по дате издания.

13. Код ISBN и имеющееся количество для десяти книг, в наибольшем количестве имеющихся на складе.

14. Все данные о последних пяти покупках.

15. Все данные о первых семи покупках, не считая трех самых первых (т.е. следующие семь после первых трех).

16. Самая дорогая цена (розничная) книги.

17. Самая малая разница между ценой закупки и ценой продажи.

18. Код ISBN и название типа обложки («твердая» / «мягкая») для всех изданий. Упорядочить по названию типа обложки.

19. Код ISBN, цена закупки, цена продажи, величина наценки, процент наценки и класс всех книг. Если цена продажи более, чем на 10 % превышает цену закупки, то класс – «VIP», иначе – «эконом». Упорядочить по убыванию процента наценки.

 

УТОЧНЕНИЕ ЗАПРОСА

 

Секция WHERE

 

Основное выражение для выборки данных имеет вид:

SELECT списокСтолбцов

FROM списокТаблиц;

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

Для выделения требуемых записей (строк) исходной таблицы используется выражение, следующее за ключевым словом WHERE. Условия поиска в операторе WHERE являются логическими выражениями, т.е. принимающими одно из двух возможных значений – true (ИСТИНА) или false (ЛОЖЬ).

Например, получим список всех книг о компьютерных технологиях. У этих книг поле subject_id равно 4. Соответственно в секцию WHERE включается оператор =, который проверяет это условие:

SELECT *

FROM books

WHERE subject_id = 4;

Выражение subject_id = 4 является истинным (имеет значение true), если в текущей записи таблицы значение столбца subject_id равно 4. В противном случае это выражение ложно (имеет значение false). Одно и то же логическое выражение может быть истинным для одних записей и ложным для других. В SQL логические выражения могут принимать еще и неопределенное значение. Это происходить тогда, когда в выражении некоторые элементы имеют значение NULL. Таким образом, SQL имеет дело не с классической двухзначной, а с трехзначной логикой.

Секция WHERE может содержать несколько условий, объединенных логическими операторами (например, AND или OR) и возвращающими одно логическое значение.

Например, получим все записи для книг о компьютерах, которые, кроме того, что они о компьютерах, написаны Марком Лутцем. Запрос уточняется объединением двух условий при помощи логического оператора AND:

SELECT title

FROM books

WHERE subject_id = 4 AND author_id = 7805;

Первое условие проверяет, что книга посвящена компьютерным технологиям (поле subject_id равно 4), а второе – что автором книги является Марк Лутц (поле author_id равно 7805). Объединение условий уменьшает объем итогового набора.

Получим все книги, посвященные компьютерным технологиям или искусству; в этом случае два условия объединяются логическим оператором OR.

SELECT title

FROM books

WHERE subject_id = 4 OR subject_id = 0;

Прежнее первое условие (книги по компьютерной тематике) объединяется со вторым условием: книги по искусству (поле subject_id равно 0). В результате объем итогового набора увеличивается, каждая его запись удовлетворяет хотя бы одному из этих условий.

Количество условий, объединяемых в секции WHERE, не ограничено, хотя при наличии двух и более условий обычно задается порядок выполнения сравнений при помощи круглых скобок.

 

Предикаты

 

При составлении логических выражений используются специальные ключевые слова и символы операций сравнения, которые называют предикатами. Наиболее часто используются предикаты сравнения, такие как (=), (<), (>), (<>), (<=) и (>=). Однако имеются и другие. Далее приведен список всех предикатов:

· предикаты сравнения (=), (<), (>), (<>), (<=) и (>=);

· between;

· in, not in;

· like, not like;

· is null;

· all, some, any;

· exists;

· unique;

· distinct;

· overlaps;

· match;

· similar.

 

Between

 

Предикат between позволяет задать выражение проверки вхождения какого-либо значения в диапазон, определяемый граничными значениями. Например,

WHERE cost between 100 and 150

Здесь ключевое слово AND представляет собой логический союз И. Граничные значения (в примере это 100 и 150) входят в диапазон. Причем первое граничное значение должно быть не больше второго.

Эквивалентным приведенному является выражение с предикатами сравнения:

WHERE cost >= 100 and cost <= 150

Кроме данных числового типа, в выражениях с between можно использовать данные следующих типов: символьные, битовые, даты-времени. Например, чтобы выбрать записи, в которых фамилии авторов находятся в диапазоне от A до G, можно использовать такое выражение:

SELECT first_name, last_name

FROM authors

WHERE last_name between 'A' and 'G';

 

IN и NOT IN

 

Предикаты IN и NOT IN применяются для проверки вхождения какого-либо значения в заданный список значений. Например, для выборки записей о книгах некоторой тематики можно использовать такое выражение:

SELECT title, author_id

FROM books

WHERE subject_id IN (7805, 0);

Если требуется получить данные о всех книгах не о компьютерах и искусстве, то можно использовать предикат NOT IN:

SELECT title, author_id

FROM books

WHERE subject_id NOT IN (7805, 0);

 

LIKE и NOT LIKE

 

Предикаты LIKE и NOT LIKE применяются для проверки частичного соответствия символьных строк. Например, столбец Телефон в некоторой таблице содержит полные номера телефонов, и требуется выбрать лишь те записи, в которых номера телефонов начинаются с 8112 или содержат такое сочетание цифр.

Критерий частичного соответствия задается с помощью двух символов-масок: знака процента (%) и подчеркивания (_). Знак процента означает любой набор символов, в том числе и пустой, а символ подчеркивания – любой одиночный символ.

Например, чтобы выбрать записи об изданиях, у которых isbn начинается с 044, можно использовать такое выражение:

SELECT isbn, edition, publication

FROM edition

WHERE isbn LIKE '044%';

Если требуется выбрать записи о книгах про Python, то для этого подойдет следующее выражение:

SELECT title, author_id

FROM books

WHERE title LIKE '%Python%';

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

SELECT title, author_id

FROM books

WHERE title NOT LIKE '%Python%';

 

SIMILAR

 

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

Пусть в некоторой таблице имеется столбец OC, содержащий названия операционных систем. Нужно выбрать записи, соответствующие Windows XP, Windows Vista и Windows 7. Тогда в выражении запроса можно использовать такой оператор WHERE:

WHERE ОС SIMILAR TO 'Windows (XP|Vista|7)';

 

IS NULL

 

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

SELECT id, subject

FROM subjects

WHERE location IS NULL;

Для получения записей, в которых столбец location содержит некоторые определенные значения (т.е. отличные от NULL) можно использовать аналогичное выражение, но с логическим оператором NOT:

SELECT id, subject

FROM subjects

WHERE location IS NOT NULL;

Не следует использовать предикаты сравнения с NULL, такие как location = NULL.

 

OVERLAPS

 

Предикат OVERLAPS используется для определения, перекрываются ли два интервала времени. Выражение с предикатом OVERLAPS можно записать, например, так:

(TIME '12:25:30', TIME '14:30:00') OVERLAPS
(TIME '12:45:00', INTERVAL '2' HOUR)

Поскольку временные интервалы в данном примере пересекаются, то предикат OVERLAPS возвращает значение true.

Предикаты для вложенных запросов ALL, SOME, ANY, EXISTS, UNIQUE, DISTINCT будут рассмотрены в разделе "Сложные запросы".

Предикат MATCH применяется для проверки сохранения ссылочной целостности при модификации данных, т.е. при добавлении, изменении и удалении записей.

 

 

Лабораторная работа 3

 

Выполнить следующие запросы к базе данных booktown:

1. Вся информация о переиздании (т.е. не первое издание, а второе и выше) книг, так чтобы сначала списка шли книги, выдержавшие наибольшее количество переизданий.

2. Вся информация об издании книг с типом h в порядке их публикации.

3. Информация об изданиях книг до XXI века.

4. Информация об имеющихся книгах на складе (stock). Отсортировать по убыванию количества, отсутствующие книги не выводить.

5. Код ISBN и отпускная цена для книг с отпускной ценой в диапазоне от 20 до 30 в порядке возрастания цены.

6. Список покупателей, которых зовут Jean.

7. Код ISBN, имеющееся количество и отпускная цена для книг, имеющихся на складе в количестве, не меньшем 50, или с отпускной ценой, меньшей 20.

8. Код ISBN, тип обложки и дата издания для книг в мягкой (бумажной) обложке, изданных в период с 1990 по 2010 гг. Упорядочить по дате издания.

9. Код ISBN, номер издания и дата издания для книг, изданных впервые или в XXI веке.

10. Названия книг, написанных автором с кодом 1809 по теме с кодом 15.

11. Код ISBN и дата покупки для покупок в период с 10-го по 15 августа 2001 года. Отсортировать по дате покупки.

12. Названия книг и коды авторов для книг, написанных авторами с кодами 1809, 7805, 1212, 15990 и 25041. Упорядочить по коду автора.

13. Названия книг и коды авторов для книг, написанных авторами, чьи номера не 1809, 7805, 1212, 15990 и 25041. Упорядочить по коду автора.

14. Название темы и отдел для тем, расположенных в отделах Main St, Black Raven Dr, Productivity Ave и Creativity St. Отсортировать по названию темы.

15. Фамилия и имя авторов, имена которых начинаются на Marg.

16. Названия книг, в которых присутствует предлог in.

17. Названия книг, в которых присутствует The или the.

18. Названия книг, в которых нет ни The, ни the.

19. Фамилии авторов, чьи имена не известны.

20. Код темы, название темы и отдел для тем с кодом, большим 10, и известным отделом.

МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ

 

Операции соединения

 

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

Довольно часто операции, основанные на операторе JOIN, называют объединением таблиц (наборов записей). Однако термин "объединение" лучше подходит для UNION – оператора теоретико-множественного объединения записей, при котором записи исходных наборов не комбинируются (не соединяются) друг с другом, а просто к одному набору записей добавляется другой набор. В случае оператора JOIN в результатную таблицу попадают записи, полученные из разных наборов путем присоединения одной из них к другой. Поэтому операции, основанные на операторе JOIN, будем называть операциями соединения таблиц (наборов записей).

 

Перекрестное соединение

 

Существуют несколько разновидностей соединения, которым соответствуют определенные ключевые слова, добавляемые к слову JOIN. Так, например, декартово произведение является операцией перекрестного соединения. В SQL-выражении для обозначения этой операции используется оператор CROSS JOIN. Результат перекрестного соединения принципиально не отличается от перечисления источников через запятую.

Пусть в базе данных имеются следующие две таблицы:

· Сотрудники (Номер_сотрудника, Фамилия, Имя, Номер_отдела);

· Отделы (Номер_отдела, Название).

Общим столбцом для этих таблиц является Номер_отдела. Декартово произведение этих таблиц получается с помощью следующих эквивалентных запросов:

SELECT *

FROM Сотрудники CROSS JOIN Отделы;

или

SELECT *

FROM Сотрудники, Отделы;

На следующих рисунках показаны примеры таблиц Сотрудники и Отделы, а также результат их декартового произведения.

 

Сотрудники
Номер_сотрудника	Фамилия	Имя	Номер_отдела
1	Иванов	Иван	1
2	Петров	Петр	2
3	Сидоров	Сидор	1

 

Отделы
Номер_отдела	Название
1	Отдел автоматизации
2	Отдел кадров

 

Декартово произведение
Номер_ сотрудника	Фами-лия	Имя	Сотрудники. Номер_ отдела	Отделы. Номер_ отдела	Название
1	Иванов	Иван	1	1	Отдел автоматизации
1	Иванов	Иван	1	2	Отдел кадров
2	Петров	Петр	2	1	Отдел автоматизации
2	Петров	Петр	2	2	Отдел кадров
3	Сидоров	Сидор	1	1	Отдел автоматизации
3	Сидоров	Сидор	1	2	Отдел кадров

 

Синтаксис CROSS JOIN всего лишь более формально выражает отношение между двумя наборами данных. Между синтаксисом CROSS JOIN и простым перечислением таблиц через запятую нет никаких функциональных различий.

В основе любого соединения наборов записей лежит операция их декартового произведения.

 

Естественное соединение

 

В полученном декартовом произведении интерес представляют не все записи, а только те, в которых идентичные столбцы имеют одинаковые значения (Сотрудники.Номер_отдела = Отделы.Номер_отдела). Следовательно, в команде выборки с перекрестным соединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. Кроме того, в результатной таблице не нужны оба идентичных столбца, достаточно лишь одного из них. Такая таблица и будет естественным соединением таблиц Сотрудники и Отделы. Она получается с помощью следующего запроса:

SELECT Сотрудники.*, Отделы.Название

FROM Сотрудники, Отделы

WHERE Сотрудники.Номер_отдела = Отделы.Номер_отдела;

Данный запрос можно переписать, используя псевдонимы:

SELECT T1.*, T2.Название

FROM Сотрудники T1, Отделы T2

WHERE T1.Номер_отдела = T2.Номер_отдела;

Эквивалентный запрос с оператором NATURAL JOIN:

SELECT T1.*, T2.Название

FROM Сотрудники T1 NATURAL JOIN Отделы T2;

 

Естественное соединение
Номер_ сотрудника	Фамилия	Имя	Номер_отдела	Название
1	Иванов	Иван	1	Отдел автоматизации
2	Петров	Петр	2	Отдел кадров
3	Сидоров	Сидор	1	Отдел автоматизации

 

При естественном соединении, выполняемом с помощью оператора NATURAL JOIN, проверяется равенство всех одноименных столбцов соединяемых таблиц.

 

Условное соединение

 

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

Пусть в базе данных имеются следующие две таблицы:

· Сотрудники (Номер, Фамилия, Имя, Номер_отдела);

· Отделы (Номер, Название).

Тогда эти таблицы можно соединить:

SELECT *

FROM Сотрудники JOIN Клиенты

ON (Номер_отдела = Отделы.Номер);

Допустимо также использовать для условного соединения инструкцию INNER JOIN ON.

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

Например:

SELECT *

FROM Сотрудники, Клиенты 

WHERE (Номер_отдела = Отделы.Номер)

AND (Фамилия = 'Иванов');

и

SELECT *

FROM Сотрудники JOIN Клиенты

ON (Номер_отдела = Отделы.Номер)

WHERE Фамилия = 'Иванов';

Две синтаксические формы функционально идентичны и возвращают одинаковые результаты. Синтаксис JOIN позволяет отделить критерий связи источников от критерия выбора записей, поскольку связи определяются только в секции ON. Это существенно упрощает чтение и модификацию запросов, так как не приходится разбираться в смысле каждого условия в секции WHERE.

 

Сложные соединения

 

Хотя одна секция JOIN соединяет всего два набора данных, на практике соединения не ограничиваются двумя источниками. За набором, созданным посредством соединения, может следовать новая секция JOIN – по аналогии с тем, как перечисляются через запятую источники данных.

Пример соединения нескольких источников данных:

SELECT last_name, publisher, e.isbn, subject

FROM authors AS a

JOIN books AS b ON a.id = b.author_id

JOIN editions AS e ON b.id = e.book_id

JOIN publishers AS p ON e.publisher_id = p.id

JOIN subjects AS s ON b.subject_id = s.id;

Хотя таблица books участвует в соединении, ни одно из ее полей не входит в итоговый набор. Включение таблицы books в секции JOIN предоставляет критерии для соединения других таблиц. Все таблицы, поля которых возвращаются в результате запроса, связываются с другими таблицами через поле id таблицы books (кроме таблицы publishers, которая связывается с таблицей editions по полю publisher_id).

 

 

Лабораторная работа 4

 

Выполнить следующие запросы к базе данных booktown:

1. Код isbn, дата издания, цена продажи и количество экземпляров для всех изданий на складе.

2. Дата покупки, код покупателя, дата издания, тип обложки для всех покупок. Отсортировать по дате издания.

3. Дата покупки, фамилия и имя покупателя для всех покупок. Отсортировать по фамилии и имени покупателя.

4. Список авторов с названиями написанных ими книг, отсортированный по авторам.

5. Список названий книг и фамилий их авторов с указанием темы, отсортировать по названию книги.

6. Фамилия и имя покупателя, название купленной им книги для всех покупок. Отсортировать по покупателям.

7. Все пары: название издательства, фамилия автора (произведение которого было издано данным издательством). Упорядочить по названию издательства.

8. Фамилия и имя покупателя, название издательства купленной им книги для всех покупок. Упорядочить по покупателю.

9. Список номеров ISBN и тип обложки для книг в бумажном переплете с указанием издательств, выпустивших книгу; список отсортировать по издательствам.

10. Фамилия автора и название тем, которые он отразил в своих произведениях. Упорядочить по автору.

11. Код ISBN, название книги, фамилия автора, затраты магазина на закупку всех имеющихся в наличии экземпляров. Отсортировать так, чтобы в начале списка шли самые малозатратные издания.

12. Название и число экземпляров книг, количество которых на складе равно нулю. Упорядочить по названию.

13. Название и дата продажи книги, которая была куплена самой первой в магазине.

14. Фамилия и имя автора книги, купленной предпоследней. Указать также дату продажи.

15. Название книги, дата издания, первое или повторное издание для всех изданий (если издание 1, то «первое»; иначе – «повторное»). Отсортировать по названию книги.

16. Название книги, издательство и тип обложки для книг, изданных издательством Roc в твердом переплете. Упорядочить по названию книги.

17. Название и тема книг, относящихся к темам Science и Science Fiction. Отсортировать по названию книги.

18. Названия книг, которые приобрела Annie Jackson (с указанием покупателя).

19. Название и издатель книг, изданных не издательствами Ace Books, Roc, Penguin, Doubleday, Random House.

20. Название книги и адрес издательства для книг, изданных не в городе New York.

 

 

ИТОГОВЫЕ ЗАПРОСЫ

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

 

Довольно часто требуется узнать, сколько записей соответствует тому или иному запросу, какова сумма значений некоторого числового столбца, его максимальное, минимальное и среднее значение. Для этого служат так называемые итоговые (статистические, агрегатные) функции. Агрегатные функции – особый класс функций, применяемых сразу к нескольким записям набора данных, но возвращающим одно значение. Обычно агрегатные функции используются в запросах с группировкой, но также встречается их применение и в запросах без группирования. В этом случае агрегатная функция обрабатывает все записи итогового набора.

Далее перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da.

 

avg(выражение)          Среднее арифметическое значений выражения для всех записей в группе

count(выражение)     Количество записей в группе, для которых значение выражения отлично от NULL

max(выражение)          Максимальное значение выражения в группе

min(выражение)          Минимальное значение выражения в группе

stddev(выражение)   Среднеквадратичное отклонение значений выражения в группе

sum(выражение)          Сумма значений выражения в группе

variance(выражение) Дисперсия значений выражения в группе

 

Термин выражение означает любой столбец в итоговом наборе или любое выражение, выполняющее операцию с этим столбцом.

При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако возможно задать заголовки для значений итоговых функций и других столбцов по своему усмотрению. Для этого достаточно после имени столбца в операторе SELECT указать выражение вида AS заголовок_столбца.

Count(параметр) – возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей итогового набора, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT.

Например:

SELECT count(location) AS set_locs,

count(ALL location) AS all_locs,

count(DISTINCT location) AS unique_locs,

count(*) AS all_rows

FROM subjects;

Результат запроса:

set_locs all_locs unique_locs all_rows

15   15     7          16

 

Примеры использования других агрегатных функций:

SELECT AVG(retail) AS средняя_цена

FROM stock;

 

SELECT MIN(reatail * 28.8) AS                           минимальная_цена_в_долларах

FROM stock;

 

SELECT SUM(retail) AS общая_стоимость_редких_книг

FROM stock

WHERE stock < 10;

 

Группировка записей

 

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

Необходимо понимать, что все целевые поля, указанные в секции SELECT, участвующие в запросе с группировкой, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Другими словами, при включении предложения GROUP BY в инструкцию SELECT список выбора может состоять только из выражений, указанных в предложении GROUP BY или из агрегатных функций.

Выведем количество книг, хранящихся в базе данных booktown для каждого издательства:

SELECT name AS publisher,

count(isbn) AS number_of_books

FROM editions AS e INNER JOIN publishers AS p

ON (e.publisher_id = p.id)

GROUP BY name;

Секция GROUP BY указывает на то, что записи объединенного набора данных должны группироваться по имени издательства. Все записи с одинаковым названием издательства группируются, после чего функция count() подсчитывает в каждой группе количество непустых значений поля isbn и возвращает результат – количество записей, объединенных в каждую группу для одного издательства.

Получим количество книг, написанных авторами по каждой теме:

SELECT last_name, first_name, subject,

count(title) AS number_of_books

FROM books AS b INNER JOIN authors AS a

    ON (b.author_id = a.id)

INNER JOIN subjects AS s

    ON(b.subject_id = s.id)

GROUP BY last_name, first_name, subject;

 

Отбор групп записей

 

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

Разница между предложениями HAVING и WHERE заключается в том, что условие отбора, заданное в предложении WHERE, применяется к отдельным записям перед объединением их в группы, а условие отбора предложения HAVING применяется к группам строк. Секция WHERE не может содержать агрегатных функций. Условия же секции HAVING, наоборот, основаны на агрегатных функциях, а не на условиях для отдельных записей.

Выведем количество книг, хранящихся в базе данных booktown, для тех издательств, которые представлены двумя и более книгами:

SELECT name AS publisher,

count(isbn) AS number_of_books

FROM editions AS e INNER JOIN publishers AS p

ON (e.publisher_id = p.id)

GROUP BY name

HAVING count(isbn)>1;

 

 

Лабораторная работа 5

 

Выполнить следующие запросы к базе данных booktown:

1. Общее количество книг на складе (stock).

2. Количество книг с розничной ценой выше 30.

3. Выручка магазина при реализации всех книг.

4. Средняя цена (cost) книги.

5. Средняя цена (cost) для имеющихся книг на складе (stock).

6. Даты наиболее ранней и наиболее поздней публикаций.

7. Количество совершенных покупок.

8. Количество покупателей, совершавших покупки.

9. Количество покупателей с различными фамилиями, совершавших покупки.

10. Количество авторов, написавших какие-либо книги.

11. Количество изданий, выпущенных в твердой и мягкой обложке.

12. Количество первых изданий, вторых и так далее. В таком же порядке.

13. Количество книг, которые приобрел каждый покупатель. Отсортировать по покупателю.

14. Список покупателей-однофамильцев с подсчетом их количества.

15. Количество кодов isbn, которые были изданы каждым издательством. Отсортировать по названию издательства.

16. Количество книг различных названий, которые выпустило каждое издательство. Отсортировать по названию издательства.

17. Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).

18. Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Издательства, выпустившие менее двух изданий (кодов isbn), не выводить. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).

19. Первая дата публикации для каждого автора. Упорядочить по датам.

20. Фамилии авторов, чьи произведения не имеют изданий выше второго.

21. Фамилии авторов, все издания которых имеются в наличии в количестве не менее 50 экземпляров.

22. Фамилии авторов, все книги которых выходили только в бумажной обложке. Отсортировать по фамилии автора.

23. Наибольшее количество книг, написанных одним автором.

24. Наименьшее количество изданий, выпущенных одним издательством.

 

 

СЛОЖНЫЕ ЗАПРОСЫ

Объединение наборов записей

 

Нередко требуется объединить наборы записей двух или более таблиц с похожими структурами в одну таблицу. Иначе говоря, к набору записей, возвращаемых одним запросом, требуется добавить записи, возвращаемые другим запросом. Для этого служит оператор UNION:

                Запрос1

                UNION

                Запрос2;

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

Например, таблицы Коробки и Крышки имеют однотипные столбцы Размер, Количество и Цвет. Тогда, чтобы получить общий список данных и о коробках, и о крышках, достаточно выполнить следующий запрос:

SELECT Размер, Количество, Цвет

FROM Коробки

UNION

SELECT Размер, Количество, Цвет

FROM Крышки;

Оператор UNION можно применять только к таблицам, удовлетворяющим следующим условиям совместимости:

· количества столбцов объединяемых таблиц должны быть равны;

· данные в соответствующих столбцах объединяемых таблиц должны иметь совместимые типы данных. Например, символьные (строковые) типы CHAR и VARCHAR совместимы, а числовой и строковый типы не совместимы.

Имена соответствующих столбцов и их размеры могут быть различными. Важно, чтобы количества столбцов были равны, а их типы были совместимы.

Пусть требуется получить сведения о том, в какие пункты можно попасть, сделав не более одной пересадки (т.е. без пересадок или с одной пересадкой). Для этого достаточно объединить записи исходной таблицы Рейсы с результатом запроса о достижимости через один промежуточный пункт:

SELECT Начальный_пункт, Конечный_пункт

FROM Рейсы

UNION

SELECT T1.Начальный_пункт, T2.Конечный_пункт

FROM Рейсы T1, Рейсы T2

WHERE T1.Конечный_пункт = T2.Начальный_пункт;

Пересечение наборов записей

 

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

                Запрос1

                INTERSECT

                Запрос2;

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

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

SELECT Размер, Количество, Цвет

FROM Коробки

INTERSECT

SELECT Размер, Количество, Цвет

FROM Крышки;

 

Вычитание наборов записей

 

Для получения записей, содержащихся в одном наборе и отсутствующих в другом, служит оператор EXCEPT:

                Запрос1

                EXCEPT

                Запрос2;

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

SELECT Размер, Количество, Цвет

FROM Коробки

EXCEPT

SELECT Размер, Количество, Цвет

FROM Крышки;

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

 

Внешние соединения

 

Все соединения таблиц, рассмотренные ранее, являются внутренними. Во всех примерах вместо ключевого слова JOIN можно писать INNER JOIN. Из таблицы, получаемой при внутреннем соединении, удаляются все записи, у которых нет соответствующих строк одновременно в обеих исходных таблицах. При внешнем соединении (OUTER JOIN) несоответствующие строки сохраняются. В этом и заключается отличие внешнего соединения от внутреннего.

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

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

Существуют три разновидности внешних соединений.

 

Левое внешнее соединение

 

Операция LEFT JOIN (LEFT OUTER JOIN) возвращает все строки из левой таблицы, соединенные с теми строками из правой таблицы, для которых выполняется условие соединения. Если во второй таблице нет таких строк, то в качестве значений столбцов правой таблицы будут установлены значения NULL.

В базе данных booktown в таблице books содержится общая информация о книгах, а в таблице editions хранятся данные, относящиеся к конкретному изданию – код ISBN, издатель и дата публикации. В таблицу editions входит поле book_id, связывающее ее с полем id, которое является первичным ключом таблицы books.

Допустим, требуется информация о каждой книге вместе со всеми имеющимися кодами ISBN:

SELECT title, isbn

FROM books INNER JOIN editions

ON (books.id = editions.book_id);

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

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

SELECT title, isbn

FROM books LEFT OUTER JOIN editions

ON (books.id = editions.book_id);

Теперь в итоговом наборе будут присутствовать и те книги, у которых отсутствуют коды ISBN. В этом запросе использовано левое внешнее соединение. Выбор объясняется тем, что запрос должен вернуть названия книг, для которых существуют или не существуют коды ISBN. Поскольку таблица books стоит слева от ключевого слова JOIN, задача решается при помощи левого внешнего соединения.

 

Правое внешнее соединение

 

Операция RIGHT JOIN (RIGHT OUTER JOIN) возвращает все строки из правой таблицы, соединенные с теми строками из левой таблицы, для которых выполняется условие соединения. Если во второй таблице нет таких строк, то в качестве значений столбцов правой таблицы будут установлены значения NULL.

В качестве примера получим названия книг, у которых нет кодов ISBN:

SELECT title

FROM editions RIGHT JOIN books

ON (editions.book_id = books.id)

WHERE isbn IS NULL;

 

Полное внешнее соединение

 

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

Если бы помимо названий всех книг (в том числе и не имеющих кодов ISBN) нас интересовали коды ISBN (в том числе и не соответствующие зарегистрированным в базе данных книгам), следовало бы воспользоваться полным внешним соединением:

SELECT title, isbn

FROM books FULL OUTER JOIN editions

ON (books.id = editions.book_id);

Ключевое слово OUTER во внешних соединениях PostgreSQL является необязательным. Определения LEFT JOIN, RIGHT JOIN и FULL JOIN подразумевают внешнее соединение.

Подзапросы

 

Подзапросом называется команда SELECT, заключенная в круглые скобки, которая выполняется в контексте другой команды SQL. Обычно подзапросы содержатся в условии оператора WHERE или HAVING внешнего запроса. В свою очередь, подзапрос может содержать другой подзапрос и т.д.

 

Простые подзапросы

 

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

Рассматривая простые подзапросы, следует выделить три частных случая:

· подзапросы, возвращающие единственное значение;

· подзапросы, возвращающие список значений из одного столбца таблицы;

· подзапросы, возвращающие набор записей.

 

Работа с набором записей

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

Допустим, что имеется таблица Рейсы (Начальный_пункт, Конечный_пункт), содержащая сведения о том, из каких пунктов и в какие можно попасть с помощью того или иного авиарейса. Получим сведения, в какие пункты можно попасть, сделав не более одной пересадки (т.е. без пересадок или с одной пересадкой), только из пункта А:

SELECT *

FROM

(SELECT Начальный_пункт, Конечный_пункт

FROM Рейсы

UNION

SELECT T1.Начальный_пункт, T2.Конечный_пункт

FROM Рейсы T1, Рейсы T2

WHERE T1.Конечный_пункт = T2.Начальный_пункт) AS T

WHERE T.Начальный_пункт = 'A';

 

Связанные подзапросы

 

Связанные (коррелированные) подзапросы позволяют выразить более сложные вопросы относительно сведений, хранящихся в базе данных. При выполнении запросов, содержащих связанные подзапросы, нет такого четкого разделения по времени выполнения между подзапросом и запросом, как в случае простых подзапросов. В случае простых подзапросов сначала выполняется подзапрос, а затем содержащий его запрос. При наличии связанного подзапроса порядок выполнения запроса в целом иной. Основной признак связанного подзапроса заключается в том, что он не может быть выполнен самостоятельно, вне всякой связи с основным запросом. Формально этот признак обнаруживается в выражении сложного запроса следующим образом: подзапрос ссылается на таблицу, которая упоминается в основном запросе.

Рассмотрим некоторый абстрактный и, в то же время, типичный запрос, содержащий связанный подзапрос:

SELECT T1.A

FROM T1

WHERE T1.B = (SELECT T2.B

         FROM T2

         WHERE T2.C = T1.C);

Данный запрос на выборку данных содержит подзапрос, сформулированный в выражении, размещенном в основном запросе после ключевого слова WHERE. Запрос в целом использует две таблицы: T1 и T2, в которых есть столбцы с одинаковыми именами B и C и одинаковыми типами. Подзапрос (SELECT T2.B FROM T2 WHERE T2.C = T1.C) обращается к этим же таблицам. Поскольку одна из таблиц (T1) фигурирует как в подзапросе, так и во внешнем запросе, то подзапрос нельзя выполнить самостоятельно, вне связи с внешним запросом. Поэтому выполнение запроса в целом (т.е. внешнего запроса) происходит следующим образом:

1. Сначала выделяется первая запись из таблицы T1, указанной в операторе FROM внешнего запроса (вся запись таблицы T1, а не только значение столбца A). Эта запись называется текущей. Значения столбцов для этой записи доступны и могут быть использованы в подзапросе.

2. Затем выполняется подзапрос, который возвращает список значений столбца B таблицы T2 в тех записях, в которых значение столбца C равно значению столбца C из таблицы T1.

3. Будем считать, что в этом примере подзапрос возвращает единственное значение. Если это не так, то потребуется использование, например, предикатов вместо оператора сравнения (=). Теперь выполняется оператор WHERE основного запроса. Если значение столбца B в текущей (выделенной) записи таблицы T1 равно значению, возвращенному подзапросом, то эта запись выделяется внешним запросом.

4. Оператор SELECT внешнего запроса выполняет проверку условия своего оператора WHERE. Если оно истинно, то значение столбца A текущей записи таблицы T1 помещается в результатную таблицу, в противном случае запись игнорируется. Затем происходит переход к следующей записи таблицы T1. Теперь для нее выполняется подзапрос. Аналогичным образом все описанное происходит для каждой записи таблицы T1.

 

Получим, например, список покупателей, когда-либо делавших покупки:

SELECT last_name, first_name

FROM customers

WHERE (SELECT count(isbn)

FROM shipments

WHERE customer_id = customers.id) >= 1;

 

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

Итак, получим список покупателей, когда-либо делавших покупки:

SELECT last_name, first_name

FROM customers

WHERE EXISTS (SELECT isbn

        FROM shipments

        WHERE customer_id = customers.id);

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

SELECT last_name, first_name

FROM customers

WHERE NOT EXISTS (SELECT isbn

            FROM shipments

            WHERE customer_id = customers.id);

 

Предикат UNIQUE имеет такой же смысл, как и EXISTS, но при этом для его истинности требуется, чтобы все записи в результатной таблице не только существовали, но и были уникальны (т.е. не повторялись).

Предикат DISTINCT почти такой же, как и UNIQUE. Отличие этих предикатов обнаруживается применительно к значениям NULL. Так, если в результатной таблице все записи уникальны (предикат UNIQUE истинен), то и предикат DISTINCT тоже истинен. С другой стороны, если в результатной таблице имеются хотя бы две неопределенные записи, то предикат DISTINCT ложен, хотя предикат UNIQUE истинен.

 

Представления

 

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

Представление можно рассматривать как хранимый запрос, на основе которого создается объект базы данных. Этот объект очень похож на таблицу, но в его содержимом динамически отражается состояние только тех записей, которые были заданы при создании представления. Представления не являются физическими объектами хранения данных. Данные в представлениях, подобно в ответах на запрос SELECT, просто выбираются из таблиц базы данных, т.е. представляются в том или ином виде. В действительности за представлением стоит скрытый SQL-запрос. Работать с представлением можно как с обычной таблицей. Однако любой запрос к представлению в действительности инициирует скрытый запрос, который комбинируется с пользовательским.

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

 

Представления создаются командой

         CREATE VIEW имяПредставления AS

         запросSELECT;

Как и обычной таблице базы данных, представлению присваивается имя, которое не должно совпадать ни с одним именем таблиц. За ключевым словом AS следует SQL-выражение запроса на выборку данных.

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

SELECT title, count(*) AS num_shipped,

    max(ship_date) AS last_date

FROM shipments JOIN editions USING (isbn)

JOIN books ON (book_id = books.id)

GROUP BY title

ORDER BY num_shipped DESC;

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

CREATE VIEW recent_shipments AS

SELECT title, count(*) AS num_shipped,

        max(ship_date) AS last_date

FROM shipments JOIN editions USING (isbn)

    JOIN books ON (book_id = books.id)

GROUP BY title

ORDER BY num_shipped DESC;

 

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

SELECT *

FROM recent_shipments;

Получим названия книг, которые были куплены в наибольшем количестве:

SELECT title

FROM recent_shipments

WHERE num_shipped = (SELECT max(num_shipped)

                 FROM recent_shipments)

ORDER BY title;

 

Некоторые современные СУБД поддерживают операции изменения содержимого представлений – вставку, изменение и удаление записей. Однако это возможно лишь в том достаточно редком случае, когда имеется однозначное соответствие между столбцами представления и столбцами таблицы базы данных. Для этого в операторе SELECT представления не должно использоваться более одной таблицы, вычисляемых выражений, группировки, ключевого слова DISTINCT и т.п.

В PostgreSQL при попытке вызова команд INSERT, UPDATE или DELETE для представления происходит ошибка.

 

Представления удаляются из базы данных командой

DROP VIEW имяПредставления;

При удалении представления данные сохраняются без изменения. Теряется только запрос, на котором основано представление.

 

 

Лабораторная работа 6

 

Следующие запросы к базе данных booktown рекомендуется набирать в файлах (по одному запросу в файле). Запустить запрос из файла можно командой \i имя_файла. Подзапросы и представления сопровождать комментариями, поясняющими их назначение.

1. Пусть автор получает половину от стоимости (cost) каждой проданной книги. Определить прибыль каждого автора. Отсортировать по авторам.

2. Определить, сколько книг было на складе до продажи. Отсортировать по кодам isbn.

3. Список тем, нашедших отражение в произведениях только одного автора. Отсортировать по названию темы.

4. Список покупателей, купивших более одной книги, но все на одну и ту же тему. Отсортировать по покупателю.

5. Список покупателей, купивших книги и в твердой, и в бумажной обложках. Отсортировать по покупателям.

6. Список тем, книги по которым выдержали наибольшее количество изданий.

7. Название самой популярной у покупателей темы (тем).

8. Код isbn, автор и издатель бестселлера(ов) – самой покупающейся книги.

9. Издатель, средняя цена книг которого самая дешевая.

10. Для каждой темы определить, сколько книг издано в жесткой и сколько – в бумажной обложках. Отсортировать по теме.

11. Фамилии авторов, писавших на одну и ту же тему несколько раз. Отсортировать по фамилии автора.

12. Список покупателей, покупавших одно и то же произведение несколько раз. Отсортировать по покупателю.

13. Список авторов, не написавших ни одной книги. Отсортировать по автору.

14. Названия тем, которые никогда не были изданы. Упорядочить по названию темы.

15. Коды ISBN книг с наименьшей разницей между ценой закупки и ценой продажи.

16. Название издательства (издательств), выпустивших наибольшее количество книг (с различными isbn).

17. Список издателей, книги которых хуже всего продаются.

18. Все различные пары кодов isbn, относящихся к одной и той же книге.

19. Все различные пары авторов, писавших на одну и ту же тему.

20. Все различные пары авторов, никогда не писавших на одну и ту же тему.

КОМАНДЫ ИЗМЕНЕНИЯ ДАННЫХ

 

При создании и дальнейшем сопровождении базы данных обычно возникает задача добавления новых и удаления ненужных записей, а также изменения содержимого ячеек таблицы. В SQL для этого предусмотрены операторы INSERT (вставить), DELETE (удалить) и  UPDATE (изменить). Запросы, начинающиеся с этих ключевых слов, не возвращают данные в виде виртуальной таблицы, а изменяют содержимое уже существующих таблиц базы данных. Запросы на модификацию данных могут содержать вложенные запросы на выборку данных из той же самой таблицы или из других таблицы, однако сами не могут быть вложены в другие запросы.

 

Добавление новых записей

 

Для добавления (вставки) записи в таблицу служит оператор INSERT, который имеет несколько форм:

· INSERT INTO имяТаблицы VALUES (списокЗначений) – вставляет пустую запись в указанную таблицу и заполняет эту запись значениями из списка, указанного за ключевым словом VALUES. При этом первое в списке значение вводится в первый столбец таблицы, второе значение – во второй столбец и т.д. Порядок столбцов задается при создании таблицы. Данная форма оператора INSERT не очень надежна, поскольку нетрудно ошибиться в порядке вводимых значений.

· INSERT INTO имяТаблицы (списокСтолбцов) VALUES (списокЗначений) – вставляет пустую запись в указанную таблицу и вводит в заданные столбцы значения из указанного списка. При этом в первый столбец из списокСтолбцов вводится первое значение из списокЗначений, во второй столбец – второе значение и т.д. Порядок имен в списке можеть отличаться от их порядка, заданного при создании таблицы. Столбцы, которые не указаны в списке, заполняются значениями NULL.

· INSERT INTO имяТаблицы (списокСтолбцов) SELECT ... – вставляет в указанную таблицу записи, возвращаемые запросом на выборку. На практике нередко требуется загрузить в одну таблицу данные из другой таблицы. Например,

INSERT INTO books

      (id, title, author_id, subject_id)

SELECT book_id, title, author_id, subject_id

FROM book_queue

WHERE subject_id = 4;

Удаление записей

 

Для удаления записей из таблицы применяется оператор DELETE (удалить):

                DELETE

                FROM имяТаблицы

                WHERE условие;

Данный оператор удаляет из указанной таблицы записи (а не отдельные значения столбцов), которые удовлетворяют указанному условию.

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

DELETE

FROM stock

WHERE stock = 0;

В операторе WHERE может находиться подзапрос на выборку данных (оператор SELECT). Подзапросы в операторе DELETE работают точно так же, как и в операторе SELECT.

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

SELECT *

FROM stock

WHERE stock = 0;

Для удаления всех записей из таблицы достаточно использовать оператор DELETE без ключевого слова WHERE. При этом сама таблица со всеми определенными в ней столбцами остается и готова для вставки новых записей. Например,

DELETE

FROM stock;

 

Изменение данных

 

Для изменения значений столбцов таблицы применяется оператор UPDATE (изменить, обновить). Чтобы изменить значения в одном столбце таблицы в тех записях, которые удовлетворяют некоторому условию, следует выполнить такой запрос:

         UPDATE имяТаблицы

         SET имяСтолбца = значение

         WHERE условие;

За ключевым словом SET (установить) следует выражение равенства, в левой части которого указывается имя столбца, а в правой – выражение, значение которого следует сделать значением данного столбца. Эти установки будут выполнены в тех записях, которые удовлетворяют условию в операторе WHERE.

Чтобы одним оператором UPDATE установить новые значения сразу для нескольких столбцов, вслед за ключевым словом SET записываются соответствующие выражения равенства, разделенные запятыми. Например:

UPDATE publishers

SET name = 'O\'Reilly & Associates',

address = 'O\'Reilly & Associates, Inc. '

    || '101 Morris St, Sebastopol, CA 95472'

WHERE id = 113;

Использование оператора WHERE в операторе UPDATE не обязательно. Если он отсутствует, то указанные в SET изменения будут произведены для всех записей таблицы.

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

SELECT *

FROM publishers

WHERE id = 113;

Условие в операторе WHERE может содержать подзапросы, в том числе и связанные.

Некоторые СУБД (например, PostgreSQL) имеют расширение стандарта SQL, позволяющее обновлять одну таблицу данными из другой. В этом случае команда UPDATE дополняется поддержкой секции FROM. Секция FROM позволяет получать входные данные из других наборов данных (таблиц и подзапросов).

Например, обновим данные таблицы stock по данным таблицы stock_backup:

UPDATE stock

SET retail = stock_backup.retail

FROM stock_backup

WHERE stock.isbn = stock_backup.isbn;

Секция WHERE описывает связь между обновляемой таблицей и источником. Каждый раз, когда в таблицах находятся совпадающие значения isbn, поле retail в таблице stock обновляется значением из резервной таблицы stock_backup.

Секция FROM поддерживает все разновидности синтаксиса JOIN, что открывает широкие возможности обновления данных в существующих наборах.

 

 

Лабораторная работа 7

 

1. Переключиться в БД с номером вашей группы (команда монитора: \c имяБазыДанных) и создать в этой БД таблицу для хранения данных о людях, характеризуемых фамилиями, именами, возрастом (число прожитых лет), весом (в кг) и ростом (в см).

2. Внести в созданную таблицу данные о шести произвольных людях в возрасте от 16 до 50 лет, имеющих вес от 40,5 до 99,5 кг и рост 150 – 195 см.

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

4. Создать третью таблицу – копию первой таблицы, но не содержащую столбца с именами людей.  

5. Преобразовать третью таблицу таким образом, чтобы она содержала данные о росте в дюймах, а весе – в фунтах (1 фунт = 454 г, 1 дюйм = 2,54 см).

6. Из третьей таблицы удалить строки, содержащие сведения о людях моложе 20 лет.

7. Добавить в третью таблицу данные о фамилии и возрасте еще двух произвольных человек.

8. Удалить из первой таблицы сведения о людях, чьи фамилии есть во второй таблице.

9. Продемонстрировав все созданные таблицы преподавателю, уничтожить все таблицы.

 

 

ОПРЕДЕЛЕНИЕ ДАННЫХ

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

 

Оператор CREATE TABLE

 

Создание таблицы производится с помощью оператора CREATE TABLE с указанием необходимых параметров.

Для создания таблицы необходимо указать ее имя и определить столбцы. Определение столбца включает его имя и тип. Если указывается длина столбца, то она заключается в круглые скобки после типа. Кроме того, можно указать ограничения для столбца. Все перечисленные элементы определения столбца указываются друг за другом через пробел. Если создаваемая таблица содержит несколько столбцов, то их определения разделяются запятыми.

Далее приведен запрос на создание таблицы authors без ограничений:

CREATE TABLE authors

(id integer,

last_name text,

first_name text);

 

Ограничения для столбцов

 

NOT NULL               Столбец не может содержать значение NULL, т.е. значения этого столбца должны быть определенными.

UNIQUE                    Значение, вводимое в столбец, должно отличаться от всех остальных значений в этом столбце, т.е. быть уникальным.

PRIMARY KEY         Столбец является первичным ключом. В каждой таблице только один столбец может быть первичным ключом. Это означает, что он не может содержать значение NULL, а вводимое в него значение должно отличаться от всех остальных значений в этом столбце. Таким образом, PRIMARY KEY является комбинацией NOT NULL и UNIQUE.

DEFAULT значение Устанавливает значение по умолчанию. Так, при добавлении новой записи столбец с таким ограничением автоматически получит указанное значение.

CHECK (условие) Позволяет производить проверку условия при вводе данных. Значение будет сохранено, если условие выполняется, в противном случае – нет.

 

Создадим таблицу authors с использованием ограничений столбцов:

CREATE TABLE authors

(id integer PRIMARY KEY CHECK (id > 0),

last_name text NOT NULL,

first_name text);

 

Ограничения для таблиц

 

В ограничениях таблиц, в отличие от ограничений полей, могут участвовать сразу несколько полей таблицы. Для ограничения таблицы может быть задано имя. В будущем имя ограничения может пригодиться для удаления ограничения (например, в секции DROP CONSTRAINT команды ALTER TABLE).

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

 

UNIQUE                    Ограничение означает, что комбинация значений полей, перечисленных за ключевым словом UNIQUE, принимает только уникальные значения. Допускается многократное вхождение псевдозначения NULL.

PRIMARY KEY         В ограничении могут перечисляться несколько полей, разделенных запятыми. Используется для составного первичного ключа.

CHECK (условие) Команды INSERT или UPDATE для записи завершаются успешно лишь при выполнении заданного условия. Может содержать ссылки на несколько полей.

 

Создадим таблицу authors с использованием ограничений таблицы:

CREATE TABLE authors

(id integer,

last_name text NOT NULL,

first_name text,

CONSTRAINT id_pkey PRIMARY KEY (id),

CONSTRAINT id_check CHECK (id > 0),

CONSTRAINT name_uniq UNIQUE (last_name, first_name));

 

Внешние ключи

 

Внешний ключ – это столбец или группа столбцов, соответствующих первичному ключу другой таблицы. Внешний ключ определяется как ограничение столбца с помощью выражения REFERENCES внешняяТаблица (первичныйКлюч). Если первичныйКлюч в ограничении не указан, проверка выполняется по первичному ключу внешней таблицы.

Создадим таблицу books:

CREATE TABLE books

(id integer PRIMARY KEY,

title TEXT NOT NULL,

author_id INTEGER REFERENCES authors (id),

subject_id INTEGER NOT NULL REFERENCES subjects);

Ограничение внешнего ключа может содержать дополнительные секции ON DELETE и ON UPDATE. В этих секциях указывается, какую операцию следует произвести с полем внешнего ключа, если будет удален или изменен соответствующий первичный ключ. Возможные операции:

NO ACTION     Если удаление (изменение) первичного ключа приводит к нарушению целостности ссылок, происходит ошибка. Используется по умолчанию, если операция не указана.

RESTRICT        Аналогично NO ACTION.

CASCADE          Удаление (обновление) всех записей, содержащих ссылки на удаляемую (обновляемую) запись.

SET NULL        Поля, содержащие ссылки на удаляемую (обновляемую) запись, заменяются псевдозначениями NULL.

SET DEFAULT Полям, содержащим ссылки на удаляемую (обновляемую) запись, присваивается значение по умолчанию.

Рассмотрим данные операции на примере создания таблицы books:

CREATE TABLE books

(id integer PRIMARY KEY,

title   TEXT NOT NULL,

author_id INTEGER REFERENCES authors (id)

ON DELETE NO ACTION ON UPDATE CASCADE,

subject_id INTEGER NOT NULL

REFERENCES subjects ON UPDATE CASCADE);

 

Ограничение внешнего ключа может быть и ограничением таблицы. Если внешний ключ – составной, то единственный способ его задать – это использовать ограничение таблицы. В случае ограничения таблицы определение ограничения предваряется ключевыми словами FOREIGN KEY (списокПолей).

Создадим таблицу books с использованием ограничений таблицы в качестве ограничений внешнего ключа:

CREATE TABLE books

(id integer PRIMARY KEY,

title   TEXT NOT NULL,

author_id INTEGER,

subject_id INTEGER NOT NULL,

CONSTRAINT author_fk

FOREIGN KEY (author_id) REFERENCES authors (id)

ON DELETE NO ACTION ON UPDATE CASCADE,

FOREIGN KEY (subject_id) REFERENCES subjects
ON UPDATE CASCADE);

 

Удаление таблиц

 

Удалить таблицу из базы данных можно следующим образом:

DROP TABLE имяТаблицы;

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

 

Модификация таблиц

 

Модификация таблицы – это изменение ее структуры. Для модификации таблиц предназначена команда ALTER TABLE. Реализация ALTER TABLE в PostgreSQL 7.1.x поддерживает следующие типы модификации:

· создание полей;

· назначение и отмена значений по умолчанию;

· переименование таблицы;

· переименование полей;

· добавление ограничений.

 

Создание полей

 

Для создания нового поля в команду ALTER TABLE включается секция ADD COLUMN:

         ALTER TABLE имяТаблицы

         ADD COLUMN имяСтолбца типСтолбца;

Ключевое слово COLUMN не является обязательным.

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

ALTER TABLE books

ADD publication date;

 

Переименование таблицы

 

Переименование таблиц осуществляется командой ALTER TABLE с секцией RENAME:

            ALTER TABLE имяТаблицы

            RENAME TO новоеИмя;

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

Переименуем таблицу books  в таблицу literature:

ALTER TABLE books

RENAME TO literature;

 

Переименование полей

 

Изменять имена полей можно без изменения данных, хранящихся в таблице. Однако, переименование полей – довольно опасная операция, поскольку существующие приложения могут содержать ссылки на имена полей. Если программа обращается к полю по имени, то переименование может нарушить ее работоспособность. Команда переименования полей имеет следующий синтаксис:

         ALTER TABLE имяТаблицы

         RENAME COLUMN имяПоля TO новоеИмя;

Ключевое слово COLUMN не является обязательным. По двум идентификаторам, разделенным ключевым словом TO, PostgreSQL может определить, что команда переименования относится к одному полю, а не таблице.

Например,

ALTER TABLE stock

RENAME COLUMN stock TO is_in_stock;

 

Добавление ограничений

 

После создания таблицы сохраняются некоторые возможности добавления ограничений. В PostgreSQL 7.1.x команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа и проверки (CHECK). Команда создания новых ограничений имеет следующий синтаксис:

ALTER TABLE имяТаблицы

ADD CONSTRAINT имяОграничения ограничение;

Синтаксис ограничения зависит от типа ограничения.

Создадим ограничение внешнего ключа для таблицы editions (связанной с полем id таблицы books):

ALTER TABLE editions

ADD CONSTRAINT foreign_book

FOREIGN KEY (book_id) REFERENCES books (id);

Добавим ограничение для проверки поля type:

ALTER TABLE editions

ADD CONSTRAINT type_check CHECK (type IN (‘p’, ‘h’));

 

 

Лабораторная работа 8

 

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

1. Создать таблицу для хранения данных о студентах с полями: фамилия, номер зачетки, адрес, год поступления в университет, номер специальности, номер группы; ввести ограничения первичного ключа, отсутствия пустых значений для фамилии и года поступления, значение специальности по умолчанию 0220100.

2. Заполнить данными созданную таблицу (не менее 5 записей), при вводе попытаться ввести записи с дублирующимися номерами зачетных книжек, однофамильцами, без фамилии, без года поступления в университет, без специальности; зафиксировать в отчете свои действия и реакцию на них сервера БД (что происходит и почему). Привести в отчете окончательный вид созданной таблицы.

3. Создать таблицу для хранения данных о сдаче студентами различных экзаменов по дисциплинам с оценками и заполнить ее данными (каждый студент сдает не менее трех экзаменов); задать первичный ключ в таблице; обеспечить выполнение условий целостности данных, чтобы экзамены сдавались только студентами, у которых есть зачетные книжки, а при удалении записей о студентах удалялись бы и записи о сданных ими экзаменах (каскадное удаление).

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

ПРИЛОЖЕНИЯ

 

П.1. Возможности PostgreSQL

 

PostgreSQL – объектно-реляционная система управления базами данных, разработка которой в различных формах ведется с 1977 года. В 1996 году был осуществлен переход на распространение PostgreSQL с открытыми исходными текстами. В настоящее время над проектом PostgreSQL активно работает группа разработчиков со всего мира.

PostgreSQL считается самой совершенной СУБД, распространяемой на условиях открытых исходных текстов. В PostgreSQL реализованы многие возможности, традиционно встречавшиеся только в масштабных коммерческих продуктах:

· объектно-реляционная модель позволяет задействовать сложные процедуры и системы правил, например, контроль параллельного доступа, поддержку многопользовательского доступа, транзакции, оптимизацию запросов, поддержку и наследование массивов;

· простота расширения: в PostgreSQL поддерживаются пользовательские операторы, функции, методы доступа и типы данных;

· полноценная поддержка SQL;

· проверка целостности ссылок;

· поддержка внутренних процедурных языков;

· архитектура «клиент-сервер».

 

П.2. Консоль psql

 

Система PostgreSQL, как и большинство сетевых СУБД, основана на парадигме “клиент-сервер”. Центральное место в PostgreSQL занимает процесс postmaster, предназначенный не для прямого взаимодействия с пользователем, а для обслуживания подключений со стороны различных клиентов.

При запуске службы (service) PostgreSQL процесс postmaster начинает работать в фоновом режиме, прослушивая заданный порт TCP/IP в ожидании подключений со стороны клиентов.

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

Запуск psql осуществляется командой:

psql -h host_name db_name [-U user_name]

Если пользователь зарегистрирован администратором БД и база данных с именем db_name имеется на сервере, то соединение произойдет, что отразится в командной строке:

db_name=>

В лабораторных работах host_name – это server, а db_name – booktown.

 

Команды консоли psql делятся на две группы:

· команды языка SQL для работы с данными в базе;

· внутренние команды psql, начинающиеся с символа ‘\’.

 

Некоторые полезные команды клиента psql:

\?                            Справка по командам psql             

\с база_данных  Подключение к другой базе данных       

\dt                          Список таблиц в текущей базе данных    

\d имя_таблицы  Структура таблицы                        

\h команда           Справка по командам sql                                   

\i имя_файла      Чтение входных данных из файла

\l                            Список имеющихся на сервере баз данных      

\o файл                 Перенаправление вывода в файл

\q                            Выход из psql                                           

\t                            Режим выдачи дополнительной информации о таблице

\z имя_таблицы  Права доступа к таблице

 

П.3. Команды SQL

 

П.3.3. Выполнение запросов

 

В psql существует два способа ввода и выполнения запросов. В интерактивном режиме запросы обычно вводятся непосредственно в приглашении командной строки (то есть из стандартного ввода). Команда psql \i читает файл и использует его содержимое в качестве входных данных.

Чтобы передать PostgreSQL команду SQL, следует просто ввести ее в приглашении. Весь вводимый текст накапливается до тех пор, пока ввод не будет завершен символом точки с запятой (;). Ввод команды не прерывается даже разрывами строк, что позволяет распределить запрос по нескольким строкам.

Если в предыдущей строке присутствует символ, требующий парного завершителя (например, круглой скобки или кавычки), этот символ включается в приглашение следующей строки. Например, если начать команду CREATE TABLE с открывающей круглой скобкой и перейти на другую строку, то приглашение будет выглядеть следующим образом:

booktown=# CREATE TABLE employees (

booktown(#

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

 

Защищенные идентификаторы

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

SELECT * FROM states;

Аналогичного эффекта можно добиться, заключив идентификатор в кавычки:

SELECT * FROM “states”;

Применение кавычек к идентификаторам, записанным символами нижнего регистра, ни на что не влияет. Однако попытка защитить идентификатор stAtes в следующей команде приводит к неудаче:

SELECT * FROM “stAtes”;

Дело в том, что команда приказывает PostgreSQL найти таблицу с именем stAtes (вместо states). Другими словами, заключение идентификатора в кавычки требует, чтобы интерпретатор PostgreSQL интерпретировал его буквально.

Все незащищенные идентификаторы преобразуются к нижнему регистру. Любая смешанная комбинация символов разных регистров (stAtEs, STATES) при отсутствии кавычек перед выполнением команды автоматически приводится к виду states.

 

П.3.5. Комментарии

 

Комментарием называется фрагмент обычного текста, оформленный специальным образом и внедренный в код SQL. Комментарии не влияют на выполнение программы, поскольку PostgreSQL удаляет их из входного потока и интерпретирует как обычные пропуски. Существует две разновидности комментариев: однострочные и многострочные.

Однострочные комментарии начинаются с двух дефисов (--) и либо находятся в отдельной строке, либо следуют за лексемами SQL (модуль лексического разбора PostgreSQL не считает комментарии лексемами, а все символы, следующие за последовательностью --, интерпретирует как пропуски).

SELECT ‘Test’ -- This can follow valid SQL tokens

         -- or be on a line of its own.

AS example;

Многострочные комментарии начинаются с последовательности /* и завершаются последовательностью */. Такой способ оформления комментариев хорошо знаком программистам С, но между интерпретатором PostgreSQL и компилятором С существует одно принципиальное различие: комментарии PostgreSQL могут быть вложенными. Иначе говоря, если внутри многострочного комментария имеется другой многострочный комментарий, то закрывающая последовательность */ внутреннего комментария не закрывает внешний комментарий.

SELECT ‘Multitest’ /* This comment extends across

        * numerous lines, and can be

        * /* nested safely */ */

AS example;

 

Символьные типы

· character(n), char(n) – символьная строка фиксированной длины, дополненная пробелами до n символов. Занимает в памяти (4 + n) байт;

· character varying(n), varchar(n) – символьная строка переменной длины, максимальный размер равен n. Занимает в памяти до (4 + n) байт;

· text – строка переменной длины, максимальный размер не ограничен.

 

Числовые типы

· integer, int, int4 – целые числа в интервале от –2 147 483 648 до 2 147 483 647. Занимает в памяти 4 байта;

· smallint, int2 – целые числа в интервале от –32 768 до 32 767. Занимает в памяти 2 байта;

· bigint, int8 – целые числа в интервале от –9 223 372 036 854 775 807 до 9 223 372 036 854 775 807. Занимает в памяти 8 байт;

· numeric(p,s), decimal(p,s) –­ целые и вещественные числа из p цифр (всего) и s цифр в дробной части. Тип numeric (также называемый типом decimal) предназначен для представления сколь угодно больших или малых значений с фиксированной точностью, задаваемой пользователем. В круглых скобках указываются два значения: точность и масштаб. Точность определяет максимальное количество цифр (включая цифры в дробной части), а масштаб определяет количество цифр только в дробной части. Если параметры не заданы, по умолчанию точность равна 30, а масштаб – 6. Максимальная точность (а следовательно, и максимальный размер), задаваемая таким образом, равна 1000. На практике 1000 цифр обычно вполне достаточно. В отличие от вещественных чисел, при попытке вставить в поле numeric число, не входящее в интервал допустимых значений, происходит ошибка переполнения. В остальном допускается вставка любого числа, не нарушающего заданной точности. Например, в поле numeric(11,6) можно безопасно сохранить значение 9.99999999 с лишними цифрами в дробной части (хотя оно будет округлено до 10.000000). С другой стороны, попытка сохранения числа 99999.99999999 завершается неудачей;

· real, float4 – вещественные числа, 6 значащих цифр, неограниченный размер (с ограниченной точностью);

· double precision, float8, float – вещественные числа, 15 значащих цифр, неограниченный размер (с ограниченной точностью). Занимает в памяти 8 байт;

· money – вещественные числа с двумя цифрами в дробной части в интервале от –21 474 836.48 до 21 474 836.47 для представления денежных величин. Занимает в памяти 4 байта. Тип считается устаревшим и использовать его не рекомендуется. Вместо типа money следует использовать тип numeric с масштабом 2 и точностью, достаточной для представления максимальной необходимой величины (включая 2 цифры для дробной части);

· serial – целые числа в интервале от 0 до 2 147 483 647 с автоматическим приращением. Занимает в памяти 4 байта. Хотя тип serial не относится к числу стандартных типов, он часто используется при создании в таблице полей-идентификаторов, содержащих уникальное значение для каждой записи. В типе serial объединены функциональные возможности 4-байтового типа integer, индекса и последовательности.

 

Типы даты и времени

· date – календарная дата (год, месяц и день) от 4 713 г. до н.э. до 32 767 г. н.э. Занимает в памяти 4 байта;

· time – время суток без часового пояса от 00:00:00 до 23:59:59.99. Занимает в памяти 4 байта;

· time with time zone – время суток с часовым поясом от 00:00:00+12 до 23:59:59.99–12. Занимает в памяти 4 байта;

· timestamp with time zone, timestamp – календарная дата и время с часовым поясом от 1 903 г. н.э. до 2 037 г. н.э. Занимает в памяти 8 байт.

Встроенные константы даты и времени:

· current – текущее время обработки транзакции, не привязывается к конкретному времени и возвращает текущее системное время;

· now –  фиксированное время обработки транзакции;

· today – полночь текущего дня;

· tomorrow – полночь следующего дня;

· yesterday – полночь предыдущего дня;

· infinity – абстрактная константа, более "поздняя" по сравнению со всеми допустимыми значениями даты и времени;

· -infinity – абстрактная константа, более "ранняя" по сравнению со всеми допустимыми значениями даты и времени;

· epoch – 1970-01-01 00:00:00+00 ("день рождения" Unix).

 

Интервальный тип

· interval – фиксированный интервал времени. Сам по себе тип interval представляет лишь количественную величину, не связанную с определенными начальным или конечным моментом. Интервалы часто используются в сочетании с типами даты и времени для вычисления новой величины посредством сложения или вычитания. Кроме того, они могут пригодиться для быстрого вычисления точного промежутка времени между двумя датами или моментами времени, для чего одна величина вычитается из другой.

Синтаксис определения интервалов:

длина1 единица1 [длина 2 единица2 ...] [ago]

Здесь:

· длина – продолжительность интервала, заданная в виде целого или вещественного (для микросекунд) числа. Интерпретация числа определяется следующим параметром;

· единица – единица, в которой измеряется заданный интервал. Разрешены следующие ключевые слова: second, minute, hour, day, week, month, year, decade, century, millenium. Также допускаются сокращения (любой длины при условии однозначной интерпретации) и формы множественного числа;

· ago – необязательное ключевое слово указывает, что описываемый период времени предшествует некоторому моменту, а не следует после него.

Примеры использования значений типа интервал:

date('1980-06-25') + interval('21 years 8 days')

date('1980-06-25') - interval('21 years 8 days ago')

 

Геометрические типы

· box – прямоугольник на плоскости;

· line – бесконечная линия на плоскости;

· lseg – отрезок на плоскости;

· circle – круг с заданным центром и радиусом;

· path – замкнутая или разомкнутая геометрическая фигура на плоскости;

· point – точка на плоскости;

· polygon – замкнутый многоугольник на плоскости.

 

Сетевые типы

· cidr – спецификация сети IP;

· inet – сетевой IP-адрес с необязательными битами подсети;

· macaddr – МАС-адрес (например, аппаратный адрес адаптера Ethernet).

 

Системные типы

· oid – идентификатор объекта (записи);

· xid – идентификатор транзакции.

 

П.4.2. Преобразование типов

 

В PostgreSQL поддерживаются три отдельных варианта синтаксиса преобразования (приведения типов), то есть механизма приведения данных от одного типа к другому. В команде SQL преобразование типов позволяет явно задать тип создаваемой константы (вместо его косвенного определения по правилам языка).

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

· тип 'значение'

· 'значение' :: тип

· CAST ('значение' AS тип)

Числовые константы преобразуются в символьную строку следующими способами:

· значение :: тип

· CAST (значение AS тип)

Здесь значение представляет константу, тип которой требуется изменить, а тип – новый тип этой константы.

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

· идентификатор :: тип

· CAST (идентификатор AS тип)

Следует учесть, что не каждый тип данных может быть приведен к любому другому типу. Например, не существует осмысленного преобразования символьной строки abcd в двоичную последовательность типа bit. Недопустимые попытки преобразования приводят к ошибкам PostgreSQL.

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

 

П.4.3. Константы

 

При работе с базами данных многие объекты хранятся на диске, а для обращения к ним используются идентификаторы (имена таблиц, полей и функций). Однако неизбежно настанет момент, когда в систему потребуется передать новые данные – например, при вставке новых записей, при формировании секций с критериями удаления или модификации или при вычислениях на базе существующих записей. Такие данные передаются в виде констант, также иногда называемых "литералами". Константы предназначены для "буквального" представления данных в командах SQL (вместо ссылки на них по идентификатору).

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

· строковые константы;

· битовые последовательности;

· целочисленные константы;

· вещественные константы;

· логические константы.

 

Строковые константы

Строковая константа представляет собой произвольную последовательность символов, заключенную в апострофы, например 'Louisa May'. Этот факт порождает следующую семантическую проблему: если в самой последовательности символов встречается апостроф, граница строковой константы будет определена неверно. Чтобы экранировать апостроф в строке (то есть обеспечить его интерпретацию как литерал), следует поставить два апострофа подряд, например 'PostgreSQL''s great!'. PostgreSQL также позволяет экранировать апострофы обратной косой чертой в стиле языка С, например 'PostgreSQL\'s great!'.

 

Битовые последовательности

Битовые последовательности предназначены для представления двоичных величин в виде произвольной последовательности нулей и единиц. Как и строковые константы, битовые последовательности заключаются в апострофы, но начинаются обязательно с префикса В (в верхнем или нижнем регистре), например B'00000000'. По наличию этого символа PostgreSQL определяет, что строка является битовой последовательностью, а не обычной символьной строкой.

В соответствии с синтаксисом PostgreSQL открывающий апостроф должен следовать сразу же после префикса В, а битовая последовательность не может содержать других символов, кроме 0 и 1.

 

Целочисленные константы

В PostgreSQL целочисленной константой считается любая лексема, состоящая из цифр (без десятичной точки) и не заключенная в апострофы.

 

Вещественные константы

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

Существуют несколько форматов представления вещественных констант:

· ##.##

· ##e[+-]##

· [##].##[e[+-]##]

· ##.[##][e[+-]##]

Запись ## означает одну или несколько цифр.

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

 

Логические константы

Допустимые обозначения логических констант: true, false, 't', 'f', 'true', 'false', 'y', 'n', 'yes', 'no', '1', '0'.

 

П.5. Операторы

 

В PostgreSQL поддерживаются стандартные операторы и функции SQL, определенные в стандарте ANSI/ISO, – математические операторы, основные функции форматирования текста, выделение компонентов даты/времени и т.д. Кроме того, в PostgreSQL реализованы многочисленные нестандартные расширения, в том числе операторы поиска по регулярным выражениям (здесь не рассматриваются) и универсальная функция форматирования данных to_char().

Операторы – это лексемы, предназначенные для выполнения операций с константами и идентификаторами и возвращающие результаты этих операций. Некоторые ключевые слова SQL тоже считаются операторами из-за воздействия на данные в командах SQL.

Действия, выполняемые оператором, зависят от контекста его применения. Область применения операторов чрезвычайно широка, от выполнения математических операций и конкатенации строк до разнообразных сравнений.

 

П.5.2. Строковые операторы

 

Операторы сравнения строк

=        Возвращает true, если первая строка точно совпадает со второй

!=      Возвращает true, если первая строка не совпадает со второй

<>      Идентичен оператору !=

<        Возвращает true, если при лексикографической сортировке первая строка предшествует второй

<=      Возвращает true, если при лексикографической сортировке первая строка предшествует второй или их значения совпадают

>        Возвращает true, если при лексикографической сортировке вторая строка предшествует первой

>=      Возвращает true, если при лексикографической сортировке вторая строка предшествует первой или их значения совпадают

 

Все операторы сравнения строк возвращают логическое значение (true или false). Лексикографическая сортировка последовательно сравнивает символы строк и определяет, какой из символов "больше" другого. Если начальные символы двух строк совпадают, проверяются следующие символы (слева направо). Перебор продолжается до тех пор, пока не будут найдены два различающихся символа. В этом алгоритме сортировки "больший" символ выбирается сравнением ASCII-кодов.

 

Конкатенация

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

Например,

SELECT 'The Title: ' || title ||
', by ' || first_name || ' ' || last_name

FROM books, authors

WHERE books.author_id = authors.id;

 

П.5.3. Числовые операторы

 

Числовые операторы PostgreSQL делятся на три категории:

· Математические операторы выполняют математическую операцию с одним или двумя операндами и возвращают значение числового типа.

· Операторы сравнения проверяют заданное соотношение между двумя числовыми величинами (например, что одна величина больше другой) и возвращают результат проверки в виде типа boolean.

· Двоичные операторы работают на уровне отдельных битов, то есть единиц и нулей в двоичном представлении.

 

Математические операторы

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

Оператор Синтаксис Описание

+   a+b         Суммирование числовых величин a и b

-   a-b         Вычитание числовой величины b из a

*   a*b         Умножение числовых величин a и b

/   a/b         Деление числовой величины a на b

%   a%b         Остаток от деления a на b

^   a^b         Возведение a в степень b

|/  |/a         Квадратный корень из a

||/ ||/a       Кубический корень из a

!   a!           Факториал a

!!  !!a         Факториал a (отличается от постфиксного оператора только расположением)

@   @a           Модуль (абсолютное значение) a

 

Например, вычислим удельную прибыль по каждой книге. Частное от деления преобразуем к типу numeric с усечением до двух цифр в дробной части. Из результата вычтем единицу, чтобы результат выражался в доле меньше 1.

SELECT isbn,
(retail/cost)::numeric(3.2)-1 AS margin

FROM stock

ORDER BY margin DESC;

 

Операторы сравнения

Операторы сравнения работают со значениями таких типов, как integer или text, но всегда возвращают результат типа boolean. Они часто встречаются в секции WHERE, но могут использоваться в любом контексте, в котором действителен тип boolean.

 

<            Возвращает true, если левое значение меньше правого

>            Возвращает true, если левое значение больше правого

<=          Возвращает true, если левое значение меньше правого или равно ему

>=          Возвращает true, если левое значение больше правого или равно ему

=            Возвращает true, если левое значение равно правому

<> или != Возвращает true, если левое значение не равно правому

 

П.5.4. Двоичные операторы

 

Оператор Синтаксис Описание

&   a&b         Поразрядная конъюнкция двоичных представлений a и b (которые могут быть заданы в виде целых чисел)

|   a|b         Поразрядная дизъюнкция двоичных представлений a и b (которые могут быть заданы в виде целых чисел)

#   a#b         Поразрядная операция исключающей дизъюнкции двоичных представлений a и b (которые могут быть заданы в виде целых чисел)

~   ~b           Поразрядное отрицание, возвращает инвертированную битовую последовательность b

<<  b<<n       Сдвиг b влево на n разрядов

>>  b>>n       Сдвиг b вправо на n разрядов

 

При сдвиге битовых последовательностей исходная длина строки не изменяется, а разряды, выходящие за левый или правый край последовательности, отсекаются. При использовании операторов &, | или # битовые операнды должны иметь одинаковую длину.

Например, сдвинем число 8 на два разряда вправо:

SELECT b'1000'>>2 AS "8 shifted right";

 

П.5.5. Логические операторы

 

Ключевые слова AND, OR и NOT являются логическими (булевыми) операторами. Обычно они используются для операций с логическими условиями в командах SQL, особенно в секциях WHERE и HAVING.

 

A    b    a AND b a OR b NOT a

true true true true false

true false false true false

true NULL NULL true false

false false false false true

false NULL false NULL true

NULL NULL NULL NULL NULL

 

П.5.6. Операторы и NULL

 

Если таблица содержит значения NULL, можно воспользоваться специальными операторами сравнения, чтобы учесть поля NULL при выборке или игнорировать их. Конструкция IS NULL проверяет, содержит ли поле значение NULL. Обратное условие проверяется конструкцией IS NOT NULL.

Например, найдем авторов, у которых отсутствуют данные об имени:

SELECT last_name, first_name

FROM authors

WHERE first_name IS NULL;

 

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

 

П.5.7. Приоритет операторов

 

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

Приоритет операторов SQL:

П.6. Функции

 

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

 

П.6.3. Строковые функции

 

ascii(s)                                      ASCII-код символа, переданного в виде строковой переменной s

btrim(s [, t])                         Строка s, в начале и конце которой удалены все символы, входящие в строку t (если аргумент t не задан, усекаются начальные и конечные пропуски – пробелы, символы табуляции и т.д.)

char_length(s)                         Длина строки s в символах

chr(n)                                          Символ с ASCII-кодом n

s ilike(f)                                  true, если выражение f совпадает (без учета регистра символов) с s

initcap(s)                                  Строка s, в которой первая буква каждого слова преобразуется к верхнему регистру

length(s)                                    Длина строки s в символах

s like(f)                                    true, если выражение f совпадает с s

lower(s)                                      Строка s, преобразованная к нижнему регистру

lpad(s, n [, c])                    Строка s, дополненная слева содержимым строки c (или пробелами, если аргумент c не задан) до длины n (или усеченная справа до n символов)

ltrim(s [, f])                         Строка s, в начале которой удалены все символы, входящие в строку f (если аргумент f не задан, усекаются начальные пропуски – пробелы, символы табуляции и т.д.)

octet_length(s)                       Длина строки s в байтах

position(b IN s)                    Позиция подстроки b в строке s (отсчет начинается с 1)

repeat(s, n)                             Строка s, повторенная n раз

rpad(s, n [, c])                    Строка s, дополненная справа содержимым строки с (или пробелами, если аргумент с не задан) до длины n (или усеченная слева до n символов)

rtrim(s [, f])                         Строка s, в конце которой удалены все символы, входящие в строку f (если аргумент f не задан, усекаются конечные пропуски – пробелы, символы табуляции и т.д.)

strpos(s, b)                             Позиция подстроки b в строке s (отсчет начинается с 1)

substr(s, n [, l])                Выделяет из строки s подстроку, начинающуюся с позиции n (отсчет начинается с 1). Необязательный аргумент l определяет максимальную длину подстроки в символах

substring(s FROM n FOR l) Выделяет из строки s подстроку, начинающуюся с позиции n (отсчет начинается с 1). Необязательный аргумент l определяет максимальную длину подстроки в символах

to_ascii(s, f)                         Строка s, преобразованная из расширенной кодировки f в ASCII

translate(s, f, r)                Строка s, в которой все символы, входящие в строку f, заменяются соответствующими символами строки r

trim(направление f FROM s) Строка s, в начале и/или конце которой удалены все символы, входящие в строку f. В аргументе направление передается ключевое слово SQL, определяющее направление усечения (LEADING, TRAILING или BOTH)

upper(s)                                      Строка s, преобразованная к верхнему регистру

 

СПИСОК ЛИТЕРАТУРЫ

 

1. Андон Ф. Язык запросов SQL. Учебный курс/ Ф. Андон, В. Резниченко. – Спб. ; Киев : Питер : Изд. группа BHV, 2006. – 415с. : ил. – ISBN 5-469-00394-9.

2. Грофф Джеймс Р., Вайнберг П. Энциклопедия SQL.-3-е изд. –СПб. : Питер, 2003. – 895с. : ил+1 CD. – ISBN 966-552-103-9;5- 88782-077-2.

3. Дунаев В.В. Базы данных. Язык SQL – СПб. : БХВ-Петербург, 2006. – 288. : ил. ISBN 5-94157-823-7.

4. Моисеенко С. И. SQL. Задачи и решения / С. И. Моисеенко. – Спб. : Питер, 2006. – 255с. : ил. – ISBN 5-469-01362-6.

5. Полякова Л. Н. Основы SQL : учеб. пособие / Л. Н. Полякова – 2-е изд., испp. – М. : Интернет-Университет Информационных Технологий : БИНОМ. Лаборатория знаний, 2007. – 223с. : ил. – (Основы информационных технологий). – ISBN 978-5-94774-649-5 ; 978-5-9556-0101-4.

6. СУБД : язык SQL в примерах и задачах : учеб. пособие для вузов / И. Ф. Астахова, В. М. Мельников, А. П. Толстобров, В. В. Фертиков. – М. : Физматлит, 2007. – 168с. : ил. – (Информационные и компьютерные технологии). – ISBN 978-5-9221-0816-4.

7. Уорсли Дж., Дрейк Дж. PostgreSQL. Для профессионалов (+ CD). – СПб. : Питер, 2003. – 496 с. : ил. ISBN 5-94723-337-1.

Для заметок

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

____________________________________________________________

 

Учебное издание

Мотина Надежда Владимировна

 

Язык SQL

 

Методические указания к лабораторным работам

по курсам «Базы данных» и «Управление данными»

 

 

Технический редактор: Н.В. Мотина

Компьютерная верстка: О.В. Воробьева

Корректор: С. Н. Емельянова

                                                                                                                                                 

 

Подписано в печать Формат 60´90/16

Гарнитура Times New Roman. Усл. п.л.

Тираж 68 экз. Заказ № +4747

 

Адрес издательства:

Россия, 180000, Псков, ул. Л. Толстого, д. 4

Издательство ПсковГУ

 

Н.В. Мотина

 

 

Язык SQL

 

Методические указания к лабораторным работам
по курсам «Базы данных» и «Управление данными»

 

Псков

Издательство ПсковГУ

 2013

 

УДК 004.655

ББК 32.973.26-018.1

              М85

Рекомендовано к изданию кафедрой
«Вычислительная техника»

Псковского государственного университета

 

Рецензенты:

– Аристов М.В. – кандидат физ.-мат. наук, доцент кафедры «Информационные системы и технологии» Псковского государственного университета;

– Хватцев А.А. – канд. физ.-мат. наук, доцент кафедры «Высшая математика» Псковского государственного университета.

 

Мотина, Н.В.

М85 Язык SQL: Методические указания к лабораторным работам по курсам «Базы данных» и «Управление данными». ¾ Псковский государственный университет. Псков : Изд.-во ПсковГУ, 2013. ¾ 104 с.

 

В методических указаниях приведены описания лабораторных работ, появященных ознакомлению с SQL – языком структурированных запросов к базе данных. Представлен синтаксис команд выборки данных, начиная с простейших и заканчивая достаточно сложными запросами с агрегированием данных и использованием подзапросов и представлений, а также команд изменения данных и команд определения данных. Изложение материала сопровождается примерами соответствующих SQL-запросов. Каждый раздел оформлен в виде отдельной лабораторной работы, где после рассмотрения теоретического материала следуют задания для самостоятельного выполнения. В приложении приведена система команд и особенности SQL-команд СУБД PostgreSQL 7.1.

 

УДК 004.655

ББК 32.973.26-018.1

 

 

© Мотина Н. В., 2013

© Псковский государственный университет, 2013

ОГЛАВЛЕНИЕ

1. ВВЕДЕНИЕ В SQL. 6

1.1. Что такое SQL. 6

1.2. Ввод команд SQL. 8

1.3. Типы данных. 8

1.3.1. Строки. 9

1.3.2. Числа. 10

1.3.3. Логические типы данных. 12

1.3.4. Дата и время. 13

1.3.5. Интервалы.. 14

1.3.6. Неопределенные значения. 15

1.3.7. Преобразование типов. 16

Лабораторная работа 1. 17

2. ПРОСТАЯ ВЫБОРКА ДАННЫХ.. 18

2.1. Структура оператора SELECT. 18

2.2. Форма оператора SELECT для простой выборки данных. 20

2.3. Сортировка записей. 22

2.4. Выбор интервалов записей. 23

2.5. Условные выражения с оператором CASE. 24

2.5.1. Оператор CASE со значениями. 24

2.5.2. Оператор CASE с условием поиска. 25

2.6. Функция COALESCE. 26

Лабораторная работа 2. 27

3. УТОЧНЕНИЕ ЗАПРОСА.. 28

3.1. Секция WHERE. 28

3.2. Предикаты.. 29

3.2.1. Between. 30

3.2.2. IN и NOT IN.. 30

3.2.3. LIKE и NOT LIKE. 30

3.2.4. SIMILAR.. 31

3.2.5. IS NULL. 31

3.2.6. OVERLAPS. 32

Лабораторная работа 3. 33

4. МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ.. 34

4.1. Выбор источников в секции FROM.. 34

4.2. Операции соединения. 35

4.2.1. Перекрестное соединение. 35

4.2.2. Естественное соединение. 37

4.2.3. Соединение по именам столбцов. 38

4.2.4. Условное соединение. 39

4.2.5. Сложные соединения. 40

Лабораторная работа 4. 41

5. ИТОГОВЫЕ ЗАПРОСЫ.. 43

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

5.2. Группировка записей. 44

5.3. Отбор групп записей. 45

Лабораторная работа 5. 47

6. СЛОЖНЫЕ ЗАПРОСЫ.. 48

6.1. Теоретико-множественные операции. 48

6.1.1. Декартово произведение наборов записей. 48

6.1.2. Объединение наборов записей. 50

6.1.3. Пересечение наборов записей. 51

6.1.4. Вычитание наборов записей. 51

6.2. Внешние соединения. 52

6.2.1. Левое внешнее соединение. 52

6.2.2. Правое внешнее соединение. 53

6.2.3. Полное внешнее соединение. 53

6.3. Подзапросы.. 54

6.3.1. Простые подзапросы.. 54

6.3.2. Связанные подзапросы.. 56

6.4. Представления. 59

Лабораторная работа 6. 61

7. КОМАНДЫ ИЗМЕНЕНИЯ ДАННЫХ.. 62

7.1. Добавление новых записей. 62

7.2. Удаление записей. 63

7.3. Изменение данных. 63

Лабораторная работа 7. 66

8. ОПРЕДЕЛЕНИЕ ДАННЫХ.. 67

8.1. Создание таблиц. 67

8.1.1. Оператор CREATE TABLE. 67

8.1.2. Ограничения для столбцов. 67

8.1.3. Ограничения для таблиц. 68

8.1.4. Внешние ключи. 69

8.2. Удаление таблиц. 70

8.3. Модификация таблиц. 70

8.3.1. Создание полей. 71

8.3.2. Назначение и отмена значений по умолчанию.. 71

8.3.3. Переименование таблицы.. 72

8.3.4. Переименование полей. 72

8.3.5. Добавление ограничений. 72

Лабораторная работа 8. 74

ПРИЛОЖЕНИЯ.. 75

П.1. Возможности PostgreSQL. 75

П.2. Консоль psql 75

П.3. Команды SQL. 76

П.3.1. Перечень команд SQL СУБД PostgreSQL. 76

П.3.2. Форматирование команд SQL. 77

П.3.3. Выполнение запросов. 78

П.3.4. Ключевые слова и идентификаторы.. 79

П.3.5. Комментарии. 80

П. 4. Типы данных PostgreSQL 7.1. 81

П.4.1. Перечень типов данных. 81

П.4.2. Преобразование типов. 84

П.4.3. Константы.. 85

П.5. Операторы.. 87

П.5.1. Правила использования операторов. 87

П.5.2. Строковые операторы.. 88

П.5.3. Числовые операторы.. 89

П.5.4. Двоичные операторы.. 90

П.5.5. Логические операторы.. 91

П.5.6. Операторы и NULL. 91

П.5.7. Приоритет операторов. 92

П.6. Функции. 93

П.6.1. Использование функций. 93

П.6.2. Математические функции. 93

П.6.3. Строковые функции. 94

П.6.4. Функции для работы с датой и временем. 96

П.6.5. Функции преобразования типа. 98

СПИСОК ЛИТЕРАТУРЫ.. 102

 

ВВЕДЕНИЕ В SQL

Что такое SQL

 

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

Запросы выполняются системой управления базой данных (СУБД). Во многих случаях операции с базой данных выполняются с помощью специальных приложений, предоставляющих пользователю удобный интерфейс. Однако доступ к базе данных можно получить и без них – с помощью только SQL. Да и специализированные приложения обычно используют SQL-фрагменты кода при обращении к данным.

Таким образом, SQL – широко распространенный стандарт языка работы с реляционными базами данных. Различных СУБД существует много, а универсальное средство работы с базами данных одно – SQL.

Первые разработки систем управления реляционными базами данных (реляционных СУБД) были выполнены в компании IBM в начале 1970-х годов. Тогда же был создан язык данных, предназначенный для работы в этих системах. Экспериментальная версия этого языка называлась SEQUEL – от англ. Structured English QUEry Language (структурированный английский язык запросов). Однако официальная версия была названа короче – SQL (Structured Query Language).

В 1981 году IBM выпускает реляционную СУБД SQL/DS. К этому времени компания Relation Software Inc. (сегодня это Oracle corporation) уже выпустила свою реляционную СУБД. Эти продукты сразу же стали стандартом систем, предназначенных для управления базами данных. В состав этих продуктов вошел и SQL, который фактически стал стандартом для языков данных. Производители других СУБД выпустили свои версии SQL. В них имелись не только основные возможности продуктов IBM. Чтобы получить некоторое преимущество для «своей» СУБД, производители вводили некоторые расширения SQL. Вместе с тем, начались работы по созданию общепризнанного стандарта SQL.

В 1986 году Американский национальный институт стандартов (American National Standarts Institute, ANSI) выпустил официальный стандарт SQL-86, который в 1989 году был обновлен и получил новое название SQL-89. В 1992 году этот стандарт был назван SQL-92. В настоящее время известен стандарт SQL-2003.

Любая реализация SQL в конкретной СУБД несколько отличается от стандарта. Так, многие СУБД (например, Microsoft Access 2003, PostgreSQL 7.3) поддерживают SQL-92 не в полной мере, а лишь с некоторым уровнем соответствия. Кроме того, они поддерживают и элементы, которые не входят в стандарт. Однако разработчики СУБД стремятся к тому, чтобы новые версии их продуктов как можно в большей степени соответствовали стандарту SQL.

SQL задумывался как простой язык запросов к реляционной базе данных, близкий к естественному (точнее, к английскому). Предполагалось, что близость по форме к естественному языку сделает SQL средством, доступным для широкого применения обычными пользователями баз данных, а не только программистами. Первоначально SQL не содержал никаких управляющих структур, свойственных обычным языкам программирования. Запросы, синтаксис которых довольно прост, вводились прямо с консоли последовательно один за другим и в этой же последовательности выполнялись. Однако SQL так и не стал инструментом банковских служащих, продавцов авиа- и железнодорожных билетов, экономистов и других служащих различных фирм, использующих информацию, хранимую в базах данных. Для них простой SQL оказался слишком сложным и неудобным, несмотря на свою близость к естественному языку вопросов.

На практике с базой данных обычно работают посредством приложений, написанных программистами на процедурных языках, например, на С, Visual Basic, Pascal, Java и др. Часто приложения создаются в специальных средах визуальной разработки, таких как Delphi, Microsoft Access, Visual dBase и т.п. Приложения имеют удобный графический интерфейс, не вынуждающий пользователя непосредственно вводить запросы на языке SQL. Вместо него это делает приложение.

Реляционные базы данных могут и действительно существуют вне зависимости от приложений, обеспечивающих пользовательский интерфейс. Если по каким-либо причинам такого интерфейса нет, то доступ к базе данных можно осуществить с помощью SQL, используя консоль или какое-нибудь приложение, с помощью которого можно соединиться с базой данных, ввести и отправить SQL-запрос (например, Borland SQL Explorer).

Язык SQL считают декларативным (описательным) языком, в отличие от языков, на которых пишутся программы. Это означает, что выражения на языке SQL описывают, что требуется сделать, а не каким образом. Нет необходимости подробно описывать действия, которые должна выполнить СУБД, чтобы выбрать из таблицы указанные в запросе данные. Надо просто описать, что хочется получить. В результате выполнения запроса СУБД возвращает таблицу, содержащую запрошенные данные. Если в базе данных не оказалось данных, соответствующих запросу, то будет возвращена пустая таблица.

Однако последние версии SQL поддерживают операторы управления вычислениями, свойственные процедурным языкам (операторы условного перехода и цикла). Поэтому SQL сейчас это не чисто декларативный язык.

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

· DML (Data Manipulation Language – язык манипулирования данными) предназначен для поддержки базы данных: выбора (SELECT), добавления (INSERT), изменения (UPDATE) и удаления (DELETE) данных из таблиц;

· DDL (Data Definition Language – язык определения данных) предназначен для создания, модификации и удаления таблиц и всей базы данных. Примерами операторов, входящих в DDL, являются CREATE TABLE (создать таблицу), CREATE VIEW (создать представление), ALTER TABLE (изменить таблицу), DROP (удалить) и др.;

· DCL (Data Control Language – язык управления данными) предназначен для обеспечения защиты базы данных от различного рода повреждений.

 

Ввод команд SQL

 

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

Все ключевые слова SQL (команды, операторы и проч.) являются зарезервированными и не должны использоваться в качестве имен таблиц, столбцов, переменных и т.п.

Подробнее о правилах записи команд SQL, принятых в СУБД PostgreSQL, см. в приложении (П.3. Команды SQL).

 

Типы данных

 

Различные СУБД поддерживают несколько отличающиеся наборы типов данных для столбцов базы данных. Аналогичная ситуация и с типами данных, поддерживаемых различными версиями и реализациями SQL. Типы данных, используемые в PostgreSQL, приведены в приложении (П.4. Типы данных PostgreSQL 7.1.). Вместе с тем, всегда имеются типы данных, которые поддерживаются всеми реализациями SQL. В спецификации SQL:2003 признаны пять предопределенных общих типов, внутри которых могут быть подтипы:

строковый (символьный):

§ character (или char);

§ character varying (или varchar);

§ character large object (или clob);

числовой:

§ точные числовые типы:

1 integer;

2 SMALLINT;

3 BIGINT;

4 NUMERIC;

5 DECIMAL;

§ приблизительные числовые типы:

1 REAL;

2 DOUBLE PRECISION;

3 FLOAT;

логический (булевский) – BOOLEAN;

даты-времени:

§ DATE;

§ TIME WITHOUT TIME ZONE;

§ TIME WITH TIME ZONE;

§ TIMESTAMP WITHOUT TIME ZONE;

§ TIMESTAMP WITH TIME ZONE;

интервальный.

 

Строки

 

Строковые данные (последовательности символов) имеют три главных строковых типа. Для столбца таблицы можно указать тип CHARACTER (n) или CHAR (n) (строка фиксированной длины), где n – максимальное количество символов, содержащихся в строке. Если (n) не указано, то предполагается, что строка состоит из одного символа. Если в столбец типа CHARACTER (n) вводится m < n символов, то оставшиеся позиции заполняются пробелами.

Тип данных CHARACTER VARYING (n) или VARCHAR (n) (строка переменной длины) применяется тогда, когда вводимые данные имеют различную длину и нежелательно дополнять их пробелами. При этом сохраняется только то количество символов, которое ввел пользователь. В данном случае указание максимального количества символов обязательно (в отличие от CHARACTER).

Данные типов CHARACTER и CHARACTER VARYING могут участвовать в одних и тех же строковых операциях.

Тип данных CHARACTER LARGE OBJECT (CLOB – большой символьный объект) используется для представления очень больших символьных строк (например, статей, книг и т.п.). В некоторых СУБД данный тип называется MEMO, а в других – TEXT. С данными этого типа можно выполнять не все операции, предусмотренные для типов CHARACTER и CHARACTER VARYING. Так, их нельзя использовать в операциях сравнения, за исключением равенства и неравенства. Кроме того, столбцы этого типа не могут быть первичными и внешними ключами, а также быть объявлены как имеющие уникальные значения.

Значения строкового типа в SQL-выражениях заключаются в одинарные кавычки. Например, ‘Иванов Иван Иванович’, ‘12345 рублей’, ‘тел. (812) 123-4567’. Пустая строка не содержит ни одного символа и имеет вид: ''. Строка, содержащая один или более пробелов, не является пустой.

 

Числа

 

Числовой тип данных может быть двух видов – точный и приблизительный. Точные числовые типы позволяют точно выразить значение числа. Некоторые величины имеют очень большой диапазон значений, и в таких случаях достаточно ограничиться некоторым приближенным их представлением с учетом технических возможностей компьютера (размеров регистра).

К точным числовым относятся следующие пять типов:

· INTEGER – целое (без дробной части) число. Количество разрядов (точность) зависит от реализации SQL. В некоторых реализациях числа этого типа лежат в диапазоне от –2147483648 до 2147483647 (четырехбайтное целое число);

· SMALLINT – малое целое число. Количество разрядов зависит от реализации SQL, но не больше количества разрядов INTEGER в этой же реализации. В некоторых реализациях числа этого типа лежат в диапазоне от –32768 до 32767 (двухбайтное целое число);

· BIGINT – большое целое число. Количество разрядов зависит от реализации SQL и превышает количество разрядов числа типа INTEGER;

· NUMERIC (x, y) – число, в котором всего x разрядов (точность), из которых y разрядов (масштаб) отводится для дробной части. Если y не указано (NUMERIC (x)), то для дробной части отводится количество разрядов, установленное в системе по умолчанию. Если не указаны ни x, ни y (NUMERIC), то принимаются обе эти величины, установленные по умолчанию. Например, если указан тип NUMERIC (6, 2), то максимальное значение числа равно 9999.99;

· DECIMAL (x, y) – десятичное число, в котором всего x разрядов, из которых y разрядов отводятся для дробной части. Если x или/и y не указаны, то принимаются значения по умолчанию. Этот тип очень похож на NUMERIC. Отличие состоит в том, что если в DECIMAL (x, y) указанные x и y меньше, чем допустимые реализацией SQL, то будут использоваться последние. Если x и y не указаны, по применяется система умолчаний. Например, для столбца задан тип DECIMAL (6, 2). Если реализация SQL позволяет, то в этот столбец можно ввести числа, превышающие 9999.99. В отличие от DECIMAL (x, y), тип NUMERIC (x, y) жестко задает диапазон возможных значений числовой величины.

К приблизительным числовым типам относятся следующие три типа:

· REAL – вещественное число одинарной точности с плавающей разделительной точкой (эта точка «плавает», появляясь в различных местах числа). Например, 5.25, 5.257, 5.2573. Точность представления числа зависит от реализации SQL и оборудования. Например, 32-битовый компьютер дает большую точность, чем 16-битовый;

· DOUBLE PRECISION – вещественное число двойной точности с плавающей разделительной точкой. Точность представления зависит от реализации SQL и оборудования. Применяется для представления научных данных (например, результатов измерений) в широком диапазоне значений, т.е. как очень малых (близких к 0), так и очень больших;

· FLOAT (x) – вещественное число с плавающей разделительной точкой и минимальной точностью x, занимающее не более 8 байтов. Если компьютер может поддерживать указанную точность, используя аппаратную одинарную точность, то система будет использовать арифметику одинарной точности. Если указанная точность требует арифметики с двойной точностью, то система будет использовать ее. Данный тип следует применять, если предполагается возможность переноса базы данных на другую аппаратную платформу, отличающуюся размерами регистров. Пример значения FLOAT: 5.318E-24 (т.е. 5.318, умноженное на 10 в степени -24). Такую же форму представления имеют и числа типа REAL и DOUBLE PRECISION.

При создании таблиц целочисленные типы применяются для столбцов, содержащих разного рода идентификаторы, например, номера (коды) клиентов, товаров, заказов и т.п. Разумеется, если содержимое столбца должно быть целым числом (например, количество ящиков, бутылок, штук и т.п.), то тип этого столбца естественно определить как INTEGER, SMALLINT или BIGINT.

Пусть в таблице Клиенты имеется столбец ID_Клиента, содержащий уникальные идентификаторы клиентов. Если количество клиентов не превышает 32000, то тип столбца можно определить как SMALLINT. Если в таблице будут храниться сведения о сотнях тысяч клиентов, то тип столбца ID_Клиента следует определить как INTEGER.

Если столбец в проектируемой таблице должен содержать числа с дробной частью, то для него можно задать какой-нибудь нецелочисленный тип. При отсутствии уверенности, что применить: точные числовые типы или приблизительные, следует выбирать точные (NUMERIC, DECIMAL). Они требуют меньше ресурсов и дают точные результаты. Если в столбце предполагается хранить данные из очень широкого диапазона (и очень малые, и очень большие числа), то следует использовать приблизительные типы данных (FLOAT, REAL).

 

Логические типы данных

 

В этой части математической логики, основоположником которой был английский математик Джон Буль, данные имеют только два значения – ИСТИНА и ЛОЖЬ, обозначаемые как true и false соответственно. Данные логического типа получаются в результате операций сравнения. Например, результатом вычисления выражения 3 < 5 является ИСТИНА, а выражение 2 + 3 = 10 – ЛОЖЬ.

В SQL тип данных BOOLEAN (булевский) имеет три значения – true, false и unknown. Значение unknown (неизвестное) было введено для обозначения результата, получающегося при сравнении со значением NULL (неопределенное). Если пользователь еще не ввел в ячейку таблицы никакого значения, то эта «пустая» ячейка содержит значение NULL, интерпретируемое как неизвестное или неопределенное значение.

Результатом любой операции сравнения true или false с NULL или с unknown всегда является unknown.

В SQL-выражениях логические значения заключаются в кавычки, например, ‘TRUE’ или ‘true’.

 

Дата и время

 

Тип DATE (дата) предназначен для хранения значений даты, элементы которых расположены в следующем порядке: год (4 цифры), дефис (-), месяц (2 цифры), дефис, день (2 цифры). Таким образом, значения даты занимают 10 позиций, например, 2005-10-02.

Данные этого типа могут содержать любую дату с 0001 года по 9999 год.

Для представления времени предусмотрены два типа:

· TIME WITHOUT TIME ZONE (время без часового пояса) предназначен для хранения значений времени, элементы которых расположены в следующем порядке: часы, двоеточие, минуты, двоеточие, секунды. Часы и минуты представляются двумя цифрами, а секунды могут быть представлены двумя и более цифрами (если требуется дробная часть), например 18:35:19.547. Длина дробной части секунд зависит от реализации, но внутреннее представление времени должно иметь не менее 6 цифр. По умолчанию время данного типа представляют без дробной части секунд. Чтобы указать, что время должно быть представлено с n цифрами после разделительной точки, достаточно использовать такой синтаксис: TIME WITHOUT TIME ZONE (n). Например, чтобы кроме секунд указывались еще и миллисекунды, следует определить тип как TIME WITHOUT TIME ZONE (3). Длина данных рассматриваемого типа без дробной части равна 8 символам, а с дробной частью – 9 плюс количество цифр после разделительной точки. Для задания времени без указания часового пояса с использованием установок по умолчанию можно использовать короткий синтаксис – TIME;

· TIME WITH TIMEZONE (время с часовым поясом) – такой же тип данных, как и TIME WITHOUT TIME ZONE. Отличие заключается лишь в том, что к значению времени добавляется еще и информация о разности между местным и всемирным временем. Всемирное время (Universal Time Coordinated, UTC) – это время по Гринвичу, т.е. время нулевого меридиана, проходящего через г. Гринвич в Великобритании (Greenwich Mean Time, GMT). Значение разности между локальным и всемирным временем находится в диапазоне от –12:59 до 13:00. Длина данных рассматриваемого типа равна длине данных типа TIME WITHOUT TIME ZONE плюс 6, поскольку дополнительная информация о разности времен занимает 6 позиций (дефис, знак (+) или (-), 2 цифры для часов, двоеточие, 2 цифры для минут).

Для одновременного представления даты и времени служат следующие два типа:

· TIMESTAMP WITHOUT TIME ZONE (дата и время без часового пояса). Элементы данных этого типа имеют такие же характеристики, как и для данных типа DATE и TIME WITHOUT TIME ZONE, за исключением одного: данные типа TIMESTAMP WITHOUT TIME ZONE по умолчанию имеют 6 цифр в дробной части секунд, а не 0, как в типе TIME WITHOUT TIME ZONE. Для указания количества цифр в дробной части используется синтаксис TIMESTAMP WITHOUT TIME ZONE (n). Если дробной части нет, то данные занимают 19 позиций: 10 позиций для даты, один пробел и 8 позиций для времени. Если определена дробная часть, то длина данных равна 20 плюс количество цифр в дробной части секунд;

· TIMESTAMP WITH TIME ZONE (дата и время с часовым поясом) – такой же тип данных, как и TIMESTAMP WITHOUT TIME ZONE. Отличие состоит в том, что к значению времени добавляется еще и информация о разности между местным и всемирным временем. Дополнительная информация занимает 6 позиций. Данные типа TIMESTAMP WITH TIME ZONE без дробной части занимают 25 позиций, с дробной частью – 26 плюс количество цифр в дробной части секунд.

 

Интервалы

 

Интервал представляет собой разность между двумя значениями типа дата-время.

Интервал времени можно задать двумя способами: в виде начального и конечного моментов или в виде начального момента и длительности, например:

· (TIME '12:25:30', TIME '14:30:00') – интервал, заданный начальным и конечным моментами;

· (TIME '12:45:00', INTERVAL '5' HOUR) – интервал, заданный начальным моментом и длительностью в часах.

Чтобы задать значение типа интервал, используется такой синтаксис:

INTERVAL 'длина' YEAR | MONTH | DAY | HOUR | MINUTE | SECOND

Здесь длина – длина интервала, после которой указывается единица измерения (возможные значения указаны через вертикальную черту):

· YEAR – год;

· MONTH – месяц;

· DAY – день;

· HOUR – час;

· MINUTE – минута;

· SECOND – секунда.

Например, для задания интервала длиной 15 дней следует использовать выражение INTERVAL '15' DAY.

 

Неопределенные значения

 

Если в ячейку (поле) таблицы вводятся какие-то значения (буквы, цифры, пробелы или еще что-нибудь), то эти ячейки приобретают так называемые определенные значения. С определенными значениями, принадлежащими тому или иному типу можно выполнять какие-то операции. Однако возможно создать запись (строку) таблицы, ничего не вводя в ее поля (столбцы). Такая запись ничего не содержит. Запись в таблице, которая создана, но в которую не введены конкретные значения, называется пустой. В каждой ячейке пустой записи содержится так называемое неопределенное значение, обозначаемое через NULL. Значение NULL понимается как неопределенное или неизвестное.

Каждый раз при добавлении в таблицу новой записи без ввода в нее каких-либо конкретных значений, СУБД устанавливает в ее полях значение NULL.

Значение NULL можно интерпретировать так, как хочется, например, "еще не введено", "пока не известно".

Важно понимать, что число 0 или пустая строка '' являются вполне определенными значениями и отличаются от NULL, хотя визуально пустая строка и неопределенное значение могут отображаться одинаково – в виде пустой ячейки таблицы. Многие функции SQL возвращают в зависимости от обстоятельств какое-то определенное значение либо NULL. Это необходимо учитывать при составлении SQL-выражений, поскольку комбинация NULL и других вполне конкретных значений может дать тот или иной результат в зависимости от конкретной операции.

Таблица базы данных может иметь некоторые записи, имеющие неопределенные значения. Такие значения появляются по различным причинам, однако при этом могут возникать неоднозначные ситуации при извлечении данных. Чтобы их избежать, необходимо помнить, что при сравнении любого определенного значения с NULL результат равен false, т.е. любое определенное значение не равно NULL. То же самое происходит при сравнении двух величин, значениями которых являются NULL, т.е. сравнение двух неопределенных величин в результате дает false.

При проектировании баз данных можно указать, что некоторые столбцы не могут иметь значение NULL, т.е их значения обязательно должны быть определены (при определении столбца в операторе CREATE TABLE указывается NOT NULL). Однако во многих случаях значения NULL являются очень полезными. Так, например, возможно создать таблицу и ввести в нее сведения, которые имеются в данный момент. Затем, по мере поступления новой информации, можно изменить значения NULL на те, которые стали известны.

 

1.3.7. Преобразование типов

 

Чтобы выполнить какую-либо операцию над данными различных типов, необходимо сделать преобразование типов. Точнее, необходимо выполнить приведение данных одного типа к другому типу, чтобы участвующие в операции данные были либо однотипными, либо их типы были соответствующими.

Соответствующими типами являются:

· строковые CHARACTER и CHARACTER VARYING;

· все числовые типы;

· дата, время, дата-время и соответствующие интервалы.

Соответствующие типы не обязательно приводить друг к другу.

Приведение значения одного типа к другому осуществляется с помощью функции CAST():

CAST (выражение AS тип);

Например:

CAST ('1234.52' AS NUMERIC (9, 2));

CAST ('2005-10-03' AS DATE);

CAST (CURRENT_TIMESTAMP (2) AS CHAR (20));

 

 

Лабораторная работа 1

 

1. Соединиться с базой данных booktown на сервере баз данных server.

2. Просмотреть список имеющихся на сервере баз данных.

3. Просмотреть список зарегистрированных пользователей слэш-командой \du и командами языка SQL:

· SELECT *

FROM pg_user;

· select usename, usesysid

from pg_user;

4. Просмотреть список групп пользователей

select *

from pg_group;

5. Просмотреть список таблиц базы данных booktown.

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

SELECT *

FROM имя_таблицы;

Рабочие таблицы:

· authors,

· books,

· customers,

· editions,

· publishers,

· shipments,

· stock,

· subjects.

 

 

ПРОСТАЯ ВЫБОРКА ДАННЫХ

 

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

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

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

 

Структура оператора SELECT

 

Основное SQL-выражение для выборки данных имеет вид:

SELECT списокСтолбцов FROM таблица;

Такой запрос возвращает таблицу, полученную из указанной в операторе FROM путем выделения в ней только тех столбцов, которые определены в операторе SELECT. Для выделения требуемых записей (строк) исходной таблицы используется выражение, следующее за ключевым словом (оператором) WHERE. Оператор WHERE является наиболее часто используемым, хотя и необязательным в SQL-выражениях. Кроме WHERE, в SQL-выражениях используются и другие операторы, позволяющие уточнить запрос.

Для уточнения запроса на выборку данных служит ряд дополнительных операторов:

· WHERE (где) – указывает записи, которые должны войти в результатную таблицу (фильтр записей);

· GROUP BY (группировать по) – группирует записи по значениям определенных столбцов;

· HAVING (имеющие, при условии) – указывает группы записей, которые должны войти в результатную таблицу (фильтр групп);

· ORDER BY (сортировать по) – сортирует (упорядочивает) записи.

Эти операторы не являются обязательными. Их можно совсем не использовать, или использовать лишь некоторые из них, или все сразу. Если применяются несколько операторов, то в SQL-выражении они используются в указанном в списке порядке.

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

SELECT списокСтолбцов

FROM имяТаблиы

WHERE условиеПоиска

GROUP BY столбецГруппировки

HAVING условиеПоиска

ORDER BY условиеСортировки;

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

1. FROM – выбирает таблицу из базы данных; если указано несколько таблиц, то выполняется их декартово произведение, и результирующая таблица передается для обработки следующему оператору.

2. WHERE  – из таблицы выбираются записи, отвечающие условию поиска, и отбрасываются все остальные.

3. GROUP BY – создаются группы записей, отобранных с помощью оператора WHERE (если он присутствует в SQL-выражении); каждая группа соответствует какому-нибудь значению столбца группирования. Столбец группирования может быть любым столбцом таблицы, заданной в операторе FROM, а не только тем, который указан в SELECT.

4. HAVING – обрабатывает каждую из созданных групп записей, оставляя только те из них, которые удовлетворяют условию поиска; этот оператор используется только вместе с оператором GROUP BY.

5. SELECT – выбирает из таблицы, полученной в результате применения перечисленных операторов, только указанные столбцы.

6. ORDER BY – сортирует записи таблицы.

 

Форма оператора SELECT для простой выборки данных

 

Тривиальный запрос, возвращающий все данные (все столбцы и все записи) из одной таблицы, формулируется так:

            SELECT *

            FROM имяТаблицы;

Например, получим все сведения обо всех авторах:

SELECT *

FROM authors;

 

Может потребоваться выбрать не все столбцы таблицы, а только некоторые. Тогда SQL-запрос будет выглядеть так:

            SELECT списокСтолбцов

            FROM имяТаблицы;

Список столбцов – это перечень имен столбцов, разделенных запятой, как они определены в таблице, указанной в выражении FROM. Можно указать все или только некоторые столбцы в любом порядке. Возможно также указать один и тот же столбец несколько раз.

Например, получим фамилии и имена всех авторов:

SELECT last_name, first_name

FROM authors;

Сразу за оператором SELECT до списка столбцов можно применять ключевые слова ALL (все) и DISTINCT (отличающиеся), которые указывают, какие записи представить в результатной таблице. Если эти ключевые слова не используются, то подразумевается, что следует выбрать все записи, что также соответствует применению ключевого слова ALL. В случае использования DISTINCT в результатной таблице представляются только уникальные записи. При этом если в исходной таблице находятся несколько идентичных записей, то из них выбирается только первая.

Например, получим все имена, которые встречаются среди авторов:

SELECT DISTINCT first_name

FROM authors;

Существует и еще одна форма оператора DISTINCT с явным перечислением полей, проверяемых на наличие дубликатов. В этом случае за словом DISTINCT следует секция ON (списокСтолбцов).

Например, получим имена, которые встречаются среди авторов, с указанием одной из фамилий, соответствующих каждому имени:

SELECT DISTINCT ON (first_name)

first_name, last_name

FROM authors;

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

AS заголовокСтолбца

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

Например,

SELECT last_name AS Фамилия, first_name AS Имя

FROM authors;

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

Например,

SELECT *

FROM authors a;

или

SELECT *

FROM authors As a;

В списке столбцов оператора SELECT могут быть не только простые поля, но и произвольные выражения (включающие вызовы функций или различные операции с идентификаторами) и константы.

Например, получим в одном столбце и фамилию, и имя автора, используя оператор конкатенации (слияния) строк:

SELECT last_name || ' ' || first_name AS name

FROM authors;

Команда SELECT также может использоваться для простого вычисления и вывода результатов выражений и констант. В этом случае она не содержит секции FROM.

Например,

SELECT 2+2 AS "2 plus 2",

pi() AS "the pi function",

'PostgreSQL is more than a calculator!' AS   comment;

Перечень операторов и функций, доступных в СУБД PostgreSQL, см. в приложении (П.5. Операторы, П.6. Функции).

 

Сортировка записей

 

Записи хранятся в таблицах в произвольном порядке. Более того, даже повторное выполнение запроса никоим образом не гарантирует одинакового порядка следования возвращаемых записей. Однако упорядочение данных играет важную роль при выборке, поэтому в SQL поддерживается конструкция ORDER BY, являющаяся гибким средством сортировки итогового набора.

Секции ORDER BY передается список полей, разделенный запятыми (или выражений, в которых используются поля). Переданный список задает критерий сортировки. Для каждого критерия сортировки могут дополнительно указываться ключевые слова ASC и DESC, управляющие типом сортировки.

· ASC. Записи сортируются по возрастанию заданного критерия (то есть числа сортируются от меньших к большим, даты – от ранних к поздним, а текст – по алфавиту). По умолчанию выбирается именно этот способ сортировки, поэтому ASC используется лишь для наглядности.

· DESC. Записи сортируются по убыванию заданного критерия (то есть числа сортируются от больших к меньшим, даты – от поздних к ранним, а текст – в порядке, обратном алфавитному).

Например, получим фамилии и имена всех авторов с сортировкой по фамилии:

SELECT last_name, first_name

FROM authors

ORDER BY last_name;

При сортировке допускается использование полей, отсутствующих в целевом списке оператора SELECT. Более того, если запрос связан с агрегированием, секция ORDER BY может содержать вызовы агрегатных функций и выражения.

Например, получим фамилии и имена всех авторов с сортировкой по идентификационному номеру:

SELECT last_name, first_name

FROM authors

ORDER BY id;

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

Например, получим фамилии и имена всех авторов с сортировкой по фамилии, а если фамилии совпадают, то и по именам:

SELECT last_name, first_name

FROM authors

ORDER BY last_name, first_name;

При сортировке по нескольким критериям для каждого критерия можно задать свой тип упорядочивания.

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

SELECT edition, publication

FROM editions

ORDER BY edition ASC, publication DESC;

Секция ORDER BY обрабатывается перед удалением дубликатов ключевым словом DISTINCT. Этот факт позволяет с помощью сортировки составить запрос, аналогичный по результату использованию агрегатных функций max() или min().

Например, получим даты последних изданий для каждого номера издания:

SELECT DISTINCT ON (edition) edition, publication

FROM editions

ORDER BY edition ASC, publication DESC;

 

Выбор интервалов записей

 

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

В SQL предусмотрены ключевые слова LIMIT и OFFSET, упрощающие выборку заданной части итогового набора.

Секция LIMIT ограничивает максимальное количество записей в итоговом наборе (хотя размер итогового набора может быть меньше заданной величины).

Например, получим последние пять номеров isbn книг, которые были изданы:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

LIMIT 5;

При наличии секции OFFSET в итоговом наборе пропускается количество записей, заданное параметром секции.

Например, получим isbn и даты изданий всех книг, кроме последних пяти изданных:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

OFFSET 5;

Если заданы оба ключевых слова, и LIMIT, и OFFSET, то отсчет ограничения, указанного в секции LIMIT, начинается после пропуска записей в соответствии с секцией OFFSET.

Например, получим предпоследние (т.е. не учитывая самую последнюю книгу) пять номеров isbn книг, которые были изданы:

SELECT isbn, publication

FROM editions

ORDER BY publication DESC

LIMIT 5

OFFSET 1;