wiki

BDA - Aula 21 - 2014/2

DML


Linguagem de Manipulação de Dados (ou DML, de Data Manipulation Language) é uma família de linguagens de computador utilizadas para a recuperação, inclusão, remoção e modificação de informações em bancos de dados. Pode ser procedural, que especifica como os dados devem ser obtidos do banco; pode também ser declarativa (não procedural), em que os usuários não necessitam especificar o caminho de acesso, isto é, como os dados serão obtidos. O padrão SQL é não procedural. DMLs foram utilizadas inicialmente apenas por programas de computador, porém (com o surgimento da SQL) também têm sido utilizadas por pessoas.


INSERT


É utilizado o comando INSERT para inserir linhas nas tabelas: 

INSERT INTO clima VALUES ('São Francisco', 46, 50, 0.25, '1994-11-27');

Repare que todos os tipos de dado possuem formato de entrada de dados bastante óbvios. As constantes, que não são valores numéricos simples, geralmente devem estar entre apóstrofos ('), como no exemplo acima O tipo date é, na verdade, muito flexível em relação aos dados que aceita

O tipo point requer um par de coordenadas como entrada, como mostrado abaixo: 

INSERT INTO cidades VALUES ('São Francisco', '(-194.0, 53.0)');

A sintaxe usada até agora requer que seja lembrada a ordem das colunas. Uma sintaxe alternativa permite declarar as colunas explicitamente: 

INSERT INTO clima (cidade, temp_min, temp_max, prcp, data)
VALUES ('São Francisco', 43, 57, 0.0, '1994-11-29');

Se for desejado, pode-se declarar as colunas em uma ordem diferente, ou mesmo, omitir algumas colunas. Por exemplo, se a precipitação não for conhecida: 

INSERT INTO clima (data, cidade, temp_max, temp_min)
VALUES ('1994-11-29', 'Hayward', 54, 37);

Muitos desenvolvedores consideram que declarar explicitamente as colunas é melhor que confiar na ordem implícita.

SELECT


  • Para trazer os dados de uma tabela, a tabela deve ser consultada
  • Para esta finalidade é utilizado o comando SELECT do SQL. Este comando é dividido em:
    • lista de seleção (a parte que especifica as colunas a serem trazidas)
    • lista de tabelas (a parte que especifica as tabelas de onde os dados vão ser trazidos)
    • uma qualificação opcional (a parte onde são especificadas as restrições)
  • Por exemplo, para trazer todas as linhas da tabela clima digite:
SELECT * FROM clima;

Aqui o * é uma forma abreviada de "todas as colunas". 

SELECT cidade, temp_min, temp_max, prcp, data FROM clima;

A saída deve ser: 

    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27
São Francisco   |       43 |       57 |    0 | 1994-11-29
Hayward         |       37 |       54 |      | 1994-11-29
(3 linhas)

Na lista de seleção podem ser especificadas expressões, e não apenas referências a colunas. Por exemplo, pode ser escrito 

SELECT cidade, (temp_max+temp_min)/2 AS temp_media, data FROM clima;

    cidade      | temp_media |    data
----------------+------------+------------
São Francisco   |         48 | 1994-11-27
São Francisco   |         50 | 1994-11-29
Hayward         |         45 | 1994-11-29
(3 linhas)

Perceba que a cláusula AS foi utilizada para mudar o nome da coluna de saída (a cláusula AS é opcional).

A consulta pode ser "qualificada", adicionando a cláusula WHERE para especificar as linhas desejadas. A cláusula WHERE contém expressões booleanas (valor verdade), e somente são retornadas as linhas para as quais o resultado da expressão booleana for verdade. São permitidos os operadores booleanos usuais (AND, OR e NOT) na qualificação. Por exemplo, o comando abaixo mostra o clima de São Francisco nos dias de chuva: 

SELECT * FROM clima
WHERE cidade = 'São Francisco' AND prcp > 0.0;

Resultado:
    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27
(1 linha)

Pode ser solicitado que os resultados da consulta sejam retornados em uma determinada ordem: 

SELECT * FROM clima
   ORDER BY cidade;
    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
Hayward         |       37 |       54 |      | 1994-11-29
São Francisco   |       43 |       57 |    0 | 1994-11-29
São Francisco   |       46 |       50 | 0.25 | 1994-11-27

Neste exemplo a ordem de classificação não está totalmente especificada e, portanto, as linhas de São Francisco podem retornar em qualquer ordem. Mas sempre seriam obtidos os resultados mostrados acima se fosse executado: 

SELECT * FROM clima
ORDER BY cidade, temp_min;

Pode ser solicitado que as linhas duplicadas sejam removidas do resultado da consulta: 

SELECT DISTINCT cidade
FROM clima;
   cidade
---------------
Hayward
São Francisco
(2 linhas)

Novamente, neste exemplo a ordem das linhas pode variar. Pode-se garantir resultados consistentes utilizando DISTINCT e ORDER BY juntos: 

SELECT DISTINCT cidade
FROM clima
ORDER BY cidade;

Até agora as consultas somente acessaram uma tabela de cada vez. As consultas podem acessar várias tabelas de uma vez, ou acessar a mesma tabela de uma maneira que várias linhas da tabela sejam processadas ao mesmo tempo. A consulta que acessa várias linhas da mesma tabela, ou de tabelas diferentes, de uma vez, é chamada de consulta de junção. Como exemplo, suponha que se queira listar todas as linhas de clima junto com a localização da cidade associada. Para fazer isto, é necessário comparar a coluna cidade de cada linha da tabela clima com a coluna nome de todas as linhas da tabela cidades, e selecionar os pares de linha onde estes valores são correspondentes.

Nota: Este é apenas um modelo conceitual, a junção geralmente é realizada de uma maneira mais eficiente que realmente comparar cada par de linhas possível, mas isto não é visível para o usuário.

Esta operação pode ser efetuada por meio da seguinte consulta: 

SELECT *
FROM clima, cidades
WHERE cidade = nome;
    cidade      | temp_min | temp_max | prcp |    data    |     nome      | localizacao
----------------+----------+----------+------+------------+---------------+-------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27 | São Francisco | (-194,53)
São Francisco   |       43 |       57 |    0 | 1994-11-29 | São Francisco | (-194,53)
(2 linhas)

Duas coisas devem ser observadas no conjunto de resultados produzido:

Não existe nenhuma linha de resultado para a cidade Hayward. Isto acontece porque não existe entrada correspondente na tabela cidades para Hayward, e a junção ignora as linhas da tabela clima sem correspondência. Veremos em breve como isto pode ser corrigido.

Existem duas colunas contendo o nome da cidade, o que está correto porque a lista de colunas das tabelas clima e cidades estão concatenadas. Na prática isto não é desejado, sendo preferível, portanto, escrever a lista das colunas de saída explicitamente em vez de utilizar o *:

Exercícios


  • 01. Abra seu SGBD
  • 02. Crie a tabela Climas
  • 03. Insira dados coletados na Web sobre as seguintes cidades
    • Belo Horizonte
    • Cuiabá
    • Curitiba
    • Fortaleza
    • Uberlândia
  • 04. Crie a tabela Cidades com os seguintes campos:
    • Nome(Str) , Habitantes (Int) , NomeEstado (Str), Aniversário (Date) , Area (Km2) (Float)
  • 05. Insira dados a tabela acima
  • 06. Crie os índices nome e Aniversário
  • 07. Altere o nome do Campo Habitantes para NroHabitantes
  • 08. Crie a tabela Eventos com os seguintes campos:
    • NomeEvento (Str) , Data (Date) , Horário (Int), Local (Str), Preço (Float)
  • 09. Insira dados de eventos quaisquer para as cidades
  • 10. Gere um relatório mostrando os dados da Cidade
  • 11. Gere um relatório mostrando apenas Nome, Estado e Area das Cidades
  • 12. Gere um relatório mostrando Nome, NomeEstado e Area das Cidades em ordem alfabética de Nome
  • 13. Gere um relatório mostrando Nome, NomeEstado e Area das Cidades em ordem descendente de Nome
  • 14. Gere um relatório com todos os eventos eliminando os redundantes
  • 15. Gere uma view com todos os dados de Cidades com o Clima
  • 16. Gere uma view com todos os dados de Cidades com seus eventos
  • 17. Exclua a cidade de Curitiba
  • 18. Recupere a tabela com a cidade de Curitiba
  • 19. Inclua a tabela Estados com os seguintes campos:
    • UF (Int), NomeEstado (Str), Regiao (Str)
  • 20. Insira dados para a tabela Estados


  1. Select * from Cidades;
  2. Select * from Climas;
  3. Select * from Estados;
  4. Select * from Eventos;


Upload do PDF referente a cada Select acima.

Arquivo:Cidades.pdf

Arquivo:Climas.pdf

Arquivo:Estados.pdf

Arquivo:Eventos.pdf







DML



SELECT cidade, temp_min, temp_max, prcp, data, localizacao
FROM clima, cidades
WHERE cidade = nome;


UPDATE


As linhas existentes podem ser atualizadas utilizando o comando UPDATE. Suponha ter sido descoberto que as leituras de temperatura realizadas após 28 de novembro de 1994 estão todas 2 graus mais altas. Os dados podem ser atualizados da seguinte maneira: 

UPDATE clima
SET temp_max = temp_max - 2,  temp_min = temp_min - 2
WHERE data > '1994-11-28';

Agora vejamos o novo estado dos dados: 

SELECT * FROM clima;
    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27
São Francisco   |       41 |       55 |    0 | 1994-11-29
Hayward         |       35 |       52 |      | 1994-11-29
(3 linhas)


DELETE


As linhas podem ser excluídas da tabela através do comando DELETE. Suponha que não estamos mais interessados no clima de Hayward. Então podemos executar comando a seguir para excluir estas linhas da tabela 

DELETE FROM clima WHERE cidade = 'Hayward';

e todos os registros de clima pertencentes a Hayward serão removidos. Agora vejamos o novo estado dos dados: 

SELECT * FROM clima;
   cidade     | temp_min | temp_max | prcp |    data
--------------+----------+----------+------+------------
São Francisco |       46 |       50 | 0.25 | 1994-11-27
São Francisco |       41 |       55 |    0 | 1994-11-29
(2 linhas)

Deve-se tomar cuidado com comandos na forma 

DELETE FROM nome_da_tabela;

porque, sem uma qualificação, o comando DELETE remove todas as linhas da tabela, deixando-a vazia. O sistema não solicita confirmação antes de realizar esta operação.




INNER JOIN

Para se mostrar dados de mais de uma tabela, é necessário usar a instrução INNER JOIN, que adiciona tabelas para visualização utilizando a chave estrangeira. 

SELECT nometab1, nometab2 FROM tab2 INNER JOIN tab1 ON (tab2.idtab = tab1.idtab);


Operadores


  • Existem 4 operadores SQL que operam sobre todos os tipos de dados:


Operador: Significado
---------------------
BETWEEN ..AND.. : Entre dois valores
IN(lista) : Corresponde a qualquer valor da lista
LIKE : Cadeia de caracteres que satisfaz uma condição
IS NULL : É um valor nulo


  • Exemplos:


SELECT Nome, Cargo, Cod_Depar
FROM Empregado
WHERE Cargo='Secretario';


SELECT Nome, N_Superior
FROM Empregado
WHERE N_Superior IN (7902,7566,7788);


SELECT Nome
FROM Empregado
WHERE Nome LIKE 'S%';


SELECT Nome, N_Superior
FROM Empregado
WHERE N_Superior IS NULL;


SELECT Nome, Ordenado
FROM Empregado
WHERE Ordenado NOT BETWEEN 100000 AND 200000;


Group By


  • Na linguagem SQL é possível obter resultados baseados em grupos de tuplas, assim, existem funções de grupo que operam sobre conjuntos de tuplas
    • O comando GROUP BY é utilizado para dividir as tuplas de uma tabela em grupos menores
    • Algumas das funções de grupo são:


AVG(n): Valor médio de n
COUNT(expr): Número de vezes que a expressão toma um valor
MAX(expr): Valor máximo de expr
MIN(expr): Valor mínimo de expr
SUM(n): Soma dos valores de n


  • Expr indica os argumentos que podem ser do tipo CHAR, Number ou DATE
    • Todas as funções de grupo, à exceção de COUNT(*), ignoram os valores nulos
    • Torna-se ainda importante destacar que as funções de grupo por si só tratam todos as tuplas de uma tabela como um grupo.


  • Exemplos:
SELECT AVG(Ordenado)
FROM Empregado;


SELECT MIN(Ordenado)
FROM Empregado
WHERE Cargo='secretario';


SELECT Cargo, AVG(Ordenado) MediaDeOrdenado
FROM Empregado
GROUP BY Cargo;


SELECT Cargo, Cod_Depar, AVG(Ordenado) MédiaDeOrdenado
FROM Empregado
GROUP BY Cod_Depar, Cargo
ORDER BY Cod_Depar;


SELECT Cargo, MAX(Ordenado) MaxDeOrdenado
FROM Empregado
GROUP BY Cargo
HAVING MAX(Ordenado) >= 300000;


Having x Where


  • A cláusula WHERE é utilizada para fazer filtros em colunas
  • Já a cláusula HAVING também permite que façamos filtros, mas não em colunas como a WHERE e sim em coluna que estejam sendo agregadas


  • Por exemplo:
SELECT TIPO_CLIENTE , COUNT(TIPO_CLIENTE)
FROM CLIENTE
WHERE TIPO_CLIENTE <> 'VIP'
HAVING COUNT(TIPO_CLIENTE) > 2
GROUP BY TIPO_CLIENTE


  • A instrução SELECT acima retorna os tipos dos clientes e quantos de cada tipo de cliente existem na tabela CLIENTE desde que haja mais de dois clientes com o mesmo tipo
  • Somente os tipos de cliente que não forem iguais a 'VIP' serão considerados.


  • Principais diferenças entre WHERE e HAVING em um SELECT


  • Vejamos um exemplo, como a consulta:
select dt_venda as 'Data',
avg(total_venda) as 'Média'
from tb_venda 
where dt_venda <> '2008-05-01' and dt_venda <> '2008-05-03' and dt_venda <> '2008-05-05'
group by dt_venda


  • Produz o mesmo resultado que :
select dt_venda as 'Data',
avg(total_venda) as 'Média'
from tb_venda 
group by dt_venda
having dt_venda <> '2008-05-01' and dt_venda <> '2008-05-03' and dt_venda <> '2008-05-05'


  • Entretanto, a consulta:
select dt_venda as 'Dia', 
sum(total_venda) as 'Total'
from tb_venda
where sum(total_venda) > 3000
group by dt_venda


  • Não substitui:
select dt_venda as 'Dia',
sum(total_venda) as 'Total'
from tb_venda 
group by dt_venda
having sum(total_venda) > 3000


  • Embora tenham comportamentos semelhantes (a cláusula WHERE e a cláusula HAVING destinam-se a filtrar resultados), elas também tem suas diferenças.
    • A primeira diferença refere-se a ordem em que são processadas.
      • Enquanto a cláusula WHERE filtra as linhas antes de agrupar, a cláusula HAVING filtra as linhas após o agrupamento (entenda-se agrupamento a presença de funções como SUM, COUNT, etc e a cláusula GROUP BY).
      • Isso já leva a uma diferença de desempenho, uma vez que na esmagadora maioria das vezes, filtrar os resultados o mais cedo possível é melhor.
      • Qual seria a utilidade de selecionar registros, agrupá-los e depois descartá-los ?
    • A segunda diferença refere-se a possibilidades de filtros.
      • Enquanto a cláusula WHERE limita-se a filtrar resultados simples, a cláusula HAVING possibilita filtros com bases em funções agregadas.
      • Não é possível por exemplo colocar filtros na cláusula WHERE usando funções como COUNT, SUM, AVG, etc e nem utilizar operadores como SOME, ANY e ALL.
      • Embora tenha funcionado no seu exemplo
    • isso é sem dúvida uma agressão ao padrão ANSI e essa construção deve ser evitada a qualquer custo.


  • Via de regra pode-se fazer o seguinte:
    • Filtros de linhas referenciando campos não agregados devem ser feitos na cláusula WHERE
    • Filtros de linhas referenciando campos agregados devem ser feitos na cláusula HAVING
    • Não utilize uma cláusula para realizar filtros da outra


Exercícios


  • 21. Relacione os registros de Estados com Cidade
  • 22. Inclua na tabela Eventos, registros coletados na Web para:
    • Rio de Janeiro
    • São Paulo
  • 23. Exclua um evento qualquer colocando data como data e hora como condição
  • 24. Altere o valor de todos os preços de todos os eventos em 5%
  • 25. Altere o valor dos preços de todos os eventos em 5% para São Paulo
  • 26. Altere a data de qualquer evento de Uberlândia postergando 3 dias
  • 27. Exclua o Estado de Minas Gerais da tabela Estados
  • 28. Mostre os eventos sobre rock
  • 29. Mostre os eventos numa faixa de valores
  • 30. Mostre os eventos e o clima da cidade que sediará o evento
  • 31. Mostre os eventos das seguintes cidades: São Paulo, Rio de Janeiro e Curitiba
  • 32. Mostre os eventos agrupados por cidade
  • 33. Mostre os eventos agrupados por cidade com preço inferior a R$ 100,00
  • 34. Calcule a média de preço de todos os eventos
  • 35. Calcule a média de preço dos eventos de São Paulo
  • 36. Calcule o evento de menor preço em São Paulo ou Rio de Janeiro
  • 37. Mostre todos os eventos com o nome da Cidade e Estado
  • 38. Mostre todos os eventos ordenados por data com o nome da Cidade e Estado
  • 39. Mostre todos os eventos ordenados por data e agrupados por Cidade
  • 40. Mostre todos os eventos ordenados por Preço e agrupados por Estado


Disponível em “http://3.143.69.156/wiki/index.php?title=BDA_-_Aula_21_-_2014/2&oldid=34181”