Baixe o app para aproveitar ainda mais
Prévia do material em texto
Aula 03 - Linguagem SQL 1 Aula 03 - Linguagem SQL Apresentação Structured Query Language, ou SQL, é a linguagem de pesquisa padrão para base de dados relacional e muitas das suas características foram inspiradas na álgebra relacional. Nesta aula, estudaremos a linguagem SQL – forma padrão de acesso aos Sistemas Gerenciadores de Banco de Dados Relacionais SGBDR. Serão apresentadas as suas partes, e faremos a correlação de seus comandos de consulta com as operações da Álgebra Relacional. Objetivos Traçar um esquema relacional e popular com dados; Demonstrar consultas SQL; Relacionar os comandos de consultas com as operações de álgebra relacional. Conhecendo a linguagem SQL A SQL - Structured Query Language, Linguagem Estruturada de Consulta) é o padrão mundial de acesso a banco de dados relacionais. Essa linguagem se originou da SEQUEL (Structured English Query Language), linguagem de consulta a banco de dados relacionais desenvolvida pela IBM na década de 1970 para seu SGBD relacional SYSTEM R. Com o sucesso da SQL, o Instituto Americano Nacional de Padrões ANSI estabeleceu um padrão inicial em 1986. No ano seguinte, o padrão foi adotado pela Organização Internacional de Padronização ISO sendo que, a partir desse ponto, outras organizações se envolveram no projeto gerando novas padronizações para linguagem. Padrões SQL SQL86 Primeira versão do padrão, lançada em 1986, consistindo praticamente na versão da linguagem criada pela IBM. SQL92 Lançada em 1992, inclui novos recursos tais como tabelas temporárias, novas funções, expressões nomeadas, valores únicos, instrução CASE e estabeleceu novos padrões de escrita para as consultas de junção. SQL99 SQL3 Aula 03 - Linguagem SQL 2 Lançada em 1999, implementou suporte às expressões regulares, recursos de orientação a objetos, queries recursivas, gatilhos e novos tipos de dados (boolean, LOB, array e outros). SQL2003 Lançada em 2003, inclui suporte básico ao padrão XML, colunas como autoincremento e instrução MERGE entre outros. Cada SGBD adere aos padrões em maior ou menor grau. Isso acarreta o surgimento dos dialetos do SQL, ou seja, a forma como cada SGBD provê suporte à linguagem. O efeito colateral desse fato é que uma consulta escrita para um SGBD, por exemplo Oracle, dependo dos recursos e funções utilizados pode não funcionar no SQL Server e vice-versa. Partes da SQL A linguagem SQL divide-se em partes, cada uma atendendo a uma necessidade específica: DDL - Linguagem de Descrição de DadosData Definition Language) Tendo como principais comandos create, alter e drop destinados respectivamente a criar, alterar e eliminar objetos de banco de dados como usuários, tabelas, índices etc. DML - Linguagem de Manipulação de DadosData Manipulation Language) Tendo como principais comandos create, alter e drop destinados respectivamente a criar, alterar e eliminar objetos de banco de dados como usuários, tabelas, índices etc. DCL - Linguagem de Controle de DadosData Control Language) Tendo como principais comandos grant e revoke destinados a conceder e revogar privilégios de acesso, respectivamente. Nesta disciplina apresentaremos alguns comandos básicos de SQL, mas não ensinaremos a linguagem. Outras disciplinas tratarão deste assunto de forma mais detalhada. Criando um esquema relacional Conforme visto na aula passada, os dados no modelo relacional são armazenados em tabelas. Para criarmos uma tabela, devemos definir o seu nome, suas colunas, os tipos de dados das colunas e suas restrições. Para se criar uma tabela, utiliza-se o comando CREATE TABLE. Aula 03 - Linguagem SQL 3 SQL - Table CREATE TABLE nome_da_tabela (nome_col1 tipo_col1 [restri_col1] [, nome_col2 tipo_col2 [restri_col2 ] [, column3 datatype, nome_coln tipo_coln [restri_coln] ); Onde: Palavra Chave Descrição nome_da_tabela Nome que será atribuído a tabela nome_coln Nome atribuído à coluna n tipo_coln Tipo de dado atribuído à coluna n restri_coln Define uma restrição de integridade automática, à qual os campos da coluna devem obedecer. Tipos de dados As colunas possuem um tipo de dados que podem armazenar de forma parecida com o conceito de tipo utilizado nas variáveis criadas em programas. Os SGBDs possuem uma variedade muito grande de tipos. Cada SGBD tem os seus conjuntos específicos, que são muitas vezes incompatíveis com outros SGBD. Por exemplo, o tipo numérico no Oracle é Number e no PostgreSQL Numeric, isSo faz com que o comando de criação de tabela feito para o Oracle tenha que ser adaptado se desejamos criar a tabela no PostgreSQL. Para os exemplos desta aula será utilizado o PostgreSQL como SGBD. Dessa forma utilizaremos nos comandos de criação de tabelas os seus tipos básicos que são: Tipos de Dados Descrição VARCHAR(tam) Caracter de tamanho variável, podendo ir de 1 até 8000 bytes, sendo limitado a tamanho. CHAR(tam) Caracter de tamanho fixo, podendo ser especificado no máximo a 8000 caracteres. INTEGER Número inteiro de 4 bytes podendo ir de 2147483648 a 2147483647. NUMERIC(i,d) Numérico com precisão, em i estabelecemos quantos dígitos tem o número e em d quantos na parte decimal, por exemplo para especificar um número com formato 99999.99. o tipo seria NUMERIC7,2. https://www.notion.so/nome_da_tabela-ac89c22e4dd84dfeadd8abff19fcc27c https://www.notion.so/nome_coln-2e44746e4e1744b19176be919318258d https://www.notion.so/tipo_coln-60d1ca2893564ebfa132dd9129cc08ec https://www.notion.so/restri_coln-c18bcf32e17849099898e4f0dc2516c6 https://www.notion.so/VARCHAR-tam-79c92b9883a44d73a4db666c7025c5ec https://www.notion.so/CHAR-tam-498e342ab23e4809a5b5f7a80b03ecb7 https://www.notion.so/INTEGER-1dae512a3e3b4ef19ddd54d5dd2e817d https://www.notion.so/NUMERIC-i-d-82d69e21db104508b35132e4829180df Aula 03 - Linguagem SQL 4 Tipos de Dados Descrição DATE Data entre 1 de janeiro de 4713 a.C. até 31 de dezembro de 32767 d.C. BIT Tipo booleano, armazena 0 (falso) ou 1 (verdadeiro). Restrições As restrições, normalmente chamadas constraint, visam estabelecer regras para orientar o SGBD na forma de manter a integridade do banco. Conforme vimos no modelo relacional, uma tabela deve possuir uma CHAVE PRIMÁRIA e pode ter chaves estrangeiras. Os SGBD possuem outros tipos de restrição, mas nesta aula iremos nos restringir a estas duas: Restrição Constraint) Descrição PRIMARY KEY Chave Primária) É a chave primária de identificação unívoca da tabela. Pode ser uma ou uma combinação de colunas. FOREIGN KEY Chave Estrangeira) É uma coluna que garante a integridade de uma relação entre duas tabelas, sendo referenciada por uma chave primária da outra tabela. Vamos ver um exemplo de criação. Considere o modelo abaixo: https://www.notion.so/DATE-dd041297f0be499d9a1b52aca38e2d47 https://www.notion.so/BIT-386e19b7b1e641ac97038a62e31facfa https://www.notion.so/PRIMARY-KEY-Chave-Prim-ria-c06125d804ff45f0a48c3eff3464f120 https://www.notion.so/FOREIGN-KEY-Chave-Estrangeira-ff2b88a1f21a492fb743fea37901f9d1 Aula 03 - Linguagem SQL 5 O comando para criar a tabela MODELO no PostgreSQL seria: CREATE TABLE MODELO ( COD_MOD NUMERIC(3) PRIMARY KEY, NOME VARCHAR(80)); Observe no comando a constraint PRIMARY KEY definindo a coluna COD_MOD como Chave Primária. Aula 03 - Linguagem SQL 6 Para vermos a tabela criada, a forma mais fácil é consultarmos o conteúdo de toda a tabela utilizando o comando: SELECT * FROM MODELO Observe a tela do PostgreSQL e veja o resultado. Esse comando é básico e lista todo o conteúdo da tabela. Note que a tabela está vazia, mas foi criada no banco com duas colunas. Aula 03 - Linguagem SQL 7 Chave Estrangeira (Foreign Key) Uma tabela, além de sua Chave Primária(PK), pode possuir uma Chave Estrangeira FK que nas tabelas FILHO referenciam colunas chaves nas tabelas PAI. Para estabelecer essa restrição, acrescentamos REFERENCES na definição da coluna: Vejamos um exemplo de utilização de FK criando a tabela VEICULO. Exemplo de utilização de FK CREATETABLE VEICULO (PLACA CHAR(7) PRIMARY KEY, COR VARCHAR(20), MODELO NUMERIC(3) REFERENCES MODELO(COD_MOD), PROPRIETARIO NUMERIC(8) REFERENCES PROPRIETARIO (COD_PROP), ANO_FAB CHAR(4), ANO_MOD CHAR(4), VALOR_SEGURADO NUMERIC(9,2)); Observando o comando, podemos notar que a coluna: PLACA é sua chave primária; PROPRIETARIO é uma chave estrangeira para a tabela PROPRIETARIO; MODELO é uma chave estrangeira para a tabela MODELO. Vamos criá-la no PostgreSQL Aula 03 - Linguagem SQL 8 Observe a mensagem de sucesso. Se uma tabela possui uma Chave Estrangeira para outra, ela tem que ser criada depois da tabela referenciada, senão ocorrerá um erro. E se desejarmos apagar uma tabela? Para isso temos o Comando Drop, cuja sintaxe é: DROP TABLE < NOME DA TABELA > Inserindo linhas nas tabelas Agora que você já aprendeu a criar e eliminar tabelas que criam o esquema do banco, vamos começar a mexer na sua instância, incluindo linhas nas tabelas. Inserindo linhas O comando INSERT insere linhas em uma tabela. Sua sintaxe básica é: insert into .< nome_tabela > (coluna1, coluna2, ..., colunan)values (valor1, valor2, ..., valorn); Onde: nome_tabela - É o nome da tabela a ser atualizada. Coluna n - É a coluna que queremos inserir. Valor n - É o novo valor associado à coluna a ser inserida. Vamos inserir uma linha na tabela de MODELO Aula 03 - Linguagem SQL 9 insert into MODELO (COD_MOD, NOME) values (101, 'CLASSIC'); A relação entre a lista de colunas COD_MOD, NOME e a lista de valores 101, ‘CLASSIC’) é posicional, portanto a coluna: COD_MOD receberá o valor 101; NOME receberá CLASSIC. Execute o comando no PostgreSQL. Observe a mensagem de linha inserida. A nossa tabela possui uma linha agora. Aula 03 - Linguagem SQL 10 No exemplo, se não fosse especificado um valor para a coluna id, a inserção resultaria em erro. O mesmo erro ocorreria se não tivéssemos valores para as colunas PRIM_NOME e ULT_NOME. já que são de preenchimento obrigatório (constraint not null). Consultando dados de uma tabela O comando SQL que permite recuperar dados de uma ou mais tabelas é o SELECT. Com esse comando, podemos materializar várias operações da Álgebra Relacional como Projeção, Seleção, Produto Cartesiano, Junção e Operações de Conjunto. Os componentes básicos do comando são: Select Lista as colunas que serão recuperadas; Aula 03 - Linguagem SQL 11 Se utilizarmos o artifício do * (asterisco) na cláusula SELECT, estaremos definindo que todas as colunas serão recuperadas. From Define a tabela que será recuperada. Veja a sintaxe abaixo: SELECT nome-col1, nome_col2, nome coln FROM nome_da_ tabela; SELECT * FROM nome_da_tabela; Onde: Palavra Chave Descrição nome_da_tabela Nome da tabela que contém os dados a serem recuperados. nome_coln Nome de uma coluna a ser recuperada. * (asterisco) Recupera todas as colunas da tabela. Por exemplo, para retornar todo o conteúdo da tabela proprietário o comando seria: Select * From Veiculo. Veja o resultado. https://www.notion.so/nome_da_tabela-d9a9b977b47d41ed88b523c221f8c7da https://www.notion.so/nome_coln-02171fe1cb9146669cea1ac4b7bafeed https://www.notion.so/asterisco-cda68a2dec4447889237f2aaf7c30c39 Aula 03 - Linguagem SQL 12 Esse comando corresponde a retornar a tabela na Álgebra Relacional, sem realizar Projeção nem Seleção, ou seja, a expressão algébrica corresponde apenas ao nome da tabela VEICULO. Observe que * identifica que não deverá ocorrer projeção. Realizando a projeção e seleção Realizando a projeção e seleção Se você deseja, por exemplo, realizar uma projeção na tabela VEICULO retornando apenas a cor e a placa do veículo, a expressão algébrica seria: π COR, PLACA VEICULO Já em SQL o comando seria: Select Cor, Placa From Veiculo Aula 03 - Linguagem SQL 13 A nossa tabela possui uma linha agora. As colunas a serem projetadas devem ser listadas na cláusula SELECT As colunas retornam na ordem em foram listadas; As colunas no SELECT devem estar separadas por virgula; Não deve existir virgula antes da cláusula FROM. Realizando a seleção Conforme você viu na aula passada a seleção implica em filtrar a linhas da tabela. Repare no conteúdo da tabela VEICULO Aula 03 - Linguagem SQL 14 Para selecionar todos os dados do veículo da placa LGA4674 a expressão algébrica seria: σ PLACA = ‘LGA4674’ VEICULO No SQL, para escrever o comando equivalente à expressão, devemos acrescentar uma nova cláusula WHERE ao SELECT, a qual estabelece uma condição que a linha deverá obedecer para fazer parte do conjunto resposta da consulta. No caso, apenas retornam as linhas cujo teste da condição dê como resposta verdadeiro. O comando equivalente à expressão seria então: SELECT *FROM VEICULOWHERE PLACA = ‘LGA4674’ Na construção das condições você pode utilizar os seguintes operadores relacionais: = igual<> diferente< menor que> maior que>= maior ou igual a<= menor ou igual a ⚠ Um cuidado que você deve tomar é com o tipo de dado que está utilizando para filtrar Realizando projeção e seleção Aula 03 - Linguagem SQL 15 Na aula anterior, você viu que na Álgebra Relacional devemos realizar primeiro a seleção e depois a projeção conforme a forma geral: π< lista de atributos > (σ< condição de seleção > (< nome da relação >)) Até agora foi realizada ou a projeção ou a seleção, mas como se faz as duas no mesmo comando? Realizando seleção com múltiplas condições Você pode especificar critérios complexos combinando várias condições de pesquisa. A utilização dos operadores lógicos AND e OR permite montar expressões para filtrar as linhas. Como toda expressão lógica, o operador AND somente retorna Verdadeiro(TRUE) se ambas as condições forem verdadeiras, enquanto o operador OR somente retorna FALSO FALSE se as duas condições forem falsas. Veja a tabela VEICULO Se você deseja a placa, a cor e o ano de fabricação dos veículos Vermelhos fabricados em 2014, a expressão algébrica seria: πPLACA, COR, ANO_FAB σCOR = ‘VERMELHO’ E ANO_FAB = ‘2014’ VEICULO E o comando seria: SQL - CREATE Table SELECT PLACA, COR, ANO_FAB FROM VEICULO Aula 03 - Linguagem SQL 16 WHERE COR = ‘VERMELHA’ AND ANO_FAB = ‘2014’ Veja o resultado. Realizando seleção com múltiplas condições Embora não tenha utilizada prática pode ser comandado um produto cartesiano em SQL A expressão VEICULO X MODELO em SQL é denominada junção cruzada e o comando correspondente é: SELECT * FROM VEICULO CROSS JOIN MODELO Aula 03 - Linguagem SQL 17 Este comando retorna 50 linhas, pois são 5 de modelo associadas a cada uma das 10 de veículo. Realizando junção O banco de dados de exemplo possui os dados dos modelos de veículos na tabela MODELO, os dos automóveis na tabela VEICULO e os dos proprietários na tabela PROPRIETARIO. Se você reparar no esquema da tabela VEICULOS, notará a existência de duas chaves estrangeiras, MODELO para a tabela MODELO e PROPRIETARIO para a tabela PROPRIETARIO que nos permitem saber, respectivamente, o modelo do veículo e seu proprietário. Se você deseja pegar dados dos veículos e de seu modelo, por exemplo, utilizará a FK MODELO para ligar as linhas das duas tabelas, fazendo uma junção. Como regra geral, podemos definir então que, para fazer junção, também conhecida como join, determinaremos as colunas em comum nas duas tabelas PK e FK e utilizaremos os seus valores para determinar as linhas que retornarão. Observe o conteúdo das tabelas VEICULO e MODELO Como visto na aula anterior, a forma geral da operação junção entre duas tabelas A e B é a seguinte: Aula 03 - Linguagem SQL 18 < condição de junção > é uma expressão relacional, normalmente utilizando o operador =, que determina qual coluna da tabela A deve ser comparada com a da tabela B. No nosso caso, para fazer a junção de veículo e modelo sem realizar projeção, a expressão seria: Em SQL a sintaxe de junção é: SELECT FROM tabela1 INNER JOIN tabela2 ON Onde: Na cláusula FROM se define o tipo de operação, nocaso INNER JOIN, que corresponde à junção interior, tipo mais comum de junção; Na subcláusula ON se escreve a condição de junção. Em SQL, o comando equivalente à expressão seria: Aula 03 - Linguagem SQL 19 SELECT * FROM VEICULO INNER JOIN MODELO ON MODELO = COD_MOD Utilizando operadores de conjunto Conforme vimos na aula 2, os operadores de conjunto são a UNIÃO, INTERSEÇÃO E DIFERENÇA, as quais existem na linguagem SQL através do operadores: UNION UNIÃO Aula 03 - Linguagem SQL 20 INTERSECT INTERSEÇÃO EXCEPT ( DIFERENÇA Veremos como cada um dos comandos funciona. Vamos lá. Realizando a união, interseção e diferença Realizando a união A operação de união retorna todas as linhas do primeiro conjunto acrescidas de todas as linhas do segundo conjunto, eliminando as duplicadas. Aula 03 - Linguagem SQL 21 JVeja a tabela VEICULO Se você desejasse listar todos os dados dos veículos dos modelo 105 ou que sejam da cor BRANCA, inicialmente teria de criar os dois conjuntos os da cor branca e os do modelo 105, para isso poderia escrever as seguintes expressões: C1 = σ COR = ‘BRANCO’ VEICULO C2 = σ MODELO = 105 VEICULO C3 = C1 U C2 Vamos entender a expressões: Inicialmente, é criada a relação C1 com o resultado da seleção dos veículos branco da tabela veículo; A seguir é criada a relação C2 com o resultado da seleção dos veículos do modelo 105 da tabela veículo; Realiza-se a junção entre C1 e C2, obtendo-se o conjunto dos veículos branco ou do modelo 105. Em SQL, a sistemática é semelhante, mas a primeira coisa que você deve entender é que a saída de um comando SELECT é uma tabela que pode ser utilizada em um novo comando. Veja as expressões convertidas para SQL. Para criar o conjunto de veículos do modelo 105 o comando seria: Aula 03 - Linguagem SQL 22 Observe que um veículo do modelo 105 é branco e tem placa TTZ0156. O comando para criar o conjunto dos brancos seria: Repare que tem um branco que não é do modelo 105, o de placa LGA4674. A partir de agora vem uma pequena diferença para a expressão mostrada. Não é necessário criar uma tabela C1 ou C2 para receber a saída do comando, basta escrever a primeira consulta, colocar o operador de UNIÃO UNION e escrever a segunda consulta: Note que: Aula 03 - Linguagem SQL 23 Retornou o veículo de placa LGA4674 por ser branco, apesar de não ser do modelo 105; O veículo de placa TTZ0156, apesar de ser branco e do modelo 105, presente nos dois conjuntos, retornou apenas uma vez. b) Realizando a interseção ASe você desejasse os veículos do modelo 105 da cor branca, portanto que aparecem nos dois conjuntos, utilizaria INTERSECT. A expressão algébrica e o comando SQL seriam, respectivamente: Expressão: (σ COR = ‘BRANCO’ VEICULO ∩ ( σ MODELO = 105 VEICULO Comando: Note que nesta solução a expressão algébrica não utilizou a criação de C1 e C2, colocamos as seleções direto com a operação de interseção, ficando em tudo similar ao comando SQL. c) Realizando a diferença As duas operações apresentadas (união e interseção) são comutativas, ou seja, tanto faz a primeira subconsulta ser pelo modelo e segunda pela cor ou o inverso, o resultado será igual. O mesmo não acontece na diferença Except). Neste caso, se a ordem for invertida, o resultado mudará. Para se retornar os veículos do modelo 105 que não são brancos, ou seja, que estão no conjunto do 105 e não estão no conjunto do branco, a expressão algébrica seria: (σ COR = ‘BRANCO’ VEICULO - ( σ MODELO = 105 VEICULO E o comando seria: Aula 03 - Linguagem SQL 24 Resumo das regras para utilização dos operadores de conjuntos Estamos chegando ao fim desta aula. Veja a seguir, um resumo das regras para utilização dos operadores de conjuntos: Os comandos SELECT participantes têm que ter o mesmo número de colunas, ou seja, devem ser compatíveis; As colunas correspondentes têm que ser do mesmo tipo de dado; Linhas duplicadas são automaticamente descartadas. Referências DATE, C. J. Introdução a sistemas de banco de dados. 7. ed. Rio de Janeiro: Campus, 2000. ELMASRI, R.; NAVATHE, S. B. Sistemas de banco de dados. 6. ed. São Paulo: Pearson Addison Wesley, 2011. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006.
Compartilhar