Structu ctured red Query ery Langua guage (SQL)
Linguagem de Manipulação de Dados (DML) • Inclusão de dados: INS NSER ERT T INT NTO O NOME NOME_T _TAB ABEL ELA A (C (COL OL1, 1, COL2,...,COLN) VALUE VA LUES S (VA (VAL1, L1, VA VAL2, L2,... ...,VA ,VALN) LN);;
• Exemplo: Insert Into PESSOA (CPF, NOME OME, SEXO) valu values es ('111 ('111222 222333 33344' 44',','Pau Paulo lo Abreu Abreu',', 'M') 'M');; Insert Into PESSOA (CPF, NOME OME, SEXO) Select CPF, NOME, SEXO From Aluno 2
Linguagem de Manipulação de Dados (DML) • Inclusão de dados: INS NSER ERT T INT NTO O NOME NOME_T _TAB ABEL ELA A (C (COL OL1, 1, COL2,...,COLN) VALUE VA LUES S (VA (VAL1, L1, VA VAL2, L2,... ...,VA ,VALN) LN);;
• Exemplo: Insert Into PESSOA (CPF, NOME OME, SEXO) valu values es ('111 ('111222 222333 33344' 44',','Pau Paulo lo Abreu Abreu',', 'M') 'M');; Insert Into PESSOA (CPF, NOME OME, SEXO) Select CPF, NOME, SEXO From Aluno 2
Linguagem de Manipulação de Dados (DML) • Consulta simples: COL1, COL1, COL2, COL2,... ...,CO ,COLN LN FROM NOME_TABELA WHERE (expre (expressã ssãoo lógica lógica)) GROUP BY (atri (atribu buto toss de agru agrupa pame ment nto) o) HAVING (con (condi diçã çãoo de agru agrupa pame ment nto) o) ORDER BY (li (lista sta de atri atribbutos) tos) SELECT
• Where: filtra antes do agrupamento. • Having: filtra depois do agrupamento. 3
Linguagem de Manipulação de Dados (DML) • Exemplo: Select * From PESSOA; Select CPF, NOME, SEXO, IDADE From PESSOA Where sexo = ‘M’ or sexo = ‘F’; ou Where sexo in (‘M’, ‘F’); Select CPF, NOME, SEXO, IDADE From PESSOA Where IDADE > ANY (10,20,30); ou Where IDADE > ALL (10,20,30); 4
Linguagem de Manipulação de Dados (DML)
5
Linguagem de Manipulação de Dados (DML) • Exemplo: SELECT * FROM DEPT WHERE DNAME LIKE '/_%' ESCAPE '/‘ Select CPF, NOME, SEXO From PESSOA Where nome like ‘P%’; (‘[CK]%’) (‘[C-K]%’ intervalo) (not like) Select CPF, NOME, SEXO From PESSOA Where idade is null; (not null) Select CPF, NOME, SEXO From PESSOA Where idade >= 10 and idade <= 20; (between 10 and 20) 6
Linguagem de Manipulação de Dados (DML) • Exemplo com Alias (apelidos): Select CPF as Meu_CPF From PESSOA Select CPF, NOME, SEXO From PESSOA as P Where P.idade is null; Select NOME, IDADE + 5 AS NOVA_IDADE From PESSOA; 7
Linguagem de Manipulação de Dados (DML) • Atualização: UPDATE NOME_TABELA SET COL1=VAL1, COL2=VAL2,...,COLN=VALN WHERE (expressão lógica)
• Exemplo: UPDATE PESSOA SET idade = 30 WHERE nome = ‘Paulo’; 8
Linguagem de Manipulação de Dados (DML) • Deleção: DELETE FROM NOME_TABELA WHERE (expressão lógica)
• Exemplo: DELETE FROM PESSOA WHERE nome = ‘Paulo’;
• TRUNCATE TABLE NOME_TABELA (sem rollback): – O comando TRUNCATE remove rapidamente todas as linhas da tabela. Tem o mesmo efeito do comando DELETE sem a cláusula WHERE, mas como não varre a tabela é mais rápido. É mais vantajoso para tabelas grandes. – O comando TRUNCATE não pode ser utilizado dentro de um bloco de transação (delimitado por BEGIN/COMMIT), porque não existe a possibilidade de desfazê-lo. 9
Linguagem de Manipulação de Dados (DML) • Funções Agregadas: SELECT COUNT(*) FROM PESSOA; SELECT COUNT(NOME) FROM PESSOA; SELECT SUM(idade) FROM PESSOA; SELECT AVG(idade) FROM PESSOA; SELECT MAX(idade) FROM PESSOA; SELECT MIN(idade) FROM PESSOA; 10
Linguagem de Manipulação de Dados (DML) • Tabela Livro: (Codigo, Titulo, Preco, Lancamento, Assunto, Editora) Assunto References Assunto(Sigla) Editora References Editora(Codigo)
• Tabela Assunto: (Sigla, Descricao)
• Tabela Editora: Editora(Codigo, Nome)
11
Linguagem de Manipulação de Dados (DML) Assunto
Editora
Livro
12
Linguagem de Manipulação de Dados (DML) • Funções Agregadas: SELECT ASSUNTO, AVG(PRECO) FROM LIVRO GROUP BY ASSUNTO; – Preço médio dos livros por assunto SELECT ASSUNTO, COUNT(*) FROM LIVRO GROUP BY ASSUNTO; – Quantidade de livros por assunto 13
Linguagem de Manipulação de Dados (DML) • Funções Agregadas: SELECT ASSUNTO, MAX(PRECO) FROM LIVRO GROUP BY ASSUNTO; – Preço do livro mais caro de cada assunto. SELECT ASSUNTO FROM LIVRO GROUP BY ASSUNTO HAVING AVG(PRECO) > 50; – Assuntos cuja média de preço dos livros seja maior que R$ 50,00. 14
Linguagem de Manipulação de Dados (DML) • Funções Agregadas: SELECT ASSUNTO, COUNT(*) FROM LIVRO GROUP BY ASSUNTO HAVING COUNT(*) > 2; – Assuntos que possuem pelo menos três livros.
15
Linguagem de Manipulação de Dados (DML) • Eliminação de Repetições: SELECT DISTINCT ASSUNTO AS ASSUNTO FROM LIVRO; – Assuntos distintos. SELECT COUNT (ASSUNTO) AS Qtd_ASSUNTO FROM LIVRO; – Quantidade de ocorrência de assuntos. SELECT COUNT (DISTINCT ASSUNTO) AS Dif_ASSUNTO FROM LIVRO; – Quantidade de assuntos distintos.
16
Linguagem de Manipulação de Dados (DML) • Ordenação de Resultados: SELECT ASSUNTO, TITULO, PRECO FROM LIVRO ORDER BY ASSUNTO ASC, PRECO DESC; SELECT ASSUNTO, TITULO, PRECO FROM LIVRO ORDER BY ASSUNTO, PRECO DESC; SELECT ASSUNTO, TITULO, PRECO FROM LIVRO ORDER BY 2, PRECO DESC; 17
Linguagem de Manipulação de Dados (DML) • Funções para cadeias de caracteres: - LOWER(col) = apresenta o resultado em minúsculas. - UPPER (col)= apresenta o resultado em maiúsculas. - INITCAP (col)= apresenta o resultado com a primeira letra maiúscula de cada palavra. - REPLACE (col, cadeia1, cadeia2)= Substitui um caractere ou caracteres de uma cadeia com 0 ou mais caracteres. Ex: REPLACE(nome, ‘be’, ‘da’) – Abel
Adal
- SUBSTR (col, inicio, qtd)= Obtém parte de uma cadeia. Ex: SUBSTR(nome,2,2) – Abel
be
- CONCAT (col1, col2)= Devolve “col1" concatenada com “col2". 18
Linguagem de Manipulação de Dados (DML) • Funções para cadeias de caracteres: - LPAD (col, tam, ‘char’)= Adiciona caracteres à esquerda do resultado até que tenha uma certa longitude. - RPAD (col, tam, ‘char’)= Adiciona caracteres à direita do resultado até que tenha uma certa longitude. Ex: LPAD(nome, 10, ‘*’) = ******Abel Ex: RPAD(nome, 10, ‘*’) = Abel****** - LTRIM (col, ‘chars’)= Suprime um conjunto de caracteres à esquerda do resultado. - RTRIM (col, ‘chars’)= Suprime um conjunto de caracteres à direita da cadeia. Ex: LTRIM(nome, ‘a’) = Abel - bel Ex: RTRIM(nome, ‘el’) = Abel - Ab 19
Linguagem de Manipulação de Dados (DML) • Funções para números: • ABS(n)= Devolve o valor absoluto de (n). • CEIL(n)=Obtém o valor inteiro imediatamente superior ou igual a "n". • FLOOR(n) = Devolve o valor inteiro imediatamente inferior ou igual a "n". • MOD (m, n)= Devolve o resto resultante de dividir "m" por "n". • NVL (col, valor)= Substitui um valor nulo por outro valor. • NULLIF(col, valor) = retorna NULL se col = valor; • POWER (m, exponente)= Calcula a potência de um número. 20 • SQRT(n)= Devolve a raiz quadrada de "n".
Linguagem de Manipulação de Dados (DML)
• Funções para conversão de tipos: – CAST – Sintaxe: • CAST(dado_origem as tipo_dado_destino)
SELECT CAST (AVG(PRECO) AS DECIMAL (10,2)) FROM PRODUTO; SELECT CAST (’11/02/2002’ AS DATE) FROM DUAL;
21
Linguagem de Manipulação de Dados (DML) • Junção Interna: – Select Titulo, Descricao From Livro INNER JOIN Assunto on Assunto = Sigla Where Preco > 100 – Select Nome, Titulo From Livro INNER JOIN Editora E on Editora = E.Codigo INNER JOIN Assunto on Assunto = Sigla
Select Titulo, Descricao From Livro, Assunto Where Assunto = Sigla and Preco > 100
Select Nome, Idade From Pessoa INNER JOIN Gente USING (CPF)
22
Linguagem de Manipulação de Dados (DML) • Junção Externa à Esquerda: – Select * From Livro li LEFT OUTER JOIN Assunto a on li.cod_assunto = a.cod_assunto
23
Linguagem de Manipulação de Dados (DML) • Junção Externa à Direita: – Select * From Livro li RIGHT OUTER JOIN Assunto a on li.cod_assunto = a.cod_assunto
24
Linguagem de Manipulação de Dados (DML) • Junção Externa Completa: – Select * From Livro li FULL OUTER JOIN Assunto a on li.cod_assunto = a.cod_assunto
25
Linguagem de Manipulação de Dados (DML) • Junção Cruzada (Produto Cartesiano): – Select Cod_Pac, Nome_Pac, Cod_Med, Nome_Med From Paciente CROSS JOIN Medico
PACI ENTE P A CI E N T E X ME D I C O
MEDI CO
26
Linguagem de Manipulação de Dados (DML) • Subconsultas não-correlacionadas: – SELECT Nome FROM Editora WHERE Codigo IN (SELECT Editora FROM Livro WHERE Lancamento IS NOT NULL) – Nome das editoras que já lançaram livros. Livro
Editora
27
Linguagem de Manipulação de Dados (DML) • Subconsultas não-correlacionadas: – SELECT Descricao FROM Assunto WHERE Sigla NOT IN (SELECT Assunto FROM Livro WHERE Lancamento IS NOT NULL) – Assuntos que não foram lançados livros. Livro
Assunto
28
Linguagem de Manipulação de Dados (DML) • Subconsultas correlacionadas: – SELECT Nome FROM Editora ED WHERE EXISTS (SELECT Editora FROM Livro L WHERE Lancamento IS NOT NULL AND ED.Codigo = L.Editora) Livro
Editora
29 29
Linguagem de Manipulação de Dados (DML) • Subconsultas correlacionadas: – SELECT Descricao FROM Assunto A WHERE NOT EXISTS (SELECT Assunto FROM Livro L WHERE Lancamento IS NOT NULL AND A.Sigla = L.Assunto)
Livro
Assunto
30
Linguagem de Manipulação de Dados (DML) • União: – SELECT Titulo FROM Livro WHERE Assunto = ‘B’ Union [ALL] (com linhas repetidas) SELECT Titulo FROM Livro WHERE Assunto = ‘P’ Livro
31
Linguagem de Manipulação de Dados (DML) • União: – SELECT * FROM Livro WHERE Assunto = ‘B’ UNION CORRESPONDING BY (Nome, Autor, Data_Lancamento) SELECT * FROM Obra WHERE Area_Concentracao = ‘P’;
32
Linguagem de Manipulação de Dados (DML) • Interseção: – SELECT Titulo FROM Livro WHERE Assunto IN (‘B’, ‘R’) INTERSECT SELECT Titulo FROM Livro WHERE Assunto IN (‘B’, ‘P’) Livro
33
Linguagem de Manipulação de Dados (DML) • Diferença: – SELECT Titulo FROM Livro WHERE Assunto IN (‘B’, ‘R’) EXCEPT SELECT Titulo FROM Livro WHERE Assunto IN (‘B’, ‘P’) Livro
34
Linguagem de Manipulação de Dados (DML) • Comando MERGE (UPSERT): MERGE INTO Cliente C USING AUTOR A ON (A.CPF = C.CPF) WHEN MATCHED THEN UPDATE SET C.NOME = A.NOME C.ENDERECO = A.ENDERECO C.DATA_NASCIMENTO = A. DATA_NASCIMENTO WHEN NOT MATCHED THEN INSERT (CODIGO, NOME, CPF, ENDERECO, DATA_NASCIMENTO) VALUES (A.MATRICULA, A.NOME, A.CPF, A.ENDERECO, A.DATA_NASC)
• Atualização da tabela Cliente a partir da tabela Autor 35
Índice sobre campo chave CREATE I NDEX ind-nome ON DROP I NDEX
EMPREGADO (CPF);
ind-nome ON tabela-nome;
36
Controle de Segurança em SQL GRANT CREATE TAB ON DATABASE TO GRANT I NSERT, DELETE ON GRANT SELECT ON
usuario;
EMPREGADO, PROJETO TO usuario;
EMPREGADO TO usuario;
GRANT CONTROL ON I NDEX
ind-dep TO usuario1, usuario2;
GRANT ALL PRI VI LEGES ON
EMPREGADO TO usuario1;
CREATE ROLE role1; GRANT
role1 TO usuario1;
REVOKE role1 FROM
usuario1;
REVOKE CONTROL ON I NDEX REVOKE DELETE ON
ind-dep FROM usuario2;
EMPREGADO FROM usuario2;
REVOKE ALL PRI VI LEGES ON EMPREGADO FROM usuario1;
37
