Building SQL Queries
SQL is almost English; it’s made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work.
The first word of each query is its name, which is an action word (a verb) that tells MySQL what you want to do. The query name is followed by words and phrases — some required and some optional — that tell MySQL how to perform the action. For instance, you always need to tell MySQL what to create, and you always need to tell it which table to insert data into or to select data from.
The following is a typical SQL query. As you can see, it uses English words:
SELECT lastName FROM Member
This query retrieves all the last names stored in the table named Member. More complicated queries, such as the following, are less English-like:
SELECT lastName,firstName FROM Member WHERE state=”CA” AND
city=”Fresno” ORDER BY lastName
This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. Although this query is less English-like, it’s still pretty clear.
Here are some general points to keep in mind when constructing an SQL query, as illustrated in the preceding sample query:
- Capitalization: The case of the SQL words doesn’t matter; for example, select is the same asSELECT, and from is the same as FROM, as far as MySQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter if your operating system is Unix or Linux. When you’re using Unix or Linux, MySQL needs to match the column names exactly, so the case for the column names has to be correct — for example, lastname isn’t the same as lastName. Windows, however, isn’t as picky as Unix and Linux; from its point of view, lastname and lastNameare the same.
- Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.
- Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are a series of characters called text strings or character strings. You’re asking MySQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes.