Linguagem SQL

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

*
*
Linguagem SQL
Luciana Vieira
http://dev.mysql.com/doc/refman/5.1/en/
*
*
“SQL é a linguagem padrão para se lidar com bancos de dados relacionais”.
Date
SQL (Structured Query Language)
*
*
A SQL possui operações de definição (DDL-Data Definition Language) de dados, de manipulação (DML-Data Manipulation Language) de dados e de controle (DCL – Data Control Language) de dados.
SQL utiliza os termos tabela (table), linha (row), e coluna (column) para relação ou entidade, tupla e atributo, respectivamente.
*
*
*
*
Atleta= (idatleta, nome, sexo, cidade, dtNascimento) 
Equipe = (idequipe, nome)
Equipe_Atleta = (idequipe, idatleta) referencia Atleta, Equipe
Competicao = ( idcompeticao, data, cidade, pais)
Participacao = (idatleta, idequipe, idcompeticao, Classificacao) referencia Equipe_Atleta, Competicao
Penalidade = (idpenalidade,idatleta, data, pontos, valor)
*
*
*
*
CREATE TABLE <nome-tabela>	(<nome-coluna> , <tipo-do-dado> [NOT NULL] [NOT NULL WITH DEFAULT] )
PRIMARY KEY (nome-coluna-chave)
FOREIGN KEY (nome-coluna-chave-estrangeira) REFERENCES (nome-tabela-pai) ON DELETE [RESTRICT] [CASCADE] [NO ACTION]
*
*
*
*
Objetivo:
	Alterar a estrutura de uma tabela acrescentando, alterando, retirando e alterando nomes, formatos das colunas e a integridade referencial definidas em uma determinada tabela.
*
*
ALTER TABLE <tabela>
	DROP <coluna> ADD <coluna> <tipodado> [NOT NULL] [NOT NULL WITH DEFAULT] 
	RENAME <coluna> <novo-nome-coluna>
	RENAME TABLE <novo-nome-tabela>
	MODIFY <nome-coluna> <tipodado> [NULL][NOT NULL][NOT NULL WITH DEFAULT] 
	ADD PRIMARY KEY <coluna>
	DROP PRIMARY KEY <coluna>
	ADD FOREIGN KEY (coluna-chave-estrangeira) REFERENCES (nome-tabela-pai) ON DELETE [RESTRICT] [CASCADE] [SET NULL]
	DROP FOREIGN KEY (coluna-chave-estrangeira) REFERENCES (nome-tabela-pai)
*
*
DROP <coluna> - Realiza a retirada da coluna especificada na estrutura da tabela.
ADD <coluna> <tipodado> - Realiza a inclusão da coluna especificada na estrutura da tabela. Na coluna correspondente a este campo nos registros já existentes será preenchido o valor NULL (Nulo). As definições NOT NULL e NOT NULL WITH DEFAULT são semelhantes à do comando CREATE TABLE.
RENAME <coluna> <novo-nome-coluna> - Realiza a troca do nome da coluna especificada.
*
*
RENAME TABLE <novo-nome-tabela> - Realiza a troca do nome da tabela especificada.
MODIFY <coluna> <tipodado> - Permite a alteração na característica da coluna especificada. 
Opções: Além das existentes na opção ADD (NOT NULL e NOT NULL WITH DEFAULT), temos a opção NULL que altera a característica do campo passando a permitir o preenchimento com o valor Nulo.
ADD PRIMARY KEY <coluna> - Esta opção é utilizada quando é acrescido um novo campo como chave primária da tabela.
*
*
DROP PRIMARY KEY <coluna> - Esta opção é utilizada quando é retirado um campo como chave primária da tabela.
ADD FOREIGN KEY <coluna> - Esta opção é utilizada quando é acrescido um novo campo sendo ele uma chave estrangeira.
DROP FOREIGN KEY <coluna> - Esta opção é utilizada quando é retirado uma chave estrangeira da estrutura da tabela.
*
*
Objetivo:
	Deletar a estrutura e os dados existentes em uma tabela. Após a execução deste comando estarão deletados todos dados, estrutura e índices de acessos que estejam a ela associados.
*
*
	DROP TABLE <nome-tabela>
onde: 
nome-tabela - Representa o nome da tabela que será deletada.
Ex: drop table paticipacao;
*
*
Objetivo:
	Criar uma estrutura de índice de acesso para uma determinada coluna em uma tabela. Um índice de acesso permite um acesso mais rápido aos dados em uma operação de seleção. Os índices podem ser criados a partir de um ou mais campos de uma tabela.
*
*
CREATE INDEX <nome-índice>
		ON <nome-tabela> (<nome-coluna> [ASC ], [<nome-coluna> [ASC ] ])
Ex: Create unique index idpenalidade on penalidade(idpenalidade)
 
*
*
a) nome-índice - Representa o nome da estrutura de índice que será criada.
b) nome-tabela - Representa o nome da tabela que contem a coluna na qual será criada o índice de acesso.
c) nome-coluna - Representa o nome da coluna que será criada.
d) Opção ASC/DESC - Representa a criação do índice ordenada crescentemente (ASC) ou decrescentemente (DESC).
*
*
Objetivo:
	Deletar uma estrutura de índice de acesso para uma determinada coluna em uma tabela.
Sintaxe:
	DROP INDEX <nome-índice>
onde: 
a) nome-índice - Representa o nome da estrutura de índice que será deletada.
*
*
INSERT INTO tabela [(coluna, coluna, ...)] 
VALUES (valor-1, valor-2, ...)
Ou
INSERT INTO tabela
VALUES (valor-1, valor-2, ...)
*
*
INSERT INTO atleta 
VALUES(1, 'Joao', 'M', 'Maringa', '72/05/01');
INSERT INTO atleta 
VALUES(2,'José', 'M', 'Curitiba', '75/03/09');
*
*
INSERT INTO equipe VALUES 
(1,‘Mineira‘),
(2,‘Flamengo‘),
(3,‘Palmeiras‘),
(4,‘Gavioes‘);
*
*
INSERT INTO atelta_equipe VALUES (1,4);
*
*
*
*
*
*
*
*
Objetivo:
 Atualiza os dados de um registro ou um grupo de registros em uma tabela do Banco de Dados.
Sintaxe:
UPDATE <nome-tabela>
SET <nome-coluna> = <novo conteúdo para o campo> WHERE <condição>
*
*
UPDATE atleta
SET cidade = 'Belém' WHERE idatleta=4;
*
*
UPDATE <nome-tabela>
SET <nome-coluna> = <novo conteúdo para o campo>
Neste caso altera todos os valores da coluna para um único valor.
*
*
Objetivo:
 Deletar um ou um grupo de registros em uma tabela do Banco de Dados.
Sintaxe:
DELETE FROM <nome-tabela>
WHERE <condição>
*
*
DELETE FROM competicao
WHERE idcompeticao = 4;
*
*
DELETE FROM <nome-tabela>
Neste caso APAGA todos os valores da tabela.
*
*
Sintaxe Simplificada:
SELECT <column(s)> FROM <table>
 [ WHERE <condition> ]
 [ ORDER BY <column(s) [asc|desc]> ]
*
*
Sintaxe Detalhada:
SELECT [DISTINCT] coluna1 [AS nome] {, 
 colunaN [as nomeN]}
 [FROM from-list]
 [WHERE where-clause]
 [GROUP BY attr_name1 {, attr_name-i....}]
 [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {, nom-atributo-i...}]
[UNION {ALL} SELECT ...]
*
*
*
*
Comando mais básico do SQL:
SELECT * FROM tabela WHERE condição;
*
*
O predicado WHERE é utilizado para filtrar as informações desejadas. 
De uma forma geral os predicados têm a seguinte forma:
Coluna1 COND [valor]
Onde
Coluna1: É a coluna cujo valor será comparado
COND: É um operador de igualdade como =, <> ou !=, <, >, LIKE, IN
*
*
Vários predicados podem ser combinados utilizando-se operadores lógicos como AND, OR, NOT, e é claro parênteses. 
Exemplo:
WHERE coluna IN (SELECT DISTINCT coluna FROM tabela WHERE ....)
WHERE coluna IN ('valor1','valor2','valor3',...)
WHERE (coluna = 'valor' and coluna = 'outro_valor' OR coluna != 'valor')
*
*
Os operadores aritméticos podem ser utilizados em qualquer coluna numérica (numeric, decimal, int, smallint, tinynt, float, real, money ou smallmoney).
O operador de módulo (%) não pode ser executado nos tipos de dados money, smallmoney, float ou real.
*
*
*
*
*
*
*
*
SELECT DATE_ADD(CURDATE(), INTERVAL 120 DAY); 
SELECT NOW(),CURDATE(),CURTIME();
SELECT DATEDIFF('2010-01-05','2009-01-05');
SELECT DATE_FORMAT(Curdate(), '%d %m %Y');
SELECT DATE_FORMAT(DATE_ADD(Curdate(), interval 120 DAY), '%d %m %Y');
SELECT nome, dtNascimento, DATEDIFF (Curdate(), dtNascimento) FROM atleta;
SELECT nome, DAYNAME(dtNascimento) FROM atleta	WHERE DAYNAME(dtNascimento) = 'MONDAY';
*
*
SELECT * FROM atleta WHERE nome like 'Paulo%';
SELECT * FROM atleta 	WHERE idAtleta between 1 and 3;
SELECT * FROM atleta 	WHERE idAtleta <> 1 and idAtleta <> 3;
SELECT * FROM atleta
where dtnascimento between '2000-01-01' and '2010-12-31';
SELECT * FROM atleta where year(dtnascimento) between 1969 and 2010;
*
*
Retorna valores resumidos
O resultado aparece como novas colunas
Utilizado na Lista de SELECT ou na Cláusula HAVING
SELECT COUNT(*) FROM atleta;
SELECT SUM(pontos) FROM penalidade;
SELECT MAX(valor) FROM penalidade;
SELECT AVG(valor) FROM penalidade;
*
*
Utilizado para organizar de A-Z ou de Z-A.
Pode-se definir na cláusula ORDER BY o número relativo da coluna ao invés do nome da coluna.
Por default, a forma ascendente (ASC) é assumida.
Pode-se classificar por até 16 colunas.
EX:
SELECT idatleta, nomeAtleta, cidade FROM atleta
	ORDER BY cidade, idatleta DESC;
*
*
Organiza os dados em grupos
Geralmente utilizada com funções de agregação
Resulta um único valor para cada grupo
Cada coluna na lista de SELECT deve estar na cláusula GROUP BY
SELECT cidade FROM atleta GROUP BY cidade;
SELECT cidade, count(*) FROM atleta GROUP BY cidade;
*
*
1. Mostre todos os dados de todos os atletas que sofreram penalidades:
2. Mostre apenas a relação dos códigos de todos os atletas que sofreram penalidades:
3. Mostre a relação das cidades de onde provêm os atletas.
4. Mostre os nomes distintos das cidades de onde provêm os atletas. 
*
*
1. Mostre os dados de todos os atletas que sofreram penalidades:
select * from penalidade;
2. Mostre apenas a relação dos códigos de todos os atletas que sofreram penalidades:
select idAtleta from penalidade;
3. Mostre a relação das cidades de onde provêm os atletas.
select cidade from atleta group by cidade;
4. Mostre os nomes distintos das cidades de onde provêm os atletas. 
select distinct cidade from atleta;
*
*
5. Mostre o número de atletas que moram em cada cidade:
6. Mostre o número de penalidades que cada atleta recebeu:
7. Mostre o valor total das penalidades recebidas por cada atleta:
*
*
5. Mostre o número de atletas que moram em cada cidade:
select cidade, count(idatleta) from atleta group by
Cidade;
6. Mostre o número de penalidades que cada atleta recebeu:
select atleta_idatleta, count(idpenalidade) from penalidade
group by atleta_idatleta;
7. Mostre o valor total das penalidades recebidas por cada atleta:
select atleta_idatleta, sum(valor) from penalidade group by atleta_idatleta;
*
*
Cláusula de restrição de grupo
Permite funções de agregação
Similar a cláusula WHERE
As colunas na cláusula HAVING devem ter somente um único valor
Uma query com HAVING deve ter um GROUP BY
SELECT atleta_idAtleta, sum(valor) FROM penalidade
group by atleta_idAtleta having sum(valor)>1000;
*
*
8. Mostre o código dos atletas cujo valor total das penalidades aplicadas á ele ultrapassam R$ 500,00:
 
9. Liste os códigos dos atletas que sofreram mais de duas penalidade:
10. Mostre os atletas que nasceram depois de 1990:
 
*
*
8. Mostre o código dos atletas cujo valor total das penalidades aplicadas á ele ultrapassam R$ 500,00
select atleta_idatleta, sum(valor) from penalidade group by atleta_idatleta having sum(valor) > 500;
9. Liste os códigos dos atletas que sofreram mais de duas penalidade
select atleta_idatleta, count(valor) from penalidade group by atleta_idatleta having count(valor) >= 2;
10. Mostre os atletas que nasceram depois de 1990
Select * from atleta where year(dtNascimento) > 1990;
*
*
11. Mostre o código dos atletas cujo valor médio das penalidades aplicadas é menor que R$ 300,00
12. Mostre todas as cidades que tem pelo menos 2 jogadores provenientes
*
*
11. Mostre o código dos atletas cujo valor médio das penalidades aplicadas é menor que R$ 300,00
select atleta_idatleta, avg(valor) from penalidade group by atleta_idatleta having avg(valor) < 300;
12. Mostre todas as cidades que tem pelo menos 2 jogadores provenientes
select cidade, count(idatleta) from atleta group by cidade having count(idatleta) >= 2;
*
*
O operador UNION combina os resultados de duas ou mais queries em um único result set;
Para utilizar o UNION, o número e a ordem das colunas precisam ser idênticos em todas as queries 
Os data types precisam ser compatíveis.
Union executa um DISTINCT, ou seja, elimina linhas repetidas, Já o UNION ALL não. 
*
*
Repetidas
*
*
Quando se quer que apareçam os registros da tabela A que não são iguais aos da tabela B, temos a opção de ‘subtrair’ os registros utilizando EXCEPT.
*
*
Por fim, o uso de INTERSECT permite uma intersecção entre as consultas, seria como exibir somente os registros que as duas consultas têm em comum.
*
*
Operação que permite o tratamento de duas ou mais tabelas
Implementado pela utilização da sentença SELECT
Na lista de SELECT colocamos as colunas de uma ou mais tabelas que desejamos listar
Na cláusula FROM definimos todas as tabelas envolvidas no JOIN,
Na cláusula WHERE especificamos quais linha serão incluídas no resultado. Na cláusula WHERE, um operador de join é utilizado entre os componentes que estão fazendo a junção.
*
*
Pode-se definir até 16 tabelas na cláusula FROM.
Normalmente, uma chave primária é comparada a uma chave estrangeira em qualquer join
O operador utilizado para o join é o sinal de igual (=), que resulta somente nas linhas onde o valor da coluna de uma tabela é igual ao valor da coluna de outra tabela.
Caso não se defina nenhuma condição de JOIN (com a cláusula WHERE), é gerado um produto Cartesiano entre as tabelas. (Join Irrestrito)
*
*
Exemplo de Join Irrestrito (Produto Cartesiano entre as Tabelas):
select * from atleta, penalidade;
select * from atleta JOIN penalidade;
select idatleta, atleta_idatleta from atleta, penalidade;
*
*
Especifica uma condição de igualdade entre as tabelas para satisfazer o Join.
EX: Mostre apenas a relação dos nomes de todos os atletas que sofreram penalidades:
select nomeAtleta from atleta, penalidade
where (atleta.idatleta = penalidade.atleta_idatleta);
select nomeAtleta from atleta a Join penalidade p
on (a.idatleta = p.atleta_idatleta);
*
*
EX: Mostre os nomes dos atletas e sua respectiva equipe:
SELECT nomeEquipe,nomeAtleta FROM atleta a, equipe e, atleta_equipe ea 
WHERE
ea.atleta_idatleta=a.idatleta and ea.equipe_idequipe=e.idequipe;
*
*
Ex: Mostre o nome dos atletas, das equipes e o país que das competições onde ocorreram participações:
SELECT nomeEquipe,nomeAtleta, Pais FROM participacao p, atleta a, equipe e, competicao c where p.atleta_equipe_atleta_idAtleta=a.idatleta and
p.atleta_equipe_equipe_idEquipe=e.idequipe 
and
p.competicao_idcompeticao=c.idcompeticao;
*
*
Ex: Mostre os códigos dos atletas mais velhos que o jogador 3.
select x.idatleta from atleta x, atleta y
Where y.idatleta = 3 and y.dtNascimento > x.dtNascimento;
select x.idatleta from atleta x INNER JOIN atleta y
on y.idatleta = 3 and y.dtNascimento > x.dtNascimento;
*
*
Mostra todas as linhas do join, inclusive aquelas que não encontram linhas correspondentes na tabela relacionada.
Só se pode fazer um outer join entre duas tabelas.
Operadores do Outer Join:
Left join -> Inclui todas as linhas da primeira tabela especificada nas restrições da sentença
Right joint  Inclui todas as linhas da Segunda tabela especificada nas restrições da sentença
*
*
Especifica uma condição de igualdade entre as tabelas para satisfazer o Join.
select nomeatleta from atleta a LEFT JOIN penalidade p on (a.idatleta = p.atleta_idatleta);
select nomeatleta from atleta a RIGHT JOIN penalidade p on (a.idatleta = p.atleta_idatleta);
*
*
São sentenças SELECT aninhadas
A sentença SELECT de uma subquery é sempre colocado dentro de parênteses.
Uma subquery pode estar inserida na lista de SELECT, numa cláusula WHERE ou HAVING da query externa.
Em aninhamentos normais de subqueries, as mais internas são executadas primeiro.
*
*
Ex: Mostre quantas outras penalidades tem valor igual a penalidade de menor valor.
Select count(valor) from penalidade where 
valor = (select min(valor) from penalidade);
Ex: Liste os nomes dos atletas que sofreram mais de uma penalidade
select nomeAtleta from atleta where idatleta in (select atleta_idatleta from penalidade group by atleta_idatleta having count(valor) >= 2)
*
*
14. Liste os códigos dos atletas que sofreram mais de uma penalidade com valor superior a 300 Reais e ordene o resultado pelo código dos atletas.
 
*
*
14. Liste os códigos dos atletas que sofreram mais de uma penalidade com valor superior a 200 Reais e ordene o resultado pelo código dos atletas.
select atleta_idatleta, count(valor) from penalidade
where valor > 200 group by atleta_idatleta
having count(valor) >=1 order by atleta_idatleta; 
*
*
15. Liste os nome dos atletas que sofreram mais de uma penalidade com valor superior a 200 Reais e ordene o resultado pelo código dos atletas.
*
*
15. Liste os nome dos atletas que sofreram mais de uma penalidade com valor superior a 200 Reais e ordene o resultado pelo código dos atletas.
select nome from atleta
where idatleta in (select atleta_idatleta from penalidade where valor >= 200 
group by atleta_idatleta
having count(valor) >=1 
order by atleta_idatleta);
*
*
16. Liste os códigos e nomes dos atletas que participaram de pelo menos uma competicao.
*
*
16. Liste os códigos e nomes dos atletas que participaram de pelo menos uma competicao.
select nomeAtleta from atleta where idatleta in (select atleta_idatleta from participacao);
*
*
17. Mostre os códigos dos atletas cujo valor total de penalidades recebidas é maior que o valor médio de penalidades aplicadas aos atletas.
*
*
17. Mostre os códigos dos atletas cujo valor total de penalidades recebidas é maior que o valor médio de penalidades aplicadas aos atletas.
select atleta_idatleta from penalidade group by atleta_idatleta having sum(valor) > (select avg(valor) from penalidade)
*
*
18. Mostre os nomes dos atletas cujo valor total de penalidades recebidas é maior que o valor médio de penalidades aplicadas aos atletas.
*
*
18. Mostre os nomes dos atletas cujo valor total de penalidades recebidas é maior que o valor médio de penalidades aplicadas aos atletas.
select nomeAtleta from atleta 
where idatleta in (select atleta_idatleta from penalidade group by atleta_idatleta
having sum(valor) > (select avg(valor) from penalidade));
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*