DML operators

Having defined the database, we can now start operating on it by means of the SQL manipulative operations SELECT, INSERT, UPDATE, and DELETE.

1.     SELECT

Synopsys:

    SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

    * | expression [ AS output_name ] [, ...]

    [ FROM from_item [, ...] ]

    [ WHERE condition ]

    [ GROUP BY expression [, ...] ]

    [ HAVING condition [, ...] ]

    [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]

    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]

    [ LIMIT { count | ALL } ]

    [ OFFSET start ]

    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

Example in fig.2 illustrates the point that dot-qualified names are sometimes necessary in SQL to "disambiguate" column references.

Figure 2 – Use SELECT

Overview some parameters of SELECT statement:

·   If DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). ALL specifies the opposite: all rows are kept; that is the default.;

·   The FROM clause specifies one or more source tables for the SELECT. If multiple sources are specified, the result is the Cartesian product (cross join) of all the sources. But usually qualification conditions are added to restrict the returned rows to a small subset of the Cartesian product;

·   WHERE condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references;

·   GROUP BY will condense into a single row all seleted rows that share the same values for the grouped expressions. expression can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name;

·   The ORDER BY clause causes the result rows to be sorted according to the specified expressions. If two rows are equal according to the leftmost expression, there are compared according to the next expression and so on.

2.     INSERT/UPDATE/DELETE

The INSERT statement inserts into table new row.

The UPDATE statement updates existing rows.

The DELETE statements deletes several rows.

Synopsys:

               INSERT INTO table [ ( column [, ...] ) ] 

               { VALUES ( { expression } [, ...] ) };

               UPADATE [ ONLY ] table [ [ AS ] alias ]

               SET { column = { expression | DEFAULT } |

               ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

               [ WHERE condition ];

               DELETE FROM [ ONLY ] table [ [ AS ] alias ]

               [ WHERE condition ]

<< К содержанию >>