Section 20.4. SQL
20.4. SQL
We now overview SQL in the context of the
Books
database. Later in the chapter, you will build C# applications that execute SQL queries and access their results using ADO.NET technology. Though the Visual C# IDE provides visual tools that hide some of the SQL used to manipulate databases, it is nevertheless important to understand SQL basics. Knowing the types of operations you can perform will help you develop more advanced database-
Figure 20.10 lists some common
SQL keywords
used to form complete
SQL statements
we discuss these keywords in the
Figure 20.10. Common SQL keywords.
20.4.1. Basic SELECT Query
Let us consider several SQL queries that retrieve information from database
Books
. A SQL
query
"selects" rows and
SELECT * FROM tableName in which the asterisk ( * ) indicates that all the columns from the tableName table should be retrieved. For example, to retrieve all the data in the Authors table, use
SELECT * FROM Authors
Note that the rows of the
Authors
table are not
Most programs do not require all the data in a table. To retrieve only specific columns from a table, replace the asterisk (
*
) with a comma-separated list of the column
SELECT AuthorID, LastName FROM Authors This query returns only the data listed in Fig. 20.11. Figure 20.11. A uthorID and LastName data from the Authors table.
20.4.2. WHERE Clause
When users search a database for rows that
SELECT columnName1, columnName2, ... FROM tableName WHERE criteria For example, to select the Title , EditionNumber and Copyright columns from table Titles for which the Copyright date is more recent than 2004 , use the query
SELECT Title, EditionNumber, Copyright FROM Titles WHERE Copyright > '2004'
Figure 20.12 shows the result of the
Figure 20.12. Titles with copyright dates after 2004 from table Titles .
The
WHERE
clause criteria can contain the relational operators
<
,
>
,
<=
,
>=
,
=
(equality),
<>
(inequality) and
LIKE
, as well as the logical operators
AND
,
OR
and
NOT
(discussed in Section 20.4.6). Operator
LIKE
is used for
pattern matching
with wildcard
A pattern that contains a percent character (
%
) searches for strings that have zero or more characters at the percent character's position in the pattern. For example, the following query
SELECT AuthorID, FirstName, LastName FROM Authors WHERE LastName LIKE 'D%'
The preceding query selects the two rows shown in Fig. 20.13, because two of the four authors in our database have a last
Figure 20.13. Authors from the Authors table whose last names start with D .
An underscore ( _ ) in the pattern string indicates a single wildcard character at that position in the pattern. For example, the following query locates the rows of all the authors whose last names start with any character (specified by _ ), followed by the letter h , followed by any number of additional characters (specified by % ):
SELECT AuthorID, FirstName, LastName FROM Authors WHERE LastName LIKE '_h%' The preceding query produces the row shown in Fig. 20.14, because only one author in our database has a last name that contains the letter h as its second letter. Figure 20.14. The only author from the Authors table whose last name contains h as the second letter.
20.4.3. ORDER BY ClauseThe rows in the result of a query can be sorted into ascending or descending order by using the optional ORDER BY clause . The basic form of a query with an ORDER BY clause is
SELECT columnName1, columnName2, ... FROM tableName ORDER BY column ASC SELECT columnName1, columnName2, ... FROM tableName ORDER BY column DESC
where
ASC
specifies ascending order (
SELECT AuthorID, FirstName, LastName FROM Authors ORDER BY LastName ASC The default sorting order is ascending, so ASC is optional in the preceding query. Figure 20.15. Authors from table Authors in ascending order by LastName .
{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %} To obtain the same list of authors in descending order by last name (Fig. 20.16), use the query
SELECT AuthorID, FirstName, LastName FROM Authors ORDER BY LastName DESC Multiple columns can be used for sorting with an ORDER BY clause of the form
ORDER BY column1 sortingOrder, column2 sortingOrder, ... where sortingOrder is either ASC or DESC . Note that the sortingOrder does not have to be identical for each column. For example, the query
SELECT Title, EditionNumber, Copyright FROM Titles ORDER BY Copyright DESC , Title ASC returns the rows of the Titles table sorted first in descending order by copyright date, then in ascending order by title (Fig. 20.17). This means that rows with higher Copyright values are returned before rows with lower Copyright values, and any rows that have the same Copyright values are sorted in ascending order by title. Figure 20.16. Authors from table Authors in descending order by LastName .
Figure 20.17. Data from Titles in descending order by Copyright and ascending order by Title .
The WHERE and ORDER BY clauses can be combined in one query. For example, the query
SELECT ISBN, Title, EditionNumber, Copyright FROM Titles WHERE Title LIKE '%How to Program' ORDER BY Title ASC returns the ISBN , Title , EditionNumber and Copyright of each book in the Titles table that has a Title ending with " How to Program " and sorts them in ascending order by Title . The query results are shown in Fig. 20.18. Figure 20.18. Books from table Titles whose titles end with Howto Program in ascending order by Title .(This item is displayed on page 1005 in the print version)
20.4.4. Merging Data from Multiple Tables: INNER JOIN
Database designers typically
normalize
databasesi.e., split
Often, it is desirable to merge data from multiple tables into a single result. This is referred to as joining the tables, and is specified by an INNER JOIN operator in the query. An INNER JOIN merges rows from two tables by testing for matching values in a column that is common to the tables. The basic form of an INNER JOIN is:
SELECT columnName1, columnName2, ... FROM table1 INNER JOIN table2 ON table1.columnName = table2.columnName The ON clause of the INNER JOIN specifies the columns from each table that are compared to determine which rows are merged. For example, the following query produces a list of authors accompanied by the ISBNs for books written by each author:
SELECT FirstName, LastName, ISBN FROM Authors INNER JOIN AuthorISBN ON Authors.AuthorID = AuthorISBN.AuthorID ORDER BY LastName, FirstName The query combines the FirstName and LastName columns from table Authors and the ISBN column from table AuthorISBN , sorting the results in ascending order by LastName and FirstName . Note the use of the syntax tableName . columnName in the ON clause. This syntax (called a qualified name ) specifies the columns from each table that should be compared to join the tables. The " tableName . " syntax is required if the columns have the same name in both tables. The same syntax can be used in any query to distinguish columns that have the same name in different tables.
As always, the query can contain an ORDER BY clause. Figure 20.19 depicts the results of the preceding query, ordered by LastName and FirstName . Figure 20.19. Authors and ISBNs for their books in ascending order by LastName and FirstName .
20.4.5. INSERT StatementThe INSERT statement inserts a row into a table. The basic form of this statement is
INSERT INTO tableName ( columnName1, columnName2, ..., columnNameN ) VALUES ( value1, value2, ..., valueN ) where tableName is the table in which to insert the row. The tableName is followed by a comma-separated list of column names in parentheses (this list is not required if the INSERT operation specifies a value for every column of the table in the correct order). The list of column names is followed by the SQL keyword VALUES and a comma-separated list of values in parentheses. The values specified here must match up with the columns specified after the table name in both order and type (e.g., if columnName1 is supposed to be the FirstName column, then value1 should be a string in single quotes representing the first name). Always explicitly list the columns when inserting rowsif the order of the columns in the table changes, using only VALUES may cause an error. The INSERT statement
INSERT INTO Authors ( FirstName, LastName ) VALUES ( 'Sue' , 'Smith' ) inserts a row into the Authors table. The statement indicates that the values 'Sue' and 'Smith' are provided for the FirstName and LastName columns, respectively.
We do not specify an
AuthorID
in this example because
AuthorID
is an
identity column
in the
Authors
table (see Fig. 20.3). For every row added to this table, SQL Server
Figure 20.20. Table Authors after an INSERT operation.
20.4.6. UPDATE StatementAn UPDATE statement modifies data in a table. The basic form of the UPDATE statement is
UPDATE tableName SET columnName1 = value1, columnName2 = value2, ..., columnNameN = valueN WHERE criteria where tableName is the table to update. The tableName is followed by keyword SET and a comma-separated list of column name-value pairs in the format columnName = value . The optional WHERE clause provides criteria that determine which rows to update. Though not required, the WHERE clause is typically used, unless a change is to be made to every row. The UPDATE statement
UPDATE Authors SET LastName = 'Jones' WHERE LastName = 'Smith' AND FirstName = 'Sue' updates a row in the Authors table. Keyword AND is a logical operator that, like the C# && operator, returns true if and only if both of its operands are true. Thus, the preceding statement assigns to LastName the value Jones for the row in which LastName is equal to Smith and FirstName is equal to Sue . [ Note: If there are multiple rows with the first name "Sue" and the last name "Smith," this statement modifies all such rows to have the last name "Jones."] Figure 20.21 shows the Authors table after the UPDATE operation has taken place. SQL also provides other logical operators, such as OR and NOT , which behave like their C# counterparts. Figure 20.21. Table Authors after an UPDATE operation.
20.4.7. DELETE Statement
A
DELETE
statement
DELETE FROM tableName WHERE criteria where tableName is the table from which to delete. The optional WHERE clause specifies the criteria used to determine which rows to delete. The DELETE statement
DELETE FROM Authors WHERE LastName = 'Jones' AND FirstName = 'Sue' deletes the row for Sue Jones in the Authors table. DELETE statements can delete multiple rows if the rows all meet the criteria in the WHERE clause. Figure 20.22 shows the Authors table after the DELETE operation has taken place. Figure 20.22. Table Authors after a DELETE operation.
SQL Wrap-Up
This concludes our SQL introduction. We demonstrated several commonly used SQL keywords, formed SQL queries that retrieved data from databases and
|
Similar products
Similar products
- Professional XML Development with Apache Tools. Xerces, Xalan, FOP, Cocoon, Axis, Xindice
Theodore w. leung professional xml development with apache tools - PDF Hacks.
Copying ebooks into pdf - Microsoft Computer Dictionary
What is point in computer graphics - An Introduction to Programming Using Visual Basic 2005
An introduction to programming using visual basic 2008 boiler plate code - How to Do Everything with Illustrator CS
Anchor control point illustrator