8 - Banco de Dados: SQL

Prévia do material em texto

1
Linguagem 
SQL
ENG1556 – Sistemas de Informação Gerenciais
Prof. Fernanda Baião
fbaiao@puc-rio.br
1
Slides adaptados e estendidos a partir do material dos Profs. Gustavo Moreira
SQL
• Linguagem padrão de acesso a banco de dados relacional.
• Permite
– definir dados.
• DDL - Linguagem de Definição de Dados
– Create table, alter table, create index, ...
– manipular dados.
• DML - Linguagem de Manipulação de Dados
– Select, update, insert, delete
– Controlar acesso aos dados.
• DCL – Linguagem de Controle de Dados
– Grant, revoke
Select
Forma Básica:
SELECT <lista das colunas desejadas>
FROM <lista de tabelas>
WHERE <restrições sobre dados recuperados>
select *
from Empregados
select nome , cargo 
from Empregados 
where cargo = ‘Engenheiro’
Select
select [ all | distinct ] [ * | expr1, expr2 , . . . ]
onde as expressões expri (i = 1, 2,…) podem ser:
– coluna
– tabela ”.” coluna 
– view ”.” coluna
– correlation-name ”.” coluna
– coluna oper expressão ( || , + , - , * , / ) 
– registrador especial ou função
– literal
From
A cláusula from tem a seguinte forma:
from tabela1 ( correlation-name 1 )
[ , tabela2 ( correlation-name 2 ) , . . . ]
• correlation-name designa a tabela correspondente.
• um correlation-name só pode ser usado uma vez.
• uma tabela sem correlation-name só pode aparecer 
uma única vez.
• CUIDADO: se mais de uma tabela é especificada, será 
feito o produto cartesiano das mesmas
Where
• Define condições de pesquisa através de operadores lógicos 
– AND, OR, NOT
where
[ not ] predicado1 { and / or } 
[ not ] predicado2 . . .
• Cada predicado especifica uma condição que pode ser 
Verdadeira, Falsa ou Desconhecida
• Ordem de avaliação dos predicados:
• condições entre parênteses
• not
• and
• or
Predicado
• Predicado básico:
expressão 1 oper { expressão 2 | 
subselect }
• oper pode ser: = , > , < , >= , <= , <>
In & Between
Between
determina se um dado valor está entre dois outros 
valores dados, em ordem ascendente.
In
testa a pertinência de um elemento em um conjunto.
expr1 [ not ] in ( subselect . . . )
( host1 , host2 , host3 , . . . )
( const1 , const2 , const3 , . . . )
Junção
• Retorna colunas procedentes de várias tabelas 
em um único comando select.
– ”caminho de navegação” pelo esquema do banco de 
dados
select nome , nome_dept
from empregados, departamento
where empregado.cod_dept = departamento.cod_dept
Ilustrando Junção
select * from tab1 , tab2 where tab1.colx = tab2.coly
tab2 tab1
tabela resultado
150
200
250
350
110
120
140
150
350
350
150 150
350
450
colx coly
Like
• Procura por um certo padrão (pattern) de 
caracteres.
• O padrão é caracterizado por:
– ocorrência de caracteres alfanuméricos.
– caracteres especiais ‘_’ ou ‘%’.
• O caractere porcento substitui qualquer número 
de caracteres alfanuméricos 
• O caractere underscore substitui um número 
exato de caracteres alfanuméricos
Exists & Null
EXISTS
teste de conjunto vazio
select …
from …
where exists ( subselect )
NULL
valor inexistente
IS NULL, IS NOT NULL
testa a existência de valores nulos nas colunas.
where expr1 is [ not ] null
Cláusulas adicionais
O comando select possui ainda as seguintes 
cláusulas (nessa ordem):
– order by
– group by
– having
Order by
• ordena logicamente as linhas da tabela 
resultado.
• A ordenação pode ser obtida por um sort interno 
ou pelo uso de um índice adequado.
• A performance do sort interno depende do 
número de linhas/tuplas, da quantidade e 
tamanho das colunas referenciadas no order by. 
Group By & Having
• Group By aglutina logicamente várias linhas/tuplas 
em grupos baseado no valor satisfeito por uma ou 
mais colunas especificadas
• Having impõe condições às linhas/tuplas agrupadas 
no Group By
– só há Having se houver Group By
– Análogo às condições impostas às tuplas das tabelas na 
cláusula From especificadas na cláusula Where
Group by / Having
110 10 100 abc
110
120
120
130
20 xyz
ijk
ijk
spdf
120
2000
1500
null
100
200
200
col1 col2 col4col3
tabela1
select col1
, min(col2)
, avg(col3)
, max(col4)
from tabela1
group by col1
110 10 100 xyz
120 120 150 ijk
130 1500 200 spdf
Funções de agregação
• Count – número total de tuplas
• Max - valor máximo de uma coluna
• Min - valor mínimo de uma coluna
• Avg - valor médio de uma coluna
• Sum - somatório dos valores de uma coluna
select cod_dept, count(*), max( salario ), min( salario ), avg (salario ), sum ( salario ) 
from empregados
group by cod_dept
União
• Gera uma tabela resultado combinando duas 
outras tabelas resultado.
• Cada comando select é processado 
separadamente, produzindo uma tabela 
resultado; 
• No final elas serão combinadas em uma única 
tabela resultado.
Ilustrando União
tab1
tab2
tabela resultado
select * from tab1 
union all
select * from tab2
Insert
• Adiciona uma ou várias linhas a uma tabela 
do banco de dados.
• Cada comando atualiza uma única tabela.
Update
• Atualiza uma ou várias linhas de uma 
tabela do banco de dados.
• Cada comando atualiza uma única tabela.
Delete
• Exclui uma ou várias linhas/tuplas de uma 
tabela do banco de dados.
• Cada comando atualiza uma única tabela.
SQL Avançado
Parei aqui
09/11/2020
SQL 
• Padrão atual:
– SQL:2011 (7ª versão)
• Padrões anteriores:
– SQL-86
– SQL-89
– SQL-92
– .....
• A cada novo padrão foram adicionadas novas 
funcionalidades a linguagem, tais como:
– triggers
– stored procedures
– queries recursivas
SQL 
• Principais recursos avançados:
– Join
– Union
– Subqueries
• Outros recursos importantes do SQL:
– table constructors
– values
– common table expressions
– subqueries escalares
– nested tables 
– case
Esquema relacional exemplo
Cargo (código, nome_cargo)
Departamento(código, nome_departamento)
Empregado (matricula, nome_empregado, salario, @cod_cargo, @cod_dept)
cod_cargo referencia Cargo(codigo)
cod_dept referencia Departamento(codigo)
Gerente (@matricula)
matricula referencia Empregado(matricula)
Dependente (matricula, nome_dependente, @matricula_empregado)
matricula_empregado referencia Empregado(matricula)
Junções (JOINS)
• Formalizados no SQL-92
• Inner Join
• Outer Join
– Left outer join
– Right outer join
– Full outer join
Inner Join
• O INNER JOIN de duas tabelas traz somente as linhas que 
possuem correspondentes nas duas tabelas
• 2 sintaxes alternativas:
select nome_empregado , nome_departamento
from empregado e inner join departamento d
on e.cod_dept = d.codigo
select nome_empregado , nome_departamento
from empregado e, departamento d
where e.cod_dept = d.codigo
Inner Join
• Utilizando a sintaxe no FROM, separamos os predicados 
locais dos predicados de junção:
  predicados locais na cláusula Where
  predicados de join na cláusula ON
Com mais de duas tabelas: 
select e.nome , e.matricula , c.cargo , d.nome_dept
from empregado e inner join cargo c on e. cod_cargo = c. código
inner join departamento d on e.cod_dept = e.codigo
Outer Join
• Outer Join traz linhas das tabelas especificadas na 
junção, mesmo que não existam linhas 
correspondentes nas 2 tabelas
• Sintaxe:
FROM tabela1 [ LEFT / RIGHT / FULL ] OUTER JOIN tabela2 
ON tabela1.campo1 = tabela2.campo2
Left Outer Join
• O Left Outer Join traz linhas da tabela especificada à 
esquerda, mesmo que não existam linhas correspondentes 
na tabela da direita.
• Quando não há correspondência, as colunas procedentes 
da tabela da direita são preenchidas com nulos. 
select e.matricula, e.nome, d.nome_dependente
from empregado e left outer join dependente d
on e.matricula = d.matricula_empregado
Right Outer Join
• O Right Outer Join traz linhas da tabela especificada à 
direita, mesmo que não existam linhas correspondentes na 
tabela da esquerda.
• Quando não há correspondência, as colunas procedentes
da tabela da esquerda são preenchidas com nulos.
select e.matricula, e.nome, d.nome_departamentofrom empregado e right outer join departamento d
on e.cod_depto = d.codigo
Full Outer Join
• O Full Outer Join traz linhas de qualquer uma das duas 
tabelas, mesmo quando elas não possuem 
correspondentes na outra tabela.
• As colunas sem correspondência são preenchidas com
nulos.
select e.matricula, e.nome, d.nome_departamento
from empregado e full outer join departamento d
on e.cod_depto = d.codigo
Comandos select na cláusula select ou having de 
outro comando sql. Podem ser:
� correlacionados: 
o sql interno faz referência à coluna(s) do 
sql externo ( outer ).
• não correlacionados:
não há referências entre os sql interno e externo
Subqueries
select matricula , nome 
from empregado t1
where exists
( select * from dependente t2 where
t2.matricula_empregado = t1.matricula)
select matricula , nome 
from empregado t1
where not exists
( select * from dependentes t2 where
t2 .matricula_empregado = t1 . matricula )
Subqueries correlacionados
• Os subqueries correlacionados são eficientes quando a 
coluna correlacionada do query interno possui índice: 
• Caso contrário o subquery pode ser muito ineficiente.
select matricula , nome 
from empregado t1
where exists ( select * from dependentes t2 where
t2.matricula_empregado = t1.matricula )
a tabela dependentes possui índice por 
matrícula_empregado
Subqueries correlacionados
select matricula , nome , salario from empregado
where salario > ( select avg ( salario )
from empregado )
order by 3 desc
select cod_dept , nome_dept from departamento
where cod_dept in ( select cod_dept from empregado 
group by cod_dept having count (*) < 5 )
Subqueries não correlacionados
• Os subqueries não correlacionados são eficientes quando: 
– o query interno executa rapidamente e 
– recupera um pequeno conjunto de valores
select matricula , nome 
from empregado
where matricula in ( select matricula from gerentes )
Subqueries não correlacionados
• De modo geral temos em ordem decrescente de 
performance:
  joins
  correlated subqueries
  non-correlated subqueries
• Quando possível o SGDB transforma um subquery em join.
• Quando o subquery retorna um conjunto de valores, este 
é ordenado e as duplicatas são eliminadas. 
Subqueries
Desempenho
• Muitos recursos, muito avanço na área
– Índices
– Re-escrita de consultas
– Views materializadas
– Particionamento (PARTITION), replicação
– …
• Recursos específicos de cada SGBD
Dinâmica
• Resolva as necessidades de informação 
descritas sobre o cenário “Alunos”