Using MySQL - Introducing SQL

This is the first in a series of MySQL workshops designed to introduce MySQL and SQL statements to a novice developer.

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:

CarID	Manufacturer	Year	Car	Model	AirCon	CDMulti
1094	Subaru	91	Legacy	2000	FALSE	FALSE
1095	Suzuki	95	Vitatra	1600	FALSE	FALSE
1096	Toyota	97	Corolla	1300	FALSE	FALSE
1097	Volkswagen	95	Golf3	1600	FALSE	FALSE

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'));

There are also other operators, but they will be discussed in a later part of the MySQL Virtual Workshop series.

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.

That's the end of this introductory Virtual Workshop - in the next part we will look at data and structures. If you want you can also attempt the following open-ended mini exercise.

Mini Exercise

Try creating your own combinations of conditions and operators to retrieve data, for more practice with the SELECT statement.