The Structured Query Language
Structured Query Language, or SQL (pronounced
), is an efficient language used by developers to retrieve, add, delete, or modify information in a relational database. Nearly all modern databases communicate via this standard language, so it's essential that you learn the basics of SQL.
For a more detailed treatment, try
Sams Teach Yourself SQL in 21 Days
There are a few easy ways to test out the SQL statements in the following sections. If you're using Access,
your database and click on the Query tab, create new queries in design view, close the box that pops up, and select SQL View from the View menu (see Figure 8.10). You can enter these queries directly and save or execute them here.
Figure 8.10. Entering SQL queries in Access.
In SQL Server, open the Query Analyzer from the Start menu.
statement is probably the most common SQL statement. Its basic purpose is to grab a set of data from a database table. The syntax for the
statement is as
[ASC DESC ]}
The structure follows basic English patterns.
a comma-delimited list of the
you want to return.
would return the values in those two columns, for instance. Additionally, an asterisk can be used to return all of the columns from a particular table.
of the table to grab the data from. Here's a simple
statement that you could use for the databases you've created:
SELECT * FROM tblUsers
This statement returns all the rows from all the columns in the
table, and you can do whatever you like with them.
The following statement is functionally identical to the one you just built:
SELECT FirstName, LastName, Address, City, State, ZIP,
Phone FROM tblUsers
You shouldn't use
because often it returns more information than you need. This can result in decreased performance. Instead, explicitly specify the columns you need to return. This will take a bit longer when coding, but your code will enjoy performance benefits.
statements always return all the rows in your database (unless qualified with a
clause, as discussed in the
section). Figures 8.11 and 8.12 show the data returned from your Access database when used with the following
SELECT * FROM tblUsers
SELECT FirstName, Phone FROM tblUsers
Figure 8.11. Returning all rows with the
Figure 8.12. Returning specific columns for all rows with the
You can use the
clause to specify additional criteria for the returned data. A
clause can generally be any logical statement, similar to an
statement in regular programming languages. For example:
SELECT * FROM tblUsers WHERE FirstName = 'Chris'
SELECT * FROM tblUsers WHERE City = 'ASP Town' AND LastName = 'Mitchell'
The first statement returns only records where the
field value is "Chris." The second statement returns only the records where
is "ASP Town" and the
field is equal to "Mitchell".
To use wildcards for your SQL
clause criteria, you can use the
keyword followed by wildcard operators. Table 8.2 lists the operators that the
Table 8.2. Access
A string of zero or more
; in SQL Server, this wildcard is represented by
Exactly one character
A specific character in the given set
The following statements show a few examples of using
1 SELECT * FROM tblUsers WHERE FirstName LIKE '*s*'
2 SELECT * FROM tblUsers WHERE FirstName LIKE '_hris'
3 SELECT * FROM tblUsers WHERE FirstName LIKE '[abcd]hris'
The SQL statement on line 1 returns any rows where the
somewhere in the value. The
operator works the same in SQL as it does in Windows, meaning zero or more characters.
The SQL statement on line 2 returns any records where the first name contains any one letter, followed by the string "hris". With the given data, this only returns one record, but you get the point. Finally, the SQL statement on line 3 returns any records where the first name contains a string that begins with any of the
a, b, c, or d, followed by the string "hris".
clause specifies the order in which the data is returned, such as alphabetical order. Without this clause, there's no way to know in what order the database will return records. Even though they may have been entered
during data entry, that doesn't mean the records will be returned the same way. The
clause lets you specify which columns to order the data by, and how. If used with columns containing string characters, it converts those characters into their ASCII equivalents and sorts based on those
. For example
SELECT * FROM tblUsers
ORDER BY FirstName ASC
This statement returns all records sorted alphabetically by first name. Likewise, specifying
order instead of
returns reverse-alphabetic order. You can specify multiple columns delimited by commas in the
clause as well, each with its own sort order. SQL will use the additional columns if the first one contains any duplicate data. Just don't specify any columns that aren't in the table!
For example, the following statement returns what's shown in Figure 8.12:
SELECT * FROM tblUsers
ORDER BY FirstName, Phone ASC
Notice that when the first
are the same (the two Jacks), the records are sorted by the phone numbers.
Figure 8.13. Ordering columns in a
You can also use
statements to return data from more than one table at once. This is done with a comma-delimited list of table names for the
argument. Typically, you would have to relate the tables somehow, but we'll discuss that further tomorrow.
All of the other SQL statements that you'll examine today follow the same basic form as the
query. Consequently, we won't
much time on the next three statements.
Another common SQL statement is the
statement, which is used to insert new rows (new data) into a database table. Its basic syntax is as follows:
VALUES (DEFAULT NULL
This is a straightforward statement and is similar in syntax to
. Let's build an example using your
INSERT INTO tblUsers (FirstName, LastName, Address, City _
State, Zip, Phone)
VALUES ('Chris', 'Payne', '135 ASP Street', 'ASPTown', 'FL', _
a new row with the field values specified in the
clause. The data type you supply in
must match the data type in the corresponding column or you'll receive an error. Also, if you specify a column, you must specify a value or you'll get another error.
statement updates existing rows in a database table. You can specify a
clause with this statement to update only a subset of the values in a database table. The syntax is as follows:
= (DEFAULT NULL
If you leave off the
clause, the specified column(s) are updated in every row in the database. Be very careful with this statement, because it's easy to ruin your data! Again, using your
database table as an example:
SET Address = '136 ASP Street', Town = 'ASPVille'
WHERE FirstName = 'Chris' AND LastName = 'Payne'
This statement changes the
values for the record where the
is "Chris" and the
is "Payne," which currently is only one record. You can specify only the columns you need in the
list, and the
clause is exactly the same as the
statement—you can use wildcards as well.
The last SQL statement you're going to look at today is the
statement, which deletes rows from a database. Let's look at the syntax:
This one is pretty simple. The only thing to remember is the
clause, which is very important. If you don't specify this clause, the
statement will delete all the rows in the entire table—normally a disastrous occurrence. Again, the
clause is exactly the same as for the
statement. For example, the following deletes only records with the first name "Chris":
DELETE FROM tblUsers
WHERE FirstName = 'Chris'