MySQL CREATE View

Introduction

In this chapter, we will learn how to use the CREATE VIEW statement in MySQL. A view is a virtual table based on the result set of an SQL query. It provides a way to encapsulate complex queries into a single virtual table that can be queried like a regular table. Views can simplify query management, improve security by restricting access to specific data, and enhance readability of complex queries. We will cover the syntax, examples, and important considerations for using the CREATE VIEW statement.

Creating a View

To create a view, we use the CREATE VIEW statement followed by the view name and the AS keyword, which precedes the select query that defines the view.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition;

• view_name: The name of the view you want to create.
• column1, column2, ..., columnN: The columns to include in the view.
• table_name: The name of the table(s) from which to select data.
• condition: The condition to filter the rows in the view (optional).

Example

CREATE VIEW employee_details AS
SELECT first_name, last_name, email
FROM employees
WHERE hire_date > '2023-01-01';

This example creates a view named employee_details that includes the first_name, last_name, and email columns from the employees table for rows where the hire_date is after January 1, 2023.

Querying a View

Once a view is created, you can query it like a regular table using the SELECT statement.

Example

SELECT * FROM employee_details;

This example retrieves all columns and rows from the employee_details view.

Full Example

Let’s go through a full example where we create a table, insert data into it, create a view, and query the view.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

5. Create a View:

CREATE VIEW recent_hires AS
SELECT first_name, last_name, email, hire_date
FROM employees
WHERE hire_date > '2023-07-01';

6. Query the View:

SELECT * FROM recent_hires;

Output

Important Considerations

• Performance: Views do not store data themselves; they store the query that defines them. The performance of a view depends on the complexity of the query and the underlying table structures.
• Updatability: Not all views are updatable. Simple views based on a single table without aggregate functions or joins are more likely to be updatable.
• Security: Views can be used to restrict access to specific columns or rows of a table, enhancing data security.

Conclusion

Creating views in MySQL can simplify complex queries, improve security, and enhance readability. This chapter covered how to use the CREATE VIEW statement to create views and how to query the view.