View

CREATE TABLE orders (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  customer   VARCHAR(100),
  product    VARCHAR(100),
  amount     DECIMAL(10,2),
  status     VARCHAR(20) DEFAULT 'pending',
  created_at DATETIME DEFAULT NOW()
);

INSERT INTO orders (customer, product, amount, status) VALUES
  ('Болд',  'Ном',    15000, 'paid'),
  ('Оюун',  'Дэвтэр',  3500, 'pending'),
  ('Дорж',  'Үзэг',    1200, 'paid'),
  ('Болд',  'Тоглоом', 45000, 'cancelled');

-- Зөвхөн төлбөр хийгдсэн захиалгын view
CREATE VIEW paid_orders AS
SELECT id, customer, product, amount, created_at
FROM orders
WHERE status = 'paid';

-- View-г хүснэгт шиг ашиглах
SELECT * FROM paid_orders;

id | customer | product | amount | created_at
---|----------|---------|--------|----------
1  | Болд     | Ном     | 15000  | ...
3  | Дорж     | Үзэг    | 1200   | ...

-- Энэ нарийн query-г
SELECT
  u.username,
  COUNT(o.id)    AS захиалга,
  SUM(o.amount)  AS нийт,
  MAX(o.created_at) AS сүүлийн_захиалга
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.id, u.username;

-- View болгоод энгийн болгоно
CREATE VIEW user_stats AS
SELECT
  u.username,
  COUNT(o.id)       AS захиалга,
  SUM(o.amount)     AS нийт,
  MAX(o.created_at) AS сүүлийн_захиалга
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.id, u.username;

-- Хаанаас ч энгийнээр дуудна
SELECT * FROM user_stats WHERE нийт > 50000;

-- Нууц үг, картын дугаар агуулаагүй safe view
CREATE VIEW public_users AS
SELECT id, username, email, created_at
FROM users;
-- password, card_number гэх мэт баганыг оруулаагүй

-- View-г дахин тодорхойлох
CREATE OR REPLACE VIEW paid_orders AS
SELECT id, customer, product, amount, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC;

-- View устгах
DROP VIEW paid_orders;
DROP VIEW IF EXISTS paid_orders;

-- View-н тодорхойлолт харах
SHOW CREATE VIEW paid_orders;

CREATE VIEW pending_orders AS
SELECT id, customer, product, amount, status
FROM orders
WHERE status = 'pending';

-- View-р мэдээлэл өөрчлөх
UPDATE pending_orders SET status = 'paid' WHERE id = 2;
-- Үр дүн: orders хүснэгтийн id=2 мөр шинэчлэгдэнэ

-- View-н нөхцөлд нийцэхгүй өгөгдөл оруулахыг хориглох
CREATE VIEW pending_orders AS
SELECT id, customer, amount, status
FROM orders
WHERE status = 'pending'
WITH CHECK OPTION;

-- Энэ INSERT амжилтгүй болно (status='paid' нөхцөлд нийцэхгүй)
INSERT INTO pending_orders (customer, amount, status)
VALUES ('Болд', 5000, 'paid');
-- ERROR: CHECK OPTION failed

-- Stored view: CREATE VIEW-р хадгалсан, дахин дахин ашиглана
CREATE VIEW active_products AS
SELECT * FROM products WHERE stock > 0;

SELECT * FROM active_products;  -- хаанаас ч дуудна

-- Inline view (derived table): FROM дотор нэг удаа ашиглах
SELECT *
FROM (
  SELECT * FROM products WHERE stock > 0
) AS active_products
WHERE price > 10000;

-- Одоогийн database-н view-үүд
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Тодорхой view-н бүтэц
DESCRIBE paid_orders;