Материалы представленные на сайте исключительно с целью ознакомления пользователям Интернета и не преследуют коммерческих целей или нарушение авторских прав.
© 2018 - 2024
На лаб. раб. мы рассматривали запросы выбирающие информация из нескольких таблиц.
Одна из наиболее важных черт запросов 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 операции соединения таблиц.
Лекция 7
Подзапрос должен выбирать только одну (или ни одной - значение предиката -unknown) записи если выбирается несколько записей - подзапрос оценивается как ошибочный;
Предикаты с подзапросами являются неперемещаемыми.
Предикаты, включающие подзапросы, используют форму <скалярное выражение> <оператор сравнения> <подзапрос>.
Конструкции <подзапрос> <оператор> <скалярное выражение> или <подзапрос> <оператор> <подзапрос> недопустимы.
Использование агрегатных функций в подзапросах.
Одним из видов функций, которые автоматически выдают единственное значение для любого количества строк - являются агрегатные функции. Любой запрос, использующий единственную агрегатную функцию без предложения GROUP BY, дает в результате единственное значение для использования его в основном предикате.
EX ?
SELECT * FROM Orders
WHERE amt >
(SELECT AVG(amt)
FROM Orders
WHERE Odate = 02/28/2011)
При применении предложения 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.11
SELECT * FROM Clients as outer
WHERE 26/02/2011 IN
(SELECT Odate FROM Orders as inner
WHERE inner.Client_num =outer.Client_num )
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 |
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. Перечислите операторы изменяющие данные в таблицах.
Лекция 8
Команда удаления строк 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 так, как будто оно является обычной таблицей.
Дата: 2019-02-02, просмотров: 406.