MySQL 不完全手册 随时更新中.....

基本操作

1. 登录 Mariadb 数据库

   mysql -h host -P 3306 -u root -p
   //其中 host 为服务器地址； 3306 为默认端口号 root 为登录用户名 -p为密码

2. 在mysql cli 模式下调用 Linux 命令

   # system + linux cmd
   system pwd

3. 退出登录 Mariadb

   quit

4. 查看 Mariadb 版本

   select version();

5. 应用指定数据库

   use database;

6. 查看当前选择使用的数据库

   select database();

7. 查看所有数据库

   show databases;

8. 查看默认认证方式

   Mysql Mariadb 数据中，用户信息存放在默认的 mysql 数据库的 user 表中

   select host, user, plugin from user;

9. 查看数据库默认编码

   status;

或者：

show variables like 'char%';

10. 显示安装后可以使用的存储引擎和默认引擎

    show engines;

    MySQL 帮助系统

为什么使用内置帮助系统

在mysql的使用过程中, 可能经常会遇到以下问题:

1. 某个操作语法忘记了, 如何快速查找?

2. 如何快速知道当前版本上某个字段类型的取值范围?

3. 当前版本都支持哪些函数?希望有例子说明..
4. 当前版本是否支持某个功能?

对于上面列出的问题, 我们可能想到的方法是查找MySQL的文档, 这些问题在官方文档都可以很清楚地查到, 但是却要耗费大量的时间和精力.
所以对于以上问题, 最好的解决办法就是使用MySQL安装后自带的帮助文档, 这样在遇到问题时就可以方便快捷地进行查询.

帮助系统的使用

1. 最简单的 help

  help
  或者
  ？

  #显示如下：

  General information about MariaDB can be found at
  http://mariadb.org
  
  List of all client 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 MariaDB server, display result vertically.
  exit      (\q) Exit mysql. Same as quit.
  go        (\g) Send command to MariaDB 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'
  ```  


2. 按层次查看帮助

  如果不知道帮助能够提供些什么, 可以用"? contents"命令来显示所有可供查询的分类, 如下例所示:

help contents;
或者：
？ contents;

#显示如下：

You asked for help about help category: “Contents”
For more information, type ‘help ’, where is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Sequences
Table Maintenance
Transactions
User-Defined Functions
Utility

3. 查看数据类型分类

help data types;
或者：
? data types;

#显示如下：

You asked for help about help category: “Data Types”
For more information, type ‘help ’, where is one of the following
topics:
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB and TEXT Data Types
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DECIMAL
DOUBLE
ENUM
FLOAT
Geometry Types
JSON Data Type
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
Numeric Data Type Overview
ROW
SET Data Type
SMALLINT
String Literals
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR Data Type

4. 查看具体数据类型

help DATE
或者:
? DATE

#显示如下：

Name: ‘DATE’
Description:
Syntax

DATE

Description

A date. The supported range is ‘1000-01-01’ to
‘9999-12-31’. MariaDB
displays DATE values in ‘YYYY-MM-DD’ format, but can be
assigned dates in looser formats, including strings or
numbers, as long as they make sense. These include a short
year, YY-MM-DD, no delimiters, YYMMDD, or any other
acceptable delimiter, for example YYYY/MM/DD. For details,
see date and time literals.

‘0000-00-00’ is a permitted special value (zero-date),
unless the NO_ZERO_DATE SQL_MODE is used. Also, individual
components of a date can be set to 0 (for example:
‘2015-00-12’), unless the NO_ZERO_IN_DATE SQL_MODE is
used. In many cases, the result of en expression involving a
zero-date, or a date with zero-parts, is NULL. If the
ALLOW_INVALID_DATES SQL_MODE is enabled, if the day part is
in the range between 1 and 31, the date does not produce any
error, even for months that have less than 31 days.

Examples

CREATE TABLE t1 (d DATE);

INSERT INTO t1 VALUES (“2010-01-12”), (“2011-2-28”),
(‘120314’),(‘130421’);

SELECT * FROM t1;

±-----------+
| d |
±-----------+
| 2010-01-12 |
| 2011-02-28 |
| 2012-03-14 |
| 2013-04-21 |
±-----------+

## 帮助规范

- 在帮助系统中，所有需要键入关键字``` help ```的地方 都可以使用 ``` ? ``` 替代；

- 根据使用帮助中 3、4 可看出，在 MySQL 帮助中，把所有命令、语句等归纳到一定的分类中，这样就可以逐级查找相应的分类下的具体需要的内容。这也是区别旧版本帮助的一个方面。比如 show 指令，在旧版本中可以直接使用  ``` help show ``` 来显示帮助信息，但是在新版本中， 指令 ``` show ``` 被划分到 ``` Administration ``` 分类中，帮助名应该为：

help administration

#显示如下：

You asked for help about help category: "Administration"
For more information, type 'help <item>', where <item> is one of the following
topics:
   About SHOW
   BINLOG
   CACHE INDEX
   FLUSH
   FLUSH QUERY CACHE
   FLUSH TABLES FOR EXPORT
   HELP Command
   KILL [CONNECTION | QUERY]
   LOAD INDEX
   RESET
   SET
   SHOW AUTHORS
   SHOW BINARY LOGS
   SHOW BINLOG EVENTS
   SHOW CHARACTER SET
   SHOW CLIENT_STATISTICS
   SHOW COLLATION
   SHOW COLUMNS
   SHOW CONTRIBUTORS
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PACKAGE
   SHOW CREATE PACKAGE BODY
   SHOW CREATE PROCEDURE
   SHOW CREATE SEQUENCE
   SHOW CREATE TABLE
   SHOW CREATE TRIGGER
   SHOW CREATE USER
   SHOW CREATE VIEW
   SHOW DATABASES
   SHOW ENGINE
   SHOW ENGINE INNODB STATUS
   SHOW ENGINES
   SHOW ERRORS
   SHOW EVENTS
   SHOW EXPLAIN
   SHOW FUNCTION CODE
   SHOW FUNCTION STATUS
   SHOW GRANTS
   SHOW INDEX
   SHOW LOCALES
   SHOW MASTER STATUS
   SHOW OPEN TABLES
   SHOW PACKAGE BODY STATUS
   SHOW PACKAGE STATUS
   SHOW PLUGINS
   SHOW PLUGINS SONAME
   SHOW PRIVILEGES
   SHOW PROCEDURE CODE
   SHOW PROCEDURE STATUS
   SHOW PROCESSLIST
   SHOW PROFILE
   SHOW PROFILES
   SHOW QUERY_RESPONSE_TIME
   SHOW RELAYLOG EVENTS
   SHOW SLAVE HOSTS
   SHOW SLAVE STATUS
   SHOW STATUS
   SHOW TABLE STATUS
   SHOW TABLES
   SHOW TABLE_STATISTICS
   SHOW TRIGGERS
   SHOW USER_STATISTICS
   SHOW VARIABLES
   SHOW WARNINGS
   SHOW WSREP_MEMBERSHIP
   SHOW WSREP_STATUS
# 字符集编码

1. 查看支持的字符集
    
show charset;
# or
show character set;
    {% asset_img show_charset.png show_charset %}
  
2. 查看数据库默认字符集及排序规则
show variables like "%character%";show variables like "%collation%";
    {% asset_img show_variables_like_charset.png show_variables_like_charset %}

3. 设置编码格式为 utf8mb4
    
SET NAMES 'utf8mb4';
set character_set_server = utf8mb4;
set character_set_database = utf8mb4;
set collation_database = utf8mb4_general_ci;
set collation_server = utf8mb4_general_ci;
{% asset_img set_names_utf8mb4.png set_names_utf8mb4 %}

```但是，重启 mariadb-server 后 重新查询显示如下```

{% asset_img set_names_reload.png set_names_reload %}

可以发现，重新回复了默认值；由此可见：用语句设置编码方式是临时性质

如果要永久设置编码方式，那么 只能在数据库配置文件 也就是 ```/etc/my.cnf``` 设置编码格式：
   [client-server]
   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_unicode_ci
   log-error=/var/log/mysqld.log
   init-connect='SET NAMES utf8mb4'
   [client]
   default-character-set=utf8mb4
   [mysql]
   default-character-set=utf8mb4
   
   重启查询：

   {% asset_img my_cnf_set_utf8mb4.png my_cnf_set_utf8mb4 %}

   发现设置成功

<font color=#FF0000>如果你的数据库中已经存在表了，建议进行修复一下</font>
    
    mysqlcheck -u root -p --auto-repair --optimize --all-databases
    
# Mysql权限与角色

1. 创建用户并授权
# 创建用户
create user 'user_name'@'%' identified with mysql_native_password by 'password';
#
# 授权
grant all privileges on database.tables to 'user_name'@'%' with grant option;
# 授权所有数据库的所有表所有权限
# grant all privileges on *.* to 'user_name'@'%' with grant option;
#
# 刷新使之生效
flush privileges;
    
   其中：
    - user_name：用户名

    - '%' 域 
         可选值：  
        %代表通配所有host地址权限(可远程访问) 
        localhost为本地权限(不可远程访问)  
        指定特殊Ip访问权限 如10.138.106.102

    - password  密码

    -  database.tables 数据库.表 格式；如果是 *.*  表示所有数据库的所有表

2. 查看用户权限
show grants for '#userName'@'#host';
    

3. 撤销授权
REVOKE '#privilege' ON '#databasename'.'#tablename' FROM '#username'@'#host';
    privilege 代表具体权限  比如： ALL  select 等
    注意用什么的语句授权就用什么样的语句收回权限，否则可能无法撤销权限


# 数据库操作

1. 创建数据库
create database { #database name } CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci Case Insensitive
    - CHARACTER: 字符集（多数情况下要选择一个支持中文的字符集）
    - COLLATE: 排序规则，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE 为 latin1_swedish_ci，GBK 编码的默认 COLLATE 为 gbk_chinese_ci，utf8mb4 编码的默认值为utf8mb4_general_ci Case Insensitive

2. 查看数据库创建库语句的SQL信息
show create database { #database_name };
    {% asset_img mysql_show_create_database.png mysql_show_create_database %}

3. 查看数据表创建库语句的SQL信息
show create table { #table_name }
3. 修改已存在数据库编码
alter database { #database_name } character set utf8mb4;
    {% asset_img alter_database_character.png alter_database_character %}

4. 显示当前数据库中所有表名称：
    
show tables;
# 或者
show tables from { #database_name }
    {% asset_img show_tables.png show_tables %}

5. 显示表中列名
show columns from { #database_name.#table_name }
# 或者
desc { #database_name.#table_name }
```

{% asset_img desc_table.png desc_table %}