Apa itu SQL? Bahasa pergaulan analisis data

Saat ini, Structured Query Language adalah cara standar untuk memanipulasi dan membuat kueri data dalam database relasional, meskipun dengan ekstensi kepemilikan di antara produk. Kemudahan dan keberadaan SQL di mana-mana bahkan telah mengarahkan pembuat banyak "NoSQL" atau penyimpanan data non-relasional, seperti Hadoop, untuk mengadopsi subset SQL atau menghasilkan bahasa kueri serupa SQL mereka sendiri.

Tetapi SQL tidak selalu menjadi bahasa "universal" untuk database relasional. Sejak awal (sekitar 1980), SQL memiliki serangan tertentu terhadapnya. Banyak peneliti dan pengembang pada saat itu, termasuk saya, berpikir bahwa overhead SQL akan membuatnya tidak praktis dalam database produksi.

Jelas sekali, kami salah. Tetapi banyak yang masih percaya bahwa, untuk semua kemudahan dan aksesibilitas SQL, harga yang dituntut dalam kinerja runtime seringkali terlalu tinggi.

Sejarah SQL

Sebelum ada SQL, database memiliki antarmuka pemrograman navigasi yang ketat, dan biasanya dirancang di sekitar skema jaringan yang disebut model data CODASYL. CODASYL (Committee on Data Systems Languages) adalah konsorsium yang bertanggung jawab atas bahasa pemrograman COBOL (mulai tahun 1959) dan ekstensi bahasa database (mulai 10 tahun kemudian).

Saat Anda memprogram terhadap database CODASYL, Anda menavigasi ke rekaman melalui kumpulan, yang mengekspresikan hubungan satu ke banyak. Database hierarki yang lebih lama hanya mengizinkan record untuk dimiliki oleh satu set. Database jaringan memungkinkan sebuah record menjadi bagian dari beberapa set.

Katakanlah Anda ingin membuat daftar siswa yang terdaftar di CS 101. Pertama Anda akan menemukan "CS 101"di Courseshimpunan berdasarkan nama, set itu sebagai pemilik atau orang tua Enrolleeshimpunan, temukan anggota pertama ( ffm) dari Enrolleeshimpunan, yang merupakan Studentcatatan, dan daftar Itu. Kemudian Anda akan masuk ke dalam loop: Temukan anggota berikutnya ( fnm) dan daftarkan itu. Jika fnmgagal, Anda akan keluar dari loop.

Itu mungkin tampak seperti banyak pekerjaan kotor untuk programmer database, tapi sangat efisien pada waktu eksekusi. Pakar seperti Michael Stonebraker dari University of California di Berkeley dan Ingres menunjukkan bahwa melakukan kueri semacam itu dalam database CODASYL seperti IDMS memakan waktu sekitar setengah dari waktu CPU dan kurang dari setengah memori sebagai kueri yang sama pada database relasional menggunakan SQL .

Sebagai perbandingan, kueri SQL yang setara untuk mengembalikan semua siswa di CS 101 akan menjadi seperti ini 

PILIH student.name FROM course, enrollees, students WHERE course.name

Sintaks itu menyiratkan gabungan dalam relasional (sebenarnya dua di antaranya), seperti yang akan saya jelaskan di bawah, dan meninggalkan beberapa detail penting, seperti bidang yang digunakan untuk gabungan.

Database relasional dan SQL

Mengapa Anda melepaskan faktor dua peningkatan dalam kecepatan eksekusi dan penggunaan memori? Ada dua alasan besar: kemudahan pengembangan dan portabilitas. Saya tidak berpikir bahwa salah satu hal tersebut terlalu penting pada tahun 1980 dibandingkan dengan kinerja dan persyaratan memori, tetapi seiring dengan peningkatan perangkat keras komputer dan menjadi lebih murah, orang-orang berhenti peduli tentang kecepatan eksekusi dan memori dan lebih mengkhawatirkan biaya pengembangan.

Dengan kata lain, Hukum Moore membunuh database CODASYL dan mendukung database relasional. Kebetulan, peningkatan waktu pengembangan sangat signifikan, tetapi portabilitas SQL ternyata hanya mimpi.

Dari manakah model relasional dan SQL berasal? EF "Ted" Codd adalah seorang ilmuwan komputer di IBM San Jose Research Laboratory yang mengerjakan teori model relasional pada 1960-an dan menerbitkannya pada 1970. IBM lamban dalam mengimplementasikan basis data relasional dalam upaya melindungi pendapatan database CODASYL-nya IMS / DB. Ketika IBM akhirnya memulai proyek System R-nya, tim pengembangan (Don Chamberlin dan Ray Boyce) tidak berada di bawah Codd, dan mereka mengabaikan makalah bahasa relasional Alpha tahun 1971 dari Codd untuk merancang bahasa mereka sendiri, SEQUEL (Structured English Query Language). Pada 1979, bahkan sebelum IBM merilis produknya, Larry Ellison memasukkan bahasa itu ke dalam database Oracle-nya (menggunakan publikasi SEQUEL pra-peluncuran IBM sebagai spesifikasinya). SEQUEL segera menjadi SQL untuk menghindari pelanggaran merek dagang internasional.

The "tom-tom beating for SQL" (seperti yang dikatakan Michael Stonebraker) tidak hanya datang dari Oracle dan IBM, tetapi juga dari pelanggan. Tidak mudah untuk merekrut atau melatih desainer dan pemrogram basis data CODASYL, jadi SEQUEL (dan SQL) tampak jauh lebih menarik. SQL sangat menarik di akhir 1980-an sehingga banyak vendor database pada dasarnya menjepit prosesor kueri SQL di atas database CODASYL mereka, yang membuat Codd sangat kecewa, yang merasa bahwa database relasional harus dirancang dari awal agar menjadi relasional.

Database relasional murni, seperti yang dirancang oleh Codd, dibangun di atas tupel yang dikelompokkan ke dalam relasi, konsisten dengan logika predikat orde pertama. Database relasional dunia nyata memiliki tabel yang berisi bidang, batasan, dan pemicu, dan tabel terkait melalui kunci asing. SQL digunakan untuk mendeklarasikan data yang akan dikembalikan, dan prosesor kueri SQL dan pengoptimal kueri mengubah deklarasi SQL menjadi rencana kueri yang dijalankan oleh mesin database.

SQL menyertakan sub-bahasa untuk mendefinisikan skema, bahasa definisi data (DDL), bersama dengan sub-bahasa untuk mengubah data, bahasa manipulasi data (DML). Keduanya memiliki akar pada spesifikasi CODASYL awal. Sub-bahasa ketiga dalam SQL mendeklarasikan kueri, melalui SELECTpernyataan dan gabungan relasional.

SELECTPernyataan SQL 

The SELECTpernyataan mengatakan query optimizer data apa untuk kembali, apa meja untuk melihat, apa hubungan untuk mengikuti, dan apa untuk memaksakan pada data kembali. Pengoptimal kueri harus mencari tahu sendiri indeks apa yang akan digunakan untuk menghindari pemindaian tabel brute force dan mencapai kinerja kueri yang baik, kecuali jika database tertentu mendukung petunjuk indeks.

Bagian dari seni desain database relasional bergantung pada penggunaan indeks yang bijaksana. Jika Anda menghilangkan indeks untuk kueri yang sering dilakukan, seluruh database bisa melambat di bawah beban baca yang berat. Jika Anda memiliki terlalu banyak indeks, seluruh database dapat melambat saat penulisan berat dan pemuatan pembaruan.

Seni penting lainnya adalah memilih kunci utama yang bagus dan unik untuk setiap meja. Anda tidak hanya harus mempertimbangkan pengaruh kunci utama pada kueri umum, tetapi bagaimana kunci itu akan diputar dalam gabungan ketika muncul sebagai kunci asing di tabel lain, dan bagaimana hal itu akan memengaruhi lokalitas referensi data.

Dalam kasus lanjutan tabel database yang dibagi menjadi beberapa volume bergantung pada nilai kunci utama, yang disebut sharding horizontal, Anda juga harus mempertimbangkan bagaimana kunci utama akan memengaruhi sharding. Petunjuk: Anda ingin tabel didistribusikan secara merata di seluruh volume, yang menunjukkan bahwa Anda tidak ingin menggunakan stempel tanggal atau bilangan bulat berurutan sebagai kunci utama.

Diskusi tentang SELECTpernyataan tersebut mungkin dimulai dengan sederhana, tetapi dapat dengan cepat menjadi membingungkan. Mempertimbangkan:

PILIH * DARI Pelanggan;

Sederhana bukan? Ini meminta semua bidang dan semua baris Customerstabel. Namun, misalkan Customerstabel memiliki seratus juta baris dan seratus bidang, dan salah satu bidang adalah bidang teks besar untuk komentar. Berapa lama waktu yang dibutuhkan untuk menarik semua data tersebut melalui koneksi jaringan 10 megabit per detik jika setiap baris berisi rata-rata 1 kilobyte data?

Mungkin Anda harus mengurangi jumlah yang Anda kirimkan melalui kawat. Mempertimbangkan:

PILIH TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers

DI MANA negara bagian DAN kota

ORDER BY lastSaleDate DESCENDING;

Sekarang Anda akan menarik lebih sedikit data. Anda telah meminta database untuk memberi Anda hanya empat bidang, untuk hanya mempertimbangkan perusahaan di Cleveland, dan memberi Anda hanya 100 perusahaan dengan penjualan terbaru. Untuk melakukannya paling efisien di server database, bagaimanapun, Customerstabel memerlukan indeks state+cityuntuk WHEREklausa dan indeks lastSaleDateuntuk klausa ORDER BYdan TOP 100.

By the way, TOP 100valid untuk SQL Server dan SQL Azure, tetapi tidak untuk MySQL atau Oracle. Di MySQL, Anda akan menggunakan LIMIT 100setelah WHEREklausa. Di Oracle, Anda akan menggunakan terikat ROWNUMsebagai bagian dari WHEREklausa, yaitu WHERE... AND ROWNUM <=100. Sayangnya, standar ANSI / ISO SQL (dan ada sembilan di antaranya hingga saat ini, mulai dari 1986 hingga 2016) hanya berlaku sejauh ini, di luar itu setiap database memperkenalkan klausul dan fitur miliknya sendiri.

SQL bergabung 

Sejauh ini, saya telah menjelaskan SELECTsintaks untuk tabel tunggal. Sebelum saya dapat menjelaskan  JOINklausa, Anda perlu memahami kunci asing dan hubungan antar tabel. Saya akan menjelaskan ini dengan menggunakan contoh di DDL, menggunakan sintaks SQL Server.

Versi singkatnya cukup sederhana. Setiap tabel yang ingin Anda gunakan dalam relasi harus memiliki batasan kunci utama; ini bisa berupa satu bidang atau kombinasi bidang yang ditentukan oleh ekspresi. Sebagai contoh:

BUAT TABEL Orang (

    PersonID int NOT NULL PRIMARY KEY,

    PersonName char (80),

    ...

Setiap tabel yang perlu terkait Personsharus memiliki bidang yang sesuai dengan Personskunci utama, dan untuk menjaga integritas relasional, bidang tersebut harus memiliki batasan kunci asing. Sebagai contoh:

BUAT Pesanan TABEL (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int FOREIGN KEY REFERENSI Persons (PersonID)

);

Ada versi yang lebih panjang dari kedua pernyataan yang menggunakan CONSTRAINTkata kunci, yang memungkinkan Anda memberi nama batasan. Itulah yang dihasilkan sebagian besar alat desain database.

Kunci utama selalu diindeks dan unik (nilai bidang tidak dapat diduplikasi). Bidang lain secara opsional dapat diindeks. Seringkali berguna untuk membuat indeks untuk bidang kunci asing dan untuk bidang yang muncul di WHEREdan ORDER BYklausa, meskipun tidak selalu, karena potensi overhead dari penulisan dan pembaruan.

Bagaimana Anda akan menulis kueri yang mengembalikan semua pesanan yang dibuat oleh John Doe?

PILIH PersonName, OrderID FROM Persons

INNER JOIN Orders ON Persons.PersonID = Orders.PersonID

DI MANA PersonName;

Bahkan, ada empat macam JOIN: INNER, OUTER, LEFT, dan RIGHT. Ini INNER JOINadalah default (Anda bisa menghilangkan kata INNER), dan itu satu-satunya yang menyertakan baris yang berisi nilai yang cocok di kedua tabel. Jika Anda ingin membuat daftar orang apakah mereka memiliki pesanan atau tidak, Anda akan menggunakan LEFT JOIN, misalnya:

PILIH PersonName, OrderID FROM Persons

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

ORDER BY PersonName;

Saat Anda mulai melakukan kueri yang menggabungkan lebih dari dua tabel, yang menggunakan ekspresi, atau yang memaksa tipe data, sintaksnya bisa menjadi sedikit rumit pada awalnya. Untungnya, ada alat pengembangan database yang bisa menghasilkan kueri SQL yang benar untuk Anda, sering kali dengan menyeret dan melepaskan tabel dan bidang dari diagram skema ke dalam diagram kueri.

Prosedur tersimpan SQL

Terkadang sifat pernyataan yang deklaratif SELECTtidak membawa Anda ke tempat yang Anda inginkan. Kebanyakan database memiliki fasilitas yang disebut prosedur tersimpan; sayangnya ini adalah area di mana hampir semua database menggunakan ekstensi berpemilik dengan standar ANSI / ISO SQL.

Di SQL Server, dialek awal untuk prosedur tersimpan (atau procs tersimpan) adalah Transact-SQL, alias T-SQL; di Oracle, itu adalah PL-SQL. Kedua database telah menambahkan bahasa tambahan untuk prosedur tersimpan, seperti C #, Java, dan R. Prosedur tersimpan T-SQL sederhana mungkin hanya berupa versi berparameter dari SELECTpernyataan. Keunggulannya adalah kemudahan penggunaan dan efisiensi. Prosedur yang disimpan dioptimalkan saat disimpan, tidak setiap kali dijalankan.

Prosedur tersimpan T-SQL yang lebih rumit mungkin menggunakan beberapa pernyataan SQL, parameter input dan output, variabel lokal, BEGIN...ENDblok, IF...THEN...ELSEkondisi, kursor (pemrosesan baris demi baris dari satu set), ekspresi, tabel sementara, dan seluruh host lainnya. sintaks prosedural. Jelas jika bahasa prosedur tersimpan adalah C #, Java, atau R, Anda akan menggunakan fungsi dan sintaks bahasa prosedural tersebut. Dengan kata lain, terlepas dari fakta bahwa SQL adalah motivasi untuk menggunakan kueri deklaratif standar, di dunia nyata Anda melihat banyak pemrograman server prosedural khusus database.

Itu tidak cukup membawa kita kembali ke masa lalu yang buruk dari pemrograman basis data CODASYL (meskipun kursor mendekati), tetapi itu berjalan kembali dari gagasan bahwa pernyataan SQL harus distandarisasi dan bahwa masalah kinerja harus diserahkan kepada pengoptimal kueri basis data . Pada akhirnya, penggandaan kinerja seringkali terlalu banyak untuk ditinggalkan.

Pelajari SQL

Situs-situs yang tercantum di bawah dapat membantu Anda mempelajari SQL, atau menemukan keunikan berbagai dialek SQL.