[Unterlagen][SQL Tutorial ...dos]

Oracle DDL/SQL Cheat Sheet (up)

Oracle DDL/SQL Cheat Sheet
• CREATE Statement
• ALTER Statement
• INSERT Statement
• UPDATE Statement
• DELETE Statement
• DROP Statement
• SELECT Statement
• Datatypes
• Functions (to_char & to_date)

CREATE Statement (up)

CREATE TABLE <table> (
  <primarykey_attribute>  <datatype>
     [CONSTRAINT <constraint> {UNIQUE|PRIMARY KEY}]
     [CONSTRAINT <constraint> {NOT NULL|NULL}]
     [CONSTRAINT <constraint> CHECK(<condition>)]
  <foreignkey_attribute>  <datatype>
  <attribute>             <datatype>
 [FOREIGN KEY (<foreign_attribute>) REFERENCES <table>(<primarykey_attribute>) [ON DELETE {CASCADE|SET NULL}]]
 [PRIMARY KEY (<primarykey_attribute>, ...)]
);
 
<condition>  ... <leftside> <operand> <rightside>
<leftside>   ... <attribute> | <literal>
<rightside>  ... <attribute> | <literal>
<operand>    ... = | <> | < | <= | > | >=

ALTER Statement (up)

ALTER TABLE <table>
ADD (CONSTRAINT <contraint>) FOREIGN KEY (<foreign_attribute>) REFERENCES <table>(<primarykey_attribute>);

INSERT Statement (up)

INSERT [INTO] <table> [(<attribute1>, ..., <attributeN>)] VALUES (<digit>, '<string>', to_date('01.01.2000', 'DD.MM.YYYY'), NULL, ...);

UPDATE Statement (up)

UPDATE <table> SET <attribute> WHERE <condition>;

DELETE Statement (up)

DELETE [FROM] <table> WHERE <condition>;

DROP Statement (up)

DROP TABLE <table> [CASCADE CONSTRAINTS];

SELECT Statement (up)

SELECT [DISTINCT] [*|<attribute1, ..., attributeN>]
FROM [<table1, ..., tableN>]
[WHERE <condition1> [AND|OR] ... [AND|OR] <conditionN>]
[START WITH] [CONNECT BY]
[GROUP BY <attribute1>, ..., <attributeN>]
[HAVING <condition>]
[ORDER BY <attribute1>, ..., <attributeN> [ASC|DESC]]

Datatypes (up)

VARCHAR2(<size>)
VARCHAR(<size>)
CHAR(<size>)
NUMBER(<precision>,<scale>)
INTEGER
FLOAT
DATE
TIMESTAMP
BLOB
XMLType
...

Functions (to_char & to_date) (up)

DD
Tag des Monats (1 - 31)
DAY
Name des Tages ('MONTAG' bis 'SONNTAG')
day
Name des Tages ('montag' bis 'sonntag')
Day
Name des Tages ('Montag' bis 'Sonntag')
MM
Monat des Jahres (1 - 12)
MON
Monatsname dreistellig ('JAN' bis 'DEZ')
mon
Monatsname dreistellig ('jan' bis 'dez')
Mon
Monatsname dreistellig ('Jan' bis 'Dez')
MONTH
Monatsname ('JANUAR' bis 'DEZEMBER')
month
Monatsname ('januar' bis 'dezember')
Month
Monatsname ('Januar' bis 'Dezember')
YY
Jahr zweistellig (00 bis 99)
YYYY
Jahr vierstellig
HH24
Uhrzeit: Stunde (0 - 24)
MI
Uhrzeit: Minute (0-60)
SS
Uhrzeit: Sekunde (0-60)
IW
Kalenderwoche nach ISO
Q
Quartal (1, 2, 3, 4)
- / , . ; : .
Formatierungszeichen
Examples: 
to_char(23012.9, '000G000D00') ⇒ '023.012,90' 
to_char(to_date('24.12.2002','dd.mm.yyyy'),'hh24:mi:ss') ⇒ '00:00:00' 
Letzte Änderung: 05.11.2008, 14:05 | 388 Worte