Treinamento básico SQL - Linguagem de Consulta Estruturada (1)

Prévia do material em texto

José Simão de Paula Pinto
1
SQL
Linguagem de Consulta Estruturada
Treinamento básico
José Simão de Paula Pinto
2
Servidores de arquivos
x
Gerenciadores de Bancos de Dados
Revisão
José Simão de Paula Pinto
3
uVisão Geral
n Sistemas de Arquivos x Banco de Dados Relacional
n Conceitos Básicos de Bancos de Dados Relacional
José Simão de Paula Pinto
4
uAcesso a Dados
n Sistemas de Arquivos
l Os dados são obtidos após percorrer-se um ou mais
arquivos, por meio de linguagens de programação,
sendo em geral necessária a construção de um novo
programa para cada nova consulta
n Banco de Dados Relacional
l O gerenciador de banco de dados obtém as
informações que foram solicitadas por meio de uma
linguagem de consulta padrão, e entrega-as em forma
de lista (ou relatório)
José Simão de Paula Pinto
5
uO Modelo de Entidades e Relacionamentos
Existe comoExiste como
ExemplosExemplos
EntidadesEntidadesEntidades RelacionamentosRelacionamentosRelacionamentos AtributosAtributosAtributos
TabelasTabelas
Informação de
um cliente;
informação de
um pedido
Informação de
um cliente;
informação de
um pedido
Colunas
ou Tabelas
Colunas
ou Tabelas
Pedidos de
um cliente
Pedidos de
um cliente
Colunas
(ou Campos)
numa Tabela
Colunas
(ou Campos)
numa Tabela
Nome,
Endereço,
Telefone do
cliente
Nome,
Endereço,
Telefone do
cliente
José Simão de Paula Pinto
6
identificador nome endereço telefone … … ...
1001
1002
1003
1004
1005
1006
João
Alberto
Franciso
Maria
Sônia
Roberto
… … .
… … .
… … ..
… … ..
… … ...
… … … .
5554444
4687999
NULL
5678900
0988855
NULL
~~~
~~~
~~~
~~~
~~~
clientesclientes
uTabelas (Entidades) e Colunas (Atributos)
n Tabelas tem nomes únicos
n São compostas por linhas e colunas
n Linhas e colunas podem estar em qualquer ordem
n Tem um identificador único: chave primária (Primary
Key)
n Colunas: atributos
José Simão de Paula Pinto
7
uRelacionamentos
identificador nome … … … . … … … .. … … . … … … … ..
1001
1002
1008
João
Alberto.
Wilson
S… ..
S… ..
… … .
… .
… .
… .
98022
98022
98026
NULL
206-555-1212
NULL
05 Jun 1992
07 Ago 1992
03 Mar 1993
clientesclientes
PK NN NN NN NN
numero cliente produto 
1
1
2
1002
1001
1001
PedidosPedidos
PK PK,FK, NN NN
567
566
122
José Simão de Paula Pinto
8
uSQL - “Structured Query Language”
n SQL - linguagem estruturada criada para a manipulação
dos elementos do banco de dados
n Características importantes:
l Padronizada para os bancos de dados relacionais.
l Cada comando é uma descrição do que se deseja
obter.
l Quem executa o comando é o gerenciador do banco de
dados.
José Simão de Paula Pinto
9
Comandos
da
Linguagem de Consulta Estruturada
Definição de dados - DDL
José Simão de Paula Pinto
10
uCriar uma tabela - CREATE TABLE
n Permite a criação de uma tabela e a especificação de
como será seu conteúdo (nomes e tipos de dados das
colunas)
n Palavras chave:
l CREATE TABLE - especifica o nome da tabela.
l CHAR, VARCHAR - tipos de dados para caracteres.
l INT - tipo de dados para números.
José Simão de Paula Pinto
11
uExemplo de criação de tabela - CREATE TABLE
ANTES
telefonenome
DEPOIS
CREATE TABLE clientes
(
nome VARCHAR(40),
telefone VARCHAR(10)
)
CREATE TABLE clientes
(
nome VARCHAR(40),
telefone VARCHAR(10)
)
José Simão de Paula Pinto
12
uApagar (destruir) uma tabela - DROP TABLE
n Permite apagar uma tabela (não somente o conteúdo,
mas a tabela em si) de um banco de dados.
n Palavras chave:
l DROP TABLE - apaga a tabela indicada
n ATENÇÃO: este comando apagará a tabela e todo o seu
conteúdo do banco de dados especificado ou em uso.
Não existe volta ! Use com cuidado !
José Simão de Paula Pinto
13
uExemplo de deleção de tabela - DROP TABLE
ANTES
telefone
João
Alberto
Maria
Francisco
445-0988
465-98-87
789-9877
null
nome
DEPOIS
DROP TABLE clientesDROP TABLE clientes
José Simão de Paula Pinto
14
Comandos
da
Linguagem de Consulta Estruturada
Manipulação de dados - DML
José Simão de Paula Pinto
15
uObtendo informações - SELECT
n Seleciona linhas e colunas de uma tabela
n Palavras chave:
l SELECT - determina quais as colunas a serem
retornadas ( o asterisco “ * ” seleciona todas).
l FROM - determina de quais tabelas serão selecionados
os dados.
l WHERE - determina quais as linhas deverão ser
retornadas. A cláusula where também é conhecida por
critério.
José Simão de Paula Pinto
16
SELECT nome,telefone
FROM clientes
SELECT nome,telefone
FROM clientes
nome telefone
João
Alberto
Maria
Francisco
445-0988
465-98-87
789-9877
null
uExemplo do comando SELECT
José Simão de Paula Pinto
17
SELECT nome,telefone
FROM clientes
WHERE nome = ‘João’
SELECT nome,telefone
FROM clientes
WHERE nome = ‘João’
nome telefone
João 445-0988
uExemplo do comando SELECT condicional
José Simão de Paula Pinto
18
uAdicionando linhas - INSERT
n Insere linhas em uma tabela
n Palavras chave:
l INSERT - indica a tabela e os nomes das colunas nas
quais será realizada a inclusão dos dados.
l VALUES - informa quais são os valores a serem
incluídos nas respectivas colunas.
José Simão de Paula Pinto
19
INSERT clientes (nome,telefone)
VALUES (Sônia, null)
INSERT clientes (nome,telefone)
VALUES (Sônia, null)
uExemplo do comando INSERT
telefone
João
Alberto
Maria
Francisco
Sônia
445-0988
465-98-87
789-9877
null
null
nome
ANTES
telefone
João
Alberto
Maria
Francisco
445-0988
465-98-87
789-9877
null
nome
DEPOIS
José Simão de Paula Pinto
20
uAlterando informações - UPDATE
n Altera o conteúdo de determinadas colunas em uma ou
mais linhas de uma tabela.
n Palavras chave:
l UPDATE - determina qual é a tabela que será alterada.
l SET - determina quais são as alterações a serem
realizadas, em em quais colunas elas ocorrerão.
l WHERE - determina quais são as linhas a serem
alteradas.
José Simão de Paula Pinto
21
UPDATE clientes
SET telefone = ”882-3344”
WHERE nome = ”Sônia”
UPDATE clientes
SET telefone = ”882-3344”
WHERE nome = ”Sônia”
uExemplo do comando UPDATE
telefone
João
Alberto
Maria
Francisco
Sônia
445-0988
465-98-87
789-9877
null
882-3344
nome
ANTES DEPOIS
telefone
João
Alberto
Maria
Francisco
Sônia
445-0988
465-98-87
789-9877
null
null
nome
José Simão de Paula Pinto
22
uApagando informações - DELETE
n Apaga uma ou mais linhas de uma tabela.
n Palavras chave:
l DELETE - determina qual a tabela que terá linhas
apagadas.
l WHERE - determina quais são as linhas a serem
apagadas.
n Atenção: este comando não apaga a tabela, somente as
suas linhas (seu conteúdo) !
José Simão de Paula Pinto
23
telefone
João
Alberto
Maria
Francisco
Sônia
445-0988
465-98-87
789-9877
null
882-3344
nome
ANTES
telefone
João
Alberto
Maria
Francisco
445-0988
465-98-87
789-9877
null
nome
DEPOIS
DELETE clientes
WHERE nome = ”Sônia”
DELETE clientes
WHERE nome = ”Sônia”
uExemplo do comando DELETE condicional
José Simão de Paula Pinto
24
uExemplo de comando DELETE sem condição !
telefone
João
Alberto
Maria
Francisco
Sônia
445-0988
465-98-87
789-9877
null
882-3344
nome
ANTES
telefonenome
DEPOIS
DELETE clientesDELETE clientes
José Simão de Paula Pinto
25
uResumo dos comandos de manipulação vistos
nSELECT colunas
FROM tabela
WHERE condição
n INSERT tabela
VALUES ( conteúdo a armazenar )
nUPDATE tabela
SET coluna =novo conteúdo
WHERE condição
nDELETE tabela
WHERE condição
José Simão de Paula Pinto
26
Prática
da
Linguagem de Consulta Estruturada
Exercícios de fixação
José Simão de Paula Pinto
27
uExercício - Criação de tabelas
nomecod
descricaocod
valorcod
produtocomprador qtdade
n cliente
n produto
n preco
n venda
José Simão de Paula Pinto
28
uRespostas - Criação de tabelas
n CREATE TABLE cliente
(
cod INT,
nome CHAR(20)
)
n CREATE TABLE produto
(
cod INT,
descricao CHAR(20)
)
José Simão de Paula Pinto
29
uRespostas - Criação de tabelas
n CREATE TABLE preco
(
cod INT,
valor MONEY
)
n CREATE TABLE venda
(
comprador INT,
produto INT,
qtdade INT
)
José Simão de Paula Pinto
30
uExercício - Inserção de valores
descricaocod
Barco
Sabonete
Abacaxi
1
2
3
nomecod
Ana
Pedro
Tânia
Maria
João
1
2
3
4
5
José Simão de Paula Pinto
31
uExercício - Inserção de valores
valorcod
500.80
32.89
2.00
1
2
3
produtocomprador qtdade
3
1
2
1
4
2
1
8
5
1
10
1
1
2
2
3
3
1
José Simão de Paula Pinto
32
uRespostas - Inserção de valores
n INSERT cliente VALUES(1,'ANA')
n INSERT cliente VALUES(2,'PEDRO')
n INSERT cliente VALUES(3,'TÂNIA')
n INSERT cliente VALUES(4,'MARIA')
n INSERT cliente VALUES(5,'JOÃO')
n INSERT produto VALUES(1,'BARCO')
n INSERT produto VALUES(2,'SABONETE')
n INSERT produto VALUES(3,'ABACAXI')
José Simão de Paula Pinto
33
uRespostas - Inserção de valores
n INSERT preco VALUES(1, 500.80)
n INSERT preco VALUES(2, 32.89)
n INSERT preco VALUES(3, 2.00)
n INSERT venda VALUES(3,1,1)
n INSERT venda VALUES(1,2,8)
n INSERT venda VALUES(2,2,5)
n INSERT venda VALUES(1,3,1)
n INSERT venda VALUES(4,3,10)
n INSERT venda VALUES(2,1,1)
José Simão de Paula Pinto
34
uExercício - Comandos de seleção
n Selecionar todos os clientes
n Selecionar todos os produtos
n Selecionar todos os preços
n Selecionar todos os registros das vendas efetuadas
José Simão de Paula Pinto
35
uResposta - Selecionar todos os clientes
SELECT nome FROM clienteSELECT nome FROM cliente
Ana
Pedro
Tânia
Maria
João
nome
José Simão de Paula Pinto
36
uResposta - Selecionar todos os produtos
SELECT descricao FROM produtoSELECT descricao FROM produto
Barco
Sabonete
Abacaxi
descricao
José Simão de Paula Pinto
37
uResposta - Selecionar todos os preços
SELECT valor FROM precoSELECT valor FROM preco
500.80
32.89
2.00
valor
José Simão de Paula Pinto
38
uResposta - Selecionar todas as vendas efetuadas
SELECT * FROM vendaSELECT * FROM venda
???
produtocomprador qtdade
3
1
2
1
4
2
1
8
5
1
10
1
1
2
2
3
3
1
José Simão de Paula Pinto
39
Comandos
da
Linguagem de Consulta Estruturada
Classificação, comparações,
junção e operadores agregadores
José Simão de Paula Pinto
40
uOperador de classificação - ORDER BY
n Destina-se à classificação (ordenação) dos dados, na
apresentação (não afeta os dados originais na tabela).
n Palavras chave:
l ASC - ordenação ascendente (do menor para o maior).
l DESC - ordenação descendente (do maior para o
menor)
José Simão de Paula Pinto
41
uExemplo do uso de ORDER BY
Ana
João
Maria
Pedro
Tânia
nome
ASC
SELECT nome
FROM cliente
ORDER BY nome
SELECT nome
FROM cliente
ORDER BY nome
Tânia
Pedro
Maria
João
Ana
nome
DESC
SELECT nome
FROM cliente
ORDER BY nome DESC
SELECT nome
FROM cliente
ORDER BY nome DESC
José Simão de Paula Pinto
42
uOperadores de comparação
= Igual
> Maior
< Menor
>= Maior ou igual
<= Menor ou igual
<> ou != Diferente ou não igual
LIKE Parecido
José Simão de Paula Pinto
43
uExemplo do uso do operador igual (=)
SELECT nome
FROM cliente
WHERE cod = 4
SELECT nome
FROM cliente
WHERE cod = 4
Maria
nome
José Simão de Paula Pinto
44
uExemplo do uso do operador diferente ( <> ou != )
Ana
Pedro
Tânia
João
nome
SELECT nome
FROM cliente
WHERE cod <> 4
SELECT nome
FROM cliente
WHERE cod <> 4
José Simão de Paula Pinto
45
uExemplo do uso do operador LIKE
Ana
Tânia
Maria
nome
SELECT nome
FROM cliente
WHERE nome LIKE ‘%a’
SELECT nome
FROM cliente
WHERE nome LIKE ‘%a’
José Simão de Paula Pinto
46
uOperadores AND e OR
n Operador AND
E = somente retornará os dados quando as condições
forem todas satisfeitas
n Operador OR
OU = retorna dados assim que qualquer das condições
for satisfeita
José Simão de Paula Pinto
47
uExemplo de uso do operador AND
nome
SELECT nome
FROM cliente
WHERE cod > 40
AND nome = ‘Maria’
SELECT nome
FROM cliente
WHERE cod > 40
AND nome = ‘Maria’
José Simão de Paula Pinto
48
uExemplo do uso do operador OR
Maria
nome
SELECT nome
FROM cliente
WHERE cod > 40
OR nome = ‘Maria’
SELECT nome
FROM cliente
WHERE cod > 40
OR nome = ‘Maria’
José Simão de Paula Pinto
49
uExemplo do uso dos operadores AND e OR juntos
Pedro
Maria
nome
SELECT nome
FROM cliente
WHERE cod < 3
AND nome LIKE ‘%o’
OR nome = ‘Maria’
SELECT nome
FROM cliente
WHERE cod < 3
AND nome LIKE ‘%o’
OR nome = ‘Maria’
José Simão de Paula Pinto
50
uJunção de tabelas - JOIN
n Utilizamos a junção (JOIN) de tabelas, e seus
operadores, de maneira a expressar os relacionamentos
que foram criados quando da montagem da base de
dados, obtendo os conjuntos de valores originais,
desnormalizados.
n A junção é efetuada basicamente efetuando-se uma
comparação entre dois campos de valores semelhantes
em tabelas distintas, selecionando em ambas as tabelas
as linhas que atendam estes valores e combinando-as.
Estas operações serão efetuadas automaticamente pelo
gerenciador de bancos de dados.
José Simão de Paula Pinto
51
uExemplo de junção - JOIN
descricaocod
Barco
Sabonete
Abacaxi
1
2
3
valorcod
500.80
32.89
2.00
1
2
3
SELECT descricao, valor
FROM produto, preco
WHERE produto.cod = preco.cod
SELECT descricao, valor
FROM produto, preco
WHERE produto.cod = preco.cod
valordescricao
500.80
32.89
2.00
Barco
Sabonete
Abacaxi
José Simão de Paula Pinto
52
uOperadores de agregação
MIN Retorna o menor valor
MAX Retorna o maior valor
COUNT Retorna a quantidade
de valores
AVG Retorna a média dos
valores
SUM Retorna a soma dos
valores
José Simão de Paula Pinto
53
uExemplos de uso de operadores de agregação
SELECT MAX( valor )
FROM preco
--> Retorna o maior preço
SELECT MAX( valor )
FROM preco
--> Retorna o maior preço
SELECT AVG( valor )
FROM preco
--> Retorna a média de preços
SELECT AVG( valor )
FROM preco
--> Retorna a média de preços
SELECT COUNT( * )
FROM cliente
--> Retorna a quantidade de clientes
SELECT COUNT( * )
FROM cliente
--> Retorna a quantidade de clientes
José Simão de Paula Pinto
54
uAgrupamento / Subtotais - GROUP BY
n Este comando permite que a apresentação dos dados
retornados da consulta seja agrupada de acordo com
uma determinada condição, digamos a soma dos
valores vendidos para cada produto. É utilizado em
conjunto com o comando SELECT.
n Uso:
SELECT colunas
FROM tabela
WHERE condição
GROUP BY coluna ou agregado
José Simão de Paula Pinto
55
uExemplo de agrupamento - GROUP BY
SELECT descricao, unidades=sum(qtdade)
FROM produto, venda
WHERE produto.cod = venda.produto
GROUP BY descricao
SELECT descricao, unidades=sum(qtdade)
FROM produto, venda
WHERE produto.cod = venda.produto
GROUP BY descricao
unidadesdescricao
11
2
13
Abacaxi
Barco
Sabonete
José Simão de Paula Pinto
56
uOperador DISTINCT
n O operador DISTINCT possibilita que uma consulta
retorne valores únicos, semrepetições, para a coluna
de dados.Este operador deve ser utilizado em conjunto
com a declaração SELECT.
n Uso:
SELECT DISTINCT (coluna)
FROM tabela
WHERE condição
José Simão de Paula Pinto
57
uExemplo do uso do operador DISTINCT
Barco
Sabonete
Sabonete
Abacaxi
Abacaxi
Barco
descricao
SEM usar DISTINCT
SELECT descricao
FROM produto, venda
WHEREproduto.cod = venda.produto
SELECT descricao
FROM produto, venda
WHEREproduto.cod = venda.produto
Abacaxi
Barco
Sabonete
descricao
Usando DISTINCT
SELECT DISTINCT (descricao)
FROM produto, venda
WHEREproduto.cod = venda.produto
SELECT DISTINCT (descricao)
FROM produto, venda
WHEREproduto.cod = venda.produto
José Simão de Paula Pinto
58
uOperadores IN e BETWEEN
n IN = para testar se um valor está dentro de um
determinado conjunto de valores. Pode utilizar o
operador IN em conjunto com o operador NOT
(formando a expressão NOT IN).
n BETWEEN = para testar se um valor está entre uma
determinada faixa de valores.
José Simão de Paula Pinto
59
uExemplo do uso do operador IN
SELECT descricao
FROM produto
WHERE descricao IN (‘Abacaxi, ‘Sabonete’)
SELECT descricao
FROM produto
WHERE descricao IN (‘Abacaxi, ‘Sabonete’)
Sabonete
Abacaxi
descricao
José Simão de Paula Pinto
60
uExemplo do uso do operador BETWEEN
SELECT nome
FROM cliente
WHERE cod BETWEEN 2 and 3
SELECT nome
FROM cliente
WHERE cod BETWEEN 2 and 3
Barco
Sabonete
Abacaxi
descricao
Pedro
Tânia
nome
José Simão de Paula Pinto
61
uOperador HAVING
n O operador HAVING deverá ser utilizado em conjunto
com a declaração SELECT e sua função será a de
estabelecer um critério extra de agrupamento ou
seleção de valores, quando utilizando-se a cláusula
GROUP BY. Pode-se entender a declaração HAVING
como sendo uma cláusula WHERE para a declaração
GROUP BY.
José Simão de Paula Pinto
62
uExemplo de uso do operador HAVING
SELECT descricao, unidades=sum(qtdade)
FROM produto, venda
WHERE produto.cod = venda.produto
GROUP BY descricao
HAVING sum(qtdade) > 12
SELECT descricao, unidades=sum(qtdade)
FROM produto, venda
WHERE produto.cod = venda.produto
GROUP BY descricao
HAVING sum(qtdade) > 12
unidadesdescricao
13Sabonete
José Simão de Paula Pinto
63
Prática
da
Linguagem de Consulta Estruturada
Exercícios de fixação
José Simão de Paula Pinto
64
uExercícios - Comandos de seleção avançados
n Quantas vendas foram registradas?
n Qual dos clientes não comprou nada?
n O que cada cliente comprou e por quanto?
n Descritivo (descricao, preços unitários e total e
quantidade) de cada venda, ordenado por produto
n Quais os totais de compras por cliente?
n Totais de vendas: quantas vendas e seu valor (geral)
José Simão de Paula Pinto
65
Comandos
da
Linguagem de Consulta Estruturada
Stored Procedures
José Simão de Paula Pinto
66
uSTORED PROCEDURES
n Stored Procedures, ou procedimentos armazenados,
são como que pequenos programas, escritos em
linguagem SQL, e ficam armazenados no gerenciador
de banco de dados.
n O servidor de bancos de dados realiza uma pré-
compilação dos procedimentos, e armazena-os em
memória cache após a sua primeira execução, de
maneira que tendem a ser / tornar-se mais rápidos que a
execução de declarações com mesma finalidade a partir
do cliente.
José Simão de Paula Pinto
67
uCriação de STORED PROCEDURES
n A sintaxe para a criação de uma stored procedure é:
l CREATE PROCEDURE nome do procedimento AS
l Exemplo:
l CREATE PROCEDURE TodosClientes AS
SELECT * FROM cliente
l Para sua execução, basta declarar o nome do
procedimento, neste exemplo TodosClientes
José Simão de Paula Pinto
68
uSTORED PROCEDURES com parâmetros
n Podemos enviar parâmetros para uma stored
procedured, desde que ela tenha sido declarada de
maneira a recebê-lo.
n Os parâmetros são declarados por meio do uso do
símbolo @ seguido do nome da variável. Deve-se
informar também o tipo de dados que será tratado:
l @nome TipoDeDado
n Os parâmetros (de entrada) são informados antes do
uso da palavra reservada AS, na declaração da stored
procedure.
José Simão de Paula Pinto
69
uExemplo de STORED PROCEDURE com parâmetro
CREATE PROCEDURE QualNome @codigo int AS
SELECT cod, nome
FROM cliente
WHERE cod = @codigo
CREATE PROCEDURE QualNome @codigo int AS
SELECT cod, nome
FROM cliente
WHERE cod = @codigo
nomecod
João5
QualNome 5QualNome 5
José Simão de Paula Pinto
70
uVariáveis em STORED PROCEDURES
n Podemos utilizar variáveis dentro de uma stored
procedure, desde que elas sejam previamente
declaradas.
n A declaração ocorre dentro do corpo da stored
procedure, utilizando-se a palavra reservada DECLARE
e informando um nome de variável (precedido do
símbolo @) e o tipo de dados que ela conterá.
l DECLARE @nome TipoDeDado
José Simão de Paula Pinto
71
uExemplo de STORED PROCEDURE com variável
CREATE PROCEDURE Total @codigo int, @quantos int AS
DECLARE @saida money
SELECT @saida = @quantos * (SELECT valor FROM preco
WHERE cod = @codigo)
SELECT ‘Preço total’ = @saida
CREATE PROCEDURE Total @codigo int, @quantos int AS
DECLARE @saida money
SELECT @saida = @quantos * (SELECT valor FROM preco
WHERE cod = @codigo)
SELECT ‘Preço total’ = @saida
Preço total
20.00
Total 3, 10Total 3, 10
José Simão de Paula Pinto
72
uAlgumas STORED PROCEDURES do sistema
n SP_WHO - mostra quem está usando o servidor.
n SP_HELP - mostra objetos do banco de dados.
n SP_HELP parâmetro - mostra características de
“parâmetro”.
l SP_HELP cliente - exibe as características da tabela
“cliente”.
n SP_HELPTEXT parâmetro - exibe o conteúdo da stored
procedure passada em.
l “parâmetro” SP_HELP TodosClientes - exibe o
conteúdo da stored procedure “TodosClientes”.
José Simão de Paula Pinto
73
Prática
da
Linguagem de Consulta Estruturada
Exercícios de fixação
José Simão de Paula Pinto
74
uExercícios usando STORED PROCEDURES
n Criar STORED PROCEDURES para:
l Exibir todos os produtos.
l Exibir todos os produtos, e seus preços.
l Retornar o preço de um produto desde que passado
seu código para o procedimento.
l Retornar quantos itens já foram vendidos de um
produto, e sua descrição, desde que passado seu
código para o procedimento.