One 、 Check the environmental status

[root@mster-k8s ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.6.7 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Wed 2022-04-20 11:50:35 CST; 12min ago
     Docs: man:mariadbd(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 11187 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 7810 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 7778 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 8024 (mariadbd)
   Status: "Taking your SQL requests now..."
    Tasks: 9
   Memory: 90.5M
   CGroup: /system.slice/mariadb.service
           └─8024 /usr/sbin/mariadbd

Two 、mariadb Add, delete, change and check the database of

1. Local access mariadb

[root@mster-k8s ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.6.7-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 

2. increase —— Create a database

MariaDB [(none)]> create database huawei;
Query OK, 1 row affected (0.000 sec)

3. check —— view the database

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huawei             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.000 sec)

4. Change —— Change database name

① Check the database list

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| huawei_reduction   |
| information_schema |
| mysql              |
| performance_schema |
| redhat             |
| sys                |
+--------------------+

② Create a new empty database

MariaDB [(none)]> create database mall;
Query OK, 1 row affected (0.000 sec)

③ Backup database

mysqldump -uroot -p123  huawei_reduction  > /backup/mariadb/huawei.sql

④ Restore to empty database

mysqld -uroot -p123  mall  < ./huawei.sql

⑤ Check the restored database

MariaDB [(none)]> use mall
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mall]> 
MariaDB [mall]> show tables;
+----------------+
| Tables_in_mall |
+----------------+
| student        |
+----------------+
1 row in set (0.000 sec)

MariaDB [mall]> select * from student;
+----+--------+--------+------+-------+--------+-------+
| id | name   | gender | age  | class | course | grade |
+----+--------+--------+------+-------+--------+-------+
|  1 |  Zhang San    |      0 |   18 |     3 |  Chinese language and literature    |    98 |
|  2 |  Li Si    |      0 |   17 |     3 |  mathematics    |    95 |
|  3 |  Wang Wu    |      1 |   16 |     2 |  Physics    |    88 |
|  4 |  peak    |      0 |   22 |     4 |  English    |   100 |
|  5 |  Chen Lin    |      1 |   15 |     5 |  chemical    |    99 |
+----+--------+--------+------+-------+--------+-------+
5 rows in set (0.000 sec)

3、 ... and 、 Add, delete, modify and query database tables

1. increase —— Create a data table

① Enter an empty database

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> 

② Create a data table and its fields

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `gender` TINYINT NOT NULL,
   `age` INT UNSIGNED,
   `class` INT UNSIGNED,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 check —— View Datasheet information

① Look at the created table

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.000 sec)

② Look at the fields of the table

MariaDB [test]> show columns from student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100)     | NO   |     | NULL    |                |
| gender | tinyint(4)       | NO   |     | NULL    |                |
| age    | int(10) unsigned | YES  |     | NULL    |                |
| class  | int(10) unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

MariaDB [test]>

③ Query creation student Statement of field

MariaDB [test]> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                           |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `gender` tinyint(4) NOT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `class` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> 

④ View the database where the current table is located

MariaDB [test]> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.000 sec)

⑤ Query the fields of the current table

MariaDB [test]> describe student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100)     | NO   |     | NULL    |                |
| gender | tinyint(4)       | NO   |     | NULL    |                |
| age    | int(10) unsigned | YES  |     | NULL    |                |
| class  | int(10) unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

MariaDB [test]> 

3. Change —— Change the name of the data table

MariaDB [test]> alter table student rename student01;
Query OK, 0 rows affected (0.008 sec)

4. Delete —— Delete table

MariaDB [test]> drop table student;
Query OK, 0 rows affected (0.055 sec)

MariaDB [test]> 

Four 、 Add, delete, query and modify fields in the data table

1. check —— Query the fields in the table

① Query field method 1

MariaDB [test]> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `gender` tinyint(4) NOT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `class` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> 

② Query field method 2

MariaDB [test]> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `gender` tinyint(4) NOT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `class` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

2. increase —— Add fields

MariaDB [test]>  alter table student add course tinyint;
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. Change —— Modify fields

① Modify field type

MariaDB [test]> alter table student modify   course VARCHAR(100);
Query OK, 0 rows affected (0.037 sec)              
Records: 0  Duplicates: 0  Warnings: 0

② Modify field name

MariaDB [test]> alter table student change course Course VARCHAR(100) after name;
Query OK, 0 rows affected (0.026 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. Delete —— Delete field

MariaDB [test]>  alter table student drop Course;

5、 ... and 、 Add, delete, check and modify the contents of the data table

1. increase —— insert data

① Single insert data

insert into student ( name, gender, age, class, Course, grade ) values ( " Zhang San ", "0", "18", "3", " Chinese language and literature ", "98" );
insert into student (name, gender, age, class, Course, grade) values (" Li Si ", "0", "17", "3", " mathematics ", "95");
insert into student (name, gender, age, class, Course, grade) values ( " Wang Wu ", "1", "16", "2", " Physics ", "88");

② Bulk insert data

insert into student ( name, gender, age, class, Course, grade ) values ( " peak ", "0", "22", "4", " English ", "100"), ( " Chen Lin ", "1", "15", "5", " chemical ", "99" );

2. Change —— Modifying data

update student set gender=0 where name=" Wang Wu " and id=5;

3. check —— Query data

① Query all contents in the table

MariaDB [test]> select * from student;
+----+--------+--------+--------+------+-------+-------+
| id | name   | Course | gender | age  | class | grade |
+----+--------+--------+--------+------+-------+-------+
|  1 |  Zhang San    |  Chinese language and literature    |      0 |   18 |     3 |    98 |
|  2 |  peak    |  English    |      0 |   22 |     4 |   100 |
|  3 |  Chen Lin    |  chemical    |      1 |   15 |     5 |    99 |
|  4 |  Li Si    |  mathematics    |      0 |   17 |     3 |    95 |
|  5 |  Wang Wu    |  Physics    |      0 |   16 |     2 |    88 |
+----+--------+--------+--------+------+-------+-------+
5 rows in set (0.000 sec)

② Query the specified content

MariaDB [test]> select name,age from student;
+--------+------+
| name   | age  |
+--------+------+
|  Zhang San    |   18 |
|  peak    |   22 |
|  Chen Lin    |   15 |
|  Li Si    |   17 |
|  Wang Wu    |   16 |
+--------+------+
5 rows in set (0.000 sec)

MariaDB [test]> 

4. Delete —— Delete data

delete from student where name=" Wang Wu ";