Разработка базы данных

SQL

определение

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

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

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

базовая грамматика

-- Запросить данные
SELECT * FROM имя таблицы WHERE условие;

--Вставить данные
INSERT INTO имя таблицы (столбец 1, столбец 2) VALUES (значение 1, значение 2);

--Обновить данные
UPDATE имя таблицы SET столбец 1 = значение 1 WHERE условие;

-- Удалить данные
УДАЛИТЬ ИЗ имени таблицы ГДЕ условие;

--Создать таблицу
Имя таблицы CREATE TABLE (
    Имя столбца 1 тип данных,
    Имя столбца 2, тип данных
);

Общие типы данных

• INT: Целочисленный тип.
• VARCHAR(n):Символовая строка переменной длины, доnперсонажи.
• DATE:Тип даты.
• FLOAT: Тип с плавающей запятой.
• BOOLEAN: логическое значение (истина или ложь).

преимущество

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

MySQL

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

MySQL — это популярная система управления реляционными базами данных (СУБД) с открытым исходным кодом, которая использует SQL в качестве языка запросов и подходит для малых, средних и крупных приложений.

характеристика

• Высокая производительность и стабильность
• Поддерживает многопользовательский режим и многопоточность.
• Работает кроссплатформенно
• Богатая поддержка сообщества и бизнеса

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

mysql -u root -p
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

Применимые сценарии

• Динамический веб-сайт и онлайн-бизнес-платформа
• Хранение и управление большими объемами данных
• Приложения, требующие высокой производительности и надежности

SQLite

Легкая база данных

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

характеристика

• Храните все данные в одном файле
• Поддерживает обработку транзакций ACID
• Кроссплатформенная поддержка
• Открытый исходный код и бесплатно

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

sqlite3 example.db
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

Применимые сценарии

• Встроенные системы и мобильные приложения
• Хранение данных для небольших веб-сайтов или инструментов
• Быстрая разработка и тестирование

PostgreSQL

PostgreSQL (часто называемый Postgres) — это мощная объектно-реляционная система баз данных с открытым исходным кодом (ORDBMS). Она основана на более чем 30-летней активной разработке и имеет прочную репутацию благодаря надежности, функциональной устойчивости и производительности и известна как «самая передовая в мире база данных с открытым исходным кодом».

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

• Высокая совместимость со стандартами SQL:Поддерживает большинство стандартных функций SQL:2023, включая сложные запросы, внешние ключи, триггеры, представления и целостность транзакций.
• Поддержка транзакций ACID:Полностью поддерживает атомарность, согласованность, изоляцию и долговечность, чтобы гарантировать правильное хранение данных при любых обстоятельствах.
• Расширяемость:Пользователи могут настраивать типы данных, типы индексов, функциональные языки (например, PL/pgSQL, Python, Perl) и даже разрабатывать собственные пакеты расширений (например, PostGIS).
• Управление многоверсионным параллелизмом (MVCC):Операции чтения и записи не блокируют друг друга, что значительно повышает производительность в средах с высоким уровнем параллелизма.
• Различные типы данных:Помимо стандартных значений и строк, изначально поддерживаются JSONB (неструктурированные данные), геометрические координаты, сетевые адреса, массивы и типы диапазонов.

Техническая архитектура

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

Общие комплекты расширения

Мощь Postgres во многом обусловлена ​​его богатым расширением экосистемы:

• PostGIS：Добавление поддержки географических информационных систем (ГИС) в Postgres на данный момент является наиболее мощным решением для обработки пространственных данных.
• TimescaleDB：Расширения, оптимизированные для данных временных рядов.
• pg_stat_statements：Важный инструмент для мониторинга и анализа производительности выполнения SQL.
• pgvector：Поддерживает поиск по сходству векторов, что является ключевым компонентом для разработки приложений искусственного интеллекта и LLM (RAG).

Сравнение преимуществ и недостатков

Применимые сценарии

• Финансовые системы и системы электронной коммерции:Требуются строгая обработка транзакций и точность данных.
• Географическая информационная система:Работайте с PostGIS для обработки карт и пространственных операций.
• Приложения искусственного интеллекта:Используйте pgvector для хранения и извлечения векторных вложений.
• Анализ больших данных:В качестве хранилища данных (Data Warehouse) обрабатывайте сложные запросы отчетов.

T-SQL

T-SQL, полное имя которого — Transact-SQL, представляет собой расширенную версию стандарта SQL, разработанную Microsoft и Sybase. Это основной язык связи для Microsoft SQL Server и служб, связанных с SQL Azure. По сравнению со стандартным SQL, T-SQL добавляет возможности программирования, позволяя не только запрашивать данные, но и выполнять сложные логические операции.

языковые особенности

• Объявление переменной: позволяет использовать ключевое слово DECLARE для определения локальных переменных для временного хранения результатов вычислений.
• Управление процессом: поддерживает определение IF...ELSE, цикл WHILE и оператор GOTO.
• Встроенные функции: Предоставляет большое количество эксклюзивных функций для строк, дат, математических операций и управления системой.
• Обработка ошибок: механизм TRY...CATCH введен для улучшения обработки исключений на стороне базы данных.
• Управление транзакциями: обеспечивает высокую степень контроля над транзакциями для обеспечения свойств ACID данных.

Ключевые различия между T-SQL и стандартным SQL

Базовый пример грамматики

Следующий код показывает, как объединить переменные, логические заключения и запросы данных в T-SQL:

-- Объявляем и устанавливаем переменные
DECLARE @Threshold INT = 100;
ОБЪЯВИТЬ @CurrentStock INT;

-- Получить инвентарь конкретного продукта
ВЫБЕРИТЕ @CurrentStock = Количество запасов
ИЗ продуктов
ГДЕ ProductID = 5;

- логическое суждение
ЕСЛИ @CurrentStock < @Порог
НАЧАТЬ
    PRINT 'Предупреждение: запасы ниже заданного порога. ';
    -- Выполнить логику пополнения...
КОНЕЦ
ЕЩЕ
НАЧАТЬ
    ВЫБЕРИТЕ * ИЗ ПРОДУКТОВ ГДЕ ProductID = 5;
КОНЕЦ

Сценарии применения

T-SQL широко используется в следующих областях:

• Разрабатывайте хранимые процедуры: инкапсулируйте сложную логику в базе данных для повышения производительности.
• Создавать триггеры (Triggers): автоматически выполнять действия при возникновении определенных событий в таблице данных.
• Поддерживать представления: предоставляйте виртуальные представления данных для упрощения запросов.
• Запланированные задачи: регулярно выполнять пакетную обработку данных и резервное копирование с помощью агента SQL Server.

Разработка программ T-SQL

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

Объявление и присвоение переменной

В T-SQL все пользовательские переменные должны начинаться с символа @. Используйте DECLARE для объявления и SET или SELECT для назначения.

-- Объявляем переменные
ОБЪЯВИТЬ @EmployeeCount INT;
DECLARE @DepartmentName NVARCHAR(50);

-- назначение
SET @DepartmentName = 'ИТ-отдел';

--Присвоение значений из результатов запроса
ВЫБЕРИТЕ @EmployeeCount = COUNT(*)
ОТ сотрудников
ГДЕ Отдел = @DepartmentName;

-- Вывод результатов
ПЕЧАТЬ @EmployeeCount;

Управление процессом ЕСЛИ...иначе

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

ОБЪЯВИТЬ @StockLevel INT;
НАБОР @StockLevel = 10;

ЕСЛИ @StockLevel < 5
НАЧАТЬ
    ПЕЧАТЬ 'Запасы очень малы, пожалуйста, пополните запасы немедленно. ';
КОНЕЦ
ИНАЧЕ, ЕСЛИ @StockLevel < 20
НАЧАТЬ
    ПЕЧАТЬ 'Запасы недостаточны, рекомендуется пополнить запасы. ';
КОНЕЦ
ЕЩЕ
НАЧАТЬ
    ПЕЧАТЬ 'В наличии. ';
КОНЕЦ

Обработка ошибок TRY...CATCH

Механизм TRY...CATCH, предоставляемый T-SQL, аналогичен современным языкам программирования (таким как C# или Java) и может перехватывать исключения во время выполнения, чтобы предотвратить нештатное прерывание программы.

НАЧАТЬ ПОПРОБОВАТЬ
    -- Код, который нужно попытаться выполнить
    INSERT INTO Sales (OrderID, ProductID, Quantity)
    ЗНАЧЕНИЯ (1001, «P01», -5); -- Предположим, что это вызовет ошибку ограничения
КОНЕЦ ПОПРОБУЙТЕ
НАЧАТЬ ЛОВИТЬ
    -- Обработка ошибок
    ВЫБРАТЬ
        ERROR_NUMBER() КАК Номер ошибки,
        ERROR_MESSAGE() КАК Сообщение об ошибке,
        ERROR_SEVERITY() КАК Серьезность;
        
    --Откат транзакции
    ЕСЛИ @@TRANCOUNT > 0
        ОТКАТ ТРАНЗАКЦИИ;
КОНЦЕВАЯ ЗАЩЕЛКА

Используйте имя вместо поля идентификатора

Применимые условия

• техническая спецификацияnameЗначения полей уникальны.
• nameДлина поля умеренная, чтобы она не была слишком длинной и не влияла на производительность.
• Приложения работают в основном с именами, а не с числовыми идентификаторами.

Преимущества

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

потенциальные проблемы

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

Применимые примеры

Например, таблица данных типа профиля:

CREATE TABLE config_types (
  name VARCHAR(50) PRIMARY KEY,
  description TEXT
);

INSERT INTO config_types (name, description) VALUES ('general', 'General settings');
SELECT * FROM config_types WHERE name = 'general';

Объекты наследования проекта реляционной базы данных

Проектирование структуры базы данных

форма животного

В этой таблице будут храниться общие атрибуты всех «животных».

кошачий стол

Эта таблица унаследует идентификатор таблицы животных и сохранит уникальные атрибуты «кошки».

SQL-команда создания таблицы

CREATE TABLE animal (
    id INT PRIMARY KEY AUTO_INCREMENT,
    species VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

CREATE TABLE cat (
    id INT PRIMARY KEY,
    breed VARCHAR(50),
    favorite_food VARCHAR(50),
    FOREIGN KEY (id) REFERENCES animal(id)
);
    

Пример вставки данных

INSERT INTO animal (species, age) VALUES ('Cat', 3);

INSERT INTO cat (id, breed, favorite_food) VALUES (1, 'Siamese', 'Fish');
    

Пример отображения HTML-таблицы

информация о животных

Специальная информация о кошках

иллюстрировать

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

Запрос информации обо всех животных

SELECT * FROM animal;
    

Запрос информации обо всех кошках

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

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id;
    

Запросить информацию о конкретном животном (например, конкретный идентификатор)

SELECT * FROM animal WHERE id = 1;
    

Запрос информации о конкретной кошке (например, об определенной породе)

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id
WHERE cat.breed = 'Siamese';
    

иллюстрировать

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

FOREIGN KEY

использовать

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

грамматика

Подтаблица CREATE TABLE (
  Имя поля Тип данных,
  FOREIGN KEY (поле внешнего ключа) REFERENCES родительская таблица данных (поле первичного ключа)
);

пример

Установите связь «один ко многим», например заказы и клиенты:

-- Создать родительскую таблицу данных (клиенты)
СОЗДАТЬ ТАБЛИЦУ клиентов (
  customer_id INT ПЕРВИЧНЫЙ КЛЮЧ,
  имя ВАРЧАР(50)
);

--Создать подтаблицы (заказы) и установить внешние ключи
СОЗДАТЬ ТАБЛИЦУ заказов (
  order_id INT ПЕРВИЧНЫЙ КЛЮЧ,
  customer_id INT,
  дата_заказа ДАТА,
  ВНЕШНИЙ КЛЮЧ (customer_id) ССЫЛКА на клиентов (customer_id)
);

На что следует обратить внимание

• Ссылочное поле в родительской таблице данных должно бытьPRIMARY KEYилиUNIQUE。
• При вставке или обновлении данных необходимо соблюдать ограничения внешнего ключа.
• При удалении данных из родительской таблицы ограничения внешнего ключа могут вызывать ошибки, если они не установлены.ON DELETEПоведение.

Расширенное использование

может пройти черезON DELETEиON UPDATEУкажите поведение внешнего ключа:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);

Варианты поведения

• CASCADE: синхронно удалить или обновить связанные данные в таблице подданных.
• SET NULL: установите для поля внешнего ключа в таблице подданных значениеNULL。
• RESTRICT: Запретить удаление или обновление, поведение по умолчанию.
• NO ACTION:иRESTRICTАналогично, отложите проверку ограничений.

Добавить комментарии к СОЗДАТЬ ТАБЛИЦУ

Используйте КОММЕНТАРИЙ, чтобы установить комментарии к полям.

В MySQL вы можете использоватьCOMMENTдля добавления примечаний к полям.

СОЗДАТЬ ТАБЛИЦУ пользователей (
  id INT PRIMARY KEY COMMENT 'Уникальный идентификационный код пользователя',
  name VARCHAR(50) КОММЕНТАРИЙ 'Имя пользователя',
  age INT COMMENT 'Возраст пользователя'
);

Используйте COMMENT для установки комментариев к таблице.

СОЗДАТЬ ТАБЛИЦУ пользователей (
  идентификатор INT ПЕРВИЧНЫЙ КЛЮЧ,
  имя ВАРЧАР(50),
  возрастINT
) COMMENT = 'Таблица информации о пользователе';

Просмотр примечаний к полям

Комментарии к полю можно запросить с помощью следующего синтаксиса:

SHOW FULL COLUMNS FROM users;

Изменить примечания к полям

Пользователи ALTER TABLE MODIFY COLUMN name VARCHAR(50) COMMENT 'Измененные примечания';

Область применения

• Поддержка MySQLCOMMENT, но SQLite его не поддерживает.
• Другие системы баз данных (например, PostgreSQL) часто используютCOMMENT ONинструкция.

Примечания к настройкам PostgreSQL

КОММЕНТАРИЙ К СТОЛБЦУ user.name ЯВЛЯЕТСЯ «имя пользователя»;

В именах столбцов SQL используются зарезервированные слова

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

1. Фундаментальное решение: избегать использования зарезервированных слов (проектирование базы данных).

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

• Переименовать: измените имя поля на неконфликтующее имя. Например, изменитьintervalИзменить наtime_interval,ВоляrankИзменить наuser_rank。
• Проверьте официальный список зарезервированных слов. На этапе проектирования проверьте официальную документацию целевой базы данных (например, MySQL, PostgreSQL, SQL Server), чтобы убедиться, что выбранное вами имя отсутствует в списке зарезервированных слов.

2. Техническое решение: Заключить идентификационные символы в кавычки (реализация программного кода).

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

3. Автоматизация программного кода (динамическая генерация SQL)

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

• Определите набор зарезервированных слов. Создайте набор, содержащий все зарезервированные слова, использование которых необходимо предотвратить.
• Обработка списка полей: напишите функцию, проверяющую, является ли имя поля зарезервированным словом. Если да, то имя заключено в обратные кавычки целевой базы данных.
• Обработка условий WHERE. При обработке предложения WHERE необходимо использовать регулярные выражения (с границами слов).\b), чтобы точно соответствовать именам полей и избежать неправильной замены строковых значений или длинных имен.

4. Преимущества платформы ORM

Если вы используете ORM (например, SQLAlchemy или Django ORM), платформа автоматически обрабатывает различия в зарезервированных словах и кавычках между разными базами данных, абстрагируя базовый вывод SQL, что значительно упрощает работу по разработке и повышает стабильность кода.

SQL-сравнение DATETIME

Используйте TIMESTAMPDIFF

Вычисляет разницу во времени в секундах между двумя полями DATETIME.

SELECT * FROM table_name
WHERE TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2) < 5;

Используйте ABS(TIMESTAMPDIFF)

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

SELECT * FROM table_name
WHERE ABS(TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2)) < 5;

Используйте DATEDIFF (только для сравнения дней)

Если вы просто хотите сравнить, совпадают ли они в один и тот же день, вы можете использовать DATEDIFF.

SELECT * FROM table_name
WHERE DATEDIFF(datetime_column1, datetime_column2) = 0;

Вычитание напрямую с помощью TIMESTAMP

Применимо к базам данных, поддерживающим операции с метками времени, например MySQL.

SELECT * FROM table_name
WHERE ABS(UNIX_TIMESTAMP(datetime_column1) - UNIX_TIMESTAMP(datetime_column2)) < 5;

MySQL обрабатывает миллисекундные метки времени

Указанное вами значение1763251200000Стандартная **метка времени в миллисекундах** (время эпохи Unix в миллисекундах).

Встроенные функции MySQL (например,FROM_UNIXTIME) Обработкой по умолчанию является **временная метка второго уровня**. Поэтому перед использованием вам необходимо преобразовать миллисекундные метки времени в секунды.

Исправление: разделить на 1000.

Разделите значение миллисекунды на $1000$, чтобы получить временную метку второго уровня, необходимую для функции MySQL:

1763251200000 / 1000 = 1763251200

1. Преобразование в формат DATETIME.

использоватьFROM_UNIXTIME()Функция преобразует временную метку второго уровня в стандартную MySQLDATETIMEФормат:

SELECT FROM_UNIXTIME(1763251200000 / 1000);

Если ваша версия MySQL поддерживает миллисекунды и вы хотите сохранить точность результатов в миллисекундах, вы можете использовать второй параметр:

SELECT FROM_UNIXTIME(1763251200000 / 1000, '%Y-%m-%d %H:%i:%s.%f');

в:

• %Y-%m-%d %H:%i:%sСтандартный формат даты и времени.
• %fЗахватывает микросекунды, поэтому вам нужно передать оставшиеся миллисекунды в виде дроби.

2. Преобразовать в UNIX_TIMESTAMP (секунды).

Если вы намерены сохранить значение миллисекунды в целом числе (например,INTилиBIGINT) в качестве временной метки второго уровня, то требуется только простая операция деления:

SELECT 1763251200000 / 1000;

Или, если ваши данные хранятся в полях таблицы (например, поле называетсяtimestamp_ms）：

SELECT timestamp_ms / 1000 AS unix_timestamp_s FROM your_table;

3. Сохраните в поле DATETIME/TIMESTAMP.

Когда вы вставляете это значение во что-то, что имеетDATETIMEилиTIMESTAMPполе типа, необходимо ввестиVALUESЧастично выполнить преобразование:

INSERT INTO your_table (datetime_column) 
VALUES (FROM_UNIXTIME(1763251200000 / 1000));

4. Обработка часового пояса UTC

FROM_UNIXTIME()Функция преобразует временную метку UTC в часовой пояс, настроенный для вашего сервера MySQL. Если ваша временная метка основана на формате UTC и вы хотите, чтобы результат также был в формате UTC, вам необходимо убедиться, что часовой пояс вашего сервера или соединения установлен правильно, или используйтеCONVERT_TZФункция выполняет явную обработку часового пояса.

Формат С плавающей запятой

Используйте ФОРМАТ().

В MySQL используйтеFORMAT()для форматирования чисел с плавающей запятой.

ВЫБРАТЬ ФОРМАТ(123.4567, 2); -- Результат: '123,46'

Используйте ОКРУГЛ()

ROUND()Используется для округления до фиксированного количества десятичных знаков.

ВЫБРАТЬ КРУГЛЫЙ(123.4567, 2); -- Результат: 123,46

Используйте CAST() или CONVERT().

Преобразуйте FLOAT в DECIMAL, чтобы сохранить фиксированное количество десятичных знаков.

SELECT CAST(123.4567 AS DECIMAL(10,2)); -- Результат: 123,46
ВЫБРАТЬ CONVERT(123.4567, DECIMAL(10,2)); -- Результат: 123,46

Применить к полям таблицы данных

SELECT id, FORMAT(price, 2) AS formatted_price FROM products;

Форматирование полей

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

СОЗДАТЬ ТАБЛИЦУ продуктов (
  идентификатор INT ПЕРВИЧНЫЙ КЛЮЧ,
  цена DECIMAL(10,2) -- два знака после запятой
);

Итого по полю запроса

Используйте МАКС()

Получите максимальное значение поля.

SELECT MAX(price) AS max_price FROM products;

Используйте МИН()

Получите минимальное значение поля.

SELECT MIN(price) AS min_price FROM products;

Используйте АВГ()

Вычислите среднее значение полей.

SELECT AVG(price) AS avg_price FROM products;

Запрос MAX, MIN, AVG одновременно

SELECT 
  MAX(price) AS max_price, 
  MIN(price) AS min_price, 
  AVG(price) AS avg_price
FROM products;

Рассчитывается по категориям

SELECT category, 
       MAX(price) AS max_price, 
       MIN(price) AS min_price, 
       AVG(price) AS avg_price
FROM products
GROUP BY category;

Используйте подзапрос, чтобы найти максимальное значение

Найдите максимальное значение другого результата запроса.

SELECT MAX(price) FROM (SELECT price FROM products WHERE category = 'electronics') AS subquery;

Используйте ORDER BY + LIMIT.

Получите максимальное значение после сортировки.

SELECT price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 1;

Используйте С (Общее табличное выражение, CTE)

WITH filtered_products AS (
  SELECT price FROM products WHERE category = 'electronics'
)
SELECT MAX(price) FROM filtered_products;

формула среднего геометрического

Формула расчета среднего геометрического:

GM = (x1 * x2 * ... * xn)^(1/n)

Использование EXP() и LOG()

В SQL среднее геометрическое можно вычислить с помощью логарифмических операций.

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

Используйте СИЛУ()

использоватьPOWER()Вычислите корень n-й степени:

SELECT POWER(EXP(SUM(LOG(price))), 1 / COUNT(price)) AS geometric_mean 
FROM products WHERE price > 0;

На что следует обратить внимание

• Цена (или стоимость) не может быть отрицательной или нулевой, иначеLOG()вызовет ошибки.
• Если данные содержат нули, их можно сначала отфильтровать:

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

SQL вычисляет стандартное отклонение поля

Вычислите стандартное отклонение, используя STDDEV().

В MySQL/MariaDB вы можете использоватьSTDDEV()для расчета стандартного отклонения.

пример:

SELECT STDDEV(salary) AS salary_stddev FROM employees;

Различие между родительским стандартным отклонением и стандартным отклонением выборки.

SQL предоставляет два метода расчета стандартного отклонения:

• STDDEV_POP()- Рассчитать стандартное отклонение населения (Population Standard Deviation)
• STDDEV_SAMP()- Рассчитать стандартное отклонение выборки (Sample Standard Deviation)

пример:

SELECT 
    STDDEV_POP(salary) AS population_stddev, 
    STDDEV_SAMP(salary) AS sample_stddev
FROM employees;

Рассчитать стандартное отклонение вручную

Если версия SQL не поддерживаетSTDDEV(), вы можете использовать следующую формулу:

SELECT SQRT(
    SUM(POW(salary - (SELECT AVG(salary) FROM employees), 2)) / COUNT(salary)
) AS salary_stddev
FROM employees;

в заключение

• использоватьSTDDEV()Стандартное отклонение можно рассчитать напрямую.
• ДоступныйSTDDEV_POP()Рассчитайте родительское стандартное отклонение,STDDEV_SAMP()Рассчитайте выборочное стандартное отклонение.
• Если встроенные функции не поддерживаются, для вычислений вручную можно использовать формулы SQL.

Предложение SQL WHERE

SQLWHEREпредложение используется для извлечения из таблицыИзвлечь записи, соответствующие заданным условиям(столбец/строка). Это одна из самых основных и важных частей языка манипулирования данными (DML), используемая для точной фильтрации необходимых данных.

Роль и цель

• Фильтрация на уровне строк: WHEREЭто предложение оценивается для каждой строки (Row), когда база данных читает таблицу. Только когда данные в определенной строке удовлетворяютWHEREСтрока будет включена в набор результатов только в том случае, если выполнены следующие условия.
• Область применения:его можно использовать сSELECT, UPDATE, DELETEИспользуйте его с командами SQL.

базовая грамматика

WHEREобычно следует пунктFROMПосле пункта:

SELECT column_name(s)
FROM table_name
WHERE condition;

или когда используется для изменения или удаления информации:

UPDATE table_name
SET column1 = value1
WHERE condition;

Часто используемые операции сравнения и логические операторы

WHEREОператоры используются в предложениях для установления условий. Наиболее распространенными из них являются:

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

Предположим, есть файл с именемEmployeesстол, в том числеEmployeeID, LastName(фамилия),Salary(зарплата) иDepartment(отделение).

Пример 1: Единственное условие

Найти всех сотрудников с отделом «Продажи»:

SELECT EmployeeID, LastName
FROM Employees
WHERE Department = 'Sales';

Пример 2. Числовое сравнение и логическое И.

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

SELECT *
FROM Employees
WHERE Salary >= 50000 AND Department != 'HR';

Пример 3: Диапазон МЕЖДУ

Найдите сотрудников с зарплатой от 60 000 до 80 000 (включительно):

SELECT *
FROM Employees
WHERE Salary BETWEEN 60000 AND 80000;

Пример 4: Список IN

Найдите сотрудников в отделе «Маркетинг» или «Финансы»:

SELECT *
FROM Employees
WHERE Department IN ('Marketing', 'Finance');

Это эквивалентно использованиюWHERE Department = 'Marketing' OR Department = 'Finance'。

Предложение SQL GROUP BY

SQLGROUP BYпункт используется дляSELECTВ операторе строки с одинаковым значением (или комбинацией значений для нескольких полей) группируются в сводные столбцы. Он часто используется с SQLсводная функцияИспользуется вместе для расчета итогового значения для каждой группы.

Роль и цель

• Информация о группе: GROUP BYОсновная функция — объединить несколько отдельных строк с одинаковым значением в таблице в одну группу (Group).
• Создать сводку:с помощьюCOUNT()、SUM()、AVG()、MAX()илиMIN()В сочетании с итоговыми функциями вы можете рассчитывать сводную статистику для каждой группы, а не для всей таблицы.

базовая грамматика

GROUP BYпункт должен быть вWHEREпункт, но послеHAVINGиORDER BYперед пунктом.

ВЫБЕРИТЕ имя_столбца(ов), агрегатная_функция(столбец_для_суммаризации)
ИЗ имя_таблицы
WHERE условие_on_rows — (необязательно) фильтровать отдельные строки перед группировкой
GROUP BY имя_столбца(ов) — указывает столбец, используемый для группировки.
HAVINGcondition_on_groups -- (необязательно) фильтровать группы после группировки
ORDER BY имя_столбца(ов);

Важные правила:все, что появляется вSELECTв списке, ноНетСтолбцы, окруженные сводными функциями,долженвключен вGROUP BYв п.

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

Предположим, существует файл с именемProductsстол, в том числеCategory(категория продукта) иPrice(цена).

Спрос: Найдите среднюю цену и количество продукции для каждой категории продукции.

SELECT Category, AVG(Price) AS AveragePrice, COUNT(ProductID) AS TotalProducts
FROM Products
GROUP BY Category;

Пример описания

• GROUP BY Category: база данных директив будет иметь одно и то жеCategoryГруппировка строк значений.
• AVG(Price): вычислить среднее значение всех цен на продукты в каждой группе (каждой категории).
• COUNT(ProductID): подсчитать общее количество товаров в каждой группе (каждой категории).

Группировка по нескольким столбцам

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

Спрос: Найдите общий объем продаж для каждой комбинации категории продуктов (Категория) и поставщика (Поставщик).

SELECT Category, Supplier, SUM(SalesAmount) AS TotalSales
FROM SalesRecords
GROUP BY Category, Supplier
ORDER BY Category, Supplier;

В результате каждая строка будет представлять общий объем продаж для уникальной комбинации «Категория + Поставщик».

Часто используемые сводные функции

Предложение SQL HAVING

В SQL,HAVINGПредложение используется для фильтрации результатов группы (Group), обычно оно используется сGROUP BYпредложения используются вместе.

ХотяWHEREПредложение используется для фильтрации строк одного столбца (Rows), но полезно, когда требуются сводные значения на основе групп (например,COUNT(), SUM(), AVG()При фильтрации групп путем ожидания результата итоговой функции) необходимо использоватьHAVINGпункт.

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

базовая грамматика

ВЫБЕРИТЕ имя_столбца(ов), агрегатная_функция(имя_столбца)
ИЗ имя_таблицы
WHERE условие_on_rows -- (необязательно) фильтровать отдельные строки
ГРУППИРОВАТЬ ПО имя_столбца(ов)
HAVINGcondition_on_groups -- (обязательные) группы фильтров
ORDER BY имя_столбца(ов);

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

Предположим, есть файл с именемOrdersстол, в том числеCustomerID(идентификатор клиента) иTotalAmount(общая сумма заказа). мы хотим узнать всеСредняя сумма заказа превышает 500клиентов.

SQL-запрос

SELECT CustomerID, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING AVG(TotalAmount) > 500;

Пример описания

• GROUP BY CustomerID: Группировать все заказы по идентификатору клиента.
• AVG(TotalAmount): расчет средней суммы заказа на одного клиента.
• HAVING AVG(TotalAmount) > 500: Это состояние фильтра. Он сообщает SQL отображать только средние суммы заказов (т. е.AVG(TotalAmount)результаты) для групп (клиентов) более 500**.

Пример сложного условия

Предположим, мы хотим найти всех клиентов, общее количество заказов которых составляет **3** и средняя сумма заказов которых меньше 1000**.

SQL-запрос

SELECT CustomerID, COUNT(OrderID) AS TotalOrders, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) >= 3 AND AVG(TotalAmount) < 1000;

Пример описания

• HAVING COUNT(OrderID) >= 3: отфильтровать группы клиентов, в общей сложности менее 3 заказов.
• AND AVG(TotalAmount) < 1000: также отфильтровать группы клиентов, средняя сумма заказа которых превышает или равна 1000.

Ссылочный псевдоним SQL с использованием подзапроса

Описание проблемы

существоватьSELECTВ запросе, если определеноexpr1 AS field1, может ли это быть вexpr2используется вfield1？

Ответ: Нет, из-за порядка выполнения SQL-запросов.

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

ВЫБОР цены * 1.1 AS новая_цена, новая_цена + 10 AS окончательная_цена ИЗ продуктов; -- Ошибка

сообщение об ошибке:

Unknown column 'new_price' in 'field list'

Решение

Способ 1: используйте подзапрос

можно вычислить сначала в подзапросеnew_price, а затем упоминается во внешнем запросе:

SELECT new_price, new_price + 10 AS final_price
FROM (SELECT price * 1.1 AS new_price FROM products) AS subquery;

Способ 2: использование CTE (оператор WITH)

Доступно, если SQL поддерживает общие табличные выражения (CTE).WITHЧтобы упростить:

WITH cte AS (
    SELECT price * 1.1 AS new_price FROM products
)
SELECT new_price, new_price + 10 AS final_price FROM cte;

Способ 3: повторите выражение

Если это простая операция, вы можете повторить расчет напрямую (но это не рекомендуется, поскольку читаемость плохая):

SELECT price * 1.1 AS new_price, price * 1.1 + 10 AS final_price FROM products;

в заключение

• SQL не может быть в одном и том жеSELECTСсылка на псевдоним в запросе.
• Вы можете использовать подзапросы или CTE (WITH) решить.
• Для простых операций расчет можно повторить напрямую.

JOIN

ПРИСОЕДИНЯЙТЕСЬ Введение

JOIN используется для объединения связанных данных из нескольких таблиц данных и установления связи на основе определенного поля (обычно внешнего ключа).

INNER JOIN

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

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN

Возвращает все данные в левой таблице. Если в правой таблице соответствующих данных нет, они будут отображены.NULL。

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

Возвращает все данные в правой таблице. Если в левой таблице нет соответствующих данных, они будут отображены.NULL。

SELECT customers.name, orders.order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

FULL JOIN

Возвращает все данные в левой и правой таблицах и отображает их, если совпадений нет.NULL。

MySQL не поддерживает FULL JOIN, доступноLEFT JOINиRIGHT JOINКомбинированное моделирование.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

CROSS JOIN

Возвращает все возможные комбинации (декартово произведение) двух таблиц.

SELECT customers.name, products.product_name
FROM customers
CROSS JOIN products;

SELF JOIN

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

SELECT A.name AS employee, B.name AS manager
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

Включить другой .sql при выполнении .sql

Используйте директиву SOURCE или \i

MySQL или МарияДБ

-- abc.sql
SOURCE other_file.sql;
SOURCE another_file.sql;

PostgreSQL (psql)

-- abc.sql
\i other_file.sql
\i another_file.sql

Выполнить с помощью пакетного скрипта

Пример оболочки Linux

#!/bin/bash
mysql -u user -p database_name < abc.sql
mysql -u user -p database_name < other_file.sql

Расширенный способ: предварительная обработка файлов SQL

Объединение файлов с помощью Shell Script

cat abc.sql other_file.sql another_file.sql > combined.sql
mysql -u user -p database_name < combined.sql

На что следует обратить внимание

Подвести итог

Передача параметров при включении других файлов .sql

MySQL и МарияДБ

Передача параметров с использованием переменных

   SET @param1 = 'value1';
   SOURCE other_file.sql;
   

   SELECT * FROM table WHERE column = @param1;
   

PostgreSQL (psql)

Передача параметров с использованием переменных

   \set param1 'value1'
   \i other_file.sql
   

   SELECT * FROM table WHERE column = :'param1';
   

Передача параметров с помощью инструментов командной строки

Пример командной строки MySQL

   sed "s/{param1}/value1/g" abc.sql | mysql -u user -p database_name
   

Пример командной строки PostgreSQL

   psql -d database_name -v param1=value1 -f abc.sql
   

Генерация SQL с использованием кода

На что следует обратить внимание

Подвести итог

хранимая процедура

Введение в хранимые процедуры (предварительно сохраненные процедуры)

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

Создать хранимую процедуру

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
  SELECT * FROM products;
END //
DELIMITER ;

вызвать хранимую процедуру

CALL GetAllProducts();

хранимая процедура с параметрами

входные параметры

Поиск товаров в определенной категории:

DELIMITER //
CREATE PROCEDURE GetProductsByCategory(IN category_name VARCHAR(50))
BEGIN
  SELECT * FROM products WHERE category = category_name;
END //
DELIMITER ;

вызов:

CALL GetProductsByCategory('electronics');

Выходные параметры

Подсчитаем общее количество товаров в категории:

DELIMITER //
CREATE PROCEDURE GetProductCountByCategory(IN category_name VARCHAR(50), OUT total_count INT)
BEGIN
  SELECT COUNT(*) INTO total_count FROM products WHERE category = category_name;
END //
DELIMITER ;

вызов:

CALL GetProductCountByCategory('electronics', @count);
SELECT @count;

Сохраняйте переменные для использования в программах.

DELIMITER //
CREATE PROCEDURE CalculateTotalRevenue()
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(price) INTO total FROM sales;
  SELECT total AS total_revenue;
END //
DELIMITER ;

вызов:

CALL CalculateTotalRevenue();

Используйте условное управление в хранимых процедурах

ЕСЛИ условие

DELIMITER //
CREATE PROCEDURE CheckStock(IN product_id INT, OUT stock_status VARCHAR(20))
BEGIN
  DECLARE stock INT;
  SELECT quantity INTO stock FROM inventory WHERE id = product_id;

  IF stock > 10 THEN
    SET stock_status = 'In Stock';
  ELSEIF stock > 0 THEN
    SET stock_status = 'Low Stock';
  ELSE
    SET stock_status = 'Out of Stock';
  END IF;
END //
DELIMITER ;

вызов:

CALL CheckStock(1, @status);
SELECT @status;

Использование LOOP в сохраненной программе

DELIMITER //
CREATE PROCEDURE CountDown(IN start_num INT)
BEGIN
  DECLARE i INT;
  SET i = start_num;

  loop_label: LOOP
    IF i <= 0 THEN
      LEAVE loop_label;
    END IF;
    SELECT i;
    SET i = i - 1;
  END LOOP;
END //
DELIMITER ;

вызов:

CALL CountDown(5);

Удалить сохраненную программу

DROP PROCEDURE IF EXISTS GetAllProducts;

в заключение

• Хранимые процедуры повышают эффективность SQL и инкапсулируют бизнес-логику.
• Параметры можно использовать для передачи значений, поддерживаются условное управление и циклы.
• использоватьDELIMITERИзбегайте конфликтов синтаксиса SQL.

Значения параметров по умолчанию для хранимой процедуры

MySQL/MariaDB не поддерживает прямую установку значений параметров по умолчанию.

В MySQL и MariaDB параметрам хранимых процедур нельзя напрямую установить значения по умолчанию (в отличие от SQL Server или PostgreSQL). Однако можно использоватьIFУсловные операторы для имитации заданных значений.

Способ 1. Используйте условия ЕСЛИ для установки значений по умолчанию.

Предположим, мы хотим запроситьusersтаблица, когда параметрыuser_idЕсли он не указан, идентификатор запроса по умолчанию равен 1:

РАЗДЕЛИТЕЛЬ //
СОЗДАТЬ ПРОЦЕДУРУ GetUserById (IN user_id INT)
НАЧАТЬ
    ЕСЛИ user_id НУЛЬ, ТО
        УСТАНОВИТЬ user_id = 1; --Значение по умолчанию
    КОНЕЦ ЕСЛИ;

    ВЫБЕРИТЕ * ИЗ пользователей ГДЕ id = user_id;
КОНЕЦ //
РАЗДЕЛИТЕЛЬ;

Метод исполнения

ВЫЗОВ GetUserById(NULL); -- запросит id = 1
ВЫЗОВ GetUserById(5); -- Идентификатор запроса = 5

Способ 2. Используйте COALESCE() для установки значений по умолчанию.

COALESCE()Вернет указанное значение по умолчанию, если параметр равен NULL:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = COALESCE(user_id, 1);
END //
DELIMITER ;

Метод исполнения

ВЫЗОВ GetUserById(NULL); -- По умолчанию – 1.
ВЫЗОВ GetUserById(10); -- Идентификатор запроса = 10

Способ 3. Использование необязательных параметров (обходной путь)

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

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Метод исполнения

ВЫЗОВ GetAllUsers(); -- нет параметров, запросить все
ВЫЗОВ GetUserById(3); -- Идентификатор запроса = 3

в заключение

• MySQL и MariaDB не поддерживают настройку параметров напрямую.DEFAULTценить.
• может пройти черезIFилиCOALESCE()Аналоговые настройки по умолчанию.
• Если параметр не является обязательным, рекомендуется создать другую хранимую процедуру.

Возвращаемое значение хранимой процедуры

Способ 1. Используйте параметр OUT для возврата значения.

Хранимая процедура MySQL/MariaDB не поддерживается.RETURNВозвращает результаты запроса, но может быть использованоOUTПараметры передают обратно значения.

DELIMITER //
CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END //
DELIMITER ;

Вызов хранимой процедуры и получение возвращаемого значения

ВЫЗОВ GetUserCount(@total);
ВЫБЕРИТЕ @всего; -- Отображение количества пользователей

Способ 2. Используйте SELECT для возврата набора результатов.

Если вы хотите вернуть результат запроса напрямуюSELECTВот и все:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Вызов хранимой процедуры

ВЫЗОВ GetUserById(5); -- Запрос пользователя с идентификатором 5

Способ 3. Используйте RETURN для возврата одного значения.

Хотя MySQL поддерживаетRETURN, но может возвращать только одно значение, обычно используемое для управления процессом:

РАЗДЕЛИТЕЛЬ //
СОЗДАТЬ ПРОЦЕДУРУ GetMaxSalary()
НАЧАТЬ
    ОБЪЯВИТЬ max_salary DECIMAL(10,2);
    ВЫБЕРИТЕ MAX(зарплата) INTO max_salary FROM сотрудников;
    ВОЗВРАТ максимальная_зарплата; -- Но это не вернет значение непосредственно в MySQL.
КОНЕЦ //
РАЗДЕЛИТЕЛЬ;

MySQL не может напрямуюCALLчтобы получить значение RETURN, поэтому рекомендуется использовать параметр OUT:

DELIMITER //
CREATE PROCEDURE GetMaxSalary(OUT max_salary DECIMAL(10,2))
BEGIN
    SELECT MAX(salary) INTO max_salary FROM employees;
END //
DELIMITER ;

ПОЗВОНИТЕ GetMaxSalary(@max);
ВЫБЕРИТЕ @макс; -- Отображение максимальной зарплаты

Способ 4: вернуть несколько значений

Используйте несколькоOUTПараметры возвращают разные результаты расчета:

DELIMITER //
CREATE PROCEDURE GetUserStats(OUT total_users INT, OUT avg_age DECIMAL(5,2))
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT AVG(age) INTO avg_age FROM users;
END //
DELIMITER ;

Вызов хранимой процедуры и получение нескольких возвращаемых значений

ВЫЗОВ GetUserStats(@total, @avg);
ВЫБЕРИТЕ @total, @avg; -- Отображение общего количества и среднего возраста пользователей

в заключение

• Если вы возвращаете листовку с одним значением, рекомендуется использоватьOUTпараметр.
• Если возвращается набор результатов запроса, используйте его напрямую.SELECT。
• Если возвращается несколько значений, используйте несколькоOUTпараметр.
• MySQL не поддерживаетRETURNВозврат результатов запроса применим только для управления процессом.

Использование возвращаемых значений вне хранимых процедур CALL

Способ 1. Используйте параметры OUT и сохраните их в переменных.

Доступ к хранимой процедуре MySQL можно получить черезOUTПараметры возвращают значения, которые затем можно использовать вне CALL.SELECTПолучите это значение.

Создать хранимую процедуру

DELIMITER //
CREATE PROCEDURE GetTotalUsers(OUT total_users INT)
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
END //
DELIMITER ;

Вызовите хранимую процедуру и используйте возвращаемое значение.

ВЫЗОВ GetTotalUsers(@total);
ВЫБЕРИТЕ @total AS UserCount; -- Использовать возвращаемое значение вне CALL

Способ 2. Используйте переменные для хранения возвращаемых результатов запроса.

Если хранимая процедура используетSELECTВозвращаемые результаты не могут быть напрямую сохранены в переменных, но их можно использовать.INSERT INTO ... SELECT。

Создать хранимую процедуру

DELIMITER //
CREATE PROCEDURE GetMaxSalary()
BEGIN
    SELECT MAX(salary) AS max_salary FROM employees;
END //
DELIMITER ;

Использование переменных для доступа к результатам запроса

СОЗДАТЬ ВРЕМЕННУЮ ТАБЛИЦУ temp_result (max_salary DECIMAL (10,2));

INSERT INTO temp_result ВЫПОЛНИТЬ GetMaxSalary();

ВЫБЕРИТЕ max_salary ИЗ temp_result; -- используется вне CALL

Способ 3. Используйте подготовленный оператор для динамического доступа к результатам

Если к результатам, сгенерированным хранимой процедурой, необходимо получить доступ в переменных, вы можете использоватьPREPAREиEXECUTE。

Создать хранимую процедуру

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT name FROM users WHERE id = user_id;
END //
DELIMITER ;

Вызов и сохранение переменных

SET @sql = 'CALL GetUserById(5)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

в заключение

• использоватьOUTПараметр возвращает одно значение в листовке, переданное@переменнаядоступ.
• Если возвращаются результаты запроса, доступныINSERT INTO ... CALLХраните в промежуточной таблице.
• Если вам нужен динамический доступ к результатам, вы можете использоватьPREPAREиEXECUTE。

Обратный вызов функции MySQL

Описание проблемы

В MySQL/MariaDB сохраненная функция (функция) не может быть возвращенаSELECTНабор результатов, иначе произойдет ошибка:

ERROR 1415 (0A000): Not allowed to return a result set from a function

Решение

Способ 1: вместо этого используйте хранимую процедуру

Функции не могут возвращать наборы результатов, а хранимые процедуры — могут.

Неправильная функция:

РАЗДЕЛИТЕЛЬ //
СОЗДАТЬ ФУНКЦИЮ GetUsers()
ТАБЛИЦА ВОЗВРАТА
НАЧАТЬ
    ВОЗВРАТ (ВЫБРАТЬ * ОТ пользователей); -- Это запрещено
КОНЕЦ //
РАЗДЕЛИТЕЛЬ;

Правильная хранимая процедура:

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Вызов хранимой процедуры:

CALL GetUsers();

Способ 2. Используйте функцию для возврата одного значения.

Если вам нужно вернуть только одно значение (например, количество или максимальное значение), вы можете использоватьRETURN。

DELIMITER //
CREATE FUNCTION GetUserCount()
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM users;
    RETURN total;
END //
DELIMITER ;

Используйте функцию:

SELECT GetUserCount();

Способ 3: использовать временную таблицу

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

DELIMITER //
CREATE FUNCTION PopulateTempUsers()
RETURNS INT DETERMINISTIC
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_users AS (SELECT * FROM users);
    RETURN 1;
END //
DELIMITER ;

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

SELECT PopulateTempUsers();
SELECT * FROM temp_users;

в заключение

• MySQL/MariaDB не позволяет функциям возвращать наборы результатов, вместо этого следует использовать хранимую процедуру.
• Если вам нужно вернуть только одно значение, вы можете использовать его внутри функции.RETURN。
• Если вам нужно вернуть несколько строк результатов, вы можете использовать временную таблицу для хранения и запроса позже.

HeidiSQL

Введение

HeidiSQL — это бесплатный SQL-клиент с открытым исходным кодом, поддерживающий MySQL, MariaDB, PostgreSQL и MS SQL Server. Он предоставляет графический интерфейс для управления базами данных, выполнения SQL-запросов, импорта/экспорта данных и т. д.

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

• Управление базами данных, таблицами, индексами, представлениями, триггерами
• Выполнение SQL-запросов и поддержка редактирования с несколькими вкладками.
• Структура таблицы визуальных данных и диаграмма ER
• Редактируйте данные в пакетном режиме
• Импорт/экспорт CSV, SQL, XML, JSON и других форматов.
• Управление разрешениями пользователей

Загрузите и установите

1. Перейдите на официальный сайт, чтобы скачать HeidiSQL:https://www.heidisql.com/download.php2. Запустите программу установки и следуйте инструкциям для завершения установки.
3. Откройте HeidiSQL и настройте новое соединение.

Подключиться к MySQL/MariaDB

1. Запустите HeidiSQL
2. Нажмите «Добавить», чтобы создать новое соединение.
3. Настройки:
   - Имя хоста/IP: 127.0.0.1 или IP удаленного сервера.
   - Имя пользователя: root или другой пользователь
   - Пароль: соответствующий пароль
   - Порт: 3306 (MySQL/MariaDB)
4. Нажмите «Открыть», чтобы подключиться к базе данных.

Выполнить SQL-запрос

Введите оператор SQL в окне запроса HeidiSQL:

SELECT * FROM users WHERE status = 'active';

Нажмите кнопку «Выполнить», чтобы просмотреть результаты.

Импорт/экспорт данных

Экспортировать SQL

1. Щелкните базу данных правой кнопкой мыши → выберите «Экспортировать SQL».
2. Выберите таблицу данных для экспорта.
3. Установите формат экспорта (.sql, .csv, .json).
4. Нажмите «Экспортировать».

Импортировать SQL

1. Откройте HeidiSQL и выберите целевую базу данных.
2. Нажмите «Инструменты» → «Выполнить SQL-файл».
3. Выберите файл .sql и выполните

Управление разрешениями пользователей

1. Войдите в «Инструменты» → «Управление разрешениями пользователей».
2. Выберите пользователей для управления
3. Установите права доступа к базе данных (SELECT, INSERT, UPDATE, DELETE и т. д.).
4. Нажмите «Сохранить».

в заключение

• HeidiSQL — легкий, но мощный SQL-клиент.
• Подходит для разработчиков и администраторов баз данных для управления MySQL, MariaDB и PostgreSQL.
• Обеспечьте функции визуального управления для повышения эффективности.

HeidiSQL добавляет новые хранимые процедуры

Шаг 1. Подключитесь к базе данных

1. Запустите HeidiSQL.
2. Подключитесь к серверу MySQL или MariaDB.
3. Выберите целевую базу данных в списке База данных слева.

Шаг 2. Создайте новую хранимую процедуру.

1. Щелкните правой кнопкой мыши имя базы данных слева и выберите «Создать новую» → «Сохранить программу».
2. HeidiSQL откроет новое окно редактирования SQL и предоставит шаблон хранимой процедуры по умолчанию.

Шаг 3. Напишите хранимую процедуру

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

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Шаг 4. Выполните хранимую процедуру

1. Нажмите кнопку «Перейти» (зеленая молния).
2. Если выполнение прошло успешно, программу можно будет найти в столбце «Сохранить программу» слева.

Шаг 5. Проверьте хранимую процедуру

CALL GetAllUsers();

Дополнительно: хранимые процедуры с параметрами

Передайте параметры для фильтрации данных, например запроса на основе идентификатора пользователя:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Вызов хранимой процедуры с параметрами:

CALL GetUserById(1);

Удалить сохраненную программу

DROP PROCEDURE IF EXISTS GetAllUsers;

в заключение

• HeidiSQL предоставляет графический интерфейс для управления хранимыми процедурами.
• использоватьDELIMITERУбедитесь, что синтаксис правильный.
• Можно использоватьCALLТестируйте хранимые процедуры.

Решение для разработки приложений баз данных

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

Решение для интеграции искусственного интеллекта и архитектуры RAG

С ростом популярности больших языковых моделей (LLM) стандартом разработки стала генерация с расширенным поиском (RAG). Этот тип решения ориентирован на векторизацию и поиск неструктурированных данных.

• Используйте PostgreSQL с расширением pgvector для обработки традиционных реляционных данных и векторных данных AI в одной базе данных.
• Используйте специализированные базы данных векторов, такие как Pinecone или Weaviate, чтобы добиться максимальной эффективности поиска по сходству.
• Интегрируйте такие платформы, как LangChain или LlamaIndex, чтобы создать автоматизированный конвейер от базы данных к модели искусственного интеллекта.

Бессерверные и облачные решения

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

• Supabase: предоставляет серверную часть как услугу (BaaS) на основе PostgreSQL, включая аутентификацию и мгновенную отправку данных.
• PlanetScale: решение MySQL на основе Vitess, которое поддерживает миграцию схем и горизонтальное расширение.
• Neon: PostgreSQL разработан специально для бессерверных приложений и поддерживает функцию ветвления базы данных для облегчения разработки и тестирования.

Комплексное решение для разработки типовой безопасности

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

• Используя Prisma или Drizzle ORM, разработчики могут получить полное завершение кода IDE и проверку ошибок при написании синтаксиса запроса с помощью автоматически генерируемых определений типов.
• В сочетании с архитектурой стека T3 tRPC используется для достижения сквозной синхронизации типов от уровня базы данных до внешнего интерфейса.

Решения для распределенных и периферийных баз данных

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

• Turso: основан на libSQL (ветвь SQLite), специально разработанной для сред периферийных вычислений, с чрезвычайно быстрым временем холодного запуска.
• Облако TiDB: распределенная база данных SQL, подходящая для сценариев смешанной нагрузки (HTAP), требующих строгой согласованности и непредсказуемого роста данных.

Сравнительная таблица планов развития

Внешний интерфейс приложения базы данных

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

Экосистема React и мета-фреймворки

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

• Next.js: на данный момент самая популярная среда React, поддерживает серверные компоненты. Он может напрямую определять логику взаимодействия с базой данных во внешнем коде, сокращая разработку уровня API.
• TanStack Query (React Query): основной инструмент для обработки асинхронного состояния базы данных с мощным механизмом кэширования, функциями автоматической повторной выборки и обработки страниц.
• Ремикс: особое внимание уделяется веб-стандартам и оптимизации загрузки данных, а также упрощается взаимодействие между внешними формами и базами данных с помощью механизмов загрузчика и действий.

Экосистемное решение Vue

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

• Nuxt.js: Полноценная платформа Vue предоставляет возможности разработки, аналогичные Next.js. Он имеет мощную модульную систему и может быстро интегрировать базу данных ORM.
• Pinia: современная библиотека управления состоянием, используемая для синхронизации межстраничных данных во внешнем интерфейсе.
• Element Plus/Ant Design Vue: зрелая библиотека компонентов пользовательского интерфейса с большим количеством встроенных компонентов таблиц и форм, предназначенных для операций CRUD базы данных.

Инструменты безопасности типов и компонентов пользовательского интерфейса

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

• TypeScript: входит в стандартную комплектацию и обеспечивает согласованность типов данных — от полей базы данных до отображения во внешнем интерфейсе.
• Tailwind CSS: универсальное решение, позволяющее быстро создавать адаптивные информационные панели данных с использованием концепций, ориентированных на служебные программы.
• Пользовательский интерфейс Shadcn: набор компонентов на основе пользовательского интерфейса Radix и Tailwind. В настоящее время он очень популярен в сообществе Next.js. Программный код полностью прозрачен и легко настраивается.

Разработка инструментов с низким кодом и собственными силами

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

• Appsmith/Retool: вы можете подключиться к PostgreSQL, MySQL или MongoDB, перетаскивая компоненты, чтобы быстро генерировать данные для анализа и обслуживания.

Сравнительная таблица выбора интерфейсных решений

Веб-интерфейс базы данных

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

1. Универсальный инструмент управления

Инструменты этого типа поддерживают несколько подключений к базам данных (например, MySQL, PostgreSQL, SQL Server) и подходят для разработчиков, управляющих различными средами.

• CloudBeaver:Веб-версия с открытым исходным кодом, основанная на движке DBeaver, является мощной и поддерживает широкий спектр драйверов баз данных.
• Adminer:Минималистичный единый инструмент управления файлами PHP, чрезвычайно быстрый в развертывании и поддерживающий несколько баз данных SQL.
• OmniDB:Инструмент управления браузером, предназначенный для совместной работы, с элементами управления безопасностью и интерактивной консолью SQL.

2. Интерфейс управления для конкретной базы данных.

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

• pgAdmin 4 (PostgreSQL):Официальный первый выбор, его можно запускать в режиме сервера для общего доступа команд.
• phpMyAdmin (MySQL/MariaDB):Самый старый и популярный инструмент управления веб-страницами.
• Redis Insight (Web):Обеспечивает визуализацию ключевых значений, анализ памяти и мониторинг потоковых данных в реальном времени.
• MongoDB Atlas:Встроенный мощный браузер данных, редактор конвейера агрегации и инструменты анализа производительности.

3. Интерфейс Low-code и электронных таблиц.

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

• NocoDB:Альтернатива Airtable с открытым исходным кодом, которая может напрямую подключаться к существующим базам данных и генерировать API.
• Retool / Appsmith:Платформа с низким кодом для быстрого создания индивидуального пользовательского интерфейса базы данных с помощью перетаскивания компонентов.
• Budibase:Внутренняя платформа разработки инструментов, ориентированная на быстрое создание приложений CRUD.

4. Собственный интерфейс облачной платформы.

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

• AWS RDS Query Editor:Выполняйте команды SQL непосредственно из Консоли управления AWS.
• Google BigQuery Console:Мощная веб-IDE, поддерживающая обработку огромных объемов данных и имеющая встроенные функции поддержки искусственного интеллекта.
• Azure SQL Editor:Упрощенный инструмент запросов, встроенный в портал Azure.

CloudBeaver

CloudBeaver — это веб-решение для управления базами данных с открытым исходным кодом, разработанное командой DBeaver. Он использует серверную часть Java и интерфейсную архитектуру React, что позволяет пользователям безопасно управлять различными базами данных через браузер, что очень подходит для сценариев, требующих удаленного доступа или совместной работы в команде.

Основные функциональные особенности

• Кроссплатформенный веб-доступ:Нет необходимости устанавливать какое-либо настольное программное обеспечение на персональный компьютер, и оно поддерживает прямую работу с основными браузерами.
• Обширная поддержка баз данных:Поддерживает MySQL, PostgreSQL, MariaDB, SQLite, Oracle, SQL Server, ClickHouse и другие базы данных SQL через драйвер JDBC.
• Мощный редактор SQL:Обеспечивает подсветку синтаксиса, автозаполнение, форматирование и функции истории запросов.
• Редактор визуальных данных:Поддерживает прямое редактирование данных в виде таблицы, фильтрацию полей, сортировку и экспорт в несколько форматов (например, CSV, JSON, XLSX).
• Контроль безопасности и разрешений:Поддерживает многопользовательский режим, администраторы могут централизованно настраивать подключения к базе данных и назначать их конкретным командам или участникам.
• Дружественный докер:Предоставляются официальные образы Docker, которые можно быстро развернуть в частных облаках или внутренних серверах.

Различия между Community Edition и Enterprise Edition

CloudBeaver предоставляет Community Edition с открытым исходным кодом (Community) и коммерческую версию Enterprise Edition (Enterprise). Основное отличие заключается в расширенной поддержке функций:

Применимые сценарии

• Работа в команде:После того как администраторы настраивают соединение с базой данных, разработчики могут просто войти в браузер и начать работу, не разглашая конфиденциальные ключи базы данных.
• Ограниченная среда:Это особенно полезно в средах, где настольное программное обеспечение невозможно установить или где необходимо проникнуть на хост-бастион для обслуживания базы данных.
• Легкое обслуживание:Подходит для мониторинга состояния данных, выполнения сценариев временных исправлений или выполнения простых операций по экспорту данных.

Способ установки CloudBeaver:

Наиболее рекомендуемый метод установки CloudBeaver — использовать Docker, поскольку он уже содержит все необходимые среды и драйверы Java. Существует три основных пути развертывания:

1. Используйте Docker для быстрого запуска

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

docker run --name cloudbeaver -d -p 8978:8978 dbeaver/cloudbeaver:latest

После выполнения откройте ввод в браузереhttp://localhost:8978Вы можете войти в мастер настройки.

2. Используйте Docker Compose (рекомендуется для производственной среды).

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

version: '3'
services:
  cloudbeaver:
    image: dbeaver/cloudbeaver:latest
    container_name: cloudbeaver
    restart: unless-stopped
    ports:
      - "8978:8978"
    volumes:
      - ./cloudbeaver-data:/opt/cloudbeaver/workspace

Сохраните приведенный выше контент какdocker-compose.yml, а затем выполнитьdocker-compose up -d。

3. Автономная установка исполняемого файла (Standalone)

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

• Экологические требования:Сначала требуется установить Java JRE 17 или новее.
• Шаги загрузки:
  1. Перейдите на страницу выпуска CloudBeaver GitHub, чтобы загрузить файл ресурса, соответствующий операционной системе (например,cloudbeaver-ce-latest-linux-x86_64.tar.gz）。
  2. Разархивируйте архив:tar -xvf cloudbeaver-ce-latest-linux-x86_64.tar.gz。
  3. Выполните скрипт запуска:./run-server.sh(Linux/macOS) илиrun-server.bat (Windows)。

Первоначальные настройки после установки

При первом входе в веб-интерфейс система проведет вас через следующую настройку:

• Server Configuration：Задайте имя службы и порт.
• Administration：Создайте учетную запись администратора и пароль.
• Database Drivers：Подтвердите, какой драйвер базы данных необходимо включить (система автоматически обрабатывает наиболее распространенные драйверы).

email: [email protected]

T:0000

資訊與搜尋 | 回dev首頁
email: Yan Sa [email protected] Line: 阿央
電話: 02-27566655 ,03-5924828

阿央
泱泱科技
捷昱科技泱泱企業