溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
Mariadb概述：
MariaDB數據庫管理系統是MySQL的一個分支，主要由開源社區在維護，采用GPL授權許可。開發這個分支的原因之一是：甲骨文公司收購了MySQL后，有將MySQL閉源的潛在風險，因此社區采用分支的方式來避開這個風險。
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能輕松成為MySQL的代替品。在存儲引擎方面，10.0.9版起使用XtraDB（名稱代號為Aria）來代替MySQL的InnoDB。
MariaDB由MySQL的創始人麥克爾·維德紐斯主導開發，他早前曾以10億美元的價格，將自己創建的公司MySQL AB賣給了SUN，此后，隨著SUN被甲骨文收購，MySQL的所有權也落入Oracle的手中。MariaDB名稱來自麥克爾·維德紐斯的女兒瑪麗亞（英語：Maria）的名字。
Mariadb的基礎概念：
◆數據類型：
作用：存儲格式、數據范圍、所能參與的運算、排序方式
字符型：
定長字符型：CHAR(#)，BIARNY(#)
變長字符型：VARCHAR(#)，VARBINARY(#)
對象存儲：
TEXT
BLOB
內建類型：ENUM，SET
數值型：
精確數值型：
INT：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
近似數值型：
FLOAT
DOUBLE
日期時間型：
DATE
TIME
DATETIME
TIMESTAMP
YEAR
修飾符：NOT NULL， DEFAULT，
類型符：UNSIGNED
◆數據庫的設計范式：
第一范式：字段是原子性的；
第二范式：存在可用的主鍵；
第三范式：任何字段都不應該依賴于其它表的非主鍵字段；
◆數據約束：向數據表中插入數據時要遵守的限制規則
主鍵：primary key，表上一個或多個字段的組合，填入主鍵字段中的數據，必須不同于已經存在的其它行的相同字段上的數據，而且也不能為空；一個表只能存一個主鍵；
惟一鍵：unique key，表上一個或多個字段的組合，填入其中字段中的數據，必須不同于已經存在的其它行的相同字段上的數據，但可以為空；一個表可以有多個惟鍵；
外鍵：foreign key，一個表中的外鍵字段中所能夠插入的數據取值范圍，取決于引用的另一個表上主鍵字段上的已經存在數據集合；
檢查條件約束：check，自定義的邏輯表達式；
命令行客戶端程序mysql：
mysql [OPTIONS] [database]
常用選項：
-uUSERNAME
-hHOST
-pPASSWORD
-Ddb_name
-S sock_file_path
-P port
-e 'STATEMENT'
[root@localhost ~]# mysql -uroot -h227.0.0.1 #使用root用戶在本機登錄 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
命令：
客戶端命令：在客戶端執行；
mysql> help
\?：獲取可用的命令幫助；
\q：退出客戶端程序；
\d CHAR：自定義語句結束符；
\g：語句結束標記；
\G：語句結束標記，豎排顯示結果；
\! SHELL_CMD：運行shell命令；
\s：當前連接及服務器相關的狀態信息；
\.
MariaDB [(none)]> help General information about MariaDB can be found at http://mariadb.org List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents'
服務端命令：SQL語句，發往服務端運行，并取回結果；需要顯式的語句結束符；
DDL：數據定義語言，主要用于數據庫組件，例如數據庫、表、索引、視圖、觸發器、事件調度器、存儲過程、存儲函數；
CREATE， ALTER， DROP
DML：數據操縱語言，CRUD操作，主要用于操作表中的數據；
INSERT，DELETE，UPDATE，SELECT
DCL：數據控制語言
GRANT, REVOKE
Mariadb的SQL語句：
◆獲取幫助：
mysql> help KEYWORD
mysql> help contents
◆數據庫管理：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
相關命令：
SHOW CHARACTER SET
SHOW COLLATION
SHOW CREATE DATABASE db_name
MariaDB [(none)]> SHOW CREATE DATABASE xiaoshui; +----------+---------------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------------+ | xiaoshui | CREATE DATABASE `xiaoshui` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+---------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> ALTER DATABASE xiaoshui CHARACTER SET 'utf8' -> ; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> SHOW CREATE DATABASE xiaoshui; +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | xiaoshui | CREATE DATABASE `xiaoshui` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec)
◆表管理：
表創建：
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]
create_definition:由逗號分隔的列表
字段定義：
column_name column_defination
約束定義：
PRIMARY KEY(col1[,col2, ....])
UNIQUE KEY
FOREIGN KEY
CHECK(expr)
索引定義：
{INDEX|KEY}
{FULLTEXT|SPATIAL}
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
MariaDB [xiaoshui]> CREATE TABLE tbl1 (id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMgender ENUM('F','M') DEFAULT 'M',UNIQUE KEY(name,gender),INDEX(name));
Query OK, 0 rows affected (0.06 sec)
MariaDB [xiaoshui]> DESC tbl;
ERROR 1146 (42S02): Table 'xiaoshui.tbl' doesn't exist
MariaDB [xiaoshui]> DESC tbl1;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | YES | | M | |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
table_option:
ENGINE [=] engine_name
查看數據庫支持的存儲引擎種類：
SHOW ENGINES;
查看表狀態信息：
SHOW TABLE STATUS [WHERE CLAUSE] [LIKE CLAUSE]
MariaDB [xiaoshui]> show TABLE STATUS\G *************************** 1. row *************************** Name: tbl1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 32768 Data_free: 0 Auto_increment: 1 Create_time: 2016-10-15 14:27:05 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
表修改：
ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]
alter_specification:
(1) 表選項
ENGINE=engine_name
...
(2) 表定義
(a) 字段
ADD
DROP
CHANGE
MODIFY
(b) 鍵和索引
ADD {PRIMARY|UNIQUE|FOREIGN} key (col1, col2, ...)
ADD INDEX(col1, col2, ...)
DROP {PRIMARY|UNIQUE|FOREIGN} KEY key_name；
DROP INDEX index_name；
查看表上的索引信息：
SHOW INDEXES FROM tbl_name；
實例：
#刪除索引
MariaDB [xiaoshui]> ALTER TABLE tbl1 DROP INDEX name_2;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#添加字段ClassID字段（默認在最后一條）
MariaDB [xiaoshui]> ALTER TABLE tbl1 ADD classID TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [xiaoshui]> DESC tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | YES | | M | |
| classID | tinyint(3) unsigned | NO | | NULL | |
+---------+----------------------+------+-----+---------+----------------+
#調整ClassID到age字段后面
MariaDB [xiaoshui]> ALTER TABLE tbl1 MODIFY ClassID TINYINT UNSIGNED NOT NULL after age;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [xiaoshui]> DESC tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| ClassID | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('F','M') | YES | | M | |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)表刪除：
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
查看表創建語句：
SHOW CREATE TABLE tbl_name
索引管理：
創建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
刪除：
DROP INDEX index_name ON tbl_name
查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
實例：
#刪除tbl1上的name字段的索引 MariaDB [xiaoshui]> DROP INDEX name ON tbl1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 #為tbl1上的name和gender字段添加索引 MariaDB [xiaoshui]> CREATE INDEX name_and_gender ON tbl1(name(5),gender); Query OK, 0 rows affected (0.51 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [xiaoshui]> SHOW INDEXES FROM tbl1; +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | tbl1 | 1 | name_and_gender | 1 | name | A | 0 | 5 | NULL | | BTREE | | | | tbl1 | 1 | name_and_gender | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | | | +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
DML語句：
INSERT，SELECT，DELETE，UPDATE
INSERT：
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
SELECT：
(1) SELECT * FROM tbl_name;
(2) SELECT col1, col2, ... FROM tbl_name;
字段別名：col1 AS ALIAS
(3) ELECT col1, col2, ... FROM tbl_name WHERE CLUASE;
WHERE expr：布爾表達式；
col_name OPERATOR value|col_name;
操作符：
>, <, <=, >=, =, !=
BETWEEN ... AND ...
LIKE 'PATTERN'：
通配符：
_：匹配任意單個字符；
%：任意長度的任意字符；
RLIKE 'PATTERN':
IN(list)
組合條件：
and, or, not
(4) SELECT col1, ... FROM tbl1_name [WEHRE CLAUSE] ORDER BY col1, col2, ... [ASC|DESC]
DELETE：
DELETE FROM tbl_name
[WHERE where_condition]
UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
實例：
#給tbl1插入數據，name和ClassID，因為gender的默認值為M
MariaDB [xiaoshui]> INSERT INTO tbl1 (name,ClassID) VALUE ('tom',1);
Query OK, 1 row affected (0.05 sec)
MariaDB [xiaoshui]> SELECT * FROM tbl1;
+----+------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
+----+------+------+---------+--------+
1 row in set (0.00 sec)
#插入多條數據
MariaDB [xiaoshui]> INSERT INTO tbl1 (name,ClassID) VALUES ('xiaoshui',2),('zhao',1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [xiaoshui]> SELECT * FROM tbl1;
+----+----------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+----------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
| 2 | xiaoshui | NULL | 2 | M |
| 3 | zhao | NULL | 1 | M |
+----+----------+------+---------+--------+
3 rows in set (0.00 sec)
#如果不指定字段，則要對應填上每個字段的值
MariaDB [xiaoshui]> SELECT * FROM tbl1;
+----+----------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+----------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
| 2 | xiaoshui | NULL | 2 | M |
| 3 | zhao | NULL | 1 | M |
| 4 | bla | 22 | 1 | F |
+----+----------+------+---------+--------+
4 rows in set (0.00 sec)
#查詢表中的所有數據
MariaDB [xiaoshui]> SELECT * FROM tbl1;
+----+----------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+----------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
| 2 | xiaoshui | NULL | 2 | M |
| 3 | zhao | NULL | 1 | M |
| 4 | bla | 22 | 1 | F |
+----+----------+------+---------+--------+
#查詢所需要的字段
MariaDB [xiaoshui]> SELECT name,gender FROM tbl1;
+----------+--------+
| name | gender |
+----------+--------+
| tom | M |
| xiaoshui | M |
| zhao | M |
| bla | F |
+----------+--------+
4 rows in set (0.00 sec)
#利用where語句條件性查找
MariaDB [xiaoshui]> SELECT name FROM tbl1 WHERE name LIKE '%o%';
+----------+
| name |
+----------+
| tom |
| xiaoshui |
| zhao |
+----------+
3 rows in set (0.00 sec)
#利用RLIKE正則表達式來匹配內容
MariaDB [xiaoshui]> SELECT name FROM tbl1 WHERE name RLIKE '^.*o.*$';
+----------+
| name |
+----------+
| tom |
| xiaoshui |
| zhao |
+----------+
3 rows in set (0.00 sec)
#使用IN的范圍性作為查找條件
MariaDB [xiaoshui]> SELECT name,ClassID FROM tbl1 WHERE ClassID IN (1,2);
+----------+---------+
| name | ClassID |
+----------+---------+
| tom | 1 |
| xiaoshui | 2 |
| zhao | 1 |
| bla | 1 |
+----------+---------+
4 rows in set (0.00 sec)
#通過id逆序排序
MariaDB [xiaoshui]> SELECT * FROM tbl1 ORDER BY id DESC;
+----+----------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+----------+------+---------+--------+
| 4 | bla | 22 | 1 | F |
| 3 | zhao | NULL | 1 | M |
| 2 | xiaoshui | NULL | 2 | M |
| 1 | tom | NULL | 1 | M |
+----+----------+------+---------+--------+
4 rows in set (0.00 sec)
#先通過ClassID排序，如果ClassID相同，在通過name排序
MariaDB [xiaoshui]> SELECT * FROM tbl1 ORDER BY ClassID,name;
+----+----------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+----------+------+---------+--------+
| 4 | bla | 22 | 1 | F |
| 1 | tom | NULL | 1 | M |
| 3 | zhao | NULL | 1 | M |
| 2 | xiaoshui | NULL | 2 | M |
+----+----------+------+---------+--------+
4 rows in set (0.00 sec)DELETE：
DELETE FROM tbl_name
[WHERE where_condition]
#刪除id=4的行 MariaDB [xiaoshui]> DELETE FROM tbl1 WHERE id=4; Query OK, 1 row affected (0.01 sec) MariaDB [xiaoshui]> SELECT * FROM tbl; ERROR 1146 (42S02): Table 'xiaoshui.tbl' doesn't exist MariaDB [xiaoshui]> SELECT * FROM tbl1; +----+----------+------+---------+--------+ | id | name | age | ClassID | gender | +----+----------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | xiaoshui | NULL | 2 | M | | 3 | zhao | NULL | 1 | M | +----+----------+------+---------+--------+ 3 rows in set (0.00 sec)
UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
#設置id的2的行age=17 MariaDB [xiaoshui]> UPDATE tbl1 SET age=17 WHERE id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [xiaoshui]> SELECT * FROM tbl1 -> ; +----+----------+------+---------+--------+ | id | name | age | ClassID | gender | +----+----------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | xiaoshui | 17 | 2 | M | | 3 | zhao | NULL | 1 | M | +----+----------+------+---------+--------+ 3 rows in set (0.00 sec) MariaDB [xiaoshui]>
謝謝瀏覽...
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
