Upload
internet
View
107
Download
1
Embed Size (px)
Citation preview
Banco de dadosBaseado no material do Professor Raul Paradeda
Revisão• Vimos na aula passada como criar um banco de
dados e realizar alterações e restrições de integridade.
SQL• Crie um banco de dados com o seu nome.• Crie uma tabela chamada alunos.• Crie outra tabela chamada cursos.• Crie outra tabela chamada alu_cur.
• Os atributos das duas primeiras tabelas você escolhe, apenas temos que ter chaves primárias em cada uma.
• Na tabela alu_cur será apresentado apenas chaves estrangeiras das duas primeiras tabelas.
SQL - Inserção• Inserir de elementos (tuplas) em uma relação (tabela).• Sintaxe:INSERT INTO tabela [<lista atributos>] VALUES (<lista de valores atômicos>)
• Exemplo:INSERT INTO alunos(nome_alu, sexo) VALUES (‘Johnny’, ‘m’);INSERT INTO alunos VALUES (1, ‘Allan’, 26, ‘1983-10-04’,‘m’);INSERT INTO alunos VALUES (2, ‘André’, 24, ‘1985-11-10’,‘m’);INSERT INTO alunos VALUES (3, ‘Renata’, 20, ‘1989-02-24’,‘f’);INSERT INTO alunos VALUES (4, ‘Lucas’, 26, ‘1983-01-07’,‘m’);
Caso este parâmetro seja a chave primária, normalmente tem-se como auto-increment, não sendo necessário seu preenchimento.
SQL - Inserção• Podemos omitir uma ou mais colunas da relação destino. • Toda tupla inserida terá um valor nulo em cada posição de
coluna omitida.• Sintaxe:INSERT INTO <tabela>(<atributos>) VALUES (<valores>);• Exemplo:INSERT INTO alunos(nome_alu, sexo) VALUES (‘Johnny’, ‘m’);
SQL - Atualização• O comando UPDATE modifica o valor de atributos de uma ou mais
tuplas.• Sintaxe:
UPDATE <tabela>SET <lista_atributos com atribuições de valores>[WHERE <condição>]
• Omitir a cláusula WHERE implica em modificar todas as tuplas da relação.
SQL - Atualização• UPDATE alunos SET id_alu=10 WHERE id_alu=1;
• UPDATE alunos SET id_alu=1 WHERE nome_alu='Allan' ;
• UPDATE alunos SET data_alu=curdate() where data_alu='0000-00-00' ;
• UPDATE alunos SET sexo='m' ;
SQL - Remoção• O comando DELETE remove tuplas de uma relação.• Sintaxe:
DELETE FROM tabela[WHERE condição]
• Omitir a cláusula WHERE implica em remover todas as tuplas da relação.• A relação permanece no BD como uma relação vazia.
SQL - Remoção• DELETE FROM alunos WHERE id_alu=1;
• DELETE FROM alunos WHERE nome_alu=‘Allan’;
• DELETE FROM alunos;
SQL - Seleção• A sintaxe básica do comando de seleção é:
SELECT <lista atributos>FROM <lista de tabelas>WHERE <condição>Onde: • <lista atributos> é uma lista de nomes de atributos cujos
valores são para ser recuperados pela consulta.• <lista de tabelas> é uma lista de nomes de relações requeridas
para processar a consulta.• <condição> é uma expressão (Booleana) que identifica as
tuplas a serem recuperadas pela consulta, se não houver será selecionada todas as tuplas da relação.
SQL - Seleção• É usada para listar os atributos desejados no resultado da
consulta, exemplo:SELECT nome, cidade FROM Fornecedores;• Será listado apenas o nome e a cidade de todos os fornecedores.
• O resultado de uma consulta SQL é também uma relação.
SQL - Seleção• A seleção permite duplicação na consulta, exemplo:
SELECT cidade FROM Fornecedores;• Será listado a cidade de todos os fornecedores da relação,
sendo os valores duplicados, caso houver.
SQL - Seleção• Para remover a duplicação é utilizado o parâmetro DISTINCT
na consulta, exemplo:
SELECT DISTINCT cidade FROM Fornecedores;
SQL - Seleção• A cláusula SELECT pode conter expressões aritméticas
envolvendo as operações de adição (+), subtração (-), multiplicação (*) e divisão (/).
SELECT nome, status*2 FROM Fornecedores;
SQL - Seleção• Para renomear atributos na seleção é utilizado o parâmetro
AS, exemplo:SELECT id_fornecedor, status AS situacao FROM
fornecedores;
SQL - Seleção• A cláusula FROM permite que se combine informações de
duas relações, exemplo:Select fornecedores.id_fornecedor, pecas.nome,
fornecedores.cidade, pecas.cidade from fornecedores, pecas;
• As linhas são obtidas combinando-se cada linha da primeira tabela com todas as linhas da segunda tabela:• O esquema resultante é a concatenação dos esquemas
das duas relações fornecidas como argumento.
SQL - Seleção
x
y
a
b
c
a
a
b
b
c
c
x
y
x
y
x
y
SQL - Seleção• A eliminação de tuplas incoerentes pode ser feita através do
parâmetro WHERE.Select fornecedores.id_fornecedor, peças.nome,
fornecedores.cidade, peças.cidade from fornecedores, peças where fornecedores.cidade=peças.cidade;
SQL - Seleção
SQL - SeleçãoSelect fornecedores.id_fornecedor, peças.nome,
fornecedores.cidade, peças.cidade from fornecedores, peças where fornecedores.cidade=“Natal”;
SQL - Seleção• Encontre todos os identificadores dos fornecedores
localizados na cidade de Pelotas:select id_fornecedorfrom fornecedoreswhere cidade=“Pelotas”;• Encontre todas as matriculas dos alunos com nome de João:Select matriculafrom alunoswhere nome_aluno=“João”;
SQL - Seleção• A cláusula WHERE usa os conectivos lógicos AND, OR e NOT:• Encontre o nome de todas as peças fornecidas por Jones para o
Projeto Classificador
select distinct peças.nomefrom fornecedores, peças, projeto, fornecedor_projeto_pecas where fornecedores.nome=“Jones” AND
projeto.nome=“Classificador”;
SQL - Seleção• Encontre a matricula e o nome de todos os alunos com o
nome de João ou Maria:
select matricula, nome_alunofrom alunoswhere nome_aluno=“João” OR nome_aluno=“Maria”;
SQL - Seleção• Na condição é permitido usar expressões aritméticas de
comparação (=, <>, <, <=, >=, >)• Encontre o nome de todos os fornecedores que fornecem mais de
300 peças para um só projeto:
select distinct fornecedores.nome_fornecedorfrom fornecedores, fornecedor_proj_peças where fornecedor_proj_peças.qde>300;
SQL - Seleção• Encontre todas as informações de todos os alunos do sexo
masculino:
select *from alunoswhere sexo=‘m’;
SQL - Seleção• Operador BETWEEN permite que um atributo seja
comparado dentro de uma faixa especificada.• É permitido usar expressões aritméticas de comparação (=,
<>, <, <=, >=, >)• Encontre o nome de todos os alunos que pagam
mensalidades entre 300 a 700 reais• select nome_aluno
from alunoswhere mensalidade between 300 AND 700;
SQL - Seleção• Operador LIKE permite a comparações em seqüências de
caracteres. • Padrões são descritos usando dois caracteres especiais:• percentual (%) casa com qualquer conjunto de caracteres, exemplo:
select * from fornecedores where cidade LIKE "P%";
SQL - Seleção• sublinhado (_). O caractere “_” casa com qualquer caractereselect * from fornecedores where cidade LIKE "P_l_t_s";
select * from fornecedores where cidade LIKE "P_l%";
SQL - Seleção• Operador IS NULL verificar se o atribulo é nulo.• Encontre os nomes de todos os projetos cuja cidade não tenha sido
informada na relação
select nome from projeto where cidade is null;
• Pode-se usar IS NOT NULLselect nome from projeto where cidade is not null;
SQL - Seleção• Operador IN permite que um atributo seja comparado com
um conjunto.select nome from peças where cor IN(‘Vermelha’, ‘Azul’);
select * from fornecedores where cidade IN ("Pelotas","Natal");
SQL - Seleção• O operador IN provê um mecanismo para o aninhamento de
subconsultas:
select matriculafrom alunos where nome IN (
select nome from alunos where nome=“J%”
);
SQL - Seleção• A função EXISTS também provê aninhamento de
subconsultas.• É usada para checar se o resultado de uma consulta
aninhada é vazia ou não.select * from alunoswhere exists (
select * from pagamento where pagamento=“ok”
);
Se existir um aluno com o pagamento OK será apresentado todos os alunos.
SQL - Seleção• Note que quando você está utilizando o operador EXISTS,
não importa o que o comando SELECT interno irá buscar interessa apenas se ele retorna ou não linhas:• select exists (select * from fornecedores where cidade
in ("Pelotas","Natal"));
SQL - Seleção• A cláusula SELECT pode conter funções que operam sobre
uma coleção de valores de uma coluna de uma relação e retorna um valor.• O SQL fornece 5 funções embutidas:• COUNT: número de tuplas ou valores.• SUM: soma os valores de uma coluna.• AVG: calcula a média dos valores de uma coluna.• MAX: identifica o maior valor de uma coluna.• MIN: identifica o menor valor de uma coluna.
• Todas as funções, exceto count(*), ignoram as tuplas com valores nulos.
SQL - Seleção• Quantas relações aluno, curso, disciplina temos?• select count(*) from aluno_curso_disciplina;
• Quantos projetos ativos temos?• select count(distinct pr)
from fornecedor_projeto_pecas;• Quantas peças são fornecidas ao todo?• select sum(qde) from fornecedor_projeto_pecas;
SQL - Seleção• Em médias quantas peças são fornecidas por um determinado
fornecedor em um certo projeto?• select avg(qde) from fornecedor_projeto_pecas;
• Qual é a maior quantidade de peças fornecidas por um único fornecedor a um só projeto?• select max(qde) from fornecedor_projeto_pecas;
SQL - Seleção• Qual é a menor quantidade de peças fornecidas por um único
fornecedor a um só projeto?• select min(qde) from fornecedor_projeto_pecas;
SQL - Seleção• ORDER BY é usada para ordenar as linhas selecionadas pelo
comando de seleção.• Crescente:
select cidade,statusfrom fornecedoresorder by status ASC;• Decrescente:
select cidade,statusfrom fornecedoresorder by status DESC;
SQL - Seleção• GROUP BY é usada para agrupar linhas com base em valores
de determinadas colunas:select f, p, sum(qde) as qdefrom fornecedor_projeto_pecasgroup by f, p;
select cidade,statusfrom fornecedoresgroup by status,cidade;
SQL - Seleção• A cláusula GROUP BY deve vir antes da ORDER BY e depois
do WHERE
select cidade,statusfrom fornecedoresgroup by status,cidade; order by cidade;
SQL - Atividade• Criar um banco de dados com seu nome e uma tabela
chamada acessos.• Nesta tabela teremos quatro atributos:• id_acesso: chave primária inteira não nula auto
incrementável;• dia_acesso: atributo inteiro não nulo;• mes_acesso: atributo inteiro não nulo;• ano_acesso: atributo inteiro não nulo;
SQL - Atividade• SQL da tabela:
CREATE TABLE acessos (id_acesso INT NOT NULL AUTO_INCREMENT ,dia_acesso INT NOT NULL ,mes_acesso INT NOT NULL ,ano_acesso INT NOT NULL ,PRIMARY KEY (id_acesso)) ;
SQL - Atividade• Realizar a inserção de 10 tuplas na tabela acessos com
valores diferentes para mês_acesso e ano_acesso:• Ex:INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso)VALUES (15, 03,2009) ;INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso)VALUES (13, 02,2009) ;INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso)VALUES (15, 01,2009) ;INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso)VALUES (10, 02,2009) ;