Операторы манипулирования данными.

1. Добавление информации в таблицу.

Insert into имя_таблицы (список полей) values (список выражений)

- добавление одной записи,

2. Чтобы добавить несколько значений из другой таблицы можно использовать следующий оператор –

Insert into имя_таблицы [(список добавляемых полей)]

или использовать полузапрос

select список полей

from список таблиц

where условие

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

3. Оператор обновления.

Update имя_таблицы set поле=значение [where условие] (все что записано в квадратных скобках это не обязательно, может быть, а может и не быть).

4. Удаление записи из таблицы.

Delete from имя_таблицы [where условие]

Если where условие опущено, то удаляются все записи.

5. Оператор поиска данных в таблицах

distinct

Select all список_полей from список_таблиц

[where условие] [group by список_полей] [having условие]

[order by поля[asc,desc]] [union [all] подзапрос]

distinct – режим, исключающий повторяющиеся записи в ответе.

All – режим, при котором в ответ включаются все записи. По умолчанию принято all, его можно не указывать.

Если одновременно присутствует where и having, то сначала будет выполняться where.

Если присутствует where, having, group by, то сначала выполнится where, потом group by, а потом having.

· Select список_полей - указывает имена полей, которые должны содержаться в ответе;

· from список_таблиц – указывает имена таблиц, которые участвуют в запросе;

· where условие – задает условие отбора записей в ответ;

· group by список_полей – задает условие группировки записей, группировка – это операция разбиения на группы, каждая из которых содержит одинаковые значения в отмеченных столбцах;

· having условие задает условие отбора групп, если нет group by, то это условие применяется ко всей таблице;

· order by поля[asc,desc] – задает тип сортировки записей в ответе, asc – это сортировка по возрастанию, desc – по убыванию, по умолчанию ставится asc;

· union [all] подзапрос позволяет объединить главный Select с результатом подзапроса, подзапросом называется вложенный оператор Select.

Как задается условие?

Выражение1 операция выражение2

В выражении могут использоваться константа и поля БД. Константы могут быть числовые, строковые (записанные в одинарных или двойных кавычках), даты ({24.12.00}), логические значения.

При задании условия разрешены следующие операции: +, -, *, \, %, <, <=, >, >=, =, <>,!=, not, and, or.

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

Приоритет:

1. ()

2. * \ %

3. + -

4. not

5. and

6. or

В качестве условия после where может стоять:

Условие типа сравнения;

· Not, and, or;

· Between, not between;

· In, not in;

· Is null, not is null;

· Поле Like (match) ‘образец’ Escape (строка);

Если перед образцом записано Like, то вместо образца может стоять знак:

· % - это означает произвольную последовательность символов;

· _ - это означает 1 любой символ.

Escape задает знак, который стоит перед % или_ и отменяет их метазначения.

Условие во фразе having может содержать агрегатные функции – это функции, которые применяются ко всем записям отобранной группы:

· Count ([distinct]) –используется для подсчета всех записей без учета дубликатов;

· Count (поле) – подсчитывает все значения в указанном поле;

· Sum (поле) – подсчитывает сумму в указанном поле;

· Avg (поле) – находит среднее значение;

· Min (поле) – находит минимальное значение в указанном поле;

· Max (поле) – находит максимальное значение в указанном поле.

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

Рассмотрим пример использования оператора поиска на примере БД о поставщиках, деталях и поставках.

1. Запрос - выдать информацию о всех деталях:

Select * from Детали 2 варианта

Select код_детали, вес, цвет, город from Детали

Звездочка * означает, что нужно выдать все поля из таблицы.

2. Выдать номера всех поставляемых деталей:

Select distinct код_детали from Поставки

3. Получит информацию о красных деталях, которые находятся в Лондоне:

Select * from Детали where цвет=’красный’ and город=‘Лондон’ order by вес DESC

Это конъюнктивный запрос, записи в ответе будут отсортированны по убыванию веса.

Запросы, которые реализуются на основе соединения.

Соединение таблиц выполняется в случае если во фразе from используется несколько таблиц, при этом во фразе where нужно указывать условие соединения.

Пример. Найти фамилии поставщиков, которые поставляют третью деталь:

Select фамилия from Поставщики 1, Поставки 2

Where 1.код-поставщика=2.код_поставщика and 2.код_детали=3

Подзапрос.

- это оператор Select, вложенный в предложение where, having или в другой select, либо в операторы insert, delete, update или в другой подзапрос.

Существует два способа обработки подзапросов:

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

· Некоррелированный – подзапрос вычисляется только один раз.

Существует три типа возвращаемых значений:

· Возвращается не одной или несколько строк, подзапрос связывается с основным запросом с помощью in, any, all;

· Возвращает только одну строку, подзапрос связывается с основным запросом с помощью =,<>, <, >, <=, >=;

· Тестовые запросы (exists), ничего не возвращается, просто проверяется существует ответ на запрос или нет, если существует ответ, то exists возвращает истина, иначе exists возвращает ложь.

Пример.

Найти фамилии поставщиков, которые поставляют деталь №3 с помощью подзапросов:

Select фамилия from Поставщики where код_поставщика in

(select код_поставщика from Поставки where код_детали=3)

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

Этот запрос является некоррелированным.

Если записать a in (b,c,d), то в результате обработки мы получим следующее a=b or a=c or a=d.

Теперь запишем тот же самый запрос так, чтобы он был коррелированным:

Select фамилия from Поставщики 1 where 3 in

(select код_детали from Поставки 2 where 1.код_поставщика=2.код_поставщика)

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

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

Пример.

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

С использованием соединения

Сначала нужно найти среднее количество

Select avg(количество) from Поставки

Полученный ответ обозначим .

Select название from Детали 1, поставки 2

Where 1юкод_детали=2.код_детали and 2.количество>

Теперь этот запрос запишем с помощью подзапроса (некоррелированного):

Select название from Детали 1, Поставки 2

Where 1.код_детали=2.код_детали and 2.количество >

(select avg(количество) from Поставки)

Обработкой подзапроса а not in (b,c,d) будет a b and a c and a d.

Пример.

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

Select фамилия from Поставщики 1

where 1.код_поставщика in

(select код-поставщика from Поставки 2

where код_детали in

(select код_детали from Детали

where цвет =‘красный’))

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

Связь осного запроса с подзапросом при помощи all и any:

Select …

From …

Where поле all (подзапрос)

…

поле any (подзапрос)

some,

где - это знак операции.

= all – в этом случае будут отобраны строки, в которых поле равно каждой строке подзапроса,

= all (1,2,3) поле должно быть =1 и =2 и =3;

> all – поле должно быть больше большего результата подзапроса,

> all (1,2,3) поле должно быть >1 и >2 и >3;

< all – возвращенное значение должно быть меньше меньшего результата подзапроса,

< all (1,2,3) поле должно быть <1 и <2 и <3;

> any – поле должно быть больше меньшего результата подзапроса;

< any – поле должно быть меньше большего результата подзапроса;

= any (1,2,3) – поле должно быть =1 или =2 или =3.

Пример.

Найти номера поставщиков, город которых не совпадает с каким-нибудь городом, где хранится деталь:

Select 1.код_поставщика from Поставщики 1

where 1.город any

(select 2.город from Детали 2)