Skapa Databas

(1)

Sven Åke Johansson

Institutionen för Kommunikation o design

Sven Åke Johansson Institutionen för Datavetenskap, Fysik och Matematik

 Uppstart

 Inloggning

 SSMS

 Skapa Databas

 Skapa Tabell

 Skapa Diagram, Fk, RI

 Hantering av Index, Pk, Fk, Ix

 Constraints

 Beräknande fält

 Några funktioner

Kapitel 5 och 6.

Beginning SQL Server 2008 for Developers

I

NNEHÅLL

(2)

Sven Åke Johansson

I

NLOGGNING VIA

SSMS

Det finns två metoder på hur ett login kan kontrolleras, authenticate.

Windows Authentication är den säkraste metoden. Då sker kontrollen via Windows inloggningssystem. Rekommenderas.

Login uppgifter hämtas från Windows.

Användaren måste alltså finnas i Windows.

SQL Server Authentication är den metod där kontrollen enbart sker i SQL Server. Användaren behöver inte finnas i Windows.

Inloggning på skolan sker först efter att du startat VPN anslutningen. Därefter kan du starta SQL Server Management Studio (SSMS) och logga in. Logga in enligt de instruktioner du fått.

2

(3)

Sven Åke Johansson

SSMS S

QL

S

ERVER

M

ANAGEMENT

S

TUDIO

3

När du väl har loggat in så öppnar sig SSMS. Observera att utseende kan varierar beroende på vilken version du använder. Express har inte all funktionalitet. Utformningen beror också på vad installationen

innehåller.

Object Explorer Resultarea

(4)

Sven Åke Johansson

T

OOLS

- O

PTIONS

Ska vara avbockad under utbildningen. Annars kan du inte ändra strukturen på en redan sparad tabell.

Funktionen är en säkerhetsfråga som kommit till i version 2008. Syftar till att man inte oavsiktligt ska ändra strukturen på en tabell.

4

Här finns det mycket du kan undersöka och påverka.

(5)

Sven Åke Johansson

S

KAPA EN DATABAS

1 Högerklicka på Databases

Välj New Database… 2 Ange namnet på databasen

Bestäm ev begynnelsestorlek och ökningsgrad.

3

5

OBS!

Ägare till databasen blir den som är inloggad. <default> är du vilket gör att du inte ska (behöver) ändra.

(6)

Sven Åke Johansson

S

KAPA TABELL

Lägg in fälten och bestäm datatyp för varje fält.

Högerklicka på Tables.

Välj New Table…

1 2

Markera fältet för Pk och klicka på nyckeln.

3

Bestäm egenskaper för fälten 4

6

(7)

Sven Åke Johansson

F

ÄLT

E

GENSKAPER

/ C

OLUMN

P

ROPERTIES

Allow Nulls Får fältet innehålla Null?

Normalt ska du inte tillåta Null men det finns anledningar….

Data Type Vilken datatyp ska fältet ha.

Default Value Förinställt värde. Om du inte själv lägger in ett värde så sätts detta automatiskt när posten sparas vid ny post.

Computed Column… Beräknande funktion i fältet Identity Specifikation Is Identity=Yes anger att SQL

Server ska uppdatera värdet i fältet automatisk.

Identity Increment anger stegen, standard är 1.

Identity Seed anger startvärdet, standard är 1

7

Vad är Null

Null är ingenting.

Null är inget tecken.

0 är ett tecken.

Vid en jämförelse:

Visa alla som är mindre än 0 så visas ändå inte de som har null i fältet.

(8)

Sven Åke Johansson

Index skapas när Pk sätts (hmmm…)

Index namn Välj Fält Indextyp Clustered

Markera fältet och klicka på nyckeln.

Högerklicka på tabellen och välj Indexes

Räknare=Yes Intervall

startvärde I egenskaper för fältet

Pk, Primary Key, Primär Nyckel.

En Pk bör vara numerisk och du bör låta databasen styra innehåll i den.

Dvs använd räknare, Identity. Du bör inte ha en tabell utan Pk. Det går men……krash…

S

KAPA

P

RIMARY

K

EY

, P

K

Clustered: posterna sorteras fysiskt efter värdet i Pk. Inget eget index skapas.

8

(9)

Sven Åke Johansson

S

KAPA

D

IAGRAM

/ F

OREIGN

K

EY

(1

AV

3)

Högerklicka på Database Diagrams Välj New Database Diagrams

1 Välj vilka tabeller som ska med i

diagrammet.

3

Välj Yes på frågan som innebär att diagramobjektet skapas.

2

9

(10)

Sven Åke Johansson

Placera dina tabeller på sätt som du önskar 4

Markera (knappen) Kundid (Pk) i Kund och dra till Kundid i Kontakt och följande bild visas.

Kontrollera att det blivit rätt.

Välj OK 5

1 0

S

KAPA

D

IAGRAM

/ F

OREIGN

K

EY

(2

AV

3)

(11)

Sven Åke Johansson

S

KAPA

D

IAGRAM

/ F

OREIGN

K

EY

(3

AV

3)

Fk, Foreign Key, Främmande Nyckel.

Bestämmer du när du vill ha en relation mellan två tabeller.

När du skapar diagram eller

Högerklicka i en tabell och välj Relationships.

Villkor?

Pk och Fk måste vara av samma datatyp!!!!

Hur ska regelverket vara för den Fk du definierar.

En Fk blir inte automatisk indexerad i MSSQL.

Resultat 6

7

1 1

(12)

Sven Åke Johansson

S

KAPA

I

NDEX PÅ VANLIGT FÄLT

Högerklicka i tabellen i Designläge Välj Indexes Keys…

1

Välj Add och markera därefter IX_Kund*

Klicka på punkterna vid Columns 2

Välj fältet Namn och därefter OK.

Sist väljer du Close 3

1 2

(13)

Sven Åke Johansson

Sven Åke Johansson Institutionen för Datavetenskap, Fysik och Matematik 1 3

I

NDEX

–

^NÄR

/

VAR

/

HUR

?

När behöver vi indexera:

 En Fk bör indexeras om det blir många poster i tabellen. Ingen exakt gräns existerar utan det beror på tabellens komplexitet. Vid ett färre antal poster belastar indexet mer än det hjälper.

 Andra fält bör indexeras om sökning ofta kommer att ske. Man kan alltid söka på alla fält med svarstiden blir längre om det inte finns index på fältet. Men var restriktiv – ha inte för många index.

 Om fältet ska vara unikt, exempelvis organisationsnummer, så indexerar du det och bestämmer att innehållet ska vara unikt.

 En Pk ger alltid ett index eller en omsortering (Clustered) av posterna i tabellen. Clustered är default.

 En Fk ger inte automatisk i MS SQL att ett index skapas på det fält som är Fk. I MySQL indexeras fältet automatiskt som du sätter Fk på.

 Andra fält indexeras endast om du bestämmer att så ska ske. Du måste skapa index manuellt på det eller de fält du vill ha index på.

(14)

Sven Åke Johansson

Lösa Fk (heap)

Du kan ha en Fk utan att använda något index i fält för Fk. Det kommer att ta längre tid men det fungerar.

Indexerade Fk (sorterade / Hash)

Du kan skapa ett eget index på ett fält på

mångasidan som du vill använda till en Fk. Det ger en snabbare metod än Lösa Fk. Du kan dock inte sätta något regelverk (RI) för hur relationen ska fungera.

Definierade Fk (sorterade / Hash)

MS SQL skapar inte index utifrån den Fk du

bestämmer. Du måste indexera fältet själv. Då får du samma effekt som indexerade Fk men du har också ett regelverk (RI) som kan tillämpas. Till

denna typ av Fk sätter du också upp regelverket för Referentiell Integritet.

KundID Namn KundID Tel

1 Kalle 1 123123

2 Otto 2 456456

3 Anna 5 234341

4 Stina 3 1245466

5 Sture 1 23456

4 988654 1 234987

KundID Namn KundID Tel

1 Kalle 1 123123

2 Otto 1 23456

3 Anna 1 234987

4 Stina 2 456456

5 Sture 3 1245466

4 988654 5 234341

V

ARIANTER PÅ

F

K

1 4

(15)

Sven Åke Johansson

Pk TelID Fk KundID

Kundens telefoner ligger utspridda i tabellen.

KundID Namn KundID TelID Tel

1 Kalle 1 1 123123

2 Otto 1 5 23456

3 Anna 1 7 234987

4 Stina 2 2 456456

5 Sture 3 6 1245466

4 3 988654 7 4 234341 KundID Namn TelID KundID Tel

1 Kalle 1 1 123123

2 Otto 2 2 456456

3 Anna 3 4 988654

4 Stina 4 7 234341

5 Sture 5 1 23456

6 3 1245466

7 1 234987

KundID Namn TelID KundID Tel

1 Kalle 1 1 123123

2 Otto 2 2 456456

3 Anna 3 4 988654

4 Stina 4 7 234341

5 Sture 5 1 23456

6 3 1245466

7 1 234987

Sammansatt

Pk TelID & KundID

Kundens telefoner ligger utspridda i tabellen.

P

K

/ F

K

/ S

AMMANSATT

- 3

OLIKA FALL

Sammansatt

Pk KundID & TelID

Kundens telefoner ligger samlade.

I

NDEX PÅ

F

K

H

UR BLIR DET DÅ

?

(16)

Sven Åke Johansson

 Att mer data lagras - Databasen blir större

 Insert, update och delete tar längre tid

Index måste uppdateras samtidigt som data ändras/läggs till

 Det går snabbare visa en sorterad lista om det finns index

(SELECT Enamn, Fnamn FROM Kund ORDER BY Enamn, Fnamn)

 Det går snabbare att hitta en post i stora tabeller om det finns index (SELECT Enamn WHERE Enamn='Berg')

 Relaterade poster hittas lättare om den främmande nyckeln indexeras. Främmande nycklar indexeras med fördel.

(SELECT … JOIN… ON yID=xID)

 Primärnycklar och unika kolumner indexeras vanligen automatiskt

I

NDEX

+ / -

16

(17)

Sven Åke Johansson

A

TT TÄNKA PÅ FÖR BÄTTRE PRESTANDA

17

 Inte tillräckligt med index

Du har inte skapat index på de fält som man ofta söker på. Svarstiderna blir därför onödigt långa. Hur är det med Fk?

 För många index.

Du har lagt in index på fält som inte behöver det. Det skapar långa svarstider vid ändring/radering/inläggning av nya poster.

 Rättigheter på tabell- och kolumnnivå

Undvik att sätta dessa rättighet då det tar onödigt lång tid för databasen att kontrollera rättigheterna.

 Felaktig databasdesign

Din datamodell är felaktig. Kan ex bero på för högt antal tabeller vilket gör att det tar lång tid att sätta samman data. Kan också vara för få med för mycket dubblerade data.

- Använd minsta möjliga datatyp. (tinyint, int, bigint?)

- Börja tabellen med Charfälten. Lägg Varcharfälten till höger.

- Definiera helst NOT NULL på fälten.

(18)

Sven Åke Johansson

C

ONSTRAINTS

- B

EGRÄNSNING

/

VILLKOR

18

Unique

Används normalt på Pk. Ett värde ska vara unikt i ett fält.

En Pk måste vara unik för att det ska gå att identifiera en post unikt. Kan vara sammansatt. Får ej vara Null.

Begreppet innebär att det finns någon form av kontrollfunktion, regel, som databasmotorn använder sig av för att kontrollera att data är riktiga och följer de villkor som vi har bestämt.

Exempelvis så ska en Pk ska vara unik. Ett värde måste vara inom vissa gränser för att få sparas i databasen eller att ett fält ska ha ett visst förinställt värde, Default value.

Default (DF)

Ett värde i en tabell kan vara Default. Exempelvis ska

betalningsvillkor i Faktura vara 30 dagar som default värde (förinställt värde).

Check (CK)

En check, kontroll, ska ske att värde i ett fält alltid motsvarar det förväntade. Det får inte avvika.

Betalningsvillkor får inte överstiga 60 dagar. Datatyp är Tinyint och då kan det vara mellan 0 och 255.

(19)

Sven Åke Johansson

D

EFAULT

C

ONSTRAINTS

19 I faktura ska vi ha att

Betalningsvillkor har ett default värde på 30 dagar.

I faktura ska vi ha att Datum har ett

default värde som är dagens datum

http://msdn.microsoft.com/en-us/library/bb510741%28v=SQL.100%29.aspx getdate() – finns det fler????

(20)

Sven Åke Johansson

C

HECK

C

ONSTRAINTS

I faktura ska vi ha att

betalningsvillkor inte får överstiga 60 dagar

1.Högerklicka i tabellen

2.Välj Check Constraints… i menyn.

4.Skriv in betvillkor<=60

5.Bestäm funktionalitet 3.Välj Add och CK_Faktura

skapas

([Postnr] like '[0-9][0-9][0-9][0-9][0-9]’) Så kan en Check-Constraints se ut för ett post- nummer. Det ska finnas en siffra i varje position.

2 0

(21)

Sven Åke Johansson

B

ERÄKNANDE FÄLT

I faktura vill man att förfallodagen ska räknas fram. Det kan ske med hjälp av fältet Datum och fältet Betvillkor där formeln kan vara:

Datum + Betvillkor

1. Skapa fältet (ex FDatum) 2. Skriv in formeln

Samma beräkning men med annan formel:

dateadd(day,betvillkor,datum)

day anger att betvillkor är i dagar betvillkor är värdet som ska adderas till

värdet i datum

datum är startdatum för beräkningen

http://msdn.microsoft.com/en-us/library/ms186819%28v=SQL.100%29.aspx Vill du veta mer om dateadd - besök Books Online

2 1

(22)

Sven Åke Johansson

H

UR SER FAKTURA UT NU

?

Fälten som finns

Nycklar som finns

Constraints

CK=Check DF=Default Index som finns

2 2

(23)

Sven Åke Johansson

V

YER

/ V

IEW

V

AD ÄR EN VY

En vy är någon form av sammansättning av kolumner från en eller flera tabeller.

Innehållet till en vy hämtas dynamiskt varje gång en vy körs / öppnas. En vy kan ses som en fråga som ställs mot databasen.

En vy mot Kund-tabellen. Alla fälten utom Kundid visas här.

En Kundlista.

En vy som visar data ur Kund, Kontakt och

Kontakttyp. En telefonlista.

2 3 Exempel

Exempel

(24)

Sven Åke Johansson

V

YER

/ V

IEW

E

N TABELL

24

Skapa en ny Vy Välj tabell/er

Välj fält ur tabellen

Kör och resultatet visas

SQL Satsen skapas

1 2

3

4

5

(25)

Sven Åke Johansson

V

YER

/ V

IEW

F

LERA TABELLER

1. Högerklick i vyn.

2. Välj Add table

3. Lägg till flera tabeller

4. Välj fälten

5. Kör (dvs välj

Execute eller

tryck Ctrl R)

(26)

Sven Åke Johansson

N

ÅGRA

F

UNKTIONER