Getting Started With MySQL

This lab will focus on getting a working MySQL installation, entering a sample database and starting to learn some basic SQL queries.

Like most relational databases, MySQL uses a client-server approach even when there is only one client. This means that a server process runs continuously waiting for connections from a client process. One or more clients connects to the server and then issues SQL commands, while the server takes care of concurrency issues and the actual storage.

Setting Up

These instructions relate to setting up MySQL on the Windows XP machines in the CSSE computer labs.

When you log in to your Windows account, the MySQL programs are already be installed. On your desktop is a folder named Databases. When you open this folder you will see it contains several applications and scripts:

A "start database server" script that will intiate the MySQL server. At the beginning of each lab you will need to start the MySQL server process by executing this script.
A "stop database server" script that will terminate the MySQL server. This script will cleanly terminate the running MySQL process. It will be automatically executed when you log off from your Windows account.
The DBVisualizer application, which allows you to interrogate any database. You will need to configure it the first time you launch it. DBVisualizer can be used with a number of database management systems.
MySQL Query Browser is an application much like DBVisualizer except it is specific to MySQL.
The DIA application is a simple diagram tool that allows you to build ER diagrams. You can also use Visio under the Microsoft Office Suite.
MySQL Administrator is an application that allows you to manage the server and the databases.
CMD is the Windows command shell, within which you type and execute MySQL commands.

You can physically transfer the entire data folder "MySQL_Data_Files" to another computer off our network (say your home computer) and work on it there, provided you deposit it in the correct location for your home installation of MySQL.

Creating your personal data area

In the labs, MySQL has been configured to use the folder

H:configMySQL_Data_Filesdata

as its storage area, but this area has been created for you and is the location where MySQL will store and keep track of the databses you construct.

You should never need to access H:configMySQL_Data_Filesdata directly as it will be controlled by MySQL itself.

WARNING: Do NOT use this directory yourself - for example to store the MySQL source files - because it is ONLY for the use of the MySQL server.

If you set up MySQL at home, then you will need to edit the file "my.ini" that it located in the "binMySQL" directory and alter the line

#Path to the database root

datadir=H:/MySQL_Data_Files/data

to reflect the location that you choose on your local machine.

Starting the MySQL server

Using the command-line environment on Windows enter the command

start mysqld-nt --standalone

In response, a blank screen may appear and then immediately vanish, but otherwise there will be no output. However, if all is well, the mysql server will now be running in the background waiting for connections. To check this, you can use the command-line administration tool "mysqladmin". Typing

mysqladmin -u root ping

should get the response

mysqld is alive

When initially created, the MySQL database only has a single user called "root" that is the overall controller of that particular server and all its databases. Normally as a user, you would be assigned a user name and password and have limited privileges, but you will have complete control over your own server in this unit.

Starting a MySQL client

The simplest MySQL client is a command-line interface that just allows you to interactively type SQL statements and view the results. There are many more sophisticated GUI-based clients but the command-line interface is a fundamental tool for DBAs.

To start this client, simply type

mysql -u root

into the command line.

After this command you should get a brief welcome message starting with the message

Welcome to the MySQL monitor.

and ending with the command prompt

mysql>

which indicates that the system is waiting for further input.

Try it out by typing in the command (don't forget the semi-colon)

show databases;

and see what you get.

One unwelcome message that you may get many times is

ERROR 1064 (42000): You have an error in your SQL syntax.

Database Initialization

The next task is to create a database and then some tables (relations) within that database and to start practicing SQL queries.

To create a database with the name databasename, you use the syntax

CREATE DATABASE databasename;

and to remove that database, together with all the tables it contains you use

DROP DATABASE databasename;

For this lab, we will be using a sample database and table from the book "MySQL" by Paul Dubois.

Start by creating a database called "sampdb" (short for sample database)

CREATE DATABASE sampdb;

SQL Queries

The fundamental command for retrieving information from an SQL database is

SELECT

The fundamental (simplified) syntax of this statement is just

SELECT <what to retrieve>

FROM <table or list of tables>

WHERE <conditions>;

A SELECT statement can be used to do something as simple as find out a simple fact from a single table or answer complex queries involving several different tables.

Notice that the output of a SELECT statement is itself a relational table and so, as we will discover later, it can itself be used as one of the tables for a further SELECT and so on.

For this week's lab, we will just use the table of US Presidents. In order to start querying this data, you will first need to create the table and add its rows.

There are SQL scripts to do that available from the Downloads page on the Web site.

You should start up MySQL and select the sample database to use

mysql> use sampdb;

Then use the "source" command to read in the contents of the file create_president.sql which will create the empty table.

mysql> source create_president.sql;

The "source" command is looking for the file called "create_president.sql" and the first place it will look is the directory that you were in when you started the MySQL client. This will usually be the H: drive. If you save the file in some other location, such as H:/Databases/Lab1 then you should make sure that you change into that directory ("cd Databases/Lab1") before starting mysql. Or you can use the full path name of the file in the "source" command.

Do NOT store the file in MySQL_Data_Files or any subdirectory of that directory; that is only for the use of the server.

Now use the same command to populate the table

mysql> source insert_president.sql;

Now it's time to write some statements and see what they do - first some example statements for you to run and then some for you to write.

Make sure that you keep a workbook recording the statements that you write and bring it to each tutorial.

Selecting columns

These commands select one or more columns from the table but if the "where"-clause is omitted, then it applies to all the rows.

Try these statements on the president database, and experiment with the syntax of the SELECT statement.

The * in the following queries is a wildcard that means that all the columns should be selected

SELECT * FROM president;

SELECT * FROM president LIMIT 10;

SELECT * FROM president LIMIT 30,5;

You can select just the named columns, and get them presented in any order

SELECT first_name, last_name FROM president;

SELECT last_name, first_name FROM president;

SELECT first_name FROM president;

SELECT first_name FROM president LIMIT 3,2;

SELECT DISTINCT first_name FROM president;

You can perform operations on the selected columns, and even rename them

SELECT first_name AS name FROM president;

SELECT CONCAT(first_name, last_name) AS fullname FROM president;

SELECT REVERSE(first_name) FROM president;
The operations that you can perform on strings are located in Section 12.3 of the MySQL reference manual (there is a link to this manual in the navigation bar on the left).

You can sort the results of your queries by using the ORDER statement.

SELECT * FROM president ORDER BY first_name;

SELECT * FROM president ORDER BY first_name DESC;

SELECT * FROM president ORDER BY state;

In order to narrow down the rows that are selected, you add a condition using the WHERE clause; notice how the second of these two queries does not do quite what you expect. Check the documentation to see how the third query fixes this.

SELECT * FROM president WHERE last_name = "Adams";

SELECT * FROM president WHERE first_name = "George";

SELECT * FROM president WHERE LOCATE("George",first_name) > 0;

The WHERE clause can check whether a value is NULL or not; in this table a NULL value for the date-of-death is used to signify that the person is still living.

SELECT * FROM president WHERE death IS NULL;

SELECT * FROM president WHERE death IS NOT NULL;
Now using these commands, and by using the reference manual where necessary write SQL queries that will enable you to answer the following questions: REMEMBER TO WRITE THESE DOWN IN YOUR WORKBOOK.

Which president has the longest last name?

Among deceased presidents, who died youngest and who died oldest? [HINT: Use TO_DAYS from Section 12.5]

From how many different states have the presidents come?

How many presidents' first names end with the letter "e" ? [HINT: Use RIGHT from Section 12.3]

How many presidents died in the 20th century?

Are you less likely to become president if you are born on a Wednesday?

Think of a challenge question - that is, a question that can be answered with an SQL query that uses just this one table and the features above. The best one(s) may be used in future tutorials, labs or even the exam.

Lab One