Information about databases, tables, columns, users, privileges, etc. are stored within databases and tables themselves. But these internal tables are generally not accessed directly. Instead, the MySQL SHOW command is used to display this information.
SHOW DATABASES;
SHOW DATABASES; returns a list of available databases. This list might be included databases used by MySQL internally i.e. mysql and information_schema etc.
SHOW DATABASES;
Above statement display the following result:
+--------------------+ | Database | +--------------------+ | information_schema | | testdb | | mysql | | categories | | subcategories | | test | +--------------------+
SHOW TABLES;
To obtain a list of tables within a database, use SHOW TABLES;:
SHOW TABLES;
Above statement display the following result:
+-----------------------+ | Tables_in_testdb | +-----------------------+ | customers | | orderitems | | orders | | products | | productnotes | | vendors | +-----------------------+
SHOW TABLES; returns a list of available tables in the currently selected database.
SHOW COLUMNS;
SHOW can also be used to display a table's columns:
SHOW COLUMNS FROM customers;
Above statement display the following result:
+---------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +---------------+-----------+------+-----+---------+----------------+
SHOW COLUMNS requires that a table name be specified, and returns a row for each field containing the field name, its data type, whether NULL is allowed, key information, default value, and extra information.
The DESCRIBE Statement MySQL supports the use of DESCRIBE as a shortcut for SHOW COLUMNS FROM. In other words, DESCRIBE customers; is a shortcut for SHOW COLUMNS FROM customers;.
What Is Auto Increment
Some table columns need unique values. For example, order numbers, employee IDs, or customer IDs. Rather than have to assign unique values manually each time a row is added (and having to keep track of what value was last used), MySQL can automatically assign the next available number for you each time a row is added to a table. This functionality is known as auto increment.
More SHOW Statements
SHOW STATUS
SHOW STATUS is used to display extensive server status information
SHOW CREATE DATABASE and SHOW CREATE TABLE
These statements are used to display the MySQL statements used to create specified databases or tables respectively
SHOW GRANTS
SHOW GRANTS is used to display security rights granted to users (all users or a specific user)
SHOW ERRORS and SHOW WARNINGS
SHOW ERRORS and SHOW WARNINGS are used to display server error or warning messages
You can learn more about SHOW in the mysql command-line utility, execute command HELP SHOW; to display a list of allowed SHOW statements.
Note: Client applications use these same MySQL commands as you've seen here. Applications that display interactive lists of databases and tables, that allow for the interactive creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights management, and more, all accomplish what they do using the same MySQL commands that you can execute directly yourself.
What you've learned:
- what SQL is and why it is useful
- basic database terminology
- what is MySQL
- introduction to MySQL client utilities
- how to connect and log in to MySQL
- how to select databases using
USE - how to introspect MySQL databases, tables, and internals using
SHOW