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.
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
If you have sub queries.
If you use certain
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.