MySQL - views

What is a view?

Views are queries that we give a name to and can store as a virtual table. When we call or invoke a view we run the stored query, create a virtual table and the result of that query will be outputted.

CREATE VIEW complete_reviews AS 
SELECT title, release_year, genre, rating, first_name 
FROM reviews
JOIN series ON series.id = reviews.series_id
JOIN reviewers ON reviewers.id = reviews.reviewer_id;

The above query creates a virtual table (Not a real one!) that we can manipulate.

SELECT * FROM complete_reviews; 

SELECT * FROM complete_reviews WHERE genre = 'Animation';

SELECT genre, AVG(rating) FROM complete_reviews 
GROUP BY genre;

This allows us to write shorter, more readable queries, but you can't do everything with a view that you can with a normal table.

Updating views

Only a small portion of views are updatable/deletable/ insertable. If you use an aggregate function in your view, it is no longer updatable, insertable or deletable. If you use GROUP BY. If you have sub queries. If you use certain JOIN's. All these things make a view unable to update, insert or delete. A full list of what makes a view updatable/deletable/insertable is available in the docs.

CREATE VIEW formatted_series 
AS SELECT * FROM series ORDER BY release_year;

The view above will let you update and delete from the tables as it doesn't break any of the rules laid out in the docs.

Replacing and altering views

You can't change a query in a view by adding or altering it and then rerunning it. In order to alter or replace the view you need to use the CREATE OR REPLACE VIEW or ALTER VIEW syntax.

CREATE OR REPLACE VIEW formatted_series 
AS SELECT * FROM series 
ORDER BY release_year DESC;

ALTER VIEW formatted_series 
AS SELECT * FROM series ORDER BY release_year;

To delete a view you can run -

DROP VIEW formatted_series;

This deletes the view but not any of the data within the tables or the tables themselves, it only deletes the virtual table you made by running the view.