• Terjemahan
• Tutorial

Adakah anda memerlukan “SELECT * WHERE a=b FROM c” atau “SELECT WHERE a=b FROM c ON *”?

Jika anda seperti saya, anda akan bersetuju: SQL ialah salah satu perkara yang kelihatan mudah pada mulanya (baca seperti bahasa Inggeris!), tetapi entah bagaimana anda perlu Google setiap pertanyaan mudah untuk mencari sintaks yang betul.

Dan kemudian bergabung, pengagregatan, subkueri bermula, dan ia ternyata benar-benar sampah. Sesuatu seperti ini:

PILIH ahli.nama pertama || " " || ahli.nama keluarga SEBAGAI "Nama Penuh" DARI peminjaman INNER JOIN ahli PADA ahli.memberid=borrowings.memberid DALAM JOIN buku PADA buku.bookid=borrowings.bookid WHERE peminjaman.bookid IN (PILIH bookid DARI buku WHERE stok>(PILIH purata(stok) ) DARI buku)) KUMPULAN OLEH ahli.nama pertama, ahli.nama keluarga;

Bue! Ini akan menakutkan mana-mana pemula, atau bahkan pembangun peringkat pertengahan jika dia melihat SQL buat kali pertama. Tetapi ia tidak begitu buruk.

Sangat mudah untuk mengingati apa yang intuitif, dan dengan panduan ini saya berharap dapat mengurangkan halangan untuk masuk ke dalam SQL untuk pemula dan menawarkan mereka yang sudah berpengalaman cara baharu untuk melihat SQL.

Walaupun sintaks SQL hampir sama antara pangkalan data yang berbeza, artikel ini menggunakan PostgreSQL untuk pertanyaan. Beberapa contoh akan berfungsi dalam MySQL dan pangkalan data lain.

1. Tiga perkataan ajaib

Terdapat banyak kata kunci dalam SQL, tetapi SELECT, FROM, dan WHERE terdapat dalam hampir setiap pertanyaan. Tidak lama kemudian anda akan faham bahawa ketiga-tiga perkataan ini mewakili aspek paling asas dalam membina pertanyaan pangkalan data, dan pertanyaan lain yang lebih kompleks hanyalah tambahan di atasnya.

2. Pangkalan kami

Mari kita lihat pangkalan data yang akan kami gunakan sebagai contoh dalam artikel ini:

Kami mempunyai perpustakaan buku dan orang. Terdapat juga meja khas untuk merekod buku yang dikeluarkan.

• Jadual "buku" menyimpan maklumat tentang tajuk, pengarang, tarikh penerbitan dan ketersediaan buku. Mudah sahaja.
• Dalam jadual "ahli" - nama pertama dan nama keluarga semua orang yang mendaftar untuk perpustakaan.
• Jadual "pinjaman" menyimpan maklumat tentang buku yang diambil dari perpustakaan. Lajur bookid merujuk kepada ID buku yang diambil dalam jadual "buku", dan lajur memberid merujuk kepada orang yang sepadan daripada jadual "ahli". Kami juga mempunyai tarikh terbitan dan tarikh buku itu mesti dipulangkan.

3. Permintaan mudah

Mari kita mulakan dengan permintaan mudah: kita perlukan nama Dan pengecam(id) semua buku yang ditulis oleh pengarang "Dan Brown"

Permintaan akan seperti ini:

PILIH bookid SEBAGAI "id", tajuk DARI buku WHERE author="Dan Brown";

Dan hasilnya adalah seperti ini:

ID	tajuk
2	Simbol yang Hilang
4	Inferno

Senang sahaja. Mari lihat permintaan untuk memahami apa yang berlaku.

3.1 DARI - dari mana kami mendapat data

Ini mungkin kelihatan jelas sekarang, tetapi FROM akan menjadi sangat penting kemudian apabila kita dapat menyertai dan membuat pertanyaan.

DARI mata ke jadual untuk pertanyaan. Ini boleh menjadi jadual sedia ada (seperti dalam contoh di atas), atau jadual yang dibuat dengan cepat melalui gabungan atau subkueri.

3.2 DI MANA - apakah data yang ditunjukkan

WHERE hanya berkelakuan seperti penapis garisan, yang ingin kami keluarkan. Dalam kes kami, kami hanya mahu melihat baris dengan nilai dalam lajur pengarang ialah "Dan Brown".

3.3 PILIH - cara memaparkan data

Memandangkan kita mempunyai semua lajur yang kita perlukan daripada jadual yang kita perlukan, kita perlu memutuskan cara tepat untuk memaparkan data ini. Dalam kes kami, kami hanya memerlukan tajuk buku dan ID, jadi itulah yang kami lakukan. jom pilih menggunakan SELECT. Pada masa yang sama, anda boleh menamakan semula lajur menggunakan AS.

Keseluruhan pertanyaan boleh digambarkan menggunakan gambar rajah mudah:

4. Sambungan (bergabung)

Sekarang kami ingin melihat tajuk (tidak semestinya unik) semua buku Dan Brown yang telah didaftar keluar dari perpustakaan, dan apabila buku tersebut perlu dipulangkan:

PILIH buku.tajuk SEBAGAI "Tajuk", peminjaman.tarikh pemulangan SEBAGAI "Tarikh Pemulangan" DARIPADA peminjaman SERTAI buku ON borrowings.bookid=books.bookid WHERE books.author="Dan Brown";

Keputusan:

Tajuk	Tarikh Kembali
Simbol yang Hilang	2016-03-23 00:00:00
Inferno	2016-04-13 00:00:00
Simbol yang Hilang	2016-04-19 00:00:00

Untuk sebahagian besar permintaan adalah serupa dengan yang sebelumnya dengan pengecualian DARI bahagian. Maksudnya begitu kami sedang menanyakan data dari jadual lain. Kami tidak mengakses sama ada jadual "buku" atau jadual "pinjaman". Sebaliknya kita beralih kepada meja baru, yang telah dibuat dengan menyertai kedua-dua jadual ini.

borrowings JOIN books ON borrowings.bookid=books.bookid - pertimbangkan ini sebagai jadual baharu yang telah dibentuk dengan menggabungkan semua rekod daripada jadual "buku" dan "pinjaman" di mana nilai bookid adalah sama. Hasil daripada penggabungan sedemikian akan menjadi:

Dan kemudian kami menanyakan jadual ini dengan cara yang sama seperti dalam contoh di atas. Ini bermakna apabila menyertai jadual, anda hanya perlu risau tentang cara membuat gabungan. Dan kemudian permintaan menjadi jelas seperti dalam kes "permintaan mudah" dari titik 3.

Mari cuba gabungan yang lebih kompleks dengan dua jadual.

Sekarang kami ingin mendapatkan nama pertama dan nama keluarga yang mengambil buku dari perpustakaan oleh pengarang "Dan Brown".

Kali ini mari kita pergi dari bawah ke atas:

Langkah Langkah 1- di manakah kita boleh mendapatkan data? Untuk mendapatkan hasil yang kami perlukan, kami perlu menyertai jadual "ahli" dan "buku" dengan jadual "pinjaman". Bahagian JOIN akan kelihatan seperti ini:

peminjaman SERTAI buku PADA peminjaman.bookid=books.bookid SERTAI ahli PADA ahli.memberid=peminjaman.memberid

Hasil sambungan boleh dilihat pada pautan.

Langkah 2- data apa yang kami tunjukkan? Kami hanya berminat dengan data di mana pengarang buku itu ialah "Dan Brown"

WHERE books.author="Dan Brown"

Langkah 3- bagaimana kami menunjukkan data? Memandangkan data telah diterima, anda hanya perlu memaparkan nama pertama dan nama akhir mereka yang mengambil buku:

PILIH ahli.nama pertama SEBAGAI "Nama Pertama", ahli.nama keluarga SEBAGAI "Nama Akhir"

Super! Apa yang tinggal ialah menggabungkan tiga komponen dan membuat permintaan yang kami perlukan:

PILIH ahli.nama pertama SEBAGAI "Nama Pertama", ahli.nama keluarga SEBAGAI "Nama Akhir" DARI peminjaman SERTAI buku PADA peminjaman.bookid=books.bookid SERTAI ahli PADA ahli.memberid=pinjaman.memberid WHERE books.author="Dan Brown";

Apa yang akan memberi kita:

Nama pertama	Nama terakhir
Mike	Willis
Ellen	Horton
Ellen	Horton

Hebat! Tetapi nama itu diulang (mereka tidak unik). Kami akan membetulkannya tidak lama lagi.

5. Pengagregatan

Secara kasarnya, Pengagregatan diperlukan untuk menukar berbilang baris menjadi satu. Pada masa yang sama, semasa pengagregatan, logik yang berbeza digunakan untuk lajur yang berbeza.

Mari kita teruskan dengan contoh kami di mana nama pendua muncul. Jelas sekali bahawa Ellen Horton mengambil lebih daripada satu buku, tetapi ini bukan cara terbaik untuk menunjukkan maklumat ini. Anda boleh membuat permintaan lain:

PILIH ahli.nama pertama SEBAGAI "Nama Pertama", ahli.nama keluarga SEBAGAI "Nama Akhir", kira(*) SEBAGAI "Bilangan buku yang dipinjam" DARIPADA peminjaman SERTAI buku PADA peminjaman.bookid=books.bookid SERTAI ahli PADA ahli.memberid=pinjaman .memberid WHERE books.author="Dan Brown" GROUP BY members.firstname, members.lastname;

Yang akan memberi kita hasil yang diingini:

Nama pertama	Nama terakhir	Bilangan buku yang dipinjam
Mike	Willis	1
Ellen	Horton	2

Hampir semua pengagregatan disertakan dengan klausa GROUP BY. Perkara ini menukar jadual yang boleh diambil melalui pertanyaan kepada kumpulan jadual. Setiap kumpulan sepadan dengan nilai unik (atau kumpulan nilai) lajur yang kami tentukan dalam GROUP BY . Dalam contoh kami, kami menukar hasil daripada latihan sebelumnya kepada sekumpulan baris. Kami juga melakukan pengagregatan dengan count , yang menukarkan berbilang baris kepada nilai integer (dalam kes kami, bilangan baris). Makna ini kemudiannya diberikan kepada setiap kumpulan.

Setiap baris dalam hasil mewakili hasil pengagregatan setiap kumpulan.

Seseorang boleh membuat kesimpulan logik bahawa semua medan dalam hasil harus dinyatakan dalam GROUP BY , atau pengagregatan harus dilakukan padanya. Kerana semua medan lain boleh berbeza antara satu sama lain dalam baris yang berbeza, dan jika anda memilihnya dengan SELECT, tidak jelas yang mana nilai yang mungkin perlu diambil.

Dalam contoh di atas, fungsi kiraan memproses semua baris (memandangkan kami mengira bilangan baris). Fungsi lain seperti jumlah atau proses maks hanya baris yang ditentukan. Sebagai contoh, jika kita ingin mengetahui bilangan buku yang ditulis oleh setiap pengarang, maka kita memerlukan pertanyaan berikut:

PILIH pengarang, jumlah(stok) DARI buku KUMPULAN OLEH pengarang;

Keputusan:

pengarang	jumlah
Robin Sharma	4
Dan Brown	6
John Green	3
Amish Tripathi	2

Di sini fungsi jumlah hanya memproses lajur stok dan mengira jumlah semua nilai dalam setiap kumpulan.

6. Subkueri

Subkueri ialah pertanyaan SQL biasa yang dibenamkan dalam pertanyaan yang lebih besar. Mereka dibahagikan kepada tiga jenis berdasarkan jenis hasil yang dikembalikan.

6.1 Jadual dua dimensi

Terdapat pertanyaan yang mengembalikan berbilang lajur. Contoh yang baik ialah pertanyaan daripada latihan pengagregatan sebelumnya. Sebagai subkueri, ia hanya akan mengembalikan jadual lain yang mana pertanyaan baharu boleh dibuat. Meneruskan latihan sebelumnya, jika kita ingin mengetahui jumlah buku yang ditulis oleh pengarang "Robin Sharma", maka satu cara yang mungkin adalah dengan menggunakan subqueries:

PILIH * DARI (PILIH pengarang, jumlah(stok) DARI buku KUMPULAN OLEH pengarang) SEBAGAI hasil WHERE pengarang="Robin Sharma";

Keputusan:

Boleh ditulis sebagai: ["Robin Sharma", "Dan Brown"]

2. Sekarang kita menggunakan hasil ini dalam pertanyaan baharu:

PILIH tajuk, tempah DARI buku DI MANA pengarang DALAM (PILIH pengarang DARI (PILIH pengarang, jumlah(saham) DARI buku KUMPULAN OLEH pengarang) SEBAGAI keputusan WHERE jumlah > 3);

Keputusan:

tajuk	tempah
Simbol yang Hilang	2
Siapa yang Akan Menangis Apabila Anda Mati?	3
Inferno	4

Ini adalah sama seperti:

PILIH tajuk, ditempah DARI buku WHERE pengarang IN ("Robin Sharma", "Dan Brown");

6.3 Nilai individu

Terdapat pertanyaan yang menghasilkan hanya satu baris dan satu lajur. Ia boleh dianggap sebagai nilai malar dan boleh digunakan di mana-mana sahaja nilai digunakan, seperti dalam operator perbandingan. Ia juga boleh digunakan sebagai jadual dua dimensi atau tatasusunan elemen tunggal.

Mari, sebagai contoh, dapatkan maklumat tentang semua buku yang bilangannya dalam perpustakaan melebihi purata semasa.

Purata boleh diperolehi dengan cara ini:

pilih purata(stok) daripada buku;

Apa yang memberi kita:

7. Operasi tulis

Kebanyakan operasi tulis pangkalan data agak mudah berbanding dengan operasi baca yang lebih kompleks.

7.1 Kemas kini

Sintaks permintaan KEMASKINI secara semantik sama dengan permintaan baca. Satu-satunya perbezaan ialah daripada memilih lajur dengan SELECT, kami menetapkan pengetahuan dengan SET.

Jika semua buku Dan Brown hilang, maka anda perlu menetapkan semula nilai kuantiti. Pertanyaan untuk ini ialah:

KEMASKINI buku SET stok=0 WHERE author="Dan Brown";

WHERE melakukan perkara yang sama seperti sebelumnya: memilih baris. Daripada SELECT yang kami gunakan semasa membaca, kami kini menggunakan SET. Walau bagaimanapun, kini anda perlu menentukan bukan sahaja nama lajur, tetapi juga nilai baharu untuk lajur ini dalam baris yang dipilih.

7.2 Padam

Pertanyaan PADAM hanyalah pertanyaan PILIH atau KEMASKINI tanpa nama lajur. Serius. Seperti SELECT dan UPDATE , blok WHERE kekal sama: ia memilih baris untuk dipadamkan. Operasi pemadaman memusnahkan keseluruhan baris, jadi tidak masuk akal untuk menentukan lajur individu. Jadi, jika kami memutuskan untuk tidak menetapkan semula bilangan buku Dan Brown, tetapi untuk memadamkan semua rekod sama sekali, maka kami boleh membuat permintaan berikut:

PADAM DARI buku WHERE author="Dan Brown";

7.3 Masukkan

Mungkin satu-satunya perkara yang berbeza daripada jenis pertanyaan lain ialah INSERT. Formatnya ialah:

MASUKKAN KE DALAM x (a,b,c) NILAI (x, y, z);

Di mana a , b , c ialah nama lajur, dan x , y dan z ialah nilai yang akan dimasukkan ke dalam lajur tersebut, dalam susunan yang sama. Itu pada asasnya.

Mari lihat contoh khusus. Berikut ialah pertanyaan INSERT yang mengisi keseluruhan jadual "buku":

MASUKKAN KE DALAM buku (penerbit buku, tajuk, pengarang, diterbitkan, stok) NILAI (1,"Scion of Ikshvaku","Amish Tripathi","06-22-2015",2), (2,"The Lost Symbol"," Dan Brown","07-22-2010",3), (3,"Siapa yang Akan Menangis Apabila Anda Mati?","Robin Sharma","06-15-2006",4), (4,"Inferno" , "Dan Brown","05-05-2014",3), (5,"The Fault in our Stars", "John Green", "01-03-2015",3);

8. Semak

Kami telah sampai ke penghujungnya, saya mencadangkan ujian kecil. Lihat permintaan itu di awal artikel. Bolehkah anda memikirkannya? Cuba pecahkannya kepada bahagian SELECT , FROM , WHERE , GROUP BY, dan lihat komponen individu subquery.

Ini adalah dalam bentuk yang lebih mudah dibaca:

PILIH ahli.nama pertama || " " || ahli.nama keluarga SEBAGAI "Nama Penuh" DARI peminjaman INNER JOIN ahli PADA ahli.memberid=borrowings.memberid DALAM JOIN buku PADA buku.bookid=borrowings.bookid WHERE peminjaman.bookid IN (PILIH bookid DARI buku WHERE stok> (PILIH purata(stok) ) DARI buku)) KUMPULAN OLEH ahli.nama pertama, ahli.nama keluarga;

Pertanyaan ini mengembalikan senarai orang yang telah menyemak buku dari perpustakaan yang jumlah bilangannya melebihi purata.

Keputusan:

Nama penuh
Lida Tyler

Saya harap anda dapat memikirkannya tanpa sebarang masalah. Tetapi jika tidak, saya mengalu-alukan komen dan maklum balas anda supaya saya boleh menambah baik siaran ini.

Tag: Tambah tag

Subquery merujuk kepada objek khas dalam program 1C. Fungsinya digunakan jika perlu untuk menjana dan melaksanakan jenis pertanyaan khas kepada jadual pangkalan data dalam program. Untuk mendapatkan hasil daripada permintaan, anda perlu mengarang teks permintaan dengan betul, yang akan mengumpulkan maklumat terkini tentang sumber untuk mendapatkan data. Ini boleh menjadi jadual, medan khas, kumpulan unik.

Bahasa pertanyaan moden untuk program 1C

Pertanyaan Bersarang ditulis menggunakan bahasa yang mempunyai ciri tersendiri dan pada masa yang sama serupa dengan bahasa SQL khas yang lain, contohnya, dalam sintaksnya. Ciri khas utama bahasa pertanyaan dalam 1C ialah:

• Menyahrujuk medan rujukan;
• Kemungkinan permintaan atipikal;
• Pesanan mengikut jenis primitif;
• Menggunakan binaan "Lihat";
• Persampelan data mengikut templat;
• Perkongsian kumpulan dan jumlah;
• Pembinaan dengan keadaan "Di mana".

Terdapat ciri tersendiri lain bahasa pertanyaan 1C yang membolehkannya berkesan.

Apa yang anda perlu tahu tentang pertanyaan bersarang dan bahasa pertanyaan?

Kajian profesional tentang reka bentuk bahasa pertanyaan membolehkan anda bukan sahaja memahami tujuannya bahasa pertanyaan, tetapi juga belajar untuk mewakili satu set objek pangkalan data dalam format jadual 2 dimensi, memproses konsol pertanyaan, mencipta fail untuk menyimpan senarai pertanyaan dan berkenalan dengan medan tertentu jadual yang mempunyai jenis rujukan. Dan semua ini hanya akan menjadi pengetahuan asas tentang sistem pertanyaan bersarang dalam 1C. Adalah penting untuk menguasai operasi kumpulan yang berkaitan dengan bahasa pertanyaan dan belajar cara membuat pilihan yang betul daripada berbilang sumber data.

Contoh pertanyaan bersarang

Mari kita pertimbangkan salah satu pilihan untuk pertanyaan bersarang berdasarkan gabungan pertanyaan. Katakan terdapat dokumen mengenai resit dan perbelanjaan, dengan entiti undang-undang yang sama bertindak sebagai kedua-dua penjual dan pembeli. Kita perlu mengetahui jumlah hutang rakan niaga. Untuk melakukan ini, kami menggunakan pembinaan "SATUKAN SEMUA" yang berkesan.

Jadual "ARRIAGE"

Jadual "CONSUMPTION"

Pertama, kami menentukan semua perbelanjaan, kemudian pendapatan oleh entiti undang-undang. Kami meletakkan pertanyaan kedua dengan tanda "-", ini akan membolehkan anda meruntuhkan data dengan betul.

Hasil tindakan:

Tetapi kita perlu mendapatkan hasil yang runtuh; pengumpulan mengikut entiti undang-undang diperlukan.

Maka hasil laporan tersebut ialah:

Apabila bercakap tentang jadual Perbelanjaan dan Pendapatan, sebagai contoh, mengikut direktori Tatanama, adalah perlu untuk menghapuskan pertindihan. Tetapi anda tidak akan dapat menggabungkan setiap permintaan secara berasingan. Oleh itu anda perlu melakukan ini:

Dalam kes ini, pertanyaan diletakkan di antara kurungan, ia dipanggil pertanyaan bersarang. Ini membolehkan kami bukan sahaja mengumpulkan rekod yang berkaitan dengan kami, tetapi juga untuk mengecualikan elemen serupa daripada dua subkueri yang digunakan.

Terdapat keperluan tertentu yang mesti dipatuhi apabila menggabungkan dua pertanyaan. Pertama sekali, ini melibatkan bilangan medan. Adalah perlu bahawa bilangan mereka adalah sama. Sebagai contoh, jika Diskaun ditunjukkan dalam jadual data "Perbelanjaan", tetapi tiada unsur sedemikian dalam data Resit, maka anda harus menggunakan pembinaan berikut:

Pada masa hadapan, anda perlu melaraskan jumlah diskaun dan melakukan pengelompokan yang diperlukan.

Syarat kedua yang amat diperlukan ialah ketertiban. Hakikatnya ialah prosedur untuk menggabungkan medan berlaku mengikut perintah mereka, iaitu, cara mereka ditetapkan dalam setiap bahagian berurutan. Untuk menukar susunan, kami menggunakan binaan berikut:

Dengan cara ini, apabila menggunakan binaan Kesatuan, ia harus dibezakan daripada konsep menggabungkan beberapa pertanyaan. Penggabungan memungkinkan untuk menyambung secara menegak hasil sampel; data diambil satu demi satu daripada yang pertama, dan kemudian beralih ke yang kedua. Ini adalah bagaimana data diperoleh hasil daripada reka bentuk menggabungkan maklumat daripada dua jadual.

SQL membolehkan anda menyusun pertanyaan antara satu sama lain. Biasanya subkueri mengembalikan nilai tunggal, yang diperiksa untuk melihat sama ada predikat itu benar.

Jenis istilah carian:
. Perbandingan dengan hasil subkueri (=, >=)
. Menyemak kepunyaan hasil subkueri (IN)
. Semakan kewujudan (EXISTS)
. Perbandingan berbilang (kuantitatif) ( MANA-MANA, SEMUA)

Nota tentang pertanyaan bersarang:
. Subkueri mesti memilih hanya satu lajur (kecuali subkueri dengan predikat EXISTS), dan jenis data hasilnya mesti sepadan dengan jenis data nilai yang ditentukan dalam predikat.
. Dalam sesetengah kes, anda boleh menggunakan kata kunci DISTINCT untuk memastikan nilai tunggal dikembalikan.
. Anda tidak boleh memasukkan klausa ORDER BY atau UNION dalam subkueri.
. Subquery boleh terletak sama ada di sebelah kiri atau di sebelah kanan keadaan carian.
. Subkueri boleh menggunakan fungsi pengagregatan tanpa klausa GROUP BY, yang secara automatik mengembalikan nilai khas untuk sebarang bilangan baris, predikat IN khas dan ungkapan berasaskan lajur.
. Apabila boleh, anda harus menggunakan gabungan jadual JOIN dan bukannya subkueri.

Contoh untuk pertanyaan bersarang:

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
PILIH * DARI Pesanan WHERE SNum=(PILIH DISTINCT SNum DARI Pesanan WHERE CNum=2001)
PILIH * DARI Pesanan WHERE Amt>(PILIH AVG(AMT) DARI Pesanan WHERE Odate=10/04/1990)
PILIH * DARI Pelanggan WHERE CNum=(PILIH SNum+1000 DARI SalesPeople WHERE SName=’Serres’)

2) Subkueri berkaitan

Dalam SQL, anda boleh membuat subkueri yang merujuk jadual daripada pertanyaan luar. Dalam kes ini, subkueri dilaksanakan beberapa kali, sekali untuk setiap baris jadual daripada pertanyaan luar. Oleh itu, adalah penting bahawa subquery menggunakan indeks. Subkueri boleh mengakses jadual yang sama dengan jadual luar. Jika pertanyaan luar mengembalikan bilangan baris yang agak kecil, maka subkueri yang dipautkan akan lebih cepat daripada yang dinyahpautkan. Jika subkueri mengembalikan sebilangan kecil baris, pertanyaan berkaitan akan menjadi lebih perlahan daripada pertanyaan yang tidak berkaitan.

Contoh untuk subkueri berkaitan:

SELECT * FROM SalesPeople Utama WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //mengembalikan semua pesanan yang nilainya melebihi nilai pesanan purata untuk pelanggan tertentu

3) Predikat WUJUD

Bentuk sintaksis: WUJUD ()

Predikat mengambil subquery sebagai hujah dan menilai kepada benar jika subquery mempunyai output dan false sebaliknya. Subkueri dilaksanakan sekali dan boleh mengandungi beberapa lajur, kerana nilainya tidak disemak, tetapi hasil kehadiran baris hanya direkodkan.

Nota mengenai predikat EXISTS:
. EXISTS ialah predikat yang mengembalikan BENAR atau SALAH dan boleh digunakan bersendirian atau dengan ungkapan Boolean yang lain.
. EXISTS tidak boleh menggunakan fungsi pengagregatan dalam subkuerinya.
. Dalam subkueri berkorelasi, predikat EXISTS dilaksanakan untuk setiap baris jadual luar.
. Anda boleh menggabungkan predikat EXISTS dengan cantuman jadual.

Contoh untuk predikat EXISTS:

SELECT * FROM Customer WHERE EXIST(SELECT * FROM Customer WHERE City=’San Jose’) – mengembalikan semua pelanggan jika mana-mana daripada mereka tinggal di San Jose.
PILIH SNum BERBEZA DARIPADA Pelanggan Didahulukan DI MANA TIDAK WUJUD (PILIH * DARI Pelanggan Hantar DI MANA Hantar.SNum=First.SNum DAN Hantar.CNumFirst.CNum) – mengembalikan nombor penjual yang berkhidmat hanya kepada seorang pelanggan.
PILIH DISTINCT F.SNum, SName, F.City DARIPADA Jurujual F, Pelanggan S DI MANA WUJUD (PILIH * DARI Pelanggan T WHERE S.SNum=T.SNum DAN S.CNumT.CNum DAN F.SNum=S.SNum) – mengembalikan nombor, nama dan bandar tempat tinggal semua penjual yang melayani beberapa pelanggan.
PILIH * DARI SalesPeople Frst DI MANA WUJUD (PILIH * DARI Pelanggan Hantar DI MANA Frst.SNum=Send.SNum DAN 1

4) Predikat perbandingan kuantitatif

Bentuk sintaksis: (=|>|=|) MANA-MANA|SEMUA ()

Predikat ini menggunakan subkueri sebagai hujah, walau bagaimanapun, berbanding dengan predikat EXISTS, ia digunakan bersama dengan predikat hubungan (=,>=). Dalam pengertian ini, mereka serupa dengan predikat IN, tetapi hanya digunakan dengan subkueri. Piawaian membenarkan BEBERAPA kata kunci digunakan dan bukannya SEBARANG, tetapi tidak semua DBMS menyokongnya.

Nota tentang predikat perbandingan:
. Predikat SEMUA menilai kepada BENAR jika setiap nilai yang dipilih semasa pelaksanaan subquery memenuhi syarat yang dinyatakan dalam predikat pertanyaan luar. Ia paling kerap digunakan dengan ketidaksamaan.
. Predikat MANA-MANA ​​menilai kepada BENAR jika sekurang-kurangnya satu nilai yang dipilih semasa pelaksanaan subkueri memenuhi syarat yang dinyatakan dalam predikat pertanyaan luar. Ia paling kerap digunakan dengan ketidaksamaan.
. Jika subquery tidak mengembalikan sebarang baris, maka SEMUA secara automatik mengambil nilai TRUE (ia dianggap bahawa syarat perbandingan telah dipenuhi), dan untuk ANY ia mengambil nilai FALSE.
. Jika perbandingan adalah BENAR untuk tiada baris dan terdapat satu atau lebih baris dengan nilai NULL, maka SEBARANG pengembalian UNKNOWN.
. Jika perbandingan adalah FALSE untuk tiada baris dan terdapat satu atau lebih baris dengan nilai NULL, maka SEMUA mengembalikan TIDAK DIKETAHUI.

Contoh untuk predikat perbandingan kuantitatif:

PILIH * DARI SalesPeople WHERE City=ANY(PILIH Bandar DARIPADA Pelanggan)
PILIH * DARI Pesanan WHERE Amt ALL(PILIH Penilaian DARI Pelanggan WHERE City='Rom')

5) Predikat keunikan

UNIK|SELAIN ()

Predikat digunakan untuk menyemak keunikan (ketiadaan pendua) dalam data keluaran subkueri. Selain itu, dalam predikat UNIQUT, rentetan dengan nilai NULL dianggap unik, dan dalam predikat DISTINCT, dua nilai yang tidak ditentukan dianggap sama antara satu sama lain.

6) Predikat padan

PERLAWANAN ()

Predikat MATCH menguji sama ada nilai rentetan pertanyaan akan sepadan dengan nilai sebarang rentetan yang terhasil daripada subkueri. Subkueri ini berbeza daripada predikat IN DAN SEBARANG kerana ia membenarkan pemprosesan padanan "separa" (SEPARUH) yang boleh berlaku di antara baris yang mempunyai beberapa nilai NULL.

7) Pertanyaan dalam bahagian DARI

Malah, adalah sah untuk menggunakan subkueri di mana-mana rujukan jadual dibenarkan.

PILIH CName, Tot_Amt DARI Pelanggan, (PILIH CNum, JUMLAH(Amt) AS Tot_Amt DARI KUMPULAN Pesanan MENGIKUT CNum) DI MANA City=’London’ DAN Pelanggan.CNum=Orders.CNum
//subquery mengembalikan jumlah pesanan yang dibuat oleh setiap pelanggan dari London.

8) Pertanyaan rekursif

DENGAN REKURSIF
S1 SEBAGAI PILIHAN … DARI … DI MANA …
S2 SEBAGAI PILIHAN … DARI … DI MANA …

Selalunya apabila mendapatkan data, adalah perlu untuk menggabungkan maklumat daripada beberapa jadual yang berkaitan. Ini boleh dilakukan menggunakan pertanyaan bersarang, atau menggunakan gabungan menggunakan SQL.

Pertanyaan Bersarang

Untuk contoh kami, katakan bahawa kami perlu mengetahui nama nod yang melawat tapak www.dom2.ru. Maklumat yang diperlukan boleh diperolehi melalui permintaan:

PILIH nama_hst DARI hos DI MANA hst_pcode DALAM (PILIH vis_hstcode DARI lawatan, tapak WHERE (sit_pcode = vis_sitcode) DAN (sit_name SEPERTI "www.dom2.ru"));

Mari kita lihat lebih dekat permintaan ini. Pernyataan SELECT pertama diperlukan untuk memilih nama nod. Untuk memilih nama yang kami perlukan, pertanyaan menentukan bahagian WHERE, di mana kunci utama jadual "Nod" (hst_pcode) disemak untuk keahlian dalam set (operator IN). Nampaknya, set untuk menyemak keahlian harus dikembalikan oleh pernyataan SELECT kedua yang terletak dalam kurungan. Mari kita lihat secara berasingan:

PILIH vis_hstcode DARI lawatan, tapak WHERE (sit_pcode = vis_sitcode) DAN (sit_name SEPERTI "www.dom2.ru")

Untuk kandungan jadual dalam contoh kami, subquery akan mengembalikan set nilai berikut

Menyambung menggunakan SQL

Seperti yang dinyatakan di atas, satu cara untuk mendapatkan semula data daripada berbilang jadual adalah dengan menyertai jadual menggunakan SQL. Tujuan utama gabungan tersebut adalah untuk mencipta hubungan baharu yang akan mengandungi data daripada dua atau lebih hubungan asal.

Gabungan dalaman

Mari lihat contoh:

PILIH hst_name, sit_name, vis_timestamp DARIPADA hos, lawatan, tapak WHERE (hst_pcode = vis_hstcode) DAN (vis_sitcode = sit_pcode)

Pertanyaan ini akan mengembalikan data berikut

hst_name	nama_duduk	vis_timestamp
ws1	www.dom2.ru	2012-08-01 07:59:58.209028
ws1	www.vkontakte.ru	2012-08-01 08:00:10.315083
1-1	www.vkontakte.ru	2012-08-01 08:00:20.025087
1-2	www.opennet.ru	2012-08-01 08:00:26.260159

Dalam contoh ini, daripada tiga jadual (hos, lawatan, tapak), satu medan dipilih dan jadual baharu dibuat, yang akan mengumpulkan nama hos, tapak yang dilawati dan masa lawatan. Pembentangan data yang dicantumkan dikawal oleh syarat dalam klausa WHERE. Anda boleh melihat bahawa terdapat dua keadaan yang menghubungkan tiga jadual. Memandangkan jadual lawatan (lawatan) mengandungi pengecam mereka dan bukannya nama hos dan nama tapak, apabila menyambungkan jadual kami menambah syarat untuk memautkan data dengan pengecam dan kemudian semuanya akan sesuai. Jika atas sebab tertentu, bertentangan dengan integriti rujukan, terdapat rekod dalam jadual lawatan dengan pengecam nod atau tapak yang tidak wujud, ia tidak akan muncul dalam set hasil pertanyaan dalam contoh ini.

Contoh di atas sedikit dipermudahkan dan disebabkan sedikit pemudahan kejelasan hilang. Bentuk pertanyaan yang lebih visual yang menggabungkan beberapa jadual dan mengembalikan set data yang sama akan kelihatan seperti

PILIH hst_name, sit_name, vis_timestamp DARI hos SERTAI lawatan HIDUP (hst_pcode = vis_hstcode) SERTAI tapak HIDUP (vis_sitcode = sit_pcode);

Permintaan mengandungi dua operator JOIN… ON. Memandangkan "Sertai" boleh diterjemahkan sebagai "sambungan" atau "kesatuan", contoh ini lebih fasih. Jika anda cuba menterjemah teks pertanyaan SQL ke dalam bahasa Rusia, anda akan mendapat sesuatu seperti itu

PILIH (medan) hst_name, sit_name, vis_timestamp FROM (jadual) hos MENYERTAI (dengan jadual) lawatan OLEH (syarat) (hst_pcode = vis_hstcode) MENYERTAI (dengan jadual) tapak BY (syarat) (vis_sitcode = sit_pcode);

Perkataan Rusia dalam kurungan telah ditambahkan untuk memudahkan pertanyaan difahami. Anda boleh menggunakan mana-mana kaedah di atas untuk menulis pertanyaan.

Sambungan luar

Kaedah yang digunakan di atas untuk menyertai jadual dipanggil gabungan dalaman(gabungan dalaman). Kaedah sambungan ini mempunyai kelemahan. Contohnya, jika kami tidak mempunyai lawatan ke salah satu tapak, atau salah satu nod tidak membuat satu lawatan, maka tapak atau nod itu tidak akan hadir dalam set data yang terhasil. Dalam contoh di atas, anda dapat melihat bahawa tapak www.yandex.ru hilang daripada data, serta nod 1-3. Kadang-kadang ini tidak diingini dan dalam kes sedemikian mereka menggunakan gabungan luar(sambungan luar). Sambungan luar boleh dibiarkan(kiri sertai) dan betul(kanan sertai). Bahagian gabungan (kiri atau kanan) sepadan dengan jadual dari mana keseluruhan data akan dipilih. Oleh itu, apabila menggunakan LEFT JOIN, data dari jadual di sebelah kiri operator JOIN akan dipilih secara keseluruhannya. Mari sandarkan ini dengan contoh. Katakan kita perlu memilih SEMUA nod dan lawatan yang dikaitkan dengannya. Ini boleh dilakukan dengan permintaan

PILIH hst_name, vis_timestamp DARI hos TINGGALKAN SERTAI lawatan HIDUP (hst_pcode = vis_hstcode);

Beri perhatian kepada data yang akan dikembalikan sebagai tindak balas kepada permintaan

hst_name	vis_timestamp
ws1	2012-08-01 07:59:58.209028
ws1	2012-08-01 08:00:10.315083
1-1	2012-08-01 08:00:20.025087
1-2	2012-08-01 08:00:26.260159
1-3

Ia boleh dilihat bahawa nod 1-3 tidak sepadan dengan satu lawatan, tetapi ia masih dalam senarai. RIGHT JOIN berfungsi dengan cara yang sama. Pertanyaan yang akan mengembalikan set data yang sama boleh ditulis menggunakan RIGHT JOIN:

PILIH hst_name, vis_timestamp DARI lawatan KANAN SERTAI hos HIDUP (hst_pcode = vis_hstcode);

Dalam kes ini, anda perlu menukar LEFT JOIN kepada RIGHT JOIN dan menukar jadual lawatan dan hos dalam pertanyaan.

Menggunakan UNION

Kadangkala anda perlu mendapatkan dua senarai rekod daripada jadual sebagai satu. Untuk tujuan ini, kata kunci UNION boleh digunakan untuk menggabungkan set hasil dua pertanyaan ke dalam satu set data. Katakan kita perlu mendapatkan senarai yang mengandungi nod rangkaian dan nama tapak. Jadual adalah berbeza, dan oleh itu pertanyaan akan berbeza. Bagaimana untuk menggabungkan semuanya ke dalam satu set data? Mudah, tetapi terdapat keperluan tertentu untuk "melekatkan" pertanyaan sedemikian:

§ permintaan mesti mengandungi bilangan medan yang sama;

§ Jenis data medan pertanyaan yang digabungkan juga mesti sepadan.

Jika tidak, menggunakan UNION tidak sukar. Sebagai contoh, untuk mendapatkan senarai nama hos dan nama tapak sebagai satu set data, kami akan menjalankan pertanyaan berikut:

PILIH nama_hst SEBAGAI nama DARI hos UNIONSELECT sit_name SEBAGAI nama DARI tapak;

Pendekatan ini boleh menyebabkan masalah dengan menyusun rekod. Untuk memastikan bahawa senarai tapak datang selepas senarai nod, anda boleh menambah medan integer dengan sengaja untuk menunjukkan nombor yang akan mengambil bahagian dalam pengisihan. Sebagai contoh

PILIH 1 SEBAGAI tahap, hst_name SEBAGAI nama DARIPADA hos UNIONSELECT 2 AS tahap, sit_name SEBAGAI nama DARIPADA tapakORDER MENGIKUT tahap, nama;

keadaan WUJUD dan TIDAK WUJUD

Kadangkala adalah perlu untuk memilih rekod daripada jadual yang sepadan (atau tidak sepadan) rekod dalam jadual lain. Katakan kita memerlukan senarai tapak yang belum dilawati. Anda boleh mendapatkan senarai sedemikian dengan meminta

PILIH nama_duduk DARI tapak WHERE ((PILIH COUNT(*) DARI lawatan WHERE vis_sitcode = sit_pcode) = 0);

Untuk contoh kami, senarainya akan pendek:

nama_duduk

www.yandex.ru

Permintaan berfungsi seperti ini:

§ Kod tapak dan namanya dipilih daripada jadual tapak;

§ Kod tapak dihantar ke dalam pertanyaan bersarang, yang mengira rekod dengan kod ini dalam jadual lawatan;

§ fungsi COUNT(*) akan mengira rekod dan mengembalikan nombornya, yang akan dihantar kepada syarat;

§ jika keadaan adalah benar (bilangan rekod ialah 0), nama tapak ditambah pada senarai.

Jika sesetengah orang mendapati pertanyaan ini mengelirukan, anda boleh mencapai hasil yang sama dengan pertanyaan menggunakan NOT EXISTS:

PILIH sit_name DARI tapak WHERE NOT EXISTS (PILIH vis_pcode DARI lawatan WHERE vis_sitcode = sit_pcode);

Ungkapan NOT EXISTS (pada pendapat saya) membawa kejelasan tambahan dan lebih mudah untuk difahami. Ungkapan EXISTS berfungsi sama, yang menyemak kehadiran rekod.

Pandangan (VIEW)

Views (VIEW) digunakan untuk membolehkan pertanyaan kompleks disimpan pada pelayan di bawah nama yang ditentukan. Katakan anda sering perlu meminta data dengan menaip pertanyaan yang besar. Jika anda mendekati masalah secara progresif, anda boleh mencipta idea. Ini mudah dilakukan. Sebagai contoh,

CREATE VIEW show_dom2 ASSELECT hst_name DARI hos WHERE hst_pcode IN (PILIH vis_hstcode DARI lawatan, tapak WHERE (sit_pcode = vis_sitcode) DAN (sit_name SEPERTI "www.dom2.ru"));

Sebenarnya, itu sahaja. Pemerhati yang penuh perhatian mungkin menyedari bahawa, sebenarnya, anda boleh menerima permintaan dan pada awalnya menambah perkataan "CREATE VIEW<имя>AS". Atas prinsip inilah kami boleh mengesyorkan membuat perwakilan. Buat permintaan, pastikan ia berfungsi dan kemudian tambahkan semua yang diperlukan untuk menyimpan permintaan ini pada pelayan sebagai paparan. Satu-satunya kelemahan untuk menggunakan paparan ialah beberapa teknik penulisan pertanyaan termaju mungkin tidak berfungsi dalam paparan. Malangnya, terdapat sedikit maklumat tentang pandangan dalam dokumentasi postgreSQL, dan anda pasti boleh mengetahui perkara yang boleh digunakan dan perkara yang tidak boleh melalui percubaan dan kesilapan. Dengan menyimpan permintaan pada pelayan sebagai paparan, anda boleh melaksanakannya seberapa banyak kali yang anda suka, dengan permintaan seperti

PILIH * DARI show_dom2;

Adalah penting untuk ambil perhatian bahawa apabila melaksanakan pertanyaan yang memilih data daripada paparan, data dipilih daripada jadual melalui pertanyaan yang disimpan dalam paparan. Paparan adalah dinamik sepenuhnya dan data yang dikembalikan oleh paparan akan menjadi terkini apabila data dalam jadual dikemas kini. Anda boleh memadamkan paparan dengan permintaan seperti

DROP VIEW show_dom2;

Kesimpulan

pesanan permintaan laporan data

Dalam kerja kursus ini, pangkalan data "Gudang Alat Tulis" telah dibangunkan, mengandungi semua maklumat yang diperlukan tentang produk, pelanggan, pembekal dan pesanan. Menggunakan pangkalan data saya, anda boleh dengan mudah dan tanpa pengetahuan khusus mengekalkan pangkalan data yang membolehkan anda melaksanakan semua operasi dengan pelanggan, pesanan dan pengilang. Iaitu, tambah, tukar, kemas kini, padam dan lihat semua data yang tersedia dan dimasukkan. Pertanyaan dan laporan telah disusun berdasarkan pangkalan data.

KESIMPULAN

BIBLIOGRAFI

LAMPIRAN A

LAMPIRAN B

Dalam pelajaran lepas kami menghadapi satu kesulitan. Apabila kami ingin mengetahui siapa yang mencipta topik "basikal", kami membuat permintaan yang sepadan:

Daripada nama pengarang, kami menerima pengecamnya. Ini boleh difahami, kerana kami membuat pertanyaan kepada satu jadual - Topik, dan nama pengarang topik disimpan dalam jadual lain - Pengguna. Oleh itu, setelah mengetahui pengecam pengarang topik, kita perlu membuat pertanyaan lain - ke jadual Pengguna untuk mengetahui namanya:

SQL menyediakan keupayaan untuk menggabungkan pertanyaan sedemikian menjadi satu dengan mengubah salah satu daripadanya menjadi subkueri (pertanyaan bersarang). Jadi, untuk mengetahui siapa yang mencipta topik "basikal", kami akan membuat pertanyaan berikut:

Iaitu, selepas kata kunci DI MANA, kami menulis permintaan lain dalam keadaan. MySQL mula-mula memproses subquery, mengembalikan id_author=2, dan nilai ini dihantar ke klausa DI MANA permintaan luar.

Terdapat beberapa subkueri dalam satu pertanyaan, sintaks untuk pertanyaan sedemikian adalah seperti berikut: Ambil perhatian bahawa subkueri boleh memilih hanya satu lajur, yang nilainya akan dikembalikan kepada pertanyaan luar. Mencuba untuk memilih berbilang lajur akan mengakibatkan ralat.

Untuk menyatukan ini, mari buat permintaan lain dan ketahui mesej yang ditinggalkan pengarang topik "basikal" di forum:

Sekarang mari kita rumitkan tugas, ketahui topik yang mana pengarang topik "basikal" meninggalkan mesej:

Mari kita fikirkan bagaimana ia berfungsi.

• MySQL akan melaksanakan pertanyaan terdalam terlebih dahulu:


• Hasil keputusan (id_author=2) akan dihantar kepada permintaan luaran, yang akan mengambil bentuk:


• Hasil keputusan (id_topic:4,1) akan dihantar kepada permintaan luaran, yang akan mengambil bentuk:


• Dan ia akan memberikan hasil akhir (nama_topik: tentang memancing, tentang memancing). Itu. pengarang topik "basikal" meninggalkan mesej dalam topik "Mengenai memancing" yang dibuat oleh Sergei (id=1) dan dalam topik "Mengenai memancing" yang dibuat oleh Sveta (id=4).

Itu sahaja yang saya ingin katakan tentang pertanyaan bersarang. Walaupun, terdapat dua perkara yang patut diberi perhatian:

• Ia tidak disyorkan untuk membuat pertanyaan dengan tahap bersarang lebih besar daripada tiga. Ini membawa kepada peningkatan masa pelaksanaan dan kesukaran untuk memahami kod.
• Sintaks yang diberikan untuk pertanyaan bersarang mungkin adalah yang paling biasa, tetapi bukan satu-satunya. Sebagai contoh, bukannya bertanya
  
  menulis
  
  Itu. kita boleh menggunakan mana-mana operator yang digunakan dengan kata kunci WHERE (kami telah mempelajarinya dalam pelajaran lepas).