Introduktion

Velkommen til Database modulet på akademiuddannelsen.

Akademiuddannelsens

Akademiuddannelserne er tilrettelagt som deltidsstudie med et omfang af 60 ECTS, svarende til et års fuldtidsstudier.

Uddannelserne er bygget op af fag, der hver har et omfang af 5 eller 10 ECTS.

En Akademiuddannelse består af obligatoriske fag for minimum 20 ECTS, valgfag for minimum 5 ECTS, samt et afgangsprojekt på 10 ECTS.

Oversigt

Kurset består af 6 kursus gange, med følgende indhold.

Kursusgang 1

• Introduktion
• MySQL - Installation og test af Workbench
• Demo databaser - Northwind og Employees
• SQL introduktion
  • SELECT
  • WHERE
  • AND, OR, NOT
  • ORDER BY
  • DISTINCT
  • BETWEEN
  • IN

Kursusgang 2

SQL

• Datatyper
• DATABASE - (CREATE, DROP)
• TABLE (CREATE, DROP, ALTER)
• INSERT INTO
• UPDATE
• AUTO INCREMENT
• NULL værdier

Kursusgang 3

Normalisering

Relationer

• JOIN (LEFT, RIGHT, INNER)
• VIEW

Kursusgang 4

Funktioner

• Beregninger
• AVG, MIN, MAX, COUNT, SUM
• GROUP BY
• HAVING

SQL

• INDEX
• CASE

Kursusgang 5

• PROCEDURE
• TRIGGER
• IMPORT CSV
• EXPORT CSV

Kursusgang 6

• Sikkerhed
• Brugere

Eksamen

Eksamen afholdes som en mundtlig eksamen på 30 minutter.

Kursusgang 1

Introduktion

Det er Open Source databasen - MySQL du skal bruge.

Den kan installeres på Windows, Mac og Linux.

Du kan læse mere om MySQL her: Link

MySQL Installation

Windows

Du kan finde en vejledning for installation på Windows her: Link

Mac

Du kan finde en vejledning for installation på Mac her: Link

Demo databaser

Hvad er et database kursus uden data?

Der er 2 demo databaser vi skal bruge på dette kursus:

• Northwind
• Employees

Northwind

Denne database er en Microsoft har brugt i mange år. Den indeholder 13 tabeller

ER-diagram - Northwind

Kan også hentes som PDF her: Link

Installation
Du skal følge disse trin for at få installeret Northwind databasen:

1. Hent zip filen Northwind.zip herfra Link
2. Pak fil ud - den indeholder to filer
3. Åben filen Northwind.sql i Workbench
4. Kør filen
5. Det tager “noget” tid at oprette databasen og indsætte data

Employees

Denne database er en af MySQL’s officielle demo databaser.
Se mere på deres www side - Link

The Employees sample database was developed by Patrick Crews and Giuseppe Maxia and provides a combination of a large base of data (approximately 160MB) spread over six separate tables and consisting of 4 million records in total. The structure is compatible with a wide range of storage engine types. Through an included data file, support for partitioned tables is also provided.

MySQL har også en officiel beskrivelse som kan hentes her: Link

ER-Diagram - Employees

Kan også hentes som PDF her: Link

Installation Du skal følge disse trin for at få installeret Employee databasen:

1. Hent filen zip filen: employees.zip herfra:
2. Pak zip-fil ud - den indeholder 15 filer
3. I Workbenck klik - Tools > Start Shell

MySQL Workbench Introduktion

MySQL Workbench er det grafiske program som du skal bruge til dit arbejde med MySQL serveren.

Der findes en online manual til Workbench

MySQL Workbench understøtter:

• SQL Development
• Data Modeling (Design)
• Server administration
• Data Migration
• MySQL Enterprise Support

MySQL Workbench kan findes i tre versioner; MySQL Workbench Community Edition, MySQL Workbench Standard Edition og MySQL Workbench Enterprise Edition.

MySQL Workbench Community Edition er Open Source (GLP Licens) og er dermed gratis at bruge, det er den version vi kommer til at bruge.

Du kan læse mere om forskellene på de tre versioner her: MySQL Workbench Features

Hoved vinduet i Workbench

SQL introduktion

SELECT

SELECT er nok den mest brugte SQL kommando. Den bruges til at hente data ud af din database.

Syntax SELECT

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn;
3 	
4 	SELECT * FROM tabel_navn

Northwind Syntax SELECT

1 	select * from Employees;
2 
3 	select 
4 		EmployeeID,
5     	FirstName,
6     	LastName
7 	from Employees;

Kommentarer

Det kan være en rigtig god ide at skrive kommentarer til dine SQL koder. Der er to forskellige muligheder for kommentarer

• På en enkelt linje - her brugere du: –
• Over flere linjer - her brugere du: /* kommentar */

**Eksempeler på en kommentar på en linje

1 --Select all
2 SELECT * FROM Customers;
3 
4 SELECT * FROM Customers -- WHERE City='Berlin';

Hvis du gerne vil skrive en kommentar der fylder mere end en linje eller det er flere linjer kode du gerne vil ud-kommenter skal du bruge denne metode:

1 /* 
2 	Vælg alle kolonner
3 	og alle poster 
4 	i Customers tabellen 
5 */
6 
7 SELECT * FROM Customers;

Alt imellem /* og */ bliver ignoreret.

DISTINCT

Bruges sammen med SELECT til at returnere unikke værdier. Altså kun en af hver værdi.

Syntax DISTINCT

1 	SELECT DISTINCT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn;

Northwind Syntax DISTINCT

1 	select distinct Country from Customers;

## WHERE Bruges sammen med SELECT for at udvælge data.
Det er kun de data der opfylder dit krav (WHERE) der vises.

Syntax WHERE

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE betingelse;

Northwind Syntax WHERE

1 	select * from Customers
2 	where Country = 'Denmark';

WHERE kan også bruges sammen med andre SQL kommandoer end SELECT. F.eks. UPDATE og DELETE

AND, OR, NOT

Du kan kombinere WHERE med AND, OR og NOT.

AND og OR bruger du til at udvælge data på mere end én betingelse.

• AND viser data der opfylder alle betingelser
• OR viser data hvor det bare er en af betingelserne der er opfyldt

NOT bruges til at vise data der ikke opfylder betingelsen.

Syntax AND

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE betingelse_1 AND betingelse_2 AND betingelse_3 ...;

Northwind Syntax AND

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE betingelse_1 AND betingelse_2 AND betingelse_3 ...;

Syntax OR

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE betingelse_1 OR betingelse_2 OR betingelse_3 ...;

Northwind Syntax OR

1 	select * from Customers
2 	where Country = 'Denmark'
3 	or Country = 'Sweden';

Syntax NOT

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE NOT betingelse;

Northwind Syntax NOT

1 	select * from Customers
2 	where not Country = 'USA';

BETWEEN

BETWEEN bruges til at udvælge et “interval”.
Det kan være tal, tekst eller datoer.

Syntax BETWEEN

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE kolonne_1 BETWEEN værdi_1 AND værdi_2;

BETWEEN har altid dine start og slut værdier med.

IN

IN giver dig mulighed for at bruge flere værdier i en WHERE sætning.

Syntax IN

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	WHERE kolonne_1 IN (værdi_1, værdi_2, ...);
4 	
5 	SELECT kolonne_1, kolonne_2,  ...
6 	FROM tabel_navn
7 	WHERE kolonne_1 IN (SELECT sætning);

ORDER BY

Hvis du vil sortere dine data skal du bruge ORDER BY.

ORDER BY sorterer i stigende orden som standard - ASC.
For at sortere i faldende orden skal du bruge DESC.

Syntax ORDER BY

1 	SELECT kolonne_1, kolonne_2,  ...
2 	FROM tabel_navn
3 	ORDER BY ASC
4 	
5 	SELECT kolonne_1, kolonne_2,  ...
6 	FROM tabel_navn
7 	ORDER BY DESC

Syntax ORDER BY Northwind

1 	select * from Order_Details
2 	order by UnitPrice ASC;
3 
4 	select * from Order_Details
5 	order by UnitPrice DESC;

Opgave 1

Du kan hente opgave 1 her som PDF - Link

Kursusgang 2

DATABASE

For at oprette en ny database skal du bruge CREATE For at slette en eksisterende database skal du bruge DROP

Når du vil skifte mellem hvilken database der er den aktive”, skal du bruge USE.

Syntaks DATABASE

1 	CREATE DATABASE database_navn
2 	
3 	DROP DATABASE database_navn
4 	
5 	USE database_navn

Du skal være opmærksom på at du ikke får nogle advarsler ved brugen af DROP og det er ikke muligt at fortryde denne kommando.

Hvis du vil se alle de databaser der er på din MySQL server, kan du brugere denne SQL kommando

1 SHOW DATABASES;

Datatyper

Hvert felt en database har et navn og en datatype.
Datatypen “styre” hvilke data det er muligt at gemme i feltet. I MySQL er datatyperne opdelt i 3 hovedgrupper:

• Tekst
• Tal
• Dato

Du kan hente en oversigt over MySQL’s datatyper her: Link

TABLE

Det er i dine tabeller at data gemmes. For at oprette en tabel brugere du SQL kommandoen CREATE TABLE

Syntaks CREATE TABLE

 1 	CREATE TABLE tabel_navn
 2 	(
 3 		kolonne_1 datatype,
 4 		kolonne_2 datatype,
 5 		kolonne_3 datatype,		
 6 		....
 7 	);
 8 	
 9 	CREATE TABLE	Kunder
10 	(
11 		Kunde_Id INT,
12 		Kunde_Navn VARCHAR(60)
13 	);

Hvis du vil slette en tabel skal du bruge SQL kommandoen DROP TABLE

Bemærk at alle data slettes og det er ikke muligt at fortryde denne kommando.

Syntaks DROP TABLE

1 	DROP TABLE tabel_navn;
2 	
3 	DROP TABLE Kunder;

Når du har oprettet en tabel skal du bruge SQL kommandoen ALTER TABLE for at ændre den.

Du kan udføre følgende ændringer:

• Tilføj en kolonne
• Slet en kolonne
• Ændre en eksisterende kolonne

Tilføj en kolonne For at tilføje en kolonne skal du bruge denne SQL kommando

1 	ALTER TABLE table_navn
2 	ADD kolonne_navn datatype;
3 	
4 	ALTER TABLE Kunder
5 	ADD Kunde_Adresse VARCHAR(50);

Slet en kolonne For at slette en kolonne skal du bruge denne SQL kommando

1 	ALTER TABLE table_navn
2 	DROP COLUMN kolonne_navn;
3 	
4 	ALTER TABLE Kunder
5 	DROP COLUMN Kunde_Adresse;

Ændre en eksisterende kolonne For at ændre en eksisterende kolonne skal du bruge SQL kommando MODIFY

1 	ALTER TABLE table_navn
2 	MODIFY COLUMN kolonne_navn datatype;
3 	
4 	ALTER TABLE Kunder
5 	MODIFY COLUMN Kunde_Adresse VARCHAR(80);

PRIMARY KEY

En primær nøgle er et felt der unikt definere en bestemt post. Primær nøgle felter skal indeholde unikke værdier og må ikke være tomme (NULL).

En tabel kan kun have en primær nøle, men denne kan godt være en kombination af flere felter.

Du tilføjer en primær nøgle, ved oprettelsen af en tabel, på følgende måde:

 1 	CREATE TABLE Kunder
 2 		(
 3 			Kunder INT PRIMARY KEY,
 4 			Kunde_Navn VARCHAR(60)
 5 		);
 6 	    
 7 	CREATE TABLE Kunder
 8 		(
 9 			Kunde_Id INT,
10 			Kunde_Navn VARCHAR(60),
11 			PRIMARY KEY (Kunde_Id)
12 		); 

Hvis du skal tilføje en primær nøgle efter at tabeller er oprette skal du bruge ALTER TABLE

1 	ALTER TABLE Kunder
2 	ADD PRIMARY KEY (Kunde_Id);
3 
4 eller med et navn
5 
6 	ALTER TABLE Kunder
7 	ADD CONSTRAINT PK_KundeId PRIMARY KEY (Kunde_Id);	

Det er ikke et krav at den tabel har en primær nøgle, men det er klart at anbefale.

NOT NULL

Et felt uden en værdi har i SQL værdien NULL. Det må ikke forveksles med værdien 0. En NULL værdi kommer når der ikke indsættes nogen værdi i feltet.

Som standard kan alle kolonner, undtaget primærnøgle kolonnen, indeholder NULL værdier. Det er muligt for dig at styre om det skal være muligt at indsætte NULL værdier eller ej. På denne måde kan du gennemtvinge at et felt altid indeholder en værdi.

1 	CREATE TABLE	Kunder
2 	(
3 		Kunde_Id INT NOT NULL,
4 		Kunde_Navn VARCHAR(60) NOT NULL,
5 		Kunde_Adresse VARCHAR (50) 
6 	);

Hvis du efter at have oprettet en tabel har behov for at tilføje en NOT NULL betingelse skal du bruge denne SQL kommando:

1 	ALTER TABLE Kunder
2 	MODIFY Kunde_Adresse VARCHAR (50) NOT NULL;

Tit er der behov for at finde felter der har værdien NULL. For at gøre dette skal du bruge IS NULL kommandoen.

1 	SELECT *
2 	FROM tabel_navn
3 	WHERE kolonne_navn IS NULL

Northwind Syntaks IS NULL

1 	select * 
2 	from Employees
3 	where Fax is null;

Du kan også bruge IS NOT NULL

1 	SELECT *
2 	FROM tabel_navn
3 	WHERE kolonne_navn IS NOT NULL

DEFAULT

Det er muligt at oprette en default værdi for en kolonne, der indsættes hvis der ikke specificere en værdi.

1 	CREATE TABLE Kunder
2 		(
3 			Kunde_Id INT PRIMARY KEY,
4 			Kunde_Navn VARCHAR(60),
5 	        Kunde_OpretDate DATETIME DEFAULT NOW()
6 		);

Du kan slette en default betingelse med

1 	ALTER TABLE Kunder
2 	ALTER COLUMN Kunde_OpretDate DROP DEFAULT;	

AUTO INCREMENT

Ved at bruge AUTO INCREMENT kan du automatisk få et fortløbende nummer på en kolonne. Det vil typisk være din primær nøgle.

1 	CREATE TABLE	Kunder
2 	(
3 		Kunde_Id INT PRIMARY KEY AUTO_INCREMENT,
4 		Kunde_Navn VARCHAR(60) NOT NULL,
5 		Kunde_Adresse VARCHAR (50) 
6 	);

Som default starter AUTO INCREMENT på 1, men det kan du ændre ved at bruge denne SQL kommando

1 	ALTER TABLE Kunder AUTO_INCREMENT=100;

Bemærk - hvis du sletter en post vil du ikke få dens “nummer” igen.

INSERT INTO

En tabel uden data bliver hurtig kedelig - for at indsætte data i en tabel skal du bruge SQL kommandoen INSERT INTO

1 	INSERT INTO tabel_navn (kolonne1, kolonne2, ...)
2 	VALUES (værdi1, værdi2, ...);
3 	
4 	INSERT INTO Kunder (Kunde_Id, Kunde_Navn, Kunde_Adresse)
5 	VALUES (100, 'Vin importen', 'Kongevejen 2');

Hvis du tilføjer værdier for alle felter er det ikke nødvendigt at angive kolonnerne. Det er dog vigtigt at du holder rækkefølgen.

1 	INSERT INTO Kunder
2 	VALUES (100, 'Vin importen', 'Kongevejen 2');

Hvis du kun vil tilføje værdier til bestemte felter skal disse angives

1 	INSERT INTO Kunder (Kunde_Id, Kunde_Navn)
2 	VALUES (101, 'Cykel importen');

UPDATE

Hvis du vil ændre på eksisterende værdier skal du bruge UPDATE kommandoen.

1 	UPDATE tabel_navn
2 	SET kolonne1 = værdi1, kolonne2 = værdi2, ...
3 	WHERE betingelse;
4 	
5 	UPDATE Kunder
6 	SET Kunde_Navn = 'Hellstern'
7 	WHERE Kunde_Id = 2;

Bemærk - hvis du ikke har en WHERE betingelse vil det være hele kolonnen der bliver opdateret.

Det vil typisk være primær nøglen der bruges i WHERE betingelsen.

DELETE

Hvis du vil slette en post skal du bruge DELETE

1 	DELETE FROM tabel_navn
2 	WHERE betingelse;
3 	
4 	DELETE FROM Kunder
5 	WHERE Kunde_Id = 2;

Husk at bruge en WHERE betingelser, elleres vil du få slettet alle poster i tabellen.

SAFE MODE

Som standard har MySQL en sikkerhed indbygget der gør at du ikke kan slette (DELETE) eller opdatere (UPDATE) uden at du angiver en WHERE betingelse med en primærnøgle.

Disse virker IKKE hvis SAFE MODE er aktiv.

1 	DELETE FROM Kunder
2 	WHERE Kunde_Navn = 'Cykel Importen';
3 	
4 	UPDATE Kunder
5 	SET Kunde_Adresse = 'Kongevejen 4'
6 	WHERE Kunde_Navn = 'Cykel Importen';

Hvis du ønsker at slette eller opdatere uden at bruge en primærnøgle, kan du ændre denne sikkerhed indstilling på 2 måder.

Via SQL Indsæt denne SQL kommando før din DELETE/UPDATE sætning

1 	SET SQL_SAFE_UPDATES = 0;

Da denne indstilling faktisk er en rigtig god sikkerheds indstilling vil det være en anbefaling at sætte den retur når du har udført dine kommandoer.

1 	SET SQL_SAFE_UPDATES = 1;

Via GUI-Workbench Du kan også gøre dette via Workbench, fordelen/ulempen ved denne metode er at det er en permanent indstilling.

Edit > Preferences > Sql Editor > Safe Updates

Fjern markering i Safe Updates

Opgave 2

Du kan hente opgave 2 her som PDF - Link

Kursusgang 3

Normalisering

Formået med normalisering er at undgå redundans og gøre databasen lettere at vedligeholde.

Redundans er er gentagelser - Når en information står flere gange

Der findes 6 normalformer og en “stramning” af den tredie ved navn Boyce-Cood normalformen.

Typisk fortager man kun en normalisering op til den tredie normalform.

1. Normalform (NF1)

Definition: En relation er på første normalform, hvis ingen af dens domæner har elementer, der i sig selv er mængder.

• Tabellen har et nøglefelt (behøver ikke at være unikt)
• Der må kun være en værdi af samme type i hver post
• Alle poster skal være lige lange dvs. have samme antal felter

2. Normalform (NF2)

Definition: En relation er på anden normalform, hvis den er på første normalform, og hvis enhver ikke-nøgle-attribut er fuldt funktionelt afhængig af enhver kandidatnøgle i relationen.

• Tabellen skal opfylder 1. Normalform
• Der skal være en primærnøgle

3. normalform (NF3)

Definition: En relation er på tredje normalform, hvis den er på anden normalform og det gælder, at ingen ikke-nøgle-attribut er transitivt afhængig af nogen kandidatnøgle i relationen.

• Tabellen skal opfylder 2. Normalform
• Hvis der er mere end et felt der kan sættes som nøgle for andre felter skal tabellen opdeles i flere

Eksempel - Skole

Udgangspunktet er et regneark der bruges til at register elever og deres karakter.

NF 0

Hvad er problemet?

NF 1

NF 2

NF 3

ER-Diagram

Over Skole i normalform 3. Med angivelse af datatyper og relationer melle de 5 tabeller.

Du kan bruge Workbench til at tegne dit ER-diagram. Jeg vil dog altid anbefale at du starter med tegne ER-Diagrammet på et stykke papir.

SQL kode Du kan hente SQL koden her:

Relationer

MySQL er en relationel database, hvilket betyder at data er opdelt i tabeller som det er muligt at oprette relationer i mellem.

Hver tabel har en primærnøgle, som består af et eller flere felter i tabellen. Primærnøglen skaber en unik identifikation af den enkelte række i tabellen.

En fremmednøgle er et felt i en tabel, som peger på en primærnøgle i en anden tabel og dermed skaber relation mellem de to tabeller.

Der findes tre typer af relationer:

• En til Mange
• En til En
• Mange til Mange

En til Mange

Det er denne relations type der typisk er flest af i en database.

I Northwind databasen har vi en typisk En til Mange relation mellem tabellerne Customers og Orders.

Hver kunde står kun en gang i Customeres tabellen, men da hver kunde har en eller flere ordre står kunden flere gange i tabellen Orders.

Det vil sige at relationen går fra primær nøglen CustomerID (En siden) i tabellen Customeres til fremmednøglen i, CustomerID i tabellen Orderes (Mange siden).

En til En

Denne relations type bruges kun brugt af to grunde:

• Sikkerhed
• Opdeling af en meget stor tabel

Årsagen er at man ligeså godt kunne have alle data i en tabel.

Eksempel

Et eksempel kunne være fortrolige oplysninger som:

• Cpr nummer
• Løn
• Pension

Der er gemt i en selvstændig tabel som så er relateret til en tabel med det mere “almendelige” oplysninger som navn, mobilnummer, e-mail osv.

Mange til Mange

Det er ikke muligt at oprette en Mange til Mange relation i MySQL databasen.

En Mange til Mange relation skal løses ved at indskyde en ekstra tabel og så oprette to stk. En til Mange relationer.

I Northwind databasen er der mange ordre og der er mange produkter - hver ordre består af et eller flere produkter.

Der er altså en Mange til Mange relation mellem tabellerne Orders og Products.

Dette løses var at oprette en ekstra tabel - Order_details - Da denne relationen melle de to tabeller ikke kan oprettes direkte.

JOIN

Du skal bruge SQL kommandoen JOIN for hente data fra flere tabeller.

Der findes 4 forskellige JOIN typer:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN (ikke understøttet i MySQL)
• SELF JOIN

INNER JOIN

Henter fællesmængden fra de to tabeller - Altså der hvor værdierne er ens.

LEFT JOIN

Henter alle data fra den venstre tabel (table1) og de data der matcher fra den anden tabel (tabel2).

Kaldes også LEFT OUTER JOIN

RIGHT JOIN

Henter alle data fra den højre tabel (table2) og de data der matcher fra den anden tabel (tabel1).

Kaldes også RIGHT OUTER JOIN

FULL JOIN

Der findes også en FULL JOIN, der henter alle data fra begge tabeller.

Bemærk at MySQL IKKE understøtter FULL JOIN

SELF JOIN

En SELF JOIN joiner en tabel med sig selv. Tabellen Employees i Northwind databasen har et eksempel på dette.

Der er ef felt ReportsTo der referer til primærnøglen EmployeeID i sammen tabel.

VIEW

Et VIEW kan betragtes som en virtuel tabel. Et VIEW har kolonner og poster som en tabel, men den bliver oprettet via en SQL sætning på basis af en eller flere andre tabeller.

Et VIEW kan erstatte en kompliceret SQL sætning. Det er altså nemmere for brugeren at hente data fra et VIEW.

Et VIEW vil altid vises de nyeste data.

 1 	CREATE VIEW view_navn AS
 2 	SELECT kolonne_1, kolonne_2, ....
 3 	FROM tabel
 4 	WHERE betingelse;
 5 
 6 -- Eksempel fra Northwind
 7 	
 8 	create view Salg_Pr_Kunde AS
 9 	select 
10 		Customers.CustomerID,
11     		Customers.CompanyName,
12     		sum(Order_Details.UnitPrice * Order_Details.Quantity) as Salg
13 	from Customers
14 	join Orders
15 	on Customers.CustomerID = Orders.CustomerID
16 	join Order_Details
17 	on Orders.OrderID = Order_Details.OrderID
18 	group by Customers.CustomerID, Customers.CompanyName;

Du finder VIEW i en selvstændig mappe i din database

Du finder VIEW i en selvstændig mappe i Worksbench.

Opgave 3

Du kan hente opgave 3 her som PDF - Opgave 3

Kursusgang 4

Funktioner

MySQL har mange funktioner som du kan bruge, nogle eksempler er:

• DAY()
• MONTH()
• YEAR()

Eksempel fra Northwind

1 SELECT 
2 	OrderID,
3     OrderDate,
4     DAY(OrderDate) as Dag,
5     MONTH(OrderDate) as Måned,
6     YEAR(OrderDate) as År
7 FROM Orders;

Du kan finde en oversigt her Link

Beregninger

Du har også mulighed for at fortage beregninger på dine data, det kan være simple beregninger som det at gange to kolonner med hinanden eller det kan være beregninger hvor du brugere nogle funktioner.

SQL Beregninger

• Plus: +
• Minus: -
• Gange: *
• Division: /
• Modulus: %

Eksempel fra Northwind

1 SELECT
2 	OrderID,
3     ProductID,
4     UnitPrice,
5     Quantity,
6     Discount,
7     UnitPrice * Quantity as Pris_Uden_Rabat
8 FROM Order_Details;

Brug af Funktioner

Du kan bruge forskellige beregnings funktioner på dine data.

 1 -- AVG
 2 SELECT AVG(UnitPrice)
 3 FROM Products;
 4 
 5 -- MIN
 6 SELECT MIN(UnitPrice)
 7 FROM Products;
 8 
 9 -- MAX
10 SELECT MAX(UnitPrice)
11 FROM Products;
12 
13 -- COUNT 
14 SELECT COUNT(ProductID)
15 FROM Products;
16 
17 -- SUM
18 SELECT
19     SUM((UnitPrice * Quantity) - (UnitPrice * Quantity * Discount)) AS Sale
20 FROM Order_Details;

GROUP BY

GROUP BY-sætningen bruges ofte sammen med en beregnings funktioner som f.eks. AVG, MIN, MAX, COUNT og SUM til at gruppere dit resultat.

Det kan f.eks. være det totale salg på en kunde eller et produkt.

 1 SELECT
 2 	Order_Details.OrderID,
 3 	COUNT(Order_Details.Quantity) AS Antal,
 4     SUM((UnitPrice * Quantity) - (UnitPrice * Quantity * Discount)) AS Sale
 5 FROM Order_Details
 6 GROUP BY Order_Details.OrderID;
 7 
 8 SELECT
 9 	Employees.LastName,                            
10     COUNT(Orders.OrderID) AS NumberOfOrders
11 FROM Orders INNER JOIN Employees
12 ON Orders.EmployeeID = Employees.EmployeeID
13 GROUP BY LastName:

HAVING

Hvis du vil “udvælge” data på basis af en gruppering (GROUP BY) kan du IKKE bruge WHERE.

Dette eksempel fra Northwind vil ikke virke

1 SELECT 
2 	Country,
3 	COUNT(CustomerID)
4 FROM Customers
5 GROUP BY Country
6 WHERE COUNT(CustomerID) > 5;

Når der er fortaget en gruppering på “CustomerID” skal du bruge SQL kommandoen HAVING for at det virker.

Samme eksempel fra Northwind, men med HAVING

1 SELECT 
2 	Country,
3 	COUNT(CustomerID)
4 FROM Customers
5 GROUP BY Country
6 HAVING COUNT(CustomerID) > 5;

Det er muligt at bruge WHERE sammen med GROUP BY det skal bare være så du udvælger på data der ikke er grupperet på.

Eksampel fra Northwind

1 SELECT 
2 	Country,
3 	COUNT(CustomerID)
4 FROM Customers
5 WHERE Country IN ('UK', 'USA', 'Italy')
6 GROUP BY Country
7 HAVING COUNT(CustomerID) > 5;

INDEX

Du kender et index fra en bog, hvor der typisk er et index bageret i bogen. Det gør det nemmere og hurtigere at finde et bestemt ord/emne.

Det sammen gælder indexer i SQL, de gør det hurtigere at finde data.

Som standard oprettes der et index når du opretter en primær nøgle. Dette index er et unikt index - det er jo en primær nøgle.

Du opretter et index med SQL kommandoen CREATE INDEX

1 CREATE INDEX index_navn
2 ON tabel_navn (felt_navn);
3 
4 CREATE INDEX FK_CustomerID
5 ON Orders (CustomerID);

Brugeren kan ikke se de indexer der er oprette, men du kan se dem under den enkelte tabel i mappen Indexes

Det er også muligt at oprette et index hvor du kombinere flere kolonner.

Eksempel fra Northwind

1 CREATE INDEX Emp_name
2 ON Employees (LastName, FirstName);

UNIQUE INDEX

Det er muligt at oprette et unikt index, ligesom det index der oprettes ved primær nøglen. Det kan f.eks. bruges til at sikre at en kunde ikke oprettes to gange - kunne f.eks. være kundes CVR nummer.

1 CREATE UNIQUE INDEX index_navn
2 ON tabel_navn (felt_navn);
3 
4 CREATE UNIQUE INDEX UniqProduct
5 ON Products (ProductName);

DROP INDEX

Du kan fjerne et index med kommandoen DROP INDEX kombineret med ALTER TABLE

1 ALTER TABLE tabel_navn
2 DROP INDEX index_navn;
3 
4 ALTER TABLE Employees
5 DROP INDEX Emp_name;

Hvilke felter

Hvilke felter skal du så oprette indekser på?

Som udgangspunkt bliver der oprettet et unikt index på dine primærnøgle. Ud over det vil det typisk være en fordel at oprette indekser på de felter der er fremmede nøgler.

Dernæst kan du med fordel oprette indekser på de felter hvor der søges meget. Det kan være svært at vide hvilke felter det er, en mulighed er at spørgere brugerne eller bruge et analyse værktøj til at finde disse felter.

Der er dog en ulempe ved oprettelsen af indekser - Det at gemme data bliver langsommere. årsagen er at indekserne skal opdateres med de nye data.

Hvis man skal indsætte eller opdatere store mængder data kan det være en fordel at slette dine indekser først og så oprette dem igen, når indsættelsen af data er færdig.

CASE

En CASE sætning opsætter forskellige betingelser som data så “holdes” op imod og der returneres en værdi i forhold til det.

En CASE sætning virker som en IF-THEN-ELSE sætning.

Når en værdi er SAND, falder indenfor en CASE, så returneres resultatet.

Hvis ingen betingelser er sande, returnerer den værdien i ELSE-klausulen.

1 CASE
2     WHEN betingelse_1 THEN resultat_1
3     WHEN betingelse_2 THEN resultat_2
4     WHEN betingelse_n THEN resultat_n
5     ELSE resultat
6 END;

Eksempel fra Northwind

1 SELECT 
2 	OrderID, 
3     Quantity,
4 CASE
5     WHEN Quantity > 30 THEN 'Antal er større end 30'
6     WHEN Quantity = 30 THEN 'Antal er 30'
7     ELSE 'Antal er under 30'
8 END AS 'Antal ordre'
9 FROM Order_Details;

Kursusgang 5

STORED PROCEDURE

En STORED PROCEDURE (SP) er SQL-kode, som du har gemt, så koden kan bruges igen og igen.

Hvis du har en SQL-forespørgsel, du skriver igen og igen, vil det være en fordel at gemme den som en SP. Du skal så bare “kalde” den for at få udført SQL koden.

Du kan også sende parametre/variabler til en SP. Hvilket gør at én SP kan bruges til flere forskellige udtræk.

Det kan f.eks. være; kundenummer, produktnummer, dato/måned/år eller lignede du brugere som parameter/variabel.

Syntaks for en STORED PROCEDURE

1 CREATE PROCEDURE procedure_navn
2 AS
3 	sql_kode;
4 GO;

Når ud skal “kører”/afvikle en STORED PROCEDURE skal du bruge denne SQL kommando

1 EXEC procedure_navn;

En SP bliver gemt i databasen, du kan se hvilke SP der ligger på den enkelte database i mappen Stored Proceduers

Der er flere fordele ved at bruge SP:

• Hvis den sammen SP bruges flere gange af den sammen forbindelse/applikation vil den blive afviklet hurtigere.
• Der er mindre data-trafik mellem en applikation og MySQL databasen, da det kun er navnet på SP og evt. parameter der sendes over netværket.
• Det er mere sikkert at bruge en SP, da adgange er begrænset til det SP har adgang til.

Der er dog også nogle ulemper ved at bruge SP:

• Det er svært at fejl-finde og fejl-rette en SP
• Det kan være svært at vedligehold og have et overblik over mange SP
• Hvis der er mange logiske operationer i en SP vil den bruge meget RAM.

DELIMITER

Når du skal oprette en SP er det nødvendigt først at bruge en anden SQL kommando, DELIMITER.

Problemet er at vi ønsker at sende hele SQL kommandoen for SP til serveren. I den SQL kode der indgår i SP’en er der en “indlejret” SQL kode afsluttes med ;.

Det er derfor nødvendigt at ændre DELIMITER til noget andet end det normale ; - oprette SP - sætte DELIMITER retur til ;.

Du kan bruge flere forskellige ““tegn” til DELIMITER f.eks. // eller $$

Her sættes DELIMITER til // og tilbage til ;

1 DELIMITER //
2 
3 CREATE PROCEDURE procedure_navn
4 AS
5 	sql_kode;
6 GO;
7 
8 DELIMITER ;

Det er ikke kun når du opretter SP at det er nødvendigt, det glæder f.eks. også når du skal oprette Trigger.

VARIABLE

En af de store fordele ved en SP er at du kan bruge variable, hvilket gør dine SP’er mere fleksible.

Når du vil bruge en variabel skal du angive (dimensionere) dens datatype. Det er de sammen datatyper som ved oprettelse af felter i en tabel du skal bruge. Typisk vil datatypen være det sammen som feltet du brugere variablen op mod.

Eksempel Northwind med én variabel (Aar) af typen INT - her er det retur typen af funktionen YEAR der afgør datatypen.

1 DELIMITER $$
2 
3 CREATE PROCEDURE OrderByYear(Aar INT)
4 BEGIN
5 	SELECT * FROM Orders
6 	WHERE YEAR(OrderDate) = Aar;
7 END $$
8 
9 DELIMITER ;

Når du vil afvikle denne SP gøres det på følgende måde.

Eksempel med året 1996

1 CALL OrderByYear(1996);

Det er også muligt at brugere flere variabler. De skal bare angives adskilt af et ,

Eksempel fra Northwind

 1 DELIMITER $$
 2 CREATE PROCEDURE OrderByYearMonth(Aar INT, Mdr INT)
 3 BEGIN
 4 	select 
 5 		Products.ProductID,
 6 		Products.ProductName
 7 	from Products
 8 	join Order_Details
 9 	on Products.ProductID = Order_Details.ProductID
10 	join Orders 
11 	on Order_Details.OrderID = Orders.OrderID
12 	where year(Orders.OrderDate) = Aar and
13 		month(Orders.orderdate) = Mdr;
14 END $$
15 DELIMITER ;

Når du skal afvikle en SP med flere variabler skal angive dem i sammen rækkefølge som de er oprettet.

1 CALL OrderByYearMonth (1997, 5);

Vis STORED PROCEDURE

Du kan få vist de SP’er der er i den enkelte database med denne SQL kommando

1 SHOW PROCEDURE STATUS WHERE db = 'database_navn';

Hvid du geren vil se SQL koden for en bestem SP kan du bruge denne SQL kommando:

1 SHOW CREATE PROCEDURE procedure_navn;

Slet STORED PROCEDURE

Du sletter en SP ved at brugere denne SQL kommando

1 DROP PROCEDURE `datbase_navn`.`sp_navn`;

Tip - Du kan også højreklikke på en SP i Workbench og vælge DROP Stored Procedure

TRIGGER

En Trigger er SQL kode, der afvikles automatisk, når en af følgende handlinger sker

• INSERT
• UPDATE
• DELETE

på den tabel hvor triggeren er oprettet.

Du kan betragte en TRIGGER som en speciel STORED PROCEDURE. Forskellen er at en TRIGGER bliver afviklet automatisk ved en af de tre førnævnte handlinger. Mens en STORED PROCEDURE skal afvikles “manuelt”.

Triggere er gode til opgaver som:

• håndhævelse af forretningsregler
• validering af input
• audit trial - typisk ved at skrive til andre tabeller

TRIGGER Syntaks

Du opretter en TRIGGER på følgende måde:

1 CREATE TRIGGER 
2 	trigger_navn 
3 	trigger_time 
4 	trigger_event
5  ON tabel_navn
6  FOR EACH ROW
7  BEGIN
8  	SQL kode
9  END;

• trigger_time er enten BEFORE eller AFTER
• trigger_event er; INSERT, UPDATE eller DELETE
• Din SQL kode skal placeres mellem BEGIN og END

Eksemple fra Northwind

 1 DELIMITER $$
 2 
 3 CREATE TRIGGER before_employee_update 
 4     BEFORE UPDATE ON Employees
 5     FOR EACH ROW 
 6 BEGIN
 7     INSERT INTO employees_audit
 8     SET action = 'update',
 9 		EmployeeID = OLD.EmployeeID,
10         LastName = OLD.LastName,
11         changedat = NOW(); 
12 END $$
13 
14 DELIMITER ;

En TRIGGER bliver gemt “under”/på en tabel - den tabel som en af de tre handlinger udføres på.

Vis TRIGGER

Du kan med denne SQL kommando se dine TRIGGER

 1 SHOW TRIGGERS;
 2 
 3 eller
 4 
 5 SHOW TRIGGERS FROM northwind;
 6 
 7 eller
 8 
 9 SHOW TRIGGERS FROM northwind
10 WHERE `table` = 'Employees';

Bemærk tegnet før og efter table

Der er også en anden måde du kan se de forskellige TRIGGER du har. Det er sådan at definitionen på dine TRIGGERE bliver gemt i en system tabel ved navn: information_schema

Se alle TRIGGERE på en bestemt database

1 SELECT * FROM
2 	information_schema.triggers
3 WHERE
4     trigger_schema = 'database_navn';

Der er også muligt at finde alle TRIGGERE på en bestem tabel.

1 SELECT * FROM
2     information_schema.triggers
3 WHERE
4     trigger_schema = 'database_navn'
5         AND event_object_table = 'table_navn';

Slet TRIGGER

Du sletter en TRIGGER ved at brugere denne SQL kommando

1 DROP TRIGGER trigger_navn;

EXPORT - IMPORT CSV

Det er muligt både at eksportere og importere data fra CSV (Comma Separated Values) filer via SQL kommandoer.

Fordelen ved at bruge CSV er at filformatet kan læses af mange andre programmer.

EXPORT

Når du skal eksportere data skal du angive følgende parameter:

• Filnavn: INTO OUTFILE
• Tegn der “omslutter” felterne: FIELDS ENCLOSED BY ‘”‘
• Escape karakter bruges i tilfælde af specialtegn: TERMINATED BY ‘;’
• Afslutning: ESCAPED BY ‘”‘
• Linje skift: LINES TERMINATED BY ‘\r\n’;

Ud over det skal du udvælge de data du geren vil eksportere. Til det brugere du en almindelig SELECT sætning evt. med en WHERE på.

Eksempel fra Northwind der eksportere alle kunder fra USA.

Bemærk at der bruges en UNION ALL for at få overskrifter med.

 1 SELECT
 2 		'CustomerID',
 3         'CompanyName',
 4         'Address',
 5         'City',
 6         'Region',
 7         'PostalCode',
 8         'Country'
 9 UNION ALL
10 SELECT 
11 		CustomerID,
12         CompanyName,
13         Address,
14         City,
15         Region,
16         PostalCode,
17         Country
18 FROM Customers
19 WHERE Country = 'USA'
20 INTO OUTFILE '/var/lib/mysql-files/Customer_USA.csv' -- Linux
21 -- INTO OUTFILE 'C:\\Customer_USA.csv' -- Windows
22 FIELDS ENCLOSED BY '"'
23 TERMINATED BY ';'
24 LINES TERMINATED BY '\n';

IMPORT

Der er også muligt at importere data fra en CSV fil.

Her er det eksemplet fra eksporten der importeres ind i en tabel med navnet Customers_USA. Den tabel skal oprettes først.

1 -- LOAD DATA INFILE 'C:\\Customer_USA.csv' -- Windows
2 LOAD DATA INFILE '/var/lib/mysql-files/Customer_USA_2.csv' -- Windows
3 INTO TABLE Customers_USA
4 FIELDS ENCLOSED BY '"'
5 TERMINATED BY ';'
6 LINES TERMINATED BY '\n'
7 IGNORE 1 ROWS;

Bemærk at den første ROW ikke importes, da den indeholder overskrifter.

Kursusgang 6

Sikkerhed

SQL Injection

Brugere

I MySQL kan du angive ikke kun hvem der har adgang til MySQL serveren, men også fra hvilken “host”, som brugeren forbinder fra.

Derfor består en brugerkonto i MySQL af et brugernavn og et “hostnavn” adskilt af et @.

For eksempel, hvis admin-brugeren forbinder til MySQL serveren fra localhost, er brugerkontoen admin@ localhost.

Det er sådan at admin-brugeren kun kan forbinde fra localhost ikke fra en remote-host, hvilket er med til at gøre MySQL serveren mere sikker.

Det er muligt at opsætte flere konti med samme navn ved at kombinere brugernavnet og host-navnet. Der kan så oprettes forbindelse fra forskellige host med forskellige rettigheder.

MySQL gemmer brugerkonti i mysql databasen i tabellen user.

Du kan se alle brugere med denne SQL kommando

1 SELECT user FROM mysql.user; 
2 
3 eller
4 
5 SELECT * FROM mysql.user;