Apa itu SQL? Lingua franca analisis data

Hari ini, Bahasa Pertanyaan Berstruktur adalah cara standard untuk memanipulasi dan meminta data dalam pangkalan data hubungan, walaupun dengan peluasan proprietari di antara produk. Kemudahan dan keberadaan SQL bahkan mendorong pencipta banyak "NoSQL" atau kedai data bukan hubungan, seperti Hadoop, untuk mengadopsi subkumpulan SQL atau membuat bahasa pertanyaan seperti SQL mereka sendiri.

Tetapi SQL tidak selalu menjadi bahasa "universal" untuk pangkalan data hubungan. Sejak awal (sekitar tahun 1980), SQL melakukan teguran tertentu terhadapnya. Banyak penyelidik dan pembangun pada masa itu, termasuk saya, berpendapat bahawa overhead SQL akan menjadikannya tidak praktikal dalam pangkalan data pengeluaran.

Jelas, kami salah. Tetapi ramai yang masih percaya bahawa, untuk semua kemudahan dan aksesibilitas SQL, harga yang disyaratkan dalam prestasi runtime sering kali terlalu tinggi.

Sejarah SQL

Sebelum ada SQL, pangkalan data mempunyai antaramuka pengaturcaraan navigasi yang ketat, dan biasanya dirancang di sekitar skema rangkaian yang disebut model data CODASYL. CODASYL (Jawatankuasa Bahasa Sistem Sistem Data) adalah sebuah konsortium yang bertanggungjawab untuk bahasa pengaturcaraan COBOL (mulai tahun 1959) dan peluasan bahasa pangkalan data (mulai 10 tahun kemudian).

Semasa anda memprogramkan pangkalan data CODASYL, anda menavigasi ke rakaman melalui set, yang menyatakan hubungan satu-ke-banyak. Pangkalan data hierarki lama hanya membenarkan rekod menjadi milik satu set. Pangkalan data rangkaian membenarkan rekod milik pelbagai set.

Katakan anda mahu menyenaraikan pelajar yang mendaftar di CS 101. Mula-mula anda akan dapati "CS 101"di Coursesset berdasarkan nama, tetapkan sebagai pemilik atau ibu bapa Enrolleesset, cari anggota pertama ( ffm) dari Enrolleesset itu, yang merupakan Studentrekod, dan daftar ia. Kemudian anda akan beralih: Cari ahli seterusnya ( fnm) dan senaraikan. Apabila fnmgagal, anda akan keluar dari gelung.

Itu mungkin kelihatan seperti banyak pekerjaan untuk pengaturcara pangkalan data, tetapi sangat efisien pada masa pelaksanaan. Pakar seperti Michael Stonebraker dari University of California di Berkeley dan Ingres menunjukkan bahawa melakukan pertanyaan seperti itu dalam pangkalan data CODASYL seperti IDMS mengambil masa kira-kira separuh masa CPU dan kurang dari separuh memori sebagai pertanyaan yang sama pada pangkalan data hubungan menggunakan SQL .

Sebagai perbandingan, pertanyaan SQL yang setara untuk mengembalikan semua pelajar di CS 101 akan menjadi seperti 

PILIH pelajar.nama DARI kursus, pendaftar, pelajar DI MANA kursus.nama

Sintaks itu menyiratkan hubungan dalaman yang relasional (sebenarnya dua daripadanya), seperti yang akan saya jelaskan di bawah, dan meninggalkan beberapa butiran penting, seperti bidang yang digunakan untuk bergabung.

Pangkalan data hubungan dan SQL

Mengapa anda melepaskan faktor dua peningkatan kelajuan pelaksanaan dan penggunaan memori? Terdapat dua sebab besar: kemudahan pembangunan dan mudah alih. Saya tidak menyangka salah satu yang penting pada tahun 1980 dibandingkan dengan prestasi dan keperluan memori, tetapi ketika perkakasan komputer bertambah baik dan menjadi lebih murah orang berhenti mengambil berat tentang kelajuan pelaksanaan dan memori dan lebih bimbang tentang kos pembangunan.

Dengan kata lain, Undang-undang Moore membunuh pangkalan data CODASYL yang memihak kepada pangkalan data hubungan. Seperti yang berlaku, peningkatan dalam masa pembangunan sangat ketara, tetapi kebolehmampuan SQL ternyata menjadi impian utama.

Dari mana model hubungan dan SQL berasal? EF “Ted” Codd adalah seorang saintis komputer di IBM San Jose Research Laboratory yang menggarap teori model hubungan pada tahun 1960-an dan menerbitkannya pada tahun 1970. IBM lambat melaksanakan pangkalan data hubungan dalam usaha melindungi pendapatan pangkalan data CODASYL IMS / DBnya. Ketika IBM akhirnya memulakan projek System R, pasukan pengembangan (Don Chamberlin dan Ray Boyce) tidak berada di bawah Codd, dan mereka mengabaikan kertas bahasa hubungan Alpha 1971 Codd untuk merancang bahasa mereka sendiri, SEQUEL (Structured English Query Language). Pada tahun 1979, sebelum IBM bahkan mengeluarkan produknya, Larry Ellison memasukkan bahasa tersebut dalam pangkalan data Oracle (menggunakan penerbitan SEQUEL sebelum pelancaran IBM sebagai spesifikasinya). SEQUEL segera menjadi SQL untuk mengelakkan pelanggaran tanda dagangan antarabangsa.

"Pemukulan tom-tom untuk SQL" (seperti yang dikatakan oleh Michael Stonebraker) bukan hanya datang dari Oracle dan IBM, tetapi juga dari pelanggan. Tidak mudah untuk menyewa atau melatih pereka dan pengaturcara pangkalan data CODASYL, jadi SEQUEL (dan SQL) kelihatan lebih menarik. SQL begitu menarik pada akhir 1980-an sehingga banyak vendor pangkalan data pada dasarnya memasang pemproses pertanyaan SQL di atas pangkalan data CODASYL mereka, sehingga Codd sangat kecewa, yang merasakan bahawa pangkalan data relasional harus dirancang dari awal untuk menjadi relasional.

Pangkalan data hubungan murni, seperti yang dirancang oleh Codd, dibina di atas tupel yang dikelompokkan dalam hubungan, sesuai dengan logik predikat pertama. Pangkalan data hubungan dunia nyata mempunyai jadual yang mengandungi medan, kekangan, dan pencetus, dan jadual dihubungkan melalui kunci asing. SQL digunakan untuk menyatakan data yang akan dikembalikan, dan pemproses permintaan SQL dan pengoptimal pertanyaan mengubah pernyataan SQL menjadi rencana permintaan yang dijalankan oleh mesin pangkalan data.

SQL merangkumi sub-bahasa untuk menentukan skema, bahasa definisi data (DDL), bersama dengan sub-bahasa untuk mengubah data, bahasa manipulasi data (DML). Kedua-duanya mempunyai akar pada spesifikasi CODASYL awal. Sub-bahasa ketiga dalam SQL menyatakan pertanyaan, melalui SELECTpernyataan dan relasi bergabung.

SELECTPenyataan SQL 

The SELECTkenyataan memberitahu pengoptimum pertanyaan apa data pulangan, apa jadual untuk melihat, apa hubungan untuk diikuti, dan apakah perintah yang mengenakan ke atas data yang dikembalikan. Pengoptimum pertanyaan mesti mengetahui sendiri indeks apa yang akan digunakan untuk mengelakkan imbasan jadual kekuatan kasar dan mencapai prestasi pertanyaan yang baik, kecuali pangkalan data tertentu menyokong petunjuk indeks.

Sebilangan seni reka bentuk pangkalan data hubungan bergantung pada penggunaan indeks yang bijaksana. Sekiranya anda menghilangkan indeks untuk pertanyaan yang kerap, keseluruhan pangkalan data dapat menjadi perlahan di bawah beban baca yang berat. Sekiranya anda mempunyai terlalu banyak indeks, keseluruhan pangkalan data dapat menjadi perlahan di bawah banyak penulisan dan kemas kini yang berat.

Seni penting lain ialah memilih kunci utama yang baik dan unik untuk setiap meja. Anda bukan sahaja harus mempertimbangkan kesan kunci utama pada pertanyaan biasa, tetapi bagaimana ia akan dimainkan semasa ia bergabung sebagai kunci asing di jadual lain, dan bagaimana ia akan mempengaruhi lokasi rujukan data.

Dalam kes jadual pangkalan data lanjutan yang dibahagikan kepada jumlah yang berbeza bergantung pada nilai kunci primer, yang disebut pecahan mendatar, anda juga harus mempertimbangkan bagaimana kunci utama akan mempengaruhi pecahan. Petunjuk: Anda mahu jadual diedarkan secara merata di seluruh jilid, yang menunjukkan bahawa anda tidak mahu menggunakan cap tarikh atau bilangan bulat berturut-turut sebagai kunci utama.

Perbincangan SELECTpenyataan mungkin bermula sederhana, tetapi cepat membingungkan. Pertimbangkan:

PILIH * DARI Pelanggan;

Ringkas, bukan? Ia meminta semua bidang dan semua baris Customersjadual. Namun, anggaplah bahawa Customersjadual mempunyai seratus juta baris dan seratus bidang, dan salah satu bidangnya ialah medan teks yang besar untuk komen. Berapa lama masa yang diperlukan untuk menurunkan semua data melalui sambungan rangkaian 10 megabit per saat jika setiap baris mengandungi rata-rata 1 kilobyte data?

Mungkin anda harus mengurangkan berapa banyak yang anda kirimkan melalui wayar. Pertimbangkan:

PILIH TOP 100 syarikatNama, lastSaleDate, lastSaleAmount, totalSalesAmount DARI Pelanggan

DI MANA negeri dan bandar

PESANAN OLEH lastSaleDate MENGHILANGKAN;

Sekarang anda akan menurunkan lebih banyak data. Anda telah meminta pangkalan data untuk memberi anda hanya empat bidang, hanya mempertimbangkan syarikat di Cleveland, dan hanya memberi anda 100 syarikat dengan penjualan terbaru. Untuk melakukannya dengan paling berkesan di pelayan pangkalan data, Customersjadual memerlukan indeks state+cityuntuk WHEREklausa dan indeks lastSaleDateuntuk ORDER BYdan TOP 100klausa.

By the way, TOP 100sah untuk SQL Server dan SQL Azure, tetapi tidak MySQL atau Oracle. Di MySQL, anda akan menggunakan LIMIT 100selepas WHEREklausa. Di Oracle, anda akan menggunakan terikat ROWNUMsebagai sebahagian daripada WHEREklausa, iaitu WHERE... AND ROWNUM <=100. Malangnya, piawaian ANSI / ISO SQL (dan ada sembilan daripadanya setakat ini, yang merangkumi 1986 hingga 2016) hanya sejauh ini, di mana setiap pangkalan data memperkenalkan klausa dan ciri kepunyaan sendiri.

SQL bergabung 

Setakat ini, saya telah menerangkan SELECTsintaks untuk jadual tunggal. Sebelum saya dapat menjelaskan  JOINklausa, anda perlu memahami kunci asing dan hubungan antara jadual. Saya akan menerangkannya dengan menggunakan contoh dalam DDL, menggunakan sintaks SQL Server.

Versi pendek ini agak mudah. Setiap jadual yang ingin anda gunakan dalam hubungan harus mempunyai kekangan utama; ini boleh menjadi medan tunggal atau gabungan medan yang ditentukan oleh ungkapan. Sebagai contoh:

BUAT JADUAL Orang (

    PersonID int KUNCI NULL PRIMARY

    Nama Person Person (80),

    ...

Setiap jadual yang perlu dihubungkan Personsharus memiliki bidang yang sesuai dengan Personskunci utama, dan untuk menjaga integriti hubungan bidang tersebut harus memiliki kekangan kunci asing. Sebagai contoh:

BUAT Pesanan JADUAL (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID dalam RUJUKAN UTAMA ASING Orang (PersonID)

);

Terdapat versi kedua penyataan yang lebih panjang yang menggunakan CONSTRAINTkata kunci, yang membolehkan anda menamakan kekangan. Itulah yang dihasilkan oleh kebanyakan alat reka bentuk pangkalan data.

Kekunci utama selalu diindeks dan unik (nilai medan tidak dapat digandakan). Medan lain boleh diindeks secara pilihan. Selalunya berguna untuk membuat indeks untuk bidang utama asing dan untuk bidang yang terdapat dalam WHEREdan ORDER BYklausa, walaupun tidak selalu, kerana potensi overhead dari penulisan dan kemas kini.

Bagaimana anda menulis pertanyaan yang mengembalikan semua pesanan yang dibuat oleh John Doe?

PILIH Nama Orang, OrderID DARI Orang

INNER JOIN Order ON Persons.PersonID = Pesanan.PersonID

MANA Nama Orang;

Malah, terdapat empat jenis JOIN: INNER, OUTER, LEFT, dan RIGHT. Ini INNER JOINadalah lalai (anda boleh menghilangkan kata INNER), dan yang hanya merangkumi baris yang mengandungi nilai yang sepadan dalam kedua-dua jadual. Sekiranya anda ingin menyenaraikan orang sama ada mereka mempunyai pesanan atau tidak, anda boleh menggunakan LEFT JOIN, misalnya:

PILIH Nama Orang, OrderID DARI Orang

KIRI BERGABUNG Pesanan PADA ORANG.PersonID = Pesanan.PersonID

PESANAN MENGIKUT NamaNama;

Apabila anda mula melakukan pertanyaan yang bergabung dengan lebih dari dua jadual, yang menggunakan ungkapan, atau yang memaksa jenis data, sintaksnya dapat sedikit berbulu pada mulanya. Nasib baik, ada alat pengembangan pangkalan data yang dapat menghasilkan pertanyaan SQL yang betul untuk anda, selalunya dengan menyeret dan melepaskan jadual dan medan dari rajah skema ke rajah pertanyaan.

Prosedur tersimpan SQL

Kadang-kadang sifat SELECTpernyataan yang tidak dinyatakan tidak membawa anda ke mana anda mahu pergi. Sebilangan besar pangkalan data mempunyai kemudahan yang disebut prosedur tersimpan; malangnya ini adalah kawasan di mana hampir semua pangkalan data menggunakan sambungan proprietari mengikut standard ANSI / ISO SQL.

Dalam SQL Server, dialek awal untuk prosedur yang tersimpan (atau procs yang tersimpan) adalah Transact-SQL, aka T-SQL; di Oracle, itu adalah PL-SQL. Kedua-dua pangkalan data telah menambahkan bahasa tambahan untuk prosedur yang tersimpan, seperti C #, Java, dan R. Prosedur tersimpan T-SQL yang sederhana mungkin hanya versi SELECTpernyataan yang diparamatisasi. Kelebihannya adalah kemudahan penggunaan dan kecekapan. Prosedur yang disimpan dioptimumkan apabila disimpan, tidak setiap kali dijalankan.

Prosedur yang tersimpan T-SQL yang lebih rumit mungkin menggunakan beberapa pernyataan SQL, parameter input dan output, pemboleh ubah tempatan, BEGIN...ENDblok, IF...THEN...ELSEkeadaan, kursor (pemrosesan set demi baris satu set), ekspresi, jadual sementara, dan sejumlah lain sintaksis prosedur. Jelas jika bahasa prosedur yang tersimpan adalah C #, Java, atau R, anda akan menggunakan fungsi dan sintaks bahasa prosedural tersebut. Dengan kata lain, walaupun fakta bahawa motivasi untuk SQL adalah menggunakan pertanyaan deklaratif standard, di dunia nyata Anda melihat banyak pengaturcaraan pelayan prosedur khusus pangkalan data.

Itu tidak membawa kita kembali ke masa lalu yang buruk dari pengaturcaraan pangkalan data CODASYL (walaupun kursor hampir tiba), tetapi ia berjalan kembali dari idea bahawa pernyataan SQL harus diseragamkan dan kebimbangan prestasi harus diserahkan kepada pengoptimum pertanyaan pangkalan data . Pada akhirnya, penggandaan prestasi seringkali terlalu banyak untuk dibiarkan.

Belajar SQL

Laman web yang disenaraikan di bawah ini dapat membantu anda mempelajari SQL, atau mengetahui kebiasaan pelbagai dialek SQL.