Views

name: portada
layout: true
class: portada-slide, middle, right
---
# Views
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Views

A __view__ is the result set of a stored query on the data, which the database users can query
just as they would in a persistent database collection object.

A view does not form part of the physical schema: as a result set, it is a virtual table computed or
collated dynamically from data in the database when access to that view is requested.

.center[[Wikipedia View (SQL)](https://en.wikipedia.org/wiki/View_(SQL%29)]
.center[[MariaDB Views](https://mariadb.com/kb/en/creating-using-views/)]

```
CREATE VIEW student_view AS
    SELECT s.DNI, p.name FROM student s
    INNER JOIN person p ON s.DNI = p.DNI;

SELECT * FROM student_view;
+-----------+----------+
| DNI       | name     |
+-----------+----------+
| 32409818N | Marta    |
| 40189039N | Enric    |
|          ...         |
+-----------+----------+
```

---
# Uses of Views
- __Simplifying__ our application's SQL queries.
- __Restricting data access__: Hide some fields in a table in case of a security issue in the application (SQL injection).
- __Row-level security__: We can also define separate views to include a specific WHERE clause for security.
- __Pre-emptive optimization__: We can also define our views in such a way less-experienced developers don't
  run the risk of running un-optimized queries or JOINs that result in full-table scans and extended locks.
  Expensive queries, queries that SELECT *, and poorly thought-out JOINs can not only slow down the database entirely,
  but can cause inserts to fail, clients to time out, and reports to error out.
- __Abstracting Tables__: We may be installing a new application with different requirements alongside a legacy application.
  Unfortunately, database redesign will tend to break backwards-compatibility. 
  Using views, we can change the format of the underlying tables while 
  still presenting the same table format to the legacy application. 
---
# Create View

The `CREATE VIEW` statement creates (or replaces) a view in the selected database from a `SELECT` statement.

- `CREATE OR REPLACE VIEW` is equivalent to `ALTER VIEW` if the view exists.

```
CREATE [OR REPLACE]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement
```
.center[[MariaDB Create View](https://mariadb.com/kb/en/create-view/)]

- The `SELECT` statement can retrieve data from a single table, multiple tables or other views.
- Views must have unique column names with no duplicates, just like base tables. By default, 
  the names of the columns retrieved by the `SELECT` statement are used for the view column names. 
  To define explicit names for the view columns, the optional `column_list`.
- `ORDER BY` is allowed in a view definition, but it is ignored if you 
  select from a view using a statement that has its own `ORDER BY`.

---
# Create View Restrictions
A view definition is subject to the following restrictions:
- The `SELECT` statement cannot contain a subquery in the `FROM` clause.
- The `SELECT` statement cannot refer to system or user variables.
- Any table or view referred to in the definition must exist. However, after a view has been created,
  it is possible to drop a table or view that the definition refers to.
  In this case, use of the view results in an error.
- The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
- You cannot associate a trigger with a view.

---
# Drop View
`DROP VIEW` removes one or more views.

```
DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
```
.center[[DROP VIEW MariaDB](https://mariadb.com/kb/en/drop-view/)]

---
# Updating with views
.center[[Updating with views MariaDB](https://mariadb.com/kb/en/inserting-and-updating-with-views/)]