![]() ![]() ![]() |
||
|
SQL (Structured Query Language) is a language that lets you communicate with databases. For example, you can use SQL to retrieve data from a database, add data to a database, delete or update records in a database, change columns in multiple rows, add columns to tables, and add and delete tables.
Unlike other computer languages, SQL is made up of a small number of language elements that let you interact efficiently with a database. Some of the more frequently used elements include the following SQL commands:
Command |
Use |
---|---|
SELECT |
Retrieve (query) information in a database. |
INSERT |
Add records to a database. |
UPDATE |
Update information in a database. |
DELETE |
Delete information in a database. |
One of the most widely used SQL statements is the SELECT statement. The SQL SELECT statement retrieves columns of data from a database. The tabular result is stored in a result table (called the record set).
You use the following SELECT statement to retrieve information from a table:
SELECTcolumn_name
(s) FROMtable_name
Consider a table named Clients that contains the following rows:
LastName |
FirstName |
Address |
City |
---|---|---|---|
Brown |
Marie |
12 State St |
Boston |
Adams |
Russell |
521 Beacon St |
Boston |
Carter |
Joan |
1 Broadway |
New York |
To select the columns named LastName and FirstName, use the following SELECT statement:
SELECT LastName, FirstName FROM Clients
The result of this SQL statement contains the following data:
LastName |
FirstName |
---|---|
Brown |
Marie |
Adams |
Russell |
Carter |
Joan |
To conditionally select data from a table, you can add a WHERE clause to the SELECT statement, which results in the following syntax:
SELECTcolumn_name
FROMtable_name
WHEREcolumn condition value
With the WHERE clause, you can use any of the following operators:
Operator |
Description |
---|---|
= |
Equal |
<> |
Not equal |
> |
Greater than |
< |
Less than |
>= |
Greater than or equal |
<= |
Less than or equal |
BETWEEN |
Between an inclusive range |
AND |
Joins one or more conditions |
OR |
Joins one or more conditions |
LIKE |
Specifies a search for a pattern in a column. You can use a percent sign (%) to define wildcards (missing letters in the pattern) before and after the pattern. |
For example, to select the columns named LastName and FirstName for Clients whose City is Boston, use the following SELECT statement:
SELECT LastName, FirstName FROM Clients Where City = 'Boston'
The result of the preceding SQL statement contains the following data:
LastName |
FirstName |
---|---|
Brown |
Marie |
Adams |
Russell |
You can compose a WHERE clause with one or more conditions; these are called subclauses. You join subclauses using the operators AND and OR. The AND operator displays a row if all conditions that are listed are True. The OR operator displays a row if any of the conditions listed are True. The following statement shows an example of a WHERE clause with multiple subclauses:
SELECT LastName, FirstName FROM Clients Where City = 'Boston' AND FirstName = 'Marie'
The result of the preceding SQL statement contains the following data:
LastName |
FirstName |
---|---|
Brown |
Marie |
Note: The preceding SQL SELECT examples use single-quotation marks around the value. SQL uses single-quotation marks around text values. Most database systems also accept double-quotation marks. Do not enclose numeric values in quotation marks.
You use the ORDER BY clause to sort the result rows. The following SQL statement returns an alphabetic list of people sorted by last name, and then first name, from the Clients table:
SELECT * FROM Clients Order By LastName, FirstName
The default is to return the results in ascending order (top to bottom). If you include the DESC keyword in the ORDER BY clause, the rows are returned in descending order (bottom to top).
The following statement returns a reverse alphabetic list of the Clients table:
SELECT * FROM Clients Order By LastName, FirstName DESC
Note: The SQL SELECT statement is quite powerful. There are several other options for retrieving data from a SQL database using the SELECT statement, which are not described in this manual. For more information, consult a SQL reference.
|
||
![]() ![]() ![]() |