Difference between revisions of "6238 Databaser Agenda/Mere sql"

Revision as of 15:43, 27 October 2015

ON DELETE CASCADE

On delete fortæller hvad databasen skal gøre hvis man forsøger at slette data der anvendes som foreign key af andre tabeller.

CREATE TABLE Saelger(
Navn NVARCHAR(20)NOT NULL,
StartDatoDATETIMENOT NULL,
EmailNVARCHAR(20)NOT NULL,
MedarbNrINTNOT NULL,
PRIMARY KEY(MedarbNr)
);

CREATE TABLE TelefonNr(
Nr NVARCHAR(8) NOT NULL,
SaelgerINT NOT NULL,
PRIMARY KEY(Nr),
FOREIGN KEY(Saelger) REFERENCES Saelger(MedarbNr) ON DELETE Cascade
);

INSERT INTO Saelger (Navn, StartDato,Email,MedarbNr) VALUES ('Hans','2001-1-1','Hans@mail.dk',1);
INSERT INTO TelefonNr (Nr, Saelger) VALUES ('12345678',1);

DELETE FROM Saelger WHERE MedarbNr = 1;

I eksemplet her laves først tabellen sælger og derefter TelefonNr der har Saelger som FK. Bemærk ON DELETE CASCADE efter FOREIGN KEY på TelefonNr.

Efterfølgende indsættes en sælger og en telefon med reference til sælgeren hvorefter vi forsøger at slette sælgeren.

Normalt vil dette ikke være muligt, men fordi vi har indsat ON DELETE Cascade vil den blot slette de numre som referere til Sælgeren når Sælgeren slettes.

Opgave

Forsøg på sælger databasen at slette en Vare

Ret i databasen ved at tilføje de nødvendige ON DELETE sådan at en Vare kan slettes uden problemer

Ret derefter også sådan at en Sælger kan slettes uden problemer. Dette er nok ikke praktisk i virkeligheden men prøv alligevel

SELECT DISTINCT

Med DISTINCT kommer hvert resultat kun en gang i output. Afprøv selv forskellen på

SELECT Navn FROM KoebesAf 
JOIN Kunde ON Kunde.KundeNr = KoebesAf.Kunde

og

SELECT DISTINCT Navn FROM KoebesAf 
JOIN Kunde ON Kunde.KundeNr = KoebesAf.Kunde

IDENTITY/AUTO_INCREMENT

Ofte vi vi ønske at have et index der automatisk tæller op.

Det er muligt i både Ms-Sql og MySql men på to forskellige måder.

/* Opret tabellen Saelger */
/*MS-Sql*/
CREATE TABLE Saelger
(
	Navn 	NVARCHAR(20)	NOT NULL,
	StartDato	DATETIME	NOT NULL,
	Email	NVARCHAR(20)	NOT NULL,
	MedarbNr	INT	IDENTITY ,
	PRIMARY KEY(MedarbNr)
);

/*MySql*/
CREATE TABLE Saelger
(
	Navn 	NVARCHAR(20)	NOT NULL,
	StartDato	DATETIME	NOT NULL,
	Email	NVARCHAR(20)	NOT NULL,
	MedarbNr	INT	AUTO_INCREMENT,
	PRIMARY KEY(MedarbNr)
);

Hvis du benytter en autoincrementeret værdi må denne ikke længere være med under INSERT

INSERT INTO Saelger (Navn,StartDato,Email) VALUES ('Ole','1-1-1','ole@mail.dk');

Unique

Med UNIQI kan vi bestemme at en attribut skal være unik selv om den ikke er nøgle attribut.

Derved kan vi anvende den som reference til foreign key.

I eksemplet her anvendes MedarbNr som nøgle, men vi vælger at angive Navn som UNIQ og kan derfor anvende det som Foreign key på TelefonNr.

/* Opret tabellen Saelger */
CREATE TABLE Saelger
(
	Navn 	NVARCHAR(20)	NOT NULL,
	StartDato	DATETIMENOT 	NULL,
	Email	NVARCHAR(20)	NOT NULL,
	MedarbNr	INT	NOT NULL,
	UNIQUE (Navn),
	PRIMARY KEY(MedarbNr)
);

/* Opret tabellen TelefonNr */
CREATE TABLE TelefonNr
(
	Nr 	NVARCHAR(8) 	NOT NULL,
	SaelgerNavn 	NVARCHAR(20) 		NOT NULL,
	PRIMARY KEY(Nr),
	FOREIGN KEY(SaelgerNavn) REFERENCES Saelger(Navn) 
);

@@ Line 1: / Line 1: @@
 ==ON DELETE CASCADE==
 On delete fortæller hvad databasen skal gøre hvis man forsøger at slette data der anvendes som foreign key af andre tabeller.
@@ Line 112: / Line 111: @@
 	FOREIGN KEY(SaelgerNavn) REFERENCES Saelger(Navn)
 );
-</source>
-==Stored procedure==
-Med stored procedure kan man automatiserer nogle processer. Det minder meget om metoder og funktioner i andre programmeringssprog.
-Der er igen en lille forskel på MS-Sql og MySql.
-Her er f.eks. vist hvordan vi indsætter Saelger og telefon nr i en og samme arbejdsgang.
-'''MS-Sql'''
-<source lang=sql>
-CREATE PROCEDURE InsertSaelger @navn NVARCHAR(20), @email NVARCHAR(20), @startDato DATETIME,  @saelgerNr INT, @telefonNr NVARCHAR(8)
-AS
-  IF NOT EXISTS (SELECT Navn FROM Saelger WHERE Saelger.MedarbNr = @saelgerNr)
-  BEGIN
-    INSERT INTO	Saelger(Navn,Email,StartDato,MedarbNr)VALUES(@navn,@email,@startDato,@saelgerNr);
-  END
-  INSERT INTO TelefonNr(Nr,Saelger) VALUES (@telefonNr,@saelgerNr);
-GO
-</source>
-Bemærk hvordan alle variable navne har @ foran sig og at scope ikke er markeret med {} som vi kender fra C# men i stedet med Begin End som i Basic.
-Proceduren kaldes således.
-<source lang=sql>
-EXECUTE InsertSaelger 'Jens', 'jens@mail.dk', '2001-1-1',4,'67891234';
-</source>
-'''MySql'''
-MySql er lidt speciel da den vil forsøge at afvikle koden hver gang den ser karakteren ; Den karakter kaldes en DELIMITER
-Løsningen er at lave dens DELIMITER om til noget andet en ; i viste tilfælde // I slutningen af proceduren laves den tilbage til ;
-Der ud over anvendes ikke @ foran variable navne, de parametrene skal stå i parantes og IF skal efterfølges af THEN og afsluttes med END IF.
-<source lang=sql>
-DELIMITER //
-CREATE PROCEDURE InsertSaelger
-(IN navn NVARCHAR(20), IN email NVARCHAR(20), IN startDato DATETIME,  IN saelgerNr INT, IN telefonNr NVARCHAR(8))
-BEGIN
-  IF NOT EXISTS (SELECT Navn FROM Saelger WHERE Saelger.MedarbNr = saelgerNr) THEN
-    INSERT INTO	Saelger(Navn,Email,StartDato,MedarbNr)VALUES(navn,email,startDato,saelgerNr);
-  END IF;
-  INSERT INTO TelefonNr(Nr,Saelger) VALUES (@telefonNr,@saelgerNr);
-END //
-DELIMITER ;
-</source>
-Når proceduren kaldes anvendes CALL ikke EXECUTE.
-<source lang=sql>
-Call InsertSaelger ('Jens', 'jens@mail.dk', '2001-1-1',4,'67891234');
 </source>

Navigation menu