Introduction to MySQL/MariaDB database SQL views

by

A database view is nothing but a virtual table, which does not contains data itself, but references data contained in other tables. Views are basically the result of stored queries which can vary on complexity and can be used, for example, to hide data from users, allowing access only on selected columns of a table, or simply to provide a different point of view on the existing data. In this tutorial we will see how to create, update, alter and drop a view on a MySQL, MariaDB database.

In this tutorial you will learn:

  • What is a view
  • How to create a view
  • How to update a view
  • How to alter a view
  • How to drop a view


mariadb-mysql

Software Requirements and Conventions Used

Software Requirements and Linux Command Line Conventions
Category Requirements, Conventions or Software Version Used
System Os-independent
Software A running MySQL/MariaDB database
Other Basic knwoledge of MySQL/MariaDB and relational databases concepts
Conventions # – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
$ – requires given linux commands to be executed as a regular non-privileged user

Creating a test database

For the sake of this tutorial we will a create a test database called “movies”. It will contain two tables: the first one will hold data about directors, the second one will contain information about titles and will be linked to the first one via a foreign key. To create our database we can issue the following commands from the MySQL/MariaDB shell:

MariaDB [(none)]> CREATE DATABASE movies;
MariaDB [(none)]> USE movies;
Database changed
MariaDB [movies]> CREATE TABLE director(
    -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> first_name VARCHAR(20) NOT NULL,
    -> last_name VARCHAR(20) NOT NULL,
    -> birth DATE NOT NULL,
    -> PRIMARY KEY(id)
    -> );

The next step is to insert some entries in the table:

MariaDB [movies]> INSERT INTO director(first_name, last_name, birth) VALUES
    -> ('Stanley', 'Kubrik', '1928-07-26'),
    -> ('Jeffrey', 'Adams', '1966-06-27'),
    -> ('Alfred', 'Hitchcock', '1899-08-13');

We can now create the “title” table and insert some entries into it:

MariaDB [movies]> CREATE TABLE title(
    -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(30) NOT NULL,
    -> genre VARCHAR(30) NOT NULL,
    -> release_date DATE NOT NULL,
    -> director_id SMALLINT UNSIGNED NOT NULL,
    -> PRIMARY KEY(id),
    -> FOREIGN KEY(director_id) REFERENCES director(id)
    -> );

MariaDB [movies]> INSERT INTO title(name, genre, release_date, director_id)
VALUES
    -> ('2001: A Space Odyssey', 'science fiction', '1968-04-02', 1),
    -> ('The Force Awakens', 'fantasy', '2015-12-14', 2),
    -> ('Psyco', 'horror', '1960-06-16', 3);

Now that we have some tables to work on, we can create a view.

Creating a view

A view is simply a virtual table which let us obtain an alternative “prospective” on data contained in real tables. We can easily create a view by selecting the columns we want to include in it from existing tables. Say for example we want our view to include the columns “name” and “genre” from the “title” table we created in our test database. Here is how we create it:

MariaDB [movies]> CREATE VIEW example AS
SELECT name,genre FROM title;

With the command CREATE VIEW, we created a view and call it “example”. The view is created by using the AS statement followed by the query necessary to obtain the data we want to include. The content of the view will be the result of the query:

MariaDB [movies]> SELECT * FROM example;
+-----------------------+-----------------+
| name                  | genre           |
+-----------------------+-----------------+
| 2001: A Space Odyssey | science fiction |
| The Force Awakens     | fantasy         |
| Psyco                 | horror          |
+-----------------------+-----------------+

We can restrict the data retrieved on the view just like we would do on a standard table, for example:

MariaDB [movies]> SELECT * FROM example WHERE genre = "science fiction";
+-----------------------+-----------------+
| name                  | genre           |
+-----------------------+-----------------+
| 2001: A Space Odyssey | science fiction |
+-----------------------+-----------------+

Providing specific names for the columns of the view

By default, the name of the columns of the created view will correspond to name of the columns included in the SELECT statement used to create it. In case we want to specify alternative names, we must provide them in parenthesis. The number of names must match the number of selected columns. Here is an example:

MariaDB [movies]> CREATE VIEW example (movie_name, movie_genre) AS SELECT name, genre FROM title;
MariaDB [movies]> SELECT * FROM example;
+-----------------------+-----------------+
| movie_name            | movie_genre     |
+-----------------------+-----------------+
| 2001: A Space Odyssey | science fiction |
| The Force Awakens     | fantasy         |
| Psyco                 | horror          |
+-----------------------+-----------------+

A view can be created using complex queries, and can include values resulting from functions. Here is an example of a view created by joining the “title” and “director” tables and by using the CONCAT function:

MariaDB [movies]> CREATE VIEW example (movie_name, movie_genre, movie_director) AS
    -> SELECT
    -> title.name
    -> , title.genre
    -> , CONCAT(director.first_name, " ", director.last_name)
    -> FROM
    -> title
    -> JOIN director ON title.director_id = director.id;

Here is the full content of the resulting view:

MariaDB [movies]> SELECT * FROM example;
+-----------------------+-----------------+------------------+
| movie_name            | movie_genre     | movie_director   |
+-----------------------+-----------------+------------------+
| 2001: A Space Odyssey | science fiction | Stanley Kubrik   |
| The Force Awakens     | fantasy         | Jeffrey Adams    |
| Psyco                 | horror          | Alfred Hitchcock |
+-----------------------+-----------------+------------------+

Updating a view

If some specifics conditions are met, it’s possible to update a view: the changes will be reflected in the underlying tables. To be able to update a view:

  • The view must be created by querying a single table and must map directly to it;
  • The view cannot contain aggregate values resulting from functions like SUM();
  • An operation on the view must correspond to an operation on a single row of the original table;

Let’s see an example. Suppose we are working on the view we created before:

+-----------------------+-----------------+
| movie_name            | movie_genre     |
+-----------------------+-----------------+
| 2001: A Space Odyssey | science fiction |
| The Force Awakens     | fantasy         |
| Psyco                 | horror          |
+-----------------------+-----------------+

Since the view respects the requisite we mentioned above, If we now update the genre of the “Psyco” movie, changing it from “horror” to “thriller”, the change will be reflected in the “title” table. Let’s verify it:

MariaDB [movies]> UPDATE example SET movie_genre = "thriller" WHERE movie_name = "Psyco";

If we now query the underlying “title” table, we can verify that the change has been applied:

MariaDB [movies]> SELECT * FROM title WHERE name = "Psyco";
+----+-------+----------+--------------+-------------+
| id | name  | genre    | release_date | director_id |
+----+-------+----------+--------------+-------------+
|  3 | Psyco | thriller | 1960-06-16   |           3 |
+----+-------+----------+--------------+-------------+

Altering a view

To change the definition of a view, we use the ALTER VIEW command. Each time we want to change the structure of a view, we have to re-write the SELECT statement used to create it. Just as an example, suppose we want to add the column “release_date” from the “title” table to our view: we cannot use a command like ADD COLUMN, we must provide a new query which comprehends the column we want to add:

MariaDB [movies]> ALTER VIEW example (movie_name, movie_genre, movie_release_date) AS SELECT name, genre, release_date FROM title;
SELECT * FROM example;
+-----------------------+-----------------+--------------------+
| movie_name            | movie_genre     | movie_release_date |
+-----------------------+-----------------+--------------------+
| 2001: A Space Odyssey | science fiction | 1968-04-02         |
| The Force Awakens     | fantasy         | 2015-12-14         |
| Psyco                 | thriller        | 1960-06-16         |
+-----------------------+-----------------+--------------------+

Dropping a view

Dropping a view is a very easy operation. The command used to accomplish the task is DROP VIEW. In this case, to remove our “example” view we would run:

DROP VIEW example;

Closing thoughts

In this article we saw how we can use MySQL/MariaDB views to gather a different point of view on the data contained in a database table. We saw how to create a view, how to alter its structure, how we can update it if some requirements are met, and how to drop it. If you are interested in other MySQL/MariaDB topics, you can take a look at our articles on the subject, for example, those about the UNION or JOIN statements.

Related Linux Tutorials:

Comments and Discussions
Linux Forum