1. Введение
Декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных.
Какие языки являются декларативными?
Декларативные: SQL, HTML. Смешанные (могут быть таковыми): JavaScript, C#, Python.
Императивный подход описывает, каким образом ты что-то делаешь. Декларативный описывает, что именно ты делаешь.
SQL от англ. Structured Query Language — «язык структурированных запросов». Изначально SQL был основным способом работы с базами данных. Он позволял выполнять следующий набор операций:
- создание в базе данных новой таблицы;
- добавление в таблицу новых записей;
- изменение записей;
- удаление записей;
- выборка записей из одной или нескольких таблиц (в соответствии с заданным условием);
- изменение структур таблиц.
Со временем SQL усложнился — обогатился новыми конструкциями, обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры) — и стал приобретать черты, свойственные языкам программирования.
Возник SQL в 70-х гг. в IBM, когда создана была релляционная БД, и назывался SEQUEL (tructured English QUEry Language — «структурированный английский язык запросов»), позволял относительно просто управлять данными в этой СУБД. Первый стандарт языка был разработан в 1986 г.
2. Постгрес и клиенты постгрес
Практически в каждой СУБД применяется свой процедурный язык, в частности, в Oracle Database используется PL/SQL (поддерживается также в DB2 и Timesten[en]), в Interbase и Firebird — PSQL, в DB2 — SQL PL[en], в Microsoft SQL Server и Adaptive Server Enterprise — Transact-SQL, в PostgreSQL — PL/pgSQL.
Мы будем тренироваться на PostgreSQL — свободная объектно-реляционная система управления базами данных. Существует в реализациях для множества UNIX-подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux, macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows. Выпущена в 1996г. как реляционная СУБД и SQL server. PostgreSQL-кластер — это один или несколько хостов базы данных, между которыми можно настроить репликацию. Репликация работает по умолчанию в любом кластере из более чем 1 хоста: хост-мастер принимает запросы на запись и дублирует изменения в репликах. Я ставлю в 2023г. версию 15.1 для винды 11- 64.
Кластер можно создать на яндексовском облаке. Здесь хранится документация, а здесь скачиваем. Там же есть каталог софта.
Также необходима платформа для разработки на PostgreSQL, которая называется pgAdmin. Об этом я писала в той же статье.
Как альтернатива, другой GUI (graphical user interface - графический интерфейс пользователя) клиент PostgreSQL - DBeaver.
PgAdmin выглядит так:
DBeaver выглядит так:
Таким образом, PgAdmin — это платформа с открытым исходным кодом для администрирования и разработки для PostgreSQL и связанных с ней систем управления базами данных. Платформа написана на Python и jQuery и поддерживает все функции PostgreSQL. Вы можете использовать pgAdmin для любых операций, начиная с записи базовых SQL-запросов и заканчивая осуществлением мониторинга ваших баз данных и настройки продвинутых архитектур баз данных.
DBeaver является кроссплатформенным инструментом и работает на платформах, поддерживаемых Eclipse (Windows, Linux, macOS X, Solaris). DBeaver доступен на английском, китайском, русском, итальянском и немецком языках. DBeaver является СПО и распространяется под лицензией GNU GPL2, написан на языке Java.
*СПО, специальное програмное обеспечение - категория программ, которые разработаны и созданы для решения задач определенного класса.
Можно почитать о них подробную статью, общие характеристики приведены ниже:
3. Операторы SQL
делятся на:
CREATE создаёт объект базы данных (саму базу, таблицу, представление, пользователя и так далее),
ALTER изменяет объект,
DROP удаляет объект;
SELECT выбирает данные, удовлетворяющие заданным условиям,
INSERT добавляет новые данные,
UPDATE изменяет существующие данные,
DELETE удаляет данные;
GRANT предоставляет пользователю (группе) разрешения на определённые операции с объектом,
REVOKE отзывает ранее выданные разрешения,
DENY задаёт запрет, имеющий приоритет над разрешением;
COMMIT применяет транзакцию,
ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции,
SAVEPOINT делит транзакцию на более мелкие участки.
4. Как SQL объединяет таблицы
JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в предложение FROM операторов SELECT, UPDATE и DELETE.
SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL. Существует 4 различных типа соединений SQL: SQL INNER JOIN (иногда называется простым соединением) SQL LEFT OUTER JOIN (иногда называется LEFT JOIN) SQL RIGHT OUTER JOIN (иногда называется RIGHT JOIN) SQL FULL OUTER JOIN (иногда называется FULL JOIN).
Оператор языка SQL UNION предназначен для объединения результирующих таблиц базы данных, полученных с применением слова SELECT.
There is a major difference between JOIN and UNION in SQL. Using the JOIN clause, we combine the attributes of two given relations and, as a result, form tuples. Whereas we use the UNION clause when we want to combine the results obtained from two queries. They both combine data differently.
5. Запросы и представления
1) Запросы
Главные задачи SQL — составлять запросы так, чтобы находить среди большого объёма информации ту, что нужна для конкретных целей, сортировать её, структурировать и представлять в наиболее простом и понятном виде.
Как и во всех языках программирования, в PL/SQL имеются операторы циклов. Их три основных типа:
Безусловные циклы (выполняемые бесконечно)
Интерактивные циклы (FOR)
Условные циклы (WHILE)
Оператор SQL IN-это логический оператор, который проверяет, соответствует ли конкретное значение в предложении WHERE SQL-запроса любому значению в содержащемся в нем списке.
Функция. COUNT (*) возвращает количество строк в указанной таблице с учетом повторяющихся строк. Она подсчитывает каждую строку отдельно. При этом учитываются и строки, содержащие значения NULL. OVER [partition_by_clause] [order_by_clause] [ROW_or_RANGE_clause]) partition_by_clause делит результирующий набор, полученный с помощью предложения. FROM, на секции, к которым применяется функция COUNT.
Оконная функция в SQL - функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.
Сложные запросы SQL представляют из себя комбинации простых запросов.
Выполняясь, простые запросы возвращают сгруппированные в промежуточные таблицы наборы данных. А сложный запрос уже манипулирует данными, полученными благодаря простым «подзапросам».
Сложные запросы получаются следующими способами: Помещением одного запроса в другой. В этом случае внешнее выражение будет называться основным запросом, а вложенное выражение - подзапросом.
Команда DISTINCT позволяет выбирать только уникальные значения из базы данных (то есть отсеивает дубли: к примеру, в таблице есть две Маши - тогда запрос выведет только первую).
Вместо DISTINCT можно использовать DISTINCTROW - в mySQL это одно и то же. См. также команду COUNT, которая может быть использована для подсчета уникальных значений (в комбинации с DISTINCT).
И снова о рекурсивных запросах...
Объединение нескольких запросов можно достич при помощи оператора UNION. Оператор UNION объединяет несколько запросов, но исключает повторяющиеся значениея из результата выдачи. Пример SELECT запроса с применением оператора UNION: (SELECT login, pass, male FROM users) UNION (SELECT login, pass, male FROM clients).
Оптимизация запросов.
У большинства MySQL серверов включено кэширование запросов. Один из наилучших способов улучшения производительности — просто предоставить кэширование самой базе данных. Когда какой-либо запрос повторяется много раз, его результат берется из кэша, что гораздо быстрее прямого обращения к базе данных.
LIKE - это стандартный оператор ANSI / ISO для сравнения значения столбца с другим значением столбца или строкой в кавычках. Возвращает 1 (ИСТИНА) или 0 (ЛОЖЬ). Оператор SQL LIKE применяется только к полю типов CHAR или VARCHAR, чтобы соответствовать шаблону. Чтобы соответствовать шаблону из слова, специальные операторы и символы подстановки могли использоваться с оператором LIKE.
recursive common table expressions (CTEs)
Общее табличное выражение, или CTE, (в SQL) - это временный именованный набор результатов, полученный из простого запроса и определенный в пределах области выполнения оператора SELECT, INSERT, UPDATE или delete. CTE можно рассматривать как альтернативу производным таблицам (подзапросам), представлениям и встроенным пользовательским функциям.
2) Представления
CTE хранятся только в период запроса, в отличие от представлений. Кроме того, представления бывают ещё и материализованными.
Представление (англ. view, иногда используются названия «вид», «взгляд») — виртуальная (логическая) таблица, представляющая собой поименованный запрос (синоним к запросу), который будет подставлен как подзапрос при использовании представления.Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему.
Для ускорения запросов используют индексы.