sql
Post on 04-Jan-2016
40 Views
Preview:
DESCRIPTION
TRANSCRIPT
SQL HiA
SQL
SQL
Standard databasespråk
for
relasjons-databaser
SQL HiA
SQL
• SQL (Structured Query Language, uttales: sequel eller ess-que-ell)er et komplett sett av kommandoer for aksess til en relasjons-database.
• SQL er i dag standard database-språk for relasjons-databaser.
• SQL benyttes til:- Opprette datase-tabeller- Lagre data- Gjenfinne data- Endre data- Endre tabell-strukturer- Kombinere og beregne data- Ta hånd om database-sikkerhet
• SQL er mengde-orientert, dvs kan anvendes på en gruppe av recordseller enkelt-record.
• SQL er ikke-prosedyrisk, men kan innebygges i prosedyre-språk.
SQL HiA
Historikk
• 1970 Codd beskrev en relasjons-algebrafor organisering av data inn i tabeller. “A Relational Model of Data for Large Shared Data Banks”.
• 1974 “SEQUEL: A structured English Query Language”.Oppfyller kravene i Codd’s relasjons-algebra.
• 1976 SEQUEL/2IBM prototype kalt System R.
• 1980 Navnet endres til SQL.ANSI-standard.
• 1997 SQL m/objektorientering
SQL HiA
SQL Data-språk for alle ?
SQL var opprinnelig tenkt å skulle være
ET DATABASE-SPRÅK FOR ALLE
I praksis bygges SQL inn i et høgnivåspråkfor bl.a. å bedre brukergrensesnittet.
SQL HiA
Hva består en database av ?
• Selve databasen med lagrede data
• Data Dictionary (System-kataloger)med alle nødvendige opplysninger om databasens struktur
• DBMS - DataBase Management SystemDatabase software til operasjoner på databasen
SQL HiA
Database-system
Bruker
Bruker
Bruker
Appl.
Appl.
Appl.
DBMSDBMS
DDDD
DatabaseDatabase
SQL HiA
Database-system - Eksempler
Bruker
Bruker
Bruker
Appl.
Appl.
Appl.
DBMSDBMS
DDDD
DatabaseDatabase
Centura Team/Web Developer
SQLTalk
C++ SQL/API
SQLBaseSQLServerOracle
SQL HiA
Eksempel på innhold i en database
TablesTables
IndexesIndexes
TriggersTriggers
ViewsViews ProceduresProcedures
RulesRules
DatatypesDatatypes
DefaultsDefaults
Database
SQL HiA
Database / Tabell / Rad / Kolonne
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Database
Tabell
RadPostRecord
KolonneFelt
SQL HiA
Flerbrukersystem Client / Server
DatabaseDBMSDBMS
Application_2Application_2
SQL-Request
Data
Application_3Application_3
Application_1Application_1
Client Server
SQL HiA
Typer av SQL-kommandoer (1)
• Data Definition Commands ( DDL )
• Data Manipulation Commands ( DML )
• Data Query Commands ( DQL )
• Transaction Control Commands
• Data Administation Commands
• Data Control Commands
SQL HiA
Typer av SQL-kommandoer (2)
• Data Definition Commands ( DDL )CREATE EVENTCREATE INDEXCREATE SYNONYMCREATE TABLECREATE TRIGGERCREATE VIEWPROCEDURE
• Data Manipulation Commands ( DML )DELETEINSERTUPDATE
• Data Query Commands ( DQL )SELECT
SQL HiA
Typer av SQL-kommandoer (3)
• Transaction Control CommandsCOMMITROLLBACKSAVEPOINT
• Data Administration CommandsAUDIT MESSAGESTART AUDITSTOP AUDIT
SQL HiA
Typer av SQL-kommandoer (4)
• Data Control Commands (1)ALTER DATABASEALTER DBAREAALTER PASSWORDALTER STOGROUPALTER TABLEALTER TRIGGERCHECK DATABASECHECK TABLECOMMENT ON
SQL HiA
Typer av SQL-kommandoer (5)
• Data Control Commands (2)CREATE DATABASECREATE DBAREACREATE EVENTCREATE INDEXCREATE STOGROUPCREATE SYNONYMCREATE TABLECREATE TRIGGERCREATE VIEW
SQL HiA
Typer av SQL-kommandoer (6)
• Data Control Commands (3)DBATTRIBUTEDEINSTALL DATABASEDROP DBAREADROP EVENTDROP INDEXDROP STOGROUPDROP SYNONYMDROP TABLEDROP TRIGGERDROP VIEWGRANTGRANT EXECUTE ONINSTALL DATABASELABELLOADLOCK DATABASE
SQL HiA
Typer av SQL-kommandoer (7)
• Data Control Commands (4)REVOKEREVOKEEXECUTE ONROWCOUNTSET DEFAULT STOGROUPUNLOADUNLOCK DATABASEDUPDATE STATISTICS
SQL HiA
Bruksmåte
• Interaktivt vha et interface-program• Innebygget i et programmerings-språk
SQL HiA
Salg-tabell - 1NF
SNr Navn Sted PNr VNr Pris Mengde
5 Nilsen Bergen 5002 8 500 302 Olsen Molde 6400 1 200 202 Olsen Molde 6400 3 400 101 Hansen Tromsø 9000 5 300 501 Hansen Tromsø 9000 8 500 404 Berg Molde 6400 1 200 704 Berg Molde 6400 3 400 504 Berg Molde 6400 5 300 20
SNr Selger-nummerNavn Selger-navnSted Arbeids-sted for selgerPNr Post-nummer (for Sted)VNr Vare-nummerPris Vare-prisMengde Antall solgte vare-enheter
SQL HiA
Salg-tabeller - 3NF
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
ID Fremmed-nøkkel
SQL HiA
Salg-tabeller - 3NF
SelgerSelger AdrAdr VareVare
SalgSalg
SQL HiA
Database / Tabell / Rad / Kolonne
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Database
Tabell
RadPostRecord
KolonneFelt
SQL HiA
Partisjonering av en database
Step 1: Opprett DatabaseArea
Step 2: Opprett StorageGroup
Step 3: Opprett Database
Vol_1 Vol_2
Handel_2
Handel_1 Handel_3
StorageGroupHandel_Files
StorageGroupHandel_Log
Handel_2
Handel_1 Handel_3
Data Log filer
Handel_2
Handel_1
Handel_3
DatabasenHandel
SQL HiA
CREATE DBAREA
CREATE DBAREA dbarea name
AS filename
SIZE megabyte
Oppretter et fysisk database-område av gitt størrelse i megabyte (default 1MB)
CREATE DBAREA Handel_1 AS Vol_1:\Centura\Handel_1 SIZE 5
CREATE DBAREA Handel_2 AS Vol_1:\Centura\Handel_2 SIZE 10
CREATE DBAREA Handel_3 AS Vol_2:\Centura\Handel_3 SIZE 10
Vol_1 Vol_2
Handel_2
Handel_1 Handel_3
SQL HiA
CREATE STOGROUP
Oppretter en storage group
CREATE STOGROUP stogroup name
,
USING dbarea-name
CREATE STOGROUP Handel_Files USING Handel_1, Handel_2
CREATE STOGROUP Handel_Log USING Handel_3
StorageGroupHandel_Files
StorageGroupHandel_Log
Handel_2
Handel_1 Handel_3
SQL HiA
CREATE DATABASE
Oppretter en database
CREATE DATABASE databasenameIN stogroup nameLOG TO stogroup name
CREATE DATABASE HandelIN Handel_FilesLOG TO Handel_Log
Data Log filer
Handel_2
Handel1 Handel_3
Databasen Handel
SQL HiA
CREATE TABLE
Oppretter en tabell
CREATE TABLE <TableName>(<ColumnName> <DataType>[<Size>], <ColumnName> <DataType>[<Size>], ...)
Tabell_1Tabell_1 Tabell_2Tabell_2
Tabell_3Tabell_3
Database
SQL HiA
Oppretting av Salg-tabeller - Adr
CREATE TABLE Adr (PNr Integer NOT NULL,Sted Char(20),PRIMARY Key(PNr)
)
CREATE UNIQUE INDEX AdrNdx ON Adr(PNr)
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
Oppretting av Salg-tabeller - Selger
CREATE TABLE Selger (SNr Integer NOT NULL,
Navn Char(20),PNr Integer,
PRIMARY KEY(SNr), FOREIGN KEY(PNr)
REFERENCES Adr ON DELETE RESTRICT)
CREATE UNIQUE INDEX SlgNrNdx ON Selger(SNr)
CREATE INDEX SlgNaNdx ON Selger(Navn)
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
ID Fremmednøkkel
SQL HiA
Oppretting av Salg-tabeller - Vare
CREATE TABLE Vare (VNr Integer NOT NULL,
Pris Integer, PRIMARY Key(VNr))
CREATE UNIQUE INDEX VareNdx ON Vare(VNr)
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
SQL HiA
Oppretting av Salg-tabeller - Salg
CREATE TABLE Salg (SNr Integer NOT NULL,
VNr Integer NOT NULL,Mg Integer,
PRIMARY KEY(SNr,VNr), FOREIGN KEY(SNr)
REFERENCES Selger ON DELETE RESTRICT,FOREIGN KEY(VNr)REFERENCES Vare ON DELETE RESTRICT
)
CREATE UNIQUE INDEX SalgNdx ON Salg(SNr,VNr)
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Data-typer ( SQLBase )
Char(length) Max 254 tegnVarChar(length) Max 254 tegnLong VarChar Vilkårlig lengde, tekst og binære data
SmallInt [-32768, +32767] 5 sifferInteger [-2147483648, + 2147483647] 10 sifferDecimal[(prec, scale)] [-999....., + 999.....] 15 siffer
precision: Totalt ant siffer (5 default)scale: Ant desimaler (0 default)
Float[prec] Vilkårlig tall opp til 15 siffer totaltNumber Vilkårlig tall opp til 15 siffer totaltDateTime Format Day.Time
Day ant dager siden 10.12.1899Time = Frac part (0 = 12:00 AM)
TimeStamp Samme som DateTime (DB2 komp.)Date Time part = 0Time Date part = 0
SQL HiA
INSERT
Innsetter en rad i en tabellInnsetter en rad i en tabell
INSERT table nameview name ,
( column name )
VALUES ( constant )bind variable ADJUSTING cursor namesystem keyword
subselect
SQL HiA
INSERT - Eksempel
INSERT INTO AdrVALUES (5002,”Bergen”)
INSERT INTO AdrVALUES (6400,”Molde”)
INSERT INTO AdrVALUES (9000,”Tromsø”)
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
INSERT - Eksempel
INSERT INTO Adr (PNr,Sted)VALUES(:1,:2)
\$DATATYPES NUMERIC,CHARACTER5002,Bergen6400,Molde9000,Tromsø/
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
Innsetting av tabell-verdier - Adr
INSERT INTO AdrVALUES (5002,”Bergen”)
INSERT INTO AdrVALUES (6400,”Molde”)
INSERT INTO AdrVALUES (9000,”Tromsø”)
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
Innsetting av tabell-verdier - Adr
INSERT INTO Adr (PNr,Sted)VALUES(:1,:2)
\$DATATYPES NUMERIC,CHARACTER5002,Bergen6400,Molde9000,Tromsø/
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
Innsetting av tabell-verdier - Selger
INSERT INTO Selger (SNr,Navn,PNr)VALUES(:1,:2,:3)
\$DATATYPES NUMERIC,CHARACTER5,Nilsen,50022,Olsen,64001,Hansen,90004,Berg,6400/
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
SELECT
SELECT SelectItemALL ,DISTINCT *
FROM TableSpecification,
WHERE SearchCondition
GROUP BY GroupingColumn,
HAVING SearchCondition
ORDER BY SortSpecification,
SQL HiA
Select - Enkel bruk
SELECT SNr, Navn, PNrFROM Selger
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
Resultatsett
SQL HiA
Select Kvalifiserte kolonne-navn
SELECT Selger.SNr, Selger.Navn, Selger.PNrFROM Selger
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
SELECT - Bruk av *
SELECT *FROM Selger
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
SELECT - Enkelt-felter
SELECT PNr, NavnFROM Selger
PNr Navn
5002 Nilsen6400 Olsen9000 Hansen6400 Berg
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
SELECT - Distinct
SELECT DISTINCT SNrFROM Salg
SNr
1245
Hvis flere felter nevnes i Select-statementet, vil Distinct gjelde alle disse, dvs kun de raderhvor alle de nevnte feltene er like medfører sløyfing av duplikate rader.Distinct er hensiksmessig i tilknytning til sub-queries.
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
WHERE
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr = 6400
SNr Navn PNr
2 Olsen 64004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
WHERE
SELECT VNr, PrisFROM VareWHERE Pris > 300
VNr Pris
8 5003 400
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
SQL HiA
WHERE
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr = 6400 AND Navn > ‘C’
SNr Navn PNr
2 Olsen 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
WHERE
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr = 6400 OR SNr > 3
SNr Navn PNr
5 Nilsen 50022 Olsen 64004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
WHERE
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr = 6400 OR NOT SNr > 3
SNr Navn PNr
2 Olsen 64001 Hansen 90004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
SQL - 3-verdi logikk
A op BTrueFalseNULL
WHERE-test Ok ved TrueGROUP BY -test Ok ved True og NULL
AND True False NULL
True True False NULLFalse False False FalseNULL NULLFalse NULL
OR True False NULL
True True True TrueFalse True False NULLNULL True NULLNULL
NOT True False NULL
False True NULL
SQL HiA
IN
SELECT PNr, StedFROM AdrWHERE Sted IN (‘Molde’,Tromsø’)
PNr Sted
6400 Molde9000 Tromsø
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
BETWEEN
SELECT SNr, Navn, PNrFROM SelgerWHERE SNr BETWEEN 1 AND 4
SNr Navn PNr
2 Olsen 64001 Hansen 90004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Joker-symbol % og _
SELECT SNr, Navn, PNrFROM SelgerWHERE Navn LIKE ‘N%’
SNr Navn PNr
5 Nilsen 5002
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Joker-symbol % og _
SELECT SNr, Navn, PNrFROM SelgerWHERE Navn LIKE ‘%sen’
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 9000
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Joker-symbol % og _
SELECT SNr, Navn, PNrFROM SelgerWHERE Navn LIKE ‘Ni%sen’
SNr Navn PNr
5 Nilsen 5002
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Joker-symbol % og _
SELECT SNr, Navn, PNrFROM SelgerWHERE Navn LIKE ‘Ni_sen’
SNr Navn PNr
5 Nilsen 5002
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Joker-symbol % og _
SELECT SNr, Navn, PNrFROM SelgerWHERE Navn LIKE ‘Ni/_%’
SNr Navn PNrSNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Joker-symbol % og _
SELECT SNr, Navn, PNrFROM SelgerWHERE Navn LIKE ‘Ni//%’
SNr Navn PNrSNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
NULL
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr IS NULL
SNr Navn PNrSNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
NULL
SELECT SNr, Navn, PNrFROM SelgerWHERE PNr IS NOT NULL
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Aggregat-funksjoner - Sum
SELECT SUM(Mg)FROM Salg
Sum(Mg)
290
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Aggregat-funksjoner - Avg
SELECT AVG(Mg)FROM Salg
Avg(Mg)
36.25
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Aggregat-funksjoner - COUNT
SELECT COUNT(*)FROM Salg
Count(*)
8
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Aggregat-funksjoner Count Null-verdier i kolonne-funksjoner
SELECT COUNT(*), COUNT(Navn), COUNT(Adr)FROM Person
Navn Adr
Nilsen Storgt 3Havnegt 7
Hansen Gågata 20BergKnutsen Storgt 3Olsen Ekornv 4PersenMadsen Svingen 8
COUNT(*) COUNT(Navn)COUNT(Adr)
8 7 6
SQL HiA
Aggregat-funksjoner Count / Distinct
SELECT COUNT(Adr), COUNT(DISTINCT Adr), FROM Person
Navn Adr
Nilsen Storgt 3Havnegt 7
Hansen Gågata 20BergKnutsen Storgt 3Olsen Ekornv 4PersenMadsen Svingen 8
COUNT(Adr) COUNT(DISTINCT Adr)
6 5
SQL HiA
Aggregat-funksjoner - Count / Distinct
SELECT COUNT(DISTINCT Mg)FROM Salg
Count(Distinct Mg)
6
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Aggregat-funksjoner - Max
SELECT MAX(Mg)FROM Salg
Max(Mg)
70
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Aggregat-funksjoner - Min
SELECT MIN(Mg)FROM Salg
Min(Mg)
10
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Aggregat-funksjoner - Max
SELECT MAX(SNr+VNr)FROM Salg
Max(SNr+VNr)
13
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Group By
SELECT SNr, MAX(Mg)FROM SalgGROUP BY SNr
SNr Max(Mg)
1 502 204 705 30
SNr VNr Mengde
1 5 501 8 40
2 1 202 3 10
4 1 704 3 504 5 20
5 8 30
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Group By
SELECT SNr, VNr, MAX(Mg)FROM SalgGROUP BY SNr, VNr
SNr VNr Max(Mg)
1 5 501 8 402 1 202 3 104 1 704 3 504 5 205 8 30
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Group By / Having
SELECT SNr, VNr, MAX(Mg)FROM SalgGROUP BY SNr, VNrHAVING MAX(Mg) > 30
SNr VNr Max(Mg)
1 5 501 8 404 1 704 3 50
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Uttrykk
SELECT VNr, Pris*1.5FROM Vare
VNr Pris*1.5
8 7501 3003 6005 450
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
SQL HiA
Tekst-plassering i utlisting
SELECT VNr, Pris, ‘Kr’FROM Vare
VNr Pris ‘Kr’
8 500 Kr1 200 Kr3 400 Kr5 300 Kr
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
SQL HiA
Order By
SELECT SNr, Navn, PNrFROM SelgerORDER BY SNr
SNr Navn PNr
1 Hansen 90002 Olsen 64004 Berg 64005 Nilsen 5002
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Order By Asc / Desc
SELECT SNr, Navn, PNrFROM SelgerORDER BY PNr ASC, SNr DESC
SNr Navn PNr
5 Nilsen 50024 Berg 64002 Olsen 64001 Hansen 9000
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Order By / Group By
SELECT SNr, VNr, MAX(Mg)FROM SalgGROUP BY SNr, VNrORDER BY VNr
SNr VNr Max(Mengde)
2 1 204 1 702 3 104 3 504 5 201 5 505 8 201 8 40
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SQL HiA
Kolonne-nummer-sortering
SELECT SNr, Navn, PNrFROM SelgerORDER BY 3 DESC
SNr Navn PNr
1 Hansen 90002 Olsen 64004 Berg 64005 Nilsen 5002
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Join
SELECT Selger.Navn, Selger.PNr, Adr.StedFROM Selger, AdrWHERE Selger.PNr = Adr.PNrID
Navn PNr Sted
Hansen 9000 TromsøOlsen 6400 MoldeBerg 6400 MoldeNilsen 5002 Bergen
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SQL HiA
Join - Produkt-tabell
SNr Navn PNr PNr Sted
5 Nilsen 5002 5002 Bergen2 Olsen 6400 5002 Bergen1 Hansen 9000 5002 Bergen4 Berg 6400 5002 Bergen5 Nilsen 5002 6400 Molde2 Olsen 6400 6400 Molde1 Hansen 9000 6400 Molde4 Berg 6400 6400 Molde5 Nilsen 5002 9000 Tromsø2 Olsen 6400 9000 Tromsø1 Hansen 9000 9000 Tromsø4 Berg 6400 9000 Tromsø
Selger AdrSNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Selger.SNr, Selger.Navn, Selger.PNr Adr.Sted
FROM Selger, Adr
SQL HiA
Join
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Selger.Navn, Selger.PNr, Adr.Sted
FROM Selger, Adr
WHERE Selger.PNr = Adr.PNr
Feilkoblinger ( PNr) markert med rødt felt.Gjenstående korrekte poster i resultatsett markert med * .
SNr Navn PNr Sted
5 Nilsen 5002 Bergen2 Olsen 6400 Bergen1 Hansen 9000 Bergen4 Berg 6400 Bergen5 Nilsen 5002 Molde2 Olsen 6400 Molde1 Hansen 9000 Molde4 Berg 6400 Molde5 Nilsen 5002 Tromsø2 Olsen 6400 Tromsø1 Hansen 9000 Tromsø4 Berg 6400 Tromsø
Selger Adr
*
*
*
*
SQL HiA
Join
SELECT Selger.Navn, Adr.StedVare.VNr, Vare.PrisSalg.Mg
FROM Selger, Adr, Vare, SalgWHERE Selger.PNr = Adr.PNr AND
Salg.SNr = Selger.SNr ANDSalg.VNr = Vare.VNr
Navn Sted VNr Pris Mg
Nilsen Bergen 8 500 30Olsen Molde 1 200 20Olsen Molde 3 400 10Berg Molde 1 200 70Berg Molde 3 400 50Bergn Molde 5 300 20Hansen Tromsø 5 300 50Hansen Tromsø 8 500 40
SQL HiA
Join - Kobling av en tabell mot seg selv
SELECT First.Navn, Sec.Navn, First.PNrFROM Selger First, Selger SecWHERE First.PNr = Sec.PNr
First.Navn Sec.Navn First.PNr
Nilsen Nilsen 5002Olsen Olsen 6400Berg Olsen 6400Hansen Hansen 9000Olsen Berg 6400Berg Berg 6400
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Join - Kobling av en tabell mot seg selv (uten dublering)
SELECT First.Navn, Sec.Navn, First.PNrFROM Selger First, Selger SecWHERE First.PNr = Sec.PNr AND
First.Navn < Sec.Navn
First.Navn Sec.Navn First.PNr
Berg Olsen 6400SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Join - Kobling av en tabell mot seg selv
Nr Navn Sjef
105 Adams 112107 Smith 105110 Clark112 Cruz 107115 Jones 112
SELECT First.Navn, Sec.NavnFROM Tilsatt First, Tilsatt SecWHERE First.Sjef = Sec.Nr
Tilsatt
Navn Sjef
Smith AdamsCruz SmithAdams CruzJones Cruz
List ut alle tilsatte med tilhørende sjef
Nr Navn Sjef
105 Adams 112107 Smith 105110 Clark112 Cruz 107115 Jones 112
First
Nr Navn Sjef
105 Adams 112107 Smith 105110 Clark112 Cruz 107115 Jones 112
Sec
SQL HiA
Join uten Parent / Child relasjon
Navn TilsattDato
Adams 12.02.90Jones 17.10.91Smith 14.06.92Clark 21.10.92Cruz 04.01.93
OrdreNr Dato Belop
11305 05.07.89 100011296 12.02.90 200015320 15.04.92 150015900 12.02.90 720017541 04.07.93 3100
Tilsatt Ordre
SELECT Ordre.OrdreNrFROM Tilsatt, OrdreWHERE Ordre.Dato = Tilsatt.TilsattDato AND
Tilsatt.Navn = ‘Adams’
Lister ut alle ordre som ble ekspedert samme dato som Adams ble tilsatt.
OrdreNr
1129615900
Ordre
SQL HiA
Join m/NULL-verdier
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen4 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Navn, PNrFROM Selger
List selgere med tilhørende postnummer
Navn PNr
Nilsen 5002Olsen 6400HansenBerg 6400
SQL HiA
Join m/NULL-verdier
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen4 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Navn, StedFROM Selger, AdrWHERE Selger.PNr = Adr.PNr
List selgere med tilhørende postnummer
Navn Sted
Nilsen BergenOlsen MoldeBerg Molde
Hansen mangler
SQL HiA
Join - Outer Join ANSI standard
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen4 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Navn, StedFROM Selger, AdrWHERE Selger.PNr *= Adr.PNr
List selgere med tilhørende postnummer
Navn Sted
Nilsen BergenOlsen MoldeHansenBerg Molde
SQL HiA
Join - Outer join SQLBase
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen4 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Navn, StedFROM Selger, AdrWHERE Selger.PNr = Adr.PNr (+)
List selgere med tilhørende postnummer
Navn Sted
Nilsen BergenOlsen MoldeHansenBerg Molde
SQL HiA
Join - Outer join SQLBase NULLVALUE
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen4 Berg 6400
Selger
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr
SELECT Navn, @NULLVALUE(Sted,’Ukjent’)FROM Selger, AdrWHERE Selger.PNr = Adr.PNr (+)
List selgere med tilhørende postnummer
Navn Sted
Nilsen BergenOlsen MoldeHansen UkjentBerg Molde
SQL HiA
Join - Left / Right / Complete
1. Begynn med inner join med matchende kolonner2. For hver rad i den første tabellen som ikke matcher en rad i den andre tabellen
(pga NULL i første tabell), adder til en rad med verdier fra den første tabellenog NULL-verdier fra den andre tabellen.
3. For hver rad i den andre tabellen som ikke matcher en rad i den første tabellen(pga NULL-verdier i andre tabell), adder til en rad med verdier fra den andretabellen og NULL-verdier fra den første tabellen.
Outer join Step ANSI syntaks SQLBase
Left Step 1 + 2 c1 *= c2 c1 = c2 (+)Right Step 1 + 3 c1 =* c2 c1 (+) = c2Full Step 1 + 2 + 3 c1 *=* c2 c1 (+) = c2 (+)
SQL HiA
Multiple Query-nivåer
SELECT SNr, VNr, MgFROM SalgWHERE SNr =
( SELECT SNrFROM Selger
WHERE Navn = ‘Hansen’)
SNr VNr Mg
1 5 501 8 40
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Multiple Quer-nivåer - Join
SELECT SNr, VNr, MgFROM SalgWHERE SNr =
( SELECT SNrFROM Selger
WHERE Navn = ‘Hansen’)
SELECT Salg.SNr, Salg.VNr, Salg.MgFROM Salg, SelgerWHERE Selger.SNr = Salg.SNr AND
Selger.Navn = ‘Hansen’
MultippelQuery-nivå
JoinSNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
SQL HiA
Multiple Query-nivåer - EXISTS
SELECT Vare.VNr, Vare.PrisFROM VareWHERE EXISTS ( SELECT Salg.VNr
FROM SalgWHERE Salg.VNr = Vare.VNr )
List ut fra vare-tabellen vare-nummer inkludert pris for alle varene som er blitt solgt.
SNr VNr Mengde
5 8 302 1 202 3 101 5 501 8 404 1 704 3 504 5 20
Salg (ID = SNr + VNr)
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
VNr Pris
8 5001 2003 4005 300
Vare (ID = VNr)
SQL HiA
View
SNr Navn PNr
5 Nilsen 50022 Olsen 64001 Hansen 90004 Berg 6400
Selger (ID = SNr)
PNr Sted
5002 Bergen6400 Molde9000 Tromsø
Adr (ID = PNr)
SNr Navn PNr Sted
5 Nilsen 5002 Bergen2 Olsen 6400 Molde1 Hansen 9000 Tromsø4 Berg 6400 Molde
Selger Adr
SelgerAdr
CREATE VIEW SelgerAdr ASSELECT Selger.SNr, Selger,Navn, Selger.PNr,
Adr.StedFROM Selger, AdrWHERE Adr.PNr = Selger.PNr
SQL HiA
View
SELECT SNr, Navn, PNr, StedFROM SelgerAdr
SNr Navn PNr Sted
5 Nilsen 5002 Bergen2 Olsen 6400 Molde1 Hansen 9000 Tromsø4 Berg 6400 Molde
SNr Navn PNr Sted
5 Nilsen 5002 Bergen2 Olsen 6400 Molde1 Hansen 9000 Tromsø4 Berg 6400 Molde
Selger Adr
SelgerAdr
SQL HiA
View
CREATE VIEW SelgerTot ASSELECT Selger.SNr, Selger.Navn, Selger.PNr,
Adr.Sted,Salg.Mg,Vare.VNr, Vare.Pris
FROM Selger, Adr, Vare, SalgWHERE Adr.PNr = Selger.PNr AND
Salg.SNr = Selger.SNr ANDSalg.VNr = Vare.VNr
SNr Navn PNr Sted Mg VNrPris
1 Hansen 9000 Tromsø 50 5300
1 Hansen 9000 Tromsø 40 8500
2 Olsen 6400 Molde 20 1200
2 Olsen 6400 Molde 10 3400
4 Berg 6400 Molde 70 1200
4 Berg 6400 Molde 50 3400
4 Berg 6400 Molde 20 5300
5 Nilsen 5002 Bergen 30 8500
SQL HiA
UPDATE
Oppdaterer en tabellOppdaterer en tabell
UPDATE table nameview name correlation name
SET column name = expressionNULL
WHERE search condition CHECK EXISTSCURRENT OF cursor name
SQL HiA
Oppdatering av databasen
UPDATE VareSET Pris = 450WHERE VNr = 3
UPDATE VareSET Pris = 450
UPDATE VareSET Pris = Pris * 1.10WHERE VNr = 3
UPDATE VareSET Pris = Pris * 1.10
UPDATE TabSET a = ...
b = ...c = ...
SQL HiA
DELETE
Sletter rad(er) i en tabellSletter rad(er) i en tabell
DELETE table nameview name correlation name
WHERE search conditionCURRENT OF cursor name
SQL HiA
DELETE - Eksempel
DELETE FROM SalgWHERE SNr = 4 AND
VNr= 3
DELETE FROM SalgSletting av samtlige posteri tabellen Salg.
SQL HiA
SQL Funksjoner
• Aggregat funksjoner
• String funksjoner
• Date / Time funksjoner
• Logiske funksjoner
• Spesielle funksjoner
• Matematikk funksjoner
• Finans funksjoner
SQL HiA
Aggregat-funksjoner
AVG GjennomsnittCOUNT AntallMAX Maksimum@MEDIAN MiddelverdiMIN MinimumSUM Sum@SDV Standard avvik
SQL HiA
String funksjoner
@CHAR ASCII tegn til en desimal kode@CODE ASCII tegn til første tegn i en string@DECODE Returnerer en string, gitt et uttrykk@EXACT Sammenligner to stringer@FIND Posisjon til string1 i string2@LEFT Første (venstre) substring@LENGTH Lengden av en string@LOWER Omgjøring til små bokstaver@MID Returnerer en string, gitt startpunkt@NULLVALUE Returnerer string/tall spesifisert ved y hvis x er NULL@PROPER Omgjøring av første bokstav i hvert ord til stor bokstav@REPEAT Konkatenering av en string med seg selv n ganger@REPLACE Erstatter tegn i en string@RIGHT Siste (høyre) substring@SCAN Søker en string etter gitt mønster@STRING Omgjøring av tall til string@SUBSTRING Returnerer en del av en string@TRIM Redusering av blanke tegn (innledende og multiple)@UPPER Omgjøring til store bokstaver@VALUE Omgjøring fra string til tall
SQL HiA
Date / Time funksjoner
@DATE Konvertering til dato@DATETOCHAR Edit en dato@DATEVALUE Edit en dato@DAY Dag i måneden@HOUR Time til en dag@MICROSECOND Mikrosekund@MINUTE Minutt i en time@MONTH Måneden i et år@MONTHBEG Første dag i en måned@NOW Gjeldende Date / Time@QUARTER Tall som representerer kvartal@QUARTERBEG Første dag i et kvartal@SECOND Sekunder av et minutt@TIME Returnerer date/time@TIMEVALUE Returnerer en date/time verdi fra hh:mm:ss@WEEKBEG Mandag av uke@WEEKDAY Dag av en uke@YEAR År relativt til 1900@YEARBEG Første dag i året@YEARNO Kalender år
SQL HiA
Matematikk funksjoner
@ABS Absolutt-verdi@ACOS Arcus cosinus@ASIN Arcus sinus@ATAN Arcus tangens@ATAN2 To-kvadrant arcus tangens@COS Cosinus@EXP Eksponential-funksjon@FACTORIAL Fakultet@INT Heltallsdel@LN Naturlige logaritme@LOG 10’er logaritme@MOD Divisjonsrest@PI Tallet Pi (=3.14159265...)@ROUND Avrunding@SIN Sinus@SQRT Kvadratrot@TAN Tangens
SQL HiA
Finans funksjoner
@CTERM Antall perioder for å oppnå en fremtidig verdi@FV Fremtidig verdi av ekvidistante innbetalinger@PMT Periodiske innbetalinger ved nedbetaling@PV Verdi av ekvidistante innbetalinger@RATE Renter for å vokse til gitt beløp@SLN Rettlinjet verdi-foringelse@SYD Sum av et års verdi-foringelse@TERM Antall innbetalings-perioder for gitt investering
SQL HiA
Spesielle funksjoner
@CHOOSE Velger en verdi basert på korrelasjon@DECIMAL Desimal verdi av en hexadesimal string@DECRYPT Dekrypting av et passord@DECODE Returnerer en string, gitt et uttrykk@HEX Hexadesimal string fra et desimal-tall@LICS Sortering basert på internasjonalt tegn-sett
SQL HiA
Logiske funksjoner
@IF Tester og returnerer 1 hvis TRUE, 2 hvis FALSE@ISNA Returnerer TRUE hvis NULL
SQL HiA
Funksjoner - Eksempel
SELECT SNr, @PROPER(Navn)FROM SelgerAdrWHERE @UPPER(@TRIM(Navn)) = ‘HANSEN’
Usikkerhet angående skrivemåte av navn i databasen (små eller store bokstaver, mellomrom, ... )
SQL HiA
@CHOOSE
@CHOOSE (SelNr, a, b, c, d, e, f, g)
SNr <= 0 gir første liste-verdi a, SNr = 1 neste verdi b, …, SNr >=6 gir her siste verdi g
SelNr Resultat
0-1212
aacg
SQL HiA
@CHOOSE
SELECT @CHOOSE(@WEEKDAY(FDato), ‘Lø’, ‘Sø’, ‘Ma’, ‘Ti’, ‘On’, ‘To’, ‘Fr’) FROM Person
List ut uke-dagen til fødselsdagen til samtlige personer i tabellen Person
SQL HiA
@FV
@FV (Belop, p, n)
Returnerer fremtidig verdi av en serie av n antall innbetalinger (hver lik Belop)til p prosent rente.
n (1 + p/100) - 1Belop * ------------------- p/100
SQL HiA
SQL Et større eksempel (1)
SELECT StlngMax.Periode, StlngMax.StRang, COUNT(*), @ROUND(AVG(Ektekskap.EVarighet),1)
FROM StlngMax, Ekteskap WHERE StlngMax.PersonID = Ekteskap.MannID AND Ekteskap.ENrMann = 1 AND GROUP BY StlngMax.Periode, StlngMax.StRang
List ut for hver tidsperiode og stilling gjennomsnittlig ekteskapsvariget (1.ekteskap med 1 desimal) for hver person i tabellen StlngMax samt totalt antall personer som er med i beregningen (for hver periode)
StlngMax Ekteskap
PersonID…..PeriodeStRang
MannIDKvinneID…..ENrMannENrKvinneEVarighet
Periode Stilling GjEVarighet Count
1500-1549 Kap 29,3 211Sp 30,1 105…..
1550-1599 Kap 31,2 323Sp 31,7 258…..
…..
SQL HiA
SQL - Et større eksempel (2)
SELECT StlngMax.StRang, StlngMax.Periode, COUNT(*), @ROUND(AVG(Ektekskap.EVarighet),1)
FROM StlngMax, Ekteskap WHERE StlngMax.PersonID = Ekteskap.MannID AND Ekteskap.ENrMann = 1 AND GROUP BY StlngMax.StRang, StlngMax.Periode
List ut for hver stilling og tidsperiode gjennomsnittlig ekteskapsvariget (1. ekteskap med 1 desimal) for hver person i tabellen StlngMax samt totalt antall personer som er med i beregningen (for hver periode)
StlngMax Ekteskap
PersonID…..PeriodeStRang
MannIDKvinneID…..ENrMannENrKvinneEVarighet
Stilling Periode GjEVarighet Count
Kap 1500-1549 29,3 2111550-1599 31,2 323…..
Sp 1500-1549 30,1 1051550-1599 31,7 258
…..
SQL HiA
UNLOAD
Laster data fra en database ut til en fil av gitt formatLaster data fra en database ut til en fil av gitt format
UNLOAD SQL ‘file name’ source tableCOMPRESS DATA CONTROL ‘file name’ OVERWRITE ALL
ASCII ‘file name’ source tableDATA CONTROL ‘file name’ OVERWRITE
DIF ‘file name’ source tableDATA CONTROL ‘file name’ OVERWRITE
DATABASE ‘file name’COMPRESS SHEMA CONTROL ‘file name’ OVERWRITE
ALL
ON CLIENT LOG ‘logfile name’SERVER
SQL HiA
UNLOAD - Eksempel
UNLOAD DATA SQL c:\data\Adr.txt Adr
INSERT INTO Adr (PNr,Sted)VALUES(:1,:2)
\$DATATYPES NUMERIC,CHARACTER5002,Bergen6400,Molde9000,Tromsø/
Kopierer data fra database-tabellen Adr, prefikser dataene med en INSERT-kommando (pga SQL)og lagrer dette til tekstfilen c:\data\Adr.txt.
SQL HiA
LOAD
Laster data fra en fil av gitt format inn til en tabell i databasenLaster data fra en fil av gitt format inn til en tabell i databasen
LOAD SQL ‘file name’COMPRESS CONTROL ‘file name’
ASCII ‘file name’ table nameCONTROL ‘file name’
DIF ‘file name’CONTROL ‘file name’ table name
ON CLIENT LOG ‘logfile name’ START AT lineSERVER
SQL HiA
LOAD - Eksempel
LOAD SQL c:\data\Adr.txt
SQL HiA
ALTER TABLE
Endrer på kolonne-strukturen i en tabellEndrer på kolonne-strukturen i en tabell
ALTER TABLE table name
,DROP column name
ADD column name data type
(size) NOT NULLNOT NULL WITH DEFAULT
,RENAME column name new name
TABLE new name,
MODIFY column name
data type (length) NULLNOT NULLNOT NULL WITH DEFAULT
SQL HiA
ALTER TABLE - Eksempel
ALTER TABLE Bedrift MODIFY BedriftsNavn Char(50)
ALTER TABLE Bedrift ADD Tlf Char(8)
Endrer datatype og/eller kolonnelengde
Legger til en kolonne
SQL HiA
ALTER TABLE (Referential Integrity)
Endrer på PRIMARY KEY / FOREIGN KEY strukturen i en tabellEndrer på PRIMARY KEY / FOREIGN KEY strukturen i en tabell
ALTER TABLE table name
,PRIMARY KEY ( column name )
DROP,
,FOREIGN KEY ( column name ) REFERENCES parent table name
DROP foreign key name
ON DELETE RESTRICTCASCADESET NULL
SQL HiA
ALTER TABLE (Error Message)
Endrer på USERERROR strukturen i en tabellEndrer på USERERROR strukturen i en tabell
ALTER TABLE table name
ADD USERERROR error numberDROPMODIFY
FOR ‘DELETE_PARENT’ OF PRIMARY KEY‘UPDATE_PARENT’ FOREIGN KEY key name‘UPDATE_DEPENDENT’
SQL HiA
ALTER TRIGGER
Enable / Disable en triggerEnable / Disable en trigger
ALTER TRIGGER trigger name ENABLEDISABLE
SQL HiA
COMMIT
Commits alle endringene til databasen siden forrige COMMIT eller ROLLBACK.Commits gjelder alle SqlHandles / Cursors som gjeldende applikasjonhar koblet opp mot databasen.
COMMITWORK TRANSACTION <id> FORCE
SQL HiA
ROLLBACK
Omgjør siste transaksjonOmgjør siste transaksjon
ROLLBACKsavepoint identifierTRANSACTION <id> FORCE
SQL HiA
SAVEPOINT
Genererer SAVEPOINT til bruk ved ROLLBACKGenererer SAVEPOINT til bruk ved ROLLBACK
SAVEPOINT savepoint identifier
SQL HiA
CREATE INDEX
Oppretter en indeksOppretter en indeks
CREATE INDEX index nameUNIQUE CLUSTERED HASHED
,ON table name ( column name )
ASCDESC
PCTFREE integer constant SIZE integer value ROWSBUCKETS
Max size = 6 + number of + sum of <= 255columns in length of allindex columns in
index
SQL HiA
CREATE INDEX - Eksempel
CREATE UNIQUE INDEX Bidndx ON Bedrift (BedriftsID)
CREATE INDEX BnNdx ON Bedrift (BedriftsNavn)
SQL HiA
CREATE SYNONYM
Oppretter synonym (alias) navn til en tabellOppretter synonym (alias) navn til en tabell
CREATE SYNONYM synonym namePUBLIC
FOR table nameauthorization ID view name
SQL HiA
ROWCOUNT
Returnerer antall rader i en tabellReturnerer antall rader i en tabell
ROWCOUNT tablename
SQL HiA
UNION
Genererer resultatsett som union av multiple SELECTGenererer resultatsett som union av multiple SELECT
UNIONALL
select command,
ORDER BY integer constantASCDESC
SELECT …UNION ALLSELECT …UNION ALLSELECT …...
Duplikater blir ikke eliminert
SQL HiA
UPDATE STATISTICS
Oppdaterer statistikk-informasjon i databasen.Benyttes til å generere applikasjonsplan.
Oppdaterer statistikk-informasjon i databasen.Benyttes til å generere applikasjonsplan.
UPDATE STATISTICS
INDEX index name,
SET system catalog column name = expression,
DISTINCTCOUNT (index key) = expression
TABLE,
SET system catalog column name = expression
DATABASE
SQL HiA
Brukere / Privilegier
• Ulike brukere gis ulike passord.
• Data i en tabell skal være tilgjengelig for noen brukere,men sperret for andre.
• Noen brukere skal kunne gjøre endringer i en tabell, andre skal kun få lov til å lese fra tabellen.
• I en tabell kan det være ønskelig med ulike restriksjonerknyttet til enkelt-kolonner.
• Noen brukere nektes bruk av interaktiv SQL-aksess,mens de samme brukerne kan benytte andre applikasjons-programfor oppdatering av den samme databasen.Eventuelle restriksjoner er da innebygget i applikasjons-programmet.
SQL HiA
Brukere / Privilegier
• Brukere med DBA-privilegier
• Brukere med RESOURCE-privilegier
• Brukere med CONNECT-privilegier
Det finnes tre hoved-typer brukere:
SQL HiA
Authority Level
SYSADMSYSADM
DBADBA
RESOURCERESOURCE
CONNECTCONNECT
SQL HiA
CONNECT
• Adgang til databasen.
• Adgang til å manipulere spesifikke tabeller i følge tilordnede privilegier.
SQL HiA
RESOURCE
• Adgang til å opprette tabeller, indekser og clustre.
• Alle rettigheter til operasjoner på egenopprettede tabeller.
• Adgang til å modifisere privilegier til egenopprettede tabellerfor gyldige brukere av databasen.
SQL HiA
DBA
• Alle rettigheter til enhver tabell i databasen.
• Alle rettigheter til Grant (tilordne) / Modify (endre) / Revoke ( fjerne)tabell-privilegier til enhver bruker.
• DBA kan ikke opprette nye brukereog heller ikke endre passord eller autoritet til eksisterende brukere.Dette kan kun utføres av SYSADM.
SQL HiA
Tilordning av autoritet / privilegier
GRANT CONNECTTO Nilsen IDENTIFIED BY Katt
GRANT CONNECTTO Olsen IDENTIFIED BY Gris
GRANT CONNECTTO Hansen IDENTIFIED BY Hest
GRANT RESOURCETO Olsen
GRANT DBATO Hansen
GRANT SELECT, UPDATE (Navn, PNr) ON SelgerTO Olsen
SQL HiA
Database Authority
,GRANT RESOURCE TO AuthID
DBA , ,
CONNECT TO AuthID IDENTIFIED BY Password
SQL HiA
Table Privileges
,GRANT ALL
SELECT
INSERT
DELETE
INDEX
ALTER
UPDATE ,( ColumnName )
, ,ON TableName TO AuthID
ViewName PUBLIC
SQL HiA
SQL / Høgnivåspråk
SQL har mange sterke sider,men språket er ikke et komplett programmerings-språkpå linje med Fortran, C, Pascal, Cobol, ...
SQL ble opprinnelig benyttet interaktivt ved direkte å giSQL-kommandoer med umiddelbar respons.
Brukervennligheten til SQL vil øke radikalt ved å innebygge SQLsom en del av et komplett prosedyre-språk.
To bruks-måter for SQL:
- Interaktiv bruk av SQL- SQL innebygget i et komplett prosedyre-språk
SQL HiA
SQL innebygget som en del av et komplett prosedyre-språk
• Embedded SQLSQL-statement legges inn sammen med den øvrige program-kode.Blanding av prosedyre-språkets egne statement og SQL-statement.Spesielle SQL-statement benyttes som aksess mot databasen.En spesiell SQL-prekompilator scanner den kombinerte koden, og lager sammen med de øvrige program-verktøyene et eksekverbart program.
• Application Program Interface ( API )Programmet ( høgnivå-språket ) kommuniserer med DBMSgjennom et sett av funksjoner kalt application program interface ( API ).Programmet overfører SQL-statement til DBMS gjennom API-kallog benytter API til aksess mot databasen.SQL-statementene overføres via parametre i API-kallene.
SQL HiA
Embedded SQL / API
DBMS API Embedded Language Support
DB2 Nei APL, Assembler, Basic, Cobol, Fortran, PL/ISQL/DS Nei APL, Assembler, Basic, Cobol, Fortran, PL/I, PrologOracle Ja Ada, C, Cobol, Fortran, Pascal, PL/IIngres Nei Ada, Basic, C, Fortran, Pascal, PL/ISybase Ja IngenInformix Nei Ada, C, CobolOS/2 EE Nei CSQLBase Ja Ingen
SQL HiA
Høgnivå-språk - SQL
Høgnivåspråk
Data-flytVariablerBlokk-strukturTestSløyferInput / Output
SQL
Database-aksess
SQL HiA
SQL-prosessering
• Parsing (traversering) av SQL-statement.Oppbryting av statementet i individuelle ord og kontroll av syntaksen.
• Validitering av statementet.Kontroll av tabell/kolonne-navn mot system-katalogene.Kontroll av brukers rettigheter assosiert med statementet.
• Optimalisering av statementet.Vurdering av alternative løsninger.Bør indeks benyttes?Hvordan optimalisere join mot søke-kriterier?Kan table-scan unngås?Valg av alternativ.
• Generering av en applikasjons-plan for statementet.Applikasjons-planen er en binær representasjon av steppenefor statement-utføring og er DBMS’s eksekverbare kode.
• Eksekvering av statementet.
SQL HiA
SQL-prosessering
SELECT A,B,CFROM X,YWHERE A < 5000 AND
C = ‘ABC’
Parse statement
Validate statement
Optimize statement
Generate Application Plan
Execute statement
Binary form ofSQL statement
Databasesystem catalog
SQL HiA
SQL knyttet opp mot høgnivåspråk-variabler
EXEC SQL SELECT SNrID, PNrINTO :Nr, :Navn, :PostNrFROM SelgerWHERE SNrID = 2
EXEC SQL INSERT INTO SelgerVALUES (:Nr, :Navn, :PostNr)
SQL HiA
Resultat-sett med mer enn en record
ApplikasjonApplikasjonDatabaseDatabase
Resultat-sett
Record_1Record_2Record_3...
SQL-statement
Cursor
Fetch
SQL HiA
Embedded SQL - Eksempel
main(){
...printf(“Enter office number: “);scanf(“%d”, &officenum);
exec sql select city, region, target, salesinto :cityname, :regionname, :targetval, :salesvalfrom officeswhere office = :officenum;
printf(“City: %s\n”, cityname);...
}
SQL HiA
5 faser ved generering av embedded SQL-program
• Embedded SQL-kilde-kode scannes av en SQL prekompilator.SQL-statementene lokaliseres og prosesseres.Ulike prekompilatorer kreves for hvert programmerings-språk supportert av DBMS.
• Prekompilatoren gir to filer som output:- Kildekode strippet for SQL-statement.
SQL-statementene erstattes av private DBMS-rutiner og som gir run-time linkmellom programmet og DBMS.Disse er vanligvis kjent kun av prekompilatoren og er ikke public interface til
databasen.- Kopi av strippede SQL-statement, kalt database request module (DBRM).
• Vertsspråk-kompilatoren traverserer strippet kilde-kode fra forrige faseog gir en objekt-kode som output.
• Linkeren lenker objekt-koden fra foregående fase med private DBMS-rutiner.
• DBRM-modulen generert av prekompilatoren i fase 2 scannes av et spesielt BIND-program.Dette programmet går gjennom de 5 fasene for prosessering av SQL-statementog genererer en applikasjons-plan for hvert statement.Resultatet er en kombinert applikasjons-plan for hele programmet,og denne planen blir lagret i databasen, vanligvis med samme navn som selvehoved-programmet.
SQL HiA
5 faser ved generering av embedded SQL-program
Embedded SQL source programEmbedded SQL source program
Stripped source programStripped source program Database request moduleDatabase request module
Precompiler
Compiler BIND
Object codeObject code DBMS libraryDBMS library Application planApplication plan
Linker
Executable programExecutable programApplication plan
Database
Application planDatabase
SQL HiA
SQL API
• Programmet begynner sin database-aksess ved et API-kallsom knytter programmet til databasen.
• For å sende et SQL-statement til DBMS, bygger programmet opp statementetsom en tekst-streng som plasseres i et buffer og kaller deretter opp en API-funksjonsom overfører buffer-innholdet til DBMS.
• Programmet gjør et API-kall for status-kontroll av DBMS-forespørselenog for å håndtere eventuelle feil.
• Hvis SQL-statementet er en query, kalles en API-funksjon for å hente database-datainn i et program-buffer.Vanligvis returneres en rad eller en kolonne ad gangen.
• Programmet avslutter sin database-aksess med et API-kall som kobler programmetfra DBMS.
SQL HiA
SQL API
Applicationprogam
Applicationprogam DBMSDBMS
CONNECT (db,user,pswd)
SEND (“Update orders set ...”)
EXECUTE ( )
STATUS_CHECK ( )
Ok
SEND (“Select * from offices ...”)
EXECUTE ( )
GETROW ( )
(101, “New York”, ...)
GETROW ( )
(12, “Chicago”, ...)
DISCONNECT ( )
SQL HiA
SQL API - Program-eksempel
main(){...DBSETLUSER(loginrec, “Scott”);DBSETLPWD (loginrec, “Tiger”);...dbproc = dbopen(loginrec, ““);...printf(“Raise/lower quotas by how much:”);gets(sAmount);
dbcmd(dbproc,”Update salesreps set quotas = quota + “);dbcmd(dbproc,sAmount);dbsqlexec(dbproc);
status = dbresults(dbproc);if (status != SUCCEED)printf(“Error during update.\n”);
elseprintf(“Update successful.\n”);
dbexit(dbproc);...
}
SQL HiA
SAL funksjonalitet i SQLBase (1)
SqlClearImmediate Disconnect Sql Handle benyttet av SqlImmediateSqlClose Lukker en navngitt cursorSqlCommit Commit gjeldende SQL transaksjonSqlConnect Connect en Sql Handle til en databaseSqlDisconnect Disconnect en Sql Handle fra en databaseSqlDropStoredCmd Sletter en lagret kommando eller lagret prosedyreSqlError Henter gjeldende error kode for gitt Sql HandleSqlExecute Eksekverer et SQL statement, lagret kommendo eller lagret prosedyreSqlExists Sjekker eksistens av spesifisert(e) rad(er)SqlFetchNext Henter neste rad i et resultatsettSqlFetchPrevious Henter forrige rad i et resultatsettSqlFetchRow Henter gitt rad i et resultatsettSqlGetErrorPosition Returnerer offset i en error innen et SQL statementSqlGetErrorText Returnerer error-tekst for et gitt SQL error numberSqlGetModifiedRows Returnerer antall endrete rader i en INSERT, UPDATE eller DELETESqlGetParameter Returnerer en databaseparameterSqlGetParameterAll Returnerer en databaseparameterSqlGetResultSetCount Returnerer antall rader i et resultatsettSqlGetRollbackFlag Returnerer database rollback flag
SQL HiA
SAL funksjonalitet i SQLBase (2)
SqlImmediate Kompilerer og eksekverer et SQL statementSqlOpen Navngir en cursor og eksekverer et SQL statementSqlPrepare Kompilerer et SQL statement eller ikke-lagret prosedyreSqlPrepareAndExecute Kompilerer og eksekverer et SQL statement eller ikke-lagret prosedyreSqlRetrieve Henter en lagret kommando eller lagret prosedyreSqlSetIsolationLevel Setter isolasjonsnivåSqlSetLockTimeout Setter timeout periode for venting på en låsSqlSetParameter Setter en database parameterSqlSetParameterAll Setter en database parameterSqlSetResultSet Setter resultatsett mode på/avSqlStor Kompilerer og lagrer en kommando eller prosedyre
SQL HiA
Relaterte SQLTalk kommandoer
ERASE Sletter en lagret kommando eller lagret prosedyreEXECUTE Eksekverer en lagret kommando eller lagret prosedyrePERFORM Eksekverer enten en preparert SQL kommando / lagret prosedyre
eller gjenfinner en lagret kommando / lagret prosedyrePREPARE Kompilerer en SQL kommando eller ikke-lagret prosedyreSET TRACE Enable/Diasable statement tracingSET TRACEFILE Trace output til server-fil eller servers aktivitets-skjermSHOW TRACE Viser enabling/disabling av TRACESHOW TRACEFILE Viser trace output (server-fil eller aktivitets-skjerm)STORE Kompilerer og lagrer en kommando eller prosedyre i systemkatalogene
(og dens eksekveringsplan for seinere eksekvering)
SQL HiA
Connect / Disconnect
SqlDatabase DEMOSqlUser SYSADMSqlPassword SYSADM
SqlConnectSqlDisconnect
Predefinerte variable Default-verdier
Centura-funksjoner
SQL HiA
End
top related