Andrew Hudson - Fedora™ Unleashed, 2008 edition

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

You can also fill multiple rows with a single INSERTstatement, 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 Nevermindentry into the cd_collection table:

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

MySQL requires the NULLvalue 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 NULLin the INSERTstatement.

Normally, INSERTstatements 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.

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 SELECTstatement, which has the following syntax:

SELECT column1 , column2 , column3 FROM table_name WHERE search_criteria ;

The first two parts of the statement — the SELECTand FROMparts —are required. The WHEREportion 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 titleand yearcolumns 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 WHEREstatement to the SELECTstatement. For example, suppose that you want to find all the CDs in the cd_collectiontable 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 using logical ANDand logical ORstatements. For example, suppose that you want to refine the query so that it lists only those CDs that were not released in 2003. You would use a query like the following:

SELECT * FROM cd_collection WHERE rating = 5 AND year != 2003;

In SQL, !=means "is not equal to." Once again looking at the table from Figure 18.2, you can see that this query returns the rows for Trouser Jazz and The Two Towers but doesn't return the row for Life for Rent because it was released in 2003.

So, what if you want to list all the CDs that have a rating of 3 or 4 except those released in the year 2000? This time, you combine logical ANDand logical ORstatements:

SELECT * FROM cd_collection WHERE rating = 3 OR rating = 4 AND year != 2000;

This query would return entries for Mind Bomb , Natural Elements , and Combat Rock . However, it wouldn't return entries for Adiemus 4 because it was released in 2000.

One of the most common errors among new database programmers is confusing logical ANDand logical OR. For example, in everyday speech, you might say "Find me all CDs released in 2003 and 2004." At first glance, you might think that if you fed this statement to the database in SQL format, it would return the rows for For All You've Done and Life for Rent . In fact, it would return no rows at all. This is because the data base interprets the statement as "Find all rows in which the CD was released in 2003 and was released in 2004." It is, of course, impossible for the same CD to be released twice, so this statement would never return any rows, no matter how many CDs were stored in the table. The correct way to form this statement is with an ORstatement instead of an ANDstatement.

SQL is capable of far more than is demonstrated here. But as mentioned before, this section is not intended to teach you all there is to know about SQL programming; rather, it teaches you the basics so that you can be a more effective DBA.

If you are just starting out and learning about using a database with Linux, the first logical step is to research which database will best serve your needs. Many database soft ware packages are available for Linux; some are free, and others cost hundreds of thou sands of dollars. Expensive commercial databases, such as Oracle, are beyond the scope of this book. Instead, this chapter focuses on two freely available databases: MySQL and PostgreSQL.

Both of these databases are quite capable, and either one could probably serve your needs. However, each database has a unique set of features and capabilities that might serve your needs better or make developing database applications easier for you.

Until recently, the speed choice was simple: If the speed of performing queries was para mount to your application, you used MySQL. MySQL has a reputation for being an extremely fast database. Until recently, PostgreSQL was quite slow by comparison.