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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 3
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"
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 4
PostgreSQL§ IndieserVorlesungwirdPostgreSQL verwendet• "Theworld's most advanced opensource database"• EinfachzuInstallieren• GuteSQLUnterstützung• Transaktionen• GuteDokumentation(esgibtauchBücherdazu)
http://www.postgresql.org/
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 5
PGAdmin3UserInterface:Downloadunterhttp://www.pgadmin.org/
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 6
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 7
BasiskonzeptederDatendefinition§ DatentypenundWertebereiche(domains)§ Tabellen• Attribute/Spalten• SchlüsseleigenschaftenundNullwerte• Default-Werte
§ Schemaänderungen
WichtigerUnterschiedzumRelationenmodell:TabellensindMulti-Mengen,keineMengen!§ TabellemussnichtunbedingteinenPrimärschlüsseldefinieren• istabermeistensderFall
§ DuplikatesindauchinAnfrageresultatenerlaubt!• Duplikateliminierungkannexplizitgefordertwerden
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 8
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 9
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))
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 10
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 11
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 12
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.
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 13
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 14
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.
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 15
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 16
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 18
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')
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 19
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!
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 20
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 21
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?
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 22
PNR FIGUR AJAHR AORT THEATERDARSTELLER (DA)
AUTOR GORT GJAHRDICHTER (DI)
TITEL AUTOR KRITIKER UORT UJAHRDRAMA (DR)
PNR NAME WORT . . .SCHAUSPIELER (SP)
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)?
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 23
PNR FIGUR AJAHR AORT THEATERDARSTELLER (DA)
AUTOR GORT GJAHRDICHTER (DI)
TITEL AUTOR KRITIKER UORT UJAHRDRAMA (DR)
PNR NAME WORT . . .SCHAUSPIELER (SP)
FIGUR TITEL RTyp . . .ROLLE (RO)
AusführungvonSQL-Anweisungen§ AbstraktesErklärungsmodellfürQ6:
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 24
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 25
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!
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 26
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 27
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 28
TITEL AUTOR KRITIKER UORT UJAHRDRAMA (DR)
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 29
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 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).
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 31
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 33
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 34
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 35
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'
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 36
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 37
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))
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 38
[NOT] EXISTS (table-exp)
QuantifiziertePrädikate– Allquantifizierung§ SQLhatkeinenexplizitenAllquantor• Allquantizierung mussdurcheineäquivalenteAnfragenmitExistenzquantizierung ausgedrücktwerden!
§ Beispiel:WelcheSchauspielerhabenalleRollengespielt?• imTupelkalkül:{𝑠𝑠|𝑠𝑠 ∈ 𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑠𝑠𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆 ∧ ∀𝑆𝑆 ∈ 𝑅𝑅𝑅𝑅𝑆𝑆𝑆𝑆𝑆𝑆𝑅𝑅(∃𝑑𝑑 ∈ 𝐷𝐷𝑆𝑆𝑆𝑆𝑠𝑠𝐷𝐷𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(
𝑑𝑑. 𝑃𝑃𝑃𝑃𝑅𝑅 = 𝑠𝑠. 𝑃𝑃𝑃𝑃𝑅𝑅 ∧ 𝑑𝑑. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅 = 𝑆𝑆. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅))}• esgiltfolgendelogischeÄquivalenz:
∀𝐷𝐷 ∈ 𝑅𝑅(𝑃𝑃 𝐷𝐷 ) ≡ ¬(∃𝐷𝐷 ∈ 𝑅𝑅 ¬𝑃𝑃 𝐷𝐷 )• Anfrageobenistalsoäquivalentzu
{𝑠𝑠|𝑠𝑠 ∈ 𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑠𝑠𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆 ∧ ¬(∃𝑆𝑆 ∈ 𝑅𝑅𝑅𝑅𝑆𝑆𝑆𝑆𝑆𝑆𝑅𝑅(¬(∃𝑑𝑑 ∈ 𝐷𝐷𝑆𝑆𝑆𝑆𝑠𝑠𝐷𝐷𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(𝑑𝑑. 𝑃𝑃𝑃𝑃𝑅𝑅 = 𝑠𝑠. 𝑃𝑃𝑃𝑃𝑅𝑅 ∧ 𝑑𝑑. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅 = 𝑆𝑆. 𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝐹𝑅𝑅))))}
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 39
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))
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 40
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 41
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 42
NULL-Werte§ Attributspezifikation:EskannfürjedesAttributfestgelegtwerden,
obNULL-Wertezugelassensindodernicht§ VerschiedeneBedeutungen• Datenwertistmomentannichtbekannt• AttributwertexistiertnichtfüreinTupel
§ EinearithmetischeOperation(+,-,*,/)miteinemNULL-WertführtaufeinenNULL-Wert
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 43
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 44
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 45
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
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 46
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.
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 47
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 48
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.
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 49
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)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 50
SQL2-Grammatik§ SQL-Syntax(Auszug,Table=Relation,Column=Attribut,
ListenelementedurchKommagetrennt)
Informationssysteme2017 Kapitel5:DieStandardspracheSQL 51
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