目錄
- 關(guān)于Linux的mariadb數(shù)據(jù)庫
- 一、什么是數(shù)據(jù)庫（DATABASE）
- 二、數(shù)據(jù)庫的分類
- 1、關(guān)系型數(shù)據(jù)庫（sql）
- 2、非關(guān)系型數(shù)據(jù)庫(nosql)
- 三、DML（data manipulation language）數(shù)據(jù)操縱語言
- 四、DDL（data definition language）數(shù)據(jù)庫定義語言
- 五、DCL（Data Control Language）數(shù)據(jù)庫控制語言
- 1、mariadb
- 2、用戶的管理和訪問權(quán)限控制
- 六、備份和還原
- 七、設(shè)置字符集
- 1.創(chuàng)建時指定字符集
- 2.修改字符集
- 八、案例
- 2、查詢年齡為20的所有學(xué)生
- 3、查詢班里名為王凱的男生相關(guān)的信息
- 4、更改馬博的birth為1998-7-7
- 5、刪除編號為4的學(xué)生
- 6、列出該表中所有學(xué)生的姓名
- 7、列出編號為3的學(xué)生姓名及年齡
關(guān)于Linux的mariadb數(shù)據(jù)庫
一、什么是數(shù)據(jù)庫（DATABASE）
高效的存儲和處理數(shù)據(jù)的介質(zhì)（磁盤和內(nèi)存）
是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的建立在計算機存儲設(shè)備上的倉庫。
簡單來說是本身可視為電子化的文件柜——存儲電子文件的處所，用戶可以對文件中的數(shù)據(jù)進行新增、截取、更新、刪除等操作。
二、數(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ù)庫非常方便。
- 易于維護：豐富的完整性(實體完整性、參照完整性和用戶定義的完整性)大大減低了數(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ù)庫革命性運動，其擁護者們提倡運用非關(guān)系型的數(shù)據(jù)存儲，相對于鋪天蓋地的關(guān)系型數(shù)據(jù)庫運用，這一概念無疑是一種全新的思維的注入。
優(yōu)點：
- 易擴展
- 大數(shù)據(jù)量，高性能
- 靈活的數(shù)據(jù)模型
- 高可用
三、DML（data manipulation language）數(shù)據(jù)操縱語言
主要有以下對數(shù)據(jù)庫的數(shù)據(jù)進行一些操作
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ū)在維護，采用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ù)進行備份
[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
退出進行還原操作
[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)默認已經(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;
進入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)文章希望大家以后多多支持腳本之家！
