SQL: Structured Query Language
| Early work on the theory of relational databases (that is, databases in which data is stored in multiple tables of varying sizes that can contain not just data but relations between data elements) by E.F. Codd resulted in IBM, in the late 1970s, creating a language called SEQUEL (Structured English Query Language), later shortened to SQL. This language attempted to standardize into an English-like syntax the methods by which data could be extracted from or inserted into a database. Using terms such as SELECT data FROM table and WHERE field IS NOT NULL, the syntax of SQL is easy to learnalthough the concepts behind what that syntax describes can be rather more challenging. A database is organized as shown in the simple example in Figure 29.2. This diagram shows a database named PictureArchive that contains a number of tables (two are shown). These tables each represent a grouping of similar rows of dataeach row, or record, has a number of fields (columns) that contain data comprising a row. For instance, in the Users table shown, a single row would contain a user's ID, full name, email address, and age. Each of these columns is specified as a certain data typeinteger, character/string, floating-point number, Boolean, and so on. A row can have blank entries in its fields, which are usually represented by the NULL value. Figure 29.2. Diagram of a simple database. The Pictures table works the same way: It's a grouping of a different set of data, with different fields, that makes up a different part of the PictureArchive database. This table contains fields such as the filename of a picture that has been added into the system, its type, size, and so on. But what makes relational databases so useful is the ability to associate tables together via relations between their elements. The Users table contains related information about the system's users, and the Pictures table contains related information about pictures; but the Pictures table contains a field with the ID of the user who owns each picture. This simple linkage between the tables will allow us to access the data in all kinds of creative ways, as you will see. Note The conceptual structure of a database, its tables, and their columns is typically referred to as a schema. Basic SQL SyntaxBeginning with the PictureArchive sample database, let's assume that its tables already contain some datathat the tables are populated and in general use. Selecting RowsThe most common thing to do with a database is to extract data from it. You do this with the SQL command SELECT. Tip SQL query commands are not case sensitive; SELECT, Select, and select will all work the same. Case sensitivity does apply in some cases to the data in the fields, as you learn later in this chapter. Also, the names of databases, tables, and columns are case sensitive. However, for the purposes of these examples, we will use all caps to denote SQL keywords and mixed case (as in PictureArchive) to denote named objects such as tables and columns. A SELECT command, like most SQL commands, is structured to flow like common English. First, after the verb SELECT, comes a nounthe name of the field (or fields) that you want to extract. Next comes FROM, followed by the name of the table from which you want to extract the data. Finally, at the end of the query comes a semicolon (;). Here's an example: SELECT * FROM Users;Tip Don't forget the semicolon at the end of the SQL query! This is probably the most common mistake among SQL novices. If you leave off the semicolon before pressing Enter, the interface will allow you to keep entering text as part of the same SQL query. Queries can span multiple lines. This command, entered either by hand into a MySQL or PostgreSQL database command line or passed invisibly to the program by a PHP or Perl interface, tells the database to return all the data in the Users tablewithout any sorting or limitations. This data will either be returned programmatically to the interface that called it or printed in tabular form to the command line. If the response contains a lot of data but you're only interested in users who match certain criteria, you can further narrow your query in a number of ways. The first is to specify only certain fields to extract, instead of entering the asterisk (*), which is a shorthand for "All fields." Here's an example: SELECT Fullname,Age FROM Users;Fields requested in this way can be specified in any order; they can even be repeated within the same query. The response in this example will return only the full name of each user and the user's age. Next, you can limit your search still further by using the WHERE keyword. This allows you to apply any number of criteria to your queries; WHERE can specify that a field must be equal to, less than, greater than, not equal to, or have any of a number of other relationships to a certain value. This value can be supplied explicitly in the query, or it can be the name of another field, as you'll learn in a moment. For now, limit the query to those users over the age of 18 by using the "greater than or equal to" operator. Note that when field contents are specified, they should be surrounded with quotes: SELECT Fullname,Age FROM Users WHERE Age>="18";Similarly, you can use the LIKE keyword and the percent (%) wildcard to specify that you want to list all users over age 18 and who have email addresses at AOL: SELECT Fullname,Age FROM Users WHERE Age>="18" AND Email LIKE "%aol.com";You've now addressed a fairly narrow range of users from the Users table. But maybe your database is so large that you're still getting hundreds of result rows; maybe you only want the top 20 such users, sorted in descending order of age. You can do this by adding LIMIT and ORDER BY clauses:
If your query cannot fit on a single line, don't worrySQL queries can contain multiple lines, and they terminate only with a semicolon. This sample query could also be entered as follows: SELECT Fullname,Age FROM Users WHERE Age>="18" AND Email LIKE "%aol.com" ORDER BY Age DESC LIMIT 20; The additional clarity that this gives you will be invaluable when you find yourself writing really long, complex queries after you've become an SQL expert. Note You must enter all these clauses in a specific order. LIMIT cannot appear before ORDER BY, for instance. The prototypes for all complete SQL commands are available in the online documentation for the various database applications. Inserting RowsThat's about it for a basic extraction of data from a single table. You use a similar process to insert data into a table, though it's a little bit more mathematical in its look and feel. You begin with the INSERT INTO statement, followed by the table in question; then, in parentheses, you enter the names of the columns into which you're adding data (you can insert into only a few fields if you want to, leaving the rest in that row as NULL). You then enter the VALUES statement, followed (in parentheses) by the values you want to insert, listed in the same order as the fields were specified: INSERT INTO Users (Fullname,Email,Age) VALUES ("Sam Jones","sjones@somewhere.com","25");Updating RowsUpdating data in a row (or group of rows) uses the syntax that borrows from both the SELECT and INSERT commands, as in these examples: UPDATE Users SET Age="26" WHERE Fullname="Sam Jones"; UPDATE Users SET Fullname="Somebody Under 18" WHERE Age<"18"; UPDATE Users SET Age=Age+1; Caution The third example shown here has no restrictions (WHERE clauses); therefore, it's a global update that applies to all records. Be very careful when using commands like thisif the WHERE clause isn't specified properly, a single command can make blanket changes to the entire database, rendering it worthless. Deleting RowsFinally, to delete a row from a table, you use a syntax similar to what you've seen already: DELETE FROM Users WHERE Age<"13"; DELETE FROM Users; The second line in this example will delete all entries from the Users table. Use this command with the same caution with which you would use rm -rf *! If you ever find yourself entering a DELETE or UPDATE query that has no WHERE statements in it, double-and triple-check that you're doing what you want to be doing before you press Enter. You'll thank yourself for your caution. Note Many more complex querying techniques, such as joins and subselects, are available in SQLbut they're beyond the scope of this book. To fully realize the power of an SQL database, refer to an SQL reference book or the fully annotated online documentation for a database server such as MySQL (http://www.mysql.com). |
LIMIT 20;