Database SQL

Tools

Squirrel

Squirrel SQL Um Squirrel mit mehr Speicher starten, in der squirrel-sql.bat Datei den -Xmx Wert erhöhen

-Xmx500m

In Squirrel kann man über

Session -> Syntax -> configure auto correct abreviation

kleine Abkürzungen anbgeben, die beim Tippen durch einen abgespeicherten Text ersetzt werden. Beispiel:

td -> to_date('20--','YYYY-MM-DD')

Schreibt man dann

... WHERE TIMESTAMP > td

wird td durch den to_date Ausdruck ersetzt.

Alternativ kann das aktuelle Statement auch über

SESSION -> Bookmarks -> ADD Bookmark

abgespeichert werden. Mit

CTRL J kann dann aus einer Liste der Bookmark das gewünschte abgerufen werden.

Squirrel SQLite

Zurück zur . Unter Drivers einen neuen Driver anlegen, dann als Extra Class Path ein SQLite JDBC jar hinzufügen. Name:

z.B. SQLite

Example URL:

jdbc:sqlite:c:tempfoo.db

Webseite URL:

http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

Danach kann für jede SQLite Datenbank Datei ein neuer Alias benutzt werden. Den gerade angelegten Driver benutzen, bei URL den Pfad zur Datei anhängen, Username und Passwort können frei bleiben.

SQL

SQL Links

SQL Data Retrieval

SELECT

SELECT * FROM foo WHERE myid=42;
SELECT * FROM foo WHERE myid=42 AND myotherid=12;
SELECT foo.myid,bar.otherid FROM foo,bar;
SELECT * FROM foo WHERE mytime BETWEEN
  TO_DATE('2007-07-01','yyyy-mm-dd') AND
  TO_DATE('2007-07-14 22:09:59','yyyy-mm-dd HH24:MI:SS');
SELECT id, foo FROM bar ORDER BY foo DESC, id ASC;

Oft will man alle Spalten auswählen und (eine) bestimmte als erstes sehen. Entweder man gibt dann alle Spalten an, oder man verwendet weiterhin * und listet einfach eine der vorhanden Spalten erneut auf. Damit das funktioniert muss man der Tabelle von der man selektiert einen Alias geben und der doppelten Spalte einen neuen Namen

SELECT f.numbers AS numbers2,
       f.*
  FROM foo f

SELECT DISTINCT

Mehrfach vorkommende Zeilen werden nur einfach ausgegeben

SELECT DISTINCT myname FROM foo;

SELECT CASE

CASE ersetzt Gelesenes durch eigene Inhalte, abhängig vom Gelesen.

SELECT foo, CASE
             WHEN b.x<0 THEN 'S'
             WHEN b.x>0 THEN 'G'
            ELSE 'E'
            END AS tg
            FROM bar b WHERE IS NOT NULL b.x;

SELECT DECODE

DECODE ersetzt den Inhalt einer Spalte durch die vorgegebene Abbildung

SELECT decode(A,
              42, 'Mr Bar',
               3, 'Mrs Bar'
             ) FROM  foo

INTERSECT

Liefert Schnittmenge beider Tabellen

SELECT myname FROM foo INTERSECT SELECT myname FROM bar;

MINUS

Listet die Werte aus der ersten Tabelle abzüglich der Werte die auch in der zweiten Tabelle sind

SELECT myname FROM foo
MINUS
SELECT myname FROM bar;

Die Inhalte zweier Tabellen vergleichen

Durch welchen Zeilen unterscheiden sich zwei Tabellen? Hier sogar zwei Tabellen in verschiedenen Datenbanken:

SELECT * FROM
(
 SELECT 'MYDB' DB_NAME, d.* FROM
 (
  SELECT * FROM foo.bar         minus
  SELECT * FROM foo.bar@OTHERDB
 ) d
 UNION
 SELECT 'OTHERDB' DB_NAME, d.* FROM
 (
 SELECT * FROM foo.bar@OTHERDB  minus
 SELECT * FROM foo.bar          
 ) d
)

Alias

SELECT foo.nm "Name", SUM(foo.nr) "Total Number" FROM foo ...;

In der Ausgabe steht statt "nm" dann "Name" und statt "sum(nr)" "Total Number".

JOIN

Verbindet alle Elemente aus einer Tabelle mit allen Elementen aus einer anderen Tabelle (auf Wunsch auch aus der gleichen). Beispiel mit zwei Tabellen. foo:

myidname
1Peter
2Paul
3John
Frank

bar:

myidage
122
251
33
  • INNER JOIN

Liefert alle Werte aus der ersten und der zweiten Tabelle bei der die Vergleichsschlüssel übereinstimmen. Ist der Vergleichsschlüssel in einer der beiden Tabellen leer wird er nicht angezeigt. Im obigen Beispiel erscheint also weder Frank, noch John, noch 33 wenn der Vergleichsschlüssel myid ist

  • NATURAL JOIN

Wie INNER JOIN, nur dass man den Vergleichsschlüssel nicht angeben muss wenn es in beiden Tabellen eine Spalte gleichen Namens gibt (im obigem Beispiel myid).

  • RIGHT OUTER JOIN

Findet man zu einem Eintrag in der rechten Tabelle keinen Eintrag in der linken Tabelle würde er bei einem normalen JOIN gar nicht angezeigt. Da man dabei Einträge verlieren würde, die man möglicherweise sehen möchte, werden solche Einträge bei einem RIGHT OUTER JOIN angezeigt und die Spalten aus der linken Tabelle werden mit null aufgefüllt. Ein typischer Fall, in dem kein passender Eintrag in der linken Tabelle gefunden werden kann, ist wenn die Vergleichspalte in einer der beiden Tabellen (oder sogar in beiden) null ist.

  • LEFT OUTER JOIN

Wie RIGHT OUTER JOIN, nur dass Einträge aus der linken Tabelle auch dann angezeigt werden wenn es keinen passenden Eintrag in der rechten Tabelle gibt.

SELECT name FROM foo             JOIN bar WHERE foo.myid=bar.myid;
SELECT name FROM foo NATURAL     JOIN bar;
SELECT name FROM foo NATURAL     JOIN bar WHERE foo.myid=42;
SELECT *    FROM foo LEFT  OUTER JOIN bar;
SELECT *    FROM foo RIGHT OUTER JOIN bar ON foo.myid=bar.myid;

In Oracle schreibt man statt einem normalen JOIN (INNER JOIN) mit Komma getrennt die Tabellen, NATURAL JOIN schreibt man dagegen wieder, bei einem LEFT oder oder RIGHT OUTER JOIN markiert man mit (+) die Seite die bei einem Vergleich nicht gefunden werden muss und dann mit null aufgefüllt wird.

SELECT a.name, SUM(a.nr) a.nr FROM foo a, bar b WHERE a.name    = b.name(+) GROUP BY a.name;
SELECT a.name, SUM(a.nr) a.nr FROM foo a, bar b WHERE a.name(+) = b.name    GROUP BY a.name;

UNION

Der Ausgabe von zwei Selects wird untereinander gemischt. Die Spalten beider Selects müssen die gleiche Datentypen haben. Doppelte Zeilen werden entfernt. Mit UNION ALL erhält man auch die doppelten Einträge.

SELECT myname FROM foo UNION SELECT myname FROM bar;
ORA-00932: inconsistent datatypes: expected - got CLOB

Das kann passieren, wenn man ein UNION macht das einen CLOB behinhaltet. Von einem CLOB kann man nicht mit distinct selektieren, ein UNION beinhaltet aber ein distinct. Daher dann UNION ALL benutzen.

LIKE

Alles was mit Peter anfängt bzw. alles was mit Peter anfängt und danach einen beliebigen Buchstaben hat (z.B. "Peters")

SELECT * FROM x WHERE name LIKE 'Peter%';
SELECT * FROM x WHERE name LIKE 'Peter_'

Wenn man mit LIKE sucht, arbeitet das Zeichen _ auch als Wildcard. Folgendes

SELECT * FROM FOO WHERE x LIKE 'BAR_%'

sucht also erfolgreich nicht nur nach BAR_X sondern auch nach BARE.

Möchte man das nicht, definiert man sich das Zeichen als Escape Zeichen und nutzt es

SELECT * FROM FOO WHERE x LIKE 'BAR_%' escape '';

GROUP BY

Zeilen mit gleichem Inhalt in bestimmten Spalten zusammenfassen, z.B. zum Aufaddieren

SELECT MYID1,SUM(MYYESNOCOL) AS MYSUM1 FROM MYTABLE1 WHERE MYTABLE1.MYID1=MYTABLE1.MYID2 GROUP BY MYTABLE1.MYYESNOCOL;
GROUP BY Strings zusammen fassen

In Oracle kann man den Inhalt mehrerer varchar Zellen in einem GROUP BY aggregieren. Hat man z.B. diese Tabelle

SELECT * FROM FOO;
IDLastName
1Frank
1Peter
2John

Und möchte alle Zeilen mit der gleichen ID zusammenfassen, geht das so

SELECT id, LISTAGG(LastName, ' : ') WITHIN GROUP (ORDER BY LastName) FROM FOO GROUP BY id;
1Frank : Peter
2John
HAVING

Gegeben ist folgende Tabelle:

MYIDMYNAME
100LA
200SD
150LA
151LA
300SD
500FR

Jetzt sollen

  • Reihen mit bestimmten Namen ausgewählt werden (WHERE Bedingung)
  • alle mit gleichem Namen gruppiert werden (GROUP BY)
  • pro gruppierten Element eine Summe gebildet werden (SUM)
  • nur die Summen angezeigt werden die eine bestimmte Größe haben (HAVING)
SELECT myname, SUM(myid) FROM foo WHERE ( myname='ABC' OR myname='DEF' )
 GROUP BY myname HAVING SUM(myid) > 300;

GROUP BY jeweils nur eine beliebige Zeile ausgeben

Gegeben dieses Beispiel:

ABC
14244
15511
21218

Es gibt offenbar mehrere Zeilen in denen A gleich ist. Über diese will man gruppieren. Will man mehr als nur Spalte A ausgeben (also z.B. auch B und C) muss man über die anderen Zeilen eine Aggregatfunktion legen (sonst gäbe es ja potentiell mehrere mögliche Werte für B und C die ausgegeben werden müssten). Oft will man aber einfach nur irgendeine der gruppierten Zeile haben.

Gruppiert man z.B. über A und wählt für B und C min()

SELECT a, MIN(b), MIN(c) FROM foo GROUP BY a

erhält man aber keine der Zeilen der DB sondern eine neue Zeile in der jeweils die Minimalwerte pro Spalte eingetragen sind.

ABC
14211
21218

Hier hat man z.B. die 42 aus der 1. Zeile und die 11 aus der 2. Zeile.

Man kann in Oracle aber auch gezielt die erste oder die letzte Zeile mit A=1 auswählen (erste oder letzte bezogen auf ein Sortierkriterium).

KEEP (DENSE_RANK FIRST ORDER BY B,C)

Damit hat man für zwar sicher für jede Gruppe nur noch höchstens eine Zeile, es wird aber dennoch eine Aggregatsfunktion erwartet. Man kann jetzt einfach min() oder max() nehmen, da wir nur noch höchstens eine Zeile haben macht das keinen Unterschied.

SELECT a,
       MIN(b) KEEP (DENSE_RANK FIRST ORDER BY b,c) "B",
      MIN(c) KEEP (DENSE_RANK FIRST ORDER BY b,c) "C"
FROM foo
GROUP BY a;

Damit erhält man das Gewünschte:

ABC
14244
21218

Will man statt der ersten die letzte Zeile haben nicht versuchen das mit min() oder max() auszuwählen (ändert nichts weil wir nur noch eine Zeile haben, da ist min()==max()) sondern FIRST gegen LAST austauschen (oder die Sortierung ändern).

Over Partition By

Angenommen man hat eine solche Tabelle:

NAMEAGETEAM
John421Frank551
Peter552Jane282

Und man möchte Information gruppiert nach Team betrachten. Das geht zwar mit einem group by, aber dabei verliert man Informationen, weil ja dabei Zeilen zusammengefasst werden.

Mit einem

OVER (PARTITION BY ...)

kann man jetzt Informationen pro Team ermitteln und trotzdem alle Zeilen behalten. Z.B. wie ist das kleinste Alter pro Team:

SELECT name, age, team, MIN(age) OVER (PARTITION BY team) AS min_age_for_one_team FROM f052653.over_the_moon
NAMEAGETEAMMIN_AGE_FOR_ONE_TEAMJohn42142Frank55142Peter55228Jane28228

Dabei muss die letzte Spalte, die vor dem OVER steht, eine Aggregatsfunktion sein.

Innere Abfrage

Erste Abfrage bezieht sich auf Ergebnis einer zweiten

SELECT SUM(mynr) FROM foo
 WHERE myname IN (SELECT myothername FROM bar WHERE myid > 5 );
UPDATE foo SET foo.price=(SELECT bar.price FROM bar WHERE foo.id=bar.id);

So kann man die Ergebnisse eines Select Statements für ein Updatestatement benutzen (das Select Statement darf höchstens eine Zeile finden)

UPDATE foo f SET (id,      name) =
(
 SELECT           b.number,b.description
 FROM bar b
 WHERE f.ref = s.ref
)
Inner Abfrage mit exists

Bei where x in (...) abfragen interessiert man sich ja nur dafür, ob x in der inneren Abfragen überhaupt vorkommt. Es ist also gar nicht notwendig die Werte aus der inneren Abfrage wirklich alle zu ermitteln. Daher kann man die Abfrage auch mit exists umschreiben. Falls Oracle das nicht schon selbst optimiert hat, kann man damit auf eine Performance Verbesserung hoffen.

SELECT SUM(mynr) FROM foo
 WHERE EXISTS (SELECT bar.myothername FROM bar WHERE foo.myname=bar.myothername AND bar.myid > 5);

IN Bedingung an mehrere Spalten gleichzeitig stellen

Man hat eine Bedingung an mehrere Spalten, z.B.

SELECT * FROM foo WHERE ( a=42 AND b=9 ) OR ( a=3 AND b=1)

Das kann man eigentlich nicht über eine WHERE IN Anweisung machen, mit einem Trick aber doch. Einfach die betroffenen Spalten zu einer zusammenfügen und auf das Zusammengesetzte testen

SELECT * FROM foo WHERE a || '_' || b IN ( '42_9', '3_1' )

Nutzt man dagegen ein Subselect kann man auch WHERE IN Abfragen mit mehr als einer Spalte durchführen

SELECT * FROM foo WHERE (a, b) IN (SELECT a, b FROM bar)

In einem CLOB suchen

SELECT * FROM BLOBTEST WHERE       MYCLOB LIKE '%Bar%'
SELECT * FROM BLOBTEST WHERE instr(MYCLOB, 'Bar')>0

Anzahl der Ergebniszeilen beschränken

Nur eine begrenzte Anzahl an Zeilen bekommen

MYSQL

SELECT a FROM foo LIMIT 20;

ORACLE

SELECT a FROM foo WHERE ROWNUM<=20;

Verkettete Listen connect by

Man hat eine solche Tabellenstruktur, in der jeder Zeile (hier über NEXTID -> ID) auf eine andere Zeile verweisen kann. Fängt man mit einem Eintrag an, kann man die ganze Kette ablaufen:

IDVALUENEXTID
7a13
13|k|1
1|e|42
2x4
4y5
5z5
11S3
3n7

Wie findet man jetzt alle Zeilen die zu einer Startzeile gehören? Die Schwierigkeit dabei ist, dass man vorher nicht wissen kann, wie viele Zeilen das sein werden, mit einem Join kommt man hier daher nicht weit.

SELECT VALUE FROM foo f START WITH f.id=11 CONNECT BY f.id = prior f.nextid;
VALUESnak|e

DML Data Manipulation Language

INSERT, UPDATE, DELETE

INSERT INTO MYTABLE1 (MYCOL1, MYCOL2, ...) VALUES (MYVALUE1, MYVALUE2, ...);
DELETE FROM MYTABLE1 WHERE MYCOL1 = 'MYVALUE1';
UPDATE      MYTABLE1 SET MYCOL1='MYVALUE3', MYCOL2='MYVALUE4' WHERE MYCOL2='MYVALUE2';

Neue Einträge aus vorhandenen erzeugen

INSERT INTO foo (a, b,         c)
     SELECT      a, '42' AS b, c  FROM bar WHERE a IS NOT NULL

MERGE (REPLACE)

Eine Tabelle in eine andere einmischen. Je nachdem ob die aktuelle Zeile schon vorhanden ist oder nicht kann ein anderer Befehl zum Einmischen benutzt werden.

MERGE INTO
  TABLE_DEST foo
USING
  TABLE_SOURCE bar
ON (FOO.id = bar.id)
WHEN     MATCHED THEN UPDATE SET foo.txt = bar.txt
WHEN NOT MATCHED THEN INSERT (id, txt) VALUES (bar.id, bar.txt);

Ersetzen von String durch andere

So kann man einen String durch einen anderen ersetzen

SELECT REPLACE('TestEn', 'e', 'a') FROM dual

Dabei ist die Groß- Kleinschreibung aber relevant. Es wird also nur das kleine e ersetzt. Möchte man statt dessen alle Vorkommnisse ersetzen

SELECT REGEXP_REPLACE('TestEn','e','a', 1, 0, 'i') FROM dual

1 und 0 (fange mit dem 1. Zeichen an und ersetze unbegrenzt viele Zeichen) sind eigentlich optional, hier aber notwendig um den Parameter 'i' angeben zu können.

SELECT REGEXP_REPLACE('TEst','e','x', 1, 0, 'i') FROM dual

Substrings

Angenommen man hat folgenden String

START:World Hello:END

Und möchte den Substring zwischen den beiden : erhalten:

SELECT text, regexp_substr(text, ':.*:') FROM
(
 SELECT 'Start:World Hello:End' AS text FROM dual
)

Ausgabe:

:World Hello:

Die ersten oder letzten Einträge bezüglich einer Sortierung

Angenommen man hat die Spalten a,b. Jetzt möchte man die Tabelle gedanklich nach b sortieren und interessiert sich dann für die erste 3 Zeilen nach dieser Sortierung

SELECT a, b FROM (
 SELECT
 a,b,
 RANK() OVER (ORDER BY b) r
 FROM
 FOO.BAR
)
 WHERE
 r<4;

DDL Data Definition Language

Achtung, DDL Statements müssen nicht per Commit abgesendet werden, sondern werden sofort aktiv.

CREATE

MySQL CREATE TABLE

CREATE       DATABASE MYDB1;
CREATE TABLE MYTABLE1 ( MYCOL1 SERIAL, MYCOL2 TEXT NOT NULL);

CREATE TABLE IPAdressen (IPid         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,IPadr      VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE Email      (EMAILid      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,EMAILadr   VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE URL        (URLid        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,URLadr     VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE PATH       (PATHid       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PATHtxt    VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE PUBTYPE    (PUBTYPEid    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PUBTYPEtxt VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE LANG       (LANGid       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,LANGtxt    VARCHAR(2)   NOT NULL UNIQUE);
CREATE TABLE PAGEID     (PAGEIDid     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,PAGEIDtxt  VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE UAGENT     (UAGENTid     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,UAGENTtxt  VARCHAR(255) NOT NULL UNIQUE);
CREATE TABLE HTMLERRLOG (HTMLERRLOGid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY, PATHid BIGINT UNSIGNED NOT NULL,
     URLid BIGINT UNSIGNED NOT NULL, IPid BIGINT UNSIGNED NOT NULL, HTMLERR BIGINT UNSIGNED NOT NULL, PAGEIDid BIGINT UNSIGNED NOT NULL,
     COUNT BIGINT UNSIGNED NOT NULL DEFAULT 1, LASTMOD TIMESTAMP );

Man kann aus einem Select auch per DDL Statement eine neue Tabelle erzeugen. Geht viel schneller als per Insert und Subselect.

CREATE TABLE foo AS SELECT * FROM bar WHERE id>42;

Eine Tabelle anlegen und gleichzeitig einen Constraint vergeben

CREATE TABLE mytable (
 id   NUMERIC(10)  NOT NULL,
 name VARCHAR2(50) NOT NULL,
 age  NUMERIC(3)   NOT NULL,
 CONSTRAINT constr_id_name UNIQUE (id, name)
)

Er kann aber auch im Nachhinein einen Contstraint vergeben

ALTER TABLE mytable ADD CONSTRAINT constr_id_name PRIMARY KEY (ID)

Siehe auch

Eigene Datentypen erzeugen

So kann man sich (in Oracle) einen eigenen Datentyp deklarieren

CREATE OR REPLACE TYPE address AS OBJECT
(
   street   CHAR(20),
   city     CHAR(20)
)

den man dann als Spaltentyp benutzen kann

CREATE TABLE foo
(
   myname     CHAR(20),
   myaddress  address)

So füllt man diese Tabelle dann

INSERT INTO foo VALUES
(
 'John',
 address('Main Street',
         'NY'
        )
)

So liest man daraus

SELECT f.myaddress.street FROM foo f

(an dieser Stelle ist das f. wohl nicht optional)

Info zum Datentypen anzeigen

DESC address

VIEWS

Aus vorhandenen Spalten eine virtuelle Tabelle machen

CREATE VIEW v_myview AS
 SELECT myname, myid FROM foo;

Geht auch über mehrere Tabellen

CREATE VIEW v_myview AS
 SELECT foo.myname, bar.myothername FROM foo,bar
  WHERE foo.myid = bar.myid GROUP BY foo.myid;

Vorsicht: Wenn man eine View mit einem * select erzeugt:

CREATE VIEW v_myview AS
 SELECT * FROM foo;

Wird das während der Erzeugung der View ausgewertet und ist danach statisch, das heißt, kommen in der Tabelle auf das sich das View bezieht neue Spalten hinzu sind diese nicht automatisch in der View enthalten.

Oracle Inlineviews

In Oracle kann man eine Subquery über eine With Clause so definieren, dass sie wie eine Inline View erscheint. Das macht Statements mit vielen Subqueries übersichtlicher und leichter zu pflegen

WITH HotEntries AS
(
 SELECT * FROM a WHERE STATUS=’active’
),
SmallEntries AS
(
 SELECT * FROM b WHERE x<100
)
SELECT f.id, f.name, b.name FROM HotEntries f, SmallEntries b WHERE f.id=b.id

Invalide Objekte

Hat man z.B. eine View A, die auf eine andere View B zeigt, und die View B wird verändert, ist View A invalide. Man kann nicht wissen ob die View B überhaupt noch die Spalten anbietet, die View A nutzt.

Alle invalide Objekte auflisten

SELECT * FROM ALL_OBJECTS a WHERE a.status <> 'VALID'

Valide werden views indem man entweder einfach von ihnen liest, oder aber durch ein COMPILE.

ALTER VIEW foo.bar COMPILE

So erhält man automatisch Statements für alle invaliden Views

SELECT 'ALTER VIEW ' || o.owner || '.' || o.OBJECT_NAME || ' COMPILE;' FROM all_objects o WHERE status<>'VALID' AND OBJECT_TYPE='VIEW'

Alle invaliden Synonyme auflisten

SELECT * FROM dba_synonyms  s WHERE table_owner NOT IN('SYSTEM','SYS') AND db_link IS NULL AND NOT EXISTS
 (SELECT  1 FROM dba_objects o WHERE s.table_owner=o.owner AND s.table_name=o.object_name)

Materialized Views

Wie normale Views, nur dass nicht bei jedem Zugriff auf die View die Daten aus den Quelltabellen bezogen wird. Statt dessen werden die Daten nur manuell bezogen und danach werden zwischengespeicherte Werte ausgeliefert. Anlegen

CREATE MATERIALIZED VIEW foo.bar AS
 SELECT * FROM blub.mytable

Relevante Rechte:

GRANT CREATE      MATERIALIZED VIEW TO foo;
GRANT CREATE  ANY MATERIALIZED VIEW TO foo;
GRANT ALTER   ANY MATERIALIZED VIEW TO foo;
GRANT DROP    ANY MATERIALIZED VIEW TO foo;
GRANT EXECUTE ON  dbms_snapshot     TO foo;
GRANT SELECT  ANY TABLE             TO foo;
GRANT CREATE  ANY TABLE             TO foo;
GRANT ALTER   ANY snapshot          TO foo;

Die manuelle Aktualisierung geht so

EXEC DBMS_MVIEW.REFRESH('foo.bar', 'C', NULL, FALSE, TRUE, 1,2, NULL, TRUE);

NULL Spalten und ihr Datentyp

Angenommen man hat ein select in dem eine Spalte für alle Zeilen null ist

SELECT a,b,c,NULL,d FROM foo

Dann ist der Datentyp dieser Spalte nicht automatisch zu ermitteln. Das kann z.B. Probleme machen, wenn dieses Select Teil einer View werden soll Mit einigen Tricks kann man den Datentyp aber beeinflussen. So erhält man z.B. als Datentype eine numerische null Spalte

SELECT a,b,c,(NULL + 0),d FROM foo
SELECT a,b,c,to_date(NULL),d FROM foo

Mit einem cast kann man sogar beliebige Datentypen erreichen. Ein eingeschobenes decode, welches nie greift verhindert dass der Trick vom cast bemerkt wird

SELECT a,b,c,CAST(decode('A', 'A', NULL) AS varchar2(255)),d FROM foo

DROP

Tabelle löschen

DROP TABLE foo;

TRUNCATE

Tabelle leeren

TRUNCATE TABLE foo;

ALTER

Tabellenspalten hinzufügen oder umbenennen

ALTER TABLE foo ADD myname VARCHAR;
ALTER TABLE foo CHANGE mynaame myname CHAR(50);
ALTER TABLE foo.bar RENAME COLUMN blub TO newblub;
ALTER TABLE foo.bar ADD blub varchar2(1);
ALTER TABLE foo.bar DROP COLUMN blub;

Eine Tabelle umbennen

ALTER TABLE USER.bar RENAME TO foo;

Beim Zielnamen ist das Schema nicht anzugeben.

Das Schema (den aktiven User) wechseln

ALTER SESSION SET CURRENT_SCHEMA = foo

Constraints

So kann man erreichen, dass bestimmte Spalten immer bestimmte Bedingungen erfüllen müssen

CREATE foo
(
  a   NUMBER,
  b   NUMBER,
  c   NUMBER,
  CHECK (a > b)
);

Kann man auch für eine vorhandene Tabelle nachreichen

ALTER TABLE foo ADD CONSTRAINT foo_check_1 CHECK (a > b);

Constraint der erzwingt dass der Wert in einer Spalte mit to_date() umgewandelt werden kann. Der Vergleich wird so gewählt, dass er immer erfüllt wird.

ALTER TABLE foo.bar ADD CONSTRAINT constr_to_date_possible CHECK
(
 to_date(foo,'dd.mm.yyyy')>to_date('01.01.1900', 'dd.mm.yyyy')
);

RENAME

ALTER TABLE foo RENAME COLUMN mynaame TO myname;

Disable

So kann man einen Constraint deaktivieren, aktivieren, entfernen (vor dem Constraint Namen darf das Schema nicht wiederholt werden)

ALTER TABLE foo.bar DISABLE CONSTRAINT my_constraint;
ALTER TABLE foo.bar ENABLE  CONSTRAINT my_constraint;
ALTER TABLE foo.bar DROP    CONSTRAINT my_constraint;

DCL Data Control Language

GRANT

Benutzer Rechte auf Datenbank gewähren

GRANT SELECT, INSERT ON customers TO webuser;

(MySQL)

GRANT ALL PRIVILEGES ON MYDB1.* TO 'USER1'@'www1.EXAMPLE.COM' IDENTIFIED BY 'MYSECRETPASSWORD' WITH GRANT OPTION;

Spaltenweise Rechte vergeben

GRANT UPDATE (age) ON foo.people TO bar;

With Grant Option

Wenn ein man eine Tabelle in einem Schema hat, und in einem anderen Schema soll eine View Zugriff auf diese Tabelle erhalten, dann braucht sie dafür entsprechende Rechte. Wenn diese View die Daten ihrerseits wieder an andere Schemata weiterreichen soll, braucht man dafür WITH GRANT OPTION. Folgendes Beispiel

So kann man PUBLIC select auf eine Tabelle erlauben und ihm auch erlauben, selbst andere zu berechtigen (with grant option). Folgendes erlaubt PUBLIC nicht nur den Zugriff auf foo.bar, sondern erlaubt es PUBLIC selbst wieder Zugriff auf Daten aus der Tabelle an andere zu gewähren.

GRANT SELECT ON foo.bar TO public WITH GRANT OPTION;

REVOKE

REVOKE ... FROM foouser;

Atomare Operationen / Transaktionen

Mehrer SQL Anweisung können zu einer atomaren Operation verbunden werden

BEGIN; SELECT ...; UPDATE ...; END;

In Oracle nutzt man immer automatisch eine Transaktion (kein BEGIN notwendig), man muss COMMIT benutzten um die Änderungen zu übernehmen. Mit ROLLBACK kommt man zu Anfang der Transaktion oder zum letzten SAVEPOINT zurück.

Flashback

In Oracle gibt es neben dem Rollback noch eine leistungsfähigere Möglichkeit, ungewollte Änderungen zurückzurollen. Angenommen man hat versehentlich eine Tabelle gelöscht

DROP TABLE foo.bar

Kann der DBA sie so wieder herstellen

flashback TABLE foo.bar TO BEFORE DROP

Eingebaute Funktionen

SUBSTR

Den Namen ab der Stelle 3 und dann die nächsten 2 Zeichen

SELECT SUBSTR(name, 3,2) FROM foo WHERE name = 'Bar';

Die letzten 2 Stellen abschneiden

SELECT SUBSTR(foo, 1, LENGTH(foo)-2) AS foo2 FROM bar;

Den Text zwischen dem 2. und 3. Komma ausschneiden

SELECT SUBSTR(s,
              instr(s, ',',1,2)+2,
              instr(s, ',',1,3)-instr(s, ',',1,2)-2
             )
FROM
    (
      SELECT 'AAA, BBB, CCC, DDD, EEE' s FROM  dual
    )

CONCAT (||)

Macht aus 2 Spalten eine durch zusammensetzten der beiden Spalten

SELECT CONCAT(name,id) FROM foo WHERE name = 'Bar';

SUM

Werte aufsummieren.

COUNT

Vorkommen zählen.

Aktuelles Datum beziehen

SELECT SYSDATE FROM dual;

Datum letzter Montag / letzter Freitag

SELECT
 next_day(trunc(sysdate, 'DDD'),'FRI')-11 AS start_monday_last_week,
 next_day(trunc(sysdate, 'DDD'),'FRI')-6  AS end_friday_last_week
FROM dual;

Das letzte Quartal ermitteln

SELECT
 TRUNC(SYSDATE, 'Q')                 AS q_prev,
 add_months(TRUNC(SYSDATE, 'Q'), -3) AS q
FROM dual;

Datum letzter Monat

SELECT
 add_months(TRUNC(sysdate, 'Month'), -1) AS s,
 add_months(TRUNC(sysdate, 'Month'), 0) AS e
FROM dual;

Teile eines Datums extrahieren

SELECT EXTRACT(YEAR FROM sysdate) FROM dual;
SELECT EXTRACT(DAY  FROM sysdate) FROM dual;

Oder aber mit to_char

Spracheinstellungen und Wochentage

Aufgepasst, die Datumsfunktionen in Oracle reagieren je nach Spracheinstellungen anders!

Tag heißt anders

SELECT
 to_char (sysdate, 'FmDay', 'nls_date_language=german'),   -- Dienstag
 to_char (sysdate, 'FmDay', 'nls_date_language=english')   -- Tuesday
FROM dual;

Wochen fangen an anderen Tagen an

-- 2
ALTER SESSION SET nls_territory = 'UNITED KINGDOM';
SELECT to_char(sysdate, 'd') FROM dual;

-- 3
ALTER SESSION SET nls_territory = 'AMERICA';
SELECT to_char(sysdate, 'd') FROM dual;

Aber so kann man erzwingen dass die Wochentage immer gleich geliefert werden und auch der Wochenbegin vorhersagbar ist

-- Get the day of the week in a numeric and predicable form
SELECT
CASE to_char (sysdate, 'FmDay', 'nls_date_language=english')
  WHEN 'Monday' THEN 1
  WHEN 'Tuesday' THEN 2
  WHEN 'Wednesday' THEN 3
  WHEN 'Thursday' THEN 4
  WHEN 'Friday' THEN 5
  WHEN 'Saturday' THEN 6
  WHEN 'sunday' THEN 7
END dow
FROM dual

Ein Datum runden

Auf Mitternacht auf oder abrunden

SELECT round(sysdate, 'DDD') FROM dual

Abrunden auf Mitternacht

SELECT trunc(sysdate, 'DDD') FROM dual

4 Stunden vor Mitternacht

SELECT trunc(sysdate, 'DDD')-(4/24) FROM dual

Mitternacht letzter Sonntag

SELECT trunc(sysdate, 'DAY') FROM dual

Mitternacht letzter Tag des vergangenen Jahres SELECT trunc(sysdate, 'IYYY') FROM dual

to_char

Mit to_char kann man Zahlen und Datumsangaben formatieren.

SELECT to_char(42, '99.99')  FROM dual                    -- 42.00
SELECT to_char(42, '$99.99') FROM dual                    -- $42.00
SELECT to_char(42, '000099') FROM dual                    -- 000042
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM dual -- 2008-01-19 18:38:51

Achtung, to_char reserviert auch bei positiven Zahlen ein Zeichen für ein mögliches Vorzeichen. Damit erhalten damit konvertierte positive Zahlen immer ein führendes Leerezeichen, was zu Problemen führen kann. Mit FM kann man dieses Verhalten steuern

SELECT
LENGTH('123') AS A,
LENGTH(to_char(123, '000')) AS B1,
LENGTH(to_char(123, 'FM000')) AS B2
LENGTH(to_char(-123,'000')) AS c
FROM dual

Nicht numerische Werte in einer Spalte finden

SELECT DISTINCT foo FROM bar WHERE UPPER(foo)!=LOWER(foo);

Nachkommastellen einer Zahl

Die Nachkommastellen einer Zahl ermitteln

SELECT            REGEXP_REPLACE(to_char(12.3456),'^[^.]*[.]*','')     FROM dual;

Die Nachkommastellen einer Zahl zählen

SELECT nvl(LENGTH(REGEXP_REPLACE(to_char(12.3456),'^[^.]*[.]*','')),0) FROM dual;

Spalten mit Nachkommastellen

ORA-01438: VALUE larger than specified PRECISION allowed FOR this COLUMN

Mit number(a,b) erzeugt man eine nummerische Spalte mit b Nachkommastellen und a-b Stellen vor dem Komma. Mit number(9,6) z.B. hat man 6 Nachkommastellen und 3 Stellen vor dem Komma.

CREATE TABLE mytable (id NUMBER PRIMARY KEY, mynumericcolumn NUMBER(9,6));

INSERT INTO mytable (id, mynumericcolumn) VALUES (1, 1.0);
INSERT INTO mytable (id, mynumericcolumn) VALUES (4, 123.456789);
INSERT INTO mytable (id, mynumericcolumn) VALUES (5, 1000.456789);
-- Error: ORA-01438: value larger than specified precision allowed for this column

ALTER TABLE mytable  MODIFY mynumericcolumn NUMBER(10,6);
INSERT INTO mytable (id, mynumericcolumn) VALUES (5, 1000.456789);

TRIM

SELECT LTRIM('   FOO   ') FROM dual;

LPAD

Einen String links mit Buchstaben auffüllen, bis er eine vorgegeben Gesamtlänge erreicht.

lpad('7', 3, '0')

Datenbanken und Tabellen Anlegen und Verwalten

SHOW     DATABASES;
SHOW     TABLES;
DESCRIBE MYTABLE1;

Schlüssel

Es kann nur einen Primary Key geben, aber mehrer Unique Keys.

Primärschlüssel

Eine Spalte als Primärschlüssel definieren: Oracle

CREATE TABLE Customer (SID INTEGER PRIMARY KEY, Last_Name VARCHAR(30));

Andere

CREATE TABLE Customer (SID INTEGER, Last_Name VARCHAR(30));
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Vorhandene Tabelle soll technischen Schlüssel bekommen

Eine neue Spalte hinzufügen und mit eindeutigen Werten initialisieren

ALTER TABLE foo.bar  ADD myid NUMBER UNIQUE;
UPDATE      foo.bar x SET x.myid=rownum;

Foreign Key

Eine Spalte in einer Tabelle verweist auf einen Primärschlüssel in eineren anderen Tabelle Oracle:

CREATE TABLE ORDERS (Order_ID INTEGER PRIMARY KEY, Order_Date DATE, Customer_SID INTEGER REFERENCES CUSTOMER(SID), Amount DOUBLE);

Mysql:

FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER(SID));

Nachträglich: Oracle:

ALTER TABLE ORDERS ADD CONSTRAINT fk_orders1 FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Andere:

ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Indizes

Aus einer oder mehrern Spalten einen Index erzeugen um schnelleres Suchen nach einzelnen Reihen zu erreichen

CREATE INDEX IDX_CUSTOMER_LOCATION ON CUSTOMER (City, Country);

Unter Oracle kann man so sehen, auf welche Spalten ein bestimmter Index geht

SELECT * FROM all_ind_columns WHERE INDEX_NAME='FOO'

Worauf sich der Index beziehen sollte

  • Index geht auf eine Spalte

Man greift mit der where Bedingung nur auf diese eine Spalte zu und erwartet (im Vergleich zur Gesamtanzahl) relativ wenig Zeilen für diese where Bedingung.

  • Mehrere Indizes, die jeweils nur auf eine Spalte gehen

Man hat mehrere einzelne Statements, die jeweils nur eine der Spalten abfragen (siehe Index geht auf eine Spalte).

  • Index über mehrere Spalten

Man hat Abfragen die where Bedingungen über mehrere Spalten beinhalten. Das gilt besonders dann, wenn die Teile der where Bedingungen, die sich auch eine Spalte beziehen die Auswahl noch nicht sehr stark einschränken, also viele Zeilen liefern würden. Hier verhindert der Index dass erst mal sehr viele Zeilen gelesen werden müssen.

Trigger

(Oracle lastig) Trigger: Trigger in Oracle

Trigger sind eventgesteuerte Prozeduren, die automatisch bei bestimmten Ereignissen durchgeführt werden. Es gibt 3 Auslöser

  • INSERT
  • UPDATE
  • DELETE

Zusätzlich kann noch der Ausführungszeitpunkt bestimmt werden

BEFOREVor der Änderung
AFTERNach der Änderung

Ein weiteres Kriterium ist wie oft der Trigger gestartet werden soll

NameBefehlAktionAnwendung
ROW-TriggerFOR EACH ROWWerden pro geänderter Zeile ausgeführtz.B. für die Protokollierung von Änderungen
Statement-TriggerFOR EACH STATEMENTwerden pro ausgeführtem Statement ausgeführt, egal wieviele Zeilen betroffen sindz.B. für erweiterten Zugriffsschutz

Trigger mit BEFORE, FOR EACH ROW und aktiv bei INSERT, UPDATE und DELETE

CREATE OR REPLACE TRIGGER mytrigger BEFORE INSERT OR UPDATE OR DELETE
ON foo FOR EACH ROW
WHEN (NEW.id>5)
DECLARE
   ...
BEGIN
   IF INSERTING THEN
    ...
   END IF;
   IF UPDATING THEN
    ...
   END IF;
   IF DELETING THEN
    ...
   END IF;
EXCEPTION
   ...
END mytrigger;
  • Nur in ROW-Triggern werden die Alten (old) und Neuen (new) Werte der Tabelle zur Verfügung gestellt
  • Bei BEFORE-Triggern besteht die Möglichkeit die NEW-Werte zu ändern

Kleines Beispiel, vor jedem Einfügen wird noch schnell ein LPAD auf die neuen Einträge gemacht

CREATE OR REPLACE TRIGGER foo.mytrigger
BEFORE INSERT OR UPDATE ON foo.mytable
 FOR each ROW BEGIN
   SELECT lpad(:NEW.mycolumn,3,'0')
     INTO :NEW.mycolumn
     FROM dual;
END;

Meta Informationen auslesen

(sehr Oracle lastig)

Alle Tabelle auflisten

Oracle

SELECT *           FROM tab;
SELECT TABLE_NAME  FROM user_tables;
SELECT TABLE_NAME  FROM all_tables;
SELECT TABLE_NAME  FROM dba_tables;
SELECT object_name FROM user_objects WHERE object_type = 'TABLE';
SELECT object_name FROM all_objects  WHERE object_type = 'TABLE';
SELECT object_name FROM dba_objects  WHERE object_type = 'TABLE';

Man kann sich auch den Quellcode aller FUNCTION, PACKAGE und PROCEDURE Objekte ansehen (und mit LIKE darin suchen)

SELECT * FROM ALL_SOURCE

Abhängige Objecte

Welche Objekte hängen vom Objekt FOO ab?

SELECT * FROM all_dependencies WHERE referenced_name='FOO'

Reservierte Oracle Namen als Spaltennamen

War jemand so unvorsichtig einen reservierten Oracle Namen als Spaltennamen zu verwenden?

SELECT * FROM  v$reserved_words WHERE KEYWORD IN (SELECT column_name FROM dba_tab_columns WHERE owner = 'FOO')

Aktuelle Datenbankinstanz

<code>select * from global_name</code>

MySQL

SHOW TABLES;
sp_databases;
sp_tables;
ERROR 1046 (3D000): No DATABASE selected

mysql --user=root --password=SECRET
mysql> SHOW DATABASES;
+--------------------+
| DATABASE           |
+--------------------+
| information_schema |
| mysql              |
| foobar             |
+--------------------+
3 ROWS IN SET (0.03 sec)

mysql> CONNECT foobar;

Alle Spalten einer Tabellen auflisten

SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE OWNER='FOO' AND TABLE_NAME='BAR'

Quelltext anzeigen

SELECT * FROM DBA_Source WHERE TYPE='FUNCTION' AND name='MYFOOFUNCTION' ORDER BY owner,name, TYPE, line

Constraint anzeigen

SELECT * FROM ALL_CONSTRAINTS i, ALL_CONS_COLUMNS c WHERE i.CONSTRAINT_NAME='FOO' AND c.CONSTRAINT_NAME=i.CONSTRAINT_NAME

Index anzeigen

SELECT * FROM all_indexes i, all_ind_columns c WHERE i.index_name='FOO' AND c.INDEX_NAME=i.INDEX_NAME

Sequenzen

CREATE SEQUENCE foo.bar START WITH 1 INCREMENT BY 1 MINVALUE 0

Eine Sequenz um eine bestimmte Anzahl erhöhen

Die Sequenz foo würde als nächstes 100 ausgeben. Sie soll aber 225 ausgeben

ALTER SEQUENCE foo INCREMENT BY 124;
SELECT foo.nextval FROM dual;
ALTER SEQUENCE foo INCREMENT BY 1;

Ein möglicher Cache auf die Sequenz könnte aber dazu führen, dass auch dann noch von einigen Prozessen (vorübergehend) Zahlen kleiner 225 aus der Sequence (bzw. deren Cache) gelesen werden können.

So kann man den Cache übrigens aktivieren und deaktivieren

ALTER SEQUENCE seq_cache NOCACHE;
ALTER SEQUENCE seq_cache 20; -- default

Gegenseitig blockierende Zugriffe

So kann man unter Oracle sehen, welche zwei Zugriffe sich gegenseitig blockieren

SELECT l1.sid, l2.sid FROM v$lock l1, v$lock l2 WHERE
     l1.block =1 AND l2.request > 0 AND l1.id1=l2.id1 AND l1.id2=l2.id2

Mit diesen beiden IDs kann man dann noch mehr Informationen ermitteln

SELECT * FROM v$session WHERE sid=FOO1 OR sid=FOO2

Auf der DB ausgeführte Befehle ermitteln

So sieht man unter Oracle welche Statements ausgeführt wurden

SELECT * FROM v$sqlarea ORDER BY FIRST_LOAD_TIME DESC

Man sieht allerdings nur die Statements, die neu auswertet werden musste. Prepared Statements tauchen also nur einmalig auf, auch wenn sie mehrfach ausgeführt werden.

Tablespace

In Oracle gibt es:

  • Dateien auf dem Dateisystem
  • Aus einer oder mehreren Dateien entsteht ein tablespace
  • Ein Schema (oder mehrere) liegen auf einem (oder mehreren) tablespace(s)
  • In einem Schema liegen dann u.a. die Tabellen

Das sind die Dateien auf dem Dateisystem (mit Größe)

SELECT * FROM dba_data_files ORDER BY bytes DESC

So sieht man alle Tablespaces:

SELECT * FROM v$tablespace ORDER BY name
SELECT * FROM DBA_TABLESPACES ORDER BY tablespace_name

So sieht man, welche Objekte auf dem Tablespace FOO noch Platz belegen:

SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'FOO' ORDER BY bytes DESC

Welcher Tablespace ist zur Zeit wie voll?

SELECT TABLESPACE_NAME, SUM(BYTES) free FROM dba_free_space GROUP BY TABLESPACE_NAME
 ORDER BY free DESC
SELECT * FROM (
 SELECT a.TABLESPACE_NAME,
  a.SIZE_BYTES/1024/1024/1024 AS SIZE_GB,
  b.FREE_BYTES/1024/1024/1024 AS FREE_GB,
  (round(((a.SIZE_BYTES-b.FREE_BYTES)/a.SIZE_BYTES)*100.0*100.0)/100.0) percent_used
 FROM
  (SELECT TABLESPACE_NAME, SUM(BYTES) SIZE_BYTES FROM dba_data_files GROUP BY TABLESPACE_NAME) a,
  (SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES FROM dba_free_space GROUP BY TABLESPACE_NAME) b
 WHERE  a.TABLESPACE_NAME=b.TABLESPACE_NAME
) ORDER BY percent_used DESC

Gibt es Dateien die gar nicht mehr benutzt werden?

SELECT f.file_name, f.file_id, f.TABLESPACE_NAME, f.bytes/1024/1024/1024 AS
Size_GB, COUNT(s.SEGMENT_NAME)
 FROM dba_data_files f, DBA_SEGMENTS s
 WHERE f.TABLESPACE_NAME=s.TABLESPACE_NAME(+)
 GROUP BY f.file_name,f.file_id,f.TABLESPACE_NAME,f.bytes
  HAVING COUNT(s.SEGMENT_NAME)=0
 ORDER BY Size_GB DESC

Wieviel Platz belegt welches Schema auf den jeweiligen Tablespaces

SELECT owner, TABLESPACE_NAME, SUM(bytes)/1024/1024/1024 AS SUM_SIZE_GB FROM
 DBA_SEGMENTS GROUP BY owner, tablespace_name ORDER BY sum_size_gb DESC

Wieviel Platz belegt welches Schema insgesamt?

SELECT owner, SUM(bytes)/1024/1024/1024 AS SUM_SIZE_GB FROM DBA_SEGMENTS GROUP
 BY owner ORDER BY sum_size_gb DESC

Gibt es Tabellen oder Indizes auf einem Tablespace:

SELECT * FROM DBA_TABLES  i WHERE i.TABLESPACE_NAME='FOO';
SELECT * FROM DBA_INDEXES i WHERE i.TABLESPACE_NAME='FOO';

Liste aller Partitionen

SELECT * FROM dba_ind_partitions

SQL Statements auf die Elemente beschränken, die sich in einer bestimmten Partition befinden

SELECT * FROM foo.bar PARTITION(partition_001) WHERE ...

Oracle

Oracle Links

Installation

Von der Oracle Webseite herunterladen und installieren, z.B. oracle-xe-universal_10.2.0.1-1.0_i386.deb (Paketname oracle-xe-universal) oder alternativ

deb http://oss.oracle.com/debian unstable main non-free

in die sources.list übernehmen.

Probleme:

/var/lib/dpkg/info/oracle-xe-universal.postinst: line 57: bc: command NOT found
apt-GET install bc
apt-GET remove --purge oracle-xe-universal
apt-GET install oracle-xe-universal
# /etc/init.d/oracle-xe configure
Specify the HTTP port that will be used FOR Oracle Application Express [8080]:
Specify a port that will be used FOR the DATABASE listener [1521]:
Specify a password TO be used FOR DATABASE accounts.  Note that the same
password will be used FOR SYS AND SYSTEM.  Oracle recommends the USE OF
different passwords FOR each DATABASE account.  This can be done after
initial configuration: admin
Confirm the password:  admin

Einloggen: Bei mir ging das Passwort das bei der Installation gesetzt wurde nicht. Also user oracle geht es auch ohne:

cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
. ./oracle_env.sh
sqlplus / AS sysdba

SQL> ALTER USER SYS IDENTIFIED BY admin;

Webfrontend für alle freigeben:

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Jetzt konnte ich auch das Webfrontend benutzten.

Einloggen nach der Installation:

# su - oracle
# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
# . ./oracle_env.sh
# sqlplus / AS sysdba

Einmal:

SQL> CREATE ROLE conn;
ROLE created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SYNONYM TO conn;
GRANT succeeded.

SQL> CREATE USER foo IDENTIFIED BY bar DEFAULT tablespace users TEMPORARY tablespace temp;
USER created.

SQL> ALTER USER foo QUOTA UNLIMITED ON users;
USER altered.

SQL> GRANT conn TO foo;
GRANT succeeded.

SQL> CONNECT foo

Enter password: bar

Connected.

SQL*Plus

sqlplus    username/passwort@foo.bar.de
sqlplus -S username/passwort@foo.bar.de @myscript.sql > myoutput.txt 2>&amp;1

(Der Name hinter dem @ ist kein Rechnername sondern kommt aus der tnsnameora)

Statements werden in sqlplus ausgeführt sobald ein ";" gelesen wird. Da es innerhalb eines ps/sql Skripts mehrere ";" geben kann ohne dass das Skript zu Ende wäre, wird in diesem Fall ein "/" am Ende des Skripts erwartet.

Allgemeine Hinweise

Befehle

define_editor=emacs

NULL

In Oracle ist der Vergleich mit NULL immer falsch. Das kann überraschende Auswirkungen haben.

ab
25
44
9null

Mit diesem Statement

SELECT * FROM foo WHERE b<>4

würde man vielleicht die erste und dritte Zeile als Ergebnis erwarten (schließlich sind 5 und null nicht gleich 4). Allerdings liefern alle Vergleiche mit null stets falsch, weshalb auch der Vergleich ob 4 ungleich null ist mit falsch beantwortet wird. Möchte man die null Werte auch erhalten kann geht das z.B. so

SELECT * FROM foo.bar WHERE lnvl(<>4)

bind_variables

Statt

SELECT * FROM foo WHERE myid=5;

lieber

variable x NUMBER
EXEC :x := 5
SELECT * FROM foo WHERE myid=:x;
EXEC :x := 42
SELECT * FROM foo WHERE myid=:x;

oder

DECLARE l_myvalue NUMBER;
...
EXECUTE IMMEDIATE
 'INSERT INTO foo VALUES (:x1, :x2)'
  USING l_myvalue, l_myvalue;

schreiben dann muss Oracle den Ausdruck nicht jedesmal neu parsen nur weil man nach einer anderen id sucht.

MTS

Statt pro Client einen Prozess zu starten gibt es einen Pool von Prozessen die sich die Clients teilen.

Read

  • Read Uncommitted

Erlaubt dirty reads während andere Clients gerade schreiben

  • Read Committed

Beim Lesen wird angehalten wenn die zu lesende Zeile gerade von einer anderen Transaktion bearbeitet wird. Ist aber auch unsicher:

  • Prozess A soll eine Spalte aufaddieren. Er liest Zeile 1,2,3,4.
  • Jetzt startet Transaktion die von Zeile 1 eine Zahl abzieht und auf 5 draufaddiert.
  • Der lesende Zugriff auf Zeile 5 wird jetzt blockiert bis der Schreibvorgang abgeschlossen ist (hier geht also nichts schief
  • Jetzt liest Prozess A weiter Zeile 5,6,7,8,...

Jetzt hat Prozess A aber Zeile 1 vor der Änderung gelesen und Zeile 5 nach der Änderung. Die festgestellte Summe hat aber so nie existiert.

  • Repeatable Read

Der zu lesensende Bereich wird während dem kompletten Vorgang gegenüber Änderungen gesichert. Die Ergebnisse sind also reproduzierbar.

In Oracle hat man immer Repeatable Read weil man auf virtueller Kopie der Daten liest.

Wenn man lesen will um das Gelesen zum Schreiben zu benutzen durch FOR UPDATE die betroffenen Bereiche sperren. Falls das nicht möglich ist warten oder fehlschlagen lassen (NOWAIT).

SELECT x,y FROM foo FOR UPDATE NOWAIT;

isolation_level

Es ist nicht möglich dass mehrere Änderungen gleichzeitig stattfinden. Falls doch gibt es einen Fehler.

isolation_level=serializable

SET TRANSACTION

Angaben über die aktuelle Transaktion. Z.B. aktuelle Transaktion schreibt nichts:

...
COMMIT;

SET TRANSACTION READ ONLY NAME 'foo';
SELECT * FROM bar WHERE ...;
COMMIT;

...

Es gibt:

  • READ ONLY
  • READ WRITE
  • ISOLATION LEVEL SERIALIZABLE
  • ISOLATION LEVEL READ
  • ISOLATION LEVEL READ COMMITTED
  • USE ROLLBACK SEGMENT foo
  • NAME bar

Datenbanktabellentypen

TypBeschreibung
HeapDie Zeilen werde im Speicher da abgelegt wo sie gerade hinpassen. Die Reihenfolge der Zeile ist folglich nicht vorhersehbar, Suchen nach einzelnen Elementen langsam, komplettes Einlesen aller Zeilen aber sehr schnell.
IndexIndex der auf einer oder mehreren Spalten basiert. Suche nach einzelnen Zeilen über diese Spalten ist sehr schnell, komplettes Einlesen ohne Index schneller (Full Scan). Dann kann man
SELECT /* FULL(foo) */ myname FROM foo;

benutzen um Oracle einen Hint zu geben dass das sinnvoller sein könnte.||

ClusteredMan hat mehrere Tabellen die über gemeinsamen Schlüssel verbunden sind. Statt die Tabellen hintereinander zu speichern werden die Zeilen aus den verschiedenen Tabellen die den gleichen Schlüssel haben zusammen gespeichert. Macht NATURAL JOINS schnell
Hash ClusteredWie Clustered, nur dass statt ein Hash als Index benutzt wird.

Indizes

IndexBeschreibung
B* Tree IndexIndex ist in einer Baumstruktur abgelegt (B-Baum, B*-Baum)
B* Clustered KeySiehe Clustered, das ist der Index dazu
Descendending IndexWie B* nur dass in den Baumknoten absteigend sortiert ist
Reverse IndexAus Konten 1234 wird Knoten 4321 (auf Bitebene). Indizes die nahe zusammenliegen werden so weit voneinder entfernt abgelegt. Hilfreich bei konkurrierenden Änderungen.
Bitmap IndexMan hat einen Index auf etwas mit sehr wenigen Ausprägungen wie z.B. das Geschlecht mit M, W, X (unbekannt oder keine natürliche Person). Normalerweise tausende verschiedenen Zeile mit tausend verschiedenen Indizies (die aber alle M, W, X sind). Bitmap macht das effizienter:
Zeile Nr1234...
Geschlecht MJaNeinJaNein...
Geschlecht WNeinJaNeinNein...
Geschlecht XNeinNeinNeinJa...

Jetzt ist es sehr effizient zu zählen wieviele Männer es in der Tabelle gibt (vor allem weil man dafür nur den Index braucht und die Zeile nicht gesucht werden muss). Schreibt man oft ist es aber sehr langsam weil dann immer die ganze Zeile im Index blockiert werden muss.

Function-Based-IndexMan schreibt eine Funktion die aus einer oder mehreren Spalten selbst einen Index erstellt
Applikation-Domain-IndexMan schreibt eine Erweiterung für Oracle (z.B. um Bilder in einer Datenbank speichern zu können) und will seinen eigenen Index mitliefern (z.B. basierend auf der Anzahl der Farben in einem Bild).

Hinweis: NULL in der Spalte auf der der Index basiert führt dazu dass der Index diese Zeile nicht erfasst. Kann ein Problem werden aber auch trickreich genutzt werden. Wenn sehr viele Zeilen normalerweise den gleichen Index hätten und nur wenige einen davon unterschiedlichen, den häufig vorkommenden auf NULL setzten, dann werden diese Zeilen nicht vom Index erfasst und man hat einen sehr viel kleineren Index (FIXME: kann sein dass man dafür 2 Spalten als Indexgrundlage braucht und nur eine NULL sein darf).

Hints

Index ignorieren bzw. erzwingen

SELECT /*+ FULL(foo)          */ bar FROM foo WHERE id=42;
SELECT /*+ INDEX(my_alias idx_bar) */ bar FROM foo.bar AS my_alias WHERE id=42;

So kann man mehr als einen Index angeben

SELECT /*+ INDEX(a ind1) INDEX (b ind2) */ a.*, b.* FROM tabx a, taby b

Hints funktionieren so allerdings nicht mehr (zuverlässig?), wenn man nicht direkt von einer Tabelle liest, sondern über eine View zugreift.

SELECT /*+ INDEX(my_alias idx_bar) */ bar FROM foo.my_view_on_bar AS my_alias WHERE id=42;

Das Problem ist, dass die View mit dem Alias my_alias ja selbst gar kein Index idx_bar hat. Es ist daher notwendig, einen Alias zu erstellen, mit dem man durch die View auf die zugrundeliegende Tabelle zugreifen kann. Das funktioniert mit einem Query Block Identifier. Diese werden automatisch generiert und können dem Explain Plan entnommen werden (Spalte Object Alias).

EXPLAIN PLAN FOR
SELECT bar FROM foo.my_view_on_bar AS my_alias WHERE id=42;
SELECT * FROM TABLE( dbms_xplan.display(NULL,  NULL, 'typical alias -rows -bytes -cost' ));
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1 / MY_ALIAS@SEL$2

Ein typischer Name ist wie hier z.B. MY_ALIAS@SEL$2 Mit diesem Query Block Indentifier kann man dann wieder einen Hint benutzen.

SELECT /*+ INDEX(MY_ALIAS@SEL$2 idx_bar) */ bar FROM foo.my_view_on_bar AS my_alias WHERE id=42;

Besser ist es natürlich, den Query Block Indentifier selbst festzulegen, damit man sicher sein kann, dass der Name sich auch nicht ändert.

CREATE OR REPLACE VIEW foo.my_view_on_bar AS
(
 SELECT /*+ QB_NAME (MyEvenNicerName) */ * FROM foo.bar
);

Jetzt hat man einen vorhersehbaren Namen, den man nutzen kann um einen Hint zu benutzen

Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$6BE3C2D9 / BAR@MYEVENNICERNAME

Nutzt man in einer View einen Join über mehrere Quellen scheint das nicht mehr zu funktionieren. Da kann es helfen die für jede Quelle ein Inner Select zu erstellen.

Performance

Explain Plan

SET TIMING ON;
SET AUTOTRACE ON EXPLAIN;
SET SERVEROUTPUT ON;

Nach größeren Änderungen kann es notwendig werden, die Statistiken zu aktualisieren

ANALYZE TABLE foo COMPUTE  STATISTICS;
ANALYZE TABLE foo COMPUTE  STATISTICS FOR ALL INDEXES;
ANALYZE TABLE foo ESTIMATE STATISTICS SAMPLE 20 PERCENT;

Alternativ

dbms_stats.gather_table_stats(ownname          => 'MYUSER',
                              tabname          => 'MYTABLE',
                              method_opt       => 'FOR ALL INDEXED COLUMNS',
                              degree           => 8,
                              granularity      => 'ALL',
                              cascade          => TRUE,
                              no_invalidate    => FALSE
                             );

Überprüfen ob die Statistiken aktuell sind

SELECT TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM ALL_TAB_COL_STATISTICS WHERE TABLE_NAME = 'FOO'
SELECT INDEX_NAME, LAST_ANALYZED              FROM ALL_INDEXES            WHERE TABLE_NAME = 'POSCHECKDETAIL'

Welche Statements haben in der nahen Vergangenheit Resourcen verbraucht (AWR Report) AWR Report erzeugen

Lang laufende Statements

Welche lang laufende Statements sind zur Zeit noch aktiv?

SELECT
 i.opname,
 i.start_time,
 i.last_update_time,
 i.time_remaining,
 i.message,
 i.username,
 s.sql_text
FROM
 v$session_longops i,
 gv$sqlarea s
WHERE
    i.sql_address = s.address
AND i.time_remaining>0;

Datenbank Link

Einen Link zu einer anderen Datenbank aufbauen und abfragen

CREATE DATABASE LINK mylink CONNECT TO mrfoo IDENTIFIED BY mysecretpasswd USING 'XE'
SELECT * FROM bar@mylink

Alle Datenbanklinks anzeigen

SELECT * FROM ALL_DB_LINKS

Tools

EXP

Daten exportieren. Auch nützlich um die gesetzten Optionen herauszufinden.

IMP

Daten die exportiert wurden wieder importieren.

SQLLDR

Daten in von der Kommandozeile in eine Datenbank einlesen.

PL/SQL

Einführung in die PL/SQL-Programmierung

PL/SQL Beispiele

SET serveroutput ON
DECLARE
  CURSOR c_mycursor1 IS SELECT DISTINCT foo FROM sys.bar;
  v_myid  sys.bar.foo%TYPE;

BEGIN
  dbms_output.enable(100000);

  dbms_output.put_line('Hello World.');
  dbms_output.put_line('============');

  -- iterate over input from cursor
  FOR c_current IN  c_mycursor1
  LOOP
    dbms_output.put('Debug: '); dbms_output.put_line(c_current.foo);

    v_myid:=NULL;

    SELECT MIN(ids) INTO v_myid FROM public.allids;

    IF v_myid IS NULL THEN
     dbms_output.put_line('Error');
    ELSE
     dbms_output.put('Min: '); dbms_output.put_line(v_myid);
    END IF;

  END LOOP;
END;
/

Eine function with no parameter

CREATE OR REPLACE FUNCTION  i_have_no_parameter
RETURN NUMBER
IS
BEGIN
RETURN 42;
END;
/

Aufruf

SELECT i_have_no_parameter FROM dual;
SET serveroutput ON format wrapped

CREATE OR REPLACE FUNCTION MyFunction
 (
  p_MyParameter         IN varchar2,
  p_MyOtherParameter    IN varchar2,
  p_AndAnotherParameter IN varchar2
 )
RETURN varchar2
IS
 myresult varchar2(32767);
BEGIN

 myresult:=p_MyParameter;
 myresult:=REGEXP_REPLACE(myresult, '([^A-Z_])' || p_MyOtherParameter ||'([^A-Z_])', '1' || p_AndAnotherParameter || '2', 1, 0, 'i');

 RETURN myresult;

END;
/

Aufruf

SELECT MyFunction('Some long String', 'long', 'really long') FROM dual;

Mit PL/SQL aus der Datenbank lesen

CREATE OR REPLACE FUNCTION foo
(
  p_myparameter         IN NUMBER
)
RETURN varchar2
IS
myresult varchar2(255);

CURSOR c_mycursor1 IS SELECT * FROM dual WHERE dummy=p_myparameter;

BEGIN

FOR c_current IN  c_mycursor1
  LOOP
    myresult:=myresult+c_current.dummy;
  END LOOP;
RETURN myresult;

END;
/

SHOW errors;

Ein to_date() das keine Fehler wirft sondern im Fehlerfall einen alternativ Wert liefert

CREATE OR REPLACE FUNCTION to_date_no_exceptions
(
  p_MaybeDate          IN varchar2, -- the string which to_date should convert into a date
  p_Format_mask        IN varchar2, -- the format you expect the string to have, see the Oracle doc for to_date. An example may be 'dd.mm.yyyy'
  p_OnErrorValue       IN DATE      -- if to_date fails this function retunrs this value (must be of format date). An example may be null or any valid date like to_date('01.1.1900', 'dd.mm.yyyy')
)
RETURN DATE
IS
BEGIN
RETURN to_date(p_MaybeDate, p_Format_mask);  -- this may fail if your p_MaybeDate is not a valid date
EXCEPTION
   WHEN others THEN   -- on any error we return the alternative date value you provided
      RETURN p_OnErrorValue;
END;
CREATE OR REPLACE FUNCTION concat_text_up_to_a_limit
(
p_id         IN NUMBER
)
RETURN varchar2
IS
myresult       varchar2(4000):='';
max_length     NUMBER:=4000;
current_length NUMBER:=0;
total_length   NUMBER:=0;
rest_length    NUMBER;
CURSOR c_mycursor1 IS SELECT text  FROM foo.bar v WHERE v.ID=p_id AND text IS NOT NULL ORDER BY id DESC;
BEGIN
FOR c_current IN  c_mycursor1
  LOOP
  myresult:= SUBSTR(CONCAT (myresult, c_current.remark), 0, max_length-4);
  END LOOP;
IF (LENGTH(myresult)=(max_length-4)) THEN
  myresult:=CONCAT(myresult, ' ...');
END IF;
RETURN myresult;
END;

Wie oft wird eine Function aufgerufen

Man sollte keinerlei Annahmen darüber treffen, wie oft Oracle eine Funktion aufruft, die in einem SQL vorkommt. Beispiel:

SELECT * FROM my_table WHERE my_table.my_value=my_function();

Die Funktion könnten einmal, einmal pro Zeile in der Tabelle oder zweimal aufgrund eines Index Range Scans aufgerufen werden, oder ... You cannot rely on how many times or when or if SQL will call your function

PL/SQL Collections als Datentyp

In SQL kann man neben den Basisdatentypen auch Collections benutzen PL/SQL Collections and Records

Index-by tables / associative arrays

Ein Schlüssel -> Wert Container, wie eine Hashtable

CREATE OR REPLACE FUNCTION  mycollectiondemo
RETURN varchar2
IS
 -- define a Associative Arrays / Index-By Tables type
 TYPE number2string_type IS TABLE OF varchar2(255) INDEX BY BINARY_INTEGER;

 -- define a variable with this type (this is a Hashtable)
 employees number2string_type;

 current_pos binary_integer;
 myresult varchar2(255);
BEGIN
 myresult:='';
 -- enter some entries in our hashtable
 employees(1235):='Mickey';
 employees(42):='Tom';
 employees(2221):='Alf';
 -- fill the hashtable with the one line result of a SQL select
 SELECT name INTO employees(2241) FROM some_table WHERE id=2;
 -- fill the hashtable with all rows a select returns
 FOR one_row IN ( SELECT id, name FROM some_table )
   loop
   employees(one_row.id) := one_row.name;
 END loop;
 -- iterate through the hashtable and build the result
 current_pos:=employees.FIRST;
 WHILE (current_pos IS NOT NULL)
   LOOP
     myresult:=myresult || current_pos || ': ' || employees(current_pos) || ', ' ;
     current_pos:=employees.NEXT(current_pos);
   END LOOP;
 RETURN myresult;
END;
/
SELECT mycollectiondemo FROM dual;
Nested tables

Wie eine einspaltige Tabelle

CREATE OR REPLACE FUNCTION  mycollectiondemo
RETURN varchar2
IS
 -- define a Nested Table type
 TYPE varchartable_type IS TABLE OF VARCHAR2(255);

 -- have a variable for it and fill it with values
 random_names varchartable_type:=varchartable_type('Frank', 'John');

 myresult varchar2(255);
BEGIN
 myresult:='';

 -- add a new element
 random_names.extend;
 random_names(random_names.last):='Jane';

-- remove an element
random_names.delete(2);

 -- iterate through nested table but watch out if it is sparse
 FOR i IN random_names.FIRST .. random_names.LAST
  LOOP
   -- you have to skip holes
   IF random_names.exists(i) THEN
    myresult:=myresult || i || ':' || random_names(i) || ';';
   END IF;
  END LOOP;

RETURN myresult;
END;
/

Wenn man den nested table type außerhalb des PL/SQL, also auf Schema Ebene definiert

CREATE OR REPLACE TYPE foo.varchartable_type IS TABLE OF VARCHAR2(255);

kann man sogar normale SQL Selects gegen die nested Table fahren

CREATE OR REPLACE FUNCTION  foo.mycollectiondemo
RETURN varchar2
IS
 -- have a variable for it and fill it with values
 random_names foo.varchartable_type:=foo.varchartable_type('Frank', 'John');
 myresult varchar2(400);
BEGIN
 myresult:='';
 -- add a new element
 random_names.extend;
 random_names(random_names.last):='Jane';
 SELECT MAX(column_value) INTO myresult FROM TABLE(random_names) WHERE column_value LIKE 'J%';
 RETURN myresult;
END;
/
SELECT foo.mycollectiondemo FROM dual;

Definiert man den Typ versehentlich innerhalb des PL/SQL Blocks erhält man

PL/SQL: ORA-22905: cannot access ROWS FROM a non-nested TABLE item
PLS-00642: LOCAL collection types NOT allowed IN SQL statements
Varrays

Wie ein Array, Größe vorgegeben, Reihenfolge fix, keine Löcher

-- Collection example
CREATE OR REPLACE FUNCTION  mycollectiondemo
RETURN varchar2
IS
-- define a varray
TYPE varray_type IS VARRAY (3) OF VARCHAR2(100);
 random_names varray_type:=varray_type();
myresult varchar2(255);
BEGIN
myresult:='';
-- add a new element
random_names.extend(2);
random_names(1):='Jane';
random_names.extend;
random_names(2):='Tim';
-- iterate through nested table but watch out if it is sparse
FOR i IN random_names.FIRST .. random_names.LAST
  LOOP
    myresult:=myresult || i || ':' || random_names(i) || ';';
  END LOOP;
RETURN myresult;
END;
/

Führende Leerzeichen verschwinden

Falls führende Leerzeichen beim dbms_output.put_line verschwinden hilft das hier

SET serveroutput ON format wrapped

Anzeige der Anzahl der veränderten Zeilen

Falls man auch in PL/SQL sehen möchte, wieviele Zeile eine update betroffen hat geht das so

UPDATE foo.bar SET x=y;
dbms_output.put_line('Numer of changed entries:' || SQL%ROWCOUNT);

postgresql

Mit postgresql Datenbank verbinden

psql -l
psql -h HOSTNAME DATABASENAME
l
d

Eine Datenbank exportieren und wieder importieren

pg_dump FOO -t BAR -d -D -F t -f /tmp/foo.tar
pg_restore --list /tmp/foo.tar > /tmp/foo.list
pg_restore --use-list=/tmp/foo.list --dbname=FOO -v /tmp/foo.tar

MySQL

SQLite

SQLite ist eine sehr kleine in C geschriebene Datenbankbibliothek, die ohne Serverinstanz auskommt und direkt ins das jeweilige Programm integriert wird, welches eine Datenbank benötigt. Siehe auch SQLite Wikipedia. Für andere Programmiersprachen gibt es SQLite Wrapper. Das ist die unterstützte SQlite SQL Syntax und hier die unterstüzten SQLite Datentypen.

In SQLite erhalten alle Tabellen automatisch einen mehr oder weniger unsichtbare Spalte

ROWID / _ROWID_ / OID

die jede Zeile dauerhaft eindeutig beschreibt. Erzeugt man eine Tabelle mit einer Spalte vom Typ

INTEGER PRIMARY KEY

zeigt diese intern auf die ROWID Spalte. Wenn man neue Zeilen einfügt und die Spalte nicht befüllt wird sie automatisch mit einer eindeutigen ID gefüllt. Siehe auch SQLite Autoincrement.

Auf der SQLite Download Seite gibt es z.B. eine Command-line Shell, mit der man SQLlite Datenbanken anlegen kann und über eine Shell direkt mit ihnen interagieren kann.

SQLite Shell

Eine neue Datenbank in der Datei MyFirstSQLiteDatabase.db im lokalen Ordner anlegen

sqlite3.exe MyFirstSQLiteDatabase.db

In der Shell kann man dann auch gleich Tabellen anlegen <pre>sqlite> create table cities (id INTEGER PRIMARY KEY ASC, name TEXT, postcode INTEGER); sqlite> insert into cities (name, postcode) values ("Berlin", 10115); sqlite> select * from cities; 1|Berlin|10115sqlite> create table people (id INTEGER PRIMARY KEY ASC, name TEXT, city_ref INTEGER REFERENCES cities (id)); sqlite> insert into people (name, city_ref) values ("John Doe", 1); sqlite> select * from people p, cities c where p.city_ref=c.id; 1|John Doe|1|1|Berlin|10115sqlite> .quit</pre> Hier ist ein und hier ist ein und so funktioniert .

Microsoft SQL Server

MS SQL Server Management Studio Express

Wenn man nicht alle Fenster geschlossen hat schlägt die Installation vielleicht fehlt

SQL server setup has encountered the following error invoke OR begininvoke cannot be called ON a control until the window has been created...

Vor einem erneuten Versuch Windows neu starten

<h3>MS SQL JDBC</h3> <a href=” http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx”>Microsoft JDBC Driver for SQL Server</a>

Falls dieser Fehler kommt

Could NOT find class com.microsoft.jdbc.sqlserver.SQLServerDriver IN neither the Java class path nor the Extra class path OF the Microsoft MSSQL Server JDBC Driver driver definition:
java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

Die JDBC Klasse ändern auf

com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC String

jdbc:sqlserver://your.db.server.name.example.com:1433;databaseName=MyDataBase

Allerdings ist dieser JDBC Treiber besser als der von MS

JTDS

In Squirrel das hier auswählen

jTDS Microsoft SQL

und JDBC Treiber von deren Webseite laden.

Windows Authentifizierung statt Username / Passwort: Die Datei README.SSO lesen. Kurzfassung: ntlmauth.dll so ablegen, dass SQL client sie findet. Squirrel: Squirrel Installationsordner. Danach einfach Username / Passwort leer lassen.

Falls er die DLL nicht findet kommt das

java.lang.RuntimeException: java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library NOT loaded. CHECK the java.library.path system property.

JDBC

JDBC