Mastering MySQL Views From Basic to Advanced Concepts

Leveling Up Database Architecture: Mastering MySQL Views From Basic to Advanced Concepts

ကျွန်တော်တို့ ရှုပ်ထွေးလှတဲ့ Database Queries တွေကို နေ့တိုင်း Application ထဲမှာ ပုံစံတူတွေ ထပ်ခါတလဲလဲ ရေးမယ့်အစား Virtual Table အဖြစ် ပြောင်းလဲပေးနိုင်တဲ့ MySQL Views အကြောင်းကို သင်တန်း Lab ကနေ ထဲထဲဝင်ဝင် လက်တွေ့ လေ့လာခွင့်ရခဲ့ပါတယ်။

ဒီ Lab မှာ departments နဲ့ employees ဆိုတဲ့ Table နှစ်ခုပေါ်မှာ အခြေခံပြီး ရိုးရိုး View တည်ဆောက်ပုံကစလို့ လုပ်ငန်းခွင်သုံး Security တွေ၊ Query Processing Algorithms တွေအထိ တစ်ဆင့်ချင်းစီကို စနစ်တကျ လေ့လာခဲ့ရပါတယ်။

💡 ဒီ Lab ကနေ အဓိက သင်ယူတတ်မြောက်ခဲ့ရတဲ့ Core Database View Concepts များ:

Multi-purpose View Types: * selected columns တွေကိုပဲ စစ်ထုတ်ပြတဲ့ Simple View (vw_employee_basic)

Table တွေကို အချင်းချင်း ချိတ်ဆက်ပြသပေးတဲ့ Join View (vw_employee_department)

လုပ်ငန်းခွင်သုံး Management Reports တွေအတွက် တွက်ချက်မှုတွေ စုစည်းပေးထားပြီး Read-Only သာဖြစ်တဲ့ Aggregate View (vw_department_salary_summary) တို့ရဲ့ တည်ဆောက်ပုံ ခြားနားချက်များ။


Data Validation via View (WITH CHECK OPTION):

View တစ်ခုကတစ်ဆင့် မူရင်း Table ထဲက Data ကို သွားပြင်တဲ့အခါ သတ်မှတ်ချက်နဲ့ မကိုက်ညီရင် ပေးမပြင်ဘဲ Error (Error Code: 1369) ပြပြီး ပိတ်ထားနိုင်တဲ့ စနစ်။


၎င်းစနစ်မှာ အခြေခံ View တွေရဲ့ စည်းကမ်းချက်တွေကိုပါ ဆင့်ကဲစစ်ဆေးပေးတဲ့ CASCADED behavior နဲ့ လက်ရှိ View ရဲ့ စည်းကမ်းချက်ကိုပဲ စစ်တဲ့ LOCAL check option တို့ရဲ့ လုပ်ဆောင်ချက် ကွဲပြားပုံ။


Internal Query Optimization (ALGORITHM options):

Outer query နဲ့ View query ကို ပေါင်းစပ်ပြီး Query မြန်အောင်လုပ်ပေးတဲ့ MERGE အလုပ်လုပ်ပုံ။


Aggregation တွေပါရင် နောက်ကွယ်မှာ ယာယီ Table တစ်ခု Auto ဆောက်ပြီး အလုပ်လုပ်တဲ့ TEMPTABLE algorithm များ။


Access Control & Security (SQL SECURITY):

View ကို ဖန်တီးခဲ့တဲ့သူ (Creator) ရဲ့ Permission အတိုင်း Run ပေးမယ့် DEFINER နဲ့ View ကို လှမ်းခေါ်သုံးတဲ့သူ (Caller) ရဲ့ Permission အတိုင်း Run မယ့် INVOKER တို့ကို ခွဲခြားသတ်မှတ်ပြီး Data Security မြှင့်တင်ပုံ။

ဒီသင်ခန်းစာကနေတစ်ဆင့် View တွေဟာ ကုဒ်တွေကို ရိုးရှင်းအောင် ကူညီပေးရုံတင်မကဘဲ လစာ (Salary) လိုမျိုး Sensitive ဖြစ်တဲ့ Columns တွေကို ကာကွယ်ပြီး ဥပမာ- vw_employee_public လိုမျိုး အများမြင်သင့်တဲ့ Data ကိုပဲ ခွဲထုတ်ပြသပေးတဲ့ Data masking (Data Security) အတွက် အလွန်အသုံးဝင်ကြောင်း လက်တွေ့ကျကျ သိရှိခဲ့ရပါတယ်။

လေ့လာစမ်းသပ်ဖြစ်ခဲ့တဲ့ အဆင့်မြင့် MySQL View Lab Script အပြည့်အစုံကို စိတ်ဝင်စားသူများ လေ့လာနိုင်ဖို့ ကျွန်တော်ရဲ့ GitHub မှာ စနစ်တကျ သိမ်းဆည်းထားပါတယ်ဗျာ။ ⬇️

🔗 GitHub Repository: Download Source Code

-- ===================================================
-- MySQL VIEW DEMO SCRIPT
-- Features:
-- Simple View
-- Complex View
-- Join View
-- Aggregation View
-- View with WHERE
-- View with WITH CHECK OPTION
-- Updatable View
-- Read-only View
-- OR REPLACE View
-- ALGORITHM option
-- SQL SECURITY option
-- DROP View
-- ===================================================

CREATE DATABASE view_demo;
USE view_demo;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 1. CREATE TABLES
-- ===================================================

CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(100) NOT NULL,
gender VARCHAR(10),
salary DECIMAL(10,2),
dept_id INT,
status VARCHAR(20),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 2. INSERT SAMPLE DATA
-- ===================================================

INSERT INTO departments (dept_name) VALUES
('HR'),
('IT'),
('Finance'),
('Sales');

INSERT INTO employees (emp_name, gender, salary, dept_id, status) VALUES
('Aung Aung', 'Male', 800000, 1, 'Active'),
('Su Su', 'Female', 950000, 2, 'Active'),
('Kyaw Kyaw', 'Male', 700000, 2, 'Inactive'),
('Hla Hla', 'Female', 1200000, 3, 'Active'),
('Mg Mg', 'Male', 600000, 4, 'Active');

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 3. SIMPLE VIEW
-- Shows selected columns only
-- ===================================================

CREATE VIEW vw_employee_basic AS
SELECT emp_id, emp_name, salary
FROM employees;

SELECT * FROM vw_employee_basic;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 4. VIEW WITH WHERE CONDITION
-- Shows only active employees
-- ===================================================

CREATE VIEW vw_active_employees AS
SELECT emp_id, emp_name, salary, status
FROM employees
WHERE status = 'Active';

SELECT * FROM vw_active_employees;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 5. JOIN VIEW
-- Combines employees and departments
-- ===================================================

CREATE VIEW vw_employee_department AS
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

SELECT * FROM vw_employee_department;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 6. AGGREGATE VIEW
-- Shows department salary summary
-- This view is read-only
-- ===================================================

CREATE VIEW vw_department_salary_summary AS
SELECT
d.dept_name,
COUNT(e.emp_id) AS total_employees,
SUM(e.salary) AS total_salary,
AVG(e.salary) AS average_salary,
MAX(e.salary) AS highest_salary,
MIN(e.salary) AS lowest_salary
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

SELECT * FROM vw_department_salary_summary;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 7. VIEW WITH ORDER BY
-- Note: ORDER BY in view is not always guaranteed
-- unless used with LIMIT or ordered again in SELECT
-- ===================================================

CREATE VIEW vw_high_salary_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary >= 800000
ORDER BY salary DESC;

SELECT * FROM vw_high_salary_employees;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 8. UPDATABLE VIEW
-- Simple view from one table can be updated
-- ===================================================

CREATE VIEW vw_employee_salary_update AS
SELECT emp_id, emp_name, salary
FROM employees;

-- Update data through view
UPDATE vw_employee_salary_update
SET salary = 850000
WHERE emp_id = 1;

SELECT * FROM employees;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 9. VIEW WITH CHECK OPTION
-- Prevents inserting/updating rows that violate view condition
-- ===================================================

CREATE VIEW vw_active_employee_check AS
SELECT emp_id, emp_name, salary, status
FROM employees
WHERE status = 'Active'
WITH CHECK OPTION;

-- Valid update
UPDATE vw_active_employee_check
SET salary = 1000000
WHERE emp_id = 2;

SELECT * FROM vw_active_employee_check;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- Invalid update example
-- This will fail because status becomes Inactive
UPDATE vw_active_employee_check
SET status = 'Inactive'
WHERE emp_id = 2;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- error message
-- Error Code: 1369. CHECK OPTION failed 'view_demo.vw_active_employee_check'

-- ===================================================
-- 10. CASCADED CHECK OPTION
-- Default behavior
-- Checks conditions of this view and underlying views
-- ===================================================

CREATE VIEW vw_high_paid_active AS
SELECT emp_id, emp_name, salary, status
FROM vw_active_employee_check
WHERE salary >= 800000
WITH CASCADED CHECK OPTION;

SELECT * FROM vw_high_paid_active;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- don't update, follow underlying Check Option (stauts=active)
UPDATE vw_high_paid_active
SET salary = 999
WHERE emp_id = 3;

UPDATE vw_high_paid_active
SET salary = 999
WHERE emp_id = 1;

vw_high_paid_active ဆိုတဲ့ View မှာ သတ်မှတ်ချက် ၂ ခု ရှိနေပါတယ် (ဘာလို့လဲဆိုတော့ သူက vw_active_employee_check ကို အခြေခံပြီး ဆောက်ထားပြီး CASCADED လို့ ပြောထားလို့ပါ)။

ပထမအခြေအနေ (အခြေခံ View မှ): ဝန်ထမ်းရဲ့ status သည် 'Active' ဖြစ်ရမည်။
ဒုတိယအခြေအနေ (လက်ရှိ View မှ): ဝန်ထမ်းရဲ့ salary သည် 800000 နှင့်အထက် (>= 800000) ဖြစ်ရမည်။

WITH CASCADED CHECK OPTION ကို သုံးထားတဲ့အတွက် ဒီ View ထဲကနေတစ်ဆင့် Data ကို သွားပြင်တဲ့အခါ ဒီအခြေအနေ ၂ ခုလုံးနဲ့ ကိုက်ညီနေမှသာ MySQL က ပြင်ခွင့်ပေးမှာ ဖြစ်ပါတယ်။

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- Error Code: 1369. CHECK OPTION failed 'view_demo.vw_high_paid_active'
-- check current Check Option and underlying check option

-- ===================================================
-- 11. LOCAL CHECK OPTION
-- Checks only current view condition
-- ===================================================

CREATE VIEW vw_local_check_example AS
SELECT emp_id, emp_name, salary, status
FROM vw_active_employee_check
WHERE salary >= 700000
WITH LOCAL CHECK OPTION;

SELECT * FROM vw_local_check_example;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

INSERT INTO vw_local_check_example
VALUES(6,'Mg Mg Aung', 760000, 'Active');

SELECT * FROM vw_local_check_example;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

SELECT * FROM vw_active_employee_check;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 12. CREATE OR REPLACE VIEW
-- Modify existing view without dropping manually
-- ===================================================

CREATE OR REPLACE VIEW vw_employee_basic AS
SELECT emp_id, emp_name, gender, salary
FROM employees;

SELECT * FROM vw_employee_basic;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 13. ALGORITHM = MERGE
-- MySQL tries to merge view query into outer query
-- Good for simple views
-- ===================================================

CREATE OR REPLACE
ALGORITHM = MERGE
VIEW vw_merge_employee AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 700000;

SELECT * FROM vw_merge_employee;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 14. ALGORITHM = TEMPTABLE
-- MySQL creates temporary table for view result
-- Usually read-only
-- ===================================================

CREATE OR REPLACE
ALGORITHM = TEMPTABLE
VIEW vw_temp_department_summary AS
SELECT dept_id, COUNT(*) AS total_employee
FROM employees
GROUP BY dept_id;

SELECT * FROM vw_temp_department_summary;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 15. ALGORITHM = UNDEFINED
-- MySQL decides best algorithm
-- ===================================================

CREATE OR REPLACE
ALGORITHM = UNDEFINED
VIEW vw_undefined_employee AS
SELECT emp_id, emp_name, salary
FROM employees;

SELECT * FROM vw_undefined_employee;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 16. SQL SECURITY DEFINER
-- View runs using permissions of creator
-- ===================================================

CREATE OR REPLACE
SQL SECURITY DEFINER
VIEW vw_security_definer AS
SELECT emp_id, emp_name, salary
FROM employees;

SELECT * FROM vw_security_definer;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 17. SQL SECURITY INVOKER
-- View runs using permissions of user who calls it
-- ===================================================

CREATE OR REPLACE
SQL SECURITY INVOKER
VIEW vw_security_invoker AS
SELECT emp_id, emp_name, salary
FROM employees;

SELECT * FROM vw_security_invoker;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 18. VIEW FOR DATA SECURITY
-- Hide sensitive columns
-- Example: users can see employee name but not salary
-- ===================================================

CREATE VIEW vw_employee_public AS
SELECT emp_id, emp_name, gender, status
FROM employees;

SELECT * FROM vw_employee_public;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 19. VIEW FOR REPORTING
-- Monthly or management reporting style
-- ===================================================

CREATE VIEW vw_management_report AS
SELECT
d.dept_name,
COUNT(e.emp_id) AS employee_count,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

SELECT * FROM vw_management_report;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 20. READ-ONLY VIEW EXAMPLES
-- These views cannot normally be updated:
-- GROUP BY, DISTINCT, JOIN, UNION, aggregate functions
-- ===================================================

CREATE VIEW vw_distinct_status AS
SELECT DISTINCT status
FROM employees;

SELECT * FROM vw_distinct_status;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- This will fail because aggregate view is read-only
UPDATE vw_department_salary_summary
SET average_salary = 1000000
WHERE dept_name = 'IT';

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 21. SHOW VIEW INFORMATION
-- ===================================================

SHOW FULL TABLES WHERE Table_type = 'VIEW';

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

SHOW CREATE VIEW vw_employee_basic;

📸 Screenshot ကြည့်ရန် နှိပ်ပါ

-- ===================================================
-- 22. DROP VIEW
-- ===================================================

DROP VIEW IF EXISTS vw_employee_public;

-- ===================================================
-- END OF SCRIPT
-- ===================================================