Andrew Hudson - Fedora™ Unleashed, 2008 edition

FIGURE 18.1 In this visualization of how an RDBMS stores data, the database stores four records (rows) that include name and address information, divided into seven fields (columns) of data.

In the example shown in Figure 18.1, the database contains only a single table. Most RDBMS setups are much more complex than this, with a single database containing multiple tables. Figure 18.2 shows an example of a database named sample_database that contains two tables.

In the sample_databaseexample, the phonebooktable contains four records (rows) and each record hold three fields (columns) of data. The cd_collectiontable holds eight records, divided into five fields of data.

If you are thinking that there is no logical relationship between the phonebooktable and the cd_collectiontable in the sample_databaseexample, you are correct. In a relational database, users can store multiple tables of data in a single database — even if the data in one table is unrelated to the data in others.

For example, suppose that you run a small company that sells widgets and you have a computerized database of customers. In addition to storing each customer's name, address, and phone number, you want to be able to look up outstanding order and invoice information for any of your customers. You could use three related tables in an RDBMS to store and organize customer data for just those purposes. Figure 18.3 shows an example of such a database.

FIGURE 18.2 A single database can contain two tables — in this case, phonebookand cd_collection.

In the example in Figure 18.3, we have added a Customer IDfield to each customer record. This field holds a customer ID number that is the unique piece of information that can be used to link all other information for each customer to track orders and invoices. Each customer is given an ID unique to him; two customers might have the same data in their name fields, but their IDfield values will never be the same. The Customer IDfield data in the Ordersand Overduetables replaces the Last Name, First Name, and Shipping Addressfield information from the Customerstable. Now, when you want to run a search for any customer's order and invoice data, you can search based on one key rather than multiple keys. You get more accurate results in faster, easier-to-conduct data searches.

FIGURE 18.3 You can use three related tables to track customers, orders, and outstanding invoices.

Now that you have an idea of how data is stored in an RDBMS and how the RDBMS structure enables you to work with that data, you are ready to learn how to input and output data from the database. This is where SQL comes in.

SQL (pronounced "S-Q-L") is a database query language understood by virtually all RDBMSs available today. You use SQL statements to get data into and retrieve data from a database. As with statements in any language, SQL statements have a defined structure that determines their meanings and functions.

As a DBA, you should understand the basics of SQL, even if you will not be doing any of the actual programming yourself. Fortunately, SQL is similar to standard English, so learning the basics is simple.

As mentioned previously, an RDBMS stores data in tables that look similar to spread sheets. Of course, before you can store any data in a database, you need to create the necessary tables and columns to store the data. You do this by using the CREATEstatement. For example, the cd_collectiontable from Figure 18.2 has five columns, or fields: id, title, artist, year, and rating.

SQL provides several column types for data that define what kind of data will be stored in the column. Some of the available types are INT, FLOAT, CHAR, and VARCHAR. Both CHARand VARCHARhold text strings, with the difference being that CHARholds a fixed-length string, whereas VARCHARholds a variable-length string.

There are also special column types, such as DATE, which takes data in only a date format, and ENUMs(enumerations), which can be used to specify that only certain values are allowed. If, for example, you wanted to record the genres of your CDs, you could use an ENUMcolumn that accepts only the values POP, ROCK, EASY_LISTENING, and so on. You will learn more about ENUM later in this chapter.

Looking at the cd_collectiontable, you can see that three of the columns hold numerical data and the other two hold string data. In addition, the character strings are of variable length. Based on this information, you can discern that the best type to use for the text columns is type VARCHAR,and the best type to use for the others is INT. You should notice something else about the cd_collectiontable: One of the CDs is missing a rating, perhaps because we have not listened to it yet. This value, therefore, is optional; it starts empty and can be filled in later.

You are now ready to create a table. As mentioned before, you do this by using the CREATEstatement, which uses the following syntax:

CREATE TABLE table_name ( column_name column_type ( parameters ) options , ...);

You should know the following about the CREATE statement:

► SQL commands are not case sensitive— For example, CREATE TABLE, create table, and Create Tableare 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_collectiondatabase:

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.

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 selectbecause 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.

After you create the tables, you can put data into them. You can insert data manually with the INSERTstatement, 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: