SQL - WordPress.com
Transcrição
SQL - WordPress.com
Introdução SQL n Originalmente proposta para o System R desenvolvido nos laboratórios da IBM na década de 70 q n Bancos de Dados I Altigran Soares da Silva IComp/UFAM – 2013/02 q q Por que SQL? n q n SQL1 (SQL-86) SQL2 (SQL-92) SQL3 (SQL:1999) Setenças Select-From-Where SQL é uma linguagem de alto nível. q Objeto de um esforço de padronização coordenado pelo ANSI/ISO: q Adaptado do Material do Professor Jeffrey Ullman SEQUEL (Structured English QUEry Language) SELECT atributos desejados FROM uma ou mais tabelas WHERE condição sobre as tuplas da(s) tabela(s) Diz “o que fazer” em vez de “como fazer” Evita que seja necessário conhecer de detalhes de como manipular os dados. SGBD procura a melhor maneira de executar a consulta. q “Otimização de Consulta.” 3 4 Esquema de Exemplo n Exemplo Todas nossas consultas SQL serão baseadas no seguinte Esquema. q n Sublinhado indica chave. Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar) Usando Beers(name, manf), quais cervejas (beers) são feitas por Anheuser-Busch? SELECT name FROM Beers WHERE manf = ’Anheuser-Busch’; 5 Resultado da Consulta 6 Definindo uma consulta usando uma única relação name Bud Bud Lite Michelob ... n n n A resposta é uma Relação com um único atributo : name e tuplas com valor de “name” de cada cerveja do fabricante Anheuser-Busch, tal como Bud. 7 Inicie com a Relação na cláusula FROM . Aplique a seleção indicada pela cláusula WHERE. Aplique a projeção extendida indicada pela cláusula SELECT. SELECT name FROM Beers WHERE manf = ’Anheuser-Busch’; 8 Semântica Operacional name Semântica Operacional (Geral) n manf n Bud Inclue t.name no resultado, se atender a condição Anheuser-Busch Verifica se é Anheuser-Busch n Imagine uma variável tupla visitando cada tupla da Relação mencionada em FROM. Verifica se a tupla corrente satisfaz a cláusula WHERE . Se sim, computa os atributos ou expressões da cláusula SELECT usando os componente da tupla. SELECT name Tupla-variável t Loop em todas as tuplas FROM Beers WHERE manf = ’Anheuser-Busch’; 9 Resultado da Consulta: * Nas cláusula SELECT n n 10 SELECT * FROM Beers WHERE manf = ’Anheuser-Busch’; Quando um “*” for utilizado na cláusula SELECT para uma relação definida na cláusula FROM, significa que “todos” atributos desta relação serão utilizados. Exemplo: Usando Beers(name, manf): SELECT * FROM Beers WHERE manf = ’Anheuser-Busch’; name Bud Bud Lite Michelob ... 11 manf Anheuser-Busch Anheuser-Busch Anheuser-Busch ... 12 Renomeando Atributos Resultado da Consulta: SELECT name AS beer, manf FROM Beers WHERE manf = ’Anheuser-Busch’ n n Caso se deseje um resultado com nome de atributos diferentes, usa-se “AS <novo nome>” para renomear um atributo. Exemplo: Usando Beers(name, manf): SELECT name AS beer, manf FROM Beers WHERE manf = ’Anheuser-Busch’ beer Bud Bud Lite Michelob ... manf Anheuser-Busch Anheuser-Busch Anheuser-Busch ... 13 Expressões em cláusula SELECT n Qualquer expressão que faça sentido pode aparecer como um elemento da cláusula SELECT. n Exemplo: Usando Sells(bar, beer, price): SELECT bar, beer, price*114 AS priceInYen FROM Sells; 14 Resultado da Consulta SELECT bar, beer, price*114 AS priceInYen FROM Sells; bar Joe’s Sue’s … 15 beer Bud Miller … priceInYen 285 342 … 16 Exemplo: Constantes como Expressões n Resultado da Consulta SELECT drinker, ’likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ’Bud’; Usando Likes(drinker, beer): drinker Sally Fred … SELECT drinker, ’likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ’Bud’; whoLikesBud likes Bud likes Bud … 17 18 Condições Complexas no WHERE n n Exemplo: Condição Complexa Operadores Booleanos AND, OR, NOT. Comparações =, <>, <, >, <=, >=. q n E muitos outros operadores que produzem um resultado boleano. Usando Sells(bar, beer, price), procure o preço da cerveja Bud em Joe’s Bar: SELECT price FROM Sells WHERE bar = ’Joe’’s Bar’ AND beer = ’Bud’; 19 20 Padrões n Uma condição pode comparar uma string com um padrão: q n Exemplo: LIKE n <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern> Pattern é uma string iniciada com % = “qualquer string”; _ = “qualquer character.” Usando Drinkers(name, addr, phone) procure os drinkers com phones inicados com 555: SELECT name FROM Drinkers WHERE phone LIKE ’%555-_ _ _ _’; 21 Valores NULL n n Comparando NULL com Valores Independente de seu domínio, os atributos pode assumir valor NULL Dependendo do Contexto. Dois casos comuns: q q 22 n n Faltando valor : e.x:., Sabe-se que o Joe’s Bar tem algum endereço, mas não sabemos qual. Não aplicável: e.x., O valor do atributo conjuge para uma pessoa que não é casada. n 23 A lógica das condições em SQL aceitam 3 valores lógicos: TRUE, FALSE, UNKNOWN. Comparando qualquer valor (incluindo o próprio NULL) com NULL retorna UNKNOWN. Uma tupla está na resposta a uma consulta, se a cláusula WHERE é verdadeira (not FALSE ou UNKNOWN). 24 Exemplo: Junção de Duas Relações Consultas em Multiplas relações n n n Consultas interessantes frequentemente combinam dados de mais de uma relação. Podemos usar diversas relações em uma consulta, listando-as na cláusula FROM. Atributos distintos com mesmo nome pode ser referenciados usando “<relation>.<attribute>” . n Usando as relações Likes(drinker, beer) e Frequents(drinker, bar), procure os apreciadores de cerveja que frequentam o Joe’s Bar. SELECT beer FROM Likes, Frequents WHERE bar = ’Joe’’s Bar’ AND Frequents.drinker = Likes.drinker; 25 Semântica Formal n Semântica Operacional Parecido com consultas por uma única relação: 1. 2. 3. 26 n Inicia com o produto cartesiano de todas as relações na cláusula FROM. Aplica a condição de seleção descritas na cláusula WHERE . Projeta em cima da lista de atributos e expressões da cláusula SELECT. Imagine uma tupla-variável para cada relação na cláusula FROM . q n 27 Estas tuplas-variáveis consultam cada combinação de tuplas, uma de cada relação. Se a tupla-variável satisfaz a cláusula WHERE , então envia esta tupla para a cláusula SELECT . 28 Exemplo drinker Tupla-Variáveis Explicitas bar drinker n beer tv1 tv2 Sally Sally n Bud Joe’s Frequentadores check for Joe Checa se é igual n Likes Algumas vezes, uma consulta precisa usar duas cópias da mesma relação. Cópias distintas seguem o nome da relação para o nome de uma tuplavariável, na cláusula FROM. Isto é uma opção para renomear relações, até mesmo quando isso não é essencial. vai para o resultado 30 29 Exemplo: Self-Join Sub-Consultas n n Da relação Beers(name, manf), procure os pares de cervejas fabricadas pelo mesmo fabricante. q Pares como (Bud, Bud) não devem aparecer n SELECT b1.name, b2.name FROM Beers b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name; Colocando entre parentenses, uma sentença SELECT-FROM-WHERE (subconsulta ) pode ser usada como valor em vários de lugares, incluindo em cláusulas FROM e WHERE. Exemplo: No lugar de uma relação na cláusula FROM, podes-se usar uma subconsulta e então usar seu resultado. q 31 Deve usar uma tupla-variável para o nome das tuplas do resultado. 32 Exemplo: Sub-consulta em FROM n Subconsultas que retornam uma tupla Procure as cervejas curtidas por pelo menos uma pessoa que frequenta o Joe’s Bar. n Drinkers que frequentam Joe’s Bar SELECT beer FROM Likes, (SELECT drinker FROM Frequents WHERE bar = ’Joe’’s Bar’)JD WHERE Likes.drinker = JD.drinker; Se uma subconsulta é garantidamente produz uma única tupla, então a subconsulta pode ser usada como um valor q q Normalmente, a tupla tem um componente. Um erro em tempo de execução ocorre, caso não exista tupla ou exista mais que uma tupla. 33 Exemplo n n Exemplo SELECT bar FROM Sells WHERE beer = ’Miller’ AND price = (SELECT price FROM Sells WHERE bar = ’Joe’’s Bar’ O preço pelo qual AND beer = ’Bud’); Joe vende Bud Usando Sells(bar, beer, price), procure os bares que servem Miller pelo mesmo preço que Joe cobra por uma Bud. Duas consultas poderiam funcionar: 1. 2. 34 Procure o preço cobrado por Joe para Bud. Procure os bares que servem Miller com este preço. 35 36 O operador IN n n <tupla> IN (<subconsulta>) é verdadeira, se e somente se a tupla é um membro da relação produzida por uma subconsulta. q n Exemplo: IN Oposto: <tupla> NOT IN (<subconsulta>). Usando Beers(name, manf) e Likes(drinker, beer), procure o nome e fabricante de cada cerveja que Fred gosta. SELECT * FROM Beers WHERE name IN (SELECT beer FROM Likes Conjunto de cervejas Que Fred gosta WHERE drinker = ’Fred’); Expressões usando IN podem aparecer em cláusulas WHERE 37 38 IN é um predicado sobre Tuplas SELECT a FROM R, S WHERE R.b = S.b; SELECT a FROM R WHERE b IN (SELECT b FROM S); SELECT a FROM R WHERE b IN (SELECT b FROM S); Um laço sobre as tuplas da relação R 39 a b 1 2 3 4 R b c 2 5 2 6 S (1,2) satisfaz a condição; 40 O operador Exists SELECT a FROM R, S WHERE R.b = S.b; n n Duplo laço, sobre as tuplas de R e S a b 1 2 3 4 R b c 2 5 2 6 S (1,2) com (2,5) e (1,2) com (2,6) ambos Satisfazem a condinção; EXISTS(<subconsulta>) é verdadeiro se e somente se o resultado da subconsulta não é vazio. Exemplo: Usando Beers(name, manf) , procure as cervejas que são únicas por fabricante. 41 Exemplo: EXISTS 42 Operador ANY SELECT name FROM Beers b1 WHERE NOT EXISTS ( SELECT * Conjunto FROM Beers de cervejas com WHERE manf = b1.manf AND O mesmo fabricante como name <> b1.name); em b1, mas com n n n <ANY é usado para encontrar tuplas que tenham valores menores que o maior valor retornado por uma subconsulta. >ANY é usado para retornar tuplas que tenham valores maiores que o menor valor retornado de uma subconsulta =ANY funciona da mesma maneira que o operador IN. cervejas diferentes 43 44 Operador =ANY S S# S1 S2 S3 S4 S5 SELECT SNAME FROM S WHERE S# = ANY (SELECT S# FROM SP CITY London Paris Paris London Athens S# S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY 300 200 400 200 100 100 300 400 200 200 300 400 WHERE P# = ‘P2’); S# S1 S2 S3 S4 Operador <ANY SP SNAME STATUS Smith 20 Jones 10 Blake 30 Clark 20 Adams 30 SNAME RESULTADO Smith Jones Blake Clark S SELECT S# FROM S WHERE STATUS < ANY (SELECT STATUS FROM S); S# S1 S2 S3 S4 S5 SNAME STATUS Smith 20 Jones 10 Blake 30 Clark 20 Adams 30 CITY London Paris Paris London Athens S# S1 S2 S4 45 Operador ALL n n n Exemplo: ALL x <> ALL(<subconsulta>) é verdadeiro se somente se para cada tupla t na relação, x não é igual em t. q 46 n Isto é, x não está no resultado da subconsulta. Qualquer operador de comparação pode ser usado ao invés de <> Exemplo: x >= ALL(<subconsulta>) significa que nenhuma tupla é maior que x no resultado da subconsulta. 47 Usando Sells(bar, beer, price), procure as cervejas vendidas com maior preço. SELECT beer FROM Sells WHERE price >= ALL( SELECT price FROM Sells); 48 Exemplo: Interseção União, Interseção e Diferença n União, interseção e diferença de relações são expressas pelas seguintes formas, cada uma envolve subconsultas: q q q n (<subconsulta>) UNION (<subconsulta>) (<subconsulta>) INTERSECT (<subconsulta>) (<subconsulta>) EXCEPT (<subconsulta>) Usando Likes(drinker, beer), Sells(bar, beer, price), e Frequents(drinker, bar), procure os consumidores e cervejas tal que: 1. 2. Consumidores que gostem de cerveja, e Consumidor que frequenta pelo menos um bar que venda cerveja. 49 Solução 50 Semântica de Bags em SQL Consumidores que frequentam um bar que venda cerveja. (SELECT * FROM Likes) INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar ); n Sentenças SELECT-FROM-WHERE usam semântica de bags ao invés de conjuntos q q q 51 Seleção: preserva o número de ocorrências Projeção: preserva o número de ocorrências (não elimina duplicados) Produto Cartesiano, Junção: não elimina duplicados Exemplo: DISTINCT Eliminação de duplicadas n Força que o resultado seja um conjunto n SELECT DISTINCT . . . n Força que o resultado seja um Bag (não elimina duplicados) de todas as tuplas n Usando Sells(bar, beer, price), procure todos os preços diferentes cobrados por cervejas: SELECT DISTINCT price FROM Sells; Note que sem DISTINCT, cada preço poderia ser listado várias vezes, conforme o par bar/beer e seus respectivos preços. . . . UNION ALL . . . 53 Expressão de Junção (Bags) n n 54 Produto e Junção Natural SQL provê diversas versões de junções. Estas expressões podem ser consultas independentes ou usadas no lugar das relações em uma cláusula FROM. n n n 55 Junção Natural: R NATURAL JOIN S; Produto Cartesiano: R CROSS JOIN S; Exemplo: Likes NATURAL JOIN Sells 56 Junção Theta Outerjoins n n n R JOIN S ON <condição> Exemplo: usando Drinkers(name, addr) e Frequents(drinker, bar): Drinkers JOIN Frequents ON name = drinker; Nos dá todas as quadruplas (d, a, d, b) tal que drinker d vive no endereço (address) a e frequenta o bar b. R OUTER JOIN S é o núcleo de uma expressão outerjoin. Podendo utilizar outros como: 1. 2. 3. Opcional NATURAL em frente do OUTER. Opcional ON <condição> depois do JOIN. Opcional LEFT, RIGHT, or FULL antes de OUTER. . 57 Agregação n n 58 Exemplo: Agregação SUM, AVG, COUNT, MIN, e MAX pode ser aplicada em uma coluna de uma cláusula SELECT para produzir a agregação de uma coluna. Também, COUNT(*) conta o número de tuplas. n 59 Usando Sells(bar, beer, price), procure a média de preço da cerveja Bud: SELECT AVG(price) FROM Sells WHERE beer = ’Bud’; 60 Eliminando duplicados em uma agregação NULL ignorado em agregação n n n Use DISTINCT dentro de uma agregação. Exemplo: Procure o número de diferentes preços cobrados pela cerveja Bud: SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = ’Bud’; n NULL nunca contribui para uma soma, média ou contagem, e nunca pode ser o mínimo ou máximo de uma coluna. Porém, se não existe nenhum valor não-NULL em uma coluna, então o resultado da agregação é NULL. q Exceção: COUNT de um conjunto vazio é 0. 61 62 Exemplo: Efeito do NULL Agrupando SELECT count(*) FROM Sells WHERE beer = ’Bud’; n O número de Bares Que vendem Bud. n SELECT count(price) FROM Sells WHERE beer = ’Bud’; O número de bares que Vendem Bud por um preço Conhecido. 63 Expressões SELECT-FROM-WHERE podem ser seguidas por um GROUP BY e uma lista de atributos. O resultado é agrupado de acordo com os valores destes atributos (em GROUP BY), e qualquer agregação é aplicada somente em cada grupo. 64 Exemplo: Agrupando n Exemplo: Agrupando n Usando Sells(bar, beer, price), procure a média de preço para cada cerveja: SELECT beer, AVG(price) FROM Sells GROUP BY beer; beer Bud … AVG(price) 2.33 … Usando Sells(bar, beer, price) e Frequents(drinker, bar), procure por cada consumidor ,a média de preço da Bud nos Bares que eles frequentam: Computa SELECT drinker, AVG(price) todos os drinker-barFROM Frequents, Sells price triples WHERE beer = ’Bud’ AND para Bud. Frequents.bar = Sells.bar Agrupa GROUP BY drinker; Por consumidor 66 65 Restrição em lista SELECT com Agregação n Exemplo de consulta Ilegal n Se qualquer agregação é usada, então cada elemento da lista do SELECT deve ser também: 1. 2. Agregado, ou Um atributo na lista do GROUP BY . n 67 Procurar o bar que vende Bud mais barato usando: SELECT bar, MIN(price) FROM Sells WHERE beer = ’Bud’; Esta consulta é ilegal em SQL. 68 Cláusula HAVING n n Exemplo: HAVING HAVING <condição> pode seguir uma cláusula GROUP BY . A condição é aplicada para cada grupo, e os grupos que não satisfazem a condição são eliminados. n Usando Sells(bar, beer, price) e Beers(name, manf), procure a média de preço destas cervejas que também são servidas em pelo menos três bares ou fabricandos por Pete’s. 69 Solução 70 Requisitos em condições HAVING Cervejas agrupadas por pelo Menos 3 non-NULL bares e Também grupos de cervejas Fabricadas por Pete’s. SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = ’Pete’’s’); n n Qualquer coisa vai em uma subconsulta. Fora das subconsultas, elas podem referenciar atributos somente se eles : 1. Cervejas Fabricadas por Pete’s. 71 2. Um atributo que agrupa, ou Agregado (mesma condição como em cláusulas SELECT com agregação). 72 Modificação de Banco de Dados Inserção Um comando de modificação não retorna resultado (como em uma consulta). Três tipos de modificações: n n n Insert uma ou mais tuplas. Delete uma ou mais tuplas. Update o(s) valor(es) de uma ou mais tuplas n Inserir uma única tupla INSERT INTO <relação> VALUES ( <lista de valores> ); Exemplo: adiciona em Likes(drinker, beer) o fato que Sally gosta de cerveja Bud. INSERT INTO Likes VALUES(’Sally’, ’Bud’); 73 Exemplo: Especificando Atributos Especificando atributos em INSERT n n Pode-se usar uma lista de atributos. Duas razões para fazer isto: 1. 2. 74 n Desconsiderar como os atributos estão ordenados na relação. Não se tem os valores para todos atributos, e queremos que o sistema preencha os componentes com NULL ou um valor padrão. Outra maneira de registrae o fato que Sally gosta da cerveja Bud para Likes(drinker, beer): INSERT INTO Likes(beer, drinker) VALUES(’Bud’, ’Sally’); 75 76 Adicionando valores padrão (default) n n Exemplo: Valores Padrões (Default) Numa sentença CREATE TABLE, podemos definir um valor DEFAULT para um atributo Quando uma inserção de uma tupla não tem valor para este atributo, o valor DEFAULT será usado. CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT ’123 Sesame St.’, phone CHAR(16) ); 77 78 Exemplo: Valores Padrões (Default) Inserindo várias Tuplas INSERT INTO Drinkers(name) VALUES(’Sally’); Resultando em uma tupla: n name Sally address 123 Sesame St Podemos inserir o resultado inteiro de uma consulta em uma relação da seguinte forma: INSERT INTO <relação> ( <subconsulta> ); phone NULL 79 80 Solução Exemplo: Inserindo uma subconsulta Outro consumidor n Usando Frequents(drinker, bar), como entrada para uma nova relação PotBuddies(name) todos os potenciais colegas de Sally, ex: Consumidores que frequentam pelo menos um bar que Sally também frequenta. INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = ’Sally’ AND d2.drinker <> ’Sally’ AND d1.bar = d2.bar ); Pares de tuplas Consumidores onde o Primeiro é Sally, o Segundo é outro Diferente de Sally, e Que frequentem o Mesmo Bar. 81 Remoção n 82 Exemplos: Remoção Para remover tuplas que satisfazem uma condição de uma relação: DELETE FROM <relação> WHERE <condição>; n 83 Remova de Likes(drinker, beer) o fato que Sally gosta de cerveja Bud: DELETE FROM Likes WHERE drinker = ’Sally’ AND beer = ’Bud’; 84 Exemplo: Remova todas as tuplas n Exemplo: Remova algumas Tuplas Torne a relação Likes vazia: Remova de Beers(name, manf) todas as cervejas que exista outra cerveja do mesmo fabricante. Cervejas com o DELETE FROM Beers b Mesmo fabricante e Um nome diferente WHERE EXISTS ( Do nome da cerveja SELECT name FROM Beers Representado pela tupla. WHERE manf = b.manf AND name <> b.name); n DELETE FROM Likes; n Note que nenhuma cláusula WHERE é necessária. 85 Semântica da Remoção --- (1) n n n n 86 Semântica de Remoção --- (2) Supondo que Anheuser-Busch fabrica somente Bud e Bud Lite. Supondo que iremos consultar a tupla b para a primeira Bud . A consulta não é vazia, por causa da tupla Bud Lite, então removemos a Bud. Agora, quando b é a tupla para Bud Lite, removemos esta tupla também? n n Resposta: Removemos a Bud Lite também. A razão é que a remoção tem dois estágios: 1. 2. 87 Marca todas as tuplas para que a condição em WHERE seja satisfeita. Remove as tuplas marcadas. 88 Atualização (Updates) n Exemplo: Update Para alterar atributos específicos em tuplas específicas de uma relação: UPDATE <relação> SET <lista de atributos> WHERE <condição sobre as tuplas>; n UPDATE Drinkers SET phone = ’555-1212’ WHERE name = ’Fred’; 89 Exemplo: Atualiza diversas tuplas n Altere o número de telefone de Fred para 555-1212: Faça como que o preço máximo para cervejas seja $4 UPDATE Sells SET price = 4.00 WHERE price > 4.00; 91 90