SQL

Introdução
ao
Oracle 8i
Sistema Gerenciador de Banco de Dados
•
•
Objetivos deste curso :
–
Descrever os conceitos de Bancos de Dados Relacionais tendo como
enfoque o ORACLE.
O que é uma Base de Dados (Database)?
–
–
Uma base de dados é uma coleção de informações organizadas.
Em um sistema manual tradicional os dados são geralmente guardados
em fichas e estocados em arquivos. Para selecionar dados, um acesso
manual nestes arquivos é necessário.
Um sistema baseado em computador, os dados são armazenados tipicamente em
meios magnéticos e são acessados através de programas.
Sistema Gerenciador de Banco de Dados
•
Base de dados informatizada:
–
Em uma base de dados informatizada existem muitas vantagens:
•
•
•
•
•
•
•
Alteração e recuperação dos dados é mais rápida;
A informação ocupa menos espaço para ser armazenada;
Muitos usuários podem compartilhar as mesmas informações ao
mesmo tempo; a redundância de dados é minimizada;
Inconsistências podem ser evitadas;
Padrões podem ser estabelecidos;
Pode-se implementar níveis de segurança nestes dados;
etc.
Sistema Gerenciador de Banco de Dados
•
O Database Management System (DBMS):
–
–
–
Para controlar o acesso e o armazenamento das informações é
necessário um DBMS. O DBMS ( Sistema Gerenciador de Banco de
Dados ) é o programa que fica responsável pelo controle de acesso dos
usuários à base de dados. Ele controla o armazenamento dos dados, a
recuperação e a alteração nestes dados.
O DBMS age na interface entre a base de dados e os usuários da base
de dados.
Dentre os tipos de bancos de dados existentes, os mais recentes são
os bancos de dados relacionais, destacando-se entre eles o ORACLE.
Sistema Gerenciador de Banco de Dados
•
Conceitos Relacionais:
–
–
Uma base de dados relacional é conhecida pelos usuários como uma
coleção de tabelas de duas dimensões. Existem quatro conceitos
básicos:
•
•
•
•
tabelas
colunas
linhas
campos
O modelo relacional tem como base o ramo da matemática conhecido
como álgebra relacional. Este modelo envolve:
•
•
uma coleção de objetos conhecidos como relações,
um conjunto de operadores que agem nestas relações
produzindo novas relações.
Visão Geral de Algumas Ferramentas ORACLE
•
•
ORACLE Server
É o servidor do banco de dados, que gerencia o armazenamento e
recuperação dos dados. Os demais produtos funcionam tendo o servidor
como base.
SQL
É a linguagem padrão dos bancos de dados relacionais, entre eles o
ORACLE.
•
PL/SQL
Extensões procedurais do ORACLE ao SQL.
•
•
Developer/2000
Conjunto de ferramentas de desenvolvimento visual que permitem a
criação de aplicativos de banco de dados, destacando-se o Forms
Builder, para criação de formulários e Report Builder, para criação de
relatórios.
SQL*Plus
É um ambiente através do qual os comandos SQL podem ser entrados,
executados e retornados na tela ou através de relatórios. Será nesta ferramenta
que constituiremos toda estrutura do nosso projeto de banco de dados relacional.
Usando a linguagem SQL, criaremos o usuário que irá possuir as tabelas do
nosso projeto.
Introdução a Linguagem SQL
•
Características SQL:
–
–
–
–
•
•
Para acesso a um banco de dados relacional, é necessário uma
linguagem. A Structured Query Language é a linguagem usada pela
maioria dos bancos de dados relacionais.
SQL é uma linguagem baseada no inglês, e usa palavras chaves como:
select, insert, delete, update como parte de seu conjunto de comandos;
SQL processa um conjunto de linhas por vez, ao invés de uma linha.
SQL oferece uma série de comandos para uma variedade de tarefas
diferentes, incluindo:
•
•
•
•
seleção de dados;
inserção, alteração, e deleção de linhas em uma tabela;
criar, deletar e alterar objetos do banco de dados;
etc.
SQL*Plus:
–
O SQL*Plus é um ambiente através do qual os comandos SQL podem
ser entrados, executados e retornados na tela ou através de relatórios.
Ele contém uma série de comandos que ajudam na edição, salvamento
e execução de comandos SQL ou arquivos de comandos SQL
chamados scripts.
Principais comandos SQL:
Comando
Descrição
select
seleciona dados de uma base de dados
insert
Insere linhas em uma tabela
update
altera valores de linhas na base de dados
delete
elimina linhas na tabela
create
cria objetos na base de dados
alter
altera a estrutura de um objeto da base
drop
elimina determinado objeto da base de dados
grant
dá direitos de acessos aos objetos do banco de dados
Escrevendo Comandos SQL
•
Para escrever comandos SQL você precisa:
–
Estar no SQL*Plus.
•
Entre no SQL*Plus através de um click no seu ícone:
Será aberta uma caixa de diálogo para você entrar com o nome do seu usuário (
scott ), sua password ( tiger ) e a identificação do banco de dados que será
conectado ( connect string );
A seguir será aberta a tela do SQL*Plus para você entrar com os
comandos:
•
Regras básicas para escrever os comandos SQL:
–
–
–
–
–
Os comandos podem ser escritos em mais de uma linha;
Cláusulas diferentes são colocadas usualmente em linhas diferentes;
Podem ser usadas tabulações ( endentação );
Comandos podem ser escritos em letras maiúsculas e/ou minúsculas;
Qualquer forma abaixo do comando é válida:
select * from emp;
select
*
from emp;
select *
from emp;
Visão Geral de Usuários e Segurança
Criação de usuários Oracle:
–
–
Para acesso ao banco de dados, é preciso informar um usuário e
password. Este usuário é criado pelo usuário administrador do banco
de dados conhecido como DBA ( DataBase Administrator ).
Para criar um usuário Oracle, usamos a seguinte sintaxe :
SQL> create user aluno identified by senha ;
User created.
Atribuindo Permissões e Direitos aos Usuários
•
•
Assim que um usuário é criado no banco ele não tem privilégios de acesso a
conexão no banco de dados nem de fazer nenhuma tarefa no banco de
dados.
Para isso o administrador do banco (DBA), deve atribuir ao usuário estes
acessos (Roles).
Atribuindo Permissões e Direitos aos Usuários
•
Existem 05 roles default no banco de dados:
Role
Direitos
connect
se conectar ao banco de dados.
resource
criar tabelas, sinônimos, views, etc. Acesso só aos próprios objetos.
imp_full_database
usar o comando imp para recuperar a base de dados de um backup.
exp_full_database
fazer backup da base de dados completa.
dba
administração do banco e acesso aos objetos de outros usuários.
Troca de Password de um Usuário
•
A troca de password de um usuário só pode ser feita pelo próprio usuário ou
pelo DBA.
–
–
Ex.:
Para trocar a password do usuário aluno :
SQL> alter user aluno identified by novasenha;
User altered.
Privilégios em Tabelas
•
•
Quando um usuário cria um objeto no banco de dados, só ele tem acesso
àquele objeto. O dono deste objeto pode passar determinado privilégio para
que outros usuários possam também acessá-lo.
Para isso o usuário deve usar o comando grant.
–
Sintaxe:
GRANT
ON
TO
privilégio
objeto
[ usuário | role ]
Ex.:
SQL> grant DBA to ALUNO;
Estrutura de Dados Oracle
•
•
•
Tabelas podem ser criadas a qualquer momento;
O tamanho dos dados é variável, somente os números e/ou caracteres são
realmente guardados no banco;
A estrutura da tabela pode ser modificada on-line;
Não é preciso especificar o tamanho de uma tabela.
Criação de Tabelas
•
O nome de uma tabela deve seguir as regras padrões de nomes de
objetos Oracle:
–
–
–
–
O nome deve começar com uma letra;
Ele pode conter letras, números e o caracter especial ( _ underscore ) .
Podem também ser usados os caracteres ( $ e # ) ( o que não é
recomendado ) ;
O nome de uma tabela não é sensível a letras maiúsculas / minúsculas.
Uma tabela pode ser referenciada por: aluno, ALUNO, Aluno, etc.;
Deve ter até no máximo 30 caracteres;
–
O nome da tabela tem que ser único ( incluindo outros objetos do
banco de dados );
O nome não pode ser uma palavra reservada de SQL
Tipos das Colunas
•
Quando uma tabela é criada, você precisa especificar o tipo de dados para
cada campo da tabela.
Tipos de dados em banco de dados Oracle:
Datatype
char ( tamanho )
varchar2( tamanho )
number ( p , e )
date
Long
raw
long raw
ROWID
Descrição
Tamanho máximo
campo caracter tamanho fixo
campo caracter tamanho variável
(tamanho máximo tem que ser
especificado)
Campo numérico de tamanho variável
tamanho fixo no formato data ( DD-MONYY )
campo caracter de tamanho variável
campo binário de tamanho variável (
tamanho máximo deve ser especificado )
campo binário de tamanho variável (
tamanho máximo deve ser especificado)
campo binário que indica o
endereçamento das linhas de uma tabela
255
4000
38,38
2Giga
2000
2Giga
06
Criando uma Tabela
•
Para criar uma tabela no SQL*Plus, usa-se o comando create table:
create table nome_da_tabela
( nome_de_coluna tipo( tamanho) [NULL
nome_de_coluna tipo( tamanho) [NULL
nome_de_coluna tipo( tamanho) [NULL
–
|
|
|
NOT NULL ],
NOT NULL ],
NOT NULL ], ...)
Ex.:
SQL> create table curso
(cod_periodo number(3) not null,
dat_inicial date
not null,
dat_final
date);
–
Após o comando ser executado o Oracle retorna a seguinte
mensagem:
Table created.
–
Para ver a descrição da estrutura da tabela, entre com o seguinte
comando:
SQL> desc curso
Name
------------------------------COD_PERIODO
DAT_INICIAL
DAT_FINAL
Null?
-------NOT NULL
NOT NULL
Type
---NUMBER(3)
DATE
DATE
Alterando uma Tabela
•
Através do comando alter table o usuário pode alterar a estrutura de uma
tabela, adicionar ou retirar uma constraint, etc.
–
Adicionar uma coluna:
SQL> alter table curso add (programa varchar2(40));
–
Modificar um tipo/tamanho de um campo:
SQL> alter table curso modify (programa varchar2(60));
–
Remover uma coluna ( somente versão do banco de dados 8.1.5 ou
superior :
SQL> alter table curso drop colunm programa;
Removendo / Renomeando uma Tabela
•
Para remover uma tabela da base de dados utilize o comando Drop
Table:
drop table nome_table;
•
Ao deletar uma tabela:
–
Todos os dados da tabela serão perdidos junto com os índices
associados;
–
•
–
Qualquer View, ou sinônimos existentes continuarão existindo porém
inválidos;
Somente o criador da tabela e o DBA podem eliminá-la.
Para renomear uma Tabela, View, ou Sinônimos
rename nome_antigo to nome_novo
Criação de Seqüências Automáticas
•
Seqüências (sequences), são estruturas do banco de dados que geram
números seqüenciais que podem ser usados para gerar chaves únicas ou
para gerar seqüências numéricas controladas pelo próprio banco.
–
Para gerar uma sequence no banco de dados:
SQL> create sequence SEQ increment by 1
2
start with
1
3
maxvalue
10
Sequencia criada.
–
No comando create sequence os operandos são os seguintes:
•
•
•
–
increment by n
– o valor (n) que será incrementado cada vez que a
seqüência for selecionada.
start with n
– valor inicial (n) da seqüência.
maxvalue n
– valor final (n) da seqüência.
Para a utilização dos números gerados pela seqüência é feito um select
na seqüência, especificando que o usuário deseja o próximo valor a ser
gerado:
Ex.:
SQL> select seq.nextval from dual;
NEXTVAL
--------1
–
Na próxima execução do comando select o valor será incrementado de
acordo com a definição da seqüência.
Ex.:
SQL> select seq.nextval from dual;
NEXTVAL
--------2
–
O usuário pode selecionar o valor corrente da seqüência através
usando :
SQL> select seq.currval from dual;
CURRVAL
--------2
Alterando/Removendo Sequences
•
Para alterar uma seqüência utilizamos o comando ALTER SEQUENCE :
SQL> alter sequence SEQ increment by 1
2
maxvalue
1000;
Obs: Não é possível alterar o número inicial de uma seqüência. Para alterar o
valor de START WITH, devemos remover e recriar a sequence.
•
Para eliminar uma seqüência utilize o comando drop:
SQL> drop sequence seq;
Seqüência eliminada.
Inserindo Novas Linhas em Tabela
•O comando insert é usado para adicionar linhas em uma tabela.
–Sintaxe:
insert into tabela [ ( coluna,coluna... ) ]
values ( valor,valor,...)
–Para inserir um novo periodo na tabela PERIODOS_LETIVOS:
SQL> insert into curso(cod_periodo, dat_inicial, dat_final)
values(5,’01-MAR-96’,’30-JUN-96’);
No caso acima onde serão inseridos dados em todas as colunas da tabela, a
lista de colunas não precisava ser especificada, portanto os dados devem ser
colocados na ordem em que aparecem na tabela.
–Valores caracter e de data devem ser especificados entre aspas simples. Valores
que não vão ser especificados podem ser definidos como Null.
Copiando Linhas de outra Tabela
•Você pode usar os dados existentes em uma tabela para inserir dados em outra:
insert into table [ ( coluna,coluna,...) ]
select lista-de-seleção
from tabela
–Ex.: Para copiar todas as informações dos alunos de NATAL para a tabela
ALUNOS_NATAL :
SQL>
insert
into alunos_natal
( cod_aluno, nom_aluno, sexo, nom_cidade,
ano_ingresso )
select cod_aluno, nom_aluno, sexo, nom_cidade,
ano_ingresso
from
aluno
where nom_cidade = ‘NATAL’;
–Neste caso não é utilizada a cláusula Values.
Alterando Linhas de Tabelas
•O comando update permite ao usuário alterar os valores em linhas da tabela:
update table
set
column [ ,column...] = { expressão, subquery }
[where condição]
–Ex.:
SQL> update curso
set
dat_inicial = ‘01-MAR-97’,
dat_final
= ‘30-JUN-97’
where cod_periodo = 5;
Se o where for omitido, todas as linhas na tabela serão alteradas.
Apagando Linhas da Tabela
•O comando delete é usado para eliminar linhas de uma tabela:
delete from tabela
[where condição ];
–Ex:. Para apagar todas as informações sobre o periodo letivo 5 da tabela curso.
SQL> delete from curso
where cod_periodo = 5;
Se o where não for especificado, todas as linhas da tabela serão eliminadas.
•Comando truncate : O comando truncate apaga todas as linhas de uma tabela.
Diferente do delete, o comando truncate não pode ser desfeito, já que não gera
informações de rollback. O comando truncate faz parte da DDL (Linguagem de
Definição de Dados).
Ex.:
SQL> truncate table nome_tabela;
Pesquisando registros em tabela
•Um Bloco de Pesquisa Simples:
–O comando select traz dados de uma tabela de banco de dados:
SQL> select ename, job, sal, deptno from emp;
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JOB
SAL
DEPTNO
--------- --------- --------CLERK
800
20
SALESMAN
1600
30
SALESMAN
1250
30
MANAGER
2975
20
SALESMAN
1250
30
MANAGER
2850
30
MANAGER
2450
10
ANALYST
3000
20
PRESIDENT
5000
10
SALESMAN
1500
30
CLERK
1100
20
JAMES
FORD
MILLER
CLERK
ANALYST
CLERK
950
3000
1300
30
20
10
14 linhas selecionadas.
–Note que o nome das colunas é separado por vírgulas.
–É possível também selecionar todas as colunas de uma tabela.
SQL> select * from dept;
DEPTNO
--------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
–Outros ítens que podem ser incluídos em uma cláusula select:
• Operadores de Concatenação :
Para concatenar duas colunas de uma tabela para um valor final específico,
usamos duas vezes o caracter | barra vertical :
SQL> select 'Setor ' || dname || ' escritório em ' || loc
from
dept;
'SETOR'||DNAME||'ESCRITÓRIOEM'||LOC
-----------------------------------------------Setor ACCOUNTING escritório em NEW YORK
Setor RESEARCH escritório em DALLAS
Setor SALES escritório em CHICAGO
Setor OPERATIONS escritório em BOSTON
• Ordenando colunas:
A cláusula order by é utilizada para trazer os dados em uma ordem específica.
SQL> select * from dept order by dname;
DEPTNO
--------10
40
20
30
DNAME
-------------ACCOUNTING
OPERATIONS
RESEARCH
SALES
LOC
------------NEW YORK
BOSTON
DALLAS
CHICAGO
Para inverter a ordem de pesquisa usamos um complemento para order by
chamado de DESC :
SQL> select * from dept order by dname desc;
DEPTNO
--------30
20
40
10
DNAME
-------------SALES
RESEARCH
OPERATIONS
ACCOUNTING
LOC
------------CHICAGO
DALLAS
BOSTON
NEW YORK
• A cláusula where :
A cláusula where corresponde ao operador relacional de restrição. Ela contém as
condições que as linhas da relação devem satisfazer para serem mostradas.
O where quando utilizado deve vir após a cláusula from.
SELECT
FROM
WHERE
colunas
tabela
condições que devem ser respeitadas
–Operadores lógicos utilizados com a cláusula where:
Para listar somente os registros onde o valor do campo salário for maior que
2.000,00 usamos :
SQL> select ename, job, sal from emp where sal > 2000;
ENAME
---------JONES
BLAKE
CLARK
SCOTT
JOB
SAL
--------- --------MANAGER
2975
MANAGER
2850
MANAGER
2450
ANALYST
3000
KING
FORD
PRESIDENT
ANALYST
5000
3000
Para listar os salários onde o valor é maior ou igual a 3.000,00:
SQL> select ename, job, sal from emp where sal >= 3000;
ENAME
---------SCOTT
KING
FORD
JOB
SAL
--------- --------ANALYST
3000
PRESIDENT
5000
ANALYST
3000
•Comparando o valor entre duas colunas da tabela:
–Ex: Somente os salários where o valor ultrapassou a meta de comissão :
SQL> select ename, job, sal, comm from emp where sal > comm;
ENAME
---------ALLEN
WARD
TURNER
JOB
SAL
COMM
--------- --------- --------SALESMAN
1600
300
SALESMAN
1250
500
SALESMAN
1500
0
•Operadores SQL:
–Existem quatro operadores que podem ser utilizados em qualquer tipo de dados.
Exemplos:
•Operador between :
SQL> select ename, job, sal from emp where sal between 3000
and 5000;
ENAME
---------SCOTT
KING
FORD
JOB
SAL
--------- --------ANALYST
3000
PRESIDENT
5000
ANALYST
3000
Na utilização do between o menor valor da comparação deve vir antes.
• Operador in
SQL> select ename, job, sal from emp where job in
('ANALYST','MANAGER');
ENAME
---------JONES
BLAKE
CLARK
SCOTT
FORD
JOB
SAL
--------- --------MANAGER
2975
MANAGER
2850
MANAGER
2450
ANALYST
3000
ANALYST
3000
Valores de caracteres devem vir entre aspas simples.
Pesquisando registros em tabela
Operador like
SQL> select ename, deptno from emp where ename like 'A%';
ENAME
DEPTNO
---------- --------ALLEN
30
ADAMS
20
• O símbolo % significa qualquer caracter ( ou conjunto ) de caracteres.
• Outro operador que pode ser usado junto com a clausula like é o ‘_’
(underscore) que substitui um número específico de caracteres.
SQL> select ename, deptno from emp where ename like '____';
ENAME
DEPTNO
---------- --------WARD
30
KING
10
FORD
20
A combinação dos dois operandos ( % e _ ) também pode ser usada.
• Selecionar os funcionários que contenham o substring ‘AN’ em qualquer posição
do campo JOB.
SQL> select ename, job, deptno from emp where job like
'%AN%';
ENAME
---------ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
TURNER
FORD
JOB
DEPTNO
--------- --------SALESMAN
30
SALESMAN
30
MANAGER
20
SALESMAN
30
MANAGER
30
MANAGER
10
ANALYST
20
SALESMAN
30
ANALYST
20
• Selecionando dados com múltiplas condições.
– Os operadores and e or podem ser utilizados para compor expressões lógicas.
– Exemplos:
SQL> select ename, sal from emp where sal > 2000 and deptno =
20;
ENAME
SAL
---------- --------JONES
2975
SCOTT
3000
FORD
3000
Pode-se utilizar também o operador OR.
SQL> select ename, sal from emp where sal > 2000 or deptno =
20
ENAME
SAL
---------- --------SMITH
800
JONES
2975
BLAKE
2850
CLARK
2450
SCOTT
3000
KING
5000
ADAMS
1100
FORD
3000
Usando Apelidos para as Tabelas
SQL> select A.ename, A.job, B.dname
from emp A, dept B
where A.deptno = B.deptno;
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
DNAME
-------------RESEARCH
SALES
SALES
RESEARCH
SALES
SALES
ACCOUNTING
RESEARCH
ACCOUNTING
SALES
RESEARCH
SALES
RESEARCH
ACCOUNTING
( Table Aliases )
BIBLIOGRAFIA
Oracle Banco de Dados
Marco Aurelio de Souza
ISBN : 85-7393-110-8
Editora Ciência Moderna
www.lcm.com.br
Personal Oracle 8i para Windows 98
Marco Aurelio de Souza
ISBN : 85-7393-135-3
Editora Ciência Moderna
www.lcm.com.br
Oracle 7 Server Administrator's Guide
Steven Bobroski e Eric Armstrong
ISBN : 6694-70-1292
Oracle Corporation
www.oracle.com.br
Oracle Developer 2000 Guide
Steven Bobroski e Eric Armstrong
ISBN : 6845-32-5978
Oracle Corporation
www.oracle.com.br