SQL

Основные разделы

Общее

• Link Types
• TransactionLevels
• Нормализация(Normalization)
• Indexes
• Базы данных
• Ограничения в SQL
• Планирование схемы таблицы
• Основные понятия
• Виды запросов
• Команды
• Оптимизация запросов
• SQL-инъекции
• Вопросы на собеседование

Базы данных

• MySql
• Postgre
• H2
• Sybase

Ограничения в SQL

• NOT NULL - столбец не может принимать значения NULL.
• PRIMARY KEY - для определения уникального указателя для каждой строки. Должен быть unique и not null.
  • Гарантирует уникальность указанных полей.
  • Нельзя значение NULL.
  • Можно иметь только один PK.
  • Создает кластерный индекс.
  • Поддерживает авто инкремент.
  • Нельзя менять/удалять выбранные поля.
• UNIQUE - в указанных столбцах уникальные значения.
  • Гарантирует уникальность указанных полей.
  • Можно значение NULL.
  • Можно иметь несколько UNIQUE.
  • Создает не кластерный индекс.
  • Не поддерживает авто инкремент.
  • Нельзя менять/удалять выбранные поля.
• DEFAULT - определяет значение по умолчанию для столбцов.
• FOREIGN KEY - это столбец или комбинация столбцов, которые используются для установления и обеспечения взаимосвязи между данными в двух таблицах.
• CHECK - используется для ограничения значений, которые могут быть помещены в столбец.

Планирование схемы таблицы

1. Имя таблицы - lowercase единичном или множественно числе: user_admin, users.
2. Необходимые колонки данных
3. Колонка PK - обычно id. Если числовое можно автоинерементацию.
4. Типы данных
5. Ограничения на колонки - UNIQUE, NOT NULL, MAX, MIN
6. PRIMARY KEY - может быть только один. Нельзя null. По умолчанию имеет кластерный индекс.
7. FOREIGN KEYS - можно применять как к PK, так и к обычным полям.
8. UNIQUE поле/поля/связки полей - можно несколько, можно null(можно указать NOT NULL).
9. TRIM - для строковых полей.
10. INDEXES

Основные понятия

Дамп

Дамп(Dump) - файл, включающий в себя содержимое памяти базы данных.

Используется для

1. Перенос данных на другой хостинг.
2. Резервное копирование.

DDL(Data Definition Language)

• CREATE
• DROP
• ALTER
• TRUNCATE
• COMMENT
• RENAME

DQL(Data Query Language)

SELECT

SELECT * FROM table1;
        

DML(Data Manipulation Language)

• INSERT

INSERT INTO table2 (user_id, phone_number) VALUE ('2','200');
            

• UPDATE

UPDATE table2 SET user_id='2', phone_number='200' WHERE phone_id='1';
            

• DELETE
• LOCK
• CALL
• EXPLAIN PLAN

DCL(Data Control Language)

• GRANT
• REVOKE

TCL(Transaction Control Language)

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

Команды

1. WHERE - ограничивающее команда для SELECT, UPDATE, DELETE. Оно выполняется до того, как будет получен результат операции.
2. GROUP BY - используется для группировки данных.

SELECT country, COUNT(*) AS n_companies
FROM companies
GROUP BY country
            

3. HAVING - фильтрующее выражение. Работает с командой GROUP BY(для результатов агрегированных функций).

SELECT
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
WHERE SUM(total) > 40000
GROUP BY product_line
ORDER BY total_gain DESC
            

4. Виды Joins:
   • INNER JOIN - общее
   • LEFT JOIN - левое и общее. Если не будет совпадения, правая часть будет null.
   • RIGHT JOIN - правое и общее. Если не будет совпадения, левая часть будет null.
   • FULL OUTER - выбирает не только записи с совпадающими значениями в этих столбцах, но и все оставшиеся строки из обеих таблиц.
   • CROSS(декартова произведения) - не общее
5. UNION - объединяет таблицы с одинаковыми полями и значением.
6. UNION ALL - как UNION, но включает дубликаты.

Оптимизация запросов

1. Проверить план запроса
2. Можно отключить планировщик запросов
3. Использовать индексы
4. Скрипт должен идти по мере увеличения размера таблиц.

Select *
From A a --100 записей
INNER JOIN B b ON a.c = b.c --1000 записей
INNER JOIN C c ON b.d = c.d --10000 записей
            

5. Фильтровать как можно раньше для уменьшения размеров обрабатываемых данных.

Советы

Создание таблицы:

Если есть хоть одно unique поле - оно будет гарантировать что вся запись уникальна.

SQL-инъекции

SQL-инъекция — это попытка изменить запрос к базе данных. Ввести ее можно через форму или ссылку, которая передает параметры методом GET.

Как защититься

В JAVA EE нужно использовать "PreparedStatement".

Вопросы на собеседование

DELETE vs TRUNCATE:

• TRUNCATE не может использовать WHERE.
• TRUNCATE быстрее и использует меньше системных ресурсов и практически не пишет лог транзакции.
• TRUNCATE после использования, откат (rollback) невозможен.
• TRUNCATE обнуляет счетчик, уникальности новых записей.

Рекомендуемые типы данных

CHAR()

CHAR() - символьная переменная ТОЛЬКО(не больше, не меньше) заданного размера.

VARCHAR()

VARCHAR() - символьная переменная до(можно меньше) заданного размера.

Рекомендуемые типы данных

Email

VARCHAR(256)

Phone number

VARCHAR(15)

Bcrypt hashed

VARCHAR(60)

Процедуры и функции

Процедуры в отличие от функций не возвращают значения.