Upload
internet
View
110
Download
3
Embed Size (px)
Citation preview
1
SQL: Aula 3
2
Roteiro
• cláusulas order by e group by
• Outer Joins e valores null
• Criação de esquemas
• Modificação da base de dados
• Definição de Visões
3
Ordenação dos Resultados
SELECT categoryFROM ProductORDER BY pname
HitachiHousehold$203.99MultiTouch
CanonPhotography$149.99SingleTouch
GizmoWorksGadgets$29.99Powergizmo
GizmoWorksGadgets$19.99Gizmo
ManufacturerCategoryPricePName
?
4
Ordernação dos Resultados
SELECT DISTINCT categoryFROM ProductORDER BY category
Compare to:
Photography
Household
Gadgets
Category
SELECT DISTINCT categoryFROM ProductORDER BY pname ?
5
Agregação
Author(login,name)
Document(url, title)
Wrote(login,url)
Mentions(url,word)
6
Author(login,name)Wrote(login,url)
• Encontre os autores que escreveram pelo menos 10 documentos:
• Tentativa 1: com consulta aninhada
SELECT DISTINCT Author.nameFROM AuthorWHERE count(SELECT Wrote.url FROM Wrote WHERE Author.login=Wrote.login) > 10
Consulta escrita porum novato
7
Author(login,name)Wrote(login,url)
• Encontre os autores que escreveram pelo menos 10 documentos:
• Tentativa 2: com GROUP BY
SELECT Author.nameFROM Author, WroteWHERE Author.login=Wrote.loginGROUP BY Author.nameHAVING count(wrote.url) > 10
Escrita porum expert
Não há necessidade de DISTINCT: automático devido oGROUP BY
8
Author(login,name)Wrote(login,url)Mentions(url,word)
• Encontre os autores que tem um vocabulário acima de 10000 palavras:
SELECT Author.nameFROM Author, Wrote, MentionsWHERE Author.login=Wrote.login AND Wrote.url=Mentions.urlGROUP BY Author.nameHAVING count(distinct Mentions.word) > 10000
Preste atenção nas duas últimas consultas: embora elas possamser escritas utilizando consultas aninhadas, é melhor utilizarGROUP BY.
9
Valores Null
• Se x=Null então 4*(3-x)/7 resulta em Null
• Se x=Null então o teste x=“Joe” resulta em UNKNOWN
• Em SQL há 3 valores booleanos:FALSE = 0
UNKNOWN = 0.5
TRUE = 1
10
Valores Null
• C1 AND C2 = min(C1, C2)• C1 OR C2 = max(C1, C2)• NOT C1 = 1 – C1
Regra em SQL: inclui somente tuplas que resultam em TRUE
SELECT *FROM PersonWHERE (age < 25) AND (height > 6 OR weight > 190)
P.ex.age=20height=NULLweight=200
11
Valores Null
Comportamento não esperado:
Algumas pessoas não são incluídas no resultado !
SELECT *FROM PersonWHERE age < 25 OR age >= 25
12
Valores Null
É possível testar se um valor é nulo explicitamente:– x IS NULL– x IS NOT NULL
Agora o resultado contém todas as pessoas.
SELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL
13
Valores Null em Junções Product(name, category) Purchase(prodName, store)
O mesmo que:
Mas produtos que nunca foram vendidos não aparecem no resultado !
SELECT Product.name, Purchase.storeFROM Product JOIN Purchase ON Product.name = Purchase.prodName
SELECT Product.name, Purchase.storeFROM Product, PurchaseWHERE Product.name = Purchase.prodName
14
Valores Null e Outerjoins
Left outer joins em SQL:Product(name, category)
Purchase(prodName, store)
SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName
15
PhotoOneClick
PhotoCamera
gadgetGizmo
CategoryName
WizCamera
RitzCamera
WizGizmo
StoreProdName
NULLOneClick
WizCamera
RitzCamera
WizGizmo
StoreName
Product Purchase
16
Outer Joins
• Left outer join:– sempre inclui a tupla da esquerda
• Right outer join:– sempre inclui a tupla da direita
• Full outer join:– sempre inclui tanto a tupla da esquera como da
direita
17
Modificação da Base de Dados
Três tipos de modificações
• Inserções
• Deleções
• Atualizações
Frequentemente as três operações são chamadas de “atualizações”
18
InserçõesForma geral:
Atributo não existente NULL.Pode omitir os nomes dos atributos se especificados em ordem.
INSERT INTO R(A1,…., An) VALUES (v1,…., vn)
INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’)
Exemplo: Inserir uma nova compra na base de dados:
19
Inserções
INSERT INTO PRODUCT(name)
SELECT DISTINCT Purchase.product FROM Purchase WHERE Purchase.date > “10/26/01”
Uma consulta no lugar da palavra-chave VALUES.Várias tuplas são inseridas em PRODUCT
20
Inserção: um Exemplo
prodName é chave estrangeira em Product.name
Suponha que a base está inconsistente e precisamos consertá-la.
gadgets100gizmo
categorylistPricename
225Smithcamera
80Smithgizmo
200Johncamera
pricebuyerNameprodName
Tarefa: inserir em Product todos os prodNames em Purchase
Product
Product(name, listPrice, category)Purchase(prodName, buyerName, price)
Purchase
21
Inserção: um Exemplo
INSERT INTO Product(name)
SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)
--camera
Gadgets100gizmo
categorylistPricename
22
Inserção: um Exemplo
INSERT INTO Product(name, listPrice)
SELECT DISTINCT prodName, price FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)
-225 ??camera ??
-200camera
Gadgets100gizmo
categorylistPricename
Depende da implementação
23
Deleções
DELETE FROM PURCHASE
WHERE seller = ‘Joe’ AND product = ‘Brooklyn Bridge’
Exemplo:
24
Atualizações
UPDATE PRODUCTSET price = price/2WHERE Product.name IN (SELECT product FROM Purchase WHERE Date =‘Oct, 25, 1999’);
Exemplo:
25
Definição de Dados em SQLAté agora nós vimos a DML (Data Manipulation Language)Em seguida: DDL (Data Definition Language)
Tipos de dados: Define os tipos.
Definição dos dados: define o esquema
• Criação de tabelas• Remoção de tabelas• Modificação do esquema
Indexes: para melhorar o desempenho
26
Tipos de Dados em SQL
• Strings: – CHAR(20) -- fixed length– VARCHAR(40) -- variable length
• Numéricos:– INT, REAL plus variations
• Data / Hora: – DATE, DATETIME
• Para criar tipos:CREATE DOMAIN address AS VARCHAR(55)
27
Criação de Tabelas
CREATE TABLE Person(
name VARCHAR(30), social-security-number INT, age SHORTINT, city VARCHAR(30), gender BIT(1), Birthdate DATE
);
Exemplo:
28
Remoção ou Modificação de uma Tabela
Remoção:
ALTER TABLE Person ADD phone CHAR(16);
ALTER TABLE Person DROP age;
Alteração: (adição ou remoção de atributo)
O que acontece quando o esquema é alterado?
Exemplo:
DROP Person; Exemplo: Use com cuidade !!
29
Valores Default
Especificação de valores default:
CREATE TABLE Person( name VARCHAR(30), social-security-number INT, age SHORTINT DEFAULT 100, city VARCHAR(30) DEFAULT ‘Seattle’, gender CHAR(1) DEFAULT ‘?’, Birthdate DATE
O default dos defaults: NULL
30
ÍndicesMUITO importante para melhorar o tempo de processamento de uma consulta.
Considere a relação:
Person (name, age, city)
O processamento sequencial do arquivo Person pode levar muito tempo.
SELECT *FROM PersonWHERE name = “Smith”
31
• Criação de um índice sobre o atributo name:
• Árvores B+ tem um fan-out de 100s: max 4 níveis !
Índices
Smith ….….CharlesBettyAdam
32
Criação de Índices
CREATE INDEX nameIndex ON Person(name)
Sintaxe:
33
Criação de ÍndicesOs índices podem ser criados sobre mais que um atributo:
CREATE INDEX doubleindex ON Person (age, city)
SELECT * FROM Person WHERE age = 55 AND city = “Seattle”
SELECT * FROM Person WHERE city = “Seattle”
Ajuda em:
Mas não em:
Exemplo:
34
Criação de Índices
Os índices podem ser úteis para pesquisas em intervalos também:
Árvores B+ ajudam em:
Por que não criar índices em tudo?
CREATE INDEX ageIndex ON Person (age)
SELECT * FROM Person WHERE age > 25 AND age < 28
35
O Problema da Seleção de Índices
• É definido um workload = um conjunto de consultas SQL e a frequência que elas são executadas
• Quais os índices que devem ser criados para melhorar o desempenho do workload?
• cláusulas FROM/WHERE: favorecem a criação de um índice
• cláusulas INSERT/UPDATE: desencorajam a criação de índices
• Seleção de índices = normalmente feito por pessoas; mais recentemente feito automaticamente