Introduction to SQL

SQL is an acronym for Structured Query Language and is the standard language for interaction with databases. SQL is both an ISO (International Organization for Standardization) and ANSI (American National Standards Institute) standard, but many database packages contain proprietary extensions that are not part of the standard. As a matter of fact, there are not many database servers that would have 100% support of the SQL standard.

Let's first look at some simple interactions that we can perform using the SQL language. Let's say, for example, that we had a table called user_table, which held information on players in a Java game. The information is a unique identification number followed by the player's username, password, and e-mail address. Here is a graphical representation of the table in the database with some sample data in it:

Figure 14-5: user_table containing some sample data

Assuming all the data had been previously entered into the database and we wanted to get the password of the player who had the username of george, we would use the following SQL statement to retrieve his password:

SELECT password FROM user_table WHERE username = 'george';

As you can see from the previous statement, the great thing about SQL is its similarities to the English language. By that I mean it is very easy to read and understand what the statement is trying to do. In this example, we are "SELECT"ing, which means retrieving, the password field "FROM" the user_table "WHERE" the username is equal to the string "george." When this query is executed, the database would then return the password (which is geo321) from the user with the username george.

So what would happen if there were two users with the username george? Well, the database would return a recordset (there is the possibility of getting an actual array to the programming language, but it must first be retrieved row-by-row from the database via the recordset object) containing the passwords for the records that it found. Unless you specifically want users to have the same usernames, the best way around this would be to check for duplicate usernames when you are inputting the users' data into your game or defining the username as a primary key. We will find out how to do this in the next chapter, along with many more useful tips and features of SQL.

Let's look at another simple example for listing all the high scores from a game in a descending order (i.e., start by getting the highest score, then the next highest, etc.). Let's assume our user_table also had a field called highscore that recorded the players' high score in a game. The default ordering is in ascending order; therefore the database would retrieve the lowest score first if we selected all the high scores from the database. The solution to this is to use the DESC keyword, which tells the database to order the results in descending order (i.e., highest first). Here is the SQL statement that we would require to do this:

SELECT username,highscore FROM user_table ORDER BY highscore DESC;

Note 

If you are defining an ordering field, it must be defined also in the select field section. You cannot sort your resultset with a field that is not included in the result.

This previous statement would retrieve the username and high score from the user_table ordered by the highscore field in descending order. Note how we can retrieve more than one field in a single statement, separating fields you wish to retrieve with commas. We can also use a *, which is known as a wildcard and simply tells the database to return all the fields from the table rather than a defined number of fields, like in the first example where we just retrieved the password field. Don't worry too much about this at the moment, as we will cover this in depth in the next chapter where everything will become clear.

Let's now look at one final example of the more powerful features of SQL and databases. This is the use of regular expressions. A regular expression in simple terms is a way to express to SQL a specific pattern of text to look for in the fields in a table. We touched on regular expressions earlier in Chapter 3; regular expressions are supported in the J2SDK 1.4 also. Although, note that the regular expressions used in SQL are not compatible with the ones used in Java.

Here is an example statement that would retrieve all the usernames from the user_table that begin with the letter G:

SELECT username FROM user_table WHERE username LIKE 'G%'; 

Notice that all we are really doing differently here is using the LIKE keyword instead of the equals sign. Also, the % acts as a wildcard when using the LIKE keyword. Therefore, the statement will select (retrieve) any usernames that start with G, as the names that start with G will fit the regular expression 'G%'. If we wished to select any names that just contained the letter G anywhere in the string, we would use the following statement:

SELECT username FROM user_table WHERE username LIKE "%G%";

As you can see, all we have changed is the regular expression by adding another wildcard (%) before the G, allowing zero or more different characters both before and after the G when the database is searching.