LIKE AS
Basic SQL Syntax
To talk to the database, you use one or more of the special words that SQL understands, as well as the
information you want to manipulate, in a statement that is issued to the database. Suppose you have a table
named Customers in your database. This Customers table is made up of the following columns:
First_Name, Last_Name, Address, and DOB (birth month). If you wanted to view all the Customers in
the database who have a birthday in January, the SQL statement would look like the following:
SELECT First_Name, Last_Name FROM Customers WHERE DOB= "January"
The first word of any SQL statement is the verb. This verb tells the database what you want. You'll learn
about what each verb does later in this chapter. In the example, you are using the SELECT verb. This
tells the database that you want to choose or select the information requested in your statement.
The words following the verb are a list of parameters that you want to view. These parameters are the
names of the columns that contain the information you want to see. Imagine that you need a report for
your boss that shows all the customers who were born in January so the company can send birthday
cards. You can generate this report using the previous SQL statement. Think of the parameter list as
the column headings for your report. This is the list of information you want to see in your report. For the
current example, you want to see the First_Name and the Last_Name of everyone who has a
birthday in January.
After the column heading list, you have the reserved word FROM. This tells the database where to
search for the information.
Following the FROM is the name of the table where the data you want is located. This can be a list of
tables—it doesn't have to be just one. In this example, you only want to use the data that is available in
the Customers table.
The next part of the SQL statement is the WHERE clause. This clause limits the information that you want
to be displayed. In this example, the WHERE clause says that you only want the records where the DOB
is equal to January. There can be multiple expressions in your WHERE clause. To limit the amount of
information even further, you could add the following:
SELECT First_Name, Last_Name FROM Customers
WHERE DOB = "January" AND Last_Name LIKE "M%"This statement would return all the first and last names of your customers whose birth month is January
and whose last name begins with the letter M.
A word of caution about your where clause. When issuing a where clause, make sure you are specific
and that your condition is logical. If you are not specific enough in your WHERE clause, or if your
condition is not logical, you may not get the results for which you are looking. This can wreak havoc and
cause serious bugs later when you start interfacing your database. The WHERE clause is where most
people make their mistakes, so be certain to pay attention when designing a WHERE clause.
The only required fields in your SQL statement are the verb, column headers, and FROM fields. The
WHERE clause is optional. So if you wanted to see all the records in your Customers table, you could
issue the following statement:
SELECT * FROM Customers
This statement would show you all the columns and all the data. Quick, simple statements like this will
show you what information your table contains. Be aware that these statements may also consume
large amounts of processor time if the table is very large. Imagine performing this query on a table that
contains half a million records. If you need to see just a sample of data that is contained in a table, use
the LIMIT keyword. This keyword limits the number of rows of data that is returned from a query. If you
wanted to see a sampling of data from the Customer table, you could perform the following query with
little worry:
SELECT * FROM Customers LIMIT 10This statement would just return the first 10 rows of data.
Another point worth mentioning is that the rows are returned in the order in which they were entered in
the sample statement. So, if Mia Claymore was entered before Ana Akin, Mia would be displayed first.
This can be troublesome, especially if you are creating a report for your boss and he likes things in
alphabetical order. Thankfully, SQL has functions that do exactly that. They are called aggregate
functions. These are the ORDER BY and GROUP BY clauses. These clauses will be explained in detail
later in this chapter.