Microsoft Access 2010 – SQL nutzen · Access – SQL in Abfragen nutzen, 29.08.14 Seite 2 Leibniz...

Leibniz Universität HannoverIT Services

Microsoft Access 2010 – SQL nutzen

Welche Kunden haben wir?

Welche Kunden wohnen

in „Chicago“?

Welche Bestellung wurden zwischen dem

„01.03.2006“ und dem 31.03.2006“ aufgegeben?

Access – SQL in Abfragen nutzen, 29.08.14 Seite 2


S(tructured)Q(uery)L(anguage)

Standardsprache für relationale Datenbanken.

Daten in Tabellen manipulieren, aktualisieren, eintragen und löschen.

Beschreibung im aktuellen Standard SQL:2011 ISO/IEC 9075:2011.



Informationen im Web

http://www.sql-und-xml.de/sql-tutorial/

http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL

http://www.1keydata.com/de/sql/ http://sql.lernenhoch2.de/lernen/ http://www.schulserver.hessen.de/darmstadt/lichtenberg/

SQLTutorial/ http://msdn.microsoft.com/de-

de/library/cc451081%28v=vs.71%29.aspx http://www.trivadis.com/fileadmin/user_upload/

email4download_Pflicht/ PLSQL_Coding_Guidelines_101125.pdf



Listen von SQL-Befehlen

http://www.ks-hechingen.de/DV_down/bs_SQL.pdf

http://www.itslot.de/2013/12/sql-befehle-fur-anfanger.html

http://iwi.wiwi.hu-berlin.de/~viehweger/db-sql-2008-1.pdf in Bezug auf mySQL.



Handbücher etc.

Handbuch des RRZN: SQL. Grundlagen und Datenbankdesign.

Alan Beaulieu: Einführung in SQL. O'Reilly



Bereiche

DML (Data Manipulation Language).

DDL (Data Definition Language).

DCL (Data Controlling Language). TCL (Transaction Control Language).



Data Definition Language

Definition des Datenbankschemas.

Erzeugen von Tabellen.

Befehle: CREATE, ALTER, DROP. Microsoft Access: Menüband Erstellen – Tabelle.



Data Controlling Language

Rechteverwaltung.

Zugriffsrechte auf eine Tabelle.

Befehle: GRANT, REVOKE.



Transaction Control Language

Ablaufsteuerung.

Jede SQL-Anweisung ist eine Transaktion. Oder: Mehrere Befehle werden zu einer Transaktion zusammengefasst.

Befehle: COMMIT, ROLLBACK, SAVEPOINT.



Data Manipulation Language

Daten auswählen, einfügen, aktualisieren oder löschen.

Befehle: SELECT, INSERT, UPDATE, DELETE.

Microsoft Access: Abfragen, Datensatzquelle für Formulare / Berichte und Listensteuerelementen.



ER-Modell und SQL

Kunde MitarbeiterBestellung nimmt angibt auf 11 n n

Nordwind. Kunden

Nordwind.Personal

Nordwind. Bestellungen

Zeige alle Bestellungen der Firma A an. Alle Vertriebsmitarbeiter.



Beispiele

Zeige alle Kunden an.SELECT * FROM Kunden;

Zeige alle Vertriebsmitarbeiter an.SELECT Nachname, VornameFROM PersonalWHERE (Position LIKE "Vertriebsmitarbeiter*");

Zeige alle Bestellungen der Firma A an.SELECT *FROM Bestellungen INNER JOIN Kunden ON Bestellungen.[Kunden-Code] = Kunden.IDWHERE (Kunden.Firma LIKE "Firma A");



Auswahlabfragen

Sortierung und Filterung von Informationen.

Rechenoperationen mit Hilfe der vorhandenen Daten.

Verknüpfung von Tabellen über ein Schlüsselwert.



… in Microsoft Access erstellen

Menüband Erstellen.

Klick auf Abfrageentwurf in der Gruppe Abfragen.

Menüband Abfragetools – Entwurf. Ansicht – SQL-Ansicht in der Gruppe Ergebnisse.



… speichern

<STRG>+<S>. Oder: Datei – Objekt speichern unter.

In dem Dialog Speichern unter wird in dem ersten Textfeld der Name der Abfrage eingegeben. Jede Bezeichnung in Access ist eindeutig. Der Name sollte das Ergebnis der Abfrage widerspiegeln.



… ausführen

Menüband Abfragetools – Entwurf.

Ausführen in der Gruppe Ergebnisse.

In Abhängigkeit der vorhandenen Daten wird das Ergebnis in Tabellenform dargestellt. Die Tabelle ist leer, wenn keine passenden Daten vorhanden sind.



Syntax einer Auswahlabfrage

SELECT [Feld, Feld]FROM [Tabelle]

WHERE [Bedingung]

ORDER BY [Feld] ASC|DESC, [Feld] ASC|DESC;

Die Auswahlabfrage basiert auf eine Tabelle.

Als Felder werden Spalten in einer Tabelle bezeichnet.



Hinweise

Das Semikolon beendet eine SQL-Anweisung.

Groß- und Kleinschreibung wird nicht beachtet.

Leserichtung von links nach rechts.



Alle Felder aus einer Tabelle

Wähle (SELECT) aus (FROM) ....

Das Sternchen ist ein Platzhalter für alle Spalten einer Tabelle.

SELECT * FROM Kunden;



Feld ... aus Tabelle ...

Selektiere (SELECT) feldA, feldB, … von der Tabelle (FROM) ...

Bestimmte Felder aus einer Tabelle werden in der Ergebnistabelle angezeigt.

Die Feldnamen werden durch Kommata getrennt.

Hinweis: Die Feldnamen müssen in Access nicht der angezeigten Spaltenüberschrift entsprechen.

SELECT Nachname, VornameFROM Personal;



Bezeichner

Tabellennamen und Feldnamen.

Tabelle.Feld. Der Feldname wird eindeutig einer Tabelle zugeordnet. Der Punkt verbindet einen Tabellennamen mit einem Feldnamen. Der Feldname identifiziert eindeutig eine Spalte in einer Tabellen.

Bestellungen.[Kunden-Code]. Bezeichner, die Sonderzeichen enthalten werden mit Hilfe der eckigen Klammern begrenzt. Sonderzeichen sind Leerzeichen, das Minuszeichen etc.



Sortierung von Zahlen

Sortierung nach x Feldern auf- oder absteigend. Die Felder werden durch Kommata getrennt.

Die Informationen werden zuerst nach der Anzahl sortiert. Diese sortierten Informationen werden [Kosten pro Einheit] sortiert, ohne die vorhandene Sortierung zu zerstören.

Die Felder werden von links nach rechts sortiert.

SELECT [Artikel-Nr], Anzahl, [Kosten pro Einheit]FROM AuftragsdetailsORDER BY Anzahl DESC, [Kosten pro Einheit] DESC;



Sortierung von Datums- und Zeitangaben

SELECT Liefername, Lieferadresse, PLZ, Lieferort, Bestelldatum, VersanddatumFROM BestellungenORDER BY Bestelldatum, Versanddatum;

Hinweise zu Datumswerten in Microsoft Access

Gültige Datumswerte liegen zwischen dem 01.01.0100 und dem 31.12.9999.

Der Wert 0 entspricht dem Datum 30.12.1899. Das Datum 15.12.2010 entspricht einem Wert von 40527. Negative Ganzzahlen beziehen sich auf ein Datum vor dem

30.12.1899.

Hinweise zu Zeitangaben in Microsoft Access

Ein Tag entspricht dem Wert 1.

Die Nachkommastellen des Tages bilden die Uhrzeit ab. 1 Stunde 1/ 24 = 0.0417 Mitternacht 0 Mittag 12 * 1/24 = 0.5



Sortierung von Textzeichen

Selektiere (SELECT) feldA, feldB, … von der Tabelle (FROM) ... Bestimmte Felder aus einer Tabelle werden in der

Ergebnistabelle angezeigt. Die Feldnamen werden durch Kommata getrennt.

SELECT Kategorie, Produktcode, ArtikelnameFROM ArtikelORDER BY Kategorie DESC, Produktcode ASC;



Hinweise zu Textzeichen

Microsoft Access sortiert Textzeichen in Abhängigkeit des Eingabegebietsschema und Systemsteuerung – Region und Sprache. Mit Hilfe des Menüs Datei – Optionen – Allgemein kann eine abweichende Sortierung eingestellt werden.

Memo-Felder können nicht sortiert werden.



Aufsteigend sortieren

ORDER BY [feld] ASC. Standardsortierung. Eine Angabe der Sortierung ist nicht nötig.




Absteigend sortieren

ORDER BY [feld] DESC.




x Datensätze anzeigen

SELECT TOP [anzahl]. Die ersten x Datensätze. In dem Beispiel wird der Artikel mit dem höchsten Einzelpreis

und höchsten Bestellmenge angezeigt.

SELECT TOP 1 [Artikel-Nr], Einzelpreis, AnzahlFROM BestelldetailsORDER BY Einzelpreis DESC, Anzahl DESC;



x Prozent Datensätze anzeigen

SELECT TOP [prozentsatz] PERCENT. Ein bestimmter Prozentsatz in Abhängigkeit der Gesamtanzahl

der Datensätze wird angezeigt.

SELECT TOP 5 PERCENT [Artikel-Nr], Einzelpreis, AnzahlFROM BestelldetailsORDER BY Einzelpreis DESC, Anzahl DESC;

Filterung in Abhängigkeit von Bedingungen

Vergleich zweier Operanden.

Ausdrücke, die wahr sind oder nicht. Eine Verknüpfung von Vergleichen ist möglich.



Beispiel

WHERE ([feld] [operator] [wert]). Der Inhalt eines Feldes wird mit einem Wert verglichen. Der Vergleichswert kann ein konstanter Wert, ein

Rückgabewert einer Funktion oder ein Feldname sein. Die runden Klammern erhöhen die Lesbarkeit bei komplexen

Ausdrücken.

SELECT [Artikel-Nr], Einzelpreis, AnzahlFROM BestelldetailsWHERE (ANZAHL > 100)ORDER BY Einzelpreis DESC, Anzahl DESC;

Vergleichsoperatoren

ist ... Operator

gleich =

ungleich <>

kleiner <

kleiner gleich <=

größer >

größer gleich >=



Vergleich von Feldern

Um Fehler zu vermeiden, sollte ein Feld in Bedingungen immer einer Tabelle zugeordnet werden.

SELECT * FROM BestellungenWHERE Bestellungen.Versanddatum = Bestellungen.Bestelldatum;



Undefinierter Inhalt

Ist der Inhalt des Feldes nicht definiert? Die Funktion IsNull() wird in Microsoft Access für die

Überprüfung von Werten genutzt, deren Datentyp nicht definiert ist.

SELECT [Auftrags-Nr]FROM AufträgeWHERE ([Genehmigt am] IS NULL);

SELECT [Auftrags-Nr]FROM AufträgeWHERE (IsNull([Genehmigt am]));



Vergleich von boolschen Werten

True (Wahr, Ja). Überprüfung: Status <> 0. False (Falsch, Nein). Überprüfung: Status = 0.

SELECT Produktcode, Artikel.ArtikelnameFROM ArtikelWHERE (Auslaufartikel = True)ORDER BY Produktcode;



Vergleich von Zahlenwerten

Als Dezimaltrennzeichen wird der Punkt genutzt.

SELECT Produktcode, Artikelname, StandardkostenFROM ArtikelWHERE (Standardkosten > 20.50)ORDER BY Standardkosten DESC

Vergleich von Zahlenwerten

ist ... Operator Kriterium Ergebnis

gleich = 3 = 4 Falsch

ungleich <> 3 <> 4 Wahr

kleiner < 3 < 4 Wahr

kleiner gleich <= 3 <= 4 Wahr

größer > 3 > 4 Falsch

größer gleich >= 3 >= 4 Falsch



Vergleich von Datumswerten

Format: #Monat/Tag/Jahr# Begrenzung durch das Hash-Zeichen.

SELECT *FROM BestellungenWHERE (Bestellungen.Versanddatum >= #06/01/2006#)ORDER BY Bestellungen.Versanddatum DESC;



Vergleich von Zeitwerten

Format: #Stunde:Minute:Sekunde# Begrenzung durch das Hash-Zeichen.

SELECT Aufträge.[Auftrags-Nr], Aufträge.[Gesendet am], Aufträge.[Gesendet um]FROM AufträgeWHERE (Aufträge.[Gesendet um] > #12:00#);



Vergleich von Datum- und Zeitwerten

Format: #Monat/Tag/Jahr Stunde:Minute:Sekunde# Begrenzung durch das Hash-Zeichen.

SELECT Bestellungen.[Bestell-Nr], Bestellungen.[Kunden-Code], Bestellungen.Versanddatum, Bestellungen.LieferdatumFROM BestellungenWHERE (Bestellungen.Lieferdatum = #06/09/2006 09:00#);

Vergleich von Datums- und Zeitwerte


gleich = #15.12.2010# = #20.11.2011# Falsch

ungleich <> #15.12.2010# <> #20.11.2011# Wahr

kleiner < #15.12.2010# < #20.11.2011# Wahr

kleiner gleich <= #15.12.2010# <= #20.11.2011# Wahr

größer > #15.12.2010# > #20.11.2011# Falsch

größer gleich >= #15.12.2010# => #20.11.2011# Falsch



Liste von Werten

WHERE ([feld] IN([wert], [wert], ...) Einer der Elemente in der Liste muss zutreffen. Die Liste wird durch die runden Klammern begrenzt. Die einzelnen Elemente in der Liste werden durch Kommata

getrennt. Als Elemente können Zahlen, Datumswerte, Zeitwerte oder

Texte genutzt werden.

SELECT [Bestell-Nr], [Artikel-Nr], Anzahl,Einzelpreis, [Status-Nr]FROM BestelldetailsWHERE (Bestelldetails.[Status-Nr] In (1,3,4))ORDER BY [Bestell-Nr] DESC;



Zwischen … und ...

WHERE ([feld] BETWEEN[wertMin] AND[wertMax]) Für einen Wertebereich wird ein minimaler und ein maximaler

Wert eingegeben. Als Elemente können Zahlen, Datumswerte oder Zeitwerte

genutzt werden. Zeitwerte beziehen sich auf ein bestimmtes Datum.

SELECT * FROM AufträgeWHERE ([Gesendet am] BETWEEN #03/01/2006# AND #03/31/2006#);

SELECT * FROM AufträgeWHERE (

([Gesendet am] >= #03/01/2006#) AND ([Gesendet am] <= #03/31/2006#));



Vergleich von Textwerten

Begrenzung durch Anführungsstriche.

SELECT *FROM PersonalWHERE (Position = "Vertriebsmitarbeiter")ORDER BY Nachname, Vorname;

Vergleich von Text


gleich = "abc" = "ABC" Wahr

ungleich <> "abc" <> "ABC" Falsch

kleiner < "abc" < "ABC" Falsch

kleiner gleich <= "abc" <= "ABC" Wahr

größer > "abc" > "ABC" Falsch

größer gleich >= "abc" >= "ABC" Wahr



Leere Zeichenfolge

Der Inhalt des Feldes hat als definierten Inhalt eine leere Zeichenfolge.

In dem Feld sind keine Textzeichen vorhanden.

SELECT [Auftrags-Nr]FROM AufträgeWHERE (Zahlungsweise <> "");



Textvergleich mit Hilfe des Operators Like

Ist der Text ähnlich dem Wert? Arbeiten mit Vergleichsmustern.

SELECT Firma, Nachname, VornameFROM LieferantenWHERE (Nachname LIKE "Hayakawa")ORDER BY Nachname;



Vergleichsmuster

Textzeichen werden durch Platzhalter ersetzt. Der Platzhalter * ersetzt eine beliebige Anzahl von Zeichen Der Platzhalter ? ersetzt ein Zeichen. Der Platzhalter # ersetzt ein nummerisches Zeichen. Die Platzhalter können an beliebiger Position in beliebiger

Kombination in einem Vergleichsmuster genutzt werden.

SELECT Firma, Nachname, VornameFROM LieferantenWHERE (Nachname LIKE "H*")ORDER BY Nachname;



Beispiele für ein oder mehrere Zeichen

(Nachname LIKE "M*"). Suche nach allen Texten in der Feld Nachname, die mit „M” beginnen.

(Nachname LIKE "M??yer"). Suche nach allen Texten in der Feld Nachname, die mit „M” beginnen und auf „yer” enden. Zwischen „M” und „yer” dürfen zwei beliebige Zeichen stehen.

(Postleitzahl LIKE "30###"). Suche nach allen Texten in dem Feld Postleitzahl, die mit „30” beginnen. Den ersten zwei Ziffern „30” folgen drei weitere beliebige Ziffern.



Mischformen von Platzhaltern

(Bestellnummer LIKE "10#-*-L?"). Suche nach allen Texten wie zum Beispiel „101-abM-La” oder

„102-a-LB”. Das erste Textelement besteht aus drei Ziffern. Die ersten Ziffern

sind durch die 1 und 0 festgelegt. Die letzte Ziffer ist beliebig. Das zweite Textelement kann aus beliebig vielen Zeichen

bestehen. Das dritte Textelement beginnt mit einem L, dem ein beliebiges

Zeichen folgt. Die Textelemente werden durch Bindestriche getrennt.



Beispiele für eine Liste von Zeichen (Bestellnummer LIKE "101-aeR-L[NB]"). An der zweiten

Position kann der Buchstabe „N” oder „B” stehen. In den eckigen Klammern wird eine Liste von Zeichen geschrieben. Die Liste von Zeichen wird ohne ein Trennzeichen geschrieben.

(Bestellnummer LIKE "101-aeR-L[!NB]"). An der zweiten Position darf nicht der Buchstabe „N” oder „B” stehen. Mit Hilfe des Ausrufezeichens wird die nachfolgende Auflistung negiert.

(Bestellnummer LIKE "101-[a-c]eR-LN"). An der fünften Position dürfen die Buchstaben „a”, „b” und „c” stehen. Ein bestimmter Bereich von Zeichen wird angegeben.



Negation von Bedingungen

SELECT * FROM AufträgeWHERE (NOT ([Status-Nr] = 1))ORDER BY Erstellungsdatum DESC;

SELECT * FROM AufträgeWHERE ([Status-Nr] <> 1)ORDER BY Erstellungsdatum DESC;



Is Not Null

SELECT *FROM AufträgeWHERE ([Genehmigt am] IS NOT NULL);

Der Inhalt des Feldes hat einen definierten Inhalt.



Bedingung Und Bedingung

SELECT *FROM AuftragsdetailsWHERE (([Artikel-Nr] = 43) AND (Anzahl > 100));

Beide Bedingungen müssen zutreffen. Sobald die linke Bedingung nicht zutrifft, wird die rechte

Bedingung nicht ausgewertet.



Bedingung Oder Bedingung

SELECT *FROM BestellungenWHERE ((Lieferort = "Memphis")

OR (Lieferort = "Chicago"))ORDER BY Versanddatum DESC;

SELECT *FROM BestellungenWHERE ((Lieferort IN( "Memphis","Chicago"))ORDER BY Versanddatum DESC;

Eine der Bedingungen muss zutreffen. Sobald die linke Bedingung zutrifft, wird die rechte Bedingung

nicht ausgewertet.



Und- und Oder-Verknüpfungen mischen

SELECT *FROM BestellungenWHERE (((Lieferort = "Memphis") OR (Lieferort = "Chicago"))AND (Versanddatum BETWEEN #03/01/2006# AND #03/31/2014#))ORDER BY Versanddatum DESC;

Die Bedingungen sollten geklammert werden.



Rangfolge

Vergleichsoperatoren: =, >, <, >=, <=, <>

NOT

AND BETWEEN, IN LIKE, OR

Microsoft Access 2010 – SQL nutzen · Access – SQL in Abfragen nutzen, 29.08.14 Seite 2 Leibniz...

Documents

Transcript of Microsoft Access 2010 – SQL nutzen · Access – SQL in Abfragen nutzen, 29.08.14 Seite 2 Leibniz...