Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 Modelo de Dados ................................................................................................................................... 4 Modelo Entidade-Relacionamento (MER) .............................................................................................. 5 Princípios Básicos e Conceitos Gerais .................................................................................................... 5 Entidades e Atributos ............................................................................................................................. 5 Relacionamento do Tipo Um para Um:................................................................................................... 5 Relacionamento do Tipo Um para Vários: .............................................................................................. 6 Relacionamento do tipo Vários para Vários: ........................................................................................... 7 Integridade Referencial:.......................................................................................................................... 8 Normalização de Tabelas:....................................................................................................................... 8 Primeira Forma Normal:......................................................................................................................... 9 Segunda Forma Normal:....................................................................................................................... 10 Terceira Forma Normal: ....................................................................................................................... 11 Roteiro para Normalização de Dados.................................................................................................... 11 Etapas na estruturação de um banco de dados: ...................................................................................... 12 Dicas para determinação dos campos em uma tabela:............................................................................ 13 Como escolher o campo que será a chave primária?.............................................................................. 14 SQL – APOSTILA ............................................................................................................................... 14 A LINGUAGEM SQL.......................................................................................................................... 15 Vantagens e Desvantagens da linguagem SQL...................................................................................... 18 O que é MySQL? ................................................................................................................................. 20 O que é um banco de dados relacional?................................................................................................. 20 Instalando o banco de dados ................................................................................................................. 21 Instalando o MySQL no Linux ............................................................................................................. 21 Os comandos CREATE e DROP .......................................................................................................... 22 O comando CREATE........................................................................................................................... 22 O comando USE................................................................................................................................... 23 O comando DROP................................................................................................................................ 23 Criando tabelas..................................................................................................................................... 24 O comando SHOW............................................................................................................................... 25 O comando DESCRIBE ....................................................................................................................... 25 IF NOT EXISTS: ................................................................................................................................. 25 Criando uma cópia de uma tabela ......................................................................................................... 25 Alterando o nome da coluna ................................................................................................................. 26 2 Alterando o tipo de coluna.................................................................................................................... 26 Renomeando uma tabela....................................................................................................................... 26 Excluindo / adicionando colunas e tabelas ............................................................................................ 26 Eliminando tabelas e colunas................................................................................................................ 26 Adicionando colunas ............................................................................................................................ 27 Adicionando colunas após uma outra determinada ................................................................................ 27 Utilizando índices................................................................................................................................. 27 Decidindo quais colunas incluir no índice ............................................................................................. 27 Criando um índice ................................................................................................................................ 27 Excluindo índices ................................................................................................................................. 28 Tipos de tabelas.................................................................................................................................... 28 O tipo MyISAM ................................................................................................................................... 28 O tipo Memory..................................................................................................................................... 29 O tipo MERGE..................................................................................................................................... 30 O tipo BDB .......................................................................................................................................... 31 O tipo InnoDB...................................................................................................................................... 31 Alterando o tipo de uma tabela ............................................................................................................. 31 Tipo de dados....................................................................................................................................... 32 Tipos numéricos ................................................................................................................................... 32 Modificadores AUTO_INCREMENT, UNSIGNED e ZEROFILL ....................................................... 33 AUTO_INCREMENT.......................................................................................................................... 33 UNSIGNED......................................................................................................................................... 34 Tipos de caractere ou de dadosde string ............................................................................................... 34 CHAR e VARCHAR............................................................................................................................ 35 TEXT e BLOB..................................................................................................................................... 35 Tipos variados...................................................................................................................................... 36 Tipo SET.............................................................................................................................................. 36 Modificadores adicionais de coluna ...................................................................................................... 37 Tipos de data e hora.............................................................................................................................. 38 Sintaxe básica da SQL.......................................................................................................................... 38 Comando INSERT ............................................................................................................................... 38 Comando SELECT............................................................................................................................... 39 Um outro caso, a cláusula WHERE ...................................................................................................... 40 3 Algumas funções que trabalham com a instrução SELECT ................................................................... 41 Um caso a parte: a união do INSERT INTO ... SELECT....................................................................... 42 Comando UPDATE.............................................................................................................................. 42 Comando DELETE .............................................................................................................................. 42 Trabalhando com Junções..................................................................................................................... 43 Chaves variadas do MySQL ................................................................................................................. 43 Chaves primárias.................................................................................................................................. 46 Chaves estrangeiras .............................................................................................................................. 46 Transações ........................................................................................................................................... 46 Stored Procedures................................................................................................................................. 47 Criando um Stored Procedure com parâmetros...................................................................................... 48 Criando Views ..................................................................................................................................... 49 Visualizando a estrutura de uma view................................................................................................... 49 Visualizando a criação da view............................................................................................................. 50 Excluindo uma view............................................................................................................................. 50 Criando Triggers (gatilhos)................................................................................................................... 50 Visualizando as triggers criadas............................................................................................................ 51 Administrando o MySQL ..................................................................................................................... 52 Confirmando o novo usuário ................................................................................................................ 53 Obtendo informações com SHOW........................................................................................................ 53 Para visualizar todos os usuários existentes no seu MySQL execute;..................................................... 53 Ferramenta de modelagem de tabelas para uitlização do MySql ............................................................ 54 Principais botões da ferramenta. ........................................................................................................... 55 Inicializando a confecção de tabelas. .................................................................................................... 58 Editing Options .................................................................................................................................... 59 DataBase Options:................................................................................................................................ 60 Relation Editor ..................................................................................................................................... 63 Conexão com o Banco de Dados e transferência das tabelas.................................................................. 64 DataBase Synchronisacion ................................................................................................................... 67 Incluir, excluir e alterar os dados da tabela............................................................................................ 68 4 Modelo de Dados O Modelo de Dados é uma descrição dos tipos de informações que estão armazenadas em um banco de dados ou a descrição formal da estrutura de um banco de dados. Para construir um modelo de dados, usa-se uma linguagem de modelagem de dados. Linguagens de modelagem de dados podem ser classificadas de acordo com a forma de apresentar modelos, em linguagens textuais ou gráficas. Cada apresentação do modelo recebe a denominação de Esquema de Banco de Dados. No projeto de banco de dados, normalmente são considerados dois níveis de abstração de modelos de dados, o do modelo conceitual e o do modelo lógico. Modelo Conceitual: São modelos que contém uma descrição do banco de dados de forma independente de implementação em um SGBD. O modelo conceitual registra que dados devem aparecer no banco de dados, mas não registra como estes dados estão armazenados a nível de SGBD. A técnica mais difundida de modelagem conceitual e a abordagem entidade-relacionamento (ER). Nessa técnica, um modelo conceitual é usualmente representado através de um diagrama, chamado de diagrama ou modelo entidade-relacionamento (DER / MER). Modelo Lógico: São modelos que representam esquematicamente as estruturas das tabelas de forma bastante próxima à existente fisicamente. Basicamente são apresentados os registros de cada tabela (inclusive seus campos) e seus relacionamentos elementares. O Modelo Relacional, o Modelo de Rede e o Hierárquico são exemplos deste tipo de representação. O modelo lógico é dependente do tipo particular de SGBD que está sendo usado. São vistos pelos DBA´s e pelos programadores. O conceito de instância, sempre muito presente, poderia ser definido como sendo o conjunto de dados que definem claramente um Banco de Dados em determinado instante. Devemos entender então o Banco de Dados como sendo não apenas um conjunto de dados digitados, mas também todo o esquema e regras armazenados e controladas pelo SGBD. 5 Modelo Entidade-Relacionamento (MER) O MER consiste em um conjunto de objetosbásicos chamados entidades e nos relacionamentos entre esses objetos. Ele foi desenvolvido para facilitar o projeto de banco de dados permitindo a especificação de um esquema de empresa. Tal esquema representa a estrutura lógica geral do banco de dados. Princípios Básicos e Conceitos Gerais A prática da Modelagem de Dados requer a compreensão de alguns conceitos básicos, sem os quais seria impossível a correta identificação das características dos dados alvo da análise e determinação dos critérios mais adequados para a sua organização. A seguir, são descritos os conceitos e o vocabulário relacionado à Modelagem de Dados. Entidades e Atributos Correspondem a quaisquer “objetos” do mundo real sobre os quais se deseja armazenar informações. São exemplos típicos de entidades: funcionário, empresa, produto, pedido, cliente, etc. Dada uma entidade que seja de nosso interesse, precisaremos guardar suas informações mais relevantes, de maneira que possamos descrevê-la com precisão sempre que necessário. Chamamos de atributos (ou propriedades) a esse conjunto de informações que descrevem uma entidade. Dessa forma, os atributos de um funcionário poderiam ser o seu Nome, Número de Matrícula, Cargo, Sexo, Salário, Data de Admissão, etc. Analogamente, os atributos de um produto seriam o seu Código, Descrição, Fornecedor, Unidade de Medida, etc. Uma entidade geralmente contém informações relativas a várias instâncias dela mesma. Por exemplo, a entidade FUNCIONÁRIO contém informações de todos os funcionários da empresa, e não apenas de um deles; a entidade PRODUTO armazena os atributos de todos os produtos com os quais a empresa trabalha, etc. Dizemos então que uma entidade tem ocorrências. Cada um dos funcionários sobre os quais guardamos informações na entidade FUNCIONÁRIO é uma de suas ocorrências. Relacionamento do Tipo Um para Um: Esta relação existe quando os campos que se relacionam são ambos Chaves Primárias em suas respectivas tabelas. Cada um dos campos não apresenta valores repetidos. Na prática existem poucas situações onde utilizaremos um relacionamento deste tipo. 6 Um exemplo poderia ser o seguinte: Imagine uma escola com um Cadastro de Alunos na tabela Alunos, destes apenas uma pequena parte participa da Banda da Escola. Por questões de design do Banco de Dados, podemos optar por criar uma Segunda Tabela "Alunos da Banda", a qual pode se relacionar com a Tabela Alunos através de um relacionamento Um para Um. Cada aluno somente é cadastrada uma vez na Tabela Alunos e uma única vez na Tabela Alunos da Banda. Poderíamos utilizar o Campo Matrícula do Aluno como o Campo que relaciona as duas Tabelas. Na Tabela Alunos da Banda poderíamos colocar apenas o Número da Matrícula do aluno, além das informações a respeito do Instrumento que ele toca, tempo de banda, etc. Quando fosse necessário buscar as informações tais como nome, endereço, etc, as mesmas podem ser recuperadas através do relacionamento existente entre as duas tabelas, evitando, com isso, que a mesma informação ( Nome, Endereço, etc) tenha que ser duplicada nas duas tabelas, inclusive aumentando a probabilidade de erros de digitação. Na Próxima Figura vemos o exemplo de um Relacionamento do tipo Um para Um entre as tabelas Alunos e Alunos da Banda. Relacionamento Um para Um entre as Tabelas Alunos e Alunos da Banda. Relacionamento do Tipo Um para Vários: Este, com certeza, é o tipo de relacionamento mais comum entre duas tabelas. Uma das tabelas (o lado um do relacionamento) possui um campo que é a Chave Primária e a outra tabela (o lado vários) se relaciona através de um campo cujos valores relacionados podem se repetir várias vezes. Considere o exemplo entre a tabela Clientes e Pedidos. Cada Cliente somente é cadastrada uma única vez ( por isso o campo Código do Cliente é uma chave primária, indicando que não podem existir dois clientes com o mesmo código), portanto a tabela Clientes será o lado um do relacionamento. Porém cada cliente pode fazer diversos pedidos, por isso que o Código de um Cliente poderá aparecer várias vezes na tabela Pedidos, tantas 7 vezes quantos forem os pedidos que o Cliente tiver feito. Por isso que temos um relacionamento do tipo Um para Vários entre a tabela Clientes e Pedidos, através do campo Código do Cliente, indicando que um mesmo Cliente pode realizar diversos pedidos. Na próxima figura vemos um exemplo de um Relacionamento Um para Vários entre as Tabelas Clientes e Pedidos, através do campo código do cliente: Relacionamento Um para Vários entre as Tabelas Clientes e Pedidos. Observe que o lado Vários do relacionamento é representado pelo símbolo do infinito ( ∞ ). No lado Um do relacionamento o campo é definido como uma Chave Primária ( Campo CódigoDoCliente na tabela Clientes) e no lado Vários não ( campo CódigoDoCliente na tabela Pedidos), indicando que no lado vários o Código do Cliente pode se repetir várias vezes, o que faz sentido, uma vez que um mesmo cliente pode fazer diversos pedidos. Relacionamento do tipo Vários para Vários: Seria uma situação onde em ambos os lados do relacionamento os valores poderiam se repetir. Vamos considerar o caso entre Produtos e Pedidos. Posso ter Vários Pedidos nos quais aparece um determinado produto, além disso vários Produtos podem aparecer no mesmo Pedido. Esta é uma situação em que temos um Relacionamento do Tipo Vários para Vários. Na prática não temos como implementar um relacionamento deste tipo, devido a uma série de problemas que teríamos. Por exemplo, na tabela Pedidos teríamos que repetir o Número do Pedido, Nome do Cliente, Nome do Funcionário, Data do Pedido, etc para cada item do Pedido. Para evitar este tipo de problema é bastante comum quebrarmos um relacionamento do tipo Vários para Vários em dois relacionamento do tipo Um para Vários. Isso é feito através da criação de uma nova tabela, a qual fica com o lado Vários dos relacionamentos. 8 Na figura abaixo temos a representação dos dois relacionamentos Um para Vários: Tabela Detalhes do Pedido ficou com o lado Vários dos Relacionamentos. Esta situação em que um relacionamento um para Vários é "quebrado" em dois Relacionamentos do tipo Um para Vários é bastante comum. Diversas vezes utilizamos esta técnica para eliminar uma série de problemas no Banco de Dados, tais como informação repetida e inconsistência de Dados. Integridade Referencial: A Integridade Referencial é Utilizada para garantir a Integridade dos dados entre as Tabelas Relacionadas. Por exemplo, existe um Relacionamento do tipo Um para Vários entre a Tabela Clientes e a Tabela Pedidos (Um Cliente pode Fazer vários pedidos ). Com a Integridade Referencial, não permite que seja cadastrado um Pedido para um Cliente ainda não Cadastrado. Também podemos garantir o seguinte: Quando o Código de um cliente for alterado na Tabela Clientes, podemos pedir para o SGBD que ele atualize, automaticamente, todos os Códigos do Cliente na Tabela Pedidos, de tal maneira que não fiquem Registros Órfãos, isto é , registros de Pedidos com um Código de Cliente que não existe mais na Tabela Clientes. Essa ação é conhecida como "Propagar atualização dos campos relacionados". Quando um Cliente for excluído da Tabela Clientes, podemos pedir para que o SGBD exclua, na tabela Pedidos, todos os Pedidos para o Cliente que está sendo Excluído. Normalização de Tabelas: O conceito de Normalização foi introduzido por Codd em um artigo sobre o Modelo Relacional em 1970. O objetivo da normalização é evitar os problemas provocados por falhas no Projeto do Banco de Dados, bem como eliminar a mistura de assuntos e as correspondentes redundâncias de dados. 9 Uma Regra de Ouro que devemos observar quando doProjeto de Banco de Dados é a de "Não Misturar assuntos em uma mesma Tabela". Por exemplo na Tabela Clientes devemos colocar somente campos relacionados com o assunto Clientes. Não devemos misturar campos relacionados com outros assuntos, tais como Pedidos, Produtos, etc. Essa "Mistura de Assuntos" em uma mesma tabela acaba por gerar repetição desnecessária dos dados bem como inconsistência dos mesmos. O Processo de Normalização aplica uma série de Regras sobre as Entidades de um Banco de Dados, para verificar se as mesmas estão corretamente projetadas. Embora existam 5 formas normais ( ou regras de Normalização ), na prática usamos um conjunto de 3 Formas Normais. Normalmente após a aplicação das Regras de Normalização, algumas tabelas acabam sendo divididas em duas ou mais tabelas, o que no final acaba gerando um número maior de tabelas do que o originalmente existente. Este processo causa a simplificação dos atributos de uma tabela, colaborando significativamente para a estabilidade do modelo, reduzindo-se consideravelmente as necessidades de manutenção. Vamos entender o Processo de Normalização na Prática, através de exemplos. Primeira Forma Normal: Uma relação está na 1FN se somente todos os domínios básicos contiverem somente valores atômicos (não contiver grupos repetitivos). Para atingir esta forma normal devemos eliminar os grupos de repetição. Como? Procedimentos: a) Identificar a chave primária da entidade; b) Identificar o grupo repetitivo e excluí-lo da entidade; c) Criar uma nova entidade com a chave primária da entidade anterior e o grupo repetitivo. A chave primária da nova entidade será obtida pela concatenação da chave primária da entidade inicial e a do grupo repetitivo. Abaixo temos um exemplo de como efetuar a normalização para a primeira forma normal: Não normalizada Normalizada usando a primeira forma normal (1FN) 10 Segunda Forma Normal: Ocorre quando a chave Primária é composta por mais de um campo. Neste caso, devemos observar se todos os campos que não fazem parte da chave de pendem de todos os campos que compõem a chave. Se algum campo depender somente de parte da chave composta, então este campo deve pertencer a outra tabela. Observe o Exemplo Indicado na Tabela da Figura abaixo: Tabela com uma Chave Primária Composta. Não está Na Segunda Forma Normal. A Chave Primária Composta é formada pela combinação dos Campos "NúmeroDaMatrícula" e "CódigoDoCurso". O Campo Avaliação depende tanto do CódigoDoCurso quanto do NúmeroDaMatrícula, porém o campo DescriçãoDoCurso, depende apenas do CódigoDoCurso. Com isso temos um campo que não faz parte da Chave Primária e Depende apenas de um dos campos que compõem a chave Primária, por isso que dizemos que esta tabela não está na Segunda Forma Normal. A Resolução para este problema também é simples: "Dividimos a Tabela que não está na Segunda Forma Normal em duas outras tabelas, conforme indicado pela figura abaixo, sendo que as duas tabelas resultantes estão na Segunda Forma Normal. Informações sobre Avaliações e Cursos em Tabelas Separadas. OBS -> A Distinção entre a Segunda e a Terceira forma normal, que veremos mais adiante, muitas vezes é confusa. A Segunda Forma normal, na maioria das vezes, está ligada a ocorrência de Chaves Primárias compostas. 11 Terceira Forma Normal: A relação deve estar em 2FN e não devem existir dependências funcionais transitivas, ou seja, atributos não-chave que dependem de outros atributos que não fazem parte de alguma chave da relação. Quando isto ocorre, dizemos que a tabela não está na Terceira Forma Normal, conforme indicado pela tabela da figura abaixo: Tabela com um Campo dependente de Outro campo que não a Chave Primária. Não está na Terceira Forma Normal. Observe que o Campo DescriçãoDoCurso depende apenas do Campo CódigoDoCurso, o qual não faz parte da Chave Primária. Por isso dizemos que esta tabela não está na terceira forma normal. Para Solucionar este problema, a solução também é simples. Novamente basta dividir a tabela em duas outras, conforme indicado pela figura abaixo. As duas tabelas resultantes estão na Terceira Forma Normal. Tabelas Resultantes que estão na Terceira Forma Normal. Com isso podemos concluir que como resultado do Processo de Normalização, iremos obter um número maior de tabelas, porém sem problemas de redundância e inconsistência dos dados. Roteiro para Normalização de Dados Desmembrar a tabela em uma ou mais tabelas sem grupos repetitivos de itens. Designar um ou mais atributos como Chave Primária das novas entidades. Estabelecer o relacionamento e a cardinalidade entre as novas entidades geradas e a entidade geradora. 12 Verificar a questão da variação temporal de certos atributos e criar relacionamento 1:N entre a entidade original e a entidade criada por questões de histórico Verificar a existência de atributos parcialmente dependentes da chave. Criar novas entidades, que absorverão os atributos com dependência funcional parcial, herdando a chave parcial. Estabelecer o relacionamento e a cardinalidade entre as novas entidades geradas e a entidade geradora. Verificar se não existem atributos dependentes de outros atributos não-chave. Destacar atributos dependentes de uma chave estrangeira e incorporar na tabela da chave estrangeira ou criá-las, se não existir. Eliminar os atributos obtidos por cálculos a partir de outro atributos da mesma tabela. Etapas na estruturação de um banco de dados: Determinar qual o objetivo do banco de dados. Isto ajuda na determinação de quais os dados devem ser armazenados. Determinar as tabelas necessárias. Após definirmos o Objetivo do Banco de Dados, as informações devem ser definidas e separadas em assuntos diferentes, tais como "Clientes", "Empregados", "Pedidos", pois cada um irá compor uma tabela no banco de dados. Determinar os Campos Necessários. Definir quais informações devem ser mantidas em cada tabela. Por exemplo, a tabela Clientes poderia ter um campo para o Código Do Cliente, outro para o Nome Do Cliente e assim por diante. Determinar quais campos serão as Chaves Primárias. Determinar, em cada tabela, quais campos serão utilizados como Chave Primária. Esta é uma etapa importante para a definição dos Relacionamentos que vem a seguir. Determinar os Relacionamentos. Decidir como os dados de uma tabela se relacionam com os dados de outras tabelas. Por exemplo, Clientes podem Fazer Vários Pedidos. Fornecedores podem fornecer Vários Produtos, etc. Refinar a Estrutura do Banco de Dados. Antes de inserir muitos dados, ou até mesmo antes de inserir qualquer dado, verificar se a estrutura contém erros, isto é, verificar se os resultados obtidos são os desejados. Isto, normalmente, pode ser obtido através do processo de Normalização. Caso necessário, deve-se alterar a estrutura do banco de dados. Com uma boa estrutura, gasta-se menos tempo na construção e manutenção do banco de dados e, ao mesmo tempo, assegura-se resultados mais rápidos e precisos. 13 Dicas para determinação dos campos em uma tabela: Relacionar diretamente cada campo ao assunto da tabela. Se um campo descreve o assunto de uma tabela diferente, este campo deve pertencer a outra tabela. O mesmo acontece quando uma informação se repete em diversas tabelas. Este é um indício de que existem campos desnecessários em algumas tabelas. Não Incluir dados Derivados ou Calculados. Não é recomendado armazenar o resultado de cálculos nas tabelas. O correto é que o cálculo seja executado quando necessitarmos do resultado. Incluir todas as informações necessárias. Como é fácil esquecer informações importantes, deve-se ter em mente todas as informações coletadas desde o início do processoe perguntar se com elas é possível obter todas os resultados desejados. Armazenar todas as informações separadamente. Existe uma tendência em armazenar informações em um único campo. Por exemplo, o nome do curso e o tempo de duração em um mesmo campo. Como as duas informações foram combinadas em um único campo, ficará difícil conseguir um relatório classificado pelo tempo de duração dos cursos. 14 Como escolher o campo que será a chave primária? Um bom Sistema Gerenciador de Banco de Dados (SGBD) é aquele que encontra e nos fornece, rapidamente, todas as informações necessárias que nele estejam armazenadas, mesmo em diferentes tabelas. Para que isto seja possível é necessário incluir um campo ou conjunto de campos que identifiquem de um modo único cada registro de uma tabela. Esta informação é chamada Chave Primária. Deve-se ter certeza que este campo (ou conjunto de campos) seja sempre diferente para cada registro, por não ser permitido valores duplicados em um campo de chave primária. Ao escolher campos de Chave Primária, considere os seguintes detalhes: Não é permitida duplicidade de valores ou nulos (informações desconhecidas). Caso não exista um identificador único para uma determinada tabela, pode-se usar um campo que numere os registros seqüencialmente. Pode-se utilizar o valor deste campo para encontrar registros. O tamanho da chave primária afeta a velocidade das operações, portanto, para um melhor desempenho, devemos utilizar o menor tamanho que acomode os valores necessários para armazenar no campo. SQL – APOSTILA INTRODUÇÃO Uma linguagem de consulta é a linguagem por meio da qual os usuários obtêm informações do banco de dados. Essas linguagens são, tipicamente, de nível mais alto que as linguagens de programação tradicionais. As linguagens de consulta podem ser categorizadas como procedurais ou não-procedurais. Em uma linguagem procedural, o usuário deve "ensinar" ao sistema a realização de uma seqüência de operações no banco de dados para obter o resultado desejado. Em uma linguagem não-procedural, o usuário descreve a informação desejada sem fornecer um procedimento específico para a obtenção dessas informações. Os sistemas de banco de dados comerciais oferecem uma linguagem de consulta que incorpora elementos de ambos os enfoques: procedurais e não-procedurais. Linguagens "puras": a álgebra relacional é procedural, enquanto o cálculo relacional de uma tupla e o cálculo relacional de um domínio são não-procedurais. Essas linguagens de consulta são concisas e formais, sem "o açúcar sintático" das linguagens comercias, mas ilustram as técnicas fundamentais para a extração de dados do banco de dados. 15 A LINGUAGEM SQL O nome "SQL" significa "Structured Query Language" - Linguagem Estruturada de Pesquisa. Essa linguagem, de grande utilização, teve seus fundamentos no modelo relacional de Codd (1970). Sua primeira versão recebeu o nome de SEQUEL ("Structured English Query Language"), sendo definida por D. D. CHAMBERLIN, entre outros, em 1974, nos laboratórios de pesquisa da IBM (Califórnia). Em 1975, foi implementado um protótipo de aplicação dessa nova linguagem. Entre 1976 e 1977, o SEQUEL foi revisado e ampliado, e teve seu nome alterado para "SQL" por razões jurídicas. Com esta revisão foi posto em prática um projeto ambicioso da IBM chamado System R. Novas alterações foram introduzidas na SQL, graças às idéias apresentadas pelos diversos usuários do ambiente. Devido ao sucesso dessa nova forma de consulta e manipulação de dados, dentro de um ambiente de banco de dados, a utilização da SQL foi se tornando cada vez maior. Com isso uma grande quantidade de SGBD's foi tendo como linguagem básica a SQL - SQL/DS e DB2 da IBM, ORACLE da Oracle Corporation, RDB da Digital, SYBASE da Sybase INC, e Microsoft® SQL ServerTM, entre outros. A SQL se tornou um padrão de fato, no mundo dos ambientes de banco de dados relacionais. Bastava agora se tornar de direito. Então, em 1982, o American National Standard Institute (ANSI) tornou a SQL padrão oficial de linguagem em ambiente relacional. Infelizmente, como todo padrão que se preze, existem hoje vários dialetos SQL, cada um, evidentemente, tentando ser mais padronizado que o outro. O modelo relacional é constituído basicamente de tabelas, cada qual contendo linhas (registros, tuplas) e colunas. Os registros na tabela não são ordenados e sua localização se faz por meio de um campo-chave, ou seja, um campo que assume o papel de chave primária da tabela. É por intermédio dessa chave que se identifica uma, e somente uma, ocorrência do valor contido no campo. Uma das razões da popularidade dos sistemas relacionais é a sua facilidade de manipulação e entendimento. A linguagem SQL foi desenvolvida especialmente para o ambiente relacional, podendo ser adaptada a qualquer ambiente não relacional. 16 A idéia original da SQL só previa seu uso de forma interativa. Após sofrer alguns acréscimos, ela passou também a ter capacidade de ser utilizada em linguagens hospedeiras, tais como: COBOL, FORTRAN, "C", etc. Atualmente, a linguagem SQL assume um papel muito importante nos sistemas de gerenciamento de banco de dados, podendo ter muitos enfoques, como apresenta a figura: • Linguagem interativa de consulta (query AdHoc)- Por meio de comandos SQL, os usuários podem montar consultas poderosas sem a necessidade da criação de um programa, podendo utilizar Forms ou ferramentas de montagem de relatório; • Linguagem de programação para acesso a banco de dados - Comandos SQL embutidos em programas de aplicação que acessam os dados armazenados; • Linguagem de administração de banco de dados - O responsável pela administração do banco de dados (DBA) pode utilizar comandos SQL para realizar suas tarefas; • Linguagem cliente/servidor - Os programas (cliente) dos computadores pessoais usam comandos SQL para se comunicarem por meio de uma rede local, compartilhando os dados armazenados em um único local (servidor). A arquitetura cliente/servidor minimiza o tráfego de dados pela rede; • Linguagem para banco de dados distribuído - A SQL auxilia na distribuição dos dados por meio de vários nós conectados ao sistema de computação. Auxilia também na comunicação de dados com outros sistemas; 17 • Caminho de acesso a outros bancos de dados em diferentes máquinas - A SQL auxilia na conversão entre diferentes produtos de banco de dados colocados em diferentes máquinas (de micro até mainframe). Por ser uma linguagem de numerosas aplicações, a SQL pode manipular objetos de diferentes classes entre as funções de um SGBD: • Definição de dados (DDL) - permite ao usuário a definição da estrutura e organização dos dados armazenados, e as relações que existem entre eles; • Manipulação de dados (DML) - permite ao usuário ou a um programa de aplicação, a inclusão, remoção, seleção ou atualização de dados previamente armazenados no banco; • Controle de acesso - protege os dados de manipulações não autorizadas; • Compartilhamento de dados - coordena o compartilhamento dos dados por usuários concorrentes, sem contudo interferir na ação de cada um deles; • Integridade dos dados - auxilia no processo de definição da integridade dos dados, protegendo contra corrupções, inconsistências e falhas do sistema de computação 18 Vantagens e Desvantagens da linguagem SQL Com o uso e a padronização da SQL, algumas vantagens são diretas: • Independência de fabricante - A SQL é oferecida em praticamente todos os SGBD's, e os que ainda não têm estão se encaminhando para lá. Com isso posso mudar de SGBD sem me preocupar com o novo que vai chegar; • Portabilidade entre computadores - A SQL pode ser utilizada desde um computador pessoal, passando poruma estação de trabalho, até um computador de grande porte; • Redução dos custos com treinamento - Baseado no item anterior, as aplicações podem se movimentar de um ambiente para o outro sem que seja necessária uma reciclagem da equipe de desenvolvimento; • Inglês estruturado de alto nível - A SQL é formada por um conjunto bem simples de sentenças em inglês, oferecendo um rápido e fácil entendimento; • Consulta interativa - A SQL provê um acesso rápido aos dados, fornecendo respostas ao usuário, a questões complexas, em minutos ou segundos; • Múltiplas visões dos dados - A SQL permite ao criador do banco de dados levar diferentes visões dos dados a diferentes usuários; • Definição dinâmica dos dados - Por meio da SQL, podem-se alterar, expandir ou incluir, dinamicamente, as estruturas dos dados armazenados com a máxima flexibilidade; Apesar de todas essas vantagens, algumas críticas são dirigidas à SQL: • A padronização leva a uma, natural, inibição da criatividade, pois quem desenvolve aplicações fica preso a soluções padronizadas, não podendo sofrer melhorias ou alterações; Mesmo enfrentando alguns problemas e críticas, a linguagem SQL veio para ficar, auxiliando de forma bastante profunda a vida dos usuários e analistas no trabalho de manipulação dos dados armazenados em um banco de dados relacional. E é sobre esse auxílio que iremos tratar, mostrando comandos e funcionalidades da SQL, por meio de exemplos práticos. Não iremos mostrar todos os comandos, principalmente os que foram definidos para serem utilizados dentro de uma linguagem hospedeira (cursor); apresentaremos os comandos para criação, atualização, alteração, pesquisa e eliminação de tabelas dentro de um ambiente relacional típico. 19 Uma instrução SQL consiste em três partes: As declarações de parâmetros A instrução manipulativa As declarações de opções Vejamos na tabela abaixo um resumo das cláusulas manipulativas e suas finalidades: Instrução Função SELECT Obtém um grupo de registros e insere os registros em um dynaset ou em uma tabela UPDATE Define os valores dos campos de uma tabela em uma atualização. DELETE FROM Remove registros de uma tabela. INSERT INTO Acrescenta um grupo de registros a uma tabela. Vejamos alguns exemplos da instrução SELECT: 1) Seleciona os campos "Primeiro nome" e "Sobrenome" de todos os registros da tabela Empregados. SELECT [Primeiro nome], [Sobrenome] FROM Empregados 2) Seleciona todos os campos da tabela Empregados. Note o uso parâmetro (*) indicando todos os campos da tabela indicada. SELECT Empregados.* FROM Empregados 3) Conta o número de registros que têm uma entrada no campo "Código postal" e coloca o título Contagem no topo da coluna. SELECT Count([Código postal]) AS Contagem FROM Clientes 4) Seleciona os campos "Primeiro nome" e "Sobrenome" de cada registro cujo sobrenome seja Pereira. SELECT [Primeiro nome], [Sobrenome] FROM Empregados WHERE [Sobrenome] = 'Pereira' 5) Seleciona os campos "Primeiro nome" e "Sobrenome" para Empregados cujos sobrenomes começam pela letra S. SELECT [Primeiro nome], [Sobrenome] FROM Empregados WHERE [Sobrenome] Like 'S*' Observações: FROM – Indica as tabelas utilizadas como fonte de dados. WHERE – Especifica as condições que os registros devem satisfazer para compor o subconjunto de dados. 20 O que é MySQL? MySQL é um sistema de gerenciamento de banco de dados relacional, multiencadeado, de código-fonte aberto e nível corporativo. O MySQL foi desenvolvido por uma empresa de consultoria na Suécia chamada inicialmente de TcX, depois, com a popularidade do MySQL, passou a se chamar MySQL AB. Seu desenvolvimento ocorreu quando estavam precisando de um sistema de banco de dados que fosse extremamente rápido e flexível. Foi, assim então, que eles criaram o MySQL, que é vagamente baseado em outro sistema de gerenciamento de banco de dados chamado de mSQL. O MySQL é rápido, flexível e confiável. É utilizado em muitos lugares por todo o mundo. Obs.: A propósito, à parte ''AB'' do nome da companhia é o acrônimo para a palavra sueca ''aktiebolag'', ou ''sociedade anônima''. Ela é traduzida para ''MySQL, Inc.'' De fato, MySQL Inc. e MySQL GmbH são exemplos de subsidiárias da MySQL AB. Elas estão localizadas nos EUA e Alemanha, respectivamente. O que é um banco de dados relacional? Um banco no mundo de cimento e tijolo é o lugar onde guardamos dinheiro. Um banco de dados também guarda, só que neste caso são dados. Chamamos de dados tudo que possamos inserir no computador, números, letras, caracteres, imagens e etc. Um banco de dados relacional é uma composição de tabelas e colunas que se relacionam entre si. Esses relacionamentos são baseados em um valor-chave que é contido em cada tabela, em uma coluna. 21 Instalando o banco de dados O MySQL tem diferentes formas de instalação quando se trata de sistemas operacionais. No caso do Windows, você pode baixar a última distribuição através do site: http://www.mysql.com/downloads. Instalando no Windows Procure pelo formato executável. O arquivo vem compactado no formato .zip. Descompacte e instale. A instalação, como não poderia deixar de ser, é feita por um assistente. Siga os passos até a finalização. Caso sua máquina tenha o sistema operacional Windows pertencente à família NT (NT, 2000 ou XP), o MySQL é instalado como serviço. Então basta iniciar ou parar o serviço, encontrado no Painel de Controle > Ferramentas Administrativas>Serviços. Você também pode utilizar o comando pelo prompt, desde que você saiba o nome do serviço do seu MySQL: Para iniciar o serviço net start mysql Para parar o serviço net stop mysql Instalando o MySQL no Linux O MySQL Server pode ser instalado no Linux de várias formas. A forma recomendada é a que está em formato RPM. Você deve baixar dois arquivos para instalar o MySQL na sua máquina. Esses arquivos são: MySQL-server-[versão].i386.rpm – para instalar o servidor mysqld no Linux MySQL-client- [versão].i386.rpm – para instalar o cliente mysql para executar os comandos no Linux. A instalação poderá ser feita através do comando rpm, no Shell do seu Linux. Um exemplo seria: Shell> rpm –ivh MySQL-server-5.0.1.i386.rpm MySQL-client-5.0.1.i386.rpm A versão RPM já vem com pré-configurações e assim que ocorrer a instalação, para iniciar ou parar o servidor, a seguinte sintaxe poderá ser feita: Shell>/etc/init.d/./mysql start – para iniciar o servidor MySQL Shell>/etc/init.d/./mysql stop – para parar o servidor MySQL 22 Acessando o banco de dados MySQL No Windows Se você estiver usando o sistema operacional Windows e utilizou a instalação padrão do programa, abra o prompt de comando e digite a seqüência: cd\mysql\bin Lembrando que você deve estar no drive em que o MySQL está instalado. Por padrão você o instala no drive C. Digitando o comando a seguir você entra no MySQL. mysql –u root -p Tecle ENTER e receberá o pedido de senha: password Digite a senha que você configurou na instalação e tecle ENTER novamente. Nota: Versões mais modernas do MySQL para o sistema operacional Windows não necessitam de tantos passos para iniciar, bastando ir até o atalho encontrado no menu Iniciar do sistema e no atalho do MySQL iniciar o prompt de comando encontrado neste local. No Linux Se você utilizou a instalação binária, em rpm (recomendado), basta abrir o terminal e digitar a seqüência: shell>mysql –u root Se já estiver logado como root, no seu sistema operacional, não há necessidade de colocar o –u root depois do comando mysql. Os comandos CREATE e DROP Quando pensar nos comandos CREATE e DROP, você deve imaginar equipamentos de terraplanagem, caminhões basculantese guindastes, porque são ferramentas que você utiliza para criar o seu banco de dados. Esses comandos, embora raramente utilizados, são os mais importantes. O comando CREATE Há muitas maneiras diferentes de criar banco de dados no MySQL. Ao criar um banco de dados, você normalmente terá o layout inteiro pronto. Normalmente adicionaria as tabelas imediatamente depois de criar o 23 banco de dado, mas, teremos uma etapa por vez. A primeira etapa para criar um banco de dados no MySQL é inserir o comando CREATE DATABASE nome_banco_de_dados da SQL (Structured Query Language) no monitor MySQL, onde nome_banco_de_dados é o nome do banco de dados que você está criado. No prompt de comando, no monitor do MySQL, insira o seguinte comando: mysql> CREATE DATABASE livraria; Note que não foi utilizado acentuação e em casos de palavras compostas não insira espaços, se for o caso insira sublinhado “ _ ” . O comando USE Depois de confirmado a criação do banco de dados, você deverá utilizar o comando USE para utilizar o banco de dados livraria. USE livraria; Um ponto importante é que o MySQL não torna ativo o banco de dados que você criou, isso deve ser implícito. O comando DROP O comando DROP é semelhante ao comando CREATE. Enquanto o último cria um banco de dados, o primeiro exclui. O comando DROP do SQL é imperdoável. Não há caixas de confirmação para ver se você tem certeza. Este comando exclui o banco de dados e tudo o que estiver nele. É só ir até o prompt de comando e no monitor do MySQL e digitar: mysql> DROP DATABASE livraria; Isso excluirá o banco de dados livraria e tudo o que estiver nele. 24 Criando tabelas Criar tabela no MySQL é uma tarefa relativamente fácil. Para se criar uma tabela basta usar a seqüência: shell>mysql –u root Após estar no monitor do MySQL digite a seguinte seqüência: mysql> CREATE DATABASE livraria; mysql> USE livraria; mysql> CREATE TABLE autores( -> autor_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, -> nome VARCHAR(100), -> dt_nasc DATE); mysql> CREATE TABLE editora( -> editora_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, -> editora_nome VARCHAR(100) -> ); mysql> CREATE TABLE publicacao( -> isbn CHAR(13), -> autor_id INT UNSIGNED, -> editora_id INT UNSIGNED -> ); mysql> CREATE TABLE livros( -> isbn CHAR(13) NOT NULL PRIMARY KEY, -> titulo VARCHAR(50), -> edicao_num TINYINT(2), -> ano_publicacao YEAR, -> descricao TEXT); 25 O comando SHOW Assim que criada sua primeira tabela. Para ver o resultado basta digitar a seqüência: SHOW TABLES FROM livraria; Para ver as colunas que existem na sua tabela digite: SHOW COLUMNS FROM livros; Ou DESCRIBE. O comando DESCRIBE Se preferir, o comando DESCRIBE faz a mesma coisa que SHOW, mostrando as colunas existentes em sua tabela. DESCRIBE livros; Ou simplesmente: DESC livros; IF NOT EXISTS: Uma maneira de se ter certeza de se não está criando uma tabela novamente é fazer o comando IF NOT EXISTS: mysql> CREATE TABLE IF NOT EXISTS livros( -> isbn CHAR(13) NOT NULL PRIMARY KEY, -> titulo VARCHAR(50), -> edicao_num TINYINT(2), -> ano_publicacao YEAR, -> descricao TEXT); Criando uma cópia de uma tabela A partir da versão 4.1 você pode copiar uma tabela com sua estrutura da seguinte maneira: CREATE TABLE copia_livros LIKE livros; Alterando tabelas existentes 26 Agora que você criou a sua tabela o que aconteceria se você precisasse alterar algo que fez? Confira os seguintes exemplos para alterar o nome da tabela, tipo de dados e o nome da coluna: Alterando o nome da coluna ALTER TABLE copia_livros CHANGE titulo titulo_do_livro VARCHAR(50); DESC copia_livros; # descreva as colunas de clientes Nota: # (sustenido) é o inicio de um comentário e não interfere em uma execução de comando. Alterando o tipo de coluna mysql> ALTER TABLE copia_livros -> MODIFY titulo_do_livro VARCHAR(30) NOT NULL; Renomeando uma tabela ALTER TABLE copia_livros RENAME livros2; Excluindo / adicionando colunas e tabelas Como você pode ver quando uma coluna é criada ou uma tabela estas não são escritas na pedra e podem ser alteradas facilmente. Isso também implica em adicionar colunas em uma tabela existente ou excluí-la. Eliminando tabelas e colunas O comando DROP também é utilizado para eliminar as colunas de uma tabela. Para excluir uma tabela existente execute a seguinte seqüência: DROP TABLE livros2; Para excluir somente uma coluna execute a seguinte seqüência: ALTER TABLE livros2 DROP editora_id; Isso excluirá a coluna e todas as informações que você armazenou. 27 Adicionando colunas O comando ADD é o responsável pela inserção de uma nova coluna. ALTER TABLE livros2 ADD editora_id INT; #onde ADD é adicionar Adicionando colunas após uma outra determinada O comando AFTER adiciona a nova coluna na tabela após o nome mencionado. ALTER TABLE livros2 ADD editora_id INT AFTER edicao_num; Utilizando índices Um índice é um arquivo estruturado que facilita o acesso a dados. Isso significa que um índice na coluna correta aumentará a velocidade de uma consulta consideravelmente. Um índice trabalha da mesma forma que pastas com separador alfabético em um gabinete de arquivo ele permite pular para a parte do alfabeto que você está procurando. Decidindo quais colunas incluir no índice Você deve colocar um índice na(s) coluna(s) que utilizará com mais freqüência como filtro em suas consultas. Os índices também funcionam melhor em colunas que contêm dados únicos. Essa é uma das razões pela as quais chaves são normalmente suas melhores escolhas para índices. Essa também pode ser uma das razões que as pessoas confundem chaves e índices. Uma chave ajuda a definir a estrutura de um banco de dados, ao passo que índice apenas aprimora o desempenho. Um índice pode ser composto de uma ou mais colunas. Você também pode ter mais de um índice em uma tabela. Criando um índice Por padrão, o MySQL cria um índice se você declara uma coluna como uma chave primária. Não há necessidade de criar um índice nessa coluna; caso contrário você teria dois índices em uma mesma coluna. A sintaxe para criar um índice em uma coluna: ALTER TABLE livros ADD INDEX idx_titulo(titulo); 28 Excluindo índices Excluir um índice é tão simples quanto criar. A sintaxe é a mesma que excluir uma coluna ou uma tabela: DROP INDEX nomedoindice ON nomedatabela; Ou... ALTER TABLE nomedatabela DROP INDEX nomedoindice; Para alterar uma tabela eliminando uma chave primária, utilize a seguinte sintaxe: ALTER TABLE nomedatabela DROP PRIMARY KEY; Nota: Se você estiver usando uma coluna com AUTO_INCREMENT, você não excluirá a chave primária enquanto não retirar esse modificador. Tipos de tabelas O MySQL possui uma característica um pouco diferente dos outros sistemas gerenciadores de banco de dados, uma vez que no MySQL é possível escolher o tipo da tabela no momento da criação. O formato de armazenamento dos dados, bem como alguns recursos do banco de dados são dependentes do tipo de tabela escolhido. A definição do tipo de tabela pode ser feita na criação da tabela, como você pode ver a seguir: CREATE TABLE teste ( id INT NOT NULL, nome VARCHAR(30) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; No comando criado, o tipo da tabela, indicado em TYPE=MyISAM, significa que você está criando uma tabela com o tipo MyISAM, que é o padrão das tabelas, caso não seja informado o TYPE. A partir da versão 4.0.18 você pode utilizar ENGINE como sinônimo de TYPE. A seguir você temalguns dos tipos de tabelas no qual você pode criar: O tipo MyISAM Este é o tipo de tabela padrão do MySQL. Caso não seja informado o tipo de tabela, o MySQL criará a tabela do tipo MyISAM. O tipo de tabela padrão pode ser alterado incluindo-se no arquivo de configuração, chamado de my.cnf (no Linux) ou my.ini (no Windows), a opção a seguir: default-storage-engine=INNODB 29 As tabelas MyISAM são armazenadas em 3 arquivos, com o mesmo nome da tabela, mas com extensões diferentes: .FRM que armazena a definição da tabela. .MYD que contém os dados. .MYI contendo os índices. Estas tabelas são de grande desempenho para leitura, uma vez que os seus índices são armazenados em árvores binárias balanceadas, o que provê um ganho para o acesso às informações. O MyISAM não trabalha com transações (commit ou rollback) e também não possui integridade referencial, isto é, ao incluir uma chave estrangeira com alguns constraints, esta servirá apenas como documentação, mas as restrições não serão respeitadas pelo banco. O tipo Memory Tabelas do tipo MEMORY (conhecida anteriormente como HEAP) são armazenadas em memória e, graças a isso, são extremamente rápidas. Em contrapartida, o seu conteúdo é volátil, uma vez que não são gravadas em disco. Caso haja uma queda do SGBD os dados destas tabelas serão perdidos. Além disto, é necessário um processo para dar a carga inicial nos dados quando o servidor de banco iniciar e sua execução. A principal aplicação das tabelas MEMORY seria para tabelas que são consultadas com muita freqüência, mas que não sofrem muitas alterações (lookup tables). 30 O tipo MERGE As tabelas do tipo MERGE são coleções de tabelas MyISAM idênticas. Este recurso permite a divisão de uma tabela grande em várias partes menores, e ainda assim permite acesso ao conteúdo de todas elas como se fossem uma única tabela. Veja um exemplo de como utilizá-la: CREATE TABLE exemplo1 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, exemplo VARCHAR(20) ); CREATE TABLE exemplo2 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, exemplo VARCHAR(20) ); INSERT INTO exemplo1 VALUES (null, 'Exemplo1'),(null,‘Teste1'); INSERT INTO exemplo2 VALUES (null, 'Exemplo2'),(null, 'Teste2'); CREATE TABLE mesclar (ids INT NOT NULL AUTO_INCREMENT, exemplos VARCHAR(20), INDEX(ids) )TYPE=MERGE UNION=(exemplo1,exemplo2); SELECT * FROM mesclar; Resultado da Mesclagem ids Exemplos 1 Exemplo1 2 Teste1 1 Exemplo2 2 Teste2 31 O tipo BDB O tipo de tabela BDB vem de BerkeleyDB, e é desenvolvido pela Sleepycat (http://www.sleepycat.com). O tipo BDB provê ao MySQL um manipulador de tabelas com controle de transação, dando assim a você a possibilidade de usar os comandos COMMIT e ROLLBACK, além de fornecer a recuperação automática de dados em caso de queda do sistema. O BDB apresenta um mecanismo de lock em nível de página, onde apenas os dados de uma mesma página ficarão bloqueados durante um período de lock. O tipo InnoDB O tipo InnoDB é do tipo de tabela transacional, desenvolvido pela InnoDBase Oy. A partir da versão 4.0 do MySQL ele passa a ser parte integrante das distribuições do MySQL. O InnoDB apresenta, além da capacidade transacional, outros recursos que são realmente úteis na utilização de tabelas: Integridade referencial, com implementação dos constraints SET NULL, SET DEFAULT, RESTRICT e CASCADE; Ferramenta de backup on-line (ferramenta comercial, não GPL); Lock de registro, como Oracle, DB2, etc; Níveis de isolamento; Armazenamentos de dados em tablespace. Por se tratar de um tipo de tabela com recursos mais avançados, requer mais espaço em memória e disco, além de se apresentar, em determinadas situações, um pouco mais lento que tabelas do tipo MyISAM. Apesar disto, o tipo InnoDB tem se mostrado extremamente rápido se comparado com outros SGBDs transacionais. Alterando o tipo de uma tabela Com o comando ALTER TABLE não é possível alterar o tipo da tabela, por isso, você pode alterar da seguinte maneira: ALTER TABLE livros ENGINE=INNODB; 32 Tipo de dados Como a maioria dos sistemas de gerenciamento de banco de dados relacional (Relational Database Management Systems – RDBMS), o MySQL tem tipos de dados específicos de coluna. O MySQL tem vários tipos de dados que suportam funções diferentes. Um tipo de dados é a definição das informações que uma coluna armazenará. Pode haver muitos tipos de dados em uma tabela, mas cada coluna armazenará seu próprio tipo de informações específicas. Há quatro tipos de grupos de formatos de dados. O primeiro é o numérico. O segundo tipo é o formato de caractere ou string. Esse formato consiste em letras e números ou qualquer coisa que você coloque entre aspas. O terceiro grupo é formado por datas e horas. O tipo final é uma forma de miscelânea. Ele consiste em tudo que não se encaixa em qualquer uma das outras categorias. Tipos numéricos Os tipos numéricos destinam-se somente a números. Os diferentes tipos de números ocupam uma quantidade diferente de espaço na memória. Um bom exemplo é você tentando comprar um chocolate em uma loja e ao passar no caixa a você descobre que deve pagar pela caixa inteira. Você diz que não precisa de tudo, mas é atacado e só e vendido de caixa. Se você vai utilizar 3 números, por que ocupar um espaço na memória como se estivesse utilizando 100? Lembre-se: você só deve pagar pelo que vai usar. Armazenamento numérico Nome do tipo Espaço na memória TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT 4 bytes BIGINT 8 bytes FLOAT(Inteiro,Decimal) 4 bytes DOUBLE(Inteiro,Decimal) 8 bytes DECIMAL(Inteiro,Decimal) O valor de bytes Inteiro + 2 Se a coluna é numérica e declarada UNSIGNED, o intervalo dobra para o tipo dado. Por exemplo, se você declara que uma coluna que é UNSIGNED TINYINT, o intervalo dessa coluna é de 0 a 255. Declarando dessa forma você faz com que essa coluna tenha somente valores positivos. 33 Tipos numéricos Nome do tipo Intervalo de valor Sem sinal TINYINT -128 a 127 0 – 255 SMALLINT -32768 a 32767 0 – 65535 MEDIUMINT -8388608 a 83888607 0 - 16777215 INT -2147483648 a 2147483647 0 - 4294967295 BIGINT -9223372036854775808 a 9223372036854775807 0 - 18446744073709550615 FLOAT (Inteiro,Decimal) Varia dependendo dos valores DOUBLE (Inteiro,Decimal) Varia dependendo dos valores DECIMAL (Inteiro,Decimal) Varia dependendo dos valores FLOATs, DOUBLEs e DECIMALs são tipos numéricos que podem armazenar frações. Os outros não. Utilize DECIMAL para números realmente grandes. DECIMALs são armazenados de maneira diferente e não têm limites. Modificadores AUTO_INCREMENT, UNSIGNED e ZEROFILL Esses modificadores só podem ser utilizados com tipos de dados numéricos. Eles utilizam operações que somente podem ser feitas com números. AUTO_INCREMENT O modificador de coluna AUTO_INCREMENT automaticamente aumenta o valor de uma coluna adicionando 1 ao valor máximo atual. Ele fornece um contador que é ótimo para criar valores únicos. Você também pode incluir um número. Se quiser que uma coluna AUTO_INCREMENT inicie com 9.000, por exemplo, é só declarar explicitamente um ponto inicial utilizando a seguinte sintaxe: mysql> CREATE TABLE teste ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT -> ) AUTO_INCREMENT=9000; 34 UNSIGNED UNSIGNED depois de um tipo inteiro significa que ele só pode ter um zero ou valor positivo. ZEROFILL O modificador de coluna ZEROFILL é utilizado para exibir zeros à esquerda de um número com base na largura de exibição.Como todos os tipos de dados numéricos têm uma largura de exibição opcional, se você declara um INT(8) ZEROFILL e o valor armazenado é 23, ele será exibido como 00000023. Para isso utilize a seguinte sintaxe: mysql>CREATE TABLE teste (id INT(4) ZEROFILL); Tipos de caractere ou de dados de string O outro grupo importante de tipo de dados são os tipos de strings ou de caractere. Uma string é um conjunto de caracteres. Um tipo de string pode armazenar dados como São Paulo ou Avenida São João, n.º 255. Qualquer valor pode ser armazenado em um tipo de dados de string. Tipos string Nome de tipo Tamanho máximo Espaço de armazenamento CHAR(X) 255 bytes X bytes VARCHAR(X) 255 bytes X + 1 byte TINYTEXT 255 bytes X + 1 byte TINYBLOB 255 bytes X + 2 bytes TEXT 65.535 bytes X + 2 bytes BLOB 65.535 bytes X + 2 bytes MEDIUMTEXT 1,6 MB X + 3 bytes MEDIUMBLOB 1,6 MB X + 3 bytes LONGTEXT 4,2 GB X + 4 bytes LONGBLOB 4,2 GB X + 4 bytes 35 CHAR e VARCHAR Fora todos esses tipos, os tipos VARCHAR e CHAR são os mais utilizados. A diferença entre eles é que o VARCHAR tem um comprimento variável e o CHAR não. Os tipos CHAR são utilizados para comprimentos fixos. Você utilizará esse tipo quando os valores não variam muito. Se você declara um CHAR(10), todos os valores armazenados nessa coluna terão 10 bytes de comprimento, mesmo se ele tiver 3 bytes de comprimento. O MySQL preenche esse valor para ajustar o tamanho que foi declarado. O tipo VARCHAR faz o contrário. Se você declara um VARCHAR(10) e armazena um valor que tem somente 3 caracteres de comprimento, a quantidade total de espaço de armazenamento é de 4 bytes (o comprimento mais um). A vantagem de utilizar os tipos CHAR é que as tabelas que contêm esses valores fixos são processadas mais rapidamente que aquelas que são compostas pelo tipo VARCHAR. A desvantagem de utilizar o tipo CHAR é o espaço desperdiçado. De um modo geral não se pode utilizar os dois na mesma tabela, pois quando feito o MySQL converte automaticamente uma coluna com o tipo CHAR em VARCHAR. A única exceção é quando você declara uma coluna como VARCHAR(3), o MySQL converte automaticamente em CHAR(3). Isso acontece porque valores de 4 caracteres ou menores são muito pequenos para o tipo VARCHAR. TEXT e BLOB TEXT e BLOB(Binary Large Object) são tipos variáveis de comprimento que podem armazenar grandes quantidades de dados. Você utilizará esses tipos quando quiser armazenar imagens, sons ou grandes quantidades de textos, como páginas da Web ou documentos. Um bom exemplo é se você estiver querendo armazenar valores de uma <TEXTAREA> de uma sessão de comentários em uma página da Web, o tipo TEXT seria uma boa escolha. 36 Tipos variados Há basicamente três tipos variados; os tipos ENUM, SET e DATE/TIME. Tipo ENUM O tipo ENUM é uma lista ENUMerada. Significa que essa coluna pode armazenar apenas um dos valores que estão declarados na lista dada. A sintaxe para criar uma coluna ENUMerada é como segue: mysql> CREATE TABLE coluna_enum ( -> estados ENUM(‘SP’,’RJ’,’MG’,’RS’) -> ); Você pode ter até 65.535 itens em sua lista enumerada. É uma boa escolha para caixas de combinação. Tipo SET O tipo SET é muito parecido com o tipo ENUM. O tipo SET, como o tipo ENUM, armazena uma lista de valores. A diferença é que no tipo SET, você pode escolher mais de uma opção para armazenar. Um tipo SET pode conter até 64 itens. O tipo SET é uma boa escolha para opções em uma página da Web em que o usuário pode escolher mais de um valor. A sintaxe para criar um tipo SET: mysql>CREATE TABLE teste ( -> passatempo SET(‘Televisão’,’Futebol’,’Vídeo Game’,’Cinema’) -> ); A coluna criada seria capaz de armazenar uma ou mais escolhas, como por exemplo: “Futebol” #como uma escolha “Futebol, Vídeo Game” # como mais de uma escolha. Quando se inserem os valores em uma coluna do tipo SET, se coloca entre aspas ou apóstrofos e separados por vírgula: mysql>INSERT INTO teste (passatempo) VALUES (‘Futebol,Vídeo Game’); 37 Modificadores adicionais de coluna O MySQL tem várias palavras-chave que modificam a maneira como uma coluna funciona. Como vimos acima, temos AUTO_INCREMENT e ZEROFILL e como eles afetam a coluna em que são utilizados. Alguns modificadores se aplicam apenas em colunas de um certo tipo de dado. Tabela de Modificadores Nome de modificador Tipos aplicáveis AUTO_INCREMENT Todos os tipos INT BINARY CHAR, VARCHAR DEFAULT Todos, exceto BLOB, TEXT NOT NULL Todos os tipos NULL Todos os tipos PRIMARY KEY Todos os tipos UNIQUE Todos os tipos UNSIGNED Tipos numéricos ZEROFILL Tipos numéricos O modificador BINARY faz com que os valores armazenados sejam tratados como strings binárias, fazendo-os distinguir letras maiúsculas e minúsculas. Ao classificar ou comparar essas strings, a distinção entre maiúsculas e minúsculas será considerada. Por padrão os tipos CHAR e VARCHAR não são armazenados como binários. O modificador DEFAULT permite especificar o valor de uma coluna se não existir um valor. Os modificadores NULL e NOT NULL especifica se na coluna deve haver um valor ou não. Por exemplo; se você especificar a coluna como NOT NULL você é forçado a colocar um valor, pois esse campo é requerido. PRIMARY KEY é um índice que não deve conter valores nulos (NULL). Cada tabela deve conter uma chave primária, isso facilita uma consulta de dados. O modificador UNIQUE impõe a regra que todos os dados dentro da coluna declarada devem ser únicos. Por exemplo; se você declarar (não faça isso) que a coluna nome deve ser UNIQUE(única), não pode haver valores duplicados, caso contrário gerará um erro. A sintaxe para sua criação é: mysql>CREATE TABLE IF NOT EXISTS teste (nome VARCHAR(30)UNIQUE NOT NULL); 38 Tipos de data e hora O MySQL suporta vários tipos de data e hora. Esses são mostrados na tabela a seguir: Tabela de data e hora Tipo Intervalo Descrição DATE 1000-01-01 a 9999-12-31 Datas. Será exibida como YYYYMM-DD TIME -838:59:59 a 838:59:59 Hora. Será exibida como HH:MM:SS DATETIME 1000-01-01 00:00:00 a 9999- 12-31 23:59:59 Data e Hora. Será exibida como YYYY-MM-DD HH:MM:SS TIMESTAMP[(F)] 1970-01-01 00:00:00 Um registro de data/hora, útil para relatório de transação. O formato de exibição depende do formato de F. YEAR[(2 | 4)] 70-69 (1970-2069) 1901-2155 Um ano. Você pode especificar 2 ou 4 formatos de dígitos. Cada um desses tem um intervalo diferente, como mostrado. Sintaxe básica da SQL A primeira coisa que devemos fazer quando criamos um banco de dados e depois uma tabela e utilizá-la inserindo dados. Comando INSERT O comando INSERT INTO adiciona dados em uma tabela. A sua sintaxe é: mysql> INSERT INTO livros VALUES ( -> '85-7585-120-5', ->'Core Java Fundamental', ->6, ->‘2004’, ->'Desenvolva Java com vários exemplos'); O nome da tabela em que você irá inserir deverá ser declarada logo no início INSIRA DENTRO nomedatabela (colunas) VALORES (‘valores inseridos dentro de cada coluna’); É importante salientar que strings ficam entre aspas ou apóstrofos e valores numéricos (declarados como tipo de dados numéricos) não precisam de “aspas” ou ‘apóstrofos’ 39 Para inserir mais de um valor separe-os por vírgula: mysql> INSERT INTO nomedatabela(colunas) VALUES (‘valores inseridos 1’), ->(‘valores inseridos 2’), ->(‘e assim por diante’); Comando SELECT A instrução SELECT é provavelmente a mais utilizada de todas as instruções de SQL. A instrução SELECT somente retornará os dados que são armazenados no banco de dados dentro de uma tabela. O MySQL realizaessa instrução mais rápido que qualquer outro banco de dados do mercado. A sintaxe é: SELECT nomedacoluna FROM nomedatabela WHERE condições; No caso do nosso banco de dados livraria: mysql>SELECT * FROM livros; # o asterisco indica todas as colunas 40 Um outro caso, a cláusula WHERE mysql>SELECT * FROM livros WHERE ISBN='85-7585-120-5'; Nesse caso foi colocada uma condição que dentre todos os registros só deverá aparecer os dados ONDE a coluna ISBN for igual à '85-7585-120-5'. A cláusula WHERE especifica o critério utilizado para selecionar linhas particulares. O único sinal igual é utilizado para testar igualdade – observe que isso é diferente do Java e é fácil se confundir. Além da igualdade, o MySQL suporta um conjunto completo de operadores e expressões regulares. Na tabela a seguir estão listadas as mais utilizadas por você: Tabela de Operadores no MySQL Operador Nome Exemplos Descrição = igual à autor_id = 1 Testa se os dois valores são iguais > maior que Quantidade > 50 Testa se um valor é maior que o outro < menor que Quantidade < 50 Testa se um valor é menor que o outro >= maior ou igual a Quantidade >= 50 Testa se um valor é maior ou igual ao outro <= menor ou igual a Quantidade <= 50 Testa se um valor é menor ou igual ao outro != ou <> diferente de Quantidade !=0 Testa se um valor é diferente do outro IN cidade in (‘São Paulo’, ‘Minas Gerais’) Testa se o valor está em um conjunto particular NOT IN cidade not in (‘São Paulo’, ‘Minas Gerais’) Testa se o valor não está em um conjunto particular IS NOT Endereço não é nulo IS NULL Endereço é nulo promocao is null Testa se o campo não contém um valor BETWEEN Quantidade entre um valor e outro valor BETWEEN 200 AND 350 Testa se o campo tem valores entre um e outro 41 Algumas funções que trabalham com a instrução SELECT MAX( ) SELECT MAX(coluna) FROM tabela; Essa função seleciona o valor máximo de uma coluna. MIN( ) SELECT MIN(coluna) FROM tabela; O contrário de MAX, retorna o valor mínimo de uma coluna. LIMIT SELECT * FROM tabela LIMIT 2; Limita a visualização de 2 linhas de dados. SELECT * FROM tabela LIMIT 2,5; Limita a visualização da linha 2 a linha 5 de dados. COUNT( ) SELECT COUNT(coluna) FROM tabela; Conta quantas linhas de dados existem na coluna nome. SELECT COUNT(*) FROM tabela; Conta quantas linhas de dados existem em todas as linhas. Nota: Em caso de fazer a contagem em campo de valor NULL a contagem será diferente da no valor total. SUM( ) SELECT SUM(coluna) FROM tabela; Soma todos os dados da coluna. ORDER BY SELECT * FROM tabela ORDER BY coluna; Coloca os dados selecionados em ordem crescente pela coluna. ORDER BY ... DESC SELECT * FROM tabela ORDER BY coluna DESC; 42 Coloca os dados selecionados em ordem decrescente pela coluna. AVG( ) SELECT AVG(coluna) FROM tabela; Faz a média aritmética da coluna designada. LIKE mysql>SELECT * FROM livros WHERE titulo LIKE ‘Java%’; Neste caso pode-se fazer uma busca por apenas a inicial do valor desejado. O sinal de %(porcentagem) é o caractere curinga que significa qualquer caractere. mysql>SELECT * FROM livros WHERE titulo LIKE ‘%Java%’; Colocando a % no início e no fim, com um valor no meio, é possível buscar todos os valores que contenham as letras Java, seja no começo, meio ou fim. Um caso a parte: a união do INSERT INTO ... SELECT INSERT INTO tabela1(coluna) SELECT tabela.coluna2 FROM tabela2; Insere na tabela tabela1.coluna valores da coluna2 da tabela tabela2. Comando UPDATE O comando UPDATE permite editar os valores de dados existentes. A sintaxe para modificar os valores é: UPDATE tabela SET coluna= ‘valor’ WHERE coluna=’valor’; Atualiza os dados da coluna determinada em SET na condição passada em WHERE. Comando DELETE A instrução DELETE é muito semelhante á instrução SELECT. A única diferença em vez de selecionar registros para visualizar, essa instrução exclui esses registros. A instrução DELETE tem a seguinte sintaxe: DELETE FROM tabela WHERE coluna=’valor’; 43 Trabalhando com Junções As junções são uma parte integrante de um banco de dados relacional. As junções permitem ao usuário de banco de dados tirar proveito dos relacionamentos que foram desenvolvidos na fase do projeto do banco de dados. Uma JUNÇÃO é o termo utilizado para descrever o ato em que uma ou mais tabelas são “unidas” entre si para recuperar dados necessários com base nos relacionamentos que são compartilhados entre elas. Criando uma junção com INNER JOIN A seguinte sintaxe cria uma junção: SELECT tabela1.coluna, tabela2.coluna FROM tabela1 INNER JOIN tabela2 on tabela1. coluna_de_valor_identico = tabela2.coluna_de_valor_identico; INNER JOIN’s são provavelmente as mais comuns de todas as junções. Uma INNER JOIN significa que todos que todos os registros que estão sem correspondência são descartados. Somente as linhas correspondidas serão exibidas no conjunto de resultados. Os dados aparecem na ordem em que você especifica. Chaves variadas do MySQL O que é uma chave? Uma chave em uma tabela em um banco de dados fornece um meio de localizar rapidamente informações específicas. Embora uma chave não precise significar qualquer coisa para o usuário humano do banco de dados, as chaves são uma parte vital da arquitetura de banco de dados e pode influenciar significativamente o desempenho. Princípios da Chave Imagine que você tem uma coleção muito simples de dados em que armazena apenas os dados “úteis” simples. Por exemplo, você talvez crie uma tabela de Clientes semelhante a um antigo arquivo de índice de fixas, com nome e detalhes de um cliente em cada ficha. Quando quiser pesquisar um cliente, você procura o arquivo e lê cada ficha sucessivamente. Ao ver a(s) ficha(s) que quer, você lê essas informações úteis – como o nome, endereço e número de telefone do cliente. Convencionalmente, você talvez classifique o arquivo de índice de ficha por ordem de sobrenome. Isso ajuda se você sabe o nome da pessoa cujos dados está examinando. Mas e se você quiser localizar as pessoas por algum outro critério. 44 Naturalmente, você pode configurar o banco de dados MySQL da mesma maneira. Mas logo ficaria com dificuldades.Com a quantidade de dados o seu banco de dados teria de ler as informações sucessivamente, o que torna uma operação ineficiente. Você chamaria essa operação de varredura de tabela. Essa é a mais demorada das operações em um banco de dados. É nesse momento em que uma chave se torna útil. Como as chaves funcionam Uma chave existe como uma tabela extra no banco de dados, embora pertença à sua tabela pai. Ela ocupa espaço físico no disco rígido (ou outras áreas de armazenamento) do banco de dados. Pode ser tão grande quanto a tabela principal e, teoricamente, até maior. Você define a chave para se relacionar com uma ou várias colunas em uma tabela específica. Como os dados em uma chave são totalmente derivados da tabela, você pode eliminar e recriar uma chave sem qualquer perda de dados. Benefícios de usar uma chave A utilização adequada de chaves pode aprimorar significativamente o desempenho do banco de dados. Para utilizar a analogia de um índice de livro, considere o pouco número de páginas que é necessário no índice de um livro para dar visão rápida dos temas importantes. Compare quanto tempo você levaria se estivesse pesquisando pelo volume, página por página. Suporte de chave do MySQL O MySQL suporta os seguintes comandos para criar chaves nas tabelas existentes:
Compartilhar