1.26k likes | 1.38k Views
Einführung in SQL. Universeller Zugriff auf Daten in Datenbanksystemen unterschiedlicher Systemhersteller!. SQL – Überblick. Nichtprozedurale Sprache zum Zugriff auf Daten. Herstellerspezifische prozedurale Erweiterungen Oracle -> PL/SQL Deklarative Beschreibung der gewünschten Daten
E N D
Einführung in SQL Universeller Zugriff auf Daten in Datenbanksystemen unterschiedlicher Systemhersteller!
SQL – Überblick • Nichtprozedurale Sprache zum Zugriff auf Daten. • Herstellerspezifische prozedurale Erweiterungen • Oracle -> PL/SQL • Deklarative Beschreibung der gewünschten Daten • Genormt aber doch nicht einheitlich • z.B.: DB-Verwaltung bei Oracle in SQL integriert • Themen: • Geschichte • SQL - Befehlskategorien • Elementare Datentypen und Operatoren • Einfache Abfragen Dipl.-Ing. Walter Sabin -- 2006
Geschichte • 1974 – SEQUEL (Chamberlin, Boyce) • Structured English Query Language • basierend auf SQUARE, einer mathematischen Formelsprache • Erster Prototyp: System R – IBM / San Jose ~1975 (Basis für DB2 und SQL/DS) • Wassergekühlter Computer • Erstes Produkt: Oracle – 1979 Version 2!! • PDP 11, Assembler • basiert auf veröffentlichten "System R" Spezifikationen • Derzeit gültiger ANSI Standard – SQL 2008 Dipl.-Ing. Walter Sabin -- 2006
Relationale Datenbank (1/3) • Definition: • Eine relationale Datenbank ist eine Datenbank, die aus Sicht des Benutzers aus Tabellen und nur aus Tabellen besteht. • Relation – mathematischer Ausdruck für Tabelle • Atomare Elemente • Gesamter Informationsinhalt in Daten • Basisarbeit: Dr. E. F. Codd (Codd Father!!)A Relational Model of Data for Large Shared Data Banks" (Comm. of ACM 1970) Dipl.-Ing. Walter Sabin -- 2006
Relationale Datenbank (2/3) • Beispiel: • Lieferanten - Produkte – Versand • 2 Entities mit Relation • Keine "Links" oder "Pointer" • zumindest nicht erkennbar für den Benutzer Dipl.-Ing. Walter Sabin -- 2006
Relationale Datenbank (3/3) Dipl.-Ing. Walter Sabin -- 2006
SQL Befehlskategorien - Oracle • DML – Data Manipulation Language • z.B.: SELECT, INSERT, UPDATE, DELETE • DDL – Data Definition Language • z.B.: (CREATE, ALTER, DROP, RENAME) TABLE • Transaktionssteuerung (COMMIT etc.) • System- und Session – Steuerung • z.B.: ALTER SYSTEM Dipl.-Ing. Walter Sabin -- 2006
Elementare Datentypen (1/3) • Zeichenketten • char(<size>) • z.B.: char(10) • feste Größe, rechts mit Leerzeichen aufgefüllt • maximal 2000 Zeichen • varchar2(<size>) • z.B.: varchar2(500) • variable Länge, maximal 4000 Zeichen Dipl.-Ing. Walter Sabin -- 2006
Elementare Datentypen (2/3) • Zahlen • number(<p>,<s>) • z.B.: number(6,2) - 1226.45 • p: Precission – maximal 38 • s: Scale – Nachkommastellen • Rundung wenn Anzahl der Nachkommastellen > s • negatives "s" möglich - rundet links vom Dezimalpunktz.B.: 12345.345 in number(5,-2) ergibt 12300 Dipl.-Ing. Walter Sabin -- 2006
Elementare Datentypen (3/3) • Datum und Zeit • date • belegt 7 Bytes - CC YY MM DD HH MI SS • Funktionen to_date, sysdate • Datum ohne Uhrzeit -> Mitternacht (00:00:00) • Standard format: DD-MON-YY Dipl.-Ing. Walter Sabin -- 2006
Operatoren und Literale (1/2) • Operator: manipuliert Datenelemente und liefert ein Ergebnis • unäre Operatoren - <operator><operand> • z.B.: +5 oder –2 • binäre Operatoren - < operand ><operator><operand> • + - * / • Verkettungsoperator – verbindet Zeichenketten • || - z.B.: 'Oracle' || 'Datenbank' ergibt 'OracleDatenbank' Dipl.-Ing. Walter Sabin -- 2006
Operatoren und Literale (2/2) • Literale • repräsentieren einen konstanten Wert • Text oder Zeichenketten in einfachen Hochkomma: 'Die Schule ist toll' • Ganze Zahlen (Integer): 24 oder –455 • Zahlen (Number) 24.45 oder –433.78 oder 2.3E-4 Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 1/12 • Allgemeiner Aufbau der Abfrage • SELECT <attributeList> FROM <relations>WHERE <condition>ORDER BY <orderClause> • Liefert als Ergebnis eine Menge von Datensätzen (Dataset oder Resultset) • Beispieldatenbank - Anhang Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 2/12 • SQL> SELECT * FROM jobs; JOBID JOB_TITLE MIN_SALARY MAX_SALARY AD_PRES President 20000 40000 ------- FI_MGR Finance Manager 8200 16000 FI_ACCOUNT Accountant 4200 9000 ------- Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 3/12 • SQL> SELECT job_title, min_salary FROM jobs; JOB_TITLE MIN_SALARY President 20000 ------- Finance Manager 8200 Accountant 4200 ------- Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 4/12 • Alias Namen • SQL> SELECT job_title AS ‘‘Title‘‘, min_salary AS "Minimum Salary" FROM jobs; Title Minimum Salary President 20000 ------- Finance Manager 8200 Accountant 4200 ------- Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 5/12 • Eindeutigkeit der Ergebnismenge sicherstellen • SQL> SELECT DISTINCT department_id FROM employees; DEPARTMENT_ID 10 20 30 --- 100 110 12 rows selected Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 6/12 • Pseudotabelle "dual" • SQL> SELECT SYSDATE, USER FROM dual; SYSDATE USER 20 -NOV-03 HR Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 7/12 • Ergebniszeilen einschränken • SQL> SELECT first_name || ' ' || last_name "Name",department_id FROM employees WHERE department_id=90; Name DEPARTMENT_ID Steven King 90 Neena Kochhar 90 Lex De Haan 90 • Vergleichsoperatoren • = <> (!=, ^=) <, <=, >, >= • Logische Operatoren: NOT, AND, OR Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 8/12 • Sonstige Operatoren • IN, NOT IN, BETWEEN, EXISTS • SELECT * FROM employees WHERE department_id in (10, 20, 90); • SELECT * FROM employees WHERE salary BETWEEN 5000 and 6000; • SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Administration'); Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 9/12 • Null Werte • IS NULL, IS NOT NULL • SELECT last_name FROM employeesWHERE department_id IS NULL; • LIKE – Pattern-Matching • _, %, Escapeclause_ matched ein einzelnes Zeichen% matched beliebig viele ZeichenEscape Claus erlaubt die Verwendung obiger Zeichen • SELECT * FROMemployeesWHERE job_id LIKE 'AC\_%' ESCAPE'\'andsalary BETWEEN 5000 and6000; Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 10/12 • Sortieren der Ergebniszeilen • SQL> SELECT first_name || ' ' || last_name "Mitarbeiter Name" FROM employeesWHERE department_id=90ORDER BY last_name; Mitarbeiter Name --------------------- Lex De Haan Steven King Neena Kochar Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 11/12 • Sortieren der Ergebniszeilen mit DISTINCT • SQL> SELECT DISTINCT 'Region ' || region_id FROM hr.countries ORDER BY region_id;ERROR ORA-01791: not a SELECTed expression • SQL> SELECT DISTINCT 'Region ' || region_id FROM hr.countries ORDER BY 'Region ' || region_id; 'Region ' || region_id ------------------------- Region 1 Region 2 Region 3 Region 4 Dipl.-Ing. Walter Sabin -- 2006
Einfache Abfragen – SELECT 12/12 • Expressions verwenden z.B.: in SELECT Klausel • SELECT ((2*4)/(3+1))*10 FROM dual; • CASE Expression - "if .. then .. else" Logik • SELECT country_name, CASE region_id WHEN 1 THEN 'Europa' WHEN 2 THEN 'AMERIKA'WHEN 3 THEN 'Asien' ELSE 'Anderes' END "Kontinent"FROM countries WHERE country_name LIKE 'I%'; COUNTRY_NAME Kontinent Israel Anderes India Asien Italy Europa Dipl.-Ing. Walter Sabin -- 2006
Quiz ? Dipl.-Ing. Walter Sabin -- 2006
Zusammenfassung • Geschichte von SQL. • Elementare Datentypen und Operatoren. • Einfache Abfragebefehle mit • SELECT, FROM, WHERE und ORDER BY • Operatoren in der WHERE Klause • Pattern-Matching Operatoren Dipl.-Ing. Walter Sabin -- 2006
Jemand versucht den Wert: 34567.2255 in ein Feld mit dem Datentyp number(7,2) zu speichern. Welcher Wert wird tatsächlich gespeichert? • A – 34567.00 • B – 34567.23 • C – 34567.22 • D – 3456.22 Dipl.-Ing. Walter Sabin -- 2006
Welche Standardanzeigelänge hat ein Datumsfeld? • A – 8 • B – 19 • C – 9 • D – 6 Dipl.-Ing. Walter Sabin -- 2006
Gegeben ist die folgende Abfrage:SELECT 'Mitarbeiter Name: ' || ename FROM empwheredeptno=10;Welche Komponente ist ein Literal? • A – 10 • B – ename • C – emp • D – || Dipl.-Ing. Walter Sabin -- 2006
Welche Klause in einer Abfrage schränkt die Anzahl der Datensätze ein? • A – ORDER BY • B – SELECT • C – FROM • D – WHERE Dipl.-Ing. Walter Sabin -- 2006
Gegeben ist die folgende Abfrage:SELECT empno, ename FROM empwhereempno=7782 orempno=7876; Welcher Operator kann die "OR Bedingung" ersetzen? • A – IN • B – BETWEEN .. AND .. • C – LIKE • D – <= Dipl.-Ing. Walter Sabin -- 2006
Jemand versucht den Wert: 12345678 in ein Feld mit dem Datentyp number(5,-2) zu speichern. Welcher Wert wird tatsächlich gespeichert? • A – 12345600 • B – 123456.78 • C – Fehler • D – 123456 Dipl.-Ing. Walter Sabin -- 2006
Folgende Klauseln können in SQL Select statements vorkommen:1. WHERE2. FROM3. ORDER BYIn welcher Reihenfolge treten sie auf? • A – 1,3,2 • B – 2,1,3 • C – 2,3,1 • D – Die Reihenfolge ist gleichgültig Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN • „Single Row“ - Funktionen • Grundlagen • Zeichen Funktionen • Numerische Funktionen • Datums – Funktionen • Konvertierungsfunktionen • Sonstige Funktionen • „Group“ – Funktionen • Grundlagen Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 1/ 16 • Verschiedene Datentype als Argumente • Auch in PL/SQL verwendbar • Verwendbar unter anderem in SELECT, WHERE und ORDER BY Klauseln • Z.B.: • SELECT last_name, TO_CHAR(hire_date, 'Day, DD-MON-YYYY')FROM employees WHERE UPPER(last_name) like 'AL%'ORDER BY soundex(last_name); Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 2/16 • NULL Werte Funktionen • NVL(Ausdruck1, Ausdruck2)SELECT last_name, salary salary*commission_pct bonus,(salary+salary*NVL(commission_pct,0)) GehaltFROM employees, • NVL2(Ausdruck1, Ausdruck2, Ausdruck3)NVL2(commission_pct, salary+salary* commission_pct, salary) Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 3/16 • Zeichenfunktionen • ASCII(c1) • liefert den Ascii Wert des ersten Zeichens in c1 • SELECT ASCII('A') GR_A, ASCII('z') KL_Z FROM dual;GR_A KL_Z 65 122 • CHR(i) • Liefert das Zeichen entsprechend dem Wert iSELECT CHR(65), chr(122), chr(223) FROM dual; Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 4/ 16 • Zeichenfunktionen (Fs) • INITCAP(c1) • liefert den ersten Buchstaben jedes Wortes in c1 als Großbuchstaben und alle anderen als Kleinbuchstaben • SELECT INITCAP('die drei lauser') FROM dual;=> Die Drei Lauser • LENGTH(c) • Liefert Länge einer Zeichenkette in Zeichen • SELECT LENGTH('Die Spengergasse') FROM DUAL;=> 16 Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 5/16 • Zeichenfunktionen (Fs) • INSTR(c1, c2[,i [,j]]) • Liefert die Zeichenposition für das j-te Vorkommen von c2 in c1, beginnend mit Position i. Negatives i bedeutet Suche von rechts (sonst von links) • SELECT INSTR('Mississippi','i',-2,3) FROM dual;=> 2 • SUBSTR(c1, i[,j]) • liefert einen Teilstring aus c1 beginnend an der Position i von j Zeichen. Ist j < 0 => Rest des Strings. Ist í < 0 => Zählen der Position von rechts. • SELECT SUBSTR('Die Spenger Gasse',5,7) FROM dual;=> Spenger Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 6/ 16 • Zeichenfunktionen (Fs) • LOWER(c1) • Kovertiert alle Zeichen auf Kleinbuchstaben • UPPER(c1) • Konvertiert alle Zeichen auf Großbuchstaben • SELECT LOWER(job_id), last_name FROM employees WHERE UPPER(last_name) LIKE 'KIN%';=> ad_pres King Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 7/ 16 • Zeichenfunktionen (Fs) • LPAD(c1,i[,c2]) • Erweitert den String c1 auf i Zeichen. Verwendet c2 um den freien Raum links aufzufüllen • SELECT LPAD(job_id,10,'.') FROM employees=> ...AD_PRES usw. • RPAD(c1,i[,c2]) • Erweitert den String c1 auf i Zeichen. Verwendet c2 um den freien Raum rechts aufzufüllen • SELECT RPAD(job_id,10,'.') FROM employees=> AD_PRES... usw. Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 8/ 16 • Zeichenfunktionen (Fs) • LTRIM(c1, c2) • Diese Funktion liefert c1 ohne die führenden Zeichen aus c2. Default: ' ' • SELECT LTRIM('Mississippi', 'Mis') FROM dual;=>ppi • RTRIM(c1,c2) • Diese Funktion liefert c1 ohne die nachlaufenden Zeichen aus c2. Default: ' ' Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 9/ 16 • Zeichenfunktionen (Fs) • REPLACE(c1,c2[c3]) • Liefert c1 wobei alle Strings c2 durch c3 ersezt werden • SELECT REPLACE('uptown','up','down') FROM dual;=> downtown • SOUNDEX(c1) • Liefert die phonetische Darstellung von C1 • SELECT SOUNDEX('Dawes' from dual);=> D200 Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 10/ 16 • Zeichenfunktionen (Fs) • TRANSLATE(c1,c2,c3) • Liefert c1 wobei alle in c1 vorkommenden Zeichen aus c2 durch die der Position in c2 entsprechenden Zeichen aus c3 ersetzt werden. • SELECT TRANSLATE('Mississippi','Mis','mIS') FROM dual;=> mISSISSIppI • SELECT translate(KUNDEN.RORT,'ÄÖÜäöüß',chr(142) || chr(153) || chr(154) || chr(132) || chr(148) || chr(129) || chr(225)) RORT FROM KUNDENwhere knr=882; Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 11/ 16 • Numerische Funktionen • ABS(n) – Absolutbetrag von n • COS, SIN, TAN, ATAN, ASIN, ACOS COSH, SINH • Winkelfunktionen • CEIL(n) – Kleinster ganzzahliger Wert größer oder gleich n • SELECT CEIL(9.8), CEIL(-32.85)from dual;=> 10 -32 • FLOOR(n) Größter ganzzahliger Wert kleiner oder gleich n • SELECT FLOOR(9.8), FLOOR(-32.85)from dual;=> 9 -33 Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 12/ 16 • Numerische Funktionen (Fs) • LN(n) – Natürlicher Logarithmus von n • SELECT LN(2.7)FROM dual;=> 0,993251773010283 • LOG(n1,n2) • Liefert den Logarithmus von n1 zur Basis n2 • SELECT LOG(27,3)FROM dual;=> 0,333333333333333 • MOD(n1, n2) • Liefert n1 modulo n2 • SELECT MOD(14,5) FROM dual;=> 4 Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 13/ 16 • Numerische Funktionen (Fs) • POWER(n1,n2) – Liefert n1 hoch n2 • SQRT(n) – Quadratwurzel aus n • ROUND(n1, n2) – Liefert n1 gerundet auf n2 Stellen • SIGN(n) – liefert 1 falls n pos.-1 falls negativ, 0 wenn 0 • TRUNC(n1, n2) – Liefert n1 auf n2 Stellen abgeschnitten Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 14/ 16 • Datumsfunktionen • ADD_MONTHS(d,i) • Addiert i Monate zu Datum d • MONTHS_BETWEEN(d1, d2) • Liefert die Anzahl der Monate zwischen d1 und d2 • SELECT MONTHS_BETWEEN('19.12.2002','19.03.2002') test from dual;=> 9 • LAST_DAY(d) • Liefert den letzten Tag des Monats für das Datum d • SELECT LAST_DAY(SYSDATE), LAST_DAY(SYSDATE) + 1 from dual; Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 15/16 • Datumsfunktionen (Fs) • EXTRACT(c FROM d)Liefert die durch c angegebene Komponente von d.c: YEAR, MONTH, DAY,HOUR, MIN, SECOND • SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;=> 1 • TRUNC(d[,fmt])Liefert ein Datum abgeschnitten je nach fmt. • SELECT TRUNC(last_analysed,'HH') FROM user_tables WHERE table_name='TEST_CASE';=> 10-Jan-2003 11:00:00 Dipl.-Ing. Walter Sabin -- 2006
FUNKTIONEN Single Row 16/16 • Conversionsfunktionen • TO_CHAR(x[,fmt]) – x:Datum oder Zahl, fmt: Formatcode • SELECT to_char(SYSDATE,'DD-MM-YYYY HH:MI'), to_char(12.3,'0009.90')=> 13-01-2003 19:35 0012.30 • TO_NUMBER(c[,fmt]) – Liefert Zahl aus String • TO_DATE(c[,fmt]) – Liefert Datum aus String • Sonstige Funktionen • DECODE(x,m1,r1,m2,r2,....,d) • SELECT DECODE(command,0,'None',2,'Insert',3,'Select'...,'Other') cmd from v$session where type <> 'BACKGROUND'; • LEAST(exp_list), GREATEST(exp_list) Dipl.-Ing. Walter Sabin -- 2006