Com escriure consultes SQL de Microsoft Access des de zero

Com escriure consultes SQL de Microsoft Access des de zero

Microsoft Access és sens dubte l’eina més potent de tota la suite de Microsoft Office, tot i que desconcerta (i de vegades fa por) als usuaris avançats d’Office. Amb una corba d’aprenentatge més pronunciada que Word o Excel, com se suposa que algú ha d’embolicar el cap amb l’ús d’aquesta eina? Aquesta setmana, Bruce Epper examinarà alguns dels temes esperonats per aquesta pregunta d’un dels nostres lectors.



Un lector pregunta:

Tinc problemes per escriure una consulta a Microsoft Access. Tinc una base de dades amb dues taules de productes que contenen una columna comuna amb un codi de producte numèric i un nom de producte associat. Vull esbrinar quins productes de la taula A es poden trobar a la taula B. Vull afegir una columna anomenada Resultats que contingui el nom del producte de la taula A si existeix i el nom del producte de la taula B quan no existeix a la taula A. Tens algun consell?





Resposta de Bruce:

Microsoft Access és un sistema de gestió de bases de dades (SGBD) dissenyat per utilitzar-se tant en màquines Windows com Mac. Utilitza el motor de base de dades Jet de Microsoft per al processament i emmagatzematge de dades. També proporciona una interfície gràfica per als usuaris que gairebé elimina la necessitat d'entendre el Llenguatge de consulta estructurat (SQL).





SQL és el llenguatge d’ordres que s’utilitza per afegir, suprimir, actualitzar i retornar informació emmagatzemada a la base de dades, així com per modificar components bàsics de la base de dades, com ara afegir, suprimir o modificar taules o índexs.

Punt de partida

Si encara no coneixeu Access o un altre RDBMS, us suggeriria que comenceu amb aquests recursos abans de continuar:



  • Llavors, què és una base de dades? on Ryan Dube utilitza Excel per mostrar els conceptes bàsics de les bases de dades relacionals.
  • Una guia ràpida per començar a utilitzar Microsoft Access 2007 que és una visió general d’alt nivell d’Access i dels components que formen una base de dades Access.
  • Un tutorial ràpid sobre les taules de Microsoft Access 2007 fa una ullada a la creació de la vostra primera base de dades i taules per emmagatzemar les vostres dades estructurades.
  • Un tutorial ràpid sobre consultes a Microsoft Access 2007 analitza els mitjans per retornar porcions específiques de les dades emmagatzemades a les taules de la base de dades.

Tenir una comprensió bàsica dels conceptes proporcionats en aquests articles farà que el següent sigui una mica més fàcil de digerir.

Relacions i normalització de bases de dades

Imagineu que dirigiu una empresa que ven 50 tipus de ginys diferents a tot el món. Teniu una base de clients de 1.250 i en un mes mitjà venreu 10.000 ginys a aquests clients. Actualment utilitzeu un sol full de càlcul per fer un seguiment de totes aquestes vendes, efectivament una única taula de base de dades. I cada any afegeix milers de files al full de càlcul.





Les imatges anteriors formen part del full de càlcul de seguiment de comandes que feu servir. Ara, digueu que tots dos clients us compren ginys diverses vegades a l'any, de manera que teniu moltes més files per a tots dos.





Si Joan Smith es casa amb Ted Baines i pren el seu cognom, ara cal canviar totes les files que continguin el seu nom. El problema s’agreuja si teniu dos clients diferents amb el nom de 'Joan Smith'. S'ha tornat molt més difícil mantenir les vostres dades de vendes coherents a causa d'un esdeveniment bastant comú.

En utilitzar una base de dades i normalitzar les dades, podem separar els elements en diverses taules, com ara inventari, clients i comandes.

Simplement mirant la part del client del nostre exemple, eliminaríem les columnes del nom i l'adreça del client i les col·locaríem en una taula nova. A la imatge superior, també he trencat les coses millor per tenir un accés més granular a les dades. La nova taula també conté una columna per a una clau principal (ClientID), un número que s’utilitzarà per accedir a cada fila d’aquesta taula.

A la taula original on vam eliminar aquestes dades, afegiríem una columna per a una clau estrangera (ClientID) que és la que enllaça a la fila adequada que conté la informació d’aquest client en particular.

Ara, quan Joan Smith canvia el seu nom per Joan Baines, el canvi només s’ha de fer un cop a la taula Client. Totes les altres referències de les taules unides obtindran el nom del client adequat i un informe que analitzi el que ha comprat Joan durant els darrers 5 anys obtindrà totes les comandes tant amb el seu nom de soltera com de casat sense haver de canviar la forma en què es genera l'informe. .

Com a avantatge addicional, això també redueix la quantitat total d'emmagatzematge consumit.

Unir tipus

SQL defineix cinc tipus diferents d'unions: INTERIOR, EXTERIOR ESQUERRA, EXTERIOR DRETA, EXTERIOR COMPLET i CROSS. La paraula clau OUTER és opcional a la sentència SQL.

Microsoft Access permet utilitzar INTERIOR (per defecte), ESQUERRA EXTERIOR, EXTERIOR DRETA i CROSS. FULL OUTER no s'admet com a tal, però si s'utilitza LEFT OUTER, UNION ALL i RIGHT OUTER, es pot falsificar al cost de més cicles de CPU i operacions d'E / S.

La sortida d'una unió CROSS conté totes les files de la taula esquerra emparellades amb totes les files de la taula dreta. L'única vegada que he vist utilitzar una unió CROSS és durant les proves de càrrega de servidors de bases de dades.

Vegem com funcionen les combinacions bàsiques i les modificarem per adaptar-les a les nostres necessitats.

Comencem creant dues taules, ProdA i ProdB, amb les propietats de disseny següents.

El número automàtic és un enter llarg que s'incrementa automàticament assignat a les entrades a mesura que s'afegeixen a la taula. L'opció Text no s'ha modificat, de manera que acceptarà una cadena de text de fins a 255 caràcters.

Ara, empleneu-los amb algunes dades.

Per mostrar les diferències en el funcionament dels 3 tipus d'unions, he suprimit les entrades 1, 5 i 8 de ProdA.

Pròxim, creeu una nova consulta anant a Crea> Disseny de consultes . Seleccioneu les dues taules del quadre de diàleg Mostra la taula i feu clic a Afegeix , doncs Tanca .

Feu clic a ProductID a la taula ProdA, arrossegueu-lo a ProductID a la taula ProdB i deixeu anar el botó del ratolí per crear la relació entre les taules.

Feu clic amb el botó dret a la línia entre les taules que representa la relació entre els elements i seleccioneu Uneix les propietats .

Per defecte, es selecciona el tipus de combinació 1 (INTERIOR). L'opció 2 és una unió a l'esquerra i 3 és una unió a la dreta.

Primerament veurem la unió interior, així que feu clic a D'acord per tancar el diàleg.

Al dissenyador de consultes, seleccioneu els camps que volem veure a les llistes desplegables.

Quan executem la consulta (el signe d’exclamació vermell de la cinta), es mostrarà el camp Nom del producte de les dues taules amb el valor de la taula ProdA a la primera columna i ProdB a la segona.

Fixeu-vos que els resultats només mostren valors on ProductID és igual a les dues taules. Tot i que hi ha una entrada per ProductID = 1 a la taula ProdB, no apareix als resultats ja que ProductID = 1 no existeix a la taula ProdA. El mateix s'aplica a ProductID = 11. Existeix a la taula ProdA però no a la taula ProdB.

En utilitzar el botó Visualitza de la cinta i canviar a Visualització SQL, podeu veure la consulta SQL generada pel dissenyador que s’utilitza per obtenir aquests resultats.

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

Tornant a Visualització de disseny, canvieu el tipus d'unió a 2 (ESQUERRA EXTERIOR). Executeu la consulta per veure els resultats.

Com podeu veure, totes les entrades de la taula ProdA es representen als resultats, mentre que només apareixen als resultats les de ProdB que tenen una entrada ProductID coincident a la taula ProdB.

L'espai en blanc de la columna ProdB.ProductName és un valor especial (NULL), ja que no hi ha un valor coincident a la taula ProdB. Això resultarà important més endavant.

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

Proveu el mateix amb el tercer tipus d'unió (RIGHT OUTER).

Els resultats mostren tot, des de la taula ProdB mentre mostra els valors en blanc (coneguts com NULL), on la taula ProdA no té un valor coincident. Fins ara, això ens apropa més als resultats desitjats en la pregunta del nostre lector.

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

Ús de funcions en una consulta

Els resultats d'una funció també es poden retornar com a part d'una consulta. Volem que aparegui una nova columna anomenada 'Resultats' al nostre conjunt de resultats. El seu valor serà el contingut de la columna ProductName de la taula ProdA si ProdA té un valor (no és NULL), en cas contrari s’hauria de treure de la taula ProdB.

Es pot utilitzar la funció IF immediata (IIF) per generar aquest resultat. La funció pren tres paràmetres. La primera és una condició que s’ha d’avaluar a un valor vertader o fals. El segon paràmetre és el valor que s’ha de retornar si la condició és True i el tercer paràmetre és el valor que s’ha de retornar si la condició és False.

La construcció de funcions completes per a la nostra situació té aquest aspecte:

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

Fixeu-vos que el paràmetre de condició no comprova la igualtat. Un valor nul en una base de dades no té un valor que es pugui comparar amb cap altre valor, inclòs un altre valor nul. En altres paraules, Null no és igual a Null. Sempre. Per superar-ho, comprovem el valor amb la paraula clau 'És'.

També podríem haver utilitzat 'No és nul' i canviar l'ordre dels paràmetres True i False per obtenir el mateix resultat.

En posar-ho al Query Designer, heu d'escriure tota la funció a l'entrada Field :. Per crear la columna 'Resultats', heu d'utilitzar un àlies. Per fer-ho, prefaceu la funció amb 'Resultats', tal com es veu a la captura de pantalla següent.

El codi SQL equivalent per fer-ho seria:

SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Ara, quan executem aquesta consulta, produirà aquests resultats.

quantes dades utilitza la transmissió

Aquí veiem per a cada entrada on la taula ProdA té un valor, aquest valor es reflecteix a la columna Resultats. Si no hi ha cap entrada a la taula ProdA, l'entrada de ProdB apareix a Resultats, que és exactament el que va demanar el nostre lector.

Per obtenir més recursos per aprendre Microsoft Access, consulteu How to Learn Microsoft Access de Joel Lee: 5 recursos en línia gratuïts.

Compartir Compartir Tweet Correu electrònic Val la pena actualitzar-lo a Windows 11?

Windows s’ha redissenyat. Però, n’hi ha prou per convèncer-vos de canviar de Windows 10 a Windows 11?

Llegiu a continuació
Temes relacionats
  • Productivitat
  • Pregunteu als experts
Sobre l'autor Bruce Epper(13 articles publicats)

Bruce ha estat jugant amb l'electrònica des dels anys 70, amb els ordinadors des de principis dels 80 i responent amb precisió preguntes sobre tecnologia que no ha utilitzat ni ha vist durant tot el temps. També es molesta intentant tocar la guitarra.

Més de Bruce Epper

Subscriu-te al nostre butlletí

Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.

Feu clic aquí per subscriure-us