SlideShare uma empresa Scribd logo
Tópicos Especiais em
Desenvolvimento de Software
Aprofundamento de DDL & DML
Objetivo: Resgatar recursos avançados de
bancos de dados
Prof. Nécio de Lima Veras
Parte I : DDL
Relembrando DDL
• Qual sua serventia?
• Possui conjuntos de:
– Domínios (incluindo o valor null);
– Comandos:
• Create (database, table, ...);
• Alter;
• P/ restrições de integridades (chaves,
valores com checagens, valor padrão,
valor único, valor não nulo, referencial);
• Certo, e agora o que podemos ter de
novidade?
Eis algumas novidades...
• Criação de domínios;
• Criação de índices;
• Criação de gatilhos;
• Criação de procedimentos;
• Criação de funções;
• Criação de visões;
Criação de Domínios
• Útil para padronizações;
• Comando:
create domain <identificador> <tipo> [opções]
• Exemplos:
1)create domain nomePessoa varchar(45);
2)create domain codigos int not null
default autoincrement;
3)create domain combustiveis varchar(15)
check (combustiveis in ('Gasolina',
'Alcool', 'Diesel', 'Flex'));
● Detalhe, o mySQL não implementa;
Apagando um domínio
• drop domain <identificador>;
• Cuidado, pois você poderá estar usando o
domínio;
Criação de Índices
• Serve para otimizações de consultas;
• Para as chaves já são criados índices automáticos;
• Sintaxe:
create index <identificador> on <tabela>
(coluna [ asc || desc] [, ...]);
• Exemplos:
create index idx_notaR on TBNotas (nNotaR
asc);
create index idx_inicioNome on tbAluno
(vNome (10)); → note a diferença (este
pega apenas os 10 primeiros caracteres do
nome);
Mais sobre índices
• Sintaxe do Comando p/ excluir:
drop index <identificador> on <tabela>;
Exemplo:
drop index idx_notaR on tbNotas;
• Visualizando (mySQL):
– show index from tbNotas;
Gatilhos (triggers)
• Servem como armas prontas para serem
disparadas;
• Sintaxe do comando (mySQL):
create trigger <identificador> <tempo>
<evento> on <tabela> for each row
begin
end
– Tempo: Before ou After;
– Evento: Insert, Update ou Delete;
Gatilhos (triggers)
• Limitações (mySQL):
– Não pode realizar chamadas a
procedimentos ou funções que retornem
dados ou usam comandos SQL
dinâmicos, por exemplo: now();
– Não pode ter comandos explícitos de
controle de transações, por exemplo:
commit ou rollback;
Gatilhos (triggers)
• Exemplo (o campo dDataNasc SEMPRE será null):
delimiter !
create trigger dataNascNull before insert on tbAluno
for each row
begin
if new.dDataNasc is not null then
set new.dDataNasc = null;
end if;
end; !
delimiter ;
Exibindo os gatilhos (mySQL)
• Comando:
show triggers from <database>;
– Exemplo:
show triggers from dbTeste;
• Excluindo:
– drop trigger <nome_da_trigger>;
– Exemplo:
drop trigger dataNascNull;
Criação de Procedimentos
• Serve para promover:
– Modularização;
– Refatoração;
– E evitar códigos duplicados;
• Sintaxe do comando (mySQL):
create procedure <identificador> ([tipo_parametro]
<identificador_parâmetro> <tipo> [,...])
begin
end;
• Observações:
– Tipo_Parâmetro: in , out ou inout;
Exemplos de Procedimento
• Criando:
create procedure addAluno (in nome varchar(30))
begin
insert into tbAluno (vNome)
values (nome);
end;
• Executando:
call addAluno ('Nécio Veras');
• Excluindo:
drop procedure addAluno;
• Visualizando:
select * from information_schema.routines; ou
show procedure status;
Exemplo de um procedimento
com retorno
• Criando o procedimento:
create procedure recuperaAluno (in cod int, out
nome varchar(30), dataNasc date)
begin
select vNome, dDataNasc from tbAluno
where id = cod
into nome, dataNasc;
end;
• Executando:
call listaAluno (10, @nm, @dt);
• Exibindo o resultado:
select @nm, @dt
Funções
• Retornam valores (podem inclusive serem
calculados);
• Também servem para criar UDF's
(Funções definidas por usuários);
• Sintaxe do comando:
create function <identificador>
(<identificador_parâmetro> <tipo> [,...])
returns <tipo>
begin
end;
Exemplo
• Retornando a média:
create function calcMedia (codNota int)
returns numeric(4,1)
begin
declare n1 numeric(4,1);
declare n2 numeric(4,1);
select nNota1, nNota2 from tbNotas where iNota_id =
codNota
into n1, n2;
return (n1 + n2) / 2;
end;
• Executando:
select calcMedia (101);
Visões
• É um select pré-moldado, serve para
personalizar comandos de consulta e deixá-
los preparados para a execução;
• Sintaxe do comando:
create view <identificador> [<lista de
colunas>] as <comando select>;
• Exemplo:
create view listaAlunos (cod, nome)
as select id, vNome from tbAluno;
• Visualizando:
select * from listaAlunos ;
Exercícios
• Abstraia e crie um banco qualquer (com 3 tabelas que
se relacionem)
– Crie índices que possam agilizar possíveis
consultas futuras;
– Crie gatilhos que possam ser úteis ao banco de
dados;
– Crie procedimentos que padronize as
operações CRUD em TODAS as tabelas;
– Crie funções que possam agilizar possíveis
cálculos;
– Crie visões que forneçam suporte para a
construção de relatórios (a serem imaginados
por você!)
Parte II - DML
Aprofundando
• Sabemos que existem as operações
CRUD que servem como base para a
DML;
• O aprofundamento se dá, principalmente,
no comando de RETRIVE;
– SELECT;
• As outras operações, praticamente, não
existem variações dentre o que já foi
exposto;
Relembrando...
• Formatação básica:
select A1, A2, A3, ..., An
from r1, r2, ..., rm
where P
onde Ai representa um atributo
ri é uma (relação) tabela
P é um predicado (condição/critério de seleção).
A cláusula BETWEEN
• Especifica intervalos (inclusive);
• Ex:
SELECT numero_emprestimo
FROM emprestimo
WHERE total BETWEEN 90 AND 100;
• Similarmente, temos o NOT BETWEEN;
A cláusula From
• Define um produto cartesiano das relações;
– A junção natural é definida em termos de produto cartesiano;
• Exemplo:
– Queremos a seguinte consulta: “para todos os clientes que
tenham um empréstimo em um banco, encontre seus
nomes e números de empréstimos”;
– Em SQL:
SELECT distinct nome_cliente,
devedor.numero_emprestimo
FROM devedor, emprestimo
WHERE devedor.numero_emprestimo =
emprestimo.numero_emprestimo
– Observe as especificações de <tabela>.<coluna> , pois isso
servirá para evitar AMBIGUIDADES;
A cláusula As
• Como as tabelas podem possuir nomes relativamente
grandes, então podemos “apelidá-las” usando a cláusula
As;
• O mesmo comando anterior ficaria:
SELECT distinct nome_cliente,
d.numero_emprestimo
FROM devedor as d, emprestimo as e
WHERE d.numero_emprestimo =
e.numero_emprestimo
• Pode ser aplicado no nome da tabela ou no nome do
campo;
– No caso do nome do campo será “apelidada” a
coluna do resultado da consulta;
Cláusula Like
• É aplicado em operações de Strings;
• Serve para checar coincidências de pares;
• Elementos:
% => Compara qualquer substring;
_ => Compara qualquer caractere;
• Lembre-se que são sensíveis ao caso;
• Exemplo:
SELECT nome_cliente FROM cliente
WHERE rua_cliente LIKE ‘%Avenida%’;
• Outros exemplos:
– 'Francisco%' => Qualquer valor que inicie com Francisco;
– '_ _ _' => Qualquer string que tenha 3 caracteres;
• Também podemos usar o NOT LIKE;
Cláusula Order By
• Serve para ordenar as tuplas resultantes de uma
consulta;
• Temos duas opções:
– Asc => Ascendente;
– Desc => Descendente;
• Pode ordenar qualquer tipo de atributo;
• Exemplo:
SELECT *
FROM emprestimo
ORDER BY total DESC, numero_emprestimo
ASC;
A Cláusula Join
• Compõem mecanismos de junção de DUAS relações, são eles:
– Junção condicional;
– Junção natural;
– Junção externa;
– Outras...
• Tipos de junção:
– Define como as tuplas de cada relação irão se corresponder e quais
atributos serão incluídos no resultado final;
– São eles:
• Inner Join (junção interna);
• Outer Join (junção externa);
– São opcionais estas especificações;
– Tipos de condições:
• Natural;
• On;
• Using;
Junção Interna
• É quando tuplas são incluídas no resultado de uma
consulta somente se existir uma correspondente na
outra relação;
• Exemplo:
SELECT tabela1.atributo1, tabela2.atributo2
FROM tabela1 INNER JOIN tabela2 ON
tabela1.atributo1 = tabela2.atributo2;
• Se o nome dos atributos for igual, não há necessidade da
cláusula ON, basta usar o NATURAL;
SELECT tabela1.atributo1, tabela2.atributo2
FROM tabela1 NATURAL JOIN tabela2;
– Neste caso, a cláusula INNER também não
precisa;
Junção Externa
• É quando tuplas são incluídas no resultado sem que exista
uma tupla correspondente na outra relação;
• Tipos:
– RIGHT OUTER JOIN = Junção Externa a Direita;
– LEFT OUTER JOIN = Junção Externa a Esquerda;
– FULL OUTER JOIN = Junção Externa Total (Junção
Externa a Esquerda + Junção Externa a Direita);
• Exemplo:
SELECT R1.A1, R2.A2
FROM R1 RIGHT OUTER JOIN R2 ON R1.A1= R2.A2;
ou
SELECT R1.A1, R2.A2
FROM R1 LEFT OUTER JOIN R2 ON R1.A1= R2.A2;
Operações de Conjunto
• Possuímos as operações de União, Interseção e
Exceção (exceto ou subtração);
• Exemplo de união:
(SELECT nome_cliente FROM depositante)
UNION
(SELECT nome_cliente FROM devedor);
• Esta operação, automaticamente, eliminina as
repetições;
– Se isso não for desejado então usa-se UNION
ALL;
– E isso é válido para TODAS as operações de
conjunto;
Operações de conjunto
• De forma análoga à união, temos:
– Interseção : intersect;
– Exceção: except;
Funções agregadas
• São funções simples que parte de um conjunto de dados
(valores) e retorna uma valor simples (único);
• Exemplos:
– Média (average): avg;
– Mínimo (minimum): min;
– Máximo (maximum): max;
– Total (total): sum;
– Contagem (count): count;
– Desvio Padrão: stddev;
• Exemplo:
SELECT avg(saldo) FROM conta
WHERE nome_agencia = ‘Parnaíba-Central’;
A cláusula Group By
• Existem circunstância em que gostaríamos de
aplicar uma função agregada não somente a
um conjunto de tuplas, mas também a um
grupo de conjunto de tuplas;
• Isso é possível usando a cláusula SQL group
by;
• Exemplo: 'Encontrar a média dos saldos nas
contas de cada uma das agências do banco':
SELECT nome_agencia, avg(saldo)
FROM conta
GROUP BY nome_agencia;
Leia sobre ...
• Existem ainda muitas outras coisas que
devem ser pesquisadas e estudadas, tais
como:
– Subconsultas (Cláusulas IN, SOME, ALL,
EXISTS, UNIQUE);
– Subconsultas escalares;
Exercícios

Mais conteúdo relacionado

ODP
Aula10 sql-ddl
Rafael Albani
 
PDF
Aula2 - SQL
Rafael Albani
 
PDF
Banco de Dados - MySQL Basico
Rangel Javier
 
PDF
Linguagem SQL (com MySQL)
Marco Pinheiro
 
PDF
Banco de dados - Aula 1 SQL
Daniel Brandão
 
PDF
Consultas SQL
Alexandre Silva
 
DOCX
Exercícios de mysql
Leo Frazão
 
PPTX
Treinamento de SQL Básico
Igor Alves
 
Aula10 sql-ddl
Rafael Albani
 
Aula2 - SQL
Rafael Albani
 
Banco de Dados - MySQL Basico
Rangel Javier
 
Linguagem SQL (com MySQL)
Marco Pinheiro
 
Banco de dados - Aula 1 SQL
Daniel Brandão
 
Consultas SQL
Alexandre Silva
 
Exercícios de mysql
Leo Frazão
 
Treinamento de SQL Básico
Igor Alves
 

Mais procurados (18)

PDF
Sql básico - Teoria e prática: Um grande resumo
Helder Lopes
 
PDF
Sql com sql server básico - Bóson treinamentos
Fábio dos Reis
 
DOCX
Comandos DDL para o MySQL
Arley Rodrigues
 
PPTX
TOTVS LINHA RM TREINAMENTO SQL
Fábio Delboni
 
PDF
Aula de SQL - Básico
Airton Zanon
 
PPTX
Sql - introdução
Samuel Novais, MBA
 
PPSX
Linguagem SQL
André Nobre
 
PPTX
Banco de dados aula 4
Ed W. Jr
 
PDF
Aula 05 acessando o mysql
Roitier Gonçalves
 
PPTX
A Estrutura da Linguagem SQL
Centro Paula Souza
 
DOCX
Sql - Introdução ao mysql
Arley Rodrigues
 
PPT
Fundamentos de SQL - Parte 5 de 8
Emiliano Barbosa
 
PPTX
Aula 11 banco de dados
Jorge Ávila Miranda
 
PDF
Introdução ao SQL
Thiago Freitas
 
PPTX
Hands on Labs - SQL Server 2008
Leonardo Lourenço Silva
 
PPTX
Aula 10 banco de dados
Jorge Ávila Miranda
 
PPTX
Banco de dadados MySQL com PHP
Leonardo Soares
 
PPT
Fundamentos de SQL - Parte 6 de 8
Emiliano Barbosa
 
Sql básico - Teoria e prática: Um grande resumo
Helder Lopes
 
Sql com sql server básico - Bóson treinamentos
Fábio dos Reis
 
Comandos DDL para o MySQL
Arley Rodrigues
 
TOTVS LINHA RM TREINAMENTO SQL
Fábio Delboni
 
Aula de SQL - Básico
Airton Zanon
 
Sql - introdução
Samuel Novais, MBA
 
Linguagem SQL
André Nobre
 
Banco de dados aula 4
Ed W. Jr
 
Aula 05 acessando o mysql
Roitier Gonçalves
 
A Estrutura da Linguagem SQL
Centro Paula Souza
 
Sql - Introdução ao mysql
Arley Rodrigues
 
Fundamentos de SQL - Parte 5 de 8
Emiliano Barbosa
 
Aula 11 banco de dados
Jorge Ávila Miranda
 
Introdução ao SQL
Thiago Freitas
 
Hands on Labs - SQL Server 2008
Leonardo Lourenço Silva
 
Aula 10 banco de dados
Jorge Ávila Miranda
 
Banco de dadados MySQL com PHP
Leonardo Soares
 
Fundamentos de SQL - Parte 6 de 8
Emiliano Barbosa
 
Anúncio

Semelhante a Aprofundamento de DDL e DML (20)

PPTX
Banco dedados aula002
Cláudio Amaral
 
PDF
Introdução ao Banco de Dados, Introdução ao Banco de Dados,
kelbyrodrigues4
 
PPTX
12 SQL - Junções / Join
Centro Paula Souza
 
PPTX
Aula 07 - Linguagem DQL - Bancos de Dados.pptx
gabrielmachado85453
 
PPTX
introduao-a-sql-prof-anderson-cavalcanti-ufrn-ct-dca.pptx
SaraR49
 
PDF
Dicas oracle sql
Marcos Oliveira
 
PDF
Sql basico postgresql_v12
aguicaetano
 
DOCX
Tutoriavapihwjejejjeekkekekekekekl_SQL.docx
OrfeuSancho
 
PDF
Um pouco sobre sql
Ricardo Kovalski Cruz
 
PDF
Apostila - Banco de Dados
José Corrêa Viana
 
PPTX
Curso de PostgreSQL: Um pouco Além dos Comandos
Marcos Thomaz
 
PDF
Aulas_SQL.pdf
DaniloPereira341965
 
PDF
Introdução a Banco de Dados (Parte 3)
Mario Sergio
 
PPT
Aula 11 - Conceitos de SQL Básicos, uso constante
dalita1592
 
PPTX
Base de Dados Consultas Aninhadas.pptx
MuseiwaLopes
 
PPT
Bd sql (1)
jheyeizah
 
PDF
jahundredsound
Felipe Galhardo
 
PPT
Banco de dados comandos sql
Nilson Augustini
 
Banco dedados aula002
Cláudio Amaral
 
Introdução ao Banco de Dados, Introdução ao Banco de Dados,
kelbyrodrigues4
 
12 SQL - Junções / Join
Centro Paula Souza
 
Aula 07 - Linguagem DQL - Bancos de Dados.pptx
gabrielmachado85453
 
introduao-a-sql-prof-anderson-cavalcanti-ufrn-ct-dca.pptx
SaraR49
 
Dicas oracle sql
Marcos Oliveira
 
Sql basico postgresql_v12
aguicaetano
 
Tutoriavapihwjejejjeekkekekekekekl_SQL.docx
OrfeuSancho
 
Um pouco sobre sql
Ricardo Kovalski Cruz
 
Apostila - Banco de Dados
José Corrêa Viana
 
Curso de PostgreSQL: Um pouco Além dos Comandos
Marcos Thomaz
 
Aulas_SQL.pdf
DaniloPereira341965
 
Introdução a Banco de Dados (Parte 3)
Mario Sergio
 
Aula 11 - Conceitos de SQL Básicos, uso constante
dalita1592
 
Base de Dados Consultas Aninhadas.pptx
MuseiwaLopes
 
Bd sql (1)
jheyeizah
 
jahundredsound
Felipe Galhardo
 
Banco de dados comandos sql
Nilson Augustini
 
Anúncio

Mais de Nécio de Lima Veras (20)

PDF
Introdução ao JavaFX
Nécio de Lima Veras
 
PDF
Introdução à analise e complexidade de algoritmos
Nécio de Lima Veras
 
PDF
Teste de software
Nécio de Lima Veras
 
PDF
Versionamento com git
Nécio de Lima Veras
 
PDF
Uma Abordagem Baseada em Agentes para Planejamento e Monitoramento de Serviço...
Nécio de Lima Veras
 
PDF
Introdução à Engenharia de Software Orientada a Agentes com JaCaMo
Nécio de Lima Veras
 
PDF
Jason: Componentes personalizados
Nécio de Lima Veras
 
PDF
Agentes inteligentes com jason
Nécio de Lima Veras
 
PDF
Ambientes em Sistemas Multi-agentes
Nécio de Lima Veras
 
PDF
Arquiteturas concretas de agentes inteligentes - bdi agents
Nécio de Lima Veras
 
PDF
Revisão de matemática
Nécio de Lima Veras
 
PDF
Especificações iniciais de agentes inteligentes
Nécio de Lima Veras
 
ODP
Notas sobre agentes inteligentes
Nécio de Lima Veras
 
ODP
Anotações do mapeamento OR
Nécio de Lima Veras
 
ODP
Hibernate-consultas
Nécio de Lima Veras
 
ODP
Mapeamento de herança OR
Nécio de Lima Veras
 
ODP
Relacionamentos do mapeamento OR
Nécio de Lima Veras
 
ODP
Processos iniciais do mapeamento OR
Nécio de Lima Veras
 
PDF
Java swing
Nécio de Lima Veras
 
PDF
Introdução à linguagem UML
Nécio de Lima Veras
 
Introdução ao JavaFX
Nécio de Lima Veras
 
Introdução à analise e complexidade de algoritmos
Nécio de Lima Veras
 
Teste de software
Nécio de Lima Veras
 
Versionamento com git
Nécio de Lima Veras
 
Uma Abordagem Baseada em Agentes para Planejamento e Monitoramento de Serviço...
Nécio de Lima Veras
 
Introdução à Engenharia de Software Orientada a Agentes com JaCaMo
Nécio de Lima Veras
 
Jason: Componentes personalizados
Nécio de Lima Veras
 
Agentes inteligentes com jason
Nécio de Lima Veras
 
Ambientes em Sistemas Multi-agentes
Nécio de Lima Veras
 
Arquiteturas concretas de agentes inteligentes - bdi agents
Nécio de Lima Veras
 
Revisão de matemática
Nécio de Lima Veras
 
Especificações iniciais de agentes inteligentes
Nécio de Lima Veras
 
Notas sobre agentes inteligentes
Nécio de Lima Veras
 
Anotações do mapeamento OR
Nécio de Lima Veras
 
Hibernate-consultas
Nécio de Lima Veras
 
Mapeamento de herança OR
Nécio de Lima Veras
 
Relacionamentos do mapeamento OR
Nécio de Lima Veras
 
Processos iniciais do mapeamento OR
Nécio de Lima Veras
 
Introdução à linguagem UML
Nécio de Lima Veras
 

Último (20)

DOCX
Mapa Histórico da Oceania Colonial .docx
Doug Caesar
 
PDF
A Geografia de Portugal.pdfRegiões e relevo.
biblioteca123
 
DOCX
Mapa de Papua-Nova Guiné - Mapa dos Países do Mundo
Doug Caesar
 
PDF
BIOLOGIA CELULAR - Biologia | Primeiro Ano Ens. Médio
islaineeli
 
DOCX
Mapa Histórico da Oceania Impérios e Reinos .docx
Doug Caesar
 
PPTX
2021_EM_ART_2ªSERIE_SLIDES_ULaaA 01.pptx
AnnaPaulaAires1
 
PPT
Ponto_5_-_Valorizacao dos profissionais da educ
mbjustus
 
PDF
🧑Entrevista de trabalho.pdf para um lugar
biblioteca123
 
PDF
Aula redação e leitura ................
VIVIANEMENDESSILVA
 
DOCX
Mapa da Austrália - Mapa dos Países do Mundo.docx
Doug Caesar
 
DOCX
Mapa da Oceania - Países e Dependências.docx
Doug Caesar
 
PPT
apresentação em power point motivação escolar.PPT
JniorFrancieleNeves
 
PDF
Aula_PPG_29-04-2025-9cd1e60aeb694adfa56ba07d0a4dc736.pdf
RassaMatias1
 
DOCX
Mapa da Regionalização da América do Norte.docx
Doug Caesar
 
PPTX
ESTUDO 25 - O PLANO DIVINO DOS SECULOS.pptx
Pr Davi Passos - Estudos Bíblicos
 
PDF
🎨 DiálogoGraça Morais.pdfartista plástica
biblioteca123
 
PPTX
PowerPoint_24_A_defini____o_institucional_de_arte.pptx
16aliceduarte
 
DOCX
Mapa de Nauru - Mapa dos Países do Mundo
Doug Caesar
 
PPTX
Saude_mental_no_trabalho_psicologia organizacional
mbjustus
 
PDF
Visita ao museu.pdf, museu marítimo de Ílhavo
biblioteca123
 
Mapa Histórico da Oceania Colonial .docx
Doug Caesar
 
A Geografia de Portugal.pdfRegiões e relevo.
biblioteca123
 
Mapa de Papua-Nova Guiné - Mapa dos Países do Mundo
Doug Caesar
 
BIOLOGIA CELULAR - Biologia | Primeiro Ano Ens. Médio
islaineeli
 
Mapa Histórico da Oceania Impérios e Reinos .docx
Doug Caesar
 
2021_EM_ART_2ªSERIE_SLIDES_ULaaA 01.pptx
AnnaPaulaAires1
 
Ponto_5_-_Valorizacao dos profissionais da educ
mbjustus
 
🧑Entrevista de trabalho.pdf para um lugar
biblioteca123
 
Aula redação e leitura ................
VIVIANEMENDESSILVA
 
Mapa da Austrália - Mapa dos Países do Mundo.docx
Doug Caesar
 
Mapa da Oceania - Países e Dependências.docx
Doug Caesar
 
apresentação em power point motivação escolar.PPT
JniorFrancieleNeves
 
Aula_PPG_29-04-2025-9cd1e60aeb694adfa56ba07d0a4dc736.pdf
RassaMatias1
 
Mapa da Regionalização da América do Norte.docx
Doug Caesar
 
ESTUDO 25 - O PLANO DIVINO DOS SECULOS.pptx
Pr Davi Passos - Estudos Bíblicos
 
🎨 DiálogoGraça Morais.pdfartista plástica
biblioteca123
 
PowerPoint_24_A_defini____o_institucional_de_arte.pptx
16aliceduarte
 
Mapa de Nauru - Mapa dos Países do Mundo
Doug Caesar
 
Saude_mental_no_trabalho_psicologia organizacional
mbjustus
 
Visita ao museu.pdf, museu marítimo de Ílhavo
biblioteca123
 

Aprofundamento de DDL e DML

  • 1. Tópicos Especiais em Desenvolvimento de Software Aprofundamento de DDL & DML Objetivo: Resgatar recursos avançados de bancos de dados Prof. Nécio de Lima Veras
  • 2. Parte I : DDL
  • 3. Relembrando DDL • Qual sua serventia? • Possui conjuntos de: – Domínios (incluindo o valor null); – Comandos: • Create (database, table, ...); • Alter; • P/ restrições de integridades (chaves, valores com checagens, valor padrão, valor único, valor não nulo, referencial); • Certo, e agora o que podemos ter de novidade?
  • 4. Eis algumas novidades... • Criação de domínios; • Criação de índices; • Criação de gatilhos; • Criação de procedimentos; • Criação de funções; • Criação de visões;
  • 5. Criação de Domínios • Útil para padronizações; • Comando: create domain <identificador> <tipo> [opções] • Exemplos: 1)create domain nomePessoa varchar(45); 2)create domain codigos int not null default autoincrement; 3)create domain combustiveis varchar(15) check (combustiveis in ('Gasolina', 'Alcool', 'Diesel', 'Flex')); ● Detalhe, o mySQL não implementa;
  • 6. Apagando um domínio • drop domain <identificador>; • Cuidado, pois você poderá estar usando o domínio;
  • 7. Criação de Índices • Serve para otimizações de consultas; • Para as chaves já são criados índices automáticos; • Sintaxe: create index <identificador> on <tabela> (coluna [ asc || desc] [, ...]); • Exemplos: create index idx_notaR on TBNotas (nNotaR asc); create index idx_inicioNome on tbAluno (vNome (10)); → note a diferença (este pega apenas os 10 primeiros caracteres do nome);
  • 8. Mais sobre índices • Sintaxe do Comando p/ excluir: drop index <identificador> on <tabela>; Exemplo: drop index idx_notaR on tbNotas; • Visualizando (mySQL): – show index from tbNotas;
  • 9. Gatilhos (triggers) • Servem como armas prontas para serem disparadas; • Sintaxe do comando (mySQL): create trigger <identificador> <tempo> <evento> on <tabela> for each row begin end – Tempo: Before ou After; – Evento: Insert, Update ou Delete;
  • 10. Gatilhos (triggers) • Limitações (mySQL): – Não pode realizar chamadas a procedimentos ou funções que retornem dados ou usam comandos SQL dinâmicos, por exemplo: now(); – Não pode ter comandos explícitos de controle de transações, por exemplo: commit ou rollback;
  • 11. Gatilhos (triggers) • Exemplo (o campo dDataNasc SEMPRE será null): delimiter ! create trigger dataNascNull before insert on tbAluno for each row begin if new.dDataNasc is not null then set new.dDataNasc = null; end if; end; ! delimiter ;
  • 12. Exibindo os gatilhos (mySQL) • Comando: show triggers from <database>; – Exemplo: show triggers from dbTeste; • Excluindo: – drop trigger <nome_da_trigger>; – Exemplo: drop trigger dataNascNull;
  • 13. Criação de Procedimentos • Serve para promover: – Modularização; – Refatoração; – E evitar códigos duplicados; • Sintaxe do comando (mySQL): create procedure <identificador> ([tipo_parametro] <identificador_parâmetro> <tipo> [,...]) begin end; • Observações: – Tipo_Parâmetro: in , out ou inout;
  • 14. Exemplos de Procedimento • Criando: create procedure addAluno (in nome varchar(30)) begin insert into tbAluno (vNome) values (nome); end; • Executando: call addAluno ('Nécio Veras'); • Excluindo: drop procedure addAluno; • Visualizando: select * from information_schema.routines; ou show procedure status;
  • 15. Exemplo de um procedimento com retorno • Criando o procedimento: create procedure recuperaAluno (in cod int, out nome varchar(30), dataNasc date) begin select vNome, dDataNasc from tbAluno where id = cod into nome, dataNasc; end; • Executando: call listaAluno (10, @nm, @dt); • Exibindo o resultado: select @nm, @dt
  • 16. Funções • Retornam valores (podem inclusive serem calculados); • Também servem para criar UDF's (Funções definidas por usuários); • Sintaxe do comando: create function <identificador> (<identificador_parâmetro> <tipo> [,...]) returns <tipo> begin end;
  • 17. Exemplo • Retornando a média: create function calcMedia (codNota int) returns numeric(4,1) begin declare n1 numeric(4,1); declare n2 numeric(4,1); select nNota1, nNota2 from tbNotas where iNota_id = codNota into n1, n2; return (n1 + n2) / 2; end; • Executando: select calcMedia (101);
  • 18. Visões • É um select pré-moldado, serve para personalizar comandos de consulta e deixá- los preparados para a execução; • Sintaxe do comando: create view <identificador> [<lista de colunas>] as <comando select>; • Exemplo: create view listaAlunos (cod, nome) as select id, vNome from tbAluno; • Visualizando: select * from listaAlunos ;
  • 19. Exercícios • Abstraia e crie um banco qualquer (com 3 tabelas que se relacionem) – Crie índices que possam agilizar possíveis consultas futuras; – Crie gatilhos que possam ser úteis ao banco de dados; – Crie procedimentos que padronize as operações CRUD em TODAS as tabelas; – Crie funções que possam agilizar possíveis cálculos; – Crie visões que forneçam suporte para a construção de relatórios (a serem imaginados por você!)
  • 20. Parte II - DML
  • 21. Aprofundando • Sabemos que existem as operações CRUD que servem como base para a DML; • O aprofundamento se dá, principalmente, no comando de RETRIVE; – SELECT; • As outras operações, praticamente, não existem variações dentre o que já foi exposto;
  • 22. Relembrando... • Formatação básica: select A1, A2, A3, ..., An from r1, r2, ..., rm where P onde Ai representa um atributo ri é uma (relação) tabela P é um predicado (condição/critério de seleção).
  • 23. A cláusula BETWEEN • Especifica intervalos (inclusive); • Ex: SELECT numero_emprestimo FROM emprestimo WHERE total BETWEEN 90 AND 100; • Similarmente, temos o NOT BETWEEN;
  • 24. A cláusula From • Define um produto cartesiano das relações; – A junção natural é definida em termos de produto cartesiano; • Exemplo: – Queremos a seguinte consulta: “para todos os clientes que tenham um empréstimo em um banco, encontre seus nomes e números de empréstimos”; – Em SQL: SELECT distinct nome_cliente, devedor.numero_emprestimo FROM devedor, emprestimo WHERE devedor.numero_emprestimo = emprestimo.numero_emprestimo – Observe as especificações de <tabela>.<coluna> , pois isso servirá para evitar AMBIGUIDADES;
  • 25. A cláusula As • Como as tabelas podem possuir nomes relativamente grandes, então podemos “apelidá-las” usando a cláusula As; • O mesmo comando anterior ficaria: SELECT distinct nome_cliente, d.numero_emprestimo FROM devedor as d, emprestimo as e WHERE d.numero_emprestimo = e.numero_emprestimo • Pode ser aplicado no nome da tabela ou no nome do campo; – No caso do nome do campo será “apelidada” a coluna do resultado da consulta;
  • 26. Cláusula Like • É aplicado em operações de Strings; • Serve para checar coincidências de pares; • Elementos: % => Compara qualquer substring; _ => Compara qualquer caractere; • Lembre-se que são sensíveis ao caso; • Exemplo: SELECT nome_cliente FROM cliente WHERE rua_cliente LIKE ‘%Avenida%’; • Outros exemplos: – 'Francisco%' => Qualquer valor que inicie com Francisco; – '_ _ _' => Qualquer string que tenha 3 caracteres; • Também podemos usar o NOT LIKE;
  • 27. Cláusula Order By • Serve para ordenar as tuplas resultantes de uma consulta; • Temos duas opções: – Asc => Ascendente; – Desc => Descendente; • Pode ordenar qualquer tipo de atributo; • Exemplo: SELECT * FROM emprestimo ORDER BY total DESC, numero_emprestimo ASC;
  • 28. A Cláusula Join • Compõem mecanismos de junção de DUAS relações, são eles: – Junção condicional; – Junção natural; – Junção externa; – Outras... • Tipos de junção: – Define como as tuplas de cada relação irão se corresponder e quais atributos serão incluídos no resultado final; – São eles: • Inner Join (junção interna); • Outer Join (junção externa); – São opcionais estas especificações; – Tipos de condições: • Natural; • On; • Using;
  • 29. Junção Interna • É quando tuplas são incluídas no resultado de uma consulta somente se existir uma correspondente na outra relação; • Exemplo: SELECT tabela1.atributo1, tabela2.atributo2 FROM tabela1 INNER JOIN tabela2 ON tabela1.atributo1 = tabela2.atributo2; • Se o nome dos atributos for igual, não há necessidade da cláusula ON, basta usar o NATURAL; SELECT tabela1.atributo1, tabela2.atributo2 FROM tabela1 NATURAL JOIN tabela2; – Neste caso, a cláusula INNER também não precisa;
  • 30. Junção Externa • É quando tuplas são incluídas no resultado sem que exista uma tupla correspondente na outra relação; • Tipos: – RIGHT OUTER JOIN = Junção Externa a Direita; – LEFT OUTER JOIN = Junção Externa a Esquerda; – FULL OUTER JOIN = Junção Externa Total (Junção Externa a Esquerda + Junção Externa a Direita); • Exemplo: SELECT R1.A1, R2.A2 FROM R1 RIGHT OUTER JOIN R2 ON R1.A1= R2.A2; ou SELECT R1.A1, R2.A2 FROM R1 LEFT OUTER JOIN R2 ON R1.A1= R2.A2;
  • 31. Operações de Conjunto • Possuímos as operações de União, Interseção e Exceção (exceto ou subtração); • Exemplo de união: (SELECT nome_cliente FROM depositante) UNION (SELECT nome_cliente FROM devedor); • Esta operação, automaticamente, eliminina as repetições; – Se isso não for desejado então usa-se UNION ALL; – E isso é válido para TODAS as operações de conjunto;
  • 32. Operações de conjunto • De forma análoga à união, temos: – Interseção : intersect; – Exceção: except;
  • 33. Funções agregadas • São funções simples que parte de um conjunto de dados (valores) e retorna uma valor simples (único); • Exemplos: – Média (average): avg; – Mínimo (minimum): min; – Máximo (maximum): max; – Total (total): sum; – Contagem (count): count; – Desvio Padrão: stddev; • Exemplo: SELECT avg(saldo) FROM conta WHERE nome_agencia = ‘Parnaíba-Central’;
  • 34. A cláusula Group By • Existem circunstância em que gostaríamos de aplicar uma função agregada não somente a um conjunto de tuplas, mas também a um grupo de conjunto de tuplas; • Isso é possível usando a cláusula SQL group by; • Exemplo: 'Encontrar a média dos saldos nas contas de cada uma das agências do banco': SELECT nome_agencia, avg(saldo) FROM conta GROUP BY nome_agencia;
  • 35. Leia sobre ... • Existem ainda muitas outras coisas que devem ser pesquisadas e estudadas, tais como: – Subconsultas (Cláusulas IN, SOME, ALL, EXISTS, UNIQUE); – Subconsultas escalares;