Hvordan du lager en tabellvisning med SQL-dummies

Til tider vil du hente spesifikk informasjon fra KUNDE-tabellen i SQL. Du vil ikke se på alt - bare bestemte kolonner og rader. Det du trenger er en visning.

A visning er et virtuelt bord. I de fleste implementeringer har en visning ingen uavhengig fysisk eksistens. Visningsdefinisjonen finnes bare i databasens metadata, men dataene kommer fra tabellen eller tabellene hvorfra du får visningen. Utsikten er ikke fysisk duplisert.

Enkelt tabellvisning

Hvis informasjonen du vil ha finnes i et enkelt bord, kan du opprette en enkelt tabellvisning av dataene. For eksempel, anta at du vil se på navnene og telefonnumrene til alle kunder som bor i delstaten New Hampshire. Du kan opprette en visning fra KUNDE-tabellen som bare inneholder de dataene du vil ha. Følgende SQL-setning lager denne visningen:

CREATE VIEW NH_CUST AS SELECT KUNDE. Fornavn, KUNDE. LastName, KUNDE. Telefon FRA KUNDEN HVOR KUNDE. Stat = 'NH';

Du kan oppnå samme oppgave med mindre å skrive hvis SQL-implementeringen din antar at alle tabellreferanser er de samme som i FRA-klausulen. Hvis systemet gjør det fornuftig standardforutsetning, kan du redusere setningen til følgende linjer:

CREATE VIEW NH_CUST AS SELECT Fornavn, Etternavn, Telefon FRA KUNDEN HVOR STATEN = 'NH';

Selv om den andre versjonen er lettere å skrive og lese, er den mer sårbar for forstyrrelser fra ALTER TABLE-kommandoer. Slike forstyrrelser er ikke et problem for dette enkle tilfellet, som ikke har noe med, men synspunkter med JOIN er mer robuste når de bruker fullt kvalifiserte navn.

Flersidig visning

Oftere enn ikke, må du trekke data fra to eller flere tabeller for å svare på spørsmålet ditt. Anta for eksempel at du jobber for en sportsbutikk, og du vil sende en salgsfremmende epost til alle kundene som har kjøpt skiutstyr siden butikken åpnet i fjor.

Du trenger informasjon fra KUNDE-tabellen, PRODUKT-tabellen, INVOICE-tabellen og INVOICE_LINE-tabellen. Du kan opprette en flersidig visning som viser dataene du trenger. Hver gang du bruker visningen, gjenspeiler den eventuelle endringer som skjedde i de underliggende tabellene siden du sist brukte visningen.

Databasen for denne sportsbutikken inneholder fire tabeller: KUNDE, PRODUKT, FAKTUR, og INVOICE_LINE.

Tabell	Kolonne	Datatype	Begrensning
KUNDE	KundID	INTEGER	IKKE NULL
Fornavn	CHAR (15)
LastName < CHAR (20)	IKKE NULL	Gate
CHAR (25)	By
CHAR (20)	Stat
CHAR (2)	Postnummer > CHAR (10)
Telefon	CHAR (13)
PRODUKT	ProduktID
INTEGER	IKKE NULL	Navn	CHAR (25)
Beskrivelse > CHAR (30)	Kategori
CHAR (15)	VendorID
INTEGER	Leverandørnavn
CHAR (30)	FAKTA
FakturaNumber	INTEGER
IKKE NULL	KundID	INTEGER	FakturaDate
DATO	TotalSale
NUMERISK (9, 2)	TotaltRemittert
NUMERISK (9, 2)	FormOfPayment > CHAR (10)
INVOICE_LINE	Linjenummer
INTEGER	IKKE NULL
FakturaNumber	INTEGER	IKKE NULL	ProductID
INTEGER	IKKE NULL > Mengde	INTEGER
Salgspris	NUMERISK (9, 2)	Merk at noen av kolonnene inneholder begrensningen IKKE NULL.Disse kolonnene er enten primærnøklene til de respektive tabellene eller kolonnene du bestemmer
må	inneholde en verdi. En tabellens primærnøkkel må unike identifisere hver rad. For å gjøre det må primærnøkkelen inneholde en ikke-null-verdi i hver rad.
Tabellene relaterer seg til hverandre gjennom de kolonnene de har til felles. Følgende liste beskriver disse forholdene:	KUNDE-tabellen har et

ett til mange forhold i INVOICE-tabellen. En kunde kan gjøre flere kjøp, generere flere fakturaer. Hver faktura handler imidlertid med en, og kun en, kunde. INVOICE-tabellen har et til mange forhold til INVOICE_LINE-tabellen. En faktura kan ha flere linjer, men hver linje vises på en og kun én, faktura.

PRODUKT-tabellen har også en til mange forhold til INVOICE_LINE-tabellen. Et produkt kan vises på mer enn én linje på en eller flere fakturaer. Hver linje omhandler imidlertid ett, og bare ett, produkt.

KUNDE-tabellen kobler til INVOICE-tabellen ved den felles CustomerID-kolonnen. FAKTUR-tabellen kobler til INVOICE_LINE-tabellen med den vanlige kolonnen InvoiceNumber. PRODUKT-tabellen kobler til INVOICE_LINE-tabellen av den vanlige ProductID-kolonnen. Disse koblingene er det som gjør denne databasen til en relasjonell database.
For å få tilgang til informasjonen om kunder som kjøpte skiutstyr, trenger du Fornavn, Etternavn, Gate, By, Stat og Postnummer fra KUNDE-tabellen; Kategori fra PRODUKT-tabellen; FakturaNummer fra INVOICE-tabellen; og LineNumber fra INVOICE_LINE-tabellen. Du kan opprette visningen du vil ha i etapper ved å bruke følgende påstander:
CREATE VIEW SKI_CUST1 AS SELECT Fornavn, Etternavn, Gate, By, Stat, Postnummer, FakturaNUMMER FRA KUNDE TILBAKE FAKTUR BRUKER (CustomerID); CREATE VIEW SKI_CUST2 AS SELECT Fornavn, Etternavn, Gate, By, Stat, Postnummer, ProduktID FRA SKI_CUST1 BLI MED INVOICE_LINE BRUKER (FakturaNumber); CREATE VIEW SKI_CUST3 AS SELECT Fornavn, Etternavn, Gate, By, Stat, Postnummer, Kategori FRA SKI_CUST2 BLI MED PRODUKT BRUKER (ProductID); CREATE VIEW SKI_CUST AS SELECT DISTINCT Fornavn, Etternavn, Gate, By, Stat, Postnummer FRA SKI_CUST3 WHERE CATEGORY = 'Ski';

Disse CREATE VIEW-setningene kombinerer data fra flere tabeller ved hjelp av JOIN-operatøren. Her er en oversikt over de fire CREATE VIEW-setningene: Den første setningen kombinerer kolonner fra KUNDE-tabellen med en kolonne i INVOICE-tabellen for å lage SKI_CUST1-visningen.

Den andre setningen kombinerer SKI_CUST1 med en kolonne fra INVOICE_LINE-tabellen for å opprette SKI_CUST2-visningen.

Den tredje setningen kombinerer SKI_CUST2 med en kolonne fra PRODUCT-tabellen for å lage SKI_CUST3-visningen.

Den fjerde setningen filtrerer ut alle rader som ikke har en kategori av Ski. Resultatet er en visning (SKI_CUST) som inneholder navnene og adressene til alle kunder som kjøpte minst ett produkt i Ski-kategorien.

DISTINCT-søkeordet i den fjerde CREATE VIEW SELECT-klausulen sikrer at du kun har én oppføring for hver kunde, selv om enkelte kunder har gjort flere kjøp av skiartikler.