|
|
|
|
|
Linklist | |
Common SQL operators, such as ALL,SOME, IN ...
Logical operators, such as AND, OR, NOT ...
Mathematical operators, such as +, -, / ...
Execution precedence
Semicolon is used when two SQL commands must be separated and is in some RDBMS optional, when only one SQL statement is used.
*
Is an alias for all fields of the selected table.
Example:
SELECT *
FROM torder
Selects all fields in all records of the table torder.
ALL [ ( SELECT-Command ) ]
ALL is used to select all records of an SELECT statement.
Example:
SELECT *
FROM tkunden
WHERE rating > ALL ( SELECT rating FROM tkunden WHERE stadt='Berlin' );
Select all customers where rating is biger than the rating of all customers living in Berlin.
ANY ( SELECT-Command )
ANY and SOME are the same. Werden verwendet um eine logische logical construct auf alle Datensätze rechts von dem Operator anzuwenden.
Example:
SELECT *
FROM tkunden
WHERE rating > ANY ( SELECT rating FROM tkunden WHERE stadt='Berlin' );
Select all customers with rating bigger than rating of at least one of living in Berlin.
Example:
SELECT vnum,anz,preis
FROM torder
WHERE preis BETWEEN 100 AND 500
Here are the fields vnum and preis from the table torder selected, where the field preis have an value between 100 and 500.
Example:
SELECT DISTINCT knum
FROM tkunden outer
WHERE EXISTS
( SELECT * FROM tkunden inner
WHERE inner.vnum=outer.vnum AND inner.knum<>outer.knum );
Here are all customers selected, which have more than one corresponding salesman.
IN ( { Value [,...] } | SELECT-Command )
Fieldname IS [ NOT ] NULL
Returns 'TRUE' if the field is empty and 'TRUE' if the field have a value.
Example:
SELECT *
FROM tkunden
WHERE stadt IS NOT NULL;
Here are all customers selected, where the field stadt is not empty.
LIKE Formatstring
LIKE is used only with CHAR and VARCHAR field types. Folowing rules apply to the format
string :
_ (underline) means all characters are allowed at this place;
% (percent) means all character combinations (more than one character too)
are allowed at this place
Example:
SELECT *
FROM tkunden
WHERE kname LIKE 'G%';
Select all customers whith an beginning 'G' in the name.
MATCHES Formatstring
MATCHES is used only with CHAR and VARCHAR field types. Folowing rules apply to the format
string :
? (query) means all characters are allowed at this place;
* (star) means all character combinations (more than one character too)
are allowed at this place;
[characterset] the square brackets with the specifyed characterset allow only
only a specifyed subset of characters at this place.
Example:
SELECT *
FROM tkunden
WHERE kname MATCHES '?[AaOoUu]*';
Select all customers whith an 'A', 'a', 'O', 'o', 'U' or 'u' as the second character in the field kname.
SOME ( SELECT-Command )
SOME and ANY mean the same. They are used to apply a logicat constuct to all records at the rigth side of the operator. It's true when the logical construct has the value true on at least one record at the right side of the operator.
Example:
SELECT *
FROM tkunden
WHERE rating > SOME ( SELECT rating FROM tkunden WHERE stadt='Berlin' );
Select all customers, where rating is bigger than the rating of at least one customer in Berlin.
Means that the values at both sides of the operator must be TRUE, otherwise this
operator returns FALSE.
The execution priority is (in SQL) bigger than OR, but smaller than NOT.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis > 10 AND preis < 100;
Select all orders, where the price of the ordered produkt is bigger than 10 and smaller then 100.
Logical negation, makes from FALSE an TRUE value and vice versa.
Biggest execution priority of all logical operators.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE NOT preis=0;
Select all orders, where the price of the ordered product is not zero.
Means that at least one of the values at both sides of the operator must be TRUE, otherwise this
operator returns FALSE.
This operator is executed after NOT and AND.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis > 10 AND preis < 100 OR preis > 1000;
Select all orders, where the price of the ordered product is bigger than 10 and smaller than 100, and such, where the price bigger than 1000 is.
Is TRUE when the values at both sides of the operator are equal.
The execution priority is bigger than these of NOT, AND and OR.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis = 10 ;
Select all orders, where the price is equal to 10.
Is TRUE when the value at the left side of the operator is greater than the value
at the right side.
The execution priority is bigger than these of NOT, AND and OR.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis > 0;
Select all orders, there the price of the ordered product bigger than 0 is.
Is TRUE when the value at the left side of the operator is greater or equal to the value
at the right side.
The execution priority is bigger than these of NOT, AND and OR.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis >= 300;
Select all orders, there the price of the ordered product bigger or equal to 300 is.
Is TRUE when the value at the left side of the operator is smaller than the value
at the right side.
The execution priority is bigger than these of NOT, AND and OR.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis < 300;
Select all orders, there the price of the ordered product smaller than 300 is.
Is TRUE when the value at the left side of the operator is smaller ot equal to the value
at the right side.
The execution priority is bigger than these of NOT, AND and OR.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis <= 300;
Select all orders, there the price of the ordered product smaller or equal to 300 is.
Is TRUE when the value at the left side of the operator is not equal to the value at the right side.
The execution priority is bigger than these of NOT, AND and OR.
Example:
SELECT preis, anz, odatum
FROM torder
WHERE preis <> 0;
Select all orders, there the price of the ordered product not equal to 0 is.
| Operator | Bedeutung | Priorität |
| +- | sing | 0 |
| * / | multiply, divide | 1 |
| + - | add, substact | 2 |
| = | equal | 3 |
| <> | not equal | 3 |
| < | greater | 3 |
| > | smaller | 3 |
| <= | greater or equal | 3 |
| >= | smaller or equal | 3 |
| [NOT] BETWEEN ... AND ... | [not] between ... and | 3 |
| IS [NOT] NULL | [not] NULL-value | 3 |
| [NOT] IN | [not] in the list | 3 |
| NOT | Negation | 4 |
| AND | logical and | 5 |
| OR | logical or | 6 |