378 lines
16 KiB
Markdown
378 lines
16 KiB
Markdown
# Базы данных
|
||
|
||
## Основы
|
||
|
||
### Основные определения
|
||
|
||
*База данных* - набор структурированной информации или данных, которые хранятся в компьютерной системе
|
||
|
||
*СУБД* - система управления БД
|
||
|
||
*SQL* - основной интерфейс работы с реляционными базами данных
|
||
|
||
*Транзакция* - Последовательность операция над БД, рассматриваемых СУБД, как одно целое
|
||
|
||
### Классификация
|
||
|
||
* Простейшие базы двнных
|
||
* SQL базы данных (реляционные)
|
||
* NoSQL базы данных (нереляционные)
|
||
* Комбинированные типы
|
||
|
||
### Реляционные базы данных
|
||
|
||
> Набор данных с предопределенными связями между ними. Эти данные организованны в виде набора таблиц, состоящих из столбцов и строк. В таблицах хранится информация об объектах, представленных в виде данных
|
||
|
||
### NoSQL базы данных
|
||
|
||
> База данных, в которой не используется табличная система строк и столбцов
|
||
|
||
### Основные функции
|
||
|
||
* Управление данными во внешней памяти
|
||
* Управление данными в оперативной памяти
|
||
* Управление транзакциями
|
||
* Ведение журнала выполнения операций в базе данных
|
||
* поддержка языков баз данных
|
||
|
||
### Основные БД
|
||
|
||
* SQL
|
||
* Oracle (Вы любите деньги больше, чем надо)
|
||
* Microsoft SQL Server (Вы любите деньги больше, чем надо)
|
||
* PostgreSQL (Халява)
|
||
* NoSQL
|
||
* MongoDB
|
||
* DB2
|
||
* Cassandra
|
||
|
||
### Первичный и внеший ключ
|
||
|
||
* *Ключ* - это колонка или колонки,не имеющие дублирующих значений.
|
||
|
||
* *Первичный ключ* - это поле или набор полей со значением, которые являются уникальными для всей таблицы.
|
||
|
||
* *Естественные* являются свойствами моделируемого объекта интересными сами по себе, даже если никто не стремится сделать из них ключ
|
||
|
||
* *Искусственные (суррогатные)* - придуманные коды для ссылки на данные или объекты
|
||
|
||
* *Внешний ключ* - столбец или группа столбцов, используемых в SQL БД для связи данных между таблицами
|
||
|
||
### Типы связи в SQL БД
|
||
|
||
1. "Один к одному"
|
||
|
||
Образуется когда ключевой столбец присутствует в другой таблице, в которой тоже является ключом либо свойствами столбца задана его уникальность. Используется, когда нужно разделить данные одной таблицы на несколько отдельных таблиц.
|
||
|
||
2. "Один к многим"
|
||
|
||
Одной записи первой таблицы соответсвует неколько записей в другой таблие. Символ ключа связи указывает, что таблица,к которой этот конец прилегает, находится на стороне "один", а символ бесконечности находится на стороне "многие".
|
||
3. "Многие к многим"
|
||
|
||
Если нескольким записям одно таблицы соответсвует несколько запясей из другой таблицы. Связь организовывается посредством связывающей таблицы.
|
||
|
||
### Реляционная алгебра. Операции
|
||
|
||
* *Проекция* - это столбец фильтра. Получаем содержимое только части столбцов.
|
||
* *Выборка* - это фильтрование строки. Включает в себя выбор строк.
|
||
* *Декартово произведение* - Каждому элементу из множества R сопоставлен в соответсвие определенный элемент из множества S, то возникает множество, сопоставленное из пар элементов множества R и S.
|
||
* *Объединение* - это отношение, которое включает в себя все кортежи обоих отношений без повторов.
|
||
* *Вычитание* - это разность односхемных отношений R и S назывется множество кортежей R, не входящих в S.
|
||
* *Пересечение* - это пересечение двух односхемных отношений R и S есть подмножество кортежей, принадлежащих обоим отношениям. Это можно выразить через разность: R-(R-S).
|
||
* *Соединение* - эта операция определяет подмножество декартова произведения двух разносхемных отношений. Предназначена для обеспечения выборки данных из двух таблиц и включение этих данных в один результативный набор.
|
||
|
||
### Нормализация и Денормализация
|
||
|
||
*Нормальная форма* - Свойство отношения, характеризующее его с точки зрения избыточности
|
||
|
||
*Нормализация* - процесс минимизации избыточности отношения(приведение к НФ)
|
||
|
||
*Денормализация* - намеренное приведение структуры базы данных в состояние, не соотвествующее критериям нормализации
|
||
|
||
## Основы синтаксиса
|
||
|
||
### Идентификаторы и ключевые слова
|
||
|
||
* *SELECT*, *UPDATE* и *VALUES* являются примерами ключевых слов, то есть слов, имеющих фиксированное значение в языке SQL
|
||
|
||
* Ключевые слова и идентификаторы без кавычек воспринимаются системой без учета регистра.
|
||
|
||
* Есть и другой тип идентификаторов: отделённые идентификаторы, или идентификаторы в ковычках
|
||
|
||
```sql
|
||
UPDATE my_table SET a = 5;
|
||
UPDATE "my_table" SET "a" = 5;
|
||
```
|
||
|
||
### Константы
|
||
|
||
* *Строковая константа в SQL* - обычная последовательность символов, заключённая в одинарные ковычки
|
||
|
||
```sql
|
||
SELECT 'foobar'
|
||
```
|
||
|
||
* Ещё один способ записи строковых констант - "заключение строк в доллары".
|
||
|
||
```sql
|
||
$$Жанна д Арк$$
|
||
```
|
||
|
||
* Числовые константы
|
||
|
||
* 42
|
||
* 3.5
|
||
* 5e2
|
||
* 1.925e-3
|
||
|
||
* Битовые константы похожи на обычные с дополнительной буквой перед опострофом, показывающих систему счисления
|
||
|
||
* B'1001'
|
||
* X'1FF'
|
||
|
||
* Константы: Со спецпоследовательностями (начинается с буквы E перед апострофом)
|
||
|
||
* \b - "забой" (BackSpace)
|
||
* \f - подача формы
|
||
* \n - новая строка
|
||
* \r - возврат каретки
|
||
* \t - табуляция
|
||
|
||
### Операторы
|
||
|
||
* ^ - возведение в степень
|
||
* IS TRUE, IS FALSE
|
||
* ISNULL
|
||
|
||
### Специальные знаки (Вот вооще не операторы)
|
||
|
||
**Вот с#ка не забудь про ;**
|
||
|
||
### Коментарии
|
||
|
||
```sql
|
||
-- Коментарий
|
||
```
|
||
|
||
```sql
|
||
/* многострочный
|
||
коментарий */
|
||
```
|
||
|
||
## Типы данных
|
||
|
||
### Числовые типы
|
||
|
||
* *smallint* (2 байта) - целое в небольшом диапозоне
|
||
* *integer* (4 байта) - типичный выбор для целых чисел
|
||
* *bigint* (8 байт) - целое в большом диапозоне
|
||
* *numeric* / *decimal* (переменный) - десятичное с указанной точностью
|
||
* *real* (4 байта) - десятичное с переменной точностью
|
||
* *double precision* (8 байт) - вещественное число с переменной точностью
|
||
* *smallserial* (2 байта) - небольшое целое с автоувелечением
|
||
* *serial* (4 байта) - целое с автоувеличением
|
||
* *bigserial* (8 байт) - большое целое с автоувеличением
|
||
* *money* - два знака после запятой, денежный тип
|
||
|
||
### Символьные типы данных
|
||
|
||
* *character varing(n)* / *varchar(n)* - строка ограниченной переменной длины
|
||
* *character(n)* / *char(n)* - строка фиксированной длины с пробелами
|
||
* *text* - строка неограничсенной переменной длины
|
||
|
||
### Логический тип данных
|
||
|
||
* *boolean* (1 байт) - TRUE / FALSE / NULL
|
||
|
||
### Типы данных даты и времени
|
||
|
||
* *date* - дата без времени YYYY-MM-DD
|
||
* *timestamp* - дата и время с точностью до микросекунды
|
||
* *interval* - интервал между датами
|
||
* *time* - время без даты. Удобный тип данных чем-то похожий на interval, который работает по модулю 24 (часа), то есть интервал, в котором отстутствуют дни
|
||
|
||
## DDL - Data Defintion Language
|
||
|
||
* CREATE - создание таблиц
|
||
* ALTER - используется для изменения объектов БД
|
||
* DROP - оспользуется для удаления БД
|
||
|
||
### Примеры
|
||
|
||
* Создать базу данных:
|
||
|
||
```sql
|
||
CREATE DATABASE test;
|
||
```
|
||
|
||
* Создать таблицу
|
||
|
||
```sql
|
||
CREATE TABLE films(
|
||
code char(5) CONSTRAINT firstkey PRIMARY KEY,
|
||
title varchar(40) NOT NULL,
|
||
did integer NOT NULL,
|
||
date_prod date,
|
||
kind varchar(10),
|
||
len interval hour to minute
|
||
);
|
||
```
|
||
|
||
* Добавить столбец
|
||
|
||
```sql
|
||
ALTER TABLE distributors ADD COLUMN adress varchar(30);
|
||
```
|
||
|
||
* Удалить столбец
|
||
|
||
```sql
|
||
ALTER TABLE distributors DROP COLUMN adress RESTRICT;
|
||
```
|
||
|
||
* Изменить тип данных
|
||
|
||
```sql
|
||
ALTER TABLE distributors
|
||
ALTER COLUMN address TYPE varchar(80),
|
||
ALTER COLUMN name TYPE varchar(100);
|
||
```
|
||
|
||
* Создать таблицу, если нет с таким же названием:
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS ... ;
|
||
```
|
||
|
||
* Получить всё из таблицы:
|
||
|
||
```sql
|
||
SELECT * FROM authors;
|
||
```
|
||
|
||
* Вставить что-то в таблицу:
|
||
|
||
```sql
|
||
INSERT INTO authors VALUES (1, 'Лёва Толстый');
|
||
```
|
||
|
||
## Фильтрация и компоновка данных
|
||
|
||
### Where
|
||
|
||
> Для осуществления фильтрации в SQL есть специальный оператор *WHERE*
|
||
|
||
```sql
|
||
SELECT * FROM stores WHERE amount > 10
|
||
```
|
||
|
||
### Агрегатные функции
|
||
|
||
> Агрегатные функции выполняют вычисления над набром значений и возвращает 1 значение. Агрегатные функции игранируют NULL значения (кроме COUNT)
|
||
|
||
* *СOUNT(\*)* - находит количество строк в запросе
|
||
* *COUNT(**expression**)* - находит количество строк в запросе, для которых **expression** не содержит значение NULL;
|
||
* *SUM* - сумма значений выьранных столбцов
|
||
* *MAX* - максимальное значение определённого столбца
|
||
* *MIN* - минимальное значение определённого столбца
|
||
* *AVG* - вычисление среднее значение столбца
|
||
|
||
### Группировка данных (*GROUP BY*)
|
||
|
||
> *GROUP BY* определяет, как строки будут группироваться, чтобы разделить их на логические наборы, благодаря чему становится возможном выполнение статических вычеслений отдельно в каждой группе
|
||
|
||
```sql
|
||
SELECT age, SUM(salary) as sum FROM workers GROUP BY age;
|
||
-- Сумма зарплат для каждого возраста
|
||
-- *as sum* меняет название столбца на нужное нам (было бы так, как написанно (SUM(salary)))
|
||
```
|
||
|
||
### Оператор *HAVING*
|
||
|
||
> Оператор *HAVING* позволяет определять условия выборки уже сгрупированных данных. *HAVING* применяется строго после GROUP_BY и может использовать результаты агрегатных функций
|
||
|
||
```sql
|
||
SELECT model, COUNT(model) as Qty_model, AVG(price) as Avg_price
|
||
FROM PC
|
||
GROUP BY model
|
||
HAVING AVG(price) < 800;
|
||
-- Фильтрует столбцы, которые были бы выданы после GROUP BY
|
||
```
|
||
|
||
### Оператор *SORT BY*
|
||
|
||
> *ORDER BY* используется для сортировки записей в наборе результатов запроса *SELECT*. Сортирует по возрастанию, но, если нужно сделать по убыванию, то используем *DESC* (от английского descending)
|
||
|
||
```sql
|
||
SELECT * FROM Sumproduct ORDER BY Amount DESC, City
|
||
-- Значала сортирует по Amount по убыванию, потом по городу по возростанию
|
||
```
|
||
|
||
### *LIMIT* и *OFFSET*
|
||
|
||
* *LIMIT* - макс количество выданных строк
|
||
* *OFFSET* - пропустить первые N строк
|
||
|
||
### Общий вид
|
||
|
||
```sql
|
||
SELECT столбцы
|
||
FROM ТАБЛИЦА
|
||
WHERE условие-фильтра
|
||
GROUP BY столбцы-для-группировки
|
||
HAVING фильтр-груп
|
||
ORDER BY столбцы для сортировки
|
||
LIMIT N
|
||
OFFSET K
|
||
```
|
||
|
||
```sql
|
||
SELECT name, AVG(salary) as avg_salary
|
||
FROM workers
|
||
GROUP BY name
|
||
HAVING name != 'JOHN'
|
||
ORDER BY avg_salary DESC
|
||
LIMIT 5
|
||
OFFSET 3
|
||
```
|
||
|
||
## Соединение и объединение таблиц
|
||
|
||
### *JOIN*
|
||
|
||
> *JOIN* позволяет соединения двух или более таблиц
|
||
|
||
#### Виды
|
||
|
||
* *JOIN* (только пересечение)
|
||
* *LEFT JOIN* (всё из первой и пересечение)
|
||
* *RIGHT JOIN* (всё из второй таблицы и пересечение)
|
||
* *FULL JOIN* (всё из обоих таблиц)
|
||
|
||
#### Пример
|
||
|
||
```sql
|
||
SELECT * FROM AUTHORS [LEFT/RIGHT/FULL/""] JOIN BOOKS
|
||
ON Author.id = Books.author_id
|
||
```
|
||
|
||
### *UNION*
|
||
|
||
> Выводит результаты двух *SELECT*. Удаляет дубликаты, если не *UNION ALL*. Кол-во и названия строк должны быть одинаковыми
|
||
|
||
#### Пример *UNION*
|
||
|
||
```sql
|
||
SELECT val1 FROM table1
|
||
UNION
|
||
SELECT val1 FROM table2
|
||
```
|
||
|
||
### *INTERSECT*
|
||
|
||
> Возвращает значения, которые выводят оба *SELECT*. Кол-во и названия строк должны быть одинаковыми. Синтаксис как у UNION
|
||
|
||
### *EXCEPT*
|
||
|
||
> Возвращает значения, которые выдал первый *SELECT*, но не второй. Кол-во и названия строк должны быть одинаковыми. Синтаксис как у UNION
|