MariaDB Tutorial / Save Reusable Query Definitions with VIEW

A VIEW stores a SELECT definition as a named database object that other statements can query. An ordinary view does not separately store a copy of its result rows; inspect its security context, dependencies and updateability instead of treating it as a cached table.

Distinguish a stored definition from a one-statement CTE

A paid-order goods-total calculation reused by reports can be represented as a view. A CTE names a result within one statement, while a created view is available to later statements. Design its exposed columns and names according to the queries that will depend on it. Compare Break Queries into Named Steps with WITH and CTEs for names needed within one statement; review who may execute a stored view using the account/privilege scope in Create Users and Grant Only Required Privileges.

Check the MariaDB documentation for the syntax and conditions used here: create view.

Prepare the base tables and a separate view name

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c34_customers, c34_products, c34_orders, c34_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c34_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c34_order_customer FOREIGN KEY (customer_id)
  REFERENCES c34_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c34_item_order FOREIGN KEY (order_id)
  REFERENCES c34_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c34_item_product FOREIGN KEY (product_id)
  REFERENCES c34_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c34_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c34_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c34_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c34_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Also inspect SHOW FULL TABLES to ensure c34_paid_order_totals does not already exist. This is a first-time exercise, so do not use CREATE OR REPLACE to overwrite an unreviewed definition. Before changing an existing view, preserve its current SHOW CREATE VIEW definition.

Create and query an INVOKER view

CREATE SQL SECURITY INVOKER VIEW c34_paid_order_totals AS
SELECT o.order_id,o.customer_id,o.ordered_at,
 SUM(i.quantity*i.unit_price) AS goods_total
FROM c34_orders o JOIN c34_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.customer_id,o.ordered_at;

SELECT order_id,goods_total FROM c34_paid_order_totals ORDER BY order_id;
SHOW CREATE VIEW c34_paid_order_totals;

The order totals are 101:11500.00, 102:8000.00 and 103:19500.00. Specify ORDER BY when using the view rather than rely on an internal order. SQL SECURITY INVOKER checks underlying access using the invoking account’s privileges, so that account also needs the required source privileges.

Observe source changes and rollback in the same connection

START TRANSACTION;
UPDATE c34_items SET quantity=3 WHERE order_id=101 AND product_id=1;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;
ROLLBACK;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;

Increasing product 1’s quantity in order 101 from two to three changes the queried view total to 15000.00 inside the transaction. ROLLBACK restores 11500.00. The calculation reads changed base data rather than updating an independent historical copy in the view. Visibility from another connection depends on isolation and commit state.

Review security context, updateability and performance

This view aggregates with GROUP BY and SUM; do not treat it as a route for arbitrary source-row updates. Some simple views are updateable, but that does not make every view updateable. Separate base-data modification paths from aggregate reporting.

A DEFINER view depends on a defining account and its security context. Restoring definitions into an environment where that account is absent can cause failures. Review these attributes during backup and restore. Creating a view does not automatically cache its result or improve speed; inspect the execution plan.

Change definitions carefully and remove the view first

Renaming or removing columns can break dependent queries. Expose explicit columns rather than SELECT * and review consumers before altering the definition. Remove the view before dropping its source tables. DROP VIEW itself does not delete this fixture’s base rows.

DROP VIEW shopcourse.c34_paid_order_totals;

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c34_items;
DROP TABLE shopcourse.c34_orders;
DROP TABLE shopcourse.c34_products;
DROP TABLE shopcourse.c34_customers;