- Making the Connection
- Selecting a Database
- Learning About Databases and Tables
This chapter is from the book
This chapter is from the book
This chapter is from the book
Learning About Databases and Tables
But what if you don’t know the names of the available databases? And for that matter, how are clients like MySQL Workbench able to display a list of available databases?
Information about databases, tables, columns, users, privileges, and more is stored within databases and tables themselves (yes, MariaDB uses MariaDB to store this information). But these internal tables are generally not accessed directly. Instead, the MariaDB SHOW command can be used to display this information (information that MariaDB then extracts from those internal tables). Look at the following example:
•Input
SHOW DATABASES;
•Output
+--------------------+ | Database | +--------------------+ | information_schema | | crashcourse | | mysql | | forta | | coldfusion | | flex | | test | +--------------------+
•Analysis
SHOW DATABASES; returns a list of available databases. Included in this list might be databases used by MariaDB internally (such as mysql and information_schema in this example). Of course, your own list of databases might not look like those shown here.
To obtain a list of tables within a database, use SHOW TABLES;, as seen here:
•Input
SHOW TABLES;
•Output
+-----------------------+ | Tables_in_crashcourse | +-----------------------+ | customers | | orderitems | | orders | | products | | productnotes | | vendors | +-----------------------+
•Analysis
SHOW TABLES; returns a list of available tables in the currently selected database.
To show a table’s columns, you can use DESCRIBE:
•Input
DESCRIBE customers;
•Output
+--------------+-----------+------+-----+---------+----------------+ | 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 | | +--------------+-----------+------+-----+---------+----------------+
•Analysis
DESCRIBE requires that a table name be specified (customers in this example), and returns a row for each field containing the field name, its datatype, whether NULL is allowed, key information, default value, and extra information (such as auto_increment for field cust_id).
Other SHOW statements are supported too, including
- SHOW STATUS—Used to display extensive server status information
- SHOW CREATE DATABASE and SHOW CREATE TABLE—Used to display the MariaDB statements used to create specified databases or tables respectively
- SHOW GRANTS—Used to display security rights granted to users (all users or a specific user)
- SHOW ERRORS and SHOW WARNINGS—Used to display server error or warning messages
It is worthwhile to note that client applications use these same MariaDB SQL 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 MariaDB SQL commands that you can execute directly yourself.
Summary
In this chapter, you learned how to connect and log in to MariaDB; how to select databases using USE; and how to introspect MariaDB databases, tables, and internals using SHOW and DESCRIBE. Armed with this knowledge, you can now dig into the all-important SELECT statement.