SQL

Table operation

Create

• Create table
• Create index

CREATE [ UNIQUE ] INDEX index
ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Alter

• Alter table / column

Selection

Select

The SELECT statement is used to assemble fields of different tables and queries to a new result.

Examples

• Select all fields of all records from table Table

SELECT *
  FROM Table

• Select the two fields Field1 and Field2 from a table Table

SELECT Field1
     , Field2
  FROM Table

Inner Join

With an inner join two tables can be linked together including all records in the resulting record set, which meet the specified criteria.

Example

• Select all fields from Table1 and Table2, where the value of Field equals in Table1 and Table2.

    SELECT Table1.*
         , Table2.*
      FROM Table1 
INNER JOIN Table2 
        ON Table1.Field = Table2.Field

• The operator in the ON statement does not necessary have to be an equal sign.
• The left and right side of the ON statement are not limited to a plain field value; it is possible to calculate something here.

Outer Join

Outer joins include all records from one table, while they are only displaying related fields of the other table, if matching records exist, otherwise the fields of the other table would be displayed with NULL values.

• Left Join: Display all records from the first table and only the matching records from the second table.
• Right Join: Display all records from the second table and only the matching records from the first table.

The "first" and "second" table is identified by the order in the SQL statement. Both outer joins are interchangeable if the order of tables is swapped or in other words: The result would be the same whether you choose to left join one table to the other or to right join the other table to the one.

Example

• Display all records from Table1 and only the matching records from Table2

    SELECT Table1.*
         , Table2.*
      FROM Table1 
 LEFT JOIN Table2 
        ON Table1.Field = Table2.Field

• Display only the matching records from Table1 and all records from Table2

    SELECT Table1.*
         , Table2.*
      FROM Table1 
RIGHT JOIN Table2 
        ON Table1.Field = Table2.Field

Distinct

• DISTINCT
• DISTINCTROW

Condition

• WHERE
• HAVING

Order

• ORDER BY
• ASC / DESC

Aggregation

• GROUP BY
• Sum, Min, Max, Avg, ...

Sub Select

• IN ( SELECT ... )

Action

Create

You can create a table by "selecting into".

Examples

• Select all records from table OldTable into table NewTable

SELECT OldTable.*
  INTO NewTable
  FROM OldTable

Insert

• INSERT INTO

INSERT INTO tblDaten
SELECT *
FROM qryDaten;

INSERT INTO tblDaten
(Feld1, Feld2)
VALUES ('Wert1', 'Wert2');

Update

• UPDATE

Delete

• DELETE