Upload
buikhue
View
221
Download
0
Embed Size (px)
Citation preview
Bases de DadosBases de Dados
Álgebra Relacional IIÁlgebra Relacional IIJunções, agregações, vistasJunções, agregações, vistas
P. Serendero, 2011-13P. Serendero, 2011-13
11
JUNÇÕES OU JOINS em SQL - ⋈
RR ⋈⋈<<condiçãocondição> > SSA A condiçãocondição do JOIN é especificada na claúsula do JOIN é especificada na claúsula ONON ou ou USINGUSING, ou , ou
implicitamente utilizando implicitamente utilizando NATURALNATURAL
A condição do JOIN determina A condição do JOIN determina quais tuplos coincidemquais tuplos coincidem nas duas nas duas tabelas fontetabelas fonte
A claúsula A claúsula ONON toma a forma duma toma a forma duma expressão booleanaexpressão booleana igual que igual que no WHERE: verdadeira se dois tuplos são avaliados como iguais no WHERE: verdadeira se dois tuplos são avaliados como iguais
USINGUSING (a,b,c) serve para indicar (a,b,c) serve para indicar quais colunasquais colunas as tabelas devem as tabelas devem ter em comumter em comum
NATURALNATURAL: indica que se devem incluir : indica que se devem incluir todas as colunastodas as colunas das das tabelastabelas
22
JUNÇÕES OU JOINS - ⋈
33
NATURAL JOIN (contraparte do AND lógico) : R ⋈ S Resultado: o conjunto de todas as combinações de tuplos em R e S que são iguais nos seus atributos comuns
Normalmente a combinação de chaves primárias numa tabela e chaves estrangeiras numa outra
Formalmente: R ⋈ S = {t ∪ s | t R ∈ ∧ s S F( t ∈ ∧ ∪ s) }
Onde F() é o predicado que é verdadeiro para a relação R
No mínimo R e S devem ter 1 atributo em comum
NATURAL JOIN - ⋈
44
curso directorMatematica Manojlovic
Informatica JesusLetras Branco
id nome curso directora2233 Lopes Matematica Manojlovic
a3345 Soares Informática Jesusa2869 oliveira Matematica Manojlovic
a3590 gomes Informática Jesus
aluno disciplina
aluno ⋈ curso
SELECT * FROM aluno NATURAL [left | outer] JOIN disciplina;
id nome curso
a2233 Lopes Matematica
a3345 Soares Informática
a2869 oliveira Matematica
a3590 gomes Informática
EQUIJOIN ou -JOIN R ⋈θ S
É um tipo de INNER JOIN Serve para combinar tuplos de 2 relações onde a condição de combinação não é só a igualdade dos atributos partilhadosΘ indica uma relação binária utilizando operadores lógicos {<, >, <=, >=, =}
As tabelas e as operações booleanas são o coração do SQL (Date)
O resultado é definido só se os cabeçalhos de R e S são disjuntos
O resultado são todos os tuplos que satisfazem a condição da relação θ
Formalmente, R ⋈θ S =
σθ(R x S)
-JOIN e EQUIJOIN
66
modeloA precoA
a1 800
a2 950
a3 1300
modeloB preçoB modeloA preçoA
A 24000 a1 800
barco atrelado
SELECT * FROM barco JOIN atrelado WHERE preçoB + preçoA <= 25000;
modeloB precoB
A 24000
B 24500
C 45000
Quando o θ é o operador “=”, cháma-se equijoin
JOINS em SQL
A cláusula JOIN combina 2 elementos de FROMA cláusula JOIN combina 2 elementos de FROM
• • [ INNER ] JOIN[ INNER ] JOIN
• • LEFT [ OUTER ] JOINLEFT [ OUTER ] JOIN
• • RIGHT [ OUTER ] JOINRIGHT [ OUTER ] JOIN
• • FULL [ OUTER ] JOINFULL [ OUTER ] JOIN
• • CROSS JOINCROSS JOIN
Os tipos INNER e OUTER requerem uma condição: Os tipos INNER e OUTER requerem uma condição:
NATURALNATURAL, , ONON <condição>, ou <condição>, ou USINGUSING (a (aaa, a, abb,…). ,…).
Nenhuma destas para CROSS JOINNenhuma destas para CROSS JOIN
CROSS JOIN e INNER JOIN produzem um simples produto cartesianoCROSS JOIN e INNER JOIN produzem um simples produto cartesiano
77
JOIN qualificados em SQL
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINJOIN T2 T2 ONON <expressão booleana> <expressão booleana>
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINJOIN T2 T2 USINGUSING ( lista de colunas ) ( lista de colunas )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } [OUTER] } JOINJOIN T2 T2
As palavras INNER e OUTER são opcionais.As palavras INNER e OUTER são opcionais.
INNER é a opção por defeito.INNER é a opção por defeito.
LEFT, RIGHT, e FULL implicam um OUTER JOINLEFT, RIGHT, e FULL implicam um OUTER JOIN
88
Tipos de JOIN: em SQL
INNER JOININNER JOIN: Por cada tuplo : Por cada tuplo tt da T1, a tabela que se junta tem da T1, a tabela que se junta tem um tuplo por cada tuplo em T2 que satisfaz a condição do join um tuplo por cada tuplo em T2 que satisfaz a condição do join comcom t t
LEFT OUTER JOIN LEFT OUTER JOIN : executar um INNER JOIN. Logo, por cada : executar um INNER JOIN. Logo, por cada tuplo em T1 que não satisfaz a condição do join com qualquer tuplo em T1 que não satisfaz a condição do join com qualquer tuplo em T2, um tuplo é adicionado com valores nulos nas tuplo em T2, um tuplo é adicionado com valores nulos nas colunas de T2.colunas de T2.Por isto, a tabela da nova relação tem pelo menos um tuploPor isto, a tabela da nova relação tem pelo menos um tuplomenos por cada tuplo em T1menos por cada tuplo em T1
RIGHT OUTER JOIN: RIGHT OUTER JOIN: Executar um INNER JOIN. Logo, por cada tuplo em T2 que não Executar um INNER JOIN. Logo, por cada tuplo em T2 que não satisfaz a condição do join com qualquer coluna em T1, um tuplo satisfaz a condição do join com qualquer coluna em T1, um tuplo com nulos é adicionado nas colunas de T1. Isto é a operação com nulos é adicionado nas colunas de T1. Isto é a operação inversa dum LEFT JOIN: a tabela resultante tem sempre um tuplo inversa dum LEFT JOIN: a tabela resultante tem sempre um tuplo por cada tuplo em T2por cada tuplo em T2
99
Tipos de JOIN: em SQL
FULL OUTER JOINFULL OUTER JOIN: executar um INNER JOIN. Logo,: executar um INNER JOIN. Logo,por cada tuplo em T1 que não satisfaz a condição do join com por cada tuplo em T1 que não satisfaz a condição do join com qualquer tuplo em T2, adicionamos um tuplo com valores nulos qualquer tuplo em T2, adicionamos um tuplo com valores nulos nas colunas de T2. Também, por cada tuplo em T2 que não nas colunas de T2. Também, por cada tuplo em T2 que não satisfaz a condição do join com qualquer coluna em T1, se satisfaz a condição do join com qualquer coluna em T1, se
adiciona um tuplo com nulos nas colunas de T1. adiciona um tuplo com nulos nas colunas de T1.
Qualquer número de tabelas podem participar encadeados ou Qualquer número de tabelas podem participar encadeados ou aninhados na operação do JOINaninhados na operação do JOIN
T1 e T2 podem ser por exemplo pela sua vez produto de T1 e T2 podem ser por exemplo pela sua vez produto de operações JOIN anterioresoperações JOIN anteriores
Podem-se utilizar parêntesis para controlar a ordem de execução Podem-se utilizar parêntesis para controlar a ordem de execução dos joindos join
1010
Exemplo CROSS JOIN
1111
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
SELECT * FROM T1 CROSS JOIN T2
num | nome|num|valor1 | a | 1 | xxx1 | a | 3 | yyy1 | a | 5 | zzz2 | b | 1 | xxx2 | b | 3 | yyy2 | b | 5 | zzz3 | c | 1 | xxx3 | c | 3 | yyy3 | c | 5 | zzz
O produto cartesiano!!
T1 T2
Exemplo INNER JOIN
1212
num nome1 a2 b3 c
num valor
1 xxx
3 yyy
5 zzz
SELECT * FROM T1 INNER JOIN T2 ON T1.num = T2.num;
num nome num valor1 a 1 xxx3 c 3 yyy
T1 T2
Por cada tuplo Por cada tuplo tt da T1, a tabela resultante tem um tuplo por da T1, a tabela resultante tem um tuplo por cada tuplo em T2 que satisfaz a condição do join com cada tuplo em T2 que satisfaz a condição do join com tt
Exemplo INNER JOIN ..USING
1313
num nome
1 a2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
SELECT * FROM T1 INNER JOIN T2 USING (num); num nome valor
1 a xxx
3 c yyyResultado igual que o anterior
T1 T2
Exemplo NATURAL INNER JOIN
1414
num nome1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
SELECT * FROM T1 NATURAL INNER JOIN T2; num nome valor
1 a xxx3 c yyyResultado igual que o anterior
T2T1
Exemplo LEFT JOIN
1515
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
num nome num valor
1 a 1 xxx2 b
3 c 3 yyy
SELECT * FROM T1 LEFT JOIN T2 ON T1.num = T2.num;
T2T1
por cada tuplo em T1 que não satisfaz a condição do join com por cada tuplo em T1 que não satisfaz a condição do join com qualquer tuplo em T2, um tuplo é adicionado com valores qualquer tuplo em T2, um tuplo é adicionado com valores nulos nas colunas dos tuplos correspondentes em T2.nulos nas colunas dos tuplos correspondentes em T2.
Exemplo LEFT JOIN..USING
1616
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
num nome valor1 a xxx2 b3 c yyy
SELECT * FROM T1 LEFT JOIN T2 USING (num);
T2T1
Resultado igual ao anterior.No MySQL, JOIN, CROSS JOIN, e INNER JOIN são sintacticamente equivalentes (substituíveis). No standard SQL eles não são equivalentes. INNER JOIN é utilizado com clausula ON. Caso contrário se utiliza CROSS JOIN
Exemplos RIGHT JOIN ..ON
1717
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
SELECT * FROM T1 RIGHT JOIN T2 ON T1.num = T2.num;
num nome num valor1 a 1 xxx3 c 3 yyy
NULL NULL 5 zzz
por cada tuplo em T2 que não satisfaz a condição da por cada tuplo em T2 que não satisfaz a condição da junção com qualquer coluna em T1, um tuplo com nulos é junção com qualquer coluna em T1, um tuplo com nulos é adicionado nas colunas de T1.adicionado nas colunas de T1.
T1T2
Exemplo FULL OUTER JOIN
1818
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.num = T2.num;
num nome num valor1 a 1 xxx2 b3 c 3 yyy
5 zzz
Resulta em todas as tuplas de T1 e T2 que são equivalentes no atributo comum, e ainda todas as tuplas de T1 e T2 que não tem valores de atributos equivalentes
T1 T2
Exemplo LEFT JOIN..on
1919
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
A condição no ON pode conter condições que não dependem directamente do join
SELECT * FROM T1 LEFT JOIN T2 ON T1.num = T2.num AND T2.valor = ’xxx’;
num nome num valor
1 a 1 xxx
2 b
3 c
T1T1 T2
Exemplos com operações de JOIN
2020
num nome
1 a
2 b
3 c
num valor
1 xxx
3 yyy
5 zzz
Colocando a condição no WHEREcausa um resultado diferente
SELECT * FROM T1 LEFT JOIN T2 ON T1.num = T2.num WHERE T2.valor = ’xxx’;
num nome num valor
1 a 1 xxx
Isto porque a restrição colocada no ON é processada ANTES do JOIN, enquanto uma restrição colocada no WHERE é processada DEPOIS do JOIN.
T1 T2
table_references: escaped_table_reference [, escaped_table_reference] ...
table_reference: table_factor | join_table
join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON conditional_expr | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition: ON conditional_expr | USING (column_list)
index_hint: USE {INDEX|KEY} [FOR JOIN] (index_list) | IGNORE {INDEX|KEY} [FOR JOIN] (index_list) | FORCE {INDEX|KEY} [FOR JOIN] (index_list)
index_list: index_name [, index_name] ...
escaped_table_reference: table_reference | { OJ table_reference }
table_factor: tbl_name [[AS] alias] [index_hint] | table_subquery [AS] alias | ( table_references )
JOIN no manual doMySQL
AgregaçõesAgregações em SQL em SQL
2222
AgregaçõesAgregações em SQL em SQL
As funções agregadas computam um só resultado onde o input vem As funções agregadas computam um só resultado onde o input vem de múltiplos tuplosde múltiplos tuplos
Operadores Operadores SUMSUM, , MINMIN, , MAXMAX, , AVGAVG, e , e COUNTCOUNT, podem ser aplicados a , podem ser aplicados a um atributo de uma tabela.um atributo de uma tabela.
Os operadores aparecem na clausula SELECT.Os operadores aparecem na clausula SELECT.
O resultado e um valor agregado para o respectivo atributoO resultado e um valor agregado para o respectivo atributo..
COUNT(*) conta o numero de tuplos.COUNT(*) conta o numero de tuplos.
2323
AgregaçõesAgregações em SQL em SQL
Tratamento aos valores NULOSTratamento aos valores NULOSOs valores nulos (NULL) são ignoradosOs valores nulos (NULL) são ignorados
Não contam para uma soma, media ou contagemNão contam para uma soma, media ou contagem
Um valor NULL nunca pode ser o mínimo ou máximo de Um valor NULL nunca pode ser o mínimo ou máximo de uma coluna.uma coluna.
Se todos os valores de uma coluna forem nulos, o Se todos os valores de uma coluna forem nulos, o resultado da agregação e também NULL.resultado da agregação e também NULL.
2424
AgregaçõesAgregações em SQL em SQLExemplos:Exemplos:
Qual é a cidade com a maior altitude?Qual é a cidade com a maior altitude?
SELECT rota, SELECT rota, MAXMAX(altitude) FROM VOO (altitude) FROM VOO ouou
SELECT rota FROM VOO SELECT rota FROM VOO WHEREWHERE altitude = altitude = MAXMAX (altitude); (altitude); XX(agregações (agregações não funcionam não funcionam dentro do dentro do WHEREWHERE, portanto fazemos , portanto fazemos um um subquerysubquery::
SELECT rota FROM VOO SELECT rota FROM VOO WHERE WHERE altitude = (SELECT altitude = (SELECT MAXMAX(altitude) as max-altitude FROM VOO);(altitude) as max-altitude FROM VOO);
Repare: osRepare: os operandosoperandos aninhadosaninhados
2525
Cláusula group by
SELECT <lista>t FROM …[WHERE ...]SELECT <lista>t FROM …[WHERE ...]
GROUP BY GROUP BY <coluna referencia> [, coluna referencia]...<coluna referencia> [, coluna referencia]...
GROUP BY Agrupa tuplos com igual valor nas colunas da listagemGROUP BY Agrupa tuplos com igual valor nas colunas da listagem
A ordem das colunas não interessaA ordem das colunas não interessa
O efeito é O efeito é combinar cada conjunto de tuplos que tem valores combinar cada conjunto de tuplos que tem valores comuns num grupocomuns num grupo que representa todos os tuplos no grupoque representa todos os tuplos no grupo
Isto é feito para eliminar redundância no output e/ou para aplicar Isto é feito para eliminar redundância no output e/ou para aplicar agregações a estes gruposagregações a estes grupos
2626
AgregaçõesAgregações em SQL em SQLGROUP BYGROUP BY: agrupa output conforme precisamos: agrupa output conforme precisamos
Utilizado após um SELECTUtilizado após um SELECT
GROUP BY <aGROUP BY <a11, a, a22,..a,..ann>>
O resultado é agrupado conforme com os valores dos atributos O resultado é agrupado conforme com os valores dos atributos indicadosindicados
As agregações são aplicadas a cada grupoAs agregações são aplicadas a cada grupo
2727
Cláusula GROUP BY
2828
x y
A 3
C 2
B 5
A 1
SELECT x FROM T1 GROUP BY x;
x
A
B
C
T1
TRSELECT * FROM T1
Repare que no 2º query não poderíamos ter escrito SELECT *… porque não existe um valor na coluna ‘y’ que poderia ser associada com algum grupo
Cláusula GROUP BY
2929
x y
A 4
B 5
C 2
TR
Em geral se uma tabela esta agrupada, as colunas que não aparecem no GROUP BY não podem serem referenciadas, excepto em expressões agregadas, tais como:
SELECT x, sum(y) FROM T1 GROUP BY x;
x y
A 3
C 2
B 5
A 1
T1
Neste exemplo sum computa um só valor sobre um grupo inteiro
Cláusula HAVING
3030
Se uma tabela foi agrupada utilizando o GROUP BY, mas só alguns grupos são de interesse, então se pode utilizar HAVING
HAVING pode ser usado quase como WHERE para eliminar grupos
Sintaxe: SELECT <lista> FROM ... [WHERE ...] GROUP BY ... HAVING <exp.booleana>
As expressões no HAVING podem-se referir a expressões agrupadas ou não
Exemplo SELECT x, sum(y) FROM T1 GROUP BY x HAVING sum(y) > 3;
x sum
A 4
B 5
AgregaçõesAgregações em SQL em SQLA diferencia fundamental entre WHERE e HAVING é: A diferencia fundamental entre WHERE e HAVING é:
WHEREWHERE selecciona tuplos de input selecciona tuplos de input antesantes de computar agregações de computar agregações e grupos, isto é, controla quais tuplos participam na operação e grupos, isto é, controla quais tuplos participam na operação de agregaçãode agregação
HAVING HAVING selecciona os grupos de tuplos selecciona os grupos de tuplos depoisdepois que agregações e que agregações e grupos foram decididos.grupos foram decididos.
Por isto, a cláusula WHERE não deve conter funções agregadas Por isto, a cláusula WHERE não deve conter funções agregadas
Pela sua parte a cláusula HAVING sempre vai ter funções Pela sua parte a cláusula HAVING sempre vai ter funções agregadasagregadas
3131
AgregaçõesAgregações em SQL em SQLSELECT destino, SELECT destino, countcount (*) as cidade-popular from (*) as cidade-popular from
folha_voofolha_voo
grouped by destino;grouped by destino;
select id-capitao select id-capitao sum sum (distancia) as Km-percorridos (distancia) as Km-percorridos
from folha-voo group by id.capitaofrom folha-voo group by id.capitao
Minúsculas podem ser utilizadas nos queries.Minúsculas podem ser utilizadas nos queries.
GROUP BYGROUP BY: agrupa output conforme precisamos: agrupa output conforme precisamos
3232
AgregaçõesAgregações em SQL em SQLSELECT SELECT AVGAVG(num_passageiros)(num_passageiros)
FROM folha_vooFROM folha_voo
WHERE companhia_id = ‘TAP';WHERE companhia_id = ‘TAP';
SELECT destino, SELECT destino, COUNTCOUNT(*)(*)
FROM folha-VooFROM folha-Voo
GROUP BY destino;GROUP BY destino;
3333
AgregaçõesAgregações em SQL em SQLRestriçõesRestrições na utilização de agregações no SELECT na utilização de agregações no SELECT
Se se utilizar um operador de agregação (max, count, Se se utilizar um operador de agregação (max, count, max, min, avg) cada elemento especificado no max, min, avg) cada elemento especificado no SELECT deve ser:SELECT deve ser:
1. uma agregação, ou1. uma agregação, ou
2. um atributo especificado em GROUP BY2. um atributo especificado em GROUP BY
3434
Vistas: Vistas: VIEWVIEW
Podemos criar um longo query como um Podemos criar um longo query como um
query permanente: Uma denominada ‘VISTA’query permanente: Uma denominada ‘VISTA’
A vista criada, pode logo ser referenciada como uma A vista criada, pode logo ser referenciada como uma tabela qualquertabela qualquer
CREATE VIEW CREATE VIEW info_cidadeinfo_cidade AS AS
SELECT id_cidade, nome, temp_min, temp_max, SELECT id_cidade, nome, temp_min, temp_max, altitude, localizaçãoaltitude, localização
FROM cidade, voo WHERE id_cidade = nome;FROM cidade, voo WHERE id_cidade = nome;
…………....
SELECT * FROM SELECT * FROM info_cidadeinfo_cidade;;
3535
Álgebra Relacional IIÁlgebra Relacional II
FIM do móduloFIM do módulo
3636