关于Linux的mariadb数据库

关于linux的mariadb数据库

一、什么是数据库（database）

高效的存储和处理数据的介质（磁盘和内存）
是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。
简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。

二、数据库的分类

按存储介质分为:关系型数据库（sql）、非关系型数据库(nosql)

1、关系型数据库（sql）

     是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

优点：

• 容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解。
• 使用方便：通用的sql语言使得操作关系型数据库非常方便。
• 易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率。

2、非关系型数据库(nosql)

     nosql最常见的解释是“non-relational”， “not only sql”也被很多人接受。nosql仅仅是一个概念，泛指非关系型的数据库，区别于关系数据库，它们不保证关系数据的acid特性。nosql是一项全新的数据库革命性运动，其拥护者们提倡运用非关系型的数据存储，相对于铺天盖地的关系型数据库运用，这一概念无疑是一种全新的思维的注入。

优点：

• 易扩展
• 大数据量，高性能
• 灵活的数据模型
• 高可用

三、dml（data manipulation language）数据操纵语言

主要有以下对数据库的数据进行一些操作
select查询
select      列名称      from      表名称

update更新
update      表名      set      更新的数据 where      条件

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

delete删除
delete      from      表名称      where      列名称=值

四、ddl（data definition language）数据库定义语言

ddl主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上
比如：

create创建

创建表

create      table      表名称
(
列名称1      数据类型,
列名称2      数据类型,
列名称3      数据类型,
…
);

创建数据库
create      database      数据库名

alter修改
alter      table      students      change       column      birth      birthday      date;
alter      table      student      rename      students;

drop删除
drop      table      表名称;
drop      database      数据库名称;

五、dcl（data control language）数据库控制语言

是用来设置或更改数据库用户或角色权限的语句，包括（grant,revoke等）语句。这个比较少用到。

1、mariadb

      mariadb数据库管理系统是mysql的一个分支，主要由开源社区在维护，采用gpl授权许可 mariadb的目的是完全兼容mysql，包括api和命令行，使之能轻松成为mysql的代替品。在存储引擎方面，使用xtradb（英语：xtradb）来代替mysql的innodb。 mariadb由mysql的创始人michael widenius（英语：michael widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司mysql ab卖给了sun，此后，随着sun被甲骨文收购，mysql的所有权也落入oracle的手中。mariadb名称来自michael widenius的女儿maria的名字。

2、用户的管理和访问权限控制

创建数据库登录用户

mariadb [openlab]> create user xixi@localhost identified by 'xixi';
query ok, 0 rows affected (0.001 sec)

查看当前登录数据库的用户

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

查看当前用户的数据库

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

退出使用xixi用户登录数据库

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

查看数据库

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

退出用root用户登录数据库给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)

xixi用户重新登录数据库

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

查看

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)

测试插入权限

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)

测试更新权限

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)

测试删除权限

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)

六、备份和还原

对数据进行备份

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

root用户登录数据库删除表

[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

退出进行还原操作

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

重新使用root登录数据库，并查看表是否还原

[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)

七、设置字符集

设置字符集一般有两种方法，一种是在创建表的时候设置字符集，另一种是表建成之后修改字符集。

1.创建时指定字符集

创建库的时候指定字符集：
语法：create database 库名 default character set=字符集；
create database db2 default character set=utf8

创建表的时候指定字符集：
语法：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.修改字符集

修改全局字符集

/建立连接使用的编码/
set character_set_connection=utf8;
/数据库的编码/
set character_set_database=utf8;
/结果集的编码/
set character_set_results=utf8;
/数据库服务器的编码/
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

修改库的字符集

语法：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) 

修改表的字符集

语法：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   #原字符集
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   #修改后的字符集
1 row in set (0.00 sec) 

修改字段的字符集

语法：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)

八、案例

1、创建一个表

安装数据库（系统默认已经安装，如果未安装，命令如下）

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

启动数据库服务

[root@redhat ~]# systemctl restart mariadb

初始化数据库，并设置root密码

[root@redhat ~]# mysql_secure_installation

登录数据库

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

创建数据库

mariadb [(none)]> create database openlab;

进入openlab数据库

mariadb [(none)]> use openlab;

创建student表

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

查看表

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)

向表中插入数据

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)

查看表中的内容

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、查询年龄为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、查询班里名为王凯的男生相关的信息

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、更改马博的birth为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、删除编号为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、列出该表中所有学生的姓名

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

7、列出编号为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)

到此这篇关于关于linux的mariadb数据库的文章就介绍到这了,更多相关linux mariadb数据库内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！