乐趣区

关于mariadb:MariaDB数据库命令行指导

jiezi

1、客户端命令 mysql

格局
mysql [OPTIONS] [database]

罕用选项

-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'：连贯至服务器并让其执行此命令后间接返回；

注：在下面的选项中，选项和参数之间能够不应用空格离开。

示例登录 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、查看客户端常见命令

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.               # 不显示正告信息

查看 mysql 服务端帮忙信息，可应用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

要查看某一类命令或者某繁多命令应用，均可应用 help KEYWORD 查看，例如：

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、数据类型

主动增长类型

    AUTO_INCREMENT

字符型：

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

数值型

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

日期型

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

4、常见数据库治理语句

4.1、操作数据库

抉择数据库

USE db_name

创立

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]

删除

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
4.2、表治理

创立

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

批改

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

删除

DROP [TEMPORARY] TABLE [IF EXISTS]
     tbl_name [, tbl_name] ...
     [RESTRICT | CASCADE]
4.3、DML 数据操作语言

查看

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

删除

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]

批改

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、权限治理（账户治理）

创立用户

CREATE USER user_specification
    [, user_specification] ...

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

删除用户

DROP USER user [, user] ...

向用户受权

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]

勾销受权或者调整权限

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] ...
退出移动版