ReLast - Klassik

SQL

Kategorien

  • DDL - Data Definition Language
    • CREATE, ALTER, DROP
  • DML - Data Manipulation Language
    • INSERT, UPDATE, DELETE, TRUNCATE
  • DCL - Data Control Language
    • GRANT, REVOKE

Select

Syntax

SELECT [DISTINCT] Auswahlliste [AS Spaltenalias]
FROM Quelle [AS Tabellenalias]
[WHERE Where-Klausel]
[GROUP BY (Group-by-Attribut)+
[HAVING Having-Klausel]]
[ORDER BY (Sortierungsattribut [ASC|DESC])+];

Wildcards

ZeichenBeschreibung
%0 oder viele beliebige Zeichen
_1 beliebiges Zeichen
[]Aflistung von Zeichen
[^] oder [!]Auflistung von Zeichen die nicht erscheinen dürfen

Beispiele

Einfache Abfragen

SELECT * 
FROM tabelle

SELECT spalte1, spalte2 
FROM tabelle

SELECT spalte1 AS "Spalte 1" 
FROM tabelle

Einschränkungen

SELECT spalte1 
FROM tabelle 
WHERE spalte1='abc'

Gruppierung

SELECT spalte1 
FROM tabelle 
ORDER BY spalte1 ASC

SELECT spalte1 
FROM tabelle 
ORDER BY spalte1 DESC

Distincte Abfragen

SELECT DISTINCT spalte1 
FROM tabelle

Zählung

SELECT count(*) 
FROM tabelle

Abfrage der Anzahl der vorhandenen Inhalte einer Spalte

Was kommt in der Spalte vor:

SELECT DISTINCT spalte1 
FROM tabelle

Wie oft kommt es vor:

SELECT spalte1, count(*)
FROM tabelle1
GROUP BY spalte1

Verknüpfungen und Joins

SELECT tabelle1.spalte1, tabelle2.spalte2
FROM tabelle1, tabelle2
WHERE tabelle1.spalte1=tabelle2.spalte2

ist theoretisch äquivalent zu

SELECT tabelle1.spalte1, tabelle2.spalte2
FROM tabelle1 INNER JOIN tabelle2
ON tabelle1.spalte1=tabelle2.spalte2

DDL

CREATE TABLE Relation ( (Attribut-Definition [PRIMARY KEY])+ 
    [, FOREIGN KEY ( Attribut+ ) REFERENCES Relation ( Attribut+ )] )
DROP TABLE Relation
ALTER TABLE Relation Alter-Definition
CREATE INDEX Index-Name ON Relation ( Attribut+ )
DROP INDEX Index-Name
CREATE VIEW Sicht [( Attribut+ )] AS SFW-Block [WITH CHECK OPTION]
DROP VIEW Sicht

DML

INSERT INTO Relation [( Attribut+ )] VALUES ( ( Konstante+ ) )+
INSERT INTO Relation [( Attribut+ )] SFW-Block
UPDATE Relation SET (Attribut=Ausdruck)+ [WHERE Where-Klausel]
MERGE INTO Relation USING Quelle ON Join-Klausel
    WHEN MATCHED UPDATE SET (Attribut=Ausdruck)+
    WHEN NOT MATCHED [BY TARGET] INSERT (Attributliste) VALUES (Ausdruckliste)
    [WHEN NOT MATCHED BY SOURCE DELETE]
DELETE FROM Relation [WHERE Where-Klausel]
TRUNCATE Relation

DCL

GRANT Operation+ ON Relation TO (PUBLIC|Benutzer) [WITH GRANT OPTION]

REVOKE Operation+ ON Relation FROM (PUBLIC|Benutzer)

Copyright © 2024

Datenschutz | Impressum