Come scrivere query SQL di Microsoft Access da zero

Microsoft Access è probabilmente lo strumento più potente nell'intera suite di Microsoft Office, eppure mistifica (ea volte fa paura) i potenti utenti di Office. Con una curva di apprendimento più ripida rispetto a Word o Excel, come si pensa che ognuno possa girare la testa intorno all'uso di questo strumento? Questa settimana, Bruce Epper esaminerà alcuni dei problemi stimolati da questa domanda da uno dei nostri lettori.

Un lettore chiede:

Ho problemi a scrivere una query in Microsoft Access.

Ho un database con due tabelle di prodotti contenenti una colonna comune con un codice prodotto numerico e un nome prodotto associato.

Voglio scoprire quali prodotti della Tabella A sono disponibili nella Tabella B. Voglio aggiungere una colonna denominata Risultati che contiene il nome del prodotto dalla Tabella A se esiste, e il nome del prodotto dalla Tabella B quando non esiste nella tabella A.

Hai qualche consiglio??

La risposta di Bruce:

Microsoft Access è un sistema di gestione dei database (DBMS) progettato per l'utilizzo su macchine Windows e Mac. Utilizza il motore di database Jet di Microsoft per l'elaborazione e l'archiviazione dei dati. Fornisce anche un'interfaccia grafica per gli utenti che quasi elimina la necessità di comprendere Structured Query Language (SQL).

SQL è il linguaggio dei comandi utilizzato per aggiungere, eliminare, aggiornare e restituire le informazioni memorizzate nel database e modificare componenti del database di base come l'aggiunta, l'eliminazione o la modifica di tabelle o indici.

Punto di partenza

Se non hai già familiarità con Access o un altro RDBMS, ti suggerisco di iniziare con queste risorse prima di procedere:

• Allora, cos'è un database? Quindi, che cos'è un database, comunque? [MakeUseOf Explains] Quindi, che cos'è un database, comunque? [MakeUseOf Explains] Per un programmatore o un appassionato di tecnologia, il concetto di un database è qualcosa che può davvero essere dato per scontato. Tuttavia, per molte persone il concetto stesso di database è un po 'estraneo ... Leggi di più dove Ryan Dube utilizza Excel per mostrare le basi dei database relazionali.
• Una guida rapida per iniziare con Microsoft Access 2007 Una guida rapida per iniziare con Microsoft Access 2007 Una guida rapida per iniziare con Microsoft Access 2007 Leggi di più che è una panoramica di alto livello di Access e dei componenti che comprendono un database di Access.
• Un tutorial rapido per le tabelle in Microsoft Access 2007 Un rapido tutorial per le tabelle in Microsoft Access 2007 Un rapido tutorial per le tabelle in Microsoft Access 2007 Leggi di più dà un'occhiata alla creazione del tuo primo database e tabelle per memorizzare i tuoi dati strutturati.
• Un'esercitazione rapida sulle query in Microsoft Access 2007 Un'esercitazione rapida sulle query in Microsoft Access 2007 Un tutorial rapido sulle query in Microsoft Access 2007 Ulteriori informazioni esamina i mezzi per restituire parti specifiche dei dati archiviati nelle tabelle del database.

Avere una conoscenza di base dei concetti forniti in questi articoli renderà il seguente un po 'più facile da digerire.

Relazioni con il database e normalizzazione

Immagina di gestire un'azienda che vende 50 tipi diversi di widget in tutto il mondo. Hai una base clienti di 1.250 e in un mese medio vendi 10.000 widget a questi client. Attualmente stai utilizzando un singolo foglio di calcolo per tracciare tutte queste vendite, in pratica una singola tabella di database. E ogni anno aggiunge migliaia di righe al tuo foglio di calcolo.

Le immagini di cui sopra fanno parte del foglio di calcolo per il tracciamento dell'ordine che stai utilizzando. Ora, entrambi questi clienti acquistano i widget da te diverse volte all'anno, quindi hai molte più file per entrambi.

Se Joan Smith sposa Ted Baines e prende il suo cognome, ogni singola riga che contiene il suo nome ora deve essere cambiata. Il problema si aggrava se ti capita di avere due clienti diversi con il nome "Joan Smith". È diventato molto più difficile mantenere coerenti i dati di vendita a causa di un evento abbastanza comune.

Utilizzando un database e normalizzando i dati, possiamo separare gli articoli in più tabelle come inventario, clienti e ordini.

Basta guardare la parte client del nostro esempio, rimuovere le colonne per Nome cliente e Indirizzo cliente e inserirle in una nuova tabella. Nell'immagine sopra, ho anche risolto meglio le cose per un accesso più granulare ai dati. La nuova tabella contiene anche una colonna per una chiave primaria (ClientID) - un numero che verrà utilizzato per accedere a ciascuna riga in questa tabella.

Nella tabella originale in cui sono stati rimossi questi dati, aggiungeremmo una colonna per una chiave esterna (ClientID) che è ciò che collega alla riga corretta contenente le informazioni per questo particolare client.

Ora, quando Joan Smith cambia il suo nome in Joan Baines, la modifica deve essere effettuata solo una volta nella tabella Client. Ogni altro riferimento dalle tabelle unite trarrà il nome del cliente appropriato e un rapporto che sta guardando ciò che Joan ha acquistato negli ultimi 5 anni otterrà tutti gli ordini con entrambi i suoi nomi da nubile e sposato, senza dover cambiare la modalità di generazione del rapporto.

Come ulteriore vantaggio, questo riduce anche la quantità totale di spazio di archiviazione consumato.

Unire tipi

SQL definisce cinque diversi tipi di join: INTERNO, SINISTRO ESTERNO, GIUSTO ESTERNO, FULL ESTERNO e CROCE. La parola chiave OUTER è facoltativa nell'istruzione SQL.

Microsoft Access consente l'uso di INNER (predefinito), LEFT OUTER, RIGHT OUTER e CROSS. FULL OUTER non è supportato come tale, ma usando LEFT OUTER, UNION ALL e RIGHT OUTER, può essere falsificato al costo di più cicli della CPU e operazioni I / O.

L'output di un join CROSS contiene ogni riga della tabella sinistra associata a ogni riga del tavolo destro. L'unica volta in cui ho mai visto un join CROSS utilizzato è durante il test di carico dei server di database.

Diamo un'occhiata a come funzionano i join di base, quindi li modificheremo in base alle nostre esigenze.

Iniziamo con la creazione di due tabelle, ProdA e ProdB, con le seguenti proprietà di progettazione.

Il Contatore è un numero intero a incremento automatico assegnato alle voci man mano che vengono aggiunte alla tabella. L'opzione Testo non è stata modificata, quindi accetta una stringa di testo lunga fino a 255 caratteri.

Ora, inseriscili con alcuni dati.

Per mostrare le differenze nel modo in cui funzionano i 3 tipi di join, ho cancellato le voci 1, 5 e 8 di ProdA.

Quindi, crea una nuova query andando a Crea> Design query. Seleziona entrambe le tabelle dalla finestra di dialogo Mostra tabella e fare clic su Aggiungi, poi Vicino.

Fare clic su ProductID nella tabella ProdA, trascinarlo su ProductID nella tabella ProdB e rilasciare il pulsante del mouse per creare la relazione tra le tabelle.

Fare clic con il tasto destro sulla linea tra le tabelle che rappresentano la relazione tra gli elementi e selezionare Unisci proprietà.

Per impostazione predefinita, è selezionato il tipo di join 1 (INTERNO). L'opzione 2 è un join LEFT OUTER e 3 è un join DESTRO ESTERNO.

Guarderemo prima il join INNER, quindi fai clic su OK per chiudere la finestra di dialogo.

In Progettazione query, selezionare i campi che si desidera visualizzare dagli elenchi a discesa.

Quando eseguiamo la query (il punto esclamativo rosso nella barra multifunzione), mostrerà il campo ProductName da entrambe le tabelle con il valore dalla tabella ProdA nella prima colonna e ProdB nella seconda.

Si noti che i risultati mostrano solo i valori in cui ProductID è uguale in entrambe le tabelle. Anche se esiste una voce per ProductID = 1 nella tabella ProdB, non viene visualizzata nei risultati poiché ProductID = 1 non esiste nella tabella ProdA. Lo stesso vale per ProductID = 11. Esiste nella tabella ProdA ma non nella tabella ProdB.

Utilizzando il pulsante Visualizza sulla barra multifunzione e passando a Vista SQL, è possibile visualizzare la query SQL generata dal progettista utilizzato per ottenere questi risultati.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Tornando a Design View, cambia il tipo di join in 2 (LEFT OUTER). Esegui la query per vedere i risultati.

Come puoi vedere, ogni voce nella tabella ProdA è rappresentata nei risultati, mentre solo quelli in ProdB che hanno una voce ProductID corrispondente nella tabella ProdB vengono visualizzati nei risultati.

Lo spazio vuoto nella colonna ProdB.ProductName è un valore speciale (NULL) poiché non esiste un valore corrispondente nella tabella ProdB. Questo sarà importante più tardi.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Prova la stessa cosa con il terzo tipo di join (RIGHT OUTER).

I risultati mostrano tutto dalla tabella ProdB mentre mostra valori vuoti (noti come NULL) in cui la tabella ProdA non ha un valore corrispondente. Finora, questo ci porta più vicino ai risultati desiderati nella domanda del nostro lettore.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA DIRETTO JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Utilizzo delle funzioni in una query

I risultati di una funzione possono anche essere restituiti come parte di una query. Vogliamo che una nuova colonna denominata "Risultati" appaia nel nostro set di risultati. Il suo valore sarà il contenuto della colonna ProductName della tabella ProdA se ProdA ha un valore (non è NULL), altrimenti dovrebbe essere preso dalla tabella ProdB.

La funzione IF immediato (IIF) può essere utilizzata per generare questo risultato. La funzione accetta tre parametri. Il primo è una condizione che deve valutare un valore True o False. Il secondo parametro è il valore da restituire se la condizione è True e il terzo parametro è il valore da restituire se la condizione è False.

Il costrutto di funzioni complete per la nostra situazione è simile a questo:

IIF (ProdA.ProductID è Null, ProdB.ProductName, ProdA.ProductName)

Si noti che il parametro condizione non controlla l'uguaglianza. Un valore Null in un database non ha un valore che può essere confrontato con qualsiasi altro valore, incluso un altro valore Null. In altre parole, Null non è uguale a Null. Mai. Per superare questo, controlliamo invece il valore usando la parola chiave 'Is'.

Potremmo anche aver usato "Is Not Null" e modificato l'ordine dei parametri True e False per ottenere lo stesso risultato.

Quando si inserisce questo nella Progettazione query, è necessario digitare l'intera funzione nel campo: voce. Per farlo creare la colonna "Risultati", è necessario utilizzare un alias. Per fare ciò, preface la funzione con 'Results:' come si vede nello screenshot seguente.

Il codice SQL equivalente per fare questo sarebbe:

SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID è Null, ProdB.ProductName, ProdA.ProductName) AS Risultati FROM ProdA DIRETTO JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Ora, quando eseguiamo questa query, produrrà questi risultati.

Qui vediamo per ogni voce in cui la tabella ProdA ha un valore, tale valore si riflette nella colonna Risultati. Se non c'è una voce nella tabella ProdA, la voce da ProdB appare in Risultati, che è esattamente ciò che il nostro lettore ha chiesto.

Per ulteriori risorse per l'apprendimento di Microsoft Access, consulta Joel Lee's Come imparare Microsoft Access: 5 risorse online gratuite Come imparare Microsoft Access: 5 risorse online gratuite Come imparare Microsoft Access: 5 risorse online gratuite Devi gestire una grande quantità di risorse di dati? Dovresti cercare in Microsoft Access. Le nostre risorse di studio gratuite possono aiutarti a iniziare e apprendere le competenze per progetti più complessi. Leggi di più .