Deutsch
Springe zu
Dein Feedback:
Hat die Seite Deine Erwartung erfüllt? vote3 Ja
vote2 Teilweise
vote1 Nein
Noch ein Kommentar?

Nur falls, Du eine Antwort erwartest, Deine E-Mailadresse

Gegebenenfalls noch Dein Name

Do not change this:
Feedback
Suchen

SQL / Datenbanken

Auf dieser Seite habe ich mir einige SQL Befehle und Hinweise zu verschiedenen Datenbanken notiert. Richtige SQL Einführungen sind für die meisten Leser sicher hilfreicher.
15-06-2007 21.27

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 SQLite Übersicht.
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:\temp\foo.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.
17-11-2012 12.28

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
John421
Frank551
Peter552
Jane282

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_TEAM
John42142
Frank55142
Peter55228
Jane28228

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
)

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:

ID VALUE NEXTID
7 a 13
13 k 1
1 e 42
2 x 4
4 y 5
5 z 5
11 S 3
3 n 7

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;

VALUE
S
n
a
k
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 Constraints

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;

Teile eines Datums extrahieren

SELECT EXTRACT(YEAR FROM sysdate) FROM dual;
SELECT EXTRACT(DAY FROM sysdate) FROM dual;
Oder aber mit to_char

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

Name Befehl Aktion Anwendung
ROW-Trigger FOR EACH ROW Werden pro geänderter Zeile ausgeführt z.B. für die Protokollierung von Änderungen
Statement-Trigger FOR EACH STATEMENT werden pro ausgeführtem Statement ausgeführt, egal wieviele Zeilen betroffen sind z.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

select * from global_name
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 ...

08-02-2014 15.31

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>&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

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;

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);

08-02-2014 15.31

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
17-11-2012 12.28

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
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|10115
sqlite> 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|10115

sqlite> .quit
Hier ist ein SQLite JDBC Beispiel und hier ist ein SQLite Hibernate Beispiel und so funktioniert SQLite Squirrel.
06-12-2012 18.42

JDBC

JDBC
17-11-2012 12.28
Powered by PHP Created with Xemacs Valid XHTML 1.0! Valid CSS!