# Using MySQL views

> Create MySQL views, virtual tables built from saved queries, to simplify complex queries and limit access to data, with an example.

Source: https://helpcenter.ruachost.com/developers/databases/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`.

## **More Information**

- [MySQL Views Documentation](http://dev.mysql.com/doc/refman/5.0/en/views.html)
- [CREATE VIEW Statement](http://dev.mysql.com/doc/refman/5.0/en/create-view.html)