View
59
Download
2
Category
Preview:
DESCRIPTION
SQL Structured Query Language. Juliano Brito da Justa Neves PESCD – Programa de Estágio Supervisionado de Capacitação Docente. SQL. Linguagem padrão para SGBDs relacionais Motivo pelo grande sucesso dos SGBDs relacionais Linguagem de Definição de Dados (DDL) - PowerPoint PPT Presentation
Citation preview
SQLStructured Query Language
Juliano Brito da Justa Neves
PESCD – Programa de Estágio Supervisionado de Capacitação Docente
SQL
• Linguagem padrão para SGBDs relacionais– Motivo pelo grande sucesso dos SGBDs
relacionais
• Linguagem de Definição de Dados (DDL)
• Linguagem de Manipulação de Dados (DML)
Esquema
• Agrupar tabelas e outros elementos (constraints, views...) que pertencem a mesma aplicação de banco de dados
CREATE SCHEMA <esquema> AUTHORIZATION <autorização> ;
Esquema
• Esquemas no Oracle– Criar várias tabelas e views e dar permissões
em uma única transação.
CREATE SCHEMA AUTHORIZATION <esquema> [create_table, create_view, grant]+ ;
Esquema
• Esquemas no Oracle– O CREATE SCHEMA não cria realmente um
esquema. O esquema é automaticamente criado quando um usuário é criado.
• Vamos utilizar um dos usuários previamente criados.
Oracle
• Conexão com o SGBD Oracle– Putty– SSH: falcon.comp.ufscar.br– Login: Pessoal de cada um– No prompt do linux:
• sqlplus• Login: compX X número de 1 a 20• Senha: igual ao login
Estudo de Caso
EQUIPE(#Codigo_Equipe,Nome_Equipe,Cidade,Estado)
JOGADOR(#Codigo_Jogador,Nome_Jogador,Posição_Jog,#Codigo_Equipe)
PARTIDA(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)
JOGA(#Codigo_Jogador,#Codigo_Partida,Numero_Gols)
Tabelas
CREATE TABLE [esquema.]tabela (coluna1 tipo_dado [DEFAULT expr]
[constraint_coluna],...colunaN tipo_dado [DEFAULT expr]
[constraint_coluna],[constraint_tabela]);
Tipos de Dados• Integer, Float, Real...• Char (n)• Varchar2 (n)• Clob• Long• Blob• Raw e Long Raw• Number (p,e)• Date• Timestamp• Interval Year (p) to month• Interval Day (dp) to second (sp)
Constraints
• Grupo 1– NOT NULL– Unique
• Grupo 2– Check– Primary key– Foreign key
CONSTRAINT nome tipo exprconstraint cod Primary Keyconstraint fcod Foreign Key references tabela(coluna)constraint chk Check (uf in (‘SP’, ‘MG’))
Exemplo
EQUIPE
(#Codigo_Equipe,Nome_Equipe,Cidade,Estado)
CREATE TABLE equipe (
codigo_equipe INTEGER constraint equipe_pk Primary Key,
nome_equipe Varchar2(20) NOT NULL,
cidade Varchar2(10),
estado Varchar2(10)
);
Exemplo
JOGADOR
(#Codigo_Jogador,Nome_Jogador,Posição_Jog,#Codigo_Equipe)
Codigo_jogador INTEGER
Nome_jogador Varchar2(20)
Posicao_jog Varchar2(15)
Codigo_Time INTEGER
ExemploJOGADOR(#Codigo_Jogador,Nome_Jogador,Posição_Jog,Codigo_equipe)
CREATE TABLE jogador (codigo_jogador INTEGER constraint jogador_pk Primary Key,nome_jogador Varchar2(20) NOT NULL,posicao_jog Varchar2(15),codigo_equipe INTEGER,
constraint jogador_fk Foreign Key (codigo_equipe) references equipe(codigo_equipe)
);
Exemplo
PARTIDA
(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)
Codigo_partida INTEGER
cidade Varchar2(10)
estado Varchar2(10)
nome_juiz Varchar2(20)
data date
Exemplo
PARTIDA(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)
CREATE TABLE partida (codigo_partida INTEGER constraint partida_pk Primary Key,cidade Varchar2(10) NOT NULL,estado Varchar2(10),nome_juiz Varchar2(20) NOT NULL,data Date
);
Mais Constraints!
• ON DELETE– SET NULL– CASCADE– SET DEFAULT
• ON UPDATE– SET NULL– CASCADE– SET DEFAULT
Exemplo
JOGA(#Codigo_Jogador,#Codigo_Partida,Numero_Gols)
codigo_jogador IntegerSe apagar jogador, apaga.
codigo_partida IntegerSe apagar partida, apaga.
numero_gols Integer
ExemploJOGA(#Codigo_Jogador,#Codigo_Partida,Numero_Gols)
CREATE TABLE joga (codigo_jogador integer,constraint joga1_fk foreign key (codigo_jogador)
references jogador(codigo_jogador)on delete cascade,
codigo_partida integer,constraint joga2_fk foreign key (codigo_partida)
references partida(codigo_partida)on delete cascade,
numero_gols integer);
Alteração de tabelas
• Criar, alterar ou eliminar colunas
• Renomear a tabela
• Criar ou eliminar constraints
• Habilitar ou desabilitar constraints
Alteração de Tabelas
ALTER TABLE [esquema.]tabela[add coluna tipo_dado [DEFAULT expr] [constraint_coluna] ]
[modify coluna tipo_dado [DEFAULT expr] [constraint_coluna] ]
[add constraint_coluna/constraint_tabela][drop constraint_coluna/constraint_tabela [cascade]]
[enable constraint_coluna/constraint_tabela][disable constraint_coluna/constraint_tabela] ;
Exemplo
• Alterar a tabela joga para que o valor default de Numero_gols seja 0;
ALTER TABLE joga MODIFY numero_gols INTEGER DEFAULT ‘0’;
• Adicionar uma Primary Key em joga
ALTER TABLE joga ADD constraint joga_pk Primary Key (codigo_jogador, codigo_partida);
Apagando Tabelas
DROP TABLE [esquema.]tabela [CASDADE CONSTRAINTS];
• CASCADE CONSTRAINTS elimina todas as restrições presentes em outras tabelas que façam referência à tabela que está sendo eliminada.
Índices
• CriarCREATE [UNIQUE] INDEX índice ON tabela (coluna [ASC | DESC]);
• UNIQUE Índice não aceita valores repetidos.
• É criado um índice UNIQUE sempre que uma Primary Key é criada.
• Apagando o índiceDROP INDEX índice;
Exemplo
select index_name from user_indexes;
CREATE UNIQUE INDEX my_index ON partida (nome_juiz);
select index_name from user_indexes;
Linguagem de manipulação de dados
• Inserindo dados
INSERT INTO [esquema.]tabela
(coluna1, coluna2, ... colunaN)
VALUES
(valor1, valor2 ... valorN);
Inserindo dados
• Se for inserir na mesma ordem da definição da tabela:
INSERT INTO [esquema.]tabela VALUES (valor1, ... valorN);
• Inserção em determinados campos
INSERT INTO [esquema.]tabela (colunaX, colunaY) VALUES (valorX, valorY);
Exemplo
• Criar o time Saravá Saci SoccerINSERT INTO equipe (codigo_equipe,nome_equipe,cidade,estado) VALUES (1,’SSS’,’São Carlos’, ‘São Paulo’);
OpçãoINSERT INTO equipe VALUES (1, ‘SSS’, ‘São Carlos’, ‘São Paulo’);
Exemplo
• Inserir time “Tiradentes”, de Brasília, DFINSERT INTO equipe VALUES (2,’Tiradentes’,’Brasilia’,’DF’);
• Inserir time “Enc97FC”
INSERT INTO equipe (codigo_equipe, nome_equipe) VALUES (3,’Enc97FC’);
Inserindo dados
• Cuidados com as restrições (constraints)!
INSERT INTO JOGADOR VALUES (1,’Juliano’,’goleiro’,4);
INSERT INTO equipe (codigo_equipe, nome_equipe) VALUES (4, ‘Selecao’);
INSERT INTO JOGADOR VALUES (1,’Juliano’,’goleiro’,4);
Atualizando dados
UPDATE tabela SET coluna = valor [, coluna = valor...] [WHERE condição];
• Exemplo: Juliano mudou para atacante!
UPDATE jogador SET posicao_jog = ‘atacante’ where codigo_jogador = 1;
Apagando dados
DELETE [FROM] tabela
[WHERE condição];• Exemplo: Apagar Enc97FC
DELETE FROM equipe WHERE codigo_equipe = 3;
Apagando dados
• Cuidados com as restrições (constraints)!
DELETE FROM equipe WHERE codigo_equipe = 4; FALHA!
DELETE jogador;
DELETE FROM equipe WHERE codigo_equipe = 4; OK!
Consultando dados: SELECT
• Até o final da aula!• Álgebra relacional
• Endereço do script http://www.dc.ufscar.br/~juliano
• Execução do scriptsqlplus> @ sql.txt
Selecionando dados
• Forma básica:
SELECT <lista de atributos>
FROM <lista de tabelas>
WHERE <condição>
Selecionando dados
SELECT [DISTINCT] {*, colunas[AS alias], expressões, funções..}
FROM {tabelas [AS alias]}[WHERE condição][GROUP BY colunas][HAVING condição][ORDER BY colunas [ASC | DESC]];
Álgebra Relacional
• π(*) jogador
SELECT DISTINCT * FROM jogador;
• σ(posicao_jog = atacante) jogador
SELECT * FROM jogador WHERE posicao_jog = ‘Atacante’;
Álgebra Relacional
• πnome_jogador(σ(posicao_jog = Atacante)Jogador)
SELECT DISTINCT nome_jogador FROM jogador where posicao_jog = ‘Atacante’;
• ρNome(πnome_jogador(σ(posicao_jog = Atacante)Jogador))
SELECT DISTINCT nome_jogador AS Nome FROM jogador where posicao_jog = ‘Atacante’;
União
• R1 πnome_jogador(σ(posicao_jog = Atacante)Jogador) • R2 πnome_jogador(σ(codigo_equipe = 2)Jogador)• Resultado R1 U R2
SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’
UNIONSELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;
Interseção
• R1 πnome_jogador(σ(posicao_jog = Atacante)Jogador) • R2 πnome_jogador(σ(codigo_equipe = 2)Jogador)• Resultado R1 ∩ R2
SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’
INTERSECTSELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;
Subtração
• R1 πnome_jogador(σ(posicao_jog = Atacante)Jogador) • R2 πnome_jogador(σ(codigo_equipe = 2)Jogador)• Resultado R1 – R2
SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’
MINUSSELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;
Produto Cartesiano
• R1 πnome_jogador(π(*) Jogador)
• R2 πnome_equipe(π(*) Equipe)
• Resultado R1 × R2
SELECT DISTINCT nome_jogador, nome_equipe
FROM jogador, equipe;
Join (Equijoin)
• R1 π(*) Jogador
• R2 π(*) Equipe
• Resultado R1 |x|equipe.codigo_equipe = jogador.codigo_equipe R2
SELECT *
FROM jogador, equipe
WHERE equipe.codigo_equipe = jogador.codigo_equipe;
Theta Join
• R1 π(*) Jogador
• R2 π(*) Equipe
• Resultado R1 |x|equipe.codigo_equipe > jogador.codigo_equipe R2
SELECT *
FROM jogador, equipe
WHERE equipe.codigo_equipe > jogador.codigo_jogador;
Natural Join
• R1 πnome_jogador, codigo_equipe(π(*) Jogador) • R2 πnome_equipe, codigo_equipe(π(*) Equipe)• Resultado πnome_jogador,nome_equipe( R1 *codigo_equipe
R2)
SELECT DISTINCT nome_jogador, nome_equipe, codigo_equipe
FROM jogador, equipeWHERE equipe.codigo_equipe = jogador.codigo_equipe;
Outer Join ]x|
• R1 πnome_jogador, codigo_equipe(π(*) Jogador) • R2 πnome_equipe, codigo_equipe(π(*) Equipe)• Resultado πnome_jogador,nome_equipe( R1 ]x|
equipe.codigo_equipe = jogador.codigo_equipe R2)
SELECT nome_jogador, nome_equipe FROM jogador, equipe
WHERE equipe.codigo_equipe (+) = jogador.codigo_jogador;
Divisão
• Todos os jogadores que fizeram gol em partidas onde Fabio Simplicio fez gol
Fabio σnome_jogador = ‘Fabio Simplicio’(Jogador)
F_Par πcodigo_partida(Joga |x|joga.codigo_jogador =
Fabio.codigo_jogador Fabio)
Jog_Par πcodigo_jogador,codigo_partida(Joga)
Result Jog_Par F_Par
Divisão
SELECT * FROM jogador WHERE nome_jogador = ‘Fabio Simplicio’;
SELECT DISTINCT codigo_partida FROM joga WHERE codigo_jogador = 51;
SELECT DISTINCT codigo_jogador, codigo_partida FROM joga;
SELECT DISTINCT codigo_jogador FROM joga WHERE (codigo_partida = 1 OR codigo_partida = 5) AND (codigo_jogador != 51);
Outras consultas
• Cobrimos todos os operadores da álgebra relacional.
• Veremos agora outros tipos de consultas comuns em SGBDs relacionais.
Consultas úteis
• Várias condições
SELECT nome_jogador FROM Jogador WHERE posicao_jog = ‘Goleiro’ AND codigo_equipe != 2;
SELECT nome_jogador FROM Jogador WHERE posicao_jog = ‘Goleiro’ OR posicao_jog = ‘Atacante’;
Consultas úteis
• DISTINCT
SELECT DISTINCT posicao_jog FROM jogador;
• LIKE
SELECT nome_jogador FROM jogador WHERE nome_jogador LIKE ‘%Luis%’;
Consultas úteis
• Operadores Matemáticos
SELECT codigo_partida, numero_gols * 10 FROM joga;
• BETWEEN
SELECT codigo_jogador, nome_jogador FROM jogador WHERE codigo_jogador BETWEEN 10 AND 20;
Consultas úteis
• INSELECT nome_jogador FROM jogador WHERE codigo_jogador IN (SELECT codigo_jogador FROM joga WHERE numero_gols = 2);
• ALLSELECT codigo_jogador FROM joga WHERE numero_gols > ALL (SELECT numero_gols FROM joga WHERE codigo_partida = 2);
Consultas úteis
• EXISTSSELECT nome_jogador FROM jogador WHERE EXISTS (SELECT * FROM joga WHERE jogador.codigo_jogador = joga.codigo_jogador);
• NOTSELECT nome_jogador FROM jogador WHERE NOT EXISTS (SELECT * FROM joga WHERE jogador.codigo_jogador = joga.codigo_jogador);
Consultas úteis
• SUM, MAX, MIN, AVG
SELECT SUM(numero_gols), MAX(numero_gols), MIN(numero_gols), AVG(numero_gols) FROM joga;
• COUNT
SELECT COUNT(*)FROM jogador WHERE codigo_equipe = 1;
Consultas úteis
• GROUP BYSELECT COUNT(*), codigo_equipe FROM jogador GROUP BY codigo_equipe;
• HAVINGSELECT COUNT(*), codigo_equipe FROM jogador GROUP BY codigo_equipe HAVING COUNT(*) > 30;
Consultas úteis
• IS [NOT] NULL
SELECT * FROM partida WHERE nome_juiz IS NULL;
SELECT * FROM partida WHERE nome_juiz IS NOT NULL;
Exercícios (desafios)
• Selecione os nomes das equipes que fizeram gols nos jogos apitados por Silvia
πnome_equipe(σ(nome_juiz = ‘Silvia’)(
(((Partida |x|partida.codigo_partida joga.codigo_partidaJoga)
|x|codigo_jogador = jogador.codigo_jogadorJogador)
|x|codigo_equipe = equipe.codigo_equipeEquipe)
)
Exercícios (desafios)
• Selecione os nomes das equipes que fizeram gols nos jogos apitados por Silvia
SELECT DISTINCT nome_equipeFROM equipe e, jogador j, partida p, jogaWHERE p.nome_juiz ='Silvia'AND p.codigo_partida = joga.codigo_partidaAND joga.codigo_jogador = j.codigo_jogadorAND j.codigo_equipe = e.codigo_equipe;
Exercícios (desafios)
• Selecione os nomes das equipes que fizeram gols nos jogos apitados por Silvia
NOME_EQUIPE
--------------------
Sao Paulo
Exercícios (desafios)
• Daqueles jogadores que marcaram gols, selecione o nome daqueles que não marcaram gols em Santos
SELECT DISTINCT nome_jogador FROM jogador j, jogaWHERE joga.codigo_jogador = j.codigo_jogadorAND j.codigo_jogador NOT IN(SELECT codigo_jogador FROM joga, partida p WHERE p.cidade = ‘Santos’ AND joga.codigo_partida = p.codigo_partida);
Exercícios (desafios)• Daqueles jogadores que marcaram gols, selecione o nome
daqueles que não marcaram gols em Santos
NOME_JOGADOR--------------------AndersonCarlos AlbertoDiegoFabio SimplicioLeandro AmaralNenemWilliam
7 linhas selecionadas.
Exercícios (desafios)
• Quantos gols cada equipe já fez?SELECT nome_equipe, SUM(numero_gols)
FROM equipe e, joga, jogador jWHERE e.codigo_equipe = j.codigo_equipe
AND j.codigo_jogador = joga.codigo_jogador
GROUP BY nome_equipe;
Exercícios (desafios)
• Quantos gols cada equipe já fez?
NOME_EQUIPE SUM(NUMERO_GOLS)
-------------------- ----------------
Corinthians 2
Santos 6
Sao Paulo 7
Exercícios (desafios)
• Quem são, para que time jogam, os jogadores que fizeram gols em mais de um jogo?
SELECT nome_jogador, nome_equipe
FROM jogador j, equipe e
WHERE j.codigo_equipe = e.codigo_equipe
AND (SELECT COUNT(*) FROM joga WHERE j.codigo_jogador = joga.codigo_jogador) > 1;
Exercícios (desafios)
• Quem são, para que time jogam, os jogadores que fizeram gols em mais de um jogo?
NOME_JOGADOR NOME_EQUIPE
-------------------- --------------------
Fabio Simplicio Sao Paulo
Luis Fabiano Sao Paulo
Recommended