SQL and MySQL course Β· Module 10: Transactions and advanced SQL
Views (VIEW) - a saved look
In this lesson4
Sometimes we repeat a certain query over and over - for example "show available books". Instead of writing it each time, we can save it under a name. That is a view (VIEW).
What is a view?
A view is a saved SELECT query that can be used as if it were an ordinary table. A view stores no data - each time it reaches into the real tables and shows the current result.
Creating a view
CREATE VIEW dostepne_ksiazki AS
SELECT * FROM ksiazki WHERE dostepna = 1;From now on the Keeper can ask the Library briefly:
SELECT * FROM dostepne_ksiazki;and will always get a fresh list of books ready to be borrowed, without repeating the condition WHERE dostepna = 1.
Why do we need views?
- Readability - a complex query is hidden behind a simple name.
- Convenience - written once, used many times.
- Security - we can show a reader only the view, hiding sensitive columns.
Dropping a view
DROP VIEW dostepne_ksiazki;A view is like a ready spyglass on the tower - once set, it always shows what is needed.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What is a view (VIEW) in MySQL?
2. Which syntax creates a view in MySQL?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Create a view named dostepne_ksiazki that shows all columns from the ksiazki table, but only those rows where the dostepna column equals 1. Use CREATE VIEW ... AS SELECT ...
- Horizontal ordering
Arrange in one line the beginning of the dostepne_ksiazki view definition:
- Code editor
Create a view named drogie_ksiazki that shows the tytul and cena columns from the ksiazki table, but only for rows where cena is greater than 50. Use CREATE VIEW ... AS SELECT ...
- Code editor
Write a query that retrieves all columns and all rows from the dostepne_ksiazki view.