目錄
- 關于Linux的mariadb數據庫
- 一、什么是數據庫（DATABASE）
- 二、數據庫的分類
- 1、關系型數據庫（sql）
- 2、非關系型數據庫(nosql)
- 三、DML（data manipulation language）數據操縱語言
- 四、DDL（data definition language）數據庫定義語言
- 五、DCL（Data Control Language）數據庫控制語言
- 六、備份和還原
- 七、設置字符集
- 八、案例
- 2、查詢年齡為20的所有學生
- 3、查詢班里名為王凱的男生相關的信息
- 4、更改馬博的birth為1998-7-7
- 5、刪除編號為4的學生
- 6、列出該表中所有學生的姓名
- 7、列出編號為3的學生姓名及年齡
關于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數據庫內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
