Файл: Сбора, хранения и манипулирования.pdf

Базы данных

План
• Введение
• База данных
• Запросы
• DDL
• DML
• Объекты баз данных
• ER-диаграмма

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

Архитектура «клиент-сервер»

База данных
• БД, DB (Database). Организованная структура, предназначенная для хранения, изменения и обработки взаимосвязанной информации, преимущественно больших объемов
• База данных позволяет хранить большое количество информации в одном месте. Качественно спроектированная база станет опорой для дальнейшей работы с данными, а кроме того, значительно облегчит ваш труд.
• При этом, вся информация не хранится в каком-то обобщенном виде, а разбивается на таблицы, каждая из которых отвечает за определенный объект предметной области. Чем больше данные обособляются в таблицы, тем выше вероятность избежать дублирования информации и захламления базы данных, а также сокращает время и ресурсы на поиск необходимых данных

СУБД
• СУБД, Database Management System. Комплекс программ, позволяющих создать базу данных (БД) и манипулировать данными (вставлять, обновлять, удалять и выбирать)

Создание БД
• Сущности (таблицы)
• Атрибуты и типы данных
• Первичные ключи
• Связи
• Автоинкременты
• Ограничения
• Наименования

Типы данных
BIT: хранит значение 0 или 1. Фактически является аналогом булевого типа в языках программирования. Занимает 1 байт.
SMALLINT: хранит числа от –32 768 до 32 767. Занимает 2 байта
INT: хранит числа от –2 147 483 648 до 2 147 483 647. Занимает 4 байта.
Наиболее используемый тип для хранения чисел.
BIGINT: хранит очень большие числа от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, которые занимают в памяти 8 байт.
DECIMAL: хранит числа c фиксированной точностью. Занимает от 5 до 17 байт в зависимости от количества чисел после запятой. Данный тип может принимать два параметра precision и scale: DECIMAL(precision, scale).

Типы данных
SMALLMONEY: хранит дробные значения от -214 748.3648 до 214 748.3647. Предназначено для хранения денежных величин. Занимает 4 байта. Эквивалентен типу DECIMAL(10,4).
MONEY: хранит дробные значения от -922 337 203 685 477.5808 до 922 337 203 685 477.5807. Представляет денежные величины и занимает 8 байт. Эквивалентен типу DECIMAL(19,4).
FLOAT: хранит числа от –1.79E+308 до 1.79E+308. Занимает от 4 до 8 байт в зависимости от дробной части. Может иметь форму определения в виде FLOAT(n), где n представляет число бит, которые используются для хранения десятичной части числа (мантиссы). По умолчанию n = 53.
REAL: хранит числа от –340E+38 to 3.40E+38. Занимает 4 байта.
Эквивалентен типу FLOAT(24).

Типы данных
Дата и время
DATE: хранит даты от 0001-01-01 (1 января 0001 года) до 9999-12-31
(31 декабря 9999 года). Занимает 3 байта.
TIME: хранит время в диапазоне от 00:00:00.0000000 до
23:59:59.9999999. Занимает от 3 до 5 байт. Может иметь форму TIME(n), где n представляет количество цифр от 0 до 7 в дробной части секунд.
DATETIME: хранит даты и время от 01/01/1753 до 31/12/9999.
Занимает 8 байт.

Типы данных
Строковые типы данных
CHAR: хранит строку длиной от 1 до 8 000 символов. На каждый символ выделяет по 1 байту. Не подходит для многих языков, так как хранит символы не в кодировке Unicode. Количество символов, которое может хранить столбец, передается в скобках. Например, для столбца с типом CHAR(10) будет выделено 10 байт. И если мы сохраним в столбце строку менее 10 символов, то она будет дополнена пробелами.
VARCHAR: хранит строку. На каждый символ выделяется 1 байт. Можно указать конкретную длину для столбца - от 1 до 8 000 символов, например, VARCHAR(10). Если строка должна иметь больше 8000 символов, то задается размер MAX, а на хранение строки может выделяться до 2 Гб: VARCHAR(MAX). Не подходит для многих языков, так как хранит символы не в кодировке Unicode.

Типы данных
В отличие от типа CHAR, если в столбец с типом VARCHAR(10) будет сохранена строка в 5 символов, то в столбце будет сохранено именно пять символов.
NCHAR: хранит строку в кодировке Unicode длиной от 1 до 4 000 символов. На каждый символ выделяется 2 байта. Например, NCHAR(15)
NVARCHAR: хранит строку в кодировке Unicode. На каждый символ выделяется 2 байта. Можно задать конкретный размер от 1 до 4 000 символов: . Если строка должна иметь больше 4000 символов, то задается размер MAX, а на хранение строки может выделяться до 2 Гб.
Еще два типа TEXT и NTEXT являются устаревшими и поэтому их не рекомендуется использовать. Вместо них применяются VARCHAR и
NVARCHAR соответственно.

Ограничения
• No Action (бездействие)
• Cascade (каскад)
• SET NULL (установить NULL)
• SET Default (установить по умолчанию)

Ограничения
SET NULL – при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, значения внешнего ключа в таблице-потомке устанавливаются в NULL.
NO ACTION — при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке никаких действий предприниматься не будет.
CASCADE – при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, автоматически удаляются или обновляются записи со ссылками на это значение в таблице-потомке.
SET DEFAULT –при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке соответствующим записям будет выставлено значение по умолчанию.
Есть одно “НО”. В РСУБД MySQL это ключевое слово не используется.

Ограничения
• Если мы хотим удалить операцию, единицу измерения, ингредиент, категорию – Запрет (No Action)
• Если мы хотим удалить блюдо, то должны удалиться и все этапы его приготовления – Каскадное удаление (Cascade)
• Если мы хотим удалить этап, то должны удалиться и все его ингредиенты – Каскадное удаление (Cascade)

Объекты баз данных
• Представление (View): виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных.
• Хранимая процедура (Stored Procedure): выполняют код на языке SQL по отношению к данным к БД (например, получает данные или изменяет их)
• Функция (Function): также код SQL, который выполняет определенную задачу
• Триггер (Trigger): специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд
INSERT, UPDATE, DELETE.

Запросы
• Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных
(RDBMS)
• С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и
TCL.

DDL – Data Definition Language
Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.
В эту группу входят следующие операторы:
• CREATE – используется для создания объектов базы данных;
• ALTER – используется для изменения объектов базы данных;
• DROP – используется для удаления объектов базы данных.

DML – Data Manipulation Language
Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.
В эту группу входят самые распространённые операторы языка SQL:
• SELECT – осуществляет выборку данных;
• INSERT – добавляет новые данные;
• UPDATE – изменяет существующие данные;
• DELETE – удаляет данные.

DCL – Data Control Language
Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.
Сюда входят:
• GRANT – предоставляет пользователю или группе разрешения на определённые операции с объектом;
• REVOKE – отзывает выданные разрешения;
• DENY – задаёт запрет, имеющий приоритет над разрешением.

TCL – Transaction Control Language
Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.
Группа операторов TCL предназначена как раз для реализации и управления транзакциями. Сюда можно отнести:
• BEGIN TRANSACTION – служит для определения начала транзакции;
• COMMIT TRANSACTION – применяет транзакцию;
• ROLLBACK TRANSACTION – откатывает все изменения, сделанные в контексте текущей транзакции;
• SAVE TRANSACTION – устанавливает промежуточную точку сохранения внутри транзакции.

Создание БД
CREATE DATABASE
[TestSQL]
GO / зависит от способа работы

Создание таблицы
CREATE TABLE
Customer
(
Id
INT PRIMARY KEY
,
Age
INT
,
FirstName
NVARCHAR
(
50
),
LastName
NVARCHAR
(
50
),
Email
VARCHAR
(
255
),
Phone
VARCHAR
(
20
)
)

Удаление таблицы
DROP TABLE
Customer

Удаление базы данных
DROP DATABASE
[TestSQL4]

Автоинкремент, nullable, уникальность
CREATE TABLE
Customer
(
Id
INT PRIMARY KEY IDENTITY
,
Age
INT
,
FirstName
NVARCHAR
(
50
) NOT NULL,
LastName
NVARCHAR
(
50
) NOT NULL,
Email
VARCHAR
(
255
)
UNIQUE
,
Phone
VARCHAR
(
20
)
UNIQUE
)

Добавить столбец
ALTER TABLE
Customer
ADD Address NVARCHAR
(
500
) NULL;

Удалить столбец
ALTER TABLE
Customer
DROP COLUMN Address
;

Изменить столбец
ALTER TABLE
Customer
ALTER COLUMN
FirstName
NVARCHAR
(
200
);

Создать внешний ключ
CREATE TABLE
[Order]
(
Id
INT PRIMARY KEY IDENTITY
,
CustomerId
INT
,
CreatedAt
Date
,
FOREIGN KEY
(
CustomerId
)
REFERENCES
Customer
(
Id
)
);

ПРАКТИКА
• Создание БД
• SSMS (создание объектов БД)
• Выгрузка скрипта
• Создание backup
• Загрузка скрипта
• Восстановление backup