note_21.4.3_MariaDB

MariaDB程序的组成：C/S

• C：Client --> mysql protocol --> Server
  mysql：CLI交互式客户端程序；
  mysqldump：备份工具；
  mysqladmin：管理工具；
  mysqlbinlog：
  ...

• S：Server
  mysqld
  mysqld_safe：建议运行服务端程序；
  mysqld_multi：多实例；

  三类套接字地址：
  IPv4|IPv6, 3306/tcp
  Unix Sock：/var/lib/mysql/mysql.sock, /tmp/mysql.sock
  C <--> S: localhost, 127.0.0.1

• 配置文件：ini风格，用一个文件为多个程序提供配置；
  [mysql]
  [mysqld]
  [mysqld_safe]
  [server]
  [client]
  [mysqldump]
  ...

mysql的各类程序启动都读取不止一个配置文件，按顺序读取，且最后读取的为最终生效；

[root@localhost ~]# my_print_defaults
...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
...

命令行交互式客户端程序：mysql

mysql [OPTIONS] [database]

常用选项：
-u, --user=username：用户名，默认为root；
-h, --host=hostname：远程主机（即mysql服务器）地址，默认为localhost; 客户端连接服务端，服务器会反解客户的IP为主机名，关闭此功能(skip_name_resolve=ON)；
-p, --password[=PASSWORD]：USERNAME所表示的用户的密码； 默认为空；

注意：mysql的用户账号由两部分组成：'USERNAME'@'HOST'; 其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务；
HOST的表示方式，支持使用通配符：
%：匹配任意长度的任意字符；
172.16.%.%, 172.16.0.0/16
_：匹配任意单个字符；

-P, --port=#：mysql服务器监听的端口；默认为3306/tcp；
-S, --socket=/PATH/TO/mysql.sock：套按字文件路径；

-D, --database=DB_name：连接到服务器端之后，设定其处指明的数据库为默认数据库；
-e, --execute='SQL STATEMENT'：连接至服务器并让其执行此命令后直接返回；

命令：

• 客户端命令：本地执行
  mysql> help
  \u db_name：设定哪个库为默认数据库
  \q：退出；
  \d CHAR：设定新的语句结束符；
  \g：语句结束标记；
  \G：语句结束标记，结果竖排方式显式；
  \s：获取服务器状态
  \!: 执行shell命令；
  \.: 装载并运行sql脚本；

• 服务端命令：通过mysql连接发往服务器执行并取回结果（SQL语句）；
  DDL， DML， DCL

  注意：每个语句必须有语句结束符，默认为分号(;)

  • 获取帮助：
    mysql> help contents
    Administration
    Account Management
    Data Definition
    Data Manipulation
    Data Types
    myslq> help '命令类别'

---

数据类型：

• 表：行和列
  创建表：定义表中的字段；

            定义字段时，关键的一步即为确定其数据类型；
                用于确定：数据存储格式、能参与运算种类、可表示的有效的数据范围；
  

• 字符型：字符集
  码表：在字符和二进制数字之间建立映射关系；

                mysql> SHOW CHARACTER SET; 
                mysql> SHOW COLLATION;
  

  • 字符型：
    定长字符型：
    CHAR(#)：不区分字符大小写
    BINARY(#)：区分字符大小写
    变长字符型：
    VARCHAR(#)：多占一个或两个字符空间；
    VARBINARY(#)：
    对象存储：
    TEXT：不区分字符大小写；TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT
    BLOB：Binary Large OBject，区分字符大小写；TINYBLOB，SMALLBLOB，MEDIUMBLOB，BLOB，LONGBLOB；
    内置类型：
    SET
    ENUM

• 数值型：
  精确数值型：
  INT（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT）
  UNSIGNED
  DECIMAL
  近似数值型：
  FLOAT
  DOBULE

  • 日期时间型：
    日期型：DATE
    时间型：TIME
    日期时间型：DATETIME
    时间戳：TIMESTAMP
    年份：YEAR(2), YEAR(4)

• 字段数据修饰符：
  NOT NULL：非空；
  AUTO_INCREMENT：自动增长；
  NULL：
  DEFAULT value：默认值；
  PRIMARY KEY：主键；
  UNIQUE KEY：惟一键；

---

服务器端命令：

• DDL：数据定义语言，主要用于管理数据库组件，例如数据库、表、索引、视图、用户、存储过程
  CREATE、ALTER、DROP
  create database 就是在/var/lib/mysql/目录下创建一个对应的目录

MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| wordpress          |
+--------------------+
6 rows in set (0.04 sec)

You can see what character sets and collations are available using,
respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See
[HELP SHOW CHARACTER SET], and [HELP SHOW COLLATION], for more
information.

• DML：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查；
  INSERT， DELETE， UPDATE， SELECT

• 获取命令帮助：
  mysql> help KEYWORD

---

数据库管理（DDL）：

            创建：
                CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;
                    [DEFAULT]  CHARACTER SET [=] charset_name
                    [DEFAULT]  COLLATE [=] collation_name
                    
                查看支持的所有字符集：SHOW CHARACTER SET 
                查看支持的所有排序规则：SHOW  COLLATION
                
            修改：
                ALTER {DATABASE | SCHEMA}  [db_name]
                    [DEFAULT]  CHARACTER SET [=] charset_name
                    [DEFAULT]  COLLATE [=] collation_name
                    
            删除：
                DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
                
            查看：
                SHOW DATABASES LIKE  ’‘;

---

表管理：

• 第二种创建方式：
  复制表结构；
  CREATE TABLE tbl_name LIKE other_table_name

• 第三种创建方式：
  复制表数据；
  CREATE TABLE tbl_name () SELECT clause

            创建：
                CREATE TABLE  [IF NOT EXISTS]  [db_name.]tbl_name  (create_defination)  [table_options]
                
                create_defination:
                    字段：col_name  data_type
                    键：
                        PRIMARY KEY (col1, col2, ...)
                        UNIQUE KEY  (col1, col2,...)
                        FOREIGN KEY (column)
                    索引：
                        KEY|INDEX  [index_name]  (col1, col2,...)
                        
                table_options：
                    ENGINE [=] engine_name
                    CHARACTER SET [=] charset_name
                    COLLATE [=] collation_name
                    
                查看数据库支持的所有存储引擎类型：
                    mysql> SHOW  ENGINES;
                    
                查看某表的状态信息：
                    mysql> SHOW  TABLES  STATUS  [LIKE  'tbl_name'][WHERE clause]
  

MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> create table tbl1 (id smallint unsigned not null auto_increment unique key,name varchar(60) not null);
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> desc tbl1;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(60)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


MariaDB [testdb]> 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: 0
      Data_free: 4194304
 Auto_increment: 1
    Create_time: 2019-04-28 23:56:34
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

修改：

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name  [alter_specification [, alter_specification] ...]
                
alter_specification:
        字段：
                添加：ADD  [COLUMN]  col_name  data_type  [FIRST | AFTER col_name ]
                删除：DROP  [COLUMN] col_name 
        修改：
                CHANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name] 
                MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]
                键：
                        添加：ADD  {PRIMARY|UNIQUE|FOREIGN}  KEY (col1, col2,...)
                        删除：
                            主键：DROP PRIMARY KEY
                            外键：DROP FOREIGN KEY fk_symbol
                索引：
                        添加：ADD {INDEX|KEY} [index_name]  (col1, col2,...)
                        删除：DROP {INDEX|KEY}  index_name
                表选项：
                        ENGINE [=] engine_name
                    
                查看表上的索引的信息：
                    mysql> SHOW INDEXES FROM tbl_name;

删除：

                DROP  TABLE  [IF EXISTS]   tbl_name [, tbl_name] ...
                
            表的引用方式：
                tbl_name
                db_name.tbl_name    

---

DML：INSERT， DELETE， UPDATE， SELECT

• INSERT：
  INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...

                注意：
                    字符型：引号；
                    数值型：不能用引号；
  

• SELECT：

                (1) SELECT  *  FROM  tbl_name[, tbl_name_2];
                    返回指定表的所有数据；慎用；
                (2) SELECT  col1, col2, ...  FROM  tbl_name;
                    显示时，字段可以显示为别名；
                        col_name  AS  col_alias
                (3)  SELECT  col1, ...  FROM tbl_name  WHERE clause; 
                    WHERE clause：用于指明挑选条件；
                        col_name 操作符 value：
                            age > 30; 
                            
                        操作符(1) ：
                            >, <, >=, <=, ==, !=
                            
                        组合条件：
                            and 
                            or
                            not
                            
                        操作符(2) ：
                            BETWEEN ...  AND ...
                            LIKE 'PATTERN'
                                通配符：
                                    %：任意长度的任意字符；
                                    _：任意单个字符；
                            RLIKE  'PATTERN'
                                正则表达式对字符串做模式匹配；
                            IS NULL
                            IS NOT NULL
                (4) SELECT col1, ... FROM tbl_name  [WHERE clause]  ORDER BY  col_name, col_name2, ...  [ASC|DESC];
                    ASC: 升序；
                    DESC： 降序；
                    
                (5) 分组：
                    GROUP BY，为了聚合；
                        count(), sum(), avg(), max(), min()
                        
                    HAVING：对聚合的结果做条件过滤；                     
  

• DELETE：删除行；

                DELETE   FROM  tbl_name  [WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]
                
                (1) DELETE  FROM  tbl_name  WHERE where_condition 
                (2) DELETE  FROM  tbl_name  [ORDER BY ...]  [LIMIT row_count]
  

• UPDATE：

                UPDATE [LOW_PRIORITY] [IGNORE] table_reference  SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition]  [ORDER BY ...] [LIMIT row_count]