Using MySQL views

This guide explains how to create and use MySQL views. Views are virtual tables that store predefined queries instead of actual data. They function like regular tables and can simplify complex queries, enhance security, and abstract database structures.

Benefits of Views

Hide sensitive columns: Grant access to a view without granting access to the underlying table.
Simplify queries: Provide an abstracted interface to complex tables.
Improve maintainability: Centralize common queries for reuse.

Creating and Using Views

You can create views on any Ruachost server that uses MySQL.

Step 1: Set Up a Test Database

Create a database for testing (replace username with your account username):

CREATE DATABASE username_test;

Note: Run SQL commands via MySQL CLI or phpMyAdmin.

Select the database:

USE username_test;

Step 2: Create a Table

Create a products table:

CREATE TABLE products (
  prod_id INT NOT NULL AUTO_INCREMENT,
  prod_name VARCHAR(20) NOT NULL,
  prod_cost FLOAT NOT NULL DEFAULT 0.0,
  prod_price FLOAT NOT NULL DEFAULT 0.0,
  PRIMARY KEY(prod_id)
);

Insert sample data:

INSERT INTO products (prod_name, prod_cost, prod_price)
VALUES
  ('Basic Widget', 5.95, 8.35),
  ('Micro Widget', 0.95, 1.35),
  ('Mega Widget', 99.95, 140.00);

Step 3: Create a View

Create a view named minimumPriceView to show products costing more than 1.00:

CREATE VIEW minimumPriceView AS
SELECT prod_name
FROM products
WHERE prod_cost > 1.00;

Note

Syntax: CREATE VIEW view_name AS SELECT ...
Views appear alongside tables in your database but do not store data themselves.

Step 4: Use the View

Query the view just like a table:

SELECT * FROM minimumPriceView;

Result

+--------------+
| prod_name    |
+--------------+
| Basic Widget |
| Mega Widget  |
+--------------+

The view automatically filters the products, returning only those with prod_cost > 1.00.