5. Die Standardsprache SQL - · PDF file§SQL wurde „de facto“-Standard in der relationalen Welt (X3H2-Vorschlag wurde 1986 von ANSI ... Tabellen sind Multi-Mengen, keine Mengen!

5.DieStandardspracheSQL

Vorlesung"Informationssysteme"Sommersemester2017

Überblick§ Grundlagen• Funktions- undEinsatzbereiche

§ MöglichkeitenderDatendefinition(DDL)• Basisrelationen• Integritätsbedingungen

§ MengenorientierteAnfragen(Retrieval)• Anfragetypen• Aggregatfunktionen,Vergleichsprädikate• ErklärungsmodellfürdieAnfrageauswertung

§ MöglichkeitenderDatenmanipulation(DML)§ Anhang:BefehlsübersichtundSQL-Grammatik

Informationssysteme2017 Kapitel5:DieStandardspracheSQL 2

AbbildungsorientierteSprachenamBeispielvonSQL§ Seit1974vieleSprachentwürfe

• SQUARE:Specifying Queries AsRelationalExpressions• SEQUEL: StructuredEnglishQueryLanguage• WeiterentwicklungzuSQL(StructuredQueryLanguage)• QUEL,OLQ,PRTV,...

§ SprachentwicklungvonSQL2(SQL-92)• EntwicklungeinervereinheitlichtenDB-SprachefüralleAufgabenderDB-Verwaltung

• LehrexperimentemitStudentenmitundohneProgrammiererfahrung• ErweiterungderAnfragesprachezur"natürlichen"FormulierungbestimmterFragen

• gezielteVerbesserungenverschiedenerSprachkonstrukte zurErleichterungdesVerständnissesundzurReduktionvonFehlern

• leichterZugangdurchverschiedene"Sprachebenen"anwachsenderKomplexität:- einfacheAnfragemöglichkeitenfürdengelegentlichenBenutzer- mächtigeSprachkonstrukte fürdenbesserausgebildetenBenutzer


AbbildungsorientierteSprachenamBeispielvonSQL(2)

§ SpezielleSprachkonstrukte fürdenDBA§ SQLwurde„defacto“-StandardinderrelationalenWelt

(X3H2-Vorschlagwurde1986vonANSI,1987vonISOakzeptiert)• PortabilitätvonAnwendungen• MinimierungderAusbildungskosten• Anwendungs-Lebensdauer• Interoperabilität

§ WeiterentwicklungdesStandards• SQL2(1992)mitdreiStufen

- entry,intermediate,full

• SQL:1999,SQL:2003,SQL:2008,SQL:2011mitvielenTeilen- Mindestunterstützung:"Core"


PostgreSQL§ IndieserVorlesungwirdPostgreSQL verwendet• "Theworld's most advanced opensource database"• EinfachzuInstallieren• GuteSQLUnterstützung• Transaktionen• GuteDokumentation(esgibtauchBücherdazu)

http://www.postgresql.org/


PGAdmin3UserInterface:Downloadunterhttp://www.pgadmin.org/


ElementareSprachelemente§ SQL-Schlüsselworte• habenessentielleBedeutunginderSprache

- z.B.CREATE,TABLE,SELECT,FROM,WHERE,INSERT,UPDATE,...• Groß-/Kleinschreibungwirdnichtunterschieden

- TABLE undtable sinddiegleichenSchlüsselworte- unsereKonventioninBeispielen:immerGroßbuchstaben,fett

§ IdentifikatorenundNamen(z.B.fürTabellen,Attribute,...)• reguläreIdentifikatoren(z.B.STUDENT)

- beginntmitBuchstabe,gefolgtvonKomb.ausBuchstaben,Ziffern,'_'- keineUnterscheidungvonGroß-/Kleinschreibung- SQL-Schlüsselwortesindnichterlaubt!

• begrenzteIdentifikatoren(z.B."Student")- beliebigeZeichenkette,beginntundendetmit"-Zeichen

• unsereBeispieleverwendenimmerreguläreIdentifikatoren


BasiskonzeptederDatendefinition§ DatentypenundWertebereiche(domains)§ Tabellen• Attribute/Spalten• SchlüsseleigenschaftenundNullwerte• Default-Werte

§ Schemaänderungen

WichtigerUnterschiedzumRelationenmodell:TabellensindMulti-Mengen,keineMengen!§ TabellemussnichtunbedingteinenPrimärschlüsseldefinieren• istabermeistensderFall

§ DuplikatesindauchinAnfrageresultatenerlaubt!• Duplikateliminierungkannexplizitgefordertwerden


Datentypen§ VerfügbareDatentypen• Zeichenketten

- CHARACTER [(length)],CHARACTER VARYING [(maxlength)]- Abk.:CHAR,VARCHAR

• Zahlen- NUMERIC[(precision[,scale])],DECIMAL[(precision[,scale])],INTEGER,SMALLINT,FLOAT(precision)

- Abk.:NUM,DEC,INT- z.B.:DECIMAL(9,2)

– 7Vorkommastellen,2davonNachkommastellen

• Zeit- DATE,TIME


Wertebereiche(Domänen)§ Domänen-KonzeptzurFestlegungzulässigerWerte

• Spezifikationsmöglichkeiten- OptionaleAngabevonDefault-Werten- WertebereichseingrenzungdurchCHECK-Bedingung(mehrdazuimnächstenKapitel)

§ BeispieleCREATEDOMAIN ABTNRAS CHAR (6)CREATEDOMAIN ALTERASINT

DEFAULTNULLCONSTRAINT ALTERSBEGRENZUNG

CHECK (VALUEISNULLOR (VALUE >18AND VALUE <70))


CREATE DOMAIN domain [AS] data type[DEFAULT {literal | niladic-function-ref | NULL}][[CONSTRAINT constraint] CHECK (cond-exp) [deferrability]]

ErzeugungvonBasisrelationen§ Definitionallerzugehörigen

AttributemitTypspezifikation• Attributname• Datentypbzw.Domain• Defaultwert

§ SpezifikationvonIntegritätsbedingungen(Constraints)• VerbotvonNullwerten(NOTNULL)

• Schlüsselkandidaten(UNIQUEbzw.PRIMARYKEY)

• Fremdschlüssel(FOREIGN-KEY)

• CHECK-Constraints(später)• NamenfürFehlerdiagnose,gezieltesAnsprechen


CREATE TABLE base-table(base-table-element-commalist)

base-table-element::= column-def | base-table-constraint-def

column-def::= column {data type | domain}

[DEFAULT {literal | niladic-function-ref | NULL}][column-constraint-def-list]

column-constraint-def::= [CONSTRAINT constraint]

{NOT NULL | PRIMARY KEY | UNIQUE} |references-def}

references-def ::=REFERENCES base-table [(column-commalist)]

base-table-constraint-def::= [CONSTRAINT constraint]

{ {PRIMARY KEY | UNIQUE} (column-commalist)| FOREIGN KEY (column-commalist)

references-def}

Vereinfachte Syntax!

BeispieleCREATETABLE ABT

(ANR ABTNR PRIMARYKEY,ANAME CHAR (30) NOTNULL,...)

CREATETABLE PERS(PNR INT PRIMARYKEY,BERUF CHAR (30),PNAME CHAR (30) NOTNULL,PALTER ALTER, (*sieheDomaindefinition*)MGR INT REFERENCES PERS,ANR ABTNR NOT NULL,(*Domaindef.*)W_ORT CHAR (25) DEFAULT '',GEHALT DEC (9,2) DEFAULT 0,00,FOREIGNKEY(ANR) REFERENCES ABT)


Schemaveränderung§ VeränderneinesTabellenschemasdurch• HinzufügenvonAttributen(ALTERTABLE...ADD...)• ÄnderenderAttributdefinition(ALTERTABLE...ALTER...)• LöschenvonAttributen(ALTERTABLE...DROP...)• VollständigesLöscheneinerTabelle(DROPTABLE...)

§ BeispielALTERTABLE PERSDROPW_ORT

§ InähnlicherWeisekönnenandereSchemaobjekte(z.B.Domänen)geändert/gelöschtwerden.


ElementareDatenmanipulation:EinfügenvonTupeln§ EinfachesEinfügenvonTupeln

§ Beispiel:FügeGarfieldmitderPNR4711undErniemitderPNR1234inAbteilungK55ein(satzweisesEinfügen)INSERTINTO PERS(PNR,PNAME,ANR)

VALUES (4711,'Garfield','K55'),(1234,'Ernie','K55')• AllenichtangesprochenenAttributeerhaltenDefault- bzw.Nullwerte

• FallsalleWerteinderrichtigenReihenfolgeversorgtwerden,kanndieAttributlisteweggelassenwerden


INSERT INTO table [(column-commalist)] VALUES row-constr.-commalist

AnfrageninSQL§ Eigenschaften• Anfragensinddeklarativ• relationalvollständig!

- AlleAnfragen,dieimRelationenkalkül bzw.derRelationenalgebraformuliertwerdenkönnen,sindauchmitSQLausdrückbar

§ Grundbaustein

EinbekanntesAttributodereineMengevonAttributenwirdmitHilfeeinerRelationineingewünschtesAttributodereinerMengevonAttributenabgebildet.


SELECT PNRFROM PERSWHERE ANR = 'K55'

Abbildung

AnfragemöglichkeiteninSQL*

§ SELECTgibtdiegewünschtenResultatsattributean• Mit"SELECT *"kanndasganzeTupel ausgegebenwerden

§ FROM-KlauselspezifiziertdasObjekt(Relation,Sicht),dasverarbeitetwerdensoll

§ WHERE-KlauselkanneineSammlungvonPrädikatenzurSelektion/Verbundenthalten,diemitANDundORverknüpftseinkönnen• FolgendePrädikate(Verbundterme)sindmöglich:

Ai Q ai ,Ai Q AjmitQ Î {=,<>,<,≤,>,≥}

§ GROUP-BYzurSpez.vonGruppierungsattributen§ HAVINGdientderAuswahlvonganzenTupelgruppen


select-exp::= SELECT [ALLïDISTINCT] select-item-commalist

FROM table-ref-commalist[WHERE cond-exp][GROUP BY column-ref-commalist][HAVING cond-exp]

(*) http://www.sql-und-xml.de/sql-tutorial/

Beispiel-DB:BÜHNE

17Informationssysteme2017 Kapitel5:DieStandardspracheSQL

Relationales SQL Schema

AUTOR GORT GJAHRDICHTER (DI)

TITEL AUTOR KRITIKER UORT UJAHRDRAMA (DR)

PNR NAME WORT . . .SCHAUSPIELER (SP)

FIGUR TITEL RTYP . . .ROLLE (RO)

PNR FIGUR AJAHR AORT THEATERDARSTELLER (DA)

SelektionundProjektionauf einerRelation§ Q1:WelcheDramenvonGoethewurdennach1800uraufgeführt?

SELECT *FROM DRAMAWHERE AUTOR='Goethe'AND UJAHR>1800

§ BenennungvonErgebnis-SpaltenSELECT NAME,

GEHALT/12 ASMGEHALTFROM SCHAUSPIELER• AusgabevonAttributenoderAusdrücken• SpaltenderErgebnisrelationkönnen(um)benanntwerden(AS)


SelektionundProjektionaufeinerRelation(2)§ EinPrädikatineinerWHERE-KlauselkanneinAttributauf

ZugehörigkeitzueinerMengetesten:Ai IN (a1,...aj)

§ DuplikateinderAusgabelistewerdennichtautomatischeliminiert(Default)DISTINCT erzwingtDuplikateliminierung

§ Q2: FindedieSchauspieler(PNR),dieFaust,HamletoderWallensteingespielthaben.SELECT DISTINCT PNRFROM DARSTELLERWHERE FIGURIN ('Faust','Hamlet','Wallenstein')


GeschachtelteAbbildungDieMenge,diezurQualifikationherangezogenwird,kannErgebniseinergeschachteltenAbbildung(d.h.einesSelect-Ausdrucks)sein.Q3: FindedieFiguren,dieinDramenvonSchilleroderGoethe

vorkommen.SELECT FIGURFROM ROLLEWHERE TITELIN (

SELECT TITELFROM DRAMAWHERE AUTORIN ('Schiller','Goethe'))

• innereundäußereRelationenkönnenidentischsein• einegeschachtelteAbbildungkannbeliebigtiefsein• hier:innereAbbildungverwendetnur"eigene"Attribute,mußnureinmalausgewertetwerden!


innere Abbildung

äußereAbbildung

Besser:SymmetrischeNotation§ Q4:FindedieFigurenundihreAutoren,dieinDramenvonSchiller

oderGoethevorkommen.SELECT R.FIGUR,D.AUTORFROM ROLLER,DRAMADWHERE R.TITEL=D.TITEL

AND (D.AUTOR='Schiller'OR D.AUTOR='Goethe')§ DefinitionvonTupelvariablen (R,D)erforderlich• AuflösenvonNamenskonflikten

§ VorteiledersymmetrischenNotation• AusgabevonGrößenausinnerenBlöcken• keineVorgabederAuswertungsrichtung(DBSoptimiert!)• direkteFormulierungvonVergleichsbedingungenüberRelationengrenzen hinwegmöglich

• einfacheFormulierungdesVerbundes


SymmetrischeNotation(2)

§ Q5: FindedieDichter(AUTOR,GORT),derenDramenvonDichternmitdemselbenGeburtsort(GORT)kritisiertwurden.SELECT A.AUTOR,A.GORTFROM DICHTERA,DRAMAD,DICHTERBWHERE A.AUTOR=D.AUTOR

AND D.KRITIKER=B.AUTORAND A.GORT=B.GORT

• WelcheRollespielendieBedingungenA.AUTOR=D.AUTORundD.KRITIKER=B.AUTORindererhaltenenLösung?






FIGUR TITEL RTyp . . .ROLLE (RO)

SymmetrischeNotation(3)

§ Q6: FindedieSchauspieler(NAME,WORT),diebeiinWeimaruraufgeführtenDramenanihremWohnortals'Held'mitgespielthaben.• SELECT S.NAME,S.WORTFROM SCHAUSPIELERS,DARSTELLERD,ROLLER,DRAMAAWHERE S.PNR=D.PNRAND D.FIGUR=R.FIGUR

AND R.TITEL=A.TITELAND A.UORT='Weimar'AND R.RTYP='Held'AND D.AORT=S.WORT

• MöglichesAuswertungsmodell(Erklärungsmodell)?






FIGUR TITEL RTyp . . .ROLLE (RO)

AusführungvonSQL-Anweisungen§ AbstraktesErklärungsmodellfürQ6:


SP DA RO DR

×

×

×

σ

π

F1∧F2∧F3∧F4∧F5∧F6

SP DA RO DR

⋈

σ

⋈

⋈

σ

π

σ

F6

F1F2

F3F5 F4

äquivalent

Verbundausdrückeanstatt"klassischer"Joinsyntax

§ ExpliziteSpezifikationvonJoinsinderFROM-KlauselSELECT R.FIGUR,D.AUTOR,D.TITELFROM ROLLERJOIN DRAMADON R.TITEL=D.TITELSELECT R.FIGUR,D.AUTOR,TITELFROM ROLLERJOIN DRAMADUSING (TITEL)SELECT R.FIGUR,D.AUTOR,TITELFROM ROLLERNATURALJOIN DRAMAD

• LieferndasgleicheErgebniswie:SELECT R.FIGUR,D.AUTOR,D.TITELFROM ROLLER,DRAMADWHERE R.TITEL=D.TITEL


join-table-expression::= table-reference CROSS JOIN table reference

| table-reference [NATURAL] [join-type] JOIN table-reference[ ON conditional-expression | USING ( column-commalist ) ]

| ( join-table-expression ) join-type

::= INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | UNION

BenutzerspezifischeReihenfolgederAusgabe

§ Q7: FindedieSchauspieler,dieaneinemOrtwohnen,andemsiegespielthaben,sortiertnachName(aufsteigend),W-Ort(absteigend).SELECT S.NAME,S.WORTFROM SCHAUSPIELERS,DARSTELLERDWHERE S.PNR=D.PNR

AND S.WORT=D.AORTORDER BY S.NAMEASC,S.WORTDESC• Default:ASC• OhneAngabederORDER-BY-KlauselwirddieReihenfolgederAusgabedurchdasSystembestimmt(OptimierungderAuswertung)

• ORDER-BYistbeigeschachteltenAbbildungennichtfürinnereAbbildungenverwendbar!


ORDER BY order-item-commalist

Aggregat-Funktionen

§ Standard-Funktionen:AVG,SUM,COUNT,MIN,MAX• EliminationvonDuplikaten:DISTINCT• keineElimination:ALL(Defaultwert)

§ Q8:BestimmedasDurchschnittsgehaltderSchauspieler,dieälterals50Jahresind.(GEHALTundALTERseienAttributevonSP)SELECT AVG (GEHALT)AS GDURCHSCHNITTFROM SCHAUSPIELERWHERE ALTER>50

§ Auswertung• Aggregat-Funktion(AVG)wirdangewendetaufeinstelligeErgebnisliste(GEHALT)

• keineEliminierungvonDuplikaten• VerwendungvonarithmetischenAusdrückenistmöglich:AVG(GEHALT/12)


Aggregate-function-ref::= COUNT(*)

| {AVG | MAX | MIN | SUM | COUNT}([ALL | DISTINCT] scalar-exp)

Aggregat-Funktionen(2)§ Vorsicht:• keinegeschachtelteNutzungvonFunktionsreferenzen!• Aggregat-FunktioneninWHERE-Klauselunzulässig!

§ Q9:WelchesDramawurdezuersturaufgeführt?Sogeht'snicht:SELECT TITEL,MIN(UJAHR)FROM DRAMAAberso:SELECTTITEL,U-JAHRFROM DRAMAWHERE U-JAHR= (SELECTMIN(U-JAHR)

FROM DRAMA)



PartitionierungeinerRelationinGruppen

§ Q10:ListealleAbteilungenunddasDurchschnittsgehaltihrerAngestelltenauf(Monatsgehalt).SELECT ANR,AVG(GEHALT/12)FROM PERSGROUP BY ANR• GROUP-BY-KlauselwirdimmerzusammenmitAggregat-Funktionbenutzt

• DieAggregat-FunktionwirdjeweilsaufdieTupeln einerGruppeangewendet

• DieAusgabe-Attribute(Select-Liste)müssenfüralleTupelneinerGruppedieselbenWertehaben- erlaubt:Gruppierungsaggregatbzw.Aggregatfunktion


GROUP BY column-ref-commalistBeispielschema: PERS (PNR, NAME, GEHALT, ALTER, ANR)

PRIMARY KEY (PNR)

AuswahlvonGruppen

§ MitHAVING werdenganzeGruppenvonTupelnnachderGruppierungausgewählt• esgeltendiegleichenEinschränkungenwiefürSelect-ListebeiGROUPBY

§ Q11: ListedieAbteilungenzwischenK50undK60auf,beidenendasDurchschnittsalterihrerAngestelltenkleinerals30ist.SELECT ANRFROM PERSWHERE ANR≥'K50'AND ANR≤'K60'GROUPBY ANRHAVING AVG(ALTER)<30


HAVING cond-expBeispielschema: PERS (PNR, NAME, GEHALT, ALTER, ANR)

PRIMARY KEY (PNR)

AuswertungvonSQL-Anfragen- Erklärungsmodell1. DieauszuwertendenRelationenwerdendurchdieFROM-Klauselbestimmt.

AliasnamenerlaubendiemehrfacheVerwendungderselbenRelation2. DasKartesischeProduktallerRelationenderFROM-Klauselwirdgebildet.3. Tupeln werdenausgewähltdurchdieWHERE-Klausel.4. Prädikatmusszu„true“evaluieren5. AusdenübriggebliebenenTupeln werdenGruppengemäßderGROUP-BY-Klausel

derartgebildet,dasseineGruppeausallenTupeln besteht,diehinsichtlichallerinderGROUP-BY-KlauselaufgeführtenAttributegleicheWerteenthalten.

6. Gruppenwerdenausgewählt,wennsiedieHAVING-Klauselerfüllen.7. PrädikatinderHAVING-Klauselmusszu„true“evaluieren.8. PrädikatinderHAVING-KlauseldarfsichnuraufGruppeneigenschaftenbeziehen

(AttributederGROUP-BY-KlauseloderAnwendungvonAggregat-Funktionen).9. DieAusgabewirddurchdieAuswertungderSELECT-Klauselabgeleitet.10. WurdeeineGROUP-BY-Klauselspezifiziert,dürfenalsSelect-Elementenur

Ausdrückeaufgeführtwerden,diefürdiegesamteGruppegenaueinenWertergeben(AttributederGROUP-BY-KlauseloderAnwendungvonAggregat-Funktionen).

11. DieAusgabereihenfolgewirdgemäßderORDER-BY-Klauselhergestellt.12. WurdekeineORDER-BY-Klauselangegeben,istdieAusgabereihenfolge

systembestimmt(indeterministisch).


ErklärungsmodellvonSQL-Anfragen- Beispiel

32

R A B C

RotRotGelbRotGelbBlauBlauBlau

1020101080108020

101050201801010200

R' A B C

RotRotGelbRotGelbBlauBlauBlau

1020101080108020

101050201801010200

FROM R WHERE B <= 50

Informationssysteme2017 Kapitel5:DieStandardspracheSQL

R'' A B C

RotRotRotGelbBlauBlau

102010101020

1010205010200

R''' A B C

RotRotRotGelbBlauBlau

102010101020

1010205010200

GROUP BY A

HAVING MAX(C) > 100

R'''' A SUM(B) 12

Blau 30

R''''' A SUM(B) 12

Blau 30 12

SELECT A, SUM(B), 12 ORDER BY A

Mengen-Operatoren§ Vereinigung(UNION),Durchschnitt(INTERSECT),Differenz

(EXCEPT)vonTabellen,diealsResultatevonTeilanfragenentstehen• ErgebnistypenderTeilanfragenmüssenübereinstimmen• Duplikatewerdenautomatischeliminiert!

- kannmitZusatzALL verhindertwerden

§ Beispiel(SELECT NAMEFROM PERS)

UNIONALL(SELECT NAMEFROMMANAGER)


Suchbedingungen§ SammlungvonPrädikaten• VerknüpfungmitAND,OR,NOT• Auswertungsreihenfolgeggf.durchKlammern

§ NichtquantifiziertePrädikate• VergleichsprädikateQ• BETWEEN-Prädikat

- Beispiel:GEHALTBETWEEN80Kand 100K

• Ähnlichkeitssuche:LIKE-Prädikat• PrädikateaufMengen(IN,ALL,ANY)

§ QuantifiziertePrädikate:EXISTS§ BehandlungvonNullwerten


comparison-cond::= row-constructor Q row-constructor

row-constructor:= scalar-exp | (scalar-exp-commalist) | (table-exp)

row-constr [NOT] BETWEEN row-constrAND row-constr

LIKE-Prädikat§ UnscharfeSuche• LIKE-PrädikatvergleichteinenDatenwertmiteinem„Muster“bzw.einer„Maske“

• DasLIKE-PrädikatistTRUE,wennderentsprechendeDatenwertderMaskemitzulässigenSubstitutionenvonZeichenfür%und_entspricht.NAMELIKE'%SCHMI%'

è 'H.-W.SCHMITT','SCHMITT,H.-W.''BAUSCHMIED','SCHMITZ‚ANRLIKE'_7%'è Abteilungenmiteiner7alszweitemZeichenNAMENOTLIKE'%-%'è alleNamenohneBindestrich

§ Suchenach'%'und'_'durchVoranstelleneinesEscape-Zeichensmöglich• STRINGLIKE'%\ _%'ESCAPE'\'

è STRING-WertemitUnterstrich


Char-string-exp [NOT] LIKE char-string-exp[ESCAPE char-string-exp]

PrädikateaufMengen– IN(Mengenmitgliedschaft)

• xIN(a,b,...,z)⇔ x=aORx=b...ORx=z• xNOTINerg ⇔ NOT(xINerg)

§ Q12:FindedieNamenderSchauspieler,diedenFaustgespielthabenSELECT S.NAMEFROM SCHAUSPIELERSWHERE S.PNRIN (SELECT D.PNR

FROM DARSTELLERDWHERE D.FIGUR='Faust')

alternativ,unterVermeidungvonSubqueries:SELECTS.NAMEFROM SCHAUSPIELERS,DARSTELLERDWHERE S.PNR=D.PNR AND D.FIGUR='Faust'


row-constr [NOT] IN (table-exp)scalar-exp [NOT] IN (scalar-exp-commalist)

PrädikateaufMengen– ALL,ANYundSOME§ All-or-Any-Prädikate• Q ALL:Prädikatwirdzu„true“ausgewertet,wennderQ-VergleichfüralleErgebniswertevontable-exp „true“ist

• Q ANY/QSOME:analog,wennderQ- VergleichfüreinenErgebniswert„true“ist- row-constr IN(table-exp)⇔ row-constr =ANY(table-exp)

§ Q13: FindedieManager,diemehrverdienenalsalleihredirektenUntergebenenSELECT M.PNRFROM PERSMWHERE M.GEHALT>ALL

(SELECT P.GEHALTFROM PERSPWHERE P.MNR=M.PNR)


row-constr Q {ALL | ANY | SOME} (table-exp)

QuantifiziertePrädikate- EXISTS§ Existenztests

• DasPrädikatwirdzu„false“ausgewertet,wenntable-exp aufdieleereMengeführt,sonstzu„true“

• ImEXISTS-Kontextwirdtable-exp meistmit(SELECT*…)spezifiziert§ Q14: FindedieNamenderSchauspieler,diemindestenseinmalgespielt

habenSELECT S.NAMEFROM SCHAUSPIELERSWHERE EXISTS(SELECT *

FROM DARSTELLERDWHERE D.PNR=S.PNR)

§ Semantik• xQ ANY (SELECT y FROM TWHERE p) ⇔

EXISTS (SELECT *FROM TWHERE (p)AND xQ T.y)• xQ ALL (SELECT yFROM TWHERE p) ⇔

NOTEXISTS (SELECT *FROM TWHERE (p)ANDNOT (xQ T.y))


[NOT] EXISTS (table-exp)

QuantifiziertePrädikate– Allquantifizierung§ SQLhatkeinenexplizitenAllquantor• Allquantizierung mussdurcheineäquivalenteAnfragenmitExistenzquantizierung ausgedrücktwerden!

§ Beispiel:WelcheSchauspielerhabenalleRollengespielt?• imTupelkalkül:{𝑠𝑠|𝑠𝑠 ∈ 𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑠𝑠𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆 ∧ ∀𝑆𝑆 ∈ 𝑅𝑅𝑅𝑅𝑆𝑆𝑆𝑆𝑆𝑆𝑅𝑅(∃𝑑𝑑 ∈ 𝐷𝐷𝑆𝑆𝑆𝑆𝑠𝑠𝐷𝐷𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(

𝑑𝑑. 𝑃𝑃𝑃𝑃𝑅𝑅 = 𝑠𝑠. 𝑃𝑃𝑃𝑃𝑅𝑅 ∧ 𝑑𝑑. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅 = 𝑆𝑆. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅))}• esgiltfolgendelogischeÄquivalenz:

∀𝐷𝐷 ∈ 𝑅𝑅(𝑃𝑃 𝐷𝐷 ) ≡ ¬(∃𝐷𝐷 ∈ 𝑅𝑅 ¬𝑃𝑃 𝐷𝐷 )• Anfrageobenistalsoäquivalentzu

{𝑠𝑠|𝑠𝑠 ∈ 𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑠𝑠𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆 ∧ ¬(∃𝑆𝑆 ∈ 𝑅𝑅𝑅𝑅𝑆𝑆𝑆𝑆𝑆𝑆𝑅𝑅(¬(∃𝑑𝑑 ∈ 𝐷𝐷𝑆𝑆𝑆𝑆𝑠𝑠𝐷𝐷𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(𝑑𝑑. 𝑃𝑃𝑃𝑃𝑅𝑅 = 𝑠𝑠. 𝑃𝑃𝑃𝑃𝑅𝑅 ∧ 𝑑𝑑. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅 = 𝑆𝑆. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅))))}


Andere Formulierung: Finde die Namen der Schauspieler,

so dass keine Rolle „existiert“, die sie nicht gespielt haben.

QuantifiziertePrädikate– Allquantifizierung(2)§ WelcheSchauspielerhabenalleRollengespielt?

{𝑠𝑠|𝑠𝑠 ∈ 𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑠𝑠𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆 ∧ ¬(∃𝑆𝑆 ∈ 𝑅𝑅𝑅𝑅𝑆𝑆𝑆𝑆𝑆𝑆𝑅𝑅(¬(∃𝑑𝑑 ∈ 𝐷𝐷𝑆𝑆𝑆𝑆𝑠𝑠𝐷𝐷𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(𝑑𝑑. 𝑃𝑃𝑃𝑃𝑅𝑅 = 𝑠𝑠. 𝑃𝑃𝑃𝑃𝑅𝑅 ∧ 𝑑𝑑. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅 = 𝑆𝑆. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅))))}

§ nundirektinSQL:SELECT *FROM SCHAUSPIELERSWHERE NOTEXISTS

(SELECT *FROM ROLLERWHERE NOTEXISTS

(SELECT *FROM DARSTELLERDWHERE D.PNR=S.PNR

ANDD.FIGUR=R.FIGUR))


GeschachtelteAnfragen§ SchachtelungvonSELECT-AnweisungenaufvielfältigeWeise

möglichSyntax:(select-expr )

§ "Gültigkeit"desResultatsderselect-expr istabhängigvomKontext• skalarerWert:Resultatmit1Attributundmax.1Tupel

- ...WHERE P.GEHALT>(SELECT AVG(GEHALT)FROM PERS)- SELECT P.NAME,P.GEHALT,(SELECT AVG(GEHALT)

FROM PERSWHERE ANR=P.ANR)

FROM PERSP• ListevonWerten:Resultatmit1AttributundnTupeln

- ...WHERE P.ANRIN (SELECT ANRFROM ABTWHERE AORT='KL')- ...WHERE P.GEHALT>ALL

(SELECT GEHALTFROM PERSWHERE ANR=P.ANR)• analogfürrow,Listevonrows• Fehlermeldungbei"ungültigem"Resultat


GeschachtelteAnfragen(2)§ SchachtelunginEXISTS• Resultatderselect-expr istbeliebigeTabelle

SELECT PNR,NAMEFROM PERSMWHEREEXISTS

(SELECT *FROM PERSPWHERE P.MNR=M.PNRAND P.GEHALT>M.GEHALT)

§ SchachtelunginderFROM-Klausel• Resultatderselect-expr istalsTabelleweiterverwendbar

SELECTM.NAME,M.GEHALT,A.DGEHALTFROM PERSM, (SELECT ANR,AVG(GEHALT)AS DGEHALT

FROM PERSGROUPBY ANR)AS A

WHEREM.ANR=A.ANRAND A.DGEHALT>M.GEHALT


NULL-Werte§ Attributspezifikation:EskannfürjedesAttributfestgelegtwerden,

obNULL-Wertezugelassensindodernicht§ VerschiedeneBedeutungen• Datenwertistmomentannichtbekannt• AttributwertexistiertnichtfüreinTupel

§ EinearithmetischeOperation(+,-,*,/)miteinemNULL-WertführtaufeinenNULL-Wert


PERS PNR ANR GEH PROV GEH+PROV

08154711111112347777

K45K45K45K56K56

80K30K20K-

80K

-50K--

100K

-80K--

180K

NULL-Werte(2)§ DieAuswertungeinesNULL-WertesineinemVergleichsprädikat

mitirgendeinemWertistUNKNOWN(?)§ AuswertungvonBooleschenAusdrückenmiteinerdreiwertigen

Logik

§ DasErgebnisUNKNOWN(?)nachvollständigerAuswertungeinerWHERE-KlauselwirdwieFALSEbehandelt


NOT

TF?

FT?

AND T F ?

TF?

TF?

FFF

?F?

OR T F ?

TF?

TTT

TF?

T??

PERS PNR ANR GEH PROV GEH>PROV GEH>70KANDPROV>50K

GEH>70KORPROV>50K

08154711111112347777

K45K45K45K56K56

80K30K20K-

80K

-50K--

100K

?F??F

?FF?T

TF??T

NULL-Werte(3)§ Vorsicht!NULListkeindefinierterWert!• Tupel mitNULL-WertenimVerbundattributnehmennichtamVerbundteil

• NULL=NULLà false!§ Achtung:beiAggregatfunktionenwerdenNULL-Werte

"gestrichen"!ImallgemeinenistalsoAVG (GEH)<>SUM (GEH)/COUNT (PNR)Hier:52,5K<>210K/5


TestaufNULL-Werte

§ SpeziellesPrädikatzumTestaufNULL-WerteBeispiel:SELECT PNR,PNAMEFROM PERSWHERE GEHALTISNULL

§ Achtung:• (c1,c2)ISNULL≣ (c1ISNULL)AND(c2ISNULL)• (c1,c2)ISNOTNULL≣ (c1ISNOTNULL)AND(c2ISNOTNULL)• NOT((c1,c2)ISNULL) ≣ NOT(c1ISNULL)ORNOT(c2ISNULL)≣ (c1ISNOTNULL)OR(c2ISNOTNULL)≢ (c1,c2)ISNOTNULL


row-constr IS [NOT] NULL

MöglichkeitenderDatenmanipulation§ EinfügenvonTupeln

• MengenorientiertesEinfügen istmöglich,wenndieeinzufügendenTupel auseineranderenRelationmitHilfeeinerSELECT-Anweisungausgewähltwerdenkönnen.

§ M2: FügedieSchauspielerausKLindieRelationTEMPeinINSERTINTO TEMP

(SELECT *FROM SPWHEREW-ORT='KL')

• Eine(leere)RelationTEMPseivorhanden.DieDatentypenihrerAttributemüssenkompatibelzudenDatentypenderausgewähltenAttributesein.

• EinmengenorientiertesEinfügenwähltdiespezifizierteTupelmengeausundkopiertsieindieZielrelation

• DiekopiertenTupel sindunabhängigvonihrenUrsprungstupeln.


INSERT INTO table [(column-commalist)] {VALUES row-constr.-commalist | table-exp| DEFAULT VALUES }

LöschenvonTupeln durchSuchklauseln

§ DerAufbauderWHERE-KlauselentsprichtdeminderSELECT-Anweisung

§ M3: LöschedenSchauspielermitderPNR4711.DELETE FROM SCHAUSPIELERWHERE PNR=4711

§ M4: LöschealleSchauspieler,dieniegespielthaben.DELETEFROM SCHAUSPIELERSWHERENOTEXISTS

(SELECT *FROM DARSTELLERDWHERE D.PNR=S.PNR)


searched-delete::= DELETE FROM table [WHERE cond-exp]

ÄndernvonTupeln durchSuchklauseln

§ M5: GibdenSchauspielern,dieamPfalztheaterspielen,eineGehaltserhöhungvon5%(Annahme:GEHALTinSchauspieler)UPDATE SCHAUSPIELERSSET S.GEHALT=S.GEHALT*1.05WHERE EXISTS

(SELECT *FROM DARSTELLERDWHERE D.PNR=S.PNRAND D.THEATER='Pfalz')

§ Einschränkung(SQL-92Entry/Intermediate)InnerhalbderWHERE-KlauselineinerLösch- oderÄnderungsanweisungdarfdieZielrelationineinerFROM-Klauselnichtreferenziertwerden.


searched-update::= UPDATE table SET update-assignment-commalist

[WHERE cond-exp]

Zusammenfassung§ Datendefinition(DDL)

• DatentypenundWertebereichealsGrundlage• ErzeugenvonTabellen

- AttributdefinitionundIntegritätsbedingungen(PRIMARYKEY,UNIQUE,NOTNULL,FOREIGNKEY...REFERENCES)

• Schemaveränderungen• UnterschiedzumRelationenmodell:SQL-TabellensindMulti-Mengen

§ SQL-Anfragen(Queries)• MengenorientierteSpezifikation,verschiedeneTypenvonAnfragen• VielfaltanSuchprädikaten• ErklärungsmodellfürdieAnfrageauswertung:FestlegungderSemantikvonAnfragenmitHilfevonGrundoperationen- OptimierungderAnfrageauswertungdurchdasDBS

§ MengenorientierteDatenmanipulation(DML)


SQL2-Grammatik§ SQL-Syntax(Auszug,Table=Relation,Column=Attribut,

ListenelementedurchKommagetrennt)


query

INSERT INTOtable-nameview-name attr-name-list

DELETE FROMtable-nameview-name condition

set-list conditionUPDATE

table-nameview-name

CREATE TABLE table-name

CREATE VIEW view-name

DROP TABLE table-nameDROP VIEW view-name

queryVALUES( ) constant-list

WHERE

SET WHERE( attr-defn-list

, PRIMARY KEY ( attr-defn-list ))

( attr-name-list ) AS query

SQL-statement:

predicate

condition conditionANDcondition conditionOR

NOT conditioncondition: constant

attr-specexpr arithm.op exprfunction

expr:arithm.op: + - * /

comparison: = <> < > <= >=

SELECTDISTINCT WHERE

expr-list FROM*

from-listcondition

GROUP BY attr-spec-listHAVING condition

ORDER BY attr-spec-list ASDESC

query:

Bitte keine Aggregatfunktionen

verwenden

SQL2-Grammatik(2)

52

expr comparisonexpr

queryexpr-list comparison expr-list

expr comparison queryALL

ANY

exprexpr-list

queryIN

NOT

EXISTS query

attr-spec IS NULLNOT

attr-spec LIKENOT

constant

predicate:

MIN(MAX(AVG(

SUM(COUNT(*)COUNT(DISTINCT)

exprDISTINCT attr-spec

attr-spec

)

)

function:

attr-spec expr=set:

attr-name

·

attr-spec:

table-namevar-name attr-name

attr-defn:attr-name INTEGER

NUMERIC(DECIMAL(CHAR(VARCHAR(

length )NOT NULL

from: table-nameview-name

from LEFT JOIN from ON condition

var-name

Informationssysteme2017 Kapitel5:DieStandardspracheSQL

Documents

5. Die Standardsprache SQL - · PDF file§SQL wurde „de facto“-Standard in der relationalen Welt (X3H2-Vorschlag wurde 1986 von ANSI ... Tabellen sind Multi-Mengen, keine Mengen!