Dicas do OsmarJr

Microsoft Jet SQL Fundamental para Access 2000

Este artigo discute a mecânica básica da utilização do Jet SQL para trabalhar com dados em bancos de dados Access 2000. Discute, também, a utilização do SQL para criar e alterar a estrutura de um banco de dados. Se você é novato na manipulação de dados usando o SQL em um banco de dados Access, este artigo é um grande ponto de partida.

 

Microsoft Jet SQL Fundamental para Access 2000
Este artigo discute a mecânica básica da utilização do Jet SQL para trabalhar com dados em bancos de dados Access 2000. Discute, também, a utilização do SQL para criar e alterar a estrutura de um banco de dados.

Por: Acey James Bunch - Tradução OsmarJr
Dificuldade: Básico
Download: acFundSQL.zip


Acey James Bunch
Microsoft Corporation
Fevereiro 2000

Aplica-se a: Microsoft© Access 2000 e superiores



Conteúdo
Microsoft Jet SQL Fundamental para Access 2000
Conteúdo
Introdução
Definindo SQL
O que é a Structured Query Language (Linguagem Estruturada de Consultas)?
Por que e onde usar o SQL?
Linguagem de Definição de Dados
Linguagem de Manipulação de Dados
ANSI e Access 2000
Convenções de Codificação SQL
Usando a Linguagem de Definição de Dados
Criando e Excluindo Tabelas
Trabalhando com Índices
Definindo Relacionamentos Entre Tabelas
Usando a Linguagem de Manipulação de Dados
Recuperando registros
Restringindo o Conjunto Resultante
Classificando o Conjunto Resultante
Usando Funções de Agregação para Trabalhar com Valores
Agrupando Registros em um Conjunto Resultante
Inserindo Registros em uma Tabela
Atualizando registros em uma Tabela
Excluíndo Registros de uma Tabela
Usando SQL no Access
Construíndo Consultas
Especificando a Origem dos Dados
Usando Comandos SQL em Código
Um Último Comentário
Recursos Adicionais


Introdução

Este é o primeiro em uma série de artigos que explicam o que é o SQL e como pode ser usado em seus aplicativos Microsoft® Access 2000. São três artigos no total: um fundamental, um intermediário é um avançado. Os artigos foram idealizados para mostrar progressivamente a sintaxe e os métodos para utilização do SQL e apresentar as novidades do SQL no Access 2000.

Definindo SQL

Para realmente obter os benefícios e aproveitar o poder do SQL se faz necessária uma compreensão básica do que é e como pode ser usado.

O que é a Structured Query Language (Linguagem Estruturada de Consultas)?

SQL é uma abreviatura para Structured Query Language (Linguagem Estruturada de Consultas). Simplificando, é a linguagem usada para extrair, manipular e estruturar dados que residem em um sistema de gerenciamento de base de dados relacional (RDBMS). Em outras palavras, para obter uma resposta do seu banco de dados a pergunta deve ser feita em SQL.

Por que e onde usar o SQL?

Talvez você não saiba, mas, se está usando o Access, também está usando o SQL. “Não! Nunca usei nada chamado SQL”, você pode dizer. Isto porque o Access faz um trabalho tão bom em usá-lo por você. O que deve ser lembrado é que para toda solicitação orientada a dados o Access a converte para SQL por baixo dos panos.

O SQL é usado em uma variedade de lugares no Access. É usado, é claro, em consultas, mas também é usado para construir relatórios, popular caixas de listagem e de combinação, e tratar os dados de formulários de entrada de dados. Como o SQL é tão usado por todo o Access, entende-lo vai melhorar bastante sua habilidade de tomar controle de todo o poder de programação que o Access permite.

Nota: O dialeto particular de SQL discutido neste artigo se aplica à versão 4.0 do motor de banco de dados Microsoft Jet. Ainda que muitas das declarações SQL funcionem em outras bases de dados, como o Microsoft SQL Server™, existem algumas diferenças na sintaxe. Para identificar a sintaxe correta, consulte a documentação do sistema de banco de dados que está usando.

Linguagem de Definição de Dados

Linguagem de Definição de Dados - Data definition language (DDL) é a linguagem ou terminologia SQL usada para gerenciar os objetos de bancos de dados que contém dados. Objetos de bancos de dados são as tabelas, índices ou relacionamentos – tudo que tenha a ver com a estrutura do banco de dados – mas não os dados em si. Dentro do SQL, algumas palavras-chave e cláusulas são usadas como comandos DDL para um banco de dados relacional.

Linguagem de Manipulação de Dados

Data manipulation language (DML) é a linguagem (ou terminologia) SQL utilizada para gerenciar os dados dentro do banco de dados. A DML não tem efeitos sobre a estrutura do banco de dados, sendo usada apenas com relação aos dados nele contidos. A DML é usada para extrair, acrescentar, modificar ou excluir informações contidas nas tabelas do banco de dados relacional.

ANSI e Access 2000

ANSI é a sigla do Instituto Nacional Americano de Padrões (American National Standards Institute), que é uma organização nacionalmente reconhecida para criação de padrões que definiu um padrão básico para o SQL. O padrão definido mais recentemente é o SQL-92, e o Access 2000 acrescenta diversas características para ficar em conformidade com os padrões, ainda que algumas destas características estejam disponíveis apenas com o uso do provedor Jet OLE DB. Entretanto, o Access também manteve a compatibilidade com as versões anteriores, permitindo maior flexibilidade. O Access também tem algumas características ainda não definidas pelos padrões que ampliam o poder do SQL.

Para entender melhor sobre OLE DB e como ele se insere na estratégia de Acesso Universal a Dados (Universal Data Access) da Microsoft, visite a página http://www.microsoft.com/data/.

Convenções de Codificação SQL

Em todo este artigo você vai notar um método consistente de convenções de codificação SQL. Como ocorre com todas as convenções de codificação, a idéia é demonstrar o código de maneira que seja de fácil leitura e compreensão. Isto é obtido pela utilização de uma mistura de espaços em branco, novas linhas e palavras-chave em caixa alta. De modo geral, use caixa alta para todas as palavras-chave do SQL e, se necessária, a quebra de linha de código SQL, tente fazê-lo em uma seção lógica da declaração SQL. Isso será mais bem entendido após ver alguns exemplos.

Código SQL mal formatado

create table tblCustomers (CustomerID integer not null,[Last Name] text(50) not null,[First Name] text(50) not null, Phone text(10),Email text(50))

Código SQL bem formatado

CREATE TABLE tblCustomers
    (CustomerID INTEGER NOT NULL,
    [Last Name] TEXT(50) NOT NULL,
    [First Name] TEXT(50) NOT NULL,
    Phone TEXT(10),
    Email TEXT(50))

Usando a Linguagem de Definição de Dados

Quando manipulamos a estrutura de uma base de dados, existem três objetos primários que serão trabalhados: tabelas, índices e relacionamentos.

  1. Tabelas são as estruturas da base de dados que contém os dados físicos, e eles estão organizados por suas colunas (ou campos) e linhas (ou registros).
  2. Índices são objetos da base de dados que definem como os dados das tabelas são arranjados e classificados na memória.
  3. Relacionamentos definem como uma ou mais tabelas se relacionam com uma ou mais outras tabelas.

Estes três objetos de base de dados formam a fundação de todas as tabelas relacionais.

Criando e Excluindo Tabelas

As tabelas são os blocos primários da construção de uma base de dados relacional. Uma tabela contém linhas (ou registros) de dados, e cada linha é organizada em um número finito de colunas (ou campos). Para criar uma nova tabela em Access usando o Jet SQL, precisamos dar um nome à tabela, dar nomes aos campos e definir o tipo de dados que cada campo irá conter. Usamos a declaração CREATE TABLE para definir uma tabela no SQL. Suponhamos que estejamos criando uma base de dados de pedidos e vamos começar criando uma tabela inicial de clientes.

CREATE TABLE tblCustomers
    (CustomerID INTEGER,
    [Last Name] TEXT(50),
    [First Name] TEXT(50),
    Phone TEXT(10),
    Email TEXT(50))

Notas

  1. Se o nome de um campo contiver espaços ou qualquer outro caracter não alfanumérico, este nome de campo deve ser fornecido entre colchetes ([ ]).
  2. Se não for declarado o tamanho de um campo texto, ele assumirá o padrão de 255 caracteres. Para manter a consistência e legibilidade do código os tamanhos dos campos devem ser sempre definidos.
  3. Para maiores informações sobre os tipos de dados que podem ser usados nas definições de campos, digite tipos de dados SQL no assistente do Access ou no tab Assistente de resposta da janela do Help do Microsoft Access, e clique em Procurar.

Um campo pode ser declarado como NOT NULL, significando que valores nulos não podem ser inseridos naquele campo, sendo exigido o fornecimento de algum valor. Um valor nulo não deve ser confundido com uma string vazia ou um valor 0. Ele é, simplesmente, a representação pela base de dados, de um valor desconhecido.

CREATE TABLE tblCustomers
    (CustomerID INTEGER NOT NULL,
    [Last Name] TEXT(50) NOT NULL,
    [First Name] TEXT(50) NOT NULL,
    Phone TEXT(10),
    Email TEXT(50))

Para remover uma tabela da base de dados, use o comando DROP TABLE.

DROP TABLE tblCustomers

Trabalhando com Índices

Um índice é uma estrutura de dados externa usada para classificar ou organizar ponteiros para os dados de uma tabela. Quando um índice é aplicado a uma tabela, estamos especificando certo arranjo dos dados de modo que possam ser acessados mais rapidamente. Entretanto, se aplicarmos índices demais em uma tabela, podemos diminuir o desempenho da base de dados pelo tempo gasto para manter os índices. E, além disso, os índices podem causar problemas de bloqueio de registros em ambientes multiusuários. Quando usados no contexto correto, os índices melhoram bastante o desempenho de um aplicativo.

Para criar um índice em uma tabela, devemos fornecer o nome do índice, o nome da tabela para qual o índice será criado, nomear o campo ou campos da tabela a serem usados e nomear as opções a serem utilizadas. Por exemplo, aqui está como criamos um índice na tabela de clientes, na base de dados de pedidos que estamos construindo.

CREATE INDEX idxCustomerID
    ON tblCustomers (CustomerID)

Campos indexados podem ser classificados de duas formas: ascendente (ASC) ou descendente (DES). A ordem padrão é ascendente, e não necessita declaração. Se usar a ordem ascendente, os dados serão classificados de 1 até 100. Se especificarmos a ordem descendente, os dados serão classificados de 100 até 1. A ordem de classificação deve ser declarada para cada campo do índice.

CREATE INDEX idxCustomerID
    ON tblCustomers (CustomerID DESC)

Temos quatro opções que podem ser usadas com um índice: PRIMARY, DISALLOW NULL, IGNORE NULL e UNIQUE. A opção PRIMARY determina que o índice é a chave primária para a tabela. Podemos ter apenas um índice de chave primária por tabela, ainda que uma chave primária possa ser declarada usando mais de um campo. Usamos a palavra-chave WITH para declarar as opções de índices.

CREATE INDEX idxCustomerID
    ON tblCustomers (CustomerID)
    WITH PRIMARY

Para criarmos um índice de chave primária com mais de um campo, incluímos todos os nomes de campos na lista de campos.

CREATE INDEX idxCustomerName
    ON tblCustomers ([Last Name], [First Name])
    WITH PRIMARY

A opção DISALLOW NULL evita a inserção de dados nulos no campo. (isto é similar à declaração NOT NULL usada no comando CREATE TABLE).

CREATE INDEX idxCustomerEmail
    ON tblCustomers (Email)
    WITH DISALLOW NULL

A opção IGNORE NULL faz com que dados nulos sejam ignorados para o índice. Isto significa que qualquer registro que contenha um valor nulo no campo declarado não será usado (ou contado) no índice.

CREATE INDEX idxCustomerLastName
    ON tblCustomers ([Last Name])
    WITH IGNORE NULL

Além das opções PRIMARY, DISALLOW NULL e IGNORE NULL, podemos declarar um índice como UNIQUE, o que significa que apenas valores únicos, sem repetição de valores, podem ser inseridos no campo indexado.

CREATE UNIQUE INDEX idxCustomerPhone
    ON tblCustomers (Phone)

Para remover um índice de uma tabela usamos o comando DROP INDEX.

DROP INDEX idxName
     ON tblCustomers


Definindo Relacionamentos Entre Tabelas

Relacionamentos são as associações estabelecidas entre duas ou mais tabelas. Os relacionamentos são baseados em campos comuns de mais de uma tabela, envolvendo, muitas vezes, chaves primárias e estrangeiras.

Uma chave primária é o campo (ou campos) usado para identificar unicamente cada registro de uma tabela. Temos três exigências para uma chave primária: não pode ser nula, deve ser única e só podemos ter uma para cada tabela. Podemos definir uma chave primária

Uma chave primária é o campo (ou campos) usado para identificar unicamente cada registro em uma tabela. Temos três exigências para a determinação de uma chave primária: não pode ser nula, deve ser única e só podemos ter uma por tabela. Podemos definir um índice de chave primária após a criação da tabela ou utilizando a cláusula CONSTRAINT da declaração da tabela, como mostrado nos exemplos mais adiante nesta seção. Uma restrição limita (ou constrange) os valores que são introduzidos em um campo. Para maiores informações sobre restrições, veja o artigo “Microsoft Jet SQL intermediários para o Access 2000”.

Uma chave estrangeira é um campo (ou campos) em uma tabela que referencia a chave primária de outra tabela. Os dados nos campos das duas tabelas são exatamente os mesmos, e a tabela com o registro da chave primária (a tabela primária) deve conter registros antes que a tabela com o registro de chave estrangeira (a tabela estrangeira) possa incluir os registros relacionados. Como as chaves primárias, podemos definir as chaves estrangeiras na declaração de criação da tabela usando a cláusula CONSTRAINT.

Existem, essencialmente, três tipos de relacionamentos:

  1. Um-para-Um – Para cada registro na tabela primária existe um, e apenas um, registro na tabela estrangeira.
  2. Um-para-Muitos – Para cada registro na tabela primária podemos ter um ou mais registros relacionados na tabela estrangeira.
  3. Muitos-para-Muitos – Para cada registro na tabela primária podemos ter um ou mais registros na tabela estrangeira e para cada registro na tabela estrangeira podemos ter um ou mais registros na tabela primária.

Por exemplo, vamos adicionar uma tabela de pedidos à nossa base de dados de pedidos. Cada cliente na nossa tabela de clientes pode ter diversos pedidos na nossa tabela de pedidos – este é o cenário clássico do um-para-muitos. Vamos pegar a chave primária da tabela clientes e defini-la como chave estrangeira na nossa tabela de pedidos, estabelecendo, assim, o correto relacionamento entre as tabelas.

Para definir os relacionamentos entre tabelas devemos utilizar a declaração CONSTRAINT a nível de campo. Isto significa que as restrições são definidas dentro de uma declaração CREATE TABLE. Para aplicar as restrições, usamos a palavra-chave CONSTRAINT após a declaração do campo, nomeamos a restrição, nomeamos a tabela referenciada por ela e nomeamos o campo ou campos daquela tabela que formará(ão) a chave estrangeira correspondente.

O comando a seguir assume que a tabela tblCustomers já foi criada e que tem a chave primária definida no campo CustomerID. O comando cria a tabela tblInvoices, definindo sua chave primária como o campo InvoiceID. Também cria o relacionamento um-para-muitos entre as tabelas tblCustomers e tblInvoices ao definir outro campo CustomerID na tabela tblInvoices. Este campo é definido como uma chave estrangeira que referencia o campo CustomerID na tabela tblCustomers. Note-se que o nome de cada restrição segue a palavra-chave CONSTRAINT.

CREATE TABLE tblInvoices
    (InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY,
    CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID
    REFERENCES tblCustomers (CustomerID),
    InvoiceDate DATETIME,
    Amount CURRENCY)

Deve-se notar que o índice de chave primária (PK_InvoiceID) para a tabela de pedidos é declarado dentro do comando CREATE TABLE. Para melhorar a performance da chave primária, um índice é criado automaticamente para ela, não havendo necessidade de usar um comando CREATE INDEX separado.

Agora vamos criar uma tabela de envios que vai conter o endereço para entrega de cada cliente. Vamos assumir que teremos apenas um registro de envio para cada registro de cliente, estabelecendo, assim, uma relação um-para-um.

CREATE TABLE tblShipping
    (CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY
    REFERENCES tblCustomers (CustomerID),
    Address TEXT(50),
    City TEXT(50),
    State TEXT(2),
    Zip TEXT(10))

Note-se que o campo CustomerID é tanto a chave primária para a tabela de envios quanto a referência de chave estrangeira à tabela de clientes.

Nota: quando criamos um relacionamento um-para-um usando declarações DDL, a interface de usuário do Access pode mostrar o relacionamento como um-para-muitos. Para corrigir este problema, depois que o relacionamento um-para-um for criado, abrimos a janela Relacionamentos clicando em Relacionamentos no menu Ferramentas. Devemos nos assegurar que as tabelas afetadas foram adicionadas à janela Relacionamentos e clicamos duas vezes na ligação entre as tabelas para abrir a caixa de diálogo Relacionamentos. Clicamos no botão Tipo de Associação para abrir a janela de diálogo Propriedades da Associação. Não é necessário selecionar uma opção, apenas clicamos em Ok para fechar a janela de diálogo e em Ok para fechar a caixa de diálogo Editar Relacionamento. O relacionamento um-para-um deve aparecer corretamente.

Usando a Linguagem de Manipulação de Dados

A Linguagem de Manipulação de Dados (Data Manipulation Language – DML) é utilizada para trabalhar com os dados armazenados em tabelas da base de dados. Ela é usada não apenas para recuperar os dados, mas, também, para criá-los, modifica-los e excluí-los.

Recuperando registros

O comando mais básico e mais usado do SQL é o comando SELECT. Comandos SELECT são os cavalos de carga de todos os comandos SQL e são referenciados normalmente como consultas seleção. Usamos o comando SELECT para recuperar dados das tabelas da base de dados, e os resultados são, normalmente, devolvidos como um conjunto de registros (ou linhas) constituídos de qualquer número de campos (ou colunas). Devemos indicar qual tabela ou tabelas de onde virão os dados usando a cláusula FROM. A estrutura básica de um comando SELECT é:

SELECT lista de campos
    FROM lista de tabelas

Para selecionar todos os campos de uma tabela, use um asterisco (*). Por exemplo, o comando abaixo seleciona todos os campos e todos os registros da tabela de clientes:

SELECT *
    FROM tblCustomers

Para limitar os campos recuperados pela consulta, simplesmente use os nomes dos campos a serem recuperados. Por exemplo:

SELECT [Last Name], Phone
    FROM tblCustomers

Para dar um nome diferente a um campo no conjunto resultante, use a palavra-chave AS para estabelecer um alias (apelido) para aquele campo:

SELECT CustomerID AS [Customer Number]
    FROM tblCustomers

Restringindo o Conjunto Resultante

Na maioria das vezes não desejamos recuperar todos os registros de uma tabela. Desejamos apenas um subconjunto daqueles registros, baseados em algum critério qualificador. Para qualificar um comando SELECT devemos usar a cláusula WHERE, que permite especificar exatamente quais registros desejamos recuperar.

SELECT *
    FROM tblInvoices
    WHERE CustomerID = 1

Note-se a parte CustomerID = 1 da clausula WHERE. Uma cláusula WHERE pode conter até 40 expressões dessas, e elas podem ser unidas usando-se os operadores lógicos AND e OR. A utilização de mais de uma expressão nos permite filtrar ainda mais os registros no conjunto resultante.

SELECT *
    FROM tblInvoices
    WHERE CustomerID = 1 AND InvoiceDate > #01/01/98#

Vemos que a data está entre cerquilhas (#) e no formato americano – mm/dd/aaaa. Se usarmos uma string em uma expressão, esta deverá estar entre aspas simples (‘). Por exemplo:

SELECT *
    FROM tblCustomers
    WHERE [Last Name] = 'White'

Se não sabemos todo o valor da string, podemos usar caracteres coringa em conjunto com o operador LIKE.

SELECT *
    FROM tblCustomers
    WHERE [Last Name] LIKE 'W*'

Temos alguns caracteres coringa que podemos escolher. A tabela abaixo detalha quais são e para que são usados.

Caracter coringa

Descrição

* ou %

Zero ou mais caracteres

? or _ (sublinhado)

Qualquer caracter

#

Qualquer dígito (0-9)

[ListaDeCaracteres]

Qualquer caracter na ListaDeCaracteres

[!ListaDeCaracteres]

Qualquer caracter que não esteja na ListaDeCaracteres

Nota: Os caracteres coringa % e _ (sublinhado) só podem ser usados com o Jet OLE DB e código ADO (ActiveX® Data Objects). Eles serão tratados como caracteres literais se usados na interface de consulta ou em código DAO (Data Access Objects).

Classificando o Conjunto Resultante

Para especificar uma ordem de classificação particular em um ou mais campos no conjunto resultante, usamos a cláusula ORDER BY. Como explicado anteriormente, na seção “Trabalhando com índices”, os registros podem ser classificados em ordem ascendente ou descendente, sendo que a ordem ascendente é a padrão.

Campos referenciados na cláusula ORDER BY não necessitam fazer parte da lista de campos do comando SELECT, e a classificação pode ser aplicada a campos texto, numéricos ou data/hora. A cláusula ORDER BY deve ser colocada sempre no final da declaração SELECT.

SELECT *
    FROM tblCustomers
    ORDER BY [Last Name], [First Name] DESC

Também podemos usar o número dos campos (ou posições) no lugar dos nomes na cláusula ORDER BY.

SELECT *
    FROM tblCustomers
    ORDER BY 2, 3 DESC

Usando Funções de Agregação para Trabalhar com Valores

Funções de agregação são utilizadas para calcular informações estatísticas e de resumo dos dados das tabelas. Estas funções são utilizadas com as declarações SELECT e todas utilizam campos ou expressões como argumento.

Para contra o número de registros em um conjunto resultante, usamos a função Count. A utilização do asterisco na função Count faz com que os valores nulos também sejam contados.

SELECT Count(*) AS [Number of Invoices]
    FROM tblInvoices

Para contar apenas os valores não nulos, usamos a função Count com o nome de um campo.

SELECT Count(Amount) AS
    [Number of Valid Invoice Amounts]
    FROM tblInvoices

Para calcular o valor médio para uma coluna ou expressão de dados numéricos, usamos a função Avg:

SELECT Avg(Amount) AS [Average Invoice Amount]
    FROM tblInvoices

Para calcular o total dos valores em uma coluna ou expressão de dados numéricos, usamos a função Sum:

SELECT Sum(Amount) AS [Total Invoice Amount]
    FROM tblInvoices

Para descobrir o menor valor em uma coluna ou expressão, usamos a função Min:

SELECT Min(Amount) AS [Minimum Invoice Amount]
    FROM tblInvoices

Para descobrir o maior valor para uma coluna ou expressão, usamos a função Max:

SELECT Max(Amount) AS [Maximum Invoice Amount]
    FROM tblInvoices

Para encontrar o primeiro valor em uma coluna ou expressão, usamos a função First:

SELECT First(Amount) AS [First Invoice Amount]
    FROM tblInvoices

Para encontrar o ultimo valor em uma coluna ou expressão, usamos a função Last:

SELECT Last(Amount) AS [Last Invoice Amount]
    FROM tblInvoices

Agrupando Registros em um Conjunto Resultante

Algumas vezes temos registros em uma tabela que são logicamente relacionados, como na tabela de pedidos. Como um cliente pode ter diversos pedidos, poderia ser interessante tratar todos os pedidos de um cliente como um grupo, de modo a obter informações estatísticas e de resumo sobre o grupo.

A chave para o agrupamento de registros é que um ou mais campos em cada registro deve conter o mesmo valor para cada registro no grupo. No caso da tabela de pedidos, o valor do campo CustomerID é o mesmo em todos os pedidos de um cliente.

Para criar um grupo de registros, usamos a cláusula GROUP BY com o nome do campo ou campos pelo qual desejamos fazer o agrupamento.

SELECT CustomerID, Count(*) AS [Number of Invoices],
    Avg(Amount) AS [Average Invoice Amount]
    FROM tblInvoices
    GROUP BY CustomerID

Note-se que a instrução vai devolver um registro que mostra a ID do cliente, o número de pedidos e o valor médio dos pedidos para cada cliente que tenha um registro de pedido na tabela de pedidos. Como os pedidos de cada cliente são tratados como um grupo, podemos contar o número de pedidos e determinar o valor médio de cada pedido.

Podemos especificar uma condição no nível de grupo utilizando a cláusula HAVING, que é similar à cláusula WHERE. Por exemplo, a consulta a seguir devolve apenas aqueles registros, para cada cliente, cujo valor médio de pedidos é menor que 100:

SELECT CustomerID, Count(*) AS [Number of Invoices],
    Avg(Amount) AS [Average Invoice Amount]
    FROM tblInvoices
    GROUP BY CustomerID
    HAVING Avg(Amount) < 100

Inserindo Registros em uma Tabela

Temos, essencialmente, dois métodos para adicionar registros em uma tabela. O primeiro é adicionar um registro por vez; o segundo faz a adição de diversos registros de uma vez. Em ambos os casos, usamos a declaração INSERT INTO do SQL para executar a tarefa. As declarações INSERT INTO normalmente são referenciadas como consultas acréscimo.

Para adicionar um registro a uma tabela, devemos usar a lista de campos para definir em que campo os dados serão colocados e, em seguida, fornecemos os dados em uma lista de valores. Para definir a lista de valores usamos a cláusula VALUES. Poe exemplo, o comando a seguir irá inserir os valores “1”, “Kelly” e “Jill” nos campos CustomerID, Last Name e First Name, respectivamente.

INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
    VALUES (1, 'Kelly', 'Jill')

INSERT INTO tblCustomers
    VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')

Nota: Podemos omitir a lista de campos apenas se fornecermos todos os valores de campos contidos pelo registro.

Para adicionar diversos registros de uma vez em uma tabela, usamos o comando INSERT INTO em conjunto com uma declaração SELECT. Quando inserimos dados de outra tabela, cada valor a ser inserido deve ser compatível com o tipo de campo que vai receber os dados. Para maiores informações sobre os tipos de dados e sua utilização, veja “Microsoft Jet SQL Intermediário para Access 2000”.

O comando INSERT INTO, a seguir, insere todos os valores nos campos CustomerID, Last Name e First Name da tabela tblOldCustomers nos campos correspondentes da tabela tblCustomers.

INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
    SELECT CustomerID, [Last Name], [First Name]
    FROM tblOldCustomers

Se as definições das tabelas são exatamente iguais, podemos deixar de fora a lista de campos:

INSERT INTO tblCustomers
    SELECT * FROM tblOldCustomers

Atualizando registros em uma Tabela

Para modificar os dados contidos em uma tabela, usamos a declaração UPDATE, que normalmente é chamada de consulta atualização. O comando UPDATE pode modificar um ou mais registros e, normalmente, tem este formato:

UPDATE table name
    SET field name = some value

Para atualizar todos os registros de uma tabela, especificamos o nome da tabela e usamos a clausula SET para especificar o campo ou campos a ser alterado.

UPDATE tblCustomers
    SET Phone = 'None'

Na maioria dos casos, precisamos qualificar o comando UPDATE com uma cláusula WHERE para limitar o número de registros alterados.

UPDATE tblCustomers
    SET Email = 'None'
    WHERE [Last Name] = 'Smith'

Excluindo Registros de uma Tabela

Para excluir dados que estão armazenados em uma tabela, usamos o comando DELETE, que também é chamado de consulta exclusão e, também, truncar uma tabela. O comando DELETE pode excluir um ou mais registros de uma tabela e, normalmente, tem este formato:

DELETE FROM table list

O com mando DELETE não remove a estrutura da tabela, apenas os dados contidos pela estrutura da tabela. Para excluir todos os registros de uma tabela, usamos o comando DELETE e especificamos de qual tabela ou tabelas vamos excluir todos os registros.

DELETE FROM tblInvoices

Na maior parte das vezes, necessitamos qualificar o comando DELETE com uma cláusula WHERE para limitar o número de registros a serem excluídos.

DELETE FROM tblInvoices
    WHERE InvoiceID = 3

Se desejarmos remover dados de apenas alguns campos de uma tabela, usamos o com mando UPDATE e deixamos tais campos iguais a NULL, mas apenas se os campos forem anuláveis. Para maiores informações sobre campos anuláveis, veja “Microsoft Jet SQL Intermediário para Access 2000”.

UPDATE tblCustomers
    SET Email = Null

Usando SQL no Access

Agora que já temos uma visão básica da sintaxe do SQL, vejamos como podemos usá-la em um aplicativo Access. Para fazer isso usaremos o banco de dados de exemplo incluído com este artigo. Através de consultas e códigos, o exemplo acFundSQL.mdb demonstra os diferentes comandos SQL discutidos neste artigo.


Nota: muitas das consultas de exemplo usadas no acFundSQL.mdb dependem da existência de certas tabelas e que elas contenham dados. Como algumas das consultas no acFundSQL alteram os dados ou a estrutura da base de dados, podemos eventualmente ter dificuldade em executar outras consultas devido a dados, tabelas ou índices ausentes ou alterados. Se ocorrer este problema, é só executar o formulário frmResetTables e clicar no botão Reset Tables para recriar as tabelas e seus valores padrão iniciais. Para executar passo-a-passo o processo de inicialização das tabelas, execute as consultas abaixo, na ordem em que aparecem:
  • Drop Shipping Table
  • Drop Invoices Table
  • Drop Customers Table
  • Create Customers Table
  • Create Invoices Table
  • Create Shipping Table
  • Populate Customers
  • Populate Invoices
  • Populate Shipping

Construindo Consultas

Consultas são comandos SQL salvos em uma base de dados Access e podem ser usadas a qualquer momento, seja diretamente a partir da interface de usuário do Access ou usando a linguagem Visual Basic® para Aplicativos (VBA). Podemos criar consultas usando o modo Design, que facilita bastante a montagem dos comandos SQL, ou digitando os comandos SQL diretamente na janela modo SQL.

Como mencionado no início deste artigo, o Access converte todas as tarefas orientadas a dados da base de dados em comandos SQL. Para demonstrar este comportamento, vamos construir uma consulta no modo Design.

  1. Abra o banco de dados acFundSQL.mdb.
  2. Verifique se a tabela tblCustomers foi criada e que contém alguns dados.
  3. Na janela Banco de dados, clique em Consulta, abaixo de Objetos e, então, clique em Nova na barra de ferramentas da janela Banco de dados.
  4. Na caixa de diálogo Nova Consulta, clique em Modo Design e em Ok.
  5. Na caixa de diálogo Mostrar tabela, clique em tblCustomers, clique em Adicionar e, em seguida, em Fechar.
  6. Na lista de campos de tblCustomers, clique no asterisco (*) e arraste-o para o primeiro campo da grade de construção de consulta.
  7. No menu Exibir, clique em Modo SQL. Isto abre a janela modo SQL e mostra a sintaxe SQL usada pelo Access para esta consulta.

Nota: Esta consulta é similar à consulta Select All Customers já existente no banco de dados acFundSQL.

Especificando a Origem dos Dados

Para criar uma conexão com os dados das tabelas de um banco de dados, o Access utilize as propriedades de origem de dados. Por exemplo, um formulário tem uma propriedade Origem dos dados que o conecta a uma tabela particular do banco de dados. Em qualquer lugar em que uma origem de dados é especificada, podemos usar um comando SQL (ou uma consulta salva) no lugar do nome de uma tabela. Por exemplo, vamos criar um novo formulário que se conecta à tabela de clientes usando um comando SQL SELECT como origem dos dados.

  • Abra o banco de dados acFundSQL.mdb e verifique se a tabela tblCustomers foi criada e contém alguns dados.
  • Na janela Banco de dados, clique em Formulários, abaixo de Objetos, e clique em Novo na barra de ferramentas da janela Banco de dados.
  • Na caixa de diálogo Novo Formulário, clique em Modo de design e, então, em Ok. Um formulário em branco se abre na janela Design.
  • No menu Exibir, clique em Propriedades para exibir a folha de propriedades do formulário.
  • Na caixa de texto da propriedade Origem dos dados, digite o seguinte comando SQL:

SELECT * FROM tblCustomers

  • Pressione ENTER no seu teclado. A lista de campos aparece, mostrando todos os campos disponíveis na tabela tblCustomers.
  • Selecione todos os campos mantendo pressionada a tecla SHIFT e clicando no primeiro e no último campo listado.
  • Arraste os campos selecionados para o centro da seção Detalhe do formulário em branco e libere o botão do mouse.
  • Feche a folha de propriedades.
  • No menu Exibir, clique em Modo formulário e use os seletores de registros no pé do formulário para passear por todos os registros na tabela tblCustomers.

Outro lugar interessante para usar um comando SQL é na propriedade Origem da linha de uma caixa de listagem ou caixa de combinação. Vamos construir outro formulário simples com uma caixa de combinação que usa um comando SQL SELECT como sua origem de dados.

  • Abra o banco de dados acFundSQL.mdb e verifique se a tabela tblCustomers foi criada e contém alguns dados.
  • Crie um novo formulário e abra-o em modo Design.
  • No menu Exibir, clique em Caixa de ferramentas.
  • Assegure-se que o botão Assistentes de controle (varinha mágica) não esteja pressionado.
  • Clique no botão Caixa de combinação e, depois, no centro da seção detalhe do formulário em branco.
  • Selecione a caixa de combinação no formulário e clique em Propriedades no menu Exibir.
  • Na caixa de texto da propriedade Origem da linha, digite o com mando SQL a seguir:

SELECT [Last Name] FROM tblCustomers

  • Tecle ENTER e feche a folha de propriedades.
  • No menu Exibir, clique em Modo formulário. No formulário, clique na seta da caixa de combinação. Veja que os sobrenomes de todos os clientes estão listados na caixa de combinação.

Usando Comandos SQL em Código

O processo de utilização de comandos SQL dentro de código VBA é chamado de “inline”. Ainda que uma discussão profunda sobre como usar o VBA esteja fora do âmbito deste artigo, é uma tarefa bastante simples a execução de código SQL em comandos VBA.

Digamos que precisamos executar um comando UPDATE no código e desejamos que este código seja executado quando o usuário clicar em um botão.

  • Abra o banco de dados acFundSQL.mdb e verifique se a tabela tblCustomers foi criada e contém alguns dados.
  • Crie um novo formulário e abra-o no Modo design.
  • No menu Exibir, clique em Caixa de ferramentas.
  • Assegure-se que o botão Assistentes de controle (varinha mágica) não esteja pressionado.
  • Clique no botão “Botão de comando” e, em seguida, no centro da seção Detalhe do formulário em branco.
  • Assegure-se que o botão incluído está selecionado e clique em Propriedades do menu Exibir.
  • Clique nas seguintes caixas de texto de propriedades e entre com os valores dados:
    Nome: cmdUpdatePhones
    Legenda: Atualiza Fones
  • Clique na propriedade Ao clicar, clique no botão Construir ([...]), e clique em Construtor de códigos para abrir o Editor do Visual Basic.
  • Digite ou cole as seguintes linhas de código no subprocedimento cmdUpdatePhones_Click:

Dim conDatabase As ADODB.Connection
Dim strSQL As String

    Set conDatabase = CurrentProject.Connection

    strSQL = "UPDATE tblCustomers SET Phone = 'None'"
    conDatabase.Execute strSQL

    MsgBox "All phones have been set to ""None""."

    conDatabase.Close
    Set conDatabase = Nothing

  • Feche o Editor do Visual Basic, feche a folha de propriedades e clique em Modo formulário do menu Exibir.
  • Clique no botão Atualizar Fones. Deve aparecer uma caixa de mensagem dizendo que os números de telefones foram alterados para “None”. Verifique abrindo a tabela tblCustomers.

Ainda que a utilização de comandos SQL inline seja ótimo para consultas ação (ou seja, acréscimo, exclusão e atualização), elas são mais utilizadas em consultas seleção para criar conjuntos de registros. Suponhamos que queremos passear por um conjunto resultante para executar o que foi feito com o comando UPDATE. Seguindo um procedimento similar ao do exemplo de UPDATE, use o código a seguir no subprocedimento cmdSelectPhones_Click:

Dim conDatabase As ADODB.Connection

Dim rstCustomers As ADODB.Recordset
Dim strSQL As String

    Set conDatabase = CurrentProject.Connection
    strSQL = "SELECT Phone FROM tblCustomers"

    Set rstCustomers = New Recordset
    rstCustomers.Open strSQL, conDatabase, _
    adOpenDynamic, adLockOptimistic

    With rstCustomers
        Do While Not .EOF
            !Phone = "None"
            .Update
            .MoveNext
        Loop
    End With

    MsgBox "All phones have been set to ""None""."

    rstCustomers.Close
    conDatabase.Close

    Set rstCustomers = Nothing
    Set conDatabase = Nothing


Na maioria dos casos teremos um melhor desempenho usando o com mando UPDATE pois ele trabalha na tabela como um todo, tratando-a como um único conjunto de dados. Entretanto, podem ocorrer situações em que é necessário executar o loop em um conjunto de registros para alcançar os resultados desejados.

Um Último Comentário

Pode parecer difícil de acreditar, mas este artigo mal arranhou a superfície da linguagem SQL aplicada ao Access. Agora você já deve ter um bom entendimento básico do SQL e como usa-lo em seus aplicativos Access 2000. Teste estes novos conhecimentos usando o SQL em qualquer propriedade Origem dos dados e Origem da linha que encontrar, e use os recursos listados na próxima seção para aprofundar seus conhecimentos de SQL e Access.

Recursos Adicionais

Recurso

Descrição

Ajuda e Referência do Microsoft Jet SQL

É a fonte definitiva para a linguagem SQL aplicada ao Access 2000. Pode ser encontrado na seção Conteúdo do Help do Microsoft Access 2000.

Help do Microsoft Access 2000

Uma fonte de informações insubstituível sobre os tópicos de programação do Access 2000.

Guia de Programação Office 2000/Visual Basic

Este livro cobre a utilização do VBA para programar aplicativos Office 2000.

Links relacionados:
Home

Contato | Copyright©Osmar José Correia Júnior | 24-Nov-2005 18:23
24-Nov-2005 19:5424-Nov-2005 19:54