SQL Flashcards
Что такое Реляционная Модель?
Предполагает мультитабличную систему организации данных, имитирующую отношения между сущностями программы (one-to-many/many-to-many/one-to-one)
Зачем в таблице primary key? Что это такое?
1) Чтобы соблюсти принцип Integrity: таблица не может содержать 2 полностью одинаковых ряда.
2) комбинация NOT NULL и UNIQUE constraints. Помечает каждую запись в базе данных уникальным значением.
~~~
country_name VARCHAR(40) NOT NULL UNIQUE, =>
ALTER TABLE countries ADD PRIMARY KEY (country_name);
also:
ADD CONSTRAINT pk_student PRIMARY KEY (name,birth_date);
~~~
Зачем в таблице foreign key?
1) создает связь между двумя таблицами и защищает от действий, которые могут нарушить связи между таблицами: он всегда ссылается на PRIMARY KEY другой таблицы.
2) С его помощью создаётся many-to-one relation.
3) Поддерживает referential actions при UPDATE / DELETE:
CASCADE:
SET NULL
SET DEFAULT
RESTRICT
NO ACTION
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(60)
department_id INT,
[ CONSTRAINT fk_department ] FOREIGN KEY (department_id)
REFERENCES departments(id) );
ALTER TABLE employees
ADD FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE employees DROP FOREIGN KEY fk_department;
Что такое one-to-one?
одному ряду (tuple) в данной таблице (relation) может соответствовать лишь один ряд в другой таблице, обратное тоже верно:гражданин - паспорт
Что такое many-to-many?
сотрудник - зона ответственности. Это отношение воплощено через сводную таблицу по принципу one-to-many.
Что такое identifying ralationship?
Когда ФК становится ПК или частью составного ПК в таблице.
Почему SQL - декларативный язык?
Потому, что он говорит что надо сделать но не говорит как.
Какие базовые типы данных есть в SQL?
- INTEGER
- FLOAT
- DECIMAL(20, 2): 20 - precision / 2 - scale
- VARCHAR(10): 10- max length.
- BOOLEAN
Что такое DDL? Какие операции в него входят? (4) Рассказать про них.
Операторы определения данных:
* CREATE создает объект БД (базу, таблицу, представление, пользователя и т. д.);
* ALTER изменяет объект;
- - ADD / MODIFY / DROP COLUMN | rename: CHANGE old new;
* DROP удаляет объект;
* TRUNCATE удаляет таблицу и создает ее пустую заново, но если в таблице были foreigh key, то создать таблицу не получится,
rollback после TRUNCATE невозможен.
NB: DDL команды транзакционны - разработчик не контролирует транзакцию, она открывается и закрывается сама.
Что такое DML? Какие операции в него входят? Рассказать про них.
- SELECT … FROM … WHERE … выбирает данные, удовлетворяющие заданным условиям;
SELECT concat(first_name, ' ', last_name) FROM persons;
**CASE** :
SELECT customer_id, first_name, CASE WHEN age >= 18 THEN 'Allowed' END AS can_vote FROM Customers;
COALESCE:
SELECT name, COALESCE(department, 'No department') AS dept
-
INSERT INTO добавляет новые данные (
VALUES);INSERT INTO SELECT FROM:
~~~
INSERT INTO table1 (column_1, column_2, …, column_n)
SELECT column_1, column_2, …, column_n
FROM table2 WHERE condition;
~~~ -
UPDATE изменяет существующие данные;
UPDATE table SET col1 = expr1, colN = exprN WHERE
- DELETE FROM удаляет данные при выполнении условия WHERE или все данные: DELETE FROM table_name;
Что такое TCL? Какие операции в него входят? Рассказать про них.
Transaction Control Language, TCL):
* BEGIN служит для определения начала транзакции;
* COMMIT применяет транзакцию;
* ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции;
* SAVEPOINT разбивает транзакцию на более мелкие.
Что такое DCL? Какие операции в него входят? Рассказать про них.
Data Control Language, DCL):
* GRANT предоставляет пользователю (группе) разрешения на определенные
операции с объектом;
* REVOKE отзывает ранее выданные разрешения;
* DENY задает запрет, имеющий приоритет над разрешением.
the order of evaluation of the statements in a quiery
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Нюансы работы с NULL в SQL. Как проверить поле на NULL?
NULL означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни 0.
1) При любой операции с NULL получится NULL (a + null = null)
2) При сравнении NULL с любым значением будет получен NULL
3) в агрегатных функциях значения null игнорируются
4)В логических операциях (OR) может вернуться true / false :
(NULL = 1) OR TRUE equals to TRUE
5) можно использовать COALISE:
SELECT id, name, COALESCE(age, 0) as age, grade FROM students;
6) можно задать constraint NOT NULL
Проверить на null: IS NULL, IS NOT NULL, сравнение с помощью “=” вернёт NULL.
Что такое JOIN? Виды Join’ов?
JOIN предназначен для обеспечения выборки данных из двух и более таблиц на основе общей колонки и включения этих данных в результирующую таблицу. Prymary key из одной таблицы должен ссылаться на Foreigh key другой таблицы.
* (INNER) JOIN Результатом объединения таблиц являются записи, общие для левой и правой таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
SELECT table1.col_name1 [AS name1], tableN.col_name2 [AS nameN]
FROM table1 [AS a]
[type_of_join] JOIN table2 [AS b]
ON table1.id = table2.id;
[type_of_join] JOIN table3
ON table2.col_name_table2 = table3.col_name_table3;
LIMIT 10, 5;
* LEFT (OUTER) JOIN. Производит выбор всех записей первой таблицы и соответствующих им записей второй таблицы. Если записи во второй таблице не найдены, то вместо них подставляется пустой результат (NULL). Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
* RIGHT (OUTER) JOIN с операндами, расставленными в обратном порядке. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
* FULL (OUTER) JOIN. Результатом объединения таблиц являются все записи, которые присутствуют в таблицах. Порядок таблиц для оператора не важен, поскольку
оператор является симметричным.
* CROSS JOIN (декартово произведение) При выборе каждая строка одной таблицы объединяется с каждой строкой второй таблицы, давая тем самым все возможные сочетания строк двух таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
NB: вместо JOIN можно применять WHERE:
SELECT table1.col_name1, table2.col_name2
FROM table1 [AS a], table2 [AS b]
WHERE table1.id = table2.id;
https://sky.pro/media/join-v-sql/
Что лучше использовать join или подзапросы? Почему?
Подзапросы могут размещаться в SELECT, UPDATE, INSERT, and DELETE;
В SELECT могут размещаться в FROM и WHERE
Обычно лучше использовать JOIN, поскольку в большинстве случаев он более понятен и лучше оптимизируется СУБД (но 100% этого гарантировать нельзя).
Подзапросы лучше использовать в случаях, когда нужно выбрать данные из таблицы на основании данных другой таблицы.
UPDATE students SET exams_passed = TRUE WHERE name in (
SELECT name FROM exam_results WHERE
math_exam_mark >= 18 AND english_exam_mark >= 18 );
DELETE FROM orders WHERE customer_id =
(SELECT customer_id FROM customers WHERE name = ‘Ann Smith’);
Что делает UNION?
UNION применяется для объединения результатов нескольких SQL-запросов в единую таблицу, состоящую из схожих записей. Оба запроса должны возвращать одинаковое число столбцов и совместимые типы данных в соответствующих столбцах.
UNION сам по себе не гарантирует порядок записей. В случаях, когда требуется определенный порядок, необходимо использовать ORDER BY.
Разница между UNION и UNION ALL заключается в том, что UNION будет пропускать дубликаты записей, тогда как UNION ALL будет включать дубликаты записей.
SELECT name FROM teachers UNION [ALL] / INTERSECT / MINUS (EXCEPT) SELECT name FROM administrative_staff
Что такое HAVING и чем отличается от WHERE (3) ?
SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) < 500;
HAVING используется для фильтрации результата применения агрегатных функций ( MIN(), MAX(), SUM(), AVG(), COUNT()) и для спецификации GROUP BY, WHERE проверяет условие для каждого ряда по отдельности и не используется с агрегатными функциями.
HAVING используется после GROUP BY, WHERE перед GROUP BY.
Что такое TRIGGER?
Это блок кода, который отработает до и/или после события.
Работают с событиями: INSERT , UPDATE , DELETE
Работают до или после события: BEFORE , AFTER
Можно организовать их последовательность: FOLLOWS , PRECEDES
CREATE TRIGGER some_trigger
BEFORE INSERT ON some_table
FOR EACH ROW [PRECEDES / FOLLOWS other_trigger]
SET @sum = @sum + NEW.int_value;
SELECT @sum as sum_of_values;
CREATE TRIGGER save_fired_employee
AFTER DELETE
ON employee
FOR EACH ROW
BEGIN
INSERT INTO fired_employee VALUES (OLD.id, OLD.name, CURDATE());
SET @sum = @sum - OLD.salary;
END;
DROP TRIGGER some_trigger;
SHOW TRIGGERS [FROM table_name];
Что такое ORDER BY?
ORDER BY сортирует запрос согласно значениям в выбранных столбцах.
В сортировке может участвовать выражение.
Возможно определять возрастание ASC или убывание DESC для каждого столбца. По умолчаниювозрастание ASC.
Что такое GROUP BY?
GROUP BY используется для колонок с повторяющимися значениями применяя агрегатную функцию к значениям ячеек других колонок:
Создает отдельную группу для всех возможных значений (включая значение NULL).
Делит результат запроса на группы рядов согласно значениям колонки-аттрибута обычно с целью применения к этой группе агрегатных функций. Таким образом SELECT, в котором выполнен GROUP BY вернёт один результат на каждую такую группу.
При использовании GROUP BY все значения NULL считаются равными.
SELECT column_name [, list_of_other_columns]
, aggregation [, list_of_aggregations]
FROM table_name
[WHERE list_of_conditions]
GROUP BY column_name [, list_of_other_columns]
[HAVING list_of_aggregate_conditions]
[ORDER BY list_of_columns/aliases];
SELECT country, state, MIN(age) as min_age
FROM Persons
GROUP BY country, state;
Что такое DISTINCT?
указывает, что для вычислений используются только уникальные значения колонки.
Что такое LIMIT / OFFSET?
LIMIT Ограничивает выборку заданным числом.
OFFSET = skip() в Stream API
SELECT first_name, last_name
FROM Customers
LIMIT 2 OFFSET 3;
Что такое EXISTS?
EXISTS используется в WHERE
Берет подзапрос, как аргумент, и оценивает его как TRUE, если подзапрос возвращает какие-либо записи, и FALSE, если нет.
Если вернулось TRUE - результат внешнего запроса добавляется в resultset,
если FALSE - результат скипается и идёт проверка следующего ряда.
SELECT customer_id, first_name
FROM Customers
WHERE [NOT] EXISTS (
SELECT order_id
FROM Orders
WHERE Orders.customer_id = Customers.customer_id
);
Расскажите про операторы BETWEEN, IN, LIKE, ANY,
IS DISTINCT FROM
BETWEEN - выдает диапазон включительно
IN перечисляет точные значения к поиску, сокращая использование OR. Можно использовать с NOT
LIKE проверяет соответствует ли строка шаблону:
- - % - любое кол-во символов: %с% -> …c…
- - _ - один символ. c -> aca / bcs / ccc / …
ANY сравнивает значение ячейки из первой таблицы со всеми значениями заданной колонки второй таблицы и возвращает подходящее значение из первой.
Найти учителей-ровестников учеников:
SELECT * FROM Teachers WHERE age = ANY (
SELECT age FROM Students );
IS DISTINCT FROM : != / <>
NULL IS NOT DISTINCT FROM NULL => TRUE
WHERE city IS DISTINCT FROM 'New-York'; => name 'John' city 'null'
Что делает оператор MERGE? Какие у него есть ограничения?
MERGE служит для обмена данными между таблицами:
позволяет осуществить слияние данных одной таблицы с данными другой таблицы.
При слиянии таблиц проверяется условие, и если оно TRUE, то выполняется UPDATE, а если нет – INSERT, если в целевой таблице присутствует значение, отсутствующее в исходной - DELETE
При этом изменять поля таблицы в секции UPDATE, по которым идет связывание двух таблиц, нельзя.
MERGE TargetProducts AS Target
USING SourceProducts AS Source
ON Source.ProductID = Target.ProductID
-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (ProductID,ProductName, Price)
VALUES (Source.ProductID,Source.ProductName, Source.Price)
-- For Updates
WHEN MATCHED THEN UPDATE SET
Target.ProductName = Source.ProductName,
Target.Price = Source.Price
-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;
Какие агрегатные функции вы знаете?
Агрегатных функции – функции, которые берут группы значений и сводят их к одиночному значению.
SELECT FUNCTION(column_name) FROM table_name ...;
- CONCAT соединяет строки;
-
COUNT производит подсчет записей, удовлетворяющих условию запроса
SELECT COUNT( * / [DISTINCT] column ), AVG(column) FROM - SUM вычисляет арифметическую сумму всех значений колонки;
- AVG вычисляет среднее арифметическое всех значений;
- MAX определяет наибольшее из всех выбранных значений;
- MIN определяет наименьшее из всех выбранных значений.
Что такое ограничения (constraints)? Какие вы знаете?
Ограничения ( CONSTRAINT ) – это ключевые слова, которые помогают установить правила размещения данных в базе.
CONSTRAINT pk_employee PRIMARY KEY (department_id, employee_id) CONSTRAINT uq_id_last_name UNIQUE (personal_id, last_name) ALTER TABLE employees DROP INDEX uq_id_last_name;
NOT NULL указывает, что значение не может быть пустым.
UNIQUE обеспечивает отсутствие дубликатов.
PRIMARY KEY один на таблицу
FOREIGN KEY один на таблицу
CHECK проверяет, вписывается ли значение в заданный диапазон (s_id int CHECK(s_id > 0)).
DEFAULT устанавливает значение по умолчанию, если значения не предоставлено (name VARCHAR(20) DEFAULT ‘noname’).
Что такое суррогатные ключи?
Суррогатный ключ – это служебное поле, задача которого гарантировать уникальность конкретного ряда, независимо от содержащейся в нем информации, в отличии от РК, который используется для разграничения рядов как единиц информации.
Мы оставляем системе следить за уникальностью суррогатного ключа и как правило используем его в качестве PK.
Значение суррогата выбирается один раз для каждой строки и потом никогда не изменяется.
CREATE TABLE Example (
SurrogateKey INT IDENTITY(1,1) – A surrogate key that increments automatically )
CREATE TABLE Example ( PrimaryKey INT PRIMARY KEY -- A primary key is just an unique identifier)
Что такое индексы? В чём их минусы?
Индексы относятся к настройке производительности, и позволяют быстрее извлекать записи из таблицы, засчет создания дополнительной стр-ры данных, элементы которой хранят ссылки на адрес соответствующих рядов таблицы.
но
- требует дополнитульного места.
- при изменении данных в БД сначала обновляется исходная таблица, а затем все её индексы, таким образом если БД постоянно обновляется, ее индексы обновляться не будут и станут бесполезны.
- Чем больше в таблице индексов, тем дольше обновляются данные в БД.
Какие существуют классификации индексов?
- simple / compound - одно / несколко полей.
-
Unique Index: гарантирует уникальность поля или уникальную комбинацию включенных в него полей.
Создание уникального индекса для СУБД равносильно объявлению unique constraint при создании таблицы или модификации таблицы. Считается, сто последнее предпочтительнее в силу наглядности. -
Partial - создаётся на сабсет данных таблицы:
CREATE INDEX unpaidOrdersIdx ON orders (order_id)
WHERE is_paid = false; -
Кластеризованный индекс (Clustered Index): Создается автоматически при объявлении РК и физически изменяет порядок строк, сортируя их на основе РК. Для поиска использует бинарный поиск.
Это ускоряет операции чтения из БД.
-** Некластеризованный индекс (Non-Clustered Index):** не меняет структуру данных а создает отдельную структуру, хранящую ссылки на элементы таблицы.
NB: таблица может содержать 1 кластеризованный и анлим некластеризованных.
В каких структурах данных хранятся индексы?
- B-Tree: самобалансирующееся дерево - дефолтная структура данных для хранения индексов
- Hash TAble
- Bitmap - переводит значения в битовое представление, подходит для узкой области значений (true / false)
- Spatial - для хранения координат и прочих геометрических данных.
DBSM сама решает какую структуру данных выбрать, но мы можем задать её явно:
CREATE INDEX index_name
ON table_name (column_name) USING BTREE;
Когда лучше не использовать индексы?
1) в небольших таблицах
2) в таблицах с частыми массовыми update & insert
3) в столбцах, значение которых часто меняется
4) в столбцах, где много null
Как правильно составить составной индекс?
Следуя правилу “left-prefix-rule”:
CREATE INDEX full_date ON people(year, month, day) - пойдёт для поиска по
year only
year and month
year, month, and day
Он не поможет при поиске по day / month and day
Что будет, если в WHERE указать сначала неиндексированное поле, а потом индексированное (where … and …) ?
СУБД сама сначала просканирует по индексированным полям, а потом просканирует получившийся результат по неинексированной колонке.
Что такое план запроса? Как с ним работать?
СУБД анализирует все возможные пути выполнения запроса и оптимизатор запроса (!1) выбирает оптимальный и исполняет его. Каждый возможный путь - ПЛАН ЗАПРОСА.
План имеет древовидную структуру (как диспетчер файлов) и читается снизу вверх:
SELECT * FROM customers WHERE age > 25 =>
SELECT
-> Filter: (age > 25)
-> Table scan on customers
EXPLAIN [ANALIZE] - позволяет увидеть план запроса с ключевыми метриками cost & rows, алгоритмы поиска, …
- ANALIZE помимо вывода предполагаемых метрик запроса исполнит этот запрос и выведет фактические метрики
!1 - компонент СУБД, анализирующий возможные пути выполнения запроса и выбирающий оптимальный из них.
Чем TRUNCATE отличается от DELETE?
DELETE Медленнее, чем TRUNCATE, так как удаляет записи по одной. Есть возможность восстановить данные (вызвав ROLLBACK). Её лучше использовать когда в таблице есть foreign key, так как будет использована referential action.
TRUNCATE – DDL оператор, удаляет всю таблицу и создает её заново. Нет возможность восстановить данные – сделать ROLLBACK. Её не стоит использовать если на таблицу ссылается foreigh_key, но мы можем применить TRUNCATE сначала на дочернюю таблицу, затем на основную, тогда всё сработает.
Что такое хранимые процедуры? Для чего они нужны?
Объект базы данных, представляющий собой набор SQL- инструкций, который хранится DBMS.
CREATE PROCEDURE my_procedure ()
BEGIN
UPDATE table SET col_1 = 100 WHERE col_2 = ‘a’; SELECT * FROM table;
END;
CALL my_procedure;
DROP PROCEDURE my_procedure;
Хранимые процедуры очень похожи на обыкновенные методы языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам.
В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.
Хранимые процедуры позволяют повысить производительность, расширяют возможности программирования и поддерживают функции безопасности данных.
В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным) и в дальнейшем ее обработка осуществляется быстрее.
PROCEDURE с параметрами:
CREATE PROCEDURE SomeName(
IN SomeParameter VARCHAR(50),
OUT SomeParameter1 INT,
)
BEGIN
– PROCEDURE BODY;
END;
CALL SomeName(“foo”, @value)
SELECT @value;
Что такое представления (VIEW)? Для чего они нужны?
View – виртуальная таблица, представляющая данные одной или более таблиц в виде хранимого в памяти именного SELECT стэйтмента.
Они работают в запросах и операторах DML точно так же, как и таблицы-родители, но не содержат никаких собственных данных. Выполнять DML команды над View можно лишь если соблюдён ряд условий по его созданию:
1) не использовались подзапросы, агрегатные функции & GROUP BY & DISTINCT
2) использовалась только одна таблицп
3) вью должен содержать уникальный или праймари ключ
4) все значения должны быть NOT NULL
Позволяют:
- дать публичный доступ к некоторой информации из основной таблицы.
- сократить код сложных запросов
- кастомизировать вид таблицы для разных пользоователей таблицы.
CREATE [OR REPLACE]
VIEW view_name AS
SELECT
column1,
column2,
...
FROM
table_name
WHERE
condition;
Что такое временные таблицы? Для чего они нужны?
Нужна
- для сложных запросов, требующих промежуточных результатов.
2 Вида:
1) Локальные: # - Таблица, которая существует только в период текущей сессии и доступна только внутри неё. Удаляется автоматически по её окончании.
2) Глобальные: ## - Таблица, которая доступна всем открытым сессиям и удалится, когда прекратся последняя сессия, использовавшая её.
CREATE TABLE #ProductSummary
(ProdId INT IDENTITY,
ProdName NVARCHAR(20),
Price MONEY)
Что такое транзакции? Какие у нее могут быть состояния (5)?
Транзакция – это единица работы DBMS, выраженная в переводе БД из одного целостного состояния в другое.
Состояния транзакции:
active / partially commited / failed state / aborted / commited
Расскажите про принципы ACID
- Атомарность (atomicity) гарантирует, что транзакция будет полностью выполнена или полностью отменена. Это означает, что при сбое любой части транзакции происходит сбой всей транзакции и состояние базы данных остается неизменным.
- Согласованность (consistency). Выполненная транзакция не нарушает согласованность базы данных (соблюдение всех правил и ограничений схемы).
-
Изолированность (isolation). параллельные транзакции не должны оказывать влияние на результат друг друга:
изменения, сделанные транзакцией не видны другим транзакциям, до её завершения. - Долговечность (durability). Независимо от проблем (потеря питания, сбои / ошибки любого рода) изменения, сделанные успешно завершенной транзакцией, обязаны быть отражены в системе.
Какие есть Transaction Isolation Issues?
Lost Update - когда две транзакции меняют один сегмент, первая транзакция успешно коммитится, а вторая делает ролбэк. В итоге апдэйт теряется
Dirty Read - первая транзакция производит запись. Между тем вторая транзакция считывает ту же cамую запись до завершения первой.
Позже первая транзакция делает ролбэк, и теперь у нас есть грязные данные во второй транзакции.
Non-Repeatable Read:
Когда первая транзакция дважды читает данные, но между этими чтениями вторая транзакция изменяет эти данные и делает commit.
В результате первая транзакция получает 2 разных результата.
Last Commit Wins - частный случай NRR, когда 2 транзакции параллельно читают и меняют данные, в базусохранятся результаты последней закоммиченной транзакции.
Phantom Read: Первая транзакция читает набор записей. Затем вторая транзакция вставляет или удаляет запись в диапазон первой транзакции.
Позднее первая транзакция снова считывает тот же диапазон и в том числе получает запись, которую только что вставила транзакция B.
Расскажите про уровни изолированности транзакций.
1) Read Uncommited : транзакция читает любую инфу, независимо от того, закоммичена ли она.
Исключается Lost Update , возможен Dirty Read:
2) Read Commited: транзакция видит состояние базы на момент своего старта.
Исключается Dirty Read. При нескольких селектах возможен non-repeatable read:
3) Repeatable Read : вся читаемая текущей т. инфа блокируется от модификации другими транзакциями до конца текущей т.
Исключается non-repeatable read, При нескольких селектах возможен phantom read.
4) Serializable: исключает все возможные аномалии.
Что такое нормализация и денормализация? Расскажите про 3 нормальные формы?
Избыточность - когда одни и те же данные хранятся в базе в нескольких местах, что приводит к аномалиям ().
Нормализация - последовательный обратимый процесс приведения БД к эталонному виду (от одной нормальной формы к следующей) с целью достижения минимальной избыточности.
Нормальная форма - Состояние схемы БД, отвечающее определенному набору критериев.
Перед нормализацией нужно, чтобы база соответствовала реляционной модели (отсутствие нумерации строк и столбцов).
1) Первая нормальная форма (1NF): таблица соответствует реляционной модели и соблюдает правила:
- строки не дублируются
- в каждой ячейке хранится атомарное значение (одно несоставное значение)
- в столбце хранятся данные одного типа
- отсутствуют массивы и списки в любом виде
2) Вторая нормальная форма (2NF): Таблица находится в 1NF и соблюдает принципы:
- Таблица должна иметь ключ
- Все неключевые столбцы должны зависеть от полного ключа (не должно быть данных, которые можно получить по части составного ключа)
PK{ project | participant } | position | project duration
3) Третья нормальная форма (3NF): Таблица находится в 2NF и соблюдает принципы:
* Отсутствие транзитивной зависимости: когда один неключевой столбец связан с РК через другой неключевой столбец:
- uuid | name | department | department description
3 * ) Нормальная форма Бойса-Кодда: таблица находится в 3НФ и
* Часть составного РК не должна зависеть от неключевого столбца:
PK{ проект | направление } | куратор
4) В таблице не должно быть мгогозначных зависимостей:
B !-> C & A –> B & A –> C ; (курс- препод - аудитория)
5) невозможно определить без понимания предметной области
- декомпозиция: разделение таблицы на несколько
- Денормализация базы данных – это процесс обратный от нормализации. Эта техника добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу. Необходимо для повышения производительности и скорости извлечения данных за счет увеличения избыточности данных.
Каждая сущность должна храниться в отдельной таблице.
Какие типы работают с датами?
DATE, TIME, DATETIME, TIMESTAMP, and INTERVAL
DATETIME
Хранит время в виде целого числа в формате YYYYMMDDHHMMSS, используя для этого 8 байт. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в SQL.
create table dt1 ( col datetime NOT NULL );
SET @@session.time_zone=’+00:00’;
insert into dt1 values(now());
select * from dt1; => 2009-06-04 18:14:10
SET @@session.time_zone=’+01:00’;
select * from dt1; => 2009-06-04 18:14:10
TIMESTAMP
хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усредненному времени Гринвича. При получении из базы отображается с учетом часового пояса. Размер 4 байта.
Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии.
TIMESTAMP по умолчанию NOT NULL, а его значение по умолчанию равно NOW().
create table tm1 (col timestamp not null);
set @@session.time_zone = ‘+00:00’;
insert into tm1 values(now());
select * from tm1; => 2009-06-04 18:25:08
mysql> set @@session.time_zone = ‘+01:00’;
select * from tm1; => 2009-06-04 19:25:08
SELECT CURDATE() / CURTIME() / CURRENT_TIMESTAMP()
SELECT DATEDIFF(first_date, second_date);
SELECT EXTRACT(MONTH FROM ‘2020-11-04’);
SELECT DATE_ADD / DATE_SUB(CURDATE(), INTERVAL 10 DAY);
