Mariadb команды консоли linux
Обновлено: 06.07.2024
В этой статье я покажу, как создать базу данных (также известную как schema, схема), таблицы (с типами данных) и объясню, как выполнять операции языка управления данными (Data Manipulation Language (DML)) на MySQL / MariaDB сервере.
Подразумевается, что вы уже установили MySQL или MariaDB сервер.
Для краткости я буду говорить MariaDB, но все концепции и команды из этой статьи также применимы к MySQL.
Создание баз данных, таблиц и авторизированных пользователей
Под базой данных понимается набор организованной информации. MariaDB является системой управления базами данных (СУБД). Для создания, модификации и управления данными используется язык структурированных запросов, по-английски structured query language, т.е. SQL. Именно основы этого языка мы и будем осваивать в этой статье. Чтобы не путаться, помните, что MariaDB использует термины «база данных» и «схема» как взаимозаменяемые.
Для хранения постоянной информации в базе данных, мы будем использовать таблицы, в которых данные сохраняются в строках. Таблицы могут быть связаны друг с другом.
Подключение к базе данных
Запросы к базе данных можно передавать из большинства языков программирования, например, из PHP, а также возможно напрямую. Для непосредственного подключения к СУБД MariaDB введите:
После появления запроса, введите пароль, приглашение оболочки Bash смениться на приглашение MariaDB:
Именно сюда мы будет вводить последующие команды.
Создание новой базы данных
Для создание новой базы данных с название BooksDB, введите в приглашение командной строки MariaDB следующую команду:
После создания базы данных, нам нужно создать в ней пару таблиц. Но перед этим давайте ознакомимся с типами данных.
Введение в типы данных MariaDB
Как сказано ранее, таблицы – это объекты базы данных, где мы будет хранить постоянную информацию. Каждая таблица состоит из колонок. Колонка содержит данные определённого типа.
Самыми распространёнными типами данных в MariaDB являются следующие
Число:
- BOOLEAN значение 0 считается false (т.е. ложь), а любые другие данные расцениваются как true (т.е. истина).
- TINYINT (tiny integer, т.е. буквально крошечное целое число), можно использовать как SIGNED (т.е. со знаком), тогда этим типом охватываются числа от -128 до 127, также можно использовать как UNSIGNED (т.е. без знака), тогда в охватываемый диапазон входят целые числа от 0 до 255.
- SMALLINT (small integer, т.е. буквально маленькие целые числа), опять же, если используется с SIGNED, то этим типом охватывается диапазон от -32768 до 32767. Диапазон UNSIGNED от 0 до 65535.
- MEDIUMINT (medium integer, т.е. буквально средние целые числа, с SIGNED это от -8388608 до 8388607. Без знака это диапазон от 0 до 16777215.
- INT (integer, буквально целое число), если используется с SIGNED, охватывает диапазон от -2147483648 до 2147483647, и от 0 до 4294967295 в противном случае.
- BIGINT (big integer, т.е. буквально большое целое число), со знаком это диапазон от -9223372036854775808 до 9223372036854775807. Без знака от 0 до 18446744073709551615.
Помните: В TINYINT, SMALLINT, MEDIUMINT, INT и BIGINT, SIGNED предполагается по умолчанию.
- DOUBLE(M, D), где M – это общее количество цифр, а D – это количество цифр после десятичной точки, представляет собой числа с двойной точностью с плавающей запятой. Если указана UNSIGNED, отрицательные значения не разрешены.
Строка:
- VARCHAR(M) представляет строку переменной длины, где M – это максимально разрешённая длина колонки в байтах (65,535 в теории). В большинстве случае количество байтов идентично количеству символов, кроме символов, которым может потребоваться до 3 байтов (utf8). Например, испанская буква ñ представляет собой один символ, но использует 2 байта.
- TINYTEXT – это текстовые значения, с максимальной длиной 255 символов. Эффективная максимальная длина меньше, если значение содержит многобайтную кодировку.
- TEXT(M) представляет колонку с максимальной длиной в 65,535 символов. Тем не менее, как и с VARCHAR(M), реальная максимальная длина уменьшается при сохранении многобайтных символов. Если M указана, создаётся самая маленькая колонка типа TEXT, которая достаточно большая для хранения значения длиной в M символов.
- MEDIUMTEXT(M) и LONGTEXT(M) похожи на TEXT(M), разница только в максимально разрешённой длине значения, которая составляет, соответственно 16,777,215 и 4,294,967,295.
Дата и время:
- DATE представляет дату в формате YYYY-MM-DD.
- TIME представляет время в формате HH:MM:SS.sss (чисы, минуты, секунды и милисекунды).
- DATETIME является комбинацией DATE и TIME в формате YYYY-MM-DD HH:MM:SS.
- TIMESTAMP используется для определения момента, когда строка была добавлена или обновлена.
Теперь, когда вы бегло ознакомились с типами данных, вам будет проще определить, данные какого типа назначить заданной колонке в таблице.
Создание таблицы с Primary и Foreign ключами
Перед тем как всё-таки перейдём к созданию таблицы, нужно ознакомиться с двумя фундаментальными концепциями реляционных баз данных, это primary и foreign ключи.
Ключ primary (главный) содержит значение, которое индивидуально определяет каждый ряд или запись в таблице. А foreign (буквально «внешний») используется для создания связи между данными в двух таблицах, и контролирования данных, которые могут сохраняться в таблице, где размещён foreign ключ. Обычно для primary и foreign ключей выбирают тип INT.
Для иллюстрации, давайте воспользуемся BookstoreDB и создадим две таблицы с именами AuthorsTBL и BooksTBL как показано ниже. Константа NOT NULL означает, что данное поле требует значение и не может быть NULL (не заданным).
Также AUTO_INCREMENT используется для автоматического увеличения на один значения ключа колонки primary каждый раз, когда в таблицу добавляется новая запись.
Если сейчас мы начнём вводить данные, СУБД не будет знать, для какой базы данных они предназначаются. На сервере может быть множество БД – в какой из них пользователь создаёт таблицы? Чтобы разрешить эту неопределённость используется команда USE, после которой указывается имя базы данных, с который вы собираетесь работать:
Посмотрите на изменившееся приглашение командной строки:
Теперь все введённые команды и данные будут относиться к БД BookstoreDB.
Скопируйте целиком и вставьте следующие команды в приглашение командной строки:
Как видим, команда создания таблицы имеет вид
Команду можно было вводить построчно или скопировать и вставить за один раз. При построчном вводе СУБД в качестве окончания вводимой команды ожидает точки с запятой (;). Также команду можно было записать в одну строку, например:
Никакой разницы нет.
Теперь мы может продолжить и начать вводить данные в AuthorsTBL и BooksTBL.
Выбор, вставка, обновление и удаление рядов
Начнём с заполнения таблица AuthorsTBL. Почему? Нам нужно иметь значения AuthorID перед вставкой записей в BooksTBL.
Выполните следующий запрос к MariaDB:
Команда вставки строк в таблицу называется INSERT и имеет общий вид:
В результате этой команды была бы вставлена одна новая строка. За один раз можно вставить сразу несколько строк. Команда вставки нескольких строк имеет вид:
Т.е. если бы мы хотели вставить трёх авторов каждому из которых присвоен уникальный номер, то мы могли ввести бы следующую команду:
Но поскольку в таблице AuthorsTBL при характеристике типа AuthorID был установлен флаг AUTO_INCREMENT, который означает автоматическую установку уникального номера, то мы смогли из нашей команды убрать информацию, относящуюся к AuthorID. СУБД всё сделала сама: сама заполнила эти поля значениями 1, 2, 3.
Чтобы в этом убедиться, давайте посмотрим на нашу таблицу командой SELECT:
Команда SELECT используется для получения (выбора) записей из таблицы.
Общий синтаксис команды:
Как мы могли убедиться, часть WHERE условие; является опциональной. Если она не определена, то выбираются все строки. После SELECT можно указать название полей, которые вас интересуют, например:
Символ звёздочки (*) означает сразу все поля, т.е. вывод последней команды полностью идентичен SELECT * FROM AuthorsTBL;
Можно указать (через запятую) любой набор полей по желанию:
После WHERE можно указывать различные условия. Например, я хочу выбрать все поля, в строках которых автором является Agatha Christie, команда для этого:
Теперь сделаем вставку (INSERT) записей в таблицу BooksTBL, мы будем использовать соответствующий AuthorID автора для каждой книги. Значение 1 в BookIsAvailable говорит о наличии книги, а 0 – об её отсутствии:
Посмотрим содержимое таблицы BooksTBL:
Мы допустили ошибку, у книги The Alchemist должна быть цена 22.75. Для изменения данных в таблице используется команда UPDATE. Её общий синтаксис:
Для того, чтобы книги, у которой BookID равен 6 присвоить столбцу BookPrice новое значение 22.75 выполним следующую команду:
Как можно убедиться, данные изменились:
При желании удалить запись (строку), можно воспользоваться командой DELETE. Синтаксис этой команды:
Например, команда для удаления из таблицы BooksTBL строки, у которой значение поля столбца равно 6:
Не забывайте с командами UPDATE и DELETE использовать условие WHERE, поскольку без него можно удалить все строки в таблице или изменить значение столбца сразу для всех записей.
Объединение вывода из нескольких таблиц
Если вы хотите объединить два (или более) полей, в том числе разных таблиц, вы можете использовать оператор CONCAT. Например, допустим мы хотим получить результат, состоящий из полей имени книги и автора, в виде “The Alchemist (Paulo Coelho)” и другой клонки с ценой.
Команду можно прочитать как SELECT (выбрать) поле1, поле2, поле3 FROM (из) таблицы1 JOIN (объединённой с) таблицей2 ON (по общим полям) таблица1.поле = таблица2.поле) :
Как мы видим, CONCAT позволяет объединить несколько строковых выражений, разделённых запятой. Также для представления результата объединения мы выбрали псевдоним Description.
Ещё обратите внимание на новый синтаксис обращения к данным в виде имя_таблицы.имя_колонки (через точку)
Создание пользователя для доступа к безе данных
Использование root для выполнения операций языка управления данными (DML) в базе данных – это плохая идея. Чтобы избежать это, мы можем создать новый пользовательский аккаунт MariaDB (мы назовём его bookstoreuser) и назначим необходимые разрешения для BookstoreDB:
Имея выделенных, отдельных пользователей для каждой базы данных предотвратит вред для всех баз данных от компрометации одного аккаунта.
Дополнительные подсказки MySQL
Для очистки приглашения MariaDB, можно воспользоваться сочетанием клавиш Ctrl+l или набрать следующую команду и нажать Enter:
Если вы ещё не догадались, последовательность символов \! отправляет последующую команду в шелл Linux (а не в СУБД) и выводят их результат на экран. После \! можно использовать любые команды Bash.
Для анализа конфигурации данной таблицы сделайте:
Для SHOW COLUMNS IN можно использовать сокращение DESC:
Быстрая проверка обнаружила, что поле BookIsAvailable может иметь значение NULL. Поскольку мы не хотим разрешать это, мы изменим таблицу командой ALTER:
При повторной проверке теперь YES на пересечении BookIsAvailable и Null смениться на NO.
Как вы помните, мы создали пользователя bookstoreuser. Давайте зайдём в MariaDB как этот пользователь. Для завершения сессии введите
или нажмите Ctrl + d
После ключа -p можно сразу указать пароль. Между ключами -u и -p и следующими за ними именем пользователя и паролем необязательно ставить пробел.
Показ всех баз данных
Чтобы увидеть все доступные базы данных, можно использовать любую из этих команд, они равнозначны:
У пользователя bookstoreuser имеются привилегии на просмотр только одной базы данных – BookstoreDB, т.е. другие базы данных, размещённые на сервере, он не может увидеть.
Как показать все таблицы в базе данных
Если вы уже выбрали базу данных и хотите увидеть, какие таблицы в ней присутствуют, то выполните команду:
Удаление базы данных
База данных удаляется командой DROP DATABASE:
Если вы не уверены, существует ли база данных, которую вы хотите удалить, то в этой ситуации можно использовать конструкцию:
Если БД существует, то она будет удалена, если она отсутствует, то данная команда не вызовет ошибку.
Заключение
В этой статье мы разобрались, как выполнять DML операции и как создать базу данных, таблицы, отдельного пользователя базы данных в MariaDB. Дополнительно, мы рассмотрели несколько подсказок, которые могут сделать вашу жизнь системного администратора / администратора базы данных проще.
Ниже предоставлен список наиболее полезных и часто используемых команд MySQL с примерами.
Это перевод статьи отсюда>>>, с некоторыми незначительными изменениями и дополнениями.
mysql> в начале строки означает, что команда выполняется из MySQL-клиента.
Общие команды
Что бы проверить статус сервера MYSQL выполните:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на том же хосте:
Работа с базами и таблицами
Работа с базами
Создать базу данных на MySQL сервере:
Показать список всех баз данных на сервере MySQL:
Переключиться для работы с определенной базой данных:
Работа с таблицами
Отобразить все таблицы в базе данных:
Просмотреть формат таблицы в базе:
Показать все содержимое таблицы:
Отобразить количество строк в таблице:
Подсчитать количество колонок в таблице:
Удаление строки в таблице:
mysql> DELETE from [table name] where [field name] = 'whatever';Удаление столбца из таблицы:
mysql> alter table [table name] DROP INDEX [column name];Удалить таблицу из базы:
Работа с колонками
Добавить колонку в таблицу:
mysql> ALTER TABLE [table name] ADD COLUMN [new column name] varchar (20);Изменение имени колонки:
mysql> ALTER TABLE [table name] CHANGE [old column name] [new column name] varchar (50);Создать колонку с уникальным именем, что бы избежать дубликатов в названиях:
mysql> ALTER TABLE [table name] ADD UNIQUE ([column name]);Изменение размера колонки:
mysql> ALTER TABLE [table name] MODIFY [column name] VARCHAR(3);Выборка данных
Показать все содержимое таблицы:
Отобразить колонки и их содержимое в выбранной таблице:
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever"; mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444'; mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number; mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444'; mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;Показать все уникальные записи:
Отобразить выбранные записи, отсортированные по возрастанию ( asc ) или убыванию ( desc ):
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;Регулярные выражения
Импорт и экспорт данных в/из файла
Загрузка файла CSV в таблицу:
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);Пользователи, пароли сервера MySQL:добавление, изменение пользователей и паролей
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username', PASSWORD('password')); mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');Восстановление/изменение пароля root сервера MySQL — остановка MySQL, запуск без таблиц привилегий, подключение под root , установка нового пароля, выход и перезапуск MySQL.
Подробнее о восстановлении пароля root для MySQL написано тут>>>.
Бывают случаи что управления базой данных необходимо произвести из консоли. Ведь не каждый из нас помнит все консольные команды наизусть. Да и не всегда это нужно. К примеру нам нужно создать всего одну базу данных скажем для установки того же wordpress. Для этого не обязательно создавать ее в консоли если имеется в системе пакет phpmyadmin. Как установить phpmyadmin вместе со стеком lamp читайте тут Но что делать если его нет? Можно конечно установить, особого труда в этом нет. Хотя, к примеру в настоящее время в Debian 10 этот пакет почему то отсутствует в официальных репозиториях. Есть разумеется способ ставить phpmyadmin не из репозиториев, а напрямую скачав его с сайта. Но это потребует намного больше усилий чем просто ввести пару команд в консоли и создать или удалить ту или иную базу данных. По этому мы и разберем сегодня несколько консольных команд призванных облегчить нам всем жизнь. И так, переходим в консоль.
Статус, запуск и остановка базы данных
В результате вы увидите что то наподобие этого:
Как можно увидеть база данных запущена и работает. Остановить работу базы данных можно с помощью команды:
А что бы запустить достаточно сменить слово stop на слово start. А так же если вы замените слово stop на слово restart то перезагрузите базу данный.
Создание новой базы данных
Что бы подключится к базе дынных введите команду:
При подключении указываете имя, в моем случае это: Cyber-X. А так же понадобится ввести пароль от базы данных.
Теперь давайте посмотрим какие базы данных у нас имеются на данный момент. В моем случаем уже были созданы 2 базы это: test-1 и test_1. У вас же их не будет. Что бы посмотреть введите команду:
Теперь создадим базу под названием cyber. Это можно сделать следующей командой:
Зададим нового пользователя этой базы данных и пароль, в моем случае это будет Cyber-X и пароли: 123456. После слова: GRANT ALL ON указываем имя создаваемой базы, обратите на это внимания:
Смотрим что у нас получилось:
Теперь когда у нас создана база, можно выходить из режима управления базами данных, сделать это можно с помощью команды:
Удаление базы данных
Что бы удалить ненужную базу данных достаточно ввести не сложную команду: DROP DATABASE. Например, нам нужно удалить только что созданную базу под названием: cyber. Команда будет выглядеть следующим образом:
Смотрим что у нас вышло с помощью уже известной нам команды:
Как видим на скриншоте, мы удалили базу данных под названием: cyber.
Создание дампа базы данных (Бэкап)
Для создания дампа базы данных можно использовать скрипт, который был специально написан для этих целей. А вот как восстановить дамп в консоли сейчас мы с вами и разберем. Восстановить дамп совсем не сложно, достаточно ввести команду:
Основные моменты работы с базами данных MariaDB из консоли. Не всегда под рукой имеется PhpMyAdmin, который удобен в работе а произвести необходимые работы с базами необходимо. Будьте внимательность при работе в консоли!
Введение
В статье вы узнаете как можно работать с базами данных MariaDB из консоли.
О том как производить оптимизацию после установки можно из статьи MariaDB оптимизация и установка.
Просмотр из консоли баз MariaDB
Подключение к серверу баз
Вывод списка имеющихся баз
Просмотр базы с выводом параметров
Подключение к необходимой базе
Просмотр всех таблиц из базы
Просмотр содержания нужной таблицы
Добавление базы в MariaDB
Для работы с базой необходимо после создания добавить пользователя к этой базе и назначить права.
Добавление базы с параметрами
Удаление базы
Просмотр пользователей с выводом их прав
Права пользователя баз данных MariaDB
В случае необходимости подключатся к базе с других компьютеров необходимо создать пользователя с нужным параметром и дать права на доступ в настройках сервера MariaDB!
Права на доступ к серверу баз данных делается в двух местах:
- Параметр bind-address=0.0.0.0 в конфигурационном файле самого сервера баз MariaDB разрешающий подключатся с любого адреса (или укажите конкретный IP) в разделе [mysqld] ;
- Права пользователя на возможность удаленного подключения к базе данных.
Для работы с базами мне удобно использовать PhpMyAdmin и эта программа позволяет настроить доступ к базам работающих на разных серверах.
Менять права пользователя root не желательно, но можно создать пользователя и дать ему полные права. Держать ещё одного пользователя с полными правами не разумно, но иногда необходимо. Добавляются пользователю полные права командой:
После добавления пользователя с полными правами и имеющегося PhpMyAdmin настроенного для подключения к другим серверам баз данных, можно зайти и создать все необходимые базы и пользователей.
Например, ниже представлена полная версия команд после выполнения которых будет создан пользователь sevo44 с полными правами:
Для безопасности я никогда не создаю пользователя с полными правами который может подключатся с удаленных мест. Для каждой базы свой пользователь!
В случае если надо поменять права имеющемуся пользователю это делается следующей командой, после подключения к серверу баз данных:
Добавление пользователя
Назначение пользователя базе
После всех манипуляция с базами необходимо обновить права доступа
Выход с сервера MariaDB
Выход производиться командой
Резервное копирование и восстановление баз MariaDB
Создадим необходимую папку для бэкапов и перейдем в неё
Создание резервной копию базы с указанием даты создания
Восстановление резервной копии базы
Вывод
В консоли без проблем можно делать все основные манипуляции с базой данных. Для более удобной работы удобней использовать PhpMyAdmin, установку и настройку которой я рассмотрел в своей статье PhpMyAdmin на Nginx.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Понравилась статья? Поделитесь ей с друзьями! Пожалуйста, оставляйте свои комментарииЧитая их я получаю информацию которая позволяет мне улучшить качество написания статей. Кроме того, оставляя комментарии вы помогаете сайту получить более высокий рейтинг у поисковых систем. Давайте общаться.
Добавить комментарий Отменить ответ
Метки
Реклама
Советую
Реклама
Комментарии
Вместо CentOS (для меня теперь это Fedora2) будет Rocky Linux :)
Теперь все статьи с Rocky будут?
Деже не надеюсь. Знаю что так и будет!
Хорошее руководство получилось. Надеюсь, Rocky Linux получит такое же распространение, как и Centos.
Ставил. Для моей версии там только контроль параметров и максимум можно запустить тест.
В настройках программы есть возможность серверам по ssh отправлять любые команды.
Порой приходится осуществлять создание, просмотр, изменение, удаление баз данных. Ставить phpMyAdmin для таких простых операций нет смысла.
В инструкции будет описан способ создания, просмотра, изменения и удаление баз данных в MariaDB базы данных через ssh (консоль, терминал).
2. Авторизация.
Все команды выполняются от имени администратора (минимальная привилегия, необходимая для удаления базы данных DROP) или от имени учетной записи root.
Если вы не установили пароль для своего корневого пользователя MariaDB, вы можете пропустить переключатель -p .
Заходим в MariaDB:
Вместо и Password вводим пароль администратора root, установленный во время инсталляции MariaDB.
3. Создание базы данных.
Теперь можно создать базу данных, введя команду:
CREATE DATABASE new_database;
Чтобы избежать ошибок, возникающих в случае, если база данных с таким именем уже существует, используйте команду:
CREATE DATABASE IF NOT EXISTS new_database;
Warning означает, что база данных с таким именем уже существует и новая база данных не была создана.
Если же опция IF NOT EXISTS не была использована, а база данных с таким именем уже существует, появится следующее уведомление об ошибке:
CREATE DATABASE new_database;
4. Просмотр баз данных.
Чтобы получить список существующих баз данных, используйте команду:
Базы данных information_schema , performance_schema и mysql в большинстве случаев создаются по умолчанию, без крайней необходимости (и умения с ними работать) их лучше не трогать.
5. Изменение баз данных.
Любая операция, выполняемая без явного указания базы данных, будет выполнена на текущую базу данных.
Чтобы узнать, какая база данных является текущей, наберите:
Результат NULL сообщает, что на данный момент текущая база данных не выбрана.
Чтобы выбрать базу данных для последующих операций, используйте следующую команду:
Снова используйте запущенную ранее команду, чтобы узнать, какая база данных является текущей:
6. Удаление баз данных.
Чтобы удалить базу данных в MariaDB, используйте команду:
DROP DATABASE new_database;
Примечание! Данную операцию невозможно отменить! Убедитесь, что базу данных действительно нужно удалить, прежде чем нажать Enter!
Если выполнить эту команду на базу данных, которой не существует, появится следующая ошибка:
DROP DATABASE new_database;
Чтобы предотвратить эту ошибку и добиться выполнения команды вне зависимости от того, существует база данных или нет, используйте опцию IF EXISTS :
DROP DATABASE IF EXISTS new_database;
В данном случае warning значит, что такой базы данных не существует, но команда выполнена.
7. Утилита mysqladmin.
Вы также можете удалить базу данных MariaDB с помощью утилиты mysqladmin.
Например, чтобы удалить базу данных с именем new_database , введите следующую команду и введите свой пароль пользователя root в MariaDB при появлении запроса:
Вместо и Password вводим пароль администратора root, установленный во время инсталляции MariaDB.
Сервер попросит подтвердить свои намерения удалить базу данных с именем new_database , ответим утвердительно символом y , и нажмём клавишу Enter. База данных будет удалена сразу.
Читайте также: