SELECT – retrieve data
Вс, 15/02/2004 – 12:57 – andre
– english, deutsch, russian –
Syntax
SELECT * | { [ DISTINCT | ALL ]
{ fieldlname | CASE-command [ [AS] Alias ] } [,…] }
FROM { Tablename [ Alias ] } [,…]
[ WHERE logical construct ]
[ GROUP BY { Fieldname | Integer } [,…] ]
[ HAVING logical construct ]
[ { UNION | INTERSECT | EXCEPT [ALL] SELECT-Command } ] […]
[ ORDER BY { Fieldname | Integer [ ASC|DESC ] } [,…] ]
[ INTO { TEMP | SCRATCH } Tablename ] ;
Description:
SELECT-commands get data from the tables of one or, in special cases, more than one database. SELECT returns a result-data-table, which is created from the data in the specifyed tables of the current database. You can get with the SELECT-command only some records from one database table, if you want, or sum them up, or you can create a new result table from different database tables or even databases on the same system.
DISTINCT
DISTINCT tells the DBMS to select only unique records. Redundant data would not be selectted in this case (the same record would not be included more than once).
Example:
SELECT DISTINCT vnum
FROM torder;
Selects all unique values of the column vnum from the table torder.
FROM { Tablename [ [AS] Alias ] } [,…]
FROM tells the DBMS from which tables the data must be retrieved.
Example:
SELECT vnum
FROM torder;
Selects all values of the whole column vnum from the table torder..
WHERE logical construct
With WHERE you can decrease the selected amount of data or you can specify in which way multiple tables are joined.
Example:
SELECT tkunden. knum, tverkauf. vnum, tverkauf. prov
FROM tkunden, tverkauf
WHERE tkunden. vnum=tverkauf. vnum;
Here is an resulting table with the column knum from the table
tkunden and the columns vnum and prov from table tverkauf created, where the logical construct of the WHERE-clause specifyes the way in which the correct record from second table is selected.
GROUP BY { Fieldname | Integer } [,…]
GROUP BY is always used with data aggregation functions to let them work only on specifyed groups of records. Instead of explicit using of an fieldname the number of the field in the column list can be used.
Example:
SELECT vnum, MAX(preis)
FROM torder
GROUP BY vnum;
Selects only the maxumum values of the field preis from each record group with the same vnum.
HAVING logical construct
HAVING is used only with GROUP when the data aggregation functions shall work only in groups of records, which fullfill the logical construct.
Example:
SELECT vnum, MAX(preis)
FROM torder
GROUP BY vnum
HAVING AVG(preis) > 10
Selects only the maximum values of the field preis from each record group with the same vnum, where the average of preis is greater than 10.
ORDER BY { Fieldname | Integer [ ASC|DESC ] } [,…]
ORDER BY sorts the records of the resulting table. When ORDER BY is used in the GROUP BY statement, then the records in the record groups will be sorted too. Instead of explicit using of an fieldname the number of the field in the column list can be used. ASC sorts the records in ascending order, DESC in descending.
Example:
UNION [ALL] SELECT-Command
UNION is used to merge the results of two or more SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result.
(No Ratings Yet)