Skip to content

Latest commit

 

History

History
708 lines (542 loc) · 49.5 KB

SQL.md

File metadata and controls

708 lines (542 loc) · 49.5 KB
Raw

SQL

Содержание:

Что такое СУБД?

Чтобы правильно обрабатывать различные запросы (поиск, изменение, удаление и т.д) в базу данных, нужно специальное программное обеспечение, такое ПО получило название системы управления базами данных (СУБД).

СУБД — это общий термин, относящийся ко всем видам абсолютно разных инструментов, от компьютерных программ до встроенных библиотек. Эти приложения управляют или помогают управлять наборами данных. Так как эти данные могут быть разного формата и размера, были созданы разные виды СУБД.

СУБД основаны на моделях баз данных — определённых структурах для обработки данных. Каждая СУБД создана для работы с одной из них с учётом особенностей операций над информацией.

Хотя решений, реализующих различные модели баз данных, очень много, периодически некоторые из них становятся очень популярными и используются на протяжении многих лет. Сейчас самой популярной моделью является реляционная система управления базами данных (РСУБД).

Какие типы СУБД в соответствии с моделями данных существуют?

(наверх)

  • Реляционные, которые поддерживают установку связей между таблицами с помощью первичных и внешних ключей. Пример — MySQL.
  • Flat File — базы данных с двумерными файлами, в которых содержатся записи одного типа и отсутствует связь с другими файлами, как в реляционных. Пример — Excel.
  • Иерархические подразумевают наличие записей, связанных друг с другом по принципу отношений один-к-одному или один-ко-многим. А вот для отношений многие-ко-многим следует использовать реляционную модель. Пример — Adabas.
  • Сетевые похожи на иерархические, но в этом случае «ребёнок» может иметь несколько «родителей» и наоборот. Примеры — IDS и IDMS.
  • Объектно-ориентированные СУБД работают с базами данных, которые состоят из объектов, используемых в ООП. Объекты группируются в классы и называются экземплярами, а классы в свою очередь взаимодействуют через методы. Пример — Versant.
  • Объектно-реляционные обладают преимуществами реляционной и объектно-ориентированной моделей. Пример — IBM Db2.
  • Многомерная модель является разновидностью реляционной и использует многомерные структуры. Часто представляется в виде кубов данных. Пример — Oracle Essbase.
  • Гибридные состоят из двух и более типов баз данных. Используются в том случае, если одного типа недостаточно для обработки всех запросов. Пример — Altibase HDВ.

Что такое первичный ключ?

(наверх)

Первичный ключ или PRIMARY KEY предназначен для однозначной идентификации каждой записи в таблице и является строго уникальным (UNIQUE): две записи таблицы не могут иметь одинаковые значения первичного ключа. Нулевые значения (NULL) в PRIMARY KEY не допускаются. Если в качестве PRIMARY KEY используется несколько полей, их называют составным ключом.
Первичный ключ, используется в качестве основного ключа и может быть использован для связи с дочерней таблицей, содержащей внешний ключ.

Пример:

CREATE TABLE USERS (
  id INT NOT NULL,
  name VARCHAR (20) NOT NULL,
  PRIMARY KEY (id)
);

Здесь в качестве первичного ключа используется поле id.

Что такое внешний ключ?

(наверх)

Внешний ключ или FOREIGN KEY также является атрибутом ограничения и обеспечивает связь двух таблиц. По сути, это поле или несколько полей, которые ссылаются на PRIMARY KEY в родительской таблице.

Пример использования:

CREATE TABLE order (
  order_id INT NOT NULL,
  user_id INT,
  PRIMARY KEY (order_id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

В данном случае внешний ключ, привязанный к полю user_id в таблице order, ссылается на первичный ключ id в таблице users, и именно по этим полям происходит связывание двух таблиц.

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

(наверх)

SQL-ограничения (constraints) указываются при создании или изменении таблицы. Это правила для ограничения типа данных, которые могут храниться в таблице. Действие с данными не будет выполнено, если нарушаются установленные ограничения.

  • UNIQUE — гарантирует уникальность значений в столбце;
  • NOT NULL — значение не может быть NULL;
  • INDEX — создаёт индексы в таблице для быстрого поиска/запросов;
  • CHECK — значения столбца должны соответствовать заданным условиям;
  • DEFAULT — предоставляет столбцу значения по умолчанию.

Что такое Self JOIN?

(наверх)

Это выражение используется для того, чтобы таблица объединилась сама с собой, словно это две разные таблицы. Чтобы такое реализовать, одна из таких «таблиц» временно переименовывается.

Следующий SQL-запрос объединяет клиентов из одного города:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

Подстановочные знаки

(наверх)

Это специальные символы, которые нужны для замены каких-либо знаков в запросе. Они используются вместе с оператором LIKE, с помощью которого можно отфильтровать запрашиваемые данные.

% — заменить ноль или более символов; _ — заменить один символ. Примеры:

SELECT * FROM user WHERE name LIKE '%test%';

Данный запрос позволяет найти данные всех пользователей, имена которых содержат в себе «test».

SELECT * FROM user WHERE name LIKE 't_est';

В этом случае имена искомых пользователей начинаются на «t», после содержат какой-либо символ и «est» в конце.

Что делают псевдонимы Aliases?

(наверх)

SQL-псевдонимы нужны для того, чтобы дать временное имя таблице или столбцу. Это нужно, когда в запросе есть таблицы или столбцы с неоднозначными именами. В этом случае для удобства в составлении запроса используются псевдонимы. SQL-псевдоним существует только на время запроса.

Пример:

SELECT very_long_column_name AS alias_name
FROM table;

Разница между командами DELETE и TRUNCATE

(наверх)

Команда DELETE — это DML-операция, которая удаляет записи из таблицы, соответствующие заданному условию:

DELETE FROM table_name WHERE condition; При этом создаются логи удаления, то есть операцию можно отменить.

А вот команда TRUNCATE — это DDL-операция, которая полностью пересоздаёт таблицу, и отменить такое удаление невозможно:

TRUNCATE TABLE table_name;

DML (Data Manipulation Language) - язык манипулирования данными. Язык DML позволяет осуществлять манипуляции с данными таблиц, т.е. с ее строками. Он позволяет делать выборку данных из таблиц, добавлять новые данные в таблицы, а так же обновлять и удалять существующие данные.

DDL (Data Definition Language) - язык описания данных. Язык DDL служит для создания и модификации структуры БД, т.е. для создания/изменения/удаления таблиц и связей.

Разница между WHERE и HAVING

(наверх)

В сущности, HAVING очень похож на WHERE - это тоже фильтр. Вы можете написать в HAVING name = ‘Anna’, как и в WHERE, и ошибки не будет.

SELECT username, COUNT(*)
FROM table
WHERE username = ‘Anna’
GROUP BY username
HAVING COUNT(*)>1

В HAVING и только в нём можно писать условия по агрегатным функциям (SUM, COUNT, MAX, MIN и т. д.). То есть если вы хотите сделать что-то вроде COUNT(*) > 10, то это возможно сделать только в HAVING.

Почему бы не оставить только HAVING? Всё кроется в том, как SQL Server выполняет запрос, в каком порядке происходит его разбор и работа с данными. WHERE выполняется до формирования групп GROUP BY. Это нужно для того, чтобы можно было оперировать как можно меньшим количеством данных и сэкономить ресурсы сервера и время пользователя.

Следующим этапом формируются группы, которые указаны в GROUP BY. После того как сформированы группы, можно накладывать условия на результаты агрегатных функций. И тут как раз наступает очередь HAVING: выполняются условия, которые вы задали.

Главное отличие HAVING от WHERE в том, что в HAVING можно наложить условия на результаты группировки, потому что порядок исполнения запроса устроен таким образом, что на этапе, когда выполняется WHERE, ещё нет групп, а HAVING выполняется уже после формирования групп.

Основные команды SQL

(наверх)

  • SHOW DATABASES

    SQL-команда, которая отвечает за просмотр доступных баз данных.

  • CREATE DATABASE

    Команда для создания новой базы данных.

  • USE

    С помощью этой SQL-команды USE <database_name> выбирается база данных, необходимая для дальнейшей работы с ней.

  • SOURCE

    SOURCE <file.sql> позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.

  • DROP DATABASE

    Стандартная SQL-команда для удаления целой базы данных.

  • DROP TABLE

    Так можно удалить всю таблицу целиком.

  • DELETE

    SQL-команда DELETE FROM <table_name> используется для удаления данных из таблицы.

  • SHOW TABLES

    С помощью этой команды можно увидеть все таблицы, которые доступны в базе данных.

  • DESCRIBE

    С помощью DESCRIBE <table_name> можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы.

  • CREATE TABLE SQL-команда для создания новой таблицы:

    CREATE TABLE <table_name1> (
  <col_name1><col_type1>,
  <col_name2><col_type2>,
  <col_name3><col_type3>
  PRIMARY KEY(<col_name1>),
  FOREIGN KEY(<col_name2>) REFERENCES <table_name2>(<col_name2>)
);

Ограничения целостности при использовании CREATE TABLE
Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

  • ячейка таблицы не может иметь значение NULL;
  • первичный ключ — PRIMARY KEY(col_name1, col_name2, …);
  • внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример Создайте таблицу «instructor»:

  CREATE TABLE instructor (
    ID CHAR(5),
    name VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary NUMERIC(8,2),
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
  );

  • UNION

    Он используется для объединения полученных данных из двух или более запросов, которые должны иметь одинаковое количество столбцов с одинаковыми типами данных и расположенных в том же порядке.

    Пример использования:

    SELECT column(s) FROM first_table
UNION
SELECT column(s) FROM second_table;

  • INSERT

    Команда INSERT INTO <table_name> в SQL отвечает за добавление данных в таблицу:

    INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …)
  VALUES (<value1>, <value2>, <value3>, …);

    При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

    INSERT INTO <table_name>
  VALUES (<value1>, <value2>, <value3>, …);

  • UPDATE

    SQL-команда для обновления данных таблицы:

    UPDATE <table_name>
  SET <col_name1> = <value1>, <col_name2> = <value2>, ...
  WHERE <condition>;

  • SELECT

    SELECT используется для получения данных из выбранной таблицы:

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>;

    Следующей командой можно вывести все данные из таблицы:

    SELECT * FROM <table_name>;

  • SELECT DISTINCT

    В столбцах таблицы могут содержаться повторяющиеся данные. SELECT DISTINCT используется для получения только неповторяющихся данных.

    SELECT DISTINCT <col_name1>, <col_name2>, …
  FROM <table_name>;

  • WHERE

    Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>;

    В запросе можно задавать следующие условия:

    сравнение текста;
    сравнение численных значений;
    логические операции AND (и), OR (или) и NOT (отрицание).
    Пример:

    SELECT * FROM table WHERE author='Достоевский';
SELECT * FROM table WHERE price>3000;
SELECT * FROM table WHERE amount=10;

  • GROUP BY

    Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT, MAX, MIN, SUM и AVG, для группировки выходных значений.

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  GROUP BY <col_namex>;

    Пример Выведем количество курсов для каждого факультета:

    SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name;

  • HAVING

    Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.

    SELECT <col_name1>, <col_name2>, ...
  FROM <table_name>
  GROUP BY <column_namex>
  HAVING <condition>

    Пример Выведем список факультетов, у которых более одного курса:

    SELECT COUNT(course_id), dept_name
  FROM course
  GROUP BY dept_name
  HAVING COUNT(course_id)>1;

  • ORDER BY

    ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  ORDER BY <col_name1>, <col_name2>, … ASC|DESC;

    Пример Выведем список курсов по возрастанию и убыванию количества кредитов:

    SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;

  • BETWEEN

    BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> BETWEEN <value1> AND <value2>;

    Пример Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

    SELECT * FROM instructor
  WHERE salary BETWEEN 50000 AND 100000;

  • LIKE

    Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.

    Есть два свободных оператора, которые используются в LIKE:

    % (ни одного, один или несколько символов);
    _ (один символ).

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namex> LIKE <pattern>;

    Пример Выведем список курсов, в имени которых содержится «to», и список курсов, название которых начинается с «CS-»:

    SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___';

  • IN

    С помощью IN можно указать несколько значений для оператора WHERE:

    SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <col_namen> IN (<value1>, <value2>, …);

    Пример Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

    SELECT * FROM student
  WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

  • JOIN

    JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. Чтобы объединить две таблицы в одну, следует использовать оператор JOIN. Соединение таблиц может быть внутренним (INNER) или внешним (OUTER), причём внешнее соединение может быть левым (LEFT), правым (RIGHT) или полным (FULL).

    • INNER JOIN — получение записей с одинаковыми значениями в обеих таблицах, т.е. получение пересечения таблиц.

    • FULL OUTER JOIN — объединяет записи из обеих таблиц (если условие объединения равно true) и дополняет их всеми записями из обеих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL.

    • LEFT JOIN — возвращает все записи, удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней (левой) таблицы, которые не удовлетворяют условию объединения.

    • RIGHT JOIN — работает точно так же, как и левое объединение, только в качестве внешней таблицы будет использоваться правая.

      SELECT <col_name1>, <col_name2>, …
FROM <table_name1>
JOIN <table_name2>
ON <table_name1.col_namex> = <table2.col_namex>;

    Следующий запрос выбирает все заказы с информацией о клиенте:

    SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

  • VIEW

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

    Создание

    CREATE VIEW <view_name> AS
  SELECT <col_name1>, <col_name2>, …
  FROM <table_name>
  WHERE <condition>;

    Удаление

    DROP VIEW <view_name>;

Математические функции в SQL

(наверх)

Функция Описание Пример
CEILING(x) возвращает наименьшее целое число, большее или равное x(округляет до целого числа в большую сторону) CEILING(4.2)=5 CEILING(-5.8)=-5
ROUND(x, k) округляет значение x до k знаков после запятой, если k не указано – x округляется до целого ROUND(4.361)=4 ROUND(5.86592,1)=5.9
FLOOR(x) возвращает наибольшее целое число, меньшее или равное x (округляет до целого числа в меньшую сторону) FLOOR(4.2)=4 FLOOR(-5.8)=-6
POWER(x, y) возведение x в степень y POWER(3,4)=81.0
SQRT(x) квадратный корень из x SQRT(4)=2.0 SQRT(2)=1.41...
DEGREES(x) конвертирует значение x из радиан в градусы DEGREES(3) = 171.8...
RADIANS(x) конвертирует значение x из градусов в радианы RADIANS(180)=3.14...
ABS(x) модуль числа x ABS(-1) = 1 ABS(1) = 1
PI() pi = 3.1415926...
COUNT(col_name) возвращает количество строк
SUM(col_name) возвращает сумму значений в данном столбце
AVG(col_name) возвращает среднее значение данного столбца
MIN(col_name) возвращает наименьшее значение данного столбца
MAX(col_name) возвращает наибольшее значение данного столбца

Оконные функции в SQL

(наверх)

Оконная функция выполняет вычисления для набора строк, некоторым образом связанных с текущей строкой. Можно сравнить её с агрегатной функцией, но, в отличие от обычной агрегатной функции, при использовании оконной функции несколько строк не группируются в одну, а продолжают существовать отдельно. Внутри же, оконная функция, как и агрегатная, может обращаться не только к текущей строке результата запроса.

Вот пример, показывающий, как сравнить зарплату каждого сотрудника со средней зарплатой его отдела:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
  FROM empsalary;
  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

Первые три столбца извлекаются непосредственно из таблицы empsalary, при этом для каждой строки таблицы есть строка результата. В четвёртом столбце оказалось среднее значение, вычисленное по всем строкам, имеющим то же значение depname, что и текущая строка. (Фактически среднее вычисляет та же функция avg, которую мы знаем как агрегатную, но предложение OVER превращает её в оконную, так что она обрабатывает лишь заданный набор строк.)

Вызов оконной функции всегда содержит предложение OVER, следующее за названием и аргументами оконной функции. Это синтаксически отличает её от обычной или агрегатной функции. Предложение OVER определяет, как именно нужно разделить строки запроса для обработки оконной функцией. Предложение PARTITION BY, дополняющее OVER, указывает, что строки нужно разделить по группам или разделам, объединяя одинаковые значения выражений PARTITION BY. Оконная функция вычисляется по строкам, попадающим в один раздел с текущей строкой.

Вы можете также определять порядок, в котором строки будут обрабатываться оконными функциями, используя ORDER BY в OVER. (Порядок ORDER BY для окна может даже не совпадать с порядком, в котором выводятся строки.) Например:

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

Как показано здесь, функция rank выдаёт порядковый номер в разделе текущей строки для каждого уникального значения, по которому выполняет сортировку предложение ORDER BY. У функции rank нет параметров, так как её поведение полностью определяется предложением OVER.

Строки, обрабатываемые оконной функцией, представляют собой «виртуальные таблицы», созданные из предложения FROM и затем прошедшие через фильтрацию и группировку WHERE и GROUP BY и, возможно, условие HAVING. Например, строка, отфильтрованная из-за нарушения условия WHERE, не будет видна для оконных функций. Запрос может содержать несколько оконных функций, разделяющих данные по-разному с помощью разных предложений OVER, но все они будут обрабатывать один и тот же набор строк этой виртуальной таблицы.

Мы уже видели, что ORDER BY можно опустить, если порядок строк не важен. Также возможно опустить PARTITION BY, в этом случае будет только один раздел, содержащий все строки.

Есть ещё одно важное понятие, связанное с оконными функциями: для каждой строки существует набор строк в её разделе, называемый рамкой окна. По умолчанию, с указанием ORDER BY рамка состоит из всех строк от начала раздела до текущей строки и строк, равных текущей по значению выражения ORDER BY. Без ORDER BY рамка по умолчанию состоит из всех строк раздела. Посмотрите на пример использования sum:

SELECT salary, sum(salary) OVER () FROM empsalary;
 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

Так как в этом примере нет указания ORDER BY в предложении OVER, рамка окна содержит все строки раздела, а он, в свою очередь, без предложения PARTITION BY включает все строки таблицы; другими словами, сумма вычисляется по всей таблице и мы получаем один результат для каждой строки результата. Но если мы добавим ORDER BY, мы получим совсем другие результаты:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

Здесь в сумме накапливаются зарплаты от первой (самой низкой) до текущей, включая повторяющиеся текущие значения (обратите внимание на результат в строках с одинаковой зарплатой).

Оконные функции разрешается использовать в запросе только в списке SELECT и предложении ORDER BY. Во всех остальных предложениях, включая GROUP BY, HAVING и WHERE, они запрещены. Это объясняется тем, что логически они выполняются после обычных агрегатных функций, а значит агрегатную функцию можно вызвать из оконной, но не наоборот.

Если вам нужно отфильтровать или сгруппировать строки после вычисления оконных функций, вы можете использовать вложенный запрос. Например:

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
    rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
   FROM empsalary
  ) AS ss
WHERE pos < 3;

Данный запрос покажет только те строки внутреннего запроса, у которых rank (порядковый номер) меньше 3.

Когда в запросе вычисляются несколько оконных функций для одинаково определённых окон, конечно можно написать для каждой из них отдельное предложение OVER, но при этом оно будет дублироваться, что неизбежно будет провоцировать ошибки. Поэтому лучше определение окна выделить в предложение WINDOW, а затем ссылаться на него в OVER. Например:

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Отличия реляционных и нереляционных баз данных

(наверх)

Понятие реляционных и нереляционных баз данных

Термин «реляционный» пришел из алгебры (теория множеств). В формате БД это значит, что данные реляционных баз хранятся в виде таблиц и строк. Нереляционные БД размещают информацию в коллекциях документов JSON.

Реляционные БД используют язык SQL (структурированных запросов). Структура таких баз данных позволяет связывать информацию из разных таблиц с помощью внешних ключей (или индексов), которые используются для уникальной идентификации любого атомарного фрагмента данных в этой таблице. Другие таблицы могут ссылаться на этот внешний ключ, чтобы создать связь между частями данных и частью, на которую указывает внешний ключ.

Зачем нужны нереляционные БД? Их главное преимущество — высокий уровень безопасности и возможность обойти аппаратные ограничения (с помощью Sharding).

РАЗЛИЧИЯ SQL И NOSQL

Язык

SQL используют универсальный язык структурированных запросов для определения и обработки данных. Это накладывает определенные ограничения: прежде чем начать обработку, данные надо разместить внутри таблиц и описать.

NoSQL таких ограничений не имеет. Динамические схемы для неструктурированных данных позволяют:

  • ориентировать информацию на столбцы или документы;
  • основывать ее на графике;
  • организовывать в виде хранилища KeyValue;
  • создавать документы без предварительного определения их структуры, использовать разный синтаксис;
  • добавлять поля непосредственно в процессе обработки.

Структура

SQL основаны на таблицах, а NoSQL — на документах, парах ключ-значение, графовых БД, хранилищах с широкими столбцами.

Масштабируемость

В большинстве случаев базы данных SQL можно масштабировать по вертикали. Что это значит? Можно увеличить нагрузку на один сервер, увеличив таким образом ЦП, ОЗУ или объем накопителя.

В отличие от SQL базы данных NoSQL масштабируются по горизонтали. Это означает, что больший трафик обрабатывается путем разделения или добавления большего количества серверов. Это делает NoSQL удобнее при работе с большими или меняющимися наборами данных.

В каких случаях используют SQL, а в каких NoSQL

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

Если объем данных большой, лучше использовать NoSQL. Отсутствие явных структурированных механизмов ускорит процесс обработки Big Data. А еще это безопаснее — такие БД сложнее взломать.

Выбирайте NoSQL, если:

  • необходимо хранить массивы в объектах JSON;
  • записи хранятся в коллекции с разными полями или атрибутами;
  • необходимо горизонтальное масштабирование.

Популярные бд
Для работы с реляционными БД лучше всего подойдут:

  • MySQL;
  • Oracle;
  • Microsoft SQL Server.

MySQL

Бесплатный продукт с открытым исходным кодом от Oracle. Отличается стабильностью и хорошим тестированием обновлений до их внедрения. MySQL можно доработать под свои нужды или поискать готовые исправления в обширной библиотеке профильного сообщества.

MySQL работает с любыми ОС: Linux, Windows, Mac, BSD и Solaris. Дружит с Node.js, Ruby, C#, C++, Java, Perl, Python и PHP.

Базу данных MySQL можно реплицировать на несколько узлов, что уменьшает рабочую нагрузку при увеличении доступности приложения.

Oracle

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

Oracle применяет свой собственный диалект SQL (PL/SQL). Это дает возможность работать со встроенными функциями, процедурами и переменными. Так же, как и MySQL, работает с любыми операционными системами. Если проекту необходимо использовать реляционные БД для работы с Big Data, то Oracle станет хорошей альтернативой NoSQL благодаря особой организации СУБД — группировке объектов по схемам, которые являются подмножеством объектов.

Еще одно важное преимущество Oracle — возможность восстановления предыдущей версии БД. Помимо этого, есть индексация растровых изображений, секционирование, индексацию на основе функций и по обратному ключу, оптимизация приоритетных запросов.

Microsoft SQL Server

Microsoft SQL Server — это отличный вариант для малого и среднего бизнеса. Диалект T-SQL обрабатывает процедуры, встроенные функции и переменные. Есть важное ограничение: Microsoft SQL Server будет работать только с Linux или Windows. Простой интерфейс ускорит процесс миграции БД, если до этого вы пользовались другой системой.

Нереляционные бд
Самыми удобными системами для обработки нереляционных баз данных являются:

  • MongoDB,
  • Apache Cassandra
  • Google Cloud BigTable

MongoDB

MongoDB — это качественный бесплатный продукт, который чаще всего используют при работе с NoSQL. Решение позволяет менять схемы данных в процессе работы, масштабироваться по горизонтали. Интерфейс очень простой — в нем легко разберется любой сотрудник компании, не обязательно быть IT-профессионалом.

Почему мы поставили Mongo на первое место в списке лидеров обработки нереляционных баз данных? Все дело в новой функции от разработчиков. Теперь в решении есть глобальная облачная БД, что дает возможность развернуть управляемую MongoDB через AWS, Azure, GCP.

Apache Cassandra

Apache Cassandra — это продукт с открытым исходным кодом, а значит, достаточно гибкий, адаптируемый практически для любых задач. Идентичность узлов упрощает масштабирование для наращивания архитектуры БД.

Apache Cassandra подойдет для масштабных проектов. Продукт обеспечивает высокую скорость чтения и записи. Даже если часть решения использует SQL, можно применить подобные SQL операторы: DDL, DML, SELECT. Для более высокого уровня безопасности есть резервное копирование и восстановление.

Apache Cassandra — это один из немногих инструментов обработки баз данных, который гарантирует безотказность работы (подробнее читайте в своем SLA).

Google Cloud BigTable

Неплохой продукт от Google, который гарантирует задержку обработки не более 10 мс. BigTable уделяют безотказности много внимания. Например, благодаря функции репликации базы данных более долговечны, доступны и устойчивы при зональных сбоях. Это отличный вариант для работы с Big Data в режиме реального времени (машинное зрение, AI) — можно изолировать рабочую нагрузку для приоритетной аналитики.