MySQL Lab Guide - Cengage Learning: Transforming Learning

129 Pages·2007·4.37 MB·English

MySQL Lab Guide

A supplement to: Database Systems: Design, Implementation and Management
(International Edition)
Rob, Coronel & Crockett (ISBN: 9781844807321)
MySQL Lab Guide
Table of Contents

Lab Title Page
1 Introduction to MySQL 4
2 Building a database: Table by Table 15
3 Data Manipulation Commands 36
4 Basic SELECT statements 47
5 Advanced SELECT statements 63
6 Joining Tables 75
7 SQL functions 90
8 Subqueries 1
9 Views 123

Keeley Crockett 2
MySQL Lab Guide
Introduction to the MySQL Lab Guide
This lab guide is designed to provide examples and exercises in the fundamentals of SQL
within the MySQL environment. The objective is not to develop full blown applications
but to illustrate the concepts of SQL using simple examples. The lab guide has been
divided up into 9 sessions. Each one comprises of examples, tasks and exercises about a
particular concept in SQL and how it is implemented in MySQL.
On completion of this 9 week lab guide you will be able to:
• Create a simple relational database in MySQL.
• Insert, update and delete data the tables.
• Create queries using basic and advanced SELECT statements
• Perform join operations on relational tables
• Use aggregate functions in SQL
• Write subqueries
• Create views of the database

This lab guide assumes that you know how to perform basic operations in the Microsoft
Windows environment. Therefore, you should know what a folder is, how to maximize or
minimize a folder, how to create a folder, how to select a file, how you maximize and
minimize windows, what clicking and double-clicking indicate, how you drag, how to
use drag and drop, how you save a file, and so on.

MySQL, is one of the most popular Open Source SQL database management systems.
The lab guide has been designed on MySQL version 5.0.45 running on Windows XP
Keeley Crockett 3
MySQL Lab Guide
Professional. The MySQL Web site (http://www.mysql.com/) provides the latest
information about MySQL database management system.
It is important to note that MySQL is an open source database and is continually under
development. Each version and sub-version may implement SQL syntax differently and
changes are being made constantly. There are also problems with upward compatibility
between different versions. For example some SQL operations that work in versions 3.0
and 4.0 do not work in version 5.0. Furthermore, different variants of a version are
released in response to bugs that have been found by database developers who are using
the latest versions in their work. If an SQL command does not work as expected or
shown in this guide, please consult the MySQL web site for more information.

Keeley Crockett 4
MySQL Lab Guide

Lab 1: 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

Keeley Crockett 5
MySQL Lab Guide
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.
Keeley Crockett 6
MySQL Lab Guide

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
Keeley Crockett 7
MySQL Lab Guide

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

Keeley Crockett 8
MySQL Lab Guide
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.
Keeley Crockett 9
MySQL Lab Guide

Figure 5: Accessing the MySQL Reference Manual
Figure 6 shows the table of contents for the reference manual.
Keeley Crockett 10