Очень полное руководство — как работать с SQL?

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

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

Давайте сделаем краткое введение. Знакомьтесь с SQL.

SQL — что это?

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

Что такое база данных? Согласно определению Oracle, «это организованный набор структурированной информации или данных, обычно хранящихся в электронном виде в компьютерной системе». Очень формально.

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

Вам понадобится какой-то инструмент, чтобы помочь вам сделать все это. Здесь на помощь приходит SQL.

Первоначально SQL был разработан в начале 1970-х годов Дональдом Д. Чемберлином и Рэймондом Ф. Бойсом после того, как они изучили реляционную модель у Эдгара Ф. Кодда.

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

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

Кроме того, у вас есть еще одна таблица с информацией о каждом продукте и так далее. Это упрощает управление и отслеживание того, кто что покупает и когда.

Или, продолжая наш старый пример с сотрудниками, у вас будет таблица со всей базовой информацией о сотрудниках, а затем, возможно, еще одна таблица с историей работы сотрудников.

Но зачем использовать SQL?

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

Я нашел 3 причины, чтобы ответить на этот вопрос:

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

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

Теперь, чтобы напомнить вам, что SQL не является заменой Excel: Excel намного проще в использовании для основных задач, имеет приятный пользовательский интерфейс и по-прежнему способен делать большое количество вещей, которые вам не понадобятся или для которых не нужен SQL.

ОПРЕДЕЛЕНИЕ ПРИЛОЖЕНИЙ SQL

Вот кое-что, что я наткнулся в Интернете, пытаясь обобщить приложения SQL.

Более того, весь SQL можно разделить на части в соответствии с SQL. Тогда будет намного легче учиться.

Это вот-вот станет формальным.

SQL как DDL (язык определения данных): это не так сложно, как кажется, но перед созданием базы данных у вас должна быть схема того, какие данные (строки, даты, целые числа и т. д.) будут входить в каждый столбец базы данных. .
Иногда вам нужно изменить тип данных, которые принимает столбец. Это можно сделать с помощью SQL, вообще не изменяя данные.
SQL как DQL (язык запросов данных): у вас есть несколько таблиц, содержащих данные. Вы, очевидно, хотели бы знать, какие данные хранятся в этих таблицах, и хотели бы видеть это в соответствии с вашими личными потребностями. Вот где на сцену выходит запрос данных.
Вы спрашиваете (очень неформально) SQL: «Эй, SQL, можешь ВЫБРАТЬ эти данные из этой таблицы, если данные соответствуют этому критерию, и ммм, пожалуйста, отсортируй их по столбцу возраста». И SQL вернет ответ на основе запроса, вообще не изменяя исходные данные. Как мы увидим, это делается с использованием очень специального механизма.
SQL как DML (язык манипулирования данными): Само собой разумеется, вам нужно будет изменить существующие данные, добавить в них или удалить некоторые части из них. В SQL есть набор запросов, которые используются для выполнения этой манипуляции ✨.
SQL как DCL (язык управления данными). Одной из важных особенностей баз данных SQL является возможность совместной работы. Язык управления данными определяет, у кого есть права и разрешения на доступ к созданной вами базе данных.
Это очень важно, как я понял, и вы тоже углубитесь в это.

Области, в которых широко используется SQL

Аналитика данных
Как обсуждалось ранее, когда у вас есть масса данных, вы можете извлечь из них нужную информацию. SQL предоставляет массу базовых инструментов для базового анализа данных, хранящихся в вашей базе данных.
Администрирование базы данных
Администратор базы данных отвечает за добавление, обновление, удаление и общее управление качеством данных, хранящихся в базе данных. Для обеспечения качества данных и их безопасности (поскольку много информации в базе данных является конфиденциальной) важна роль администратора данных.
Бэкенд-разработка
Наконец, одно из лучших приложений SQL становится очевидным, когда вы занимаетесь Бэкенд-разработкой. Допустим, вы используете Spotify, вам «нравится» песня, которая затем добавляется в вашу библиотеку. Когда вы переключаете устройства или удаляете и снова устанавливаете Spotify, эта песня все еще остается в библиотеке. Это связано с тем, что информация о вашей библиотеке хранится где-то на сервере Spotify в базе данных.

Теперь, когда мы закончили с тем, что такое SQL и почему, давайте посмотрим, как использовать SQL, не так ли?

SQL: КАК

Как мы видели выше, было несколько применений SQL. Важно отметить, что между этими приложениями нет общих SQL-запросов. Таким образом, мы можем провести четкое различие между всеми этими запросами и соответственно разделить их.

Люди жалуются, что SQL трудно выучить, потому что они смешивают все типы запросов друг с другом. Разделение каждого оператора SQL и запросов значительно облегчает понимание.

Но сначала вам потребуется правильная настройка для выполнения запросов. Правильная настройка для SQL состоит из 3 частей:

Механизм базы данных
SQL-клиент и, конечно же,
SQL-запрос.

Давайте сначала посмотрим, что такое механизм базы данных:

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

Системы управления базами данных — и почему мы собираемся использовать Postgresql

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

На рынке доступны различные СУБД, давайте рассмотрим самые популярные из них, чтобы получить представление о том, чем они отличаются и что между ними похоже.

Во-первых, существует два типа баз данных: реляционные и нереляционные. Также известен как SQL и NoSQL соответственно.

ПЯТЬ САМЫХ ПОПУЛЯРНЫХ СУБД

MySQL
МарияДБ
Оракул
PostgreSQL
MSSQL

MYSQL

Это одна из самых популярных систем реляционных баз данных. Раньше это было приложение с открытым исходным кодом, но теперь оно принадлежит Oracle. Из-за популярности и основы C/C++ его легко использовать в системе любого типа.

Некоторые из преимуществ: Бесплатная установка, Простота использования и Совместимость с облаком.

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

МАРИАДБ

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

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

Недостаток использования MariaDB заключается в том, что он зависит от MySQL. Что, если они выпустят функцию, доступную только в MySQL? Другим недостатком также является то, что сообщество пользователей MariaDB все еще невелико, поэтому поиск помощи, если вы застряли, менее вероятен, чем другие.

ОРАКУЛ

По сравнению с двумя предыдущими записями, Oracle — это полностью закрытая база данных. В последних выпусках также большое внимание уделяется облачным вычислениям.

Можно сказать, что эта СУБД самая «продвинутая». С каждым выпуском Oracle идет в ногу с инновациями, происходящими в мире. Хотя основное внимание уделяется информационной безопасности.

Некоторыми преимуществами Oracle являются надежная поддержка, документы и повышенная производительность.

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

POSTGRESQL — тот, который мы будем использовать

Полностью открытый исходный код. Он принадлежит Глобальной группе разработчиков PostgreSQL. Он во многом похож на MySQL, в том числе очень популярен. Основное внимание в PostgresSQL уделяется укреплению стандартов соответствия и повышению расширяемости.
Он также предоставляет множество инструментов для анализа данных.

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

Нет ничего идеального, у PostgreSQL есть недостатки, такие как нестандартизированная документация, что приводит к несогласованности, и всегда есть шанс, что, если что-то пойдет не так, разработчики заметят это слишком поздно из-за отсутствия проблем с отчетами.

MSSQL

Принадлежит Microsoft, это собственность. Он имеет T-SQL, Transact SQL, который Microsoft предоставляет в дополнение к стандартному SQL.

Это снова один из самых популярных доступных в этой области. Он более «бесплатен в использовании», чем MySQL, поскольку существуют различные варианты установки и использования инструмента. Например, версия для разработчиков и версия Express бесплатны. Существуют также версии, такие как выпуски Enterprise, которые предоставляют административные инструменты и услуги и, конечно же, стоят денег.

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

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

Как установить PostgreSQL

Установка PostgreSQL может быть типичной задачей для разных платформ. Иногда после установки программного обеспечения вы можете столкнуться с проблемами с правами доступа, с ошибками сервера и т. д.

В этом мы рассмотрим, как установить PostgreSQL и запустить самую первую команду.

Установите PostgreSQL для Windows/Linux

Перейдите по этой ссылке". Нажмите на ссылку для загрузки, которая лучше всего подходит для ОС и архитектуры вашей системы.
i)
После завершения загрузки программы установки. Чтобы установить PostgreSQL в системе Windows, щелкните правой кнопкой мыши программу установки и запустите ее от имени администратора.
ii)
В противном случае, чтобы установить PostgreSQL на машине Linux, сначала дайте разрешение на выполнение установки с помощью chmod .
$ chmod 755 postgresql-10.22–1-linux-x64.run
Затем выполните скрипт от имени суперпользователя:
$ sudo ./postgresql-10.22–1-linux-x64.run

3. Вы увидите диалоговое окно, подобное этому:

Нажмите «Далее».

По умолчанию каталог установки PostgreSQL находится в /opt/PostgreSQL/__version__. Измените его, если хотите.

Здесь вы выбираете все, что хотите установить. Я собираюсь использовать все, поэтому оставлю их такими, какие они есть по умолчанию. Нажмите «Далее».

Затем установщик PostgreSQL спросит вас о том, где будут храниться данные. Измените его, если вы имеете в виду что-то особенное.

В следующем диалоговом окне PostgreSQL запрашивает мастер-пароль. ПОЖАЛУЙСТА, НЕ ЗАБЫВАЙТЕ ЭТО. Поскольку вам придется пройти через много неприятностей, если вы этого не помните.

Нажмите "Далее.

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

Выберите свой регион и нажмите «Далее».

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

Нажмите «Далее» еще раз.

Теперь PostgreSQL будет установлен в вашей системе. Наконец, нажмите «Готово».

Когда вы закончите настройку, вы заметите, что pgAdmin был установлен в вашем списке приложений. Нажмите на нее, чтобы запустить.

Вы увидите подобное всплывающее окно с запросом мастер-пароля, который вы ввели ранее.

Введите его, и теперь вы можете использовать PostgreSQL!! Поздравляю.

У нас установлен PostgreSQL. Пришло время познакомить вас с некоторыми основными запросами! Как создание таблицы в базе данных.

Если вы посмотрите на левую панель pgAdmin, вы увидите неоткрытый раскрывающийся список Серверы. Нажмите на него, чтобы развернуть. Вы заметите кое-что.

По умолчанию PostgreSQL предоставляет вам базу данныхpostgres и сервер с именем PostgreSQL_‹версия›.

Мы собираемся создать новую базу данных. Я очень творчески назвал его test. Чтобы создать базу данных самостоятельно, щелкните правой кнопкой мыши имя сервера (PostgreSQL_‹версия›), выберите «Создать» и выберите «База данных…».

Дайте имя вашей базе данных.

Затем нажмите сохранить. Как вы видите на левой панели, PostgreSQL создал для вас новую базу данных на сервере.

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

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

CREATE TABLE employees
(
first_name varchar(50),
last_name varchar(50),
email_id varchar(100)
);

Обратите внимание на синтаксис: сначала мы сообщаем SQL, что хотим что-то СОЗДАТЬ, это ТАБЛИЦА, а затем мы называем эту таблицу employees. В этом случае мы также должны указать имена столбцов и типы данных, которые эти столбцы будут принимать.

Как вы запускаете это в pgAdmin?

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

Написав запрос, вы можете выполнить его с помощью значка Play в середине верхней вкладки. У вас может быть значок молнии ⚡ вместо значка воспроизведения в зависимости от используемой версии.

Когда вы нажимаете на этот значок, ваш запрос должен выполняться идеально, если синтаксис правильный.
При успешном выполнении запроса PostgreSQL отобразит это на вкладке вывода.

Это создало для вас таблицу сотрудников. Но как убедиться, что он создан? На левой панели щелкните правой кнопкой мыши и введите Обновить. Разверните новую базу данных, затем следуйте Schemas -> Public -> Tables. Здесь вы увидите, что наша таблица сотрудников была создана, и pgAdmin показывает информацию о том, какие столбцы, ограничения и т. д. она имеет.

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

Подождите минутку, есть более официальный и формальный способ убедиться, что ваша таблица создана. Это путем проверки (используя запрос), что он содержит.

Мы делаем это, просто запустив:

SELECT * FROM employees;

Вот и все. Символ звездочки — это подстановочный знак, который указывает SQL выбрать ВСЕ из таблицы сотрудников. Когда вы выполняете запрос, нажимая значок Play/Lightning выше, вы должны ожидать этого на вкладке вывода ниже.

Как видите, он показывает таблицу с именами столбцов и их типами данных. Но здесь очень пусто. Это потому, что мы никогда не вставляли какие-либо данные в нашу таблицу. Время сделать именно это!

Вот как вы можете вставить данные в столбец в SQL:

INSERT INTO employees (first_name, last_name, email_id) 
VALUES 
(‘John’, ‘Smith’, ‘[email protected]’),
 (‘Harry’, ‘Potter’, ‘[email protected]’);

SELECT * FROM employees;

Примечание. В SQL для идентификации строк используются одинарные кавычки. Если бы вы использовали двойные кавычки «», вы бы получили ошибку. Это связано с тем, что двойные кавычки используются для идентификаторов SQL, таких как имена столбцов или таблиц.

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

Чтобы ВСТАВИТЬ что-то в базу данных в SQL, вы должны указать столбцы, которые вы хотите заполнить, за которыми следует термин «ЗНАЧЕНИЯ», а затем значение строки.

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

В приведенном выше примере мы создаем три столбца с типом данных varchar. Varchar(n) в PostgreSQL используется для хранения длинной строки переменной неопределенной длины, но принимает параметр n, указывающий базе данных не принимать строку, превышающую n символов.

Что, если вы попытаетесь ввести значения, которые не соответствуют ожидаемым столбцам? Скажем целое число? Ну, он все равно будет вставлен, но сначала он будет преобразован в тип varchar.

Давайте создадим еще одну таблицу с более широким набором типов столбцов.

CREATE TABLE teachers (
 id bigserial,
 first_name varchar(25),
 last_name varchar(50),
 school varchar(50),
 joining_date date,
 salary money
 );

SELECT * FROM teachers

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

Затем мы используем знакомый тип данных, varchar, для получения значений имен учителей и их школ.

Чтобы принимать даты, мы используем тип данных дата, который принимает значения в формате по умолчанию гггг-мм-дд. Затем, наконец, у нас есть зарплата учителей как тип данных money. Тип данных money в PostgreSQL используется для хранения денежных значений с фиксированным значением точности.
Давайте попробуем вставить некоторые значения в нашу только что созданную таблицу, чтобы понять, чем они отличаются для каждой переменной и как выглядят выходные данные.

INSERT INTO teachers (first_name, last_name, school, joining_date, salary) VALUES
(‘Matt’, ‘Smith’, ‘JPV School’, ‘2020–11–08’, 35120),
(‘Lizzy’, ‘Bennet’, ‘JPV School’, ‘2019–12–15’, 38230.22),
(‘Terry’, ‘Crux’, ‘JPV School’, ‘2021–06–14’, ‘$32000.00’);

SELECT * FROM teachers;

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

Имеет смысл заключить имена учителей и название школы в кавычки, но то же самое верно и для столбца даты. PostgreSQL примет дату как строку и сохранит ее в этом формате.

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

Типы данных Postgres Pro

Типы данных в PostgreSQL можно разделить на следующие категории:

BOOLEAN

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

Или, другими словами, значения, которые могут быть истинными или ложными, должны следовать за этим типом данных.
Все, что вставляется в этот тип данных со значением true, 1, t, y и yes, преобразуется в «true». С другой стороны, значения false, 0, f и no преобразуются в false.

ПЕРСОНАЖ

Строковые типы данных в PostgreSQL можно вставлять тремя способами.

i) char(n)
n — это значение, которое вы вводите, чтобы указать, сколько символов может содержать вставляемая строка. Если вы введете символов меньше n, оставшаяся строка символов в строке будет дополнена пробелами.

Это используется довольно редко, так как будет потреблять фиксированный объем данных независимо от размера данных.

ii) varchar(n)

В отличие от char, если вы вводите символы длиной меньше n, PostgreSQL не будет сохранять лишние пробелы. Это делает его намного лучше для использования памяти. Но, как и char, он принимает n в качестве параметра, который ограничивает количество символов, которые вы можете вставить в строку.

III) текст

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

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

Если вы не уверены в размере вводимого текста, можно использовать этот тип данных.

ЦИФРОВОЙ

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

Целые числа

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

В основе категоризации лежит диапазон, до которого может доходить каждый тип. Вот они:

i) smallint: это тип данных, который хранит число в 2-байтовом хранилище. Диапазон этого составляет от -32768 до +32767.

ii) целое: 4 байта, диапазон от -2147483648 до +2147483647.

iii) bigint: 8 байт, диапазон от -9223372036854775808 до -9223372036854775808.

Автоматически увеличивающиеся целые числа

В таблице учителей, которую мы создали выше, был тип данных bigserial, который увеличивался всякий раз, когда мы добавляли столбец. Подобно bigserial, существуют также smallserial и serial.

И, очевидно, они используются для идентификаторов и индексов, тип данных с автоинкрементом не имеет отрицательных значений.

ДЕСЯТИЧНЫЕ ЗНАЧЕНИЯ

Десятичные числа представляют собой целые числа в дополнение к части целого числа. В SQL они представлены в двух формах:

i) Числа с фиксированной точкой
Рассматривайте это как строку фиксированной длины. Только вы решаете, сколько всего цифр будет в числе и сколько цифр может быть в правой части.

Это делается путем передачи аргументов. Можно использовать числовой(точность, масштаб) или десятичный(точность, масштаб). Точность показывает, сколько всего цифр должно быть во всем числе, а масштаб показывает максимальное количество цифр, допустимых справа от десятичной точки, например, 12,4. имеет точность 3 и масштаб 1.

Что, если вы не сообщите базе данных значения точности и масштаба? Что ж, это позволит вам хранить максимально возможное количество значений для каждого. Это означает до 131072 цифр слева и 16383 цифр справа.

ii) Числа с плавающей запятой

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

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

В реальном типе данных может храниться до шести десятичных цифр, в то время как с двойной точностью может храниться до 15 цифр.

Чтобы получить более четкое представление об их работе и отличии от чисел с фиксированной точкой, давайте возьмем пример:

CREATE TABLE number_example (
fixed_col numeric(15, 5),
real_col real,
double_col, double precision
);
INSERT INTO number_example
VALUES 
(.1, .1, .1),
(1.132, 1.132, 1.132),
(5.423242344, 5.423242344, 5.423242344);

SELECT * FROM number_example;

Когда вы выполняете приведенный выше запрос, в выходных данных отображается следующая таблица:

Заметили фиксированный и плавающий характер десятичной точки в каждом столбце? Это первое, что вы замечаете. Но также обратите внимание, что в столбце с фиксированной точкой мы передали шкалу числа равной 5 (числовому (15, 5)), и, следовательно, в третьем ряд, число усекается после 5-й цифры с правой стороны.

Как уже упоминалось, настоящий тип данных имеет точность всего 6, и с ним тоже самое происходит в третьем столбце. Только тип данных с двойной точностью может точно представлять число в соответствии с вводом из-за его значения высокой точности.

Вот несколько советов, которые я нашел в Интернете по использованию числовых типов данных:

По возможности используйте целые числа. Если ваши данные не имеют десятичных значений, то какой смысл использовать десятичный тип данных?
Типы с плавающей запятой экономят место из-за того, как они хранятся в памяти, но если вы хотите, чтобы ваши вычисления были точными, всегда используйте числовые или десятичные.
Используйте достаточно большой тип данных. При работе с десятичными дробями устанавливайте точность и масштаб соответственно, а при работе с целыми числами используйте более чем достаточный тип данных, например bigint, если только вы не уверены, что меньший целочисленный тип данных сможет удерживать ваши данные. данные.

ДАТА И ВРЕМЯ

Для даты и времени PostgreSQL предоставляет основные типы данных:

date: Только дата, время не поддерживается. Поддерживается дата от 4713 г. до н.э. до 5874897 г. н.э. Хранится в 4 байта.
время: только время, дата не поддерживается. 24-часовой формат. 8 байт требуется для хранения. Вы могли бы указать часовой пояс, используя "с часовым поясом" (я приведу пример ниже). Становится очень сложно отслеживать время по всему миру, не имея значения часового пояса.
метка времени: поддерживаются как дата, так и время в диапазоне от 4713 г. до н.э. до 294276 г. н.э. 8 байт необходимо для его хранения. Опять же, "с часовым поясом" было бы желательно.
интервал: Интервал — это разница между двумя метками времени. Этот тип данных поддерживает временной интервал +-178000000 лет. Для хранения также требуется 16 байт.

Возьмем пример:

CREATE TABLE date_time_example (
timestamp_col timestamp with time zone,
interval_col interval
);
INSERT INTO date_time_example
VALUES
('2022–11–20 05:00 EST', '5 days'),
('2022–11–20 05:00 -8', '1 month'),
('2022–11–20 05:00 Australia/Melbourne', '1 year'),
(now(), '2 weeks');

SELECT * FROM date_time_example;

Когда вы запустите приведенный выше запрос, вы получите следующее:

ПРИМЕЧАНИЕ. Кроме того, если вы хотите добавить временную метку в Индии, не используйте IST, так как это относится к стандартному времени Израиля, а не к стандартному времени Индии. Вместо этого используйте Азию/Калькутту.

По умолчанию используется формат времени гггг-мм-дд чч:мм:сс, который является стандартом ISO для хронометража. Другие форматы также поддерживаются.

Я знаю, что вывод немного сбивает с толку. Но посмотрите на это по отношению к моему часовому поясу, который является индийским. Поэтому, когда вы пишете EST, он преобразует 5:00 в индийское время в первой строке, устанавливает часовой пояс от 8 часов до UTC (что является тихоокеанским временем или PT), а затем преобразует 5:00 индийского времени в PT. во втором ряду и преобразует австралийское время 5:00 в индийское в третьем ряду.

Обратите внимание, что время UTC равно 00:00 и относится к общемировому стандарту времени, поэтому отставание от него на 8 часов соответствует тихоокеанскому часовому поясу.

Также есть третья строка, использующая функцию now(), которая получает текущее время из системы, в которой работает сервер PostgreSQL.

Мы уже достаточно видели, как работает свойство timestamp. Но что здесь делает интервальный тип данных? Какова цель?

Убедимся в этом, выполнив следующий запрос:

SELECT 
timestamp_col, 
interval_col,
timestamp_col + interval_col AS time_after_col
FROM date_time_example;

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

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

БОЛЬШЕ ТИПОВ В POSTGRESQL

я). Массивы

Данные в строках также могут храниться в виде массивов в PostgreSQL. Они изменчивы и многомерны. Для ввода в массив вы можете использовать фигурные скобки {} для представления одного ввода. Например:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');


SELECT * FROM sal_emp;

Выполнение приведенного выше запроса дает следующие результаты.

ii). UUID

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

Пример:

Я буду использовать их позже в соответствующих ситуациях.

МАССИВ

JSON

HSTORE

СПЕЦИАЛЬНЫЙ

СЛОВАРИ ДАННЫХ И ИХ ВАЖНОСТЬ

ЗАПРОСЫ SQL

Для лучшего понимания SQL и различных типов запросов, которые вы можете выполнять, давайте разделим их!

Запросы определения данных:

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

Когда вы создаете приложение с SQL для управления данными в серверной части. Пользователь вашего приложения не должен иметь доступа к этим запросам со своей стороны. Всем должен управлять хозяин.

CREATE query
Когда этот запрос выполняется, в базе данных создается новый объект, например, таблица, функция и т. д. Вы даже можете создать новую базу данных с помощью оператора CREATE.

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

CREATE TABLE table_name (
  col1 column_type,
  col2 column_type,
  .
  .
  colN column_type
);

ИЗМЕНИТЬ запрос

Этот запрос используется для изменения схемы вашей базы данных или компонентов базы данных. Например, после создания таблицы вы можете захотеть изменить тип данных столбца, возможно, изменить имя столбца и т. д.

Давайте посмотрим, каков синтаксис оператора ALTER:

ALTER TABLE table_name <expression>

Первые слова в операторе выше делают таблицу модифицируемой. Но что делать после этого? Это то, что ‹выражение› представляет выше.

Вот пример того, что вы можете сделать:

CREATE TABLE example 
(
  col1 text,
  col2 text
);

ALTER TABLE example ADD COLUMN col3 text;

SELECT * FROM example;

Как вы можете, в нашу таблицу добавлен еще один столбец. В операторе ALTER мы сказали SQL добавить столбец с определенным именем и определенным типом данных. Точно так же, как при создании таблицы.

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

ALTER TABLE example ALTER COLUMN col3 SET DATA TYPE varchar(50);
SELECT * FROM example;

Это дает ожидаемые результаты, изменяя тип данных третьего столбца на varchar с ограничением в 50 символов.

DROP-запрос

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

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

DROP TABLE table_name

Приведенный выше запрос удалит/удалит указанную вами таблицу. Но что, если вы хотите сделать что-то менее впечатляющее? Что делать, если вы хотите удалить только один столбец?

Этого можно добиться, используя комбинацию ALTER и DROP.

ALTER TABLE table_name DROP COLUMN col_name

ОБРЕЗАТЬ запрос

Функция запроса TRUNCATE очень похожа на функцию DROP. Он очищает таблицу или другой объект, но не удаляет его структуру.

Вот как выглядит синтаксис TRUNCATE:

TRUNCATE table_name;

Использование приведенного выше запроса очистит все данные, которые были в таблице, но если вы выполните SELECT * FROM table_name, вы все равно получите пустую таблицу с сохраненной структурой.

Некоторые вещи, которые вы должны знать, отличаются. DROP против TRUNCATE

TRUNCATE обычно быстрее удаляет данные и идеально подходит для удаления временной таблицы*.
TRUNCATE сохраняет структуру таблицы в отличие от DROP.
Есть шанс восстановить данные, которые вы удалили через TRUNCATE. Чего нельзя сказать о DROP. Следовательно, DROP нужно использовать с умом.
Временные таблицы будут обсуждаться в ближайшее время.

Рассмотренные выше запросы можно использовать в сочетании друг с другом. Пример которого вы уже видели выше. Но больше будет использоваться позже, и со временем это станет естественным.

Язык запроса данных и исследование данных:

Операторы DQL используются для запросов к базе данных. Позвольте мне исправить себя. Оператор DQL используется для запроса базы данных. Это потому, что есть только один.

Это оператор SELECT, который мы уже видели несколько раз. А теперь давайте посмотрим на что он способен.

ВЫБЕРИТЕ запрос

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

Давайте подробнее изучим запрос SELECT.

Запрос SELECT может состоять из 4 частей.

SELECT <projection>
FROM <table name>
WHERE <boolean expression>
ORDER BY <columns names>

Сначала мы сосредоточимся на проекционной части, чтобы начать.

Для исследования давайте сначала настроим стол! Вы могли бы скопировать CSV-файл, который я использую, из моего Github, если хотите следовать за ним.

Ссылка на employees.csv: https://github.com/aad1tya/SQL-Book

CREATE TABLE employee (
 EMPLOYEE_ID smallint,
 FIRST_NAME varchar(30),
 LAST_NAME varchar(30),
 EMAIL varchar(70),
 PHONE_NUMBER varchar(15),
 HIRE_DATE date,
 JOB_ID varchar(10),
 SALARY decimal(8, 2),
 MANAGER_ID smallint,
 DEPARTMENT_ID smallint
);

COPY employee --- COPY command to import employee.csv with a HEADER and CSV format.
FROM '/employees/employees.csv'
WITH (FORMAT CSV, HEADER);

SELECT * FROM employee;

Вот как выглядит результат, когда вы запускаете вышеуказанный запрос.

Вернемся к проекционной части оператора SELECT!

Звездочка * в приведенном выше запросе в основном говорит SQL, чтобы он взял все из таблицы сотрудников.

Что делать, если вы хотите получить только несколько выбранных столбцов?

SELECT first_name, last_name, salary
FROM employee;

Наш запрос вместо использования подстановочного знака просто указывает SQL получить все указанные столбцы.

Используя ключевое слово «AS», вы можете указать псевдоним для столбца, который вы хотите получить, столько раз, сколько раз имена столбцов не имеют смысла/не выглядят хорошо при извлечении.

SELECT first_name as EmployeeName
FROM employee;

Что, если вы хотите предоставить псевдоним, в котором есть пробел, потому что псевдоним должен выглядеть как обычный английский язык без каких-либо символов подчеркивания, верно? Ну, если ты попробуешь сделать это нормально, вот так...

SELECT first_name as "First Name"
FROM employee;

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

SELECT first_name as "First Name"
FROM employee;

Оно работает!

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

SELECT first_name AS "First Name", last_name AS "Last Name"
FROM employee;

ПРИМЕЧАНИЕ. Вы ничего не меняете при выполнении оператора SELECT.

Что, если я скажу вам, что «AS» не является обязательным? Это правда, вы можете предоставить псевдоним без AS. Следующий запрос также даст те же результаты, что и приведенный выше, но вам следует избегать такого подхода к написанию запросов, поскольку он плохо читается.

SELECT first_name "First Name", last_name "Last Name"
FROM employee;

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

SELECT first_name || ' ' || last_name AS "Full Name"
FROM employee;

В основном, || оператор соединяет два упомянутых столбца с пробелом между ними. Это все равно, что сказать: имя + ‘ ’ + фамилия.

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

SELECT now()::date - hire_date AS "Employee Days"
FROM employee;

Ладно, тут много чего распаковать. Прежде всего, now() возвращает текущее время. Это зависит от того, где работает ваш сервер PostgreSQL. Функция now() возвращает отметку времени со значением даты и времени, нас интересует только текущая дата.
Итак, чтобы получить только дату, мы используем «::date», который преобразует отметку времени только в дату. После этого мы просто вычитаем из него сохраненную дату.

Вы можете найти разницу между двумя датами. Точно так же вы можете делать то же самое с целыми числами, вы можете их складывать, вы можете их вычитать. Но прежде чем вы начнете это делать, сначала познакомьтесь с приведением типов в PostgreSQL, поскольку работа с целыми и десятичными числами может быть сложной.

SELECT first_name, last_name, job_id, cast(salary as bigint) 
FROM employee;

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

Это было просто, чтобы дать вам пример, зарплаты остаются в десятичной точке. Этим клеркам и так меньше платят!

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

Я хочу узнать, какие сотрудники получают зарплату больше 10000. Как мне это сделать? Вот тут-то и появляются условия в SQL.

SELECT first_name, last_name, salary
FROM employee
WHERE salary > 10000;

Запустив это, мы получим ожидаемые результаты, то есть мы получим таблицу с именами, фамилиями и зарплатами сотрудников, которым платят более 10000.

Давайте сделаем что-нибудь более полезное! Запрос к базе данных только для возврата таблицы сотрудников, которые работают в ИТ.

Это можно легко сделать с помощью:

SELECT first_name, last_name, hire_date, job_id
FROM employee
WHERE job_id = 'IT_PROG';

Использование одного оператора WHERE довольно просто. Допустим, вы хотите узнать по какой-то причине сотрудников ИТ, которые присоединились к вам после 2006 года. Как бы вы это сделали?

Здесь на помощь приходит оператор AND.

SELECT first_name, last_name, hire_date, job_id
FROM employee
WHERE job_id = 'IT_PROG' AND hire_date > '2006-01-01';

Оператор AND сравнивает оба наших условия (где job_id — IT, а Hire_date больше 2006) и возвращает строки, удовлетворяющие обоим этим требованиям. Что делать, если вы хотите выполнить запрос, который может удовлетворять одному из условий?

Вопрос. Найдите сотрудников, которые либо являются клерками, ИЛИ работают в ИТ-отделе.

Это похоже на использование оператора AND.

SELECT first_name, last_name, hire_date, job_id
FROM employee
WHERE job_id = 'IT_PROG' OR job_id = 'ST_CLERK';

Выполнение приведенного выше запроса возвращает более 20 результатов (скрытых на рисунке ниже), так как у нас много клерков. В основном он возвращает значения, которые верны либо для IT_PROG, либо для ST_CLERK.

Когда вы отфильтровываете таблицу на основе определенного столбца (в приведенном выше случае job_id), обычно рекомендуется иметь этот столбец фильтра в выходных данных, поскольку он может помочь вам лучше понять данные.

До сих пор мы видели оператор «›» больше и «=» равно. Давайте перечислим их всех ниже.

Оператор равенства («=»): этот оператор проверяет, равны ли два значения или нет. После сравнения, если значения не равны, условие имеет значение False, а если они равны, условие имеет значение True.
Оператор «не равно» («!=» или «‹›»): можно использовать любой из них. Они проверяют, равны ли два значения или нет. Если значения не равны, условие становится истинным, а если они равны, условие становится ложным.
Оператор «больше» («›»): проверяет, больше ли левый операнд, чем правый операнд. Условие истинно, если это так, иначе ложно.
Оператор меньше («‹»): проверяет, меньше ли левый операнд, чем правый операнд. Условие истинно, если это так, иначе ложно.
Больше или равно («›=»): Условие истинно, если левый операнд больше или равен правому операнду, в противном случае — ложно.
Меньше или равно («‹=»): Условие истинно, если левый операнд меньше или равен правому операнду, в противном случае — ложно.

Все эти операторы, а также И и ИЛИ позволяют вам задавать более точные вопросы при использовании оператора WHERE, и именно это делает SQL отличным языком для запросов к вашим данным.

С этими операторами все в порядке. Но остался еще один. Что делать, если вы хотите найти значения, которые находятся МЕЖДУ двумя значениями.

Как вы уже догадались, оператор сравнения для этой конкретной задачи — это оператор BETWEEN.

Давайте возьмем пример, задав вопрос, кто такие сотрудники, которые зарабатывают в определенном диапазоне:

SELECT first_name, last_name, salary
FROM employee
WHERE salary BETWEEN 4000 AND 8000;

Приведенный выше запрос возвращает следующий результат.

Вам поставили задачу, найдите сотрудников с идентификаторами 111, 121, 112 и 123. Первый ответ, который придет вам в голову, может выглядеть так:

SELECT employee_id, first_name, last_name
FROM employee
WHERE employee_id = 111 OR employee_id = 121 OR
employee_id = 112 OR employee_id = 123;

Конечно, это вернет желаемый результат, но есть лучший, более изощренный способ задать тот же вопрос.

Используйте оператор «IN».

SELECT employee_id, first_name, last_name
FROM employee
WHERE employee_id IN (111, 121, 112, 123);

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

Сопоставление шаблонов SQL

Вы можете узнать, в каком столбце есть значения, соответствующие вашему конкретному шаблону. Например, вы можете захотеть найти имена сотрудников, которые начинаются с буквы U. Или, лучшим примером может быть то, что вы хотите узнать сотрудников, чьи номера телефонов начинаются с «515».

А как это сделать в SQL? Ответ прост, сопоставление с образцом.

В PostgreSQL есть два оператора LIKE и ILIKE для сопоставления с образцом. Давайте посмотрим каждый из них в действии, а также поймем основную разницу между ними.

НРАВИТСЯ и МНЕ НРАВИТСЯ

Единственная разница между LIKE и ILIKE заключается в том, что LIKE чувствителен к регистру (для него «hello» и «HELLO») разные. ILIKE, с другой стороны, нечувствителен к регистру, строчные и прописные буквы означают для него одно и то же.

SELECT first_name, last_name, job_id, phone_number
FROM employee
WHERE phone_number LIKE '515%'

Приведенный выше запрос возвращает следующие результаты:

Как видите, у нас есть только сотрудники, номера телефонов которых начинаются с 515. «%» в «WHERE phone_number LIKE ‘515%’» действует как подстановочный знак. По сути, это говорит: «Меня волнует только 515, и если только 515 соответствует строке, я выберу ее, независимо от того, что идет после нее».

Точно так же у вас могут быть сотрудники с телефонными номерами, которые начинаются с 515 и заканчиваются на 9, а все, что находится между ними, является подстановочным знаком. Так:

SELECT first_name, last_name, job_id, phone_number
FROM employee
WHERE phone_number LIKE '515%9'

Вы также можете искать значения, которые содержат определенную букву/буквы между строками. Это можно сделать, используя подчеркивание «_» вместо «%».

SELECT first_name, last_name, job_id
FROM employee
WHERE first_name LIKE '%_am_%'

Он возвращает имена с «am» в строке, затем два любых символа сбоку, а затем подстановочные знаки. Вы можете подумать, чем это отличается от простого использования «%am%». Что ж, «%_am_%» требует, чтобы в базе данных было как минимум два окружающих символа, «%am%» этого не требует. Вот результат последнего.

Обратите внимание, что второй также возвращает имена, оканчивающиеся на «am»?

Еще одно важное замечание: если вы запустите запрос только с "_am_", он ничего не вернет. Потому что нет имени, состоящего из четырех букв И содержащего «ам».

Оператор НЕ SQL:

Допустим, вы хотите найти строки, не соответствующие шаблону «%_am_%»? Как ты это делаешь?

Оператор NOT в SQL обеспечивает именно эту функциональность.

SELECT first_name, last_name, job_id
FROM employee
WHERE first_name NOT LIKE '%_am_%'

Запрос возвращает следующий результат, который не включает двух сотрудников с именем «Джеймс».

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

Я говорю, что, поскольку SQL позволяет вам писать в нем циклы IF-ELSE, и на основе этих предложений IF-ELSE вы можете получить много дополнительных рычагов, которых у вас не было раньше.

оператор CASE в PostgreSQL

Если вы знакомы с языками программирования (которых, я думаю, вы не можете избежать из-за срочности их изучения), то вы должны знать циклы ЕСЛИ. Оператор CASE позволяет делать то же самое в PostgreSQL. За исключением того, что вместо «ЕСЛИ» используется ключевое слово «КОГДА». Картошка Картошка.

Давайте посмотрим, как работает его синтаксис.

CASE
WHEN ‹условие 1› THEN ‹результат 1›
WHEN ‹условие 2› THEN ‹результат 2›
.
.
WHEN ‹условие n› ТОГДА ‹результат n›

ELSE ‹результат else›
END

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

Job_ids не дает нам правильного представления о том, чем на самом деле занимается человек. Конечно, это несколько указывает на их роль, но вы не можете быть уверены. Итак, давайте классифицируем каждый job_id по роли, описывающей то, что человек делает.

Вот как это можно сделать с помощью CASE.

SELECT first_name, last_name, job_id, ---Notice the comma after the last column
CASE
  WHEN job_id LIKE 'IT%' THEN 'Programmer'
  WHEN job_id LIKE '%CLERK' THEN 'Clerk'
  WHEN job_id LIKE '%MGR%' THEN 'Manager'
  ELSE 'Unknown'
END job_title
FROM employee;

Этот запрос создает столбец в результате, который вводит Clerk для всех job_id, в которых есть слово «CLERK», Programmer для ИТ-специалистов и Manager для всех менеджеров.

Блок ELSE обрабатывает все остальные идентификаторы job_id и пока делает «неизвестно».

Примечание. Вы должны закончить предложение WHEN ключевым словом END после того, как закончите писать условия. Кроме того, оператор ELSE в приведенном выше запросе является необязательным.

Операторы SQL —LIMIT, DISTINCT и ORDER BY

Это неправильный инструмент управления базой данных, если он не имеет базовых функций, таких как сортировка.

Предложение ORDER BY в SQL сортирует таблицу по упомянутому столбцу/столбцам. Давайте посмотрим.

SELECT first_name, last_name
FROM employee
ORDER BY first_name;

Наша таблица была отсортирована относительно first_name. По умолчанию сортировка выполняется в порядке возрастания. Вы можете указать SQL тип сортировки, который вы хотите сделать следующим образом:

SELECT first_name, last_name
FROM employee
ORDER BY first_name DESC;

То же самое можно сделать и с цифрами. Посмотрим, кто больше всего зарабатывает в нашей базе!

SELECT first_name, last_name, money
FROM employee
ORDER BY money DESC;

Стивен Кинг. Фитинг.

Подобно сортировке по одному столбцу, вы также можете сортировать по нескольким столбцам. Как это работает?

SELECT first_name, last_name
FROM employee
ORDER BY first_name, last_name DESC;

Наш запрос сначала сортирует таблицу по first_name, а затем, если два или более имен повторяются, он сортирует эти дубликаты в порядке убывания на основе их фамилий. Вы можете видеть, что то же самое происходит для строк 2 и 3.

Давайте посмотрим общее количество различных значений для job_id, которые у нас есть. Вы можете сделать это, используя ключевое слово DISTINCT.

SELECT DISTINCT job_id
FROM employee
ORDER BY job_id;

У нас есть 17 разных job_id в нашей таблице. Но лучше ли это сделать вместо проверки индекса? Да, есть. Это с помощью COUNT.

SELECT COUNT (DISTINCT job_id)
FROM employee;

Ключевое слово COUNT подсчитывает значения, возвращаемые проекцией SELECT. Счетчик — это агрегатная функция, одна из многих, которые мы увидим позже.

Давайте посмотрим, что делает ключевое слово LIMIT. Он делает то, что должен делать. В основном он возвращает первые N строк из результата вашего запроса. Взяв пример, вы лучше поймете это.

SELECT first_name, last_name
FROM employee
LIMIT 10;

Вывод ограничивается первыми 10 строками.

Функции агрегирования и группировка по

Агрегатные функции называются агрегатными, потому что они выполняют операцию над строками и возвращают ответ в одной строке.

Мы уже видели пример функции COUNT(), которая берет всю таблицу и возвращает количество строк в этой таблице.

Эти агрегатные функции часто используются вместе с оператором GROUP BY. Вы поймете, что делает GROUP BY, на примере.

Но сначала давайте рассмотрим каждую агрегатную функцию на примере. Как вы нашли среднюю зарплату из таблицы сотрудников? Что ж, для этого в SQL есть стандартная функция avg(). Давайте посмотрим.

SELECT avg(salary) AS average_salary
FROM employee;

Вы можете легко вычислить максимальное значение из столбца, используя max() следующим образом:

SELECT max(salary)
FROM employee;

Функция max() возвращает максимальное значение из столбца. Чтобы получить всех сотрудников, у которых зарплата равна максимальной зарплате, вы можете либо отсортировать таблицу в порядке убывания относительно столбца зарплаты, а затем ограничить вывод, чтобы просто получить правильное количество сотрудников ( это было бы неправильно) ИЛИ вы можете использовать функцию max() следующим образом:

SELECT first_name, last_name, salary
FROM employee
WHERE salary = (SELECT max(salary) FROM employee);

Теперь, как я понял, самое время представить вам, что вы можете использовать вывод другого оператора SELECT для сравнения в условии WHERE.

Подобно функции max(), у нас есть функция min(), которая возвращает наименьшее значение из столбца. Задаем тот же вопрос, который мы задавали выше, но заменяем максимум на минимум.

SELECT first_name, last_name, salary
FROM employee
WHERE salary = (SELECT max(salary) FROM employee);

Функция SUM() сообщает вам сумму всего столбца. И сейчас самое время понять, как работает предложение GROUP BY. Давайте сначала разберемся с функцией SUM() на примере:

SELECT sum(salary) AS company_spending
FROM employee;

Теперь вопрос, сколько компания тратит на каждую должность. Вот тут-то и появляется оператор GROUP BY. Смотрите внимательно.

SELECT sum(salary) AS job_spending
FROM employee
GROUP BY job_id
ORDER_BY job_id;

Видите, как он суммирует зарплаты по каждой должности отдельно? Это связано с тем, что инструкция GROUP BY группируется по таблице из каждого job_id, а затем запускает для них функцию sum(), так как она выполняется второй. Так обстоят дела с SQL. Это странный сотрудник, но он выполняет свою работу.

ОБЩИЕ ТАБЛИЧНЫЕ ВЫРАЖЕНИЯ и инструкции WITH

CTE или общие табличные выражения позволяют нам упростить запросы, которые мы пишем с помощью инструкции SELECT.

Из-за того, как SQL обрабатывает запрос, если вы напишете следующее в редакторе запросов, это приведет к ошибке.

SELECT job_id, CAST (avg(salary) as bigint) AS avg_salary
FROM employee
GROUP BY job_id
WHERE avg_salary > 5000; --- SQL doesn't know that avg_salary exists yet.

То, что мы написали выше, было выражением табличных значений. Любой запрос, возвращающий таблицу, является табличным выражением. Таким образом, чтобы правильно выполнить приведенный выше запрос, может помочь предложение WITH.

Посмотрим, как.

WITH avg_salary_table AS ( --- avg_salary_table is the new table created
  SELECT job_id, CAST(avg(salary) as bigint) AS avg_salary
  FROM employee
  GROUP BY job_id
) --- Finishing the query just here will result in error

SELECT * FROM avg_salary_table --- WITH is followed by SELECT and
WHERE avg_salary > 5000; ---       it must have table alias WITH used.

Приведенный выше запрос, с другой стороны, работает отлично. Он возвращает нам рабочие места отделов со средней зарплатой более 5000.

Примечание. Выше следует отметить, что вам придется написать оператор SELECT после предложения WITH, и вам придется использовать таблицу псевдонимов, которую вы использовали в операторе WITH.

Теперь давайте убедимся в полезности предложения WITH, задав вопрос. Что, если я хочу увидеть среднюю зарплату по должности по отношению к каждому сотруднику, работающему в этом конкретном отделе?
Чтобы объяснить это дальше, если сотрудник A работает в области ИТ, а сотрудник B работает клерком, то я хочу увидеть среднюю заработную плату их соответствующего отдела по сравнению с A и B. Как мне это сделать?

Это можно сделать с помощью предложения WITH. Вот как:

WITH table_one as ( --- A basic SELECT query to get some relevant columns
  SELECT first_name, last_name, salary, job_id
  FROM employee
), 
table_two as ( --- A query that calculates each job_id's avg salary
  SELECT job_id, CAST(avg(salary) as bigint) as avg_salary
  FROM employee
  GROUP BY job_id
)
SELECT t1.*, t2.avg_salary --- Wherever job_id matches, normally join the two tables
FROM table_one AS t1 JOIN table_two AS t2 ON t1.job_id = t2.job_id
ORDER BY t1.first_name;

Вы можете создать серию таблиц, разделенных запятой, с предложением WITH. Каждая созданная таблица будет иметь псевдоним, который можно будет использовать позже в запросе SELECT.

Функции WINDOW в SQL

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

Они идут сразу после оператора SELECT и являются частью проекции.

Вот синтаксис функций WINDOW.

window_function_name() OVER (
‹columns›
ORDER BY ‹columns›
)

Существует 3 типа функций WINDOW:

Совокупность
Ценить
Рейтинг

Чтобы понять превосходство оконных функций, я собираюсь написать последний запрос, который я написал, используя предложение WITH, снова используя оконную функцию. Тип оконной функции, который я собираюсь использовать ниже, — это совокупная оконная функция.

SELECT first_name, last_name, job_id, salary,
  CAST(AVG(salary) OVER (PARTITION BY job_id) AS BIGINT) AS avg_salary_jobid
FROM employee
ORDER BY first_name;

Точно такой же результат, используя гораздо более сложный запрос! Я знаю, трудно поверить, что последние 15-строчный запрос дает тот же результат, что и этот 5-строчный запрос, который мы написали выше.

Позвольте мне объяснить запрос:
- Сначала я обычно выбираю столбцы, затем
- Мы усредняем столбец зарплаты НАД разделом, и
- Разделы находятся над столбцом job_id, так что в основном это означает усреднение заработной платы по этим группам, а затем их включение в результат.

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

SELECT first_name, last_name, job_id, salary,
  CAST(AVG(salary) OVER (PARTITION BY job_id) AS BIGINT) AS avg_salary_jobid,
  CAST(MAX(salary) OVER (PARTITION BY job_id) AS BIGINT) AS max_salary_jobid
FROM employee
ORDER BY first_name;

Ты можешь написать,

SELECT first_name, last_name, job_id, salary,
  CAST(AVG(salary) OVER part AS bigint), --- "part" is the name of the alias
  CAST(MAX(salary) OVER part AS bigint)
FROM employee
WINDOW part AS (PARTITION BY job_id) --- we use "part" again to define partitions
ORDER BY first_name;

Представления PostgreSQL

До сих пор любые запросы SELECT, которые мы использовали, не создавали никаких новых данных в вашей базе данных.

Представления в SQL дают вам возможность сохранять новые таблицы из операторов SELECT. Он не хранит ваши новые данные отдельно, он просто предоставляет вам сохраненное «представление».

Давайте создадим его.

CREATE VIEW it_people as
  SELECT * 
  FROM employee
  WHERE job_id LIKE 'IT%'

Когда вы запускаете вышеуказанный запрос, вы не видите никаких результатов. Это потому, что все, что мы выбрали, было сохранено для представления. Если вы используете PostgreSQL, если вы обновите левую панель, вы увидите представление, созданное следующим образом:

И если вы запустите этот запрос:

SELECT *
FROM it_people

Вы увидите вывод, который в основном представляет собой запрос, сохраненный как представление.

Но каков здесь вариант использования?

Одной из причин использования представлений является то, что вы можете писать очень сложные запросы. Это потому, что теперь, в отличие от предыдущих операторов SELECT, у нас есть запрос, который постоянно сохраняется и может использоваться повторно.

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

Временные таблицы

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

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

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

СОЗДАТЬ ВРЕМЕННУЮ ТАБЛИЦУ table_temp (…);

Например, создание временной таблицы на основе таблицы сотрудников будет выглядеть примерно так:

CREATE TEMPORARY TABLE employee_temp (LIKE employee);

SELECT * FROM employee_temp;

Employee_temp — это таблица, в которой нет данных, из приведенного выше запроса она копирует только дизайн таблицы таблицы сотрудников вместо копирования данных.

Чтобы также скопировать данные, вам нужно будет запустить:

INSERT INTO employee_temp
SELECT * FROM employee;

SELECT * FROM employee_temp;

Таким образом, вы получите таблицу, которая является точной копией таблицы сотрудников. Мы поговорим о предложении INSERT позже в этом курсе.

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

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

Но для этого вам сначала нужно знать другую форму SQL, то есть язык манипулирования данными.

SQL как язык обработки данных

У вас есть некоторые данные в вашей таблице. Очевидно, что бывают случаи, когда вы хотите изменить эти данные, возможно, не в этой конкретной таблице, а во временной таблице или представлении. Как ты это делаешь?

Есть 4 оператора SQL, которые позволяют вам сделать это различными способами:

ВСТАВИТЬ предложение

Используя предложение INSERT, вы можете вставлять значения и столбцы в уже существующую таблицу.

В этом случае вы также можете вставить только значения и значения и столбцы вместе.

Давайте возьмем пример обоих. Во-первых, давайте создадим новую таблицу.

CREATE TABLE persons (
id bigserial,
first_name varchar(30),
last_name varchar(30),
email_id varchar(80),
phone_num varchar(10)
)

После того, как таблица создана, приступаем к вставке!

ВСТАВЬТЕ только значения.

INSERT INTO persons (first_name, last_name, email_id, phone_num)
VALUES
('Mary', 'Sue', '[email protected]', '1245681901');

SELECT * FROM persons;

Это было легко! Вы также можете добавить несколько строк, разделив значения запятой. Так:

INSERT INTO persons (first_name, last_name, email_id, phone_num)
VALUES
('Mary', 'Sue', '[email protected]', '1245681901'),
('Mary', 'Sue', '[email protected]', '1245681901'),
('Mary', 'Sue', '[email protected]', '1245681901');

SELECT * FROM persons;

Как видите, в нашу базу данных добавлено еще 3 столбца!

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

INSERT INTO persons (first_name, email_id, phone_num)
VALUES
('Mary', '[email protected]', '1245681901')

SELECT * FROM persons;

В таком случае SQL покажет null вместо данных в таблице.

Для нахождения медианы из столбца в данных PostgreSQL не предоставляет никаких функций, и стандарты ANSI SQL не предъявляют никаких требований. Но у нас есть две функции для вычисления процентиля из группы.

я). Функция percentile_disc().
ii). Функция percentile_cont().

Первая функция находит дискретное значение процентиля, а вторая находит непрерывное значение. Но как мы можем использовать это, чтобы найти медиану из набора данных?

Мы знаем, что медианное значение — это среднее значение в отсортированных данных. Это значение, выше которого лежит половина больших (или меньших значений, в зависимости от того, как вы отсортировали данные), а ниже лежит другая половина значений, которые меньше его.

Находя 50-й процентиль, т. е. находя значение, превышающее 50 процентов значений в совокупности, мы в основном находим медиану.

Но в чем разница между двумя вышеперечисленными функциями?

Как можно предположить из названий, centile_disc() проверяет только дискретное значение из набора данных. Это означает, что если у вас есть четное количество значений в ваших данных, то у вас будет два медианных значения, например, 14 и 15. Тогда эта функция вернет одно из них.

percentile_cont(), с другой стороны, находит непрерывное значение. Если у нас есть два значения посередине, это даст нам среднее значение этих двух значений. Это соответствует определению того, что такое медиана.

Запрос

percentile_cont(0.5)ВНУТРИ ГРУППЫ (ЗАКАЗАТЬ ПО «column_name»)

возвращает значение 50-го процентиля в столбце, следовательно, медиану. Но как насчет всех ключевых слов, таких как «ВНУТРИ ГРУППЫ», и почему мы используем «ORDER BY». Как мы знаем, ORDER BY по умолчанию сортирует нашу базу данных по возрастанию. Мы также знаем, что для вычисления Медианы у нас должны быть отсортированные данные.

Таким образом, приведенный выше запрос в основном говорит: ЗАКАЗАТЬ базу данных, используя этот столбец, и найти 50-й процентиль (медиану) для этого столбца, о котором мы упоминали.

Вместо того, чтобы находить только медиану, вы также можете использовать функцию centile_cont() для поиска других процентилей, таких как первый квартиль (самые низкие 25 процентов данных), второй квартиль (который, конечно, снова является медианой), третий квартиль и т. д.

Вы также можете рассчитать моду в наборе данных. Мода — это значение, которое чаще всего появляется в наборе данных.

Подобно медиане, он не является частью стандартного SQL, но Postgresql предоставляет его.

Синтаксис очень похож на вычисление медианы.

ВЫБРАТЬ режим() ВНУТРИ ГРУППЫ (ЗАКАЗАТЬ ПО p0010001)

ОТ table_name;

Приведенный выше запрос сортирует таблицу и находит режим внутри группы отсортированного столбца.

Выполнение основных математических операций, таких как эти, важно для работы с SQL. Мой основной язык — Python, поэтому, изучая эти вещи, я все время думал, почему я не могу просто скопировать данные в Python и обрабатывать их там.

Но нет, я (а теперь и вы) изучали SQL, и это его часть.

ОБЪЕДИНЕНИЕ ТАБЛИЦ В РЕЛЯЦИОННОЙ БАЗЕ ДАННЫХ — полезная вещь

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

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

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

ВЫБЕРИТЕ *

ИЗ таблицы_a ПРИСОЕДИНИТЬСЯ к таблице_b

ON table_a.key_column = table_b.foreign_key_column;

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

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

ВЫБЕРИТЕ *

ИЗ таблицы_a ПРИСОЕДИНИТЬСЯ к таблице_b

ON table_a.key_column ‹= table_b.foreign_key_column;

Соединение таблиц путем сравнения оператора меньше или больше всегда будет редким случаем из-за того, насколько меньше возникает необходимость/применение, но оно всегда есть, если вам это нужно.

ПРИМЕЧАНИЕ. Используйте эту точку с запятой, чтобы сообщить SQL, что все готово. Не оставляйте его висеть.

КЛЮЧЕВЫЕ СТОЛБЦЫ

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

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

Но что может быть важным, так это, скажем, идентификатор, уникальный код для каждой строки таблицы. Например, у вас может быть таблица с информацией о клиентах, их именами, контактными данными и т. д. Каждая строка в этой таблице имеет уникальный идентификатор. В другой таблице может быть информация о том, когда клиенты посещали сайт, но вместо имени клиента мы можем использовать идентификатор для их идентификации.
Таким образом, оба столбца будут связаны.

CREATE TABLE customers 
( Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), 
 first_name varchar(25),
last_name varchar(25) );

INSERT INTO customers VALUES 
(“Max”, “Burton”), (“John”, ”Smith”);

CREATE TABLE visit_date
(Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
visit_date DATE);

INSERT INTO visit_date 
SELECT Id FROM customers;

INSERT INTO visit_date VALUES
(“2020–05–11”, “2020–05–12”);

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

Возьмем другой пример. У вас есть данные о некоторых школьных сотрудниках и о том, как они получают зарплату.
Но вместо того, чтобы красиво упорядочивать их в одной таблице, они поступают к вам в формате нескольких CSV-файлов. Как вы с этим справляетесь, не говоря уже об анализе?

Ответ прост. Давайте пройдем через это.

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

Чтобы следовать модели реляционной базы данных, мы собираемся создать единый общий id для каждой таблицы. Таким образом, таблица, содержащая информацию о заработной плате, также будет косвенно связана с субъектом учителя.

Давайте посмотрим на код.

---1
CREATE TABLE subjects ( 
subject_id bigserial,
subject varchar(100),
CONSTRAINT subject_key PRIMARY KEY (subject_id));

---2
CREATE TABLE teachers ( 
teacher_id bigserial,
first_name varchar(50),
last_name varchar(50),
salary integer,
subject_id integer REFERENCES subjects (subject_id),
CONSTRAINT teacher_key PRIMARY KEY (teacher_id),
CONSTRAINT teacher_subject_unique UNIQUE (teacher_id, subject_id)
);

---3
INSERT INTO subjects (subject) VALUES 
('Mathematics'), ('Chemistry'), ('Chemistry');

INSERT INTO teachers (first_name, last_name, salary, subject_id)
VALUES
('John', 'Spektor', 42000, 1),
('Mary', 'Thomas', 41000, 2),
('Carly', 'Shelby', 35000, 2);

Давайте посмотрим, что произошло в каждом из приведенных выше запросов.

Мы создали таблицу, которая обрабатывает данные субъекта. В нем есть индексный столбец subject_id типа bigserial, который автоматически увеличивается всякий раз, когда вы добавляете значения в таблицу.
Тогда просто имя субъекта varchar.
Но о чем эта последняя строка?
Ограничения — это правила, которым таблица должна следовать. Здесь мы устанавливаем ограничение первичного ключа, что означает, что все строки в этой таблице должны быть разными. Subject_id становится первичным ключом, а subject_key — это имя первичного ключа, окруженное ключевыми словами CONSTRAIN и PRIMARY KEY. Это означает, что каждая запись будет иметь уникальный идентификатор (в данном случае subject_id).
Это просто, пока мы не определим столбец зарплаты как целое число. Мы создали таблицу с информацией об учителях.
Затем мы используем ключевое слово REFERENCES, чтобы сообщить Postgresql, что subject_id для каждого предмета учителя будет ссылаться на subject_id из таблицы предметов, которую мы создали ранее.
Как и в случае с таблицей предметов, мы затем создаем первичный ключ Teacher_id для таблицы нашего учителя.
Теперь мы увидели ключевое слово PRIMARY KEY. UNIQUE похож на него. Это гарантирует, что все записи в нашей таблице не могут иметь одинаковые (teacher_id, subject_id), что означает, что один учитель не может преподавать несколько предметов. Не самый совершенный пример, но он хорошо объясняет и помогает мне понять.
Теперь мы просто вставляем значения в таблицы. Обратите внимание, что при вставке в таблицу учителей у двух учителей одинаковый subject_id. Это возможно, поскольку другой учитель может быть замещающим учителем.

Вы можете заметить, что во второй таблице subject_id ссылается на subject_id из первой. Обратите внимание, что это возможно только в том случае, если в первой таблице есть столбец с именем subject_id. Этот subject_id в таблице учителя называется внешним ключом.

Возникает много вопросов, самый важный из которых заключается в том, какую пользу приносит нам такая организация данных?

Скажем, если бы у вас были все данные, расположенные в одной таблице. Чем бы вы тогда рискнули?

Если бы у нас были данные о нескольких школах, а не об одной, то один и тот же предмет преподавали бы несколько учителей. Допустим, они преподают «Защиту от темных искусств», если у вас есть несколько сотен строк, это не имеет большого значения, потому что вы можете повторить эту информацию. Но когда вы имеете дело с миллионом столбцов и строкой, повторяющейся, скажем, более 100 тысяч раз, это потребует гораздо больше памяти, чем простое присвоение целочисленного идентификатора (как мы это сделали).
Что, если они завтра сменят название предмета «Защита от темных искусств» на «Проклятый предмет»? Вам пришлось бы обновить все записи, если бы у вас была одна таблица. Здесь это не так, поскольку вы просто обновляете имя один раз в таблице субъекта.

Как работает запрос с помощью JOIN?

Когда вы СОЕДИНЯЕТЕ две таблицы с помощью упомянутого ранее запроса, SQL объединяет все столбцы в строках, которые удовлетворяют поставленному нами условию.

Например,

SELECT *
FROM teachers JOIN subjects
ON teachers.subject_id = subjects.subject_id;

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

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

Типы JOIN

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

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

ПРИСОЕДИНИТЬСЯ
ЛЕВОЕ СОЕДИНЕНИЕ
ПРАВОЕ ПРИСОЕДИНЕНИЕ
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ

ПРИСОЕДИНИТЬСЯ

Если вы соединяете две таблицы, используя этот тип JOIN (альтернативный синтаксис — INNER JOIN). SQL вернет строки из обеих таблиц, где совпадающие значения находятся в том месте, где вы соединили две таблицы.

ЛЕВОЕ СОЕДИНЕНИЕ

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

ПРАВОЕ ПРИСОЕДИНЕНИЕ

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

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Все строки возвращаются как из левой, так и из правой таблицы, затем объединяются строки, в которых значения совпадают.

ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ

Это возвращает все, что возможно из комбинации двух таблиц. На основе соединительной колонки, конечно.

Очень полное руководство — как работать с SQL?