用户工具

站点工具

您的足迹: • mysql_build_in_manual

侧边栏123123

最近发布

随机推荐

docs:mysql:mysql_build_in_manual

目录

引言

某一天，有心留意到登陆mysql的提示，那么就从这里启航吧 

[root@centos ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 178
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 



mysql> ?;

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

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.
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.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

mysql>

Outline of the manual

帮助大纲

help contents

mysql>help Account Management mysql>help Administration 

mysql> 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                                   # DDL CREATE、ALTER、DROP等.DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
   Data Manipulation                                 # DML 增删改查等，SELECT、UPDATE、INSERT、DELETE
   Data Types                                        # 数据类型(创建表时)
   Functions                                         # 函数
   Functions and Modifiers for Use with GROUP BY     # 可与GROUP BY共用的函数与修饰符
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility
mysql># 查看帮助主题分类的帮助示例
mysql>help Account Management
mysql>help Administration

Administration运维管理

主要包括子主题有如下

  1. show状态查询
  2. flush、binlog
  3. 主从状态
  4. 服务管理：kill 、shutdown

mysql> help Administration
You asked for help about help category: "Administration"
For more information, type 'help <item>', where <item> is one of the following
topics:
   BINLOG
   CACHE INDEX
   FLUSH
   FLUSH QUERY CACHE
   HELP COMMAND      # 这个帮助命令是说：help show、help select...等可以显示sql帮助等。是直达命令的帮助，后面也有示例。
   KILL
   LOAD INDEX
   RESET
   SET
   SET CHARACTER SET
   SET NAMES
   SHOW
   SHOW BINARY LOGS
   SHOW BINLOG EVENTS
   SHOW CHARACTER SET
   SHOW COLLATION
   SHOW COLUMNS
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SHOW CREATE TRIGGER
   SHOW CREATE USER
   SHOW CREATE VIEW
   SHOW DATABASES
   SHOW ENGINE
   SHOW ENGINES
   SHOW ERRORS
   SHOW EVENTS
   SHOW FUNCTION CODE
   SHOW FUNCTION STATUS
   SHOW GRANTS
   SHOW INDEX
   SHOW MASTER STATUS
   SHOW OPEN TABLES
   SHOW PLUGINS
   SHOW PRIVILEGES
   SHOW PROCEDURE CODE
   SHOW PROCEDURE STATUS
   SHOW PROCESSLIST
   SHOW PROFILE
   SHOW PROFILES
   SHOW RELAYLOG EVENTS
   SHOW SLAVE HOSTS
   SHOW SLAVE STATUS
   SHOW STATUS
   SHOW TABLE STATUS
   SHOW TABLES
   SHOW TRIGGERS
   SHOW VARIABLES
   SHOW WARNINGS
   SHUTDOWN    # 停止后，mysql端口监听停止，可能console未断开，但已经无法操作了。需要通过正常的启动服务等其它方式从外部启动服务监听端口。

mysql>

SHOW PROCESSLIST

显示线程状态 


mysql> SHOW PROCESSLIST;
+----+-----------------+----------------------+--------------+---------+------+------------------------+------------------+
| Id | User            | Host                 | db           | Command | Time | State                  | Info             |
+----+-----------------+----------------------+--------------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost            | NULL         | Daemon  | 1269 | Waiting on empty queue | NULL             |
|  3 | root            | 100.100.100.62:37736 | kaixin       | Sleep   |   14 |                        | NULL             |
|  4 | root            | 100.100.100.62:37738 | kaixin       | Sleep   |   14 |                        | NULL             |
|  5 | root            | 100.100.100.62:37740 | kaixin       | Sleep   |   15 |                        | NULL             |
|  8 | root            | localhost            | NULL         | Query   |    0 | starting               | SHOW PROCESSLIST |
+----+-----------------+----------------------+--------------+---------+------+------------------------+------------------+
5 rows in set (0.00 sec)


mysql> help SHOW PROCESSLIST;
Name: 'SHOW PROCESSLIST'
Description:
Syntax:
SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows which threads are running. If you have the
PROCESS privilege, you can see all threads. Otherwise, you can see only
your own threads (that is, threads associated with the MySQL account
that you are using). If you do not use the FULL keyword, only the first
100 characters of each statement are shown in the Info field.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-processlist.html    #  关于此命令的在线文档

mysql>

权限管理

内置权限说明 

mysql> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

mysql>

存储过程

mysql> help SHOW PROCEDURE CODE
Name: 'SHOW PROCEDURE CODE'
Description:
Syntax:
SHOW PROCEDURE CODE proc_name

This statement is a MySQL extension that is available only for servers
that have been built with debugging support. It displays a
representation of the internal implementation of the named stored
procedure. A similar statement, SHOW FUNCTION CODE, displays
information about stored functions (see [HELP SHOW FUNCTION CODE]).

To use either statement, you must be the owner of the routine or have
SELECT access to the mysql.proc table.

If the named routine is available, each statement produces a result
set. Each row in the result set corresponds to one "instruction" in the
routine. The first column is Pos, which is an ordinal number beginning
with 0. The second column is Instruction, which contains an SQL
statement (usually changed from the original source), or a directive
which has meaning only to the stored-routine handler.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-procedure-code.html

Examples:
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
    -> BEGIN
    ->   DECLARE fanta INT DEFAULT 55;
    ->   DROP TABLE t2;
    ->   LOOP
    ->     INSERT INTO t3 VALUES (fanta);
    ->     END LOOP;
    ->   END//
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROCEDURE CODE p1//
+-----+----------------------------------------+
| Pos | Instruction                            |
+-----+----------------------------------------+
|   0 | set fanta@0 55                         |
|   1 | stmt 9 "DROP TABLE t2"                 |
|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
|   3 | jump 2                                 |
+-----+----------------------------------------+
4 rows in set (0.00 sec)

mysql>

docs/mysql/mysql_build_in_manual.txt · 最后更改: 2020/09/25 21:49 (modify by Amos)

页面工具

您的足迹: • mysql_build_in_manual