ЗНАЕТЕ ЛИ ВЫ?

Использование множества таблиц в одном запросе.



 

На лаб. раб. мы рассматривали запросы выбирающие информация из нескольких таблиц.

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

Операция такого рода называется соединением (Join) и является одной из самых мощных операций для рел. баз данных.

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

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

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

Наиболее часто встречающимся примером эквисоединения может служить операция соединения таблиц посредством ссылочной целостности.

Но в операции соединения могут участвовать любые столбцы различных таблиц (и даже одной и той же таблицы) не обязательно связанных отношением “предок - потомок”.

Выполнение операции соединения – декартово произведение.

 

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

 

Объединенную таблицу образуют пары тех строк из различных таблиц, у которых в связанных столбцах содержаться одинаковые значения. Если пара отсутствует (соответствующие значение еще не присвоено, т.е. имеет значение NULL), то стандартное SQL – объединение может привести к потере информации.

Рассмотренное объединение иногда называют внутренним объединением таблиц.

 

Внешнее объединение

 

В стандарте SQL 89 определено только внутренне объединение.

 

Построение внешнего объединения(объединения “сохраняющего информацию”)

1. Создать внутреннее объединение двух таблиц обычным образом.

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

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

4. Результирующая таблица является внешним объединением двух таблиц.

 

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

Левое внешнее объединение

Выполнить пункты 1 и 2, пункт 3 пропустить.(т.е. попадают в рез-т все несвязанные строки из левой таблицы). Обозначается *=.

 

Правое внешнее объединение

Выполнить пункты 1 и 3, пункт 2 пропустить.(т.е. попадают в рез-т все несвязанные строки из правой таблицы). Обозначается =*.

 

*=*

 

В sql2

 

Select * from tabl inner Join tbl2

On tabl.col1=tabl2.col2

 

Или

 

Select * from tabl inner Join tbl2

Using (col1, col2)

 

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

 

Select * from tabl Natural Inner Join tbl2

 

 

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

 

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

 

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

Выбор всех записей в которых поле “имя”=<значение>

Старый вариант - использовался в лаб. работе

if !empty(thisform.combo2.value)

select nam

locate for nam_val = alltrim(thisform.combo2.value)

w_n_num=n_num

else

w_n_num=0

endif

 

select test_lb.uniq_id,fam.f_val as family, nam.nam_val as nam, otc.otc_val as otc,;

street.s_val as street, test_lb.bldn as bld, test_lb.bldn_kor as bld_k,;

test_lb.appr as appr, test_lb.telef as tel;

from test_lb, fam, nam, otc, street;

where nam.n_num=test_lb.name_ AND;

fam.f_num=test_lb.fam AND;

street.s_num=test_lb.street AND;

otc.otc_n=test_lb.sndname AND;

nam.n_num=w_n_num;

INTO table test

 

Новый вариант - с подзапросом:

 

select test_lb.uniq_id,fam.f_val as family, nam.nam_val as nam, otc.otc_val as otc,;

street.s_val as street, test_lb.bldn as bld, test_lb.bldn_kor as bld_k,;

test_lb.appr as appr, test_lb.telef as tel;

from test_lb, fam, nam, otc, street;

where nam.n_num=test_lb.name_ AND;

fam.f_num=test_lb.fam AND;

street.s_num=test_lb.street AND;

otc.otc_n=test_lb.sndname AND;

nam.n_num=(SELECT n_num

FROM Nam

WHERE Nam_val =‘Иван’);

INTO table test

 

Чтобы оценить внешний (основной) подзапрос, SQL должен оценить внутренний запрос в предложении WHERE. Эта оценка осуществляется так, как будто внутренний запрос является одиночным:

Просматриваются все строки таблицы Nam и выбираются строки для которых значение поля Nam_val=‘Иван’ для таких строк выбирается значение поля n_num. Выбранной оказывается одна строка.

Выбранное значение подставляется в предикат основного запроса. Затем основной запрос выполняется как обычный.

Преимущества: - сработает при изменении номера, более гибкий (годится для всех имен);

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

 

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

1. Перечислите функции агрегирования, используемые в предложении Select.

2. Опишите операцию соединения таблиц.

3. Особенности операции внешнего соединения таблиц.

4. Синтаксис SQL92 операции соединения таблиц.

 

Лекция 15

 

Подзапрос должен выбирать только одну (или ни одной - значение предиката -unknown) записи если выбирается несколько записей - подзапрос оценивается как ошибочный;

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

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

 

Использование агрегатных функций в подзапросах.

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

EX ?

SELECT * FROM Orders

WHERE amt>

(SELECT AVG(amt)

FROM Orders

WHERE Odate = 02/28/1999)

 

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

Такие команды отвергаются в принципе(при синтаксическом разборе запроса - без выборки данных). Несмотря на то, что применение GROUP BY и HAVING в некоторых случаях дает единственную группу в качестве результата подзапроса.

 

Использование подзапросов возвращающих более одной строки.

Для использования подзапросов возвращающих более одной записи можно применить оператор IN во внешнем запросе. (Нельзя применять BEETWEEN, LIKE, IS NULL) . IN - определяет множество значений, которые тестируются на совпадение с другими значениями для определения истинности предиката. Когда IN применяется

с подзапросом SQL строит это множество из выходных данных этого подзапроса.

 

Orders

U_id Sl_num Part Amt Odate Client_num
N10 N5 N5 N10.2 D8 N5

 

Структура справочников:

 

Clients  
client_num N4
Nam_val C15
Sales_p  
Sl_num N6
Sl_nam C15
Sl_city C20

 

Найти все заказы для продавцов из Москвы

 

SELECT *

FROM Orders

WHERE Sl_num IN

(SELECT sl_num

FROM Sales_p

WHERE Sl_city=‘Москва’)

 

Данную задачу можно решить с использованием Join

 

SELECT Orders.U_id, Orders.part, Sales_p.sl_nam

FROM Orders, Sales_p

WHERE Orders.Sl_num = Sales_p.sl_num

AND Sales_p.Sl_city = ‘Москва’

 

(Достоинства и недостатки: результаты запроса непосредственно не видны и если есть ошибки в данных обнаружить их будет трудно).

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

 

Эффективность: оптимизатор, зависящий от реализации, join -> подзапрос

 

Общим во всех рассмотренных подзапросах было использование в качестве результата единственного столбца. Это необходимо - поскольку выходные данные подзапроса сравниваются с единственным значением. Следовательно, вариант SELECT * нельзя использовать в подзапросе. (Исключением являются подзапросы с оператором EXISTS)

 

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

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

Строка внешнего запроса, для которой выполняется внутренний запрос, называется текущей строкой - кандидатом. Алгоритм выполнения связанного подзапроса состоит в следующем:

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

2. Сохранить значения этой строки в алиасе, имя которого указано в предложении FROM внешнего запроса.

3. Выполнить подзапрос. Использование в подзапросе значения из строки-кандидата внешнего запроса называется внешней ссылкой.

4. Оценить предикат внешнего запроса на основе результатов подзапроса, выполненного на шаге 3. Это позволяет определить, будет ли строка кандидат включена в состав выходных данных.

5. Повторять процедуру для следующей строки-кандидата таблицы до тех пор, пока не будут проверены все строки в таблице.

найти всех клиентов сделавших заказы 26.02.02

 

SELECT *

FROM Clients as outer

WHERE 26/02/1999 IN

(SELECT Odate

FROM Orders as inner

WHERE outer.Client_num=inner.Client_num)

insert into secondpart (uniq_id)

select firstpart.uniq_id from firstpart

where

not exists

(Select seconpart.uniq_id from secondpart Where

firstpart.uniq_id = secondpart.uniq_id )

 

Рассмотрим в подробностях пример записанный выше.

Добавляет записи во вторую таблицу с кодом uniq_id - такие что, записи с этим uniq_id существуют в первой части (first_part) и не существуют во второй.

Оператор Select используемый в операторе Insert в свою очередь использует связанный подзапрос для нахождения таких строк из первой таблицы для которых нет соответствия во второй. Результатом выполнения оператора Select является столбец из uniq_id’ов которые добавляются во вторую таблицу. Для оценки результатов самого внутреннего запроса используется оператор EXISTS.

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

EXISTS - принимает значение “истина” если подзапрос используемый в качестве аргумента генерирует выходные данные, и “ложь” в противном случае. В отличии от прочих операторов и предикатов, он не может принимать значения “неизвестно” (unknown). В примере EXISTS выбирает один столбец, аналогично предыдущим примерам. На самом деле несущественно сколько столбцов извлекает EXISTS поскольку он не применяет полученных значений, а только фиксирует наличие данных.

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

 

 

Объединение множества запросов в один.

 

Все предшествующие примеры показывают различные варианты запросов с расположением “один внутри другого”. Существует другой способ объединения множества запросов - их объединение с использованием предложения UNION.

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

Для выполнения команды UNION столбцы запросов входящие в состав выходных данных должны быть совместимы по объединению (union compatible).

- одинаковое количество столбцов (столбцы должны быть сравнимы по объединению)

Одинаковые типы данных(тип и длина - числовые)

Для символьных данных (тип и длина - строгость ограничений зависит от конкретного продукта )

Если для одного столбца установлено ограничение NOT NULL то это ограничение должно быть у соответствующих столбцов других запросов.

 

Синтаксис:

 

select -without-order-by

... UNION [ALL] select-without-order-by

... [ UNION [ALL] select-without-order-by ] ...

... [ ORDER BY integer [ ASC | DESC ], ... ]

 

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

 

 

alter procedure

yura.get_param(in sta_ char(15),in typ char(20),in metall char(15),in otpr char(20),in pol char(20),in otvets char(20),in from_ char(20),in to_ char(20),in stn_o char(20),in stn_n char(20),in kontr char(20))

result(stats smallint,"\\x27num_fr\\x27" char(6))

begin

(select status.stats,'status' from status

where status.name_s=sta_

union select types.typ_num,'types ' from types

where types.name_t=typ

union select owners.own_num,'owners' from owners

where owners.name_otv=otvets

order by 2 asc

 

 

Команды обновления DML

 

Команда добавления новых записей в таблицу.

Format 1

INSERT INTO [ owner.]table-name [( column-name, ... )]

... VALUES ( expression | DEFAULT, ... )

 

Format 2

INSERT INTO [ owner.]table-name [( column-name, ... )]

... select-statement

 

Назначение

Для добавления одной записи используется формат 1.

Предложение DEFAULT может быть использовано для присвоения столбцу значений заданных для него по умолчанию . Если необязательный список имен столбцов задан то, значения из списка переносятся в указанные столбцы. Если список столбцов не указан - значения записываются в столбцы в том порядке в котором они были созданы ( такой же порядок получается при использовании SELECT *). Записи добавляются в таблицу в призвольную позицию. (В реляционных БД таблицы не упорядочены.)

 

Insert в формате 2 используется для добавления результатов запроса в указанную таблицу.

 

Ограничения доступа:

Пользователь должен иметь доступ по INSERT к указанной таблице table.

 

Формат 2 позволяет пользователю одной операцией добавлять в таблицу результаты сгенерированные оператором SELECT общего вида(без ограничений).Записи добавляются в произвольном порядке вне зависимости от того содержит ли оператор SELECT предложение ORDER BY. Столбцы в операторе SELECT должны совпадать со столбцами указанными в списке оператора INSERT или физическому порядку столбцов в таблице.( порядку в котором они были созданы ( такой же порядок получается при использовании SELECT *)).

 

 

Examples

 

Insert into Fam Values (123,’Склеймин’)

 

Вставка NULL значений

В команде Insert могут быть указаны имена столбцов:

 

Insert into Fam(fam_cod,fam_val) Values (123,’Склеймин’)

 

INSERT INTO department ( dept_id, dept_name )

VALUES ( 230, 'Eastern Sales' )

 

 

Команда обновления значений столбцов

 

UPDATE table-list

... SET column-name = expression, ...

... [ WHERE search-condition ]

... [ ORDER BY expression [ ASC | DESC ] ,... ]

 

Ограничение по правам доступа:

Для пользователя д.б. разрешено выполнения UPDATE для тех столбцов которые он пытается модифицировать..

 

Оператор UPDATE используется для изменения строк одной или более таблиц.(в новых стандартах и реализациях)

Стандарт SQL(старый) не допускает изменения нескольких таблиц одной командой Update, т.к. в выражении <column-name> нельзя указывать имя таблицы.

Каждый указанный столбец принимает значение выражения указанного справа от знака равенства. Структура выражения никак не ограничена. <column-name> может быть использовано в выражении — существующие значение будет использовано.

Если предложение Where не указано будут изменены все записи в таблице.

Если Where присутствует в команде - будут обновлены только те записи которые удовлетворяют предикату <search-cond>.

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

Update разрешает использование подзапросов внутри предиката.

 

Счета в таблице нумеруются начиная с ID 2001.

Запрос перенумеровывает все существующие счета вычитанием 2000 из поля id.

 

UPDATE sales_order_items AS items ,

sales_order AS orders

SET items.id = items.id - 2000,

orders.id = orders.id - 2000 ;

 

Update cust Set raiting=200

 

Обновление нескольких столбцов одной командой

 

Update cust Set raiting=200, city=‘Москва’ WHERE snum=101

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

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

1. В чем особенность выражений с подзапросами?

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

3. Сформулируйте алгоритм выполнения связанных подзапросов.

4. Перечислите ограничения при выполнении оператора Union.

5. Перечислите операторы изменяющие данные в таблицах.

 

 

Команда удаления строк DELETE

 

Syntax

 

DELETE [FROM] [ owner.]table-name

... [FROM table-list]

... [WHERE search-condition]

Применение

Для удаления записей из БД.

Ограничения

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

 

Оператор DELETE удаляет все записи удовлетворяющие условию WHERE из указанной таблицы. Если WHERE не указано, все записи в таблице будут удалены.

 

Удаляет строки целиком - не может быть использована для удаления значений отдельных полей.(Не нужно указывать имя поля).

 

Примеры

Удаляет поставщика из таблицы (по значению emp_id)

 

DELETE

FROM employee

WHERE emp_id = 105

 

Удаляет записи из таблицы fin_data в которых значение поля year меньше 1993

 

DELETE

FROM fin_data

WHERE year < 1993

 

Удаляет записи из таблицы contact, если такие записи уже есть в таблице customer.

DELETE

FROM contact

FROM contact, customer

WHERE contact.last_name = customer.lname

AND contact.first_name = customer.fname

 

Delete From Fam

Для удаления конкретных строк используется предикат(мб с подзапросом)

Delete From Fam WHERE fam_cod=103

Указание в предикате первичного ключа - гарантия удаления одной строки.

 


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

 

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

 

Определение представлений

 

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

 

Представления используются по нескольким причинам:

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

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

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

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

Имя представления указывается в предложении FROM как имя обычной таблицы, а ссылка на столбцы представления в инструкции SELECT осуществляется точно так же, как на столбцы таблицы. К некоторым представлениям можно применять инструкции insert, delete и update для изменения данных. Таким образом, представление можно использовать в инструкциях SQL так, как будто оно является обычной таблицей.

 





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

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