How To Use ‘Views’ In Your Applications Database

What are views?

Images from essentialsql.com

Think of VIEWS like temporary tables in your Application Database, or think of it as virtual TABLES. By virtual, we imply that views don’t contain any real data, they are just holding data or displaying data stored from other tables. Literally, it means a view into another Table. A view is dynamic and update automatically whenever the field in the reference table or the table you are viewing into changes or is updated. So it saves you time and space having to rewrite a query to fetch a particular data from a table each time you need to display it probably to a user in an app. Since they don’t store any data and just show you data from other Tables, they don’t take up any extra memory. This makes VIEW worth giving a shot in your applications.

Why use views?

VIEWS comes in handy when you want to allow a user to see a subset of the actual data in a TABLE. A VIEW can consist of a subset of one TABLE or more. For example, the players view below, is a subset of the players table.

Players View captured from the Players Table only First_name, Last_name and Goals is captured leaving out other details

Players Table

This view could be used to allow other players to see their fellow player’s goals but not allow them access to personal information like wages and phone_number.

Some Basic Views syntax

Let’s now look at the basic syntax used to create a view in MariaDB.

Creating Views

CREATE VIEW `view_name` AS SELECT statement;

If we want to see the SQL statements that make up a particular view, we can use the script shown below to do that.

How to see the SQL Statement used to create a view

SHOW CREATE VIEW `players_view`;

Dropping views

The DROP command can be used to delete a view from the database that is no longer required. The basic syntax to drop a view is as follows.

DROP VIEW ` players_view `;

Tips On How You Can Employ Views In Your Application Database

Assuming you have an application like a team’s football web-page and you want to display the three highest goal scorers in a team you can do the following:

1. Creating a Database

Open the terminal and type:

mysql −u root –p

You will be asked for a password you used during the installation process. Enter it.

2. Create a database called Manutd using the following command:

CREATE DATABASE Manutd;

3. In order to use the Database type:

USE Manutd;

Now that you have created a database called manutd you can create a table called players using the following command.

CREATE TABLE `players` (
`Player_id` INT(3) NOT NULL AUTO_INCREMENT,
`First_name` VARCHAR(25) NOT NULL ,
`Last_name` VARCHAR(25) NOT NULL ,
`position` VARCHAR(25) NOT NULL ,
`Home_address` VARCHAR(50) NOT NULL ,
`Phone_number` VARCHAR (12) NOT NULL,
`wages` FLOAT NOT NULL,
`goals` INT(4) NULL DEFAULT ‘0’,
PRIMARY KEY (`Player_id`)
)

Next populate your table as follows:

INSERT INTO `Players` (`First_name`, `Last_name`, `position`, `Home_address`, `Phone_number`, `wages`, `goals`)
VALUES('Eric', 'Bailey', 'Defender', '392 Carling Road', '321-555-4491', '500.00', 2),
('David', 'Degea', 'Goal Keeper', '324 Stanford Bridge', '321-555-3867', '1000.00', 2),
('Chris', 'Smalling', 'Defender', '1240 burkinham Avenue', '321-555-3456', '500.00', 5),
('Paul', 'Pogba', 'Midfielder', '183 Randle Boulevard', '321-555-2049', '1500.00', 7),
('Romelu', 'lukaku', 'Striker', '1023 London Lane', '321-555-6124', '500.00', 19),
('Alexis', 'Sanches', 'Striker', '442 Arming Court', '321-555-2476', '500.00', 10);

The database will look as follows:

Players Table

Now Where Does Views Come In?

Assuming you have an application like a team’s football web-page and you want to display the three highest goal scorers in a team(manutd) you can do the following:

1. You will run a query “to sort by goals” and display the top three goal scorers.

SELECT Player_id, First_name, goals FROM players ORDER BY goals DESC LIMIT 3

The following result will be displayed from the query:

Instead of having to type the SELECT query all the time you want to display the top three highest goal scorers, you can simply create a VIEW and once the goals is updated in the players table, the view automatically updates.

CREATE VIEW players_view AS
SELECT Player_id, First_name, goals FROM players ORDER BY goals DESC LIMIT 3

Players View highlighted in Blue

Instead of typing:

SELECT Player_id, First_name, goals FROM players ORDER BY goals DESC LIMIT 3

You can simply type:

SELECT First_name, goals FROM players_view

and still get the same result:

output from the query

now this underscores how important view is.

Further Reasons You Should Start using Views

“Views are also useful for security. In larger organizations, where many developers may be working on a project, views allow developers to access only the data they need. What they don’t need, even if it is in the same table, is hidden from them, safe from being seen or manipulated. It also allows queries to be simplified for developers”.

I will link up with you later on more thought on views.