Transcript Join e Index(Prova)

Operação de União “JOIN”
Professor Victor Sotero
SGD
1
JOIN
O “join” é uma operação de multi-tabelas
• Select: o nome da coluna deve ser precedido pelo
nome da tabela, se mais de uma coluna na tabela
especificada na cláusula “from” tiver o mesmo
nome.
• From: duas ou mais tabelas listadas no comando
“from” indicam ao SGBD que uma união é desejada.
• Where: colunas são comparadas; elas devem ter
valores similares.
SGD
2
ALGUMAS OBSERVAÇÕES
• Não precisam ter o mesmo tipo de dados; mas tem de ser
de um tipo que o SGBD automaticamente converta.
• Ex.:
– (int, smallint, tinyint, decimal real or float);
– (char, varchar, datetime and smalldatetime)
• Valores nulos nunca se unem
• Colunas na condição de “join” não precisam ser definidas
na cláusula “select”.
SGD
3
SINTAXE SIMPLIFICADA
select [tabela].nome_de_coluna, [,...]
from lista_de_tabelas
[where condições_de_pesquisa]
Exemplo:
select publishers.pub_id, titles.title_id
from publishers, titles
where publishers.pub_id = titles.pub_id
SGD
4
JOINS BASEADOS EM IGUALDADE
• Baseado em uma igualdade, entre os valores
nas colunas especificadas:
Select produto.nome,vendas.codigo
From produto, vendas
Where produto.data_chegada=vendas.data_saida;
SGD
5
JOINS BASEADOS EM IGUALDADE
• Utilizando o Join com order by
Select produto.nome,vendas.descricao
From produto, vendas
Where
produto.data_chegada=vendas.data_saida
order by produto.nome;
SGD
6
ALIASES
• Função para “apelidar” tabelas dentro do SQL:
Ex.: 1- Select a.nome,d.nome
from aluno a, disciplina d
where a.mat>30 and d.nome=‘BDD’;
2- select t.title_id
from titles t,titleauthor ta
where t.title_id = ta.title_id
and au_id = ‘409-56-7008’;
SGD
Quais os livros
escritos pelo autor de
código 409-56-7008?
7
JOINS BASEADOS EM NÃO
IGUALDADE
• Operadores de comparação usados.
• > maior que
• < menor que
• >= maior igual que
• <= menor igual que
• Exemplo:
select e.estado,a.Pnome,a.Snome,a.estado
from editora e,autor a
where a.estado>e.estado and nome_editora="Editora abril";
(Que autores moram num estado cujo o nome é
alfabeticamente maior que o estado da editora “Editora Abril”?)
SGD
8
SELF JOINS
• Você pode utilizar o Join usando a mesma
tabela, dando dois aliases, fazendo
comparações dentro da mesma:
Ex.: select a1.Snome, a1.Pnome
from autor a1, autor a2
where a1.Snome=a2.Snome and
a1.codigo!=a2.codigo;
SGD
Que autores possuem
o mesmo sobrenome?
9
OUTER JOIN
• Seleção que só mostra se as linhas de uma
tabela estão relacionadas(chave estrangeira)
com a outra tabela.
• Exemplificando na vida real: Mostrar quais
clientes tem algum pedido.
SGD
10
OUTER JOIN
• Left outer join: são incluídas todas as linhas da tabela do
primeiro nome da tabela(a tabela mais a esquerda expressão)
• Right outer join: são incluídas todas as linhas da tabela do
segundo nome da tabela (a tabela mais a esquerda)
• Ex.: Quais são os clientes que têm pedido e os que não têm?
Select cliente.nome,
pedido.codigo_cliente,pedido.num_pedido
from cliente left outer join pedido
on cliente.codigo_cliente=pedido.codigo_cliente;
SGD
11
INDEX
• Uma índex no banco de dados, como o
próprio nome sugere, é um índice que serve
para organizar os dados e agilizar na
pesquisa/consulta dos dados armazenado nas
tabelas.
SGD
12
INDEX
• Normalmente a criação de índex no banco de dados
segue o padrão ANSI, isso significa que o mesmo
comando para criar uma índex em um determinado
SGBD, pode ser usado em qualquer outro.
• Os índices são utilizados para encontrar registros
com um valor específico de uma coluna
rapidamente. Sem um índice o MySQL tem de iniciar
com o primeiro registro e depois ler através de toda
a tabela até que ele encontre os registros relevantes.
SGD
13
INDEX
• Normalmente você cria todos os índices em
uma tabela ao mesmo tempo em que a
própria tabela é criada com CREATE TABLE.
• Para colunas CHAR e VARCHAR, índices que
utilizam apenas parte da coluna podem ser
criados, usando a
sintaxenome_coluna(length) para indexar os
primeiros length() bytes de cada valor da
coluna.
SGD
14
EXEMPLO DE CRIAÇÃO DE ÍNDEX
• Pode ser usado tanto no SQL SERVER, MySQL
ou Oracle:
– Índice na tabela tab_cliente, sendo que o campo
de pesquisa é cpf (numero do CPF)
• CREATE INDEX nome_do_indice on
tab_cliente(cpf)
SGD
15
INDEX
• Exemplo de index para colunas do tipo char e
varchar:
– CREATE INDEX indice1 ON empregado
(nome(10));
Como a maioria dos nomes normalmente diferem
nos primeiros 10 caracteres, este índice não deve
ser muito menor que um índice criado com toda a
coluna nome_empregado.
SGD
16
VERSÕES DO MYSQL COMPATÍVEIS
• Você só pode adicionar um índice em uma
coluna que pode ter valores apenas se você
estiver usando o MySQL Versão 3.23.2 ou mais
novo e estiver usando os tipos de
tabelas MyISAM, InnoDB, ou BDB.
SGD
17
INDEX
• Normalmente para um melhor resultado de
performance, o ideal para criar índices são campos:
– Que sejam chaves;
select nome_cliente from cliente
where cpf = 12345678910
– Campos que façam join com outras tabelas.
– Campos que sejam números (tipo: integer, numeric)
SGD
18
DROP INDEX
• DROP INDEX nome_indice ON nome_tabela
• DROP INDEX apaga o índice
chamado nome_indice da
tabela nome_tabela. DROP INDEX não faz
nada nem versões do MySQL anteriores a
3.22.
SGD
19
RESTRIÇÕES UNIQUE
• Você pode usar as restrições UNIQUE para ter certeza de que
não há valores duplicados digitados em colunas específicas
que não participam de nenhuma chave primária. Embora a
restrição UNIQUE e a restrição PRIMARY KEY impõem
exclusividade, use a restrição UNIQUE em vez da restrição
PRIMARY KEY quando for impor a exclusividade de uma
coluna, ou uma combinação de colunas, que não seja uma
chave primária.
• Ex.:
– Create table empregado(
cod int not null,
primary key(cod),
nome varchar(45) unique not null);
SGD
20
CLÁUSULA DISTINCT
• Com exceção da chave primária, podem existir
colunas que tenham valores repetitivos. Essa
cláusula aplicada em uma consulta evita
valores repetitivos dentro de uma consulta.
– Ex.:
Select distinct nome
from produto;
SGD
21
STORED PROCEDURES
• Stored procedures não retornam dados. Elas
executam scripts no MySQL, que podem ser
desde alterações da estrutura de tabelas, até
migração de dados de uma tabela para outra,
ou executar ações de acordo com os
resultados de uma consulta.
SGD
22
STORED PROCEDURE
• Um dos novos recursos no MySQL 5.0
ainda em versão alfa
• conjunto de comandos SQL que podem ser
armazenados no servidor
• Aumento no desempenho: menos informação
enviada entre cliente/servidor
• Mais trabalho para o servidor
• Chamados pela isntrução CALL;
SGD
23
MOTIVOS PARA UTILIZAR
• Clientes em diferentes
linguagens
• Operações repetitivas
• Segurança
SGD
24
EXEMPLO DE STORED PROCEDURE
SGD
25
PROCEDURES
• Podem alterar dados:
mysql> create procedure apagaPessoas () delete
from pessoas;
Query OK, 0 rows affected (0.00 sec)
mysql> call apagaPessoas ();
Query OK, 9 rows affected (0.00 sec)
mysql> call pessoas ();
Empty set (0.00 sec)
SGD
26
PROCEDIMENTO PARA EXIBIR A
DATA E A HORA ATUAL
• CREATE PROCEDURE dataEhora() SELECT
• CURRENT_TIMESTAMP;
SGD
27
Like e Not Like
• Só funcionam com colunas do tipo char;
• Têm praticamente o mesmo funcionamento
que os operadores = e !=;
• Sua vantagem é a utilizações dos símbolos:
– %: substitui uma palavra;
– _: substitui um caracter.
SGD
28
Like e Not Like
• Exemplos:
• Listar todos os produtos cujo nome comece
com Q.
– Select codigo_produto, descricao_produto
from produto
where descricao_produto
like ’Q_’;
SGD
29
Like e Not Like
• Mostrar os professores que tenham o primeiro
nome João.
Select codigo,nome
From professor
where nome like ‘João%’;
SGD
30
Like e Not like
• Exemplos:
• Mostrar todos os produtos que tenham
nomes que comecem ou com J ou com Q.
– Select nome, codigo from produtos
where nome like ‘[JQ]%’;
SGD
31
Operadores baseados em IS NULL e
IS NOT NULL
• Mostrar os empregados que tenham seus
salários cadastrados no sistema como NULO.
– Select nome from empregado
where salario is null;
Mostrar as disciplinas que tenham a carga horária
como não nulo.
Select nome, codigo from disciplina
Where carga_horaria is not null;
SGD
32
MAX e MIN
• Max: mostra o maior valor dentro de um
campo em uma tabela;
• Min: mostra o menor valor de um campo
dentro de uma tabela;
• Ex: Select min(salario_fixo),max(salario_fixo)
From vendedor;
SGD
33
SUM
• Serve para fazer o somatório de todos os
valores de uma coluna.
• Ex: select sum(quantidade)
from item_pedido
where codigo_produto=‘50’;
SGD
34
AVG
• Apresenta a média de uma coluna.
• Exemplo:Qual a média dos salários fixos dos
vendedores?
– Select AVG(salario_fixo)
from vendedor;
SGD
35
SGD
36
Trigger
• Um TRIGGER ou gatilho é um objeto de banco
de dados, associado a uma tabela, definido
para ser disparado, respondendo a um evento
em particular;
• Tais eventos são os comandos da DML (Data
Manipulation
Language): INSERT, REPLACE, DELETE ou UPDA
TE.
SGD
37
Trigger
• Os TRIGGERS poderão ser disparados para
trabalharem antes ou depois do evento.
• Usaremos a seguinte tabela para nossos testes
SGD
38
Trigger
• Baseados nessa tabela, podemos criar um
trigger que seja executado antes ou depois de
inserir algo na mesma;
• antes (BEFORE)
• depois (AFTER)
SGD
39
Sintaxe: Trigger
SGD
40
SGD
41
SGD
42
SGD
43
SGD
44