Consultando Dados em Tabelas com SQL

Prévia do material em texto

Curso Superior de Análise e 
Desenvolvimento de Sistemas
Professor: Dr. Guilherme Dal Bianco
Sistema de Gerenciamento de Banco de Dados (S075)
 
● Tópicos
– Junção em consultas
– Update
– Delete
– Exercícios
DML – CONSULTANDO DADOS EM 
TABELAS
Estrutura Básica
SELECT => PROJEÇÃO
FROM => TABELA OU PRODUTO CARTESIANO DELAS
WHERE => SELEÇÃO
Coluna1[,Coluna2 [,...] ] (Condição(Tabela1 [X Tabela2 [X ... ] ]))
SELECT Coluna1[,Coluna2 [, ... ] ]
FROM Tabela1,[Tabela2 [, ... ] ]
WHERE Condição
DML – CONSULTANDO DADOS EM 
TABELAS
● SELECT/FROM - Projeta os dados da(s) tabela(s), de acordo 
com os critérios especificados.
● A projeção do resultado é em uma estrutura tipo tabela
● Basta informar o que se quer, sem se preocupar como fazer 
isto (SQL Não é procedural).
● Na cláusula SELECT, pode-se utilizar operadores aritméticos 
e funções de agregações, para projetar cálculos.
DML – CONSULTANDO DADOS EM TABELAS
Exemplos:
/* Projetar todas as informações dos autores */
SELECT CodAutor, Nome, Nascimento
FROM AUTOR ;
OU
SELECT *
FROM AUTOR ;
DML – CONSULTANDO DADOS EM TABELAS
Exemplos:
/* Projetar a média dos valores dos livros */
SELECT AVG (Valor) FROM LIVRO;
DML – CONSULTANDO DADOS EM TABELAS
Exemplos:
/* Projetar todas as informações dos autores */
SELECT CodAutor, Nome, Nascimento
FROM AUTOR ;
OU
SELECT *
FROM AUTOR ;
/* Projetar a média dos valores dos livros */
SELECT AVG (Valor)
FROM LIVRO;
/*Projetar todos os livros(títulos) e seus valores com 10% de desconto*/
SELECT Titulo, Valor - (Valor * 0.1)
FROM LIVRO;
/* Projetar a quantidade de autores cadastrados */
SELECT COUNT (*) AS QUANTIDADE, ‘ud’ AS UNIDADE
FROM AUTOR;
DML – CONSULTANDO DADOS EM 
TABELAS
 ALL é o padrão quando não especificado DISTINCT.
 Exemplos:
/* Projetar todas as cidades das editoras sem duplicatas */
SELECT ALL Cidade
 FROM EDITORA;
OU
SELECT Cidade
FROM EDITORA;
SELECT DISTINCT Cidade
FROM EDITORA;
/* Projetar todas as cidades
das editoras repetidamente*/
DML – CONSULTANDO DADOS EM 
TABELAS
 Uma coluna pode ser especificada pelo nome da sua tabela
(Tabela.Coluna), bem como, ser renomeada durante a consulta
(Coluna AS ColunaRenomeada)
/* Projetar todos os nomes e respectivos nascimentos da tabela 
autor. NOTE: mesmo especificando Tabela.Coluna, FROM é 
obrigatório */
SELECT AUTOR.Nome , AUTOR.Nascimento FROM AUTOR;
SELECT A.Nome , A.Nascimento FROM AUTOR as A;
DML – CONSULTANDO DADOS EM 
TABELAS
● WHERE - Especifica quais linhas da(s) tabela(s) listada(s) na 
cláusula FROM são afetadas pela condição.
 Se esta não for especificada, a consulta retornará todas as linhas da 
tabela.
● Operadores Utilizados
● A condição de WHERE pode ser de três tipos:
 Comparação
 Ligação entre tabelas (Join)
 Sub-Consulta (Sub-Queries)
DML – CONSULTANDO DADOS EM 
TABELAS
Comparação
● Expressão Operador Relacional Expressão
/* Projetar livros publicados após 30 de maio de 1993 */
SELECT *
FROM LIVRO
WHERE Publicacao > ‘5/30/93’;
 
●
● /* Projetar livros com valor de 10.00 a 100.00 */
SELECT *
FROM LIVRO
WHERE Valor BETWEEN 10.00 AND 100.00;
DML – CONSULTANDO DADOS EM 
TABELAS
Comparação
● Expressão [NOT] IN (Valores)
A condição é verdadeira se o valor da expressão é 
igual a um dos valores entre parênteses
/* Projetar as Editoras com sede em São Paulo ou Rio 
de Janeiro*/
SELECT *
FROM EDITORA
WHERE Cidade IN (‘São Paulo’, ‘Rio de Janeiro’);
A seguinte instrução SQL retornará pessoas com nomes que começam com 
um 'O':
SELECT * FROM persons
ONDE FirstName LIKE '% O'
A seguinte instrução SQL retornará pessoas com nomes que terminam com 
um 'a':
SELECT * FROM persons
ONDE FirstNameLIKE '% a'
Condição LIKE
A seguinte instrução SQL retornará pessoas com primeiros nomes que 
contenham o padrão 'la':
SELECT * FROM Persons
WHERE FirstName LIKE '%la%' 
Condição LIKECondição LIKE
● % (porcentagem), qualquer string de zero ou mais 
caracteres;
_ (anderscore), qualquer caractere simples; 
● [] (colchetes), qualquer caractere simples dentro da série 
especificada;
● [^] (colchetes e acento circunflexo), qualquer caractere 
simples fora da série especificada. 
Condição LIKE
 
Ordenando tuplas com Order By
● Exemplo: Liste em ordem alfabética os funcionarios que trabalham no 
departamento financeiro 
select distinct funcionario.nome
from funcionario, departamento
 where funcionario.codDepto=departamento.codDepto AND
 departamento.nome=‘financeiro’
 order by funcionario.nome
● Order by pode ser em ordem descentente 
– Exemplo: order by nome desc
A instrução UPDATE é usada para modificar os dados em uma tabela.
Syntax
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value 
UPDATEUpdate
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Storgt 67
Queremos adicionar um primeiro nome para a pessoa com um sobrenome de 
"Rasmussen":
UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen' 
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67
UPDATEUpdate
Queremos mudar o endereço e adicionar o nome da cidade:
UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen' 
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Storgt 67
UPDATEUpdate
A instrução DELETE é usado para apagar linhas de uma tabela.
Syntax
DELETE FROM table_name
WHERE column_name = some_value 
Delete
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger
"Nina Rasmussen" vai ser excluído
DELETE FROM Person WHERE LastName = 'Rasmussen' 
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
DELETEDelete
DML – CONSULTANDO DADOS EM 
TABELAS
● Ligação entre tabelas (Join)
● Diz-se que tabelas estão relacionadas se tiverem 
campos comuns (ch.primária e ch. estrangeira).
● O efeito do JOIN é a criação de uma tabela temporária 
em que cada par de linhas, que satisfação a condição 
de ligação, são ligados para formar uma única linha.
● A ligação é sempre estabelecida à frente da cláusula 
WHERE usando o operador relacional da igualdade (=).
 FROM estabelece o produto cartesiano entre as tabelas 
listadas
 WHERE filtra as linhas úteis segundo a condição 
especificada
 
Comando SQL
SELECT nome_emp, salario_base, nome_dept
FROM Empregado, Departamento, Categoria
WHERE nome_emp = ´António Abreu´
AND Empregado.cod_cat = Categoria.cod_cat 
AND Departamento.cod_dept = Empregado. cod_dept 
SELECT nome_emp, salario_base, nome_dept
FROM Empregado, Departamento, Categoria
WHERE nome_emp = ´António Abreu´
AND Empregado.cod_cat = Categoria.cod_cat 
AND Departamento.cod_dept = Empregado. cod_dept 
Qual o salário do empregado ´António Abreu´ e o nome do 
departamento a que pertence?
 
Projecção
SELECT cod_emp, nome_emp
FROM empregado
Clausulas Clausulas 
Select Select 
FromFrom
cod_emp nome_emp data_admissão cod_cat cod_dept cod_emp_chefe
1 António Abreu 13-Jan-75 1 1 1
2 Bernardo Bento 1-Dec-81 1 2 1
3 Carlos Castro 4-Jun-84 3 3 1
... ... ... ... ... ...
20 Manuel Matos 7-Feb-90 3 2 2
... ... ... ... ... ...
Empregado
 
Restrição
SELECT *
FROM categoria
WHERE salario_base > 200
Clausula WhereClausula Where
Categoria cod_cat designação salario_base
1 CategoriaA 300
2 CategoriaB 250
3 CategoriaC 160
... ... ...
 
Junção
A partir do produto cartesiano 
selecciona-se somente as 
linhas que satisfazem a 
condição
EMPREGADO.COD_DEPT= DEPTARTAMENTO.COD_DEPT
cod_emp nome_emp data_admissão cod_cat cod_dept cod_emp_chefe
1 António Abreu 13-Jan-75 1 1 1
2 Bernardo Bento 1-Dec-81 1 2 1
3 Carlos Castro 4-Jun-84 3 3 1
... ... ... ... ... ...
20 Manuel Matos 7-Feb-90 3 2 2
... ... ... ... ... ...
Empregado
cod_dept nome_dept localização
1 Contabilida Lisboa
2 VendasPorto
3 Investigação Coimbra
... ... ...
Departamento
 
Junção
SELECT nome_emp, empregado.cod_dept, nome_dept
FROM empregado, departamento
WHERE empregado.cod_dept = departamento.cod_dept
 
Caso o nome de uma coluna seja igual em várias tabelas então 
a REGRA é
Nome_Tabela.Nome_Coluna
em qualquer sítio da cláusula SELECT
 
Projecção, Restrição e Junção
SELECT empregado.cod_dept, nome_emp
FROM empregado, departamento
WHERE empregado.cod_dept = departamento.cod_dept
AND
nome_dept = 'Vendas'
Restrição Junção
Projecção
Qual o nome dos empregados pertencentes ao departamento de Vendas
 
Aliases de Tabelas
Correlation Name
Pretende-se o nome de cada empregado e o nome do respectivo chefe
SELECT E.nome, CH.nome
FROM empregado E, empregado CH
WHERE E.cod_emp_chefe = CH.cod_emp
SELECT cod_emp, D.cod_dept, nome_dept
FROM empregado E, departamento D
WHERE E.cod_dept = D.cod_dept 
Particularmente útil quando se pretende usar a mesma tabela com 
significados diferentes
 
Junções Múltiplas
cod_emp nome_emp data_admissão cod_cat cod_dept cod_emp_chefe
1 António Abreu 13-Jan-75 1 1 1
2 Bernardo Bento 1-Dec-81 1 2 1
3 Carlos Castro 4-Jun-84 3 3 1
... ... ... ... ... ...
20 Manuel Matos 7-Feb-90 3 2 2
... ... ... ... ... ...
Empregado
cod_cat designação salario_base
1 CategoriaA 300
2 CategoriaB 250
3 CategoriaC 160
... ... ...
Categoria
cod_dept nome_dept localização
1 Contabilidade Lisboa
2 Vendas Porto
3 Investigação Coimbra
... ... ...
Departamento
 
Junções Múltiplas
SELECT categoria.cod_cat, nome_emp, nome_dept, salario_base
FROM empregado, departamento, categoria
WHERE empregado.cod_dept = departamento.cod_dept
AND
empregado.cod_cat = categoria.cod_cat
Para cada categoria listar o nome dos empregados, salário_base e repectivo departamento
Texto 2. Implementando Integridade 
Referencial no MySQL
● Para adicionarmos restrições de 
integridade (constraints) às chaves 
estrangeiras, é necessário criar as tabelas 
como InnoDB. 
 
2.1.1. MyISAM
● Bom desempenho na leitura de dados 
– Índices em árvores binárias balanceadas;
– Arquitetura simplificada.
● Desvantagens 
– Não oferece mecanismos para controle de transação e 
mecanismos de integridade referencial;
– Mecanismo de lock (geral) por tabela.
 
2.1.2. InnoDB
● Desenvolvido pela InnoDBase e integrado ao MySQL a partir da 
versão 4.0.
● Vantagens: 
– Integridade referencial (SET NULL, RESTRICT, etc);
– Ferramenta para backup on-line;
– Lock de registro;
– Níveis de isolamento;
● Exige mais memória e disco que o MyISAM. Apresenta mais lento 
em algumas situações. 
Tipos de Restrições
● CASCADE: ao se remover um registro da tabela 
referenciada pela chave estrangeira os registros 
relacionados àquele removido serão eliminados em 
todas as tabelas relacionadas. 
● RESTRICT:não permite a remoção de registros que 
possuam relacionamentos em outras tabelas. 
● SET NULL e SET DEFAULT. atribuem os valores 
DEFAULT ou NULL para as chaves estrangeiras cujos 
registros relacionados foram excluídos. 
Exemplo
● CREATE TABLE aluno (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 nome CHAR(30) NOT NULL
) TYPE=InnoDB; 
● CREATE TABLE cursos (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 nome CHAR(30) NOT NULL
) TYPE=InnoDB; 
● CREATE TABLE notas (
 aluno_id INT NOT NULL,
 cursos_id INT NOT NULL,
 date DATE NOT NULL,
 nota DOUBLE NOT NULL,
 PRIMARY KEY(aluno_id, cursos_id, date),
 FOREIGN KEY (aluno_id) REFERENCES aluno(id) ON DELETE CASCADE,
 FOREIGN KEY (cursos_id) REFERENCES cursos(id) ON DELETE RESTRICT
) TYPE=InnoDB; 
Explicação
● A tabela cursos que contém as disciplinas ministradas e a tabela 
notas com os pontos dos alunos em todos os cursos freqüentados 
por eles. 
● No modelo é possível que um curso possua várias avaliações em 
datas distintas. Neste caso, foram criadas as tabelas como tipo 
InnoDB (TYPE=InnoDB), para que as regras de integridade sejam 
respeitadas. As regras definidas foram: um CASCADE para aluno, 
isto é, se for removido um registro da tabela de aluno, todas as 
suas notas serão removidas automaticamente. 
● No caso da tabela de cursos, não será possível remover um curso 
que possua notas cadastradas para ele. 
● Além da restrição ON DELETE, o InnoDB permite também o ON 
UPDATE, que aplica as restrições no caso de atualizações dos 
campos ralacionados entre as tabelas.
	Diapositivo 1
	Diapositivo 2
	Diapositivo 3
	Diapositivo 4
	Diapositivo 5
	Diapositivo 6
	Diapositivo 7
	Diapositivo 8
	Diapositivo 9
	Diapositivo 10
	Diapositivo 11
	Diapositivo 12
	Diapositivo 13
	Diapositivo 14
	Diapositivo 15
	Diapositivo 16
	Diapositivo 22
	Diapositivo 23
	Diapositivo 24
	Diapositivo 25
	Diapositivo 26
	Diapositivo 27
	Diapositivo 28
	Comando SQL
	Projecção
	Restrição
	Junção
	Diapositivo 38
	Projecção, Restrição e Junção
	Aliases de Tabelas
	Junções Múltiplas
	Diapositivo 42
	Diapositivo 45
	Diapositivo 46
	Diapositivo 47
	Diapositivo 48
	Diapositivo 50
	Diapositivo 51
	Diapositivo 52