Die SQL-Datenmanipulationssprache

Die Datenmanipulationssprache SQL
•
Daten eingeben
•
•
Daten ändern
Datenbank-Inhalte aus Dateien laden
DBS1 2004
SQL-DML
Seite 1
Klöditz
Hochschule Anhalt (FH)
Data Manipulation Language
•
A DML statement is executed when you
– Add new rows to a table
– Modify existing rows in a table
– Remove existing rows from a table
Klöditz
Hochschule Anhalt (FH)
DBS1 2004
SQL-DML
Seite 2
Adding a New Row to a Table
50 DEVELOPMENT DETROIT
New row
“…insert a new row
“…insert
into DEPT table…”
DEPT
DEPTNO
-----10
20
30
40
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
DEPT
DEPTNO
-----10
20
30
40
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
50 DEVELOPMENT DETROIT
DBS1 2004
SQL-DML
Seite 3
Klöditz
Hochschule Anhalt (FH)
The INSERT Statement
•
Add new rows to a table by using the INSERT statement
•
Only one row is inserted at a time with this syntax
INSERT INTO
VALUES
Klöditz
Hochschule Anhalt (FH)
table [(column [, column...])]
(value [, value...]);
DBS1 2004
SQL-DML
Seite 4
Inserting New Rows
•
Insert a new row containing values for each column
•
•
•
Optionally list the columns in the INSERT clause
List values in the default order of the columns in the table
Enclose character and date values within single quotation marks
SQL> INSERT INTO
2 VALUES
1 row created.
dept (deptno, dname, loc)
(50, 'DEVELOPMENT', 'DETROIT');
DBS1 2004
SQL-DML
Seite 5
Klöditz
Hochschule Anhalt (FH)
Inserting Rows with Null Values
•
Implicit method: Omit the column from the column list
SQL> INSERT INTO
2 VALUES
1 row created.
•
dept (deptno, dname )
(60, 'MIS');
Explicit method: Specify the NULL keyword
SQL> INSERT INTO
2 VALUES
1 row created.
Klöditz
Hochschule Anhalt (FH)
dept
(70, 'FINANCE', NULL);
DBS1 2004
SQL-DML
Seite 6
Inserting Special Values
•
The SYSDATE function records the current date and time
SQL> INSERT INTO
2
3
4 VALUES
5
6
1 row created.
emp (empno, ename, job,
mgr, hiredate, sal, comm,
deptno)
(7196, 'GREEN', 'SALESMAN',
7782, SYSDATE, 2000, NULL,
10);
DBS1 2004
SQL-DML
Seite 7
Klöditz
Hochschule Anhalt (FH)
Inserting Specific Date Values
•
Add a new employee
SQL> INSERT INTO
2 VALUES
3
4
1 row created.
•
emp
(2296,'AROMANO','SALESMAN',7782,
TO_DATE('FEB 3,97', 'MON DD, YY'),
1300, NULL, 10);
Verify your addition
EMPNO ENAME
JOB
MGR
HIREDATE SAL COMM DEPTNO
----- ------- -------- ---- --------- ---- ----- ----2296 AROMANO SALESMAN 7782 03-FEB-97 1300
10
Klöditz
Hochschule Anhalt (FH)
DBS1 2004
SQL-DML
Seite 8
Copying Rows from Another Table
•
Write your INSERT statement with a subquery
•
•
Do not use the VALUES clause
Match the number of columns in the INSERT clause to those in
the subquery
SQL> INSERT INTO managers(id, name, salary, hiredate)
2
SELECT empno, ename, sal, hiredate
3
FROM
emp
4
WHERE job = 'MANAGER';
3 rows created.
DBS1 2004
SQL-DML
Seite 9
Klöditz
Hochschule Anhalt (FH)
Changing Data in a Table
EMP
EMPNO ENAME
7839
7698
7782
7566
...
KING
BLAKE
CLARK
JONES
JOB
...
DEPTNO
PRESIDENT
MANAGER
MANAGER
MANAGER
10
30
10
20
“…update a row
“…update
in EMP table…”
EMP
EMPNO ENAME
7839
7698
7782
7566
...
Klöditz
Hochschule Anhalt (FH)
KING
BLAKE
CLARK
JONES
JOB
PRESIDENT
MANAGER
MANAGER
MANAGER
...
DEPTNO
10
30
20
10
20
DBS1 2004
SQL-DML
Seite 10
The UPDATE Statement
•
Modify existing rows with the UPDATE statement
•
Update more than one row at a time, if required
UPDATE
SET
[WHERE
table
column = value [, column = value]
condition];
DBS1 2004
SQL-DML
Seite 11
Klöditz
Hochschule Anhalt (FH)
Updating Rows in a Table
•
Specific row or rows are modified when you specify the WHERE
clause
SQL> UPDATE emp
2 SET
deptno = 20
3 WHERE
empno = 7782;
1 row updated.
•
All rows in the table are modified if you omit the WHERE clause
SQL> UPDATE employee
2 SET
deptno = 20;
14 rows updated.
Klöditz
Hochschule Anhalt (FH)
DBS1 2004
SQL-DML
Seite 12
Updating with Multiple -Column Subquery
•
Update employee 7698’s job and department to match that of
employee 7499
SQL> UPDATE emp
2 SET
(job, deptno) =
3
(SELECT job, deptno
4
FROM
emp
5
WHERE
empno = 7499)
6 WHERE
empno = 7698;
1 row updated.
DBS1 2004
SQL-DML
Seite 13
Klöditz
Hochschule Anhalt (FH)
Updating Rows Based on Another Table
•
Use subqueries in UPDATE statements to update rows in a
table based on values from another table
SQL> UPDATE employee
2 SET
deptno =
3
4
5 WHERE
job
=
6
7
2 rows updated.
Klöditz
Hochschule Anhalt (FH)
(SELECT
FROM
WHERE
(SELECT
FROM
WHERE
deptno
emp
empno = 7788)
job
emp
empno = 7788);
DBS1 2004
SQL-DML
Seite 14
Updating Rows: Integrity Constraint Error
•
Department number 55 does not exist
SQL> UPDATE
2 SET
3 WHERE
emp
deptno = 55
deptno = 10;
UPDATE emp
*
ERROR at line 1:
ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK)
violated - parent key not found
DBS1 2004
SQL-DML
Seite 15
Klöditz
Hochschule Anhalt (FH)
Removing a Row from a Table
DEPT
DEPTNO
-----10
20
30
40
50
60
...
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
DEVELOPMENT DETROIT
MIS
Klöditz
Hochschule Anhalt (FH)
“…delete a row
“…delete
from DEPT table…”
DEPT
DEPTNO
-----10
20
30
40
60
...
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
MIS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
DBS1 2004
SQL-DML
Seite 16
The DELETE Statement
•
You can remove existing rows from a table by using the
DELETE statement
DELETE [FROM]
[WHERE
table
condition];
DBS1 2004
SQL-DML
Seite 17
Klöditz
Hochschule Anhalt (FH)
Deleting Rows from a Table
•
Specific row or rows are deleted when you specify the WHERE
clause
SQL> DELETE FROM
2 WHERE
1 row deleted.
•
department
dname = 'DEVELOPMENT';
All rows in the table are deleted if you omit the WHERE clause
SQL> DELETE FROM
4 rows deleted.
Klöditz
Hochschule Anhalt (FH)
department;
DBS1 2004
SQL-DML
Seite 18
Deleting Rows Based on Another Table
•
Use subqueries in DELETE statements to remove rows from a
table based on values from another table
SQL> DELETE FROM
2 WHERE
3
4
5
6 rows deleted.
employee
deptno =
(SELECT
FROM
WHERE
deptno
dept
dname ='SALES');
DBS1 2004
SQL-DML
Seite 19
Klöditz
Hochschule Anhalt (FH)
Deleting Rows: Integrity Constraint Error
•
You cannot delete a row that contains a primary key that is used
as a foreign key in another table
SQL> DELETE FROM
2 WHERE
dept
deptno = 10;
DELETE FROM dept
*
ERROR at line 1:
ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK)
violated - child record found
Klöditz
Hochschule Anhalt (FH)
DBS1 2004
SQL-DML
Seite 20
Datenbank-Inhalte
aus anderen Oracle-Datenbanken laden
•
Importprogramm IMP
übernimmt von Oracle exportierte Daten
einschl. Tabellenbeschreibung, Constraints, Rechten usw.
•
Voraussetzung:
mit EXP exportierte Dateien (Verträglichkeit der Versionen
beachten!)
DBS1 2004
SQL-DML
Seite 21
Klöditz
Hochschule Anhalt (FH)
Datenbank-Inhalte
aus sequentiellen Dateien laden
•
•
•
•
Ladeprogramm SQLLDR
übernimmt Datensätze mit durch Trennzeichen getrennten
Datenwerten in Oracle-Datenbanken unter Steuerung eines ControlFiles
Voraussetzung:
Datenbank-Tabellen müssen vorher definiert sein; nur Daten werden
übernommen
Steuerfile beschreibt Zuordnung von Datei-Daten zu Tabellenspalten
Beispiel:
LOAD DATA
INFILE *
INTO TABLE bibliothek.leser
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
(vorname,name,str,plz,ort,lnr)
BEGINDATA
Thomas;Achtzehn; Turmstr. 21d;06124;Halle;22180533
Thomas;Ackermann;Thomas-Müntzer-Str. 16;06406;Bernburg-Strenzfeld;12032021
...
Klöditz
Hochschule Anhalt (FH)
DBS1 2004
SQL-DML
Seite 22
Summary
Statement
Description
INSERT
Adds a new row to the table
UPDATE
Modifies existing rows in the table
DELETE
Removes existing rows from the table
IMP / EXP
Import aus anderen Oracle-Datenbanken
SQL*Loader
Laden aus sequentiellen Dateien
Klöditz
Hochschule Anhalt (FH)
DBS1 2004
SQL-DML
Seite 23