Aula_10

Prof. SIDNEY NICOLAU VENTURI FILHO

BANCO DE DADOS – AULA 10

Prof. SIDNEY NICOLAU VENTURI FILHO

Prof. SIDNEY NICOLAU VENTURI FILHO

AULA 10SQL – Parte 4

OBJETIVOS

• Inserir, modificar e excluir registros nas tabelas.

• Aprender sobre o conceito e as propriedades das transações em um ambiente de banco de dados.

• Aprender a definir transações em SQL.• Aprender a criar índices e visões• Conhecer os comandos que tratam da segurança

do banco de dados.• Conhecer os operadores de conjunto

Prof. SIDNEY NICOLAU VENTURI FILHO

Acessando o Oracle

Prof. SIDNEY NICOLAU VENTURI FILHO

Acessando o Oracle

FAÇA LOGINNOME USUÁRIO: AULABDSENHA : SENHA

Prof. SIDNEY NICOLAU VENTURI FILHO

Acessando o Oracle

Prof. SIDNEY NICOLAU VENTURI FILHO

Acessando o Oracle

Prof. SIDNEY NICOLAU VENTURI FILHO

Acessando o Oracle

Prof. SIDNEY NICOLAU VENTURI FILHO

MODELO DE DADOS

Prof. SIDNEY NICOLAU VENTURI FILHO

TABELAS

Prof. SIDNEY NICOLAU VENTURI FILHO

TABELAS

Prof. SIDNEY NICOLAU VENTURI FILHO

TABELAS

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

Comando Insert

O comando INSERT insere linhas em uma tabela. A forma mais simples do comando INSERT insere somente uma linha , dados os valores conforme a sintaxe abaixo:

insert into <nome_tabela> (coluna1, coluna2, ..., colunan)

values (valor1, valor2, ..., valorn);

Prof. SIDNEY NICOLAU VENTURI FILHO

DMLEMPREGADOS QUE GANHEM MAIS QUE O SALARIO MEDIO DA EMPRESA

INSERIR UM NOVO DEPARTAMENTO

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

INSERT INTO CLIENTE VALUES ( 200, ‘MEU CLIENTE’, 6)

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

SELECT * FROM CLIENTE

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

INSERT INTO CLIENTE (NOME, ID)VALUES (‘MEU CLIENTE2’, 300)

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

SELECT * FROM CLIENTE

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

Comando Delete

Para excluirmos linhas em uma tabela utilizamos o comando DELETE.

Veja a sintaxe do comando abaixo:

DELETE [FROM] [schema.]nome_tabela

WHERE condição

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

APAGAR A LINHA DO CLIENTE DE ID 300

DESMARCAR COMMIT AUTOMATICO

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

DELETE CLIENTE

COMMIT DESMARCADO

SEM FROM, NO ORACLE FROM É OPCIONAL

APAGOU 5 LINHAS????

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

SELECT * FROM CLIENTE

DELETE SEM WHERE APAGA TODAS AS LINHASE AGORA?

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

ROLLBACK

COMMIT DESMARCADO

CANCELA A TRANSAÇÃO

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

VOLTARAM AS LINHAS

SELECT * FROM CLIENTE

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

DELETE FROM CLIENTEWHERE ID = 300

COMMIT DESMARCADO

AGORA COM FROM E WHERE

SO A LINHA DE ID 300 APAGADA

COMO TORNAR A ALTERAÇÃO DEFINITIVA?

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

COMMITFECHOU A TRANSAÇÃO

IMPORTANTEDEPOIS DO COMMIT NÃO ADIANTA DAR ROLLBACK

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

LINHA DE ID 300 FOI APAGADA

SELECT * FROM CLIENTE

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

DELETE FROM CLIENTEWHERE ID = 300

COMMIT DESMARCADO

AGORA COM FROM E WHERE

Prof. SIDNEY NICOLAU VENTURI FILHO

DMLComando Update

Para alterarmos dados já existentes em nossas tabelas utilizaremos o comando UPDATE.

Veja a sintaxe do comando abaixo:UPDATE [schema. ] nome_tabela

SET coluna1 = expressão I subquery [, colunan = ... ]

WHERE condição

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

ALTERAR O VENDEDOR DO CLIENTE DE ID 200 PARA 5

SELECT * FROM CLIENTE

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

UPDATE CLIENTESET VENDEDOR = 5WHERE ID = 200

COMMIT DESMARCADO

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

ALTEROU O VENDEDOR DO CLIENTE DE ID 200 PARA 5

SELECT * FROM CLIENTE ALTERAR O ID E O NOME DO CLIENTE 200 PARA 250 E CLIENTE250

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

UPDATE CLIENTESET ID = 250,

NOME = ‘CLIENTE 250’WHERE ID = 200

ALTERANDO DUAS COLUNASREPARE A VIRGULA SEPARANDO AS COLUNAS

Prof. SIDNEY NICOLAU VENTURI FILHO

DML

SELECT * FROM CLIENTE

ALTEROU O ID E O NOME DO CLIENTE 200 PARA 250 E CLIENTE250

IMPORTANTECOMO A CAIXA ESTA DESMARCADA

NÃO ESQUEÇA DE DAR COMMIT PARA CONFIRMAR

Prof. SIDNEY NICOLAU VENTURI FILHO

INDICES

Índices: estruturas de dados auxiliares cujo único propósito é tornar mais rápido o acesso a registros baseado em certos campos, chamados campos de indexação.

Tipos:• Índice primário - baseado na chave de ordenação.

• Índice secundário - baseado em qualquer campo não ordenado de um arquivo.

Prof. SIDNEY NICOLAU VENTURI FILHO

INDICES

Criando um Índice

Vejamos a sintaxe do comando abaixo:CREATE INDEX [schema.]nome_indice ON tabela (coluna1 [, coluna2 [,...] ] )

E

Prof. SIDNEY NICOLAU VENTURI FILHO

INDICES

CREATE INDEX IND_VEND ON CLIENTE(VENDEDOR)

Prof. SIDNEY NICOLAU VENTURI FILHO

INDICES

Eliminando um Índice

Prof. SIDNEY NICOLAU VENTURI FILHO

SEGURANÇA

Comando GRANT

Em SQL, os privilégios são concedidos através do comando grant, cuja a forma geral é:

GRANT <lista_de_privilégios> ON <nome_objeto>

TO <lista_de_usuários>;

Comando REVOKE

Da mesma forma que podemos conceder privilégios de acesso a outros usuários, podemos também retirá-los através do comando REVOKE, cuja a forma geral é:

REVOKE <lista_de_privilégios> ON <nome_objeto>

FROM <lista_de_usuários>;

Prof. SIDNEY NICOLAU VENTURI FILHO

GRANT E REVOKE

ASSEGURANDO DIREITOS DE DBA PARA AULABD

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

Criando Visões

Uma View funciona de forma semelhante a uma tabela. É utilizada em comandos SELECT, INSERT, UPDATE e DELETE, para recuperação e manipulação de dados (com restrições), porém, não armazena estes dados.

Este objeto tem suas linhas e colunas calculadas dinamicamente através de um SELECT pré-estabelecido, cada vez que o solicitamos.

Apenas a sua definição é armazenada no dicionário de dados.

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

CRIAR UM VISÃO COM O ID, NOME COMPLETO E CARGO DO EMPREGADO E NOME DO DEPARTAMENTO ONDE ELE TRABALHA

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

CREATE VIEW EMP_DEP ASSELECT E.ID , E.PRIM_NOME|| ' ' ||E.ULT_NOME AS "NOME EMPREGADO" ,

E.CARGO , D.NOME AS "NOME DEPARTAMENTO"FROM EMPREGADO E INNER JOIN DEPARTAMENTO D ON E.ID_DEPTO = D.ID

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

SELECT * FROM EMP_DEP

USUARIO PENSA QUE É UMA TABELA

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

Prof. SIDNEY NICOLAU VENTURI FILHO

VISÕES

DROP VIEW EMP_DEP

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

Trabalhando com Conjuntos

• Muitas vezes necessitamos que nossas consultas incluam, em uma mesma coluna, dados de mais de uma tabela. Para isto utilizamos os operadores de conjuntos UNION, UNION ALL, INTERSECT e MINUS.

Regras para Utilização dos Operadores de Conjuntos

• Os comandos SELECT participantes têm que ter o mesmo número de colunas,

• As colunas correspondentes têm que ser do mesmo tipo de dado,

• Linhas duplicadas são automaticamente descartadas, exceto com UNION ALL,

• Os nomes das colunas resultantes são os da primeira consulta,

• ALIAS de colunas só tem efeito se utilizados na primeira consulta,

• A cláusula ORDER BY só pode ser utilizada ao final do comando,

• Os operadores de conjuntos podem ser utilizados em subconsultas

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

O Operador UNION

• Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, eliminando as linhas duplicadas resultantes.

• Se quisermos, por exemplo, id dos empregados que trabalham nos departamentos 10 ou 30 ou que atendem clientesSelect id from empregado where id_depto in (10,30)

Union

Select vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

id dos empregados que trabalham nos departamentos 10 ou 30 ou que atendem clientes

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

Select id from empregado where id_depto in (10,30)UnionSelect vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

O Operador UNION ALL

• Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, mantendo todas as linhas duplicadas.

• Se quisermos, por exemplo, id dos empregados que trabalham nos departamentos 10 ou 20 ou que atendem clientesSelect id from empregado where id_depto in (10,20)

Union all

Select vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

id dos empregados que trabalham nos departamentos 10 ou 20 ou que atendem clientes

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

Select id from empregado where id_depto in (10,20)UnionSelect vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

Select id from empregado where id_depto in (10,20)Union allSelect vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

O Operador INTERSECT

• Resulta na interseção entre todas as linhas de duas ou mais tabelas participantes do INTERSECT, ou seja, apenas as linhas comuns entre ela

• Se quisermos, por exemplo, id dos empregados que trabalham nos departamentos 10 ou 20 e que atendem clientesSelect id from empregado where id_depto in (10,20)

Intersect

Select vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

id dos empregados que trabalham nos departamentos 10 ou 20 e que atendem clientes

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

Select id from empregado where id_depto in (10,20)IntersectSelect vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

O Operador MINUS (Except)

• Resulta nas linhas existentes na primeira tabela, mas que não existem na segunda.

• As linhas comuns também não são resultantes. Se quisermos, por exemplo, id dos empregados que trabalham nos departamentos 10 ou 20 e que não atendem clientesSelect id from empregado where id_depto in (10,20)

Intersect

Select vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

id dos empregados que trabalham nos departamentos 10 ou 20 e que não atendem clientes

Prof. SIDNEY NICOLAU VENTURI FILHO

OPERADORES DE CONJUNTO

Select id from empregado where id_depto in (10,20)MINUSSelect vendedor from cliente

Prof. SIDNEY NICOLAU VENTURI FILHO

PROXIMA AULA

• Na próxima aula :

• Faremos uma revisão para a AV2