Welcome to my set of tutorials on MySQL. While there are loads of tutorials on MySQL (usually in combination with a scripting language) on the Net aimed at building applications etc.

MySQL Databases and the SQL Language

Welcome to my set of tutorials on MySQL. While there are loads of tutorials on MySQL (usually in combination with a scripting language) on the Net aimed at building applications etc, there are very few that want to use MySQL to teach the SQL syntax and with it a bit about databases for the novice. Thus these workshops are simply about that... seeing how a simple database can be created and manipulated by typing SQL statements. Knowledge of what is going on 'underneath' user friendly interfaces is pretty good to know in my opinion.

The workshops themselves are in themed parts, but it is intended that the reader work their way through each, building up SQL knowledge and understanding along the way. They are also meant to be a simple, if fairly comprehensive, introduction to MySQL and to SQL in general however the MySQL Documentation Site is still the best place to go for uptodate information and advanced features and techniques.

If you are not comfortable setting up a server or need more robust environment then you'll need a good host. webhostingsearch.com asked if I would recommend them and having checked out their site it seems a whole lot better then scrambling around trying to find hosts and compare them yourself - so why not start there...

Part 1 - Introducing SQL

What is MySQL and why are we using it?

MySQL is a powerful Relational Database Management System (RDBMS) which we will use to learn the basic principles of database and data manipulation using Structured Query Language (SQL) statements. SQL is a database language that is used to retrieve, insert, delete and update stored data. This is achieved by constructing conditional statements that conform to a specific syntax (i.e. the strict order required of elements for a statement to work).

Although it is assumed that most people reading this know what a database and SQL are (if not necessarily how to use them), there follows a little recap that it does no harm ;-)

How does MySQL work?

MySQL is a database server program and as such is installed on one machine, but can 'serve' the database to a variety of locations. To explain look at the following diagram.

The MySQL Server is installed on a Server and can be accessed directly via various client interfaces, which send SQL statements to the server and then display the results to a user. Some of these are:

A Local Client - a program on the same machine as the server. An example of this is the command line MySQL client software we will be using in the rest of the MySQL workshops (although there are other programs including graphical interfaces).

A Scripting Language - can pass SQL queries to the server and display the result.

A Remote Client - a programme on a different machine that can connect to the server and run SQL statements.

You can also use two more indirect methods.

Remote Login - You may be able to connect to the Server Machine to run one of its local clients.

Web Browser - you can use a web browser and scripts that someone has written (we're going to use this method for the rest of the workshop).

A bit about SQL

Structured Query Langauge is cross between a math-like language and an English-like language that allows us to ask a database questions or tell it do do things. There is a structure to this language: it uses English phrases to define an action, but uses math-like symbols to make comparisons. For example:

SELECT * FROM table;

Where 'SELECT', 'FROM' and 'table' are in English, but '*' is a symbol that means all.

It is important to learn SQL as it is common to almost all database programs and was developed specifically as a language used to retrieve, add and manipulate data in databases. You will find it not only here in MySQL, but underlying MS Access, MS SQL Server, and in every web-based database application. While it may seem confusing at first it is almost like telling a story or asking a question once you become comfortable with the syntax.

A Bit About Database Structure

Databases can be as complicated as you wish to make them... so lets start with simple and work out way up from there. A database can have many TABLEs holding data. Imagine a simple table of car information:

If you look at the blue Cell we call this a 'FIELD' and it has a value of 'Suzuki'.

This FIELD exists in the COLUMN named 'Manufacturer'. The 'Model' COLUMN is green in this example. All the FIELDs in the 'Model' COLUMN contain the same type of data (i.e. the model of the car).

Whereas a ROW (in this case red) contains a series of FIELDs, one in each COLUMN, together comprising a record about one car. This record represents the real world uniqueness of each thing we are recording (in this case a car) and thus is given a unique number (in database language the 'Primary Key') with which to identify it. In our simple table each unique number is stored as a FIELD in the 'carID' COLUMN.

Creating a First SQL Statement

As we have yet to create a database it would be difficult to construct some simple SQL statements to explain the above without first getting involved in some MySQL server administration. However as we saw above there are many ways to interact with a database and thus I have created a database and a 'cars' table filled with car info and provided a web browser interface to accept SQL statements and return the results for you to experiment with.

So open the web interface in a new browser window and switch between the two or print this out and work from it.

The first SQL statement we will look at is the SELECT statement. The basic SELECT statement has the following syntax.

SELECT columns FROM table [WHERE (condition)]

The WHERE condition is in square brackets as it is optional. So using our 'cars' table we can start issuing commands and you should see the actual data being displayed.

Note: As an SQL statement can span many lines of a script or when typing it in at a command line (this virtual workshop will 'format' the SQL statements over multiple lines to avoid overlapping and to aid readability). When using MySQL the statement is only deemed 'complete' when a semi-colon is typed at the end to signify that you have finished constructing your statement. So ensure you include a semi-colon.......

To display all the data from the 'cars' table we would issue the following command (where '*' is the wildcard symbol for all).

SELECT * 
FROM cars;

The result should be a large amount of data displayed (due to the quantity it may take a few moments to display). This is not very useful, but we can begin to restrict the output by including WHERE conditions. For example to display only the records that contain the data '95-98' in the 'Year' field, try the following command:

SELECT * 
FROM cars 
WHERE (Year = '95-98');

There are a couple of items of good practice that make life easier at this point. The first is that our conditions (the bit after WHERE) should be enclosed by brackets. This 'forces' the condition to be evaluated and is needed when you have nested conditions in complex queries, so you should to get into the habit of doing this from the beginning. Secondly, it is likely that you will at some point wish to display data from different tables using the same query - making it a good idea to also get into the habit of using a the full TABLE.COLUMN reference (as different tables sometimes have COLUMNs of the same name).

For example if we use another select statement, perhaps all records that have 'Volvo' as 'Manufacturer', we are explicit that we mean the Manufacturer COLUMN in the cars TABLE.

SELECT * 
FROM cars 
WHERE (cars.Manufacturer = 'Volvo'); 

As hinted at above, conditions can be combined to achieve better filtering of results, the simplist being to use the 'AND' operator

SELECT * 
FROM cars 
WHERE ((cars.Year = '95-98') 
AND (cars.Manufacturer = 'Volvo'));

This last statement should produce only one result and you can begin to see how using conditions can be useful in finding individual records.

A Tiny Bit about Operators

Operators are another tool that you can use within your SQL statement to refine your search for specific records.

SELECT * 
FROM cars 
WHERE ((cars.Year = '95-98') 
AND (cars.Manufacturer = 'Volvo'));

The above statement uses the 'AND' operator (it can also be expressed as '&&') to combine two conditions. Both conditions have to be met in order for the record to be displayed. We can also use the 'OR' operator (can be expressed as '||' ) to ask for a record to be displayed if either condition is met.

SELECT * 
FROM cars 
WHERE ((cars.Year = '95-98') 
OR (cars.Manufacturer = 'Volvo'));

The final operator we'll discuss here is the 'NOT' operator ('!' in case you were wondering), which is a bit more complicated. Rather than joining conditions together it becomes part of the condition, turning a positive into a negative. The following statement retrieves all records that do not contain 'Volvo' as 'Manufacturer'.

SELECT * 
FROM cars 
WHERE (cars.Manufacturer != 'Volvo');

As the 'NOT' operator has become part of the condition it can be used with another operator to combine positive and negative conditions. For example to retrieve the records that contain the data '95-98' in the 'Year' field but do not contain 'Volvo' as 'Manufacturer' enter the following.

SELECT * 
FROM cars 
WHERE ((cars.Year = '95-98') 
AND (cars.Manufacturer != 'Volvo'));

Restricting Columns

Before leaving our initial encounter with the SELECT statement we need to address one final component. In all the examples we have used so far the wildcard '*' has been used to retrieve all the COLUMNs. While this may be okay for a table that only has 7 COLUMNs, it would not work quite so well for a table with 20 COLUMNs. Thus it may be desirable to sometimes restrict which COLUMNs are returned.

If we look again at the seven fields that make up the 'cars' table

+-------+--------------+-------+------+-----------+--------+---------+
| CarID | Manufacturer | Year  | Car  | Model     | AirCon | CDMulti |
+-------+--------------+-------+------+-----------+--------+---------+

We may only be interested in seeing the 'Manufacturer','Year','Car' and 'Model' fields and thus we would construct a statement like so:

SELECT cars.Manufacturer, cars.Year, cars.Car, cars.Model 
FROM cars 
WHERE (cars.Year = '95-98');

Which, as you can see, asks for only certain columns to be returned - each field separated by a comma.