SQL -

Document Sample
SQL - Powered By Docstoc
					Datenbankfunktionen: SQL und andere



SQL-Gebiet


       DDL              create table, create view, mater. View, create index, drop jeweils

       DML              insert, update, delete, select, where, Trigger

       DCL              Benutzerverwaltung, Transaktionsverwaltung

       SysL             Programmiersprache




Datenbank-Funktionen jenseits SQL


       Selbstverwaltung         Data Dictionary

       Administration           Sicherung, Überwachung

       Oberflächen              interaktiv, programmierbar, administrativ




TFH Berlin/Steyer
SQL


Data Definition Language

Datenbank

Idee:     Datenbehälter, übergeordnete organisatorische Einheit, z.B. für Benutzer, Gebiet

create database database_name, drop dataabse database_name


Tabelle

Idee:     Datenbehälter für ein Anwendungsobjekt, z.B. für Artikel
          mit Spalten (Attribute des Objektrs) und Zeilen (Exemplare des Objekts)

(1) Grundform: create table artikel (    anr             integer,
                                         aname           char(12),
                                         apreis          decimal (5,2),
                                         amaterial       char(15),
                                         alagermenge     integer)

                                         Zeilen
                                         Spalten
                                         Spaltenüberschriften




drop table table_name

(2) Datentypen: integer, char(n), decimal(p,s) etc.

(3) einfache Spaltenbeschränkungen: unique, mandatory, optional, primary key

(4) Wertebeschränkungen:         check (S.143)

(5) Beschränkungen durch andere Tabellen, referentielle Integrität

          z.B.
                         kunde           bestellt        artikel




Sicht

                                                                   Sichten




                                                         Tabelle


TFH Berlin/Steyer
SQL


Idee:     Benutzerdatenausschnitt entsprechend den Benutzeranforderungen aus dem Datenmodell
          Enthält nur die Definition, die Daten werden bei Bedarf aus der Tabelle abgeleitet

3 Funktionen:    neue Überschriften: Namenänderung der Spalten
                 Verdeckungen
                 Virtuelle Werte (Berechnungen)

create view view_name, drop view view_name
create view teureartikel name, bruttopreis) as …



Index

Idee:     Hilfsdatei zum schnelleren Durchsuchen der Haupttabelle
          vergleichbar mit dem Register von Stichwörtern in einem Buch

Umorganisierte Hilfsdatei zu einer Tabelle beschleunigt das Lesen.

Tabelle                                 Indexdatei

          einfach zu finden

          x              y                      y    x




          aufwendig zu finden



lesefrundlich, änderungsunfreundlich

create index index_name, drop index index_name



materialisierte Sicht

Der Grund dafür ist die manchmal lange Aufbauzeit einer normalen kompliziert definierten View.
Der Aufbau geschieht durch Speichern in eine Tabelle (create table + insert-select).
Nötig ist ein periodischer Update.



                                                                   Sicht

                                                                                      materialisierte
                                                                                      Sicht

                                                         Tabelle




TFH Berlin/Steyer
SQL



Data Manipulation Language

insert:         zeilenweise (definitionsgemäss, freihe Reihenfolge, mit select)

update:         zeilenmengenweise (mit/ohne where)

delete:         zeilenmengenweise (mit/ohne where)

select:         zeilenmengenweise

          (1)   Grundfunktionen:          ganze Tabelle
                                          Spaltenauswahl
                                          Zeilenauswahl
                                          sortieren
                                          doppelte Zeilen
                                          arithmetische Operationen (zur Ausgabe)

          (2)   where                     Grundform
                                          logische Operationen
                                          arithmetische Operationen (als Bedingung)

          (3)   Join                      Tabellenverknüpfung (equi-joins, inner, outer)

          (4)   Subselects                in where-Bedingung untergeordnete Wertermittlung
                                          *
          (5)   Mengenoperationen         , count(*), sum, min, max, avg (total oder gruppenweise)

          (6)   Gruppierung               Mengenfunktionen für Zeilengruppen

          (7)   Trigger                   zusätzliche (automatische) Kommandoausführung

                          create table insertzaehler (zeilenzahl integer)
                          insert into insertzaehler values (0)
                          create trigger t1
                          on person
                          for insert
                          as update insertzaehler set zeilenzahl = zeilenzahl + 1

          (8)   cursor Die Datenbank liefert eine Zeilenmenge. Die Programmiersprache bearbeitet nur
                       einzeln. Deshalb wird ein Zwischenspeicher (Cursor) eingerichtet, aus dem mit
                       Cursoroperationen (first, next, prev, last, absolute n, relative n) einzelne Ergebnisse
                       abgerufen werden und in Programmvariablen übertragen werden können.

Beispiel:       Tabelle artikel (anr, aname, apreis, amaterial, alagermenge)
                Tabelle 'kunde': kundennr nachname vorname kalter
                Tabelle 'bestellt': kundennr, artikelnr, menge


                          kunde           bestellt        artikel




TFH Berlin/Steyer
Datenmanipulation


Datenmanipulation genauer
Daten einfügen

direktes Einfügen mit und ohne Spaltennamen

insert into person (knr,nachname,vorname,konto)
     values (3395,'Brenner','Karl',-4913.00)
insert into person
     values (3390,'Heigert','Maria',-2.19)
Zeilen einfügen aus einer anderen Tabelle
insert into person
     select knr, nachname, vorname, konto
           from kunde
           where konto < 0

Daten ändern, löschen

Die Sprachkonzepte von SQL kommen in geeigneter Weise in den Änderungskommandos wieder vor.

update kunde
       set plz = 70441, ort = 'Stuttgart'
       where knr = 3390
delete from kunde

Daten lesen: Spaltenauswahl

Durch Aufzählen der gewünschten Spalten in der ersten Zeile erhält man die Ergebnistabelle mit den
zugehörigen Spalten in derselben Anordnung.

select nachname, vorname
                 from kunde
select vorname, nachname
          from kunde
select * from kunde

Daten lesen: Zeilenauswahl

Die Zeilenauswahl geschieht durch eine Wertebedingung in der WHERE-Zeile.

select anrede, vorname, nachname, ort
         from kunde
         where ort = 'Muenchen'
select nachname, ort, konto
         from kunde
         where konto = 0.0
select nachname, ort
         from kunde
         where konto is null

Daten lesen: Sortieren

select ort
from kunde
order by ort desc


TFH Berlin/Steyer
Datenmanipulation


Daten lesen: doppelte Zeilen vermeiden

select distinct ort
from kunde

Daten lesen: logische Verknüpfungen

Die Zeilenauswahl kann durch Logik im WHERE-Teil beeinflusst, z.B. verschärft werden.
select vorname, nachname, ort, konto
         from kunde
         where ort = 'Muenchen' or konto > 0
select vorname, nachname, ort, konto
         from kunde
         where ort = 'Muenchen' and konto > 0

         =       GLEICH
         <       KLEINER ALS
         <=      KLEINER ODER GLEICH
         >       GRÖSSER ALS
         >=      GRÖSSER ODER GLEICH
         <>      UNGLEICH
         für mehrere Bedingungen          AND, OR
         für Werte in einem Bereich       BETWEEN x AND y
         für Werte in einer Menge         IN (x,y,z)
         für Vergleich mit einem Teilwert LIKE '%abc% oder LIKE '_a_'
         für verneinte Bedingungen        NOT
         Klammernsetzung ist möglich

Daten lesen: Arithmetik

Arithmetische Ausdrücke sind an zwei Stellen möglich: In der WHERE-Bedingung dienen sie zur flexibleren
Zeilenauswahl: in der Ausgabeliste und in der WHERE-Bedingung

select hname, ort, preis
         from hotel
         where preis*7 < 500

select hname, ort, preis, preis+preis, preis*7
         from hotel

Daten lesen: Tabellenverknüpfung

Zwei Tabellen            kunde                   bestellt


                         knr nachname            knr menge


select kunde.nachname, bestellt.menge
 from kunde, bestellt
 where kunde.nachname = 'Barth' and kunde.knr=bestellt.knr;

Die Verknüpfung der beiden Tabellen erfolgt über zwei identische Spalten im WHERE-Teil,
die normale Wertebeschränkung ebenfalls.




TFH Berlin/Steyer
Datenmanipulation



ANSI Syntax:

select kunde.nachname, bestellt.menge
 from kunde join bestellt
 on kunde.knr=bestellt.knr;
 where kunde.nachname = 'Barth'

Die beiden Tabellen sind durch das Wort „join“ verbunden.
Die Tabellenverknüpfung und normale Wertebeschränkung sind getrennt, die
Tabellenverknüpfungsbedingung steht nach ON.

Weitere Operatoren:            =          (gleich)
                               <          (kleiner)
                               <=         (kleiner oder gleich)
                               <>         (ungleich)
                               >          (grösser)
                               >=         (grösser oder gleich)

Inner Join:

SELECT *
 FROM mitarbeiter, abteilung
 WHERE wohnort = stadt

 m_nr m_name                    m_vorname            abt_nr wohnort                  abt_nr abt_name                 stadt
----------------------------------------------------------------------------------------------------------------------------
  2581 Kaufmann                 Brigitte             a2         München              a2         Diagnose             München
25348 Keller                    Hans                 a3         München              a1         Beratung             München
  2581 Kaufmann                 Brigitte             a2         München              a1         Beratung             München
25348 Keller                    Hans                 a3         München              a2         Diagnose             München

Outer Join:

SELECT mitarbeiter.*, abteilung.abt_nr
 FROM mitarbeiter, abteilung
 WHERE wohnort *= stadt

 m_nr m_name                    m_vorname            abt_nr wohnort                  abt_nr
----------------------------------------------------------------------------------------------
25348 Keller                    Hans                 a3         München              a1
25348 Keller                    Hans                 a3         München              a2
  2581 Kaufmann                 Brigitte             a2         München              a1
  2581 Kaufmann                 Brigitte             a2         München              a2
29346 Probst                    Andreas              a2         Augsburg             NULL
  9031 Maier                    Rainer               a2         Augsburg             NULL
10102 Huber                     Petra                a3         Landshut             NULL
28559 Mozer                     Sybille              a1         Ulm                  NULL
18316 Müller                    Gabriele             a1         Rosenheim            NULL

Subselects

An der Stelle von Werten können (in Klammern) untergeordnete Selects stehen, die diese Werte erst
ermitteln.

delete from bestellt where kundennr = (select kundennr from kunde where nachname = 'Müller') and
artikelnr = (select artikelnr from artikel where artikelname = 'Rahmen');

TFH Berlin/Steyer
Datenmanipulation


Daten lesen: V ereinigung von Ergebnismengen

select hname, ort, preis, 'UEBER 100'
  from hotel
  where preis > 100.00
union
  select hname, ort, preis, 'UNTER 100'
  from hotel
  where not preis > 100.00
order by 1, 2

Die Teile müssen vereinigungsverträglich sein.

Mengenoperationen für die ganze Tabelle

select sum(gehalt) from Person

Mengenoperationen gruppenweise

select sum(gehalt) from Person group by wohnort

Mengenoperationen gruppenweise nur für bestimmte Gruppen

select sum(gehalt) from Person group by wohnort having sum(gehalt) < 5000

Trigger

Es gibt eine Tabelle person (pnr integer, pname char(12))

  pnr                    pname
  --------------------------------
  222                    Adam
  223                    Berta

Konstanter Eintrag:

create table ereignis (kommentar char(50))

create trigger insertprotokoll
on person
for insert
as insert into ereignis values ('Schon wieder ein Neuer')

insert into person ...

select * from ereignis               kommentar
                                     --------------------------------
                                     Schon wieder ein Neuer
Update-Zähler:

create table zähler (anzahl integer)
insert into zähler values (0)

create trigger updatezähler
on person
for update
as update zähler set anzahl = anzahl + 1

TFH Berlin/Steyer
Datenmanipulation


Delete-Protokoll:

create table delperson (pnr integer, pname char(12))

create trigger deleteprotokoll
on person
for delete
as insert into delperson select * from deleted

delete from person ... und select * from delperson und select * from person

                             pnr                    pname          pnr                   pname
                             --------------------------------      --------------------------------
                             223                    Berta          222                    Adam

Die Tabelle deleted (mit gleicher Struktur wie person) enthält die durch einen Trigger gelöschten Werte.
Die Tabelle inserted (mit gleicher Struktur wie person) enthält die durch einen Trigger eingefügten Werte.
Beide Tabellen enthalten die durch einen Trigger geänderten Werte.

ausführliches Delete-Protokoll:

Neben den gelöschten Daten werden noch User und Datumuhrzeit protokolliert.

create table delperson2 (us char(10), ti datetime, pnr int, pname char(12))

create trigger deleteprotokoll2
on person
for delete
as insert into delperson2 select user_name(), getdate(), * from deleted

delete from person where pnr = 222
select * from delperson2

dbo             2007-12-04 11:19:00.497               222       Adam




TFH Berlin/Steyer
SQL


Data Control Language
(vom Datenbankadministrator ausgeführt)

Es gibt Gefahren für die Daten: 1)      unberechtigte Zugriffe
                                2)      Mehrfachbenutzung
                                3)      Irrtümer (rückgängig machen)
                                4)      Technische Abbrüche (sollen keine Trümmer hinterlassen)

zu 1) Benutzerverwaltung

create user … identified by …
drop user …

grant { connect | resource | dba } to meier
grant <Tätigkeit> on <Objekt> to <Benutzer>
revoke <Tätigkeit> on <Objekt> from <Benutzer>

Ablauf: - Superuser wird bei Installation festgelegt.
        - Superuser legt Datenbankadministratoren an.
        - Jeder Datenbankadministrator legt seine Mitarbeiter-User an (resource, connect).


        meier           Operationen (select, insert, update, delete, definieren, benutzen)

        person                          x
        form1                                                                    x




Oberflächen werden immer "höher", d.h. entfernen sich immer mehr vom DBMS. Die Benutzer werden
immer datenbankunausgebildeter. SQL verschwindet in den Tiefen der Systeme, bleibt aber als funktionaler
Standard erhalten.

zu 2) Sperren

Bei gleichzeitiger Mehrfachbenutzung tritt evtl. lost update auf (Die spätere Änderung überschreibt die
frühere.). Abhilfe für beides ist sperren.

        in der Datenbank:

                        meier 500

        user 1                          user 2

        meier +100                      meier -100


Ablauf: - user1 holt den Satz mit 500
    - user2 holt den Satz mit 500
    - user1 addiert 100: 600
    - user2 subtrahiert 100: 400
    - user1 schreibt zurück: 600
    - user2 schreibt zurück: 400

TFH Berlin/Steyer
SQL


Die Änderung des user1 (+100) geht verloren. user1 müsste den Satz während der ganzen Zeit sperren.

Weitere Unterscheidungen: Lesesperren, Schreibsperren, gravierendes Problem: Deadlock

zu 3) Transaktionsverwaltung

Eine Transaktion ist eine benutzerdefinierte Befehlsfolge, die ganz oder garnicht ausgeführt wird, auch
wenns länger dauert.

Beginn einer Transaktion am Anfang der Sitzung
Ende einer Transaktion am Ende der Sitzung
dazwischen mit "commit" Ende und Anfang einer Transaktion zugleich (früher mal begin transaction, end
transaction)

Zweck:          Realisierungsgarantie auch längerer Abläufe

Realisierung:   Im Log-Protokoll werden alle Datenänderungen (kein Lesen) schnell parallel notiert und bei
                 Bedarf nachgearbeitet oder rückgängig gemacht ("rollback, undo").

SQL:            COMMIT, ROLLBACK
Probleme:       Je weiter zurück die Dinge liegen, die man ungeschehen machen will, desto mehr Platz für
                das Protokoll braucht man.




ACID-Eigenschaften einer Transaktion:

A (atomicity):          Eine Transaktion ist unteilbar, wird also ganz oder gar nicht ausgeführt.
C (consistency):        VOR und NACH der Transaktion ist der Datenbestand konsistent.
I (isolation):          Die Einzelaktionen können ungestört durchgeführt werden.
D (durability):         Die Ergebnisse werden dauerhaft/permanent gespeichert.

Es gibt eine einstellbare Transaktionsschärfe (isolation level)

Isolierungsstufe 0: Es wird nichts gesperrt. (READ UNCOMMITTED) Bsp. Ist Tabelle leer ?

Isolierungsstufe 1: Es wird implizit eine Zeile gesperrt, wenn sie angefasst wird. Es ist immer nur eine Zeile
                        pro Tabelle gesperrt. (READ COMMITTED) Bsp. Gehaltserhöhung für alle.

Isolierungsstufe 2: Es wird eine Zeile gesperrt, wenn sie angefasst wird. Diese Sperre wird für
                        wiederholtes Arbeiten gehalten. (REPEATABLE READ) Bsp. Umbuchung

Isolierungsstufe 3: Es wird implizit eine ganze Ergebnismenge für wiederholtes Arbeiten gesperrt.
                        (CURSOR STABILITY) Bsp. Jahresabschluss u.a. Statistiken

zu 4) technische Abbrüche

werden damit auch erledigt




TFH Berlin/Steyer
SQL


System Languages
(industriell eigenständige Weiterentwicklungen)
Prozeduralität

Der klassische Fall ist die Kommunikation zwischen einem Programm und der Datenbank.
Bei ausgedehnten Computernetzen wird durch die Programmierfähigkleit des Datenbanksystems die
Kommunikationsbelastung reduziert.

Deshalb können in der Datenbank Prozeduren angelegt werden, die alle üblichen Anforderungen erfüllen:
Abkürzung für einen oder mehrere SQL-Befehle, Steuerungsbefehle (if/while/begin-end/lokaleVariable),
Parametrisierung, Anschluss von Prozeduren anderer Sprachen

SQL-Abkürzung

create procedure p1
as select * from person

execute p1

Ablaufsteuerung

create procedure p2
as select 'xxxxx' =
case when nr = 1 then "eins"
   when nr = 2 then "zwei"
end

execute p2

create procedure p3
as if exists (select * from person)
print 'Personen existieren'

execute p3

create procedure p4
as declare @x int
select @x=1
while @x<4
begin
print 'eine person'
select @x=@x+1
end

execute p4

Parameter

create procedure p5 @nachname varchar(40), @vorname varchar(20)
as      select * from autoren
        where au_vname = @vorname
        and au_nname = @nachname

execute p5 ‘Meier’ ‘Otto’


TFH Berlin/Steyer
SQL


Metadaten durch ein Data Dictionary

Systemtabellen

       sysobjects    Beschreibt Systemobjekte: S definiert eine Systemtabelle, U eine Benutzertabelle, V
                     eine View, L eine Log-Datei, P eine DB-Prozedur, R eine Integritätsregel, D einen
                     default-Wert, TR einen Trigger.

                     Beispiel:        select * from sysobjects
                                      where xtype = "U"

       syscolumns    Beschreibt Spalten.

       sysindexes    Beschreibt Indizes.

       sysusers      Beschreibt Benutzer.

       sysdatabases Beschreibt Datenbanken.

Systemprozeduren

       sp_help       Meldet Informationen zu Datenbankobjekten (jedes in der sysobjects-Tabelle
                     aufgeführte Objekt), Datentypen.

       sp_helptext   Gibt den Text einer Regel, eines Standardwertes, einer unverschlüsselten
                     gespeicherten Prozedur, eines Triggers oder einer Sicht aus.

       sp_rename     Ändert in der aktuellen Datenbank den Namen eines vom Benutzer erzeugten
                     Objekts (z. B. Tabelle, Spalte, benutzerdefinierter Datentyp).




TFH Berlin/Steyer