SELECT

Seznam témat:

popis funkce

příklad

seznam sloupců tabulky s popisy

seznam tabulek s popisy

Popis funkce

Use this statement to retrieve information from the database.

SELECT  [ ALL  | DISTINCT  ] [ FIRST  | TOP  n  ] select-list

...[ INTO  { host-variable-list  | variable-list  } ]

...[ FROM  table-expression  ]

...[ WHERE  search-condition  ]

...[ GROUP  BY  group-by-list  ]

...[ HAVING  search-condition  ]

...[ ORDER  BY  { expression  | integer  } [ ASC  | DESC  ], ... ]

select-list :

{ column-name  | expression  } [ [ AS  ] alias-name  ], ...| *

group-by-list :

{ column-name  | alias-name  | function  | expression  }, ...

Parameters

ALL or DISTINCT    All (the default) returns all rows that satisfy the clauses of the SELECT statement. If DISTINCT is specified, duplicate output rows are eliminated. Many statements take significantly longer to execute when DISTINCT is specified, so you should reserve DISTINCT for cases where it is necessary.

FIRST or TOP    These keywords are principally for use with ORDER BY queries. You can explicitly retrieve only the first row of a query or the first n  rows of a query.

The FIRST and TOP keywords cannot be used in a derived table query. You should not use the keywords in view definitions.

select list    The select list is a list of expressions, separated by commas, specifying what will be retrieved from the database. An asterisk (*) means to select all columns of all tables in the FROM clause.

Aggregate functions are allowed in the select list (see SQL Functions ). Subqueries are also allowed in the select list (see Expressions ). Each subquery must be within parentheses.

Alias-names can be used throughout the query to represent the aliased expression.

Alias names are also displayed by Interactive SQL at the top of each column of output from the SELECT statement. If the optional alias name is not specified after an expression, Interactive SQL will display the expression itself.

INTO host-variable-list    This clause is used in Embedded SQL only. It specifies where the results of the SELECT statement will go. There must be one host-variable item for each item in the select list. Select list items are put into the host variables in order. An indicator host variable is also allowed with each host-variable,  so the program can tell if the select list item was NULL.

INTO variable-list    This clause is used in procedures and triggers only. It specifies where the results of the SELECT statement will go. There must be one variable for each item in the select list. Select list items are put into the variables in order.

FROM clause    Rows are retrieved from the tables and views specified in the table expression . Joins can be specified using join operators. A SELECT statement with no FROM clause can be used to display the values of expressions not derived from tables. For example:

SELECT @@version

displays the value of the global variable @@version. This is equivalent to:

SELECT @@version

FROM DUMMY

For more information, see FROM clause .

WHERE clause    This clause specifies which rows will be selected from the tables named in the FROM clause. It is also used to do joins between multiple tables. This is accomplished by putting a condition in the WHERE clause that relates a column or group of columns from one table with a column or group of columns from another table. Both tables must be listed in the FROM clause.

 For more information, see Search conditions .

GROUP BY clause    You can group by columns, alias names, or functions. The result of the query contains one row for each distinct set of values in the named columns, aliases, or functions. All NULL-containing rows are treated as a single set. The resulting rows are often referred to as groups  since there is one row in the result for each group of rows from the table list. Aggregate functions can then be applied to these groups to get meaningful results.

When GROUP BY is used, the select-list , HAVING clause, and ORDER BY clause must not reference any identifier that is not named in the GROUP BY clause. The exception is that the select-list  and HAVING clause may contain aggregate functions.

HAVING clause    This clause selects rows based on the group values and not on the individual row values. The HAVING clause can only be used if either the statement has a GROUP BY clause or the select list consists solely of aggregate functions. Any column names referenced in the HAVING clause must either be in the GROUP BY clause or be used as a parameter to an aggregate function in the HAVING clause.

ORDER BY clause     This clause sorts the results of a query. Each item in the ORDER BY list can be labeled as ASC for ascending order (the default) or DESC for descending order. If the expression is an integer n , then the query results will be sorted by the n 'th item in the select list.

In embedded SQL, the SELECT statement is used for retrieving results from the database and placing the values into host variables via the INTO clause. The SELECT statement must return only one row. For multiple row queries, you must use cursors.

Description

The SELECT statement is used for retrieving results from the database.

A SELECT statement can be used in Interactive SQL to browse data in the database, or to export data from the database to an external file.

A SELECT statement can also be used in procedures and triggers or in embedded SQL. The SELECT statement with an INTO clause is used for retrieving results from the database when the SELECT statement only returns one row. For multiple row queries, you must use cursors.

A SELECT statement can also be used to return a result set from a procedure.

Příklad

Jak vybrat jen ty věty, které mají v daném sloupci pouze číslice?

SELECT *

FROM dba.omater

WHERE id_material NOT LIKE '%[^0-9]%'

Ten dotaz lze přečíst takto:

Vyber ty skladové karty, které v svém kódu nemají ani na jedné pozici něco jiného než znaky '0'..'9'

Analogicky lze podmínku aplikovat i na jiné množiny znaků, přičemž množinu lze zadat nejen intervalem (jako výše), ale také výčtem či kombinací obou způsobů. Pro bližší info viz help ASA - operátor LIKE.

Seznam tabulek s popisy

Pro získání seznamu tabulek i s popisy lze s výhodou využít dotaz na systémové tabulky ASA. Následující dotaz vrátí seznam všech tabulek včetně pohledů a dočasných tabulek:

select table_name, remarks

from sys.systable

where creator=user_id('DBA')

order by table_name

Pokud někoho zajímají jen pohledy, lze použít lehce upravenou verzi:

select table_name, remarks

from sys.systable

where creator=user_id('DBA') and table_type='VIEW'

order by table_name

Seznam sloupců tabulky s popisy

Pro získání seznamu tabulek i s popisy lze s výhodou využít dotaz na systémové tabulky ASA. Následující dotaz vrátí seznam všech tabulek včetně pohledů a dočasných tabulek:

SELECT col.column_name, col.pkey as primary_key, col.nulls as allow_nulls, dom.domain_name, col.width, col.remarks

FROM sys.syscolumn as col, sys.systable as tbl, sys.sysdomain as dom

WHERE (col.table_id=tbl.table_id) and

(col.domain_id=dom.domain_id) and

(tbl.table_name='gfirmy')

ORDER BY column_id

Související témata