關(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ì)算機(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)點(diǎn)：
- 容易理解：二維表結(jié)構(gòu)是非常貼近邏輯世界的一個概念，關(guān)系模型相對網(wǎng)狀、層次等其他模型來說更容易理解。
- 使用方便：通用的SQL語言使得操作關(guān)系型數(shù)據(jù)庫非常方便。
- 易于維護(hù)：豐富的完整性(實(shí)體完整性、參照完整性和用戶定義的完整性)大大減低了數(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是一項(xiàng)全新的數(shù)據(jù)庫革命性運(yùn)動，其擁護(hù)者們提倡運(yùn)用非關(guān)系型的數(shù)據(jù)存儲，相對于鋪天蓋地的關(guān)系型數(shù)據(jù)庫運(yùn)用，這一概念無疑是一種全新的思維的注入。
優(yōu)點(diǎn)：
- 易擴(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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Linux下重啟oracle服務(wù)及監(jiān)聽器和實(shí)例詳解
在Linux下重啟Oracle數(shù)據(jù)庫及監(jiān)聽器和實(shí)例，本文通過三種方法給大家實(shí)例詳解的，介紹的非常詳細(xì)，具有參考借鑒價值，感興趣的朋友一起看看吧2016-11-11
Apache由http自動跳轉(zhuǎn)到https的多種方法
本文主要和大家分享Apache http自動跳轉(zhuǎn)到https的幾種方法，非常不錯，具有參考借鑒價值，需要的朋友參考下2018-02-02
linux下查看內(nèi)存條數(shù)及每根內(nèi)存大小的實(shí)現(xiàn)方法(推薦)
下面小編就為大家?guī)硪黄猯inux下查看內(nèi)存條數(shù)及每根內(nèi)存大小的實(shí)現(xiàn)方法(推薦)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-11-11
CentOS 7.6安裝MySQL 5.7 GA版的教程圖解
本文通過圖文并茂的形式給大家介紹了CentOS 7.6安裝MySQL 5.7 GA版,需要的朋友可以參考下2019-09-09
使用多線程的理由之一是和進(jìn)程相比，它是一種非?！肮?jié)儉”的多任務(wù)操作方式。我們知道，在Linux系統(tǒng)下，啟動一個新的進(jìn)程必須分配給它獨(dú)立的地址空間，建立眾多的數(shù)據(jù)表來維護(hù)它的代碼段、堆棧段和數(shù)據(jù)段，這是一種“昂貴”的多任務(wù)工作方式。2014-08-08
Linux系統(tǒng)下如何搭建luarocks環(huán)境
這篇文章主要介紹了Linux系統(tǒng)下如何搭建luarocks環(huán)境問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
這篇文章主要介紹了Linux系統(tǒng)三種模式下的簡單命令，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2018-07-07
這篇文章主要為大家詳細(xì)介紹了Linux查看系統(tǒng)配置常用命令，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-12-12
這篇文章主要介紹了linux中把.c的文件編譯成.so文件的相關(guān)資料，本文以編譯mylib.c為例講如何編譯.so文件，感興趣的朋友跟隨腳本之家小編一起學(xué)習(xí)吧2018-05-05
xargs是給命令傳遞參數(shù)的一個過濾器，也是組合多個命令的一個工具。下面這篇文章主要給大家介紹了關(guān)于linux中xargs命令用法的相關(guān)資料，需要的朋友可以參考借鑒，下面來跟著小編一起看看吧。2017-05-05