1.4 SQL Einführung 1.4 SQL Einführung 1.4 SQL Einführung 1.4
Werbung
1.4 SQL Einführung 1.4 SQL Einführung Relationale Datenbanksprachen: SQL est omnis divisa in partes tres: • SQL - Structured Query Language I Als SEQUEL 1974-77 bei IBM für System R entwickelt I ANSI/ISO Standards: SQL (1986), SQL2 (1992), SQL3 (1999) I die meisten heutigen DBS unterstützen eine Obermenge einer Untermenge von SQL2 • QUEL - Query Language I zeitgleich zu SQL für Ingres an Berkeley University entwickelt I trotz Überlegenheit zu SQL in vielen Bereichen“ (Date) keine ” Marktdurchdringung Hochschule Niederrhein University of Applied Sciences Abfrage und Manipulation der Daten Kommandos: select, insert, update, delete Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -3- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4 SQL Einführung SQL rein sequentielle Sprache SQL Syntax • Kommandos werden sequentiell abgearbeitet • Kommandos durch Semikolon (;) getrennt • keine Programmiersprache. Insbesondere fehlen (nicht immer: in SQL-Interpreter ja, in ESQL nicht) Variablen, Kontrollflusssteuerung, Prozeduren • Key words und Identifier nicht case sensitive • SQL muss in im allgemeinen in Host-Language“ ” Ausnahme: quoted Identifier (z.B. "Bla" 6= bla) Zulässige Identifier: [ a-z][ a-z0-9]* Es gibt prozedurale Erweiterungen • String Konstanten in single quotes (’bla bla’) • PL/SQL (Oracle) und PL/PgSQL (PostgreSQL) Single quotes in Strings escaped durch verdoppeln (’Peter’’s house’) • Persistend Stored Modules (PSM) in ANSI SQL3 PL/SQL werden wir im Zusammenhang mit Stored Procedures und Triggern behandeln • einzeilige Kommentare durch Doppelminus (-- Kommentar) mehrzeilige Kommentare wie in C (/*Kommentar*/) SQL3 erlaubt geschachtelte Kommentare (/*/*bla*/*/) Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -2- • DML - Data Manipulation Language Steuerung Datenzugriff und Datensicherheit Kommandos: grant, revoke, begin, commit, rollback 1.4 SQL Einführung eingebettet werden Definieren und Ändern von Strukturen Kommandos: create, alter, drop • DCL - Data Control Language • QBE - Query by Example I intuitiver grafischer Zugriff I in Frontends für Joe User“ realisiert (z.B. MS Access) ” Dalitz: Datenbanksysteme Kap1.4. -1- • DDL - Data Definition Language University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -4- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4 SQL Einführung 1.4.1 SQL-DDL Laufendes Beispiel: Hersteller hnr# name stadt H1 H2 H3 Henkel Pelikan Brause Düsseldorf Hannover Iserlohn pnr# name preis hnr P1 P2 P3 P4 Pritt Uhu Tinte Füller 2.50 2.70 3.20 12.98 H1 H1 H2 H2 Produkt Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -5- University of Applied Sciences Datentyp Beschreibung character(n) char(n) String der Länge n, am Ende ggf. mit Blanks aufgefüllt char varying(n) varchar(n) String variabler Länge (maximal n) im allg. gegenüber char vorzuziehen integer int Ganzzahl mit Vorzeichen Postgres unterscheidet int2, int4, int8 numeric(n,m) numeric(n) Dezimalzahl mit n Stellen, davon m nach dem Komma; numeric(n) = numeric(n,0) bool true, false oder unknown (NULL) date Datum (4 Bytes, tagesgenau) time Uhrzeit (8 Bytes, mikrosekundengenau) timestamp Datum und Uhrzeit Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.1 SQL-DDL Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -7- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.1 SQL-DDL Anlegen einer Tabelle Constraints • Constraints werden nach den Felddefinitionen angegeben CREATE TABLE tabellenname ( • Constraints, die sich nur auf ein Feld beziehen, können direkt bei der Felddefinition angegeben werden feld1 datentyp1, feld2 datentyp2, ... ..., • optional können Constraints mit Namen versehen werden CREATE TABLE example1 ( a integer, b integer, c varchar(2) REFERENCES example2(a), PRIMARY KEY (feldn, ...), weitere_constraints ); PRIMARY KEY (a,b), CONSTRAINT check_b CHECK (b > 0) Mögliche Datentypen hängen vom konkreten DBS ab ); Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -6- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -8- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.1 SQL-DDL 1.4.1 SQL-DDL Mögliche Constraints (1): Anlegen des Beispiels hersteller, produkt: • NOT NULL I Feld darf nicht leer (NULL) sein I Angabe nur möglich in Spaltendefinition • UNIQUE I Feldwert muss eindeutig sein I mehrfache Nullwerte sind aber zulässig • CHECK (bedingung) I Bedingungen für Feldwerte I alle Felder in Bedingung müssen aus der Tabelle stammen Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -9- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.1 SQL-DDL Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -11- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.1 SQL-DDL Mögliche Constraints (2): Auch andere Datenbankobjekte (z.B. index) werden mit create angelegt • PRIMARY KEY (felder) I formal identisch zu Unique + Not Null I pro Tabelle nur maximal ein Primary Key möglich Beispiel: CREATE INDEX produkt_i1 ON produkt (hnr); • FOREIGN KEY (felder) REFERENCES tabelle (felder) I referenziert Primary Key anderer Tabelle I Wert darf aber NULL sein I Verhalten bei Änderung in Referenztabelle spezifizierbar (z.B. on update cascade, on delete set null) Eindeutiger Index: CREATE UNIQUE INDEX ... Ausführliche Dokumentation siehe ddl-constraints.ps.gz (Homepage zur Vorlesung) Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -10- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -12- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.1 SQL-DDL 1.4.2 SQL-DML Ändern von Strukturen Einfügen von Datensätzen • Löschen mit drop INSERT INTO hersteller ( DROP INDEX produkt_i1; DROP TABLE produkt; hnr, name, stadt ) VALUES ( • Ändern mit alter table ’H1’, ’Henkel’, ’Düsseldorf’ ALTER TABLE produkt ADD FOREIGN KEY (hnr) REFERENCES hersteller(hnr); ); Die Attributliste kann weggelassen werden. Nicht zu empfehlen! (Warum?) ALTER TABLE hersteller ADD COLUMN strasse VARCHAR(30); Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -13- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Es müssen nicht alle Spalten angegeben werden: Kommandos zur Datenmanipulation INSERT INTO produkt (pnr, name) VALUES (’P5’, ’Papier’); • insert Einfügen eines neuen Datensatzes Ergebnis: • update Änderung von Feldwerten • delete Löschen von Datensätzen komplette Tabelle auch mit truncate • select Auswahl von Daten. Ausgabe ist wieder Tabelle Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -14- Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -15- University of Applied Sciences pnr# name preis hnr P1 P2 P3 P4 P5 Pritt Uhu Tinte Füller Papier 2.50 2.70 3.20 12.98 NULL H1 H1 H2 H2 NULL Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -16- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Anstelle der values-Klausel kann auch select stehen: Where-Klausel INSERT INTO produkt (pnr, name) SELECT a, b FROM other_table; • Weglassen where-Klausel ⇒ alle Sätze betroffen UPDATE produkt SET preis = preis * 2; Bemerkungen: • Beschreibung von select siehe unten • Datentypen von a,b und pnr,name müssen zusammenpassen • Zulässige Vergleichsoperatoren und Behandlung von NULL-Werten später bei select • Ermöglicht Einlesen von Daten aus anderer Tabelle, nicht jedoch aus einer Datei. • Zugewiesener Wert kann auch Ergebnis eines Dafür gibt es Datenbankspezifische Tools, z.B. sqlldr (Oracle) oder den Befehl \copy in psql (PostgreSQL) Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -17- select sein (”Subselect”) Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -19- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Ändern von Datensätzen Löschen von Datensätzen DELETE FROM produkt WHERE preis > 3; UPDATE hersteller SET name = ’Soennecken’, stadt = NULL WHERE hnr = ’H3’; Ergebnis: pnr# name P1 P2 Pritt Uhu preis 2.50 2.70 hnr H1 H1 Ergebnis: Löschen aller Datensätze hnr# name stadt H1 H2 H3 Henkel Pelikan Soennecken Düsseldorf Hannover NULL Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -18- University of Applied Sciences DELETE FROM produkt; TRUNCATE TABLE produkt; Unterschied: truncate nicht in Transaktion Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -20- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Demonstrationsbeispiele Selektion von Daten SELECT name, preis FROM produkt where preis > 3; • SELECT * FROM produkt; • SELECT name AS "Name", Project Restrict preis AS Brutto FROM produkt; pnr# name preis hnr P1 P2 P3 P4 Pritt Uhu Tinte Füller 2.50 2.70 3.20 12.98 H1 H1 H2 H2 • SELECT ’bla’, name FROM produkt WHERE name >’S’; • SELECT ’bla’ FROM produkt; Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -21- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -23- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Die Spaltenauswahl Operatoren in where-Klausel • Stern (*) ist Abkürzung für alle Spalten SELECT * FROM produkt; • Umbenennung Spalten des Ergebnisses möglich SELECT name AS "Name", preis AS "Brutto" FROM produkt; • Auch Konstanten können selektiert werden Operator Beschreibung =,>,<,>=,<= gleich, größer, kleiner <> ungleich, Postgres: auch != BETWEEN x AND y Bereichsprüfung LIKE Pattern matching mit Wildcards _ (ein Zeichen), % (beliebig viele) SIMILAR TO Pattern matching mit Posix 1003.2 regulären Ausdrücken (SQL3) IS (NOT) NULL Prüfung, ob Feld (nicht) leer ist SELECT ’bla’, name FROM produkt; Verknüpfung von Bedingungen mit AND und OR Negation mit NOT Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -22- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -24- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Behandlung von NULL-Werten normale SQL-Funktionen • NULL-Werte werden weder durch =“ ” noch durch <>“ oder !=“ erwischt ” ” Typumwandlung kompatibler Datentypen mit cast: CAST (preis AS FLOAT) • explizites Prüfen mit IS NULL“ nötig ” Bemerkungen: • dreiwertige Logik: true, false, unknown • bitte absurde Syntax beachten: AS“ statt ,“ ” ” Beispiel für Unstrukturiertheit von S“QL ” Insbesondere ist z.B. das Gegenteil von • die meisten DBS führen auch implizite Casts durch feld1 > 2 z.B. castet Postgres ’...’ - Konstanten nach Bedarf Empfehlung: keine optimistischen Annahmen machen! nicht feld1 <= 2“, sondern ” (feld1 <= 2) OR (feld1 IS NULL) Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -25- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Faculty of Electrical Engineering and Computer Science CAST (’01.02.02’ AS DATE) Ergebnis abhängig vom eingestellten Datumsformat (Parameter des Servers oder der Client-Session): normale“ Funktionen ” I Elektrotechnik und Informatik Cast kann mehrdeutig sein: SQL kennt zwei Klassen von Funktionen: I University of Applied Sciences 1.4.2 SQL-DML SQL-Funktionen • Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -27- werden auf einzelne Argumente angewandt Typumwandlung, binäre Operatoren, Stringfunktionen, Datumsfunktionen, ... 01. Februar 2002 02. Januar 2002 02. Februar 2001 • Aggregatfunktionen I I Lösung: werden auf komplette Spalte einer Query angewandt Maximum, Summe, Mittelwert, Anzahl, Auswahl verschiedener Werte (distinct), ... Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -26- University of Applied Sciences • Formatierte Umwandlung mit to date • Analog: to char, to number, to time(stamp) Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -28- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Datumsformatierung Zahlenformatierung to_date (’01.02.02’, ’DD.MM.YY’) to_char (einfuehrung, ’DD.MM.YYYY’) to_number (’11-’, ’99S’) to_char (preis, ’099.99’) Formatkennzeichen Beschreibung Formatkennzeichen Beschreibung YYYY, YY MM Month, Mon DD, DDD D HH24, HH am MI, SS Jahr vierstellig, zweistellig Monat (01-12) Monat als Text (”Januar”, ”Jan”) Tag des Monats (01-31), Jahres (001-366) Tag der Woche (1-7, Sonntag=1) Stunde (00-23), (01-12) mit am/pm Minute (00-59), Sekunde (00-59) 9 0 S PL . , D G Ziffer ohne führende Nullen Ziffer mit führender Null Minus-Zeichen bei negativen Zahlen Minus- oder Plus-Zeichen Dezimalpunkt und Tausendergruppe Dezimalpunkt und Tausendergruppe unter Berücksichtigung von locale Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -29- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -31- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Beispielabfrage: Überblick aller seit 1982 eingeführten Produkte Stringfunktionen Funktion Beschreibung str1 || str2 lower(str), upper(str) substr(str, pos, len) substring(str FROM pos FOR len) trim(str [, chars]) trim([chars] FROM str) translate(str, from, to) String Concatenation Conversion to lower, upper case Extraktion Teilstring (pos0=1) abstruse SQL2 Syntax vorn und hinten abschneiden SQL2 Syntax Character Translation Beispiel: SELECT upper(name) || ’ kostet ’ || trim(to_char(preis,’99D99’)) || ’ EUR.’ AS "Preisliste" FROM produkt; Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -30- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -32- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Mathematische Funktionen Überblick Aggregatfunktionen Funktion Beschreibung Aggregatfunktion Beschreibung + - * / abs(x) trunc(x [, n]) round(x [, n]) arithmetische Operatoren Absolutwert abschneiden auf n Nachkommastellen runden auf n Nachkommastellen count() min(), max() sum(), avg() Anzahl Minimum, Maximum Summe, Mittelwert Bemerkungen: Datumsfunktionen • Bei Stern“ Aufrufmodus wird keine Spalte angegeben ” ⇒ nur bei count sinnvoll Funktion Beschreibung current_date current_timestamp age([ts1,] ts2) extract(feld FROM ts) Aktuelles Datum oder Uhrzeit SQL3: keine Klammern! Intervall ts1 - ts2 Feldextraktion (z.B. year) Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -33- • Außer bei count wird NULL zurückgegeben, wenn nichts selektiert wurde Insbesondere gibt sum dann nicht 0 zurück, sondern NULL (kann bei Bedarf mit case umgangen werden) Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -35- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Aggregatfunktionen Sortieren und Gruppieren • Berechenen einen Wert aus kompletter Spalte Sortierung mittels order by: eines Abfrageergebnisses (z.B. Maximum) SELECT name, preis FROM produkt ORDER BY name ASC; • Aufrufmodi I I I Berücksichtigung aller not null Werte aggregate_func (expression) aggregate_func (ALL expression) Bemerkungen: • Modifier asc (default) oder desc für Sortierreihenfolge • Sortierspalte muss in Spaltenliste vorkommen Berücksichtigung verschiedener not null Werte aggregate_func (DISTINCT expression) • mehrere Spalten in order by durch Komma trennen • statt Spaltenname kann auch Index in vorheriger Berücksichtigung sämtlicher (incl. NULL) Werte aggregate_func ( * ) Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -34- University of Applied Sciences Spaltenliste verwendet werden (order by 1) Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -36- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Gruppierung mittels group by Demonstration where versus having: Frage: • SELECT hnr, count(*), max(preis) Welcher Hersteller stellt wieviele Produkte her? FROM produkt WHERE preis > 5 GROUP BY hnr; Antwort: SELECT hnr, count(pnr) FROM produkt GROUP BY hnr; • SELECT hnr, count(*), max(preis) Bemerkungen: FROM produkt GROUP BY hnr HAVING max(preis) > 5; • ohne group by ist Abfrage fehlerhaft (Warum?) • pnr muss aggregiert werden, da verschiedene Werte innerhalb einer Gruppe auftreten können • Gruppierspalte sollte in Spaltenliste auftauchen Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -37- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Joins Frage: Wunsch: Welche Hersteller haben höchstens zwei Produkte? Produktliste mit Herstellername statt hnr Antwort: Naiver Ansatz SELECT hnr, count(*) FROM produkt GROUP BY hnr HAVING count(*)<=2; SELECT hersteller.name, produkt.name FROM produkt, hersteller ORDER BY 1,2; Bemerkung: Bedingung in where-Klausel nicht möglich I University of Applied Sciences 1.4.2 SQL-DML Ergebniseinschränkung mittels having: I Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -39- liefert nicht gewünschtes Ergebnis (Warum?) where-Bedingung wird vor Gruppenbildung angewandt Aggregatfunktionen in where-Klausel sinnlos Lösung: Join über gemeinsame Spalte hnr Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -38- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -40- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Outer Join SELECT hersteller.name, produkt.name FROM produkt, hersteller WHERE produkt.hnr = hersteller.hnr ORDER BY 1,2; SELECT hersteller.name, produkt.name FROM hersteller LEFT OUTER JOIN produkt ON hersteller.hnr = produkt.hnr; Bemerkungen: • Verbundspalte muss selben Typ haben (ggf. casten) Bemerkungen: • Tabellen können über Aliasnamen referenziert werden • Schlüsselwort OUTER ist optional SELECT a.name, b.name FROM hersteller a, produkt b ... • Modifier LEFT, RIGHT oder FULL bestimmt, welche Tabelle vollständig ausgewertet werden soll • keine Doubletten im Ergebnis mittels select distinct ... • Mehrere Join-Spalten durch AND in ON-Klausel kombinieren • Hierbei handelt es sich um einen Inner Join: Ergebnis enthält nur Sätze, die in beiden Tabellen vorkommen. Im Beispiel fehlt also Brause“. ” Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -41- • Zusätzliche WHERE-Klausel kann nach ON-Klausel folgen Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -43- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Äquivalente Abfragen mit Inner Join: Union, Intersect, Except Kombination von Abfragen mit selben Ergebnisspalten durch Mengenoperationen • SELECT hersteller.name, produkt.name FROM produkt, hersteller WHERE produkt.hnr = hersteller.hnr; • Union - Vereinigungsmenge • Intersect - Schnittmenge • SELECT hersteller.name, produkt.name FROM produkt JOIN hersteller on produkt.hnr = hersteller.hnr; • Except - Differenzmenge Achtung: Bemerkung: statt nur JOIN kann es auch ausführlicher INNER JOIN heißen Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -42- University of Applied Sciences Tatsächlich Mengenoperationen, dh. Doubletten werden entfernt! Wenn unerwünscht, Modifier ALL verwenden Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -44- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML Beispiel SELECT ’Max’ AS max(preis) AS FROM produkt UNION SELECT ’Min’ AS min(preis) AS FROM produkt; Wenn die Subquery nur eine Spalte selektiert, kann das Ergebnis einer Abfrage auch innerhalb einer where-Bedingung verwendet werden: " ", "Wert" SELECT * FROM produkt WHERE preis = (SELECT max(preis) FROM produkt); " ", "Wert" Bemerkungen: • Abfrage select *, max(preis) from produkt; Bemerkungen: nicht möglich (Warum?) • Selektierte Spalten müssen selben Typ haben • hier kein Tabellenkonstruktor, weil Ergebnis nicht als • Spaltenalias ist nicht zwingend erforderlich, aber für Tabelle verwendet wird Überschrift sinnvoll • Verwendung in where-Klausel von update, delete genauso Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -45- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML (34) Verwendung Subquery in where-Klausel: Subqueries Ergebnis einer Abfrage kann als Unterabfrage (engl. subquery) anstelle einer Tabelle verwendet werden • Vergleich mit =“, wenn Subquery genau ” einen Wert zurückliefert SELECT x.nr FROM ( SELECT nr, name FROM produkt WHERE preis > 5.0 ) x; • Vergleich mit IN“, wenn Subquery ” mehrere Werte zurückliefern kann • Alternative zu IN“ ist Formulierung über Bemerkungen: • x ist Bezeichner für die von Subquery zurückgegebene Tabelle • die Klammern (...) und der Name x um die select-Anweisung wirken als Tabellenkonstruktor einer neuen Tabelle x Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -46- Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -47- University of Applied Sciences ” EXISTS“ als correlated Subquery ” Bemerkung: Die exists Variante ist oft schneller Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -48- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML 1.4.2 SQL-DML IN versus EXISTS ALL und ANY • Vergleichsoperationen wenn Subquery SELECT * FROM hersteller WHERE hnr IN ( SELECT hnr FROM produkt WHERE preis > ’3.0’ ); mehrere Tupel zurückgibt SELECT * FROM hersteller WHERE EXISTS ( SELECT hnr FROM produkt WHERE preis > ’3.0’ AND produkt.hnr = hersteller.hnr ); Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -49- University of Applied Sciences I Bedingung muss auf alle Treffer passen: SELECT * FROM produkt WHERE preis >= ALL ( SELECT preis FROM produkt ); I Bedingung muss auf mindestens einen Treffer passen: SELECT * FROM produkt WHERE preis > ANY ( SELECT preis FROM produkt ); Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.2 SQL-DML Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -51- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.3 SQL-DCL Data Control Language für Datenschutz/-sicherheit: SELECT * FROM hersteller WHERE EXISTS ( SELECT hnr FROM produkt WHERE preis > ’3.0’ AND produkt.hnr = hersteller.hnr ); • Sicherheit vor fehlerhaften Zugriffen I I Stichwort Transaktionen“ ” SQL-Kommandos: begin, commit, rollback Bemerkungen: • Schutz vor unberechtigten Zugriffen • exists-Bedingung ist wahr, wenn Subquery irgendein I Ergebnis liefert. Was die Subquery selektiert, ist egal. Insbesondere könnte innere Abfrage auch lauten: (select ’ ’ from produkt ...) I Vor allem wichtig im Mehrbenutzerbetrieb dh. bei Client-Server Datenbanken • Correlated Subquery: Innerhalb der Subquery wird eine Tabelle der äußeren Abfrage referenziert (im Beispiel: hersteller) Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -50- University of Applied Sciences Stichwort Benutzerrechte“ ” SQL-Kommandos: grant, revoke Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -52- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.3 SQL-DCL 1.4.3 SQL-DCL SQL-Kommandos für Transaktionen Transaktionen Stand Konto A Stand Konto B x y BEGIN [WORK] BEGIN [TRANSACTION] COMMIT UPDATE konto SET stand=stand−500 WHERE nr=’A’ x − 500 Inkonsistenter Zustand ROLLBACK Start einer Transaktion Achtung: ggf. implizit (Oracle) Abschluss Transaktion mit Übernahme der Änderungen Abschluss Transaktion ohne Übernahme der Änderungen y Bemerkung: UPDATE konto SET stand=stand+500 WHERE nr=’B’ • In Oracle und SQL2 beginnt Transaktion implizit mit jedem transaction-initiating“ Kommando ” x − 500 • Die meisten anderen DBS (auch PostgreSQL) machen dagegen y + 500 ein auto-commit nach jedem Statement, wenn nicht explizit eine längere Transaktion mit BEGIN gestartet wird Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -53- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.3 SQL-DCL University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.3 SQL-DCL Transaktionen erfüllen ACID-Prinzip Benutzerrechte • Atomicity DBS hat eigene Benutzerverwaltung Transaktion ist Einheit: Alles oder Nichts. • Anlage mit CREATE USER ... • Consistency • Ändern mit ALTER USER ... Transaktion überführt kosistenten Zustand in konsistenten Zustand. Innerhalb Transaktion Inkonsistenz möglich. Kommandos sind nicht standardisiert. Beispiel Passwortänderung: • Isolation Änderungen in einer Transaktion sind bis zum Abschluss unsichtbar für andere Transaktionen. • Oracle: ALTER USER usr IDENTIFIED BY ’pwd’; • PostgreSQL: ALTER USER usr WITH PASSWORD ’pwd’; • Durability Nach Abschluss Transaktion bleiben Änderungen bestehen, auch im Fall eines folgenden Systemabsturzes Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -54- Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -55- University of Applied Sciences Auch Zuweisung Admin-Recht (DBA) systemspezifisch Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -56- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.3 SQL-DCL 1.4.3 SQL-DCL Einfachere Rechteverwaltung mitGroups Der Anleger einer Tabelle ist ihr Owner. Sonst kann keiner auf die Tabelle zugreifen. • Anlegen Gruppe mit CREATE GROUP grp; Wenn auch andere User die Tabelle nutzen sollen, muss der Owner ihnen Privileges erteilen: • Privilegien dieser Gruppe zuweisen mit GRANT ... TO GROUP grp; GRANT SELECT ON produkt TO PUBLIC; GRANT UPDATE ON produkt TO peter; • User in die Gruppe aufnehmen mit ALTER GROUP grp ADD USER usr; Privileges werden erteilt mit GRANT ... TO ... und entzogen mit REVOKE ... FROM ... • User können aus Gruppe entfernt werden mit ALTER GROUP grp DROP USER usr; Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -57- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science 1.4.3 SQL-DCL (7) Hochschule Niederrhein University of Applied Sciences Dalitz: Datenbanksysteme Kap1.4. -59- Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Ausblick Überblick Privilegien: SQL-Befehle können interaktiv über SQL-Interpreter eingegeben werden (Oracle: sqlplus, Postgres: psql) Privileg Berechtigung SELECT INSERT UPDATE DELETE ... Lesen Einfügen neuer Sätze Ändern bestehender Sätze Löschen weitere Rechte je nach DBS z.B. rule, references, trigger • Metakommandos I Befehle an den Interpreter I In psql durch Backslash gekennzeichnet, z.B \d (describe), \i (import script), \set (set psql option) I Liste aller Metakommandos: man psql • SQL-Kommandos Vereinfachungen: I • ALL kann für alle Privilegien verwendet werden Wie greift man aus einem Programm auf DB zu? ⇒ nächstes Kapitel • PUBLIC kann für alle User verwendet werden Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -58- University of Applied Sciences werden an den Datenbankserver weitergereicht Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science Hochschule Niederrhein Dalitz: Datenbanksysteme Kap1.4. -60- University of Applied Sciences Elektrotechnik und Informatik Faculty of Electrical Engineering and Computer Science
