SQL Flashcards
SQL Оптимизации запросов
Методы оптимизации SQL-запросов:
1) Индексирование (Создание индексов на часто используемых столбцах позволяет базе данных быстро находить нужные строки без необходимости сканирования всей таблицы)
2) Разбиение больших таблиц на более мелкие, более управляемые сегменты помогает уменьшить объём данных, которые необходимо сканировать
3) Нормализация таблиц (уменьшает объём избыточных данных, делая запросы более быстрыми и эффективными)
4) Оптимизация алгоритмов запросов
5) Использование хранимых процедур (они компилируются один раз, можно использовать для инкапсуляции часто используемых запросов)
6) Кэширование результатов запроса для повторного использования (во временной таблице или табличном выражении)
7) Изучение плана выполнения запроса (Explain - показывает, как движок SQL выполняет запрос, и раскрывает проблемные фрагменты)
Основные практики оптимизации:
1) Использование индексов для больших таблиц, для нечасто обновляющихся таблиц, для часто исп. столбцов
2) Использовать WHERE вместо HAVING:
- HAVING фильтрует данные при создании групп (GROUP BY)
- WHERE позволяет отфильтровать данные до создания групп (соответственно для группировки будет меньше работы)
3) Извлекать только нужные столбцы (Не использовать SELECT *)
4) Избегайте запросов внутри цикла (нужные запросы можно сделать вне цикла и сохранить результаты в кэш)
5) Используйте Exist() вместо Count() (Exist() прекращает выполнение после первого выполнения условия)
6) Применение денормализации таблиц (таблицы будут хранить избыточную информацию, но запросы могут выполняться быстрее (т.к. снижается необходимость использования медленных операций JOIN))
SQL Подзапросы
Подзапросы - запросы, встроенные в другие запросы.
Коррелирующие подзапросы - подзапросы, результаты которых зависят от строк, которые выбираются в основном запросе.
Пример: найдем товары, цена которых выше средней = 42142
SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
Операторы ALL, ANY, SOME, EXISTS
- ALL (условие в операции сравнения должно быть верно для всех значений, которые возвращаются подзапросом):
SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer=’Apple’) - ANY (условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом)
- EXISTS (позволяет проверить, возвращает ли подзапрос какое-либо значение, т.е. указывает, что в базе данных есть как минимум одна строка, которые соответствует данному запросу)
Подзапросы в основных командах SQL
1) в SELECT: с помощью WHERE, HAVING, FROM
2) в INSERT
3) в UPDATE
4) в DELETE:
DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName=’Galaxy S8’)
SQL Реляционная модель БД
Реляционная модель - основана на таблицах, состоящих из строк и столбцов, и отношениях между ними, соотв. правилам Кодда (мат. операции над таблицами - выборка, проекция, объединение, соединение).
Атрибут - колонка таблицы с названием и типом данных (аналог свойства класса, где класс - это таблица).
Запись - строка таблицы (аналог экземпляра класса).
Постулаты реляционной модели:
1) Порядок строк в таблице не имеет значения
2) В таблице нет двух одинаковых строк
Первичный ключ - колонка для уникального id записи.
Представление - это выборка данных из таблицы и их визуализация в виде таблицы.
Условия целостности данных - ограничения на столбцы.
Транзакция - группа неразрывных действий, т.е. она либо полностью выполняется, либо полностью отменяется.
Правила Кодда
Правила Кодда:
1) Правило информации. Любые данные хранятся в ячейке таблицы, будь то метаданные или пользовательские данные.
2) Гарантированный доступ. Для доступа необходимо и достаточно: имя таблицы, первичный ключ, имя колонки. Косвенный доступ (через указатели) запрещён.
3) NULL-ы должны обрабатываться единообразно.
4) Активный каталог. Метаданные должны храниться в специальной базе. Пользователь может работать с ними, как и с любыми другими.
5) Доступ к данным открыт ТОЛЬКО через язык с линейным синтаксисом, поддерживающий описание данных, манипуляцию данными, управление транзакциями. Нельзя в обход, напрямую.
6) Все представления, которые могут обновляться, обновляются системой.
7) Высокоуровневая вставка, обновление, удаление данных. Вставка, обновление, удаление нескольких строк сразу.
8) Физическая независимость: приложениям должно быть не важно, как данные хранятся внутри.
9) Логическая независимость: представление не должно поменяться, если поменялись подлежащие таблицы.
10) Независимость целостности. Приложение должно быть независимым от условий целостности данных. Если меняются условия целостности данных, приложение менять не требуется.
11) Независимость от распределённости. Конечному пользователю должно быть всё равно, хранятся данные тут, там или раскиданы по всему миру.
12) Устойчивость. Если система предоставляет обходной интерфейс к данным, обходной путь не должен позволять обходить настройки безопасности и целостности данных.
Индексы БД
Индекс - вспомогательная информация для ускорения запросов, он привязан к колонке или колонкам (с любым типом данных с фиксированным размером), представляет собой сбалансированное двоичное дерево поиска (быстрее линейного поиска).
Типы индексов:
1) Кластеризованный индекс:
- в листьях дерева такого индекса хранятся сами строки таблицы
- может быть только один
- таблица физически отсортирована по этому индексу
2) Некластеризованный индекс:
- в листьях хранятся не сами строки, а косвенная информация, по которой можно будет найти настоящую строку (указатель для кучи, значение ключа для не кучи)
- Поиск по некластеризованному индексу медленнее, чем по кластеризованному. Требуется дополнительная операция перехода
- может быть много
3) Составной индекс: сортирует таблицу по нескольким столбцам:
- при этом оптимизация будет достигаться, если делать выборку по этим столбцам в порядке следования в индексе, либо делать выборку по первому столбцу индекса
4) Уникальный индекс: Обеспечивает уникальность значений в столбце или их сочетаний
Замечания:
Индексы ускоряют операции чтения, но могут замедлять операции записи (вставка, обновление, удаление), так как индексы нужно обновлять при изменении данных.
Недостатки индексов
Недостатки индексов:
1) занимает много памяти и места на жестком диске
2) может не давать выгоды, если запросы по этой колонке редкие
3) издержки системы на поддержание индекса в корректном состоянии
MS SQL Server Системные базы и таблицы:
- master (данные о самой СУБД)
- model (образ БД для создания на ее основе других)
- msdb (для планирования и автоматического запуска задач по расписанию)
- Resource (База только для чтения, где хранятся все метаданные (например, sys.objects и sys.indexes))
- tempdb (Пространство для временных таблиц. Будут уничтожены при выходе)
Отношения между таблицами
1) Один к одному (Каждой записи слева соответствует ровно одна запись справа)
2) Один ко многим (Пример: В каждой стране может быть МНОГО городов и Каждый город принадлежит лишь ОДНОЙ стране)
3) Много ко многим (Пример: Каждая группа в течение обучения проходит МНОГО предметов и Каждый предмет изучается МНОГИМИ группами):
- выражается в промежуточной таблице - по сути будет 2 отношения один ко многим
Нормализация и Нормальные формы
Нормализация - процесс поэтапного улучшения структуры БД.
Нормальные формы:
1) Первая нормальная форма (1НФ)
- В ячейках запрещено хранить составные значения
2) Вторая нормальная форма (2НФ):
- Таблица должна быть в 1НФ
- Данные в строке должны зависеть от всего ключа (не от части ключа!) - Надо находить зависимые колонки в таблице и отселять их в отдельную таблицу
3) Третья нормальная форма (3НФ):
- Таблица находится в 2НФ
- Нет транзитивных зависимостей между колонками (когда данные зависят от ключа не напрямую, а через промежуточную колонку)
SQL Соединение таблиц
1) Неявное соединение:
SELECT * FROM Orders, Customers
WHERE Orders.CustomerId = Customers.Id
2) Явное соединение таблиц:
2.1) INNER JOIN - внутреннее соединение:
SELECT Orders.CreatedAt, Orders.ProductCount, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
2.2) OUTER JOIN - внешнее соединение:
2.2.1) LEFT ( выборка будет содержать все строки из первой или левой таблицы):
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.Id
2.2.2) RIGHT (выборка будет содержать все строки из второй или правой таблицы)
2.2.3) FULL (выборка будет содержать все строки из обоих таблиц)
2.2.4) CROSS JOIN (перекрестное (каждая строка из одной таблицы соединяется с каждой строкой из второй таблицы))
3) Соединение однотипных выборок:
- UNION (без дубликатов, соответствие столбцов)
- EXCEPT (разность двух выборок)
- INTERSECT (пересечение (общие строки))
Сравнение INNER и OUTER JOIN
LEFT JOIN взял все строки из таблицы Customers, не смотря на то, что кто-то из них не имеет заказа, а INNER JOIN берет только те, которые связаны со второй таблицей.
T-SQL DDL (Data Definition Language)
T-SQL DDL (Data Definition Language):
1) Создание и удаление БД
2) Создание и удаление таблиц
3) Изменение таблицы
Пример:
CREATE DATABASE internetstore;
GO
USE internetstore;
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
);
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt DATE,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);
T-SQL Пакеты команд
Пакеты команд - набор команд в скрипте, применяют потому, что одни выражения должны успешно выполниться до запуска других выражений, в качестве сигнала завершения пакета (т.е. выполнения всех его команд) служит команда GO
T-SQL DML (Data Manipulation Language)
T-SQL DML (Data Manipulation Language):
1) INSERT
2) SELECT
3) ORDER BY
4) WHERE
5) UPDATE
6) DELETE
7) GROUP BY, HAVING
T-SQL Встроенные функции для строк
T-SQL Встроенные функции для строк:
- LEN: возвращает количество символов в строке
- LTRIM: удаляет начальные пробелы из строки.
- RTRIM: удаляет конечные пробелы из строки
- CHARINDEX: возвращает индекс, по которому находится первое вхождение подстроки в строке
- PATINDEX: возвращает индекс, по которому находится первое вхождение определенного шаблона в строке
- LEFT: вырезает с начала строки определенное количество символов
- RIGHT: вырезает с конца строки определенное количество символов.
- SUBSTRING: вырезает из строки подстроку определенной длиной, начиная с определенного индекса
- REPLACE: заменяет одну подстроку другой в рамках строки
- REVERSE: переворачивает строку наоборот
- CONCAT: объединяет две строки в одну
- LOWER: переводит строку в нижний регистр
- UPPER: переводит строку в верхний регистр
- SPACE: возвращает строку, которая содержит определенное количество пробелов
T-SQL Встроенные функции для чисел
T-SQL Встроенные функции для чисел:
- ROUND: округляет число
- ISNUMERIC: определяет, является ли значение числом
- ABS: возвращает абсолютное значение числа
- CEILING: возвращает наименьшее целое число, которое больше или равно текущему значению
- FLOOR: возвращает наибольшее целое число, которое меньше или равно текущему значению
- SQUARE: возводит число в квадрат
- SQRT: получает квадратный корень числа
- RAND: генерирует случайное число с плавающей точкой в диапазоне от 0 до 1
- COS: возвращает косинус угла, выраженного в радианах
- SIN: возвращает синус угла, выраженного в радианах
- TAN: возвращает тангенс угла, выраженного в радианах
T-SQL Встроенные функции для даты/времени
T-SQL Встроенные функции для даты/времени:
- GETDATE: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime
- GETUTCDATE: возвращает текущую локальную дату и время по гринвичу (UTC/GMT)
- SYSDATETIME: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime2
- SYSUTCDATETIME
- DAY: возвращает день даты, который передается в качестве параметра
- MONTH: возвращает месяц даты
- YEAR: возвращает год из даты
- DATENAME: возвращает часть даты в виде строки
- DATEPART: возвращает часть даты в виде числа
- DATEADD: возвращает дату, которая является результатом сложения числа к определенному компоненту даты
- DATEDIFF: возвращает разницу между двумя датами.
- ISDATE: проверяет, является ли выражение датой
- DATEFROMPARTS: по году, месяцу и дню создает дату
T-SQL Встроенные функции для преобразования данных
T-SQL Встроенные функции для преобразования данных:
- CAST (преобразует выражение одного типа к другому.)
- CONVERT (преобразование с форматированием)
- TRY_CONVERT (если выражение не удается преобразовать к нужному типу, то функция возвращает NULL)
- STR(float [, length [,decimal]]): преобразует число в строку. Второй параметр указывает на длину строки, а третий - сколько знаков в дробной части числа надо оставлять
- CHAR(int): преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры
- ASCII(char): преобразует символ в числовой код ASCII
- NCHAR(int): преобразует числовой код UNICODE в символ
- UNICODE(char): преобразует символ в числовой код UNICODE
T-SQL Встроенные функции для создания условий
T-SQL Встроенные функции для создания условий:
- CASE .. WHEN…END (проверяет значение некоторого выражение, и в зависимости от результата проверки может возвращать тот или иной результат):
SELECT ProductName, Manufacturer,
CASE ProductCount
WHEN 1 THEN ‘Товар заканчивается’
WHEN 2 THEN ‘Мало товара’
WHEN 3 THEN ‘Есть в наличии’
ELSE ‘Много товара’
END AS EvaluateCount
FROM Products
- IIF (в зависимости от результата условного выражения возвращает одно из двух значений - типо тернарный оператора):
SELECT ProductName, Manufacturer,
IIF(ProductCount>3, ‘Много товара’, ‘Мало товара’)
FROM Products
T-SQL Основы
T-SQL - процедурное расширение языка SQL с новыми возможностями:
- Обработка ошибок с помощью TRY / CATCH
- управляющие операторы,
- Ветвления, циклы: IF / ELSE, WHILE, блок BEGIN .. END
- локальные и глобальные переменные:
DECLARE @i;
SET @i = 0;
- различные дополнительные функции для обработки строк, дат, математики ит.п.,
- поддержка аутентификации Microsoft Windows.
Типы команд T-SQL:
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
Транзакционные уровни изоляции
Транзакционные уровни изоляции:
- READ UNCOMMITTED - Разрешает грязные чтения. Самый низкий уровень
- READ COMMITTED - Запрещает грязные чтения. Режим по умолчанию
- REPEATABLE READ - Гарантирует, что повторные выборки возвращают одно и то же
- SNAPSHOT - Делает слепок базы данных в начале транзакции
- SERIALIZABLE - Блокирует другим транзакциям
Высокие уровни изоляции снижают скорость работы
T-SQL Переменные
Переменная - именованный объект, хранящий значение:
DECLARE @name NVARCHAR(20);
SET @name=’Tom’;
Присваивание переменной результата запроса:
DECLARE @count INT;
SET @count = (SELECT SUM(ProductCount) FROM Orders);
Переменные в сложных запросах:
DECLARE @sum MONEY, @id INT, @prodid INT, @name NVARCHAR(20);
SET @id=2;
SELECT @sum = SUM(Orders.Price*Orders.ProductCount),
@name=Products.ProductName, @prodid = Products.Id
FROM Orders
INNER JOIN Products ON ProductId = Products.Id
GROUP BY Products.ProductName, Products.Id
HAVING Products.Id=@id
T-SQL Условные выражения
1) IF … ELSE:
IF @count > 0
BEGIN
PRINT ‘Дата последнего заказа: ‘ + CONVERT(NVARCHAR, @lastDate)
PRINT ‘Продано ‘ + CONVERT(NVARCHAR, @count) + ‘ единиц(ы)’
PRINT ‘На общую сумму ‘ + CONVERT(NVARCHAR, @sum)
END;
ELSE
PRINT ‘Заказы в базе данных отсутствуют’
T-SQL Циклы
1) WHILE:
WHILE @number > 0
BEGIN
SET @factorial = @factorial * @number
SET @number = @number - 1
END;
PRINT @factorial
T-SQL Обработка ошибок
Конструкция TRY…CATCH:
В блоке CATCH можно использовать функции:
- ERROR_NUMBER (возвращает номер ошибки)
- ERROR_MESSAGE (возвращает сообщение об ошибке)
- ERROR_SEVERITY (возвращает степень серьезности ошибки)
- ERROR_STATE (возвращает состояние ошибки)
Пример:
CREATE TABLE Accounts (FirstName NVARCHAR NOT NULL, Age INT NOT NULL)
BEGIN TRY
INSERT INTO Accounts VALUES(NULL, NULL)
PRINT 'Данные успешно добавлены!'
END TRY
BEGIN CATCH
PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ':' + ERROR_MESSAGE()
END CATCH
T-SQL Представления View
Представления (View) - виртуальные таблицы (могут иметь не более 1024 столбцов и обращаться не более чем к 256 таблицам):
- не хранит данные самостоятельно, а предоставляет определенный способ просмотра данных из одной или нескольких таблиц
- содержат запросы, которые динамически извлекают используемые данные
- упрощают комплексные SQL-операции
- могут дать доступ к части таблицы, а не ко всей таблице
- позволяют возвращать отформатированные значения из таблиц
- можно создавать представления на основе других представлений (<= 32)
Пример:
CREATE VIEW OrdersProductsCustomers AS
SELECT Orders.CreatedAt AS OrderDate,
Customers.FirstName AS Customer,
Products.ProductName As Product
FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id
INNER JOIN Customers ON Orders.CustomerId = Customers.Id
//удаление представления DROP VIEW OrdersProductsCustomers
T-SQL Табличные переменные
Табличные переменные - переменные с типом TABLE, позволяющие сохранить содержимое целой таблицы, они живут в пределах пакета в оперативной памяти (после завершения пакета они удаляются):
Пример:
//создание табличной переменной со столбцами
DECLARE @ABrends TABLE (ProductId INT, ProductName NVARCHAR(20))
T-SQL Временные таблицы
Временные таблицы - существуют на протяжении сессии БД, хранятся в таблице tempdb (MS SQL Server), с ней можно работать как с обычной таблицей, они удобны для хранения каких-то временных промежуточных данных.
Пример локальной временной таблицы:
CREATE TABLE #ProductSummary
(ProdId INT IDENTITY,
ProdName NVARCHAR(20),
Price MONEY)
INSERT INTO #ProductSummary
VALUES ('Nokia 8', 18000),
('iPhone 8', 56000)
SELECT * FROM #ProductSummary
Пример глобальной временной таблицы:
CREATE TABLE ##OrderDetails
(ProductId INT, TotalCount INT, TotalSum MONEY)
T-SQL Обобщенные табличные выражения
Обобщенные табличные выражения (common table expression (CTE)) - являются производными от обычного запроса (более эффективны, чем временные таблицы), хранятся в оперативной памяти и живут только во время выполнения первого запроса:
WITH OrdersInfo AS
(
SELECT ProductId,
SUM(ProductCount) AS TotalCount,
SUM(ProductCount * Price) AS TotalSum
FROM Orders
GROUP BY ProductId
)
SELECT * FROM OrdersInfo -- здесь нормально SELECT * FROM OrdersInfo -- здесь ошибка SELECT * FROM OrdersInfo -- здесь ошибка
Плюсы CTE:
- улучшает читаемость кода
- Позволяет разбивать сложные запросы на логические части
- Поддерживает рекурсивные запросы
T-SQL Триггеры
Триггер - специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, данные об изменяемых данных триггер берет из виртуальных таблиц:
- INSERTED
- DELETED
Пример:
Создание триггера:
USE productdb;
GO
CREATE TRIGGER Products_INSERT_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
UPDATE Products
SET Price = Price + Price * 0.38
WHERE Id = (SELECT Id FROM inserted)
Удаление триггера:
DROP TRIGGER Products_INSERT_UPDATE
Отключение триггера:
DISABLE TRIGGER Products_INSERT_UPDATE ON Products
Включение триггера:
ENABLE TRIGGER Products_INSERT_UPDATE ON Products
Триггер INSTEAD OF (срабатывает вместо операции с данными):
USE prods
GO
CREATE TRIGGER products_delete
ON Products
INSTEAD OF DELETE
AS
UPDATE Products
SET IsDeleted = 1
WHERE ID =(SELECT Id FROM deleted)
T-SQL Хранимые процедуры
Хранимая процедура - набор инструкций, которые выполняются как единое целое:
- выполняются быстрее, чем обычные SQL-инструкции, т.к. код процедуры компилируются один раз и затем переиспользуется.
Создание процедуры:
CREATE PROCEDURE ProductSummary AS
SELECT ProductName AS Product, Manufacturer, Price
FROM Products
Выполнение:
EXEC ProductSummary
Удаление процедуры:
DROP PROCEDURE ProductSummary
Параметры в процедурах:
- Входные параметры могут быть необязательными, если задать им значение по умолчанию:
CREATE PROCEDURE AddProduct
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
@count INT,
@price MONEY
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
– ее использование
EXEC AddProduct @prodName, @company, @prodCount, @price
- Выходные параметры определяются с помощью слова OUTPUT:
CREATE PROCEDURE GetPriceStats
@minPrice MONEY OUTPUT,
@maxPrice MONEY OUTPUT
AS
SELECT @minPrice = MIN(Price), @maxPrice = MAX(Price)
FROM Products
– ее использование
DECLARE @minPrice MONEY, @maxPrice MONEY
EXEC GetPriceStats @minPrice OUTPUT, @maxPrice OUTPUT - Возвращаемое значение (только типа INT) с помощью оператора RETURN:
CREATE PROCEDURE GetAvgPrice AS
DECLARE @avgPrice MONEY
SELECT @avgPrice = AVG(Price)
FROM Products
RETURN @avgPrice;
– ее использование
DECLARE @result MONEY
EXEC @result = GetAvgPrice
Какие типы СУБД в соответствии с моделями данных вы знаете?
1) Реляционные БД (поддерживают установку связей между таблицами с помощью первичных и внешних ключей)
2) Key-value БД (хранение данных по принципу ключ-значение): Redis, Memcached
3) Документо-ориентированные БД (хранение иерархических структур данных (документов)): MongoDB
4) Графовые БД (Данные и их связи представляются как вершины и ребра графа): Neo4J
5) Поисковые БД (совмещают с функционалом хранения информации еще и функционал поиска по текстам): Elasticsearch
Что такое первичный ключ?
Первичный ключ - предназначен для однозначной идентификации каждой записи в таблице и является строго уникальным.
Что такое внешний ключ?
Внешний ключ - это ограничение, обеспечивает связь двух таблиц. По сути, это поле или несколько полей, которые ссылаются на PRIMARY KEY в родительской таблице:
- обеспечивает ссылочную целостность, гарантируя, что значения в столбце внешнего ключа соответствуют существующим значениям в связанной таблице
- Ограничивается вставка некорректных данных (невозможно вставить значение в столбец внешнего ключа, если такого значения нет в связанной таблице)
- Ограничивается удаление связанных записей (невозможно удалить без дополнительных действий запись из родительской таблицы, если на нее ссылаются записи в дочерней таблице)
Основные типы соединения в SQL
INNER JOIN — получение записей с одинаковыми значениями в обеих таблицах, т.е. получение пересечения таблиц.
FULL OUTER JOIN — объединяет записи из обеих таблиц (если условие объединения равно true) и дополняет их всеми записями из обеих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL.
LEFT JOIN — возвращает все записи, удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней (левой) таблицы, которые не удовлетворяют условию объединения (недостающее поле будет иметь значение NULL).
RIGHT JOIN — работает точно так же, как и левое объединение, только в качестве внешней таблицы будет использоваться правая (недостающее поле будет иметь значение NULL).
Что такое Self JOIN?
Self JOIN - это когда таблица объединяется сама с собой, чтобы такое реализовать нужно использовать псевдонимы:
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.CityFROM Customers A, Customers BWHERE A.CustomerID <> B.CustomerIDAND A.City = B.CityORDER BY A.City;
Для чего нужен оператор UNION?
UNION - используется для объединения полученных данных из двух или более запросов, которые должны иметь одинаковое количество столбцов с одинаковыми типами данных и расположенных в том же порядке
Какими бывают подстановочные знаки?
% — заменить ноль или более символов;
_ — заменить один символ.
Что такое нормализация и денормализация?
Нормализация отношений в SQL призвана организовать информацию в базе данных таким образом, чтобы она не занимала много места и с ней было удобно работать. Это удаление избыточных данных, устранение дублей, идентификация наборов связанных данных через PRIMARY KEY.
Денормализация является обратным процессом, который вносит в нормализованную таблицу избыточные данные
Для чего нужен оператор INSERT INTO SELECT?
INSERT INTO SELECT - копирует данные из одной таблицы и вставляет их в другую, при этом типы данных в обеих таблицах должны соответствовать
Объясните разницу между командами DELETE и TRUNCATE
Команда DELETE — это DML-операция, которая удаляет записи из таблицы, соответствующие заданному условию.
TRUNCATE — это DDL-операция, которая полностью пересоздаёт таблицу, и отменить такое удаление невозможно.
Чем VARCHAR отличается от NVARCHAR?
VARCHAR - хранит значения в формате ASCII (1 символ занимает 1 байт).
NVARCHAR - хранит значения в формате Unicode, где символ занимает 2 байта.
Что такое SQL и для чего он используется?
SQL (Structured Query Language) — это стандартный язык для взаимодействия с реляционными базами данных. Он используется для определения, управления и извлечения данных из БД.
Объясните различия между DDL, DML и DCL в SQL?
1) DDL (Data Definition Language) — язык определения данных:
Используется для определения структуры базы данных (схемы).
Основные команды: CREATE, ALTER, DROP, TRUNCATE, RENAME.
2) DML (Data Manipulation Language) — язык манипулирования данными:
Используется для работы с данными внутри таблиц.
Основные команды: SELECT, INSERT, UPDATE, DELETE.
3) DCL (Data Control Language) — язык управления доступом:
Используется для управления правами доступа к базе данных.
Основные команды: GRANT, REVOKE.
Как удалить дубликаты в результате SQL-запроса?
Использовать ключевое слово DISTINCT в операторе SELECT, чтобы вернуть только уникальные записи:
SELECT DISTINCT position
FROM employees;
Объясните разницу между WHERE и HAVING
1) WHERE:
- Фильтрует строки до группировки данных.
- Не может использовать агрегатные функции (SUM(), COUNT(), AVG(), и т.д.).
- Применяется к отдельным записям таблицы.
2) HAVING:
- Фильтрует группы строк после группировки данных.
- Может использовать агрегатные функции.
- Применяется к результатам GROUP BY.
Свойства транзакций (ACID)
Свойства транзакций (ACID):
1) Атомарность (Atomicity):
- Транзакция выполняется полностью или не выполняется вовсе (Если происходит сбой, все изменения отменяются).
2) Согласованность (Consistency):
- Транзакция переводит базу данных из одного согласованного состояния в другое.
- Все правила и ограничения базы данных соблюдаются.
3) Изоляция (Isolation):
- Результаты транзакции невидимы для других транзакций до ее завершения.
- Предотвращает взаимное влияние параллельных транзакций.
4) Долговечность (Durability):
- После успешного завершения транзакции ее результаты сохраняются даже при сбоях системы.
- Изменения записываются на постоянное хранилище.
Оператор LIKE
Оператор LIKE - используется в условиях WHERE для поиска строк, соответствующих определенному шаблону с подстановочными символами
Что такое агрегатные функции?
Агрегатные функции - выполняют вычисления над набором значений и возвращают одно значение:
- COUNT() — подсчитывает количество строк.
- SUM() — вычисляет сумму значений.
- AVG() — вычисляет среднее значение.
- MAX() — находит максимальное значение.
- MIN() — находит минимальное значение.
Объясните разницу между UNION и UNION ALL
UNION - объединяет результаты запросов без дубликатов
UNION ALL - объединяет результаты запросов с дубликатами
Что такое хранимая процедура и как она отличается от функции?
1) Хранимая процедура:
- Набор SQL-команд, сохраненных на сервере для повторного использования
- Может выполнять операции SELECT, INSERT, UPDATE, DELETE
- Не может быть вызвана внутри SQL-запроса
- Может иметь входные и выходные параметры
- может быть void
2) Функция:
- Возвращает одно значение (скалярная функция) или таблицу (табличная функция)
- Может использоваться в SQL-выражениях (например, в SELECT или WHERE)
- Должна возвращать значение
- Обычно используется для вычислений и возвращает детерминированный результат.
Что такое ограничения (constraints) и какие виды существуют?
Ограничения обеспечивают целостность и надежность данных в таблице, определяя правила для данных в столбцах.
- NOT NULL
- UNIQUE
- PRIMARY KEY (комбинация NOT NULL + UNIQUE)
- FOREIGN KEY (Значение должно соответствовать существующему значению первичного ключа в связанной таблице)
- DEFAULT (Устанавливает значение по умолчанию для столбца, если значение не указано при вставке)
- CHECK (Определяет условие, которому должны соответствовать значения в столбце):
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);
Что такое SQL-инъекция и как от нее защититься?
SQL-инъекция — это метод атаки на базу данных, при котором злоумышленник вставляет вредоносный SQL-код через вводимые данные, позволяя выполнять несанкционированные SQL-запросы.
Способы защиты от SQL-инъекций:
1) Параметризированные запросы (Использование параметров вместо конкатенации строк):
- СУБД автоматически экранирует специальные символы
2) Использование ORM (Object-Relational Mapping):
- Библиотеки ORM часто включают механизмы защиты от SQL-инъекций
3) Проверка и фильтрация вводимых данных:
- Проверять данные на соответствие ожидаемому формату
- Использовать валидацию на стороне сервера и клиента
4) Использование хранимых процедур:
- Логика работы с данными инкапсулирована в процедуре
Функции для работы с NULL
1) COALESCE (Возвращает первый элемент списка не равный NULL)
2) ISNULL (Возвращает 1 или 0 в зависимости равно ли выражение NULL)
3) IFNULL (Возвращает значение, переданное 1-ым аргументом, если оно не равно NULL. В противном случае, возвращает значение переданное вторым аргументом)
Объясните различия между CHAR и VARCHAR
1) CHAR(n):
- Хранит строки фиксированной длины n
- Если введенная строка короче n, она дополняется пробелами до длины n
- всегда занимает фиксированное количество памяти
2) VARCHAR(n):
- Хранит строки переменной длины до n символов
- более эффективен с точки зрения использования памяти, но может быть немного медленнее при доступе.
Применение временной таблицы
Применение временной таблицы:
- Хранение промежуточных результатов в сложных запросах.
- Обработка больших объемов данных в пакетных операциях
- Избегание конфликтов при одновременной работе нескольких пользователей.
Что такое оконные функции в SQL?
Оконные функции — это функции, которые выполняют вычисления по набору строк (окну), связанных с текущей строкой, и возвращают результат для каждой строки без группировки данных.
Как удалить таблицу вместе с ее данными?
DROP TABLE:
- удаляет таблицу
- удаляет все, связанные с ней, данные, индексы, триггеры
- необратимо (если не настроено резервное копирование или механизмы восстановления)
Как добавить новый столбец в существующую таблицу? ALTER TABLE
ALTER TABLE table_name
ADD column_name data_type [constraints];
Выбор между командами DELETE, TRUNCATE и DROP
- DELETE используется, когда нужно удалить определенные записи.
- TRUNCATE используется для быстрого удаления всех данных из таблицы, сохраняя ее структуру.
- DROP используется для полного удаления таблицы из базы данных
Как выполнить резервное копирование и восстановление базы данных?
1) PostgreSQL:
- Резервное копирование с помощью утилиты pg_dump:
pg_dump -U username mydatabase > backup.sql
- Восстановление базы данных с помощью утилиты psql:
psql -U username mydatabase < backup.sql
Как реализовать отношения многие-ко-многим в SQL?
Нужны три таблицы (2 основные и 1 промежуточная)
Пример:
–Создание таблицы студентов:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100)
);
–Создание таблицы курсов:
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
Title VARCHAR(100)
);
–Создание промежуточной таблицы для установления связи многие-ко-многим:
CREATE TABLE StudentCourse (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
Объясните использование функций хеширования в SQL
Хеш-функции в SQL - используются для преобразования входных данных произвольной длины в фиксированную строку определенной длины:
- применяется для хранения паролей в зашифрованном виде
