Banco de Dados
SQL
Elaini Simoni Angelotti
[email protected]
1
SQL (DDL)
– Define o esquema do BD (base, tabelas, integridade, etc)
– Criando BD com comandos SQL:
create database nome_do_banco_de_dados
create database Sistema_Bancário
– Por default o SQLServer seta os seguintes parâmetros:
•
•
•
•
Caminho: C:\Arquivos de programas\Microsoft SqlServer\MSSQL\data
Tamanho: 1 MB
Log: 1 MB
Crescimento do banco e do Log: 10% e sem limite
– O usuário pode “setar” os parâmetros usando comandos SQL:
Create database Exemplo1
ON (Name = Exemplo1_data,
Filename = ‘C:\Arquivos de programas\Microsoft SQL Server\MSSQL\data\Exemplo1.mdf’,
Size = 2 MB,
MaxSize = 10 MB,
Filegrowth = 25% )
2
LOG
ON (Name = Exemplo1_log,
Filename = ‘C:\Arquivos de programas\Microsoft SqlServer\MSSQL\data\Exemplo1.ldf’
Size = 2 MB,
MaxSize = 6 MB,
Filegrowth = 10% )
• Alterando BD com comandos SQL:
– Para expandir o BD:
Alter database Sistema_Bancário
modify file
(Name = Sistema_Bancário,
Size = 3MB)
– Para reduzir o BD:
DBCC SHRINKDATABASE
(Sistema_Bancário, 5)
• Excluindo BD com comandos SQL:
Drop database Sistema_Bancário
3
• Criando Tabelas com comandos SQL:
Create Table nomeTabela
( Atributo1 Tipo_de_Dados [opções],
...
)
– Exemplo:
USE Sistema_Bancário // é necessário se posicionar no BD antes
// de criar as tabelas
Create Table Cliente
( RG_Cli
int not null,
Nome_Cli
varchar (50),
Data_Cadastro datetime not null default (getdate()),
Cidade_Cli
varchar (12),
UF char (2)
default (‘PR’)
)
• Excluindo Tabela com comandos SQL:
DROP table nome_da_tabela
DROP table Cliente
4
• Alterando a Estrutura de uma Tabela com comandos SQL:
– Adicionando um novo atributo
Alter table Cliente
ADD Celular_Cli varchar (8) null
– Alterando o tipo de um atributo
Alter table Cliente
Alter column Cidade_Cli varchar (25)
– Excluindo um atributo de uma tabela
Alter Table Cliente
Drop Column Celular_cli
5
SQL - Constraints
• UNIQUE
– Restrição Unique é utilizada para manter os dados inseridos
com valores únicos.
Create Table Alunos
( Matricula Int,
CPF
numeric(11) Unique,
Nome
varchar(70)
)
• NOT NULL
– A restrição Not Null indica que este atributo deve ter o valor
obrigatoriamente preenchido
Create Table Alunos
( Matricula Int,
CPF
numeric(11) Unique,
Nome
varchar(70) Not Null
)
6
• DEFAULT
– A restrição default indicar qual valor será atribuído ao registro
quando não vier valor para este atributo no Insert.
Create Table Alunos
( Matricula Int,
CPF
numeric(11) Unique,
Nome
varchar(70) Not Null,
Estado char(2) default ‘PR’
)
• CHECK
– A restrição Check verifica se o valor atribuído ao registro (no
Insert) é um dos valores permitidos para o atributo.
Create Table Alunos
( Matricula Int,
CPF numeric(11) Unique,
Nome varchar(70) Not Null,
Estado char(2) default ‘PR’,
Sexo char(1) check (Sexo in (‘M’,’F’))
)
7
• PRIMARY KEY
– Restrição Utilizada para definir a chave primaria.
Create table Aluno
( Matricula
Int Primary Key,
CPF
numeric(11) Unique,
Nome
varchar(70) Not Null,
Estado
char(2) default ‘PR’,
Sexo
char(1) check (Sexo in (‘M’,’F’)
)
• FOREIGN KEY
– Esta restrição garante a integridade referencial.
( Matricula
CPF
Nome
Estado
Sexo
Codprofissao
)
Int Primary Key,
numeric(11) Unique,
varchar(70) Not Null,
char(2) default ‘PR’,
char(1) check (Sexo in (‘M’,’F’),
Int foreign key references Profissao (cod)
8
Nomenclatura de Restrições
Create Table Aluno
( Matricula
CPF
Nome
Estado
Sexo
Codprofissao
Int Constraint PKAluno Primary Key,
numeric(11) Constraint UnCPF Unique,
varchar(70) Constraint NNNome Not Null,
char(2) Constraint DFEstado default ‘PR’,
char(1) Constraint CKSexo check (Sexo in (‘M’,’F’)),
Int Constraint FKProfissao foreign key
references Profissao (cod)
)
9
Restrições de chaves compostas
• Exemplo 1: chave primária composta
Create Table AlunoCurso
( CodAluno int not null,
CodCurso int not null,
Constraint PkAlunoCurso Primary Key(CodAluno, CodCurso),
Constraint FkACAluno Foreign key (CodAluno) references Aluno(codAluno),
Constraint FkACCurso Foreign key (CodCurso) references Curso(codCurso)
)
• Exemplo 2: chave estrangeira composta
Create Table Curso
( CodCurso int Constraint pkCurso Primary Key,
Nome varchar (70) Not Null,
CodCurriculo int not null,
AnoCurriculo int not null,
Constraint fkCurriculo foreign Key (CodCurriculo, AnoCurriculo) References
Curriculo (CodCurriculo, AnoCurriculo)
)
10
Exercício: Crie o BD abaixo e as respectivas tabelas (com
chaves primárias, estrangeiras e as restrições (Constraint)
necessárias)
n
CONTA
1
Agencia_
conta
n
1
Depositant
e
agenciaempréstimo
n
CLIENTE
AGENCIA
n
n
devedor
n
EMPRÉSTIMO
Modelo Relacional:
Conta (num_conta, nome_ag, saldo)
Agencia (nome_ag, cidade_ag, fundos)
Cliente (nome_cli, cidade_cli, rua_cli)
Depositante (num_conta, nome_cli)
Emprestimo (num_emp, nome_ag, total)
Devedor (nome_cli, num_emp)
11
SQL (DML)
• INSERT
– podemos especificar uma tupla a ser inserida ou escrever
uma consulta cujo resultado é um conjunto de tuplas a
inserir.
– Os valores dos atributos para as tuplas a serem inseridas
devem pertencer ao domínio desses atributos
– as tuplas a serem inseridas devem estar na ordem correta.
– Exemplo: inserir a informação de que a conta “A-998” da
agência Batel tem um saldo de 1.200
insert into conta
values (‘A-998’, ‘Batel’, 1.200)
insert into conta (nome_agencia, numero_conta, saldo)
values (‘Batel’, ‘A-998’, 1.200)
12
• SELECT
– A estrutura básica de uma expressão em SQL consiste em
três claúsulas:
• select: corresponde à operação de projeção (). É usada para
relacionar os atributos desejados no resultado de uma consulta.
• from: corresponde a operação do produto cartesiano ( x ). Associa
as Tabelas que serão pesquisadas durante a evolução de uma
expressão.
• where: corresponde à operação de seleção (). Consiste em um
predicado (condição) envolvendo atributos da Tabela que aparece
no from.
– Uma consulta típica em SQL:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
onde A = atributo, r = Tabela e P = condição
– O SQL forma um produto cartesiano das Tabelas de from,
executa uma seleção usando o predicado de where e projeta
os resultados sobre os atributos da cláusula select.
13
– O resultado de uma consulta em SQL é uma Tabela.
– Exemplo: Encontre os nomes de todas as agências da Tabela
empréstimo.
select nome_ag
from emprestimo
– SQL permite duplicidade nas Tabelas, pois a eliminação da
duplicidade consome muito tempo.
– Para forçar a eliminação de duplicidade usa-se o comando
distinct. Exemplo:
select distinct nome_ag
from emprestimo
– O asterisco “*” pode ser usado para denotar “todos os atributos”.
select emprestimo.* from emprestimo
select * from emprestimo
– Select pode conter expressões aritmética (+, -, * e /). Exemplo:
select nome_ag, total * 100
from emprestimo
14
• WHERE
– Encontre todos os números de empréstimos feitos na agencia
Batel com totais emprestados acima de R$1.200.
select num_emp,total
from emprestimo
where nome_ag = ‘Batel’ and total > 1.200
– O SQL usa os conectores AND, OR e NOT, além do operadores
de comparação <, <=, >, >=, = e <> na cláusula where.
– O operador de comparação between pode ser usado para
simplificar a cláusula where que especifica em valor que seja
maior que ou igual a algum valor e menor que ou igual a algum
outro valor.
– Exemplo: Encontre os números de empréstimos e totais cujos os
montantes sejam entre R$9.000 e R$100.000
select num_emp, total
from emprestimo
where total between 9000 and 100000
– Not between também é permitido.
15
• FROM
– Define a(s) tabelas que serão utilizadas na consulta.
– Para duas ou mais tabelas, pode-se fazer o produto cartesiano ou a
junção das tabelas.
– Ex: para todos os clientes que tenham um empréstimo em um banco,
encontre seus nomes, números de empréstimos e nome da agência
Produto cartesiano:
select distinct nome_cli, devedor.num_emp, nome_ag
from devedor, emprestimo
where devedor.num_emp = emprestimo.num_emp
Junção:
select distinct nome_cli, devedor.num_emp , nome_ag
from devedor INNER JOIN emprestimo ON
devedor.num_emp = emprestimo.num_emp
– usa-se a notação nome_tabela.nome_atributo para evitar
ambigüidades
16
• ALIASES
– Pode-se renomear tanto atributos qto Tabelas.
– Para renomear Tabelas ou atributos utilizamos a cláusula as
que pode aparecer tanto tanto no select qto no from.
nome_antigo as nome_novo
– Exemplo:
select distinct nome_cli, devedor.num_emp, nome_ag as ‘Nome Agencia’
from devedor INNER JOIN emprestimo ON
devedor.num_emp = emprestimo.num_emp
– Encontre os nomes de todas as agências que tenham fundos
maiores que ao menos uma agência daquelas localizadas em
Curitiba
select distinct T.nome_ag
from agencia as T, agencia as S
where T.fundos > S.fundos and S.cidade_ag = ‘Ctba’
17
• OPERAÇÕES COM STRINGS
– As mais usadas são as verificações de coincidências de
pares, usando o operador like.
• Porcentagem (%): compara qquer substring
• sublinhado ( _ ): compara qquer caracter.
– maiúsculas não são iguais a minúsculas.
– Exemplos:
‘Ba%’ - qualquer string que comece com ‘Ba’
‘%iba%’ - qualquer string que possua uma substring ‘iba’.
‘_ _ _’ - qualquer string com 3 caracteres
‘_ _ _%’ qualquer string com pelo menos 3 caracteres
18
– Exemplo: encontre os nomes de todos os clientes
cujas as ruas possuam a substring “aio”
select nome_cli
from cliente
where rua_cli like ‘%aio%’
– O SQL permite o uso de um caracter de escape para
comparar caracteres especiais.
• like “ab\%cd%” escape “\” corresponde a todas as strings
que começam com “ab%cd”
• like “ab\\cd%” escape “\” corresponde a todas as strings
que começam com “ab\cd”
– é possível pesquisar diferenças em strings pelo
operador de comparação not like
19
• ORDER BY
– A cláusula order by faz com que as tuplas do resultado de
uma consulta apareçam em uma determinada ordem.
– Exemplo: listar em ordem alfabética todos os clientes que
tenham um empréstimo na agência Batel.
select distinct nome_cli, devedor.num_emp
from devedor INNER JOIN emprestimo ON
devedor.num_emp = emprestimo.num_emp
where nome_ag = ‘Batel’
order by nome_cli
– Order by relaciona os itens em ordem ascendente. Para
especificar a ordem deve-se usar:
• Desc - para ordem descendente (maior para o menor)
• Asc - para ordem ascendente (menor para o maior)
– a ordenação pode ser realizada por diversos atributos. Ex:
select *
from emprestimo
order by total desc, num_emp asc
20
• UNION
– as Tabelas participantes da operação de União
precisam ser compatíveis, ou seja, precisam ter o
mesmo número de atributos.
– Encontre todos os clientes que tenham um
empréstimo, uma conta ou ambos no banco.
(select nome_cli
from depositante)
union
(select nome_cli
from devedor)
– elimina repetições automaticamente.
– Para obter todas as repetições devemos escrever
union all no lugar de union
21
• FUNÇÕES AGREGADAS
– São funções que tomam uma coleção de valores como
entrada e retorna um valor simples
– A SQL oferece 5 funções agregadas:
•
•
•
•
•
média (average): avg
mínimo (minimum): min
máximo (maximum): max
Total (total): sum
contagem (count): count
– A entrada para sum e avg precisa ser um conjunto de
números
– mim, max e count podem operar com conjuntos de tipos
de dados não-numéricos.
– Exemplo: Encontre a média dos saldos em contas na
agência “batel”.
select avg (saldo) as media
from conta
where nome_ag = ‘batel’
22
– A cláusula GROUP BY é usada qdo queremos aplicar uma
função agregada a um grupo de registros.
– O(s) atributo(s) fornecidos em uma cláusula group by são
usados para formar grupos.
– Registros com os mesmos valores de todos os atributos da
cláusula group by são colocadas em um grupo.
– Exemplo: encontre a média dos saldos nas contas de cada
uma das agências do banco.
select nome_ag, avg (saldo)
from conta
group by nome_ag
– Quando tratamos a Tabela como um todo isto é, como um
grupo simples, não é necessário utilizar a cláusula group
by.
– Exemplo: Encontre a média dos saldos de todas as contas.
select avg (saldo)
from conta
23
– Se desejarmos eliminar repetições utilizaremos a palavra
distinct na expressão agregada.
– Ex: Encontre o número de depositantes de cada agência
select nome_ag, count (distinct nome_cli)
from depositante INNER JOIN conta ON
depositante.num_conta = conta.num_conta
group by nome_ag
– Pode-se definir condições e aplicá-las a grupos ao
invés de aplicá-las a tuplas. Ex:
– Encontre quais agências possuem média dos saldos
aplicados em conta maior que R$1.200.
select nome_ag, avg (saldo)
from conta
group by nome_ag
having avg (saldo) > 1.200
24
– Para contar o número total de registros em uma Tabela,
utilizamos a função count (*).
– Encontre o número de registros da Tabela cliente
select count (*)
from cliente
– A SQL não permite o uso do distinct com count (*)
– Se uma cláusula where e having aparecem na mesma
consulta o predicado que aparece em where é aplicado
primeiro.
– Exemplo: encontre o saldo médio para cada cliente que mora
em Curitiba e tenha ao menos 3 contas.
select depositante.nome_cli, avg (saldo)
from conta INNER JOIN depositante ON
depositante.num_conta = conta.num_conta INNER JOIN
cliente ON depositante.nome_cli = cliente.nome_cli
where cidade_cli = ‘Curitiba’
group by depositante.nome_cli
having count (depositante.nome_cli) >= 3
25
• VALORES NULOS
– A SQL permite o uso do valor nulo para indicar ausência de
informação.
– Pode-se utilizar a palavra is null para testar a existência de
valores nulos.
– Ex: encontre todos os números de empréstimo que
aparecem na Tabela empréstimo com valores nulo para total
select num_emp
from emprestimo
where total is null
– O predicado is not null testa a ausência de valores
nulos
– O resultado de uma expressão aritmética (+, -, * e /) é nula
se qquer um dos valores de entrada for nulo.
26
– Comparações envolvendo valores nulos pode
ser visto como false. No entanto algumas
versões tratam o resultado dessas
comparações como unknown (desconhecido)
– Funções agregadas podem tratar valores nulos
usando a seguinte regra: todas as funções
agregadas, exceto o count(*), ignoram os valores
nulos dos seus conjuntos de valores de entrada.
Select sum (total)
from emprestimo
27
• SUBCONSULTAS ANINHADAS
– Uma subconsulta é uma expressão select-from-where
aninhada dentro de outra consulta.
– Membros de Conjuntos
• permite verificar se um registro é membro ou não de uma Tabela.
• O conectivo in testa os membros de um conjunto, no qual o
conjunto é a coleção de valores produzidos pela cláusula select.
• O conectivo not in testa a ausência de membros de um conjunto.
– Ex: Encontre todos os clientes que possuem tanto
uma conta qto um empréstimo no banco.
select distinct nome_cli
from devedor
where nome_cli in (select nome_cli
from depositante)
28
– Ex2: Encontre todos os clientes que possuem um empréstimo, mas
não uma conta no banco.
select distinct nome_cli
from devedor
where nome_cli not in (select nome_cli
from depositante)
– Ex3: Encontre todos os clientes que tenham um empréstimo e cujos
os nomes não sejam nem “Smith” nem “Jonas”
select distinct nome_cli
from devedor
where nome_cli not in (‘Smith’, ‘Jonas’)
– Ex4: Encontre todos os clientes que tenham tanto uma conta qto um
empréstimo na agência “Batel”
select nome_cli,nome_ag
from devedor INNER JOIN emprestimo ON devedor.num_emp=emprestimo.num_emp
where nome_ag = 'Batel' and nome_cli in
(select nome_cli
from depositante INNER JOIN conta ON
depositante.num_conta = conta.num_conta)
29
• VERIFICAÇÃO DE TABELAS VAZIAS
– o construtor exists retorna true se o argumento de uma subconsulta é
não-vazio.
– Através do construtor not exists podemos testar a não existência de
tuplas na subconsulta
– Exemplo: encontre todos os clientes que tenham tanto uma conta qto
um empréstimo no banco
select nome_cli
from devedor
where exists (select nome_cli from depositante
where depositante.nome_cli = devedor.nome_cli)
• TABELAS DERIVADAS
– É possível o uso de uma expressão de subconsulta na cláusula from.
– Ex: Encontre a média dos saldos das agências em que a média dos
saldos em conta é > que 1.200
select nome_ag
from (select nome_ag, avg (saldo)
from conta
group by nome_ag)
as resultado (nome_ag, saldo_medio)
where saldo_medio > 1.200
30
• COMPARAÇÃO DE CONJUNTOS
– A frase “maior que ao menos uma” pode ser
representada em SQL pelo construtor > some.
– Ex: encontre os nomes de todas as agências que tenham
fundos maiores que ao menos uma agência localizada em
Curitiba.
select distinct nome_ag
from agencia
where fundos >some (select fundos
from agencia
where cidade_ag = ‘Curitiba’)
– A SQL permite comparações <some, <= some, >= some, =
some, <> some
– a palavra any é sinônimo de some em SQL.
– A frase “maior que todos” pode ser representada pelo
construtor > all
– A SQL permite comparações <all, <= all, >= all, = all, <> all
31
– Ex: encontre os nomes de todas as agências que
tenham fundos maiores que cada uma das agências
localizada em Curitiba.
select distinct nome_ag
from agencia
where fundos > all (select fundos
from agencia
where cidade_ag = ‘Curitiba’)
– Funções agregadas não podem ser agregadas em
SQL, por exemplo, max (avg (...))
– Ex: Encontre a agência que tem o maior saldo médio.
select distinct nome_ag
from conta
group by nome_ag
having avg (saldo) >= all (select avg (saldo)
from conta
group by nome_ag)
32
• INSERT usando uma consulta cujo resultado é
um conjunto de tuplas a inserir.
– Exemplo: Dê a todos os clientes da agencia Batel uma
caderneta de poupança de R$200, sendo que o numero do
empréstimo serve como o numero da caderneta de
poupança.
insert into conta
select num_emp, nome_ag, 200
from emprestimo
where nome_ag = ‘Batel’
– O comando select é executado primeiro resultando
em um conjunto de tuplas que são inseridas em uma
Tabela conta.
33
• UPDATE
– modifica valores de tuplas sem alterar todos os valores.
– Ex1: Dê a todos os saldos 5% como pagamento da taxa de juros
update conta
set saldo = saldo * 0.05
comando acima é aplicado uma vez para cada tupla de conta.
– Ex2: Dê às contas com saldo superior a 10.000 juros de 6% e as
demais contas juros de 5%.
update conta
set saldo = saldo * 0.06
where saldo > 10.000
update conta
set saldo = saldo * 0.05
where saldo < = 10.000
– Ex3: Pague 5% de taxa de juros para as contas cujo o saldo seja
maior que a média dos saldos.
update conta
set saldo = saldo * 0.05
where saldo > (select avg (saldo)
from conta)
34
• DELETE
– podemos remover somente tuplas inteiras. A remoção é expressa por:
delete from r
where P
– o comando delete encontra primeiro todas as tupla t em r para os quais
P(t) é verdadeira, e então remove-as de r.
– Pode-se ter uma cláusula where vazia. Ex:
delete from empréstimo // remove todos os registros
– Exemplo1: remova todos os registros de contas do cliente “Smith”.
delete from depositante
where nome_cli = ‘Smith’
– remova todas as contas de cada uma das agências localizadas em “ctba”
delete from conta
where nome_ag in (select nome_ag
from agencia
where cidade_ag = ‘ctba’)
– Ex: remova os registros de todas as contas com saldos abaixo da média.
delete from conta
where saldo < (select avg (saldo)
from conta)
35
• VISÕES (VIEW)
– Definimos uma visão:
create view v as <expressão da consulta>
– Ex1: suponha que desejemos criar uma visão denominada
todos_clientes que seja composta dos nomes das agências e
nomes dos clientes que tenham uma conta ou um empréstimo na
agência Batel.
create view todos_clientes as
(select nome_ag, nome_cli
from depositante INNER JOIN conta ON depositante.num_conta=conta.num_conta
where nome_ag = ‘Batel’)
union
(select nome_ag, nome_cli
from devedor INNER JOIN emprestimo ON devedor.num_emp = emprestimo.num_emp
where nome_ag = ‘Batel’)
36
• Ex2: Crie uma visão que mostre a soma dos totais de todos os
empréstimos de cada agência.
create view emprestimo_total_agencia (nome_agencia,emprestimo_total) as
select nome_ag, sum (total)
from emprestimo
group by nome_ag
• Atualização de Visões
– O nome de uma visão pode aparecer em qualquer lugar onde o nome de
uma Tabela aparece.
create view agencia_emprestimo as
select nome_ag, num_emp
from emprestimo
insert into agencia_emprestimo
values (‘Batel’, 998)
– na Tabela empréstimo (Tabela real) será inserido: (‘Batel’, ‘L-998l’, null)
– Regra para alterações por meio de visões na maioria dos banco de
dados:
• uma alteração por meio de visão somente é permitida se a visão em
questão é definida em termos de uma Tabela real do banco de dados.
37