Mariadb的基础应用

Mariadb概述：

在网站制作、成都网站制作中从网站色彩、结构布局、栏目设置、关键词群组等细微处着手，突出企业的产品/服务/品牌，帮助企业锁定精准用户，提高在线咨询和转化，使成都网站营销成为有效果、有回报的无锡营销推广。创新互联建站专业成都网站建设十年了，客户满意度97.8%，欢迎成都创新互联客户联系。

    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]>

                                                                                                                        谢谢浏览...