The Structured Query Language
Structured Query Language, or SQL (pronounced
sequel
), 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
.
Tip
There are a few easy ways to test out the SQL statements in the following sections. If you're using Access,
open
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.
In SQL Server, open the Query Analyzer from the Start menu.
The
SELECT
Statement
The
SELECT
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
SELECT
statement is as
follows
:
SELECT
selectlist
FROM
tablename
[WHERE
searchCondition
]
[ORDER BY
orderbyExpression
[ASC DESC ]}
The structure follows basic English patterns.
selectlist
is
generally
a comma-delimited list of the
columns
you want to return.
FirstName, LastName
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.
tablename
is the
name
of the table to grab the data from. Here's a simple
SELECT
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
tblUsers
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
Tip
You shouldn't use
SELECT *
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.
SELECT
statements always return all the rows in your database (unless qualified with a
WHERE
clause, as discussed in the
next
section). Figures 8.11 and 8.12 show the data returned from your Access database when used with the following
SELECT
statements:
SELECT * FROM tblUsers
SELECT FirstName, Phone FROM tblUsers
You can use the
WHERE
clause to specify additional criteria for the returned data. A
WHERE
clause can generally be any logical statement, similar to an
if
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
FirstName
field value is "Chris." The second statement returns only the records where
City
is "ASP Town" and the
LastName
field is equal to "Mitchell".
To use wildcards for your SQL
WHERE
clause criteria, you can use the
LIKE
keyword followed by wildcard operators. Table 8.2 lists the operators that the
LIKE
keyword accepts.
Table 8.2. Access
LIKE
Operator Wildcards
|
*
|
A string of zero or more
characters
; 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
LIKE
:
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
FirstName
contains an
s
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
letters
a, b, c, or d, followed by the string "hris".
The
ORDER BY
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
alphabetically
during data entry, that doesn't mean the records will be returned the same way. The
ORDER BY
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
numbers
. For example
SELECT * FROM tblUsers
ORDER BY FirstName ASC
This statement returns all records sorted alphabetically by first name. Likewise, specifying
DESC
sort
order instead of
ASC
returns reverse-alphabetic order. You can specify multiple columns delimited by commas in the
ORDER BY
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
names
are the same (the two Jacks), the records are sorted by the phone numbers.
You can also use
SELECT
statements to return data from more than one table at once. This is done with a comma-delimited list of table names for the
tablename
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
SELECT
query. Consequently, we won't
spend
much time on the next three statements.
The
INSERT
Statement
Another common SQL statement is the
INSERT
statement, which is used to insert new rows (new data) into a database table. Its basic syntax is as follows:
INSERT [INTO]
tablename
[(
column list
)]
VALUES (DEFAULT NULL
expression
)
This is a straightforward statement and is similar in syntax to
SELECT
. Let's build an example using your
user
database:
INSERT INTO tblUsers (FirstName, LastName, Address, City _
State, Zip, Phone)
VALUES ('Chris', 'Payne', '135 ASP Street', 'ASPTown', 'FL', _
'36844', '8006596598')
This statement
inserts
a new row with the field values specified in the
VALUES
clause. The data type you supply in
VALUES
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.
The
UPDATE
Statement
The
UPDATE
statement updates existing rows in a database table. You can specify a
WHERE
clause with this statement to update only a subset of the values in a database table. The syntax is as follows:
UPDATE
tablename
SET
column name
= (DEFAULT NULL
expression
)
[WHERE searchCondition]
If you leave off the
WHERE
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
tblUser
database table as an example:
UPDATE tblUsers
SET Address = '136 ASP Street', Town = 'ASPVille'
WHERE FirstName = 'Chris' AND LastName = 'Payne'
This statement changes the
Address
and
Town
values for the record where the
FirstName
is "Chris" and the
LastName
is "Payne," which currently is only one record. You can specify only the columns you need in the
column name
list, and the
WHERE
clause is exactly the same as the
SELECT
statement—you can use wildcards as well.
The
DELETE
Statement
The last SQL statement you're going to look at today is the
DELETE
statement, which deletes rows from a database. Let's look at the syntax:
DELETE FROM
tablename
[WHERE searchCondition]
This one is pretty simple. The only thing to remember is the
WHERE
clause, which is very important. If you don't specify this clause, the
DELETE
statement will delete all the rows in the entire table—normally a disastrous occurrence. Again, the
WHERE
clause is exactly the same as for the
SELECT
statement. For example, the following deletes only records with the first name "Chris":
DELETE FROM tblUsers
WHERE FirstName = 'Chris'
|
