Shulou(Shulou.com)05/31 Report--

This article focuses on "the interaction process between mysql and MariaDB". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "the interaction process between mysql and MariaDB".

First, interact with MariaDB

You can use the mysql command to interact with MariaDB. First, use the subcommand ping to confirm that your service is running, and enter the password after the prompt:

$mysqladmin-u root-p ping Enter password: mysqld is alive

To make it easier for readers to understand, open an interactive MariaDB session:

$mysql-u root-p Enter password: Welcome to the MariaDB monitor. Commands end with; or\ g. [...] Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement. MariaDB [(none)] >

You are now in a MariaDB sub-shell, and the prompt is the MariaDB prompt. The normal Bash command cannot be used here, only the MariaDB command. Enter help (or?) View a list of commands. These are administrative commands for your MariaDB shell, and you can use them to customize your shell, but they don't belong to the SQL language.

Learn the basic knowledge of SQL

Structured query languages are defined based on their capabilities: a method of querying the contents of a database with regular and consistent syntax to get useful results. SQL looks like an ordinary English sentence, a little blunt. For example, if you log in to the database server and want to see which libraries are available, type SHOW DATABASES; and enter to see the results.

The SQL command ends with a semicolon. If you forget to enter a semicolon, MariaDB will think that you want to continue entering your query command on the next line, and you can continue to enter the command or enter the semicolon end command on the next line.

MariaDB [(NONE)] > SHOW DATABASES; +-+ | DATABASE | +-+ | information_schema | | mysql | | performance_schema | test | +-+ 4 ROWS IN SET (0.000 sec)

The above example shows that there are currently four databases: information_schema, mysql, performance_schema, and test. You must specify which library MariaDB uses before you can use query statements against that library. The command to specify the database is use. When you select a library, the MariaDB prompt box will switch to the selected library.

MariaDB [(NONE)] > USE test; MariaDB [(test)] >

1. Display the tables of the database

There are tables in the database, similar to spreadsheets: a series of rows (called records in the database) and columns. A row and a column uniquely determine a field.

To view the tables available in a database (which can be understood as a page in a multi-form spreadsheet), use the SQL keyword SHOW:

MariaDB [(test)] > SHOW TABLES; empty SET

The test database is empty, so use the use command to switch to the mysql database:

MariaDB [(test)] > USE mysql; MariaDB [(mysql)] > SHOW TABLES +-- + | Tables_in_mysql | +-- + | column_stats | | columns_priv | | db | [...] | time_zone_transition_type | | transaction_registry | | USER | +-- + 31 ROWS IN SET (0.000 sec) |

There are many tables in this database! the mysql database is the system management database for this MariaDB instance. It contains important data, such as the user structure used to manage database permissions. This database is important and you don't need to interact with it directly often, but it's common to use SQL scripts to manipulate it. It is useful to understand the mysql database when you are learning MariaDB because it helps to illustrate some basic SQL commands.

two。 Check a table

The last table of the mysql database for this instance is named USER. This table contains users who can access the database. There is currently only one root user, but you can add users with different permissions and give them permission to view, update or create data. You can look at the header of a table to see all the properties of an MariaDB user:

> SHOW COLUMNS IN USER; MariaDB [mysql] > SHOW COLUMNS IN USER +-+ | FIELD | TYPE | NULL | KEY | DEFAULT | +-- -+ | Host | CHAR (60) | NO | PRI | USER | CHAR (80) | NO | PRI | Password | CHAR (41) | NO | Select_priv | enum ('N') 'Y') | NO | | N | Insert_priv | enum (' Nickel Magazine Y') | NO | | N | Update_priv | enum ('Numbai Magazine Y') | NO | | N | N | Delete_priv | enum (' Numbai Magazine Y') | NO | | N | Create_priv | enum ('N') 'Y') | NO | | N | | Drop_priv | enum (' Nickel Magazine Y') | NO | | N | [...] 47 ROWS IN SET (0.001 sec)

3. Create a new user

Whether or not you need a normal account to manage the database or configure the database for your computer (such as when installing WordPress, Drupal, or Joomla), it is common to create an additional user account in MariaDB. You can prompt MariaDB to create a SQL user by adding a user to the USER table of the mysql database or by using the MariaDB keyword CREATE. Using CREATE to create new users will execute some useful methods by default, so you don't need to generate all the information manually:

> CREATE USER 'tux'@'localhost' IDENTIFIED BY' really_secure_password'

4. View the fields of the table

You can use the SELECT keyword to view the fields and values of the database table. In this example, you create a user named tux, so query the columns in the USER table:

> SELECT USER,host FROM USER; +-+-+ | USER | host | +-+-+ | root | localhost | [...] | tux | localhost | +-+-+ 7 ROWS IN SET (0.000 sec)

5. Give permissions to a user

You can see the status of the user by looking at the information listed in the USER table. For example, the new user tux does not have any permissions on this database. Using the WHERE statement, you can check only the tux record.

> SELECT USER,select_priv,insert_priv,update_priv FROM USER WHERE USER='tux' +-+ | USER | select_priv | insert_priv | update_priv | +-+ | tux | N | | N | N | +-+ |

Use the GRANT command to modify the user's permissions:

> GRANT SELECT ON *. * TO 'tux'@'localhost'; > FLUSH PRIVILEGES

Verify your changes:

> SELECT USER,select_priv,insert_priv,update_priv FROM USER WHERE USER='tux' +-+ | USER | select_priv | insert_priv | update_priv | +-+ | tux | Y | | N | N | +-+ |

The tux user now has permission to query records from all tables.

Third, create a custom database

So far, you have been interacting with the default database. Apart from user management, most people rarely interact with the default database. Typically, you will populate the created database with custom data.

1. Create a MariaDB database

You may already be able to create a new database in MariaDB yourself. Creating a database is similar to creating a new user.

Use the use command to use the newly created database as the currently used library:

> CREATE DATABASE example; Query OK, 1 ROW affected (0.000 sec) > SHOW DATABASES; +-+ | DATABASE | +-+ | example | [...]

Use the use command to use the newly created database as the currently used library:

> USE example

two。 Create a table

Creating a table is more complex than creating a database because you have to define column headers. MariaDB provides many convenient functions for creating columns, introducing data type definitions, self-incrementing options, null constraints, automatic timestamps, and so on.

The following is a simple table that describes a series of users:

> CREATE TABLE IF NOT EXISTS member (- > id INT AUTO_INCREMENT PRIMARY KEY,-> name VARCHAR 128NOT NULL,-> startdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP); Query OK, 0 ROWS affected (0.030 sec)

This table uniquely identifies each row by using an automatic increment method. The field that represents the user's name cannot be empty (or null), and each row is automatically timestamped when it is created.

Use the SQL keyword INSERT to populate this table with some sample data:

> INSERT INTO member (name) VALUES ('Alice'); Query OK, 1 ROW affected (0.011 sec) > INSERT INTO member (name) VALUES (' Bob'); Query OK, 1 ROW affected (0.011 sec) > INSERT INTO member (name) VALUES ('Carol'); Query OK, 1 ROW affected (0.011 sec) > INSERT INTO member (name) VALUES (' David'); Query OK, 1 ROW affected (0.011 sec)

Verify the data in the table:

> SELECT * FROM member +-+ | id | name | startdate | +-+ | 1 | Alice | 2020-10-03 15:25:06 | | 2 | Bob | 2020-10-03 15:26:43 | | 3 | Carol | 2020-10-03 15:26:46 | | 4 | David | 2020-10-03 15:26:51 | +-+ 4 ROWS IN SET (0.000 sec)

3. Add multiple rows of data at the same time

Create another table:

> CREATE TABLE IF NOT EXISTS linux (- > id INT AUTO_INCREMENT PRIMARY KEY,-> distro VARCHAR 128NOT NULL); Query OK, 0 ROWS affected (0.030 sec)

Fill in some sample data, this time using VALUES shortcuts, so you can add multiple rows of data at a time. The VALUES keyword requires a list surrounded by parentheses as a parameter, or multiple lists separated by commas as parameters.

> INSERT INTO linux (distro)-> VALUES ('Slackware'), (' RHEL'), ('Fedora'), (' Debian'); Query OK, 4 ROWS affected (0.011 sec) Records: 4 Duplicates: 0 Warnings: 0 > SELECT * FROM linux +-+ | id | distro | +-+-+ | 1 | Slackware | | 2 | RHEL | | 3 | Fedora | | 4 | Debian | +-+

4. Associate multiple tables

Now you have two tables, and there is no connection between them. The data of the two tables are independent, but you may need a value in Table 1 to identify the records in Table 2.

You can add a column to Table 1 that corresponds to the values in Table 2. Because both tables have unique identifiers (automatically incrementing id fields), the easiest way to associate them is to use the id field in Table 1 as the query condition for Table 2.

Create a column in Table 1 to represent a value in Table 2:

> ALTER TABLE member ADD COLUMN (os INT); Query OK, 0 ROWS affected (0.012 sec) Records: 0 Duplicates: 0 Warnings: 0 > DESCRIBE member; DESCRIBE member +-+ | FIELD | TYPE | NULL | KEY | DEFAULT | Extra | + -+-+ | id | INT (11) | NO | PRI | NULL | auto_ | | name | VARCHAR | NO | | NULL | startdate | TIMESTAMP | NO | | cur [.] | os | INT (11) | YES | NULL | | +- +-+

Assign a unique ID in the linux table to each member. Because the record already exists, use the UPDATE keyword instead of INSERT. Especially if you want to query a row and then update a column of values. Grammatically, the expression is a little inverted, update first and then query:

> UPDATE member SET os=1 WHERE name='Alice'; Query OK, 1 ROW affected (0.007 sec) ROWS matched: 1 Changed: 1 Warnings: 0

To populate the data, repeat this process for other names. For the sake of data diversity, three different values are assigned to four rows of records.

Connection table:

Now that the two tables are related to each other, you can use SQL to show the associated data. There are many ways to connect in the database, please try as much as you can. The following example is to associate the os field in the member table with the id field in the linux table:

SELECT * FROM member JOIN linux ON member.os=linux.id +-id | name | startdate | os | id | distro | + -- +-+ | 1 | Alice | 2020-10-03 15:25:06 | 1 | 1 | Slackware | 2 | Bob | 2020-10-03 15:26:43 | 3 | 3 | Fedora | 4 | David | 2020-10-03 15:26:51 | 3 | Fedora | 3 | Carol | 2020-10-03 15:26:46 | 4 | Debian | +-- -+ 4 ROWS IN SET (0.000 sec)

Connect the os and id fields.

In graphical applications, you can imagine that the os field can be set in the drop-down menu, and the source of the value is the distro field in the linux table. By using independent but related data in multiple tables, you can ensure the consistency and validity of the data, and with SQL you can relate them dynamically.

Download MariaDB and MySQL cheat sheets

MariaDB is an enterprise-level database. It is a robust, powerful and efficient database engine. Learning it is a great step towards managing web applications and writing language libraries. You can download MariaDB and MySQL cheat sheets for quick reference when you use MariaDB.

At this point, I believe you have a deeper understanding of "the interaction process between mysql and MariaDB". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.