關(guān)于Linux的mariadb數(shù)據(jù)庫(kù)
關(guān)于Linux的mariadb數(shù)據(jù)庫(kù)
一、什么是數(shù)據(jù)庫(kù)（DATABASE）
高效的存儲(chǔ)和處理數(shù)據(jù)的介質(zhì)（磁盤(pán)和內(nèi)存）
是按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的建立在計(jì)算機(jī)存儲(chǔ)設(shè)備上的倉(cāng)庫(kù)。
簡(jiǎn)單來(lái)說(shuō)是本身可視為電子化的文件柜——存儲(chǔ)電子文件的處所，用戶(hù)可以對(duì)文件中的數(shù)據(jù)進(jìn)行新增、截取、更新、刪除等操作。
二、數(shù)據(jù)庫(kù)的分類(lèi)
按存儲(chǔ)介質(zhì)分為:關(guān)系型數(shù)據(jù)庫(kù)（sql）、非關(guān)系型數(shù)據(jù)庫(kù)(nosql)
1、關(guān)系型數(shù)據(jù)庫(kù)（sql）
是指采用了關(guān)系模型來(lái)組織數(shù)據(jù)的數(shù)據(jù)庫(kù)，其以行和列的形式存儲(chǔ)數(shù)據(jù)，以便于用戶(hù)理解，關(guān)系型數(shù)據(jù)庫(kù)這一系列的行和列被稱(chēng)為表，一組表組成了數(shù)據(jù)庫(kù)。用戶(hù)通過(guò)查詢(xún)來(lái)檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)，而查詢(xún)是一個(gè)用于限定數(shù)據(jù)庫(kù)中某些區(qū)域的執(zhí)行代碼。關(guān)系模型可以簡(jiǎn)單理解為二維表格模型，而一個(gè)關(guān)系型數(shù)據(jù)庫(kù)就是由二維表及其之間的關(guān)系組成的一個(gè)數(shù)據(jù)組織。
優(yōu)點(diǎn)：
- 容易理解：二維表結(jié)構(gòu)是非常貼近邏輯世界的一個(gè)概念，關(guān)系模型相對(duì)網(wǎng)狀、層次等其他模型來(lái)說(shuō)更容易理解。
- 使用方便：通用的SQL語(yǔ)言使得操作關(guān)系型數(shù)據(jù)庫(kù)非常方便。
- 易于維護(hù)：豐富的完整性(實(shí)體完整性、參照完整性和用戶(hù)定義的完整性)大大減低了數(shù)據(jù)冗余和數(shù)據(jù)不一致的概率。
2、非關(guān)系型數(shù)據(jù)庫(kù)(nosql)
NoSQL最常見(jiàn)的解釋是“non-relational”， “Not Only SQL”也被很多人接受。NoSQL僅僅是一個(gè)概念，泛指非關(guān)系型的數(shù)據(jù)庫(kù)，區(qū)別于關(guān)系數(shù)據(jù)庫(kù)，它們不保證關(guān)系數(shù)據(jù)的ACID特性。NoSQL是一項(xiàng)全新的數(shù)據(jù)庫(kù)革命性運(yùn)動(dòng)，其擁護(hù)者們提倡運(yùn)用非關(guān)系型的數(shù)據(jù)存儲(chǔ)，相對(duì)于鋪天蓋地的關(guān)系型數(shù)據(jù)庫(kù)運(yùn)用，這一概念無(wú)疑是一種全新的思維的注入。
優(yōu)點(diǎn)：
- 易擴(kuò)展
- 大數(shù)據(jù)量，高性能
- 靈活的數(shù)據(jù)模型
- 高可用
三、DML（data manipulation language）數(shù)據(jù)操縱語(yǔ)言
主要有以下對(duì)數(shù)據(jù)庫(kù)的數(shù)據(jù)進(jìn)行一些操作
select查詢(xún)select 列名稱(chēng) from 表名稱(chēng)
update更新update 表名 set 更新的數(shù)據(jù) where 條件
insert插入insert into table_name (列1, 列2,…) values; (值1, 值2,…)
delete刪除delete from 表名稱(chēng) where 列名稱(chēng)=值
四、DDL（data definition language）數(shù)據(jù)庫(kù)定義語(yǔ)言
DDL主要是用在定義或改變表的結(jié)構(gòu)，數(shù)據(jù)類(lèi)型，表之間的鏈接和約束等初始化工作上
比如：
create創(chuàng)建
創(chuàng)建表
create table 表名稱(chēng)
(
列名稱(chēng)1 數(shù)據(jù)類(lèi)型,
列名稱(chēng)2 數(shù)據(jù)類(lèi)型,
列名稱(chēng)3 數(shù)據(jù)類(lèi)型,
…
);創(chuàng)建數(shù)據(jù)庫(kù)
create database 數(shù)據(jù)庫(kù)名
alter修改
alter table students change column birth birthday date;
alter table student rename students;
drop刪除
drop table 表名稱(chēng);
drop database 數(shù)據(jù)庫(kù)名稱(chēng);
五、DCL（Data Control Language）數(shù)據(jù)庫(kù)控制語(yǔ)言
是用來(lái)設(shè)置或更改數(shù)據(jù)庫(kù)用戶(hù)或角色權(quán)限的語(yǔ)句，包括（grant,revoke等）語(yǔ)句。這個(gè)比較少用到。
1、mariadb
MariaDB數(shù)據(jù)庫(kù)管理系統(tǒng)是MySQL的一個(gè)分支，主要由開(kāi)源社區(qū)在維護(hù)，采用GPL授權(quán)許可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能輕松成為MySQL的代替品。在存儲(chǔ)引擎方面，使用XtraDB（英語(yǔ)：XtraDB）來(lái)代替MySQL的InnoDB。 MariaDB由MySQL的創(chuàng)始人Michael Widenius（英語(yǔ)：Michael Widenius）主導(dǎo)開(kāi)發(fā)，他早前曾以10億美元的價(jià)格，將自己創(chuàng)建的公司MySQL AB賣(mài)給了SUN，此后，隨著SUN被甲骨文收購(gòu)，MySQL的所有權(quán)也落入Oracle的手中。MariaDB名稱(chēng)來(lái)自Michael Widenius的女兒Maria的名字。
2、用戶(hù)的管理和訪問(wèn)權(quán)限控制
創(chuàng)建數(shù)據(jù)庫(kù)登錄用戶(hù)
MariaDB [openlab]> create user xixi@localhost identified by 'xixi'; Query OK, 0 rows affected (0.001 sec)
查看當(dāng)前登錄數(shù)據(jù)庫(kù)的用戶(hù)
MariaDB [openlab]> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.000 sec)
查看當(dāng)前用戶(hù)的數(shù)據(jù)庫(kù)
MariaDB [openlab]> select database(); +------------+ | database() | +------------+ | openlab | +------------+ 1 row in set (0.000 sec)
退出使用xixi用戶(hù)登錄數(shù)據(jù)庫(kù)
[root@redhat ~]# mysql -uxixi -pxixi
查看數(shù)據(jù)庫(kù)
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.001 sec)
退出用root用戶(hù)登錄數(shù)據(jù)庫(kù)給xixi用戶(hù)設(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用戶(hù)重新登錄數(shù)據(jù)庫(kù)
[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)
測(cè)試插入權(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)
測(cè)試更新權(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)
測(cè)試刪除權(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)
六、備份和還原
對(duì)數(shù)據(jù)進(jìn)行備份
[root@redhat ~]# mysqldump -u root -p openlab > /openlab_backup_20210904.dump Enter password:
root用戶(hù)登錄數(shù)據(jù)庫(kù)刪除表
[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ù)庫(kù)，并查看表是否還原
[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í)候設(shè)置字符集，另一種是表建成之后修改字符集。
1.創(chuàng)建時(shí)指定字符集
創(chuàng)建庫(kù)的時(shí)候指定字符集：語(yǔ)法：create database 庫(kù)名 default character set=字符集；
create database db2 default character set=utf8
創(chuàng)建表的時(shí)候指定字符集：語(yǔ)法：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ù)庫(kù)的編碼/ set character_set_database=utf8; /結(jié)果集的編碼/ set character_set_results=utf8; /數(shù)據(jù)庫(kù)服務(wù)器的編碼/ set character_set_server=utf8; set character_set_system=utf8; set collation_connection=utf8; set collation_database=utf8; set collation_server=utf8;
修改庫(kù)的字符集
語(yǔ)法：alter database 庫(kù)名 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)
修改表的字符集
語(yǔ)法：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)
修改字段的字符集
語(yǔ)法：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)建一個(gè)表
安裝數(shù)據(jù)庫(kù)（系統(tǒng)默認(rèn)已經(jīng)安裝，如果未安裝，命令如下）
[root@redhat ~]# yum install mariadb -y
啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)
[root@redhat ~]# systemctl restart mariadb
初始化數(shù)據(jù)庫(kù)，并設(shè)置root密碼
[root@redhat ~]# mysql_secure_installation
登錄數(shù)據(jù)庫(kù)
[root@redhat ~]# mysql -uroot -proot
創(chuàng)建數(shù)據(jù)庫(kù)
MariaDB [(none)]> create database openlab;
進(jìn)入openlab數(shù)據(jù)庫(kù)
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、查詢(xún)年齡為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、查詢(xún)班里名為王凱的男生相關(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、刪除編號(hào)為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、列出編號(hào)為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ù)庫(kù)的文章就介紹到這了,更多相關(guān)Linux mariadb數(shù)據(jù)庫(kù)內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信