Exploring MySQL
This lesson is a gentle exploration of MySQL.
Exploring MySQL
In this lesson, we’ll start with the initial state of a MySQL installation. The
DBMS doesn’t contain any user databases as you’ll shortly observe. Follow
the steps below for this exercise:
Connect to the terminal below by clicking in the widget. Once connected,
the command line prompt will show up. Enter or copy and paste the
command ./DataJek/Lessons/[Link] and wait for the mysql prompt
to start-up.
-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
SHOW DATABASES;
-- Query 2
USE mysql;
-- Query 3
SHOW CREATE DATABASE mysql;
-- Query 4
SHOW TABLES;
-- Query 5
DESCRIBE user;
-- Query 6
SHOW CREATE TABLE servers;
-- Query 7
SHOW COLUMNS FROM servers;
Terminal
1. Now execute the following command and observe the output:
SHOW DATABASES;
You’ll see four databases that are used by the system. The query only
shows databases that you have the privilege to view.
2. In order to explore a particular database, we need to tell the DBMS
that we want our queries directed to the database of our choice. For
our case, let’s pick the existing MySQL database by executing the
following command:
USE mysql;
The MySQL prompt will respond with a “Database changed”
message. The USE statements allow us to let MySQL know the
database we want to interact with. Any queries we execute in the
future are directed to the selected database.
3. The database MySQL has been created for us already. We can
examine how the database was created using the following query:
SHOW CREATE DATABASE mysql;
The line /*!40100 DEFAULT CHARACTER SET latin1 */ is a comment
and encloses MySQL extensions to the SQL standard. For instance,
the numeral 40100 indicates the minimum version of MySQL that can
process the SHOW CREATE DATABASE query.
4. Let’s explore the MySQL database further. We’d like to know what
tables the MySQL database holds. We can do this by using the SHOW
statement as follows:
SHOW TABLES;
The response is a long list of tables, the mysql database holds.
5. We can also explore the structure of a table using the DESCRIBE
command. Let’s describe the user table as follows:
DESCRIBE user;
The output will show the various columns the table is made of, the
data type of each column, and other related metadata.
6. We can also use the SHOW statement to display how the table was
created. For instance, the following query shows how the servers
table was created:
SHOW CREATE TABLE servers;
7. We can display the column information for a table using the SHOW
statement. For example:
SHOW COLUMNS FROM servers;
This completes a brief exploratory tour of mysql and the various
commands we can use to explore it.