Mariadb的基礎(chǔ)應(yīng)用

Mariadb概述：

    MariaDB數(shù)據(jù)庫(kù)管理系統(tǒng)是MySQL的一個(gè)分支，主要由開(kāi)源社區(qū)在維護(hù)，采用GPL授權(quán)許可。開(kāi)發(fā)這個(gè)分支的原因之一是：甲骨文公司收購(gòu)了MySQL后，有將MySQL閉源的潛在風(fēng)險(xiǎn)，因此社區(qū)采用分支的方式來(lái)避開(kāi)這個(gè)風(fēng)險(xiǎn)。

MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能輕松成為MySQL的代替品。在存儲(chǔ)引擎方面，10.0.9版起使用XtraDB（名稱(chēng)代號(hào)為Aria）來(lái)代替MySQL的InnoDB。

MariaDB由MySQL的創(chuàng)始人麥克爾·維德紐斯主導(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)自麥克爾·維德紐斯的女兒瑪麗亞（英語(yǔ)：Maria）的名字。

Mariadb的基礎(chǔ)概念：

◆數(shù)據(jù)類(lèi)型：
    作用：存儲(chǔ)格式、數(shù)據(jù)范圍、所能參與的運(yùn)算、排序方式
    
    字符型：
        定長(zhǎng)字符型：CHAR(#)，BIARNY(#)
        變長(zhǎng)字符型：VARCHAR(#)，VARBINARY(#)
        對(duì)象存儲(chǔ)：
            TEXT
            BLOB
        內(nèi)建類(lèi)型：ENUM，SET
    數(shù)值型：
        精確數(shù)值型：
            INT：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
        近似數(shù)值型：
            FLOAT
            DOUBLE
    日期時(shí)間型：
        DATE
        TIME
        DATETIME
        TIMESTAMP
        YEAR

    修飾符：NOT NULL， DEFAULT，
    類(lèi)型符：UNSIGNED
 

◆數(shù)據(jù)庫(kù)的設(shè)計(jì)范式：
    第一范式：字段是原子性的；
    第二范式：存在可用的主鍵；
    第三范式：任何字段都不應(yīng)該依賴(lài)于其它表的非主鍵字段；

◆數(shù)據(jù)約束：向數(shù)據(jù)表中插入數(shù)據(jù)時(shí)要遵守的限制規(guī)則
    主鍵：primary key，表上一個(gè)或多個(gè)字段的組合，填入主鍵字段中的數(shù)據(jù)，必須不同于已經(jīng)存在的其它行的相同字段上的數(shù)據(jù)，而且也不能為空；一個(gè)表只能存一個(gè)主鍵；
    惟一鍵：unique key，表上一個(gè)或多個(gè)字段的組合，填入其中字段中的數(shù)據(jù)，必須不同于已經(jīng)存在的其它行的相同字段上的數(shù)據(jù)，但可以為空；一個(gè)表可以有多個(gè)惟鍵；
    外鍵：foreign key，一個(gè)表中的外鍵字段中所能夠插入的數(shù)據(jù)取值范圍，取決于引用的另一個(gè)表上主鍵字段上的已經(jīng)存在數(shù)據(jù)集合；
    檢查條件約束：check，自定義的邏輯表達(dá)式；

命令行客戶(hù)端程序mysql：

mysql [OPTIONS] [database]
    常用選項(xiàng)：
        -uUSERNAME
        -hHOST
        -pPASSWORD
        
        -Ddb_name
        
        -S sock_file_path
        -P port
        
        -e 'STATEMENT'
 

[root@localhost ~]# mysql -uroot -h227.0.0.1    #使用root用戶(hù)在本機(jī)登錄
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)]>

       
命令：
    客戶(hù)端命令：在客戶(hù)端執(zhí)行；
        mysql> help
            \?：獲取可用的命令幫助；
            \q：退出客戶(hù)端程序；
            \d CHAR：自定義語(yǔ)句結(jié)束符；
            \g：語(yǔ)句結(jié)束標(biāo)記；
            \G：語(yǔ)句結(jié)束標(biāo)記，豎排顯示結(jié)果；
            \! SHELL_CMD：運(yùn)行shell命令；
            \s：當(dāng)前連接及服務(wù)器相關(guān)的狀態(tài)信息；
            \.
            

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'

    服務(wù)端命令：SQL語(yǔ)句，發(fā)往服務(wù)端運(yùn)行，并取回結(jié)果；需要顯式的語(yǔ)句結(jié)束符；
        DDL：數(shù)據(jù)定義語(yǔ)言，主要用于數(shù)據(jù)庫(kù)組件，例如數(shù)據(jù)庫(kù)、表、索引、視圖、觸發(fā)器、事件調(diào)度器、存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)；
            CREATE， ALTER， DROP
        DML：數(shù)據(jù)操縱語(yǔ)言，CRUD操作，主要用于操作表中的數(shù)據(jù)；
            INSERT，DELETE，UPDATE，SELECT
        DCL：數(shù)據(jù)控制語(yǔ)言
            GRANT, REVOKE

Mariadb的SQL語(yǔ)句：

◆獲取幫助：
    mysql> help KEYWORD
    mysql> help contents

◆數(shù)據(jù)庫(kù)管理：
    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
    
    相關(guān)命令：
        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)

◆表管理：
表創(chuàng)建：
    CREATE  TABLE [IF NOT EXISTS] tbl_name  (create_definition,...)  [table_options]

    create_definition:由逗號(hào)分隔的列表
        字段定義：
            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
        
    查看數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎種類(lèi)：
        SHOW ENGINES;
        
    查看表狀態(tài)信息：
        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) 表選項(xiàng)
                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；

實(shí)例：

#刪除索引
MariaDB [xiaoshui]> ALTER TABLE tbl1 DROP INDEX name_2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#添加字段ClassID字段（默認(rèn)在最后一條）
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    |                |
+---------+----------------------+------+-----+---------+----------------+
#調(diào)整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] ...
    
查看表創(chuàng)建語(yǔ)句：
    SHOW CREATE TABLE tbl_name

索引管理：
    創(chuàng)建：
        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]
                  

實(shí)例：

#刪除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語(yǔ)句：

    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：布爾表達(dá)式；
            col_name OPERATOR value|col_name;
                
                操作符：
                    >, <, <=, >=, =, !=
                    BETWEEN ... AND ...
                    LIKE 'PATTERN'：
                        通配符：
                            _：匹配任意單個(gè)字符；
                            %：任意長(zhǎng)度的任意字符；
                    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]

實(shí)例：

#給tbl1插入數(shù)據(jù)，name和ClassID，因?yàn)間ender的默認(rèn)值為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)
#插入多條數(shù)據(jù)
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)
#如果不指定字段，則要對(duì)應(yīng)填上每個(gè)字段的值
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)
#查詢(xún)表中的所有數(shù)據(jù)
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      |
+----+----------+------+---------+--------+
#查詢(xún)所需要的字段
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語(yǔ)句條件性查找
MariaDB [xiaoshui]>  SELECT name FROM tbl1 WHERE name LIKE '%o%';
+----------+
| name     |
+----------+
| tom      |
| xiaoshui |
| zhao     |
+----------+
3 rows in set (0.00 sec)
#利用RLIKE正則表達(dá)式來(lái)匹配內(nèi)容
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)
#通過(guò)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)
#先通過(guò)ClassID排序，如果ClassID相同，在通過(guò)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]

#設(shè)置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]>

                                                                                                                        謝謝瀏覽...