Fedora™ Unleashed, 2008 edition

> SQL commands are not case sensitive — For example, CREATE TABLE, create table, and Create Table are all valid.

> Whitespace is generally ignored — This means you should use it to make your SQL commands clearer.

The following example shows how to create the table for the cd_collection database:

CREATE TABLE cd_collection
(
 id INT NOT NULL,
 title VARCHAR(50) NOT NULL,
 artist VARCHAR(50) NOT NULL,
 year VARCHAR(50) NOT NULL,
 rating VARCHAR(50) NULL
);

Notice that the statement terminates with a semicolon. This is how SQL knows you are finished with all the entries in the statement. In some cases, the semicolon can be omitted, and we will point out these cases when they arise.

TIP

SQL has a number of reserved keywords that cannot be used in table names or field names. For example, if you keep track of CDs you want to take with you on vacation, you would not be able to use the field name select because that is a reserved keyword. Instead, you should either choose a different name (selected?) or just prefix the field name with an f, such as fselect.

Inserting Data into Tables

After you create the tables, you can put data into them. You can insert data manually with the INSERT statement, which uses the following syntax:

INSERT INTO table_name VALUES('value1' , 'value2', 'value3', ...);

This statement inserts value1, value2, and so on into the table table_name. The values that are inserted constitute one row, or record, in the database. Unless specified otherwise, values are inserted in the order in which the columns are listed in the database table. If, for some reason, you want to insert values in a different order (or if you want to insert only a few values and they are not in sequential order), you can specify in which columns you want the data to go by using the following syntax:

INSERT INTO table_name (column1,column4) VALUES('value1', 'value2');

You can also fill multiple rows with a single INSERT statement, using syntax such as the following:

INSERT INTO table_name VALUES('value1', 'value2'),('value3', 'value4');

In this statement, value1 and value2 are inserted into the first row and values and value4 are inserted into the second row.

The following example shows how you would insert the Nevermind entry into the cd_collection table:

INSERT INTO cd_collection VALUES(9, 'Nevermind', ''Nirvana', '1991, ''NULL);

MySQL requires the NULL value for the last column (rating) if you do not want to include a rating. PostgreSQL, on the other hand, lets you get away with just omitting the last column. Of course, if you had columns in the middle that were null, you would need to explicitly state NULL in the INSERT statement.

Normally, INSERT statements are coded into a front-end program so that users adding data to the database do not have to worry about the SQL statements involved.

Retrieving Data from a Database

Of course, the main reason for storing data in a database is so that you can later look up, sort, and generate reports on that data. Basic data retrieval is done with the SELECT statement, which has the following syntax:

SELECT column1, column2, column3 FROM table_name WHERE search_criteria;

The first two parts of the statement — the SELECT and FROM parts —are required. The WHERE portion of the statement is optional. If it is omitted, all rows in the table table_name are returned.

The column1, column2, column3 indicates the name of the columns you want to see. If you want to see all columns, you can also use the wildcard * to show all the columns that match the search criteria. For example, the following statement displays all columns from the cd_collection table:

SELECT * FROM cd_collection;

If you wanted to see only the titles of all the CDs in the table, you would use a statement such as the following:

SELECT title FROM cd_collection;

To select the title and year of a CD, you would use the following:

SELECT title, year FROM cd_collection;

If you want something a little fancier, you can use SQL to print the CD title followed by the year in parentheses, as is the convention. Both MySQL and PostgreSQL provide string concatenation functions to handle problems such as this. However, the syntax is different in the two systems.

In MySQL, you can use the CONCAT() function to combine the title and year columns into one output column, along with parentheses. The following statement is an example:

SELECT CONCAT(title,' (',year, ')') AS TitleYear FROM cd_collection;

That statement lists both the title and year under one column that has the label TitleYear. Note that there are two strings in the CONCAT() function along with the fields — these add whitespace and the parentheses.

In PostgreSQL, the string concatenation function is simply a double pipe (||). The following command is the PostgreSQL equivalent of the preceding MySQL command:

SELECT (genus||'' ('||species||')') AS TitleYear FROM cd_collection;

Note that the parentheses are optional, but they make the statement easier to read. Once again, the strings in the middle and at the end (note the space between the quotes) are used to insert spacing and parentheses between the title and year.

Of course, more often than not, you do not want a list of every single row in the data base. Rather, you want to find only rows that match certain characteristics. For this, you add the WHERE statement to the SELECT statement. For example, suppose that you want to find all the CDs in the cd_collection table that have a rating of 5. You would use a statement like the following:

SELECT * FROM cd_collection WHERE rating = 5;

Using the table from Figure 18.2, you can see that this query would return the rows for Trouser Jazz, Life for Rent, and The Two Towers. This is a simple query, and SQL is capable of handling queries much more complex than this. Complex queries can be written