MySQL has a lot of basic commands and show commands is one of them.MySQL has SHOW command which have a several different uses.MySQL SHOW Command generates output displaying a useful information about your Schema.
What is SHOW Command in MySQL
MySQL database, users, and tables.SHOW Command depends on the access level at database so depending on the access level, SHOW commands will not be available to provide only information.The root-level user has the capability to use all the SHOW commands, with the most comprehensive results.
Let’s have a look into the common uses of SHOW commands and learn about in more detail:
- SHOW GRANTS FOR user
- SHOW DATABASES [LIKE value]
- SHOW [OPEN] TABLES [FROM database_name] [LIKE value]
- SHOW CREATE TABLE table_name
- SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE value]
- SHOW INDEX FROM table_name [FROM database_name]
- SHOW TABLE STATUS [FROM db_name] [LIKE value]
- SHOW STATUS [LIKE value]
- SHOW VARIABLES [LIKE value]
1. SHOW GRANTS FOR user
The SHOW GRANTS command displays the privileges for a given user at a given host.It’s a easiest way to get user’s privileges information and to check on the current status of a user that user have access of modification or not.you can check that the user doesn’t already have the requested privileges with SHOW GRANTS.
Let’s understand it by example:
1 2 3 | SHOW GRANTS FOR root@localhost; |
OutPut screen will be like in below
Here in Show command I have used root user so I have got information.If the user, not a root level, will get the error.The user can see only the information relevant to privileges.If user hasn’t an access, get Error like
1 2 3 | ERROR 1044: Access denied for user:'bhumi@localhost' to database 'mysql' |
Remember: If you are not the root-level user, some of the commands will not be available for you or will display only limited information.
2. SHOW DATABASES [LIKE value]
This command just lists all the databases on your MySQL server.
1 2 3 | SHOW DATABASES; |
SHOW DATABASES have the server host list in MySQL database. You can also use the MySQL Show Command to get the host list. You can see that database of certain privileges unless you have the global SHOW DATABASES privilege.
3. SHOW [OPEN] TABLES [FROM database_name] [LIKE value]
After selecting a database, you can also use SHOW commands to list the tables in the database
1 2 3 | SHOW TABLES; |
If you add OPEN into SHOW TABLES command, you will get a list of all the tables in the table cache, which shows that how many times they’re cached.
1 2 3 | SHOW OPEN TABLES; |
Remember: If SHOW OPEN TABLES shows that tables are cached numerous times but aren’t currently in use so use FLUSH TABLES to free up that memory. FLUSH tables i have already explained earlier.
4. SHOW CREATE TABLE table_name
A very useful command is SHOW CREATE TABLE which shows the SQL statement used to create a table.
1 2 3 | SHOW CREATE TABLE category |
This Command gives same information that you would get if you dumped the table schema, but the SHOW CREATE TABLE command can be used quickly.
5. SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE value]
Now, you want to know the structure of the table then you can use SHOW COLUMNS command
1 2 3 | SHOW COLUMNS FROM category; |
The SHOW COLUMNS and DESCRIBE commands are alias of each other.
6. SHOW INDEX FROM table_name [FROM database_name]
The SHOW INDEX command displays information about all the indexes available in a table.
1 2 3 | SHOW INDEX FROM table_name |
7. SHOW TABLE STATUS [FROM db_name] [LIKE value]
Another helpful command is SHOW TABLE STATUS gives a table full results.
8. SHOW STATUS/VARIABLES [LIKE value]
The SHOW STATUS and SHOW VARIABLES commands will give important information about the database server.
The Most Useful status variables of SHOW STATUS are
Slow_queries : The number of queries that have taken more than default time that is 10 seconds
Connections : The number of connection attempts to the MySQL server during the current time period.
Max_used_connections : The maximum number of connections that have been in use simultaneously during the current time period.
You may also like:
UDF in MySQL
MySQL FLUSH Commands
MySQL CURSOR Explained
If you like the article, don’t forget to share it on Facebook and Twitter and do subscribe to our newsletter to keep receiving updates.How do you use Show command and which command is more usual for you? Leave me a note in the comments.
