Преимущество использования представлений

Цель работы

Изучить инструкции языка определения данных для создания и удаления БД и ее объектов; изучить инструкции манипулирования данными.

 

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

2.1 Инструкции языка определения данных

Базы данных

Для создания баз данных в SQL служит инструкция CREATE DATABASE, синтаксис которой (с сокращениями) имеет следующий вид:

CREATE DATABASE база_данных

[ [ WITH ] [ OWNER [=] пользователь]

/* владелец базы данных (по умолчанию - пользователь, который ввел команду create) */

[ TEMPLATE [=] шаблон ]

/* используемый шаблон (по умолчанию - template1) */

[ ENCODING [=] кодировка ]

/* используемая кодировка (по умолчанию - кодировка шаблона) */

[ LC_COLLATE [=] порядок_сравнения]

/* порядок сравнения строк символов (по умолчанию - как в шаблоне) */

[ LC_CTYPE [=] тип_категоризации]

/* категоризация символов (нижний/верхний регистр, цифра) (по умолчанию - как в шаблоне) */

[ TABLESPACE [=] табличное_пространство]

/* используемое табличное пространство (по умолчанию - как в шаблоне) */

[ CONNECTION LIMIT [=] connlimit ] ]

/* количество параллельных подключений (по умолчанию - «-1», т.е. не ограничено) */

Например, в результате выполнения команды

CREATE DATABASE a_new_database

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

WITH OWNER = postgres

ENCODING = 'UTF8'

TABLESPACE = pg_default

LC_COLLATE = 'Ukrainian_Ukraine.1251'

LC_CTYPE = 'Ukrainian_Ukraine.1251'

CONNECTION LIMIT = -1

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

Базовые таблицы

Инструкция CREATE TABLE

Таблицы создаются c помощью инструкции CREATE TABLE, которая в основном определяет тип таблицы (временная или базовая), имя таблицы, набор столбцов и ограничения целостности (ограничения для столбцов или для всей таблицы). Каждая таблица должна иметь, по крайней мере, один столбец. Синтаксис инструкции CREATE TABLE в PostgreSQL в сокращенном виде:

 

CREATE [{TEMPORARY | TEMP}] TABLE таблица

([{столбецтип_данных [ DEFAULT значение_по_умолчанию] [<ограничение_столбца> [... ] ]

| < ограничение_таблицы >

| LIKE родительская_таблица [<параметры_ LIKE> ] } [,... ] ])

[ INHERITS (родительская_таблица [,... ]) ]

[ TABLESPACE табличное_пространство ]

 

где

< ограничение_столбца >::= [CONSTRAINT ограничение]

{ { NOT NULL | [ NULL ] }

| CHECK (ограничение_на_значение_столбца)

| UNIQUE параметры_индексирования

| PRIMARY KEY

| REFERENCES таблица [ (столбец) ]

[ MATCH FULL - один столбец в составом внешнем ключе не может быть NULL

|MATCH PARTIAL - не реализовано

| [ MATCH SIMPLE] ] - один столбец в составом внешнем ключе может быть NULL

[ ON DELETE действие ] – действие при удалении родительского ключа

[ ON UPDATE действие] – действие при изменении родительского ключа }

 

< ограничение_таблицы >::= [CONSTRAINT имя_ограничения ]

{ CHECK (ограничение_на_значение_столбца)

| UNIQUE (столбец [,... ]) параметры_индексирования

| PRIMARY KEY (столбец [,... ]) параметры_индексирования

| FOREIGN KEY (столбец [,... ]) REFERENCES таблица [ (столбец [,... ]) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ ON DELETE действие]

[ ON UPDATE действие] }

Если указано TEMPORARY или TEMP, создается временная таблица, т.е. такая таблица автоматически удаляется сразу после завершения транзакции.

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

Ограничения NULL / NOT NULL разрешают или запрещают ввод в поле NULL-значений. Очевидно, что ограничение NOT NULL должно быть указано для первичных ключей, поскольку в противном случае под угрозой окажется целостность данных. Кроме того, отдельные поля таблиц по своему назначению могут требовать только определенных значений. Если поместить ключевые слова NOT NULL сразу после типа данных столбца, любая попытка поместить значение NULL в это поле будет отклонена. В противном случае, SQL будет считать, что для этого столбца NULL-значения разрешены.

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

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

Ограничение FOREIGN KEY (внешний ключ) обеспечивает принцип ссылочной целостности. Когда столбец является внешним ключом, он определенным образом связан с таблицей, на которую он ссылается. Фактически это означает, что каждое значение в этом столбце (внешнем ключе) непосредственно привязано к значению в другом столбце (родительском ключе). Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это так, то система будет в состоянии ссылочной целостности. Понятно, что любое число внешних ключей может ссылаться к единственному значению родительского ключа. Понятно также, что в качестве родительского ключа могут выступать столбцы с ограничениями PRIMARY KEY или UNIQUE, т.е. столбцы с уникальными значениями и не содержащие NULL-значений. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или NULL-значения, попытка ввода других значений приводит к ошибке. Присутствие NULL-значений во внешнем ключе не нарушит ссылочную целостность, но даст возможность не вводить данные, если они пока не известны.

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

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

Создаваемая таблица может наследовать столбцы других таблиц, которые задаются в предложении LIKE или INHERITS. Различие между LIKE и INHERITS - в том, что при использовании INHERITS создается постоянная связь между дочерней и родительской таблицами, причем все изменения, производимые с родительской таблицей, наследуются дочерней и по умолчанию при выборке значений родительской таблицы данные из дочерней автоматически включаются в эту выборку. При использовании LIKE родительская и дочерняя таблица полностью независимы.

<параметры_ LIKE>::=

{ INCLUDING | EXCLUDING } -- включая/исключая

{ DEFAULTS -- значения по умолчанию

| CONSTRAINTS – ограничения

| INDEXES – индексы

| STORAGE – параметры хранения

| COMMENTS -- комментарии

| ALL }

Инструкция CREATE TABLE AS

C помощью инструкции CREATE TABLE ASможно создать таблицу, в том числе временную, на основе результатов запроса. Новая таблица получает те же столбцы с теми же типами данных, что и в запросе. При желании можно ввести иные имена столбцов для создаваемой таблицы, а также сохранить данные, полученные в результате выполнения запроса, в созданной таблице. Синтаксис инструкции CREATE TABLE AS (в упрощенном виде):

CREATE [ { TEMPORARY | TEMP } ] TABLE таблица [ (столбец [,...]) ]

AS запрос [ WITH [ NO ] DATA ]

Инструкция DROP TABLE

Базовую таблицу можно в любой момент удалить с помощью предложения DROP TABLE (уничтожить таблицу):

DROP TABLE [ IF EXISTS ] базовая_таблица [,...] [ CASCADE | [RESTRICT] ]

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

Для удаления содержимого таблицы (но не самой таблицы) можно использовать инструкции DELETE или TRANCATE.

Инструкция ALTER TABLE

В SQL существует также инструкция ALTER TABLE (изменить таблицу), которая позволяет добавлять, удалять и модифицировать столбцы и ограничения. Синтаксиса ALTER TABLE имеет вид:

ALTER TABLE [ ONLY ] имя_таблицы [ * ] <действие> [,... ]

где (ограничиваясь лишь стандартными элементами)

 

<действие>::= {

-- добавить столбец

ADD [ COLUMN ] столбецтип [ограничение_столбца [... ] ]

-- удалить столбец

| DROP [ COLUMN ] [ IF EXISTS ] столбец [ RESTRICT | CASCADE ]

-- изменить тип столбца

| ALTER [ COLUMN ] столбец [ SET DATA ] TYPE тип

-- изменить значение по умолчанию

| ALTER [ COLUMN ] столбец SET DEFAULT выражение

| ALTER [ COLUMN ] столбец DROP DEFAULT

-- изменить ограничение NOT NULL

| ALTER [ COLUMN ] столбец { SET | DROP } NOT NULL

-- переименовать столбец

| RENAME [ COLUMN ] имя_столбца TO новое_имя

-- добавить/удалить ограничение таблицы

| ADD ограничение таблицы

| DROP CONSTRAINT [ IF EXISTS ] ограничение[ RESTRICT | CASCADE ] }

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

Индексы

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

PostgreSQL поддерживает четыре типа индексов: B-tree, hash, GiST, GIN. По умолчанию используется B-tree. Обычно при ведении ограничений первичного ключа и UNIQUE соответствующий индекс создается автоматически, но для первичного это делается неявно (его нельзя удалить как обычные индексы), В PostgreSQL создать индекс указанного типа для одного или нескольких полей таблицы можно с помощью инструкции (упрощенный вид):

CREATE [ UNIQUE ] INDEX [ индекс ]
ON таблица [ USING тип_индекса ]

({ столбец | выражение } [ [ASC] | DESC] [,...])

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

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

При выборе типа индекса следует учитывать, что создать UNIQUE-индекс и указать порядок сортировки (ASC/DESC) можно только для индексов типа B-tree. Индексировать по нескольким полям можно только при выборе индексов B-tree, GiST, GIN.

Любой индекс может быть кластерным (CLUSTERED). Это означает, что на его основе можно выполнить кластеризацию таблицы, т.е. переупорядочивание физических страниц данных в соответствии с логическим порядком данного индекса (что естественно увеличивает число дисковых операций ввода-вывода). Поэтому для каждой таблицы может быть создан только один кластерный индекс, тогда как не кластерных индексов может быть несколько (практически оптимальным количеством является 2-6 не кластерных индексов на таблицу). Кластеризация дает преимущество в том случае, если большую часть данных, которые выбираются из таблицы, составляют группы индексированных данных, а не случайно выбранные отдельные данные, т.к. такие группы располагаются на одной и той же физической странице (или последовательных страницах). Но необходимо учитывать, что при выполнении кластеризации таблицы все операции, в том числе чтение, с этой таблицей блокируются.

Для кластеризации в PostgreSQL используется инструкция

CLUSTER [VERBOSE] [ таблица [ USING индекс ] ]

где VERBOSE позволяет вывести сообщение о кластеризации каждой таблицы.

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

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

Чтобы удалить индекс, надо знать его имя или его OID. Для удаления индекса по имени используется инструкция:

DROP INDEX имя_индекса

Индекс можно удалить непосредственно из системной таблицы pg_index, зная его OID. Удаление индекса не влияет на содержание полей.

 

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

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

Представление создается с помощью инструкции CREATE VIEW, синтаксис которой имеет следующий вид:

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW имя_представления

[ (столбец [,...]) ] AS запрос

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

При использовании OR REPLACE, если представление с таким же именем уже существует в данной схеме, оно будет переопределено.

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

Синтаксис инструкции удаления представления подобен синтаксису удаления базовых таблиц:

DROP VIEW имя_представления

Инструкция INSERT

Таблицы создаются инструкцией CREATE TABLE. Эта инструкция создает пустую таблицу – таблицу без строк. Значения вводятся, удаляются или обновляются с помощью инструкций языка манипулирования данными (DML), основными из которых являются инструкции INSERT (вставить), DELETE (удалить), и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями.

Синтаксис инструкцииINSERT:

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

{ DEFAULT VALUES

| VALUES ({ DEFAULT | NULL | выражение } [,...])

| запрос }

[ RETURNING { * | выражение [ [ AS ] имя ] } [,...] ]

Предложение INTO необязательно и просто улучшает читабельность инструкции.

Предложение RETURNING (не стандартное) позволяет вывести на экран (так же, как и при использовании SELECT) значения выражений, построенных на основе вставляемых данных.

Возможны два варианта использования INSERT: без запроса и с запросом. В первом варианте в указанную таблицу вставляется строка со значениями полей, указанными в перечне предложения VALUES (значения), причем i-е значение должно соответствовать i-му столбцу в списке столбцов (как и в инструкции SELECT, порядок следования столбцов может быть произвольным). Столбцы, не указанные в списке, заполняются NULL-значениями или значениями по умолчанию. Если NULL-значениями для такого столбца не допустимы и не указано значение по умолчанию, то транзакция отменяется и выводится сообщение об ошибке. Если в списке VALUES указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов после имени таблицы или представления можно опустить.

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

Инструкция DELETE

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

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

[ USING список ]

[ WHERE { условие | WHERE CURRENT OF курсор } ]

[ RETURNING { * | выражение [ [ AS ] имя ] [,...] } ]

Список в предложении USING аналогичен списку предложения FROM инструкции SELECT.

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

Предложение RETURNING аналогично такому же предложению инструкции INSERT.

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

Для удаления всех строк инструкция DELETE используется редко. Если необходимо удалить все строки в таблице, во многих случаях следует отдать предпочтение инструкции TRUNCATE TABLE, т.к. эта инструкция работает более быстро, удаляя ссылки данной таблицы на физические страницы данных, а не удаляя данные построчно как инструкция DELETE. Но следует помнить, что TRUNCATE TABLE, в отличие от DELETE, не записывает удаляемые строки в журнал транзакций, а значит, удаленные строки восстановить нельзя. Кроме того, TRUNCATE TABLE не применима к таблицам, на которые ссылаются другие таблицы через внешние ключи.

 

Инструкция UPDATE

Инструкция UPDATE используется для изменения существующих значений. В разных диалектах SQL форматы этой инструкции могут отличаться как друг от друга, так и от формата ANSI SQL. В PostgreSQL принят следующий формат (в упрощенном виде):

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

SET { столбец = { выражение | DEFAULT } |

(столбец [,...]) = ({ выражение | DEFAULT } [,...]) } [,...]

[ FROM список ]

[ WHERE условие | WHERE CURRENT OF курсор ]

[ RETURNING { * | выражение [ [ AS ] имя ] [,...] } ]

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

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

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

Если команды манипулирования данными нарушают ограничения, установленные на столбцы таблиц, то соответствующие транзакции отменяются, и выводится сообщение об ошибке. Наиболее важным является вопрос о взаимоотношении команд манипулирования данными и внешних и родительских ключей. Для столбцов, определенных как внешние ключи, любые значения, которые помещаются в эти столбцы командами INSERT или UPDATE должны быть представлены в их родительских ключах. Можно помещать NULL-значения в эти столбцы, несмотря на то, что NULL-значения не допустимы в родительских ключах. Можно удалять или изменять любые строки с внешними ключами, но любое значение родительского ключа не может быть удалено или изменено. Это означает, например, что нельзя удалить запись о заказчике из таблицы Заказчики пока на эту таблицу есть ссылки внешних ключей других таблиц.

Ход работы

Порядок выполнения работы

 

БД BookShop

 

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

 

2. В одну из базовых таблиц, на которую не ссылаются внешние ключи других таблиц, добавить столбец типа uuid и сделать его первичным ключом, отменив существующее в этой таблице ограничение первичного ключа. В этот столбец в каждой строке ввести значение, равное количеству секунд, прошедших от ‘01.01.1900 00:00’ до того момента, когда очередное такое значение вносится в таблицу. Для столбцов, входивших в состав первичного ключа, создать уникальный индекс.

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

Таблицы Klient и usluga связаны между собой отношением многие-ко-многим. Каждый клиент пользуется многими услугами и каждой услугой пользуются многие клиенты. Таблица schet является связующей для них.

 

CREATE OR REPLACE VIEW public.schet_is_null AS

SELECT *

FROM schet

WHERE schet.id_klient IS NULL OR schet.id_usluga IS NULL

Написать инструкции для:

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

UPDATE schet_is_null

SET id_klient=(SELECT id FROM klient LIMIT 1),

id_usluga=(SELECT id FROM usluga LIMIT 1)

 

б) удаления таких строк

DELETE FROM schet_is_null

 

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

 

CREATE TEMPORARY TABLE ttt AS (SELECT client.num_client

FROM client, zakaz

WHERE client.num_client=zakaz.num_client

GROUP BY client.num_client

HAVING MAX(zakaz.data)<(now()-interval '1 year'));

 

DELETE FROM client WHERE num_client IN (SELECT num_client FROM temp);

 

DELETE FROM zakaz WHERE num_client IN (SELECT num_client FROM temp);

 

DROP TABLE IF EXISTS ttt;

 

19. Создайте представления для ролей «Директор», «Закупщик», «Продавец», исходя из того, что им необходимо создавать отчеты:

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

 

CREATE VIEW dir_reiting_book AS

SELECT "Book".name, COUNT(zakaz.*) AS kolvo

FROM "Book", zakaz

WHERE "Book".num_book=zakaz.num_book

GROUP BY "Book".num_book

ORDER BY kolvo DESC, name;

 

CREATE VIEW dir_reiting_sotr AS

SELECT "Sotrudnik".fam_im, COUNT(zakaz.*) AS kolvo, SUM("Book".price) AS cena

FROM "Sotrudnik", zakaz, "Book"

WHERE zakaz.lic_sotrudnik="Sotrudnik".lic_nomer

AND zakaz.num_book="Book".num_book

GROUP BY "Sotrudnik".lic_nomer

ORDER BY cena DESC;

 

CREATE VIEW zak_postavchiki AS

SELECT postavchik.name, postavka.cnt AS kolvo, postavka.data, "Book".price AS cena

FROM postavchik, postavka, "Book"

WHERE postavchik.num_suppl=postavka.num_postavchik

AND postavka.num_book="Book".num_book

AND "Book".name='Іван Богун'

 

CREATE VIEW zak_knigi AS

SELECT * FROM "Book" ORDER BY name;

 

Для проверки возможности добавлять информацию:

INSERT INTO zak_knigi (name, author, publish, price, balance)

VALUES ('Москва 2042', 'Войнович', 'UAPRINT', 98, 100);

 

CREATE VIEW prod_prodazha AS

SELECT "Sotrudnik".fam_im, COUNT(zakaz.*) as kolvo

FROM "Sotrudnik", zakaz

WHERE zakaz.lic_sotrudnik="Sotrudnik".lic_nomer

AND zakaz.data='2016-03-20'

GROUP BY "Sotrudnik".lic_nomer

ORDER BY "Sotrudnik".fam_im

 

CREATE ROLE direktor LOGIN PASSWORD '1111';

GRANT SELECT ON dir_reiting_book TO direktor;

GRANT SELECT ON dir_reiting_sotr TO direktor;

GRANT SELECT ON "Book" TO direktor;

GRANT SELECT ON zakaz TO direktor;

GRANT SELECT ON "Sotrudnik" TO direktor;

 

CREATE ROLE zakupchik LOGIN PASSWORD '1111';

GRANT SELECT, INSERT ON zak_knigi TO zakupchik;

GRANT SELECT ON zak_postavchiki TO zakupchik;

GRANT SELECT ON "Book" TO zakupchik;

GRANT SELECT ON postavka TO zakupchik;

GRANT SELECT ON postavchik TO zakupchik;

 

CREATE ROLE prodavec LOGIN PASSWORD '1111'

GRANT SELECT, INSERT ON zak_knigi TO prodavec;

GRANT SELECT ON prod_prodazha TO prodavec;

GRANT SELECT ON "Book" TO prodavec;

GRANT SELECT ON zakaz TO prodavec;

GRANT SELECT ON "Sotrudnik" TO prodavec;

 

Выводы

Выполняя эту лабораторную работу я углубил свои знания в командах SQL. Больше работая не с мастерами построения каких либо объектов, а непосредственно в окне выполнения пользовательских запросов. Тем самым выработал навыки программирования в среде PostgreSQL.

 

Раздел «Ход работы» должен содержать инфологическую модель БД (на языке таблица-связь) для своего варианта и БД BookShop; отчеты DDL для всех таблиц БД BookShop и БД своего варианта; скрипты и результаты выполнения запросов для БД BookShop и БД своего варианта.

 

 

**************************************************************************

 

 

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

Что такое представление?

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

Представление создается с помощью инструкции CREATE VIEW, синтаксис которой имеет следующий вид:

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW имя_представления

[ (столбец [,...]) ] AS запрос

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

При использовании CREATE OR REPLACE, если представление с таким же именем уже существует в данной схеме, оно будет переопределено.

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

Синтаксис инструкции удаления представления подобен синтаксису удаления базовых таблиц:

DROP VIEW имя_представления

 

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

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

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

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

 

Цель работы

Изучить инструкции языка определения данных для создания и удаления БД и ее объектов; изучить инструкции манипулирования данными.

 

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

2.1 Инструкции языка определения данных

Базы данных

Для создания баз данных в SQL служит инструкция CREATE DATABASE, синтаксис которой (с сокращениями) имеет следующий вид:

CREATE DATABASE база_данных

[ [ WITH ] [ OWNER [=] пользователь]

/* владелец базы данных (по умолчанию - пользователь, который ввел команду create) */

[ TEMPLATE [=] шаблон ]

/* используемый шаблон (по умолчанию - template1) */

[ ENCODING [=] кодировка ]

/* используемая кодировка (по умолчанию - кодировка шаблона) */

[ LC_COLLATE [=] порядок_сравнения]

/* порядок сравнения строк символов (по умолчанию - как в шаблоне) */

[ LC_CTYPE [=] тип_категоризации]

/* категоризация символов (нижний/верхний регистр, цифра) (по умолчанию - как в шаблоне) */

[ TABLESPACE [=] табличное_пространство]

/* используемое табличное пространство (по умолчанию - как в шаблоне) */

[ CONNECTION LIMIT [=] connlimit ] ]

/* количество параллельных подключений (по умолчанию - «-1», т.е. не ограничено) */

Например, в результате выполнения команды

CREATE DATABASE a_new_database

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

WITH OWNER = postgres

ENCODING = 'UTF8'

TABLESPACE = pg_default

LC_COLLATE = 'Ukrainian_Ukraine.1251'

LC_CTYPE = 'Ukrainian_Ukraine.1251'

CONNECTION LIMIT = -1

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

Базовые таблицы

Инструкция CREATE TABLE

Таблицы создаются c помощью инструкции CREATE TABLE, которая в основном определяет тип таблицы (временная или базовая), имя таблицы, набор столбцов и ограничения целостности (ограничения для столбцов или для всей таблицы). Каждая таблица должна иметь, по крайней мере, один столбец. Синтаксис инструкции CREATE TABLE в PostgreSQL в сокращенном виде:

 

CREATE [{TEMPORARY | TEMP}] TABLE таблица

([{столбецтип_данных [ DEFAULT значение_по_умолчанию] [<ограничение_столбца> [... ] ]

| < ограничение_таблицы >

| LIKE родительская_таблица [<параметры_ LIKE> ] } [,... ] ])

[ INHERITS (родительская_таблица [,... ]) ]

[ TABLESPACE табличное_пространство ]

 

где

< ограничение_столбца >::= [CONSTRAINT ограничение]

{ { NOT NULL | [ NULL ] }

| CHECK (ограничение_на_значение_столбца)

| UNIQUE параметры_индексирования

| PRIMARY KEY

| REFERENCES таблица [ (столбец) ]

[ MATCH FULL - один столбец в составом внешнем ключе не может быть NULL

|MATCH PARTIAL - не реализовано

| [ MATCH SIMPLE] ] - один столбец в составом внешнем ключе может быть NULL

[ ON DELETE действие ] – действие при удалении родительского ключа

[ ON UPDATE действие] – действие при изменении родительского ключа }

 

< ограничение_таблицы >::= [CONSTRAINT имя_ограничения ]

{ CHECK (ограничение_на_значение_столбца)

| UNIQUE (столбец [,... ]) параметры_индексирования

| PRIMARY KEY (столбец [,... ]) параметры_индексирования

| FOREIGN KEY (столбец [,... ]) REFERENCES таблица [ (столбец [,... ]) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ ON DELETE действие]

[ ON UPDATE действие] }

Если указано TEMPORARY или TEMP, создается временная таблица, т.е. такая таблица автоматически удаляется сразу после завершения транзакции.

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

Ограничения NULL / NOT NULL разрешают или запрещают ввод в поле NULL-значений. Очевидно, что ограничение NOT NULL должно быть указано для первичных ключей, поскольку в противном случае под угрозой окажется целостность данных. Кроме того, отдельные поля таблиц по своему назначению могут требовать только определенных значений. Если поместить ключевые слова NOT NULL сразу после типа данных столбца, любая попытка поместить значение NULL в это поле будет отклонена. В противном случае, SQL будет считать, что для этого