Cara Menulis Pertanyaan SQL Microsoft Access Dari Awal

Cara Menulis Pertanyaan SQL Microsoft Access Dari Awal

Microsoft Access boleh dikatakan alat paling kuat di seluruh rangkaian Microsoft Office, namun ia mengagumkan (dan kadang-kadang menakutkan) pengguna kuasa Office. Dengan keluk pembelajaran yang lebih curam daripada Word atau Excel, bagaimana ada orang yang harus memikirkan penggunaan alat ini? Minggu ini, Bruce Epper akan melihat beberapa isu yang ditimbulkan oleh soalan ini dari salah seorang pembaca kami.





Pembaca bertanya:

Saya menghadapi masalah untuk menulis pertanyaan di Microsoft Access. Saya mempunyai pangkalan data dengan dua jadual produk yang mengandungi lajur umum dengan kod produk berangka dan nama produk yang berkaitan. Saya ingin mengetahui produk mana dari Jadual A yang dapat dijumpai dalam Jadual B. Saya ingin menambahkan lajur bernama Hasil yang mengandungi nama produk dari Jadual A jika ada, dan nama produk dari Jadual B apabila tidak ada di Jadual A.Adakah anda mempunyai nasihat?





Balas Bruce:

Microsoft Access adalah Sistem Pengurusan Pangkalan Data (DBMS) yang direka untuk digunakan pada mesin Windows dan Mac. Ini menggunakan mesin pangkalan data Jet Microsoft untuk pemprosesan dan penyimpanan data. Ia juga menyediakan antara muka grafik untuk pengguna yang hampir menghilangkan keperluan untuk memahami Structured Query Language (SQL).





SQL adalah bahasa perintah yang digunakan untuk menambah, menghapus, mengemas kini, dan mengembalikan maklumat yang disimpan dalam pangkalan data serta mengubah komponen pangkalan data teras seperti menambah, menghapus, atau mengubah jadual atau indeks.

Titik permulaan

Sekiranya anda belum mengetahui tentang Access atau RDBMS lain, saya cadangkan anda memulakan dengan sumber ini sebelum meneruskan:



  • Jadi Apa itu Pangkalan Data? di mana Ryan Dube menggunakan Excel untuk menunjukkan asas pangkalan data hubungan.
  • Panduan Pantas Untuk Memulakan Microsoft Access 2007 yang merupakan gambaran keseluruhan mengenai Akses dan komponen yang merangkumi pangkalan data Access.
  • Tutorial Pantas untuk Jadual di Microsoft Access 2007 melihat-lihat membuat pangkalan data dan jadual pertama anda untuk menyimpan data berstruktur anda.
  • Tutorial Pantas Mengenai Pertanyaan Di Microsoft Access 2007 melihat kaedah untuk mengembalikan bahagian tertentu dari data yang disimpan dalam jadual pangkalan data.

Memiliki pemahaman asas mengenai konsep yang disediakan dalam artikel ini akan menjadikan perkara berikut sedikit lebih mudah dicerna.

Hubungan dan Normalisasi Pangkalan Data

Bayangkan anda menjalankan syarikat yang menjual 50 jenis widget di seluruh dunia. Anda mempunyai pangkalan pelanggan 1,250 dan dalam sebulan rata-rata menjual 10,000 widget kepada pelanggan ini. Anda kini menggunakan satu hamparan untuk mengesan semua penjualan ini - dengan berkesan jadual pangkalan data tunggal. Dan setiap tahun menambah ribuan baris ke hamparan anda.





Gambar di atas adalah sebahagian daripada spreadsheet penjejakan pesanan yang anda gunakan. Sekarang katakan kedua-dua pelanggan ini membeli widget dari anda beberapa kali dalam setahun sehingga anda mempunyai lebih banyak baris untuk kedua-dua mereka.





Sekiranya Joan Smith mengahwini Ted Baines dan mengambil nama keluarga, setiap baris yang mengandungi namanya kini perlu diubah. Masalahnya bertambah buruk sekiranya anda mempunyai dua pelanggan yang berbeza dengan nama 'Joan Smith'. Menjadi lebih sukar untuk memastikan data penjualan anda tetap konsisten kerana peristiwa yang agak biasa.

Dengan menggunakan pangkalan data dan menormalkan data, kita dapat memisahkan item ke dalam beberapa jadual seperti inventori, klien, dan pesanan.

Hanya dengan melihat bahagian pelanggan dari contoh kami, kami akan membuang lajur untuk Nama Pelanggan dan Alamat Pelanggan dan memasukkannya ke dalam jadual baru. Dalam gambar di atas, saya juga telah menguraikan perkara dengan lebih baik untuk akses data yang lebih terperinci. Jadual baru juga mengandungi lajur untuk Kunci Utama (ClientID) - nombor yang akan digunakan untuk mengakses setiap baris dalam jadual ini.

Dalam jadual asal di mana kami membuang data ini, kami akan menambahkan lajur untuk Foreign Key (ClientID) yang merupakan pautan ke baris yang betul yang mengandungi maklumat untuk pelanggan tertentu ini.

Sekarang, apabila Joan Smith menukar namanya menjadi Joan Baines, perubahan itu hanya perlu dilakukan sekali dalam jadual Pelanggan. Setiap rujukan lain dari jadual bergabung akan menarik nama pelanggan yang betul dan laporan yang melihat apa yang telah dibeli oleh Joan selama 5 tahun terakhir akan mendapat semua pesanan di bawah nama gadis dan perempuannya tanpa perlu mengubah cara laporan dihasilkan .

Sebagai faedah tambahan, ini juga mengurangkan jumlah keseluruhan simpanan yang digunakan.

Jenis Gabungan

SQL menentukan lima jenis gabungan: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, dan CROSS. Kata kunci OUTER adalah pilihan dalam penyataan SQL.

Microsoft Access membenarkan penggunaan INNER (default), LEFT OUTER, RIGHT OUTER, dan CROSS. FULL OUTER tidak disokong seperti itu, tetapi dengan menggunakan LEFT OUTER, UNION ALL, dan RIGHT OUTER, ia dapat dipalsukan dengan kos lebih banyak kitaran CPU dan operasi I / O.

Keluaran gabungan CROSS mengandungi setiap baris meja kiri yang dipasangkan dengan setiap baris meja kanan. Satu-satunya masa saya melihat penyertaan CROSS digunakan adalah semasa ujian beban pelayan pangkalan data.

Mari kita lihat bagaimana cara asas bergabung berfungsi, maka kita akan mengubahnya agar sesuai dengan keperluan kita.

Mari mulakan dengan membuat dua jadual, ProdA dan ProdB, dengan sifat reka bentuk berikut.

Nombor Auto adalah bilangan bulat panjang secara automatik yang ditugaskan untuk entri semasa ia ditambahkan ke jadual. Pilihan Teks tidak diubah, jadi ia akan menerima rentetan teks dengan panjang hingga 255 aksara.

Sekarang, isi dengan beberapa data.

Untuk menunjukkan perbezaan cara kerja 3 jenis gabungan, saya telah memadam entri 1, 5 dan 8 dari ProdA.

Seterusnya, buat pertanyaan baru dengan pergi ke Buat> Reka Bentuk Pertanyaan . Pilih kedua-dua jadual dari dialog Tunjukkan Jadual dan klik Tambah , kemudian Tutup .

Klik pada ProductID dalam jadual ProdA, seret ke ProductID dalam jadual ProdB dan lepaskan butang tetikus untuk mewujudkan hubungan antara jadual.

Klik kanan pada baris antara jadual yang mewakili hubungan antara item dan pilih Join Properties .

Secara lalai, jenis join 1 (INNER) dipilih. Pilihan 2 adalah gabungan LEFT OUTER dan 3 adalah gabungan RIGHT OUTER.

Kami akan melihat penyertaan INNER terlebih dahulu, jadi klik OK untuk menutup dialog.

Dalam pereka pertanyaan, pilih bidang yang ingin kita lihat dari senarai juntai bawah.

Semasa kita menjalankan pertanyaan (tanda seru merah di pita), ia akan menunjukkan medan ProductName dari kedua-dua jadual dengan nilai dari jadual ProdA di lajur pertama dan ProdB di kedua.

Perhatikan hasilnya hanya menunjukkan nilai di mana ProductID sama di kedua-dua jadual. Walaupun terdapat entri untuk ProductID = 1 dalam tabel ProdB, ia tidak muncul dalam hasil kerana ProductID = 1 tidak ada dalam jadual ProdA. Perkara yang sama berlaku untuk ProductID = 11. Ia ada di jadual ProdA tetapi tidak di ProdB jadual.

Dengan menggunakan butang Lihat pada pita dan beralih ke SQL View, anda dapat melihat pertanyaan SQL yang dihasilkan oleh pereka yang digunakan untuk mendapatkan hasil ini.

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

Kembali ke Design View, ubah jenis bergabung menjadi 2 (LEFT OUTER). Jalankan pertanyaan untuk melihat hasilnya.

Seperti yang anda lihat, setiap entri dalam jadual ProdA ditunjukkan dalam hasil sementara hanya entri dalam ProdB yang mempunyai entri ProductID yang sepadan dalam jadual ProdB yang muncul dalam hasil.

Ruang kosong di lajur ProdB.ProductName adalah nilai khas (NULL) kerana tidak ada nilai yang sepadan dalam jadual ProdB. Ini akan terbukti penting kemudian.

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

Cuba perkara yang sama dengan jenis gabungan ketiga (KANAN KANAN).

Hasilnya menunjukkan semuanya dari jadual ProdB sementara menunjukkan nilai kosong (dikenali sebagai NULL) di mana jadual ProdA tidak mempunyai nilai yang sepadan. Setakat ini, ini mendekatkan kita dengan hasil yang dikehendaki dalam soalan pembaca kita.

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

Menggunakan Fungsi dalam Pertanyaan

Hasil fungsi juga dapat dikembalikan sebagai bagian dari pertanyaan. Kami mahu lajur baru bernama 'Hasil' muncul dalam kumpulan hasil kami. Nilainya akan menjadi kandungan lajur ProductName dari tabel ProdA jika ProdA mempunyai nilai (bukan NULL), jika tidak, ia harus diambil dari jadual ProdB.

Fungsi IF Segera (IIF) dapat digunakan untuk menghasilkan hasil ini. Fungsi ini mengambil tiga parameter. Yang pertama adalah keadaan yang mesti dinilai ke nilai Betul atau Salah. Parameter kedua adalah nilai yang akan dikembalikan jika kondisinya Benar, dan parameter ketiga adalah nilai yang akan dikembalikan jika keadaannya Salah.

Pembinaan fungsi penuh untuk keadaan kita kelihatan seperti ini:

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

Perhatikan bahawa parameter keadaan tidak memeriksa kesamaan. Nilai Null dalam pangkalan data tidak mempunyai nilai yang dapat dibandingkan dengan nilai lain, termasuk Null yang lain. Dengan kata lain, Null tidak sama dengan Null. Pernah. Untuk mengatasi ini, kami sebaliknya memeriksa nilai menggunakan kata kunci 'Is'.

Kita juga boleh menggunakan 'Is Not Null' dan mengubah susunan parameter True and False untuk mendapatkan hasil yang sama.

Semasa memasukkan ini ke dalam Query Designer, anda mesti memasukkan keseluruhan fungsi ke dalam bidang: entri. Untuk mendapatkannya membuat lajur 'Hasil', anda perlu menggunakan alias. Untuk melakukan ini, awalkan fungsi dengan 'Hasil:' seperti yang dilihat pada tangkapan skrin berikut.

Kod SQL yang setara untuk melakukan ini adalah:

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;

Sekarang, apabila kita menjalankan pertanyaan ini, ia akan menghasilkan hasil ini.

nama fail terlalu panjang untuk dipadamkan

Di sini kita melihat setiap entri di mana jadual ProdA mempunyai nilai, nilai itu ditunjukkan dalam lajur Hasil. Sekiranya tidak ada entri dalam jadual ProdA, entri dari ProdB muncul dalam Hasil yang betul-betul diminta oleh pembaca kami.

Untuk lebih banyak sumber untuk belajar Microsoft Access, lihat Joel Lee Cara Belajar Akses Microsoft: 5 Sumber Dalam Talian Percuma.

Berkongsi Berkongsi Tweet E-mel Adakah Perlu Ditingkatkan ke Windows 11?

Windows telah direka bentuk semula. Tetapi apakah itu cukup untuk meyakinkan anda untuk beralih dari Windows 10 ke Windows 11?

Baca Seterusnya
Topik-topik yang berkaitan
  • Produktiviti
  • Tanya Pakar
Mengenai Pengarang Bruce Epper(13 Artikel Diterbitkan)

Bruce telah bermain dengan elektronik sejak tahun 70-an, komputer sejak awal 80-an, dan dengan tepat menjawab soalan mengenai teknologi yang tidak pernah dia gunakan dan tidak lihat sepanjang masa. Dia juga menjengkelkan dirinya dengan cuba bermain gitar.

Lebih Banyak Dari Bruce Epper

Langgan buletin kami

Sertailah buletin kami untuk mendapatkan petua, ulasan, ebook percuma, dan tawaran eksklusif!

Klik di sini untuk melanggan