MySQL Views: Create, Alter, Drop, and Use Cases

Introduction

MySQL is a very popular database widely used for managing data. It is an open-source platform introduced by MySQL AB and currently managed by Oracle Corporation. This platform is known for its performance, ease of use, and represents data in a structured format, which is easily readable by providing data in a tabular format represented with rows and columns.

MySQL views are one of the most powerful tools available to database administrators and developers. A view in MySQL acts like a virtual table that is derived from one or more tables. It simplifies queries and enhances security.

In this article, we will discuss views in MySQL in detail – covering what they are, how to create, alter, and drop them, along with working examples showing cases where views are highly used.

Key Characteristics and uses of SQL views

Given below is the list of main characteristics and use cases of SQL views.

1.      Simplification of Queries: MySQL views simplify complex queries by hiding the underlying complexity of joins, aggregations, and subqueries instead of writing lengthy and intricate queries.

2.      Data Security: Views make it restricted for accessing the sensitive rows and columns for unauthorized users. For example, students can only see their score table through a view instead of accessing the whole table.

3.      Logical Data Independence: Changes made in the underlying database structure do not necessarily affect users if they interact with the database through views.

4.      Reusability: Views can be reused across multiple queries and thus saving development time and effort. This is achieved by encapsulating complex queries, data transformation, or UI elements into a single, named entity that can be invoked or included in various parts of the application, and thus simplifying maintenance and improving consistency.

5.      Readability and Maintenance: Queries in MySQL become easier to read and maintain when encapsulated in a view by simplifying complex joins and calculations, encapsulating business logic, and abstracting the underlying database structure.

Creating Views in MySQL

The keyword CREATE VIEW is used to define a new view. The basic syntax of creating a view in MySQL is given below.

SQL Syntax for creating view

CREATE VIEW view_name AS

SELECT column1, column2, …

FROM table_name

WHERE condition,

Basic view in MySQL

You are given a table named employees.

emp_id 1 2 3

Name Harry Peter Suraj

department HR IT IT

salary 55000 62000 60000

You need to write MySQL to display the names and salaries of IT employees,

MySQL Query to display the above result

CREATE VIEW it_employees AS

SELECT name, salary

FROM employees

WHERE department = ‘IT’;

 

Output: The output of above MySQL query is given below

name Peter Suraj

salary 62000 60000

 

Altering Views in MySQL

The ALTER VIEW keyword is used to modify a view in MySQL. The syntax for altering a view in MySQL is given below.

SQL Syntax for altering a view

ALTER VIEW view_name AS

SELECT column1, column2, …

FROM table_name

WHERE condition,

 

Example: You need to add the employee ID in the resultant table.

MySQL Query to include the employee ID in the it_employees view.

ALTER VIEW it_employees AS

SELECT emp_id, name, salary

FROM employees

WHERE department = ‘IT’;

 

Output: The output of the above MySQL query for altering the table is given below.

emp_id 2 3

name Peter Suraj

salary 62000 60000

 

Dropping Views in MySQL

If there is no need for a particular view in the database, you can remove it using the DROP VIEW statement. Given below is the syntax and example of dropping views in MySQL.

Syntax

DROP VIEW view_name;

 

Example: We use the same table that we have used in creating views in MySQL

DROP VIEW it_employees;

The above query will permanently remove the it_employees view from the database.

Using Views in MySQL

The purpose of using views in MySQL is to simplify complex queries as views allow you to encapsulate (enclose something in a capsule) complex SELECT statements, including joins, aggregations, and subqueries, into a single virtual table.

Views are also used for enhancing security as they can restrict access to sensitive data by only exposing the useful information from the underlying tables to the user. Given below is the syntax of using views in MySQL.

Syntax

SELECT * FROM student_table     

You can also perform operations like applying conditions, joins, and aggregations to views. Given below is an example of displaying students’ names stored in a table who have scored more than 75% marks in an examination.

Table: Student

S.No 1 2 3 4 5 6

Name Vikram Shubham Aditi Nitya Alok Vivek

%Marks 67 78 56 87 92 54

 

MySql Query

SELECT * FROM Student

SELECT Name

FROM Student

WHERE marks > 75;

                                                                       

Output: The output of the above MySQL query is given below.

 

Name Shubham Nitya Alok

 

Limitations of Views in MySQL

Views are very powerful tool in MySQL, but they come with certain limitations. Given below is the list of limitations of views in MySQL.

1)     No Indexes: Indexes can not be created directly on a view, as views are virtual tables, and indexes are physical structures associated with base tables. Views depend on the indexes of the underlying tables 

2)     Performance Issues: When the database is very large with complex queries involved in it, views might give slow performance since they execute the underlying SQL each time they are queried.

3)     Read-only View: There are some views that are not updatable, such as those involving joins, group by, aggregate functions, or distinct clauses. Views that include elements that prevent a direct, unambiguous mapping between changes made to the view and the corresponding changes in the underlying base tables.

4)     Dependency Management: Since the view is dependent on the underlying table. If there is any change in the table, the view may no longer work. MySQL does not automatically fix the view, and instead, it may throw an error when you try to query it, and thus view is limited to dependency management.   

 

Practical Use Cases of Views in MySQL

Views are an important concept in MySQL, and they have wide-range applications in real-world database systems. Given below is the list of practical use cases of views in MySQL.

1.      Simplifying Complex Queries: Managing a large database system often involves queries having multiple joins and subqueries. By creating a view, you can store the complex queries once and allow users to fetch data easily.

2.      Enhancing Security: If the data stored in a table contains sensitive data, you can hide that part of the table by creating a view. Users with limited permissions can access only the useful part of a table instead of fetching the whole table.

3.      Providing Customized Data: Different users need to modify different columns in a database table. For example, in an employment table, Hr needs to update names and departments in a table, whereas finance may require updating names and salaries in a database table. Views allow you to customize each subset for each queue.

4.      Reusing Aggregations: If the reports are generated frequently using the same aggregated data, creating a view reduces repetitions.

 

Conclusion

MySQL view is an important concept in MySQL as they provides a better way to simplify queries, improve security, and enhance data abstraction. Views act as virtual tables that allow developers and users to interact with data more easily without dealing with the complexities of the underlying schema.

With commands like CREATE, ALTER, and DROP, managing the views has become straightforward. This article describes views in MySQL with their examples in real-life and with their limitations.

If you are looking for such kind of information, I suggest you visit the Tpoint Tech Website, a platform where you get complete courses from basic to advanced for different programming languages such as Java, Python, JavaScript, etc.

This website is useful for both students and working professionals as all the topics are explained with working examples and in great detail. You will also get an online compiler on this website to test and run your code.