Starting MySQL
The learning objectives of this lab are to
• Learn how to start MySQL
• Learn how to use the MySQL command line client window
• Obtain help in MySQL
1.1 Starting MySQL
Before starting this guide, you must obtain a user ID and a password created by your
database administrator in order to log on to the MySQL RDBMS. How you connect to
the MySQL database depends on how the MySQL software was installed on your server
and on the access paths and methods defined and managed by the database administrator.
You may therefore need to follow specific instructions provided by your instructor,
College or University. This section will describe how to start MySQL from a Windows
XP installation of MySQL 5.0.45.
To start MySQL you would:
1. Select the Start button
2. Select All Programs and then MySQL
3. Select MySQL Server 5.0
4. Click on the MySQL Command line client
The MySQL command line client window should then open as shown in Figure 1.
Figure 1: MySQL command line client window
Enter your password to log on to MySQL as shown in Figure 2.
Figure 2: Logging on to MySQL
Once you have successfully logged on you will see the opening screen as shown in
Figure 2. To work in MySQL requires you to type in commands. For example typing in
the following will show you a list of help commands shown in Figure 3:
mysql> \h
Figure 3: Help commands in MySQL
Figure 3 shows some additional sources of help available from three different websites. It
also displays a list of commands and the shortcuts for running these commands. If you
want help about a specific command you can type the word help followed by the name of
the command. For example to display information about how to create a database you
would type:
mysql> help create database
Figure 4 shows the results of executing this command.
Figure 4: Example Help command
A full list of help topics available through the command line can be found by first typing:
mysql> help contents
However to get more detailed help you would use the MySQL reference manual. If you
are using MySQL from a Windows XP installation, then you can access the manual via
the programs menu as shown in Figure 5.
Figure 5: Accessing the MySQL Reference Manual
Figure 6 shows the table of contents for the reference manual.
Figure 6: Contents of the MySQL Reference Manual
1.2 Creating Databases from script files
In this section you will learn how to create a small database called SaleCo from a script
file. The SQL script file SaleCo.sql for creating the tables and loading the data in the
database are located in the Student CD-ROM companion. The database design for the
SaleCo database is shown in Figure 7 in the form of an Entity Relationship Diagram
(ERD).
Figure 7 The SaleCo Database ERD
Before creating any tables, MySQL requires you to create a database by executing the
CREATE DATABASE command. To create a database called SaleCo you would type
the following:
mysql> CREATE DATABASE SALECO;
Notice that you need a semi-colon to end the command. Figure 8 shows the successful
creation of this database.
Figure 8 Creating the SaleCo Database
Task 1.1 Create the SALECO database as shown in Figure 8.
To check to see if your database has been created you need to use the SHOW
DATABASES command which lists the databases on the MySQL server host. You will
only be able to see those databases for which you have some kind of privilege.
Task 1.2 Execute the following MySQL command to show the databases that you
currently have access to (Figure 9 is a guide only to what you should see). Check
that you can see the SALECO database that you have just created.
mysql> SHOW DATABASES;
Figure 9 Executing the SHOW DATABASES command
To work with any specific database you first have to select it. When you first login to
MySQL, the default database is always selected, so you need to execute the USE
command followed by the name of the database that you want to use.
Task 1.3 Execute the following MySQL command to begin using the SALECO
database.
mysql> USE SALECO;
MySQL will then inform you that the database has changed.
Task 1.4 To create the SaleCo database from a MySQL script file you would enter
the following command:
mysql> SOURCE C:\MYSQL\SALECO2.SQL
Note that in order for this command to work correctly you should have copied the script
files accompanying this Lab guide into the directory C:\MYSQL\. If your files are located
in a different directory then change the path accordingly.
The command SOURCE will load and execute the script to create the SaleCo database.
Notice that prompts will indicate that tables are being created and data added as shown in
Figure 10. When the script has completed executing, use the SHOW TABLES command
as shown in Figure 10, to check if all five tables have been created.
Figure 10. Creating the SaleCo database
Note
When you run the script for the first time, you will see some error messages on the
screen. These error messages are caused by the script attempting to DROP the
database tables before they have been created. Including SQL DROP commands in a
script that is being used for development is a good idea to ensure that if changes are
made to the database structure, all tables are then recreated to reflect this change. If
you run the script again you will see that the error messages no longer appear.
Note
Chapter 8 Introduction to Structured Query Language and Chapter 9, Advanced SQL
should be studied alongside this lab guide.
