Baixe o app para aproveitar ainda mais
Prévia do material em texto
Universidade Federal de Mato Grosso Instituto de Computação Banco de Dados Prof. MSc. Allan Gonçalves de Oliveira allan@ic.ufmt.br Outubro - 2012 SQL (Structured Query Language) Autores: Adriano Siqueira Arantes Enzo Seraphim Josiel Maimone de Figueiredo Introdução ➔ Desenvolvida e implementada: ➔ pelo Laboratório de Pesquisas da IBM em San Jose ➔ entre os anos de 1964 e 1969 ➔ Inicialmente chamada “SEQUEL” ➔ Forma de interface com o primeiro SGBD Relacional: ➔ System R ➔ SEQUEL/Original SQL - 1974 ➔ SQL-86 (ANSI/ISO) - 1986 ➔ SQL-92 - 1992 ➔ SQL:1999 - 1999 ➔ SQL:2003 - 2003 ➔ SQL:2006 - 2006 In tr od uç ão Padronizações ➔ SQL1 (1986) ➔ buscou padronizar as várias implementações SQL da época, principalmente ➔ANSI ➔ISO In tr od uç ão Padronizações ➔ SQL 2 (1992) ➔ agentes SQL; ➔ conexões cliente/servidor SQL; ➔ suporte para SQL embutida em novas linguagens; ➔ suporte para novos tipos de dados ➔blobs, varchar, date, time e timestamp; ➔ suporte para tabelas temporárias; ➔ suporte para operadores de junção ; ➔ alteração do esquema via ALTER e DROPIn tr od uç ão Padronizações ➔ SQL 3 (1999) ➔ introduziu alguns conceitos do paradigma OO. ➔ permite campos multivalorados. ➔ permite campos compostos. In tr od uç ão Padronizações ➔ SQL (2003) ➔ inclusão de características XML ➔ definida cláusula MERGE In tr od uç ão Padronizações ➔ SQL (2006) ➔ mais características XML ➔ suporte à XQuery ➔ padrão para interface XML-SQL In tr od uç ão Padronizações ➔ Divisões atuais In tr od uç ão Padronizações Parte Descrição 1 – SQL / Framework Descrição das partes comuns nas divisões 2 – SQL / Foundation SQL / OLAP Definição dos dados, sintaxe e semântica de manipulação 3 – SQL / CLI Call Level Interface (ODBC) 4 – SQL / PSM Persistent Storage Module: linguagem procedural 5 – SQL / Bindings SQL Embutido 6 – SQL / Transactions (cancelado) Especialização da especificação X-Open XA 7 – SQL / Temporal (paralisado) Tratamento de dados temporais 8 – SQL / Objects Extended Objects (incorporada à divisão 1) Trata do modelo de objetos 9 – SQL / MED Managemen of External Data: acesso a dados externos 10 – SQL / OLB Object Language Binding: SQL com Java (SQLJ) 11 – SQL / Schemata Informação e definição de esquema 12 – SQL / Replication Funcionalidade de replicação de dados 13 – SQL / JRT Java Routines and Types: uso de Java dentro do BD 14 – SQL / XML Uso de XML ➔ Divisões atuais (cont.) ➔ SQL/MM (Multimedia) In tr od uç ão Padronizações Parte Descrição 1 – Framework Definição da extensão 2 – Full Text Tratamento de textos longos (>64Kb) 3 – Spatial Dados espacias 5 – Still Image Imagens 6 – Data mining Mineração de dados ➔ instruções SQL não são case sensitive. ➔ instruções SQL podem ser feitas em mais de uma linha. ➔ palavras chave não podem ser abreviadas. ➔ cláusulas geralmente são colocadas em linha separadas. ➔ normalmente são utilizadas identações para melhorar a legibilidade das instruções. In tr od uç ão Regras Gerais In tr od uç ão cod_autor Livro Editora escreve publica N 1 NMAutor nome nascimento cod_livro título valor volume cod_editora razão endereço cidade MER Exemplo Autor = { codigo, nome, nascimento } Escreve = { codigoAutor, codigoLivro} Livro = { codigo, titulo, valor, volume, codigoEditora } Editora = { codigo, razao, endereco, cidade } In tr od uç ão Mapeamento para o Relacional ➔ Os comandos podem ser agrupados em 3 classes: ➔ DDL (Data Definition Language) ➔inclui comandos para definir, alterar e remover tabelas e índices; ➔ DML (Data Manipulation Language) ➔comandos para inserir, remover, atualizar e consultar os dados armazenados nas tabelas; ➔ DCL (Data Control Language) ➔comandos para se trabalhar em ambiente multi-usuário, permitindo estabelecer níveis de segurança e manipular transações.De fin iç õe s Classes dos Comandos ➔ Para todos comandos da DDL: ➔ CREATE - Cria uma definição ➔ ALTER - Altera uma definição ➔ DROP - Exclui uma definição ➔ Alguns comandos da DDL: ➔ TABLE - Define uma tabela, seus campos e as restrições de campo. ➔ INDEX - Define um índice associado a um campo de uma tabela. DD L - D ef in iç õe s DDL ➔ Alguns comandos da DDL (cont.): ➔ TRIGGER ➔define um conjunto de instruções que são automaticamente executadas (gatilhos) antes ou depois de um comando INSERT, UPDATE ou DELETE. ➔ PROCEDURE ➔define um conjunto de instruções (stored procedures). ➔podem receber ou retornar valores. ➔podem ser executadas através de uma solicitação do usuário ou por um TRIGGER.DD L - D ef in iç õe s DDL ➔ CHAR(n) ➔ tamanho de armazenamento n bytes. ➔ para armazenamento de dados textuais. ➔ o número máximo de caracteres é definido por n e deve estar entre 1 e 32.765. ➔ reserva previamente o tamanho definido por n, mesmo que o dado armazenado não preencha totalmente o campo. ➔ VARCHAR(n) ou CHAR VARYING(n) ou CHARACTER VARYING(n) ➔ armazena somente a quantidade de caracteres para conter o dado tendo no máximo o valor n. ➔ problemas com alteração de valor do dado implica em mudança de local de armazenamento. DD L – Ti po s de D ad os Tipos de Dados ➔ NUMBER (precisão, decimal) ➔ tamanho de armazenamento variável para valores decimais. ➔ precisão define a quantidade de dígitos numérico máximo para o dado e decimal a quantidade de números decimais. ➔ por exemplo: NUMBER (10,3) indica que o dados pode ter no máximo 7 dígitos inteiros e 3 dígitos decimais. ➔ INTEGER ➔ mesmo que Number(38). ➔ tamanho de armazemanento 32 bits. ➔ para armazenamento de números inteiros no intervalo de –2.147.483.648 to 2.147.483.647. DD L – Ti po s de D ad os Tipos de Dados ➔ DATE ➔ tamanho de armazenamento 64 bits. ➔ para armazenamento de data e hora. ➔ o intervalo de datas válidas é 1/Jan/100 até 11/Jan/5941. ➔ a hora é inclusa. ➔ BLOB (Binary Large Object) ➔ armazena os dados em formato binário. ➔ tamanho de armazenamento variável. ➔ para armazenamento de grande quantidade de dados como áudio, vídeo, gráficos, etc. DD L – Ti po s de D ad os Tipos de Dados ➔ Lógicos ➔ AND ➔ OR ➔ NOT ➔ Aritméticos ➔ +,-,*,/ ➔ Relacionais ➔ < <= ➔ > >= ➔ <> = ➔ LIKE, BETWEENDD L – Ti po s de D ad os Operadores Lógicos ➔ Conjunturais =ANY <ANY >ANY >=ANY <=ANY <>ANY <ALL >ALL <>ALL EXISTS NOT EXISTS IN NOT IN DD L – Ti po s de D ad os Operadores ➔ Cria uma nova tabela com seus campos e define as restrições de campo ➔ Sintaxe: CREATE TABLE tabela (campo1 tipo [(tamanho)] [NOT NULL] [PRIMARY KEY | UNIQUE] [DEFAULT literal], [campo2 tipo [(tamanho)] [NOT NULL] [PRIMARY KEY | UNIQUE], [DEFAULT literal], [, ...], [CONSTRAINT nome] {PRIMARY KEY (primária1[, primária2 [, ...]]) | UNIQUE (exclusiva1[, exclusiva2 [, ...]]) | FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES tabelaexterna [(campoexterno1 [, campoexterno2 [, ...]]) | CHECK [condição] } )DD L – Co m an do s CREATE TABLE ➔ Exemplo 1: /* Cria a tabela Autor */ CREATE TABLE Autor( codigo NUMBER(3) NOT NULL , nome VARCHAR(70) NOT NULL, nascimento DATE NOT NULL, CONSTRAINT ChaveAutor PRIMARY KEY (codigo), CONSTRAINT UnicoAutor UNIQUE( nome, nascimento) ); DD L – Co man do s CREATE TABLE ➔ Exemplo 2: /* Cria tabela editora */ CREATE TABLE Editora( codigo NUMBER(3) NOT NULL, razao VARCHAR(70), endereco CHAR(70), cidade CHAR(70), CONSTRAINT ChaveEditora PRIMARY KEY (codigo) ); DD L – Co m an do s CREATE TABLE ➔ Exemplo 3: /* Cria tabela livro */ CREATE TABLE Livro( codigo NUMBER(9) NOT NULL, titulo CHAR(70) NOT NULL, valor NUMBER(7,2), volume NUMBER(2), codigoEditora NUMBER(3) NOT NULL, CONSTRAINT ChaveLivro PRIMARY KEY (codigo), CONSTRAINT LivroEditora FOREIGN KEY (codigoEditora) REFERENCES Editora );DD L – Co m an do s CREATE TABLE ➔ Exemplo 4: /* Cria tabela escreve */ CREATE TABLE Escreve ( codigoLivro NUMBER(9) NOT NULL, codigoAutor NUMBER(3) NOT NULL, CONSTRAINT ChaveEscreve PRIMARY KEY (codigoLivro, codigoAutor), CONSTRAINT EstrangeiraLivro FOREIGN KEY (codigoLivro) REFERENCES Livro, CONSTRAINT EstrangeiraAutor FOREIGN KEY (codigoAutor) REFERENCES Autor ); DD L – Co m an do s CREATE TABLE ➔ Altera as definições de campos e de restrições de uma tabela. ➔ Sintaxe: ALTER TABLE tabela [ADD campo tipo [(tamanho)] [NOT NULL] [PRIMARY KEY | UNIQUE], [, ...], [DROP campo tipo [(tamanho)] [NOT NULL] [PRIMARY KEY | UNIQUE], [, ...], [ADD CONSTRAINT restrição [, ...]] [DROP CONSTRAINT restrição [, ...]] DD L – Co m an do s ALTER TABLE ➔ Exemplo 1: ➔ Adicionar o campo Email na tabela Autor ALTER TABLE Autor ADD email VARCHAR(30); ➔ Exemplo 2: ➔ Apagar a CONSTRAINT UnicoAutor da tabela Autor ALTER TABLE Autor DROP CONSTRAINT UnicoAutor; DD L – Co m an do s ALTER TABLE ➔ Exclui uma tabela existente de um banco de dados. ➔ Não pode ser excluída uma tabela que possui alguma referência. ➔ nesse caso deve-se primeiro excluir a referência à tabela. ➔ Sintaxe: DROP TABLE tabela; ➔ Exemplo: ➔ Apaga tabela Autor DROP TABLE Autor;DD L – Co m an do s DROP TABLE ➔ Cria um novo índice em uma tabela existente. ➔ A criação dos índices ocorrem para: ➔ auxiliar o gerenciador em uma pesquisa de um ou mais campos na base de dados, quando for solicitado; ➔ auxiliar o gerenciador em uma ordenação de um ou mais campos na base de dados, quando for solicitado. ➔ Sintaxe: CREATE [UNIQUE] INDEX nome_índice ON nometabela ( campo [ASC | DESC] [, campo…]) DD L – Co m an do s CREATE INDEX ➔ Exemplo 1 : ➔ Cria índice do campo nascimento em ordem descendente para tabela Autor CREATE INDEX NascimentoIDX ON Autor (nascimento DESC); ➔ Exemplo 2: ➔ Cria índice do campo cidade e razão em ordem ascendente para tabela Editora CREATE INDEX CidadeRazaoIDX ON Editora (cidade, razao); DD L – Co m an do s CREATE INDEX ➔ Exclui um índice existente de uma tabela. ➔ Sintaxe DROP INDEX nome_índice ➔ Exemplo: ➔ Apaga índice CidadeRazaoIDX da tabela editora DROP INDEX CidadeRazaoIDX; DD L – Co m an do s DROP INDEX ➔ Comandos DML ➔ INSERT INTO ➔ adiciona um (ou múltiplos) registros a uma tabela. ➔ UPDATE ➔ altera valores de campos em uma determinada tabela com base em critérios especificados. ➔ DELETE ➔ remove os registros de uma ou mais tabelas que satisfaçam aos critérios. ➔ SELECT ➔ procura na tabela ou tabelas especificadas, extrai as colunas escolhidas, seleciona as linhas que atendem aos critérios e classifica ou agrupa as linhas resultantes na ordem especificada. DM L – De fin iç õe s DML ➔ adiciona um registro ou múltiplos registros a uma tabela. ➔ Sintaxe ➔ Insere múltiplos registros: INSERT INTO nomeTabela [(campo1[, campo2[, ...]])] SELECT [origem.]campo1[, campo2[, ...] FROM expressãodetabela ➔ Insere um único registro: INSERT INTO nomeTabela [(campo1[, campo2[, ...]])] VALUES (valor1[, valor2[, ...]) DM L – Co m an do s INSERT INTO ➔ Exemplo 1: ➔ Insere um registro na tabela autor INSERT INTO Autor (codigo, nome, nascimento) VALUES (1, 'Oliveira, J.', '13-May- 1960'); ➔ Insere um registro na tabela editora INSERT INTO Editora VALUES (1, 'MCGRAW - HILL', 'R.São Carlos, 321', 'São José do Rio Preto'); DM L – Co m an do s INSERT INTO ➔ Exemplo 2: ➔ Cria tabela autorvelho em seguida insere na tabela autorvelho, os autores com nascimento < 01/01/1960 CREATE TABLE AutorVelho( codigo NUMBER(3) NOT NULL CONSTRAINT ChaveAutorVelho PRIMARY KEY, nome VARCHAR(30) NOT NULL, nascimento DATE NOT NULL, CONSTRAINT UnicoAutorVelho UNIQUE( nome, nascimento) ); DM L – Co m an do s INSERT INTO ➔ Exemplo 2 (continuação): INSERT INTO AutorVelho SELECT * FROM Autor WHERE nascimento < '01/01/1960'; DM L – Co m an do s INSERT INTO ➔ altera valores de campos em uma determinada tabela, com base em critérios especificados ➔ Sintaxe: UPDATE nometabela SET novovalor [WHERE condição_booleana]; DM L – Co m an do s UPDATE ➔ Exemplo 1: ➔ Alterar o endereço e a cidade da tabela editora com código = 1 UPDATE Editora SET endereco = 'AV.N.S.DE FATIMA, 123', cidade = 'SANTA FÉ' WHERE codigo = 1; ➔ Exemplo 2: ➔ Reajusta o valor de todos os livros em 10% UPDATE Livro SET valor = valor * 1.1;DM L – Co m an do s UPDATE ➔ remove os registros de uma ou mais tabelas que satisfaçam aos critérios. ➔ Sintaxe: DELETE FROM nome_tabela [WHERE condição_booleana]; ➔ Exemplo 1: ➔ Apaga o registro da tabela Autor com codigo = 1 DELETE FROM Autor WHERE codigoAutor = 1; ➔ Exemplo 2 : ➔ Apaga todos os registros da tabela livro DELETE FROM Livro;DM L – Co m an do s DELETE ➔ Extrai os valores armazenados nas tabelas. ➔ Realiza várias operações da Álgebra Relacional: ➔ Seleção, Projeção, Junção, etc ➔ Sintaxe: SELECT [DISTINCT | ALL] { * | tabela.* | [tabela.]campo1 [AS alias1] [, ...]]} FROM expressãodetabela [, ...] [WHERE condição_booleana] [GROUP BY... ] [HAVING... ] [UNION... ] [ORDER BY... ] DM L – Co m an do s SELECT ➔ Exemplo 1: ➔ Seleciona todos os campos na tabela autor SELECT * FROM Autor; ➔ Exemplo 2: ➔ Selecionar todos os nomes e datas de nascimento da tabela autor SELECT nome, nascimento FROM Autor; ➔ Exemplo 3: ➔ Selecionar todos os nomes e datas de nascimento da tabela autor renomeando a coluna nome para autor SELECT nome AS autor, nascimento FROM Autor; DM L – Co m an do s SELECT ➔ Especifica quais registros das tabelas listadas na cláusula FROM são afetados por uma instrução SELECT, UPDATE ou DELETE. ➔ Se não for especificado uma cláusula WHERE, a consulta retornará todas as linhas da tabela. ➔ Exemplo 1: ➔ Seleciona todos os livros cujo valor sejam maiores que R$ 60,00 SELECT titulo, valor FROM Livro WHERE valor > 60;DM L – Co m an do s Cláusula WHERE ➔ Exemplo 2: ➔ Selecionar as editoras da cidade de São Paulo SELECT razao FROM Editora WHERE cidade = ‘São Paulo’; ➔ Exemplo 3: ➔ Selecionar título da tabela livro e nome da tabela autor e renomeando a tabela para facilitar a digitação SELECT AU.nome, LI.titulo FROM Autor AU, Livro LI, Escreve ES WHERE (AU.codigo = ES.codigoAutor) AND (LI.codigo = ES.codigoLivro);DM L – Co m an do s Cláusula WHERE ➔ Combina registros com valores idênticos na lista de campos especificada em um único registro. Um valor de resumo é criado para cada registro se você incluir uma função agregada SQL, como Sum ou Count, na instrução SELECT. ➔Exemplo: ➔ Todas editoras que publicaram livro SELECT Editora.razao FROM Livro, Editora WHERE Livro.codigoEditora = Editora.codigo GROUP BY Editora.razao; DM L – Co m an do s Cláusula GROUP BY ➔ Especifica quais registros agrupados são exibidos na instrução SELECT com uma cláusula GROUP BY. Depois de GROUP BY combinar os registros, HAVING exibirá qualquer registro agrupado pela cláusula GROUP BY que satisfaça às condições da cláusula HAVING. ➔ Exemplo: ➔ Editoras cujo total de publicações é maior que 1 SELECT codigoEditora AS editora FROM Livro GROUP BY codigoEditora HAVING COUNT(codigoEditora) > 1; DM L – Co m an do s Cláusula HAVING ➔ Classifica os registros resultantes de uma consulta em um campo ou campos especificados, em ordem crescente ou decrescente. Os registros são classificados pelo primeiro campo listado após ORDER BY. Os registros que têm valores iguais naquele campo serão então classificados pelo valor no segundo campo listado e assim por diante. ➔ Exemplo: ➔ Selecione nome e nascimento da tabela autor em ordem dos autores mais novos SELECT nome,nascimento FROM Autor ORDER BY nascimento DESC; DM L – Co m an do s Cláusula ORDER BY ➔ coloca as letras da coluna especificada em maiúsculo ➔ Exemplo 1: SELECT UPPER(nome) FROM Autor ➔ Exemplo 2: SELECT * FROM Autor WHERE UPPER(nome) = “JOSE” DM L – Co m an do s Operador UPPER ➔ Retira valores repetidos na coluna especificada. ➔ Exemplo: SELECT DISTINCT(codigoLivro) FROM Escreve DM L – Co m an do s Operador DISTINCT ➔ Determina se o valor de uma expressão é igual a algum dos vários valores em uma lista especificada. Se expr for encontrado na lista de valores, o operador In retornará True; caso contrário, retornará False. ➔ Exemplo: ➔ Selecionar editora localizadas nas cidades de são paulo, são carlos e rio de janeiro SELECT * FROM Editora WHERE cidade IN ('SÃO PAULO', 'SÃO CARLOS', 'RIO DE JANEIRO'); DM L – Co m an do s Operador IN ➔ Determina se o valor de uma expressão se situa dentro de um intervalo especificado de valores. Se o valor de expr estiver entre valor1 e valor2 (inclusive), o operador Between...And retornará True; caso contrário, retornará False. ➔ Exemplo: ➔ Selecionar livros com valor entre R$ 50,00 e R$ 60,00 SELECT titulo, valor FROM Livro WHERE valor BETWEEN 50 AND 60; DM L – Co m an do s Operador BETWEEN … AND ➔ Compara uma expressão de seqüência de caracteres com um padrão em uma expressão SQL. Para padrão, você pode utilizar caracteres coringas (por exemplo, Like ‘MAK%’, para ‘MAKRON BOOKS’) ou utilizar caracteres isolados (por exemplo, Like ‘_OSE’, para ‘JOSE E ROSE’) ➔ Exemplo: ➔ Selecionar nome de autor que inicie com letra 'e' SELECT nome FROM Autor WHERE nome LIKE 'E%'; DM L – Co m an do s Operador LIKE ➔ Determina se o valor de uma expressão é nulo. ➔ Exemplo: ➔ Selecionar as editoras com endereço nulo SELECT razao FROM Editora WHERE endereco IS NULL; DM L – Co m an do s Operador IS NULL ➔ Calcula a média aritmética de um conjunto de valores contido em um campo especificado de uma consulta. ➔ Exemplo: ➔ Média de valores dos livros SELECT AVG(VALOR) AS MEDIA FROM LIVRO; ➔ Média de valores dos livros de cada editora SELECT ED.razao, AVG(LI.valor) AS media FROM Editora ED, Livro LI WHERE ED.codigo=LI.codigoEditora GROUP BY ED.razao; DM L – Co m an do s Operador AVG ➔ Calcula o número de registros retornado por uma consulta. A função Count não conta registros que tenham campos Null, exceto quando expr for o caractere curinga asterisco (*). Se expr identificar vários campos, a função Count contará um registro somente se um dos campos não for Null. Separe os nomes de campo com um &. ➔ Exemplo: ➔ Contar autores que nasceram antes de 01/01/1960 SELECT COUNT (*) FROM Autor WHERE nascimento< '1/1/1960'; DM L – Co m an do s Operador COUNT ➔ Exemplo: ➔ Contar quantos livros não tem volume com null ➔SELECT COUNT (’VOLUME') ➔FROM Livro; ➔ Contar total de editoras ➔SELECT COUNT (*) ➔FROM Editora; ➔ Contar os livros publicado por cada editora SELECT ED.razao, COUNT(LI.codigoEditora) FROM Editora ED, Livro LI WHERE ED.codigo = LI.codigoEditora GROUP BY ED.razao; DM L – Co m an do s Operador COUNT ➔ Retornam o mínimo ou o máximo de um conjunto de valores contido em um campo especificado em uma consulta. ➔ Exemplo: ➔ Selecionar o valor do livro mais caro SELECT MAX(valor) AS VR_ALTO FROM Livro; ➔ Selecionar valor do livro mais barato SELECT MIN(valor) AS VR_BARATO FROM Livro; DM L – Co m an do s Operadores MIN, MAX ➔ Exemplo: ➔ Selecionar o título com valor mais alto SELECT titulo, valor FROM Livro WHERE valor IN ( SELECT MAX(valor) FROM Livro ); ➔ Selecionar o título com valor mais alto SELECT titulo, valor FROM Livro WHERE valor IN (SELECT MIN(valor) FROM Livro); DM L – Co m an do s Operadores MIN, MAX ➔ Retorna a soma de um conjunto de valores contido em um campo especificado em uma consulta. A função Sum ignora os registros que contenham campos Null. ➔ Exemplo: ➔ Soma dos valores dos livros pulicados pela editora com código 2 SELECT SUM(valor) AS somaEditora FROM Livro WHERE codigoEditora = 2; DM L – Co m an do s Operador SUM ➔ Somar os valores dos livros por cada editora SELECT ED.razao, SUM(LI.valor) FROM Editora ED, Livro LI WHERE ED.codigo = LI.codigoEditora GROUP BY ED.razao; DM L – Co m an do s Operador SUM ➔ Realiza operações que envolvem duas tabelas. ➔ Normalmente faz a “ligação” entre uma chave estrangeira e a chave primárias a que faz referência. ➔ Tipos de Joins: ➔ equi ➔ outer DM L – Co m an do s JOIN (Junções) ➔ a condição de junção é de igualdade. ➔ recupera somente as tuplas que obedecem à condição de junção. ➔ Exemplo 1: ➔ Selecione os livros e suas editoras. SELECT Livro.nome, Editora.nome FROM Livro, Editora WHERE Livro.codigoEditora = Editora.codigo DM L – Co m an do s Equi Join ➔ Exemplo 2: ➔ Selecione os livros e suas editoras. SELECT Livro.nome, Editora.nome FROM Livro JOIN Editora ON codigoEditora= codigo DM L – Co m an do s Equi Join ➔ a condição de junção não é de igualdade. ➔ recupera somente as tuplas que obedecem à condição de junção. ➔ Exemplo: ➔ Selecione os autores cadastrados depois dos autores do livro “João e Maria”. SELECT Autor.nome FROM Autor, Livro, Escreve WHERE Livro.Titulo = “João e Maria” AND Livro.codigo = Escreve.codigoLivro AND Autor.codigo > Escreve.codigoAutor DM L – Co m an do s Não Equi Join ➔ recupera tuplas das duas tabelas mesmo que elas não obedeçam à condição de junção. ➔ Tipos: ➔ LEFT ➔ RIGTH ➔ FULL DM L – Co m an do s OUTER Join ➔ recupera todas as tuplas da primeira tabela, mesmo que as não obedeçam à condição de junção. ➔ Exemplo 1 (Oracle): ➔ Selecione os livros e suas editoras, recuperando os livros que não possuem editoras. SELECT Livro.nome, Editora.nome FROM Livro, Editora WHERE Livro.codigoEditora (+) = Editora.codigo DM L – Co m an do s LEFT OUTER JOIN ➔ Exemplo 2 (Interbase): ➔ Selecione os livros e suas editoras, recuperando os livros que não possuem editoras. SELECT Livro.nome, Editora.nome FROM Livro LEFT OUTER JOIN EditoraON codigoEditora = codigo DM L – Co m an do s LEFT OUTER JOIN ➔ recupera todas as tuplas da segunda tabela, mesmo que as não obedeçam à condição de junção. ➔ Exemplo 1 (Oracle): ➔ Selecione os livros e suas editoras, recuperando as editoras que não possuem livros publicados. SELECT Livro.nome, Editora.nome FROM Livro, Editora WHERE Livro.codigoEditora = Editora.codigo (+) DM L – Co m an do s RIGHT OUTER JOIN ➔ Exemplo 2 : ➔ Selecione os livros e suas editoras, recuperando as editoras que não possuem livros publicados. SELECT Livro.nome, Editora.nome FROM Livro RIGHT OUTER JOIN Editora ON codigoEditora = codigo DM L – Co m an do s RIGHT OUTER JOIN ➔ recupera todas as tuplas das duas tabelas, mesmo que as não obedeçam à condição de junção. ➔ Exemplo 1 (Oracle): ➔ Selecione os livros e suas editoras, recuperando as editoras que não possuem livros publicados e os livros sem editora. SELECT Livro.nome, Editora.nome FROM Livro, Editora WHERE Livro.codigoEditora (+) = Editora.codigo (+) DM L – Co m an do s FULL OUTER JOIN ➔ Exemplo 1 (Oracle): ➔ Selecione os livros e suas editoras, recuperando as editoras que não possuem livros publicados e os livros sem editora. SELECT Livro.nome, Editora.nome FROM Livro FULL OUTER JOIN Editora ON codigoLivro = codigo DM L – Co m an do s FULL OUTER JOIN ➔ Uma subconsulta é uma consulta embutida em uma outra cláusula SQL. ➔ A subconsulta é sempre executada primeiro. ➔ O resultado da subconsulta é utilizado pela consulta principal. ➔ Exemplo: ➔ Selecione os livros escritos pelo autor 5. SELECT titulo FROM Livro WHERE codigo IN (SELECT codigoLivro FROM Escreve WHERE codigoAutor = 5)DM L – Co m an do s Subconsultas ➔ Utilizada para ocasionar um processamento tupla-tupla. ➔ A subconsulta referencia uma coluna na consulta principal. ➔ Cada sub consulta é executada uma vez para cada tupla da consulta principal. ➔ Passos: 1.Obtém a tupla na consulta principal 2.Executa a subconsulta com o valor da obtido em 1 3.Utiliza o(s) valor(es) da subconsulta para qualificar a tupla obtida em 1. DM L – Co m an do s Subconsultas correlacionada ➔ Exemplo: ➔ Selecione os livros cujos preços sejam maiores que a média de preço dos livros de sua editora. SELECT titulo FROM Livro LivroPrinc WHERE valor > (SELECT AVG(valor) FROM Livro LivroSub WHERE LivroSub.codigoEditora = LivroPrinc.codigoEditora)DM L – Co m an do s Subconsultas correlacionada ➔ Se uma tupla é encontrada na subconsulta: ➔ a procura não continua na subconsulta. ➔ a condição retorna TRUE. ➔ Se o valor não é encontrado na subconsulta: ➔ a condição retorna FALSE. ➔ a busca continua na subconsulta. DM L – Co m an do s Operador EXISTS ➔ Exemplo: ➔ Encontre os nomes dos autores do livro 1234. SELECT Autor.Nome FROM Autor WHERE EXISTS ( SELECT * FROM Escreve WHERE (Escreve.CodigoAutor = Autor.Codigo AND Escreve.CodigoLivro = 1234) ) DM L – Co m an do s Operador EXISTS ➔ retorna TRUE se a comparação é verdadeira para pelo menos um valor retornado pela subconsulta. ➔ Exemplo ➔ Selecione o título dos Livros que possua autores nascidos em 1970. SELECT Livro.titulo FROM Livro WHERE Livro.codigo IN ( SELECT codigoLivro FROM Escreve WHERE codigoAutor =ANY ( SELECT codigo FROM Autor WHERE YEAR(nascimento) = 1970 ) )DM L – Co m an do s Operador ANY ➔ retorna TRUE se a comparação é verdadeira para todos os valores retornados pela subconsulta. ➔ Exemplo ➔ Selecione os livros que não foram escritos pelo João. SELECT titulo FROM Livro WHERE codigo IN ( SELECT DISTINCT(codigoLivro) FROM Escreve WHERE codigoAutor <> ALL ( SELECT codigo FROM Autor WHERE UPPER(nome) = UPPER(‘João’ ) )DM L – Co m an do s Operador ALL ➔ Faz a união das tuplas de duas relações resultantes operações SELECT. ➔ Exemplo ➔ Selecione os livros que custam mais que R$30,00 ou que são do quarto volume. SELECT codigo, titulo FROM Livro WHERE Valor > 30 UNION SELECT codigo, titulo FROM Livro WHERE volume = 4 DM L – Co m an do s Operador UNION ➔ Faz a intersecção das tuplas de duas relações resultantes operações SELECT. ➔ Exemplo ➔ Selecione os livros que custam mais que R$30,00 e que são do quarto volume. SELECT codigo, titulo FROM Livro WHERE Valor > 30 INTERSECT SELECT codigo, titulo FROM Livro WHERE volume = 4 DM L – Co m an do s Operador INTERSECT ➔ Faz a diferença das tuplas de duas relações resultantes operações SELECT. ➔ Exemplo ➔ Selecione os livros que custam mais que R$30,00 e que não são do quarto volume. SELECT codigo, titulo FROM Livro WHERE valor > 30 MINUS SELECT codigo, titulo FROM Livro WHERE volume = 4 DM L – Co m an do s Operador MINUS ➔ A cláusula SELECT deve selecionar o mesmo número de colunas. ➔ As colunas correspondentes devem ser o mesmo tipo de dado. ➔ As linhas duplicadas são automaticamente eliminados. ➔ Os nomes das colunas do primeiro SELECT é que aparecem no resultado. ➔ ORDER BY somente pode ser usada indicando o número da coluna. ➔ ORDER BY deve aparecer no final do comando. DM L – Co m an do s Regras para UNION, INTERSECT e MINUS ➔ Os operadores UNION, INTERSECT e MINUS podem ser utilizados em subqueries. ➔ As colunas SELECTs são executadas de cima para baixo. ➔ Vários SET OPERATORS podem ser utilizados. Para indicar a seqüência de execução deve-se utilizar-se parênteses. DM L – Co m an do s Regras para UNION, INTERSECT e MINUS ➔ CONNECT ➔ Permite a conexão a uma base de dados através de um gerenciador. ➔ DISCONNECT ➔ Desconecta de uma base de dados. ➔ COMMIT ➔ Torna permanente todas as alterações (como os comandos INSERT, DELETE e UPDATE) feitas desde o início de uma transação. ➔ ROLLBACK ➔ Descarta todas as alterações feitas desde o início da transação, ou do último comando COMMIT ou ROLLBACK.DC L – De fin iç ão Comandos DCL ➔ Torna permanente todas as alterações (comandos INSERT, DELETE e UPDATE) feitas desde o início da conexão. ➔ Todos os comandos da DDL causam um COMMIT automático. ➔ Exemplo: ➔ Tornar permanente todas as alterações pendentes na sessão corrente COMMIT; DC L – Co m an do s Comando COMMIT ➔ Descarta todas as alterações (comandos INSERT, DELETE e UPDATE) feitas desde o início da transação (do último comando COMMIT ou ROLLBACK). ➔ Exemplo: ➔ Descartar todas as alterações ROLLBACK ; DC L – Co m an do s Comando ROLLBACK Slide 1 Capa Introdução Int - SQL1 Slide 5 Int - SQL2 Int - SQL3 Slide 8 Slide 9 Slide 10 Slide 11 Regras Gerais MER Ex Relacoes Ex Classe Com DDL 1 DDL 2 Tipo Dado 1 Tipo Dado 2 Tipo Dado 3 Oper 1 Oper 2 Creat Tab Creat TabEx 1 Creat Tab Ex 2 Creat TabEx 3 Creat Tab Ex4 Alter Tab Alter Tab Ex Drop Tab Crea Ind Crea Ind Ex Drop IDX DML INSERT INTO Inser Into Ex1 Inser Into Ex2a Inser Into Ex2b Update Update Ex Delete Select Select Ex WHERE Where Ex GROUP BY HAVING ORDER BY UPPER DISTINCT IN BETWEEN LIKE IS NULL AVG COUNT COUNT Ex MIN MAX MinMax Ex SUM SUM Ex JoinsEqui Join Equi Interbase não EQUI Join Outer Join LEFT Outer Left Outer Interb Right outer Right outer Interb Full outer Full outer Interb Sub Consult Sub Cons Corr Sub Cons Corr Ex EXISTS EXISTS Ex ANY ALL UNION INTERSECT MINUS Slide 83 Regras U - DCL COMMIT Rollback
Compartilhar