Baixe o app para aproveitar ainda mais
Prévia do material em texto
SUMÁRIO ............................................................................................. 9 .............................................................................................. 10 ............................................................................ 11 ...................................................................................... 11 ............................................................................................................... 12 ....................................................................................................................... 13 .......................................................................................................................... 14 ....................................................................... 15 .................................................................................................................... 15 ............................................................................................ 15 ....................................................................................... 15 ............................................................................................................ 15 .............................................................................. 16 .................................................................................................................................. 16 ........................................................................................ 16 .................................................................................................................................. 17 ....................................................................... 18 .......................................................................................... 18 .............................................................................................. 19 ......................................................................................................... 19 ............................................................................................................... 20 .......................................................................... 20 .................................................................................................................. 21 ............................................................................................ 21 ................................................................................................ 21 ................................................................ 22 ......................................................... 23 ....................................................................... 24 .................................................................................................... 25 ................................................................. 25 ................................................................................................................................. 27 ..................................................................................... 28 .................................................................................................... 28 ...................................................................................................... 29 ................................................................................................................................. 29 ........................................................................................................................... 30 ..................................................................................................... 32 ................................................................................................................................. 32 .......................................................................................................................... 32 ....................................................................................................... 33 ................................................................................................................................. 33 .......................................................................................................................... 34 ................................................................................................................................. 35 .................................................................................. 37 ....................................................................................... 37 ............................................................................................ 38 ............................................................................................................. 38 ................................................................................................. 38 ............................................................................................................... 41 ...................................................................................................................... 41 ................................................................................................................. 42 ..................................................................................................................................... 42 ..................................................................................................................................... 42 .................................................................................................................................... 43 ............................................................................................................................ 43 .......................................................................................................... 43 ........................................................................................................ 44 ...................................................................................................... 44 ......................................................................................................... 44 ......................................................................................................... 45 ................................................................................................................................. 45 ................................................................................................................. 46 ........................................................................................ 49 ....................................................... 49 .................................................................................. 50 ................................................. 50 .................................................................................... 51 ..................................................................................... 52 ............................................................................................... 52 .............................................................................................. 53 ................................................................................................................. 54 .......................................................................................... 54 .......................................................................................................55 ............................................................................ 56 ............................................................................................ 56 .......................................................................................................... 57 .................................................................... 58 ................................................................................................................................. 58 ........................................................................................................ 59 ................................................................... 59 ........................................................................................ 59 ................................................................................................................. 60 ........................................................................................... 61 ............................................................................................... 62 ............................................................................................. 62 .................................................................. 63 .............................................................................. 64 ............................................................................................................... 64 ............................................................................................................................ 65 ........................................................................................................................ 65 ............................................................................... 66 ................................................................................................................. 67 ............................................................................................................. 67 ....................................................................................................................... 68 ................................................. 68 .......................................................................................................................... 69 ..................................................... 69 ........................................................................................................................ 70 ................................................... 70 ....................................................................................................... 71 ......................................................... 71 ............................................................................................................. 72 ......................................................................... 73 ......................................................................................... 73 ............................................................................................. 74 ..................................................................................... 74 .................................................................................. 75 ................................................................................................................................. 75 ................................................................................................................. 76 .................................................................................................................................. 76 ........................... 77 .................................................................................................... 78 ........................................................................................................ 78 ................................................................................................... 80 ................................................................ 81 .................................................................................................. 81 .................................................................................................................................. 82 ................................................................................................................................. 83 ............................................................................................. 83 ........................................................................................................................................ 83 ................................................................................................................................... 84 ................................................................................................................ 86 .................................................................................................................... 86 .......................................................................................................................... 86 .......................................................................................................................... 87 ........................................................................................................................... 87 .......................................................................................................................... 87 ............................................................................................................. 88 .................................................................................................................. 88 ................................................................................................................................. 89 ................................................................................................................ 89 ...................................................................................................................... 89 ....................................................................................................................... 89 ..................................................................................................................... 90 .................................................................................................................... 90 ......................................................................................................................... 90 .......................................................................................................................... 91 O primeiro tipo de Banco de Dados a ser comercializado foi o Banco de Dados em Rede ou Reticular, também conhecido como CODASYL (Conference on Data Systems) ou DBTG (Database Task Group). Em 1960, a IBM e a North American Aviation desenvolveram em conjunto a DL/I (Data Language), que usava hierarquias para representar os relacionamentos. Surgia o Banco de Dados Hierárquico. A implementação mais popular foi o IMS (Information Management System). Os Modelos em rede e hierárquico eram conhecidos como sistemas de navegação e eram complexos e de difícil manutenção (os relacionamentos eram definidos através de ponteiros). Em 1970, o Dr. E. F. Codd publicou um paper intitulado “Um Modelo Relacional de Dados para Grandes Bancos de Dados Compartilhados”. Neste paper, o Dr. Codd propôs o modelo relacional para sistemas de banco dedados. Codd dizia: “... não pode ser tão difícil assim...” ao se referir aos modelos em rede e hierárquico. O modelo relacional é baseado na teoria dos conjuntos, e as operações sobre as relações (tabelas) são as operações da teoria dos conjuntos. Mais recentemente, surgiram os bancos de dados Objeto-Relacional, que se propõem a ser tanto banco de dados Relacionais como Bancos de Dados Orientados a Objetos. Os sistemas de gerenciamento de banco de dados relacionais (Relational Data Base Management System - RDBMS) tornaram-se populares devido a sua facilidade de uso e flexibilidade na sua estrutura. Figura 1-1: Modelo de banco de dados relacional Um banco de dados relacional representa os dados como uma coleção de relações e cada relação é representada por uma tabela bi-dimensional no banco de dados. Por exemplo, você poderia querer armazenar informações sobre todos os empregados de sua companhia. Em um banco de dados relacional, você cria várias tabelas para armazenar pedaços diferentes de informação sobre seus empregados, como uma tabela de empregado, uma tabela de departamento, e uma tabela de salário. Em um banco de dados relacional, você não especifica o caminho de acesso às tabelas, e também não necessita saber como os dados estão organizados fisicamente. Conjunto de objetos ou relações que armazenam os dados; Conjunto de operadores que podem agir nas relações para produzir outras relações; Integridade de dados para precisão e consistência. Figura 1-2: Relacionamento entre tabelas Cada tabela contém dados que descrevem exatamente uma entidade. Por exemplo, a tabela EMP contém informações sobre empregados. Devido aos dados de entidades estarem armazenados em diferentes tabelas, pode ser necessário combinar duas ou mais tabelas para responder uma questão específica. Por exemplo, pode-se querer saber a localização do departamento onde um empregado trabalha. Neste caso, você precisa de informações da tabela EMP (que contém os dados sobre os empregados) e da tabela DEPT (que contém informações sobre os departamentos). Um RDBMS permite relacionar os dados em uma tabela com os dados contidos em outra se utilizando as chaves estrangeiras. Uma chave estrangeira é uma coluna ou um conjunto de colunas que se referem a uma chave primária da mesma tabela ou de outra. A facilidade para relacionar dados em uma tabela com dados em outra tabela permite a organização das informações em unidades separadas. Os dados de empregados podem ser mantidos logicamente distintos de dados de departamentos armazenando-os em tabelas separadas. Tem o objetivo de capturar o significado dos objetos de interesse, obtendo uma descrição organizada num nível puramente informal dos requisitos de informação da organização. Representa as regras de negócios sem limitações tecnológicas, por isso é a etapa mais adequada para o envolvimento do usuário que não necessita de conhecimentos técnicos. Exemplo de um requisito de negócio: “Sou dono de uma escola que provê cursos de informática. Um curso pode ter várias inscrições, no qual deve ser especificado o nome, preço, duração, data de início e a avalição dada pelo aluno no final do mesmo”. Um estudo realizado na USA Air Force identificou que 41% dos problemas de projeto de software decorriam de falhas na definição dos requisitos. O custo para corrigir um erro encontrado durante a análise de requisitos é muitas vezes menor do que em qualquer das outras etapas do projeto de um sistema. Custos para corrigir um erro encontrado a cada fase do ciclo de vida de um software (fonte: Rational Software): Figura 1-3: Custos para corrigir um erro durante o ciclo de vida de um software Durante o modelo lógico, o modelo conceitual é convertido para um modelo lógico de dados de acordo com a abordagem do SGBD (Sistema Gerenciador do Banco de Dados) escolhido para implementação. O modelo lógico mostra as ligações entre as tabelas de banco de dados, as chaves primárias e os componentes de cada uma. O projeto lógico inclui considerações de eficiência, depende da classe de modelos de dados (ex: abordagem relacional) utilizada pelo SGBD. Futuras alterações no modelo lógico devem ser primeiro efetuadas sobre o modelo conceitual. Exemplo de modelo lógico relacional: Figura 1-4 : Exemplo de modelo lógico relacional É o mapeamento do esquema lógico de dados em um esquema físico de acordo com o SGBD específico, visando propiciar o acesso eficiente aos dados (adequação da visão lógica dos dados às características físicas do banco de dados armazenado). Descrição da implementação da base de dados. Descreve as estruturas de armazenamento e os métodos de acesso. O esquema físico depende das características específicas do SGBD (ex.: Oracle, PostgreSQL, SQLServer, MySQL, etc) Exemplos: Definição de tipos de dados – tipos controlados pelo SGBD, cadeias de caracteres de tamanho fixo X tamanho variável, escolha entre tipos de dados numéricos. Localização de tabelas – espelhamento de disco, localização de dispositivos físicos diferentes, alocação dinâmica de espaço, clusterização, particionamento físico de tabelas. Um modelo Entidade-Relacionamento é um dos meios mais efetivos para coletar, organizar e documentar necessidades de informação de uma organização. Um Modelo E-R documenta as necessidades de informação de uma organização em um formato claro e preciso. Usuários podem facilmente entender a linguagem e o conteúdo de um Modelo E- R. Um Modelo E-R pode ser desenvolvido facilmente e pode ser refinado. Um Modelo E-R provê um quadro claro do escopo das necessidades de informação de uma organização para uma aplicação. Um Modelo de E-R é uma ferramenta efetiva para integrar múltiplas aplicações, num desenvolvimento de projeto e/ou pacotes de aplicação adquiridos. Obs.: Esteja seguro de contemplar completamente as necessidades de informação de uma organização durante a fase de Modelagem de Negócios. Mudanças no modelo durante as fases seguintes do ciclo de vida do desenvolvimento do sistema podem significar um custo alto. Um modelo Entidade-relacionamento é independente do hardware ou software a ser utilizado para sua implementação. Uma entidade é um elemento de vital importância para o negócio da aplicação, sobre a qual alguma informação precisa ser conhecida e mantida. Um objeto de interesse para o negócio. Uma classe ou categoria de objetos. Um objeto com um nome. Exemplo: Os elementos a seguir poderiam ser entidades, uma vez que sobre as quais seria necessário manter informações importantes com relação às necessidades do negócio de um determinado Sistema de aplicação. EMPREGADO DEPARTAMENTO PROJETO Atributos são informações que descrevem a Entidade; são elementos específicos de informação sobre uma determinada Entidade que precisam ser conhecidos e mantidos. Exemplo: Possíveis atributos para a entidade EMPREGADO são: Número de identificação Nome Data de nascimento Salário Possíveis atributos para a entidade DEPARTAMENTO são: Número de identificação Nome Localização Obs.: Uma entidade deve obrigatoriamente possuir atributosque precisam ser conhecidos do ponto de vista de negócio ou então não é uma entidade dentro do âmbito das exigências do negócio. ENTIDADES ATRIBUTOS Qualquer coisa sobre a qual informações devam ser mantidas Descreve uma entidade. Possui um ou mais atributos. Não possui atributos. Se uma entidade não possui atributos talvez ela seja somente um atributo de outra entidade. Se um atributo possui um atributo, então ele é uma entidade. Pode possuir múltiplas ocorrências associadas com outra entidade. Possui um único valor para cada ocorrência de entidade. Tabela 2-1 : Diferenças entre entidades e atributos Representação de Entidades Representada por um retângulo com bordas arredondadas com qualquer dimensão. Nome de entidade no singular e único. Nome da entidade em letras maiúsculas. Sinônimo opcional para nome da entidade (em parênteses) Nome de atributos em letras minúsculas. Obs.: Um sinônimo é um nome alternativo para uma entidade. Sinônimos são úteis quando dois grupos de usuários utilizam nomes diferentes para a mesma entidade. Exemplo: Figura 2-1 : Exemplo de Representação de Entidades Exemplo: A entidade EMPREGADO possui uma instância para cada empregado no negócio. Carlos Magno, Ramiro Antunes, Ana Maria, João Augusto são instâncias da entidade EMPREGADO. A entidade DEPARTAMENTO possui uma instância para cada departamento na empresa. O departamento de Finanças, o de Vendas e o de Compras são instâncias da entidade DEPARTAMENTO. Cada instância deve ser unicamente identificável de outras instâncias da mesma entidade. Um atributo ou conjunto de atributos que exclusivamente identificam uma entidade é chamado de Identificador Único (UID). Uma entidade pode possuir mais de um atributo ou conjunto de atributos que a identificam, mas normalmente é apenas um. Não existe um nome específico para ele, mas com frequência encontramos com os seguintes nomes: ID, CODIGO e NUMERO. Exemplo: Cada empregado tem um número de identificação único, sendo este um candidato a identificador único para a entidade EMPREGADO. Passos para identificar e modelar entidades a partir de entrevistas 1. Examine os substantivos. Eles são de real significado para o negócio da aplicação? 2. Dê um nome para cada entidade identificada. 3. Verifique se realmente existem informações de interesse sobre a entidade em relação às necessidades empresariais que devem ser mantidas e controladas. 4. Cada instância da entidade é unicamente identificável? Qual atributo ou conjunto de atributos poderiam servir como seu UID? 5. Escreva o conceito da entidade, por exemplo, EMPREGADO tem significado como um trabalhador que presta serviços à empresa. 6. Escreva exemplos de instâncias da entidade, por exemplo, Carlos Magno e Ramiro Antunes. 7. Desenhe num diagrama cada entidade identificada e alguns de seus atributos já identificados. Dica: Não desqualifique um candidato à entidade muito cedo, pois poderão ser descobertos atributos adicionais de interesse para o negócio mais tarde. Relacionamento é uma associação bidirecional entre duas entidades ou entre uma entidade e ela mesma. Um relacionamento deve ser lido em duas direções. Primeiro leia o relacionamento em uma direção e então leia o relacionamento na direção contrária. Existem três tipos de relacionamentos: Relacionamentos Muitos para Um. Relacionamentos Muitos para Muitos. Relacionamentos Um para Um Relacionamentos Muitos para Um (M para 1 ou M:1) possuem a cardinalidade Um ou mais em uma direção e a cardinalidade Um e somente um na direção inversa. Figura 2-2 : Relacionamentos muitos para um Cada CLIENTE pode ser a origem de um ou mais PEDIDOS. Cada PEDIDO deve ser originado por um e somente um CLIENTE. Obs.: Relacionamentos M:1 são muito comuns. Relacionamento M:1 obrigatórios em ambas as direções são muito raros. Exemplo: relacionamento PEDIDO x ITEM. Relacionamentos Muitos para Muitos (M para M ou M:M) possuem a cardinalidade Um ou mais nas duas direções. Exemplo: Figura 2-3 : Relacionamentos muitos para muitos Cada PRODUTO pode ter um ou mais CURSO FORNECEDOR. Cada FORNECEDOR pode ter um ou mais PRODUTOS. Na prática, não é possível criar um relacionamento M:M apenas com duas entidades, por isso, adicionamos uma entidade de intersecção (entidade associativa) entre as duas entidades. Uma entidade de Intersecção frequentemente é identificada por seus dois relacionamentos originando as duas barras de UID. As relações da entidade de intersecção sempre são obrigatórias. Entidades de intersecção frequentemente representam entidades do negócio. Entidades de intersecção normalmente contêm artigos de consumo como quantidades e datas. Elas tendem a ter alto volume de registros. Relacionamentos Um para Um (1 para 1 ou 1:1) possuem a cardinalidade um e somente um nas duas direções. Exemplo: Figura 2-4 : Relacionamentos um para um Cada PRODUTO pode ser representado por uma e somente uma IMAGEM. Cada IMAGEM deve ser a representação de um e somente um PRODUTO. Obs.: Relacionamentos 1:1 são muito raros. Um relacionamento 1:1 obrigatório nas duas direções é muito raro. Entidades que possuem relacionamentos 1:1 podem ser na verdade uma única entidade. Faça um diagrama E-R claro, limpo e fácil de ler. Alinhe os retângulos das entidades pela linha superior. Desenhe os relacionamentos horizontalmente ou verticalmente; procure evitar que os relacionamentos se cruzem, se necessário utilize ângulos de 30 a 60 graus. Utilize bem os espaços do seu diagrama, distribuindo bem os objetos. Evite congestionar uma porção do diagrama. Em certas situações é interessante esticar o tamanho horizontal ou vertical de uma entidade para evitar cruzamentos de relacionamentos. Distribua as entidades seguindo a regra “Os corvos voam para o Leste ou para o Sul (Crows fly east or south)". Exemplo: Figura 2-5 : Diagrama exemplificando o layout desejável 1. Identifique e modele as entidades a partir das necessidades de informação a seguir. "Eu sou o gerente de uma empresa de treinamento que provê cursos técnicos. Nós possuímos muitos cursos, sendo que cada um possui um código, um nome, e um preço. Introdução ao Oracle e Programação em Java são dois de nossos cursos mais populares. Os cursos variam em duração de um a quatro dias. Um instrutor pode ser capaz de ministrar vários cursos. Rodrigo Ribeiro e Paula Andrade são dois de nossos melhores instrutores. Nós possuímos em nosso cadastro o nome de cada instrutor e número do seu telefone. Cada curso é agendado para uma determinada data de início, sendo ministrado por um único instrutor. Nós agendamos um curso e então alocamos um instrutor. Os alunos podem realizar vários cursos com o passar do tempo, e muitos deles fazem isto. Júlio Almeida da Telecom já realizou todos os cursos que nós oferecemos. Nós também registramos o nome de cada aluno, o número de telefone e o seu endereço. Alguns de nossos alunos não possuem telefone.” Normalizaçãoé um conceito de banco de dados relacional, mas seus princípios se aplicam a Modelagem de Dados Conceitual. Objetivo da Normalização do Modelo de Dados é eliminar redundâncias no modelo de dados. Um Banco de Dados normalizado é de fundamental importância para o sucesso da aplicação, visto que redundâncias podem comprometer seriamente a integridade das informações. Valide a colocação de cada atributo utilizando as regras de normalização. Primeira Forma Normal (1FN) Segunda Forma Normal (2FN) Terceira Forma Normal (3FN) Obs.: Um modelo Entidade-Relacionamento normalizado é facilmente traduzido em um design de banco de dados de relacional. A Terceira Forma Normal é a meta geralmente aceita para um design de banco de dados sem redundâncias. Existe ainda a Quarta Forma Normal (4FN) na modelagem de relacionamentos complexos. Para a maioria dos documentos e arquivos, a decomposição até a 3FN é suficiente para obter o esquema de um banco de dados correspondente ao documento. Regra: Todos os atributos da entidade devem possuir um único valor para cada instância da entidade. Eliminação de atributos repetitivos ou ocorrências. Verifique se cada atributo da entidade possui um único valor para cada instância da entidade. Caso haja algum atributo multivalorado, este deve dar origem a uma nova entidade, se ela ainda não existir ou, se essa entidade já existir, este atributo deve migrar para esta entidade. O atributo data contato pode possuir mais de um valor, visto que o cliente pode ser contatado várias vezes, logo este atributo está violando a 1FN. Da mesma forma o atributo telefone pode possuir mais de um valor, também violando a 1FN. Figura 3-1 : Diagrama exemplificando a violação da primeira forma normal O atributo data contato dá origem a uma nova entidade CONTATO e migra para esta nova entidade que tem um relacionamento Muitos para um com a entidade CLIENTE original. Este relacionamento faz parte do identificador de CONTATO juntamente com o atributo data contato. Da mesma forma, a entidade TELEFONE é criada. Figura 3-2 : Diagrama exemplificando a utilização da primeira forma normal Regra: Um atributo deve ser dependente de todo o identificador único principal (UID 1) de sua entidade. Eliminação de atributos com dependência de apenas uma parte do identificador único principal. Verifique se cada atributo da entidade é dependente de todo o identificador único principal. Caso haja algum atributo que dependa de somente parte do identificador único principal, este deve dar origem a uma nova entidade se ela ainda não existir ou, se essa entidade já existir, este atributo deve migrar para esta entidade. O atributo agencia nome depende somente do relacionamento para AGENCIA, ou seja, depende somente de parte do identificador único, logo, este atributo está violando a 2FN. Figura 3-3 : Diagrama exemplificando a violação da segunda forma normal O atributo nome agencia migra para a entidade AGENCIA, visto que esta entidade já existia. Figura 3-4 : Diagrama exemplificando a utilização da primeira forma normal Regra: Nenhum atributo não único pode depender de outro atributo não único. Eliminação de atributos com dependência indireta ou eliminação de atributos derivados. Verifique se cada atributo não único da entidade depende ou é derivado de algum outro atributo não único da entidade ou até mesmo se é derivado de atributos de outras entidades. Caso haja algum atributo nesta situação, este deve dar origem a uma nova entidade se ela ainda não existir ou, se essa entidade já existir, este atributo deve migrar para esta entidade. O atributo cliente nome depende do atributo não único cliente código, logo este atributo está violando a 3FN. Figura 4-5 : Diagrama exemplificando a violação da terceira forma normal Os atributos id_cliente, nome_cliente e situacao_cliente migram para a entidade CLIENTE, visto que esta entidade já existia na aplicação. Figura 4-6 : Diagrama exemplificando a utilização da terceira forma normal 1. A partir da breve descrição dos requisitos do usuário: a. Identifique e modele as entidades a partir das necessidades de informação descritas a seguir. Escreva uma breve descrição de cada entidade e inclua os atributos que você identificar para cada entidade. b. Identifique e modele os relacionamentos a partir das necessidades de informação descritas abaixo. c. Identifique os identificadores únicos principais (UID 1) a partir das necessidades de informação descritas abaixo. Descrição do usuário: "Eu sou o dono de uma locadora de DVDs. Nós temos mais de 3000 DVDs que precisamos controlar. Cada um de nossos DVDs tem um número de identificação. Para cada filme, nós precisamos saber seu título e categoria (por exemplo, comédia, suspense, drama, ação, guerra, ou ficção científica). Nós temos cópias múltiplas de muitos de nossos filmes. Para cada filme existe um código específico e registramos o seu título e descrição, e também são necessárias as informações de línguas de áudio e legenda do DVD. Nós frequentemente somos solicitados por filmes com atores específicos, por exemplo, Julia Roberts, Angelina Jolie, Brad Pitt e Tom Cruise sempre são populares. Assim, nós gostaríamos de manter registro dos atores principais de cada filme. Nem todos os nossos filmes possuem registro de atores e os Clientes gostam de saber nome real e a data de nascimento de cada ator. Nós cadastramos só atores que aparecem nos filmes em nosso estoque. Nós temos muitos clientes. Para cada cliente, registramos o primeiro e último nome, o telefone e o endereço atual. Nós precisamos manter registro das cópias de filmes que cada cliente está locando no momento. Um cliente pode locar múltiplas cópias de filmes em qualquer momento. Atualmente, controlamos e registramos o aluguel atual do cliente. “Mantemos registro do histórico de aluguéis de cada cliente.” Para realizar operações sobre as tabelas (relações) de um banco de banco de dados relacional, você executa comandos SQL (Structured Query Language). Com SQL, pode-se dividir, combinar e modificar as tabelas (relações) do banco de dados. Seguindo regras simples e as diretrizes apresentadas abaixo você pode construir comandos válidos que são tão fáceis de ler quanto de editar: Comandos SQL não fazem distinção entre maiúsculas e minúsculas, a menos que especificado. Podem ser escritos em uma ou mais linhas. Palavras chaves (keywords) não podem ser divididas em mais de uma linha ou abreviadas. As diferentes cláusulas são normalmente separadas em linhas distintas para facilitar a visualização e edição do comando. Tabulações e identações podem ser utilizadas para tornar o código mais legível. Palavras chaves normalmente são escritas em maiúsculo, enquanto as outras palavras, como nomes de tabelas e colunas, são escritas em minúsculo. SINTAXE CREATE DATABASE bdexemplo02 SINTAXE CREATE TABLE IF NOT EXISTS ALUNO ( id_aluno INT(11) NOT NULL AUTO_INCREMENT, nome VARCHAR(45) NULL, email VARCHAR(45) NULL, PRIMARY KEY (id_aluno)); TIPOS NUMÉRICOS Existem tipos de dados numéricos que podem se dividir em dois grandes grupos:os que estão em vírgula flutuante (com decimais) e os que não. TIPOS DE DADOS MYSQL Bit ou Bool um número inteiro que pode ser 0 ou 1. Integer, Int : número inteiro com ou sem signo. Com signo a margem de valores válidos é desde -2147483648 até 2147483647. Sem signo, a margem de valores é de 0 até 429.496.295 Float número pequeno em vírgula flutuante de precisão simples. Os valores válidos vão desde -3.402823466E+38 até -1.175494351E- 38,0 até desde 175494351E-38 até 3.402823466E+38. Decimal Número com vírgula flutuante. TIPOS DATA Na hora de armazenar datas, há de se ter em conta que MySQL não verifica se uma data é válida ou não e simplesmente comprova que o mês está compreendido entre 0 e 12 e que o dia está compreendido entre 0 e 31. TIPOS DE DADOS MYSQL Date tipo data, armazena uma data. A margem de valores vai desde o 1 de Janeiro de 1001 ao 31 de dezembro de 9999. O formato de armazenamento é de ano-mês-dia. DateTime Combinação de data e hora. A margem de valores vai desde o 1 de Janeiro de 1001 às 0 horas, 0 minutos e 0 segundos ao 31 de Dezembro de 9999 às 23 horas, 59 minutos e 59 segundos. O formato de armazenamento é de ano-mês-dia horas:minutos:segundos TimeStamp Combinação de data e hora. A margem vai desde o 1 de Janeiro de 1970 ao ano 2037. Time armazena uma hora. A margem de horas vai desde -838 horas, 59 minutos e 59 segundos. O formato de armazenamento é 'HH:MM:SS'. TIPOS DE CADEIA TIPOS DE DADOS MYSQL Char(n): armazena uma cadeia de longitude fixa. A cadeia poderá conter desde 0 até 255 caracteres. VarChar(n): armazena uma cadeia de longitude variável. A cadeia poderá conter desde 0 até 255 caracteres. Blob e Text: um texto com um máximo de 65535 caracteres. Tipos de chave Primária (primary key) Estrangeira (foreign key) Chaves Únicas (unique key) Praticamente todos os produtos comerciais incluem a definição de chaves. Chaves primárias (em inglês Primary Keys ou PK) referem-se a tuplas (conjuntos) de um ou mais campos cujos valores, considerando a combinação de valores de todos os campos da tupla, nunca se repetem e podem ser usadas como um índice para os demais campos da tabela do banco de dados. Em chaves primárias, não pode haver valores nulos nem repetição de tuplas. Simplificando, quando a chave primária é simples, ou seja, é formada por um único campo da tabela, esse campo não pode ter dois ou mais registros de mesmo valor, e também não pode conter nenhum registro nulo. Se a chave primária é composta, ou seja, formada por mais de um campo, os valores de cada campo podem se repetir, mas não a combinação desses valores. Exemplo: a tabela 'Livros_Autores' tem como chave primária (cod_livro, cod_autor). Podem existir nessa tabela os registros: (5, 9), (5, 10), (4, 9), (9, 5) Mas não podem existir dois registros (5, 9). Podemos inserir uma chave primária durante ou após a criação da tabela. Com a tabela já criada, o campo que escolhermos para ser a chave primária deve ter a opção NOT NULL adicionada. Uma chave estrangeira é chamada quando há o relacionamento entre duas tabelas. Sempre em chave estrangeira vai haver relacionamentos entre tabelas, por exemplo se uma tabela tem uma chave primária de outra tabela. Uma chave estrangeira é um campo que aponta para a chave primária de outra tabela. Ou seja, passa a existir uma relação entre essas duas tabelas. A finalidade da chave estrangeira é garantir a integridade dos dados referenciais, pois apenas serão permitidos valores que supostamente vão aparecer na Base de Dados. Caso nada seja especificado na definição de uma chave estrangeira, uma linha que contenha uma chave primária referenciada em chave estrangeira não pode ser excluída (regra RESTRICT). SINTAXE Especifica que uma linha não pode ser excluída caso existam chaves estrangeiras que a referenciem. Especifica que as chaves estrangeiras que referenciam a linha excluída devem ser tornadas vazias. Válido somente para chaves estrangeiras opcionais. Pode ser definida a propagação da exclusão da linha para as linhas que a referenciam. É uma candidata a identificar unicamente uma linha em uma tabela. Uma chave única ou uma chave primária deve ser única, nenhuma outra linha de uma tabela pode ter o mesmo valor ou combinação de valores. Dependendo da modelagem, uma tabela pode ter arbitrariamente muitas chaves únicas, mas ao menos uma chave primária. Chaves únicas podem também, assim como as chaves primárias, ser referenciadas por chaves estrangeiras. Para alterar uma tabela, use a instrução ALTER TABLE, que serve para modificar a definição original da tabela. Nem todas as modificações são permitidas, portanto modele bem o seu banco de dados antes de implementá-lo. SINTAXE ALTER TABLE ALUNO ADD dtcadastro DATE Esta instrução adiciona uma nova coluna com o valor vazio para todas as linhas. Os valores para as diversas linhas devem ser adicionados através de instruções da DML. Não pode ser especificada uma coluna com NOT NULL, pois os campos são criados como vazios, a não ser que um valor default seja especificado. Para eliminar completamente uma tabela (vazia ou não) da base de dados, é usada a instrução: SINTAXE DROP TABLE ALUNO Para inserir dados em uma tabela, utilizamos o comando INSERT. SINTAXE INSERT INTO ALUNO VALUES(null, 'José Carlos', 'josecarlos@targettrust.com.br', sysdate()); INSERT INTO ALUNO VALUES (null, 'Marta', 'marta@targettrust.com.br', sysdate()); INSERT INTO ALUNO VALUES (null, 'Simone','simone@targettrust.com.br', sysdate()); INSERT INTO ALUNO VALUES (null, 'Rafael', 'rafael@targettrust.com.br', sysdate()); INSERT INTO ALUNO VALUES (null, 'ROCK', 'rock@targettrust.com.br', sysdate()); O comando UPDATE nos possibilita modificar registros de uma tabela. Exemplo: SINTAXE UPDATE ALUNO SET nome = 'Renata' WHERE id_aluno = 1 O comando DELETE nos possibilita excluir registros de uma tabela. Exemplo: SINTAXE DELETE FROM aluno WHERE id_aluno = 1 1. Crie um banco de dados chamado ‘bdexemplo01’ e crie as tabelas definidas abaixo, incluindo os valores demonstrados: Tabela departamento Tabela funcionario Para extrair dados a partir de um banco de dados você precisa utilizar o comando SQL (structured query language) SELECT. Você pode precisar restringir as colunas que serão exibidas. Este capítulo descreve todos os comandos SQL que você precisa para executar estas ações. Este capítulo também cobre o uso de comandos psql para executar comandos SQL. A sintaxe básica da instrução de consulta em SQL é: SINTAXE SELECT <LISTA DE COLUNAS> FROM <TABELA> Banco de Dados de Exemplo: Tabela ALUNO: Tabela CURSO: Tabela INSCRICAO: Tabela TELEFONE: Exemplo de instruções de consulta em SQL (modelo básico) SINTAXE SELECT id_aluno, nome, email, dtcadastro FROM ALUNO Ou SINTAXE SELECT * FROM ALUNO Comandos Básicos SINTAXE SELECT columns FROM table Em sua forma mais simples, um comando SELECT deve incluir o seguinte: Uma cláusula SELECT que especifica as colunas a serem exibidas. Uma cláusula FROMque especifica as tabelas que possuem as colunas listadas na cláusula SELECT. Na sintaxe: SELECT é uma lista de uma ou mais colunas. column seleciona a coluna nomeada. FROM table especifica a tabela que contém as colunas. SINTAXE SELECT * FROM ALUNO Você pode exibir todas as colunas de dados de uma tabela colocando um asterisco (*) logo após a palavra chave SELECT. No exemplo acima, a tabela de ALUNOS possui três colunas: id_aluno, nome, email, dtcadastro. Você também pode exibir todas as colunas da tabela listando-as depois da palavra chave SELECT. Por exemplo, o seguinte comando SQL, como no exemplo acima, também exibe todas as colunas e todas as linhas da tabela ALUNO: SINTAXE SELECT id_aluno, nome, email, dtcadastro FROM ALUNO SINTAXE SELECT id_aluno, nome FROM ALUNO Você pode usar o comando SELECT para exibir colunas específicas da tabela especificando os nomes das colunas, separados por vírgulas. O exemplo acima exibe todos os ids e nomes da tabela ALUNO. Na cláusula SELECT, especifique as colunas que você quer ver, na ordem na qual você quer que elas sejam mostradas. Por exemplo, para exibir nome antes do id do aluno, você utiliza o seguinte comando: SINTAXE SELECT id_aluno, nome FROM ALUNO O comportamento dos cabeçalhos de colunas do select depende do fabricante. Normalmente o cabeçalho é o nome da coluna, mas ele pode ser maiúsculo, minúsculo, centralizado, alinhado a direita ou à esquerda. Ainda pode ser truncado para o tamanho da coluna ou não. Cabeçalhos de colunas tipo caracter e data podem ser truncados, enquanto cabeçalhos de colunas numéricas não podem ser truncados. Você pode substituir os cabeçalhos de colunas por um alias. Alias de colunas serão vistos posteriormente neste capítulo. SINTAXE SELECT nome FROM ALUNO Para exibir o resultado de uma consulta, o SQL normalmente utiliza o nome da coluna selecionada como seu cabeçalho. Em muitos casos, este título pode não ser descritivo e consequentemente pode ser difícil de entender. Você pode mudar o cabeçalho de uma coluna utilizando um alias (apelido) de coluna. Especifique o alias depois da coluna na lista da cláusula SELECT utilizando a palavra ‘AS’. O default de exibição dos cabeçalhos depende do fabricante do banco de dados. Se o alias possui espaços, caracteres especiais (como # ou $), ou deve diferenciar maiúsculas e minúsculas, coloque o alias entre aspas duplas (" "). SINTAXE SELECT nome as nome_aluno FROM ALUNO O exemplo exibe o nome de todos os alunos. Observe que a palavra chave AS foi utilizada antes do nome do alias da coluna. Você pode unir colunas do tipo caractere (CHAR ou VARCHAR) para criar uma expressão de caracteres usando o operador de concatenação (concat). Colunas em qualquer lado do operador são combinadas para fazer uma única coluna de saída. SINTAXE SELECT concat ('O aluno ',nome,' possui email ', email) as "Email" FROM ALUNO No exemplo, são concatenadas as colunas nome e e-mail, sendo que o resultado recebe o alias de "E-mail". Observe que o nome do aluno e o e-mail são combinados obtendo-se uma única coluna de saída. SINTAXE SELECT nome FROM ALUNO A menos que você indique o contrário, o sql exibe os resultados de uma consulta sem eliminar as linhas duplicadas. O exemplo acima exibe todos os nomes de alunos a partir da tabela ALUNOS. Observe que os nomes dos alunos são repetidos. SINTAXE SELECT DISTINCT nome FROM ALUNO Para eliminar linhas duplicadas do resultado da consulta, inclua a palavra chave DISTINCT imediatamente após a palavra SELECT. Você pode especificar múltiplas colunas depois da palavra DISTINCT. O qualificador DISTINCT afeta todas as colunas selecionadas, e o resultado representa uma combinação distinta das colunas. SINTAXE SELECT DISTINCT id_aluno, nome FROM ALUNO OPERADOR DESCRIÇÃO + Soma - Subtração * Multiplicação / Divisão Figura 5-1: Operadores aritméticos Você pode precisar modificar a forma como os dados são exibidos, por exemplo, executando cálculos. Isto é possível através do uso de expressões aritméticas. Uma expressão aritmética pode conter nomes de colunas, valores numéricos constantes e os operadores aritméticos. SINTAXE SELECT nome_curso, preco, format(preco/2, 2) FROM CURSO O exemplo acima utiliza o operador de divisão para calcular um desconto de 50% para todos os cursos e mostrar uma nova coluna preco/2 na tela. Note que a coluna resultante preco/2 não é uma nova coluna da tabela CURSO, sendo utilizada somente na exibição. Figura 5-2: Precedência dos operadores aritméticos Se uma expressão aritmética possui mais de um operador, os de multiplicação e divisão são avaliados primeiro. Se os operadores dentro de uma expressão são da mesma prioridade, então a avaliação é feita da esquerda para direita. Você pode usar parênteses para forçar a expressão colocada dentro deles a ser avaliada primeiro. SINTAXE SELECT nome_curso, preco, 2*preco+100 FROM CURSO O exemplo acima exibe o nome do curso e o preço do curso aumentado em 100% + R$ 100,00. Observe que a multiplicação é executada antes da adição. Nota: Use parênteses para reforçar a ordem padrão de precedência e melhorar a clareza do comando. Por exemplo, a expressão acima poderia ser escrita desta forma, sem mudança no resultado: (2*preco)+100. SINTAXE SELECT nome_curso, preco, 12*(preco+100) FROM CURSO Você pode alterar as regras de precedência usando parênteses para especificar a ordem na qual devem ser executados os operadores. Devido ao uso dos parênteses, a adição recebe prioridade sobre a multiplicação. Se o valor de alguma coluna em uma expressão aritmética é nulo, o resultado da expressão também é nulo. Por exemplo, se você tentar executar uma divisão por zero, você obtém um erro. Porém, se você divide um número por nulo, o resultado é nulo ou desconhecido. A partir do banco de dados ‘bdexemplo01’ 1. Liste o nome e data de nascimento dos funcionários. 2. Liste todas as colunas da tabela funcionário. 3. Liste a descrição e a localização dos departamentos. 4. Liste o id, o nome e a data de nascimento dos funcionários, apelidando a coluna data_nasc para ‘data_nascimento_funcionario’. 5. Concatene o nome e a cidade do funcionário da seguinte maneira: Ex.: ‘O funcionário Abel mora em Porto Alegre’. 6. Liste o nome e a cidade dos funcionários, suprimindo as cidades repetidas. 7. Altere o nome Abel para João. 8. Altere o salário dos funcionários aumentado de 10% para cada registro. Quando recuperar dados do banco de dados você pode necessitar restringir os registros que serão retornados ou especificar a ordem na qual as linhas serão exibidas. Este capítulo explica os comandos SQL que você utilizará para executar estas ações. Vamos imaginar que você queira exibir todos os alunos que apresentam nome igual a Douglas. O método de restrição é à base da cláusula WHERE em SQL. SINTAXE SELECT columns FROM table [WHERE condition(s)][LIMIT rows] Você pode restringir as linhas recuperadas pela consulta utilizando a cláusula WHERE. A cláusula WHERE contém uma condição que deve ser satisfeita, devendo estar imediatamente após a cláusula FROM. Na sintaxe: WHERE restringe a consulta para as linhas que satisfazem a condição. condition é composta de nomes de coluna, expressões, constantes, e operadores de comparação. LIMIT limita os registros retornados rows quantas linhas deverão ser retornadas A cláusula WHERE pode comparar valores em colunas, valores literais, expressões aritméticas ou funções. A cláusula WHERE consiste de três elementos: Nome da coluna Operador de comparação Nome de coluna, constante, função ou lista de valores. SINTAXE SELECT nome FROM ALUNO LIMIT 2 No exemplo, o comando SELECT recupera apenas 2 registros do total. SINTAXE SELECT nome FROM ALUNO WHERE nome = 'Douglas' No exemplo, o comando SELECT recupera o nome de todos os alunos cujo nome é Douglas. Observe que o nome Douglas foi especificado em minúsculas para assegurar que a comparação feita com a coluna cargo da tabela ALUNO esteja de acordo com os dados nela armazenados. Strings de caractere fazem distinção entre maiúsculas e minúsculas. SINTAXE SELECT nome_curso FROM CURSO WHERE nome_curso = 'PHP' Strings de caracteres e datas na cláusula WHERE devem ser inseridas entre aspas simples (''). Constantes numéricas, entretanto, não precisam estar, a não ser que se queira que o número seja tratado como uma string de caracteres. OPERADOR SIGNIFICADO = Igual a > Maior que >= Maior que ou igual a < Menor que <= Menor que ou igual a <> Diferente de Figura 6-1: Operadores de comparação Operadores de comparação são utilizados em condições que comparam uma expressão com outra. Eles são usados na cláusula WHERE da seguinte forma: Sintaxe: SINTAXE … WHERE expr operator value Sintaxe: SINTAXE … WHERE preco >= 30 … WHERE nome = 'JAVA' SINTAXE SELECT nome_curso, preco FROM CURSO WHERE preco >= 30 No exemplo, o comando SELECT recupera o nome e o preço a partir da tabela CURSO, quando o preço do curso for maior ou igual a 30.00. OPERADOR SIGNIFICADO Between…and… Entre dois valores(inclusive) In(list) Igual a um dos valores da lista Like Igual a um padrão de caracteres Is null Possui um valor nulo Figura 6-2: Outros operadores de comparação SINTAXE SELECT * FROM ALUNO WHERE id_aluno BETWEEN 2 AND 5 Você pode exibir linhas baseadas em um intervalo de valores utilizando o operador BETWEEN. O intervalo que você especifica é composto por um limite inferior e um limite superior. O comando SELECT acima recupera linhas da tabela ALUNO para qualquer aluno cujo id está entre 2 e 5 inclusive. Os valores especificados no operador BETWEEN fazem parte do intervalo, sendo também recuperados. Você deve especificar o limite inferior primeiro. SINTAXE SELECT id_curso,nome_curso, preco FROM curso WHERE nome_curso IN ('Java', 'Oracle' ) Utilize o operador IN para executar a comparação com os valores de uma lista. O exemplo acima exibe o id do curso, nome e preço de todos os cursos cujo nome é Java ou Oracle. O operador IN pode ser utilizado com qualquer tipo de dado. SINTAXE SELECT id_aluno, nome, email FROM ALUNO WHERE nome LIKE '%S' Às vezes você pode não saber o valor exato a pesquisar, então você pode selecionar linhas que combinem com um padrão de caracteres utilizando o operador LIKE. Podem ser utilizados dois símbolos para construir a string de procura. SÍMBOLO DESCRIÇÃO % Representa qualquer sequência de zero ou mais caracteres - Representa um único caractere qualquer O comando SELECT acima recupera o nome de aluno da tabela ALUNO para qualquer aluno cujo nome começa com um “S”. Observe que os nomes que começam com um “s” minúsculo não serão recuperados. O operador LIKE pode ser utilizado como um atalho para algumas comparações normalmente executadas com o operador BETWEEN. Os símbolos % e _ podem ser utilizados em qualquer combinação com literais de caracteres. O exemplo abaixo exibe os nomes de todos os alunos cujo nome possui a letra “A” como o segundo caractere. SINTAXE SELECT nome FROM ALUNO WHERE nome LIKE '_A%'; SINTAXE SELECT nome FROM ALUNO WHERE dtcadastro IS NULL O operador IS NULL testa os valores que são nulos. Um valor nulo é um valor que é indisponível, não atribuído, desconhecido ou inaplicável. Portanto, você não pode testá-los com (=) porque um valor nulo não pode ser igual ou diferente de qualquer valor. O exemplo acima recupera o nome de todos os alunos que não possuem uma data de cadastro. SÍMBOLO DESCRIÇÃO AND Retorna TRUE se ambas as condições resultarem TRUE OR Retorna TRUE se qualquer uma das condições resultarem TRUE NOT Retorna TRUE se a condição seguinte retornar FALSE Figura 6-3: Operadores lógicos Um operador lógico combina o resultado de duas condições para produzir um único resultado ou para inverter o resultado de uma única condição. Três operadores lógicos estão disponíveis em SQL: AND OR NOT Todos os exemplos mostrados até o momento especificaram somente uma condição na cláusula WHERE. Você pode usar várias condições em uma cláusula WHERE utilizando os operadores AND e OR. SINTAXE SELECT id_curso,nome_curso, preco FROM CURSO WHERE nome_curso LIKE '%H%' AND preco <= 40 No exemplo, ambas as condições devem ser verdadeiras para qualquer registro a ser selecionado. Portanto, um curso que apresente h em seu nome e seu preço e seja maior ou igual a 40 será selecionado. Todas as pesquisas do tipo caractere fazem distinção entre maiúsculas e minúsculas. A tabela abaixo mostra o resultado da combinação de duas expressões com o operador AND: AND TRUE FALSE UNKNOW TRUE TRUE FALSE UNKNOW FALSE FALSE FALSE FALSE UNKNOW UNKNOW FALSE UNKNOW SINTAXE SELECT id_curso,nome_curso, preco FROM CURSO WHERE nome_curso LIKE '%H%' OR preco <= 40 No exemplo, para um registro ser selecionado basta que uma das duas condições seja verdadeira. Portanto, um curso que apresenta a letra h em seu nome ou seu preço e seja maior ou igual a 40 será selecionado. A tabela abaixo mostra o resultado da combinação de duas expressões com o operador OR: OR TRUE FALSE UNKNOW TRUE TRUE FALSE TRUE FALSE TRUE FALSE UNKNOW UNKNOW TRUE UNKNOW UNKNOW SINTAXE SELECT id_curso,nome_curso, preco FROM CURSO WHERE id_curso NOT IN (1,2,3) O exemplo acima exibe o nome, o id do curso e o preço de todos os cursos cujo id não seja 1, 2 ou 3. A tabela abaixo mostra o resultado da aplicação do operador NOT a uma condição: NOT TRUE FALSE UNKNOW FALSE TRUE UNKNOW Nota: O operador NOT também pode ser utilizado com outros operadores SQL como BETWEEN, LIKE e NULL. SINTAXE... WHERE nome_curso NOT IN ('Java', 'Oracle') ... WHERE preco NOT BETWEEN 10 AND 20 ... WHERE nome_curso NOT LIKE '%A%' ... WHERE nome IS NOT NULL ORDEM OPERADOR 1 Todos os operadores de comparação 2 NOT 3 AND 4 OR Figura 6-4: Precedência de operadores SINTAXE SELECT id_curso,nome_curso, preco FROM CURSO WHERE (nome_curso = 'Java' OR nome_curso = 'Oracle') AND duracao = 20 No exemplo, existem duas condições: A primeira condição é aquela onde o nome do curso deve ser Java ou Oracle. A segunda condição é aquela onde a duração deve ser igual a 20. Portanto, lê-se o comando SELECT como segue: “Selecione a linha se o nome do curso for Java ou Oracle e se duração for igual a 20.” SINTAXE SELECT * FROM ALUNO ORDER BY nome A ordem das linhas recuperadas no resultado de uma consulta é indefinida. A cláusula ORDER BY pode ser utilizada para ordenar as linhas. Se utilizada, deve aparecer como a última cláusula do comando SELECT. Você pode ordenar por uma expressão ou por um alias de coluna. Sintaxe: SINTAXE SELECT columns FROM table [WHERE condition(s)] [ORDER BY {column, expr} [ASC | DESC]] Onde: ORDER BY especifica a ordem na qual as linhas recuperadas serão exibidas. ASC ordena as linhas em ordem ascendente. Esta é a ordenação padrão. DESC ordena as linhas em ordem descendente. Se a cláusula ORDER BY não for utilizada, a ordem em que as linhas serão recuperadas é indefinida, e o RDBMS pode não recuperar as linhas duas vezes na mesma ordem para a mesma consulta. Utilize a cláusula ORDER BY para exibir as linhas em uma ordem específica. SINTAXE SELECT * FROM ALUNO ORDER BY nome DESC A ordenação padrão dos dados é ascendente: Valores numéricos são exibidos com o valor mais baixo primeiro, por exemplo: 1–999. Valores tipo data são exibidos com o valor mais antigo primeiro, por exemplo: 01-JAN-92 antes de 01-JAN-95. Valores caractere são exibidos em ordem alfabética, por exemplo: A primeiro e Z por último. Valores nulos são exibidos por último para ordenações ascendentes e por primeiro para ordenações descendentes. Para inverter a ordem na qual as linhas são exibidas, especifique a palavra chave DESC depois do nome da coluna na cláusula ORDER BY. SINTAXE SELECT nome as nome_aluno FROM ALUNO ORDER BY nome_aluno Você pode utilizar um alias de coluna na cláusula ORDER BY. O exemplo acima ordena os dados alfabeticamente pelo nome. SINTAXE SELECT * FROM ALUNO WHERE dtcadastro IS NOT NULL ORDER BY dtcadastro, nome ASC Você pode ordenar os resultados das consultas por mais de uma coluna. Na cláusula ORDER BY, especifique as colunas separando-as por vírgulas. Se você quiser inverter a ordem de uma coluna, especifique DESC depois de seu nome. Você pode ordenar por colunas que não estão incluídas na lista da cláusula SELECT. A partir do banco de dados ‘bdexemplo01’ 1. Liste todos os campos de funcionários ordenados pela cidade. 2. Liste os funcionários que apresentam salário superior a R$ 1.500, 00 ordenados pela cidade. 3. Liste a data de nascimento, o nome e o endereço dos funcionários ordenados do mais novo ao mais velho. 4. Liste todos os campos dos funcionários que apresentam cidade ‘Porto Alegre’. 5. Liste todos os campos dos funcionários que apresentem a letra ‘a’ em seu nome. 6. Liste todos os campos dos funcionários que o nome se inicia com a letra ‘R’ e moram em Porto Alegre. 7. Liste todos os campos dos funcionários que o nome se inicia com a letra ‘R’ ou moram em Porto Alegre. 8. Liste o nome dos funcionários que não são do suporte técnico. 9. Liste o nome e a data de nascimento dos funcionários que apresentam salário entre R$ 800 e R$ 1.200. 10. Liste o nome e a cidade dos funcionários que apresentam salário maior que R$ 1.000. Quando dados de mais de uma tabela são requeridos, uma condição de join é utilizada. Linhas em uma tabela podem ser unidas a linhas em outra tabela de acordo com valores comuns que existem em colunas correspondentes, que normalmente são colunas de chaves primárias e estrangeiras. Quando escrever um comando SELECT que relaciona tabelas, preceda o nome das colunas com o nome da tabela para obter maior clareza e melhorar o acesso ao banco de dados. Se o mesmo nome de coluna existir em mais de uma tabela, o nome de coluna deve ser prefixado com o nome da tabela. Para unir n tabelas, você precisa de um mínimo de (n-1) condições de join. Portanto, para unir quatro tabelas, um mínimo de três joins são necessários. Esta regra pode não se aplicar se a tabela possuir uma chave primária concatenada. Neste caso mais de uma coluna é necessária para identificar cada linha de forma exclusiva. Left join é uma operação em consultas SQL para retornar todos os dados da coluna esquerda, independente de haver ou não dados na coluna da direita. Imagine que você deseja listar todos os clientes de uma empresa e os valores de compras dentro do mês. Se você deseja que apareça todos os clientes, independente de terem feito compras ou não, então o Left join é a junção ideal para esta consulta SQL. A junção Inner join é um pouco diferente do left join. Se aplicarmos ao exemplo anterior, veremos que ela só retornaria os clientes que tiveram compras no mês. O cross Join possui uma funcionalidade completamente diferente dos outros tipos de Join. Ele simplesmente obtém todos os registros de todas as tabelas e faz o produto cartesiano (ou seja, cada registro de uma tabela é relacionado com cada registro da outra tabela), obtendo assim, o número total de registros através da multiplicação do total de registros das tabelas envolvidas no Cross Join. Com base nesse modelo de dados abaixo, iremos exemplificar cada um dos joins a seguir. Imagem com os dados das tabelas Aluno e Telefone, lembrando que nem todos os alunos possuem telefone: SINTAXE SELECT * FROM ALUNO WHERE dtcadastro IS NOT NULL ORDER BY dtcadastro, nome ASC Neste exemplo com Inner Join, apenas dois alunos foram mostrados, pois apenas esses possuem registros na tabela Telefone. SINTAXE SELECT ALUNO.nome, TELEFONE.numero FROM ALUNO INNER JOIN TELEFONE ON TELEFONE.id_aluno = ALUNO.id_aluno O left join irá fazer a junção das duas tabelas “dando preferência” aos registros da tabela Aluno. Assim, todos os registros da tabela aluno serão mostrados, independente de haver correspondência na tabela Telefone. Quando não houver correspondência na tabela Telefone, será mostrado o valor NULL ou nulo. SINTAXE SELECT ALUNO.nome, TELEFONE.numero FROM ALUNO LEFT JOIN TELEFONE ON TELEFONE.id_aluno = ALUNO.id_aluno A junção cross join irá juntar todos os registros da tabela Aluno com todos os registros da tabela Telefone, formando um produto cartesiano. SINTAXE SELECT ALUNO.nome, TELEFONE.numero FROM ALUNO CROSS JOIN TELEFONE Você precisa qualificar os nomes das colunas no SQL com o nome da tabelapara evitar ambiguidade. SINTAXE SELECT ALUNO.id_aluno, ALUNO.nome, TELEFONE.numero FROM ALUNO INNER JOIN TELEFONE ON TELEFONE.id_aluno = ALUNO.id_aluno Se não existir nenhum nome de coluna comum entre as duas tabelas, não há necessidade de qualificar as colunas. Entretanto, você ganhará desempenho utilizando o prefixo de tabela porque você informa exatamente para o Servidor onde procurar pelas colunas. A exigência para qualificar os nomes de coluna ambíguos também é aplicável para colunas que podem ser ambíguas em outras cláusulas como SELECT ou ORDER BY. Qualificar os nomes de coluna com os nomes de tabela pode consumir muito tempo, particularmente se os nomes de tabelas forem longos. Você pode utilizar alias de tabela em vez de nomes de tabela. Da mesma maneira que um alias de coluna fornece outro nome para uma coluna, um alias de tabela fornece outro nome para uma tabela. Alias de tabela ajudam a manter o código SQL menor, utilizando menos memória. SINTAXE SELECT a.id_aluno, a.nome, t.numero FROM ALUNO a INNER JOIN TELEFONE t ON t.id_aluno = a.id_aluno Observe que no exemplo os alias de tabela são identificados na cláusula FROM. O nome da tabela é especificado por completo, seguido por um espaço e então pelo alias de tabela. A tabela ALUNO recebeu o alias A, enquanto que a tabela telefone recebeu o alias t. Alias de tabelas podem ter até 30 caracteres de tamanho, porém, quanto menor, melhor. Se um alias de tabela for utilizado para um nome de tabela específico na cláusula FROM, então este alias de tabela deve ser utilizado para substituir o nome da tabela em todo o comando SELECT. Alias de tabelas devem ser de preferência significativos. alias de tabela só é válido para o comando SELECT no qual foi declarado. A partir do banco de dados ‘bdexemplo01’ 1. Liste o nome do funcionário e a descrição do seu departamento. 2. Liste o nome do funcionário e a descrição do departamento apenas daqueles funcionários que moram em São Leopoldo. 3. Liste o nome e o salário do funcionário, e a descrição do seu departamento apenas daqueles funcionários que nasceram antes de 01-01-1988. 4. Liste o nome do funcionário e a descrição do seu departamento ordenados pela localização do departamento. O operador Union combina os resultados de duas ou mais queries em um único resultado de consulta. Todas as colunas correspondentes da pesquisa devem possuir o mesmo tipo de dado. Existem dois tipos de Union: o UNION e o UNION ALL. O operador UNION, por padrão, combina o resultado da execução das duas queries e então executa um DISTINCT a fim de eliminar as linhas duplicadas. Este processo é executado mesmo que não haja registros duplicados. Duplicaremos o nome de um fornecedor da tabela fornecedor para que possamos diferenciar o UNION do UNION ALL. SINTAXE SELECT... UNION SELECT... Exemplo: SINTAXE SELECT * FROM ALUNO UNION SELECT * FROM CURSO No exemplo acima, unimos todas as colunas da tabela aluno com todas as colunas da tabela telefone. Note que a consulta não retornou linhas duplicadas, afinal o UNION aplica um DISTINCT. O operador UNION ALL apresenta a função de combinar os resultados de duas consultas, assim como o UNION. A diferença entre os dois é que o operador UNION ALL não aplica o DISTINCT, selecionando todos os registros, mesmo os duplicados. Sintaxe: SINTAXE SELECT... UNION ALL SELECT... Exemplo: SINTAXE SELECT * FROM ALUNO UNION ALL SELECT * FROM TELEFONE No exemplo acima, note que fizemos praticamente a mesma consulta, só que agora com o UNION ALL. Note que a consulta retornou a linha duplicada que há na base de dados, nome = “Douglas”. Uma query com uma ou mais cláusulas OR pode ser reescrita utilizando o operador UNION ALL. Exemplo: SINTAXE SELECT nome FROM ALUNO WHERE id_aluno = 4 OR nome LIKE ‘%A%’ A consulta acima possui duas condições separadas na cláusula WHERE. Sendo assim, para que esta query utilize um índice, as duas colunas referenciadas (id_aluno e nome) deverão fazer parte do índice. Esta query pode ser reescrita utilizando-se o operador UNION ALL. Exemplo: SINTAXE SELECT nome FROM ALUNO WHERE id_aluno = 4 UNION ALL SELECT nome FROM ALUNO WHERE nome LIKE ‘%A%’ As chamadas funções de grupo permitem obter informações resultantes de processamento sobre valores pertencentes a um grupo de linhas, como média, desvio de padrão. As principais funções de grupo são: Retorna o número de linhas afetadas pelo comando. Exemplo: SINTAXE SELECT COUNT(*) FROM ALUNO Retorna os valores somados de N, ignorando valores nulos. Exemplo: SINTAXE SELECT SUM(preco) FROM CURSO No exemplo acima, a consulta realizada retornou o preço total de todos os cursos cadastrados na tabela CURSO. Extrai a média aritmética de um de um determinado grupo de linhas. Exemplo: SINTAXE SELECT AVG(preco) FROM CURSO O exemplo acima calcula o preço médio dos cursos. Retorna o menor valor de uma coluna em um grupo de linhas. Exemplo: SINTAXE SELECT MIN(preco) FROM CURSO No exemplo acima é retornado o preço mais baixo entre os cursos da tabela CURSO. Retorna o maior valor de uma coluna em um grupo de linhas. Exemplo: SINTAXE SELECT MAX(preco) FROM CURSO No exemplo acima é retornado o preço mais alto entre os cursos do banco de dados. Até este momento, todas as funções de grupos trataram a tabela como um grande grupo de informações. Às vezes, é necessário dividir a tabelas em grupos menores. Para esse propósito, dispomos da cláusula Group by. Exemplo: SINTAXE SELECT id_curso, COUNT(id_inscricao) FROM INSCRICAO GROUP BY id_curso No exemplo acima, agrupamos as inscrições pelo id do curso e contabilizamos quantas inscrições por cursos há. Da mesma forma em que é utilizado a cláusula WHERE para restringir as linhas que são selecionadas, poderá ser utilizado a cláusula HAVING para restringir grupos. Exemplo: SINTAXE SELECT id_curso, count(id_inscricao) FROM INSCRICAO GROUP BY id_curso HAVING id_curso = 2 No exemplo acima, agrupamos inscrições por id do curso e filtramos através da cláusula HAVING os cursos que possuem id igual a 2. A partir do banco de dados ‘bdexemplo01’ 1. Calcule a média dos salários dos funcionários. 2. Liste o maior salário dos funcionários. 3. Liste o menor salário ganho entre os funcionários. 4. Liste a quantidade de funcionários por departamento. As funções SQL formam o bloco básico mais avançado e são utilizadas para manipulação de valores de dados. Função para converter os valores de caracteres alfabéticos para letras minúsculas. Exemplo: SINTAXE SELECT LOWER(nome) FROM ALUNO Função para converter os valores de caracteres alfabéticos para letras maiúsculas. Exemplo: SINTAXE SELECT UPPER(nome) FROM ALUNO Obtém parte de uma string. Sintaxe: SINTAXE SUBSTR (string, nº inicial, nº final) Exemplo: SINTAXE SELECT SUBSTR(nome, 1, 3) AS nome
Compartilhar