Transcript Relationsdatabaser og SQL
Relationsdatabaser og SQL
Del 2 af 4: Data Definition Language (DDL) i SQL Aalborg Universitet, d. 4. september 2006 Bent Møller Madsen
Tegn-forklaring
Denne skrifttype STORE BOGSTAVER er lig kode er lig Oracle kommandoer
[tekst] betyder at tekst er valgfri og kan udelades. De firkantede parenteser skal aldrig skrives i koden.
{tekst1|tekst2} betyder at en af teksterne adskilt af de lodrette streger skal vælges. De krøllede parenteser skal aldrig skrives i koden.
Elementer i en tabel
Postnumre Postnr Bynavn Postnumre Postnr Bynavn
9000 9760 Aalborg Vrå 8000 4180 7430 Århus C Sorø Ikast 9000 9760 8000 4180 7430 Aalborg Vrå Århus C Sorø Ikast
SQL til tabelstruktur
Data Definition Language (DDL) CREATE Opret tabel ALTER DROP ( DESCRIBE ( RENAME Ændre tabel Nedlæg tabel Beskriv tabel) Omdøb tabel) Vedrører tabelstrukturen ikke de faktiske data i tabeller
Postnumre Postnr Bynavn
Oprettelse af tabel
CREATE TABLE
kunder
(
kunde_id
NUMBER(10),
kunde
VARCHAR2(20), maks. længde
adresse postnr
VARCHAR2(50), NUMBER(4) DEFAULT 9000 ); datatype standardværdi
Tabel- og kolonnenavn
Tabel og kolonnenavne skal: begynde med et bogstav bestå af mellem 1 og 30 karakterer • lovlige karakterer: A-Z, 0-9, _, $, # • undgå æ, ø eller å være entydige / unikke • Ikke to tabeller af samme navn • Ikke to kolonner af samme navn i én tabel ikke være et reserveret ord, fx kommandoer ( SELECT , TABLE , BY m.fl.) Der skal normalt ikke tage ikke hensyn til STORE og små bogstaver Hvis navnene skrives i dobbelt anførselstegn, kan næsten alle reglerne dog brydes!!!
Generelle datatyper
Tekst Tal Dato/tid Binære filer Lyd Billeder Dokumenter Boolsk type (Boolean) Sand, falsk eller null (tom) Findes ikke i Oracle (i stedet kan en almindelig tekstkolonne med disse værdier anvendes)
Oracle datatyper
CHAR(maks. længde) Fast karakterfelt med plads til maks. 2000 tegn VARCHAR2(maks. længde) Variabelt karakterfelt med plads til maks. 4000 tegn NUMBER[(præcision[,skala])] Tal, hvor præcision er antal tilladte cifre, mens skala er antal decimaler ud af det samlede antal cifre.
DATE Datofelt med præcision ned til 1 sekund TIMESTAMP Datofelt med præcision ned til brøkdele af sekunder
Oracle datatyper
CLOB Character Large Object Tekstfelt med plads til 4 GB BLOB Binary Large Object Binært felt med plads til 4 GB
Beskrive & slette tabeller m.m.
Beskrive én tabel DESCRIBE
Betingelser / constraints
Udover at datatypen afgrænser mulighederne for at indtaste data er der 5 typer betingelser, der kan knyttes på en eller flere kolonner i tabellerne for at sikre dataintegritet.
NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY …… REFERENCES …… CHECK Alle betingelser kan enten oprettes direkte på én kolonne eller selvstændigt med mulighed for at omfatte flere kolonner.
NULL / NOT NULL
NULL er lig en ukendt værdi Med en NOT NULL constraint på en kolonne bliver det et krav, at der altid skal være en værdi i kolonnen.
CREATE TABLE
kunder
(
kunde_id
NUMBER(10),
kunde postnr
VARCHAR2(20),
adresse
VARCHAR2(50)
[CONSTRAINT adresse_nn] NOT NULL
, NUMBER(4), Not null
[CONSTRAINT adresse_nn] CHECK (adresse IS NOT NULL)
);
UNIQUE
En UNIQUE constraint på en eller flere kolonner betyder, at værdierne i disse skal være unikke på tværs af alle rækker Der må godt forekomme tomme/null felter CREATE TABLE
kunder
(
kunde_id
NUMBER(10),
kunde
VARCHAR2(20)
[CONSTRAINT kunde_uk] UNIQUE
,
adresse postnr
VARCHAR2(50) NOT NULL, NUMBER(4), Unique (key)
[CONSTRAINT kunde_uk] UNIQUE (kunde)
);
Primærnøgle
En primærnøgle anvendes til unikt at identificerer hver række i en tabel Den kan bestå af flere kolonner (en sammensat primærnøgle) Krav til en primærnøgle: Der kan kun være én for hver tabel Alle data i primærnøglen skal være unikke Der må ikke forekomme tomme (null) felter Yderligere bør data være forholdsvis ”stabile” Der kan være flere kandidatnøgler, der opfylder ovenstående krav, til en primærnøgle.
Primærnøgle
Overvejelser omkring at anvende naturlige eller syntetiske/surrogat data til primærnøgler CREATE TABLE
kunder
(
kunde_id
NUMBER(10)
[CONSTRAINT kunder_pk] PRIMARY KEY
,
kunde
VARCHAR2(20) UNIQUE,
adresse postnr
VARCHAR2(50) NOT NULL, NUMBER(4), Primærnøgle
[CONSTRAINT kunder_pk] PRIMARY KEY (kunde_id)
);
Fremmednøgle
Fremmednøgler sikrer integriteten mellem tabeller Krav til en fremmednøgle: Fremmednøglen skal referere til en primær eller unik nøgle.
En fremmednøgle-værdi skal enten kunne findes i reference-tabellen eller være NULL.
En kolonnen med en fremmednøgle skal være samme datatype som kolonnen med den primære eller unikke nøgle i reference-tabellen.
En fremmednøgle kan referere til den tabel, som den selv er placeret i. (Rekursivt hierarki)
Fremmednøgle
ID
101 102 105 103
Kunder Kunde
Bilka Kvickly Spar Ikea
Postnr
9000 4180 5000
Postnumre Postnr
9000 9760 8000 4180 7430
Bynavn
Aalborg Vrå Århus C Sorø Ikast
ALTER TABLE kunder
ADD [CONSTRAINT
kunder_postnumre_fk
] FOREIGN KEY (
postnr
) REFERENCES postnumre (
postnr
) [ON DELETE {CASCADE|SET NULL}];
Fremmednøgle
CREATE TABLE
kunder
(
kunde_id
NUMBER(10) PRIMARY KEY,
kunde
VARCHAR2(20) UNIQUE,
adresse
VARCHAR2(50) NOT NULL,
postnr
NUMBER(4)
[CONSTRAINT kunder_postnumre_fk] REFERENCES postnumre (postnr) [ON DELETE
Fremmednøgle
{CASCADE|SET NULL}]
,
[CONSTRAINT kunder_postnumre_fk] FOREIGN KEY (postnr) REFERENCES postnumre (postnr) [ON DELETE {CASCADE|SET NULL}]
);
Fremmednøgle
Ingen ON DELETE regel på fremmednøglen Det er ikke tilladt at slette rækker fra tabellen som fremmednøglen refererer til (postnumre), hvis de anvendes i tabellen med fremmednøglen (kunder).
ON DELETE CASCADE regel på fremmednøglen Sletning af rækker fra tabellen, som fremmednøglen refererer til (postnumre), medføre at sammenhængende rækker i tabellen med fremmednøglen (kunder) også bliver slettet.
ON DELETE SET NULL regel på fremmednøglen Sletning af rækker fra tabellen, som fremmednøglen refererer til (postnumre), gør at værdier i kolonnen med fremmednøglen sættes til null for sammenhængende rækker.
CHECK
CHECK Constraints er fleksible betingelser, man selv definerer, som data skal opfylde.
CREATE TABLE
kunder
(
kunde_id
NUMBER(10) PRIMARY KEY, );
kunde
VARCHAR2(20) UNIQUE,
adresse
VARCHAR2(50) NOT NULL,
postnr
NUMBER(4) REFERENCES
postnumre
(
postnr
)
[CONSTRAINT postnr_laengde_cc] CHECK (LENGTH(postnr) = 4)
,
[CONSTRAINT postnr_laengde_cc] CHECK (LENGTH(postnr) = 4)
Check constraint
Ændre tabeller
ALTER TABLE ADD MODIFY DROP RENAME ENABLE/DISABLE - tilføje kolonne / constraint - ændre kolonne / constraint - slette en kolonne / constraint - omdøbe kolonne / constraint - deaktivere/aktivere constraint
Ændre tabel – kolonne eksempler
ALTER TABLE
kunder
ADD
tlf
( NUMBER(8),
VARCHAR2(30) ); ALTER TABLE
kunder
MODIFY
tlf
( VARCHAR2(15) NOT NULL,
VARCHAR2(50) ); ALTER TABLE
kunder
DROP
(
tlf
,
); ALTER TABLE
kunder
RENAME
COLUMN
tlf
TO
telefon
;
Ændre tabel – constraint eksempler
ALTER TABLE kunder
ADD
CONSTRAINT
email_cc
CHECK (email LIKE '%@%'); ALTER TABLE
kunder
MODIFY
CONSTRAINT ALTER TABLE
kunder email_cc
{ENABLE|DISABLE};
DROP
CONSTRAINT
email_cc
ALTER TABLE
kunder
;
RENAME
CONSTRAINT ALTER TABLE
kunder email_cc
{ENABLE|DISABLE}
TO CONSTRAINT
email_cc
; Samme resultat
email_check
;
Vis tabeller og constraints
Vis alle tabeller SELECT * FROM user_tables; Vis alle constraints SELECT * FROM user_cons_columns NATURAL JOIN user_constraints; Tømning af ”Papirkurv” Hvis der optræder navne lignende følgende i ovenstående forespørgsler • BIN$EO/cSFBIQqOsWWy9uhmgFQ==$0 så tøm Oracles papirkurv med denne kommando • PURGE RECYCLEBIN;