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 -pEnter password:Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 45Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04Copyright (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)]> helpGeneral information about MariaDB can be found athttp://mariadb.orgList 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 contentsYou asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of the followingcategories:   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 followingtopics:   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_statementOr: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] ...