SQL - ESEV

S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
SQL (Structured Querie Language)
SQL é mais que uma linguagem de interrogação estruturada. Inclui características para a definição
da estrutura de dados, para alterar os dados de uma base de dados, e para especificar esquemas de
segurança. Estas características agrupam-se do seguinte modo:
DDL – Data Definition Language
DML – Data Manipulation Language
DCL – Data Control Language
Numa base de dados relacional, toda a informação está logicamente organizada em tabelas.
Podemos considerar a existência de dois tipos de tabelas numa base de dados:
•
as tabelas base que efectivamente estão armazenadas num suporte físico da base de
dados
•
as tabelas virtuais que só existem em resultado da execução de um conjunto de
comandos de consulta, executados sobre as tabelas base.
A palavra inglesa querie designa uma consulta à base de dados e consiste na execução de uma ou
mais operações sobre tabelas.
As queries ou consultas podem ser executadas sobre as tabelas base ou sobre as tabelas virtuais
que resultam de outras consultas.
Do ponto de vista lógico, o resultado de uma consulta (querie) é uma tabela. A diferença entre uma
tabela base e uma tabela virtual é basicamente a seguinte: as tabelas base existem fisicamente como
estruturas de dados; as tabelas virtuais (resultados de queries) existem apenas como um conjunto de
comandos que permitem extrair informação da base de dados.
Pág. 1 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Noções Elementares
São válidos os seguintes operadores aritméticos:
•
Soma
+
•
Subtracção
-
•
Multiplicação
*
•
Divisão
/
Os operadores lógicos são: AND, OR e NOT.
Predicados – Um predicado é uma condição que pode ser realizada para produzir um valor
verdadeiro, falso ou desconhecido. São eles:
•
Comparação (=, <, >, <>, <=, =>)
•
Entre (... BETWEEN ... AND ..., IN, NOT IN)
•
LIKE
•
NULL
•
Quantificadores (ALL, SOME, ANY)
•
EXISTS, NOT EXISTS
As cláusulas são expressões realizadas em tabelas que se usam para derivar tabelas, tais como
FROM, WHERE, GROUP BY, HAVING e ORDER BY.
Pág. 2 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Linguagem de Manipulação de Dados (DML – Data Manipulation Language)
É utilizada para efectuar operações de selecção, ordenação, cálculo de informação guardada em
tabelas, entre outras.
Se lect Q ue r ie s
Cláusula SELECT
Permite extrair informação de uma Base de Dados (DB – Data Base).
Sintaxe:
SELECT <lista_campos>
FROM <lista_tabelas>
•
Consulta todos os autores da tabela Autores:
SELECT Autor FROM Autores
•
Consulta todos os campos da tabela Autores:
SELECT * FROM Autores
•
Consulta todos os autores visualizando o dobro da sua idade:
SELECT Autor, Idade * 2 AS [Idades]
FROM Autores
Cláusula WHERE
Permite estabelecer condições entre campos da tabela(s) ou até mesmo envolvendo input’s
digitados pelo utilizador.
Sintaxe:
SELECT <lista_campos> FROM <lista_tabelas>
WHERE <critério>
) as listas são separadas por vírgulas
) se desejarmos seleccionar todos os campos usamos *
Pág. 3 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
•
Uma consulta à tabela no sentido de obter apenas os elementos relativos às editoras
situadas na cidade de Lisboa:
SELECT Pubid, Nome AS [Nome Editora], Morada, Cod_Postal, Cidade, Telefone
FROM Editoras
WHERE Cidade=’Lisboa’
•
Consulta o nome e morada de todas as editoras da cidade de Lisboa:
SELECT Nome, Morada
FROM Editoras
WHERE Cidade=’Lisboa’
•
Consulta o Nome, Região e Cidade quando a região toma o valor ‘CA’ e a cidade é Lisboa:
SELECT Nome, Região, Cidade
FROM Editoras
WHERE Região=’CA’ AND Cidade=’Lisboa’
Utilizar na cláusula WHERE – LIKE
Destina-se a comparar strings com padrão.
•
Consulta os nomes de todos os autores que tenham Lopes no nome:
SELECT Autor
FROM Autores
WHERE Autor LIKE ‘*Lopes*’
•
Consulta o(s) código(s) e descrição(ões) do(s) produto(s) quando na descrição está
envolvida a palavra sumos:
SELECT Prod_Cod, Prod_Descrição
FROM Produtos
WHERE Prod_Descrição LIKE ‘*sumos*’
Pág. 4 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Utilizar na cláusula WHERE – BETWEEN … AND
•
Consulta todos os nomes e cidades onde o campo Pubid toma valores entre 10 e 20:
SELECT Nome, Cidade
FROM Editoras
WHERE Pubid BETWEEN 10 AND 20
Utilizar na cláusula WHERE – IN
•
Consulta todos os nomes e cidades onde o campo região toma valores no conjunto (‘NY’,
’CA’, ’AK’):
SELECT Nome, Cidade
FROM Editoras
WHERE Região IN (‘NY’, ’CA’, ’AK’)
Cláusula WHERE na “ligação” de duas ou mais tabelas
Sintaxe:
SELECT <tabela1.colunaA>, <tabela2.colunaA>
FROM <tabela1>, <tabela2>
WHERE <tabela1.colunaA> = <tabela2.colunaA>
•
Consulta todas as linhas da tabela Títulos e da tabela Autores com o mesmo campo Au_ID.
Se existir algum título para o qual não foi introduzido código de um Autor, o mesmo não
aparecerá (se o código do autor não for chave primária).
SELECT Titulos.Titulo, Autores.Autor
FROM Titulos, Autores
WHERE Titulos.Au_ID = Autores.Au_ID
Esta operação designa-se por INNER JOIN, como se verá na devida altura.
Pág. 5 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Cláusula AS
Permite alterar o cabeçalho (titulo) que irá aparecer na listagem obtida.
SELECT Titulos.Titulo AS [Titulo do Livro], Autores.Autor AS [Autor do Livro]
FROM Titulos.Autores
WHERE Titulos.Au_ID = Autores.Au_ID
Funções agregadas
Devolve o número de linhas em que
COUNT(<nome_campo>)
o campo <nome_campo> não é nulo
MAX(<nome_campo>)
Devolve o maior valor para o campo <nome_campo>
MIN(<nome_campo>)
Devolve o menor valor para o campo <nome_campo>
SUM(<nome_campo>)
Devolve a soma dos conteúdos do campo <nome_campo>
AVG(<nome_campo>)
Devolve a média dos conteúdos do campo <nome_campo>
FIRST(<nome_campo>)
Devolve o valor do primeiro registo da tabela ou Querie
LAST(<nome_campo>)
Devolve o valor do último registo da tabela ou Querie
•
Devolve o número de elementos da tabela Produtos:
SELECT COUNT(Prod_Codigo) AS [Total]
FROM Produtos
Se não for mencionado o Nome da coluna [Total], é por defeito Expr1000. Como tal, é conveniente
atribuir um título à coluna.
Pág. 6 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
•
Devolve o menor, o maior e o preço médio dos livros:
SELECT COUNT(Unidades) AS [Nº Elementos],
MIN(Preço_Unidades) AS Min,
MAX(Preço_Unidades) AS Max,
AVG(Preço_Unidades) AS Média
FROM Preços_Livros
•
Consulta os livros com menor preço
SELECT titulo, anopublicação, preço
FROM titulos, preços
WHERE titulos.codpreco = precos.codpreco AND
Preco = (SELECT Min(preco)
FROM precos)
Cláusula INNER JOIN
Estabelece ligação entre duas ou mais tabelas através de campos chave.
SELECT titulos.titulo, autores.autor
FROM titulos,
autores.titulos INNER JOIN autores ON titulos.au_ID = autores.au_ID
Poderíamos escrever o mesmo comando com uma cláusula WHERE (talvez mais prático).
SELECT titulos.titulo, titulos.ISBN, autores.autor, editoras.nome
FROM titulos, autores, editoras
WHERE editoras.editID = titulos.editID
AND titulos.au_ID = autores.au_ID
Pág. 7 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Cláusulas LEFT JOIN e RIGHT JOIN
Uma cláusula RIGHT JOIN inclui todos os registos da segunda tabela (à direita), mesmo que não
exista nenhuma relação com a primeira tabela (à esquerda).
•
Listar todas as editoras, mesmo que não tenham nenhum titulo publicado:
SELECT titulos.titulo, editoras.nome
FROM titulos, editoras
titulos RIGHT JOIN editoras ON titulos.editID = editoras.auID
A cláusula LEFT JOIN inclui todos os registos da primeira tabela (à esquerda), mesmo que não
exista nenhuma relação com a segunda tabela (à direita).
•
Listar todos os titulos, mesmo os que não tenham editoras:
SELECT titulos.titulo, editoras.nome
FROM titulos, editoras
titulos LEFT JOIN editoras ON titulos.editID = editoras.auID
Predicado ALL
ALL – Se não incluir um dos predicados referidos, ALL é assumido e todos os registos que verificam
as condições são devolvidos.
Dois exemplos equivalentes:
SELECT ALL
FROM titulos, autores
WHERE editoras.editID = titulos.editID
SELECT *
FROM titulos, autores
WHERE editoras.editID = titulos.editID
Pág. 8 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Predicado DISTINCT
Esta cláusula omite todos os registos que contêm dados duplicados nos campos seleccionados. Por
outras palavras, se o querie contiver um campo com o mesmo valor em mais do que uma linha, a cláusula
DISTINCT elimina todos os duplicados.
•
Lista as editoras que têm pelo menos um titulo publicado:
SELECT editoras.nome
FROM titulos, editoras
WHERE editoras.editID = titulos.edit.ID
Este querie devolve tantas linhas quantos os títulos que existem na tabela de títulos. Para eliminar a
repetição do nome das editoras, usa-se:
SELECT DISTINCT editoras.nome
FROM titulos, editoras
WHERE editoras.editID = titulos.edit.ID
Predicado DISTINCTROW
Omite dados baseados em registos duplicados, mas não referentes a campos duplicados.
SELECT DISTINCTROW titulos.titulo, titulos.anoedicao,
autores.autor, editoras.nome AS Editora
FROM editoras, titulos.(editoras RIGHT JOIN titulos
ON editoras.editID = titulos.editID) LEFT JOIN autores
ON titulos.au_ID = autores.au_ID
WHERE (editoras.nome = [Insira Nome])
ORDER BY titulos.titulo
Observa que na sexta linha existe [Insira Nome]. Isto significa que é solicitado ao utilizador que
insira um nome para depois efectuar-se a comparação. Deste modo, quando se utiliza uma string entre
parêntesis rectos que não seja o nome de um campo, o SQL solicita ao utilizador que insira um valor (string
ou não) pelo teclado.
Pág. 9 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Cláusula ORDER BY
•
Permite ordenar a listagem pelo campo referido, por ordem crescente:
SELECT au_ID, autor
FROM autores
ORDER BY autor
•
Consulta as regiões e cidades por ordem descendente e ascendente respectivamente:
SELECT regiao, cidade
FROM editoras
ORDER BY regiao DESC, cidade ASC
Cláusulas TOP n e TOP n PERCENT
Estas cláusulas servem para limitar o número de registos num conjunto de resultados.
TOP n – Devolve os primeiros n registos.
Quando utilizamos a cláusula TOP por vezes recorre-se ao uso da cláusula ORDER BY.
SELECT TOP 5 *
FROM precos_livros
ORDER BY unidades DESC
TOP n PERCENT – Devolve os primeiros n porcentos de registos.
SELECT TOP 5 PERCENT *
FROM preco_livros
ORDER BY unidades DESC
Pág. 10 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Cláusula GROUP BY
Agrupar os resultados das queries segundo um determinando critério.
GROUP BY <coluna1>, <coluna2>, ..., <colunaN>
•
Consulta o número de elementos da tabela Produtos para cada categoria de Produto:
SELECT prod_categoria AS Categoria, COUNT(prod_categoria) AS [Nº Elementos]
FROM Produtos
GROUP BY prod_categoria
A cláusula GROUP BY especifica o modo como queremos agrupar o resultado da função agregada.
•
Consulta o menor preço, maior preço e preço médio dos produtos por categoria:
SELECT prod_categoria AS Categoria,
COUNT(prod_categoria) AS [Nº Elementos],
MIN(prod_preco) AS Min,
MAX(prod_preco) AS Max,
AVG(prod_preco) AS Média
FROM Produtos
GROUP BY prod_categoria
SELECT ISBN AS Código,
COUNT(au_ID) AS [Nº Elementos]
FROM Titulos
GROUP BY ISBN
Pág. 11 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Cláusula HAVING
Podemos utilizar a cláusula HAVING para aplicar um filtro ao resultado de um querie ao contrário da
cláusula WHERE que aplica um filtro a toda(s) a(s) tabela(s) envolvida(s).
Vejamos o mesmo querie com a aplicação de um filtro, para limitar as categorias de produtos a
bolachas e aperitivos.
SELECT prod_categoria AS Categoria,
COUNT(prod_categoria) AS [Nº Elementos],
MIN(prod_preco) AS Min,
MAX(prod_preco) AS Max,
AVG(prod_preco) AS Média
FROM Produtos
GROUP BY prod_categoria
HAVING prod_categoria IN [“Bolachas”,”Aperitivos”]
Podemos formatar os resultados numéricos obtidos (Visual Basic):
SELECT prod_categoria AS categoria,
FORMAT(COUNT(prod_categoria),”000”) AS [Nº Elementos]
FORMAT(MIN(prod_preco),”$00”) AS Min,
FORMAT(MAX(prod_preco),”$00”) AS Max,
FORMAT(AVG(prod_preco),”$00”) AS Média
FROM Produtos
GROUP BY prod_categoria
Vejamos exemplos que involvem mais que uma tabela:
•
Consulta o nome dos autores e o númerode titulos publicados pelos mesmos:
SELECT autores.autor, COUNT(titulos.titulo)
FROM titulos, autores
WHERE titulos.au_ID = autores.au_ID
GROUP BY autores.autor
Pág. 12 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
•
Consulta os autores que escrevam mais de um titulo
SELECT autores.autor, COUNT(titulos.titulo)
FROM titulos, autores
WHERE titulos.au_ID = autores.au_ID
GROUP BY autores.autor
HAVING COUNT(titulos.titulo)>1
Revisão:
SELECT titulos.titulo, titulos.anoedicao, autores.autor, editoras.nome AS Editora
FROM editoras, titulos, autores, (editoras RIGHT JOIN titulos
ON editoras.editID = titulos.editID) LEFT JOIN autores
ON titulos.au_ID = autores.au_ID
ORDER BY titulos.titulo
) Observe que o nome de um campo quando possui um ou mais espaços, deve estar envolvido entre
parêntesis rectos.
Pág. 13 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
Nomes Alternativos para Tabelas
Um nome alternativo para uma tabela deve ser definido na cláusula FROM. O nome alternativo
pode depois ser usado tanto na cláusula SELECT como na cláusula WHERE. Por exemplo: para abreviar o
nome da tabela autores para au, escreve-se:
SELECT au.autor, COUNT(titulos.titulo)
FROM titulos, autores AS au
WHERE titulos.au_ID = au.au_ID
AC TION QU ER IES
As Action Queries não devolvem um conjunto de registos. Apenas modificam o conteúdo de uma
ou mais tabelas.
Comando INSERT INTO
O comando INSERT serve para inserir novas linhas, ou parte de novas linhas, numa tabela.
Sintaxe:
INSERT INTO <destino> [IN <externaldatabase>][(<campo1>[,<campo2>...)]]
SELECT [<origem>.]<campo1>[,<campo2>[,...]
FROM <expressão>
INSERT INTO <destino> [(<campo1>[,<campo2>,...])]
VALUES (<valor1>[,<valor2>,...])
Existem duas maneiras principais de o utilizar:
•
Selecciona todos os registos da tabela Novo Cliente e adiciona-o à tabela Clientes:
INSERT INTO Clientes
SELECT [Novo Cliente].*
FROM [Novo Cliente]
Pág. 14 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
•
Selecciona todos os registos da tabela Ajudantes que verificam a condição “DataSalario <
Now()-30” e adiciona-os à tabela empregados:
INSERT INTO Empregados
SELECT Ajudantes.*
FROM Ajudantes
WHERE [Data Salário] < Now() - 30
•
Insere um registo na tabela autores co os valores especificados:
INSERT INTO autores(au_ID, autor, idade)
VALUES (11,”Francisco Manuel”,30)
Outro processo equivalente será:
INSERT INTO autores(au_ID, autor, idade)
SELECT 11, “Francisco Manuel”, 30
Actualizar Registos
Usa-se a cláusula UPDATE para actualizar um registo numa tabela.
Sintaxe:
UPDATE <tabela>
SET <coluna1> = <valor1>
SET <coluna2> = <valor2>
..............
SET <colunaN> = <valorN>
WHERE <critério>
•
Aumenta os preços dos produtos da categoria “Bebidas” em 3%:
UPDATE produtos
SET prodpreco = 1.03 * prodpreco
WHERE prodcategoria = ”Bebidas”
A cláusula especifica a operação a efectuar nas colunas.
Pág. 15 de 16
S.Q.L. (Structured Query Language)
Escola Secundária de Emídio Navarro 2001/2002
Estruturas, Tratamento e Organização de Dados
•
Atribui ao campo relatorio o valor 5 em todos os registos onde a condição relatorio = 2 se
verifica:
UPDATE Empregados
SET relatorio = 5
WHERE relatorio = 2
Apaga Registo(s)
A cláusula DELETE é utilizada para eliminar registos.
•
Remover os registos cujos empregados sejam do tipo “Provisório”
DELETE *
FROM empregados
WHERE tipo = “Provisório”
•
Remover todos os empregados e os seus registos de (remover numa relação do lado N)
DELETE pagamentos.*
FROM empregados, pagamentos, empregados INNER JOIN pagamentos
ON empregados.empregadoID = pagamento.empregadoID
WHERE tipo = “Provisório”
) Nota: O comando DELETE apaga o registo e não valores isolados da tabela.
Pág. 16 de 16