63
Bando de Dados Relacional RDBMS (Relational Database Management System) 06/10/2022 1 Treinar Informática : Oracle

SQL Oracle

Embed Size (px)

DESCRIPTION

SQL Oracle

Citation preview

Page 1: SQL Oracle

04/12/2023 1

Bando de Dados

RelacionalRDBMS (Relational Database

Management System)

Treinar Informática : Oracle

Page 2: SQL Oracle

04/12/2023 2

O que é?

Conceito abstrato que define maneiras de armazenar, manipular e recuperar dados

estruturados unicamente na forma de tabelas, construindo um bando de dados.

Treinar Informática : Oracle

Page 3: SQL Oracle

04/12/2023 3

Visão Padrão

Treinar Informática : Oracle

Page 4: SQL Oracle

04/12/2023 4

Integridade Referencial

Treinar Informática : Oracle

Page 5: SQL Oracle

04/12/2023 5

Constraints

Definições de regras.

Manter a integridade dos

dados.

Not Null, Unique, Primary Key, Foreign Key

e Check.

Treinar Informática : Oracle

Page 6: SQL Oracle

04/12/2023 6

SQL• Linguagem de pesquisa declarativa para banco

de dados relacional.• Características inspiradas na álgebra relacional.• Padrão de banco de dados.• Simplicidade e facilidade de uso.• Uma consulta SQL especifica a forma do

resultado e não o caminho para chegar a ele.• Pode ser migrado de plataforma para plataforma

sem mudanças estruturais principais.

Treinar Informática : Oracle

Page 7: SQL Oracle

04/12/2023 7

SQL• Consulta a dados• Inserção, atualização e deleção das linhas de uma

tabela• Criação, substituição, alteração e eliminação de

objetos• Controle de acesso ao banco de dados e seus

objetos• Garantia de consistência e integridade do banco

de dados.

Treinar Informática : Oracle

Page 8: SQL Oracle

04/12/2023 8

Palavras Chaves SQL• DML – Linguagem de Manipulação de Dados• DDL – Linguagem de Definição de Dados• DCL – Linguagem de Controle de Dados• DTL – Linguagem de Transação de Dados• DQL – Linguagem de Consulta de Dados

Treinar Informática : Oracle

Page 9: SQL Oracle

04/12/2023 9

DDL• Definição de novas tabelas e elementos

associados.• Os comandos básicos são:• CREATE• DROP

Treinar Informática : Oracle

Page 10: SQL Oracle

04/12/2023Footer Text 10

Tipos de Colunas• Quando se cria uma tabela é necessário especificar

o tipo de dados das colunas e a sua dimensão. Esta determina o comprimento máximo que podem ter os valores nela armazenados.

• VARCHAR2 (n)• Armazena uma cadeia de caracteres de

comprimento variável. O valor máximo de n são 4000 bytes. O Oracle só guarda o que o utilizador introduzir não preenchendo o que falta para o limite n. Se usarmos BYTE n traduz o número de bytes;

Page 11: SQL Oracle

04/12/2023Footer Text 11

Tipos de Colunas• CHAR(n)• Caracteres de comprimento fixo n. O valor por

omissão é 1 sendo o valor máximo 2000. A base de dados ocupa sempre n caracteres pois preenche o que falta com espaços em branco;

• NUMBER(n,d)• Número com n digitos sendo d casas décimais (à

direita da virgula);

Page 12: SQL Oracle

04/12/2023Footer Text 12

Tipos de Colunas• DATE• Data desde 1 Jan de 4712 AC até 31 de Dez de

9999 DC. Também é armazenada a hora, minuto e segundo. O calendário interno da base de dados tem em conta as alterações de calendário impostas ao longo do tempo, por exemplo a passagem do calendário Juliano para Gregoriano em 1582-10-05, onde foram eliminados 10 dias;

Page 13: SQL Oracle

04/12/2023Footer Text 13

Tipos de Colunas• VARCHAR2 surgiu na versão 7 para substituir

VARCHAR• Se criar uma coluna com o tipo VARCHAR o SGBD

converte em VARCHAR2• Quando insere valores do tipo VARCHAR2 o ORACLE

não preenche espaços em branco no fim. Isto significa que JOSE e JOSE§§§ serão strings diferentes. Se os valores fossem do tipo CHAR o ORACLE acrescentava espaços no fim automaticamente;

• Quando um valor a introduzir numa coluna excede a sua dimensão a base de dados emite uma mensagem de erro;

Page 14: SQL Oracle

04/12/2023 14

DML• Realizado para realizar inclusões, consultas,

alterações e exclusões de dados presentes em registros.

• Pode ser executado em diversos registros de diversas tabelas ao mesmo tempo.

• Os comandos são:• INSERT• SELECT• UPDATE• DELETE

Treinar Informática : Oracle

Page 15: SQL Oracle

04/12/2023 15

DCL• Controla os aspectos de autorização de dados e

licenças de usuários para controlar quem tem acesso para ver ou manipular dados dentro do banco de dados.

• Os comandos são :• GRANT• REVOKE

Treinar Informática : Oracle

Page 16: SQL Oracle

04/12/2023 16

DTL• Próprio nome ja diz: controle de transação de

dados.• Os comandos são:• BEGIN WORK (ou START TRANSACTION,

dependendo do dialeto SQL)• COMMIT• ROLLBACK

Treinar Informática : Oracle

Page 17: SQL Oracle

04/12/2023 17

DQL• É a mais utilizada• Possui apenas um comando : SELECT• Esse comando possui várias cláusulas:• FROM• WHERE• GROUP BY• HAVING• ORDER BY• DISTINCT

Treinar Informática : Oracle

Page 18: SQL Oracle

04/12/2023 18

DQL• Operadores lógicos:• AND• OR• NOT

• Operadores Relacionais:• <• >• <=• >=

Treinar Informática : Oracle

Page 19: SQL Oracle

04/12/2023 19

DQL• Operadores Relacionais:• =• <>• BETWEEN• LIKE• IN

• Funções de Agregação:• AVG• COUNT

Treinar Informática : Oracle

Page 20: SQL Oracle

04/12/2023 20

DQL• Funções de Agregação:• SUM• MAX• MIN

Treinar Informática : Oracle

Page 21: SQL Oracle

04/12/2023 21

DBA (Administrador de Banco de Dados)

• Avaliar o hardware do servidor de banco de dados• Instalar o Oracle• Planejar, criar e abrir banco de dados• Backup• Controlar usuários• Design• Recuperação após falhas• Monitorar

Treinar Informática : Oracle

Page 22: SQL Oracle

04/12/2023 22

Arquitetura do Banco de Dados

• Arquivos de Controle• Arquivos de dados• Arquivos de redo log on-line• Arquivo de parâmetros• Arquivo de senha• Arquivo de log arquivados

Treinar Informática : Oracle

Page 23: SQL Oracle

04/12/2023Footer Text 23

Banco de dados objeto-

relacional (BDOR)• A maior parte dos sistemas gerenciadores de

bancos de dados (SGBDs) utilizados é baseada no modelo relacional. No entanto, SGBDs baseados em outros modelos têm surgido devido à demanda de novas aplicações. Muitas aplicações, impulsionadas pela WEB, requererem técnicas de acesso que melhorem o desempenho e estruturas de dados muito mais complexas que as tabelas relacionais, com diversos tipos adicionais, como imagem e vídeo, fundamentados na tecnologia de orientação a objetos.

Page 24: SQL Oracle

04/12/2023 24

Banco de dados objeto-

relacional (BDOR) • Sistema de gerenciamento de banco de dados

semelhante a um banco de dados relacional, porém com um modelo de banco de dados orientado a objetos: objetos, classes e herança são suportados diretamente nos esquemas de banco de dados e na linguagem de consulta.

• Teve sua origem na combinação de idéias dos modelos de dados tradicionais e de linguagens de programação orientada a objetos.

Treinar Informática : Oracle

Page 25: SQL Oracle

04/12/2023Footer Text 25

Banco de dados objeto-

relacional (BDOR) • A principal estrutura do modelo objeto-relacional

são as tabelas, entretanto, com muito mais recursos do que as tabelas puramente relacionais. Entre esses recursos podemos destacar:

• definição de tipos pelo usuário;• registros e vetores;• métodos e funções; • referências, herança e polimorfismo.• São acrescentadas também estruturas a

linguagens de consulta relacionais, como SQL, para tratar os tipos de dados acrescentados.

Page 26: SQL Oracle

04/12/2023Footer Text 26

Banco de dados objeto-

relacional (BDOR) • O objetivo é preservar os fundamentos

relacionais, em particular o acesso declaratório ao dado, acrescentando novas funcionalidades.

• Para utilizar os recursos de orientação a objetos a Oracle, a partir da versão 8, implementou diversos conceitos que definem um modelo objeto-relacional tais como: tipo objeto, tabela de objetos, etc.

Page 27: SQL Oracle

04/12/2023 27

Banco de dados objeto-

relacional (BDOR)• Em que são utilizados:• Objetos complexos – são formados por

construtores (conjunto, listas, tuplas, registros, coleções e arrays) aplicados a objetos simples (inteiros, booleans, strings)

• Também podemos utilizar estes tipos de dados estruturados onde a consulta ao banco de dados precisa ser mais complexa, pois ao invés de acesso a tabela e registros, é necessário o acesso a listas, tuplas, arrays entre outros.

Treinar Informática : Oracle

Page 28: SQL Oracle

04/12/2023 28

Banco de dados objeto-

relacional (BDOR)• Exemplo de aplicações complexas:• Projetos de engenharia e arquitetura• Experiências científicas• Telecomunicações• Sistemas de informações geográficas• Multimídia

Treinar Informática : Oracle

Page 29: SQL Oracle

04/12/2023 29

Banco de dados objeto-

relacional (BDOR)• Características das aplicações complexas:• Transações de duração mais longa• Novos tipos de dados para armazenar imagens ou

grandes itens de texto• Necessidade de definir operações específicas de

de aplicações não-padronizadas.

Treinar Informática : Oracle

Page 30: SQL Oracle

04/12/2023 30

Banco de dados objeto-

relacional (BDOR)• Características:• Cada objeto possui um identificador de objeto ou

OID (object identifer), que o torna único, não usa a linguagem SQL, por isso não há querys, você busca seus objetos através de metodologias predefinidas. Essas metodologias são chamadas de Native Query´s.

• Na diferenciação do modelo relacional e orientado a objeto, ficaria da seguinte maneira:

Treinar Informática : Oracle

Page 31: SQL Oracle

04/12/2023 31

Banco de dados objeto-

relacional (BDOR)

Treinar Informática : Oracle

Page 32: SQL Oracle

04/12/2023 32

Banco de dados objeto-

relacional (BDOR)• A forma de acesso aos dados é remodelada

porque os SGDBs orientados a objetos sugerem novos tipos de dados como sequência de bits, ponteiros, linhas, números complexos e elementos de dados do tipo array.

• Para acessar uma array, um modo especial de consulta teria que ser construído por exemplo:

Treinar Informática : Oracle

Page 33: SQL Oracle

04/12/2023 33

Banco de dados objeto-

relacional (BDOR)• A consulta realizada retorna o nome dos carros

vendidos no mês de fevereiro, representado pela sentença (2) e com quantidade acima de 200 unidades; o mês de fevereiro faz parte de uma array de 12 elementos, representando os meses.

Treinar Informática : Oracle

Page 34: SQL Oracle

04/12/2023 34

Banco de dados objeto-

relacional (BDOR)• Oracle 11g – Um SGBD Objeto Relacional:• Mistura banco de dados relacional com conceitos

de orientação a objetos.• Usa SQL no sistema Objeto Relacional.• Existe no Oracle o Object Type que um tipo

definido pelo usuário na qual equivale ao de classe em POO (Programação Orientada a Objetos) .

• O Object Type captura tanto a estrutura como o comportamento de um objeto. A sintaxe segue o exemplo:

Treinar Informática : Oracle

Page 35: SQL Oracle

04/12/2023 35

Banco de dados objeto-

relacional (BDOR)

Treinar Informática : Oracle

Page 36: SQL Oracle

04/12/2023 36

Banco de dados objeto-

relacional (BDOR)• Vantagens:• Capacidade de Armazenamento de Objetos• Poder de processamento de requisições• Não possuem chaves primárias nem estrangeiras,

aumentando o desempenho das consultas e processos

• Os objetos se comunicam entre si através de mensagens

Treinar Informática : Oracle

Page 37: SQL Oracle

04/12/2023 37

Banco de dados objeto-

relacional (BDOR)• Desvantagens:• Falta de padronização das linguagens de

manipulação dos dados• Alto custo de aquisição das novas tecnologias• Curva de aprendizagem e adaptação ao novo

ambiente demorada.

Treinar Informática : Oracle

Page 38: SQL Oracle

04/12/2023 38

Diferentes Meios de Armazenar Dados

• Os dispositivos de armazenamento foram um meio tecnológico inventado para trazer comodidade e mobilidade para as pessoas.

• Os dispositivos podem reter informações, processar informações ou ambos.

• Como exemplos de dispositivos de armazenamento por meio ópticos temos os CDs, DVDs e Blu-Ray.

• Como exemplos de dispositivos de armazenamento por meio eletrônico temos os pen drives e cartões de memória.

Treinar Informática : Oracle

Page 39: SQL Oracle

04/12/2023 39

Modelo de Dados• É um subconjunto do modelo de implementação

que descreve a representação lógica e física dos dados persistentes no sistema.

• Abrange qualquer comportamento definido no banco de dados, como procedimentos armazenados, triggers, restrições etc.

• O designer de banco de dados é responsável pela integridade do modelo de dados, garantindo que ele esteja correto, consistente e compreensível como um todo.

Treinar Informática : Oracle

Page 40: SQL Oracle

04/12/2023 40

Modelo de Dados• Precisa ser adaptado à semântica do banco de

dados, que pode variar um pouco entre RDBMSes. Os sistemas de Bancos de Dados de Objetos possuem semânticas bem diferentes.

Treinar Informática : Oracle

Page 41: SQL Oracle

04/12/2023 41

Joins• Significa Unir ou União• Utilizamos quando precisamos buscar dados em

outras tabelas desde que satisfaça as condiçõespara isso.

• Funciona em qualquer banco de dados relacional.• Existem 3 tipos de Joins:• LEFT• RIGHT• INNER

Treinar Informática : Oracle

Page 42: SQL Oracle

04/12/2023 42

Joins• LEFT JOIN – o banco de dados irá analisar a

estrutura da tabela da direita e comparar com a tabela da esquerda .

Treinar Informática : Oracle

Page 43: SQL Oracle

04/12/2023 43

Joins• Na primeira situação temos o Left Join, a seta

está apontando para a tabela FUNCIONARIOS simbolizando que a precedência é da tabela da esquerda.

• RIGHT JOIN – Também conhecido apenas por JOIN, dependência da tabela da Direita.

• INNER JOIN – Oberva se existem registros combinados em ambas as tabelas, ou seja, para que um registro seja incluído na lista de resultados, o campo de ligação entre as duas tabelas deve satisfazer as condições de ambas.

Treinar Informática : Oracle

Page 44: SQL Oracle

04/12/2023 44

Functions• A function é usada para fazer um

processamento e retornar o resultado deste processamento, ou seja, uma function obrigatoriamente precisa retornar um valor.

• Uma function pode ser de única linha ou de várias linhas. É preciso criar um tipo para que nossa function saiba o que retornar, ja que dentro de um tipo temos um ou vários atributos. Esses atributos são os valores que vamos retornar na nossa function.

Treinar Informática : Oracle

Page 45: SQL Oracle

04/12/2023 45

Functions• Exemplo function com única linha :• CREATE OR REPLACE FUNCTION DIA_SEMANA

(DATA DATE) RETURN DIA AS VARCHAR2(20); • BEGIN • SELECT DIA_SEM • INTO RESULT• FROM TABELA_DIAS_ANO• WHERE DATA_ANO = DATA• RETURN; • END;

Treinar Informática : Oracle

Page 46: SQL Oracle

04/12/2023 46

Functions• Exemplo function vários dados.

• (Script exemplo aula)

Treinar Informática : Oracle

Page 47: SQL Oracle

04/12/2023 47

Funções de Caracter• Sua forma de utilização é bem simples: só precisa

saber o nome da função que precisa trabalhar e os argumentos que utilizará.

• Falaremos de algumas funções de caracter conhecidas e para isso usaremos a tabela de teste DUAL do ORACLE. A tabela DUAL permite ser utilzada por vários usuários do banco. ela contém apenas uma linha e uma coluna. Este objeto é muito utilizado para fazer testes.

Treinar Informática : Oracle

Page 48: SQL Oracle

04/12/2023 48

Funções de Caracter• Função LOWER• Retorna a informação do campo com caixa

baixa(letras minúsculas).• Função UPPER• Retorna a informação do campo com caixa

baixa(letras minúsculas).• Função INITCAP• Retorna o primeiro caractere em maiúsculo.• Função CONCAT• Concatena campos

Treinar Informática : Oracle

Page 49: SQL Oracle

04/12/2023 49

Funções de Caracter• Função SUBSTR

Conta a partir do X caractere y caracteres

Substr(campo,X,Y)X à posiçãoY à quantidade que vai contar

• Função LPAD

Joga o conteúdo disponível do campo para esquerdaSELECT LPAD(SAL,10,’*’) FROM EMP2;LPAD(Campo,número , ‘caractere’);

Treinar Informática : Oracle

Page 50: SQL Oracle

04/12/2023 50

Funções de Caracter• Função RPAD• Joga o conteúdo disponível do campo para direita• Função TRIM• Tira os espaços do campo• Função REPLACE • Analisa a string de caracteres mencionada na

função e substitui por outra string que foi especificada;

• SELECT REPLACE(‘JACK AND JUE’,’J’,’BL’) FROM DUAL;

Treinar Informática : Oracle

Page 51: SQL Oracle

04/12/2023 51

Funções de Data• Com o Oracle é possível obter uma série de

resultados a partir de uma ou mais datas, como por exemplo verificar o último dia do mês ou verificar a quantidade de meses entre duas datas, etc.

• Data atual do sistema: SYSDATE• Remover meses de uma data:

ADD_MONTHS(SYSDATE, -1)• Adicionar meses de uma data:

ADD_MONTHS(SYSDATE, +1)• Buscar o último dia do

mês: LAST_DAY(SYSDATE)• Primeiro dia do mês: TRUNC(SYSDATE,

‘MONTH’)Treinar Informática : Oracle

Page 52: SQL Oracle

04/12/2023 52

Funções de Data• Quantidade de meses entre duas datas: 

MONTHS_BETWEEN(SYSDATE, ‘27/07/1982’)• Primeiro dia do ano: TRUNC(SYSDATE, ‘YEAR’)• Dias da semana: • DECODE( TO_NUMBER( TO_CHAR (SYSDATE, ‘D’) )• ,1, ‘domingo’• ,2, ‘segunda-feira’• ,3, ‘terça-feira’• ,4, ‘quarta-feira’• ,5, ‘quinta-feira’• ,6, ‘sexta-feira’• ,7,’sábado’• )

Treinar Informática : Oracle

Page 54: SQL Oracle

04/12/2023 54

Outras Funções• INSTR

Pesquisa a posição de um caracter dentro de uma string. A função retorna um inteiro indicando a posição do primeiro caractere localizado na string.

• SELECT INSTR(‘THIS IS A TEST’, ‘IS’) FROM DUAL;• TRANSLATE

Substitui uma seqüência de caracteres em uma string com um outro conjunto de caracteres. No entanto, é importante destacar que ele substitui um único caractere de cada vez.

• SELECT TRANSLATE(‘Mississippi’, ‘s’, ‘S’) FROM dual;Treinar Informática : Oracle

Page 55: SQL Oracle

04/12/2023 55

Outras Funções• MEDIAN

A ideia da mediana é separar a distribuição em duas partes iguais. Para calcular essa medida, inicialmente os dados devem ser ordenados. A mediana corresponderá ao ponto do meio da distribuição. Em nosso exemplo, se ordenarmos as punições de forma ascendente, teremos o seguinte conjunto de valores: 1, 2, 3, 3, 4, 4, 4. 

• Nesse caso, o valor 3 é a mediana, pois é ele quem separa a distribuição em duas partes (1, 2, 3 e 4 , 4, 4). No SGBD Oracle, a função MEDIAN (disponível a partir do Oracle 10) é responsável por realizar o cálculo da mediana.

Treinar Informática : Oracle

Page 56: SQL Oracle

04/12/2023Footer Text 56

Outras Funções• MODA• A moda é a medida de tendência central que consiste

no valor mais frequente em uma distribuição. • Para obter a moda da variável PUNICAO no SQL do

Oracle, basta utilizar a função STATS_MODE.

• O resultado é 4, pois, a maioria das punições (3 casos) foi de 4 jogos para os jogadores expulsos.

Page 57: SQL Oracle

04/12/2023 57

Outras Funções• GREATEST

Função que irá retornar o maior valor de uma lista. Não confundir com MAX.

• greatest(2, 5, 12, 3) -> retorna 12greatest(’2′, ’5′, ’12′, ’3′) -> retorna ’5′greatest(‘apples’, ‘oranges’, ‘bananas’) -> retorna ‘oranges’greatest(‘apples’, ‘applis’, ‘applas’) -> retorna ‘applis’

Treinar Informática : Oracle

Page 58: SQL Oracle

04/12/2023 58

Outras Funções• LEAST

Esta função é utilizada para retornar o menor valor de uma lista. Não confundir que MIN.

• least(2, 5, 12, 3) -> retorna 2least(‘apples’, ‘oranges’, ‘bananas’) -> retorna ‘apples’least(‘apples’, ‘applis’, ‘applas’) -> retorna ‘applas’least(‘apples’, ‘applis’, ‘applas’, null) -> retorna NULL

Treinar Informática : Oracle

Page 59: SQL Oracle

04/12/2023 59

Union (all), Intersect e Minus

• Essas quatro funcionalidade ( UNION ALL é um caso a parte ) são primordiais para obter fragmentos de resultados. Ao invés de sobrecarregar sua query com diversas condições e controles tente utilizar essas funcionalidades.

• UNION• Agrega o resultado da query subsequente a query

anterior, EX:• SELECT TABLE_NAME FROM USER_TABLES• UNION • SELECT TABLE_NAME FROM ALL_TABLES• Ela retornará todas as tabelas do usuario e todas as

tabelas do sistema, porém não repetirá.

Treinar Informática : Oracle

Page 60: SQL Oracle

04/12/2023 60

Union (all), Intersect e Minus

• Se voce quiser trazer tudo, mesmo que repita, use:

• SELECT TABLE_NAME FROM USER_TABLES • UNION ALL • SELECT TABLE_NAME FROM ALL_TABLES• A query acima trará as tabelas de USER_TABLES e

também de ALL_TABLES repetindo caso seja necessário.

Treinar Informática : Oracle

Page 61: SQL Oracle

04/12/2023 61

Union (all), Intersect e Minus

• INTERSECT• Traz a diferença entre dois conjuntos de dados. Digamos

que voce queira os registros que existam em uma query e NECESSARIAMENTE não existam na outra. ( e vice-versa )

• SELECT TABLE_NAME FROM USER_TABLES • INTERSECT • SELECT TABLE_NAME FROM ALL_TABLES• Isso trará todas as tabelas do usuário que não estejam

em ALL_TABLES ( que no caso é 0 porque todas as tabelas estão lá ) e também trará todas as tabelas que tenham na ALL_TABLES e não na USER_TABLES.

• Podemos resumir o INTERSECT como: Traz todos os registros que se repetem nas sentenças.

Treinar Informática : Oracle

Page 62: SQL Oracle

04/12/2023 62

Union (all), Intersect e Minus

• MINUS• Traz todos os registros da primeira

consulta MENOS ( MINUS ) os registros da segunda consulta. (ele também ignora os repetidos)

• SELECT TABLE_NAME FROM USER_TABLES • MINUS • SELECT TABLE_NAME FROM ALL_TABLES• O resultado será vazio. Pois todos os registros da

primeira query estão na segunda.

Treinar Informática : Oracle

Page 63: SQL Oracle

04/12/2023Footer Text 63

Truncate• O comando TRUNCATE é como se fosse um

DELETE (sem cláusula Where) com COMMIT implícito, ou seja, ele elimina todas as linhas da tabela, deixando apenas sua estrutura e, NOTA IMPORTANTE, sem opção de ROLLBACK.

• -- Executa um TRUNCATE (Delete+Commit implícito) na tabela

• truncate table teste;