HOSTCMS v.6. Полезные SQL запросы
Подборка SQL запросов к базе данных для сайтов на HostCMS v.6. Может быть очень полезной в некоторых случаях в пройцессе создания или обслуживания сайта...
SQL (Structured Query Language) — это язык программирования, который позволяет управлять данными и манипулировать ими в системах управления реляционными базами данных. В наши дни все малые и крупные предприятия полагаются на SQL для хранения и преобразования данных. В большинстве сценариев изучение только основных команд позволяет нам эффективно управлять нашими базами данных.
SQL предоставляет полный список команд sql для взаимодействия с базами данных. SQL можно рассматривать как набор инструкций, передаваемый в базу данных. Эти инструкции, известные как команды языка SQL, позволяют нам выполнять широкий спектр действий. Например, мы можем использовать команды SQL для создания структуры базы данных, создания таблицы или временной таблицы, заполнения базы данных, извлечения определенной информации, изменения данных, а также управления доступом и безопасностью.
Ниже приведен краткий обзор основных команд SQL, описанных ниже в этой статье. Новички могут изучить эти команды, чтобы понять основы SQL.
Это структурированный запрос, используемый для связи с базой данных. Он следует определенному синтаксису, который включает предложения, ключевые слова и условия для написания запроса. Пользователи могут настраивать SQL-операторы в соответствии со своими конкретными потребностями в базе данных и выполняемыми операциями.
SELECT column_name;
FROM table_name WHERE condition;
Ниже приведены различные типы SQL-команд:
DDL состоит из команд уровня базы данных для изменения структуры базы данных. Эти команды DDL определяют, изменяют и удаляют таблицы, представления, индексы и схемы баз данных базы данных. Более того, команды DDL фиксируются автоматически, что гарантирует, что изменения будут навсегда сохранены в базе данных и не смогут откатиться к предыдущему изменению.
Эта команда создает новые объекты базы данных. Объектом может быть таблица или база данных, как показано ниже.
CREATE DATABASE database_db;
Этот SQL-оператор создает новую database_db базы данных.
CREATE TABLE PERSONS (id INT, name VARCHAR(255));
Этот SQL-оператор создает новую таблицу PERSONS со столбцами id и name.
Эта команда изменяет структуру существующего объекта путем добавления, изменения или удаления столбцов таблицы, изменения типов данных или переименования объектов.
ALTER TABLE PERSONS ADD COLUMN address VARCHAR(255);
Эта команда SQL добавляет новый столбец ADDRESS в таблицу PERSONS.
Команда DROP удаляет существующие объекты базы данных.
DROP DATABASE database_db;
Эта инструкция SQL удаляет всю database_db базы данных.
DROP TABLE PERSONS;
Эта инструкция удаления удаляет существующую таблицу PERSONS из базы данных.
При этом из таблицы удаляются все существующие данные, при этом сохраняется исходная структура таблицы. TRUNCATE обычно работает быстрее, чем DELETE, так как он не регистрирует удаление отдельных строк.
TRUNCATE TABLE PERSONS;
Приведенный выше оператор SQL удаляет все записи/строки из таблицы PERSONS.
Примечание: Ключевое слово CASCADE является обязательным, если мы усекаем таблицу, содержащую первичные ключи, которые используются в других таблицах в качестве внешних ключей. Это приведет к усечению всех зависимых таблиц.
Этот SQL-оператор добавляет комментарий к определению конкретного объекта базы данных, что важно для целей документирования.
COMMENT ON TABLE PERSONS IS 'Table contains persons information';
DML состоит из основных команд SQL для управления данными, присутствующими в базе данных. Например, эти списки команд SQL включают команды для вставки, изменения и удаления данных. Команды DML не фиксируются автоматически, что гарантирует, что изменения не будут навсегда сохранены в базе данных и мы сможем вернуться к предыдущему состоянию. Например, мы можем восстановить удаленную строку с помощью оператора ROLLBACK.
Эта команда добавляет новые данные в таблицу. Приведенная ниже команда добавляет новую строку в таблицу PERSONS.
INSERT INTO PERSONS (id, name) VALUES (10, 'Alice');
Это приведет к обновлению существующих данных в таблице. Как показано ниже, команда UPDATE обновляет имя ПЕРСОНЫ с идентификатором 10.
UPDATE PERSONS SET name = 'Alice' WHERE id = 10;
При этом существующие данные удаляются на основе некоторого условия.
DELETE FROM PERSONS WHERE id = 5;
Оператор delete удаляет пользователя с идентификатором 5 из таблицы PERSONS.
Команда DQL — это подмножество SQL-команд, специально разработанных для запроса и извлечения данных из базы данных. Команда DQL (SELECT) выполняет определенные задачи над данными в объектах схемы и извлекает отношения схемы на основе переданного ей запроса. Он использует различные предложения, функции и ключевые слова для фильтрации и обработки данных, тем самым расширяя свою функциональность.
Эта команда извлекает указанный столбец (имя) из таблицы:
SELECT name FROM PERSONS;
Чтобы получить данные из всех столбцов, вы можете использовать SELECT * (звездочка):
SELECT * FROM PERSONS;
Однако использование * обычно не рекомендуется, так как оно увеличивает объем передаваемых данных за счет включения всех столбцов, даже тех, которые не являются обязательными. Это может повлиять на производительность запросов. Вместо этого лучше явно перечислить нужные столбцы:
SELECT id, name, email FROM PERSONS;
Оператор SELECT обычно используется с другими предложениями и функциями, такими как DISTINCT, AVG(), WHERE, ORDER BY, GROUP BY и HAVING для извлечения данных и их агрегирования, фильтрации, сортировки или группировки для возврата одного или нескольких столбцов.
SELECT DISTINCT name FROM PERSONS;
Эта команда игнорирует повторяющиеся строки или множественные значения и возвращает только уникальные значения из указанного столбца, такие как столбец name из таблицы PERSONS.
SELECT column_name(s) FROM table_name WHERE column_name operator value;
Предложение WHERE фильтрует данные на основе указанного условия, например WHERE name = 'Alice'.
SELECT column_name(s) FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;
Это позволяет нам объединять несколько условий с помощью логических операторов.
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
Мы можем использовать подстановочные знаки (% для любой строки, _ для одного символа) для выполнения поиска шаблона с помощью оператора LIKE.
SELECT column_name(s) FROM table_name LIMIT number;
Это предложение ограничивает количество возвращаемых строк.
SELECT column_name FROM table_name ORDER BY column_name ASC | DESC;
Это предложение сортирует результаты на основе указанного столбца таблицы в порядке возрастания (ASC) или убывания (DESC).
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
Это предложение часто используется с агрегатными функциями, такими как COUNT(), для группировки строк на основе значений в указанном столбце.
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
Это предложение используется с GROUP BY для фильтрации сгруппированных результатов.
SELECT column_name(s) FROM table_1 JOIN table_2 ON table_1.column_name = table_2.column_name;
Это предложение объединяет строки из нескольких таблиц, в которых условие соединения истинно.
SELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;
Это предложение извлекает данные из двух или более таблиц. Здесь он объединяет все строки из table_1 и совпадающие строки из table_2. Если в table_2 нет совпадения, он использует значения NULL.
SELECT column_name AS 'Alias' FROM table_name;
Это ключевое слово отображает результаты с временным именем столбца.
WITH temporary_name AS (SELECT FROM table_name) SELECT FROM temporary_name WHERE column_name operator value;
Это предложение определяет временный результирующий набор, на который можно ссылаться в запросе.
Мы также можем использовать операторы SELECT для извлечения и агрегирования данных из базы данных с помощью встроенных функций, таких как AVG(), SUM(), COUNT() и т. д.
Эта функция извлекает среднее число из выбранного столбца в SQL-операторе. Здесь AVG() вычисляет среднее значение столбца оценок из таблицы учащихся.
SELECT AVG(MARKS) as AVERAGE_SCORE from STUDENT;
Эта функция извлекает сумму чисел из выбранного столбца в операторе SQL. Здесь SUM() вычисляет среднее значение столбца оценок из таблицы учащихся.
SELECT SUM(MARKS) as TOTAL_MARKS from STUDENT;
На основе приведенных выше команд SQL существует логический порядок, который соблюдается при извлечении таблицы или набора таблиц. На рисунке ниже показано, как 2 таблицы используются для получения реляционных данных на основе нескольких SQL-команд.
Команды, управляющие правами и разрешениями пользователей в базе данных, относятся к DCL. Команды DCL управляют доступом к объектам базы данных путем предоставления или отзыва привилегий пользователям, а также контролируют уровень доступа пользователей к различным частям базы данных.
Эта команда используется для предоставления пользователям определенных привилегий к объектам базы данных.
GRANT SELECT, INSERT ON PERSONS TO admin;
Это позволяет администратору выбирать и вставлять данные в таблицу ПЕРСОНЫ.
Эта команда используется для отзыва назначенных привилегий у пользователей.
REVOKE INSERT ON PERSONS FROM admin;
При этом у администратора отменяется разрешение на вставку в таблицу PERSONS.
TCL поддерживает согласованность данных, гарантируя, что либо все операторы в транзакции успешно зафиксированы, либо ни один из них не применяется. Мы используем команды TCL, такие как "COMMIT" и "ROLLBACK" в сочетании с DML-командами (язык обработки данных).
Эта команда навсегда сохраняет все изменения, сделанные в рамках транзакции.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;
COMMIT;
Инструкция commit обновляет обе учетные записи, обеспечивая согласованность данных. Это гарантирует, что транзакционные данные передаются без каких-либо расхождений.
Эта команда откатывает все изменения, сделанные в транзакции с момента последнего COMMIT или ROLLBACK.
BEGIN TRANSACTION;
DELETE FROM accounts WHERE account_id = 555;
ROLLBACK;
Приведенная выше команда откатывает удаление, восстанавливая учетные записи.
Эта команда определяет точку транзакции, до которой можно откатить состояние таблицы в любой момент времени.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
SAVEPOINT after_insert;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 123;
ROLLBACK TO after_insert;
При этом происходит откат обновления добавления баланса в счетах после использования SAVEPOINT.
Эти выражения добавляют логику в запросы. Операторы IF, CAND и COALESCE можно использовать для записи условных выражений.
Эта команда не является SQL-командой, но может использоваться в некоторых диалектах SQL, таких как MySQL, PostgreSQL и т.д. Он выполняет SQL-операторы на основе заданного условия:
-- MySQL syntax:
IF (Score > 50) THEN
SELECT 'Pass' AS ExamStatus;
ELSE
SELECT 'Fail' AS ExamStatus;
END IF;
-- PostgreSQL (PL/pgSQL) syntax:
IF Score > 50 THEN
result := 'Pass';
ELSE
result := 'Fail';
END IF;
Важное примечание: Операторы IF нельзя использовать в обычных SQL-запросах. Для условной логики в стандартных SQL-запросах используйте выражение CASE. CASE поддерживается всеми базами данных SQL и считается стандартным способом обработки логики условных запросов.
Эта команда работает как оператор if-else в языках программирования:
SELECT StudentID,
CASE
WHEN Score > 50 THEN 'Pass'
ELSE 'Fail'
END AS ExamStatus
FROM STUDENTS;
Эта функция SQL управляет значениями NULL и возвращает первое значение, отличное от NULL. Например, если я дам ему список выражений только с одним значением, отличным от NULL, он вернет только это значение. В приведенном ниже коде, если столбец Score имеет значение NULL, эта функция заменяет его на ноль.
SELECT StudentID,
COALESCE(Score1, 0) AS FinalScore
FROM STUDENTS;
Практика | Преимущество |
Управление доступом на основе ролей | Ответственное назначение ролей пользователей в зависимости от потребностей пользователя в доступе |
Шифрование данных | Шифрование конфиденциальных данных, таких как пароли и данные банковских карт |
Безопасные методы аутентификации | Используйте OAuth 2.0 для защиты от несанкционированного доступа |
Ниже приведены основные диалекты SQL и их использование, на которых они сравниваются и используются в целях разработки различных систем баз данных.
Диалект | Функции |
PL/pgSQL (PostgreSQL) | – Известен расширенными функциями, такими как поддержка JSON/JSONB, оконные функции и CTE. — Поддерживает полнотекстовый поиск. — Открытый исходный код с обширной поддержкой сообщества. – Поддерживает различные стеки и используется в основном в системах, требующих сложных запросов с высокопроизводительной аналитикой, таких как финансовые системы и хранилища данных. |
MySQL | — Открытый исходный код и широко используется для веб-разработки. — Простая интеграция с веб-технологиями (PHP, Python и т.д.) – Ограниченная поддержка расширенных аналитических функций. — Используется в основном на платформах электронной коммерции. |
TSQL (SQL Server) | — Отличная интеграция с продуктами Microsoft, такими как Azure. – В основном используется в крупномасштабных приложениях. — Предоставляет расширенные возможности настройки. – Это может быть дорогостоящий вариант, используемый в основном в ERP-системах. |
PL/SQL (оракул) | – Предназначен для применения в больших объемах. – Отличное управление восстановлением и параллелизмом. – Высокая стоимость, лучше всего работает с экосистемой Oracle. – Используется в отраслях, требующих высокой доступности и масштабируемости. |
SQL играет решающую роль во взаимодействии с реляционными базами данных, а его интеграция с языками программирования, такими как Python и JAVA, а также инструментами бизнес-аналитики (BI) расширяет возможности создания мощных приложений, управляемых данными.
Предположим, что в базе данных есть таблица клиентов и разрабатывается приложение на языке Python, которое извлекает данные о клиентах и использует их для получения аналитических сведений о клиентах. Вот как SQL-запросы интегрируются в Python с помощью библиотек SQLite3 или SQLalchemy.
import sqlite3
#connecting to the database
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
#building query
cursor.execute("SELECT * FROM CUSTOMERS")
#fetch all data
rows = cursor.fetchall()
В целом, команды SQL помогают нам в эффективном управлении базами данных. Пользователи могут выбирать из его категорий и нескольких диалектов для подключения к базе данных, выполнения операций и обеспечения целостности и безопасности своих данных.
Команды SQL используются для связи с реляционными базами данных для хранения, извлечения и обработки данных.
Мы можем интегрировать SQL с помощью различных встроенных библиотек, таких как SQLalchemy.
Команда truncate удаляет все строки таблицы, сохраняя при этом структуру таблицы. Однако команда delete удаляет данные из таблицы на основе некоторого пользовательского условия или логики, предоставленной в запросе. Более того, удаленные (команда DML) объекты могут быть откатаны, в то время как усеченные (команда DDL) строки удаляются навсегда.
Другие статьи по теме:
Подборка SQL запросов к базе данных для сайтов на HostCMS v.6. Может быть очень полезной в некоторых случаях в пройцессе создания или обслуживания сайта...