SQL

Databaser & databasdesign
SQL
1
Föreläsningens innehåll
SQL: Syfte?
SQL: Data
definition
SQL: Att skriva SQL
SQL: Data
manipulation
2
Först liten repetition av några
relationella begrepp
(Date, 2000, p.111)
3
SQL: Syfte?



Skapa databaser och relationella strukturer
Utföra diverse datahanteringsuppgifter
Att tillhandahålla möjlighet att ställa både
enkla och komplexa frågor

Indelas i en DDL-del och en DML-del
4
SQL: Att skriva SQL


Mer läsbar SQL-kod:
 Varje sats börjar med en ny rad
 Början av varje rad ska ligga i linje med andra satser
 Då en sats har flera delar, bör varje del börja på en ny
indragen rad
Extended Backus Naur Form (BNF):
 VERSALER - reserverade ord
 Gemener - användardefinerade ord
 a|b - val mellan olika alternativ
 {a} – element som krävs
 [a] – valbart element
 (...) – valbar iteration
5
SQL: Ett exempel
CREATE TABLE n_kund (
kundnummer INT NOT NULL,
namn VARCHAR(50) NOT NULL,
personnummer CHAR(11),
PRIMARY KEY (kundnummer));
6
SQL: Data manipulation
Fyra huvudsakliga typer av frågor:




SELECT – presentera data ifrån databasen
INSERT – lägga till nya data i en tabell
UPDATE – uppdatera innehåll i en tabell
DELETE – ta bort data från en tabell
7
SQL-DML

SQL bygger på
relationsalgebra:
8
SQL/DML: SELECT-sats
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM
TableName [alias] [, ...]
[WHERE
condition]
[GROUP BY columnList] [HAVING condition]
[ORDER BY columnList]
9
SQL-DML: sökning i tabell
Exempel: Vi vill hämta alla rader i tabellen artikel.
 SELECT * FROM artikel;
 Exempel: Vi vill hämta enbart kolumnerna artnr och
artnamn från tabellen artikel.
 SELECT artnr, artnamn FROM artikel;
 Exempel: Vi vill hämta alla artiklar som kostar mer än
1000kr.
 SELECT * FROM artikel
WHERE pris>1000;

operator
=
<>
>
<
>=
<=
betydelse
lika med
skilt från
större än
mindre än
större än eller lika med antal
mindre än eller lika med
exempel
artnr=’10001’
artnr<>”10002”
pris>1000
pris<20
>=50
pris<=20
10
SQL-DML

Det finns även inbyggda funktioner
Exempel: Hur många artiklar har vi?
 SELECT COUNT (*) FROM artikel;

11
SQL-DML

UPDATE (uppdatera befintliga data)
Exempel: Vi vill ändra pris till 33kr och lagerantal till
10 för artkel 10001.
 UPDATE artikel
SET pris=33.00, lagerantal=10
WHERE atrnr=’10001’;


INSERT (lägga till en ny rad)
Exempel: Vi vill lägga till en ny artikel med
artikelnummer 10006 och namnet SuperComputer.
De andra fälten lämnas i detta fall blanka.
 INSERT INTO artikel
(artnr, artnamn)
VALUES (’10006’, ’SuperComputer’);

12
SQL-DML

DELETE (ta bort rad/rader)
Exempel: Vi vill ta bort alla rader i tabellen artikel.
OBS! Anges inga villkor tas ALLA rader bort!!!
 DELETE FROM artikel;
 Exempel: Vi vill ta bort artikeln 10006.
 DELETE FROM artikel
WHERE artnr=’10006’;

13
En liten övning i SQL

a) Vilka leverantörer
finns det?
(Visa namn)

b) Vilka artiklar är
beställda från
leverantörer?
(Visa antnr och antal)

c) I vilka orter finns våra
leverantörer?
(Obs! Ej dubletter!)
Segerlund, M. & Stridsman, F. (1998). SQL-introduktion, p29. Lysekil: Pontes.
14
SQL: DDL skapa en tabell
Exempel: Skapa tabellen artikel (artnr, artnamn,
lagerantal, bestpunkt, pris). Det finns olika typer av
dataformat som används beroende på vad som ska
lagras, tex CHAR(n) – n tecken, INTEGER – ett
heltal eller DECIMAL(n,m) – ett decimaltal med n
siffror och m decimaler. Dessa format kan variera
beroende på vilken databashanterare vi arbetar mot.
NOT NULL innebär att ett värde måste matas in, tex
för ett primärnyckelfält.
 CREATE TABLE artikel
(artnr CHAR(5) NOT NULL,
artnamn CHAR(15) NOT NULL,
lagerantal INTEGER,
bestpunkt INTEGER,
15
pris DECIMAL(5,2));

SQL-DDL
Utöka tabeller
 Exempel: Vi vill lägga till en enhetskolumn till
tabellen artikel.
 ALTER TABLE artikel
ADD (enhet CHAR(3));
 Ta bort en tabell
 Exempel: Vi vill ta bort tabellen artikel.
 DROP TABLE artikel;
 Skapa index
 att skapa en unik nyckel i en tabell
 att göra sökningar snabbare
 Exempel: Vi vill göra artnr till unikt index.
 CREATE UNIQUE INDEX artindex
ON artikel(artnr);

16
Open DB Connectivity (ODBC)
är ett funktionsbibliotek utformat för att tillhandahålla ett
gemensamt programmeringsgränssnitt (API: Applikation
Programming Interface) mot det underliggande
databassystemet.
 Vi kan nå databaser över nätverket med hjälp av ODBC.
 Det unika med ODBC är att ingen leverantörsspecifik
kod behövs. Vi kan tex använda samma kod för frågor
till en tabell i MS SQL-server, Informix eller i MySQL...
 ODBC gör programmen mer portabla mellan olika
system, men långsammare än när man använder
systemspecifika DB bibliotek.

Program anropar
ODBCs
funktioner
Drivrutinshanterare läser in
ODBC-drivrutin
ODBC-anrop
sänder SQLfrågor, returnerar
resultat...
Datakälla:
underliggande
DBMS
17
Database Languages

Data Definition Language (DDL)



Data Manipulation Language (DML)


allows user to tell system exactly how to manipulate data.
Non-Procedural DML


Provides basic data manipulation operations on data held in
the database.
Procedural DML


Allows the DBA or user to describe and name entities,
attributes, and relationships required for the application
plus any associated integrity and security constraints.
allows user to state what data is needed rather than how it
is to be retrieved.
Fourth Generation Languages (4GLs)
18
Constraint on relationships

Cascade delete



The relation below try to describe the structure of an
order. Can an orderhead exist without a row?
If you delete a certain row in orderhead…what happens?
Cascade update

If you update oredernr in orderhead…what happens?
Orderhead
Ordernr
Date
custnr
1
*
Orderrow
Ordernr
Partnr
Quantity
What is a stored procedure?

How its working?




SQL server has a language called transact SQL and
its used to create modules of logic database actions.
It is stored in database with a name to call when you
need it.
An example of executing a stored procedure to create
a new customer:
EXEC sp_newcustomer (’23’,’Jesper’, ’Hakeröd’,…)
More practise on this in laboratory work 2.
Example of a stored procedure
-- =============================================
-- Author:
<Jesper>
-- Create date: <2009-10-10>
-- Description:
<Insert a new customer>
-- =============================================
CREATE PROCEDURE [dbo].[uspNewCustomer] (@CustomerID as bigint, @firstname as
nvarchar(50), @surename as nvarchar(50), @address as nvarchar(50), @cellular as
nvarchar(50), @email as nvarchar(50), @zipcode as nvarchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insertion of the customer
INSERT INTO CUSTOMER (CustomerID, firstname, surename, address, cellular, email,
zipcode)
VALUES (@CustomerID, @firstname, @surename, @address, @cellular, @email, @zipcode);
END
Kommer ni ihåg begreppen?
Supernyckel = Ett eller flera attribut som ensamt eller
sammansatt unikt identifierar en tuple i en relation.
 Kandidatnyckel = En supernyckel där det inte kan tas
bort något attribut som ingår i supernyckeln, utan att
man mister den unika identifieringen av en tuple.
 Kompositnyckel = En nyckel som består av flera
attribut kallas för sammansatt- eller kompositnyckel.
 Primärnyckel = En primärnyckel är en vald
kandidatnyckel som unikt kan identifiera en tuple.
 Alternativ nyckel = kandidatnycklar förvandlas till
alternativa nycklar när primärnyckeln valts.
 Främmande nyckel = Ett attribut eller en samling
attribut i en relation som matchar en kandidatnyckel i en
22
annan relation.

Normaliseringsformer
repetition

Onormaliserat – UNF


Första Normalform - 1NF


En tabell är i 1NF om det i varje tuple bara finns ett
värde för varje attribut. Välj även ut en primärnyckel.
Andra normalform - 2NF


En tabell som innehåller repeterade grupper.
En tabell är i 2NF om alla icke nyckelattribut är
funktionellt beroende av hela primärnyckeln.
Tredje normalform - 3NF

En tabell är i 3NF om alla icke nyckelattribut inte är
transitivt beroende av primärnyckeln.
23
Data dictionary - entities
24
Data dictionary - attributes
25