Using the MySQL and MySQL Shell command line applications

Our textbook book Learning SQL performs SQL operations via a command lime applicaiton. On Windows you will use the “MySQL Shell” application, while on MacOS will use the mysql terminal application. While it is possible to type all of your SQL commands out on the terminal, it is helpful to run the commands using a script file and use the command line program to execute them.

MySQL Shell Windows

NOTE: The leading mysql> is not a part of the command but to illustate commands executed using the SQL command line client

When you open the MySQL Shell you can login to MySQL using the command:

mysql> \connect YourUsername@localhost`

Make sure to change YourUsername to the local account you made for MySQL. Once you have logged in, you may be asked to save your login for future use. After you login, you will need to switch to SQL mode using the command:

mysql> \sql

Once you are logged in and have activated sql mode, you can run queries against a database but remember that you must identify what database you wish to run the commands against using the use TheDatabaseName;. You can enter this command on the command line or place it at as the first line in the script file you plan to run.

For example, suppose we want to get data from the Actor table in the Sakila database. We can make a script named get_actors.sql (the specific name isn’t important, but the extension is) that contains the following text:

use sakila;
SELECT * FROM actor LIMIT 10;

We can run this script via the sql command line program using the command:

mysql> source PathToTheScript/get_actors.sql

You can easily get the path to the script by dragging and dropping the file to the command line applicaiton. Note if the file path contains a space, you might need to wrap the path in quotes.

mysql> source "Path To The Script/get_actors.sql"

MySQL MacOS

NOTE: The leading $ and mysql> are not a part of the command but to illustate a bash command or a SQL command respectively

Connecting to MySQL can be done with the command from the macOS terminal:

$ mysql -p 

The above command will work if your MySQL username is the same as your Mac account name, otherwise you will need to use the following command instead:

$ mysql -u YourUserName -p

Once you are logged in, you can run queries against a database but remember that you must identify what database you wish to run the commands against using the use TheDatabaseName;. You can enter this command on the command line or place it at as the first line in the script file you plan to run.

For example, suppose we want to get data from the Actor table in the Sakila database. We can make a script named get_actors.sql (the specific name isn’t important, but the extension is) that contains the following text:

use sakila;
SELECT * FROM actor LIMIT 10;

We can run this script via the sql command line program using the command:

mysql> source PathToTheScript/get_actors.sql

You can easily get the path to the script by dragging and dropping the file to the command line applicaiton. Note if the file path contains a spaces, a drag and drop of the file on macOS will add backslash escape characters before each space like this:

mysql> source My\ Path\ To\ The\ Script/get_actors.sql

You need to delete the backslashes and the following command should run the script:

mysql> source Path To The Script/get_actors.sql