| 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. Figure 8.10. Entering SQL queries in Access.
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 Figure 8.11. Returning all rows with the SELECT statement.
Figure 8.12. Returning specific columns for all rows with the SELECT statement.
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 | Operator | Translation | | * | 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. Figure 8.13. Ordering columns in a SELECT statement.
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' | 
