Команды MySQL в консоли: от входа до бэкапа

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

Как войти в консоль MySQL

Консоль – это клиент mysql. Он подключается к серверу и отправляет ему ваши запросы. Синтаксис сверен с документацией MySQL 8.4 LTS. Oracle перевёл ветку 8.0 на Sustaining Support, поэтому для новой установки берите 8.4 или новее.

1. Откройте терминал и введите mysql -u root -p. Флаг -u задаёт пользователя, -p просит пароль.
2. Введите пароль на запрос клиента. Не пишите его прямо в команде: он останется в истории оболочки.
3. Проверьте вход: в начале строки появилось приглашение mysql>. Если сервер ответил отказом, сверьте имя пользователя и пароль.

mysql -u root -p

К удалённому серверу подключайтесь с флагами -h (хост) и -P (порт, заглавная буква). Первый аргумент без флага клиент считает базой по умолчанию. Так вы попадёте сразу в нужную базу:

mysql -h db.example.com -P 3306 -u app -p company

Каждый SQL-запрос заканчивайте точкой с запятой. Так вы говорите клиенту, что запрос готов к отправке. Без неё консоль ждёт продолжения ввода, ведь запрос может занимать несколько строк.

Ожидание видно на экране: вместо mysql> появилось приглашение ->. Введите ; на новой строке, и запрос уйдёт. Передумали – введите \c: клиент сбросит набранное и ничего не отправит.

mysql> SELECT VERSION()
    -> ;

Встроенная справка открывается командой \h. Выйти из консоли можно тремя способами: exit, quit или \q.

Осмотр сервера: команды SHOW

Команды SHOW ничего не меняют. Они отвечают на вопрос «что здесь есть». Начните со списка баз:

SHOW DATABASES;

Состав списка зависит от установки и от ваших прав. Без глобальной привилегии SHOW DATABASES команда покажет только базы, на которые у вас есть какие-либо права. Среди них бывают служебные базы, например mysql. Их не удаляйте и не правьте.

Ваши базы появятся в этом списке рядом: строку company вы увидите после следующего раздела.

Остальные команды осмотра сведены в таблицу. Примеры с таблицей employees заработают после раздела «Таблицы: создать и изменить», где вы её создадите.

Команда SHOW VARIABLES пригодится в разделе «Агрегатные функции и группировка». В её ответе для sql_mode ищите режим ONLY_FULL_GROUP_BY. Шаблон работает как в LIKE: 'character%' выведет все переменные кодировок.

Базы данных: создать, выбрать, удалить

База – контейнер для связанных таблиц. Заводите отдельную базу под каждый проект: права и бэкапы тогда настраиваются на неё одну. Создайте учебную базу и сделайте её текущей:

CREATE DATABASE company CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE company;

Проверьте результат: SHOW DATABASES; покажет строку company, а консоль ответит Database changed. Создание базы не делает её текущей, поэтому USE нужен отдельно. Какая база текущая, скажет SELECT DATABASE();.

Кодировку задавайте явно. В MySQL 8.4 умолчание сервера уже utf8mb4 с сортировкой utf8mb4_0900_ai_ci. Явная запись защищает при переносе базы на другой сервер, где умолчания иные.

Не берите кодировку utf8. В MySQL это устаревший псевдоним utf8mb3, и её удалят в одном из будущих мажорных релизов. Она хранит до трёх байт на символ, а utf8mb4 – до четырёх. Четвёртый байт нужен символам вне базовой плоскости Юникода, например эмодзи.

Текущая база – только умолчание, а не ограничение. К таблице другой базы обращайтесь через точку: SELECT * FROM company.projects; сработает из любой базы. Изменить кодировку существующей базы можно так:

ALTER DATABASE company CHARACTER SET utf8mb4;

Удаляет базу DROP DATABASE company;. Команда сносит все таблицы и данные сразу и без вопросов.

Перед ней снимите дамп (см. раздел «Бэкап и восстановление») и перечитайте имя базы в самой команде: удаляется та база, которую вы назвали, а не текущая. Вернуть удалённое можно только из бэкапа.

Таблицы: создать и изменить

Учебная схема состоит из проектов и сотрудников. Каждый сотрудник может работать над одним проектом, поэтому таблица employees ссылается на projects внешним ключом. Сначала создайте родительскую таблицу, потом дочернюю:

CREATE TABLE projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  started DATE NOT NULL
);

CREATE TABLE employees (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  hired DATE,
  salary DECIMAL(10,2) DEFAULT NULL,
  project_id INT UNSIGNED,
  FOREIGN KEY (project_id) REFERENCES projects (id)
);

Проверьте результат командами SHOW TABLES; и DESCRIBE employees;:

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| projects          |
+-------------------+

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int unsigned  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)   | NO   |     | NULL    |                |
| hired      | date          | YES  |     | NULL    |                |
| salary     | decimal(10,2) | YES  |     | NULL    |                |
| project_id | int unsigned  | YES  | MUL | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Типы в схеме выбраны под задачу. NOT NULL запрещает пустое значение, DEFAULT подставляет значение, когда вы поле не указали. AUTO_INCREMENT выдаёт каждой строке следующий номер сам.

Деньги храните в DECIMAL, а не в дробных типах: он не теряет копейки на округлении.

Менять таблицу помогает ALTER TABLE. Пройдите цепочку до конца, и схема вернётся к исходной. Добавьте колонку, измените её тип, переименуйте и удалите:

ALTER TABLE employees ADD COLUMN grade VARCHAR(20) NOT NULL DEFAULT 'junior';
ALTER TABLE employees MODIFY COLUMN grade VARCHAR(30) NOT NULL DEFAULT 'junior';
ALTER TABLE employees RENAME COLUMN grade TO level;
ALTER TABLE employees DROP COLUMN level;

Есть и CHANGE COLUMN: он задаёт новое имя и полное определение колонки одной командой. Проверяйте каждый шаг через DESCRIBE employees;. Таблицу переименовывает RENAME TABLE:

RENAME TABLE employees TO staff;
RENAME TABLE staff TO employees;

Индекс ускоряет поиск по колонке. Создайте индекс по дате найма, посмотрите его и удалите:

CREATE INDEX idx_employees_hired ON employees (hired);
SHOW INDEX FROM employees;
DROP INDEX idx_employees_hired ON employees;

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

CREATE TABLE tmp_log (id INT PRIMARY KEY, msg VARCHAR(50));
TRUNCATE TABLE tmp_log;
DROP TABLE tmp_log;

TRUNCATE TABLE очищает таблицу, а DROP TABLE убирает её вместе со структурой. Чем они отличаются от DELETE, разобрано в разделе «Изменение данных: INSERT, UPDATE, DELETE».

Выборка данных: SELECT

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

INSERT INTO projects (title, started) VALUES
  ('Сайт магазина', '2025-03-01'),
  ('Мобильное приложение', '2025-09-15'),
  ('Складской учёт', '2026-01-10');

INSERT INTO employees (name, hired, salary, project_id) VALUES
  ('Анна Орлова', '2022-04-11', 95000.00, 1),
  ('Борис Ким', '2023-02-01', 82000.00, 1),
  ('Вера Лунина', '2021-09-20', 120000.00, 2),
  ('Глеб Седов', '2024-06-03', 70000.00, 2),
  ('Дарья Мохова', '2020-01-15', 135000.00, 3),
  ('Егор Волков', '2024-11-01', 65000.00, NULL);

Консоль ответит Query OK, 6 rows affected для сотрудников. Теперь выведите всё: SELECT * FROM employees;. Звёздочка означает «все колонки». Для рабочих запросов перечисляйте нужные поля: ответ короче, а схему можно менять без последствий.

+----+--------------+------------+-----------+------------+
| id | name         | hired      | salary    | project_id |
+----+--------------+------------+-----------+------------+
|  1 | Анна Орлова  | 2022-04-11 |  95000.00 |          1 |
|  2 | Борис Ким    | 2023-02-01 |  82000.00 |          1 |
|  3 | Вера Лунина  | 2021-09-20 | 120000.00 |          2 |
|  4 | Глеб Седов   | 2024-06-03 |  70000.00 |          2 |
|  5 | Дарья Мохова | 2020-01-15 | 135000.00 |          3 |
|  6 | Егор Волков  | 2024-11-01 |  65000.00 |       NULL |
+----+--------------+------------+-----------+------------+

Условия задаёт WHERE: операторы =, <>, >, BETWEEN, IN и IS NULL. Сортирует ORDER BY: ASC по возрастанию, DESC по убыванию. Найдите тех, кого приняли до 2023 года:

SELECT name, hired FROM employees WHERE hired < '2023-01-01' ORDER BY hired;

+--------------+------------+
| name         | hired      |
+--------------+------------+
| Дарья Мохова | 2020-01-15 |
| Вера Лунина  | 2021-09-20 |
| Анна Орлова  | 2022-04-11 |
+--------------+------------+

Диапазон задаёт BETWEEN: границы входят в результат. Выберите зарплаты от 80 000 до 100 000, начиная с большей:

SELECT name, salary FROM employees
WHERE salary BETWEEN 80000 AND 100000
ORDER BY salary DESC;

+-------------+----------+
| name        | salary   |
+-------------+----------+
| Анна Орлова | 95000.00 |
| Борис Ким   | 82000.00 |
+-------------+----------+

Пустое значение сравнивайте только через IS NULL, а не через =. Запрос SELECT name FROM employees WHERE project_id IS NULL; вернёт одну строку: Егор Волков без проекта. Оператор IN заменяет цепочку OR: WHERE project_id IN (1, 3) берёт сотрудников проектов 1 и 3.

DISTINCT убирает повторы. Запрос SELECT DISTINCT project_id FROM employees WHERE project_id IS NOT NULL ORDER BY project_id; вернёт три строки: 1, 2 и 3, хотя в таблице пять записей с проектом.

Для лишних строк есть LIMIT. Он берёт первые N строк, а OFFSET пропускает столько строк, сколько вы укажете. Возьмите двух самых высокооплачиваемых:

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 2;

+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Дарья Мохова | 135000.00 |
| Вера Лунина  | 120000.00 |
+--------------+-----------+

Запрос с LIMIT 2 OFFSET 2 пропустит этих двоих и вернёт следующих: Анну и Бориса. Так постраничный вывод и работает.

Шаблон LIKE знает два знака: _ заменяет один символ, % – любое число символов. Найдите фамилии на «-ова»:

SELECT name FROM employees WHERE name LIKE '%ова' ORDER BY name;

+--------------+
| name         |
+--------------+
| Анна Орлова  |
| Дарья Мохова |
+--------------+

Шаблон 'Б_рис%' найдёт «Борис Ким»: подчёркивание встало на место «о».

Сложные шаблоны пишите через REGEXP. В MySQL 8 его обеспечивает библиотека ICU, и в ней же есть функция REGEXP_LIKE(). Регистр учитывает сортировка колонки: для сортировок с окончанием _ci он не важен. Найдите имена, начинающиеся на «А», «Б» или «В»:

SELECT name FROM employees WHERE name REGEXP '^[АБВ]' ORDER BY name;

+-------------+
| name        |
+-------------+
| Анна Орлова |
| Борис Ким   |
| Вера Лунина |
+-------------+

С версии 8.0 в MySQL есть выражения WITH (CTE) и оконные функции. Для типовых задач администратора хватает того, что показано выше.

Агрегатные функции и группировка

Агрегатная функция сворачивает много строк в одно значение. Пять главных собраны в таблице. Результаты посчитаны на учебных данных:

Пробел между именем функции и скобкой – частая ловушка новичка. В MySQL по умолчанию между именем встроенной функции и открывающей скобкой не должно быть пробела, и запрос SELECT COUNT (*) FROM t; с пробелом после COUNT завершается ошибкой синтаксиса. Правило действует и для SUM, MIN, MAX. Пишите COUNT(*) слитно.

Режим IGNORE_SPACE это требование снимает, но запрос с пробелом перестанет работать на сервере без него. Соберите три значения в одном запросе:

SELECT COUNT(*) AS staff, ROUND(AVG(salary), 2) AS avg_salary, MAX(salary) AS top_salary
FROM employees;

+-------+------------+------------+
| staff | avg_salary | top_salary |
+-------+------------+------------+
|     6 |   94500.00 |  135000.00 |
+-------+------------+------------+

GROUP BY делит строки на группы и считает агрегат в каждой. Посчитайте людей и сумму зарплат по проектам:

SELECT project_id, COUNT(*) AS people, SUM(salary) AS payroll
FROM employees
WHERE project_id IS NOT NULL
GROUP BY project_id
ORDER BY project_id;

+------------+--------+-----------+
| project_id | people | payroll   |
+------------+--------+-----------+
|          1 |      2 | 177000.00 |
|          2 |      2 | 190000.00 |
|          3 |      1 | 135000.00 |
+------------+--------+-----------+

Группы отбирает HAVING. Он работает после группировки, а WHERE – до неё. Оставьте проекты, где занято больше одного человека:

SELECT project_id, COUNT(*) AS people
FROM employees
WHERE project_id IS NOT NULL
GROUP BY project_id
HAVING COUNT(*) > 1;

Запрос вернёт проекты 1 и 2 с двумя сотрудниками в каждом. Проект 3 отсеян, потому что в нём один человек.

Зачем нужен GROUP BY, показывает такая ошибка. Агрегат и обычное поле в одном запросе без группировки сервер отклоняет:

SELECT name, COUNT(*) FROM employees;

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'company.employees.name'; this is incompatible with sql_mode=only_full_group_by

Причина в режиме ONLY_FULL_GROUP_BY. В MySQL 8.4 он входит в умолчание вместе с STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO и NO_ENGINE_SUBSTITUTION. Сервер не знает, какое из шести имён показать рядом с одним числом.

Не отключайте режим, чтобы заглушить ошибку: вы получите случайное имя вместо честного ответа. Добавьте поле в GROUP BY или уберите его из запроса.

Объединение таблиц: JOIN

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

SELECT e.name, p.title
FROM employees AS e
INNER JOIN projects AS p ON p.id = e.project_id
ORDER BY e.id;

+--------------+----------------------+
| name         | title                |
+--------------+----------------------+
| Анна Орлова  | Сайт магазина        |
| Борис Ким    | Сайт магазина        |
| Вера Лунина  | Мобильное приложение |
| Глеб Седов   | Мобильное приложение |
| Дарья Мохова | Складской учёт       |
+--------------+----------------------+

INNER JOIN оставляет только пары с совпадением. Егора Волкова в ответе нет: у него нет проекта. Чтобы сохранить всех сотрудников, замените слово на LEFT JOIN. Он берёт все строки левой таблицы и подставляет NULL, где пары нет:

SELECT e.name, p.title
FROM employees AS e
LEFT JOIN projects AS p ON p.id = e.project_id
ORDER BY e.id;

+--------------+----------------------+
| name         | title                |
+--------------+----------------------+
| Анна Орлова  | Сайт магазина        |
| Борис Ким    | Сайт магазина        |
| Вера Лунина  | Мобильное приложение |
| Глеб Седов   | Мобильное приложение |
| Дарья Мохова | Складской учёт       |
| Егор Волков  | NULL                 |
+--------------+----------------------+

Шестая строка – ответ на вопрос «кто остался без проекта». Старая запись через запятую в FROM тоже работает, но устарела. Пишите JOIN … ON: условие связи там отделено от условий отбора.

Изменение данных: INSERT, UPDATE, DELETE

Вставку в часть полей вы уже видели у INSERT: перечислите колонки, и остальные получат значение по умолчанию. У колонок без DEFAULT и без NOT NULL это NULL. Добавьте сотрудницу без зарплаты и проекта:

INSERT INTO employees (name, hired) VALUES ('Жанна Крылова', '2025-02-17');
SELECT * FROM employees WHERE name = 'Жанна Крылова';

+----+---------------+------------+--------+------------+
| id | name          | hired      | salary | project_id |
+----+---------------+------------+--------+------------+
|  7 | Жанна Крылова | 2025-02-17 |   NULL |       NULL |
+----+---------------+------------+--------+------------+

Данные меняет UPDATE. Присваивания перечисляйте через запятую, а условие ставьте в WHERE. Сначала прочтите предупреждение, потом выполняйте запрос.

UPDATE и DELETE без WHERE меняют или стирают всю таблицу, и консоль не спросит подтверждения. Сначала выполните SELECT с тем же условием и убедитесь, что строки те. Для одной таблицы добавьте LIMIT: он не даст задеть больше строк, чем вы ждёте.

Назначьте Жанне зарплату и третий проект:

SELECT * FROM employees WHERE id = 7;
UPDATE employees SET salary = 72000.00, project_id = 3 WHERE id = 7;

Консоль ответит Rows matched: 1 Changed: 1 Warnings: 0. Так вы проверяете, что изменилась одна строка, а не вся таблица. Удаляет строки DELETE. Подстраховка та же: сначала SELECT с тем же условием, затем удаление с LIMIT:

SELECT * FROM employees WHERE id = 7;
DELETE FROM employees WHERE id = 7 LIMIT 1;

Ответ Query OK, 1 row affected подтверждает удаление одной строки. Учебные данные вернулись к исходным шести сотрудникам.

Три команды убирают данные по-разному, и путать их дорого.

TRUNCATE быстрее DELETE на больших таблицах: он пересоздаёт таблицу, а не удаляет строки по одной. Но для таблицы, на которую ссылается внешний ключ другой таблицы, он не сработает в InnoDB.

Пользователи и права

Не работайте в приложениях под root. Утечка одного пароля тогда отдаёт злоумышленнику весь сервер. Заведите отдельного пользователя и дайте ему права только на нужную базу. Сначала создайте пользователя, затем выдавайте права:

CREATE USER 'report'@'localhost' IDENTIFIED BY 'Str0ng-pass';
GRANT SELECT ON company.* TO 'report'@'localhost';
SHOW GRANTS FOR 'report'@'localhost';

+-----------------------------------------------------+
| Grants for report@localhost                         |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `report`@`localhost`          |
| GRANT SELECT ON `company`.* TO `report`@`localhost` |
+-----------------------------------------------------+

Имя состоит из логина и хоста: 'report'@'localhost' и 'report'@'%' – разные учётные записи. Запись company.* означает все таблицы базы company. Строка USAGE говорит, что глобальных прав у пользователя нет. Проверьте вход: mysql -u report -p company и запрос SELECT * FROM employees; должны сработать, а INSERT – нет.

Несколько прав перечисляйте через запятую: GRANT SELECT, INSERT ON company.* TO 'report'@'localhost';. Форма ALL PRIVILEGES даёт все права на базу, кроме GRANT OPTION. Приложению она обычно не нужна: чем меньше прав, тем меньше вреда от взлома. Отозвать право можно так:

REVOKE INSERT ON company.* FROM 'report'@'localhost';

Пароль меняет ALTER USER. Удаляет учётную запись DROP USER:

ALTER USER 'report'@'localhost' IDENTIFIED BY 'New-Str0ng-pass';
DROP USER 'report'@'localhost';

DROP USER не закрывает уже открытые сессии пользователя: удаление вступает в силу после их закрытия. Формы CREATE USER IF NOT EXISTS и DROP USER IF EXISTS заменяют ошибку предупреждением. Их удобно ставить в скрипты.

Команда FLUSH PRIVILEGES после этих запросов не нужна. Сервер сам перечитывает права после GRANT, REVOKE и других команд управления учётными записями. Она нужна лишь тогда, когда вы правите таблицы привилегий напрямую через INSERT, UPDATE или DELETE. Так делать не рекомендуют.

Новые учётные записи в MySQL 8.4 получают плагин аутентификации caching_sha2_password. Плагин mysql_native_password в 8.4 отключён по умолчанию, а в 9.0 удалён. Если старая программа не подключается, обновите её клиентскую библиотеку до поддержки caching_sha2_password.

Бэкап и восстановление

Дамп – это текстовый файл с запросами, которые воссоздают базу. Его снимает утилита mysqldump. Запускайте её из обычного терминала, а не из консоли mysql>. Дамп одной базы:

mysqldump -u root -p company > company.sql

Проверьте дамп сразу. На Linux и macOS размер файла покажет ls -l company.sql, на Windows – dir company.sql. Нулевой размер значит, что дамп не снялся.

Опция --databases добавляет в дамп запросы CREATE DATABASE и USE. Имена нескольких баз пишутся после неё через пробел. Опция --all-databases снимает все базы сервера сразу:

mysqldump -u root -p --databases company > company_with_create.sql
mysqldump -u root -p --all-databases > all_bases.sql

Процедуры и события в дамп добавляют опции --routines и --events, триггеры – --triggers. Если такие объекты есть в вашей базе, укажите все три явно:

mysqldump -u root -p --routines --events --triggers company > company_full.sql

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

CREATE DATABASE company CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

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

mysql -u root -p company < company.sql

Второй способ – изнутри консоли. Выберите базу, затем команда SOURCE прочитает файл и выполнит запросы из него:

USE company;
SOURCE /home/user/company.sql

На Windows пишите в пути прямые косые: C:/backups/company.sql. Дамп с опцией --databases или --all-databases базу создаёт сам, поэтому команда mysql -u root -p < company_with_create.sql обходится без имени базы.

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

Шпаргалка по командам MySQL

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

Полное описание синтаксиса ищите в официальной документации MySQL 8.4. Там же описаны опции mysqldump и все привилегии для GRANT.

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