Tipps und Tricks

Aus Wikibooks
Zur Navigation springen Zur Suche springen

Seitentitel: Einführung in SQL: Tipps und Tricks
(Einführung in SQL: Tipps und Tricks)


Dieses Kapitel stellt ein paar nützliche Verfahren vor, die in keines der anderen Kapitel passten und „zuwenig Stoff“ für ein eigenes Kapitel enthalten.

Die letzte ID abfragen[Bearbeiten]

Wenn bei einer Tabelle für die Spalte ID die neuen Werte automatisch als AUTO_INCREMENT vergeben werden, benötigt man den neu vergegebenen Wert häufig für die korrekte Behandlung der Fremdschlüssel-Beziehungen.

Firebird: Rückgabewerte benutzen[Bearbeiten]

Seit Firebird 2.x kann der INSERT-Befehl Maßnahmen, die durch einen Before-Insert-Trigger ausgeführt werden – also auch die Zuweisung einer Sequence – durch eine RETURNING-Klausel abfragen:

INSERT INTO <Tabelle> ( <Spaltenliste> )
     VALUES ( <Werteliste> )
  RETURNING <Spaltenliste> [ INTO <Variablenliste> ]

Die INTO-Klausel wird bei Aufgaben innerhalb der SQL-Programmierung benutzt. Beispiel für direkten Aufruf:

Firebird-Version
insert into Fahrzeug ( Kennzeichen, Fahrzeugtyp_ID )
              values ( 'B-JT 1234', 7 )
  RETURNING ID, Farbe;
Crystal Clear app terminal.png Ausgabe
------ Inserted values ------
ID = 652
FARBE = <null>

Die RETURNING-Klausel gibt es auch bei DB2 und Oracle.

MS-SQL: spezielle Abfragen[Bearbeiten]

Je nach Situation wird nach einem INSERT in einem weiteren Befehl der neu zugeordnete Wert abgefragt.

Variante 1 mit einer lokalen Variablen:

SELECT @@IDENTITY

Dies liefert den letzten für eine IDENTITY-Spalte vergebenen Wert der aktuellen Verbindung zurück. Hierbei wird keine Tabelle angegeben; es kann aber auch der Wert einer anderen Tabelle geliefert werden (beispielsweise wenn indirekt über einen Trigger eine weitere Tabelle bearbeitet wird).

Variante 2 mit einer Funktion:

SELECT SCOPE_IDENTITY();

Dies liefert den letzten für eine IDENTITY-Spalte vergebenen Wert der aktuellen Verbindung zurück.

Variante 3 mit einer Funktion, die sich auf eine bestimmte Tabelle bezieht:

SELECT IDENT_CURRENT('Fahrzeug');

Zu den Unterschieden zwischen diesen Verfahren siehe MSDN: @@IDENTITY.

MySQL: spezielle Abfragen[Bearbeiten]

Unmittelbar nach einem INSERT wird in einem weiteren Befehl der neu zugeordnete Wert mit einer speziellen Funktion abgefragt:

SELECT LAST_INSERT_ID();

Dies liefert immer den letzten für eine AUTO_INCREMENT-Spalte vergebenen Wert der aktuellen Verbindung zurück. Hierbei wird keine Tabelle angegeben. Je nach Arbeitsumgebung gibt es auch die interne Funktion mysql_insert_id(), die manchmal (siehe Dokumentation) abweichende Ergebnisse liefert.

Oracle: Wert abfragen[Bearbeiten]

In Oracle dient die SEQUENCE zur Vergabe eines automatischen Zählers. Mit <sequence_name>.NEXTVAL wird der nächste Wert zugewiesen, mit <sequence_name>.CURRVAL der aktuelle Wert abgefragt.

Im Skript zur Beispieldatenbank wird für die Tabelle Mitarbeiter eine SEQUENCE Mitarbeiter_ID definiert und verwendet. Für einen neuen Mitarbeiter erhält man so die zugewiesene ID:

Oracle-Version
INSERT INTO MITARBEITER
       ( ID, PERSONALNUMMER, NAME, VORNAME /* und weitere Angaben */ )
VALUES ( Mitarbeiter_ID.NEXTVAL, '80017', 'Schicker', 'Madelaine' );
select CONCAT('Last ID = ' , to_char(Mitarbeiter_ID.CURRVAL)) from dual;
Crystal Clear app terminal.png Ausgabe
1 row inserted;
Last ID = 23

Weitere Informationen über Sequenzen siehe Oracle: Sequenzen.

Tabellenstruktur auslesen[Bearbeiten]

Die Tabellen, Spalten, Views, Fremdschlüssel usw. werden in der Datenbank in systemeigenen Strukturen gespeichert. Sie können genauso wie die „eigenen“ Daten per SELECT abgefragt werden. Auch wenn der SQL-Standard dafür mit INFORMATION_SCHEMA ein detailliertes Muster vorgesehen hat, gibt es Unterschiede zwischen den DBMS.

DB2[Bearbeiten]

Übersicht

Bei DB2 spricht man von Katalog-Tabellen. Sie befinden sich im Schema SYSIBM. Einige Beispiel-Zugriffe auf die Katalog-Tabellen.

-- Liste aller Tabellen in einer Datenbank, die von P123 erstellt wurden.
SELECT creator, name
FROM sysibm.systables
WHERE creator = 'P123'
AND type = 'T'
--
-- Liste aller Views in einer Datenbank, die von P123 erstellt wurden.
SELECT creator, name
FROM sysibm.systables
WHERE creator = 'P123'
AND type = 'V'
--
-- Liste aller Fremdschlüssel-Beziehungen, die von P123 erstellt wurden.
-- TBNAME ist die (Detail-)Tabelle mit dem Fremdschlüssel
-- REFTBNAME ist die (Master-)Tabelle, auf die der Fremdschlüssel verweist
SELECT creator, tbname, reftbname
FROM sysibm.sysrels
WHERE creator = 'P123'

Firebird[Bearbeiten]

Übersicht

Bei Firebird und Interbase beginnen Bezeichner der „Systemtabellen“ mit RDB$.

-- listet die Tabellen einer Datenbank auf
SELECT rdb$relation_name from rdb$relations
 where rdb$system_flag = 0
   and rdb$relation_type = 0;

-- listet die Views einer Datenbank auf
SELECT rdb$relation_name from rdb$relations
 where rdb$system_flag = 0
   and rdb$relation_type = 1;

-- listet die Spaltennamen mit den dazugehörigen Datentypen einer Tabelle auf
SELECT rdb$relation_name, rdb$field_name, rdb$field_source
  from rdb$relation_fields
 where rdb$system_flag = 0
   and rdb$view_context is null
 order by rdb$relation_name, rdb$field_position

-- listet die Fremdschlüssel einer Tabelle auf (i.d.R. nur zwischen je einer Spalte)
SELECT rel.RDB$Constraint_Name AS ForeignKey,
       co1.RDB$Relation_Name   AS DetailTable,
       CASE idx.RDB$Segment_Count
           WHEN 1 THEN fl1.RDB$Field_Name
           ELSE idx.RDB$Segment_Count
       END AS Fields,
       rel.RDB$Const_Name_UQ   AS PrimaryKey,
       co2.RDB$Relation_Name   AS MasterTable,
       fl2.RDB$Field_Name      AS MasterField
  FROM RDB$Ref_Constraints rel
/*  RDB$Relation_Constraints wird 2x benötigt:
    als co1 für den Tabellennamen des ForeignKey
    als co2 für den Tabellennamen des PrimaryKey, auf den sich der ForeignKey bezieht  */
/*  ebenso RDB$Index_Segments
    als fl1 für den Spaltennamen des FK
    als fl2 für den Spaltennamen des PK */
       JOIN RDB$Relation_Constraints co1
         ON rel.RDB$Constraint_Name = co1.RDB$Constraint_Name
       JOIN RDB$Indices idx
         ON rel.RDB$Constraint_Name = idx.RDB$Index_Name
       JOIN RDB$Relation_Constraints co2
         ON rel.RDB$Const_Name_UQ = co2.RDB$Constraint_Name
       JOIN RDB$Index_Segments fl1
         ON rel.RDB$Constraint_Name = fl1.RDB$Index_Name
       JOIN RDB$Index_Segments fl2
         ON rel.RDB$Const_Name_UQ = fl2.RDB$Index_Name
 WHERE (NOT rel.RDB$Constraint_Name LIKE 'RDB$')
 ORDER BY   rel.RDB$Constraint_Name

/* Liste der Indizes
   die Zugehörigkeit zu den Tabellen und die Bedeutung 
   ergibt sich aus dem Namen des Index:
   PK = Primary Key
   FK = Foreign Key
   MI usw. = Foreign Key auf die Tabelle mi = Mitarbeiter
   Unter anderen Bedingungen braucht man geeignete JOINs.  */
select * from RDB$Index_Segments
 where rdb$index_name not starting with 'RDB$'
 order by rdb$index_name, rdb$field_position

MS-SQL Server[Bearbeiten]

Übersicht
-- listet die Tabellen einer Datenbank auf
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_TYPE = 'BASE TABLE'
-- listet die Views einer Datenbank auf
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_TYPE = 'VIEW'
-- listet die Spaltennamen mit den dazugehörigen Datentypen einer Tabelle auf
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = 'Tabellenname'

Oracle[Bearbeiten]

Übersicht

In Oracle gibt es fast alle Dictionary-Views in dreifacher Ausführung:

  • Views mit dem Präfix USER_ zeigen die eigenen Objekte an, also die Objekte, die im eigenen Schema erstellt sind.
  • Views mit dem Präfix ALL_ zeigen alle Objekte an, für die man eine Zugriffsberechtigung hat. Das sind die Objekte im eigenen Schema und auch Objekte in anderen Schemata, für die man durch den GRANT-Befehl eine Zugriffsberechtigung erhalten hat.
  • Auf Views mit dem Präfix DBA_ kann man nur zugreifen, wenn man das Administrations-Recht hat. In dieser View werden alle Objekte der gesamten Datenbank angezeigt, also auch die, auf die man keine Zugriffsrechte besitzt.

Alle Oracle Dictionary-Views sind im Manual Reference (nicht: SQL-Reference) beschrieben.

Beispiele:

-- listet alle eigenen Tabellen einer Datenbank auf.
SELECT TABLE_NAME FROM USER_TABLES 

-- listet alle Tabellen auf, auf die man zugriffsberechtigt ist.
SELECT TABLE_NAME FROM ALL_TABLES 

-- listet alle Tabellen auf, die es in der gesamten Datenbank gibt.
SELECT TABLE_NAME FROM DBA_TABLES 

-- listet die Views auf
SELECT VIEW_NAME FROM USER_VIEWS 

-- listet die Indizes auf. In der Spalte UNIQUENESS ist angegeben,
-- ob es sich um einen eindeutigen Index handelt (UNIQUE)
-- oder ob die Index-Werte in der Tabelle mehrmals vorkommen dürfen (NONUNIQUE)
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS FROM USER_INDEXES 

-- listet die Spaltennamen mit den dazugehörigen Datentypen einer Tabelle auf
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, 
       DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID 
 FROM USER_TAB_COLUMNS 
 WHERE TABLE_NAME = 'Tabellenname'
 ORDER BY COLUMN_ID

-- Liste aller Fremdschlüssel-Beziehungen und anderen Constraints
-- Fremdschlüssel-Beziehungen haben den Typ 'R'
-- Bei DELETE_RULE = 'CASCADE' handelt es sich um eine Beziehung mit Löschweitergabe
-- bei 'NO ACTION' um eine Beziehung mit Lösch-Restriktion.
SELECT CONSTRAINT_NAME, TABLE_NAME, R_CONSTRAINT_NAME REFERENCED_TABLE, DELETE_RULE
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'

Es gibt noch eine weitere Gruppe von Dictionary-Views. Diese geben über den Zustand der Datenbank Auskunft. Diese Views haben den Präfix V$; sie sind hauptsächlich für die Administration wichtig.

Beispiele:

-- Anzeigen aller Sessions, die gerade aktiv sind oder zuletzt aktiv waren
SELECT * FROM V$SESSION

-- Anzeigen von Informationen über die aktuelle Datenbank-Instanz.
-- Datenbank-Name, auf welchem Server die Datenbank läuft, Oracle-Version, 
-- seit wann die Datenbank aktiv ist, ob aktuell Logins möglich sind
-- uns in welchem Status sich die Datenbank befindet. 
SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, LOGINS, DATABASE_STATUS
FROM V$INSTANCE

Weitere Erläuterungen dazu stehen unter Oracle: Table; dort gibt es auch eine Anleitung, um die Einfügereihenfolge zu ermitteln.

Siehe auch[Bearbeiten]

Weitere Erläuterungen sind in den folgenden Kapiteln zu finden: