mysql show simple operation example

This article describes the mysql show operation with examples. Share with you for your reference, the details are as follows:

SHOW CHARACTER SET

Displays all available character sets

SHOW CHARACTER SET;
SHOW CHARACTER SET LIKE 'latin%';

SHOW COLLATION

The output includes all available character sets

SHOW COLLATION;
SHOW COLLATION LIKE 'latin1%';

SHOW COLUMNS

Displays information about the columns in a given table. This statement also works for views.

SHOW COLUMNS FROM mydb.mytable;
SHOW COLUMNS FROM mytable FROM mydb;

SHOW CREATE DATABASE

Displays the CREATE DATABASE statement used to create a given database. You can also use SHOW CREATE SCHEMA .

SHOW CREATE DATABASE test;
SHOW CREATE DATABASE test\G;

SHOW CREATE TABLE

Show the sql statement to create a table

SHOW CREATE TABLE java;
SHOW CREATE TABLE java\G;

SHOW DATABASES

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command. You can see only databases for which you have some privilege, unless you have the global SHOW DATABASES privilege.

SHOW DATABASES;

SHOW ENGINE

SHOW ENGINE displays log or status information of a storage engine. The following statements are currently supported:

SHOW ENGINE BDB LOGS;
SHOW ENGINE INNODB STATUS;

SHOW ENGINES

SHOW ENGINES displays status information about storage engines. This statement is useful for checking whether a storage engine is supported, or for seeing what the default engine is.

SHOW ENGINES;
SHOW ENGINES\G;

SHOW ERRORS

This statement displays only errors, not errors, warnings, and notes.

SHOW COUNT(*) ERRORS;
SHOW ERRORS;

SHOW GRANTS

View related permissions

SHOW GRANTS FOR user;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

SHOW INDEX

SHOW INDEX returns table index information.

SHOW INDEX FROM mydb.mytable;
SHOW INDEX FROM mytable FROM mydb;

SHOW INNODB STATUS (mysql5.6 will report an error)

View server information and locate problems

This is a deprecated synonym for SHOW ENGINE INNODB STATUS .

SHOW OPEN TABLES

Lists the non-TEMPORARY tables that are currently open in the table cache.

SHOW OPEN TABLES;

SHOW PRIVILEGES

Displays a list of system privileges supported by the MySQL server. The exact output depends on your server version.

SHOW PRIVILEGES;

SHOW PROCESSLIST

Shows which threads are running. show full processlist is often used to view the number of MySQL connections. You can also use mysqladmin processlist statement to obtain this information. If you have the SUPER privilege, you can see all threads. Otherwise, you can only see your own threads

SHOW STATUS

Provides server status information. This information is also available using the mysqladmin extended-status command.

SHOW STATUS;

SHOW TABLE STATUS

SHOW TABLE STATUS is similar in nature to SHOW TABLE, but provides extensive information about each table. You can also get this list using the mysqlshow --status db_name command.

This statement also displays view information.

SHOW TABLE STATUS;
SHOW TABLE STATUS FROM test;

SHOW TABLES

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command.

SHOW TABLES;

SHOW TRIGGERS

SHOW TRIGGERS lists the triggers currently defined by the MySQL server.

SHOW TRIGGERS;

SHOW VARIABLES

View configuration variables

SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SHOW VARIABLES LIKE 'have%';

SHOW WARNINGS

Displays error, warning, and notice messages resulting from the last statement that generated a message. If the last statement using the table did not generate a message, nothing is displayed. SHOW ERRORS is a related statement that displays only errors.

SHOW COUNT(*) WARNINGS;
SHOW WARNINGS;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.