Изучение MySQL / MariaDB для начинающих
В этой статье я покажу, как создать базу данных (также известную как 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 используется для определения момента, когда строка была добавлена или обновлена.
Теперь, когда вы бегло ознакомились с типами данных, вам будет проще определить, данные какого типа назначить заданной колонке в таблице.
Например, имя человека может легко поместиться в VARCHAR(50), в то время как сообщение в блоге потребует тип TEXT (можно указать M под специфические нужды).
Создание таблицы с 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, т.е. другие базы данных, размещённые на сервере, он не может увидеть.
Как показать все таблицы в базе данных
Если вы уже выбрали базу данных и хотите увидеть, какие таблицы в ней присутствуют, то выполните команду:
Также можно непосредственно указать интересующую базу данных после FROM или IN. Например, я хочу увидеть таблицы в базе данных db_softocracy.ru, тогда я выполняю:
Удаление базы данных
База данных удаляется командой DROP DATABASE:
Если вы не уверены, существует ли база данных, которую вы хотите удалить, то в этой ситуации можно использовать конструкцию:
Если БД существует, то она будет удалена, если она отсутствует, то данная команда не вызовет ошибку.
Заключение
В этой статье мы разобрались, как выполнять DML операции и как создать базу данных, таблицы, отдельного пользователя базы данных в MariaDB. Дополнительно, мы рассмотрели несколько подсказок, которые могут сделать вашу жизнь системного администратора / администратора базы данных проще.
Connecting to MariaDB
This article covers connecting to MariaDB and the basic connection parameters. If you are completely new to MariaDB, take a look at A MariaDB Primer first.
In order to connect to the MariaDB server, the client software must provide the correct connection parameters. The client software will most often be the mysql client, used for entering statements from the command line, but the same concepts apply to any client, such as a graphical client, a client to run backups such as mysqldump, etc. The rest of this article assumes that the mysql command line client is used.
If a connection parameter is not provided, it will revert to a default value.
For example, to connect to MariaDB using only default values with the mysql client, enter the following from the command line:
In this case, the following defaults apply:
- The host name is localhost .
- The user name is either your Unix login name, or ODBC on Windows.
- No password is sent.
- The client will connect to the server, but not any particular database on the server.
These defaults can be overridden by specifying a particular parameter to use. For example:
- -h specifies a host. Instead of using localhost , the IP 166.78.144.191 is used.
- -u specifies a user name, in this case username
- -p specifies a password, password . Note that for passwords, unlike the other parameters, there cannot be a space between the option ( -p ) and the value ( password ). It is also not secure to use a password in this way, as other users on the system can see it as part of the command that has been run. If you include the -p option, but leave out the password, you will be prompted for it, which is more secure.
- The database name is provided as the first argument after all the options, in this case database_name .
Connection Parameters
Connect to the MariaDB server on the given host. The default host is localhost . By default, MariaDB does not permit remote logins — see Configuring MariaDB for Remote Client Access.
password
The password of the MariaDB account. It is generally not secure to enter the password on the command line, as other users on the system can see it as part of the command that has been run. If you include the -p or —password option, but leave out the password, you will be prompted for it, which is more secure.
On Windows systems that have been started with the —enable-named-pipe option, use this option to connect to the server using a named pipe.
The TCP/IP port number to use for the connection. The default is 3306 .
protocol
Specifies the protocol to be used for the connection for the connection. It can be one of TCP , SOCKET , PIPE or MEMORY (case-insensitive). Usually you would not want to change this from the default. For example on Unix, a Unix socket file ( SOCKET ) is the default protocol, and usually results in the quickest connection.
- TCP : A TCP/IP connection to a server (either local or remote). Available on all operating systems.
- SOCKET : A Unix socket file connection, available to the local server on Unix systems only.
- PIPE . A named-pipe connection (either local or remote). Available on Windows only.
- MEMORY . Shared-memory connection to the local server on Windows systems only.
shared-memory-base-name
Only available on Windows systems in which the server has been started with the —shared-memory option, this specifies the shared-memory name to use for connecting to a local server. The value is case-sensitive, and defaults to MYSQL .
socket
For connections to localhost, this specifies either the Unix socket file to use (default /tmp/mysql.sock ), or, on Windows where the server has been started with the —enable-named-pipe option, the name (case-insensitive) of the named pipe to use (default MySQL ).
TLS Options
A brief listing is provided below. See Secure Connections Overview and TLS System Variables for more detail.
Enable TLS for connection (automatically enabled with other TLS flags). Disable with ‘ —skip-ssl ‘
ssl-ca
CA file in PEM format (check OpenSSL docs, implies —ssl ).
ssl-capath
| CA directory (check OpenSSL docs, implies —ssl ). |
ssl-cert
X509 cert in PEM format (implies —ssl ).
ssl-cipher
TLS cipher to use (implies —ssl ).
ssl-key
X509 key in PEM format (implies —ssl ).
ssl-crl
Certificate revocation list (implies —ssl ).
ssl-crlpath
Certificate revocation list path (implies —ssl ).
ssl-verify-server-cert
Verify server’s «Common Name» in its cert against hostname used when connecting. This option is disabled by default.
The MariaDB user name to use when connecting to the server. The default is either your Unix login name, or ODBC on Windows. See the GRANT command for details on creating MariaDB user accounts.
Option Files
It’s also possible to use option files (or configuration files) to set these options. Most clients read option files. Usually, starting a client with the —help option will display which files it looks for as well as which option groups it recognizes.
Как использовать MySQL / MariaDB из командной строки
Хотя инструменты, такие как PHPMYADMIN, очень легко взаимодействуют с базами данных MySQL / Mariadb, иногда необходимо получить доступ к базе данных непосредственно из командной строки. Эта статья будет касаться попадания в базу данных и некоторые общие задачи, но не предоставит полного образования на SQL Syntax, управлении базами данных или других темов высокого уровня. Примеры в этом руководстве предназначены для CentOS 7 и Mariadb, как включено в наше изображение VPS WordPress, но должно работать на нашей VPSes CPanel, стеками лампы и другими. Эта страница предполагает, что у вас есть Подключено к вашему серверу через SSH.
подсказки указывают на то, что следует ввести из командной строки Bash, > подсказки находятся внутри самого MySQL.
Общие задачи MySQL, выполняемые из командной строки
Войти в базу данных MySQL
Чтобы войти в базу данных в качестве пользователя root, используйте следующую команду:
Введите пароль root.
Сбросить пароль MySQL
открытый текст использовать MySQL;Обновите пользователя Установите пароль = пароль («insertasswaswore), где user = ‘root’;где «insertasswordhere» — настоящий пароль привилегии промывки;выход
(Другие дистрибутивы Linux на основе системой Systemd могут иметь аналогичные команды в зависимости от того, запускают ли они фактическими mysql или mariadb; другие системы init будут разными)
Как только вы запустите команду ниже и введите свой пароль, вам будет представлен подсказку, которая сообщает вам, что программа действительно работает (Mariadb), и используется база данных:
Перечислите свои базы данных
Выдать шоу базы данных; Команда, как видно ниже, чтобы увидеть все базы данных. Пример показан ниже:
База данных переключения с помощью команды «Использовать»:
Команда «Show» также используется для перечисления таблиц в базе данных:
Всегда делайте резервную копию перед внесением каких-либо изменений
Использовать mysqldump. Чтобы сделать резервную копию вашей базы данных, прежде чем продолжить с этим руководством настоятельно рекомендуется.
Замените имя базы данных вашим фактическим именем базы данных и резервную копию базы данных с именем файла, который вы хотели бы создать и заканчивать его .sql. как тип файла для сохранения вашей базы данных. Это позволит вам восстановить базы данных MySQL с помощью mysqldump из этого файла резервной копии в любое время.
Мы рекомендуем вам запустить эту команду из каталога, который не является публично доступен, так что ваша база данных не может быть загружена с вашей учетной записи без входа в командную строку или FTP. Обязательно поменяйте свой каталог на / корень или /Главная или другое место в файловой системе, требующее надлежащих учетных данных.
Пример: сброс пароля администратора WordPress
Ознакомьтесь с приведенными выше инструкциями о том, как сделать резервную копию вашей базы данных, прежде чем продолжить.
Первый шаг: Вы должны знать, какая база данных, имя пользователя и пароль используются при установке WordPress. Они находятся в wp-config.php в корневом каталоге вашей установки WordPress как DB_NAME, DB_USER и DB_PASSWORD:
Шаг второй: Имея эту информацию, вы можете адаптировать инструкции из Как сбросить пароль администратора WordPress и сделаем то же самое из командной строки:
Шаг третий: Переключитесь на базу данных appdb:
База данных изменена
Шаг четвертый: и покажем таблицы:
Шаг пятый: Затем мы можем выбрать user_login и user_pass из таблицы wp_users, чтобы увидеть, какую строку мы будем обновлять:
Шаг шестой: Это позволяет нам установить новый пароль с помощью
Шаг седьмой: И мы снова видим новый хэш пароля с тем же SELECT
Connect to MariaDB
Summary: in this tutorial, you will learn how to connect to the MariaDB server using the mysql command-line program.
To connect to MariaDB, you can use any MariaDB client program with the correct parameters such as hostname, user name, password, and database name.
In the following section, you will learn how to connect to a MariaDB Server using the mysql command-line client.
Connecting to the MariaDB server with a username and password
The following command connects to the MariaDB server on the localhost :
In this command:
-u specifies the username
-p specifies the password of the username
Note that the password is followed immediately after the -p option.
For example, this command connects to the MariaDB server on the localhost:
In this command, root is the username and S@cure1Pass is the password of the root user account.
Notice that using the password on the command-line can be insecure. Typically, you leave out the password from the command as follows:
It will prompt for a password. You type the password to connect the MariaDB server:
Once you are connected, you will see a welcome screen with the following command-line:
Now, you can start using any SQL statement. For example, you can show all databases in the current server using the show databases command as follows:
Here is the output that shows the default databases:
Connecting to the MariaDB server on a specific host
To connect to MariaDB on a specific host, you use the -h option:
For example, the following command connects to the MariaDB server with IP 172.16.13.5 using the root account:
It will also prompt for a password:
Note that the root account must be enabled for remote access in this case.
Connecting to a specific database on the MariaDB server
To connect to a specific database, you specify the database name after all the options:
The following command connects to the information_schema database of the MariaDB server on the localhost :
The mysql client command-line default parameters
When you type the mysql command with any option, mysql client will accept the default parameters.
- The hostname is localhost
- The username is either login name on Linux or ODBC on Windows
- No password is sent
- The client will connect to the server without any particular database.
In this tutorial, you will learn how to connect to the MariaDB server using the mysql command-line client.