MySQL操作表命令

315 views Posted on 2023-04-11 by 林坤源

平时借助phpMyAdmin/Navicat/Sequel或者其他数据库管理工具，基本上都忘记了基本命令，纯记录一下。

使用help命令，可以查看MySQL所有命令。

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
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility

1、help Account Management 账号管理

ALTER USER
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD

2、help Administration 数据库管理

BINLOG
CACHE INDEX
FLUSH
FLUSH QUERY CACHE
HELP COMMAND
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

3、help Compound Statements 复合语句

BEGIN END
CASE STATEMENT
CLOSE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE VARIABLE
FETCH
GET DIAGNOSTICS
IF STATEMENT
ITERATE
LABELS
LEAVE
LOOP
OPEN
REPEAT LOOP
RESIGNAL
RETURN
SIGNAL
WHILE

4、help Data Definition 数据定义

ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER INSTANCE
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CONSTRAINT
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SERVER
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
RENAME TABLE
TRUNCATE TABLE

5、help Data Manipulation 数据操作

CALL
DELETE
DO
DUAL
HANDLER
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
REPLACE
SELECT
UNION
UPDATE

6、help Data Types 数据类型

AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE

7、help Functions 功能

Bit Functions
Comparison operators
Control flow functions
Date and Time Functions
Encryption Functions
Information Functions
Logical operators
Miscellaneous Functions
Numeric Functions
String Functions

8、help Functions and Modifiers for Use with GROUP BY 组合函数

AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNT DISTINCT
GROUP_CONCAT
JSON_ARRAYAGG
JSON_OBJECTAGG
MAX
MIN
STD
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VARIANCE
VAR_POP
VAR_SAMP

9、help Geographic Features 地理特征

topics:
   GEOMETRY
   GEOMETRY HIERARCHY
   SPATIAL
categories:
   Geometry constructors
   Geometry properties
   Geometry relations
   LineString properties
   MBR
   Point properties
   Polygon properties
   WKB
   WKT

10、help Help Metadata 帮助元数据

HELP_DATE
HELP_VERSION

11、help Language Structure 语言结构

12、help Plugins 插件

Name: 'SHOW PLUGINS'
Description:
Syntax:
SHOW PLUGINS

SHOW PLUGINS displays information about server plugins. Plugin
information is also available in the INFORMATION_SCHEMA.PLUGINS table.
See http://dev.mysql.com/doc/refman/5.7/en/plugins-table.html.

Example of SHOW PLUGINS output:

mysql> SHOW PLUGINS\G
*************************** 1. row ***************************
   Name: binlog
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 2. row ***************************
   Name: CSV
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 3. row ***************************
   Name: MEMORY
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 4. row ***************************
   Name: MyISAM
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
...

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

13、help Procedure 程序

14、help Storage Engines 存储引擎

show Engines

15、help Table Maintenance 表维护

ANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE
OPTIMIZE TABLE
REPAIR TABLE

16、help Transactions 事务

CHANGE MASTER TO
CHANGE REPLICATION FILTER
DEALLOCATE PREPARE
EXECUTE STATEMENT
ISOLATION
LOCK
PREPARE
PURGE BINARY LOGS
RESET MASTER
RESET SLAVE
SAVEPOINT
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
SET SQL_LOG_BIN
START SLAVE
START TRANSACTION
STOP SLAVE
XA

17、help User-Defined Functions 自定义函数

CREATE FUNCTION UDF
DROP FUNCTION UDF

18、help Utility 效率命令

EXPLAIN
HELP STATEMENT
USE

其他操作，如SHOW 命令 HELP SHOW 可以查看全部

SHOW {BINARY | MASTER} LOGS 显示binlog文件及大小
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 显示binlog文件
SHOW CHARACTER SET [like_or_where] 查看字符集
SHOW COLLATION [like_or_where] 查看字符集规则
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where] 查看表结构
SHOW CREATE DATABASE db_name 查看某数据库的创建代码
SHOW CREATE EVENT event_name 查看某事件的创建代码
SHOW CREATE FUNCTION func_name 查看某函数的创建代码
SHOW CREATE PROCEDURE proc_name 查看某存储过程创建代码
SHOW CREATE TABLE tbl_name 查看某表建表代码
SHOW CREATE TRIGGER trigger_name 查看某触发器创建代码
SHOW CREATE VIEW view_name 查看视图
SHOW DATABASES [like_or_where] 显示数据库
SHOW ENGINE engine_name {STATUS | MUTEX}  查看指定存储引擎的详细信息
SHOW [STORAGE] ENGINES 查看数据库所有存储引擎
SHOW ERRORS [LIMIT [offset,] row_count] 查看错误
SHOW EVENTS 查看事件
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user 查看某个用户的权限
SHOW INDEX FROM tbl_name [FROM db_name] 查看某个表的索引
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where] 查看当前打开的表
SHOW PLUGINS 查看插件
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES 查看当前mysql的权限
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS 查看从库
SHOW SLAVE STATUS [FOR CHANNEL channel] 查看从库状态
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
This entry was posted in 应用. Bookmark the permalink.

发表评论