4 - DML (1)

Prévia do material em texto

• Linguagem de Definição de Dados
• Linguagem de Manipulação de Dados
• Linguagem SQL
Banco de Dados I
1
• DDL:
– Um esquema de banco de dados é especificado 
por um conjunto de definições expressas por uma 
linguagem especial chamada linguagem de 
definição de dados (Data Definition Language, 
DDL);
– O resultado dos comandos de uma DDL é um 
conjunto tabelas que são armazenadas em um 
arquivo especial chamado dicionário (ou 
diretório) de dados.
BD.I - Linguagem de Definição de Dados
2
– Um diretório de dados é um arquivo que contém 
metadados (dados sobre dados);
– Este diretório é consultado antes que os dados 
sejam lidos ou modificados no SGDB;
BD.I - Linguagem de Definição de Dados
3
– A estrutura de armazenagem e os métodos de 
acesso usados em um SGDB são especificados por 
um conjunto de definições em um tipo especial de 
DDL chamado linguagem de armazenagem e 
definição de dados;
– O resultado da compilação destas definições é um 
conjunto de instruções para especificar a 
implementação de detalhes do esquema de banco 
de dados que estão normalmente escondidos dos 
usuários.
BD.I - Linguagem de Definição de Dados
4
• DML:
– Os níveis de abstração (níveis físico, conceitual e 
de visão) não se aplicam somente à definição ou 
estrutura de dados, mas também à sua 
manipulação;
– No nível físico definimos algoritmos que permitem 
um acesso eficiente aos dados. 
– Nos níveis mais altos de abstração é dada ênfase à 
facilidade de uso. O objetivo é fornecer uma 
interação humana eficiente com o sistema
BD.I - Linguagem de Modificação de Dados
5
• DML:
– A linguagem de manipulação de dados (Data 
Manipulation Language) é a linguagem que 
permite aos usuários fazer o acesso aos dados ou 
manipulá-los, conforme o modelo de dados.
– A manipulação de dados significa:
• a busca da informação armazenada no BD; 
• a inserção de novas informações nos BD; 
• a eliminação de informações no BD; 
• a modificação de dados armazenados no BD.
BD.I - Linguagem de Modificação de Dados
6
• Introdução:
– A linguagem SQL é um padrão de linguagem de 
consulta que usa uma combinação de construtores em 
Álgebra e Cálculo Relacional e possui as seguintes 
partes:
• Linguagem de definição de dados (DDL); 
• Linguagem interativa de manipulação de dados (DML); 
• Incorporação DML (Embedded SQL); 
• Definição de Visões; 
• Autorização; 
• Integridade; 
• Controle de Transações. 
BD.I - Linguagem SQL
7
• Estrutura básica:
– Uma expressão básica de consulta SQL consiste 
em três cláusulas: 
• Select
• From;
• Where.
BD.I - Linguagem SQL
8
• SELECT:
– Corresponde à operação de projeção da álgebra 
relacional. É usada para relacionar os atributos 
desejados no resultado de uma consulta (relação);
– O SQL permite duplicidades nas tuplas de 
resposta. 
• Para forçar a eliminação de duplicidade usa-se a palavra 
chave DISTINCT depois de SELECT;
– O asterisco “*” pode ser usado para denotar 
“todos os atributos”.
BD.I - Linguagem SQL
9
• FROM:
– Corresponde à operação de produto cartesiano da 
álgebra relacional;
– Associa as relações (tabelas) que serão 
pesquisadas durante a avaliação de uma 
expressão;
BD.I - Linguagem SQL
10
• WHERE:
– Corresponde à seleção do predicado da álgebra 
relacional;
– Consiste em um predicado envolvendo atributos 
da relação que aparece na cláusula FROM;
– A cláusula where pode conter expressões 
aritméticas envolvendo os operadores de 
comparação <, <=, >, >=, = e <>, e operandos
constantes ou atributos das tuplas.
BD.I - Linguagem SQL
11
• WHERE:
– Nessa cláusula pode-se usar os conectores lógicos 
AND, OR e NOT;
– O SQL possui o operador de comparação 
BETWEEN AND para simplificar a cláusula where
que especifica que um valor pode ser menor ou 
igual a algum valor e maior ou igual a algum outro 
valor.
BD.I - Linguagem SQL
12
BD.I - Linguagem SQL
• Exercícios 1:
– Pesquisar todos os registros da relação “cliente”, 
da base “SAKILA”;
– Selecionar somente os registros com o atributo 
“cliente_id” menor que 50;
– Selecionar somente os dados com o atributo 
“cliente_id” entre 100 e 150;
– Selecionar somente os registros com o atributo 
que identifica que o cliente está ativo com o 
sistema;
13
• SELECT – Renomeando colunas (ALIAS):
– Para renomear atributos (colunas) ou relações 
(tabelas), usamos a cláusula AS:
• EX: nome_antigo as nome_novo;
– Pode ser usado tanto na cláusula SELECT quanto 
na cláusula FROM.
– A cláusula AS é particularmente útil como ALIAS, 
ou seja, um apelido para as relações constantes na 
cláusula FROM ou, ainda, quando criamos 
atributos novos (colunas calculadas, somatórios, 
agrupamentos, etc).
BD.I - Linguagem SQL
14
BD.I - Linguagem SQL
• Exercícios 2:
– Selecionar somente os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME dos registros com o atributo “CLIENTE_ID” menor 
que 100, da relação CLIENTE, alterando os nomes dos atributos 
PRIMEIRO_NOME e ULTIMO_NOME para “Nome” e “Sobre_nome”, 
respectivamente;
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE, usando o ALIAS “cli” para a 
relação CLIENTE e informando o nome canônico dos atributos;
• Ex:
• SELECT x.coluna1, x.coluna2 FROM tabela1 AS x;
15
• SELECT – Comparando Strings:
– As operações em strings mais usadas são as 
checagens para verificação de coincidências, 
utilizando o operador LIKE. Para isso, usamos dois 
caracteres especiais:
• Porcentagem ( % ): compara qualquer string; 
• Sublinhado ( _ ): compara qualquer caractere. 
– Utilizando NOT LIKE pode-se pesquisar diferenças, 
ao invés de coincidências.
BD.I - Linguagem SQL
16
• SELECT – Comparando Strings:
– Exemplos:
• “Uni%” corresponde a qualquer string que comece com 
“Uni”: “universo”, “universal”, “universidade”...;
• “%er” corresponde a qualquer string que termine com 
“er”: “fazer”, “trazer”, “correr”, ...;
• “%abc%” corresponde a qualquer string que contenha a 
sequência de caracteres “abc”;
• “_ _ _ _%” corresponde a qualquer string com pelo 
menos quatro caracteres;
• “_ _ X _” corresponde a qualquer string com quatro 
caracteres e que o terceiro seja “X”;
BD.I - Linguagem SQL
17
BD.I - Linguagem SQL
• Exercícios 3:
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE que tenham o atributo 
“PRIMEIRO_NOME” iniciando com a letra “G”;
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE que tenham o atributo 
“ULTIMO_NOME” terminando com a string “on”;
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE cujo atributo 
“ULTIMO_NOME” contenha a string “ann”;
18
BD.I - Linguagem SQL
• Exercícios 4:
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE que tenham o atributo 
“PRIMEIRO_NOME” com pelo menos 4 caracteres;
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE que tenham o atributo 
“PRIMEIRO_NOME ” com 4 caracteres e com a letra “a” 
como segundo caracter;
– Refazer todos os exercícios de comparação de strings 
utilizando o operador NOT LIKE;
19
• SELECT – Concatenando Strings:
– Podemos concatenar (somar) dois atributos do 
tipo string, criando uma nova coluna, usando o 
comando CONCAT():
• Ex: Select CONCAT(coluna_nome , coluna_sobrenome)
– OBS.: será criado um novo atributo, porém sem 
nome e, para que possamos referenciar esse 
atributo em outras consultas, devemos fornecer 
uma ALIAS para essa coluna.
BD.I - Linguagem SQL
20
BD.I - Linguagem SQL
• Exercícios 5:
– Selecionar os atributos CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE, criando um novo atributo 
chamado Nome_Completo que será a concatenação dos atributos 
PRIMEIRO_NOME e ULTIMO_NOME, de forma que o nome completo 
fique legível.
– Selecionar os atributos Conta CLIENTE_ID, PRIMEIRO_NOME e 
ULTIMO_NOME da relação CLIENTE, criando um novo atributo 
chamado Nome_Completo que será a concatenaçãodos atributos 
PRIMEIRO_NOME e ULTIMO_NOME, de forma que o nome completo 
fique na notação inglesa.
21
• Exercícios 6:
– Criar 3 consultas para selecionar os atributos 
CLIENTE_ID, PRIMEIRO_NOME e ULTIMO_NOME da 
relação CLIENTE, sendo que :
• a 1ª deve conter somente os registros com atributo 
DATA_NASCIMENTO anteriores a “30/07/1980”;
• a 2ª deve conter somente os registros com atributo 
DATA_NASCIMENTO entre “30/07/1980” a “30/08/1985”;
• a 3ª deve conter somente os registros com atributo 
DATA_NASCIMENTO após “30/08/1980”.
BD.I - Linguagem SQL
22
• Exercícios 7:
– Criar uma consulta para selecionar os atributos 
CLIENTE_ID, PRIMEIRO_NOME e ULTIMO_NOME da 
relação CLIENTE, sendo que deve obedecer os 
critérios:
• trazer os registros com atributo DATA_NASCIMENTO 
anteriores a “30/07/1980 e atributo ATIVO = 0;
• Ou trazer os registros com atributo DATA_NASCIMENTO 
entre “30/08/1985” a “30/08/1989” e atributo ATIVO = 0;
• Ou trazer os registros com atributo DATA_NASCIMENTO 
posterior a “30/09/1989” e atributo ATIVO = 1.
BD.I - Linguagem SQL
23
• Exercícios 8:
– Criar uma consulta para selecionar os atributos 
CLIENTE_ID, PRIMEIRO_NOME e ULTIMO_NOME da 
relação CLIENTE, sendo que deve obedecer os 
critérios:
• trazer os registros com atributo DATA_NASCIMENTO 
entre “30/08/1985” a “30/08/1989” e com atributo 
ATIVO = 0;
• E trazer os registros que o atributo CLIENTE_ID seja par;
BD.I - Linguagem SQL
24
As cláusulas são condições de modificação utilizadas para definir os dados que deseja
selecionar ou modificar em uma consulta.
• FROM - Utilizada para especificar a tabela que se vai selecionar os registros.
• WHERE – Utilizada para especificar as condições que devem reunir os registros que serão 
selecionados.
• GROUP BY – Utilizada para separar os registros selecionados em grupos específicos.
• HAVING – Utilizada para expressar a condição que deve satisfazer cada grupo.
• ORDER BY – Utilizada para ordenar os registros selecionados com uma ordem especifica.
• DISTINCT – Utilizada para selecionar dados sem repetição.
Cláusulas
Funções de Agregação
• As funções de soma se usam dentro de uma cláusula SELECT em grupos de registros 
para devolver um único valor que se aplica a um grupo de registros.
• AVG – Utilizada para calcular a média dos valores de um campo determinado.
• COUNT – Utilizada para devolver o número de registros da seleção.
• SUM – Utilizada para devolver a soma de todos os valores de um campo determinado.
• MAX – Utilizada para devolver o valor mais alto de um campo especificado.
• MIN – Utilizada para devolver o valor mais baixo de um campo especificado.
Cláusulas
• Base de dados – WORLD:
– Calcular expectativa mínima e máxima de vida e o 
total da população por Continente;
– Calcular expectativa mínima e máxima de vida e o 
total da população por Continente, cuja 
expectativa mínima seja >= que 50 anos;
– Mostrar a média da população por país, 
calculando sobre as cidades que pertencem a cada 
país, apenas do continente europeu; 
Exercícios 1
• Base de dados – WORLD:
– Calcular expectativa mínima e máxima de vida e o 
total da população por Região e Continente;
– Calcular a quantidade de cidades por País;
– Calcular a quantidade de cidades por País, Região 
e Continente;
• Aplicar as funções de agregação nas tabelas do 
banco Sakila;
Exercícios 2
• Usando a base Sakila, responda:
– Quantidade total de filmes;
– Duração média dos filmes
– Quantidade de filmes por categoria;
– Duração média dos filmes por categoria;
– Quantidade de filmes por categoria das categorias 
com menos de 57 filmes;
– Duração média dos filmes por categoria das 
categorias com menos de 57 filmes;
– Quantidade de filmes alugados por cliente em ordem 
decrescente de quantidade de filmes alugados;
Exercícios 3
• INSERT:
– É usado para inserir dados em uma relação 
(tabela);
– Estrutura:
• INSERT INTO nome_tabela (campo1, campo2,...)
• VALUES (valor1, ‘valor2’,...)
– Opcional:
• INSERT INTO nome_tabela (campo1, campo2,...)
• SELECT campo1, campo2 FROM nome_tabela2
• WHERE nome_tabela2.campoX = valorY
BD.I - Linguagem SQL
30
• Exercícios 9:
– Insira os seus dados na relação CLIENTE;
• Use os dados abaixo para os seguintes campos:
– LOJA_ID: 1;
– ENDERECO_ID: 5;
– ATIVO: 1;
– Faça uma consulta na relação CLIENTE selecionando 
os registros com CLIENTE_ID <= 2 e insira esses dados 
novamente na própria tabela;
BD.I - Linguagem SQL
32
• UPDATE:
– É usado para alterar dados em uma relação 
(tabela);
– Estrutura:
• UPDATE nome_tabela
• SET campo1 = valor1, campo2 = valor2
• FROM nome_tabela
• WHERE campoX = valorY
BD.I - Linguagem SQL
33
• Exercícios 10:
– Altere o atributo EMAIL para NULO (sem valor) da 
tabela CLIENTE do registro criado com os seus dados;
– Altere o atributo ATIVO para o valor 0 dos registros 
novos inseridos no Exercício 9;
BD.I - Linguagem SQL
34
• DELETE:
– É usado para apagar dados em uma relação 
(tabela);
– Estrutura:
• DELETE FROM nome_tabela
• WHERE campoX = valorY
BD.I - Linguagem SQL
35
• Exercícios 11:
– Apague o registro da tabela CLIENTE criado com os 
seus dados;
– Apague os registros novos inseridos no Exercício 9;
BD.I - Linguagem SQL
36
• JOINS:
– O comando JOIN é utilizado para trazer dados de duas 
ou mais tabelas, fazendo junção dessas relações por um 
ou mais atributos;
– O SGDB utiliza os JOINS para controlar como os dados 
de uma tabela serão utilizados para selecionar dados de 
outra tabela;
– Dessa forma, podemos selecionar dados de uma tabela 
(Tab1), filtrando por uma determinada coluna 
(Tab1.ColunaX) e trazer todos os registros de outra 
tabela (Tab2) que tenham relação com a primeira 
(Tab1.ColunaY = Tab2.ColunaZ)
BD.I - Linguagem SQL
37
• Tipos de JOINs:
– INNER JOIN:
• Ou também chamado de Junção natural:
– OUTER JOIN:
• Pode ser de 3 sub-tipos:
– LEFT JOIN;
– RIGHT JOIN;
– FULL JOIN;
– CROSS JOIN:
• Ou também chamado de produto cartesiano;
BD.I - Linguagem SQL
38
• Tipos de JOINs:
– INNER JOIN:
• Ou também chamado de Junção natural;
• Retorna todos as linhas onde ambas as colunas sejam 
coincidentes
• Ex:
– SELECT *
FROM cliente AS c 
INNER JOIN endereco AS e ON c.endereco_id = e.endereco_id
INNER JOIN cidade AS cid ON e.cidade_id = cid.cidade_id
INNER JOIN pais AS p ON cid.pais_id = p.pais_id;
BD.I - Linguagem SQL
39
• Tipos de JOINs:
– OUTER JOINs:
• LEFT JOIN – inclui todas as linhas da tabela da esquerda
e junta com as linhas da tabela da direita. Onde NÃO
houver coincidência será exibido o valor NULL;
• RIGHT JOIN – inclui todas as linhas da tabela da direita
e junta com as linhas da tabela da esquerda. Onde NÃO
houver coincidência será exibido o valor NULL;
BD.I - Linguagem SQL
40
• Ex. de OUTER JOINs:
• LEFT JOIN:
– SELECT f.titulo, a.data_de_aluguel FROM filme AS F LEFT JOIN 
inventario AS i ON f.filme_id = i.filme_id LEFT JOIN aluguel AS a ON 
i.inventario_id = a.inventario_id WHERE f.titulo = 'ACADEMY 
DINOSAUR';
• RIGHT JOIN:
– SELECT f.titulo,I.nome FROM FILME AS F RIGHT JOIN idioma AS I ON 
F.idioma_id = I.idioma_idORDER BY I.nome; 
BD.I - Linguagem SQL
41
• Tipos de JOINs:
– OUTER JOINs:
• FULL JOIN – inclui todas as linhas de ambas as tabelas, 
inserindo o valor NULL em ambos os lados da ligação 
quando não houver coincidências;
– Obs.: No MySQL não há suporte para o FULL JOIN, contudo 
pode-se simular o comportamento usando o comando UNION 
e juntando duas consultas de LEFT e RIGHT JOIN.
BD.I - Linguagem SQL
42
• Exemplos:
CREATE TABLE tab1 ( 
id INT NOT NULL, 
nome VARCHAR(45) NULL, 
PRIMARY KEY (id)); 
CREATE TABLE tab2 ( 
id INT NOT NULL, 
nome VARCHAR(45) NULL, 
PRIMARY KEY (id));
insert into tab1 (id, nome) values (1, 'pedro'), (2, 'paulo');
insert into tab2 (id, nome) values (1, 'pedro'), (3, 'josé');
BD.I - Linguagem SQL
43
• Exemplos:
– INNER JOIN:
SELECT * FROM tab1 INNER JOIN tab2 ON tab1.id = tab2.id;
– LEFT JOINSELECT * FROM tab1 LEFT JOIN tab2 ON tab1.id = tab2.id;
– RIGHT JOIN:
SELECT * FROM tab1 RIGHT JOIN tab2 ON tab1.id = tab2.id;
– FULL JOIN (simulação):
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.id = tab2.id
UNION
SELECT * FROM tab1 RIGHT JOIN tab2 ON tab1.id = tab2.id;
BD.I - Linguagem SQL
44