MariaDB数据库命令行指导 mariadb

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.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 at http://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 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 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 ', where 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_statementOr:CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }...

3、数据类型自动增长类型

AUTO_INCREMENT

字符型：

char/binaryvarchar/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]

【MariaDB数据库命令行指导】取消授权或者调整权限

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