Если вам сейчас нравится выполнять SQL-запросы, вы должны были больше ценить алгебру и устанавливать операции…
Я никогда не был хорош в математике в целом; тем не менее, у меня были свои моменты. У меня были хорошие результаты, когда я задумался об этом. Но я никогда особо не ладил с алгеброй, она казалась скучной и бессмысленной. Помню, я считал это пустой тратой времени. Теперь я пожалел об этом, когда осознал его полезность и обнаружил его применимость в реальном мире. Например, когда кто-то проверяет некоторые записи в базе данных или выполняет манипуляции с изображениями, используются операции над множествами и принципы алгебры.
Манипуляции/обработка изображений полезны в автомобильной промышленности. Понимание того, как транспонировать матрицу, выполнять свертки или повороты, пригодится. Я коснусь этого позже…
На данный момент я сосредоточусь на запросе данных из баз данных SQL. Если вы взаимодействуете с веб-ресурсом, скорее всего, ваш запрос попадет в базу данных.
SQL был одним из первых коммерческих языков, использовавших реляционную модель Эдгара Ф. Кодда. Модель была описана в его влиятельной статье 1970 года Реляционная модель данных для больших общих банков данных.
Ссылка:
В то время возникла необходимость разработать модель для эффективного хранения данных, повышения скорости, обеспечения их постоянного хранения и уменьшения объема за счет удаления дублирующейся информации. Данные были упорядочены в записях на основе «сходств/признаков». Данные структурированы в виде таблиц, и между ними устанавливаются отношения на основе общей информации. Это называется ссылкой. Существуют "отношения" "один-к-одному", "один-ко-многим", "многие-ко-многим".
Нормализация базы данных — это процесс структурирования реляционной базы данных[требуется уточнение] в соответствии с рядом так называемых нормальных форм с целью уменьшения избыточность данных и повысить целостность данных. Впервые он был предложен Эдгаром Ф. Коддом как часть его реляционной модели.
Первая нормальная форма (1NF):
- Данные хранятся в таблицах, строки которых однозначно идентифицируются первичным ключом.
- Данные в каждой таблице хранятся в отдельных столбцах в наиболее сокращенной форме.
- Нет повторяющихся групп
Вторая нормальная форма (2NF):
- Все от 1НФ
- В каждой таблице хранятся только данные, относящиеся к первичному ключу таблицы.
Третья нормальная форма (3НФ):
- Все от 2НФ
- Внутритабличные зависимости между столбцами в каждой таблице отсутствуют.
Ссылка:
Чтобы показать математические принципы, я буду использовать базу данных MySQL. Концептуально существует разделение между логическим представлением данных и физическим уровнем.
С логической точки зрения база данных имеет уровень подключения, анализатор запросов, оптимизатор запросов, кеш запросов и механизм хранения. Уровень подключения не уникален для MySQL и обрабатывает протоколы клиент-сервер, аутентификацию, безопасность и т. д. Оптимизатор запросов определяет наиболее эффективный способ выполнения запроса. Кэш запросов — это место, где хранятся наиболее распространенные запросы.
MySQL имеет несколько механизмов хранения, но по умолчанию используется InnoDB. Это механизм хранения, совместимый с ACID. Он поддерживает блокировку на уровне строк, восстановление после сбоев и управление параллельным выполнением нескольких версий. Только механизм хранения InnoDB поддерживает ограничения внешних ключей.
Базы данных поддерживают отношения, связывая данные из одной таблицы с данными из другой. База данных MySQL хранит «ссылки/ссылки» с первичными и внешними ключами:
- Ограничение PRIMARY KEY однозначно идентифицирует каждую запись в таблице. Первичные ключи должны содержать значения UNIQUE и не могут содержать значения NULL.
- ВНЕШНИЙ КЛЮЧ — это ключ, используемый для связи двух таблиц. ВНЕШНИЙ КЛЮЧ — это поле (или набор полей) в одной таблице, которое ссылается на ПЕРВИЧНЫЙ КЛЮЧ в другой таблице.
Я буду использовать MySQL Workbench (инструмент администрирования) для взаимодействия и загрузки данных.
В сети есть несколько бесплатных образцов БД, и один из них — sakila, доступный здесь. Я загружаю БД в свою локальную среду, затем создаю пользователя и даю ему соответствующие права на БД.
Загрузить образец БД
mysql -t < sakila-schema.sql mysql -t < sakila-data.sql
Создать пользователя БД
grant usage on `sakila`.* TO ‘uadmin’ @localhost identified by "passwd"; grant usage on `sakila`.* TO ‘uadmin’ @ ‘%’ identified by "passwd"; grant all privileges on `sakila`.* TO ‘uadmin’ @localhost; MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec)
MySQL Workbench поставляется с некоторыми полезными утилитами для администрирования. Одной из таких функций является обратный инжиниринг базы данных, который позволяет нам создать схему ERP. Эта диаграмма создает визуальное представление базы данных и показывает отношения между различными таблицами.
Например, существует отношение «один ко многим» между таблицей актеров и film_actor. Таким образом, запись из таблицы фильмов указывает на несколько записей в таблице film_actor.
То же самое видно, если в оболочке MySQL выполнить команду «show create table»:
show create table actor\G; *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.000 sec) show create table film_actor\G; *************************** 1. row *************************** Table: film_actor Create Table: CREATE TABLE `film_actor` ( `actor_id` smallint(5) unsigned NOT NULL, `film_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`actor_id`,`film_id`), KEY `idx_fk_film_id` (`film_id`), CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.000 sec)
Между таблицами film и actor существует отношение многие ко многим . Таблица film_actor является своего рода промежуточной таблицей между ними. Один актер может играть в нескольких фильмах, но в то же время в фильме много актеров.
show create table film_actor\G; *************************** 1. row *************************** Table: film_actor Create Table: CREATE TABLE `film_actor` ( `actor_id` smallint(5) unsigned NOT NULL, `film_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`actor_id`,`film_id`), KEY `idx_fk_film_id` (`film_id`), CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.000 sec) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.000 sec)
Соединение таблиц SQL
Предложение JOIN используется для объединения строк из двух или более таблиц на основе связанного столбца между ними. Это похоже на пересечение между двумя множествами. Пересечение двух множеств A и B, обозначаемое A ∩ B, состоит из всех элементов, которые находятся как в A, так и в B. Например, {1,2,3,4}∩{2, 3,5}={2,3}.
Установить операторы
Реляционная алгебра использует объединение множеств, разность множеств и декартово произведение из теории множеств, но добавляет к этим операторам дополнительные ограничения.
Ссылка:
Наиболее распространенные объединения в MySQL:
- внутреннее соединение/перекрестное соединение
- левое соединение
- правильно присоединиться
В написании MySQL неквалифицированное СОЕДИНЕНИЕ подразумевает ВНУТРЕННЕЕ СОЕДИНЕНИЕ, или, другими словами, ВНУТРЕННЕЕ в ВНУТРЕННЕМ СОЕДИНЕНИИ не является обязательным. INNER и CROSS являются синонимами в MySQL.
Давайте проверим эти разные соединения и различия между ними.
Внутреннее соединение
Ключевое слово INNER JOIN выбирает записи, которые имеют совпадающие значения в обеих таблицах. Он выбирает строки, в которых есть совпадение слева, а также в правом столбце.
Мы выполним пересечение на основе actor_id, что означает, что мы сопоставим то, что находится в таблице actor, с тем, что находится в таблице film_actor. Всего у нас будет 5462 строки данных, потому что для каждого актера есть соответствующая строка в таблице film_actor.
MariaDB [sakila]> select count(*) from actor; +----------+ | count(*) | +----------+ | 200 | +----------+ 1 row in set (0.000 sec) MariaDB [sakila]> select count(*) from actor; +----------+ | count(*) | +----------+ | 200 | +----------+ 1 row in set (0.000 sec) ================================================================== MariaDB [sakila]> select * from actor join film_actor on actor.actor_id=film_actor.actor_id limit 10; +----------+------------+-----------+---------------------+----------+---------+---------------------+ | actor_id | first_name | last_name | last_update | actor_id | film_id | last_update | +----------+------------+-----------+---------------------+----------+---------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 1 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 23 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 25 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 106 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 140 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 166 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 277 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 361 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 438 | 2006-02-15 05:05:03 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | 1 | 499 | 2006-02-15 05:05:03 | +----------+------------+-----------+---------------------+----------+---------+---------------------+ 10 rows in set (0.000 sec) ================================================================== MariaDB [sakila]> select count(*) from actor join film_actor on actor.actor_id=film_actor.actor_id limit 10; +----------+ | count(*) | +----------+ | 5462 | +----------+ 1 row in set (0.000 sec)
Левое соединение
Ключевое слово LEFT JOIN возвращает все записи из левой таблицы (клиент) и соответствующие записи из правой таблицы (актер). Результат с правой стороны равен NULL, если совпадений нет.
MariaDB [sakila]> select * from customer limit 2\G;
*************************** 1. row ***************************
customer_id: 1
store_id: 1
first_name: MARY
last_name: SMITH
email: [email protected]
address_id: 5
active: 1
create_date: 2006-02-14 22:04:36
last_update: 2006-02-15 04:57:20
*************************** 2. row ***************************
customer_id: 2
store_id: 1
first_name: PATRICIA
last_name: JOHNSON
email: [email protected]
address_id: 6
active: 1
create_date: 2006-02-14 22:04:36
last_update: 2006-02-15 04:57:20
*************************** 3. row ***************************
MariaDB [sakila]> select * from actor limit 2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.000 sec)
Я ограничу результаты десятью, нет необходимости печатать весь вывод. Если мы посмотрим ниже, то увидим, что некоторые записи имеют значения NULL справа.
Если мы посчитаем это соединение и исключим значения NULL, мы увидим разницу.
MariaDB [sakila]> select count(*) from customer left join actor on customer.last_name=actor.last_name; +----------+ | count(*) | +----------+ | 620 | +----------+ 1 row in set (0.002 sec) ================================================================= MariaDB [sakila]> select count(*) from customer left join actor on customer.last_name=actor.last_name where actor_id!="NULL"; +----------+ | count(*) | +----------+ | 43 | +----------+ 1 row in set, 3 warnings (0.002 sec)
Правильно присоединиться
Ключевое слово RIGHT JOIN возвращает все записи из правой таблицы (клиент) и соответствующие записи из левой таблицы (актер). Результат с левой стороны равен NULL, если совпадений нет.
select * from customer right join actor on customer.last_name=actor.last_name limit 4\G;
*************************** 1. row ***************************
customer_id: NULL
store_id: NULL
first_name: NULL
last_name: NULL
email: NULL
address_id: NULL
active: NULL
create_date: NULL
last_update: NULL
actor_id: 1
first_name: PENELOPE
last_name: GUINESS
last_update: 2006-02-15 04:34:33
*************************** 2. row ***************************
customer_id: NULL
store_id: NULL
first_name: NULL
last_name: NULL
email: NULL
address_id: NULL
active: NULL
create_date: NULL
last_update: NULL
actor_id: 2
first_name: NICK
last_name: WAHLBERG
last_update: 2006-02-15 04:34:33
*************************** 3. row ***************************
customer_id: NULL
store_id: NULL
first_name: NULL
last_name: NULL
email: NULL
address_id: NULL
active: NULL
create_date: NULL
last_update: NULL
actor_id: 3
first_name: ED
last_name: CHASE
last_update: 2006-02-15 04:34:33
*************************** 4. row ***************************
customer_id: 6
store_id: 2
first_name: JENNIFER
last_name: DAVIS
email: [email protected]
address_id: 10
active: 1
create_date: 2006-02-14 22:04:36
last_update: 2006-02-15 04:57:20
actor_id: 4
first_name: JENNIFER
last_name: DAVIS
last_update: 2006-02-15 04:34:33
4 rows in set (0.001 sec)
Я делаю тот же подсчет, но исключаю строки, содержащие значения NULL.
MariaDB [sakila]> select count(*) from customer right join actor on customer.last_name=actor.last_name where customer.customer_id!="NULL"; +----------+ | count(*) | +----------+ | 43 | +----------+ 1 row in set, 3 warnings (0.001 sec) MariaDB [sakila]> select count(*) from customer right join actor on customer.last_name=actor.last_name; +----------+ | count(*) | +----------+ | 200 | +----------+ 1 row in set (0.002 sec)
Ссылки:
