Базы данных и субд

1.

БАЗЫ ДАННЫХ И СУБД
ПОВТОР

2.

ОСНОВНЫЕ ПОНЯТИЯ
• Информационная система совокупность технических и программных средств,
обеспечивающих сбор, хранение, обработку, поиск, выдачу информации в задачах любой
сложности.
• Предметная область часть реального мира, данные о которой хранятся и используются в
информационной системе.
• База данных (БД) именованная совокупность взаимосвязанных данных, отображающая
состояние объектов и их отношений в некоторой предметной области, используемых
несколькими пользователями.
• Система управления базами данных (СУБД) это совокупность языковых и программных
средств, предназначенных для управления созданием и использованием баз данных.

3.

ОСНОВНЫЕ ПОНЯТИЯ
Выделяют следующие функции СИСТЕМ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ :
1.
2.
3.
4.
5.
управление данными во внешней памяти;
управление данными в оперативной памяти;
управление транзакциями;
журнализация, резервное копирование и восстановление;
поддержка языков БД.

4.

ОСНОВНЫЕ ПОНЯТИЯ
Типовая организация современной СИСТЕМЫ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ:
1.
2.
3.
4.
Ядро СУБД (DATA BASE ENGINE);
Компилятор (процессор) языка БД (обычно SQL);
Подсистема поддержки времени выполнения;
Набор утилит.
Ядро отвечает за управление данными во внешней памяти, буферами ОП, транзакциями и журнализацию.
Процессор языка БД компилирует операторы языка баз данных в некоторую выполняемую программу,
представляемую в машинных кодах.
Реальное выполнение операторов БД производится с привлечением подсистемы поддержки времени
выполнения, представляющей собой интерпретатор этого внутреннего языка
В отдельные утилиты БД обычно выделяют такие процедуры, как например, загрузка и выгрузка БД, сбор
статистики, глобальная проверка целостности БД и т.д.

5.

ОСНОВНЫЕ ПОНЯТИЯ
Модель данных включает три аспекта:
1) Аспект структуры – методы описания типов и логических структур
данных в базе данных (логическое представление базы данных)
2) Аспект манипуляции – методы манипулирования данными (способы
модификации, способы извлечения данных из базы данных)
3) Аспект целостности – методы описания и поддержки целостности
базы данных (описываются механизмы ограничений целостности)

6.

ОСНОВНЫЕ ПОНЯТИЯ
К основным моделям данных относятся следующие:
Системы, основанные на инвертированных списках;
Иерархическая;
Сетевая;
Реляционная;
Постреляционная;
Многомерная;
Объектно-ориентированная.
Разрабатываются также всевозможные системы, основанные на других моделях данных,
расширяющих
известные:
объектно-реляционные,
дедуктивно-объектно-реляционные,
семантические, концептуальные, ориентированные

7.

ОСНОВНЫЕ ПОНЯТИЯ
Реляционная модель данных в некоторой предметной области представляет собой набор
отношений, изменяющихся во времени.
Отношение представляет собой двумерную таблицу, содержащую некоторые данные.
Атрибут отношения – свойство (характеристика) объекта предметной области, информация о
котором хранится в базе данных (обычно представляет собой заголовок некоторого столбца
таблицы).
Домен – допустимое ограниченное подмножество значений определенного (простого) типа
данных.
Кортеж – строка отношения.
Степень отношения – количество атрибутов отношения (столбцов).
Мощность отношения – количество кортежей отношения (строк).
Схема отношений – это именованное множество пар {имя атрибута, имя домена} (можно
представить как строку заголовков столбцов таблицы).

8.

ОСНОВНЫЕ ПОНЯТИЯ
Основные свойства отношений:
1. В отношении нет одинаковых кортежей.
2. Кортежи не упорядочены. Одно и тоже отношение может быть представлено разными
таблицами, в которых строки идут в разных порядках.
3. Атрибуты не упорядочены. Каждый атрибут имеет уникальное имя в пределах отношения,
порядок не имеет значения. Одно и тоже отношение может быть представлено разными таблицами,
в которых столбцы идут в разном порядке.
4. Все значения атрибутов атомарны. Это следует из того, что лежащие в их основе атрибуты
не могут быть множеством.

9.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Реляционная алгебра – это замкнутая система операций над отношениями в
реляционной модели данных, где результат каждой операции также является
отношением.
Операции можно разделить на две группы:
1. Базовые теоретико-множественные (объединение, пересечение,
вычитание, прямое произведение отношений);
2. Специальные реляционные (ограничение, проекция, деление,
соединение отношений)

10.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Также в состав алгебры включается операция присваивания, позволяющая
сохранить в базе данных результаты алгебраических выражений, и
операция переименования атрибутов, дающая возможность корректно
сформировать заголовок (схему) результирующего отношения.
Операции реляционной алгебры могут выполняться над одним
отношением (унарная операция) или над двумя отношениями
(бинарная операция). При выполнении бинарных операций отношения,
участвующие в операциях, должны быть совместимы по структуре.

11.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Объединением
(UNION)
двух
совместимых отношений одинаковой
размерности
является
отношение,
содержащее все элементы исходных
отношений с исключением повторений, т.е.
полученное отношение включает все
кортежи, которые входят хотя бы в одно из
отношений операндов/

12.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Пересечением
(INTERSECT)
двух
совместимых отношений одинаковой
размерности
порождает
отношение,
включающее в себя все кортежи, которые
входят в оба исходные отношения

13.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Вычитанием (MINUS) двух совместимых
отношений одинаковой размерности есть
отношение, состоящее из множества
кортежей,
принадлежащих
первому
отношению, но не входящих во второе
отношение.

14.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Произведением (TIMES) отношения R1 степени а1 и отношения R2 степени а2, которые не
имеют одинаковых имен атрибутов есть отношение R степени (а1 + а2), заголовок которого
представляет сцепление заголовков отношений R1 и R2, а тело имеет всевозможные
соединения кортежей R1 и R2, такие что первые а1 элементы принадлежат множеству R1 а
последние а2 элементы принадлежат множеству R2.
При
необходимости
получить
произведение
двух
отношений,
имеющих одинаковые имена одного
или нескольких атрибутов, применяется
операция переименования.

15.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Операция
ограничения
(выборка)
WHERE требует наличия двух
операндов:
ограничиваемого
отношения и простого условия
ограничения и представляет собой
новое отношения с таким же
заголовком и телом, состоящим из
кортежей
отношения,
которые
удовлетворяют
истинности
логического выражения.

16.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Проекция отношения А на атрибуты {а1,а2,…, аn}, где множество {а1,а2,…, аn} является
подмножеством полного списка атрибутов заголовка отношения А, представляет собой
отношение с заголовком {а1,а2,…, аn} и телом, содержащим кортежи отношения за
исключением повторяющихся кортежей.

17.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Делением отношения А на В
(DIVIDE
BY)
называют
отношение с заголовком а и
телом, содержащим множество
всех кортежей {a}, таких, что
существует кортеж {a, b} который
принадлежит отношению A для
всех кортежей {b}, принадлежащих
отношению B.
Результат содержит такие значения из
А, для которых соответствующие
значения b из отношения А
включают все значения b из
отношения B.

18.

Основы реляционной алгебры.
ОСНОВНЫЕ ПОНЯТИЯ
Соединением Cf (R1, R2)
отношений R1
и R2 по
условию
f,
представляет
собой отношение R, которое
можно
получить
путем
декартова
произведения
отношений R1
и R2 с
последующим применением
к
результату
операции
выборки по формуле f.
(R1 TIMES R2) WHERE Q,
где Q – логическое выражение

19.

Ключи.
ОСНОВНЫЕ ПОНЯТИЯ
Ключи представляют способ идентификации строк в таблице. С помощью
ключей можно связывать строки между различными таблицами в
отношения.
Первичным ключом называются атрибуты отношения (набор полей таблицы),
однозначно идентифицирующие каждый из его кортежей. Он должен
соответствовать следующим ограничениям:
Должен быть уникальным
Должен постоянно присутствовать и иметь значение
Не должен часто менять свое значение (в идеале он вообще
изменять значение)
не должен
Первичный ключ представляет один столбец таблицы, но также может быть составным и
состоять из нескольких столбцов. Если для таблицы можно выделить потенциальный ключ,
то его можно использовать в качестве первичного ключа.

20.

Ключи.
ОСНОВНЫЕ ПОНЯТИЯ
Потенциальный ключ – представляет собой минимальный ключ отношения
(таблицы), то есть набор атрибутов, который удовлетворяет ряду условий:
Неприводимость: он не может быть сокращен, он содержит минимально возможный набор
атрибутов
Уникальность: он должен иметь уникальные значения вне зависимости от изменения
строки
Наличие значения: он не должен иметь значения NULL, то есть он обязательно должен
иметь значение.
Какое поле (какие поля) могут быть потенциальным ключом?
Фамилия
Имя
Отчество
Дата рождения
Телефон

21.

Ключи.
ОСНОВНЫЕ ПОНЯТИЯ
Если есть несколько потенциальных ключей, то те потенциальные ключи,
которые не составляют первичный ключ, являются альтернативными
ключами.
Альтернативный ключ – это
атрибут (или группа атрибутов),
несовпадающий с первичным ключом и уникально идентифицирующий
экземпляр сущности. Атрибуты, составляющие альтернативный ключ,
однозначно (уникально) идентифицируют экземпляры сущности.

22.

Ключи.
ОСНОВНЫЕ ПОНЯТИЯ
Искусственный ключ – придуманный код, используемый для ссылки
на данные или объекты.
Суррогатный ключ – искусственно созданное ключевое поле, не
несущее информации об объектах.
Главное достоинство суррогатного ключа состоит в том, что он
практически никогда не меняется, поскольку не несёт никакой
информации из предметной области и, следовательно, в
минимальной степени зависит от изменений, происходящих в ней.

23.

Ключи.
ОСНОВНЫЕ ПОНЯТИЯ
Внешний ключ – это ключ, используемый для связи между таблицами.
Внешний ключ устанавливается для столбцов из зависимой,
подчиненной таблицы, и указывает на один из столбцов из главной
таблицы. Как правило, внешний ключ указывает на первичный ключ
из связанной главной таблицы.

24.

Проектирование реляционных БД.
ОСНОВНЫЕ ПОНЯТИЯ
Проектирование БД – это процесс, который для заданного набора
данных, относящихся к заданной предметной области, позволяет
выбрать и построить соответствующую оптимальную структуру.

25.

Модель «Сущность-связь».
ОСНОВНЫЕ ПОНЯТИЯ
ER-модель фактически является стандартом при концептуальном
моделировании баз данных.
Сущность – это некоторый объект реального мира, который может
существовать независимо.
Экземпляр сущности – это конкретный представитель данной
сущности.
Атрибут – это именованная характеристика, являющаяся некоторым
свойством сущности.

26.

Модель «Сущность-связь».
ОСНОВНЫЕ ПОНЯТИЯ
Связь – это отношение одной сущности к другой или к самой себе. Каждая
сущность может обладать любым количеством связей разного типа:
«один к одному» – 1:1
«один ко многим» – 1:М
«многие ко многим» – М:N
Связь «один к одному» – один экземпляр первой сущности связан только с
одним экземпляром второй сущности.
Связь «один ко многим» – каждый экземпляр первой сущности связан с
несколькими экземплярами второй сущности.
Связь «многие ко многим» – каждый экземпляр первой сущности может
быть связан с несколькими экземплярами второй сущности, и наоборот.

27.

Модель «Сущность-связь».
ОСНОВНЫЕ ПОНЯТИЯ
Каждая связь может иметь одну из двух модальностей связи (класс
принадлежности):
обязательный (должен)
необязательный (может)

28.

Логическое проектирование БД
ОСНОВНЫЕ ПОНЯТИЯ
При проектировании логической структуры БД осуществляется
преобразование исходной концептуальной модели в модель
данных, поддерживаемую конкретной СУБД и нахождение
оптимального проектного решения для конкретной предметной
области.
Для каждой сущности создается отношение, причем каждому
атрибуту сущности соответствует столбец отношения.

29.

ОСНОВНЫЕ ПОНЯТИЯ
Правила преобразования ERмодели
в
реляционную
модель данных
Правило №1: если связь типа 1:1 и класс принадлежности обеих сущностей
является обязательным, то необходимо только одно отношение.
Правило №2: если связь типа 1:1 и класс принадлежности одной сущности
является обязательным, а другой – необязательный, то необходимо построить
отношение для каждой сущности. Первичный ключ сущности, для которой класс
принадлежности является необязательным, добавляется как атрибут в отношение для сущности с
обязательным классом принадлежности.
Правило №3: если связь типа 1:1 и класс принадлежности обеих сущностей
является необязательным, то необходимо построить три отношения – по одному
для каждой сущности и одну для связи.

30.

ОСНОВНЫЕ ПОНЯТИЯ
Правила преобразования ERмодели
в
реляционную
модель данных
Правило №4:
если связь типа 1:М (определенная) и класс принадлежности
сущности на стороне M является обязательным, то необходимо построить
отношение для каждой сущности.
Правило №5: если связь типа 1:М (определенная) и класс принадлежности
сущности на стороне M является необязательным, то необходимо построить три
отношения – по одному для каждой сущности и одну для связи.
Правило №6: если связь типа M:N (неопределенная), то необходимо построить
три отношения – по одному для каждой сущности и одну для связи.

31.

Нормализация
ОСНОВНЫЕ ПОНЯТИЯ
Нормализация представляет процесс реорганизации данных путем
ликвидации избыточности и приведения базы данных к оптимальному
виду.
Метод нормализации основан на теории реляционных моделей данных. Исходной
точкой является представление предметной области в виде одного или нескольких
отношений, и на каждом шаге проектирования производится некоторый набор
таблиц, обладающих «улучшенными» свойствами. Т.е. осуществляется декомпозиция
отношений в два или более отношения, удовлетворяющих требованиях следующей
нормальной формы.
Декомпозиция таблицы – это процесс деления таблицы на несколько
таблиц с целью устранения избыточности.

32.

Нормализация
ОСНОВНЫЕ ПОНЯТИЯ
Первая нормальная форма (1NF)
Вторая нормальная форма (2NF)
Нормальная форма БД – это
Третья нормальная форма (3NF)
набор правил и критериев, которым
Нормальная форма Бойса-Кодда (BCNF) должна отвечать база данных.
Четвертая нормальная форма (4NF)
Пятая нормальная форма (5NF)
Доменно-ключевая нормальная форма (DKNF)
Шестая нормальная форма (6NF)
Обычно нормализацию проводят до третьей нормальной формы.

33.

Нормализация
ОСНОВНЫЕ ПОНЯТИЯ
Отношение находится в первой нормальной форме тогда и только тогда,
когда каждый атрибут атомарен и все строки различны.
Отношение находится во второй нормальной форме, если оно находится
в первой нормальной форме, и при этом любой атрибут, не входящий в
состав первичного ключа, функционально полно зависит от первичного
ключа. У каждого отношения во второй нормальной форме должен быть
первичный ключ.
Отношение находится в третьей нормальной форме, если оно находится
во второй нормальной форме и отсутствуют транзитивные
функциональные зависимости неключевых атрибутов от ключевых.
Необходимо выносить все неключевые поля, содержимое которых относится к
нескольким записям, в отдельные отношения.

34.

ЯЗЫК SQL
Язык SQL является первым и пока единственным стандартным языком
работы с базами данных, является непроцедурным языком.
Язык SQL может использоваться двумя способами:
1) Интерактивная работа, заключающаяся во вводе отдельных SQLоператоров
2) Внедрение SQL-операторов в программы на процедурных языках.
Выделяются две разновидности языка SQL: PL-SQL и T-SQL.
PL-SQL используется в CУБД Oracle.
T-SQL (Transact-SQL) применяется в SQL Server.

35.

ЯЗЫК SQL
SQL подразделяется:
язык определения данных – DDL – дает возможность создания, изменения и
удаления различных объектов БД (CREATE, ALTER, DROP)
язык манипулирования данными – DML – дает возможность выборки информации
из БД и ее преобразования (INSERT, UPDATE, DELETE, SELECT)
язык управления доступа к данным – DCL – дает возможность управлять доступом
к информации, находящейся внутри базы данных (GRANT, REVOKE)
язык контроля транзакций – TCL – дает возможность управлять транзакциями
(COMMIT, ROLLBACK, SET TRANSACTION)

36.

СУБД MICROSOFT SQL SERVER
SQL Server является одной из наиболее популярных систем управления базами
данных (СУБД) в мире.
В SQL Server используется два типа баз данных: системные и пользовательские.
1. Пользовательские базы данных создаются пользователями сервера и могут хранить
любую произвольную информацию. Их можно изменять и удалять, создавать
заново.

37.

СУБД MICROSOFT SQL SERVER
2. Системные базы данных необходимы серверу SQL для корректной работы. В MS
SQL Server по умолчанию создается четыре системных баз данных:
master: эта главная база данных сервера, в случае ее отсутствия или повреждения сервер не
сможет работать. Она хранит все используемые логины пользователей сервера, их роли,
различные конфигурационные настройки, имена и информацию о базах данных, которые
хранятся на сервере, а также ряд другой информации.
model: эта база данных представляет шаблон, на основе которого создаются другие базы
данных. То есть пользовательская БД создается как копия базы model.
msdb: хранит информацию о работе, выполняемой таким компонентом как планировщик
SQL. Также она хранит информацию о бекапах баз данных.
tempdb: эта база данных используется как хранилище для временных объектов. Она заново
пересоздается при каждом запуске сервера.

38.

СУБД MICROSOFT SQL SERVER
Типы данных в SQL Server объединены в следующие категории:
Точные числа (bigint, int, smallint, tinyint, numeric, decimal, money, smallmoney, bit)
Приблизительные числа (float, real)
Дата и время (date, time, datetime, datetime2, smalldatetime, datetimeoffset)
Символьные строки (char, varchar)
Символьные строки в Юникоде (nchar, nvarchar)
Двоичные данные (binary, varbinary)
Прочие типы данных

39.

СУБД MICROSOFT SQL SERVER
Для создания любого объекта базы данных используется команда CREATE:
CREATE DATABASE Biblioteka
GO
USE Biblioteka
GO
CREATE TABLE Autor
Для удаления любого объекта используется команда DROP:
DROP DATABASE Biblioteka
Для изменения любого объекта используется команда ALTER:
ALTER TABLE Customers DROP COLUMN Address

40.

СУБД MICROSOFT SQL SERVER
Создание таблицы:
CREATE TABLE Test
(
Id_test int IDENTITY,
dDate DATE,
iApartment INT DEFAULT 1,
cPol NCHAR(1) DEFAULT ‘M’,
Price MONEY NOT NULL,
vc_Phone VARCHAR(13) NOT NULL
CONSTRAINT pk_ID PRIMARY KEY (Id_test),
CONSTRAINT fk_iApartment FOREIGN KEY
(iApartment) REFERENCES Customers (Id) ON DELETE CASCADE,
CONSTRAINT ch_Price CHECK (Price BETWEEN 1 and 1000),
CONSTRAINT ch_dDate CHECK (dDate<getdate()),
CONSTRAINT ch_cPol CHECK (cPol IN ('M', 'Ж'))
CONSTRAINT uq_vcPhone UNIQUE (vc_Phone)
)

41.

СУБД MICROSOFT SQL SERVER
Обеспечение целостности данных:
1.Разрешение или запрещение введения нулевых значений (NULL).
2.Ограничение целостности данных (CHECK)
3.Ограничение уникальности (UNIQUE)
4.Задание первичного ключа (PRIMARY KEY)
5.Задание ссылочной целостности (FOREIGN KEY)

42.

СУБД MICROSOFT SQL SERVER
Параметры ссылочной целостности для внешнего ключа:
CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или
изменении связанных строк в главной таблице.
NO ACTION: предотвращает какие-либо действия в зависимой таблице при удалении или
изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
SET NULL: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего
ключа значение NULL.
SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для столбца
внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для
столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.

43.

СУБД MICROSOFT SQL SERVER
Для добавления данных в таблицу:
INSERT Test VALUES (’22/12/2000’,1,’M’,150, ’+375291234567’)
INSERT INTO Test(vc_Phone,Price)
VALUES (’+375291234567’, 250)

44.

СУБД MICROSOFT SQL SERVER
Для удаления данных из таблицы:
DELETE Test
DELETE FROM Test WHERE cPol=‘M’

45.

СУБД MICROSOFT SQL SERVER
Для изменения данных в таблице:
UPDATE Test SET Price=Price+500
UPDATE Test
SET Price=Price+500
WHERE cPol=‘M’
UPDATE TOP(3) Test
SET Price=Price+500, cPol=‘M’

46.

СУБД MICROSOFT SQL SERVER
Общая структура запроса SELECT:

47.

СУБД MICROSOFT SQL SERVER
SELECT * FROM Test
SELECT dDate, cPol ,T vc_Phone FROM Test
WHERE YEAR(dDate)=2000
SELECT TOP 3 dDate AS ‘Дата’,cPol AS ‘Пол’, T vc_Phone,
Price * 10 AS Оплата
FROM Test WHERE MONTH(dDate)=3

48.

СУБД MICROSOFT SQL SERVER
Для получения данных из таблицы:
SELECT DISTINCT dDate FROM Test
SELECT TOP 25 PERCENT dDate,cPol , vc_Phone FROM Test
ORDER BY Price DESC
SELECT ALL * FROM Test
ORDER BY Price
OFFSET 2 ROWS
FETCH NEXT 5 ROWS ONLY

49.

СУБД MICROSOFT SQL SERVER
Для получения данных из таблицы:
SELECT dDate,cPol , vc_Phone FROM Test
WHERE Price BETWEEN 100 AND 500
SELECT dDate,cPol , vc_Phone FROM Test
WHERE cPol =‘M’ AND vc_Phone LIKE ‘+375[2-4]%’

50.

СУБД MICROSOFT
SQL SERVER

51.

СУБД MICROSOFT SQL SERVER

52.

СУБД MICROSOFT SQL SERVER
SELECT Employees.ID, Employees.Name, Employees.DepartmentID,
Departments.ID, Departments.Name
FROM Employees
JOIN Departments ON Employees.DepartmentID=Departments.ID
SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name
FROM Employees emp
JOIN Departments dep ON emp.DepartmentID=dep.ID

53.

СУБД MICROSOFT SQL SERVER
SELECT e.ID, e.Name EmployeeName, p.Name PositionName, d.Name
DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
LEFT JOIN Positions p ON e.PositionID=p.ID
SELECT e.ID, e.Name EmployeeName, d.Name DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID=d.ID
WHERE e.Name IS NULL

54.

СУБД MICROSOFT SQL SERVER
SELECT Name,
CASE PositionID
WHEN 1 THEN ‘АСУ’
WHEN 2 THEN ‘ИТ’
WHEN 3 THEN ‘СТУ’
END AS ‘Отдел’
FROM Employees
SELECT Name, Salary,
CASE
WHEN Salary>2500 THEN ‘Высокая зарплата’
WHEN Salary BETWEEN 1500 AND 2500 THEN ‘Хорошая зарплата’
WHEN Salary BETWEEN 1000 AND 1499 THEN ‘Просто зарплата’
ELSE ‘Минимальная зарплата’
END AS ‘Примечание’
FROM Employees

55.

СУБД MICROSOFT SQL SERVER
Неявное соединение таблиц:
SELECT *
FROM Employees, Departments
WHERE Employees.DepartmentID=Departments.ID
SELECT e.ID, e.Name, p.Name, d.Name
FROM Employees AS e, Departments AS d, Positions AS p
WHERE e.DepartmentID=d.ID AND e.PositionID=p.ID

56.

СУБД MICROSOFT SQL SERVER
Агрегатные функции выполняют вычисления над значениями в наборе строк.
В T-SQL имеются следующие агрегатные функции:
AVG: находит среднее значение
SUM: находит сумму значений
MIN: находит наименьшее значение
MAX: находит наибольшее значение
COUNT: находит количество строк в запросе
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Следует различать два случая применения агрегатных функций.
Первый: агрегатные функции используются сами по себе и возвращают одно
результирующее значение.
Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с
группировкой по полям (столбцам) для получения результирующих значений в каждой
группе.

57.

СУБД MICROSOFT SQL SERVER
SELECT MAX(Salary)
FROM Employees
SELECT COUNT(BonusPersent), AVG(Salary), COUNT(*)
FROM Employees

58.

СУБД MICROSOFT SQL SERVER
SELECT p.Name, MAX(e.Salary) AS Максимум
FROM Employees e
JOIN Positions p ON e.PositionID=p.ID
GROUP BY p.Name
SELECT d.Name, COUNT(*)
FROM Employees e
JOIN Departmens d ON e.PositionID=d.ID
GROUP BY d.Name
HAVING COUNT(*)>1

59.

СУБД MICROSOFT SQL SERVER
T-SQL поддерживает функциональность подзапросов (subquery), то есть таких
запросов, которые могут встроены в другие запросы.
Первая инструкция SELECT подзапроса называется внешним запросом (outer query), а
внутренняя инструкция (или инструкции) SELECT, используемая в сравнении,
называется вложенным запросом (inner query). Первым выполняется вложенный
запрос, а его результат передается внешнему запросу. Вложенные запросы также могут
содержать инструкции INSERT, UPDATE и DELETE.
Существует два типа подзапросов: независимые и связанные. В независимых
подзапросах вложенный запрос логически выполняется ровно один раз. Связанный
запрос отличается от независимого тем, что его значение зависит от переменной,
получаемой от внешнего запроса. Таким образом, вложенный запрос связанного
подзапроса выполняется каждый раз, когда система получает новую строку от
внешнего запроса.

60.

СУБД MICROSOFT SQL SERVER
Подзапросы (вложенные запросы), возвращающие единственное значение, наиболее
часто применяются в случаях, когда значение определённого столбца в основном
запросе требуется сравнить с некоторым единственным значением при помощи
одного из операторов сравнения (=, <, >, <=, >=). Значение, с которым производится
сравнение, как раз и возвращается подзапросом (вложенным запросом).
Признаки того, что подзапрос вернёт одно единственное значение:
в подзапросе применяется одна из агрегатных функций (COUNT, SUM, AVG, MAX,
MIN);
подзапрос извлекает значение уникального идентификатора.
В остальных случаях нужно быть полностью уверенным, что условиям, указанным в
секции WHERE подзапроса, соответствует единственное значение выбираемого столбца.

61.

СУБД MICROSOFT SQL SERVER
Независимый подзапрос может применяться со следующими операторами:
операторами сравнения;
оператором IN;
операторами ANY и ALL.
В общем случае запрос возвращает множество значений. Поэтому использование
подзапроса в предложении WHERE без предикатов EXISTS, IN, ALL и ANY, может
привести к ошибке времени выполнения запроса.
SELECT Name, Birthday
FROM Employees
WHERE DepartamentID = (SELECT ID
FROM Departments
WHERE Name = 'Бухгалтерия')

62.

СУБД MICROSOFT SQL SERVER
SELECT Name, Birthday
FROM Employees
WHERE DepartamentID = (SELECT ID
FROM Departments
WHERE Name = 'Бухгалтерия')
SELECT Name
FROM Departments
WHERE ID = IN (SELECT DepartamentID
FROM Employees
WHERE DepartamentID > 3)

63.

СУБД MICROSOFT SQL SERVER
Подзапрос, возвращающий результат или результаты, для получения которых
значения указанного столбца должны соотноситься со значениями столбцов,
указанных в основном запросе, называется коррелирующим (связанным).
То есть, результат выполнения подзапроса зависит от значений, возвращаемых
основным запросом. Часто коррелирующие подзапросы применяются для получения
значений одного из столбцов результирующей таблицы и в этих случаях подзапрос,
заключённый в скобки, перечисляется через запятую вместе с именами столбцов из
таблиц или соединения таблиц.

64.

СУБД MICROSOFT SQL SERVER
SELECT Name, (SELECT AVG(Salary) FROM Employees DepEmpl
WHERE DepEmlp.DepartamentID=Empl.DepartamentID) AS ’Среднее’
FROM Employees Empl
WHERE Salary > (SELECT AVG(Salary) FROM Employees DepEmpl
WHERE DepEmlp.DepartamentID=Empl.DepartamentID)

65.

СУБД MICROSOFT SQL SERVER
Вложенные запросы:
DELETE Test FROM
(SELECT TOP 2 * FROM Test
WHERE cPol=‘M’) AS Selected
WHERE Test.Id = Selected.Id