Permintaan laporan dua puluh baris yang sama telah disalin ke lima dasbor, dan setiap salinan sedikit menyimpang. Yang satu menghitung pesanan yang dibatalkan, sementara yang lain tidak. Tampilan SQL selesaikan masalah ini dengan mendefinisikan logika tersebut satu kali, dalam database, dengan nama yang jelas.

Kami menjalankan setiap contoh di bawah ini di SQLite 3.50, dan hasil yang ditampilkan berasal dari proses tersebut. Tampilan adalah SQL standar, namun detail seperti pembaruan melalui tampilan, tampilan terwujud, dan penanganan ketergantungan bervariasi antar database, dan kami menunjukkannya.

Apa itu pemandangan?

Tampilan adalah pernyataan SELECT yang disimpan dalam database dengan nama. Saat Anda mengkueri tampilan, database menjalankan kueri yang disimpan tersebut. Ini bertindak seperti tabel untuk membaca, tetapi tampilan normal tidak menyimpan salinan datanya sendiri.

Bayangkan filter yang disimpan di spreadsheet. Filter bukanlah salinan kedua dari sheet tersebut. Ini adalah cara melihat data yang sama, yang selalu mencerminkan nilai-nilai terkini.

Tabel sampel

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

Buat tampilan

Tampilan ini merangkum pesanan setiap pelanggan. Itu menyembunyikan join, pengelompokan dan CASE ekspresi di belakang satu nama sederhana:

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

Sekarang siapa pun dapat menggunakannya, tanpa mengetahui cara kerjanya:

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben muncul dengan nol, karena tampilannya menggunakan LEFT JOIN dan COALESCE. Keputusan seperti ini, yang diambil setelah dilihat, akan menjaga konsistensi laporan. Untuk menyegarkan pemahaman Anda tentang penanganan NULL, lihat Nilai SQL NULL.

Tampilan selalu terkini

Karena tampilan normal menjalankan kuerinya setiap saat, data baru segera muncul. Kami menambahkan pesanan berbayar untuk Ben dan menanyakan tampilannya lagi:

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

Tidak ada langkah penyegaran dan tidak ada salinan untuk tetap disinkronkan. Inilah perbedaan utama dari menyimpan hasil kueri ke dalam tabel terpisah.

Menyembunyikan kolom

Tampilan sering kali digunakan untuk mengekspos hanya kolom yang dibutuhkan seseorang atau aplikasi. Di sini, alamat email dan batas kredit tidak disertakan:

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

Dalam database yang memiliki izin, seperti PostgreSQL, SQL Server, dan MySQL, Anda dapat memberikan akses kepada pengguna pelaporan ke tampilan, namun tidak ke tabel yang mendasarinya. SQLite tidak memiliki akun pengguna, jadi kami hanya dapat menampilkan batasan kolom itu sendiri. Perlakukan tampilan sebagai satu lapisan perlindungan. Data sensitif masih memerlukan kontrol akses yang tepat, dan beberapa database menawarkan keamanan tingkat baris untuk memfilter baris berdasarkan pengguna.

Performa: tampilan bukanlah peningkatan kecepatan

Mitos yang umum adalah bahwa tampilan membuat kueri menjadi lebih cepat. Tampilan normal digantikan oleh definisinya saat Anda menanyakannya, sehingga database melakukan pekerjaan yang sama seolah-olah Anda sendiri yang menulis kueri lengkapnya. Rencana eksekusi kami menunjukkan penggabungan dan pengelompokan tampilan dijalankan sebagai bagian dari kueri:

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

Jadi tampilan kueri yang lambat masih merupakan kueri yang lambat. Kecepatan berasal dari indeks dan desain kueri yang baik, seperti yang dibahas dalam optimasi kueri SQL dan penjelasan indeks SQL. Perhatikan juga tampilan yang dibangun berdasarkan tampilan lain. Lapisan bersarang memudahkan untuk bergabung dengan meja besar yang sama beberapa kali tanpa menyadarinya.

Pandangan yang terwujud

Tampilan terwujud tidak menyimpan hasilnya, seperti tabel cache. Pembacaannya cepat, namun datanya hanya segar seperti penyegaran terakhir.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

Dukungan bervariasi. PostgreSQL dan Oracle memiliki tampilan yang terwujud, dan SQL Server menawarkan tampilan yang diindeks dengan aturannya sendiri. SQLite dan MySQL tidak memilikinya, sehingga orang menggunakan tabel ringkasan reguler yang disegarkan sesuai jadwal. Kami tidak menjalankan contoh PostgreSQL untuk artikel ini.

Bisakah Anda memperbarui tampilan?

Itu tergantung pada database dan tampilan. Tampilan sederhana pada satu tabel, tanpa pengelompokan atau DISTINCT, sering kali dapat diperbarui di PostgreSQL, MySQL, dan SQL Server, dan perubahan tersebut diteruskan ke tabel. Tampilan dengan gabungan atau agregat umumnya tidak bisa, karena database tidak bisa menentukan baris mana yang harus diubah.

SQLite tidak mengizinkan penulisan langsung ke tampilan sama sekali. Upaya kami gagal:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

Beberapa database mendukung pemicu INSTEAD OF untuk membuat tampilan dapat ditulisi, dan banyak database menawarkan WITH CHECK OPTION, yang menghentikan penulisan yang tidak akan terlihat melalui tampilan. Dalam praktiknya, sebagian besar tim menyimpan tampilan hanya-baca dan menulis ke tabel.

Ketergantungan dan kerusakan

Tampilan bergantung pada tabel tempat pembacaannya. Kita membuat tampilan pada tabel, lalu membuang tabel tersebut:

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite mengizinkan penurunan dan hanya gagal nanti, saat tampilan digunakan. Basis data lain, seperti PostgreSQL, menolak untuk menghapus tabel yang bergantung pada tampilan, kecuali Anda menambahkan CASCADE, yang juga akan menghapus tampilan tersebut. Apa pun pilihannya, mengganti nama atau menghapus kolom dapat merusak tampilan, jadi periksalah di pengujian regresi Anda setelah perubahan skema.

Ada juga jebakan halus dengan SELECT * dalam tampilan. Kami membuat CREATE VIEW star AS SELECT * FROM customers, lalu menambahkan kolom ke tabel. Di SQLite, tampilan mengambil kolom baru. Database lain, termasuk PostgreSQL, memperluas daftar kolom saat tampilan dibuat, sehingga kolom baru tidak muncul. Jadi cantumkan kolomnya secara eksplisit. Ini mendokumentasikan tampilan, dan berperilaku sama di mana pun.

Mengelola tampilan

Menghapus tampilan hanya akan menghapus kueri yang disimpan. Dalam pengujian kami, tabel customers masih memiliki ketiga baris setelah dihilangkan public_customers. Pertahankan definisi tampilan dalam kontrol versi bersama dengan migrasi Anda, sehingga perubahan ditinjau seperti kode lainnya.

Lihat vs CTE vs tabel

Kesalahan umum

• Mengharapkan peningkatan kecepatan. Tampilan normal menjalankan kuerinya setiap saat.
• Menggunakan SELECT * dalam tampilan. Perilaku berbeda antar database saat tabel berubah.
• Sarang yang dalam. Tampilan yang dibuat berdasarkan tampilan menyembunyikan gabungan yang mahal.
• Menggunakan ORDER BY di dalam tampilan. Pesanan tidak dijamin saat Anda menanyakannya, jadi urutkan di kueri luar.
• Nama yang tidak jelas. Awalan seperti v_, atau kata benda yang jelas seperti customer_sales, membantu.
• Melupakan tampilan selama migrasi. Uji tampilan tersebut setelah setiap perubahan skema.

Penutup

Tampilan adalah cara sederhana untuk memberikan logika penting ke dalam database. Gunakan ini untuk menjaga konsistensi laporan dan menyembunyikan kolom yang tidak diperlukan, bukan sebagai trik kinerja. Untuk memulai, temukan kueri yang muncul di beberapa tempat dalam kode Anda, ubah menjadi tampilan dengan kolom eksplisit, dan arahkan setiap pemanggil ke kueri tersebut.

Pertanyaan yang sering diajukan

Teruslah belajar