ÜK Modul 105 Übersicht SQL - Arbeitsbuch Patrick Urfer
Werbung
Ausbildung Arbeitsbuch Informatik ÜK Modul 105 Modulbezeichnung: ÜK Modul 105 Kompetenzfeld: Datenbanken mit SQL bearbeiten Kompetenz: Logisches Datenbankschema mit Standardsprachmitteln (Structured Query Language SQL) in ein relationales Datenbank Management System (RDBMS) integrieren. Transaktionen durchführen. 1. 2. 3. 4. 5. Ein Logisches Datenbankschema mit Hilfe von SQL-Befehlen (DDL) das Datenbankschema in einem RDBMS implementieren. Rollen/Berechtigungen vergeben zur Gewährleistung der Datensicherheit und des Datenschutzes. Mit einem Datenbank Utility (Bulk load) oder mittels SQL-Befehlen die Datenbank mit Testdaten laden. Mit SQL-Befehlen Transaktionen zur Bearbeitung und Auswertung der Datenbank ausführen. Aufgrund geänderter Anforderungen das Datenbankschema anpassen. Übersicht SQL SQL ist eine Datenbanksprache zur Definition von Datenstrukturen und dessen Modulierung in relationalen Datenbanken, welche eine hohe Verwendung in vielen Datenbankmanagementsystemen(DBMS) findet. Sie besteht aus 4 Sprachschichten welche jeweils bestimme Einsatzgebiete abdecken. Die 4 Sprachschichten sind folgende: Data Definition Language(DDL): Die DDL oder auch Data Definition Language hat den Nutzen Datenbanken, Tabellen und Attribute erstellen / ändern und löschen zu können. Die Wichtigsten in der DDL enthaltenen Befehle sind CREATE, ALTER und DROP. Data Control Language(DCL): Die DCL oder auch Data Control Language hat wie der Name es verrät den Nutzen wie bei Sensiblen Daten üblich die Zugriffskontrolle zu Regeln und Verwalten. Die dafür am Wichtigsten in der DCL enthaltenen Befehle sind GRANT und REVOKE um die Daten zu schützen. Data Manipulation Language(DML): Die DML oder auch Data Manipulation Language ist die Sprache zur Manipulation vorhandener Daten sofern allfällige Änderungen an den Vorhandenen Daten anfallen, wird die DML mit den Unterstützenden Befehlen wie INSERT, UPDATE und DELETE verwendet. Mit diesen Befehlen lassen sich die benötigten Manipulationen durchführen ohne viel Aufwand. Data Query Language(DQL): Die DQL oder auch Data Query Language ist die Sprache zur Abfrage/Suche/Filterung der vorhandenen Daten, sie ist die gebräuchlichste und meist verwendete Sprachschichte von SQL der bekannteste Befehl der DQL ist dieser Abfragebefehl nämlich SELECT. Modul105 Patrick Urfer 17.06.2015 Seite 1 von 19 Ausbildung Arbeitsbuch Informatik Datenbanksystem(DBS): Was genau ist ein Datenbanksystem und welche 2 Komponenten sind Hauptbestandteil davon? Dieser Frage gehen wir hier auf den Grund um zu verstehen wie ein Datenbanksystem funktioniert und was dafür von Nöten ist damit es Funktioniert wie es gewünscht ist. Ein Datenbanksystem auch bekannt als DBS besteht aus 2 unabdingbaren Elementen und diese sind zum einen die Datenbank/en auch bekannt als DB und zum anderen das Datenbankmanagementsystem auch bekannt als DBMS dieser Aufbau ist ein wenig leichter zu verstehen mittel der folgenden Grafik. In dieser sehen sie ein Anwenderprogramm welches Daten speichert und verwendet mittels des Datenbanksystems, die Aufgabe des Anwenderprogrammes erfolgt an das Datenbanksystems dieses nimmt danach Zugriff auf das Datenbankmanagementsystem und dieses wiederrum verwaltet die verwendeten Datenbanken und lässt uns mit Ihnen arbeiten. Als nächste erzähle ich Ihnen noch etwas über die 2 Elemente welche nun mehrfach erwähnt aber nie genau beschrieben wurden, ich spreche natürlich von der DB und dem DBMS. Datenbank(DB): Die Datenbank ist wie gesagt der eigentliche Aufbewahrungsort unserer Daten, an sich gibt es keine wirklichen Begrenzungen wofür Datenbanken verwendet werden können, hier stellt sich immer nur die Sinnigkeit und ob es Vernünftig ist eine Datenbank für diesen Zweck anzulegen. Gerade bei Sensiblen Daten ist es auch umso Wichtiger dass diese Daten korrekt und sicher aufbewahrt/gelagert sowie regelmäßig Gewartet werden, damit die Sicherheit und Aktualität sichergestellt werden kann. Je nach Größe und Art von Datenbank um die es sich handelt kann der Unterhalt sofern man diese Wartungen nicht selber realisieren kann, sehr Teuer werden. Datenbankmanagementsystem(DBMS): Die Daten innerhalb einer Datenbank haben nur dann einen realen Wert, sofern man als Benutzer auch damit arbeiten oder die Daten einsehen kann. Für beide Tätigkeiten benötigt man eine Schnittstelle, welche das DBMS auch bekannt als Datenbankmanagementsystem liefert und uns somit Zugriff auf unsere Daten ermöglicht. Es ermöglicht allerdings nicht nur den Zugriff sondern auch die zentrale Steuerung und Kontrolle unserer Daten. Das DBMS gewährleistet uns auch einen Schutz gegen Soft- und Hardware-Fehler dass die Daten bei diesen nicht verloren gehen oder wiederhergestellt werden können. Modul105 Patrick Urfer 17.06.2015 Seite 2 von 19 Ausbildung Arbeitsbuch Informatik Relationale Datenbanken Bei relationalen Datenbanken werden die Informationen in verschiedenen miteinander verbundenen Tabellen aufbewahrt, diese Verbindungen erfolgen mittels eines Schlüssel-Prinzips mit denen man die Datensätze von zwei Tabellen in eine Relation(Verbindung) bringt. Relationale Datenbanken erlauben es vor allem das Daten möglichst frei von Redundanzen gespeichert werden können, hiermit lassen sich vor allem auch Inkonsistenzen in der Datenbank nahezu gänzlich vermeiden. Als nächstes erläutere ich die 2 Wichtigsten Schlüssel dieses Prinzips. Primärschlüssel(Primary Key): In der Regel verfügt jede Tabelle auch über einen Primary Key dem Primary Key sind folgende Eigenschaften vordefiniert: Der Primary Key muss Einzigartig und Eindeutig sein. Je kürzer umso besser da der Primary Key an vielen Stellen innerhalb einer Datenbank Verwendung findet, würde ein zu langer Primary Key unnötige Performance verschwenden. Er muss für eine Funktionstüchtige Datenbank somit Unveränderlich sein da sonst bei den vielen Referenzen welcher der Primary Key besitzt auch die Veränderungen durchgeführt werden müssen was eine Menge Aufwand und Fehleranfälligkeit hervorruft. Er sollte möglichst einfach Verwendbar sein das beim Einfügen eines neuen Datensatzes die Werte automatisiert oder ohne Zusatzaufwand für den Primary Key gesetzt werden können Aus genau diesem Grund wird beim Primary Key nahezu immer ein Ganzzahliger Wert verwendet mit einer Auto_Increment Funktion verknüpft, welche den Wert um den Festgelegten Betrag automatisch erhöht und Korrekt einfügt sowie weiterführt. Fremdschlüssel(Foreign Key): Der Foreign Key ist ein Attribut oder eine Kombination von verschiedenen Attributen welche in einer anderen Tabelle derselben Datenbank als Primary Key definiert und in Verwendung stehen. Mit dem Foreign Key wird also immer in anderen Tabellen der Primary Key von anderen Tabellen referenziert und somit Verknüpfungen zu diesen Primary Keys realisiert. Referentielle Integrität Die Referentielle Integrität in einem Datenbanksystem sichert ab, dass nur Foreign Keys eingegeben werden können, die in der verknüpften Tabelle als Primary Key verwendet werden, hiermit gewährleistet die Referentielle Integrität deutlich weniger Fehleingaben und hält somit die Datenbank sauber. Die Referentielle Integrität regelt somit das Verhalten der Datenbank bei der Löschung oder Mutation von Primary Keys welche in anderen Tabellen als Foreign Keys in einer Relation stehen. Modul105 Patrick Urfer 17.06.2015 Seite 3 von 19 Ausbildung Arbeitsbuch Informatik Ablauf Datenbankerstellung Datenbankschema erstellen Bevor man eine Datenbank mittels SQL erstellt wird zuerst immer ein Datenbankschema erstellt beim Erstellen eines Datenbankschemas gibt es 2 gängige Methoden dies sind folgende: Datenmodellierung Wenn noch keine Datensätze vorhanden sind entwickelt man das Datenmodell aufgrund der Anforderungen und Wünschen des Kunden oder des zukünftigen Benutzers. Datennormalisierung Sind bereits Datensätze in anderer Form vorhanden realisiert man mittels der Normalisierung des aktuellen Datenbankschemas in das neue Datenbankschema. Beziehungen definieren Nachdem die Tabellen des Datenbankschemas bekannt sind, benötigen wir die Beziehungen, zwischen den jeweiligen Tabellen. Hier ein Beispiel von Beziehungsnotationen: Hier in diesem Beispiel wird Links die IBM- und rechts die Zehnder-Notation verwendet, nach diesen jeweiligen Linien werden noch die Tabellen angehängt das sieht so aus: 1:n([0 oder 1] zu beliebig vielen) Beziehung Jede Entität aus der ersten Entitätsmenge kann mitbeliebig vielen Entitäten aus der zweiten Entitätsmenge in Beziehung stehen. Jede Entität aus der zweiten Entitätsmenge kann mit höchstens einer Entität aus der ersten Entitätsmenge in Beziehung stehen. Attribute und Datentypen bestimmen Nachdem die Beziehungen der jeweiligen Tabellen definiert sind, definieren wir nun die Attribute(Spalten der Tabellen) und die jeweilig dazugehörenden Datentypen. In einem Attribut werden Informationen sowie Eigenschaften z.B. ein Text, ein Datum oder eine Zahl, verwaltet, somit verfügt jedes Attribut über die gebrauchten Eigenschaften einen Wert des entsprechenden Datentyps zu speichern. Modul105 Patrick Urfer 17.06.2015 Seite 4 von 19 Ausbildung Arbeitsbuch Informatik Normalisierungsprozess In der Praxis ist der am meisten aufkommende Fall das bereits Daten existieren, diese müssen mittels Normalisierung in die Entsprechende Normalform gebracht werden, ich werde hier nur ein Einblick auf die ersten 3 Normalisierungsstufen geben, da mehr als dies in der Praxis eher eine Seltenheit ist. 1.Normalform Definition: Eine Relation ist dann in der 1. Normalform, wenn alle Attribute nur einfache Attributwerte aufweisen. Das Nichtvorhandensein von Daten ist zulässig. 2.Normalform Definition: Eine Datenbank befindet sich dann in der zweiten Normalform, wenn die erste Normalform erfüllt ist und für jeden Primärschlüssel atomare(Eindeutige) Attributwerte vorhanden sind. 3.Normalform Definition: Die Relation befindet sich in der 3. Normalform, wenn sie sich in der 2. Normalform befindet und alle Nichtschlüsselattribute (= alle Attribute, die nicht zum Primärschlüssel gehören) direkt von dem/einem Schlüssel abhängen (und nicht: von einem Nichtschlüsselattribut) - wenn also keine transitive Abhängigkeit zwischen einem Nichtschlüsselattribut und einem Schlüssel besteht. Modul105 Patrick Urfer 17.06.2015 Seite 5 von 19 Ausbildung Arbeitsbuch Informatik Datenbanken Einrichten und Verwalten Datenbanken verwalten In der Verwaltung von Datenbanken geht es um einige spezifische Aufgaben wie das Erstellen, das Anzeigen, das Löschen und das Auswählen, diese 4 Teilbereiche werden wir in diesem Thema anschauen. Datenbank erstellen Im Ersten Schritt muss sicherlich immer die Datenbank erstellt werden, denn wo nichts ist kann auch nichts verwaltet werden. Die Erstellung der Datenbank erfolgt in SQL über die Syntax: CREATE DATABASE IF NOT EXISTS datenbankname; Mit dem Zusatz = IF NOT EXISTS überprüfen wir ob die Datenbank mit dem Namen bereits Existent ist. Datenbank anzeigen Wenn Sie Ihre Datenbanken mit Namen anzeigen lassen möchten können Sie mit folgender Syntax genau dies Erreichen: SHOW DATABASES; Datenbank auswählen Wenn Sie mit einer Ihrer Datenbanken arbeiten möchten so müssen Sie diese vorher auswählen, um eine Datenbank auszuwählen wird folgende Syntax verwendet: USE datenbankname; Sie können nun damit anfangen in Ihrer Datenbank alles zu bearbeiten, Erstellen oder Verwalten, die Ausgewählte Datenbank bleibt bis zum Ende der Sitzung oder zur Auswahl einer anderen Datenbank bestehen. Datenbank löschen Um eine Datenbank wieder zu löschen verwenden wir folgende Syntax: DROP DATABASE datenbankname; Mit diesem Befehl sollte sehr vorsichtig umgegangen werden, da die komplette Datenbank inklusive allem Inhalt ohne eine Warnung gelöscht wird. Modul105 Patrick Urfer 17.06.2015 Seite 6 von 19 Ausbildung Arbeitsbuch Informatik Tabellen Einrichten und Verwalten Tabellen verwalten In der Verwaltung von Tabellen geht es primär um einige spezifische Aufgaben wie das Erstellen, das Anzeigen, das Löschen und das Auswählen, diese 4 Teilbereiche und das Erstellen sowie Definieren der Attribute werden wir in diesem Thema anschauen. Tabellen erstellen Alsbald wir eine Datenbank zur Verfügung haben, können wir auch beginnen Daten innerhalb von Tabellen abzulegen, hierfür jedoch müssen wir Tabellen erstellen. Für die Erstellung von Tabellen verwenden wir folgende Syntax: CREATE TABLE IF NOT EXIST tabellenname ( ID-Attribut INTEGER NOT NULL AUTO_INCREMENT, Attribut1 INTEGER NOT NULL, Attribut2 datentyp2 DEFAULT „bla“, Primary Key (ID-Attribut), Foreign Key (Attribut1) REFERENCES tabellenname (Attributname) ); In diesem Beispiel sieht man die Syntax zur Erstellung einer Tabelle mit einem Primary Key welches man meistens mit einem ID-Attribut benennt und im Normalfall immer ein Integer-Wert, welches nicht NULL sein kann und über ein Auto_Increment verfügt. Mit dem Zusatz = IF NOT EXISTS überprüfen wir ob die Tabelle mit dem Namen bereits Existent ist. Mit Not Null wird die Eingabe eines Wertes für das jeweilige Attribut erzwungen, da ein NULL-Wert verboten wird. Mit Auto_Increment wird der Wert des Attributs automatisch hochgezählt. Mit Default wird ein Standardwert definiert, sofern also bei der Eingabe eines Datensatzes dem Attribut bei der Eingabe kein Wert zugewiesen wird, so übernimmt dieses den Default-Wert. Ebenfalls Erstellen wir eine Verknüpfung zu einer anderen Tabelle mittels eines Foreign Keys dies machen wir in dem wir ein Attribut erstellen welches ebenfalls ein Integer-Wert ist welcher nicht NULL sein kann, hier benötigen wir kein Auto_Increment da es lediglich eine Referenz zu einem Primary Key einer anderen Tabelle ist und falls nötig dort bereits ein Auto_Increment implementiert ist. Modul105 Patrick Urfer 17.06.2015 Seite 7 von 19 Ausbildung Arbeitsbuch Informatik Übersicht Datentypen Da wir zum Erstellen der Tabelle den Attributen sogenannte Datentypen mitgeben müssen, erkläre ich nun welche Datentypen für welchen Nutzen gedacht sind. Text-Datentypen Sind wie der Name bereits sagt Datentypen zum Speichern von Textinformationen, bei diesen Datentypen wird zwischen fester und variabler Länge unterschieden. Der Unterschied bei fester und variabler Länge liegt in der Speicher-Art. Bei der festen Länge wird die definierte Länge des Textfeldes immer verwendet auch wenn der eigentliche Text weniger benötigen würde. Bei der variablen Länge wird von der definierten Länge nur das verwendet was der Text effektiv benötigt und nicht die komplett definierte Länge. CHAR (Längen-definition): Der Datentyp CHAR kann beliebige Textinformationen speichern, bei CHAR wird eine Länge zwischen 1 und 255 Zeichen definiert, was auch bedeutet das die Maximallänge der zu Speichernden Texte 255 Zeichen sein kann. CHAR benötigt exakt die definierte Länge an Speichergrösse in Byte pro Datensatz. VARCHAR (Längen-definition): Der Datentyp VARCHAR ist dem Datentyp CHAR sehr ähnlich bei beiden wird eine definierte Länge vorgegeben, jedoch ist ein Markanter Unterschied auszumachen. VARCHAR benötigt im Gegensatz zu CHAR immer 2 Byte für die definierte Länge + die effektiv verwendete Länge an Text an Speichergrösse. BLOB: Der Datentyp BLOB auch bekannt als Binary-Large-Object ist wie der Name sagt dafür gedacht grosse Objekte zu speichern, er wird vor allem fürs Speichern von sehr Grossen Textdateien, Videodateien, Grafiken oder Bildern verwendet. TEXT: Der Datentyp TEXT ist dem Datentyp BLOB sehr ähnlich auch hier werden grössere Informationsmengen mit variabler Länge gespeichert. Der Unterschied zu BLOB liegt in der verschiedenen Sortierreihenfolge, welche bei TEXT unabhängig von Gross- und Kleinschreibung ist. Datum-Datentypen Für Datums- und Zeitangaben gibt es einen speziellen Datentyp, im Datumstypwenn keine Angaben gemacht werden wird automatisch auch die Zeit gespeichert. Die zwei am häufigsten aufzutauchenden Datums-Datentypen sind: DATE: Mit DATE können Datumswerte zwischen 1.1.100 und 11.12.5941 mittels 8 Byte Speicherbedarf gespeichert werden. TIME: Mit TIME können Zeitwerte gespeichert werden. Da es bei jedem DBMS ein wenig anders ist, ist es am besten in der direkten Dokumentation nachzuschauen. Modul105 Patrick Urfer 17.06.2015 Seite 8 von 19 Ausbildung Arbeitsbuch Informatik Numerische Datentypen Für das Speichern von Zahlen stehen uns für allerlei Bedürfnisse verschiedene Numerische Datentypen zu Verfügung. SMALLINT: Mittels SMALLINT können Ganzzahlige Werte zwischen den Bereichen von Minus 32 768 bis Plus 32 767 gespeichert werden. SMALLINT hat einen Speicherbedarf von 2 Byte. INTEGER: Mit INTEGER können ebenfalls ganzzahlige Werte zwischen den Bereichen von Minus 2 147 483 648 bis Plus 2 147 483 647 gespeichert werden. Der INTEGER benötigt dafür lediglich 4 Byte Speicher. FLOAT: Mit FLOAT können Sie Zahlenwerte mit Nachkommastellen im Bereiche von 7 Stellen inklusiv Dezimalpunkt speichern. FLOAT benötigt dafür lediglich 4 Byte Speicher. DOUBLE PRECISION: Mit DOUBLE lassen sich wie mit FLOAT Zahlenwerte mit Nachkommastellen speichern, mit dem Unterschied das DOUBLE einen Bereich von 15 Stellen inklusive Dezimalpunkt bereitstellt. Der DOUBLE verwendet dadurch 8 Byte Speicher. NUMERIC: Mittels NUMERIC werden auch Zahlenwerte gespeichert, welche Ganzzahlen oder Zahlen mit Nachkommastellen sein können, um den Speicherbereich zu bestimmen für NUMERIC werden die Parameter(Stellenanzahl, Nachkommastellen)übergeben. Der Speicherverbrauch ist hier Variabel und Abhängig von übergebenen Parametern. DECIMAL: Mittels DECIMAL werden Zahlenwerte wie bei NUMERIC gespeichert jedoch mit dem Unterschied, das bei DECIMAL mit dem Parameter(Stellenanzahl) die Minimale Anzahl festlegt wird. CURRENCY/MONEY: Mittels CURRENCY/MONEY werden Währungsbeträge definiert jedoch ist dieser Datentyp in jedem DBMS verschieden daher gehe ich nicht genauer darauf ein, lesen sie hierfür die Dokumentation Ihres DBMS. Zusatz-Parameter NOT NULL: Mittels NOT NULL wird festgelegt das bei der Eingabe das Feld mit dem Parameter NOT NULL nicht leer gelassen werden kann und daher eine Eingabe erzwingt. DEFAULT: Mittels DEFAULT wird ein Standardwert für das Feld festgelegt sofern keine Eingabe erfolgt wird eben dieser DEFAULT-Wert verwendet anstelle von NULL. AUTO_INCREMENT: Mittel AUTO_INCREMENT wird eine sich automatisch Hochzählende Zahl eingesetzt, die von 1 an beginnt und bei jedem Eintrag +1 dazuzählt standardmässig. Dies kann natürlich mit Parametern verändert werden. Modul105 Patrick Urfer 17.06.2015 Seite 9 von 19 Ausbildung Arbeitsbuch Informatik Übersicht Schlüssel Primary Key Der Primary Key ermöglicht dass jeder Datensatz eindeutig Identifizierbar ist, normalerweise verfügt jede Tabelle über einen Primary Key. Definition: Ein Primary Key besteht normalerweise aus einem Attribut oder wird aus mehreren zusammengesetzt. Wenn mehrere Attribute als Primary Key definiert werden sollen so schreibt man die Attribute getrennt durch Kommas in die Klammern. Anwendungsbeispiel: Primary Key (Attributliste); Nachträglich definieren: Normalerweise sollte man den Primary Key beim Erstellen der Tabelle festlegen, jedoch kann auch jederzeit später das Erstellen vorgenommen werden. Anwendungsbeispiel: Alter Table tabellenname Add Primary Key (Liste der Attribute); Löschen: Ebenfalls ist es möglich in SQL bereits bestehende Primary Keys wieder zu löschen, um diese anders oder neu zu definieren. Anwendungsbeispiel: Alter Table tabellenname Drop Primary Key; Foreign Key In der Relationalen Datenbank werden Datenbestände meist auf mehrere Tabellen aufgeteilt um Redundanzen sowie Fehleingaben vermeidbar zu machen. Diese Tabellen sind in der Regel mittels eines Foreign Keys verbunden, welcher auf einen Primary Key einer anderen Tabelle zeigt. Definition: Ein Foreign Key bekommt ein Attribut oder mehrere Attribute als Parameter mitgegeben, welcher auf einen Primary Key einer anderen Tabelle zeigt. Anwendungsbeispiel: Foreign Key(Attributliste) References Tabellenname (Fremdschlüsselfeld) Nachträglich definieren: Hier ist es ebenfalls möglich in SQL nach bereits bestehender Tabelle einen Foreign Key zu definieren und hinzuzufügen. Anwendungsbeispiel: Alter Table tabellenname Add Constraint Keyname Foreign Key (Attribut) References Tabellenname(fremdschlüsselfeld) [ON Update Anweisung][ON Delete Anweisung]; Löschen: Auch hier ist es möglich in SQL bereits bestehende Foreign Key wieder zu löschen. Anwendungsbeispiel: Alter Table tabellenname Drop Foreign Key Keyname Modul105 Patrick Urfer 17.06.2015 Seite 10 von 19 Ausbildung Arbeitsbuch Informatik Zusatzparameter Für das Löschen und Aktualisieren eines Datensatzes auf den Referenzen in anderen Tabellen bestehen, kann man das Verhalten steuern mit den Parametern ON DELETE \ ON UPDATE das bedeutet dass bei einer Löschung oder einer Aktualisierung folgendes Verhalten eintritt. Welche Verhaltensparamter möglich sind schauen Sie am Besten in der Referenz Ihres DBMS nach. Übersicht Index Index Da das Auffinden von Informationen in einer stetig wachsenden Datenbank eine Schwierigkeit darstellen kann, gibt es für genau diesen Fall den Index, welcher wie ein Stichwortverzeichnis funktioniert in welchem der Computer das gesuchte ausliest. Erstellt wird er mit dem Befehl Create Index. Definition: Beim Erstellen des Index muss man einen Indexnamen angeben und nachdem Schlüsselwort ON werden der Tabellenname sowie in runden Klammern ein oder mehrere Attribute übergeben. Anwendungsbeispiel: Create Index indexname ON tabellenname(Attribut); Löschen: Man kann einen Index auch wieder Löschen sofern dieser nichtmehr Benötigt wird. Anwendungsbeispiel: Drop Index indexname ON Tabellenname; Übersicht Tabellenmanipulation Tabellen anzeigen Definition: Mit dem Anweisungswort SHOW können die vorhandenen Tabellen Ihrer Datenbank angezeigt werden. Anwendungsbeispiel: SHOW TABLES; Tabellenstruktur ändern Die Attribute einer Tabelle können jederzeit mit dem Befehl ALTER TABLE den eigenen Bedürfnissen angepasst werden. Definition: Mittels ALTER TABLE kann man einer bestehenden Tabelle neue Attribute hinzufügen oder bereits vorhandene Attribute löschen. Anwendungsbeispiel: ALTER TABLE tabellenname ADD/Drop attributname datentyp; Tabellen löschen Definition: Für das Löschen von kompletten Tabellen besitzen wir das Anweisungswort DROP, damit wird die Tabelle samt Inhalt ohne weitere Rückfragen komplett Gelöscht. Anwendungsbeispiel: DROP TABLE tabellenname; Modul105 Patrick Urfer 17.06.2015 Seite 11 von 19 Ausbildung Arbeitsbuch Informatik Rollen und Berechtigungen Das in Datenbanksystemen die vertraulichen Daten sicher geschützt sind, gibt es die Rollen und Berechtigungen in Datenbanksystemen welche als Schutzmassnahme getroffen werden kann. Datenbankadministrator Der Datenbankadministrator ist der Master einer Datenbank, welcher die Benutzerverwaltung übernimmt und die Rechte der Benutzer verwaltet, was auch bedeutet das der Datenbankadministrator sämtliche Rechte einer Datenbank besitzt. Benutzer Die Verwaltung der Benutzer von einem Datenbanksystems erfolgt grösstenteils über Spezielle Anwendungsprogramme, die Handhabung dieser Programme unterscheidet sich hauptsächlich zwischen den verschiedenen Datenbanksystemen. Ein Benutzer kann Zugriff auf alle oder einzelne ausgewählte Datenbanken bzw. Tabellen erhalten, auch gilt im Grunde das jeder Benutzer der ein Datenbankobjekt wie eine Tabelle erstellt, automatisch auch der Besitzer von diesem Objekt ist, alle anderen Benutzer bis auf den Datenbankadministrator besitzen für dieses Objekt keine Rechte ausser der Datenbankadministrator weist diese Rechte einem Benutzer zu. Administrator absichern Definition: Eine Möglichkeit das Passwort zu setzen besteht in der Nutzung des mysqladmin-Befehls, welcher die sicherste und einfachste Möglichkeit ist. Anwendungsbeispiel: mysqladmin –u root –p neues_password; Benutzer erstellen Definition: Damit nicht jedermann die Daten aus der Datenbank auslesen kann, können individuelle Benutzer mit unterschiedlichen Rechten eingerichtet werden. Anwendungsbeispiel: GRANT rechteliste ON datenbankobjekt TO benutzer@hostname IDENTIFIED BY passwort; (WITH GRANT OPTION); Modul105 Patrick Urfer 17.06.2015 Seite 12 von 19 Ausbildung Informatik Arbeitsbuch Erteilbare Rechte Rechteliste Erklärung ALL SELECT UPDATE DELETE REFERENCES ALTER DROP CREATE INDEX Alle Rechte für gewähltes Datenbankobjekt Leserecht, Berechtigung für Abfragen Recht zum Ändern von Datensätzen Recht zum Löschen von Datensätzen Rechte zum Definieren von Referenz-Regeln Manipulation von Tabellen und dessen Attribute Löschen von Datenbankobjekten(Tabellen, Schlüssel, Attribute) Recht, Datenbanken und Tabellen zu erstellen Das Recht Indexe auf Attribute in Tabellen zu erstellen Auswählbare Datenbankobjekte Datenbankobjekte Erklärung *.* Datenbank.* Datenbank.Tabelle Die Rechte gelten für alle Datenbanken und Tabellen Die Rechte gelten für alle Tabellen der Ausgewählten Datenbanken Die Rechte gelten für die angegebene Tabelle der definierten Datenbank Benutzeranmeldung durchführen Definition: Mit diesem Befehl können wir uns mit den Definierten Daten Anmelden, nur so können wir auf erteilte Rechte zugreifen. Anwendungsbeispiel: mysql –h(host) –u (user) –p (password) Zugriffsrechte ändern Definition: Mit dem Anweisungswort GRANT können wir jederzeit Benutzern neue Rechte zuteilen zu bestimmten Datenbankobjekten. Anwendungsbeispiel: GRANT rechteliste ON datenbankobjekt TO benutzername; Zugriffsrechte entziehen Definition: Rechte welche mit GRANT erteilt wurden können auch wieder mit REVOKE entzogen werden. Anwendungsbeispiel: REVOKE rechteliste ON datenbankobjekt FROM benutzer; Benutzer löschen Definition: Die Schnellste und Zuverlässigste Art einen Benutzer zu löschen ist mit dem Standardbefehl DROP. Anwendungsbeispiel: DROP USER benutzername; Modul105 Patrick Urfer 17.06.2015 Seite 13 von 19 Ausbildung Informatik Arbeitsbuch Daten erfassen Dass eine Datenbank überhaupt einen Verwertbaren Wert besitzt für die Betreiber sowie auch die Benutzer müssen in der Datenbank erstmals Daten erfasst werden mit denen gearbeitet werden kann. Einfügen von Datensätzen Definition: Um einen oder mehrere Einzelne Dateneinträge vorzunehmen haben wir den Befehl INSERT INTO. Anwendungsbeispiel: INSERT INTO tabellenname (Attribut1, Attribut2, …) VALUES (Wert1, Wert2, …); Einfügen Vorhandener Daten Definition: Oft hat man in der Realität bereits bestehende Daten/Datenbanken welche man weiter verarbeiten muss, für diese Möglichkeit gibt es Befehle um bereits vorhandene Daten komplett zu integrieren. Anwendungsbeispiel: LOAD DATA LOCAL INFILE [pfadangabe] INTO TABLE [tabellenname] FIELDS TERMINATED BY “ ; “ “, “ IGNORE 1ROWS (VALUES COLUMNNAME) (LINES TERMINATED BY “\n“); Daten manipulieren Daten aktualisieren Definition: Mit SQL kann man die eigenen Daten jederzeit ändern, dafür steht uns der Befehl UPDATE zur Verwendung, mit diesem man einzelne Datensätze oder vollständige Inhalte von Tabellen ändern kann. Anwendungsbeispiel: UPDATE tabellenname SET Attribut1 =wert1, Attribut2 = wert2 (WHERE bedingung); Änderungsmöglichkeiten Wertzuweisung Beschreibung SET Anrede = “Herr“ SET Anzahl = 2 SET Anzahl = Anzahl * 2 Mit dieser Anweisung wird eine Textinformation geändert. Mit dieser Anweisung wird ein Zahlenwert geändert. Mit Mathematischer Funktion Zahlenwert änderung. Daten löschen Definition: Mit SQL kann man die bestehenden Datensätze in einer Tabelle auch zu jederzeit wieder löschen. Anwendungsbeispiel: DELETE FROM tabellenname (WHERE Bedingung); Modul105 Patrick Urfer 17.06.2015 Seite 14 von 19 Ausbildung Informatik Arbeitsbuch Daten Auswerten Attribute anzeigen Definition: Mit SQL kann man jeweils bestimmte/alle Attribute einer Tabelle abfragen und anzeigen lassen. Anwendungsbeispiel: SELECT Attribut1, Attribut2… FROM tabellenname; Zusatzparameter AS: Mittels des Zusatzparameters AS nach dem jeweiligen Attribut kann man der Ausgabe einen neuen Namen zuweisen. Anwendungsbeispiel: SELECT Attribut1 AS ausgabename1, Attribut2… FROM tabellenname; Definierte Bedingungen In SQL kann man die Ausgabe der Abfrage jeweils mit Bedingungen und Bedingungsparametern manipulieren und an die Anforderungen anpassen. Einfache Bedingung Definition: Mittels der Einfachen Bedingung kann man Daten in einem Abfrageergebnis filtern, dies geschieht mittels der WHERE-Bedingung. Anwendungsbeispiel: SELECT Attribut1 AS ausgabename1, Attribut2… FROM tabellenname WHERE bedingungen; (z.B. Bedingung = gesuchter wert) Im Anwendungsbeispiel der Einfachen Bedingung oberhalb in der Klammer sehen wir eine Bedingungsabfrage mittels dem = Operator, solche Anwendung von Operatoren ist ein Wesentlicher Teil von SQL und welche Operatoren welche Funktion besitzen werden wir nun anschauen. Operatoren Basis-Operatoren Bei diesen Operatoren werden jeweils die Datensätze mit einem Gewählten Wert verglichen und gibt nur diese Datensätze aus welche Zutreffend sind. Operatoren = < > <> >= <= Modul105 Bedeutung Gibt nur Übereinstimmende Datensätze aus Gibt nur Datensätze aus welche kleiner sind. Gibt nur Datensätze aus welche grösser sind. Gibt nur Datensätze aus welche Ungleich dem Wert sind. Gibt nur Datensätze aus welche grösser oder gleich sind. Gibt nur Datensätze aus welche kleiner oder gleich sind. Patrick Urfer 17.06.2015 Seite 15 von 19 Ausbildung Arbeitsbuch Informatik SQL-Operatoren Bei diesen Operatoren gibt es ganz verschiedene Anwendungsmöglichkeiten weswegen wir diese etwas genauer anschauen. BEETWEEN.. AND.. Mit BEETWEEN.. AND.. kann der Wert geprüft werden ob dieser im Festgelegten Wertebereich liegt. Anwendungsbeispiel: SELECT Attribut1, Attribut2… FROM tabellenname WHERE Attribut BEETWEEN Wert1 AND Wert2 LIKE Mit LIKE welcher Speziell für Text-Werte gemacht ist, wird ein flexibler Vergleich zwischen dem Wert und dem Text ermöglicht. LIKE kann mit 1 von 2 Platzhaltern verwendet werden welche die Suche weiter definieren. Der _ Platzhalter steht für ein beliebiges Zeichen. Der % Platzhalter steht für ein, kein oder mehrere beliebige Zeichen. Anwendungsbeispiel: SELECT Attribut1, Attribut2… FROM tabellenname WHERE Attribut LIKE Wort1(LIKE W%)( LIKE W_rt1) NOT Mit NOT können Werte welche nicht gewollt sind aus der Abfrage entfernt werden, mit NOT lassen sich ebenfalls weitere Operatoren bilden wie z.B. NOT LIKE, NOT BEETWEEN.. AND.. Anwendungsbeispiel: SELECT Attribut1 FROM tabellenname WHERE Attribut1 NOT BEETWEEN Wert1 AND Wert2; IS NULL Mit IS NULL kann nach leeren Feldinhalten gesucht werden, sprich Felder welche keine Inhalte besitzen. Anwendungsbeispiel: SELECT Attribut1 FROM tabellenname WHERE Attribut1 IS NULL IN Mit IN können mehrere Werte definiert werden, auf welche geprüft wird. Mit IN können auch Unterabfragen mittels weiterem SELECT-Befehl innerhalb der IN-Bedingung gemacht werden, dafür ein Anwendungsbeispiel. Anwendungsbeispiel: SELECT Attribut1, Attribut2 FROM tabellenname1 WHERE Attribut3 IN (SELECT Attribut3 FROM tabellenname2 WHERE Attribut4 = Wert1); Für weitere Anleitungen zur Verwendung von den Operatoren an sich verweise ich auf http://www.w3schools.com/sql/ Modul105 Patrick Urfer 17.06.2015 Seite 16 von 19 Ausbildung Arbeitsbuch Informatik Logische Verknüpfungen Die Bedingungen bzw. Operatoren sind mittels Logischen Verknüpfungen in Abfragen kombinierbar, für diesen Zweck haben wir zwei unersetzliche Verknüpfungen zur Verfügung. AND Mit AND können mehrere Kriterien als gemeinsame Voraussetzung kombiniert werden, die dadurch entstandene AND-Bedingung setzt voraus das beide Bedingungen erfüllt sind. Anwendungsbeispiel: SELECT Attribut1, Attribut2 FROM tabellenname WHERE Attribut1 IS NULL AND Attribut2 NOT LIKE Wort1; OR Mit OR können mehrere Kriterien miteinander kombiniert werden, die OR-Bedingung setzt voraus dass eine der Bedingungen erfüllt sein muss. Anwendungsbeispiel: SELECT Attribut1, Attribut2 FROM tabellenname WHERE Attribut1 IS NULL OR Attribut2 LIKE Wort1; Erweitertes definieren Ordnen Definition: Mit dem Select-Statement wird bei der Anzeige keine Bestimmte Reihenfolge definiert in der die Datensätze angezeigt werden, meistens wird die Anzeige-Reihenfolge bestimmt durch die Reihenfolge der Eingabe. Um dieses Problem zu bewältigen haben wir das ORDER BY-Statement. Anwendungsbeispiel: SELECT Attribut1, Attribut2 FROM tabellenname ORDER BY Attribut1; Gruppieren Definition: Mit dem Select-Statement wird bei der Anzeige keine Bestimmte Gruppierung definiert in der die Datensätze angezeigt werden, mit GROUP BY lässt sich dieses Problem lösen. Anwendungsbeispiel: SELECT Attribut1, Attribut2 FROM tabellenname GROUP BY Attribut1; Doppelte Datensätze eliminieren Definition: In einer Tabelle ist der Wert1 doppelt erfasst, daher würde beim Erstellen einer Ausgabe der Wert1 doppelt ausgegeben werden. Dies lässt sich mit DISTINCT verhindern da es Doppelte Werte eliminiert und jeden Wert nur einmal anzeigen lässt.. Anwendungsbeispiel: SELECT DISTINCT Attribut1, Attribut2 FROM tabellenname; Modul105 Patrick Urfer 17.06.2015 Seite 17 von 19 Ausbildung Arbeitsbuch Informatik Aggregatsfunktionen Aggragatsfunktionen fassen Daten aus mehreren Datensätzen zu einem Ergebniswert zusammen, um dies Umzusetzen unterstützt SQL mit Mathematischen Funktionen welche in der Fachsprache auch als Aggregatsfunktionen bekannt sind. SUM Mit dieser Funktion dem SUM kann man die Summe von numerischen Attributen berechnen, sprich Addition ausführen. Anwendungsbeispiel: SELECT SUM(Attribut1) FROM tabellenname; AVG Mit dieser Funktion dem AVG kann man den Durchschnitt von numerischen Attributen berechnen. Anwendungsbeispiel: SELECT AVG (Attribut1) FROM tabellenname; COUNT Mit dieser Funktion dem COUNT kann man die Anzahl der vorhandenen Datensätze Abfragen. Anwendungsbeispiel: SELECT COUNT (Attribut1) FROM tabellenname; MAX Mit dieser Funktion dem MAX kann man den grössten Wert eines Attributes Abfragen. Anwendungsbeispiel: SELECT MAX (Attribut1) FROM tabellenname; MIN Mit dieser Funktion dem MIN kann man den kleinsten Wert eines Attributes Abfragen. Anwendungsbeispiel: SELECT MIN (Attribut1) FROM tabellenname; Multi-Tabellen Hier lernen Sie wie Abfragen und Verbindungen von 1er oder über mehrere Tabellen funktioniert. Zwingend Notwendig für das Verständnis sind die Vorangegangenen Punkte sofern Sie kein eigenes Vorwissen besessen haben. UNION-Abfrage Definition: Mit dieser Funktion kann man Attribute aus zwei verschiedenen Tabellen als ein Abfrageresultat ausgeben. Anwendungsbeispiel: SELECT Attribut1, Attribut2… FROM tabellenname1; UNION SELECT Attribut1, Attribut2… FROM tabellenname2; Bei einer UNION-Abfrage werden alle Datensätze miteinander verglichen und jene welche die komplett Identisch sind miteinander vereinigt. Sind jedoch minimale Unterschiede zwischen den Datensätzen zu erkennen so gelten beide als unterschiedliche Datensätze. Modul105 Patrick Urfer 17.06.2015 Seite 18 von 19 Ausbildung Arbeitsbuch Informatik UNION-Tabelle Definition: Mit dieser UNION-Abfrageart können wir das Resultat des herkömmlichen UNIONS in einer neuen Tabelle abspeichern. Anwendungsbeispiel: INSERT INTO tabellenname (Attribut1, Attribut2…) SELECT Attribut1, Attribut2… FROM tabellenname1; UNION SELECT Attribut1, Attribut2… FROM tabellenname2; Verbinden von Tabellen Im Datenbankenbereich ist es oft Notwendig bestehende Datenbanken für bestimmte Abfragen zu verbinden um damit die Gestellten Aufgaben zu bewältigen. Einfache Verknüpfungen Um einfache Verknüpfungen zu realisieren stehen uns mehrere Wege zur Verfügung, ich werde bewusst auf 2 Wege eingehen, welche sich als Standard etabliert haben. Definition: Mit dieser WHERE-Bedingung aufgebauten Verbindung erzielt man schnellere Verarbeitungszeiten, daher wird Sie in der Praxis häufig benutzt. Anwendungsbeispiel: Select tabellenname1.Attribut1, tabellenname2.Attribut1 from tabellenname1, tabellenname2 Where tabellenname1.pkIDAttribut = tabellenname2.fkIDAttribut; Definition: Mit dieser JOIN-Bedingung aufgebauten Verbindung erzielt man dasselbe Resultat wie mit der WHERE-Bedingung aufgebauten Verbindung. Anwendungsbeispiel: Select tabellenname1.Attribut1, tabellenname2.Attribut1 from tabellenname1 JOIN tabellenname2 ON tabellenname1.pkIDAttribut = tabellenname2.fkIDAttribut; Zusatz: Mit dem Keyword AS können wir den Tabellen selbstdefinierte Namen verleihen, was uns beim Verbinden der Tabellen die Arbeit erleichtert. Anwendungsbeispiel: Select tabellenname1.Attribut1, tabellenname2.Attribut1 from tabellenname1 AS a, tabellenname2 AS b Where a.pkIDAttribut = b.fkIDAttribut; Das LEFT oder RIGHT bezieht sich auf die Nennung der Tabelle im JOIN, dabei wird die Erstgenannte Tabelle als Linke und die Zweitgenannte als Rechte angesehen. Mittels den Erweiterten JOIN-Befehlen dem LEFT-JOIN und RIGHT-JOIN können wir für eine Bestimmte der 2 zu Verbindenden Tabellen innerhalb des JOINS bestimmen das von der gewählten Tabelle auch alle Gesuchten Datensätze ohne Übereinstimmung mit der anderen Tabelle gelistet werden, diese Werte ohne Übereinstimmung werden mit dem Wert NULL gekennzeichnet. Alle Restlichen Kombinations-Möglichkeiten des SQL’s lesen Sie bitte Online nach. Modul105 Patrick Urfer 17.06.2015 Seite 19 von 19
