MySQL Shell

Get introduced to the MySQL Shell.

We'll cover the following

Generally speaking, two possibilities exist for allowing users to interact with a MySQL server to access a database. The first of the two possibilities is mysql, a command-line interface (CLI) that enables access to a MySQL server from a terminal (e.g., /bin/bash). The second possibility is provided by MySQL Shell, a software similar to mysql, that enables access to a MySQL server using a graphical user interface. In addition to mysql, MySQL Shell provides access to the X DevAPI. The X DevAPI enables using a MySQL server as a document store rather than a relational database. MySQL Shell also provides scripting capabilities in JavaScript and Python to that extent. However, as this course focuses on SQL rather than NoSQL, we will not further cover the X DevAPI or MySQL Shell. Rather, we will work with the mysql CLI for the remainder of this course.

Press + to interact
A shell to a MySQL server on macOS in Warp
A shell to a MySQL server on macOS in Warp

The mysql CLI commands

Let us discuss how to set up a MySQL server and access it through mysql. This will enable us to continue the learning journey with MySQL beyond the scope of this course.

Setup of MySQL server

The simplest way to set up a MySQL server is through Docker, particularly, using the mysql-server Docker image. Of course, knowing Docker is not a requirement for taking this course. However, we encourage looking into Docker, so feel free to dive into Docker’s documentation.

With Docker installed, there’s only a single command necessary to get a MySQL server up and running:

Press + to interact
docker run --name mysql-server -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql/mysql-server:8.0

Access to the MySQL server through mysql

As it is more involved to connect from the host to the MySQL server running inside a Docker container, we will connect to the MySQL server from within the Docker container. Hence, we need to get into the Docker container, first. To do so, we issue the following from a terminal:

Press + to interact
docker exec -it mysql-server /bin/bash

Next, to connect to the MySQL server with mysql, we issue mysql -p and enter the password (i.e., root). From now on, we can issue mysql commands to interact with the MySQL server and issue SQL commands as we are used to. For example, to create a database, we can simply issue:

Press + to interact
-- Backticks are necessary here since we issue the SQL command from a `bash` terminal.
CREATE DATABASE `advanced-sql-course`;

To verify the result of our query, run SHOW DATABASES;. To switch the database we use by default, issuing USE `advanced-sql-course`; is sufficient.

Playground

To get familiar with the mysql CLI and gain hands-on experience, we can use the code widget below. It enables us to issue the SQL commands from above to the MySQL server:

Press + to interact
-- Create a database as explained above.
CREATE DATABASE `advanced-sql-course`;
-- List all databases available on the MySQL server.
SHOW DATABASES;
-- Use the database that we just created by default.
USE `advanced-sql-course`;
-- Create a temporary table for testing purposes.
CREATE TEMPORARY TABLE Bar
(
    bar INT,
    foo TEXT
);
-- Output the structure of the temporary table that we just created.
DESCRIBE Bar;
-- Record a set of artifical values in the temporary table.
INSERT INTO Bar
VALUES (0, 'a'),
       (1, 'b'),
       (2, 'c');
-- Output the temporary table's values for inspection.
-- The `TABLE` statement is a shortcut for: SELECT * FROM Bar;
TABLE Bar;

After running the SQL file above, we can observe that a new database with the name advanced-sql-course has been created (line 2). The SHOW DATABASES statement visualizes this fact where the USE statement ensures that the new database is used by default (lines 4–6).

The mysql CLI outputs tables in an ASCII format, which makes it easier to understand the command’s output. This is especially useful if we are looking at actual tables as below, which resemble the output of lines 15 and 24 from the second to last SQL file:

Press + to interact
mysql> DESCRIBE Bar;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| bar   | int  | YES  |     | NULL    | NULL  |
| foo   | text | YES  |     | NULL    | NULL  |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> TABLE Bar ORDER BY foo;
+------+------+
| bar  | foo  |
+------+------+
|    0 | a    |
|    1 | b    |
|    2 | c    |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT *
    -> FROM Bar
    -> ORDER BY foo;
+------+------+
| bar  | foo  |
+------+------+
|    0 | a    |
|    1 | b    |
|    2 | c    |
+------+------+
3 rows in set (0.00 sec)

From this output, we learn that DESCRIBE does not only output a table’s fields and their types but also further information about whether these fields are allowed to be NULL or are part of any key.

The TABLE Bar; statement is simply a handy shortcut for SELECT * FROM Bar;, their output is identical here. While TABLE does not allow specifying a set of columns for display, we can still combine the statement with the ORDER BY and LIMIT clauses we are familiar with from the SELECT clause.