免费视频一区二区,一区2区3区在线看,久久国产精品成人免费观看的软件

主頁 > 知識庫 > 關(guān)于Linux的mariadb數(shù)據(jù)庫

關(guān)于Linux的mariadb數(shù)據(jù)庫

關(guān)于Linux的mariadb數(shù)據(jù)庫

一、什么是數(shù)據(jù)庫（DATABASE）

高效的存儲和處理數(shù)據(jù)的介質(zhì)（磁盤和內(nèi)存）
是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的建立在計算機(jī)存儲設(shè)備上的倉庫。
簡單來說是本身可視為電子化的文件柜——存儲電子文件的處所，用戶可以對文件中的數(shù)據(jù)進(jìn)行新增、截取、更新、刪除等操作。

二、數(shù)據(jù)庫的分類

按存儲介質(zhì)分為:關(guān)系型數(shù)據(jù)庫（sql）、非關(guān)系型數(shù)據(jù)庫(nosql)

1、關(guān)系型數(shù)據(jù)庫（sql）

是指采用了關(guān)系模型來組織數(shù)據(jù)的數(shù)據(jù)庫，其以行和列的形式存儲數(shù)據(jù)，以便于用戶理解，關(guān)系型數(shù)據(jù)庫這一系列的行和列被稱為表，一組表組成了數(shù)據(jù)庫。用戶通過查詢來檢索數(shù)據(jù)庫中的數(shù)據(jù)，而查詢是一個用于限定數(shù)據(jù)庫中某些區(qū)域的執(zhí)行代碼。關(guān)系模型可以簡單理解為二維表格模型，而一個關(guān)系型數(shù)據(jù)庫就是由二維表及其之間的關(guān)系組成的一個數(shù)據(jù)組織。

優(yōu)點：

容易理解：二維表結(jié)構(gòu)是非常貼近邏輯世界的一個概念，關(guān)系模型相對網(wǎng)狀、層次等其他模型來說更容易理解。
使用方便：通用的SQL語言使得操作關(guān)系型數(shù)據(jù)庫非常方便。
易于維護(hù)：豐富的完整性(實體完整性、參照完整性和用戶定義的完整性)大大減低了數(shù)據(jù)冗余和數(shù)據(jù)不一致的概率。

2、非關(guān)系型數(shù)據(jù)庫(nosql)

NoSQL最常見的解釋是“non-relational”， “Not Only SQL”也被很多人接受。NoSQL僅僅是一個概念，泛指非關(guān)系型的數(shù)據(jù)庫，區(qū)別于關(guān)系數(shù)據(jù)庫，它們不保證關(guān)系數(shù)據(jù)的ACID特性。NoSQL是一項全新的數(shù)據(jù)庫革命性運(yùn)動，其擁護(hù)者們提倡運(yùn)用非關(guān)系型的數(shù)據(jù)存儲，相對于鋪天蓋地的關(guān)系型數(shù)據(jù)庫運(yùn)用，這一概念無疑是一種全新的思維的注入。

優(yōu)點：

易擴(kuò)展
大數(shù)據(jù)量，高性能
靈活的數(shù)據(jù)模型
高可用

三、DML（data manipulation language）數(shù)據(jù)操縱語言

主要有以下對數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行一些操作
select查詢
select 列名稱 from 表名稱

update更新
update 表名 set 更新的數(shù)據(jù) where 條件

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

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

四、DDL（data definition language）數(shù)據(jù)庫定義語言

DDL主要是用在定義或改變表的結(jié)構(gòu)，數(shù)據(jù)類型，表之間的鏈接和約束等初始化工作上
比如：

create創(chuàng)建

創(chuàng)建表

create      table      表名稱
(
列名稱1      數(shù)據(jù)類型,
列名稱2      數(shù)據(jù)類型,
列名稱3      數(shù)據(jù)類型,
…
);

創(chuàng)建數(shù)據(jù)庫
create      database      數(shù)據(jù)庫名

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

drop刪除
drop table 表名稱;
drop database 數(shù)據(jù)庫名稱;

五、DCL（Data Control Language）數(shù)據(jù)庫控制語言

是用來設(shè)置或更改數(shù)據(jù)庫用戶或角色權(quán)限的語句，包括（grant,revoke等）語句。這個比較少用到。

1、mariadb

MariaDB數(shù)據(jù)庫管理系統(tǒng)是MySQL的一個分支，主要由開源社區(qū)在維護(hù)，采用GPL授權(quán)許可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能輕松成為MySQL的代替品。在存儲引擎方面，使用XtraDB（英語：XtraDB）來代替MySQL的InnoDB。 MariaDB由MySQL的創(chuàng)始人Michael Widenius（英語：Michael Widenius）主導(dǎo)開發(fā)，他早前曾以10億美元的價格，將自己創(chuàng)建的公司MySQL AB賣給了SUN，此后，隨著SUN被甲骨文收購，MySQL的所有權(quán)也落入Oracle的手中。MariaDB名稱來自Michael Widenius的女兒Maria的名字。

2、用戶的管理和訪問權(quán)限控制

創(chuàng)建數(shù)據(jù)庫登錄用戶

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

查看當(dāng)前登錄數(shù)據(jù)庫的用戶

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

查看當(dāng)前用戶的數(shù)據(jù)庫

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

退出使用xixi用戶登錄數(shù)據(jù)庫

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

查看數(shù)據(jù)庫

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

退出用root用戶登錄數(shù)據(jù)庫給xixi用戶設(shè)置權(quán)限

[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用戶重新登錄數(shù)據(jù)庫

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

測試插入權(quán)限

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)

測試更新權(quán)限

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)

測試刪除權(quán)限

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)

六、備份和還原

對數(shù)據(jù)進(jìn)行備份

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

root用戶登錄數(shù)據(jù)庫刪除表

[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

退出進(jìn)行還原操作

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

重新使用root登錄數(shù)據(jù)庫，并查看表是否還原

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

七、設(shè)置字符集

設(shè)置字符集一般有兩種方法，一種是在創(chuàng)建表的時候設(shè)置字符集，另一種是表建成之后修改字符集。

1.創(chuàng)建時指定字符集

創(chuàng)建庫的時候指定字符集：
語法：create database 庫名 default character set=字符集； create database db2 default character set=utf8

創(chuàng)建表的時候指定字符集：
語法：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;
/數(shù)據(jù)庫的編碼/
set character_set_database=utf8;
/結(jié)果集的編碼/
set character_set_results=utf8;
/數(shù)據(jù)庫服務(wù)器的編碼/
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、創(chuàng)建一個表

安裝數(shù)據(jù)庫（系統(tǒng)默認(rèn)已經(jīng)安裝，如果未安裝，命令如下）

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

啟動數(shù)據(jù)庫服務(wù)

[root@redhat ~]# systemctl restart mariadb

初始化數(shù)據(jù)庫，并設(shè)置root密碼

[root@redhat ~]# mysql_secure_installation

登錄數(shù)據(jù)庫

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

創(chuàng)建數(shù)據(jù)庫

MariaDB [(none)]> create database openlab;

進(jìn)入openlab數(shù)據(jù)庫

MariaDB [(none)]> use openlab;

創(chuàng)建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)

向表中插入數(shù)據(jù)

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)

查看表中的內(nèi)容

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的所有學(xué)生

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、查詢班里名為王凱的男生相關(guān)的信息

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的學(xué)生

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、列出該表中所有學(xué)生的姓名

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

7、列出編號為3的學(xué)生姓名及年齡

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

到此這篇關(guān)于關(guān)于Linux的mariadb數(shù)據(jù)庫的文章就介紹到這了,更多相關(guān)Linux mariadb數(shù)據(jù)庫內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

標(biāo)簽：蕪湖泰州松原慶陽那曲朝陽銅川周口

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《關(guān)于Linux的mariadb數(shù)據(jù)庫》，本文關(guān)鍵詞關(guān)于,Linux,的,mariadb,數(shù)據(jù)庫,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。