SQLite Tampilan, Indeks & Pemicu dengan Contoh

Dalam penggunaan sehari-hari SQLite, Anda memerlukan beberapa alat administratif pada database Anda. Anda juga dapat menggunakannya untuk membuat kueri database menjadi lebih efisien dengan membuat indeks, atau lebih dapat digunakan kembali dengan membuat tampilan.

SQLite Liha

Tampilan sangat mirip dengan tabel. Tapi Tampilan adalah tabel logis; mereka tidak disimpan secara fisik seperti tabel. Tampilan terdiri dari pernyataan pilih.

Anda dapat menentukan tampilan untuk kueri kompleks Anda, dan Anda dapat menggunakan kembali kueri tersebut kapan pun Anda mau dengan memanggil tampilan tersebut secara langsung, alih-alih menulis ulang kueri tersebut lagi.

pernyataan BUAT LIHAT

Untuk membuat tampilan pada database, Anda bisa menggunakan pernyataan CREATE VIEW diikuti dengan nama tampilan, lalu masukkan kueri yang Anda inginkan setelah itu.

Contoh: Pada contoh berikut, kita akan membuat View dengan nama “AllStudentsView” di database sampel “TutorialsSampleDB.db” sebagai berikut:

Langkah 1) Buka Komputer Saya dan navigasikan ke direktori berikut “C:\sqlite” lalu jalankan “sqlite3.exe”:

Langkah 2) Buka basis data “TutorialsSampleDB.db” dengan perintah berikut:

Langkah 3) Berikut ini adalah sintaks dasar perintah sqlite3 untuk membuat View

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Seharusnya tidak ada keluaran dari perintah seperti ini:

Langkah 4) Untuk memastikan tampilan dibuat, Anda dapat memilih daftar tampilan dalam database dengan menjalankan perintah berikut:

SELECT name FROM sqlite_master WHERE type = 'view';

Anda seharusnya melihat tampilan “AllStudentsView” dikembalikan:

Langkah 5) Sekarang tampilan kita telah dibuat, Anda dapat menggunakannya sebagai tabel normal seperti ini:

SELECT * FROM AllStudentsView;

Perintah ini akan meminta tampilan “AllStudents” dan memilih semua baris darinya seperti yang ditunjukkan pada tangkapan layar berikut:

Tampilan Sementara

Tampilan sementara bersifat sementara untuk koneksi basis data saat ini yang digunakan untuk membuatnya. Kemudian jika Anda menutup koneksi basis data, semua tampilan sementara akan dihapus secara otomatis. Tampilan sementara dibuat menggunakan salah satu perintah berikut:

• BUAT TAMPILAN SUHU, atau
• BUAT TAMPILAN SEMENTARA.

Tampilan sementara berguna jika Anda ingin melakukan beberapa operasi untuk sementara waktu dan tidak membutuhkannya sebagai tampilan permanen. Jadi, Anda tinggal membuat tampilan sementara, lalu melakukan pemrosesan menggunakan tampilan tersebut. Later ketika Anda menutup koneksi dengan database, itu akan terhapus secara otomatis.

Contoh:

Dalam contoh berikut, kita akan membuka koneksi basis data, lalu membuat tampilan sementara.

Setelah itu, kita akan menutup koneksi tersebut, dan kita akan memeriksa apakah tampilan sementara tersebut masih ada atau tidak.

Langkah 1) Buka sqlite3.exe dari direktori “C:\sqlite” seperti yang dijelaskan sebelumnya.

Langkah 2) Buka koneksi ke basis data “TutorialsSampleDB.db” dengan menjalankan perintah berikut:

.open TutorialsSampleDB.db

Langkah 3) Tuliskan perintah berikut yang akan membuat tampilan sementara “AllStudentsTempView”:

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Langkah 4) Pastikan tampilan sementara “AllStudentsTempView” telah dibuat dengan menjalankan perintah berikut:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Langkah 5) Tutup sqlite3.exe dan buka lagi.

Langkah 6) Buka koneksi ke basis data “TutorialsSampleDB.db” dengan perintah berikut:

.open TutorialsSampleDB.db

Langkah 7) Jalankan perintah berikut untuk mendapatkan daftar tampilan sementara yang dibuat pada database:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Anda seharusnya tidak melihat output apa pun karena tampilan sementara yang kita buat terhapus saat kita menutup koneksi basis data pada langkah sebelumnya. Jika tidak, selama Anda tetap membuka koneksi dengan basis data, Anda akan dapat melihat tampilan sementara dengan data.

Catatan:

• Anda tidak dapat menggunakan pernyataan INSERT, DELETE, atau UPDATE dengan tampilan, hanya Anda yang dapat menggunakan perintah “pilih dari tampilan” seperti yang ditunjukkan pada langkah 5 dalam contoh CREATE View.
• Untuk menghapus VIEW, Anda dapat menggunakan pernyataan “DROP VIEW”:

DROP VIEW AllStudentsView;

Untuk memastikan tampilan dihapus, Anda dapat menjalankan perintah berikut yang memberi Anda daftar tampilan dalam database:

SELECT name FROM sqlite_master WHERE type = 'view';

Anda tidak akan menemukan tampilan yang dikembalikan karena tampilan tersebut telah dihapus, seperti berikut:

Selain menggunakan kembali kueri kompleks dengan tampilan, SQLite juga mengambil baris yang cocok lebih cepat melalui indeks.

SQLite Indeks

Jika Anda memiliki sebuah buku, dan Anda ingin mencari kata kunci pada buku tersebut. Anda akan mencari kata kunci tersebut di indeks buku. Kemudian Anda akan menavigasi ke nomor halaman untuk kata kunci tersebut untuk membaca informasi lebih lanjut tentang kata kunci tersebut.

Namun, jika tidak ada indeks pada buku tersebut maupun nomor halaman, Anda akan memindai seluruh buku dari awal hingga akhir hingga menemukan kata kunci yang Anda cari. Dan ini sangat sulit terutama jika Anda memiliki indeks dan proses pencarian kata kunci yang sangat lambat.

Indeks masuk SQLite (dan konsep yang sama berlaku untuk yang lain sistem manajemen basis data juga) bekerja dengan cara yang sama seperti indeks yang ditemukan di bagian belakang buku.

Saat Anda mencari beberapa baris dalam sebuah SQLite tabel dengan kriteria pencarian, SQLite akan mencari di semua baris tabel hingga menemukan baris yang dicari sesuai dengan kriteria pencarian. Dan proses itu menjadi sangat lambat bila Anda memiliki tabel yang lebih besar.

Indeks akan mempercepat permintaan pencarian data dan akan membantu melakukan pengambilan data dari tabel. Indeks ditentukan pada kolom tabel.

Meningkatkan kinerja dengan Indeks:

Indeks dapat meningkatkan kinerja pencarian data pada tabel. Saat Anda membuat indeks pada kolom, SQLite akan membuat struktur data untuk indeks tersebut di mana setiap nilai bidang memiliki penunjuk ke seluruh baris tempat nilai tersebut berada.

Lalu, jika Anda menjalankan kueri dengan kondisi pencarian pada kolom yang merupakan bagian dari indeks, SQLite akan mencari nilai pada indeks terlebih dahulu. SQLite tidak akan memindai seluruh tabel untuk itu. Kemudian akan terbaca lokasi dimana nilai menunjuk pada baris tabel. SQLite akan menemukan baris di lokasi itu dan mengambilnya.

Namun, jika kolom yang Anda cari bukan bagian dari indeks, SQLite akan melakukan pemindaian nilai kolom untuk menemukan data yang Anda cari. Biasanya prosesnya akan lebih lambat jika tidak ada indeks.

Bayangkan sebuah buku tanpa indeks dan Anda perlu mencari kata tertentu. Anda akan memindai seluruh buku dari halaman pertama hingga halaman terakhir untuk mencari kata itu. Namun, jika Anda memiliki indeks pada buku itu, Anda akan mencari kata di dalamnya terlebih dahulu. Dapatkan nomor halaman di mana lokasinya, lalu navigasikan ke sana. Ini akan jauh lebih cepat daripada memindai keseluruhan buku dari sampul ke sampul.

SQLite BUAT INDEKS

Untuk membuat indeks pada kolom, sebaiknya gunakan perintah CREATE INDEX. Dan Anda harus mendefinisikannya sebagai berikut:

• Anda harus menentukan nama indeks setelah perintah CREATE INDEX.
• Setelah nama indeks, Anda harus memasukkan kata kunci “ON”, diikuti dengan nama tabel di mana indeks akan dibuat.
• Kemudian daftar nama kolom yang digunakan untuk indeks.
• Anda dapat menggunakan salah satu kata kunci berikut “ASC” atau “DESC” setelah nama kolom mana pun untuk menentukan urutan sortir yang digunakan untuk mengurutkan data indeks.

Contoh:

Pada contoh berikut, kita akan membuat indeks “StudentNameIndex” pada tabel students di database “Students” sebagai berikut:

Langkah 1) Arahkan ke folder “C:\sqlite” seperti yang dijelaskan sebelumnya.

Langkah 2) Buka sqlite3.exe.

Langkah 3) Buka basis data “TutorialsSampleDB.db” dengan perintah berikut:

.open TutorialsSampleDB.db

Langkah 4) Buat indeks baru “StudentNameIndex” menggunakan perintah berikut:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Anda seharusnya tidak melihat keluaran untuk ini:

Langkah 5) Untuk memastikan indeks telah dibuat, Anda dapat menjalankan kueri berikut, yang akan memberikan Anda daftar indeks yang dibuat dalam tabel Students:

PRAGMA index_list(Students);

Anda akan melihat indeks yang baru saja kita buat dikembalikan:

Catatan:

• Indeks dapat dibuat tidak hanya berdasarkan kolom tetapi juga ekspresi. Sesuatu seperti ini:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

“OrderTotalIndex” akan didasarkan pada kolom OrderId dan juga perkalian nilai kolom Quantity dan nilai kolom Price. Jadi kueri apa pun untuk “OrderId” dan “Quantity*Price” akan efisien karena kueri akan menggunakan indeks.

• Jika Anda menentukan klausa WHERE dalam pernyataan CREATE INDEX, indeks akan menjadi indeks parsial. Dalam hal ini, akan ada entri dalam indeks hanya untuk baris yang sesuai dengan kondisi dalam klausa WHERE. Misalnya, dalam indeks berikut:

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (Dalam contoh di atas, indeks akan menjadi indeks parsial karena terdapat klausa WHERE yang ditentukan. Dalam hal ini, indeks akan diterapkan hanya pada pesanan yang memiliki nilai kuantitas lebih besar dari 10000. Perhatikan bahwa, indeks ini disebut indeks parsial indeks karena klausa WHERE, bukan ekspresi yang digunakan di dalamnya. Namun, Anda dapat menggunakan ekspresi dengan indeks normal.)

• Anda dapat menggunakan pernyataan CREATE UNIQUE INDEX alih-alih CREATE INDEX untuk mencegah entri duplikat untuk kolom dan dengan demikian semua nilai untuk kolom yang diindeks akan menjadi unik.
• Untuk menghapus indeks, gunakan perintah DROP INDEX diikuti dengan nama indeks yang akan dihapus.

Meskipun indeks mempercepat pembacaan, pemicu memungkinkan SQLite Secara otomatis merespons setiap kali data berubah.

SQLite Pelatuk

Pengantar SQLite Pelatuk

Pemicu adalah operasi otomatis yang telah ditetapkan sebelumnya yang dijalankan saat tindakan tertentu terjadi pada tabel basis data. Pemicu dapat ditetapkan untuk diaktifkan setiap kali salah satu tindakan berikut terjadi pada tabel:

• MASUKKAN ke dalam tabel.
• HAPUS baris dari tabel.
• UPDATE salah satu kolom tabel.

SQLite mendukung pemicu FOR EACH ROW sehingga, operasi yang telah ditetapkan dalam pemicu akan dieksekusi untuk semua baris yang terlibat dalam tindakan yang terjadi pada tabel (apakah itu penyisipan, penghapusan, atau pembaruan).

SQLite BUAT PEMICU

Untuk membuat TRIGGER baru, Anda dapat menggunakan pernyataan CREATE TRIGGER sebagai berikut:

• Setelah CREATE TRIGGER, Anda harus menentukan nama pemicu.
• Setelah nama pemicu, Anda harus menentukan kapan tepatnya nama pemicu harus dieksekusi. Anda memiliki tiga opsi:
  • SEBELUM – pemicu akan dieksekusi sebelum pernyataan INSERT, UPDATE, atau delete ditentukan.
  • Setelah – pemicu akan dieksekusi setelah pernyataan INSERT, UPDATE, atau delete yang ditentukan.
  • BUKAN – Ini akan menggantikan tindakan yang terjadi yang memicu pemicu dengan pernyataan yang ditentukan dalam PEMICU. BUKAN pemicu tidak berlaku dengan tabel, hanya dengan tampilan.
• Kemudian, Anda harus menentukan jenis tindakan, pemicunya akan diaktifkan ketika itu terjadi. Entah HAPUS, MASUKKAN, atau UPDATE.
• Anda dapat memilih nama kolom opsional sehingga pemicu tidak akan diaktifkan kecuali tindakan terjadi pada kolom tersebut.
• Kemudian Anda harus menentukan nama tabel di mana pemicunya akan dibuat.
• Di dalam badan pemicu, Anda harus menentukan pernyataan yang harus dieksekusi untuk setiap baris saat pemicu diaktifkan.

Pemicu akan diaktifkan (diaktifkan) hanya tergantung pada jenis pernyataan yang ditentukan pada perintah buat pemicu. Misalnya:

• Pemicu BEFORE INSERT akan diaktifkan (diaktifkan) sebelum pernyataan penyisipan apa pun.
• Pemicu AFTER UPDATE akan diaktifkan (diaktifkan) setelah pernyataan pembaruan apa pun,… dan seterusnya.

Di dalam pemicu, Anda dapat merujuk ke nilai yang baru dimasukkan menggunakan kata kunci “baru”. Anda juga dapat merujuk ke nilai yang dihapus atau diperbarui menggunakan kata kunci lama. Sebagai berikut:

• Di dalam pemicu INSERT – kata kunci baru dapat digunakan.
• Di dalam pemicu UPDATE – kata kunci baru dan lama dapat digunakan.
• Di dalam pemicu DELETE – kata kunci lama dapat digunakan.

Example

Selanjutnya, kita akan membuat trigger yang akan aktif sebelum memasukkan siswa baru ke dalam tabel “Siswa”.

Sistem akan mencatat siswa yang baru dimasukkan ke dalam tabel “StudentsLog” dengan stempel waktu otomatis untuk tanggal dan waktu saat pernyataan penyisipan terjadi. Seperti berikut:

Langkah 1) Masuk ke direktori “C:\sqlite” dan jalankan sqlite3.exe.

Langkah 2) Buka basis data “TutorialsSampleDB.db” dengan menjalankan perintah berikut:

.open TutorialsSampleDB.db

Langkah 3) Buat trigger “InsertIntoStudentTrigger” dengan menjalankan perintah berikut:

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

Fungsi “datetime()” akan memberikan stempel waktu tanggal saat ini ketika pernyataan insert terjadi. Sehingga kita dapat mencatat transaksi insert dengan stempel waktu otomatis yang ditambahkan ke setiap transaksi.

Perintah tersebut seharusnya berjalan dengan sukses, dan Anda tidak mendapatkan keluaran:

Trigger “InsertIntoStudentTrigger” akan aktif setiap kali Anda memasukkan siswa baru ke dalam tabel siswa. Kata kunci “new” mengacu pada nilai yang akan dimasukkan. Misalnya, “new.StudentId” akan menjadi ID siswa yang akan dimasukkan.

Sekarang, kita akan menguji bagaimana perilaku trigger ketika kita memasukkan siswa baru.

Langkah 4) Tulis perintah berikut yang akan memasukkan siswa baru ke dalam tabel siswa:

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

Langkah 5) Tuliskan perintah berikut yang akan memilih semua baris dari tabel “StudentsLog”:

SELECT * FROM StudentsLog;

Anda akan melihat baris baru untuk siswa baru yang baru saja kita sisipkan:

Baris ini disisipkan oleh trigger sebelum menyisipkan siswa baru dengan id 11.

Dalam contoh ini, kita menggunakan trigger “InsertIntoStudentTrigger” yang telah kita buat, untuk mencatat setiap transaksi insert ke dalam tabel “StudentsLog” secara otomatis. Dengan cara yang sama, Anda dapat mencatat setiap pernyataan update atau delete.

Mencegah pembaruan yang tidak diinginkan dengan pemicu:

Menggunakan pemicu BEFORE UPDATE pada tabel, Anda dapat mencegah pernyataan pembaruan pada kolom berdasarkan ekspresi.

Example

Dalam contoh berikut, kami akan mencegah pernyataan pembaruan apa pun untuk memperbarui kolom “namasiswa” di tabel Siswa:

Langkah 1) Masuk ke direktori “C:\sqlite” dan jalankan sqlite3.exe.

Langkah 2) Buka basis data “TutorialsSampleDB.db” dengan menjalankan perintah berikut:

.open TutorialsSampleDB.db

Langkah 3) Buat trigger baru “preventUpdateStudentName” pada tabel “Students” dengan menjalankan perintah berikut.

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

Perintah “RAISE” akan menimbulkan kesalahan dengan pesan kesalahan “Anda tidak dapat memperbarui studentname”, dan kemudian akan mencegah pernyataan pembaruan dieksekusi.

Sekarang, kami akan memverifikasi bahwa pemicunya berfungsi dengan baik, dan mencegah pembaruan apa pun untuk kolom nama siswa.

Langkah 4) Jalankan perintah pembaruan berikut, yang akan memperbarui nama siswa “Jack” menjadi “Jack1”.

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

Anda akan mendapatkan pesan kesalahan yang telah kami tentukan pada pemicu, yang menyatakan bahwa “Anda tidak dapat memperbarui studentname” seperti berikut:

Langkah 5) Jalankan perintah berikut, yang akan memilih daftar nama siswa dari tabel siswa.

SELECT StudentName FROM Students;

Anda akan melihat bahwa nama siswa “Jack” masih sama dan tidak berubah:

Pertanyaan Umum Demo Slot