Find out which 10 articles have top views mysql năm 2024

In this article, we are going to learn about the fundamentals of Views. In this article, I am going to cover the following topics:

1. What are Database Views and the benefits of using them
2. Create, Update, and Delete a view using the queries and MySQL workbench wizard
3. Understand the concept of the MySQL Updatable views

The database views are the virtual tables that are generated by the query output. The views are considered as an object, and it can be queried using the SELECT statement. The View does not store the physical data on the database. When we run a SELECT statement on a database view, it executes the query and populates the data from the underlying tables used to create a view.

Benefits of using a MySQL view

1. The database view helps to simplify the complex business logic written in the SQL queries. Instead of executing the same complex query multiple times, you can create a view from it. This View can be referenced by using a simple SELECT query
2. The views add an extra layer of security. If you want your application not to access the base tables, you can create a view that refers to the Table you want to use. Suppose your application is using the customer table. You do not want to show the customer’s SSN details then you can create a view that populates the customer’s general information (Name, Address, contact details) grant access to the View only

MySQL View processing algorithm

The MySQL CREATE VIEW syntax has an optional clause ‘ALGORITHM.‘ This clause specifies how the views are going to be processed. The MySQL database views can be created using three algorithms.

1. MERGE algorithm
2. TEMP TABLE algorithm
3. UNDEFINED

MERGE Processing algorithm

To understand the MERGE processing algorithm, I have created a view named vw_customer using the customer table. The create view statement is the following:

create or replace ALGORITHM \= MERGE view vw_customer(store_id, fname,lname,email_id)

as

select store_id, first_name,last_name,email from customer limit 50;

Suppose to populate the data from the view; we are executing the following query:

Select * from customer WHERE store_id\=2

When we create a MySQL view using the MERGE algorithm, first, it converts the view vw_customer to the customer table. Secondly, it converts the star (*) into the list of the columns(store_id, f_name, l_name). These column names correspond to the actual columns of the underlying tables (store_id, first_name, last_name), and lastly, it applies the WHERE clause.

The resulting query that executes on the database is the following:

select store_id, first_name,last_name,email from customer where store_id\=2

• Note: When we use the MERGE algorithm in MySQL View, and if MySQL database engine is unable to process it, MySQL creates a view using ALGORITHM=UNDEFINED and generates a warning.

TEMPTABLE Processing algorithm

The TEMPTABLE algorithm is simple and easy to understand. When we create a database view using the TEMPTABLE algorithm, MySQL performs the following steps

1. It creates a temporary table to store the output generated by the SELECT statement that is used in the view definition
2. Executes the SELECT statement to insert the data into the View

UNDEFINED

When we create a view without specifying any algorithm type, MySQL uses the UNDEFINED algorithm. MySQL always chooses the MERGE algorithm over the TEMPTABLE because the performance of the views created with the MERGE algorithm is higher than the views created using the TEMPTABLE algorithm.

Create a database View

The syntax to create a database view is the following:

CREATE ALGORITHM \= MERGE | TEMPTABLE | UNDEFINED

VIEW [vw_Name] ([Column_1],[Column_2],[Column_3]…)

AS

SELECT * FROM Table;

In the syntax:

1. The Name of the View is specified after the CREATE VIEW keyword. If you are creating a view with the specific processing algorithm, you must specify the ALGORITHM keyword between the CREATE and VIEW keyword
2. The ALGORITHM keyword is used to specify the processing algorithm of the database view
3. The select statement is followed by the AS keyword

Now, let us create a database view. Suppose you want to populate the list of the films whose actor is MATTHEW. To populate the list, the query should be written as follows:

SELECT Concat(first_name, ' ', last_name) AS 'ActorName',

title AS 'MovieTitle',

release_year AS 'ReleaseYear',

length AS 'MovieLength',

c.rating AS 'MovieRating',

c.rental_rate AS 'Rental'

FROM actor a

LEFT JOIN film_actor b

ON a.actor_id \= b.actor_id

INNER JOIN film c

ON b.film_id \= c.film_id

WHERE a.first_name \= 'MATTHEW'

AND last_name \= 'JOHANSSON'

Now, to create a view using the above query, the query should be written as follows:

CREATE VIEW vw_moviesByMatthewJohanssan

as

SELECT Concat(first_name, ' ', last_name) AS 'ActorName',

title AS 'MovieTitle',

release_year AS 'ReleaseYear',

length AS 'MovieLength',

c.rating AS 'MovieRating',

c.rental_rate AS 'Rental'

FROM actor a

LEFT JOIN film_actor b

ON a.actor_id \= b.actor_id

INNER JOIN film c

ON b.film_id \= c.film_id

WHERE a.first_name \= 'MATTHEW'

AND last_name \= 'JOHANSSON'

The above query creates a database view named vw_moviesByMatthewJohanssan in the sakila database. You can use the SELECT statement to populate the result of the View. The following query is used to populate the data from the View.

Select * from vw_moviesByMatthewJohanssan

You can also use the WHERE clause while querying a database view. The following query populates the film title with ‘R’ ratings from the vw_moviesByMatthewJohanssan.

select * from vw_moviesByMatthewJohanssan Where MovieRating \='R'

As you can see in the above image, the list of movies with R ratings is populated from the view vw_moviesByMatthewJohanssan.

Alter a database View

We can change the definition of the MySQL view using CREATE OR REPLACE View statement. Suppose we want to add a rental duration column in a view, then the query should be written as follows:

CREATE OR REPLACE VIEW vw_moviesByMatthewJohanssan

as

SELECT Concat(first_name, ' ', last_name) AS 'ActorName',

title AS 'MovieTitle',

release_year AS 'ReleaseYear',

length AS 'MovieLength',

c.rating AS 'MovieRating',

c.rental_rate AS 'Rental',

c.rental_Duration AS 'RentalDuration'

FROM actor a

LEFT JOIN film_actor b

ON a.actor_id \= b.actor_id

INNER JOIN film c

ON b.film_id \= c.film_id

WHERE a.first_name \= 'MATTHEW'

AND last_name \= 'JOHANSSON'

As you can see in the above image, the new column RentalDuration has been added.

MySQL Updatable View

The MySQL views are Updatable, meaning you can execute UPDATE and DELETE queries on the database view. When we execute a DELETE and UPDATE query on the database view, the underlying tables are also updated. The queries that are used to create an updatable view must not have the following:

1. Any aggregate function, e.g., MIN, MAX, AVG, SUM, and COUNT
2. Subqueries in SELECT and WHERE clause
3. UNION or UNION ALL
4. Outer Join or Left Joins
5. HAVING and GROUP BY Clause

Moreover, the updatable View cannot refer to the non-updatable View, and you cannot update the View that has been created using the TEMPTABLE algorithm. The updatable views must be created using a table that has a primary key column.

First, let us create a simple database view named vw_actor using the actor table.

create view vw_actor (actor_id,first_name,last_name)

as

select actor_id, first_name, last_name from actor;

You can run the following query to verify that the view is updatable or not.

select table_catalog, table_schema, IS_UPDATABLE from information_schema.views where table_schema\='sakila';

Let us delete the same record from the vw_actor. Run the following statement:

delete from vw_actor where actor_id\=9;

As you can see, the record has been deleted.

Manage views using MySQL Workbench

To create a view using MySQL workbench, we are creating a view named vw_films using the film table of the sakila database. First, Expand Sakila schema Right-click on Views Click on Create View.

A New View pan opens Enter the following query in the New View pane Click on Apply.

CREATE VIEW `vw_films` AS Select * from films;

Screenshot:

A dialog box Apply SQL Script to Database opens. In the dialog box, you can review the query that is used to create a vw_films view. You can choose the view processing algorithm from the Algorithm drop-down box. You can choose the type of the lock from the Lock Type drop-down box the chosen lock will be placed on the tables that are used to create a view. Click on Apply.

The script will be executed successfully.

Screenshot:

Screenshot of the MySQL Workbench Navigator:

As you can see that a new database view has been created under the Views folder in MySQL workbench.

Drop a View

To drop the View, you can use the DROP VIEW statement. The syntax is the following:

To drop the vw_films View, execute the following query:

To drop the View using MySQL Workbench, expand Views Right-click on vw_films Click on the Delete View.

On the Drop View confirmation dialog box, click on Drop Now.

The View will be deleted.

Summary

In this article, we learned the basics of MySQL views. I have covered the following topics.

1. Database Views and the benefits of using them.
2. Create, Update, and Delete a view using the queries and MySQL workbench wizard.
3. Understand the concept of the MySQL Updatable views.

In the next article, we are going to learn about MySQL functions.

Table of contents

Learn MySQL: Querying data from MySQL server using the SELECT statement Learn MySQL: What is pagination Learn MySQL: Sorting and Filtering data in a table Learn MySQL: Add data in tables using the INSERT statement Learn MySQL: Create and drop temp tables Learn MySQL: Delete and Update Statements Learn MySQL: The Basics of MySQL Stored Procedures Learn MySQL: The Basics of MySQL Views Learn MySQL: An overview of MySQL Binary Logs Learn MySQL: An overview of the mysqlbinlog utility Learn MySQL: Run multiple instances of MySQL Server on Windows 10 Learn MySQL: MySQL String Functions Learn MySQL: Control Flow functions Learn MySQL: Install MySQL server 8.0.19 using a noinstall Zip archive Learn MySQL: MySQL Copy table

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on [email protected]

How to find top 10 records in MySQL?

Explanation: SELECT * FROM Customers ORDER BY Order_val DESC LIMIT 10: This is a subquery that selects all columns (*) from the Customers table, sorts the records in descending order (DESC) by the Order_val column, and limits the result to the top 10 records (LIMIT 10)nullMySQL Query to Select Top 10 Records? - GeeksforGeekswww.geeksforgeeks.org › mysql-query-to-select-top-10-recordsnull

How to get list of views in MySQL?

Click on the database name that you want to select. The right hand pane should change with the list of all tables in the selected database. Click on Views tab at the top to list all the views in the database.nullMySQL query to find all views in a database - Geeks Worldwidegeeksww.com › database_management_systems › mysql › tips_and_tricksnull

How to delete top 10 records in MySQL?

It is used in the DELETE LIMIT statement so that you can order the results and target those records that you wish to delete. It specifies a limited number of rows in the result set to delete based on row_count. For example, LIMIT 10 would delete the first 10 rows matching the delete criteria.nullMySQL: DELETE LIMIT Statement - TechOnTheNetwww.techonthenet.com › mysql › delete_limitnull

How to show top 50 rows in MySQL?

Here's the syntax to select top N rows in MySQL. In the above statement, we list the columns column1, column2, … that you want to select in your query. Also, you need to specify LIMIT n after the table name, where n is the number of rows you want to select. The above query will select top n records in your table.nullMySQL Select Top N Rows - Ubiq BIubiq.co › database-blog › mysql-select-top-n-rowsnull