5-esquemaSQL-8

Prévia do material em texto

1
Aind a m ais SQL
2
Roteiro
• Criação d e esquem as
• Rest r ições d e In tegr id ad e
• Mod ificação d a base d e d ad os
3
Defin ição d e Dad os em SQL
DDL (Da ta Definit ion La ng ua g e )
Defin ição d os d ados:define o esquem a
• Criação d e tabelas
• Rem oção de tabelas
• Mod ificação do esquem a
Ind ices : p ara m elhorar o d esem p enho
4
Criação d e Tabelas
CREATE TABLE Pessoa(
nom e VARCHAR(30),
cp f INT,
id ade SHORTINT,
cid ade VARCHAR(30),
sexo BIT(1),
nascim en to DATE
);
Exem p lo:
5
Rem oção ou Mod ificação de um a
Tabela
Rem oção:
ALTER TABLE Pessoa
ADD tel CHAR(16);
ALTER TABLE Pessoa
DROP id ad e;
Alteração:(ad ição ou rem oção d e at r ibu to)
O que acon tece quando o esquem a é alterado?
Exem p lo:
DROPPessoa;Exem p lo: Use com cu idad o!!
6
Valores Defau lt
Esp ecificação d e valores d efau lt :
CREATE TABLE Pessoa(
nom e VARCHAR(30),
cp f INT,
id ade SHORTINT DEFAULT 100,
cid ad e VARCHAR(30)DEFAULT ‘Cur it iba’,
sexo CHAR(1) DEFAULT ‘?’,
nascim en to DATE
)
O defau lt d os defau lts : NULL
7
Rest r ições de In tegr id ad e
em SQL
8
Rest r ição de In tegr id ad e
em SQL
• Um a rest r ição = um a p rop r ied ad e
que d esejam os que seja m an t id a na
base d e d ad os
• O SGBD vai m ater a rest r ição
tom and o algum as ações:
– p roib ind o algum a atualiz ações
– ou execu tand o atualiz ações d e
com p ensação
9
Restr ições d e In tegr id ad e em
SQL
Tip os:
• Chave, chaves est rangeiras
• Restr ições d e atr ibu to
• Restr ições d e tup las
• Restr ições globais : asserções
Quan to m ais com p lexa um a rest r ição, é
m ais d ifícil ver ificar quand o ela é violad a
e m an ter a in tegr id ad e d a base.
s
m ais
s im p les
m ais
com p lexa
10
Chave
OU:
CREATE TABLE Prod u to (
nom e CHAR(30)PRIMARY KEY,
categor ia VARCHAR(20))
CREATE TABLE Prod u to (
nom e CHAR(30),
categor ia VARCHAR(20)
PRIMARY KEY (nom e))
11
Chaves com Múlt ip los
Atr ibu tos
CREATE TABLE Prod u to (
nom e CHAR(30),
categor ia VARCHAR(20),
p reco INT,
PRIMARY KEY (nom e, categor ia))
12
Outras Chaves
CREATE TABLE Prod u to (
p rod u toID CHAR(10),
nom e CHAR(30),
categor ia VARCHAR(20),
p reco INT,
PRIMARY KEY
(p rod u toID),
UNIQUE (nom e, categor ia))
Exis te som ente um aPRIMARY KEY;
m as pod em exis t ir d iversos atr ibu tosUNIQUE
13
Chaves Est rangeiras
CREATE TABLE Com p ra (
nom eProd CHAR(30)
REFERENCESProd u to(nom e),
d ata DATETIME)
n om eProd é um a chave estrangeira que
faz referência aProdu to(n om e).
n om e tem que ser um a chave d eProdu to.
In tegr id ad e
Referencial
14
elet ron icostelevisao
fotografiacam era
papelar ialap is
Categor iaNom e
Wal Martcam era
Pon to Fr iocam era
Wal Martlap is
lo janom eProd
Produ to Com pra
15
Chaves Est rangeiras
• Form a alternat iva:
• (nom e, categor ia) tem que ser
um a PRIMARY KEY
CREATE TABLE Com p ra (
nom eProd CHAR(30),
categor ia VARCHAR(20),
d ata DATETIME,
FOREIGN KEY(nom eProd , categor ia)
REFERENCESProd u to(nom e, categor ia)
16
O que acon tece d uran te
atualiz ações?
Tip os d e atualiz ações :
• Em Com p ra: inserção/ atualiz ação
• Em Prod u to: rem oção/ atualiz ação
elet ron icostelevisao
fotografiacam era
papelar ialap is
Categor iaNom e
Wal Martcam era
Pon to Fr iocam era
Wal Martlap is
lo janom eProd
Produ
to
Com pra
17
O que acon tece d u ran te
atualiz ações?
• o SQL tem três p olít icas p ara
m anu tenção d a in tegr id ad e referencial:
• Rejeitarm od ificações que a
vio lam (d efau lt )
• Cascad e: d ep ois d e d elete/ up d ate faz er
m ais d elete/ up d ate
• Set- nu ll:coloca NULL no cam p o que é
chave est rangeira
18
Rest r ições em Atr ibu tos e
Tup las
• Restr ições em atr ibu tos :
NOT NULL - - valor não p od e ser nu lo
CHECK cond ição- - qualquer cond ição !
• Restr ições em tup las :
CHECK cond ição
19
CREATE TABLE Com p ra (
nom eProd CHAR(30)
CHECK (nom eProd IN
SELECTProd u to.nom e
FROM Prod u to),
d ata DATETIMENOT NULL)
Qual
a d iferença com
chave est rangeira ?
20
Mod ificação d a Base d e
Dad os
Três t ip os d e m od ificações
• Inserções
• Rem oções
• Atualiz ações
Frequen tem en te as t rês op erações são
cham ad as d e “atualiz ações”
21
Inserções
Form a geral:
At r ibu to não exis ten te→ NULL.
Pod e om it ir os nom es dos at r ibu tos se esp ecificad os
em ord em .
INSERT INTO R(A1,…., An ) VALUES (v1,…., vn )
INSERT INTO Com p ra(com p rad or, vended or , p rod u to, lo ja)
VALUES (‘Joao’, ‘Jose’, ‘d esp er tad or ', 'Pon to Fr io ')
Exem p lo: Inser ir um a nova com pra na base de dad os:
22
Inserções
INSERT INTO PRODUTO(nom e)
SELECT DISTINCT Com p ra.p rod u to
FROM Com pra
WHERE Com p ra.d ata > “26/ 10/ 01”
Um a consu lta no lugar d a palavra- chave VALUES.
Vár ias tup las são inser id as em PRODUTO
23
Inserção: um Exem p lo
nom eProdé chave estrangeira que referencia
Prod u to.nom e
Sup onha que a base está incons is ten te e p recisam os
consertá- la.
p ap elar ia100lap is
categor iap recoTabelanom e
225Josecam era
80Joselap is
200Joaocam era
p reco
com p rad o
r
nom eProd
Tarefa: inser ir emProdu totod os osnom eProdem Com p ra
Produ to
Produ to(nom e, p recoTabela, categor ia)
Com pra(nom eProd , com p rador , p reco)
Com p ra
24
Inserção: um Exem p lo
INSERT INTO Produ to(nom e)
SELECT DISTINCT nom eProd
FROM Com p ra
WHERE nom eProd NOT IN (SELECT nom eFROM Prod u to)
--cam era
p ap elar ia100lap is
categorylis tPr icenam e
25
Inserção: um Exem p lo
INSERT INTO Prod u to(nom e, p recoTabela)
SELECT DISTINCT nom eProd , p reco
FROM Com p ra
WHERE nom eProd NOT IN (SELECT nom eFROM Produ to)
-225 ??cam era ??
-200cam era
p ap elar ia100lap is
categor iap recoTabelanom e
Dep end e d a im p lem en tação
26
Rem oções
DELETE FROM Com pra
WHERE com p rador = ‘Jose’ AND
p reco > 500
Exem p lo:
27
Atualiz ações
UPDATE PRODUTO
SET p recoTab = p recoTab/ 2
WHERE Produ to.nom eIN
(SELECTnom eProd
FROM Com p ra
WHERE Data = ‘22/ 10/ 2005’);
Exem p lo: