Gewerbliche Schulen der Stadt Essen

INFORMATIONSUNTERLAGEN
zu
„Grundzüge der SQL-Programmierung“
Vag 09/2005
1.
SQL
1.1. Einführung
Die Sprache SQL (structured query language) wird als einer der Hauptgründe für den
kommerziellen Erfolg von relationalen Datenbanken in der Geschäftswelt gesehen. Die
ANSI (American National Standards Institute) und die ISO (International Standards Organization) entwickelten eine Standard Version von SQL (ANSI 1986) mit dem Namen SQL86 oder SQL1. Seit den 90er Jahren wird der erweiterte Standard SQL2 oder SQL-92 angewendet. Durch den Einsatz von SQL in kommerziellen DBMS-Produkten wurde die Migration zwischen einzelnen DBMS-Produkten, die den Standard implementiert haben, für
den Endbenutzer vereinfacht. Der Benutzer von relationalen Datenbanken muss sich im
Idealfall keine Gedanken mehr machen, mit welchem DBMS-Produkt er momentan arbeitet, denn die Schnittstelle (SQL) zu den einzelnen Systemen bleibt für ihn immer die gleiche.
Leider ist diese Vorstellung nur begrenzt gültig, da jedes DBMS eigene Sprachdialekte
implementiert hat. Zwar gelten viele syntaktische Regeln bei allen Systemen allerdings
kommt der Programmierer nicht an der Tatsache vorbei, sich bei dem verwendeten System über die Besonderheiten und einen ggf. erweiterten Sprachumfang zu informieren.
(Bsp. PL-SQL der Fa. Oracle, „MS-Access SQL)
In dem vorliegenden Informationsmaterial wird auf diese Besonderheiten verzichtet. Vielmehr soll durch die allgemeine Struktur der Leser ermuntert werden, das eine oder andere
in der Referenz zu dem jeweiligen System nachzuschlagen.
Vag
2
SQL_Grundlagen.doc
2.
Programmierung eines Datenbanksystems mit SQL
2.1. Grundlagen zu SQL
Unter der Programmierung einer Datenbank versteht man üblicherweise die Umsetzung
des Datenmodells in eine entsprechende Tabellenstruktur innerhalb eines Datenbanksystems. Dazu muss zunächst eine Datenbank im System generiert werden. Anschließend
werden dann der Datenbank die einzelnen Tabellen hinzugefügt. Die Programmiersprache, die ein relationales Datenbankmanagementsystem versteht nennt sich SQL (Structured Query Language). MySQL lehnt sich an den Standard ANSI SQL 92 an.
Die Sprache SQL gliedert sich gemäß Bild 5 in die Bereich DDL = Data Definition Language, DML = Data Manipulation Language und DCL = Data Control Language. Den drei
Sprachbereichen sind jeweils verschiedene Statements zugeordnet, die entsprechend
dem zugehörigen Sprachbereich die Verwaltung eines Datenbanksystems via SQL ermöglichen.
SQL
DDL
DML
DCL
Anlegen, Verändern und Löschen
von Datenbanken,
Tabellen
Anlegen, Verändern und Auswählen von Datensätzen in Tabellen
Verwaltung der
Zugangskontrolle
auf Datenbanken
und Tabellen
Create Database
Insert ...
Grant ...
Create Table
Update ...
Revoke ...
Drop Database
Delete ...
Lock / Unlock
Drop Table
Select ...
Alter Table
Bild 5: Die Struktur der Datenbanksprache SQL
Vag
3
SQL_Grundlagen.doc
In den nun folgenden Abschnitten werden die in Bild 5 grau hinterlegten Statements mit
ihrer Grundsyntax dargestellt. Eine Darstellung bis ins Detail soll in diesem Kompendium
nicht erfolgen. Vielmehr sei hier auf die Literatur bzw. auf einschlägige Internetseiten verwiesen.
http://cheese.geo.unizh.ch:9000/student/all/de/basic/RelQueryLang/BasicQueries
2.2. Anlegen, Löschen und Ändern von Datenbanken und Tabellen (Create .., Alter ..
Drop ...)
Die einzelnen Befehlsnamen sprechen im Grunde genommen für sich. Daher wird die
Syntax im folgenden überwiegend kommentarlos aufgeführt:
CREATE DATABASE datenbankname;
Es wird eine Datenbank mit dem Namen „datenbankname“ angelegt. Es ist zu beachten, dass MySQL Groß-/Kleinschreibung unterscheidet!!
DROP DATABASE datenbankname;
Die angegebene Datenbank wird unwiederbringlich gelöscht!
CREATE TABLE tablename (Attribut1 Datentyp [NOT NULL] [PRIMARY KEY]
[AUTO_INCREMENT], Attribut 2 Datentyp [Modifikatoren] , …);
Das Create Table Statement wird für das Anlegen von Tabellen benötigt. Dazu müssen
einerseits sämtliche Attribute einer Tabelle angegeben werden und andererseits die Datentypen deklariert werden. Eine Auflistung der gängigen Datentypen befindet sich am
Ende des Umdrucks,
Zusätzlich können zu den Attributen sogenannte Modifikatoren angegeben werden.
Diese sind optional und haben folgende Bedeutung:
NOT NULL: Der Wert des Attributs darf nicht leer sein!
PRIMARY KEY: Bei dem Attribut handelt es sich um den Primärschlüssel oder
einen Teil davon.
AUTO_INCREMENT: Der Wert des Attributs wird automatisch beim Anlegen eines
neuen Datensatzes inkrementiert. Es sind nur ganzzahlige
Datentypen zulässig.
Hinweis: Sollte der Primärschlüssel aus mehreren Attributen zusammengesetzt werden,
so gilt folgende Syntax:
Vag
4
SQL_Grundlagen.doc
CREATE TABLE Bestelldetails (
Artikelnr INT( 10 ) NOT NULL ,
Bestellungnr INT( 10 ) NOT NULL ,
Datum DATE NOT NULL ,
Kommentar VARCHAR( 50 ) NOT NULL,
PRIMARY KEY(Artikelnr, Bestellungnr)
);
DROP TABLE tabellenname;
Die angegebene Tabelle wird gelöscht.
ALTER TABLE tabellenname MODIFY meinespalte neuertyp;
In der Tabelle tabellenname wird der Datentyp der Spalte meinespalte in „neuertyp“
umgewandelt. Das Alter table Statement hat eine Vielzahl von Parametern, die hier
nicht näher erläutert werden. Bei Bedarf können diese nachgeschlagen werdn.
2.3. Einfügen, Ändern und Löschen von Daten in Tabellen (Insert ..., Update ..., Delete ... )
INSERT INTO tabellenname (attribut1, attribut2 ....) VALUES (wert1, wert2, ....);
Bei der Ausführung des Statements werden den Attributen der gewählten Tabelle die
Werte entsprechend der Liste zugewiesen.
Folgendes ist bei der Angabe der Werte zu beachten:
• Handelt es sich um Zahlen, wird die übliche Darstellung gewählt
• Sollen Zeichnketten eingegeben werden, so müssen diese in ″ ″ geschrieben
werden.
• Hat ein Attribut den Modifikator Auto_Increment erhalten so wird dieses Attribut
sowohl in der Attributliste als auch in der Werteliste weggelassen.
• Soll ein Passwort eingegeben werden so ist der Wert des Attributes miit der
Funktion PASSWORD(zeichenkette) zu übergeben.
• Der Tabellenname ist grndsätzlich in Hochkommas einzuschließen.
´tabellenname´
UPDATE tabellenname SET attribut1=wert1, attribut2=wert2 … [where-Klausel];
Das Update-Statement ist notwendig, um Attributwerte innerhalb einer Tabelle zu
verändern. Die angefügte Where-Klausel erlaubt dann die Auswahl des jeweiligen
Datensatzes. Die Syntax der Where-Klausel entspricht der beim Select Statement
und kann in dem zugehörigen Abschnitt 2.4 nachgelesen werden.
DELETE FROM tabellenname [where-Klausel];
Es werden entweder alle Datensätze in der Tabelle gelöscht oder nur diejenigen, die
durch die where-Klausel markiert werden.
2.4. Abfragen auf Tabellen (Select ...)
Vag
5
SQL_Grundlagen.doc
Abfragen auf Tabellen werden mit dem Schlüsselwort Select eingeleitet. Grundsätzlich
ergibt sich folgende Syntax für das Select – Statement:
SELECT [DISTINCT | ALL] <Liste der Spalten, Funktionen, Konstanten etc> FROM tabellenname
WHERE Bedingungen
GROUP BY Gruppierte Spalten
HAVING Bedingung
ORDER BY <zu sortierende Spalten> [ASC|DESC]
;
Im folgenden werden die einzelnen Ausdrücke und Optionen näher betrachtet:
• Distinct | ALL: Distinct bewirkt, dass Dopplungen im Ergebnis einer Abfrage unterdrückt werden. z.B. Eine Abfrage ermittelt die Postleitzahlen der gespeicherten Kunden. Zwangsläufig kommt es zu Dopplungen. Diese werden mit Distinct unterdrückt.
ALL hat denselben Effekt wie das Fehlen eines der beiden Ausdrücke.
• Liste der Spalten ...: Im einfachsten Fall werden hier durch Kommas abgetrennt die
Spaltenname der Tabelle aufgeführt, die im Rahmen der Abfrage interessieren. ZUsätzlich können hier sogenannte Aggregatfunktionen eingesetzt werden z.B. zur Min
Max Bestimmung oder zu Berechnungen wie Summenbildung etc..
Î min Bestimmen des minimalen Werts eines Attributs
Î max Bestimmen des maximalen Werts eines Attributs
Î sum Berechnen der Summe aller Werte eines Attributs
Î count Anzahl der Werte eines Attributs (nicht der verschiedenen Werte!)
Î avg Berechnen des Durchschnitts aller Werte einen Attributs, wobei NULLWertenicht in die Berechnung einfliessen
•
•
tabellennamen gibt hier an, welche Tabelle die Grundlage für die Abfrage ist. Sind
mehrere Tabellen beteiligt, müssen diese über sogenannte JOINS verknüpft werden.
WHERE Bedingungen: In der Where-Klausel können z.B. mathematische Ausdrücke
eingesetzt werden, um die Anzahl der Ergebnisdatensätze einzuschränken. SQL unterstützt eine Reihe von Vergleichs- und Mengenoperatoren. Vergleichsoperatoren wie
=,<,>,<>,<=,>=,between.
Bsp: Anzeige aller Datensätze, in denen die Postleitzahl zwischen 45000 und
45277angesiedelt ist: ... where PLZ >= 45000 AND PLZ <=45277 ...
Zusätzlich kann für die Suche in Zeichenketten der Ausdruck RegEXP (Regular Expession) verwendet werden: where Name Regexp Mack%
Hiermit werden alle Namen selektiert, die mit Mack beginnen und mit beliebig vielen
Zeichen fortgesetzt werden. (z.B. Macke, Mackielski usw.)
•
GROUP BY Gruppierte Spalten: Die Gruppierung von Spalten hat nur beim Einsatz
der o.g. Aggregatfunktionen eine Bedeutung
•
HAVING Bedingung: Having wird wie die Where Klausel bei der Verwendung von
Aggregatfunktionen verwendet, um das Berechnungsergebnis ebenfalls als Filterkriterium einzusetzen.
Vag
6
SQL_Grundlagen.doc
•
ORDER BY <zu sortierende Spalten> [ASC|DESC]: Soll das Ergebnis einer Abfrage
sortiert erscheinen, so lässt sich dies mit der Order By Klausel erreichen. Dabei wird
angegeben, welche Spalte sortiert erscheinen soll und in welcher Reihenfolge, ASC=
Aufsteigend und DESC=Absteigend.
Beispiele:
a)
Select * from Kunde;
b)
Select Name, Vorname From Kunde
c)
Select Vorname FROM Kunde
Where Name=”Müller”
Select Vorname FROM Kunde
Where Name=”Müller”
Order By Vorname ASC
d)
e)
e)
Vag
SELECT *
FROM `bestellungen`
WHERE Versanddatum
BETWEEN '1996/01/01'
AND '1996/08/31'
SELECT Kategorienr, Sum( Einzelpreis )
FROM artikel
GROUP BY Kategorienr
7
Es wird die gesamte Kundentabelle zurückgegeben
Es werden alle Name und Vorname zurückgegeben
Es werden die Vorname aller Kunden mit
dem Namen Müller ausgegeben
Es werden die Vorname aller Kunden mit
dem Namen Müller ausgegeben, die Vorname sind alphabetisch aufsteigend sortiert.
Ausgabe aller Bestelldaten, deren Versand zwischen dem 01.01.1996 und dem
31.08.1996 stattgefunden hat. Das Format des Datums ist in der Referenzliste
nachzulesen.
Berechnung der Summe der Einzelpreise
in den Artikelkategorien und Ausgabe der
Summenwerte je Kategorie.
SQL_Grundlagen.doc
Numerische Werte
Typname
TINYINT
sehr kleine ganze Zahl
SMALLINT
kleine ganze Zahl
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL(M,D)
mittelgroße ganze Zahl
Standard
groß
Fließkommazahl, einfache Genauigkeit
Fließkommazahl, doppelte Genauigkeit
als Zeichenkette dargestellte Fließkommazahl
DECIMAL(4,1)
DECIMAL(6,2)
Bedeutung
Bereich
-128 bis 127 oder
0 bis 255 (UNSIGNED)
-32768 bis 32767 oder
0 - 535
-223 - 223 - 1 (0 - 224)
-231- 231- 1
-263 - 263 - 1
rund -10-38 - 238
rund -10-308 - 2308
hängt von der Anzeigegröße M und den Dezimalstellen D ab
-999.9 bis 9999.9
-9999.99 bis 99999.99
Speicherbedarf
1 Byte
2 Byte
3 Byte
4 Byte
8 Byte
4 Byte
8 Byte
(M+2) Byte
Alle Integer-Typen können UNSIGNED deklariert werden, so dass keine negativen Zahlen möglich sind.
Der Wertebereich beginnt dann bei 0, bleibt aber insgesamt gleich groß (d.h. größere positive Zahlen möglich).
Für numerische Spalten kann mit AUTO_INCREMENT ein eindeutiger Eintrag erzwungen werden (eine Spalte pro Tabelle)
Va
16
SQL_Grundlagen.doc
Zeichenkettenwerte (String, Character)
Typname
Bedeutung
maximale Größe
Zeichenkette fester Länge
Zeichenkette variabler Länge
sehr kleines Binary Large Object (Bild, Töne)
kleines BLOB
mittleres BLOB
großes BLOB
sehr kleine Textzeichenkette
kleine Textzeichenkette
mittelgroße Textzeichenkette
große Textzeichenkette
Auflistung, Auswahl nur eines Feldes möglich
Menge, Mehrfachauswahl möglich, auch nichts
CHAR(M)
VARCHAR(M)
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET
M Byte
M Byte
(28 -1) Byte
(216 -1) Byte
(224 -1) Byte
(232 -1) Byte
(28 -1) Byte
(216 -1) Byte
(224 -1) Byte
(232 -1) Byte
65535 Elemente
64 Elemente
Mit Hilfe des Attributs DEFAULT kann ein Standardwert vorgegeben werden (außer für BLOB und TEXT).
Datums- und Zeitwerte
Typname
DATE
TIME
DATETIME
TIMESTAMP
YEAR
Va
Bedeutung
Datum im Format: JJJJMM-TT
Zeitwert im Format :
hh:mm:ss
Datum / Zeitwert
Zeitstempelwert im Format:
JJJJMMDDhhmmss
Jahreswert im Format JJJJ
Bereich
Speicherbedarf
1.1.1000 bis 31.12.9999
3 Byte
abgelaufene Zeit:
-838h 59min 59s bis 838h 59min 59s
1.1.1000 0Uhr bis 31.12.9999 23:59:59
1970.... bis 2037....
3 Byte
1901 bis 2155
1 Byte
17
8 Byte
4 Byte
SQL_Grundlagen.doc