Основные понятия теории баз данных.

 

Пример. Увеличим Воронину оклад на 10%:

UPDATE Сотрудники SET Оклад = Оклад  * 1.1

WHERE ТабНомер = 7738;

 

Пример. Увеличить оклад аналитиков и экономистов отдела 20 на 15%:

 

UPDATE Сотрудники  SET Оклад = Оклад*1,15

WHERE (Должность = ‘Аналитик’ OR Должность  = ‘Экономист’) AND Отдел = 20;

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

 

Пример. Увеличить оклад сотрудников, содержащихся в таблице “Список”:

 

UPDATE Сотрудники SET Оклад = Оклад*1,05

WHERE Фамилия IN (SELECT Фамилия FROM Список)

Предложение SET тоже может содержать  запрос.

 

Пример. Установить оклад экономистам  как у сотрудника с номером 7456:

 

UPDATE Сотрудники  SET Оклад =

( SELECT Оклад FROM Сотрудники WHERE Табномер = 7456)

WHERE Должность = ‘Экономист’;

DELETE (Удаление записей).

 

Для удаления записей используется оператор DELETE, имеющий формат:

 

DELETE FROM <имя таблицы>

WHERE <условие отбора>;

 

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

 

Пример. Удалить из таблицы “Сотрудники” запись об экономисте Воронине:

 

DELETE FROM Сотрудники 

WHERE Табномер = 7738

 

Или

 

DELETE FROM Сотрудники

WHERE Фамилия = ‘Воронин’  AND Должность  = ‘Экономист’

Оператор SELECT.

 

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

 

Представления – это просто хранящиеся в БД SELECT – выражения.

 

С точки зрения пользователя представление  – это таблица, которая постоянно  не хранится в БД, а возникает  при обращении.

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

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

Оператор SELECT имеет следующий формат:

SELECT [DISTINCT]

<Список полей> или *

FROM <Список таблиц>

[WHERE <Условие отбора>]

[ORDER BY <Список полей для сортировки>]

[GROUP BY <Список полей для группирования>]

[HAVING <Условие группирования>]

[UNION <Вложенный оператор SELECT>]

 

Выбрать все поля

SELECT *

FROM <имя таблицы>

 

Выбор определенных полей

SELECT <имя столбца 1>,…,<имя  столбца n>

FROM <имя таблицы>

 

Пример. Выбрать все поля таблицы «Сотрудники»:

 

SELECT *

FROM Сотрудники

 

Пример. Выбрать определенные поля таблицы «Сотрудники»:

 

SELECT Табномер, Фамилия, Отдел 

FROM Сотрудники

 

Выбор полей, удовлетворяющих некоторому условию:

 

SELECT <имя столбца 1>,…,<имя  столбца n>

FROM <имя таблиц(ы)>

WHERE <условие отбора>;

 

Пример. В результате запроса выдать фамилию и отдел работы всех сотрудников  с должностью ‘Заведующий’:

 

SELECT Отдел, Фамилия

FROM Сотрудники

WHERE Должность = “'Заведующий”

Модификатор DISTINCT (предотвращение выборки повторяющихся слов).

 

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

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

 

SELECT [DISTINCT] <имя столбца 1>,…,< имя столбца n>

FROM <имя таблиц(ы)>

WHERE <условие отбора>;

 

Пример. Выбрать все должности таблицы “Сотрудники”:

SELECT DISTINCT Должность

FROM Сотрудники;

ORDER BY (упорядочение строк в результате  запроса).

 

Операнд ORDER BY содержит список полей, определяющих порядок сортировки записей  результирующего набора данных. По умолчанию сортировка по каждому полю выполняется в порядке возрастания значений. Если необходимо задать для поля сортировку по убыванию, то после имени этого поля указывается описатель DESC.

 

ORDER BY <порядок строк> [ASC | DESC]

 

Операнд ORDER BY всегда должен следовать  за WHERE. Порядок строк может задаваться одним из двух способов:

 

именами столбцов

номерами столбцов.

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

 

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

 

SELECT *

FROM Сотрудники

ORDER BY ТабНомер ASC | DESC;

 

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

 

SELECT Отдел, Фамилия

FROM Сотрудники

WHERE Должность = ‘Заведующий’

ORDER BY 2

 

Пример. Выдать все записи таблицы  «Сотрудники», при этом записи упорядочить  по фамилии, а затем по табельному номеру в возрастающем порядке:

 

SELECT *

FROM Сотрудники

ORDER BY Фамилия, ТабНомер ASC;

Использование псевдонимов (alias).

 

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

Псевдонимы (алиасы или имя корреляции) вводится в разделе FROM и пишутся через пробел после имени таблицы.

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

 

 

 

Пример:

SELECT S.Фамилия, O.НомОтд

FROM Сотрудники S, Отдел O

 

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

 

Пример:

SELECT S1.Фамилия AS Фамилия1, S2.Оклад  AS ОкладМаленький

FROM Сотрудники S1, Сотрудники S2

WHERE S1.Оклад < S2.Оклад

 

4. Арифметические выражения.

 

Арифметические выражения в WHERE.

В WHERE формируется условие соединения таблиц, либо условие выбора строк.

 

Пример:

SELECT Фамилия, Оклад, Премия

FROM Сотрудники

WHERE Премия > 0,25*Оклад

 

Пример. Получить список инженеров  в убывающем порядке отношения  Премия/Оклад:

 

SELECT Фамилия, Оклад, Премия, Премия/Оклад

FROM Сотрудники

ORDER BY Премия/Оклад,DESC

 

 

 

 

5. Групповые функции.

 

К группам строк , извлеченным в  результате запроса можно применить  групповые функции:

 

AVG, COUNT, MAX, MIN, SUM

 

Пример. Определить средний оклад экономистов:

SELECT AVG(Оклад)

FROM Сотрудники

WHERE Должность = ‘Экономист’

 

Пример. Определить общий оклад  и общую премию у всех инженеров:

SELECT SUM(Вклад), SUM(Премия)

FROM Сотрудники

WHERE Должность = ‘Инженер’

 

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

Пример:

SELECT AVG(Оклад+Премия)*12

FROM Сотрудники

WHERE Должность = ‘Инженер’

 

Пример. Определить в организации  минимальные и максимальные оклады, и их разницу:

SELECT MAX(Оклад), MIN(Оклад), MAX(Оклад) - MIN(Оклад) AS Результат

 

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

 

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

SELECT Фамилия, Оклад

FROM Сотрудники

WHERE Оклад = ( SELECT МАХ(Оклад) FROM Сотрудники)

 

Тот же результат выдаст и следующий  запрос:

SELECT Фамилия, MAX(Оклад).

Пример. Подсчитать  количество сотрудников 30-го отдела, получающих премию:

SELECT COUNT(Премия)

FROM Сотрудники

WHERE Отдел = 30 AND (Премия >0 OR Премия NOT NULL)

 

COUNT можно использовать со словом DISTINCT

 

Пример. Подсчитать количество должностей сотрудников 30-го отдела:

SELECT COUNT (DISTINCT Должность)

FROM Сотрудники

WHERE Отдел = 30

 

COUNT(*) подсчитывает количество строк, удовлетворяющих условиям поиска выбора.

 

Пример:

SELECT COUNT(*)

FROM Сотрудники

WHERE Отдел = 30

 

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

GROUP BY служит для выборки итоговых  данных по группам:

 

Пример. Определить средний оклад  сотрудников отделов № 10, №20, №30:

SELECT  AVG(Оклад)

FROM Сотрудники

GROUP BY Отдел = 10 (20,30)

 

GROUP BY делит таблицу на группу  строк с совпадающими значениями  в одном и том же столбце  или столбцах.

 

Пример. Выбрать номера отделов и средний оклад по отделу:

SELECT Отдел AVG(Оклад)

FROM Сотрудники

GROUP BY Отдел 

 

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

Предложение GROUP BY всегда должно следовать  за WHERE, если оно есть, а если WHERE нет  – то за FROM

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

 

Пример:

SELECT Отдел, Должность, COUNT(*), AVG(Оклад)

FROM Сотрудники

GROUP BY Отдел, Должность

 

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

SELECT НазвОтд, Должность, COUNT(*), AVG(Оклад)

FROM Сотрудники, Отделы

WHERE Сотрудники.Отдел = Отделы.Отдел

GROUP BY НазвОтд, Должность

Предложение HAVING.

 

Может задавать условие поиска для  групп (как для отдельных строк SELECT):

 

HAVING поле GROUP BY

Здесь сравниваются атрибуты групп

 

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

SELECT Должность, COUNT(*), AVG(Оклад)*12

FROM Сотрудники

GROUP BY Должность

HAVING COUNT(*)>2

 

В данном запросе могут присутствовать как WHERE так и  HAVING. Сначала применяется WHERE. Из оставшихся строк формируется  группа в соответствии с GROUP BY. К полученным группам применяется предложение HAVING.

Пример. Составить список отделов, в которых работают по крайней мере два инженера:

SELECT Отдел

FROM Сотрудники

WHERE Должность = ‘Инженер’

GROUP BY Отдел

HAVING COUNT(*) > 2

 

Предложение HAVING может сравнивать различные атрибуты групп.

 

Пример. Составить список отделов, средняя премия в которых превышает 85% оклада:

SELECT Отдел, AVG(Оклад), AVG(Премия), AVG(Оклад) + 0,25

FROM Сотрудники

GROUP BY Отдел

HAVING AVG(Премия)> 0.25*AVG(Оклад)

 

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

 

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

SELECT Должность, AVG(Оклад)

FROM Сотрудники

GROUP BY Должность

HAVING AVG(Оклад) > (SELECT AVG(Оклад)

FROM Сотрудники

WHERE Должность = ‘Заведующий’)

 

 

 

 

 

 

 

 

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

 

Подзапрос – это дополнительный метод манипулирования с несколькими таблицами. Это оператор SELECT, вложенный:

 

в предложение WHERE, HAVING или SELECT другого  оператора SELECT;

в оператор INSERT, UPDATE или DELETE

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

 

Именно возможность вложения операторов SQL  друг в друга является причиной, по которой SQL первоначально был  назван Structured Query Language. Термин подзапрос  часто используется для ссылки на всю совокупность операторов, которая  включает один или несколько подзапросов, а также на отдельное вложение. Каждый включающий оператор – следующий по старшинству уровень в подзапросе – представляет собой внешний уровень для внутреннего подзапроса.

 

Упрощенный синтаксис подзапроса.

 

Условия поиска, относящиеся к другому запросу, также могут встречаться в предложении WHERE внешнего запроса – до или после внутреннего запроса.

 

SELECT [DISTINCT]
<Список полей> или *
FROM <Список таблиц>	Начало внешнего оператора SELECT
WHERE
{выражение { [NOT] IN | оператор сравнения [ANY | ALL] } | [NOT] EXISTS}
(SELECT [DISTINCT] список выбора подзапроса		Подзапрос
FROM <Список таблиц>		заключенный
WHERE условия )		в скобки.
[GROUP BY <Список полей для группирования>]
[HAVING <Условие группирования>]
[ORDER BY <Список полей для сортировки>]

 

Как работает подзапрос?

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

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