From produtos

Curso EFA
de técnico de Informática e
Sistemas
EDUCAÇÃO E FORMAÇÃO DE ADULTOS
Curso EFA – Sec. Turma C - 2012 / 2013
Ano letivo 2012-2013
Docente: Ana Batista
Exemplos de comandos

Criar e alterar a estrutura de uma BD:

Create database – para iniciar a criação de uma base
de dados;

Create table – para criar a estrutura de uma tabela;

Alter table – modificar a estrutura de uma tabela;

Drop table – eliminar uma tabela;

Drop database – eliminar uma BD.
Docente: Ana Batista
2
Exemplos de comandos

Atualizar e consultar uma BD:

Insert – inserir dados numa tabela;

Update – atualizar ou alterar dados;

Delete – apagar dados;

Select – efetuar consultas ou pesquisas
a uma base de dados.
Docente: Ana Batista
3
Exemplos de comandos

Controlar o acesso e o funcionamento
seguros de uma BD:

grant – permite atribuir ou definir os direitos dos
utilizadores de uma BD;

revoke – retira os direitos atribuídos;

Lock table – bloqueia uma tabela de forma a impedir
que outros utilizadores a alterem;

Unlock table – desbloqueia tabela bloqueada;

Start
transation
–
inicia
uma
transação/operação
controlada numa BD.
Docente: Ana Batista
4
Comandos para criar e eliminar
uma BD

Criação de uma BD

Create database Vendas
Objetivo: iniciar a criação de uma bd
com o nome Vendas.
Docente: Ana Batista
5
Comandos para criar e eliminar
uma BD

Eliminar uma BD em SQL

Drop database Vendas
Objetivo: elimina a bd com o nome Vendas, de
forma irrecuperável (a não ser que tenham sido
feitas cópias de segurança).
NOTA: Só os utilizadores do sistema que tenham o
estatuto de superutilizador ou a quem tenham sido
atribuídos todos os direitos sobre a base de dados, é
que podem efetuar este tipo de operação.
Docente: Ana Batista
6
Criar uma tabela - sintaxe
Create table nome_tabela
(definição_campo1
…
Definição campo_n);
Cria uma tabela com um dado nome. Dentro dos
parêntesis é indicada a estrutura dos campos que
se pretende para a tabela.
Docente: Ana Batista
7
Assim…

Definição_campo1 corresponde a:
Nome_campo tipo_dados
[not null] [default valor]
[primary key]
[references…]
Docente: Ana Batista
8
Ou seja…
Para cada campo da tabela indicamse obrigatoriamente:


Nome do campo
Tipo de dados do campo
Para além destes elementos obrigatórios,
podem ainda ser indicados outros
elementos opcionais.
Docente: Ana Batista
9
NOTA: os parêntesis retos servem
apenas para indicar os elementos
opcionais; se estes elementos forem
indicados,
são
escritos
sem
os
parêntesis retos.
Docente: Ana Batista
10
Elementos opcionais


Not null – serve para indicar que o
campo não pode conter valores nulos
Default valor – permite indicar um
valor por defeito para o campo.
Por exemplo: default 10 (o campo
assume este valor se não for indicado
outro)
Docente: Ana Batista
11
Elementos opcionais


Primary key – indica que o campo é ou faz
parte da chave primária da tabela.
References – inicia a indicação que o campo
funciona como chave estrangeira.
Deve ser escrito:
References nome_tabela (nome_campo) , ou seja, a seguir à palavra
References é escrito o nome da tabela e o nome do campo (dentro
parêntesis curvos) a que o campo faz referência como chave
estrangeira.
Docente: Ana Batista
12
Principais tipos de dados em SQL
Tipos de dados
Descrição
Char (n)
Caracter ou string – de comprimento indicado
até um máximo de 255.
VarChar (n)
Semelhante ao anterior, mas neste caso, Var
significa tamanho variável em relação ao
espaço realmente ocupado, consoante o dado
inserido.
text
Texto até um máximo de 65535.
smallint
Números inteiros de tamanho reduzido
Docente: Ana Batista
13
Principais tipos de dados em SQL
Tipos de dados
Descrição
Int ou integer
Números inteiros de tamanho médio
bigint
Números inteiros de tamanho grande
Numeric (m,d)
ou decimal (M,d)
Valor numérico que é guardado como uma
string formada por digitos decimais, em que
m representa o n.º digitos e d as casas
decimais
Float (m,d)
Valor numérico que é guardado com o
formato floating point (virgula flutuante)
Docente: Ana Batista
14
Principais tipos de dados em SQL
Tipos de dados
Descrição
Double
Semelhante ao anterior
Date
No mysql o formato padrão é “yyyy-mm-dd”
– ano, mês, dia
time
Formato da hora: “hh:mm:ss”
Docente: Ana Batista
15
Exemplo de um Modelo físico de
uma base de dados
Clientes
Produtos
Encomendas
CodCLi
CodProd
Nenc
Cliente
Preco
CodCli
CodProd
Morada
Quant
Docente: Ana Batista
16
Exemplificação da criação de
tabelas
Create table Clientes
(CodCli Int auto_increment primary key,
Cliente Varchar(20),
morada varchar (30));
CodCli
Cliente
Docente: Ana Batista
Morada
17
Explicação

A tabela Clientes é composta por 3
campos:



CodCli - int
Cliente - varchar
Morada – varchar
Docente: Ana Batista
18
Campo Codcli

Código do cliente é definido como int, isto é
número
inteiro,
auto_increment,
além
ou
seja,
disso
o
é
sistema
incrementa o n.º do código sempre que é
inserido um novo registo nesta tabela.

É indicado como primary key, pois é a chave
primária da tabela clientes.
Docente: Ana Batista
19
Campo Cliente

Definido como varchar(20), o campo cliente
aceita até um máximo de 20 carateres.
Docente: Ana Batista
20
Campo Morada

Definido como varchar(30), o campo morada
aceita até um máximo de 30 carateres.
Docente: Ana Batista
21
Exemplificação da criação de
tabelas
Create table Produtos
(Codprod Int auto_increment primary key,
Produto Varchar(20), preco numeric
(10,2));
CodProd
Produto
Docente: Ana Batista
preco
22
Tabela Produtos

É semelhante à tabela Clientes, o que
muda é o campo Preco.
NOTA: não se deve escrever preço, porque os
nomes tabelas, campos em SQL só podem
conter os carateres do alfabeto inglês, onde
não há acentos nem caracteres especiais
como o ç.
Docente: Ana Batista
23
Campo Preco

O
campo
preço
é
do
tipo
numérico, não inteiro, mas com
casas decimais, aconselhável para
pôr
por
exemplo,
valores
monetários.
Docente: Ana Batista
24
Tabela Encomendas
Create table Encomendas
(Nenc Int Auto_increment primary key,
CodCli Int references Clientes (CodCli),
CodProd
Int
references
Produtos
(CodProd),
Quant Int);
Docente: Ana Batista
25
Campo NEnc

O campo Nenc (nº da encomenda)
é
definido
como
inteiro,
incrementado, e chave primária
da tabela encomendas.
Docente: Ana Batista
26
Campo Quant

O campo Quant (quantidade) é
definido como inteiro.
Docente: Ana Batista
27
Parte especial da tabela
encomendas…

Reside nos campos codCli e CodProd que são
chaves estrangeiras, por se referirem a
campos que são chaves primárias em outras
tabelas.

Esses campos vão fazer referência aos mesmos
campos nas tabelas de origem (Clientes e
produtos).
Docente: Ana Batista
28
Eliminar e alterar a estrutura de
uma tabela

Para eliminar
comando sql é
uma
tabela,
o
drop table nome_tabela
Ex: drop table Encomendas
Docente: Ana Batista
29
Eliminar e alterar a estrutura de
uma tabela

Para alterar uma tabela, o comando sql é
alter table que contém 3 variantes: add,
modify e drop.
Ex: alter table nome_tabela
{Add (novo_campo tipo_dados)
| modify (nome_campo tipo_dados)
| drop (nome_campo)};
Docente: Ana Batista
30

O facto de as 3 opções, add,
modify e drop serem incluídas
dentro de chavetas {} significa que
só uma delas pode ser usada em
cada instrução.
Docente: Ana Batista
31
Add
novo_campo
Alter table xxx
Modify
campo
Drop campo
Adiciona um novo campo à
tabela xxx
Modifica um novo campo na
tabela xxx
Elimina um novo campo na
tabela xxx
Docente: Ana Batista
32
Exemplo 1
Alter table Clientes
Add telefone integer;
Acrescenta na tabela clientes, um
novo campo com o nome Telefone e
do tipo inteiro.
Docente: Ana Batista
33
Exemplo 2
Alter table Clientes
Modify telefone char (9);
Modifica na tabela clientes, o campo
Telefone passando a ser do tipo char
com 9 carateres.
Docente: Ana Batista
34
Exemplo 3
Alter table Clientes
drop telefone;
Modifica a tabela clientes, eliminando
o campo telefone.
Docente: Ana Batista
35
Atualizar dados numa tabela

Quando se fala em atualizar uma
base de dados, tal compreende 3
tipos de operações:



Inserir
Apagar
alterar
Docente: Ana Batista
36
Inserir dados numa tabela
Insert into nome_tabela
(lista de campos)
{values (valores) | select instrução};
Inserir
dados
na
tabela
indicada,
especificando esses dados a seguir à
palavra values ou obtendo-os através de
uma instrução select.
Docente: Ana Batista
37
NOTA


Após a indicação do nome tabela,
podemos indicar opcionalmente os
nomes dos campos, onde queremos
inserir os dados.
Se os campos não forem indicados,
supõe-se que vamos inserir dados em
todos os campos da tabela.
Docente: Ana Batista
38
Exemplo 1
Insert into Clientes
(Cliente, morada) values
(“Aníbal”, “Lisboa”),
(“Belmiro”, “Braga”),
(“Casimiro”, “Coimbra”);
Docente: Ana Batista
39
Tabela clientes
CodCli
Cliente
Morada
1
Anibal
lisboa
2
Belmiro
Braga
3
Casimiro
Coimbra
Docente: Ana Batista
40
Exemplo 2
Insert into Produtos values
(null, “portátil”, 400),
(null, “berbequim”, 50),
(null, “ms office”, 150);
Docente: Ana Batista
41
Tabela produtos
CodProd
Produto
preço
1
Portátil
400
2
Berbequim
50
3
Ms office
150
Docente: Ana Batista
42


No exemplo anterior, como não indicámos
os campos onde queríamos inserir dados,
temos de atribuir valores para todos os
campos da tabela.
Null significa que o 1º campo da tabela era
auto increment (codprod) e não podemos
mexer, por isso a forma é indicar o valor
null.
Docente: Ana Batista
43
Exemplo 3
Insert into Encomendas values
(null, 1,1,10),
(null, 2,1,2),
(null, 3,2,5),
(null, 1,3,1);
Docente: Ana Batista
44
Tabela encomendas
NEnc
Cod Cli
CodProd
Quant
1
1
1
10
2
2
1
2
3
3
2
5
4
1
3
1
Na tabela encomendas, o primeiro campo é Nenc é do tipo
auto_increment, o que impede de atribuir valores, por isso indicamos
o null para todos os dados correspondentes a esse código.
Quanto aos campos CodCli e CodProd como são chaves
estrangeiras, pois referem-se a chaves primárias de outras tabelas,
isto obriga a que os valores inseridos devam existir nas tabelas
iniciais, por forma a respeitar a integridade referencial.
Docente: Ana Batista
45
Apagar registos e alterar dados

O comando delete apaga registos
(linhas) por inteiro numa tabela.
Delete from nome_tabela
[where condição];
Docente: Ana Batista
46
Objetivo



Apagar todos os registos na tabela indicada,
podendo apagar toda a tabela (se não for
especificada uma condição) ou apenas o(s) registos
que correspondam à condição indicada.
Se a cláusula Where condição não for indicada,
serão apagados todos os registos (todos os dados)
da tabela.
Se Where condição for indicada, serão apagados
os registos que respeitem a condição indicada.
Docente: Ana Batista
47
Exemplo 1
Delete from produtos;
Efeito: todos os dados da tabela
produtos serão apagados.
Docente: Ana Batista
48
Exemplo 2
Delete from Produtos
Where produto=“Ms office”;
Efeito: apaga na tabela produtos os
registos (linhas) em que o campo
produto é igual a ms office.
Docente: Ana Batista
49
Comando update

A
alteração,
modificação
ou
atualização de dados em SQL, fazse com o comando update.
Docente: Ana Batista
50
Sintaxe - update
Update nome_tabela
Set campo1=expressão1
[, campo2=expressão2], [, …]
[where condição];
Alterar dados na tabela indicada, no(s)
campo(s) indicado(s), podendo essa alteração
abranger todos os dados de determinado
campo ou apenas o que correspondem a uma
condição indicada.
Docente: Ana Batista
51


A
expressão
campo1=expressão1
modificação a efetuar.
set
indica
a
É possível efetuar a vários campos
na mesma instrução, daí os
parêntesis retos que se seguem.
Docente: Ana Batista
52
Cláusula where


A cláusula Where condição é
opcional – isto implica o seguinte:
Se a cláusula where condição não
for usada, as alterações indicadas
nas
expressões
campo1=expressão1
afetarão
todos os dados do campo indicado
em set.
Docente: Ana Batista
53
Se
a cláusula where condição for
usada,
as
alterações
indicadas
afetarão apenas os registos que
respeitem a condição especificada.
Docente: Ana Batista
54
Exemplo 1
Update Produtos
Set Preco =Preco*1.05;
Esta instrução altera a tabela produtos, no campo
preço, para o resultado da expressão preco*1.05, ou
seja, produz um aumento de 5% em todos os preços
da tabela produtos.
Docente: Ana Batista
55
Exemplo 2
Update Produtos
Set preco=250
Where produto =“portátil”;
Neste caso, a alteração do preço para 250,
incide apenas sobre o produto que tem o nome
de portátil, uma vez que foi isso que foi
indicado na cláusula where.
Docente: Ana Batista
56
Comando Select


É o comando que permite efetuar
consultas a uma BD relacional com
SQL.
A sintaxe do comando select é, nas
suas versões mais complexas,
muito extensa, com muitas opções
e pode revestir-se de alguma
complexidade.
Docente: Ana Batista
57
Sintaxe - select
Select lista_campos
From tabela(s)
[where condição]
[group by lista_campos]
[having condição]
[order by lista_campos]
Efetuar consultas à
tabela(s) indicada,
baseadas em critérios
de filtragem,
agrupamentos e/ou
ordenação.
Docente: Ana Batista
58
Exemplos
Select *
from produtos;
Vai consultar todos
tabela produtos.
Docente: Ana Batista
os
dados
da
59
Select produto, preco
From produtos;
Vai apresentar os campos produto e
preço da tabela produtos.
Docente: Ana Batista
60
Operadores usados na cláusula
where

Operadores de comparação:
= igualdade
< menor que
> Maior que
<= menor ou igual
>= maior ou igual
<> Diferente de
Docente: Ana Batista
61

Outros operadores de comparação:
Between – compreendido entre
In – dentro de
Like – semelhante a
Is (not) null – é (não é) valor nulo
Docente: Ana Batista
62

Operadores lógicos:
And – e
Or – ou
Not - não
Docente: Ana Batista
63
Exemplos
Select *
From produtos
Where preco>=200 and preco<=400;
Select *
From produtos
Where preco between 200 and 400;
Docente: Ana Batista
64
Select *
From produtos
Where preco= 100 or preco=200;
Select *
From produtos
Where preco in (100, 200);
Docente: Ana Batista
65
Select *
From produtos
Where produto like “P%”;
Vai buscar todos os dados de
produtos, cujo nome de produto
comece com a letra P.
Docente: Ana Batista
66
Select *
From produtos
Where produto like “%office”;
Vai buscar tds os dados de Produtos,
cujo produto tenha a palavra office no
seu nome.
Docente: Ana Batista
67
Select *
From produtos
Order by preco desc;
Vai buscar todos os dados da tabela
produtos ordenando o preço por
ordem descendente.
Docente: Ana Batista
68
Select *
From produtos
Where preco>200
Order by produto;
Ordena
por
ordem
alfabética
crescente, os produtos cujos preços
são maiores que 200.
Docente: Ana Batista
69
Funções de cálculos e agregações
Função
Significado
Count
Devolve o nº de linhas/registos encontrados
Sum
Devolve a soma de todos os valores encontrados
no campo indicado
Avg
Devolve a média
Max
Devolve o maior valor
Min
Devolve o menor valor
Docente: Ana Batista
70
Tabela Produtos
CodPro
Produto
Preco
1
Portátil
400
5
Portátil
200
3
Ms Office
150
4
Impressora
100
6
Impressora
50
2
Berbequim
50
Docente: Ana Batista
71
Função Contar
Select count (*) As Total
From Produtos;
Vai contar quantas linhas existem na
tabela Produtos e devolve o resultado
através do campo total.
Total
6
Docente: Ana Batista
72
Exemplo
Select count (distinct preco)
From produtos;
Devolve o número de preços distintos
no campo preço, neste caso, o
resultado seria 5; o preço 50 só é
contado uma vez.
Docente: Ana Batista
73
Funções Sum e Avg - exemplo
Select avg(preco) as Media
From produtos;
O resultado será a média do preço
dos produtos.
Docente: Ana Batista
74
Select Sum(preco)
From produtos
Where produto like “portátil”;
O resultado será a soma dos preços dos
produtos correspondentes a portáteis.
Resultado: 600
Docente: Ana Batista
75
Funções max e min
Select max(preco)
From produtos;
Seleciona o maior valor encontrado.
Docente: Ana Batista
76
Select min(preco)
From produtos
Where produto like “impressora”;
O resultado será o menor valor
encontrado, tendo em conta os
produtos de nome impressora.
Docente: Ana Batista
77
Claúsulas group by e having

A claúsula Group by é usada para
agrupar
dados
que
contenham
características semelhantes.

Normalmente, é usada em conjugação
com as funções de agregação (count,
sum, avg, max e min), para obter
cálculos por grupos.
Docente: Ana Batista
78
Exemplo 1
Select produto, count(*)
From produtos
Group by produto;
Vamos obter uma listagem com os
nomes dos diferentes produtos e com
a indicação de quantos registos de
cada produto existem.
Docente: Ana Batista
79
Resultado do exemplo anterior:
Produto
Count(*)
Berbequim
1
Impressora
2
Ms Office
1
Portátil
2
Docente: Ana Batista
80
Exemplo 2
Select produto, max(preco)
From produtos
Group by produto;
Iremos obter o preço mais elevado
dentro de cada grupo de produtos
com o mesmo nome.
Docente: Ana Batista
81

Quando se utiliza a cláusula group by,
também podemos acrescentar a cláusula
having.

A cláusula having aplica-se aos grupos de
registos,
agrupados
com
group
by,
apresentando apenas qs que correspondem
à condição indicada com having.
Docente: Ana Batista
82
Exemplo 3
Select produto, max(preco)
From produtos
Group by produto
Having max(preco) > 100;
Aqui iremos obter o preço mais elevado de
cada grupo de produtos com o mesmo nome,
mas queremos obter os grupos de produtos
que têm um preço máximo superior a 100.
Docente: Ana Batista
83
Resultado do exemplo 3
Produto
Max(preco)
Ms Office
150
Portátil
400
Docente: Ana Batista
84
Mais exemplos de comandos…
Select *
From clientes, encomendas;
Vai buscar todos os dados, das 2
tabelas.
Docente: Ana Batista
85
Select *
From clientes, encomendas
Where clientes.codcli=encomendas.cod.cli;
Esta igualdade na cláusula where vai fazer com
que sejam apresentados os dados dos clientes,
com os dados das encomendas, mas apenas
daqueles em que os valores dos campos codcli
coincidem nas 2 tabelas.
Docente: Ana Batista
86
Expressão inner join
O
exemplo
anterior
pode
ser
substituído pela expressão inner join
na cláusula from (deixando ser
necessário utilizar a cláusula where).
Select *
From clientes inner join encomendas on
Clientes.codcli=encomendas.codcli;
Docente: Ana Batista
87