About MariaDB database in Linux

About MariaDB database in Linux

About MariaDB database in Linux

1. What is a database?

Efficient media for storing and processing data (disks and memory)
It is a warehouse built on computer storage devices that organizes, stores and manages data according to data structures.
To put it simply, it can be regarded as an electronic filing cabinet - a place to store electronic files, where users can add, extract, update, delete, and perform other operations on the data in the files.

2. Classification of Databases

According to the storage medium, it is divided into: relational database (sql), non-relational database (nosql)

1. Relational database (sql)

It refers to a database that uses a relational model to organize data. It stores data in the form of rows and columns to facilitate user understanding. This series of rows and columns of a relational database is called a table, and a group of tables constitutes a database. Users retrieve data from a database through queries, which are executable codes used to limit certain areas of the database. The relational model can be simply understood as a two-dimensional table model, and a relational database is a data organization consisting of two-dimensional tables and the relationships between them.

advantage:

  • Easy to understand: The two-dimensional table structure is a concept that is very close to the logical world. The relational model is easier to understand than other models such as network and hierarchical models.
  • Easy to use: The universal SQL language makes it very convenient to operate relational databases.
  • Easy to maintain: Rich integrity (entity integrity, referential integrity, and user-defined integrity) greatly reduces the probability of data redundancy and data inconsistency.

2. Non-relational database (nosql)

The most common explanation for NoSQL is "non-relational", and "Not Only SQL" is also accepted by many people. NoSQL is just a concept, referring to non-relational databases. Unlike relational databases, they do not guarantee the ACID properties of relational data. NoSQL is a new revolutionary movement in databases. Its supporters advocate the use of non-relational data storage. Compared with the overwhelming use of relational databases, this concept is undoubtedly an injection of new thinking.

advantage:

  • Easy to expand
  • Large data volume, high performance
  • Flexible data model
  • High Availability

3. DML (data manipulation language)

There are mainly the following operations on the database data
Select query
select 列名稱 from 表名稱

update
 update 表名 set 更新的數據where 條件

insert
insert into table_name (列1, 列2,…) values; (值1, 值2,…)

delete
delete from 表名稱 where 列名稱=值

4. DDL (data definition language) database definition language

DDL is mainly used to define or change the table structure, data type, links and constraints between tables, and other initialization tasks.
for example:

create

Create Table

create table table name
(
Column Name 1 Data Type,
Column Name 2 Data Type,
Column Name 3 Data Type,
…
);

Create a database
create database database name

alter modification
alter table students change column birth birthday date;
alter table student rename students;

drop
drop table table name;
drop database database name;

5. DCL (Data Control Language) Database Control Language

It is used to set or change the permissions of database users or roles, including (grant, revoke, etc.) statements. This is rarely used.

1. mariadb

MariaDB database management system is a branch of MySQL, mainly maintained by the open source community. It uses the GPL license. The purpose of MariaDB is to be fully compatible with MySQL, including API and command line, so that it can easily become a substitute for MySQL. In terms of storage engine, XtraDB is used instead of MySQL's InnoDB. MariaDB was developed by Michael Widenius, the founder of MySQL. He had previously sold his company MySQL AB to SUN for $1 billion. After SUN was acquired by Oracle, the ownership of MySQL also fell into the hands of Oracle. MariaDB is named after Michael Widenius' daughter Maria.

2. User management and access control

Create a database login user

MariaDB [openlab]> create user xixi@localhost identified by 'xixi';
Query OK, 0 rows affected (0.001 sec)

View the user currently logged in to the database

MariaDB [openlab]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

View the current user's database

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

Exit and log in to the database using user xixi

[root@redhat ~]# mysql -uxixi -pxixi

View Database

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)

Log out and log in to the database as root user to set permissions for user xixi

[root@redhat ~]# mysql -uroot -proot
MariaDB [(none)]> grant select,update,insert,delete on openlab.student to xixi@localhost;
Query OK, 0 rows affected (0.001 sec)

User xixi logs into the database again

[root@redhat ~]# mysql -uxixi -pxixi

Check

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| openlab |
+--------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> use openlab;
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 [openlab]> select * from student;
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 5 | mabo | 20 | nan | 1998-07-07 |
+--------+---------+------+------+------------+
4 rows in set (0.000 sec)

Testing insert permissions

MariaDB [openlab]> insert into student(number,name,age,sex,birth) values ​​(4,"zhangsan",100,"nan","100-01-01");
Query OK, 1 row affected (0.001 sec)

MariaDB [openlab]> select * from student;
+--------+----------+------+------+------------+
| number | name | age | sex | birth |
+--------+----------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 5 | mabo | 20 | nan | 1998-07-07 |
| 4 | zhangsan | 100 | nan | 0100-01-01 |
+--------+----------+------+------+------------+
5 rows in set (0.000 sec)

Test Update Permissions

MariaDB [openlab]> update student set age=19 where number=4;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [openlab]> select * from student;
+--------+----------+------+------+------------+
| number | name | age | sex | birth |
+--------+----------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 5 | mabo | 20 | nan | 1998-07-07 |
| 4 | zhangsan | 19 | nan | 0100-01-01 |
+--------+----------+------+------+------------+
5 rows in set (0.000 sec)

Test delete permissions

MariaDB [openlab]> delete from student where number=4;
Query OK, 1 row affected (0.001 sec)

MariaDB [openlab]> select * from student;
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 5 | mabo | 20 | nan | 1998-07-07 |
+--------+---------+------+------+------------+
4 rows in set (0.000 sec)

6. Backup and Restore

Back up your data

[root@redhat ~]# mysqldump -u root -p openlab > /openlab_backup_20210904.dump
Enter password:

Log in to the database as root user and delete the table

[root@redhat ~]# mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.3.28-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)]> use openlab;
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 [openlab]> drop table student;
Query OK, 0 rows affected (0.112 sec)

MariaDB [openlab]> select * from student;
ERROR 1146 (42S02): Table 'openlab.student' doesn't exist

Exit to restore

[root@redhat ~]# mysql -u root -p openlab < /openlab_backup_20210904.dump
Enter password:

Log in to the database again as root and check whether the table is restored.

[root@redhat ~]# mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.3.28-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)]> use openlab;
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 [openlab]> select * from student;
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 5 | mabo | 20 | nan | 1998-07-07 |
+--------+---------+------+------+------------+
4 rows in set (0.000 sec)

7. Set the character set

There are generally two ways to set the character set. One is to set the character set when creating the table, and the other is to modify the character set after the table is built.

1. Specify the character set when creating

Specify the character set when creating a library:
語法：create database 庫名default character set=字符集；
create database db2 default character set=utf8

Specify the character set when creating a table:
語法：create table 表名（屬性）default character set = 字符集；

mysql> create table test(id int(6),name char(10)) default character set = 'gbk';
Query OK, 0 rows affected (0.39 sec)

2. Modify the character set

Modify the global character set

/Encoding used to establish the connection/
set character_set_connection=utf8;
/Database Encoding/
set character_set_database=utf8;
/Result set encoding/
set character_set_results=utf8;
/Database server code/
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

Modify the character set of the library

語法：alter database 庫名default character set 字符集;
alter database shiyan default character set gbk;

mysql> show create database shiyan\G
*************************** 1. row ***************************
       Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql> alter database shiyan default character set gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database shiyan\G
*************************** 1. row ***************************
       Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

Modify the character set of a table

語法：alter table 表名convert to character set 字符集;
alter table test1 convert to character set utf8;

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(6) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk #Original character set 1 row in set (0.00 sec)
mysql> alter table test1 convert to character set utf8;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(6) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #Modified character set 1 row in set (0.00 sec)

Modify the character set of a field

語法：alter table 表名modify 字段名字段屬性character set gbk；
alter table test1 modify name char(10) character set gbk;

mysql> show full columns from test1;
+-------+----------+-----------------+------+-----+---------+-------+--------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+----------+-----------------+------+-----+---------+-------+--------------------------------+---------+
| id | int(6) | NULL | YES | | NULL | | select,insert,update,references | |
| name | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+----------+-----------------+------+-----+---------+-------+--------------------------------+---------+
2 rows in set (0.01 sec)
mysql> alter table test1 modify name char(10) character set gbk;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show full columns from test1;
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | NULL | YES | | NULL | | select,insert,update,references | |
| name | char(10) | gbk_chinese_ci | YES | | NULL | | select,insert,update,references | |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

8. Case

1. Create a table

Install the database (the system has been installed by default. If it is not installed, the command is as follows)

[root@redhat ~]# yum install mariadb -y

Start the database service

[root@redhat ~]# systemctl restart mariadb

Initialize the database and set the root password

[root@redhat ~]# mysql_secure_installation

Login to database

[root@redhat ~]# mysql -uroot -proot

Create a database

MariaDB [(none)]> create database openlab;

Enter the openlab database

MariaDB [(none)]> use openlab;

Create the student table

MariaDB [openlab]> create table student(number int,name varchar(20),age int,sex varchar(3),birth date);

View Table

MariaDB [openlab]> show tables;
+-------------------+
| Tables_in_openlab |
+-------------------+
| student |
+-------------------+
1 row in set (0.001 sec)

MariaDB [openlab]> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.001 sec)

Insert data into the table

MariaDB [openlab]> insert into student(number,name,age,sex,birth) values ​​(1,"wangkai",22,"nan","1996-02-02");
Query OK, 1 row affected (0.003 sec)

MariaDB [openlab]> insert into student(number,name,age,sex,birth) values ​​(2,"lili",21,"nv","1997-03-03");
Query OK, 1 row affected (0.001 sec)

MariaDB [openlab]> insert into student(number,name,age,sex,birth) values ​​(3,"kaili",21,"nv","1997-04-04");
Query OK, 1 row affected (0.001 sec)

MariaDB [openlab]> insert into student(number,name,age,sex,birth) values ​​(4,"wangkai",20,"nv","1998-05-05");
Query OK, 1 row affected (0.001 sec)

MariaDB [openlab]> insert into student(number,name,age,sex,birth) values ​​(5,"mabo",20,"nan","1998-02-02");
Query OK, 1 row affected (0.001 sec)

View the contents of the table

MariaDB [openlab]> select * from table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table' at line 1
MariaDB [openlab]> select * from student;
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 4 | wangkai | 20 | nv | 1998-05-05 |
| 5 | mabo | 20 | nan | 1998-02-02 |
+--------+---------+------+------+------------+
5 rows in set (0.001 sec)

2. Query all students whose age is 20

MariaDB [openlab]> select * from student where age=20;
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 4 | wangkai | 20 | nv | 1998-05-05 |
| 5 | mabo | 20 | nan | 1998-02-02 |
+--------+---------+------+------+------------+
2 rows in set (0.001 sec)

3. Query the information about a boy named Wang Kai in the class

MariaDB [openlab]> select * from student where name="wangkai";
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 4 | wangkai | 20 | nv | 1998-05-05 |
+--------+---------+------+------+------------+
2 rows in set (0.000 sec)

4. Change Ma Bo's birthdate to 1998-7-7

MariaDB [openlab]> update student set birth="1998-07-07" where name="mabo";
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [openlab]> select * from student where name="mabo";
+--------+------+------+------+------------+
| number | name | age | sex | birth |
+--------+------+------+------+------------+
| 5 | mabo | 20 | nan | 1998-07-07 |
+--------+------+------+------+------------+
1 row in set (0.000 sec)

5. Delete student number 4

MariaDB [openlab]> delete from student where number=4;
Query OK, 1 row affected (0.001 sec)

MariaDB [openlab]> select * from student;
+--------+---------+------+------+------------+
| number | name | age | sex | birth |
+--------+---------+------+------+------------+
| 1 | wangkai | 22 | nan | 1996-02-02 |
| 2 | lili | 21 | nv | 1997-03-03 |
| 3 | kaili | 21 | nv | 1997-04-04 |
| 5 | mabo | 20 | nan | 1998-07-07 |
+--------+---------+------+------+------------+
4 rows in set (0.000 sec)

6. List the names of all students in the table

MariaDB [openlab]> select name from student;
+---------+
| name |
+---------+
| wangkai |
| lili |
| kaili |
|mabo|
+---------+
4 rows in set (0.001 sec)

7. List the name and age of student number 3

MariaDB [openlab]> select number,name,age from student where number=3;
+--------+-------+------+
| number | name | age |
+--------+-------+------+
| 3 | kaili | 21 |
+--------+-------+------+
1 row in set (0.001 sec)

This is the end of this article about the mariadb database in Linux. For more information about the Linux mariadb database, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Detailed example of installing MariaDB database on Linux
  • MariaDB database installation problems and solutions under Linux (binary version installation)
  • Basic operation tutorial for migrating database from MySQL to MariaDB under Linux
  • How to Reset MySQL or MariaDB Root Password in Linux
  • MariaDB 10 automated installation and deployment on Linux servers

<<:  A brief analysis of whether MySQL primary key uses numbers or uuids for faster query

>>:  Sample code for cool breathing effect using CSS3+JavaScript

Recommend

The implementation code of the CSS3 input box is similar to the animation effect of Google login

Use CSS3 to animate the input box similar to the ...

Three networking methods and principles of VMware virtual machines (summary)

1. Brigde——Bridge: VMnet0 is used by default 1. P...

ElementUI implements cascading selector

This article example shares the specific code of ...

How to modify the initial password of MySQL on MAC

Problem description: I bought a Mac and installed...

Detailed explanation of using CSS3's var() to change scss variable values ​​at runtime

var() Introduction and Usage Details (MDN) IE is ...

Summary of common tool examples in MySQL (recommended)

Preface This article mainly introduces the releva...

Summary of learning HTML tags and basic elements

1. Elements and tags in HTML <br />An eleme...

Detailed explanation of slave_exec_mode parameter in MySQL

Today I accidentally saw the parameter slave_exec...

Native JS to implement breathing carousel

Today I will share with you a breathing carousel ...

Teach you how to use AWS server resources for free

AWS - Amazon's cloud computing service platfo...

Web designers also need to learn web coding

Often, after a web design is completed, the desig...

Vue implements video upload function

This article example shares the specific code of ...

Mysql master-slave synchronization Last_IO_Errno:1236 error solution

What is the reason for the Last_IO_Errno:1236 err...

Perfect solution to the problem that MySQL cannot connect to the database through localhost

Problem: The PHP program on one server cannot con...

How to Find the Execution Time of a Command or Process in Linux

On Unix-like systems, you may know when a command...