EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL SHOW

MySQL SHOW

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 24, 2023

MySQL SHOW

Introduction to MySQL SHOW

MySQL SHOW command is a special query in MySQL to view the information schema of any records stored on the database. We can define MySQL SHOW with several commands that help deliver important data records or info like logs, events created, triggers, databases, tables, columns, status information of commands, and many more administrative-level commands. This MySQL SHOW command helps provide various transactional, datetime, and output statuses of all the MySQL statement queries on the MySQL server, which we need to keep records of.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The MySQL SHOW commands permit showing off the structure, fields, indexes, and functions created or stored procedures of the MySQL server, which may be necessary to view for the users at a time.

Syntax:

You can find a simple basic syntax to denote the SHOW commands in MySQL, like:

SHOW DATABASES;
SHOW ERRORS;
SHOW TABLES;
SHOW COLUMNS FROM TableName;
SHOW [FULL] PROCESSLIST;
SHOW EVENTS;
SHOW TABLE STATUS;
SHOW PRIVILEGES;
SHOW WARNINGS;
SHOW CREATE VIEW;
SHOW VARIABLES;
SHOW ENGINE;
SHOW INDEX;
SHOW GRANTS;
SHOW STATUS;
SHOW PLUGINS;
SHOW CREATE FUNCTION;

How does SHOW Command Work in MySQL?

Further, the MySQL SHOW Commands like SHOW CREATE VIEW query allow the display of the create view MySQL statement; SHOW DATABASES query helps list all the databases on the MySQL server. Also, we can use SHOW SCHEMAS as a synonym for SHOW DATABASES, etc. Even we can implement a MySQL LIKE clause along with the SHOW DATABASES that indicates the list of those databases which match a specific pattern type. TO view the server status, we can apply the SHOW STATUS command.

For some critical conditions, SHOW commands in MySQL provide a SHOW ERRORS query to show any errors, warnings, or notes on the server. We can also look up the privileges using the SHOW PRIVILEGES command, supported on that MySQL server version.

In this way, the MySQL SHOW commands work on the MySQL database server to perform different activities responsible for other tasks.

Examples to Implement MySQL SHOW

Let us view some examples regarding the MySQL SHOW commands to understand the functionality better.

Example #1 – Using SHOW DATABASES Command

This MySQL command is responsible for displaying all the databases present on the server using the following statement:

Query:

SHOW DATABASES;

Output:

MySQL SHOW Example 1

We can also use the SHOW SCHEMAS command, which also results in the same value as the above output.

Query:

SHOW SCHEMAS;

Output:

MySQL SHOW Example 1

If we want to fetch the information of databases by applying a particular pattern, we should use the LIKE clause in the previous query.

Query:

SHOW DATABASES LIKE '%hu%';

Output:

MySQL SHOW Example 1

Example #2 – Using SHOW TABLES Command

We will apply this command to query and retrieve tables from a specific database on the server. For this, when we log in to the MySQL server or phpMyAdmin then, we need to select a particular database to list out the tables available there using the following query:

Query:

SHOW TABLES;

Output:

MySQL SHOW Example 2

The below SHOW command shows if the existing table is a base or view table in the result fetched.

Query:

SHOW FULL TABLES;

Output:

MySQL SHOW Example 2

If we further want to fetch the result of tables specifying a specific name, like suppose starting or ending with particular letters, then we use the query here:

Query:

SHOW TABLES LIKE 'b%';

Output:

MySQL SHOW Example 2

Example #3 – Using SHOW COLUMNS Command

This MySQL command is used to query the list of table columns found in a specific table in a particular database. We will execute the following statement:

Query:

SHOW COLUMNS FROM TableName;

Suppose we have a sample table named ‘Books’ in a database.

Query:

SHOW COLUMNS FROM Books;

Output:

MySQL SHOW Example 3

Example #4 – Using SHOW CHARACTER SET Command

We can check all the present character sets available on the MySQL server. We use the following MySQL statement for this:

Query:

SHOW CHARACTER SET;

Output:

MySQL SHOW Example 4

To find a matching character set from the database, we can apply an option such as a LIKE clause to specify a certain pattern expression to produce the matched results. We run the below code:

Query:

SHOW CHARACTER SET LIKE 'asc%';

Output:

MySQL SHOW Example 4

Example #5 – Using SHOW COLLATION Command

In MySQL, a Collation is a set of procedures for relating letterings in a character set. By using this SHOW COLLATION query statement, we will get the list of collations supported by the MySQL server. Below is the syntax for this command.

Query:

SHOW COLLATION SET [LIKE 'Specified_pattern' | WHERE expression];

Also, we use the LIKE clause option to show matched collations on the MySQL server, and with the help of the WHERE clause, we can join a conditional expression with the SHOW query.

Query:

SHOW COLLATION;

Output:

COLLATION Command Example 5

Query:

SHOW COLLATION LIKE '%bin';

Output:

COLLATION Command Example 5

Example #6 – Using the SHOW ENGINE command

This MySQL SHOW query displays the MySQL Database Engine status report that provides us with functioning information about the storage engine on the MySQL server.

Query:

SHOW ENGINE INNODB STATUS;

Output:

ENGINE command Example 6

We can also perform the following SHOW command:

Query:

SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

Output:

ENGINE command Example 6

Example #7 – Using SHOW ERRORS Command

A SHOW command is implemented to show errors, notes, warnings, and notices. This SHOW command works equivalent to SHOW WARNINGS but is an exceptional case of showing errors.

Query:

SHOW ERRORS;

Output:

ERRORS Command Example 7

Query:

SHOW WARNINGS;

Output:

ERRORS Command Example 7

Also, the SHOW code below shows the number of errors found on the server.

Query:

SHOW COUNT(*) ERRORS;

Output:

ERRORS Command Example 7

We can also use the LIMIT clause with the SHOW ERRORS command to provide a specific rows number to be displayed after execution having syntax like:

Query:

SHOW ERRORS [LIMIT clause];

Example #8 – Using SHOW FUNCTION STATUS Command

If we want to view the status or characteristics of functions stored in the database, including database, type, name, creation, creator, character set, and revision dates, then follow to use the SHOW FUNCTION command as follows:

Query:

SHOW FUNCTION STATUS;

Output:

FUNCTION STATUS Command Example 8

And many more SHOW commands are available to use in different conditions.

Conclusion

  • In this article, we have learnt some important uses of different SHOW commands in MySQL, and can work for distinct operations.
  • Using the SHOW syntax mentioned above, we can query to view different sections of database information, from tables and columns to events, errors, and functions, as well as server status and logs.

Recommended Articles

We hope that this EDUCBA information on “MySQL SHOW” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Introduction to MySQL Operators
  2. Top 23 MySQL String functions
  3. MySQL vs SQLite | Top 14 Comparisons
  4. Guide to MySQL Timestamp
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more