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 hosting.com 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.
