SQL Select Command
Used to retrieve selected data. Syntax:
SELECT [ALL | DISTINCT] columnname1 [,columnname2]
FROM tablename1 [,tablename2]
[WHERE condition] [ and|or condition...]
[GROUP BY column-list]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
The sections between the brackets [] are optional. A simpler syntax statement is:
select columnname1 [,columnname2] from tablename [where condition];
A "*" may be used to select all columns. The where clause is optional and only one column name must be specified.
The Where Clause :
This clause is used to specify which columns and values are returned. Where conditions specify an OPERATOR to use for comparison. OPERATORs include:
• = - Equal
• < - Less than
• > - Greater than
• <= - Less than or equal
• >= - Greater than or equal
• <> - Not equal
• LIKE - Allows the wildcard operator, %, to be used to select items that
are a partial match. An example is:
select city, state from towntable where state LIKE 'north%';
This allows selection of all towns in states that begin with the word "north" allowing states like North Dakota and North Carolina to be selected.
The GROUP BY Clause :
This "GROUP BY" clause allows multiple columns to be grouped so aggregate functions (listed below) may be performed on multiple columns with one command.
Aggregate function keywords:
• AVG - Get the average of a specified column.
• COUNT - Get the quantity of values in the column.
• MAX - Return the maximum value in a specified column.
• MIN - Return the minimum value in a specified column.
• SUM - Return the sum of all numeric values in the specified column.
Example:
SELECT MAX(population)
FROM citylist;
WHERE state = 'Indiana';
Example using the GROUP BY clause which gets the smallest population of each city in every state:
SELECT MIN(population)
FROM citylist;
GROUP BY state;
The HAVING Clause :
Allows selection of set test criteria on rows. You can display average size of towns whose population is less than 100.
The ORDER BY Clause :
This clause lets results be displayed in ascending or descending order. Keywords:
• ASC - Ascending order.
• DESC - Descending order.
Other Keywords :
• ALL - Used to select all records.
• DISTINCT - Used to select unique records. Only unique values are returned.
Example :
SELECT city, state FROM towntable WHERE population > '100000';
الأحد نوفمبر 30, 2008 1:28 pm
