Exploring MySQL

This lesson is a gentle exploration of MySQL.

We'll cover the following

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/1lesson.sh 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
Terminal
Loading...
  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.

  1. 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.

  1. 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.

  1. 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.

  1. 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;

  1. 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.