Diverse weitere Notizen zum SQL -Kurs
Quellen
Literatur: MySQL manual, Visual Quickstart
Gesamtgehaltsaufwand
PERSON
+------+--------+--------+--------+
| MF | NAME | SALARY | DEPT |
+------+--------+--------+--------+
| m | John | 1000 | pers |
| m | Marion | 2000 | pers |
| f | Mary | 3900 | pers |
| m | Frank | 3500 | sales |
| f | Susan | 5000 | sales |
| m | Martin | 4500 | sales |
| m | Otto | 1500 | dev |
| f | June | 6500 | dev |
+------+--------+--------+--------+
Gesamtgehaltsaufwand pro Abteilung und Geschlecht.
SELECT
+------+------+-------+
| MF | AUFW | DEPT |
+------+------+-------+
| m | 3000 | pers |
| f | 3900 | pers |
| m | 8000 | sales |
| f | 5000 | sales |
| m | 1500 | dev |
| f | 6500 | dev |
+------+------+-------+
Basislösung (ohne die angepaßte Sortierung):
SELECT ##############################################;
Umwandeln von Spaltenwerten in Spaltentitel (Pivot-Tabellen)
Wegen Kritik an Enums mit Alternative zu Enums:
WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;DROP TABLE IF EXISTS NAME;
CREATE TABLE NAME ( NAME VARCHAR ( 255 ) PRIMARY KEY );INSERT INTO NAME ( NAME ) VALUES
( 'Kai' ), ( 'Marion' ), ( 'Angela' ), ( 'Frank' ),
( 'Nicole' ), ( 'Martin' ), ( 'Otto' ), ( 'Claudia' );DROP TABLE IF EXISTS GESCHLECHT;
CREATE TABLE GESCHLECHT ( GESCHLECHT CHAR ( 1 ) PRIMARY KEY );INSERT INTO GESCHLECHT ( GESCHLECHT ) VALUES ( 'm' ), ( 'w' );
DROP TABLE IF EXISTS ABTEILUNG;
CREATE TABLE ABTEILUNG ( ABTEILUNG VARCHAR ( 255 ) PRIMARY KEY );INSERT INTO ABTEILUNG ( ABTEILUNG ) VALUES
( 'Vertrieb' ), ( 'Personal' ), ( 'Entwicklung' );DROP TABLE IF EXISTS PERSON;
CREATE TABLE PERSON
( NAME VARCHAR ( 255 ),
FOREIGN KEY ( NAME ) REFERENCES NAME ( NAME ),GESCHLECHT CHAR ( 1 ),
FOREIGN KEY ( GESCHLECHT ) REFERENCES GESCHLECHT ( GESCHLECHT ),ABTEILUNG VARCHAR ( 255 ),
FOREIGN KEY ( ABTEILUNG ) REFERENCES ABTEILUNG ( ABTEILUNG ),GEHALT DECIMAL ( 12, 2 ));
INSERT INTO
PERSON( NAME, GESCHLECHT, ABTEILUNG, GEHALT ) VALUES
( 'Kai', 'm', 'Personal', 51000.00 ),
( 'Marion', 'w', 'Personal', 66000.00 ),
( 'Angela', 'w', 'Personal', 57000.00 ),
( 'Frank', 'm', 'Vertrieb', 45000.00 ),
( 'Nicole', 'w', 'Vertrieb', 50000.00 ),
( 'Martin', 'm', 'Vertrieb', 65000.00 ),
( 'Otto', 'm', 'Entwicklung', 55000.00 ),
( 'Claudia', 'w', 'Entwicklung', 49000.00 );
Gesucht ist der Gehaltsaufwand jeder Abteilung pro Geschlecht sowie der gesamte Gehaltsaufwand jeder Abteilung.
SELECT ABTEILUNG, GESCHLECHT, SUM( GEHALT )
FROM PERSON
GROUP BY ABTEILUNG, GESCHLECHT;+-------------+------------+---------------+
| ABTEILUNG | GESCHLECHT | SUM( GEHALT ) |
+-------------+------------+---------------+
| Entwicklung | m | 55000.00 |
| Entwicklung | w | 49000.00 |
| Personal | m | 51000.00 |
| Personal | w | 123000.00 |
| Vertrieb | m | 110000.00 |
| Vertrieb | w | 50000.00 |
+-------------+------------+---------------+
»SUM« ist eine Statistikfunktion, welche die Summe der Gehälter pro Abteilung und Geschlecht berechnet. Die Spalten, deren Werte zusammengefaßt werden sollen, gibt man hinter »GROUP BY«. Durch die Zusammenfassung mehrerer Zeilen entsteht jeweils eine neue Zeile. Da in der neuen Zeile pro Zelle nur genau ein Wert vorkommen darf, kann man dort nur Spalten verwenden, die entwender mit »GROUP BY« zu einem Wert zusammengefaßt wurde, weil sie ohnehin alle denselben Wert enthalten, oder Spalten, aus denen mit einer Statistikfunktion, wie »SUM«, ein einzelner Wert gemacht wurde. (Eine Statistikfunktion ist eine Funktion, die aus einer Liste mehrere Werte beliebiger Anzahl genau einen einzigen Wert berechnet, wie beispielsweise die Summe oder der Mittelwert.) Die Spalte »NAME« hat weder denselben Wert pro Abteilung und Geschlecht, noch ist sie das Ergebnis einer Statistikfunktion. Deswegen darf sie oben nicht hinter »SELECT« verwendet werden. (MySQL toleriert dies aber trotzdem und wählt dann einfach willkürlich einen Namen aus, was in der Regeln aber nicht besonders hilfreich ist.)
SELECT ABTEILUNG, SUM( GEHALT )
FROM PERSON
GROUP BY ABTEILUNG;+-------------+---------------+
| ABTEILUNG | SUM( GEHALT ) |
+-------------+---------------+
| Entwicklung | 104000.00 |
| Personal | 174000.00 |
| Vertrieb | 160000.00 |
+-------------+---------------+
Die gesuchten Informationen lassen sich alle durch die voranstehenden beiden einfachen Abfragen ermitteln.
Gewünscht war jedoch eine Darstellung in dem folgenden Format.
+-------------+-----------+-----------+-----------+
| ABTEILUNG | M | W | GESAMT |
+-------------+-----------+-----------+-----------+
| Entwicklung | 55000.00 | 49000.00 | 104000.00 |
| Personal | 51000.00 | 123000.00 | 174000.00 |
| Vertrieb | 110000.00 | 50000.00 | 160000.00 |
+-------------+-----------+-----------+-----------+
Die Darstellung von Informationen in einer bestimmten Formatierung gehört nicht zum Kern der Arbeit eines Datenbanksystems. Dies ist eine „Kosmetik“, die manchmal auch mit prozeduralen Programmiersprachen erledigt wird, welche auf die Datenbank zugreifen und die Ergebnisse zur Ausgabe aufbereiten.
Die Darstellung wäre jedoch dann keine bloße Kosmetik, wenn eine Tabelle in dem gewünschten Format benötigt wird, weil sie so mit SQL besser weiterverarbeitet werden kann, um darauf aufbauende Abfragen zu erstellen.
Jedenfalls zeigen wir im folgenden, wie die Information in dem gewünschten Format erhalten werden kann.
CREATE VIEW STATISTIK AS
SELECT ABTEILUNG, GESCHLECHT, SUM( GEHALT ) AS GEHALT
FROM PERSON GROUP BY ABTEILUNG, GESCHLECHT;+-------------+------------+-----------+
| ABTEILUNG | GESCHLECHT | GEHALT |
+-------------+------------+-----------+
| Entwicklung | m | 55000.00 |
| Entwicklung | w | 49000.00 |
| Personal | m | 51000.00 |
| Personal | w | 123000.00 |
| Vertrieb | m | 110000.00 |
| Vertrieb | w | 50000.00 |
+-------------+------------+-----------+CREATE VIEW M AS
SELECT ABTEILUNG, GEHALT AS M FROM STATISTIK WHERE GESCHLECHT = 'M';+-------------+-----------+
| ABTEILUNG | M |
+-------------+-----------+
| Entwicklung | 55000.00 |
| Personal | 51000.00 |
| Vertrieb | 110000.00 |
+-------------+-----------+CREATE VIEW W AS
SELECT ABTEILUNG, GEHALT AS W FROM STATISTIK WHERE GESCHLECHT = 'W';+-------------+-----------+
| ABTEILUNG | W |
+-------------+-----------+
| Entwicklung | 49000.00 |
| Personal | 123000.00 |
| Vertrieb | 50000.00 |
+-------------+-----------+CREATE VIEW GESAMT AS
SELECT M.ABTEILUNG, M, W, M+W AS GESAMT
FROM M, W WHERE M.ABTEILUNG = W.ABTEILUNG;+-------------+-----------+-----------+-----------+
| ABTEILUNG | M | W | GESAMT |
+-------------+-----------+-----------+-----------+
| Entwicklung | 55000.00 | 49000.00 | 104000.00 |
| Personal | 51000.00 | 123000.00 | 174000.00 |
| Vertrieb | 110000.00 | 50000.00 | 160000.00 |
+-------------+-----------+-----------+-----------+
ohne Views:
Wir beginnen mit der Abfrage aus dem letzten View »GESAMT«:
SELECT M.ABTEILUNG, M, W, M+W AS GESAMT
FROM M, W WHERE M.ABTEILUNG = W.ABTEILUNG;
Einsetzen der Abfragen aus »M« und »F« hinter »FROM« in jener letzten Abfrage:
SELECT M.ABTEILUNG, M, W, M+W AS GESAMT
FROM
( SELECT ABTEILUNG, GEHALT AS M
FROM STATISTIK WHERE GESCHLECHT = 'M' ) AS M,
( SELECT ABTEILUNG, GEHALT AS W
FROM STATISTIK WHERE GESCHLECHT = 'W' ) AS W
WHERE M.ABTEILUNG = W.ABTEILUNG;
Dort kommt nun noch die Abfrage »STATISTIK« vor:
SELECT M.ABTEILUNG, M, W, M+W AS GESAMT
FROM
( SELECT ABTEILUNG, GEHALT AS M
FROM STATISTIK WHERE GESCHLECHT = 'M' ) AS M,
( SELECT ABTEILUNG, GEHALT AS W
FROM STATISTIK WHERE GESCHLECHT = 'W' ) AS W
WHERE M.ABTEILUNG = W.ABTEILUNG;
Einsetzen der Abfrage aus »STATISTIK« hinter »FROM« ergibt:
SELECT M.ABTEILUNG, M, W, M+W AS GESAMT
FROM
( SELECT ABTEILUNG, GEHALT AS M
FROM
( SELECT ABTEILUNG, GESCHLECHT, SUM( GEHALT ) AS GEHALT
FROM PERSON GROUP BY ABTEILUNG, GESCHLECHT ) AS STATISTIK
WHERE GESCHLECHT = 'M' ) AS M,
( SELECT ABTEILUNG, GEHALT AS W
FROM
( SELECT ABTEILUNG, GESCHLECHT, SUM( GEHALT ) AS GEHALT
FROM PERSON GROUP BY ABTEILUNG, GESCHLECHT ) AS STATISTIK
WHERE GESCHLECHT = 'W' ) AS W
WHERE M.ABTEILUNG = W.ABTEILUNG;
Rückgängigmachen des ersten »USE S;«
Man sucht ein Wort, das noch kein Schema benennt, wie beispielsweise »xf22«, dann verwendet man:
CREATE SCHEMA XF22;
USE XF22;
DROP XF22;
ERROR 1820: You must SET PASSWORD before executing this statement
SET PASSWORD = PASSWORD( 'password' );
Verbindung mit Excel
Es gibt verschiedene Möglichkeiten der Kommunikation mit Microsoft ® Excel :
Publizieren von MySQL-Datenquelle
http://dev.mysql.com/get/Downloads/MyODBC3/MyODBC-3.51.11-2-win.msi/from/pick
dev.mysql.com/downloads/connector/odbc/5.1.html.
Datenquelle anlegen
Start > Control Panel > System and Security > Administrative Tools > Data Sources(ODBC).
Datenquelle in Excel öffnen (Data -> Import External Data -> Import Data, New Source, ODBC DSN, Tabelle auswählen, Next->Finish)
Click on New Source button, select Other/advanced and click Next.
Test Connection tab
[Select Datasource]Open, Zelle wählen
Prüfen: username und password
MySQL for Excel
MySQL for Excel allows you to work with a MySQL database from within Microsoft Excel.
Für »5 = 5 OR 5 = 7 OR 5 = 8« kann abkürzend geschrieben werden: »5 IN( 5, 7, 8 )« (auch mit einer anderen Anzahl von Vergleichen als 3).
mysql> SELECT 5 IN( 5, 7, 8 );
+-----------------+
| 5 IN( 5, 7, 8 ) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.17 sec)
Mehr zu Operatoren
Diese Operatoren sind im Skalar-Kapitel zu früh und werden daher hier beschrieben.
Bereichsoperatoren
Die mathematische Bedeutung von »9>7>5« (9 und 5 ausschließend) wird mit »7 BETWEEN 6 AND 8« (6 und 8 einschließend) ausgedrückt.
mysql> SELECT 7 BETWEEN 6 AND 8;
+-------------------+
| 7 BETWEEN 6 AND 8 |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.08 sec)
Schreibweisen wie »7 BETWEEN 6 AND 8« machen den Bezug zur englischen Sprache in “structured English query language ” deutlich. SEQUEL sollte wie normales Englisch gelesen werden können und weniger an mathematische Schreibweisen erinnern.
LIKE
»x LIKE pattern«
»%« Platzhalter für beliebige Zeichenfolge.
»_« Platzhalter für genau ein beliebiges Zeichen.
Beispiele: »x LIKE '%Alpha%'«, »x LIKE 'Alpha%'«, »x LIKE '%Alpha'«.
Bei einigen Datenbanksystemen ist die Groß- und Kleinschreibung bei »LIKE« signifikant. Bei einigen Datenbanksystemen sind Zeichenlisten, wie [abc] möglich, um Zeichenmengen anzugeben („eines der Zeichen 'a', 'b', oder 'c'“).
Diverse weitere Notizen
Besonderheiten von MySQL 5.6 (Auswahl)
Wie man sieht kann auch ganz ohne Bezug auf eine Tabelle selektiert werden!
Einige Datenbanksysteme bieten keine Funktionen »LEFT« und »RIGHT« [8].
Bei einigen Datenbanksystemen sind einige Besonderheiten in Zusammenhang mit »SUBSTRING« zu beachten [9].
Bei einigen Datenbanksystemen wird der Operator »+« an Stelle von »CONCAT« verwendet [4].
Einige Datenbanksysteme verlangen zum Selektieren immer die Angabe eines FROM-Abschnittes [7]. In diesem Fall kann eine Tabelle mit einer Zeile verwendet werden, deren Inhalt keine Rolle spielt.
Für MySQL 5.6 kann die besonders effiziente Maschine InnoDB verwendet werden. Dann sind einige besondere Regeln zu beachten, die weiter unten teilweise genannt werden.
SET default_storage_engine=InnoDB;
SHOW ENGINES\G;
Anmerkungen zu Implementationen
[1] Abschluß einer Select-Abfrage
Microsoft SQL Server »«.
MySQL »;«.
Oracle »;«.
[2] Namen mit Leerzeichen
Microsoft SQL Server »[alpha beta]«.
MySQL »`alpha beta`« (Gravis).
Oracle »"alpha beta"« (Groß- und Kleinschreibung ist hier signifikant).
[3] Spaltentitel bei Spalte »'alpha'«
Microsoft SQL Server »«.
MySQL »alpha«.
Oracle »ALPHA«.
[4] Textverkettung
Microsoft SQL Server »a+b+c«.
MySQL »CONCAT(a,b,c)« (variable Anzahl von Argumenten).
Oracle »a||b||c« oder »CONCAT(a,b)« (nur zwei Argumente möglich).
[5] Wird ein Apostroph bei Spaltenaliasnamen ohne Leerzeichen verlangt?
Microsoft SQL Server Ja
MySQL Ja.
Oracle Nein.
[6] Wird ein »AS« bei Tabellenaliasnamen verwendet?
Microsoft SQL Server Ja.
MySQL Ja.
Oracle Nein (statt »AS Alpha« nur »Alpha«).
[7] Wird ein »FROM« in »SELECT«-Abfragen verlangt?
Microsoft SQL Server Nein.
MySQL Nein.
Oracle Ja. (Es kann »FROM DUAL« verwendet werden.)
[8] Gibt es »LEFT« und »RIGHT«-Funktionen?
Microsoft SQL Server Ja.
MySQL Ja.
Oracle Nein. (Es kann »SUBSTR« verwendet werden.)
[9] Besonderheiten bei Verwendung von »SUBSTRING«
Microsoft SQL Server —.
MySQL Zwischen »SUBSTRING« und »(« darf kein Leerzeichen verwendet werden.
Oracle Die Funktion heißt »SUBSTR« und erlaubt auch einen negativen Wert des zweiten Arguments, um von rechts zu starten.
[10] Aktuelle Zeit
Microsoft SQL Server »GETDATE()«.
MySQL »NOW()«.
Oracle »CURRENT_DATE()«.
[11] Name der Nullfunktion
Microsoft SQL Server »ISNULL«.
MySQL »IFNULL«.
Oracle »NVL« (Der Wert »NULL« wird als Strich »—« dargestellt).
[12] Beschränkung der Zeilenzahl auf 7
Microsoft SQL Server »TOP 7« direkt nach »SELECT«.
MySQL »LIMIT 7« nach FROM-Klausel.
Oracle »SELECT * FROM ( SELECT … ) WHERE ROWNUM < 7« (in den Klammern steht die restliche Abfrage).
[13] Groß- und Kleinschreibung bei »LIKE«
Microsoft SQL Server nicht signifikant.
MySQL nicht signifikant.
Oracle signifikant.
[14] Sind Zeichenlisten, wie »[abc]« und »[^abc]« möglich?
Microsoft SQL Server Ja.
MySQL Nein.
Oracle Nein.
[15] Gibt es »DIFFERENCE«?
Microsoft SQL Server Ja.
MySQL Nein.
Oracle Nein.
[16] Gibt es »ALTER VIEW«?
Microsoft SQL Server Ja.
MySQL Ja.
Oracle Nein.
[17] Gibt es »INTERSECT«?
Microsoft SQL Server Ja.
MySQL Nein.
Oracle Ja.
[18] Gibt es »EXCEPT«?
Microsoft SQL Server Ja.
MySQL Nein.
Oracle Ja, als »MINUS«.
[19] Können mit »INSERT« mehrere Zeilen eingefügt werden?
Microsoft SQL Server Ja.
MySQL Ja.
Oracle Nein.
[20] »NULL« und »''«
Bei Oracle-Datenbanken soll NULL gleich dem leeren Text »''« sein (und in UNIONs (Vorgriff) ist der Wert NULL nur mit Zeichenketten statt mit allen Datentypen kompatibel (muß konvertiert werden)).
SQL-Ausführer
Dr. Gordon Russell
Dr Gordon Russell http://db.grussell.org/sql/interface.cgi
ms jet http://www.w3schools.com/sql/sql_tryit.asp
http://sqlzoo.net/
http://sqlzoo.net/wiki/SELECT_basics/de
http://www.berkeleybop.org/goose/
http://www.sqlcourse2.com/sql_interpreter.html
http://sqlfiddle.com/
http://sqleditor.co.uk/
captcha http://data.stackexchange.com/stackoverflow/query/new
? http://www.mylittletools.net/mla_sql_2.7/
MySQL 5.5 http://demo.phpmyadmin.net/
SQL-Formatierer
http://sqlformat.appspot.com/
http://www.freeformatter.com/sql-formatter.html
SQL Escape http://www.freeformatter.com/sql-escape.html
http://www.dpriver.com/pp/sqlformat.htm
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
SQL-Validator
http://developer.mimer.com/validator/index.htm
http://developer.mimer.com/validator/reservedwords/index.tml
MySQL-Skript
(auch mit einigen anderen Datenbanken verträglich.)
create table families ( id int not null auto_increment, name varchar(20) not null, primary key (id));
insert into families (name) values ('Meier');
insert into families (name) values ('Schultz');
insert into families (name) values ('Mueller');
Lesen aus Textdatei
LOAD DATA INFILE 'example.dat'
INTO TABLE books
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 0 LINES
( title, author, publisher, topic, comment, price );
MySQL-Quellen
Handbuchhttp://dev.mysql.com/doc/refman/5.6/en/index.htmlHandbuch-Downloadhttp://downloads.mysql.com/docs/refman-5.6-en.a4.pdf
http://dev.mysql.com/doc/Handbuch-Registerhttp://dev.mysql.com/doc/refman/5.6/en/ix01.htmlHandbuch-Glossarhttp://dev.mysql.com/doc/refman/5.6/en/glossary.html
MySQL-Stapel-Einfügen mit Java (JDBC)
Abfrage der Systemtabellen
Objekte
select
table_name,
case table_type
when 'BASE TABLE' then 'BT'
when 'TEMPORARY' then 'TT'
when 'SYSTEM VIEW' then 'SV'
else table_type
end from
information_schema.tables where
table_schema = 'S'
union all
select
routine_name, routine_type from
information_schema.routines where
routine_schema = 'S' ;
Spalten
select
column_name, column_type, is_nullable from
information_schema.columns where
table_schema = 'S' and table_name = 'b' order by
ordinal_position ;
Joins
select
referenced_table_name,
referenced_column_name,
column_name from
information_schema.key_column_usage where
table_schema = 'S' and table_name = 'B' and position_in_unique_constraint is not null
union all
select
table_name,
column_name,
referenced_column_name from
information_schema.key_column_usage where
referenced_table_schema = 'S' and referenced_table_name = 'B' and position_in_unique_constraint is not null order by 1 ;