Running a script

A script is simply a series of commands in a text file. Putting them into a script means that you can execute them all in one go. By convention, SQL scripts have the file extension .sql. Figure 1, shows that the directory project_1 containstwo SQL scripts. Scripts can be executed once you are connected to MySQL.

You can start a MySQL session with the command

mysql -u <username> -p

Once you hit RETURN after this command, you will be prompted for your MySQL password (which may be different from the password you use to log in on the server). As you type, the characters will not be shown for security. Hit RETURN again after typing the whole password and you will be connected. You should see a welcome message and the MySQL prompt as shown in Figure 2.

Figure 2. MySQL prompt

Like working at the Linux command prompt, there are no visual cues to help you find your way around. You therefore need to know a few useful MySQL commands. Notice that all SQL commands (except use) must be terminated with a semicolon.

SHOW DATABASES;       -- List the available databases
USE <db>              -- Set database <db> as your working database
SHOW TABLES;          -- List the tables in your working database
DESCRIBE <table_name> -- List the columns in table <table_name> (Can be abbreviated to DESC)

(Note: You may have a shortcut set up for you that simplifies the job of connecting to MySQL)

The concept of the working directory is also important when you want to run a script file in MySQL. Your current directory at the point you start your MySQL session continues to be your current directory when you are working at the MySQL prompt. This means that you can refer to files in your current directory just by their names, but if you want to run a script from somewhere else, you will need to use an absolute reference or construct a relative path to the file. The simplest approach is to change into the appropriate directory before starting you MySQL session.

The command to run a script at the MySQL prompt is

SOURCE <file_name>  -- Run the commands in script file <file_name>

In the example above where we assume that the MySQL session was started from the home directory, we could run the script schema.sql with the commands

SOURCE /home/myhome/project_1/schema.sql  -- Absolute reference
SOURCE project_1/schema.sql               -- Relative reference

Changing into the project_1 directory before starting the MySQL session eliminates the complications:

SOURCE schema.sql                         -- File in current directory