Соединение таблицы со своей копией 


Мы поможем в написании ваших работ!



ЗНАЕТЕ ЛИ ВЫ?

Соединение таблицы со своей копией



Теоретические сведения

Синтаксис инструкции Select

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

Инструкция SELECT используется в основном как:

- самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений);

- элемент другого запроса, называемый «вложенный запрос»;

- фраза выбора в командах создания представления, курсора или вставки;

- средство присвоения переменным значений из строк сформированной таблицы.

 

Инструкция SELECT в упрощенном виде имеет следующий формат:

SELECT [[ALL] | DISTINCT] { * | элемент [[AS] [псевдоним_столбца]

[,элемент [AS] [псевдоним_столбца]]...}

FROM {базовая_таблица | представление} [AS] [псевдоним]

[,{базовая_таблица | представление} [AS] [псевдоним]]...

[WHERE условие_where]

[GROUP BY список_ group_by

[HAVING условие_having]]

[ORDER BY список_order_by]

[ FOR UPDATE [ OF таблица [....]]] [ LIMIT { число | ALL } [ { OFFSET |. } начало ] ]

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

SELECT (выбрать) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями;

FROM (из) перечисленных таблиц, в которых расположены эти столбцы;

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

GROUP BY (группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя в списке элементов SELECT агрегирующие SQL-функции, например: SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение);

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

ORDER BY (сортируя) результаты выбора данных; при этом упорядочение можно производить в порядке возрастания — ASC (ASCending) или убывания —DESC (DESCending), по умолчанию принимается ASC;

 

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

Элемент_SELECT::= [таблица.]* | выражение

выражение::= константа
| скалярная_функция
| [псевдоним.]столбец
| (выражение)
| (скалярный_подзапрос)
| {унарный оператор} выражение
| выражение {бинарный_оператор} выражение

где:

константа - одиночные алфавитно-цифровые символы, строки символов и числовые константы (символьные данные и данные типа дата/время берутся в одинарные кавычки);

скалярная_функция - SQL-функция, возвращающая скалярное значение, например SUM, CAST и др.(выражения, выступающие в качестве параметров SQL-функций, не должны содержать вложенных SQL-функций)

базовая_скалярная_функция::= | {SUM|AVG|MIN|MAX|COUNT} ([[ALL]|DISTINCT][таблица.]столбец)| {SUM|AVG|MIN|MAX|COUNT} ([ALL] выражение)| COUNT(*)

псевдоним - временный идентификатор, присваиваемый таблице с помощью служебного слова AS в предложении FROM;

столбец - собственно имя столбца, но не четырехсоставное имя, состоящее из имени базы данных, владельца, таблицы и имени столбца;

скалярный_подзапрос - подзапрос, возвращающий скалярный результат; подзапрос - это инструкция SELECT, вложенная в:

- предложения WHERE, HAVING или SELECT другой инструкции SELECT;

- инструкции INSERT, UPDATE и DELETE, предназначенные для модификации данных в таблицах;

- другой подзапрос.

Предложение WHERE включает набор условий для отбора строк:

условие_where::= [NOT] условие [[AND|OR][NOT] условие]...

условие::= выражение { = | <> | < | <= | > | >= } { выражение | (подзапрос) }

выражение_1 [NOT] BETWEEN выражение _2 AND выражение _3

выражение [NOT] IN { (константа [,константа]...) | (подзапрос) }

выражение IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS (подзапрос)

Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в предложении WHERE используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (проверка на существование данных, удовлетворяющих критериям подзапроса; если данных нет, то результат – ложь), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами AND, OR, NOT (с учетом их приоритета и скобок).

При обработке условия числа сравниваются алгебраически — отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины. Строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII. Если сравниваются две строки символов, имеющих разные длины, более короткая строка дополняется справа пробелами для того, чтобы они имели одинаковую длину перед осуществлением сравнения.

Синтаксис модификатора GROUP BY имеет вид

GROUP BY [таблица.]столбец [,[таблица.]столбец]... [HAVING условие_having]

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

С помощью предложения HAVING, где условие_having определяется так же, как и условие_where, можно исключить из результата группы, не удовлетворяющие заданным условиям.

Список параметров модификатора ORDER BY имеет вид

список_order_by::=

{[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]

[, {[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]] …

В отличие от GROUP BY модификатор ORDER BY не удаляет повторяющиеся значения.

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

SQL_функция [, SQL_функция] … [BY столбец [,столбец]…]

 

2.2 Преобразование типов данных в инструкции SELECT.

В PostgreSQL существует четыре фундаментальные SQL-конструкции, требующие четких правил преобразования типов: вызовы функций, применение операторов, присваивание значений при вставке и модифицировании данных, применение конструкций UNION (объединение результатов запросов) и CASE (аналог оператора if..then..else).

В PostgreSQL поддерживаются три варианта синтаксиса явного преобразования (приведения) типов:

· для строковых констант

тип 'значение'

'значение'::тип

CAST ('значение' AS тип)

· для числовых констант

значение:: тип

CAST (значение AS тип)

· для полей набора данных, возвращаемых запросом SQL

идентификатор:: тип

CAST (идентификатор AS тип)

Пример:

SELECT ("Остаток"+10)::float * 1.1,

"Цена"::char(8) || char(4) ' UAH', --соединение двух строк

CAST("Цена" AS text)

FROM "Книги"

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

SELECT 'От ' || '01/01/2011' || ' до ' || CAST(NOW() AS varchar(64)) || ' прошло ' || (CAST (now() AS varchar(64)):: date - date '01/01/2011'):: text || ' дней'

Возможно также использование преобразования типов в предложении WHERE. Следующий запрос возвратит данные об январских поставках в базе данных BookShop:

SELECT *

FROM "Поставки"

WHERE CAST("Дата" AS varchar) LIKE '%2011-01-%'

Тот же результат получается с помощью стандартной функции EXTRACT:

SELECT *

FROM "Поставки"

WHERE EXTRACT (MONTH FROM "Дата") = 1 AND EXTRACT (YEAR FROM "Дата") = 2011

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

 

Вложенные подзапросы

Вложенный подзапрос - это подзапрос, заключенный в круглые скобки и вложенный в предложение WHERE (HAVING) инструкции SELECT или других инструкций, использующих предложение WHERE. Вложенный подзапрос может содержать в своем предложении WHERE (HAVING) другой вложенный подзапрос и т.д. Назначение вложенных подзапросов состоит в том, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других.

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

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

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

Подзапросы включаются в предложение WHERE (HAVING) с помощью ключевых слов IN, EXISTS или одного из условий сравнения (= | <> | < | <= | > | >=):

подзапросы, не возвращающие ни одного или возвращающие несколько значений, начинаются с IN или операторов сравнения и содержат ключевые слова ANY или ALL;

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

подзапросы, представляющие собой тест на существование или присутствие данных, начинаются с EXISTS.

Подзапросы c ANY и ALL

ANY и ALL отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении они подобны оператору IN, однако, в отличие от IN, они могут использоваться только с подзапросами.

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

SELECT *

FROM ”Заказчики”

WHERE “Код_заказчика” = ANY (SELECT “Код_заказчика” FROM “Заказы”)

равнозначен запросу

SELECT *

FROM Заказчики

WHERE Код_заказчика IN (SELECT Код_заказчика FROM Заказы);

Отличие заключается в том, что оператор ANY может использовать другие операторы сравнения, а не только оператор «=» как в случае IN. Например, если в предыдущем запросе в предложении WHERE заменить оператор «=» на оператор «<=», то получим информацию о заказчике с минимальным значением в поле Код_заказчика, поскольку это – единственное значение, для которого условие основного запроса выполняется.

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

В SQL сказать, что значение больше(или меньше), чем любое(ANY) из набора значений, - тоже самое что сказать, что оно больше(или меньше) чем любое одно отдельное из этих значений. И наоборот, сказать, что значение не равно всему (ALL) набору значений, равносильно тому, что нет такого значения в наборе которому оно было бы равно.

Любой запрос, который может быть сформулирован с ANY или с ALL, может быть также сформулирован с EXISTS, хотя обратное в общем случае неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в том, как обрабатываются NULL-значения. Всякий раз, когда допустимый подзапрос возвращает пустое множество, предикат с ALL автоматически верен, а с ANY – автоматически неверен.

Ход работы.

3.1 Задания для самостоятельной работы (БД BookShop)

1. Напишите запрос для вывода названия, автора и цены книг из таблицы Книги.

SELECT name, author, price FROM "Book"

 

2. Напишите запрос для вывода всех уникальных имен авторов книг в текущем порядке из таблицы Книги.

SELECT DISTINCT author FROM "Book" ORDER BY author

 

3. Напишите запрос для вывода всех заказов из таблицы Заказы со значениями суммы оплаты выше 1,000 грн.

SELECT zakaz.*

FROM zakaz, "Book"

WHERE zakaz.num_book="Book".num_book AND "Book".price>1000

 

4. Напишите запрос к таблице Заказы для вывода всех оплаченных заказов, соответствующих заказчику с именем «Иванов И.».

SELECT zakaz.*

FROM zakaz, client

WHERE zakaz.num_client=client.num_client

AND zakaz.pay='Да'

AND client.name=' Иванов И.'

 

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

SELECT "Book".num_book,"Book".price, postavchik.num_suppl

FROM "Book", postavchik, postavka

WHERE postavka.num_book="Book".num_book

AND postavka.num_postavchik=postavchik.num_suppl

AND postavchik.city='Киев'

 

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

SELECT * FROM client WHERE LEFT(name,1) BETWEEN 'А' AND 'М'

 

 

7. Напишите запрос для выборки всех книг, в названии которых есть словосочетание «база данных» или «базы данных» или «баз данных».

SELECT * FROM "Book"

WHERE name LIKE '%база данных%'

OR name LIKE '%базы данных%'

OR name LIKE '%баз данных%'

 

8. Напишите запрос для подсчета суммы всех заказов на 31 января 2014 года.

SELECT SUM("Book".price) FROM "Book", zakaz

WHERE "Book".num_book=zakaz.num_book

AND zakaz.data='2014-01-31'

 

9. Напишите запрос для подсчета общей суммы заказов за каждый день с 01.02.14 по 01.04.14.

SELECT zakaz.data, SUM("Book".price) FROM "Book", zakaz

WHERE "Book".num_book=zakaz.num_book

AND zakaz.data BETWEEN '2014-02-01' AND '2014-04-01'

GROUP BY zakaz.data

 

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

SELECT "Book".author, COUNT(zakaz.*) as cnt

FROM "Book", zakaz

WHERE "Book".num_book=zakaz.num_book

AND zakaz.data BETWEEN '2014-02-01' AND '2014-04-01'

GROUP BY "Book".author

ORDER BY cnt DESC

 

3.2 Задания для самостоятельной работы (БД Komunal)

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

SELECT klient.full_name, SUM(schet.dolg) AS zadolj

FROM klient, schet

WHERE schet.id_klient=klient.id

GROUP BY klient.id

HAVING SUM(schet.dolg)>0

 

 

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

SELECT usluga.name, usluga.price, AVG(schet.volume) AS sred_potreb

FROM usluga, schet

WHERE schet.id_usluga=usluga.id

GROUP BY usluga.id

 

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

SELECT klient.full_name, usluga.name, schet.volume,

(SELECT AVG(volume) FROM schet

WHERE schet.id_klient=klient.id AND schet.id_usluga=usluga.id) AS sred

FROM klient, schet, usluga

WHERE klient.id=schet.id_klient

AND usluga.id=schet.id_usluga

AND schet.mes=date_part('month', now()-interval '1 month')

AND schet.god=date_part('year', now()-interval '1 month')

AND volume > (SELECT AVG(volume) FROM schet

WHERE schet.id_klient=klient.id AND schet.id_usluga=usluga.id)

 

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

а) сумма оплаты по каждому клиенту, проживающему по адресу "Щорса, 12":

SELECT klient.full_name, SUM(schet.oplata) as k_oplate

FROM klient, schet

WHERE klient.id=schet.id_klient

AND schet.mes=date_part('month', now()-interval '1 month')

AND schet.god=date_part('year', now()-interval '1 month')

AND klient.adres='Щорса, 12'

GROUP BY klient.full_name

 

б) сумма оплаты по каждому виду услуг на адрес "Щорса, 12"

SELECT usluga.name, SUM(schet.oplata) as k_oplate

FROM usluga, schet, klient

WHERE klient.id=schet.id_klient

AND usluga.id=schet.id_usluga

AND schet.mes=date_part('month', now()-interval '1 month')

AND schet.god=date_part('year', now()-interval '1 month')

AND klient.adres='Щорса, 12'

GROUP BY usluga.name

 

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

SELECT usluga.name, schet.mes, SUM(schet.dolg) AS dolg

FROM usluga, schet

WHERE usluga.id=schet.id_usluga

AND schet.god=date_part('year', now())

GROUP BY usluga.name, schet.mes

ORDER BY usluga.name, schet.mes

 

Выводы.

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

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

 

4.1 Контрольные вопросы

Теоретические сведения

Синтаксис инструкции Select

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

Инструкция SELECT используется в основном как:

- самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений);

- элемент другого запроса, называемый «вложенный запрос»;

- фраза выбора в командах создания представления, курсора или вставки;

- средство присвоения переменным значений из строк сформированной таблицы.

 

Инструкция SELECT в упрощенном виде имеет следующий формат:

SELECT [[ALL] | DISTINCT] { * | элемент [[AS] [псевдоним_столбца]

[,элемент [AS] [псевдоним_столбца]]...}

FROM {базовая_таблица | представление} [AS] [псевдоним]

[,{базовая_таблица | представление} [AS] [псевдоним]]...

[WHERE условие_where]

[GROUP BY список_ group_by

[HAVING условие_having]]

[ORDER BY список_order_by]

[ FOR UPDATE [ OF таблица [....]]] [ LIMIT { число | ALL } [ { OFFSET |. } начало ] ]

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

SELECT (выбрать) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями;

FROM (из) перечисленных таблиц, в которых расположены эти столбцы;

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

GROUP BY (группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя в списке элементов SELECT агрегирующие SQL-функции, например: SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение);

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

ORDER BY (сортируя) результаты выбора данных; при этом упорядочение можно производить в порядке возрастания — ASC (ASCending) или убывания —DESC (DESCending), по умолчанию принимается ASC;

 

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

Элемент_SELECT::= [таблица.]* | выражение

выражение::= константа
| скалярная_функция
| [псевдоним.]столбец
| (выражение)
| (скалярный_подзапрос)
| {унарный оператор} выражение
| выражение {бинарный_оператор} выражение

где:

константа - одиночные алфавитно-цифровые символы, строки символов и числовые константы (символьные данные и данные типа дата/время берутся в одинарные кавычки);

скалярная_функция - SQL-функция, возвращающая скалярное значение, например SUM, CAST и др.(выражения, выступающие в качестве параметров SQL-функций, не должны содержать вложенных SQL-функций)

базовая_скалярная_функция::= | {SUM|AVG|MIN|MAX|COUNT} ([[ALL]|DISTINCT][таблица.]столбец)| {SUM|AVG|MIN|MAX|COUNT} ([ALL] выражение)| COUNT(*)

псевдоним - временный идентификатор, присваиваемый таблице с помощью служебного слова AS в предложении FROM;

столбец - собственно имя столбца, но не четырехсоставное имя, состоящее из имени базы данных, владельца, таблицы и имени столбца;

скалярный_подзапрос - подзапрос, возвращающий скалярный результат; подзапрос - это инструкция SELECT, вложенная в:

- предложения WHERE, HAVING или SELECT другой инструкции SELECT;

- инструкции INSERT, UPDATE и DELETE, предназначенные для модификации данных в таблицах;

- другой подзапрос.

Предложение WHERE включает набор условий для отбора строк:

условие_where::= [NOT] условие [[AND|OR][NOT] условие]...

условие::= выражение { = | <> | < | <= | > | >= } { выражение | (подзапрос) }

выражение_1 [NOT] BETWEEN выражение _2 AND выражение _3

выражение [NOT] IN { (константа [,константа]...) | (подзапрос) }

выражение IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS (подзапрос)

Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в предложении WHERE используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (проверка на существование данных, удовлетворяющих критериям подзапроса; если данных нет, то результат – ложь), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами AND, OR, NOT (с учетом их приоритета и скобок).

При обработке условия числа сравниваются алгебраически — отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины. Строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII. Если сравниваются две строки символов, имеющих разные длины, более короткая строка дополняется справа пробелами для того, чтобы они имели одинаковую длину перед осуществлением сравнения.

Синтаксис модификатора GROUP BY имеет вид

GROUP BY [таблица.]столбец [,[таблица.]столбец]... [HAVING условие_having]

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

С помощью предложения HAVING, где условие_having определяется так же, как и условие_where, можно исключить из результата группы, не удовлетворяющие заданным условиям.

Список параметров модификатора ORDER BY имеет вид

список_order_by::=

{[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]

[, {[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]] …

В отличие от GROUP BY модификатор ORDER BY не удаляет повторяющиеся значения.

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

SQL_функция [, SQL_функция] … [BY столбец [,столбец]…]

 

2.2 Преобразование типов данных в инструкции SELECT.

В PostgreSQL существует четыре фундаментальные SQL-конструкции, требующие четких правил преобразования типов: вызовы функций, применение операторов, присваивание значений при вставке и модифицировании данных, применение конструкций UNION (объединение результатов запросов) и CASE (аналог оператора if..then..else).

В PostgreSQL поддерживаются три варианта синтаксиса явного преобразования (приведения) типов:

· для строковых констант

тип 'значение'

'значение'::тип

CAST ('значение' AS тип)

· для числовых констант

значение:: тип

CAST (значение AS тип)

· для полей набора данных, возвращаемых запросом SQL

идентификатор:: тип

CAST (идентификатор AS тип)

Пример:

SELECT ("Остаток"+10)::float * 1.1,

"Цена"::char(8) || char(4) ' UAH', --соединение двух строк

CAST("Цена" AS text)

FROM "Книги"

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

SELECT 'От ' || '01/01/2011' || ' до ' || CAST(NOW() AS varchar(64)) || ' прошло ' || (CAST (now() AS varchar(64)):: date - date '01/01/2011'):: text || ' дней'

Возможно также использование преобразования типов в предложении WHERE. Следующий запрос возвратит данные об январских поставках в базе данных BookShop:

SELECT *

FROM "Поставки"

WHERE CAST("Дата" AS varchar) LIKE '%2011-01-%'

Тот же результат получается с помощью стандартной функции EXTRACT:

SELECT *

FROM "Поставки"

WHERE EXTRACT (MONTH FROM "Дата") = 1 AND EXTRACT (YEAR FROM "Дата") = 2011

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

 

Соединение таблицы со своей копией

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

FROM “Заказчики” AS A, “Заказчики” AS “B”

или просто

FROM “Заказчики” A, “Заказчики” B

то будут сформированы две копии таблицы Заказчики с именами A, B. Тогда, чтобы узнать, есть ли в базе данных два разных заказчика с одинаковыми телефонами, надо ввести запрос:

SELECT A.*, B.*

FROM “Заказчики” A, “Заказчики” B

WHERE A.”Телефон” = B.”Телефон”

AND A.”Код_заказчика”!= B.”Код_заказчика”

Вложенные подзапросы

Вложенный подзапрос - это подзапрос, заключенный в круглые скобки и вложенный в предложение WHERE (HAVING) инструкции SELECT или других инструкций, использующих предложение WHERE. Вложенный подзапрос может содержать в своем предложении WHERE (HAVING) другой вложенный подзапрос и т.д. Назначение вложенных подзапросов состоит в том, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других.

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

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

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

Подзапросы включаются в предложение WHERE (HAVING) с помощью ключевых слов IN, EXISTS или одного из условий сравнения (= | <> | < | <= | > | >=):

подзапросы, не возвращающие ни одного или возвращающие несколько значений, начинаются с IN или операторов сравнения и содержат ключевые слова ANY или ALL;

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

подзапросы, представляющие собой тест на существование или присутствие данных, начинаются с EXISTS.



Поделиться:


Последнее изменение этой страницы: 2017-01-25; просмотров: 497; Нарушение авторского права страницы; Мы поможем в написании вашей работы!

infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 54.173.214.79 (0.208 с.)