MySQL VIEWs

MySQL views are virtual tables that are created based on the result of a SELECT query. 

In other words, a view is a stored query that you can treat as a table. When you create a view, you define the SELECT statement that will be used to populate the view.

The main purpose of views is to simplify the complexity of a database by allowing users to access only the information they need, without giving them direct access to the underlying tables. 

Views can also be used to combine data from multiple tables or to restrict access to sensitive data.

Once a view is created, you can query it just like any other table, using SELECT statements

MySQL views can be created using the CREATE VIEW statement, and can be modified or dropped using the ALTER VIEW and DROP VIEW statements, respectively. 

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

CREATE VIEW example:

mysql> CREATE VIEW Prices_Higher_Than_100 AS
    -> SELECT store_name, product, available, price
    -> FROM Stores
    -> WHERE Price > 100;
Query OK, 0 rows affected (0.42 sec)

mysql>

The query creates a view named "Prices_Higher_Than_100" that selects data from a table named "Stores". 

The view includes four columns: "store_name", "product", "available", and "price". 

The view only includes records where the "price" column is greater than 100.

Once this view is created, it can be queried like any other table in the database. 

Tables in our database, with our view:

mysql> SHOW TABLES FROM Corp;
+------------------------+
| Tables_in_corp         |
+------------------------+
| documents              |
| internalcontrol        |
| prices_higher_than_100 |
| stores                 |
+------------------------+
4 rows in set (0.02 sec)

mysql>

In this case, there are four tables: "documents", "internalcontrol", "prices_higher_than_100", and "stores".

This command is useful to quickly get an overview of the tables in a database, and to ensure that a particular table or view exists before executing queries or commands that reference it.

When we run our view:

mysql> SELECT * FROM prices_higher_than_100;
+------------+-----------+-----------+-------+
| store_name | product   | available | price |
+------------+-----------+-----------+-------+
| Berlin_1   | Green PSU |        50 |   120 |
+------------+-----------+-----------+-------+
1 row in set (0.00 sec)

mysql>

The query retrieves all records from the view, which only includes records where the "price" column is greater than 100.

The result set returned by the query includes one record that matches the criteria specified in the view's SELECT statement. The values in these columns correspond to the data in the underlying "Stores" table, but only for records where the "price" column is greater than 100.

Our query demonstrates how views can be used to simplify database access and retrieve only the data that meets specific criteria.