Teil 8 - Einführung in SQL (Structured Query Language
Werbung
Einführung in die Wirtschaftsinformatik Teil 8 - Einführung in SQL (Structured Query Language) Wintersemester 2015/16 Lehrstuhl für Wirtschaftsinformatik und Electronic Government Universität Potsdam Chair of Business Information Systems and Electronic Government University of Potsdam Univ.-Prof. Dr.–Ing. habil. Norbert Gronau Lehrstuhlinhaber | Chairholder August-Bebel-Str. 89 | 14482 Potsdam | Germany Tel Fax +49 331 977 3322 +49 331 977 3406 E-Mail [email protected] Web lswi.de 1 Inhalt Abfragesprachen Die Datenbanksprache SQL Tabellen verwalten Datensätze verwalten Datenkontrolle und -steuerung 2 Abfragesprachen Die Datenbanksprache SQL Tabellen verwalten Datensätze verwalten Datenkontrolle und -steuerung 3 Sprachen und Zugriffsmethoden Geeignete Sprachmittel für Datenbankzugriffe Sprache für Spezifikation von Datentypen und physischen Datenstrukturen (data definition language) Sprache für Formulierung von Anfragen und Änderungsoperationen (query languages) Zugriffe auf das DBMS Nutzerfunktionen Verwaltungsfunktionen Zugriffsmethoden Full Table Scan - Suchdurchlauf durch alle Datensätze einer Tabelle Indexzugriff - Notwendig für beschleunigte Suche sehr großer Tabellen 4 Imperative (Programmier-)Sprachen Befehl1 Befehl2 int width = 640 ; if (cond1 = cond2) if (x < y < z) do anything ; else ... Befehl5 Befehl3 Befehl4 Prozedurale Sprachen Prozedurale Programmierung Problemorientiert Imperatives Prinzip - Programm als Folge von Befehlen, Ausführung im Rechner in vorgegebener Reihenfolge Explizite Steuerung Aufbau von Computerprogrammen aus kleineren Teilproblemen (Aufgaben, Prozeduren) Kleinster und unteilbarer Schritt bei diesem Verfahren -> die Anweisung Implizite Logik Jeder Programmalgorithmus besteht aus Logik und Steuerung. 5 Deklarative Sprachen (4GL) Gib mir alle Daten über die männlichen Mitarbeiter, die älter als 50 Jahre sind! Deklarative/Deskriptive Sprachen SELECT abt_nr, position, SUM(gehalt) Gehaltssumme FROM mitarbeiter GROUP BY abt_nr, position ORDER BY abt_nr; Deklarative Programmierung Beschreibend (deklarativ) - beschreibt Informationen, nicht aber die Darstellung Beschreibung über Anfragen, welche Informationen gesucht werden Deskriptive Sprache - Vereinfachung des Programmierprozesses Eine Anweisung beinhaltet eine Anfrage Explizite Logik Implizite Steuerung 6 Formulierungsmerkmale Prozedurale Formulierung (3. Generation) (1) Nimm Mitarbeiter aus Tabelle Personal (2) Prüfe, ob männlich (3) Falls JA, notiere Mitarbeiter (4) Prüfe, ob letzter Mitarbeiter (5) Falls NEIN, zurück zu (1); sonst ENDE Deklarative Formulierung (4. Generation, SQL) Wähle Spalte aus, wähle Tabelle aus, lege Bedingung(en) fest Beispiel: SELECT Name FROM Personal WHERE Geschlecht="männlich" 7 Der Weg zur Datenbank (Abfragen) ER-Modellierung Anforderungen Reale/ ideelle Welt Selektion Gedankenmodell Konzeptionelles Modell Relationale Modellierung Logisches Modell Normalisierung Schemata für Tabellen Tabellen Abfrage Realisierung/ physisches Modell Gewünschte Daten: Ausgabe in tabellarischer Form 8 Abfrageprinzip Abfragen liefern die gewünschten Daten in Tabellenstruktur Ausgabe - Kombination von Datensätzen und Spalten Tabelle 1 Tabelle 2 Tabelle 3 Abfragesystem Tabelle 4 Spezifikation der Daten Abfrageschema Tabelle Benutzer Abfragen liefern eine Ausgabetabelle 9 Abfragesprachen Die Datenbanksprache SQL Tabellen verwalten Datensätze verwalten Datenkontrolle und -steuerung 10 Grundlegende Merkmale von SQL Befehle zur Definition, Manipulation und Abfrage von Daten Ermöglicht Benutzer, Online-Abfragen über ein Programm direkt am Bildschirm einzugeben --> Abfrageergebnis liegt sofort vor "Konstruktionsteile" Sichten Tabelle PLZ Ort 03122Riesa SQL Formular 15788Zossen 51270KleinGerau 10530Berlin 22800Sieversen Tabelle <- Anfragen -> SQL ist die Standardsprache relationaler Datenbanksysteme. 11 Sprachelemente - Zuordnung zu Kategorien SELECT Datenabfrage (DML) Auswahl Datensätze und Spalten, Verknüpfung von Tabellen Beispiel: SELECT <Spalte(n)> FROM <Tabelle(n)> Definition des Datenbankschemas (DDL) Befehle zur Datenmanipulation (DML) Einfügen, Ändern, Löschen in Tabellen Beispiel: INSERT INTO <Tabelle> <Merkmale> VALUES <Werte> Befehle zur Zugriffssteuerung (DCL) Erzeugen, Ändern, Löschen von Tabellen Erzeugen, Ändern, Löschen von Benutzerrechten Beispiel: CREATE TABLE tabname Beispiel: GRANT <Funktion(en)> ON <Tabelle> TO <Nutzer> Die wesentlichste Kategorie sind Abfragen mit SELECT. 12 Abfragen und Datenmerkmale Attr_A Attr_B Attr_C Martin 24 1,78 Anne 21 1,69 Grit 23 1,71 Text Zahl Zahl Ergebnisse von Abfragen Attr_A A B C Attr_B Attr_C 235 01.09.89 32241 23.04.93 99 17.12.56 ganzzahlige Werte 0...65.535 Feld - Kreuzungspunkt Attribute - Spaltenorientierung Wertevorrat durch Wertebereich festgelegt Attributausprägung - Konkrete Werte von Attributen einzelner Entities Wertebereich - numerischer, alphanumerischer, Datums-/Uhrzeit- oder logischer (wahr, falsch) Datentyp Domäne - Wertebereiche von Attributen Jedes einfache Attribut eines Entitytyps ist mit einer Wertemenge verknüpft. Quelle: Elmasri, 2002, S. 72 13 Wertebereiche Numerische Werte Zeichenkettenwerte Ganze Zahlen (in dezimalem oder hexadezimalem Format) Alphanumerische Zeichen (in Hochkommata bzw. Anführungszeichen gesetzt) Fließkommazahlen (Ziffernfolge - Dezimalpunkt Ziffernfolge) Sonderzeichen (nicht druckbare Zeichen, Steuerzeichen) Wissenschaftliche Notation (Darstellung von Exponentialzahlen) Datums- und Zeitwerte Datum (z. B. im Format 'Jahr-Monat-Tag') Uhrzeit (Stunden-Minuten-Sekunden NULL-Wert Typloser Wert ('kein Wert' oder 'unbekannter Wert' in einem Feld) Quelle: Elmasri, 2002, S. 227f 14 Spaltentypen (Attributtypen) Feldtypen - Numerische Werte Ganze Zahlen - Number (Oracle), INTEGER, SMALLINT (MySQL) Reelle Zahlen - FLOAT, REAL Feldtypen - Zeichenketten (Strings), alphanumerische Werte CHAR(Länge), VARCHAR2(Länge) Strings mit max. Zeichenlänge (Oracle) Feldtypen - Datums- und Uhrzeitwerte DATE: Feldtyp für Datumsanzeige "2000-04-03" TIME: spezieller Feldtyp zur Anzeige der Zeit Die vorherrschenden Datentypen in Attributen sind numerische und Zeichenkettenwerte. 15 Spaltentypen - Optionen und Sondertypen Zusätzlich zum Feldtyp sind als Optionen möglich: Große Datenobjekte - Grafiken, Textmengen 'not null' - das Feld darf nicht leer bleiben Große, unstrukturierte Objekte (BLOB - Binary Large Objects) 'default value' - wird als Vorgabe genommen Große Zeichendatentypen (CLOB Character Large Objects) Feldtyp - Logischer Wert BOOLEAN: kann nur Werte true oder false annehmen 'auto_increment' - für Primärschlüsselfelder, Wert wird mit neuem Datensatz automatisch erhöht primary key - Primärschlüssel ("Es kann nur EINEN geben") In modernen Datenbanken können auch beliebige multimediale Objekte gespeichert werden. 16 Attribute des Entitytyps Artikel Zulässige Datentypen in Oracle Datentyp Beschreibung VARCHAR2 (wert) Zeichendaten variabler Länge CHAR (wert) Zeichendaten fester Länge NUMBER (p,s) Numerische Daten variabler Länge (p: Länge insges., s: davon Nachkommastellen) DATE Datums- und Zeitwerte CLOB Zeichendaten bis 4 GB Beispiel-Schema einer Artikelstammdatei Spaltenname Artikelnummer Artikelname Hersteller Artikelgruppe Menge Nettopreis Datentyp VARCHAR2 (6) VARCHAR2 (30) VARCHAR2 (30) VARCHAR2 (8) NUMBER (5,0) NUMBER (6,2) Auf NULL setzbar No No No Yes Yes Yes Primärschlüssel 1 - - - - - Feld 1 Feld 2 Feld 3 Feld 4 Feld 5 Feld 6 Die Attributwerte des Primärschlüssels dürfen nie NULL sein! 17 SQL - deklarative Abfragesprache Nutzer legt nur Bedingungen für die gesuchten Daten fest: SELECT attribut FROM tabelle WHERE attribut = ,Angela Kohl‘ Welches Merkmal soll ausgewählt werden? Aus welcher Tabelle stammt das Merkmal? Nach welchen Auswahlbedingungen soll gesucht werden? Häufig leichtere Formulierungen --> Nutzer kann unkompliziert eine effiziente Auswertung realisieren Viel kürzer als imperative Programmierung --> billigere Programmentwicklung Schnittstellen zu höheren Programmiersprachen vorhanden 18 Abfragesprachen Die Datenbanksprache SQL Tabellen verwalten Datensätze verwalten Datenkontrolle und -steuerung 19 Betrachtung der SQL-Anweisungen am Beispiel Ausgangssituation: Struktur der Datenbank „Firma WiProM AG“ mitarbeiter PERS_NR VORNAME NAME A* LEITER POSITION G* EINSTELLUNG GEHALT abteilung ABT_NR ABT_NAME ABT_NR PROJ_NR .. A* ~ ANREDE G* ~ GEBURTSTAG BETR_TEIL bereich BETR_TEIL BEREICHSNAME BETRIEBSTEILNAME BETR_ORT PLZ ORT STRASSE LAND betriebsort BETR_ORT BETRIEBSTEILNAME PLZ ORT STRASSE LAND projekt PROJ_NR PROJ_NAME arbeitet_in PERS_NR PROJ_LEITER PROJ_BEGINN PERSONALKOSTEN PROJEKTBESCHREIBUNG Projektdaten PROJ_NR PROJ_MA_NR BEGINN ENDE Projektpartner bank BLZ BANK PROJ_NR PROJ_PARTNER ADRESSE 20 Regeln für die Tabellenerstellung Namensregeln für Tabellen und Spalten Müssen mit einem Buchstaben beginnen Länge mindestens 1 und maximal 30 Zeichen Zulässige Zeichen: A-Z, a-z, 0-9, _, $, # Grundsätzlich unterschiedliche Namen der Objekte (z.B. Tabellen) eines Benutzers Nicht zulässige Namen - für Oracle reservierte Worte Rechtevergabe - Privilegien für Tabellenerstellung CREATE TABLE [schema.]tabelle (spalte datentyp [DEFAULT ausdruck][, ...]); Quelle: Greenberg 2002, S. 372 21 Tabellenverwaltung mit CREATE CREATE TABLE - Erstellen einer Tabelle Voraussetzung für Benutzer - Besitz des CREATE TABLE-Privilegs CREATE TABLE tabelle ( spalte datentyp [NOT NULL] , ..., PRIMARY KEY (spalte,..), FOREIGN KEY (spalte,..) REFERENCES referenztabelle(spalte) ); Schlüsselwörter CREATE TABLE - Tabellenname, Spaltenbezeichner NOT NULL - Feld darf nicht leer sein (optional) PRIMARY KEY - Schlüsselattribut FOREIGN KEY – Verknüpfungen zu anderen Tabellen der Datenbank REFERENCES - Verweis, Attribut ist Primärschlüssel in Fremdtabelle Alle Attributnamen innerhalb einer Tabelle müssen eindeutig sein. 22 Erzeugung einer neuen Tabelle Erster Schritt - Tabelle mit CREATE erstellen CREATE TABLE betriebsort (betr_ort VARCHAR2(1) NOT NULL, betriebsteilname VARCHAR2(35), plz VARCHAR2(6), ort VARCHAR2(30), strasse VARCHAR2(40), land VARCHAR2(20) laendercode number(5); Table created. Schlüsselwörter DESCRIBE betriebsort; Name Null? Type BETR_ORT BETRIEBSTEILNAME PLZ yes VARCHAR2(1) VARCHAR2(35) VARCHAR2(6) ORT VARCHAR2(30) STRASSE LAND LAENDERCODE VARCHAR2(40) VARCHAR2(20) NUMBER(5) 23 Tabellenverwaltung mit ALTER Nachträgliches Ändern einer Tabellenstruktur ALTER TABLE tabelle ADD (spalte datentyp DEFAULT ausdruck),... MODIFY (spalte datentyp DEFAULT ausdruck),... DROP (spalte, ...) COLUMN ({spalte datentyp [NOT NULL]} , ...); Mögliche Aktionen Hinzufügen neuer Spalten - ADD Ändern vorhandener Spalten - ALTER COLUMN - ändert Datentyp Spalte Definition von Default-Werten für einzelne Spalten Löschen von Spalten - DROP Quelle: Greenberg 2002, S. 391 24 Hinzufügen von Spalten Hinzufügen mit ADD - neue letzte Spalte ALTER TABLE abteilung ADD (bemerkung VARCHAR2(40)); Table altered. ABT_NR ABT_NAME 100VVorstandsstab 105CControlling 106QQualitätssicherung 107RInterne Revision 210EEinkauf 220LLogistik 230PProduktionsplanung 240PProzessplanung 250AArbeitsvorbereitung 260FProduktion-Fertigung ...... BETR_TEIL GL GL GL GL PO PO PO PO PO PO ... ... BEMERKUNG ... 25 Ändern von Spalten Änderung von Datentyp, Größe und Default-Werte einer Spalte Auswirkung des Default-Wertes nur auf nachfolgende Einfügungen ALTER TABLE projekt MODIFY (proj_name VARCHAR2(50)); Table altered. Struktur der erstellten Tabelle prüfen Name Null? Type PROJ_NR yes NUMBER(3) PROJ_NAME yes VARCHAR2(50) PROJ_LEITER PROJ_BEGINN VARCHAR2(30) yes DATE PERSONALKOSTEN NUMBER(8) PROJEKTBESCHREIBUNG VARCHAR2(100) Quelle: Greenberg 2002, S. 394 26 Löschen von Spalten Löschen nicht mehr benötigter Spalten ALTER TABLE bereich DROP betriebsname; Table altered. Löschung nur für jeweils eine Spalte Anzeige der Änderung mit SELECT BETR_TEIL BEREICHSNAME BETR_ORT PLZ ORT STRASSE LAND GL Geschäftsleitung ZK 14482 Potsdam Griebnitzseeallee 1 PO Produktion, Organisation PB 14482 Potsdam Buchenallee 8 KE Konstruktion, Entwicklung PB 15745 Wildau Waldarbeiterweg 8 FR Finanzbuchhaltung, Rechnungswesen ZK 31080 Toulouse Cedex 6 Square du général Charles de Gaulle 22 Frankreich HR Personal ZK #3976 Quito Av. 10 de Agosto Ecuador IT Technik ZK 12084 Douala Rue Pierre Loti Bonanjo Kamerun 27 Tabellenverwaltung mit DROP Löschen einer Tabelle Ergebnis - Tabelle, Struktur und alle in ihr enthaltenen Datensätze sind danach gelöscht Vorgang ist NICHT umkehrbar DROP TABLE tabelle; DROP TABLE projektpartner; Achtung: Mit Ausführen des Befehls erfolgt eine unwiderrufliche Löschung (kein zusätzliches Speichern notwendig). 28 Abfragesprachen Die Datenbanksprache SQL Tabellen verwalten Datensätze verwalten Datenkontrolle und -steuerung 29 Datenmanipulation ... Teilmenge von Privilegien, die vergeben werden können Hinzufügen Ändern Löschen Voraussetzung für Benutzer - Objektprivilegien Berechtigung zur Durchführung bestimmter Aktionen für ausgewählte Tabellen Eigentümer hat alle Privilegien für ein Objekt (z.B. Tabelle) Möglichkeit der Rechtevergabe an andere Nutzer ... ist die Modifikation von Datensätzen bereits bestehender Tabellen 30 Einfügen von Datensätzen mit INSERT Voraussetzung - bereits bestehende Tabelle INSERT INTO tabelle [(spalte [, spalte...])] VALUES (wert [, wert...]); Forderung - Verträglichkeit der Werte mit dem Datentyp des Feldes VALUES - Einfügen der Werte in Reihenfolge der Attribute CHAR- und DATE-Werte in Hochkommata INSERT INTO Kunde (kd_nr, vorname, name) VALUES (233, 'Max', 'Rotberg'); INSERT INTO Kunde VALUES (232, 'Ulrike', 'Rost', '14478', 'Potsdam', 'Habichthorst 12'); Quelle: Greenberg 2002, S. 321 31 Erzeugen eines neuen Datensatzes Neue Zeile mit Werten für jede Spalte Angabe der Werte in der Default-Spaltenreihenfolge Spaltenangabe optional in der INSERT-Klausel INSERT INTO abteilung(abt_nr, abt_name, betr_teil) VALUES (108M, 'Marketing', 'PO'); 1 row created. ABT_NR ABT_Name BETR_TEIL 100V Vorstandsstab GL 105C Controlling GL 106Q Qualitätssicherung GL ... ... ... 630E Technik Endgeräte IT 640S Technik Service, Support IT 108M Marketing PO Quelle: Greenberg 2002, S. 322 32 Ändern von Datensätzen mit UPDATE Anweisung UPDATE - Änderung des Inhalts einer Spalte Möglichkeit der gleichzeitigen Änderung einer oder mehrerer Spalten UPDATE tabelle SET spalte = wert [, spalte = wert, ...] [WHERE bedingung]; WHERE-Klausel - Auswahl bestimmter Datensätze zum Ändern Ohne Einschränkung - Aktualisierung aller Datensätze Quelle: Greenberg 2002, S. 329 33 Aktualisierung von Datensätzen WHERE-Klausel - Einschränkung auf einzelne Zeile oder Gruppe von Zeilen UPDATE mitarbeiter SET anrede = 'Herr' WHERE anrede = 'H'; 86 row(s) updated. SELECT pers_nr, anrede, name, position FROM mitarbeiter; Anzeige der Änderung mit SELECT PERS_NR ANREDE NAME POSITION 101001 Herr Schulze Assistent 101002 Herr Lange Geschäftsführer 101003 F Metz Sekretärin ... ... ... ... 101049 Herr Junge Auszubildender 101050 Herr Altermann Praktikant Die Auswahl der Zeilen kann über AND- bzw. OR-Operatoren eingeschränkt bzw. erweitert werden. 34 Löschen von Datensätzen mit DELETE Anweisung DELETE - Löschung von Zeilen Über Bedingung der WHERE-Klausel - Auswahl der Datensätze Ohne WHERE-Klausel - Löschung aller Datensätze einer Tabelle Keine Löschung von Tabelle und Struktur DELETE [FROM] tabelle [WHERE bedingung]; Löschung einzelner Zeilen - Bedingung: Wert aus Primärschlüssel Löschung von Gruppen - Bedingung: Wert aus Nichtschlüsselspalte oder Verknüpfung mehrerer Werte Das Löschen von Datensätzen sollte grundsätzlich nur mittels Angabe einer WHERE-Klausel erfolgen! Quelle: Greenberg 2002, S. 336 35 Löschung einzelner Datensätze WHERE-Klausel - Abfrage der Werte aus Primärschlüssel oder aus Schlüsselfeld mit Eigenschaft UNIQUE DELETE FROM abteilung WHERE abt_name = 'Controlling'; 1 row deleted. Hinweis: Löschung erfolgt unter Beachtung der Datenintegrität SELECT * FROM abteilung; Anzeige der Änderung mit SELECT Gelöschte Datensätze werden nicht mehr angezeigt ABT_NR ABT_Name BETR_TEIL 100V Vorstandsstab GL 106Q Qualitätssicherung GL ... ... ... 630E Technik Endgeräte IT 640S Technik Service, Support IT 108M Marketing PO Zeilen mit einem Primärschlüssel, der in einer anderen Tabelle als Fremdschlüssel dient, können nicht gelöscht werden. 36 Löschung von Datensätze mit Unterabfragen Löschung von Zeilen basierend auf Werten einer anderen Tabelle über Unterabfragen DELETE FROM mitarbeiter m WHERE m.abt_nr = (SELECT a.abt_nr FROM abteilung a WHERE abt_name = 'Controlling'); 2 rows deleted. Suche von Datensätzen über Fremdschlüssel PERS_NR NAME …....... .......... VORNAME ..... ..... ANREDE ..... ..... LEITER ..... ..... ..... ABT_NR PROJ_NR .......... 105C..... ABT_NR PROVISION ..... ..... ABT_NAME .......... 105CControlling BETR_TEIL ..... P Quelle: Greenberg 2002, S. 337 37 Abfragesprachen Die Datenbanksprache SQL Tabellen verwalten Datensätze verwalten Datenkontrolle und -steuerung 38 Steuerung der Benutzerrechte Datenbanksicherheit Privilegien Systemsicherheit - Zugriff auf System (Organisation) Zugriff auf Datenbank Systemprivilegien Datensicherheit - Zugriff auf Datenbankobjekte Zugriff auf Datenbankobjekte und Bearbeitung - Objektprivilegien Datenbanknutzer Systemadministrator (Einfacher) Benutzer 39 Datenbank - Kontrolle der Zugriffsrechte Datenkontrollsprache (Data Control Language) Typische SQL-Befehle der DCL Kontrolle der Sicherheit und der Zugriffsrechte für Objekte oder Teile eines Datenbanksystems GRANT – vergibt Zugriffsrechte Befehle liegen näher bei der Sprache des DBMS REVOKE – löscht vorher vergebene oder verweigerte Zugriffsrechte DENY – verweigert Zugriffsrechte 40 Zusammenfassung - Übersicht der SQL-Anweisungen Datenmanipulationssprache (Data Manipulation Language) Datendefinitionssprache (Data Definition Language) INSERT - Einfügen CREATE - Erzeugen UPDATE - Ändern ALTER - Ändern DELETE - Löschen DROP - Löschen RENAME - Umbenennen 41 Evaluation https://pep.uni-potsdam.de/ce/e8a4cdc3/de.html Android App: z.B. Barcode Scanner iOS App: z.B. QR Reader for iPhone Windows Phone/10 App: z.B. QuickMark QR Code Reader 42 Kontrollfragen Welche Aufgaben können mit DDL-Operationen realisiert werden? Kann über DML-Sprachelemente ein neuer Datensatz erzeugt werden? Können neue Datensätze auch eingefügt werden, obwohl nicht für alle Spalten Daten vorhanden sind und eingetragen werden können? Wo finden Datumsfunktionen ihre Anwendung? 43 Literatur Kemper, A./Eickler, A.: Datenbanksysteme; 6. Auflage, 2006, Oldenbourg Verlag Greenberg, N./Nathan, P: Professioneller Einstieg in Oracle9i SQL - Band 1; 2002, Oracle Elmazri, R./Navathe, S. B.: Grundlagen von Datenbanksystemen; 3. Auflage, 2002, Addison-Wesley 44 Zum Nachlesen Kontakt Univ.-Prof. Dr.-Ing. Norbert Gronau Universität Potsdam August-Bebel-Str. 89 | 14482 Potsdam Germany Tel. +49 331 977 3322 E-Mail [email protected] Gronau, N., Gäbler, A.: Einführung in die Wirtschaftsinformatik GITO Verlag Berlin, 5. Auflage 2014 ISBN 978-3942183901 45
