MariaDB database command line guide

1. The client command mysql

format
mysql [OPTIONS] [database]

Common options

-u, --user=username：用户名，默认为root；
-h, --host=hostname：远程主机（即mysql服务器）地址，默认为localhost; 
    客户端连接服务端，服务器会反解客户的IP为主机名，关闭此功能(skip_name_resolve=ON)；
-p, --password[=PASSWORD]：USERNAME所表示的用户的密码； 默认为空；
-P, --port=#：mysql服务器监听的端口；默认为3306/tcp；
-S, --socket=/PATH/TO/mysql.sock：套按字文件路径；
-D, --database=DB_name：连接到服务器端之后，设定其处指明的数据库为默认数据库；
-e, --execute='SQL STATEMENT'：连接至服务器并让其执行此命令后直接返回；

Note: In the above options, there is no need to use spaces to separate the options and parameters.

Example login to MySQL

glc@LAPTOP-LEMON:~$ mysql -uadmin -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2. View common commands on the client

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.　　　　　　　　　　　　　　　　　　　　　　　　　　　 # 查看帮助
quit      (\q) Quit mysql.　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 # 退出
source    (\.) Execute an SQL script file. Takes a file name as an argument.       # 读取SQL脚本
system    (\!) Execute a system shell command.                                     # 执行shell命令
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. 　　　　　　　　　　　　　 # 不显示警告信息　

View the help information of mysql server, you can use help Contents :

MariaDB [(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> 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
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

To view a certain type of command or a single command, you can use help KEYWORD view, for example:

MariaDB [(none)]> help Data Definition;
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION

   ...

   RENAME TABLE
   TRUNCATE TABLE

MariaDB [(none)]> help CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

...

3. Data type

Automatic growth type

    AUTO_INCREMENT

Character type:

    char/binary        varchar/varbinary
    text/ngtext/blob/ediumblob/longblob
    set/enum

Numerical

    int/tinyint/smallint/mediumint/bigint
    decimal
    float/double

date type

    date/time/datetime/timestamp/year(2,4)

4. Common database management statements

4.1, operating database

Select database

USE db_name

Create

CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;
        [DEFAULT] CHARACTER SET [=] charset_name                 # 设置字符集
        [DEFAULT] COLLATE [=] collation_name                     # 设置排序方式
                        
SHOW CHARACTER SET 　　　#  查看支持的所有的字符集
SHOW COLLATION          #  查看支持的所有排序方式

Modification

ALTER {DATABASE | SCHEMA}  [db_name]

delete

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

View

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]          

4.2, table management

Create

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    
       (create_definition,...)          #  字段定义  
       [table_options]                  #  数据表的基本设置
       [partition_options]              #  分割选项

modify

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
      [alter_specification [, alter_specification] ...]
      [partition_options]

delete

DROP [TEMPORARY] TABLE [IF EXISTS]
     tbl_name [, tbl_name] ...
     [RESTRICT | CASCADE]

4.3, DML data manipulation language

View

SELECT
    [ALL | DISTINCT | DISTINCTROW ]      
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

insert

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Delete

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

modify

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition] 

5. Authority management (account management)

Create user

CREATE USER user_specification
    [, user_specification] ...

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

Delete user

DROP USER user [, user] ...

Authorize users

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

Cancel authorization or adjust permissions

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...